Thread: create temp table ... inherits
Hey folks, Running 7.4b4 on a 2 processor SMP system: PostgreSQL 7.4beta4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 (Mandrake Linux 9.1 3.2.2-3mdk) CREATE TEMP TABLE is being executed in two processes (same Perl/DBI script). It occurs when the scripts are executed atthe same time. One of the processes dies with the following: Oct 12 09:39:45 thunder postgres[31398]: [2-1] ERROR: tuple concurrently updated Oct 12 09:39:45 thunder postgres[31398]: [2-2] STATEMENT: CREATE TEMP TABLE temp_obs_v() INHERITS( obs_root ) ON COMMITDELETE ROWS Is this something I'm going to have to deal with, or is it a bug? I found reference, but no follow up, at http://archives.postgresql.org/pgsql-php/2001-10/msg00055.php. I've tried without success to create a small self containedexample, but can reproduce at will on my system. tassiv=# \d obs_root Table "public.obs_root" Column | Type | Modifiers ---------+---------+------------------------------------------------ x | real | not null y | real | not null imag | real | not null smag | real | not null loc | spoint | not null obs_id | integer | not null default nextval('"obs_id_seq"'::text) file_id | integer | not null use | boolean | default false solve | boolean | default false star_id | integer | mag | real | Foreign-key constraints: "$1" FOREIGN KEY (file_id) REFERENCES files(file_id) "$2" FOREIGN KEY (star_id) REFERENCES catalog(star_id) Cheers, Rob -- 09:55:17 up 72 days, 2:22, 4 users, load average: 1.04, 1.01, 1.00
Robert Creager <Robert_Creager@LogicalChaos.org> writes:
> CREATE TEMP TABLE is being executed in two processes (same Perl/DBI script). It occurs when the scripts are executed
atthe same time. One of the processes dies with the following:
> Oct 12 09:39:45 thunder postgres[31398]: [2-1] ERROR: tuple concurrently updated
> Oct 12 09:39:45 thunder postgres[31398]: [2-2] STATEMENT: CREATE TEMP TABLE temp_obs_v() INHERITS( obs_root ) ON
COMMITDELETE ROWS
The two temp tables share a common parent?
My guess is that it's failing because setRelhassubclassInRelation just
does an unconditional simple_heap_update even when it doesn't need to.
We could fix that fairly easily, which would greatly reduce the odds
of the problem although not prevent it completely. (I think complete
prevention would require locking the parent table, which cure seems
worse than the disease.)
Can you get a backtrace from the errfinish call to confirm this theory?
regards, tom lane
When grilled further on (Sun, 12 Oct 2003 13:06:25 -0400), Tom Lane <tgl@sss.pgh.pa.us> confessed: > > Can you get a backtrace from the errfinish call to confirm this theory? Breakpoint 1, 0x081b4806 in errfinish () (gdb) bt #0 0x081b4806 in errfinish () #1 0x081b5546 in elog_finish () #2 0x0807cc32 in simple_heap_update () #3 0x080d9d57 in setRelhassubclassInRelation () #4 0x080d92ee in MergeAttributes () #5 0x080d8bc6 in DefineRelation () #6 0x08159d68 in ProcessUtility () #7 0x08158ce5 in PortalRunUtility () #8 0x08158f78 in PortalRunMulti () #9 0x0815887e in PortalRun () #10 0x08155b5c in exec_simple_query () #11 0x08157b87 in PostgresMain () #12 0x0813663f in BackendFork () #13 0x08136108 in BackendStartup () #14 0x0813498d in ServerLoop () #15 0x08134318 in PostmasterMain () #16 0x0810a09c in main () #17 0x4025e7f7 in __libc_start_main () from /lib/i686/libc.so.6 Cheers, Rob -- 11:37:34 up 72 days, 4:04, 4 users, load average: 3.15, 3.14, 2.56
When grilled further on (Sun, 12 Oct 2003 13:06:25 -0400), Tom Lane <tgl@sss.pgh.pa.us> confessed: > > The two temp tables share a common parent? Ops, forgot. Yes. "obs_root" (which is what the temp table inherits from), is a persistent table. The temp table is used to filter data going into other tables derived from "obs_root". Cheers, Rob -- 13:57:08 up 72 days, 6:24, 4 users, load average: 2.00, 2.02, 2.00
Robert Creager <Robert_Creager@LogicalChaos.org> writes:
> Tom Lane <tgl@sss.pgh.pa.us> confessed:
>> Can you get a backtrace from the errfinish call to confirm this theory?
> Breakpoint 1, 0x081b4806 in errfinish ()
> (gdb) bt
> #0 0x081b4806 in errfinish ()
> #1 0x081b5546 in elog_finish ()
> #2 0x0807cc32 in simple_heap_update ()
> #3 0x080d9d57 in setRelhassubclassInRelation ()
> #4 0x080d92ee in MergeAttributes ()
> #5 0x080d8bc6 in DefineRelation ()
Yup, sho' nuf. Will fix.
regards, tom lane