[BUGS] 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 at the 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 COMMIT DELETE 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 contained example, 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
pgp0.pgp
Description: PGP signature
Re: [BUGS] create temp table ... inherits
Robert Creager <[EMAIL PROTECTED]> writes: > CREATE TEMP TABLE is being executed in two processes (same Perl/DBI script). It > occurs when the scripts are executed at the 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 COMMIT DELETE 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] create temp table ... inherits
When grilled further on (Sun, 12 Oct 2003 13:06:25 -0400), Tom Lane <[EMAIL PROTECTED]> 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 pgp0.pgp Description: PGP signature
Re: [BUGS] create temp table ... inherits
When grilled further on (Sun, 12 Oct 2003 13:06:25 -0400), Tom Lane <[EMAIL PROTECTED]> 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 pgp0.pgp Description: PGP signature
Re: [BUGS] create temp table ... inherits
Robert Creager <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
