Sync metadata by default & for all clusters

[onderkalaci]

In this issue, I wanted to list some of the things we need in order to enable Citus MX on all clusters. Assuming that we enable this feature on Citus 11.0, all the clusters upgrading from earlier version would sync metadata to all nodes.

• Sync metadata to all nodes by default / sync always [3 weeks]
  • Probably requires lots of regression test changes
  • Likely to find Citus bugs
  • Maybe merge mx- schedules as all the schedules are now MX
  • Local tables: All tables are auto-converted to Citus local tables
  • Coordinator is always added to the metadata
  • Automatically convert Local tables to Citus Local Tables at CREATE TABLE time
  • Do not remove local tables from metadata automatically Not automatically remove user-added local tables from metadata #4691
  • Support logical replication for local tables Logical replication for Citus local tables #4526
  • Not undistribute local table added to metadata when creating reference or distributed table Not undistribute local table added to metadata when creating reference or distributed table #4692
  • Support partitioned local tables [1.5 weeks] Support partitioning for citus local tables #5179
  • Prepared transactions on local tables Cannot use Citus local table in a prepared transaction #4509
  • Local tables + MX + Triggers Do not allow accesses to citus local tables with triggers on mx workers in a xact block #4148
  • Reconsider switching to local execution in transactions Reconsider switching to local execution in transactions #4179: If all local tables are Citus local tables, can we avoid to switch to local execution immediately?
  • Table Level DDL Limitations apply to Local Tables: DDL Support · Issue DDL Support #4812 · citusdata/citus (github.com)
  • at least support Identity Columns Support IDENTITY columns with citus tables and table operations #4660
  • Enable volatile functions in DML

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

• Sequences:

  • Simplify code as we can always sync all the sequences immediately
  • We only support big-int sequences properly. Is that a problem

• Distributed views

  • Automatically propagate views to all nodes
  • What about views accessing local tables? They should fail immediately

• Foreign tables Metadata syncing fails when there are foreign distributed tables #4947

• Observibility

  • citus_dist_stat_activity should show idle connections #5190
  • In general, make sure that citus_dist_stat_activity, citus_worker_stat_activity, citus_lock_waits works as expected. See CREATE INDEX CONCURRENTLY's locks cannot be detected #2966
  • Cluster health checks Create a Citus healthcheck function/view for basic sanity checks #4276

• Function propagation is simplified

  • Function call in DEFAULT column expression Cannot create Citus table with a column that has a function call as default #3851
  • Call stored procedure/function from any node
  • Call a function in a query
  • SQL Functions accessing local tables #5482

• Propagate pg_dist_object Propagate pg_dist_object for metadata nodes #5065

• Some features that are not very relevant to Citus MX. Can we drop support for them?

  • Replication factor > 1
  • Append/range partitioned tables: We currently do not sync metadata for these

• Upgrades / downgrades

  • Sync metadata to all the nodes
  • Make sure that the coordinator is added to the metadata [regarding local tables]
  • Any problems in case other extension exists? postgres_fdw or dblink?
  • Should we stop metadata sync on downgrades?
  • Can we have --check-upgrade type of UDF?

• Connection scaling

  • (Marco) Random thing that bugs me a bit about MX which I'm not sure I mentioned: XXX did transactions with many inserts across different shards in large batches, causing every transaction to span across all nodes, which also means that even with outbound pgbouncers and a million nodes you still cannot handle more than max_connections concurrent transactions
  • PG 14 connection scaling benchmarks

• Testing

  • Can we run more queries from the workers on the regression tests?

• Multi-user testing

  • https://github.com/citusdata/citus-enterprise/issues/657

• Various MX specific deadlocks&bugs requiring some testing

  • Improve concurrent start_metadata_sync_to_node and other metadata changes #1199
  • Distributed deadlock detection improvement - Add regression tests for MX #1554 (testing)
  • truncate after select may cause distributed deadlock in mx nodes #2373
  • start_metadata_sync_to_node concurrently with DROP TABLE may error #4366
  • Re-syncing metadata breaks sequences Re-syncing metadata breaks sequences #5303
  • Run more regression tests under Citus MX
  • Drop schema fails 3741
  • Some code refactoring rename get_all_active_transactions -> get_all_active_distributed_executions or such #5283

• Remove code

  • Maintenance daemon codes regarding metadata syncing can be removed
  • create_distributed_function syncing the metadata can be removed
  • Check use of ShouldSyncTableMetadata
  • Can we have ErrorIfAnyMetadataNodeOutOfSync anymore? All the metadata nodes should always be synced?
  • Simplify node APIs. Currently we have workers/metadata workers etc.

[marcocitus]

Local tables: All tables are auto-converted to Citus local tables #5305

This relates to the discussion in #3851 . I don't think it is strictly necessary for an MVP as long as we have a fallback mechanism for views and functions.

It does seem like a "should have", given the general problem of local tables in MX.

[marcocitus]

Maybe merge mx- schedules as all the schedules are now MX

It would be good if we can have a SQL-oriented schedule that gets repeated on a worker.

[onderkalaci]

I don't think it is strictly necessary for an MVP as long as we have a fallback mechanism for views and functions.

Agreed, this issue mostly aims to describe Ultimate experience :)

local tables #5305

Github seems to add a new feature, if you click on a task item, it opens an issue. So, 5305 opened accidentally. I agree that 3851 has more information