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
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 |