On 2006-04-22, Wes <wespvp@xxxxxxxxxxxx> 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
