Thread: Relation 0 does not exist
The subject says it all:
DEBUG: StartTransactionCommand
DEBUG: ProcessQuery
ERROR: Relation 0 does not exist
LOG: statement: INSERT INTO trans (meter_id,stats_id,flowindex,firsttime,firstt...
The code generating the insert statement didn't change, just the server - but
what does it mean? There is no 0. The most complicated are bits like:
('08:52:11 Mon 22 Jul 2002'::timestamp without time zone+'5536695.89 second')::time
Cheers,
Patrick
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> The subject says it all:
> ERROR: Relation 0 does not exist
> LOG: statement: INSERT INTO trans (meter_id,stats_id,flowindex,firsttime,firstt...
Could we see the *whole* query? And the schemas of the table(s) it
uses? Seems like a bug to me, but without enough context to reproduce
it I can't do much.
regards, tom lane
On Wed, Sep 25, 2002 at 04:52:30PM -0400, Tom Lane wrote:
> Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> > The subject says it all:
> > ERROR: Relation 0 does not exist
> > LOG: statement: INSERT INTO trans (meter_id,stats_id,flowindex,firsttime,firstt...
>
> Could we see the *whole* query? And the schemas of the table(s) it
> uses? Seems like a bug to me, but without enough context to reproduce
> it I can't do much.
DEBUG: StartTransactionCommand
DEBUG: ProcessQuery
ERROR: Relation 0 does not exist
LOG: statement: INSERT INTO trans
(meter_id,stats_id,flowindex,firsttime,firsttimed,firsttimet,firsttimei,lasttime,lasttimed,lasttimet,lasttimei,sourcepeeraddress,sourcepeername,sourcetransaddress,destpeeraddress,desttransaddress,sourcetranstype,frompdus,fromoctets,topdus,tooctets,deltafromoctets,deltatooctets)
VALUES(411, currval('stats_id_seq'),5,'08:52:11 Mon 22 Jul 2002'::timestamp without time zone+'5536695.89
second',('08:52:11Mon 22 Jul 2002'::timestamp without time zone+'5536695.89 second')::date,('08:52:11 Mon 22 Jul
2002'::timestampwithout time zone+'5536695.89 second')::time,553669589,'08:52:11 Mon 22 Jul 2002'::timestamp without
timezone+'5660731.53 second',('08:52:11 Mon 22 Jul 2002'::timestamp without time zone+'5660731.53
second')::date,('08:52:11Mon 22 Jul 2002'::timestamp without time zone+'5660731.53
second')::time,566073153,'192.168.3.4','hostname.here.ac.uk',2,'192.168.3.2',53,17,14403,5271978,14419,1226291,507098::bigint,109306::bigint)
DEBUG: AbortCurrentTransaction
LOG: pq_recvbuf: unexpected EOF on client connection
DEBUG: proc_exit(0)
One thing which bugs me: I have a currval in there, and that is the very
first query which reaches the database, so it won't be "set", will it, but
then, how could it have worked for months with the other version of server?
(Source files are old - recompiled just in case postgres header files changed)
Hmm then it would have complained about Relation "stats_id_seq" no?
Table "public.trans"
Column | Type | Modifiers
--------------------+--------------------------------+-----------
meter_id | integer |
stats_id | integer |
flowindex | integer |
firsttime | timestamp(6) without time zone |
firsttimed | date |
firsttimet | time(0) without time zone |
firsttimei | integer |
lasttime | timestamp(6) without time zone |
lasttimet | time(0) without time zone |
lasttimed | date |
lasttimei | integer |
sourcepeeraddress | inet |
sourcepeername | text |
sourcetransaddress | integer |
destpeeraddress | inet |
destpeername | text |
desttransaddress | integer |
sourcetranstype | integer |
frompdus | integer |
fromoctets | integer |
topdus | integer |
tooctets | integer |
deltafromoctets | bigint |
deltatooctets | bigint |
dpndate | date |
nettype | integer |
Indexes: firsttimei_idx btree (firsttimei),
srcpeername_idx btree (sourcepeername)
Triggers: RI_ConstraintTrigger_14413070,
RI_ConstraintTrigger_14413073
Cheers,
Patrick
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> One thing which bugs me: I have a currval in there, and that is the very
> first query which reaches the database, so it won't be "set", will it, but
> then, how could it have worked for months with the other version of server?
Good question. Do you have any ON INSERT rules on that table?
Could you try setting a breakpoint at elog() to capture the stack trace
leading up to the error? (Note elog() will be called for each log entry
that's made, so if you just set the breakpoint at the start of the
routine, you'll have to 'continue' several times until the actual ERROR
call occurs.)
regards, tom lane
On Wed, Sep 25, 2002 at 05:49:17PM -0400, Tom Lane wrote:
> Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> > One thing which bugs me: I have a currval in there, and that is the very
> > first query which reaches the database, so it won't be "set", will it, but
> > then, how could it have worked for months with the other version of server?
>
> Good question. Do you have any ON INSERT rules on that table?
No
> Could you try setting a breakpoint at elog() to capture the stack trace
> leading up to the error?
#0 elog (lev=15, fmt=0x821133b "statement: %s") at elog.c:114
#1 0x81812db in elog (lev=20, fmt=0x8196b02 "Relation %u does not exist")
at elog.c:438
#2 0x80791a2 in relation_open (relationId=0, lockmode=2) at heapam.c:474
#3 0x8079329 in heap_open (relationId=0, lockmode=2) at heapam.c:602
#4 0x816d94b in RI_FKey_check (fcinfo=0xbfbfc884) at ri_triggers.c:212
#5 0x816dee1 in RI_FKey_check_ins (fcinfo=0xbfbfc884) at ri_triggers.c:506
#6 0x80d4d2b in ExecCallTriggerFunc (trigdata=0xbfbfc9ac, finfo=0x82dd01c,
per_tuple_context=0x83342d8) at trigger.c:974
#7 0x80d5852 in DeferredTriggerExecute (event=0x833801c, itemno=0,
rel=0x82f5494, finfo=0x82dd01c, per_tuple_context=0x83342d8)
at trigger.c:1497
#8 0x80d5a5f in deferredTriggerInvokeEvents (immediate_only=1 '\001')
at trigger.c:1620
#9 0x80d5c29 in DeferredTriggerEndQuery () at trigger.c:1775
#10 0x8136d57 in finish_xact_command () at postgres.c:894
#11 0x8136c25 in pg_exec_query_string (query_string=0x82d701c, dest=Remote,
parse_context=0x82818ac) at postgres.c:827
#12 0x8137e19 in PostgresMain (argc=6, argv=0xbfbfccb4,
username=0x825f925 "root") at postgres.c:1924
#13 0x811cad2 in DoBackend (port=0x825f800) at postmaster.c:2276
#14 0x811c3f9 in BackendStartup (port=0x825f800) at postmaster.c:1908
#15 0x811b5af in ServerLoop () at postmaster.c:993
#16 0x811b132 in PostmasterMain (argc=4, argv=0x825a040) at postmaster.c:774
#17 0x80f4ee5 in main (argc=4, argv=0xbfbfd4c0) at main.c:209
#18 0x8069880 in ___start ()
The definition of trans had:
Triggers: RI_ConstraintTrigger_14413070,
RI_ConstraintTrigger_14413073
I was inserting meter_id=411, stats_id=currval('stats_id_seq')
meter.id=411 exists. Hard to tell about the other one.. Still don't see
why this ever worked..
Cheers,
Patrick
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
>> Could you try setting a breakpoint at elog() to capture the stack trace
>> leading up to the error?
> #0 elog (lev=15, fmt=0x821133b "statement: %s") at elog.c:114
> #1 0x81812db in elog (lev=20, fmt=0x8196b02 "Relation %u does not exist")
> at elog.c:438
> #2 0x80791a2 in relation_open (relationId=0, lockmode=2) at heapam.c:474
> #3 0x8079329 in heap_open (relationId=0, lockmode=2) at heapam.c:602
> #4 0x816d94b in RI_FKey_check (fcinfo=0xbfbfc884) at ri_triggers.c:212
> #5 0x816dee1 in RI_FKey_check_ins (fcinfo=0xbfbfc884) at ri_triggers.c:506
Hm. Apparently tgconstrrelid is 0 in the pg_trigger row for your ON
INSERT trigger --- can you confirm that by looking in pg_trigger?
Next question is how it got that way. Did you create this table from a
dump, and if so do you still have the dump file? I'm wondering exactly
what SQL command was used to create the trigger ...
> I was inserting meter_id=411, stats_id=currval('stats_id_seq')
> meter.id=411 exists. Hard to tell about the other one.. Still don't see
> why this ever worked..
I'm confused about that too. The trigger failure is definitely
happening after we insert the row, so currval() must have gotten done
before reaching this point. So *something* is executing a nextval()
before we get to the point of evaluating the currval(). You got any
defaults on the table that might do it?
regards, tom lane
On Wed, Sep 25, 2002 at 05:49:17PM -0400, Tom Lane wrote: > Patrick Welche <prlw1@newn.cam.ac.uk> writes: > > One thing which bugs me: I have a currval in there, and that is the very > > first query which reaches the database, so it won't be "set", will it, but > > then, how could it have worked for months with the other version of server? > > Good question. Do you have any ON INSERT rules on that table? Curious: if I get the program to print what it thinks it is sending the database, it does: SELECT id FROM meter WHERE meterstart = '08:52:11 Mon 22 Jul 2002' INSERT INTO stats (timeslice,timesliced,timeslicet,aps,... SELECT MAX(fromoctets),MAX(tooctets) FROM stats,trans WHERE... INSERT INTO trans (meter_id,stats_id,flowindex,firsttime,firsttimed,firsttimet,f so, there currval should be OK. I was running postmaster -d4, yet the only query I saw was the last LOG one. I pretty sure that I would see all queries with -d3 before.. (Now postmaster won't shutdown pg_ctl: postmaster does not shut down) Cheers, Patrick
On Wed, Sep 25, 2002 at 11:28:21PM +0100, Patrick Welche wrote:
> (Now postmaster won't shutdown pg_ctl: postmaster does not shut down)
It's stuck in ServerLoop () at postmaster.c:949
(gdb) print rmask
$1 = {fds_bits = {8, 0, 0, 0, 0, 0, 0, 0}}
(gdb) print wmask
$2 = {fds_bits = {0, 0, 0, 0, 0, 0, 0, 0}}
(gdb) print timeout
$3 = {tv_sec = 60, tv_usec = 0}
Remembers Tip 1: Don't kill -9 the postmaster... Hmm 2 and 15 don't do anything..
Patrick
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
>> (Now postmaster won't shutdown pg_ctl: postmaster does not shut down)
> It's stuck in ServerLoop () at postmaster.c:949
> (gdb) print rmask
> $1 = {fds_bits = {8, 0, 0, 0, 0, 0, 0, 0}}
> (gdb) print wmask
> $2 = {fds_bits = {0, 0, 0, 0, 0, 0, 0, 0}}
> (gdb) print timeout
> $3 = {tv_sec = 60, tv_usec = 0}
That's about what I'd expect it to be doing. The final decision to exit
would normally be made when we see the shutdown process exit (about
line 1587 in postmaster.c). What are the contents of ShutdownPID,
CheckPointPID, Shutdown, and FatalError? Are there any remaining child
processes of the postmaster?
> Remembers Tip 1: Don't kill -9 the postmaster... Hmm 2 and 15 don't do anything..
That tip is pretty obsolete, but before you pull the trigger it would be
nice to try to learn more. I wonder if you have hit some obscure race
condition that prevents the postmaster from realizing it's done?
regards, tom lane
On Wed, Sep 25, 2002 at 06:46:27PM -0400, Tom Lane wrote: ... > That's about what I'd expect it to be doing. The final decision to exit > would normally be made when we see the shutdown process exit (about > line 1587 in postmaster.c). What are the contents of ShutdownPID, > CheckPointPID, Shutdown, and FatalError? (gdb) print ShutdownPID $1 = 0 (gdb) print CheckPointPID $2 = 0 (gdb) print Shutdown $3 = 2 (gdb) print FatalError $4 = 0 '\000' > Are there any remaining child > processes of the postmaster? # ps ax | grep post 10828 p3 S+ 0:00.04 postmaster -o -W 15 -d4 (postgres) 10829 p3 S+ 0:00.00 postmaster: stats buffer process (postgres) 10831 p3 S+ 0:00.01 postmaster: stats collector process (postgres) 11387 p5 D+ 0:00.00 grep post 11360 p9 SW+ 0:00.00 vi postmaster.c > That tip is pretty obsolete, but before you pull the trigger it would be > nice to try to learn more. I wonder if you have hit some obscure race > condition that prevents the postmaster from realizing it's done? ? Nothing else was going on... Now I'm hammering the box looking for the constraint in the 3.3Gb dump file, so swap is a bit low. Cheers, Patrick
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> Remembers Tip 1: Don't kill -9 the postmaster... Hmm 2 and 15 don't do anything..
How about SIGQUIT (if you didn't use -9 already)?
I've been staring at the logic in postmaster.c and I don't see how it
could fail to quit when Shutdown is nonzero and there aren't any
children left... maybe the signal is being held off for some weird
reason?
regards, tom lane
On Wed, Sep 25, 2002 at 06:26:22PM -0400, Tom Lane wrote:
> Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> >> Could you try setting a breakpoint at elog() to capture the stack trace
> >> leading up to the error?
>
> > #0 elog (lev=15, fmt=0x821133b "statement: %s") at elog.c:114
> > #1 0x81812db in elog (lev=20, fmt=0x8196b02 "Relation %u does not exist")
> > at elog.c:438
> > #2 0x80791a2 in relation_open (relationId=0, lockmode=2) at heapam.c:474
> > #3 0x8079329 in heap_open (relationId=0, lockmode=2) at heapam.c:602
> > #4 0x816d94b in RI_FKey_check (fcinfo=0xbfbfc884) at ri_triggers.c:212
> > #5 0x816dee1 in RI_FKey_check_ins (fcinfo=0xbfbfc884) at ri_triggers.c:506
>
> Hm. Apparently tgconstrrelid is 0 in the pg_trigger row for your ON
> INSERT trigger --- can you confirm that by looking in pg_trigger?
Even more entertaining: tgconstrrelid=0 for all rows in pg_trigger.
> Next question is how it got that way. Did you create this table from a
> dump, and if so do you still have the dump file? I'm wondering exactly
> what SQL command was used to create the trigger ...
Originally it was created with
create table meter (
id serial primary key,
...
create table stats (
id serial primary key,
...
create table trans (
meter_id integer references meter(id),
stats_id integer references stats(id),
...
then dumped with the v7.3 pg_dumpall which generated:
ALTER TABLE ONLY meter
ADD CONSTRAINT meter_pkey PRIMARY KEY (id);
ALTER TABLE ONLY stats
ADD CONSTRAINT stats_pkey PRIMARY KEY (id);
--
-- TOC entry 117 (OID 8658004)
-- Name: RI_ConstraintTrigger_8658003; Type: TRIGGER; Schema: ; Owner: prlw1
--
CREATE CONSTRAINT TRIGGER "<unnamed>"
AFTER INSERT OR UPDATE ON trans
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'trans', 'meter', 'UNSPE
CIFIED', 'meter_id', 'id');
--
-- TOC entry 113 (OID 8658006)
-- Name: RI_ConstraintTrigger_8658005; Type: TRIGGER; Schema: ; Owner: prlw1
--
CREATE CONSTRAINT TRIGGER "<unnamed>"
AFTER DELETE ON meter
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'trans', 'meter', 'UN
SPECIFIED', 'meter_id', 'id');
--
-- TOC entry 114 (OID 8658008)
-- Name: RI_ConstraintTrigger_8658007; Type: TRIGGER; Schema: ; Owner: prlw1
--
CREATE CONSTRAINT TRIGGER "<unnamed>"
AFTER UPDATE ON meter
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'trans', 'meter', 'UN
SPECIFIED', 'meter_id', 'id');
--
-- TOC entry 118 (OID 8658010)
-- Name: RI_ConstraintTrigger_8658009; Type: TRIGGER; Schema: ; Owner: prlw1
--
CREATE CONSTRAINT TRIGGER "<unnamed>"
AFTER INSERT OR UPDATE ON trans
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'trans', 'stats', 'UNSPE
CIFIED', 'stats_id', 'id');
--
-- TOC entry 115 (OID 8658012)
-- Name: RI_ConstraintTrigger_8658011; Type: TRIGGER; Schema: ; Owner: prlw1
--
CREATE CONSTRAINT TRIGGER "<unnamed>"
AFTER DELETE ON stats
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'trans', 'stats', 'UN
SPECIFIED', 'stats_id', 'id');
--
-- TOC entry 116 (OID 8658014)
-- Name: RI_ConstraintTrigger_8658013; Type: TRIGGER; Schema: ; Owner: prlw1
--
CREATE CONSTRAINT TRIGGER "<unnamed>"
AFTER UPDATE ON stats
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'trans', 'stats', 'UN
SPECIFIED', 'stats_id', 'id');
Cheers,
Patrick
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> then dumped with the v7.3 pg_dumpall which generated:
> CREATE CONSTRAINT TRIGGER "<unnamed>"
> AFTER INSERT OR UPDATE ON trans
> NOT DEFERRABLE INITIALLY IMMEDIATE
> FOR EACH ROW
> EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'trans', 'meter', 'UNSPE
> CIFIED', 'meter_id', 'id');
Yeek. The lack of a FROM <table> clause in that trigger definition is
why it's not working. IIRC, the FROM was optional in pre-7.3 releases,
but it is *required* now. (We probably should adjust the syntax
accordingly.)
7.3 pg_dump is not working hard enough to regenerate the appropriate
info, which we can fix, but I'm wondering how it got that way in the
first place. The bug that could originally cause tgconstrrelid to be
forgotten was a pg_dump bug that existed up to about 7.0. Is it
possible that these tables have a continuous history of being dumped
and reloaded back to 7.0 or before?
Anyway the quickest fix seems to be to manually drop the triggers
and reconstruct the FK relationships with ALTER TABLE ADD FOREIGN KEY
commands. If that seems too messy to do by hand, you can wait till
I've got a pg_dump patch to do it for you.
regards, tom lane
On Thu, Sep 26, 2002 at 09:59:32AM -0400, Tom Lane wrote:
> Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> > then dumped with the v7.3 pg_dumpall which generated:
>
> > CREATE CONSTRAINT TRIGGER "<unnamed>"
> > AFTER INSERT OR UPDATE ON trans
> > NOT DEFERRABLE INITIALLY IMMEDIATE
> > FOR EACH ROW
> > EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'trans', 'meter', 'UNSPE
> > CIFIED', 'meter_id', 'id');
>
> Yeek. The lack of a FROM <table> clause in that trigger definition is
> why it's not working. IIRC, the FROM was optional in pre-7.3 releases,
> but it is *required* now. (We probably should adjust the syntax
> accordingly.)
>
> 7.3 pg_dump is not working hard enough to regenerate the appropriate
> info, which we can fix, but I'm wondering how it got that way in the
> first place. The bug that could originally cause tgconstrrelid to be
> forgotten was a pg_dump bug that existed up to about 7.0. Is it
> possible that these tables have a continuous history of being dumped
> and reloaded back to 7.0 or before?
I wrote the system last year and it started running for real
Thu 29 Mar 22:41:25 2001
I think that is after 7.0? It has gone through a number of dump/restore
cycles.
> Anyway the quickest fix seems to be to manually drop the triggers
> and reconstruct the FK relationships with ALTER TABLE ADD FOREIGN KEY
> commands. If that seems too messy to do by hand, you can wait till
> I've got a pg_dump patch to do it for you.
Just a note on output. Before I had
\d trans...
Indexes: firsttimei_idx btree (firsttimei),
srcpeername_idx btree (sourcepeername)
Triggers: RI_ConstraintTrigger_14413070,
RI_ConstraintTrigger_14413073
\d meter...
Indexes: meter_pkey primary key btree (id)
Triggers: RI_ConstraintTrigger_14413071,
RI_ConstraintTrigger_14413072
\d stats...
Indexes: stats_pkey primary key btree (id)
Triggers: RI_ConstraintTrigger_14413074,
RI_ConstraintTrigger_14413075
after drop trigger/alter table add foreign key:
\d trans
Indexes: firsttimei_idx btree (firsttimei),
srcpeername_idx btree (sourcepeername)
Foreign Key constraints: $1 FOREIGN KEY (meter_id) REFERENCES meter(id) MATCH FULL ON UPDATE NO ACTION ON DELETE NO
ACTION,
$2 FOREIGN KEY (stats_id) REFERENCES stats(id) MATCH FULL ON UPDATE NO ACTION ON DELETE NO
ACTION
\d meter...
Indexes: meter_pkey primary key btree (id)
\d stats...
Indexes: stats_pkey primary key btree (id)
I take it the difference is because before tgconstrrelid was zero, and now
it isn't? (Apart from pg_sync_pg_pwd and pg_sync_pg_group)
Thank you for the help! (Working now :-) .. now to see what's up with libpq++)
Patrick
Patrick Welche <prlw1@newn.cam.ac.uk> writes:
>> Anyway the quickest fix seems to be to manually drop the triggers
>> and reconstruct the FK relationships with ALTER TABLE ADD FOREIGN KEY
>> commands. If that seems too messy to do by hand, you can wait till
>> I've got a pg_dump patch to do it for you.
> Just a note on output. Before I had
> \d trans...
> Indexes: firsttimei_idx btree (firsttimei),
> srcpeername_idx btree (sourcepeername)
> Triggers: RI_ConstraintTrigger_14413070,
> RI_ConstraintTrigger_14413073
> after drop trigger/alter table add foreign key:
> \d trans
> Indexes: firsttimei_idx btree (firsttimei),
> srcpeername_idx btree (sourcepeername)
> Foreign Key constraints: $1 FOREIGN KEY (meter_id) REFERENCES meter(id) MATCH FULL ON UPDATE NO ACTION ON DELETE NO
ACTION,
> $2 FOREIGN KEY (stats_id) REFERENCES stats(id) MATCH FULL ON UPDATE NO ACTION ON DELETE NO
ACTION
> I take it the difference is because before tgconstrrelid was zero, and now
> it isn't?
No, the difference is that now there is a pg_constraint entry for the
foreign-key relationship, and the system understands that the triggers
exist to implement that FK constraint so it doesn't show them separately.
Before they were just random triggers and \d didn't have any special
knowledge about them.
The main advantage of having the pg_constraint entry is that you can
ALTER TABLE DROP CONSTRAINT to get rid of the FK constraint (and the
triggers of course). No more manual mucking with triggers.
regards, tom lane