Phantom tables by create or replace tables and views in parallel transactions

Detail name	Value
Changelog Number	5485
Type	Bug
Status	Resolved
Affected Versions	EXASOL 6.0.0, EXASolution 5.0.0, Exasol 6.1.beta1
Fix Versions	Exasol 6.1.0
Resolution Date	2018-11-06

Background

As "phantom objects" we classify objects that are not accessible/visible to the users (DBA included) but are still using up system resources. This means they are not visible in catalog system tables and no queries can be performed on them. They are harmless in the sense that no query may accidentally use them, but phantom tables do use space like normal tables do. For large phantom tables, the extra space used may be very large.

Synopsis

We have discovered a scenario where phantom tables are created when two parallel transactions execute create or replace table }} and {{create or replace view statements respectively, where the view uses the table.

Details

The following table details the scenario and helps explain how phantom tables come into being:
Assumption: We assume we have view v on table phantom:

create table phantom (i int);
create view v as select * from phantom;

Session 1	Session 2	Details
set autocommit off	set autocommit off	Autocommit switched off in both sessions
create or replace table phantom ...		Table phantom is recreated, meaning old table dropped, now a brand new table is created
	create or replace view v as select * from phantom	The newly created view refers the old phantom table
commit;		new table committed
	commit	new view committed

Notes:

• The view uses the new table but internally still refers to the old one preventing the deletion of the old table
• The order in which the transactions commit is irrelevant, either way the phantom table is generated
• Other scenarios which replace an existing table to one with the same name lead to the very same outcome (e.g. create table tmp; drop table phantom; rename table tmp to phantom; )

Workaround

• Remove the phantom tables: the only way to remove the phantom tables is to restart the database and set the command-line parameter -removePhantomViewReferences=1
• Minimize phantom table effect: avoid executing in parallel transactions create or replace table and create or replace view statements with the view using the table. If this cannot be avoided, one can miminize the effect of phantom tables by executing truncate table before dropping the table - this way very little space is kept occupied and never used.