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]

  • [x] Probably requires lots of regression test changes
  • [x] Likely to find Citus bugs
  • [x] Maybe merge mx- schedules as all the schedules are now MX
  • [x] Local tables: All tables are auto-converted to Citus local tables
  • [ ] Coordinator is always added to the metadata
  • [x] Automatically convert Local tables to Citus Local Tables at CREATE TABLE time
  • [x] Do not remove local tables from metadata automatically https://github.com/citusdata/citus/issues/4691
  • [ ] Support logical replication for local tables https://github.com/citusdata/citus/issues/4526
  • [ ] Not undistribute local table added to metadata when creating reference or distributed table #4692
  • [x] Support partitioned local tables [1.5 weeks] #5179
  • [ ] Prepared transactions on local tables #4509
  • [ ] Local tables + MX + Triggers https://github.com/citusdata/citus/issues/4148
  • [ ] Reconsider switching to local execution in transactions https://github.com/citusdata/citus/issues/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 #4812 · citusdata/citus (github.com)
  • [x] at least support Identity Columns #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

• [x] Sequences:

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

• [x] Distributed views

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

• [x] Foreign tables #4947

• [x] Observibility

  • [x] #5190
  • [x] In general, make sure that citus_dist_stat_activity, citus_worker_stat_activity, citus_lock_waits works as expected. See #2966
  • [x] Cluster health checks https://github.com/citusdata/citus/issues/4276

• [x] Function propagation is simplified

  • [x] Function call in DEFAULT column expression #3851
  • [x] Call stored procedure/function from any node
  • [x] Call a function in a query
  • [x] #5482

• [x] Propagate pg_dist_object https://github.com/citusdata/citus/issues/5065

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

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

• [x] Upgrades / downgrades

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

• [x] Connection scaling

  • [x] (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
  • [x] PG 14 connection scaling benchmarks

• [x] Testing

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

• [x] Multi-user testing

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

• [ ] Various MX specific deadlocks&bugs requiring some testing

  • [x] #1199
  • [ ] #1554 (testing)
  • [ ] #2373
  • [x] #4366
  • [x] Re-syncing metadata breaks sequences #5303
  • [x] Run more regression tests under Citus MX
  • [x] Drop schema fails 3741
  • [x] Some code refactoring #5283

• [ ] Remove code

  • [x] Maintenance daemon codes regarding metadata syncing can be removed
  • [x] create_distributed_function syncing the metadata can be removed
  • [x] Check use of ShouldSyncTableMetadata
  • [x] 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 https://github.com/citusdata/citus/issues/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