BUG: Repeatedly executing DDL on an Iceberg table seems to cause a connection leak in the meta_store.

[cyberchen98]

Describe the bug

As title, connection leakage may occur in managing the iceberg catalog.

Error message/log

meta node log

Exception in thread "Thread-1095" java.lang.RuntimeException: org.apache.iceberg.jdbc.UncheckedSQLException: Failed to connect: jdbc:postgresql://metastore-postgresql.tpch-iceberg-table-test-01:5432/risingwave
        at com.risingwave.connector.catalog.JniCatalogWrapper.create(JniCatalogWrapper.java:130)
Caused by: org.apache.iceberg.jdbc.UncheckedSQLException: Failed to connect: jdbc:postgresql://metastore-postgresql.tpch-iceberg-table-test-01:5432/risingwave
        at org.apache.iceberg.jdbc.JdbcClientPool.newClient(JdbcClientPool.java:90)
        at org.apache.iceberg.jdbc.JdbcClientPool.newClient(JdbcClientPool.java:35)
        at org.apache.iceberg.ClientPoolImpl.get(ClientPoolImpl.java:143)
        at org.apache.iceberg.ClientPoolImpl.run(ClientPoolImpl.java:70)
        at org.apache.iceberg.ClientPoolImpl.run(ClientPoolImpl.java:65)
        at org.apache.iceberg.jdbc.JdbcCatalog.initializeCatalogTables(JdbcCatalog.java:160)
        at org.apache.iceberg.jdbc.JdbcCatalog.initialize(JdbcCatalog.java:146)
        at org.apache.iceberg.CatalogUtil.loadCatalog(CatalogUtil.java:274)
        at com.risingwave.connector.catalog.JniCatalogWrapper.create(JniCatalogWrapper.java:127)
Caused by: org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for roles with the SUPERUSER attribute
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
        at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2845)
        at org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:176)
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:323)
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54)
        at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:273)
        at org.postgresql.Driver.makeConnection(Driver.java:446)
        at org.postgresql.Driver.connect(Driver.java:298)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681)
        at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:190)
        at org.apache.iceberg.jdbc.JdbcClientPool.newClient(JdbcClientPool.java:88)
        ... 8 more
2025-02-11T17:56:20.359257421Z ERROR risingwave_meta::rpc::ddl_controller: failed to create streaming job id=516 error=failed to validate sink: Java exception was thrown
2025-02-11T17:56:20.362601644Z  WARN risingwave_meta::rpc::ddl_controller: aborted streaming job id=516

meta_store(pg) log

2025-02-11 17:55:13.112 GMT [127367] FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute
2025-02-11 17:55:46.941 GMT [92] LOG:  checkpoint complete: wrote 384 buffers (2.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=38.288 s, sync=0.006 s, total=38.305 s; sync files=84, longest=0.003 s, average=0.001 s; distance=4647 kB, estimate=4647 kB; lsn=0/6D65A6F0, redo lsn=0/6D57D7C0
2025-02-11 17:56:20.358 GMT [127468] FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

To Reproduce

dev=> create table lineitem(
    l_orderkey      BIGINT,
    l_partkey       BIGINT,
    l_suppkey       BIGINT,
    l_linenumber    BIGINT,
    l_quantity      NUMERIC,
    l_extendedprice NUMERIC,
    l_discount      NUMERIC,
    l_tax           NUMERIC,
    l_returnflag    VARCHAR,
    l_linestatus    VARCHAR,
    l_shipdate      DATE,
    l_commitdate    DATE,
    l_receiptdate   DATE,
    l_shipinstruct  VARCHAR,
    l_shipmode      VARCHAR,
    l_comment       VARCHAR
) ENGINE = ICEBERG;
CREATE_TABLE
dev=> drop table lineitem;
DROP_TABLE
...
-- Repeat "create" and "drop" will encounter --
Caused by these errors (recent errors listed first):
  1: gRPC request to meta service (call `/ddl_service.DdlService/CreateSink`) failed: Internal error
  2: failed to validate sink
  3: Java exception was thrown

Expected behavior

No response

How did you deploy RisingWave?

RisingWave Cloud

The version of RisingWave

dev=> select version();
                                       version                                        
--------------------------------------------------------------------------------------
 PostgreSQL 13.14.0-RisingWave-2.3.0-alpha (f9e420f8743215eff528f81bdd8478bb8467abf5)
(1 row)

Additional context

meta_store:

Before executing "create" and "drop" table.

risingwave=# SELECT count(*), usename FROM pg_stat_activity GROUP BY usename;
 count | usename  
-------+----------
     4 | 
     2 | postgres
     4 | root
(3 rows)

First time encountered an error.

risingwave=# SELECT count(*), usename FROM pg_stat_activity GROUP BY usename;
 count | usename  
-------+----------
     4 | 
     2 | postgres
    95 | root
(3 rows)

After a long time without any execution.

risingwave=# SELECT count(*), usename FROM pg_stat_activity GROUP BY usename;
 count | usename  
-------+----------
     4 | 
     2 | postgres
    38 | root
(3 rows)

After executing SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND usename = 'root';

risingwave=# SELECT count(*), usename FROM pg_stat_activity GROUP BY usename;
 count | usename  
-------+----------
     4 | 
     2 | postgres
     4 | root
(3 rows)

[chenzl25]

Thanks for the report. I will take a look.