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