Search code examples
postgresqlcitus

In Citus, how do I lock a distributed table during a COPY?

Citus does not support multi-statement transactions for data modification commands, making it impossible to use the LOCK command for preventing concurrent access during data migrations.

Is there any way to lock access to a table during an initial data migration?

Solution

  • Though Citus does prevent multi-statement transactions which contain modifications, this check apparently does not apply to the new COPY support. So if you are actually doing a COPY and not INSERT, UPDATE, or DELETE, you can use BEGIN and LOCK to get the single-write semantics you desire:

    BEGIN;
LOCK target_table IN ACCESS EXCLUSIVE MODE;

COPY target_table FROM '/file/on/server';
\copy target_table from '~/local/file'

COMMIT;

    If a concurrent session attempts an INSERT, UPDATE, or DELETE against this table, it will block until the above transaction block commits.