Adding local tables to metadata: Limitations & TO-DO's

Limitations:

Existing limitations affecting citus local tables as well as other citus tables:

• Several limitations for DDL commands #4812
• Not supporting INSERT INTO citus_local_table with subqueries.
• Not supporting IDENTITY columns #4660
• Not allowing citus local tables to be involved in any multi-level partitioning.
• Not allowing citus local tables to be involved in any inheritance relationship with other tables.

Limitations specific to citus local tables:

• Not supporting foreign keys from reference tables to citus local tables with CASCADE, SET NULL & SET DEFAULT behaviors (RESTRICT & NO ACTION behaviors are still allowed)
• Not allowing UPDATE/DELETEs involving postgres local and citus local tables
• Queries from MX nodes on citus local tables having triggers might fail due to 2PC within xact blocks https://github.com/citusdata/citus/issues/4148
• Not allowing UPDATE/DELETE of reference tables selecting from citus local tables (other way around is just OK).

Limitations that we might not think to resolve:

• ~Not supporting creating foreign keys between citus local tables and postgres tables Solving this problem is equivalent to bringing foreign key support between postgres local tables and reference tables and that was the main point that leaded us to bring citus local tables :)~

Considerations & further improvements on citus local tabes:

• We don't allow creating citus local tables in MX worker nodes or moving citus local tables from coordinator to other MX worker nodes.
• Related with above item, ColocationId for citus local tables is set to INVALID_COLOCATION_ID. We might think of setting common colocationIds for citus local tables thinking of them as grouped according to the nodes that their shard live after enabling above item.

Some other TO-DO's:

• ~We need to fix citus local table creation from tables with GENERATED STORED columns (#4151)~
• ~We need to adjust pre-existing views on citus local tables after citus local table creation so that pre-existing views point to the shell relation, not to the shard relation. https://github.com/citusdata/citus/issues/4147~
• Enable volatile functions in DML's:

update citus_local_table set a = random()::int;
DEBUG: functions used in UPDATE queries on distributed tables must not be VOLATILE
ERROR: functions used in UPDATE queries on distributed tables must not be VOLATILE

• Support deferred fast-path planning to allow Citus local tables rely on caching.
• Support this using ctes's wrapping distributed tables to modify postgres local tables:

WITH cte  AS (SELECT * FROM distributed_table) update postgres_local_table SET a = cte.a FROM cte;

(Hint: DeferErrorIfUnsupportedModifyQueryWithPostgresLocalTable)

• Test function call delegation that was implemented for reference tables. Should it work with Citus local tables ? https://github.com/citusdata/citus/pull/4143#discussion_r484906356
• Not automatically remove user-added local tables from metadata (#4691)
• Not undistribute local table added to metadata when creating reference or distributed table (#4692)

Not allowing UPDATE/DELETEs involving postgres local and citus local tables

This seems to work for me on master.

Not allowing UPDATE/DELETEs involving postgres local and citus local tables

This seems to work for me on master.

Updated the issue with new improvements made in 10.0 and with new improvement ideas, soon will also look into sql coverage items to see what we improved in this area.

Multi-level partitioned tables are also not supported

Updateable views are not supported on Citus local tables:

show citus.use_citus_managed_tables ;
┌────────────────────────────────┐
│ citus.use_citus_managed_tables │
├────────────────────────────────┤
│ on                             │
└────────────────────────────────┘


CREATE TABLE aa (a int, b int);
CREATE VIEW aav1 AS SELECT * FROM aa;
INSERT INTO aav1 VALUES (1,2);
ERROR:  cannot modify views when the query contains citus tables

Another relatively important limitation: https://github.com/citusdata/citus/issues/6318