Re: postmaster -d option (was Re: [GENERAL] 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(at)newn(dot)cam(dot)ac(dot)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(at)newn(dot)cam(dot)ac(dot)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:11 Mon 22 Jul 2002'::timestamp without time zone+'5536695.89 second')::date,('08:52:11 Mon 22 Jul 2002'::timestamp without time zone+'5536695.89 second')::time,553669589,'08:52:11 Mon 22 Jul 2002'::timestamp without time zone+'5660731.53 second',('08:52:11 Mon 22 Jul 2002'::timestamp without time zone+'5660731.53 second')::date,('08:52:11 Mon 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(at)newn(dot)cam(dot)ac(dot)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(at)newn(dot)cam(dot)ac(dot)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(at)newn(dot)cam(dot)ac(dot)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(at)newn(dot)cam(dot)ac(dot)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

Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)uk> writes:
> ... 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..

It looked to me like you were just running with the recently-added
frill to log only queries that cause errors; which is on by default.

(Looks at code...) Ah. It looks like -d to the postmaster no longer
means anywhere near what it used to. Bruce --- compare the handling
of -d in the backend (postgres.c lines 1251ff) with its handling in
the postmaster (postmaster.c lines 444ff). Big difference. Are we
going to make these more alike? If so, which one do we like?

regards, tom lane

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(at)newn(dot)cam(dot)ac(dot)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(at)newn(dot)cam(dot)ac(dot)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

Tom Lane wrote:
> Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)uk> writes:
> > ... 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..
>
> It looked to me like you were just running with the recently-added
> frill to log only queries that cause errors; which is on by default.
>
> (Looks at code...) Ah. It looks like -d to the postmaster no longer
> means anywhere near what it used to. Bruce --- compare the handling
> of -d in the backend (postgres.c lines 1251ff) with its handling in
> the postmaster (postmaster.c lines 444ff). Big difference. Are we
> going to make these more alike? If so, which one do we like?

I am sorry but I don't understand. They look like they both set
server_min_messages. There was a comment in one that said
client_min_messages but I just fixed that.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> (Looks at code...) Ah. It looks like -d to the postmaster no longer
>> means anywhere near what it used to. Bruce --- compare the handling
>> of -d in the backend (postgres.c lines 1251ff) with its handling in
>> the postmaster (postmaster.c lines 444ff). Big difference. Are we
>> going to make these more alike? If so, which one do we like?

> I am sorry but I don't understand. They look like they both set
> server_min_messages.

Yeah, but postgres.c *also* sets log_connections, log_statement,
debug_print_parse, debug_print_plan, debug_print_rewritten depending
on the -d level. This behavior is not random; it's an attempt to
reproduce the effects of the historical -d switch. The postmaster.c
code is blowing off all those considerations.

> *** 1275,1288 ****
> if (atoi(optarg) >= 5)
> SetConfigOption("debug_print_rewritten", "true", ctx, gucsource);
> }
> - else
> -
> - /*
> - * -d 0 allows user to prevent postmaster debug
> - * from propagating to backend.
> - */
> - SetConfigOption("server_min_messages", "notice",
> - ctx, gucsource);
> }
> break;

I think you are deleting your own code there ... why?

regards, tom lane

Uh, yes, it is a little confusing and I am not sure that patch is right
anymore. I haven't applied it.

Another issue is that we used to have a global debug_level variable that was
propogated to the client. Now, we just have the GUC value which does
propogate like the global one did. Does the postmaster still pass -dX
down to the child like it used to? I don't see why you say, "The
postmaster.c code is blowing off all those considerations."

I -d0 think functions properly except that it sets the value to 'notice'
rather than resetting it to the postgresql.conf value. Is there a way
to do that?

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Tom Lane wrote:
> >> (Looks at code...) Ah. It looks like -d to the postmaster no longer
> >> means anywhere near what it used to. Bruce --- compare the handling
> >> of -d in the backend (postgres.c lines 1251ff) with its handling in
> >> the postmaster (postmaster.c lines 444ff). Big difference. Are we
> >> going to make these more alike? If so, which one do we like?
>
> > I am sorry but I don't understand. They look like they both set
> > server_min_messages.
>
> Yeah, but postgres.c *also* sets log_connections, log_statement,
> debug_print_parse, debug_print_plan, debug_print_rewritten depending
> on the -d level. This behavior is not random; it's an attempt to
> reproduce the effects of the historical -d switch. The postmaster.c
> code is blowing off all those considerations.
>
> > *** 1275,1288 ****
> > if (atoi(optarg) >= 5)
> > SetConfigOption("debug_print_rewritten", "true", ctx, gucsource);
> > }
> > - else
> > -
> > - /*
> > - * -d 0 allows user to prevent postmaster debug
> > - * from propagating to backend.
> > - */
> > - SetConfigOption("server_min_messages", "notice",
> > - ctx, gucsource);
> > }
> > break;
>
> I think you are deleting your own code there ... why?
>
> regards, tom lane
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> ... Now, we just have the GUC value which does
> propogate like the global one did. Does the postmaster still pass -dX
> down to the child like it used to?

Evidently not; else Patrick wouldn't be complaining that it doesn't
work like it used to.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > ... Now, we just have the GUC value which does
> > propogate like the global one did. Does the postmaster still pass -dX
> > down to the child like it used to?
>
> Evidently not; else Patrick wouldn't be complaining that it doesn't
> work like it used to.

OK, got it. I knew server_min_messages would propogate to the client,
but that doesn't trigger the -d special cases in postgres.c. I re-added
the -d flag propogation to the postmaster. I also changed the postgres
-d0 behavior to just reset server_min_messages rather than setting it to
'notice.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, Sep 25, 2002 at 06:26:22PM -0400, Tom Lane wrote:
> Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)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(at)newn(dot)cam(dot)ac(dot)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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> diff -c -c -r1.294 postgres.c
> *** src/backend/tcop/postgres.c 25 Sep 2002 20:31:40 -0000 1.294
> --- src/backend/tcop/postgres.c 26 Sep 2002 05:15:41 -0000
> ***************
> *** 1281,1288 ****
> * -d 0 allows user to prevent postmaster debug
> * from propagating to backend.
> */
> ! SetConfigOption("server_min_messages", "notice",
> ! ctx, gucsource);
> }
> break;

> --- 1281,1287 ----
> * -d 0 allows user to prevent postmaster debug
> * from propagating to backend.
> */
> ! ResetPGVariable("server_min_messages");
> }
> break;

If you want "export PGOPTIONS=-d0" to do what the comment says, you'd
also need to Reset all of the other GUC variables that -dN might have
set. However, I'm not sure that I agree with the goal in the first
place. If the admin has set debugging on the postmaster command line,
should it really be possible for users to turn it off so easily?

regards, tom lane

On Thu, Sep 26, 2002 at 09:59:32AM -0400, Tom Lane wrote:
> Patrick Welche <prlw1(at)newn(dot)cam(dot)ac(dot)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(at)newn(dot)cam(dot)ac(dot)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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > diff -c -c -r1.294 postgres.c
> > *** src/backend/tcop/postgres.c 25 Sep 2002 20:31:40 -0000 1.294
> > --- src/backend/tcop/postgres.c 26 Sep 2002 05:15:41 -0000
> > ***************
> > *** 1281,1288 ****
> > * -d 0 allows user to prevent postmaster debug
> > * from propagating to backend.
> > */
> > ! SetConfigOption("server_min_messages", "notice",
> > ! ctx, gucsource);
> > }
> > break;
>
> > --- 1281,1287 ----
> > * -d 0 allows user to prevent postmaster debug
> > * from propagating to backend.
> > */
> > ! ResetPGVariable("server_min_messages");
> > }
> > break;
>

Turns out I had to revert this change. There isn't a username at this
point in the code so the ResetPGVariable username test fails, and even
then, I don't think there is any way to set a variable to the value
before -d5 set it.

> If you want "export PGOPTIONS=-d0" to do what the comment says, you'd
> also need to Reset all of the other GUC variables that -dN might have
> set. However, I'm not sure that I agree with the goal in the first
> place. If the admin has set debugging on the postmaster command line,
> should it really be possible for users to turn it off so easily?

I see what you are saying, that you can pass -d0 from the client and
undo the -d5. Yes, I was wondering about that because even on the
command line, if you do -d5 -d0, you still get those extra options.

OK, attached patch applied. The restriction that you can't lower the
debug level with PGOPTIONS is a separate issue. What I clearly didn't
want to do was to reset those other options for -d0, and I didn't have
to do that for -d0 to work as advertised.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073