pg_get_expr() hangs with uncommitted DDL transaction

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: pg_get_expr() hangs with uncommitted DDL transaction
Date: 2014-11-11 12:33:12
Message-ID: m3svm8$41j$1@ger.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I noticed the following behaviour in the JDBC driver:

In one transaction run an ALTER TABLE ADD COLUMN ... statement with auto-commit off, but don't commit the statement

In another transcation, call DatabaseMetaData.getColumns() for this table - this call will wait until the first transaction commits or rolls back.

I could narrow this down to the usage of pg_catalog.pg_get_expr() in the statement that the JDBC driver uses.

A stripped down version of that statement is this:

SELECT a.attname, .....
pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS adsrc -- << this is the "problem"
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid)
JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid)
LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum)
WHERE a.attnum > 0
AND NOT a.attisdropped
AND c.relname LIKE 'foo'
AND n.nspname LIKE 'public';

When the call to pg_catalog.pg_get_expr() is removed from this statement, it will return even if the ALTER TABLE has not been committed.

Is there a reason why pg_catalog.pg_get_expr() will wait until the exclusive lock on the table is released?
The value from pg_attrdef.adbin can be selected without any problems, so it appears to be something inside the function.

Versions used:

Postgres: PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit
JDBC Driver: PostgreSQL 9.3 JDBC4 (build 1102)

Regards
Thomas

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2014-11-11 13:58:49 Re: trigger Before or After
Previous Message jaime soler 2014-11-11 10:11:09 Re: reindex table deadlock