I'm guessing that CREATE TABLE in itself doesn't take an ACCESS
EXCLUSIVE lock because there's nothing yet to lock. But can CREATE
TABLE ... INHERITS ... take an ACCESS EXCLUSIVE lock? Is it
functioning as an ALTER TABLE?
I'm dealing with an application that can potentially do ad hoc DDL.
It uses a PG/pgSQL function, and the only DDL statements in the
function are CREATE TABLE and CREATE INDEX statements. But I'm
noticing that during the backup process (with pg_dump or pg_dumpall),
the function is acquiring ACCESS EXCLUSIVE locks and bringing the
application to its knees. This seems to be a result of connections
backing up waiting for the DDL to finish, and the DDL can't finish
until the backup process finishes because of the function's ACCESS
EXCLUSIVE lock conflicting with the database-wide ACCESS SHARE locks
acquired by the backup process.
--
Thomas F. O'Connell
Database Architecture and Programming
Co-Founder
Sitening, LLC
http://www.sitening.com/
3004 B Poston Avenue
Nashville, TN 37203-1314
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)
