Unexplained lock creating table

From: Wes <wespvp(at)syntegra(dot)com>
To: pgsql general <pgsql-general(at)postgresql(dot)org>
Subject: Unexplained lock creating table
Date: 2006-04-22 06:20:49
Message-ID: C06F3571.2380D%wespvp@syntegra.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
COPY into header_1

open connection 2
begin
create table header_2
COPY into header_2

open connection 3
begin
create table header_3
COPY into header_3

[ potentially more connections ]

end copy 1
end copy 2
end copy 3

commit 1
commit 2
commit 3

After the database is initialized, I run the application. It creates table
header_1 and initiates the copy (transaction still pending). However, when
it tries to create table header_2, it hangs. A ps shows

postgres: test trace [local] COPY
postgres: test trace [local] CREATE TABLE waiting

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.

The problem occurs only when the database has been freshly initialized and
no child table has ever existed.

I confirm this by:

1. Reinitialize database.
2. Run application. Verify hang occurs. I can rerun step 2 any number of
times and it continues to hang.
3. Create header_dummy using psql
4. Drop header_dummy
5. Run application - works.

I can repeat this with the 'detail' table. It is 100% reproducible.

What's going on?

Wes

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew - Supernews 2006-04-22 08:51:58 Re: Unexplained lock creating table
Previous Message Mavinakuli, Prasanna (STSD) 2006-04-22 03:15:49 Re: HUGE Stack space is gettiing consumed