Re: Unexplained lock creating table

On 2006-04-22, Wes <wespvp(at)syntegra(dot)com> wrote:
> I have a C application (libpq) that uses partitioning. I create parent
> tables 'header' and 'detail'. The application reads opens multiple
> connections, creates the child tables, and uses COPY to import the data:
>
> open connection 1
> begin
> create table header_1

If this is the first child table of "header", which it presumably is, then
the pg_class row for "header" has to be updated to set relhassubclass. Since
you do not commit the create before continuing, this backend continues to
hold a row lock on the updated, uncommitted pg_class row while you do...

> COPY into header_1
>
> open connection 2
> begin
> create table header_2

At this point connection 2, which does not yet see the existence of header_1
and the updated pg-class row for "header" (since they are not yet committed
and are thus invisible even in SnapshotNow), _also_ believes it needs to
update the pg_class row for "header" for the same reason. However, the
update attempt immediately runs into the locked/uncommitted row belonging
to connection 1, and must therefore wait on the lock before proceeding...

(If in fact you allow connection 1 to complete and commit, the create table
in connections 2+ may then bail out with a "tuple concurrently updated"
error, since catalog updates don't generally have the recheck-after-lock
logic used for user queries in read-committed mode, so can't cope with the
fact that another connection updated the tuple. If connection 1 aborts the
transaction instead, then connection 2 can proceed.)

[...]
> However, if I force table header_1 to be created outside the COPY
> transaction (using psql, manually committing the transaction from within
> gdb, etc.), then run the application, it works regardless of the number of
> open connections/transactions.
>
> I then drop all the child tables, leaving the parent table, and rerun the
> application. It again works for all connections.

relhassubclass isn't reset to false when all child tables of a parent
table are removed. So next time through there is no need to update the
pg_class row for the parent table.

So the simplest workaround is probably to ensure that you create at least
one partition in each table at the outset, before trying to actually load
any data. You've already discovered that this works, but at least you now
know why :-)

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services