robertmu opened a new issue, #1265:
URL: https://github.com/apache/cloudberry/issues/1265
## 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.
```text
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.
```text
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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
