apache
Feature Request: Support `LOCK TABLE ... COORDINATOR ONLY` syntax
Issue Description
Cloudberry currently lacks support for the LOCK TABLE ... IN ACCESS SHARE MODE COORDINATOR ONLY syntax, a feature present in Greenplum Database 7 and later.
This option was introduced in GPDB 7 to provide a performance boost for utilities like gpbackup when locking a large number of tables. By acquiring locks only on the coordinator, it avoids the overhead of dispatching lock requests to all segment nodes. The original commit noted this was particularly useful for ACCESS SHARE locks, which are primarily used to prevent DDL changes (like ALTER TABLE or DROP TABLE) that are initiated through the coordinator anyway.
The absence of this feature in Cloudberry forces tools to use the standard LOCK TABLE command, which can lead to unnecessary network traffic and increased lock resource consumption in large-scale scenarios.
Reproduction and Evidence
The following raw psql session logs demonstrate the syntax incompatibility on Cloudberry versus the expected behavior on Greenplum 7.
Cloudberry (Actual Behavior)
The command fails with a syntax error.
cbdb=# select version();
version
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.4 (Apache Cloudberry 2.1.0-devel+dev.2019.g1cc76495e18 build dev) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit compiled on Jul 18 2025 11:29:40
(1 row)
cbdb=# create table ao_test(a int, b int) with(appendonly=true);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
cbdb=#
cbdb=# begin;
BEGIN
cbdb=*# lock table ao_test IN ACCESS SHARE MODE COORDINATOR ONLY;
ERROR: syntax error at or near "COORDINATOR"
LINE 1: lock table ao_test IN ACCESS SHARE MODE COORDINATOR ONLY;
^
cbdb=!# end;
ROLLBACK
Greenplum 7 (Expected Behavior)
The command executes successfully.
gpdb7=# select version();
version
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.12 (Greenplum Database 7.0.0-beta.0+482967c1b4 build dev) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit compiled on Nov 8 2024 23:43:47 Bhuvnesh C.
(1 row)
gpdb7=#
gpdb7=# create table ao_test(a int, b int) with(appendonly=true);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
gpdb7=#
gpdb7=# begin;
BEGIN
gpdb7=# lock table ao_test IN ACCESS SHARE MODE COORDINATOR ONLY;
LOCK TABLE
gpdb7=#
gpdb7=# end;
COMMIT
Impact
The lack of this feature creates a feature gap between Cloudberry and Greenplum 7+. While described as a "small performance boost" in the original implementation, for tools like gpbackup operating on databases with thousands of tables, this can translate to a noticeable difference in performance and resource usage.
Implementing this feature would bring Cloudberry into closer alignment with GPDB 7, allowing ecosystem tools to operate more efficiently and consistently across both platforms.
It is related to https://github.com/greenplum-db/gpdb-archive/commit/2f88f4c0667af1f4061cbddee64b6ca6ef028216
