Re: [HACKERS] should I worry?
On Mon, 5 Nov 2007, Tom Lane wrote:
Date: Mon, 05 Nov 2007 15:53:10 -0500
From: Tom Lane [EMAIL PROTECTED]
To: Heikki Linnakangas [EMAIL PROTECTED]
Cc: Rick Gigger [EMAIL PROTECTED], [EMAIL PROTECTED],
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] should I worry?
Heikki Linnakangas [EMAIL PROTECTED] writes:
Rick Gigger wrote:
Doesn't DROP TRIGGER require the name of the trigger? He says they are
unnamed. How then does he drop them?
They're not really unnamed. pg_dump just replaces the real name with
unnamed.
And \d will show the real names of the triggers, so it's not really
that hard to drop them:
u=# \d t1
Table public.t1
Column | Type | Modifiers
+-+---
f1 | integer | not null
Indexes:
t1_pkey PRIMARY KEY, btree (f1)
Triggers:
RI_ConstraintTrigger_229629 AFTER DELETE ON t1 FROM t2 NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
RI_FKey_noaction_del('unnamed', 't2', 't1', 'UNSPECIFIED', 'f2', 'f1')
RI_ConstraintTrigger_229630 AFTER UPDATE ON t1 FROM t2 NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
RI_FKey_noaction_upd('unnamed', 't2', 't1', 'UNSPECIFIED', 'f2', 'f1')
u=# drop trigger RI_ConstraintTrigger_229629 on t1;
DROP TRIGGER
I do recall newbies forgetting to double-quote the mixed-case trigger
names when this came up in times past, though.
regards, tom lane
I'd love to find a query against pg_triggers giving the table name for
each RI_ConstraintTrigger_xxx.
That would be quick to delete them all instead of going through all
tables.
Regards,
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges+33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: [EMAIL PROTECTED]
--
Make your life a dream, make your dream a reality. (St Exupery)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] should I worry?
[EMAIL PROTECTED] wrote: I'd love to find a query against pg_triggers giving the table name for each RI_ConstraintTrigger_xxx. SELECT t.tgname, c.relname, tgconstrname FROM pg_trigger t, pg_class c WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%'; -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] should I worry?
[EMAIL PROTECTED] writes: IIUC, I have drop every trigger like this : SELECT t.tgname, c.relname, tgconstrname FROM pg_trigger t, pg_class c WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%'AND tgconstrname = 'unnamed'; and I delete all those ancient foreign key WITHOUT disturbing any others Is that right? Not necessarily --- are you sure you don't have any real constraints named unnamed? However, if you do, the DROP TRIGGER command will just fail, so maybe you don't need to bother with looking into pg_depend for yourself. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] should I worry?
Heikki Linnakangas [EMAIL PROTECTED] writes: [EMAIL PROTECTED] wrote: I'd love to find a query against pg_triggers giving the table name for each RI_ConstraintTrigger_xxx. SELECT t.tgname, c.relname, tgconstrname FROM pg_trigger t, pg_class c WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%'; Um ... that will find all triggers named like that, but I think Olivier only wants to find the ones that are not attached to a constraint. In HEAD it would do to add ... AND tgconstraint = 0 but in 8.2 I'm afraid he's got to dig through pg_depend ... regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] should I worry?
On Tue, 6 Nov 2007, Heikki Linnakangas wrote: Date: Tue, 06 Nov 2007 13:07:23 + From: Heikki Linnakangas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Tom Lane [EMAIL PROTECTED], Rick Gigger [EMAIL PROTECTED], pgsql-hackers@postgresql.org Subject: Re: [HACKERS] should I worry? [EMAIL PROTECTED] wrote: I'd love to find a query against pg_triggers giving the table name for each RI_ConstraintTrigger_xxx. SELECT t.tgname, c.relname, tgconstrname FROM pg_trigger t, pg_class c WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%'; Thanks! GREAT! IIUC, I have drop every trigger like this : SELECT t.tgname, c.relname, tgconstrname FROM pg_trigger t, pg_class c WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%'AND tgconstrname = 'unnamed'; and I delete all those ancient foreign key WITHOUT disturbing any others Is that right? Regards -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] should I worry?
Dear Tom,
On Tue, 6 Nov 2007, Tom Lane wrote:
Date: Tue, 06 Nov 2007 10:05:58 -0500
From: Tom Lane [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Heikki Linnakangas [EMAIL PROTECTED],
Rick Gigger [EMAIL PROTECTED],
pgsql-hackers list pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] should I worry?
[EMAIL PROTECTED] writes:
IIUC, I have drop every trigger like this :
SELECT t.tgname, c.relname, tgconstrname FROM pg_trigger t, pg_class c
WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%'AND
tgconstrname = 'unnamed';
and I delete all those ancient foreign key WITHOUT disturbing any others
Is that right?
Not necessarily --- are you sure you don't have any real constraints
named unnamed?
yes
However, if you do, the DROP TRIGGER command will just fail, so maybe
you don't need to bother with looking into pg_depend for yourself.
regards, tom lane
Thanks to your last sentence, I ended up with the rather crude script
below that works for me (expect a lot of errors)
DBNAME=xxx
for s in `psql -t ${DBNAME} EOD
SELECT 'DROP TRIGGER ' || t.tgname || ' ON ' || c.relname || ';'
FROM pg_trigger t, pg_class c
WHERE t.tgrelid = c.oid AND tgname like 'RI_ConstraintTrigger_%'
EOD`
do
echo $s
done|psql ${DBNAME}
Could maybe help others like me
What's your opinion?
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges+33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: [EMAIL PROTECTED]
--
Make your life a dream, make your dream a reality. (St Exupery)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] should I worry?
Hi Tom On Sun, 4 Nov 2007, Tom Lane wrote: Date: Sun, 04 Nov 2007 19:47:04 -0500 From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] should I worry? I wrote: Hmm, this is messier than I thought. What evidently has happened is that at one time or another, one of the two tables involved in an FK relationship has been dropped and re-created. If you'd had proper FK constraints the constraints would have gone away cleanly, but with these old trigger definitions there was no mechanism to make that happen, and so the triggers on the other table remained in place. That seems to have happened several times, in fact. After tweaking ConvertTriggerToFK() to be more verbose and to insist on finding all three triggers, I get this: NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_competences(pcomp_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_position(ppos_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY objet_position(pobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY objet_position(pobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: converting trigger group into constraint unnamed FOREIGN KEY objet_position(pobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referenced table's UPDATE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_objets(perobj_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_objets(perobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_objets(perobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: converting trigger group into constraint unnamed FOREIGN KEY perso_objets(perobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referenced table's UPDATE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY messages_dest(dmsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY messages_dest(dmsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: converting trigger group into constraint unnamed FOREIGN KEY messages_dest(dmsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referenced table's UPDATE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY messages_dest(dmsg_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY messages_exp(emsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY messages_exp(emsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: converting trigger group into constraint unnamed FOREIGN KEY messages_exp(emsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referenced table's UPDATE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY messages_exp(emsg_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_competences(pcomp_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_competences(pcomp_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_position(ppos_pos_cod) REFERENCES positions(pos_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_position(ppos_pos_cod) REFERENCES positions(pos_cod) DETAIL: Found referenced table's UPDATE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY objet_position(pobj_pos_cod) REFERENCES positions(pos_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY objet_position
Re: [HACKERS] should I worry?
[EMAIL PROTECTED] writes: On Sun, 4 Nov 2007, Tom Lane wrote: So you have a *bunch* of partially broken FK constraints in that source database. I just talk to my customer and he/we'll make a big batch deleting and recreating all foreign keys on 8.2.5. The question is, how do we get rid of those useless unamed triggers? DROP TRIGGER should be fine. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] should I worry?
Doesn't DROP TRIGGER require the name of the trigger? He says they are unnamed. How then does he drop them? On Nov 5, 2007, at 6:31 AM, Tom Lane wrote: [EMAIL PROTECTED] writes: On Sun, 4 Nov 2007, Tom Lane wrote: So you have a *bunch* of partially broken FK constraints in that source database. I just talk to my customer and he/we'll make a big batch deleting and recreating all foreign keys on 8.2.5. The question is, how do we get rid of those useless unamed triggers? DROP TRIGGER should be fine. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] should I worry?
Rick Gigger wrote: Doesn't DROP TRIGGER require the name of the trigger? He says they are unnamed. How then does he drop them? They're not really unnamed. pg_dump just replaces the real name with unnamed. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] should I worry?
Heikki Linnakangas [EMAIL PROTECTED] writes:
Rick Gigger wrote:
Doesn't DROP TRIGGER require the name of the trigger? He says they are
unnamed. How then does he drop them?
They're not really unnamed. pg_dump just replaces the real name with
unnamed.
And \d will show the real names of the triggers, so it's not really
that hard to drop them:
u=# \d t1
Table public.t1
Column | Type | Modifiers
+-+---
f1 | integer | not null
Indexes:
t1_pkey PRIMARY KEY, btree (f1)
Triggers:
RI_ConstraintTrigger_229629 AFTER DELETE ON t1 FROM t2 NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
RI_FKey_noaction_del('unnamed', 't2', 't1', 'UNSPECIFIED', 'f2', 'f1')
RI_ConstraintTrigger_229630 AFTER UPDATE ON t1 FROM t2 NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
RI_FKey_noaction_upd('unnamed', 't2', 't1', 'UNSPECIFIED', 'f2', 'f1')
u=# drop trigger RI_ConstraintTrigger_229629 on t1;
DROP TRIGGER
I do recall newbies forgetting to double-quote the mixed-case trigger
names when this came up in times past, though.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] should I worry?
Ah, yes it was the quotes. I guess that makes me a newbie. :)
On Nov 5, 2007, at 1:53 PM, Tom Lane wrote:
Heikki Linnakangas [EMAIL PROTECTED] writes:
Rick Gigger wrote:
Doesn't DROP TRIGGER require the name of the trigger? He says
they are
unnamed. How then does he drop them?
They're not really unnamed. pg_dump just replaces the real name with
unnamed.
And \d will show the real names of the triggers, so it's not really
that hard to drop them:
u=# \d t1
Table public.t1
Column | Type | Modifiers
+-+---
f1 | integer | not null
Indexes:
t1_pkey PRIMARY KEY, btree (f1)
Triggers:
RI_ConstraintTrigger_229629 AFTER DELETE ON t1 FROM t2 NOT
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
RI_FKey_noaction_del('unnamed', 't2', 't1', 'UNSPECIFIED', 'f2',
'f1')
RI_ConstraintTrigger_229630 AFTER UPDATE ON t1 FROM t2 NOT
DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
RI_FKey_noaction_upd('unnamed', 't2', 't1', 'UNSPECIFIED', 'f2',
'f1')
u=# drop trigger RI_ConstraintTrigger_229629 on t1;
DROP TRIGGER
I do recall newbies forgetting to double-quote the mixed-case trigger
names when this came up in times past, though.
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] should I worry?
Dear Tom, On Sat, 3 Nov 2007, Tom Lane wrote: Date: Sat, 03 Nov 2007 21:21:20 -0400 From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Heikki Linnakangas [EMAIL PROTECTED], pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] should I worry? [EMAIL PROTECTED] writes: Is there a query I can use to know all the unamed trigger, delete them and recreate with the right sentence? I've applied a patch that should persuade the backend to convert the old CREATE CONSTRAINT TRIGGER commands into proper foreign-key constraints. I'd suggest applying the patch and re-loading the dump instead of trying to fix things manually. http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/trigger.c.diff?r1=1.219;r2=1.220 regards, tom lane I've tried it and got those logs: Nov 4 16:02:24 sun postgres[12505]: [189-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint RI_ConstraintTrigger_28974025 on table Nov 4 16:02:24 sun postgres[12505]: [189-2] perso_competences Nov 4 16:02:24 sun postgres[12505]: [190-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table perso_position Nov 4 16:02:24 sun postgres[12505]: [191-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table objet_position Nov 4 16:02:24 sun postgres[12505]: [192-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table objet_position Nov 4 16:02:24 sun postgres[12505]: [193-1] NOTICE: converting foreign-key trigger group into constraint unnamed on table objet_position Nov 4 16:02:25 sun postgres[12505]: [194-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table perso_objets Nov 4 16:02:25 sun postgres[12505]: [195-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table perso_objets Nov 4 16:02:25 sun postgres[12505]: [196-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table perso_objets Nov 4 16:02:25 sun postgres[12505]: [197-1] NOTICE: converting foreign-key trigger group into constraint unnamed on table perso_objets Nov 4 16:02:25 sun postgres[12505]: [198-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table messages_dest Nov 4 16:02:25 sun postgres[12505]: [199-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table messages_dest Nov 4 16:02:25 sun postgres[12505]: [200-1] NOTICE: converting foreign-key trigger group into constraint unnamed on table messages_dest Nov 4 16:02:25 sun postfix/smtpd[12751]: connect from 82-32-100-168.cable.ubr01.hawk.blueyonder.co.uk[82.32.100.168] Nov 4 16:02:26 sun postgres[12505]: [201-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table messages_dest Nov 4 16:02:26 sun postgres[12505]: [202-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table messages_exp Nov 4 16:02:26 sun postgres[12505]: [203-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table messages_exp Nov 4 16:02:26 sun postgres[12505]: [204-1] NOTICE: converting foreign-key trigger group into constraint unnamed on table messages_exp Nov 4 16:02:26 sun postgres[12505]: [205-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table messages_exp Nov 4 16:02:26 sun postgres[12505]: [206-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint RI_ConstraintTrigger_28974598 on table Nov 4 16:02:26 sun postgres[12505]: [206-2] perso_competences Nov 4 16:02:26 sun postgres[12505]: [207-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint RI_ConstraintTrigger_28974610 on table Nov 4 16:02:26 sun postgres[12505]: [207-2] perso_competences Nov 4 16:02:26 sun postgres[12505]: [208-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table perso_position Nov 4 16:02:26 sun postgres[12505]: [209-1] NOTICE: converting foreign-key trigger group into constraint unnamed on table perso_position Nov 4 16:02:26 sun postgres[12505]: [210-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table objet_position Nov 4 16:02:26 sun postgres[12505]: [211-1] NOTICE: converting foreign-key trigger group into constraint unnamed on table objet_position Nov 4 16:02:26 sun postgres[12505]: [212-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table or_position Nov 4 16:02:26 sun postgres[12505]: [213-1] NOTICE: converting foreign-key trigger group into constraint unnamed on table or_position Nov 4 16:02:26 sun postgres[12505]: [214-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint unnamed on table ligne_evt Nov 4 16:02:26 sun postgres[12505]: [215-1] NOTICE
Re: [HACKERS] should I worry?
[EMAIL PROTECTED] writes: I've got two problems: Looking at the errors, ISTM foreign statement is the over way round : levt_tevt_cod is in ligne_evt NOT in type_evt No, that's just how we've worded FK violation errors for some time. The real question is how did FK violations get into your dump? And, looking at the generated foreign keys, I don't see ON UPDATE/ON DELETE clauses The ones we can see here are default (NO ACTION) cases, so I'm not convinced you're describing a real problem. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] should I worry?
[EMAIL PROTECTED] writes: I've tried it and got those logs: BTW, is that a complete list of the NOTICEs you got? I'd expect to see exactly two ignoring messages for each converting message, and it's a bit worrisome that that's not what you seem to have. Another thing that's strange is that some of the messages reference auto-generated trigger names instead of constraint names: Nov 4 16:02:26 sun postgres[12505]: [227-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint RI_ConstraintTrigger_28974035 on table Nov 4 16:02:26 sun postgres[12505]: [227-2] perso_competences That's fairly unhelpful since it makes it harder to match up the messages, but I didn't see any such cases when I was testing the patch here. Would it be possible for you to send me (off-list) all of the CREATE CONSTRAINT TRIGGER commands appearing in the dump? I don't need to see anything else, but I'm curious to look at those. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] should I worry?
On Sun, 4 Nov 2007, Tom Lane wrote: Would it be possible for you to send me (off-list) all of the CREATE CONSTRAINT TRIGGER commands appearing in the dump? [done] Hmm, this is messier than I thought. What evidently has happened is that at one time or another, one of the two tables involved in an FK relationship has been dropped and re-created. If you'd had proper FK constraints the constraints would have gone away cleanly, but with these old trigger definitions there was no mechanism to make that happen, and so the triggers on the other table remained in place. In some cases it looks like the FK constraint was re-established with a fresh ALTER TABLE ADD CONSTRAINT command, leading to redundant sets of triggers on one side of the relationship, while in other cases it wasn't, leading to a partially functional FK constraint :-( This explains why your dump contains some invalid data: the constraint wasn't being enforced against the FK table. So the question is what to do when we see a situation like this. As the code stands, it will try to re-create an FK constraint after seeing the two triggers on the PK table, regardless of whether a trigger is present on the FK table. That has a couple of disadvantages: * it may try to create an FK constraint that was only partially enforced before, leading to errors like we saw in Olivier's report. * in the situation where the user had re-established the constraint, we may create redundant FK constraints. The only thing I can think to do differently is to insist on seeing all three matching triggers before we create the FK constraint. This could be programmed so that we make only one constraint not two when there's redundant triggers in the input. The downside is that we'd fail to translate a constraint that was only partially enforced in the source database. Maybe that's the best thing; it's symmetric with what will happen when we see only the trigger on the FK table and none for the PK table. (We can't do anything else in that case, for lack of sufficient information.) It would be nice if we could throw warnings for incomplete trigger sets, but I see no very helpful way to do that --- we'd only be sure we could emit the warning upon client disconnect, and then it's too late to be sure the user would see the warning. Comments, better ideas? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] should I worry?
I wrote: Hmm, this is messier than I thought. What evidently has happened is that at one time or another, one of the two tables involved in an FK relationship has been dropped and re-created. If you'd had proper FK constraints the constraints would have gone away cleanly, but with these old trigger definitions there was no mechanism to make that happen, and so the triggers on the other table remained in place. That seems to have happened several times, in fact. After tweaking ConvertTriggerToFK() to be more verbose and to insist on finding all three triggers, I get this: NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_competences(pcomp_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_position(ppos_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY objet_position(pobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY objet_position(pobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: converting trigger group into constraint unnamed FOREIGN KEY objet_position(pobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referenced table's UPDATE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_objets(perobj_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_objets(perobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_objets(perobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: converting trigger group into constraint unnamed FOREIGN KEY perso_objets(perobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referenced table's UPDATE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY messages_dest(dmsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY messages_dest(dmsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: converting trigger group into constraint unnamed FOREIGN KEY messages_dest(dmsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referenced table's UPDATE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY messages_dest(dmsg_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY messages_exp(emsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY messages_exp(emsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: converting trigger group into constraint unnamed FOREIGN KEY messages_exp(emsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referenced table's UPDATE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY messages_exp(emsg_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_competences(pcomp_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_competences(pcomp_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_position(ppos_pos_cod) REFERENCES positions(pos_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY perso_position(ppos_pos_cod) REFERENCES positions(pos_cod) DETAIL: Found referenced table's UPDATE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY objet_position(pobj_pos_cod) REFERENCES positions(pos_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY objet_position(pobj_pos_cod) REFERENCES positions(pos_cod) DETAIL: Found referenced table's UPDATE trigger. NOTICE: ignoring incomplete trigger group for constraint unnamed FOREIGN KEY or_position(por_pos_cod) REFERENCES positions(pos_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: ignoring
Re: [HACKERS] should I worry?
I wrote: Hmm, this is messier than I thought. What evidently has happened is that at one time or another, one of the two tables involved in an FK relationship has been dropped and re-created. If you'd had proper FK constraints the constraints would have gone away cleanly, but with these old trigger definitions there was no mechanism to make that happen, and so the triggers on the other table remained in place. I looked back in our CVS history and found that the above statement is incorrect, or at least an oversimplification. Ever since PG 7.0 (the first release with FOREIGN KEY support) there has been a mechanism to auto-drop the FK triggers on the other side of the relationship. Since 7.3 the pg_depend mechanism has handled it, but before that DROP TABLE did a scan of pg_trigger for entries having tgconstrrelid pointing to the doomed table. So how come it broke? Further study provided a pathway that could cause this: 7.0 pg_dump failed to include tgconstrrelid (the FROM table clause) in its CREATE CONSTRAINT TRIGGER commands. This was fixed in 7.1 and all later releases, but was never back-patched to 7.0.x. In 7.3 and later, the backend has a hack to regenerate the missing tgconstrrelid value when loading an RI constraint trigger definition, but 7.0-7.2 will just load the definition and set tgconstrrelid = 0. So the scenario must have gone like this: 1. Create some FOREIGN KEY constraints in 7.0. 2. Dump the database using 7.0's pg_dump. 3. Load into 7.0, 7.1, or 7.2. 4. Drop and recreate the table on one side of the FK relationship, but don't re-create the FK constraint. 5. Continue to use the database up to the present day without ever noticing that the FK constraint was only partially enforced. (Dumps and reloads would not have changed its status.) It's still not clear to me how perso managed to have only a DELETE trigger and no UPDATE trigger for the FK reference from perso_competences, but all the other inconsistencies seem to be explainable by this mechanism. Also, I was wondering why some of the trigger definitions had names like RI_ConstraintTrigger_28974011 rather than the underlying constraint name, which is always unnamed in this set of triggers. I now notice that these entries are also associated with duplicated sets of triggers, which makes me think they are the result of manual attempts at patching broken RI trigger sets. I don't suppose there is enough history of schema changes in this DB to confirm or deny these theories? Anyway, the conclusion that the breakage must have gone undetected since 7.2 or before makes me feel that maybe this isn't quite as critical as I thought before. There can't be a huge number of people in such situations, and the FKs aren't working per spec for them anyway. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] should I worry?
Hi Tom et all!
Thanks for your mails.
On Fri, 2 Nov 2007, Tom Lane wrote:
Date: Fri, 02 Nov 2007 16:08:24 -0400
From: Tom Lane [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Heikki Linnakangas [EMAIL PROTECTED],
pgsql-hackers list pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] should I worry?
[EMAIL PROTECTED] writes:
All triggers in the schema are named. So I assume they are triggers for
foreign keys.
No, foreign-key triggers always have names too, and they don't look like
that (they look like RI_ConstraintTrigger_nnn). I cannot find anyplace
in PG that supplies unnamed as a default name for a trigger, either.
So there's something weird about your schema, and we really need to
see a test case ...
I'm confused, until I have clearence to send the schema, here are pg logs:
Nov 3 14:44:20 sun postgres[17963]: [189-1] ERROR: trigger unnamed for
relation objets already exists
Nov 3 14:44:20 sun postgres[17963]: [189-2] STATEMENT: CREATE CONSTRAINT
TRIGGER unnamed
Nov 3 14:44:20 sun postgres[17963]: [189-3]AFTER UPDATE ON objets
Nov 3 14:44:20 sun postgres[17963]: [189-4]FROM objet_position
Nov 3 14:44:20 sun postgres[17963]: [189-5]NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [189-6]FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [189-7]EXECUTE PROCEDURE
RI_FKey_noaction_upd('unnamed', 'objet_position', 'objets', 'UNSPECIFIED',
'pobj_obj_cod',
Nov 3 14:44:20 sun postgres[17963]: [189-8] 'obj_cod');
Nov 3 14:44:20 sun postgres[17963]: [190-1] ERROR: trigger unnamed for
relation perso_objets already exists
Nov 3 14:44:20 sun postgres[17963]: [190-2] STATEMENT: CREATE CONSTRAINT
TRIGGER unnamed
Nov 3 14:44:20 sun postgres[17963]: [190-3]AFTER INSERT OR UPDATE ON
perso_objets
Nov 3 14:44:20 sun postgres[17963]: [190-4]FROM objets
Nov 3 14:44:20 sun postgres[17963]: [190-5]NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [190-6]FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [190-7]EXECUTE PROCEDURE
RI_FKey_check_ins('unnamed', 'perso_objets', 'objets', 'UNSPECIFIED',
'perobj_obj_cod', 'obj_cod');
Nov 3 14:44:20 sun postgres[17963]: [191-1] ERROR: trigger unnamed for
relation objets already exists
Nov 3 14:44:20 sun postgres[17963]: [191-2] STATEMENT: CREATE CONSTRAINT
TRIGGER unnamed
Nov 3 14:44:20 sun postgres[17963]: [191-3]AFTER DELETE ON objets
Nov 3 14:44:20 sun postgres[17963]: [191-4]FROM perso_objets
Nov 3 14:44:20 sun postgres[17963]: [191-5]NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [191-6]FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [191-7]EXECUTE PROCEDURE
RI_FKey_noaction_del('unnamed', 'perso_objets', 'objets', 'UNSPECIFIED',
'perobj_obj_cod',
Nov 3 14:44:20 sun postgres[17963]: [191-8] 'obj_cod');
Nov 3 14:44:20 sun postgres[17963]: [192-1] ERROR: trigger unnamed for
relation objets already exists
Nov 3 14:44:20 sun postgres[17963]: [192-2] STATEMENT: CREATE CONSTRAINT
TRIGGER unnamed
Nov 3 14:44:20 sun postgres[17963]: [192-3]AFTER UPDATE ON objets
Nov 3 14:44:20 sun postgres[17963]: [192-4]FROM perso_objets
Nov 3 14:44:20 sun postgres[17963]: [192-5]NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [192-6]FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [192-7]EXECUTE PROCEDURE
RI_FKey_noaction_upd('unnamed', 'perso_objets', 'objets', 'UNSPECIFIED',
'perobj_obj_cod',
Nov 3 14:44:20 sun postgres[17963]: [192-8] 'obj_cod');
Nov 3 14:44:20 sun postgres[17963]: [193-1] ERROR: trigger unnamed for
relation messages already exists
Nov 3 14:44:20 sun postgres[17963]: [193-2] STATEMENT: CREATE CONSTRAINT
TRIGGER unnamed
Nov 3 14:44:20 sun postgres[17963]: [193-3]AFTER UPDATE ON messages
Nov 3 14:44:20 sun postgres[17963]: [193-4]FROM messages_dest
Nov 3 14:44:20 sun postgres[17963]: [193-5]NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [193-6]FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [193-7]EXECUTE PROCEDURE
RI_FKey_noaction_upd('unnamed', 'messages_dest', 'messages', 'UNSPECIFIED',
'dmsg_msg_cod',
Nov 3 14:44:20 sun postgres[17963]: [193-8] 'msg_cod');
Nov 3 14:44:20 sun postgres[17963]: [194-1] ERROR: trigger unnamed for
relation messages_dest already exists
Nov 3 14:44:20 sun postgres[17963]: [194-2] STATEMENT: CREATE CONSTRAINT
TRIGGER unnamed
Nov 3 14:44:20 sun postgres[17963]: [194-3]AFTER INSERT OR UPDATE ON
messages_dest
Nov 3 14:44:20 sun postgres[17963]: [194-4]FROM perso
Nov 3 14:44:20 sun postgres[17963]: [194-5]NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [194-6]FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [194-7]EXECUTE PROCEDURE
RI_FKey_check_ins('unnamed', 'messages_dest
Re: [HACKERS] should I worry?
[EMAIL PROTECTED] writes:
I'm confused, until I have clearence to send the schema, here are pg logs:
Nov 3 14:44:20 sun postgres[17963]: [189-1] ERROR: trigger unnamed for
relation objets already exists
Nov 3 14:44:20 sun postgres[17963]: [189-2] STATEMENT: CREATE CONSTRAINT
TRIGGER unnamed
Nov 3 14:44:20 sun postgres[17963]: [189-3] AFTER UPDATE ON objets
Nov 3 14:44:20 sun postgres[17963]: [189-4] FROM objet_position
Nov 3 14:44:20 sun postgres[17963]: [189-5] NOT DEFERRABLE INITIALLY
IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [189-6] FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [189-7] EXECUTE PROCEDURE
RI_FKey_noaction_upd('unnamed', 'objet_position', 'objets',
'UNSPECIFIED', 'pobj_obj_cod',
Nov 3 14:44:20 sun postgres[17963]: [189-8] 'obj_cod');
These must be hangovers from some truly ancient version of Postgres :-(
I'd suggest dropping all these triggers and setting up real foreign key
constraint declarations instead. If there seem to be too many to do it
manually, you might try contrib/adddepend which used to be included
with Postgres (between 7.3 and 8.1).
Looking into it, I think the reason you're getting bit now is that
CREATE CONSTRAINT TRIGGER didn't use to insist on a unique trigger name.
Now it does. But it's way past time for you to get rid of these
old-style foreign keys anyway.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] should I worry?
Sorry to post on my own post,
I found this (as a little exemple I've got plenty of these)
could that be the culprit and how to correct that?
--
-- Name: RI_ConstraintTrigger_609094000; Type: TRIGGER; Schema: public; Owner:
delain
--
CREATE CONSTRAINT TRIGGER unnamed
AFTER DELETE ON objets
FROM objet_position
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE RI_FKey_noaction_del('unnamed', 'objet_position',
'objets', 'UNSPECIFIED', 'pobj_obj_cod', 'obj_cod');
--
-- Name: RI_ConstraintTrigger_609094001; Type: TRIGGER; Schema: public; Owner:
delain
--
CREATE CONSTRAINT TRIGGER unnamed
AFTER UPDATE ON objets
FROM objet_position
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE RI_FKey_noaction_upd('unnamed', 'objet_position',
'objets', 'UNSPECIFIED', 'pobj_obj_cod', 'obj_cod');
This dump was generated by pg_dump 8.3beta1 against a 8.2.5 db
Best regardsb
On Fri, 2 Nov 2007, Heikki Linnakangas wrote:
Date: Fri, 02 Nov 2007 19:59:03 +
From: Heikki Linnakangas [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: pgsql-hackers list pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] should I worry?
[EMAIL PROTECTED] wrote:
On Fri, 2 Nov 2007, Heikki Linnakangas wrote:
Date: Fri, 02 Nov 2007 18:11:14 +
From: Heikki Linnakangas [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: pgsql-hackers list pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] should I worry?
[EMAIL PROTECTED] wrote:
I'm now testing 8.3beta2 on a relatively big (10G) database.
I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those
errors:
Could you be a bit more specific? The database you tried to restore to
was empty, right? Can you post the dump file (schema-only)?
Yes it was empty, Sorry, I can't post the schema, it belongs to one of my
customer...
Maybe you could reduce it to a small self-contained test case, with
table names changed to protect the innocent?
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges+33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: [EMAIL PROTECTED]
--
Make your life a dream, make your dream a reality. (St Exupery)
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] should I worry?
On Sat, 3 Nov 2007, Tom Lane wrote:
Date: Sat, 03 Nov 2007 12:42:24 -0400
From: Tom Lane [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Heikki Linnakangas [EMAIL PROTECTED],
pgsql-hackers list pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] should I worry?
[EMAIL PROTECTED] writes:
I'm confused, until I have clearence to send the schema, here are pg logs:
Nov 3 14:44:20 sun postgres[17963]: [189-1] ERROR: trigger unnamed
for relation objets already exists
Nov 3 14:44:20 sun postgres[17963]: [189-2] STATEMENT: CREATE CONSTRAINT
TRIGGER unnamed
Nov 3 14:44:20 sun postgres[17963]: [189-3]AFTER UPDATE ON
objets
Nov 3 14:44:20 sun postgres[17963]: [189-4]FROM objet_position
Nov 3 14:44:20 sun postgres[17963]: [189-5]NOT DEFERRABLE
INITIALLY IMMEDIATE
Nov 3 14:44:20 sun postgres[17963]: [189-6]FOR EACH ROW
Nov 3 14:44:20 sun postgres[17963]: [189-7]EXECUTE PROCEDURE
RI_FKey_noaction_upd('unnamed', 'objet_position', 'objets',
'UNSPECIFIED', 'pobj_obj_cod',
Nov 3 14:44:20 sun postgres[17963]: [189-8] 'obj_cod');
These must be hangovers from some truly ancient version of Postgres :-(
Yes, this db is restored on every new version for nearly 5 years now :)
I'd suggest dropping all these triggers and setting up real foreign key
constraint declarations instead. If there seem to be too many to do it
manually, you might try contrib/adddepend which used to be included
with Postgres (between 7.3 and 8.1).
Done see below
Actually, I can't even edit the dump 'cause it's 3.5G uncompress, xemacs
gives up at 2G :-(
Looking into it, I think the reason you're getting bit now is that
CREATE CONSTRAINT TRIGGER didn't use to insist on a unique trigger name.
Now it does. But it's way past time for you to get rid of these
old-style foreign keys anyway.
I've reload the dump on a 8.2.5 then ran adddepend.pl, took a dump and
reloaded it on a 8.3beta2, and have less but still errors.
What do I loose if I leave it as is, I guess I'll miss a few foreign keys,
is there an easy way to know which?
Is there a query I can use to know all the unamed trigger, delete them and
recreate with the right sentence?
regards, tom lane
regards,
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges+33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: [EMAIL PROTECTED]
--
Make your life a dream, make your dream a reality. (St Exupery)
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [HACKERS] should I worry?
[EMAIL PROTECTED] writes: Is there a query I can use to know all the unamed trigger, delete them and recreate with the right sentence? I've applied a patch that should persuade the backend to convert the old CREATE CONSTRAINT TRIGGER commands into proper foreign-key constraints. I'd suggest applying the patch and re-loading the dump instead of trying to fix things manually. http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/trigger.c.diff?r1=1.219;r2=1.220 regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] should I worry?
Hi hackers, I'm now testing 8.3beta2 on a relatively big (10G) database. I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those errors: ERROR: trigger unnamed for relation objets already exists ERROR: trigger unnamed for relation perso_objets already exists ERROR: trigger unnamed for relation objets already exists ERROR: trigger unnamed for relation objets already exists ERROR: trigger unnamed for relation messages already exists ERROR: trigger unnamed for relation messages_dest already exists ERROR: trigger unnamed for relation messages already exists ERROR: trigger unnamed for relation messages already exists ERROR: trigger unnamed for relation messages_exp already exists ERROR: trigger unnamed for relation positions already exists ERROR: trigger unnamed for relation positions already exists ERROR: trigger unnamed for relation positions already exists ERROR: trigger unnamed for relation positions already exists ERROR: trigger unnamed for relation positions already exists ERROR: trigger unnamed for relation type_evt already exists ERROR: trigger unnamed for relation objet_generique already exists ERROR: trigger unnamed for relation objet_generique already exists ERROR: trigger unnamed for relation objets_caracs already exists ERROR: trigger unnamed for relation competences already exists ERROR: trigger unnamed for relation race_comp already exists I don't remember having those with beta1 and don't know what to look at. I've tried both 8.2.5 and 8.3beta2 pg_dump. I'm also playing with checkpoint logging. What should I see? few buffers dump, little write time, little sync time? Unrelated, who should I contact to get my password of jaguar in build_farm, this animal has been set up to test with -DCLOBBER_CACHE_ALWAYS and I never received the password. Maybe pgfbuildfarm.org web should include a button to resend account to the owner. Just my 0.01$... Best regards -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] should I worry?
On Fri, 2 Nov 2007, Heikki Linnakangas wrote: Date: Fri, 02 Nov 2007 18:11:14 + From: Heikki Linnakangas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] should I worry? [EMAIL PROTECTED] wrote: I'm now testing 8.3beta2 on a relatively big (10G) database. I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those errors: Could you be a bit more specific? The database you tried to restore to was empty, right? Can you post the dump file (schema-only)? Yes it was empty, Sorry, I can't post the schema, it belongs to one of my customer... All triggers in the schema are named. So I assume they are triggers for foreign keys. It's hard to tell if all foreign keys have been created after restore is complete... I'm also playing with checkpoint logging. What should I see? few buffers dump, little write time, little sync time? Depends on how much activity there is. If there's a lot of dirty buffers, a lot of written buffers and long write time is perfectly normal. Thanks for the explanation. Regards -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] should I worry?
[EMAIL PROTECTED] wrote: I'm now testing 8.3beta2 on a relatively big (10G) database. I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those errors: Could you be a bit more specific? The database you tried to restore to was empty, right? Can you post the dump file (schema-only)? I'm also playing with checkpoint logging. What should I see? few buffers dump, little write time, little sync time? Depends on how much activity there is. If there's a lot of dirty buffers, a lot of written buffers and long write time is perfectly normal. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] should I worry?
[EMAIL PROTECTED] wrote: On Fri, 2 Nov 2007, Heikki Linnakangas wrote: Date: Fri, 02 Nov 2007 18:11:14 + From: Heikki Linnakangas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] should I worry? [EMAIL PROTECTED] wrote: I'm now testing 8.3beta2 on a relatively big (10G) database. I've tried with pg_dymp -Fc/pg_restore and pg_dump/pgsql and get those errors: Could you be a bit more specific? The database you tried to restore to was empty, right? Can you post the dump file (schema-only)? Yes it was empty, Sorry, I can't post the schema, it belongs to one of my customer... Maybe you could reduce it to a small self-contained test case, with table names changed to protect the innocent? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] should I worry?
[EMAIL PROTECTED] writes: All triggers in the schema are named. So I assume they are triggers for foreign keys. No, foreign-key triggers always have names too, and they don't look like that (they look like RI_ConstraintTrigger_nnn). I cannot find anyplace in PG that supplies unnamed as a default name for a trigger, either. So there's something weird about your schema, and we really need to see a test case ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] should I worry?
Hi, Le Friday 02 November 2007 21:08:24 Tom Lane, vous avez écrit : No, foreign-key triggers always have names too, and they don't look like that (they look like RI_ConstraintTrigger_nnn). I cannot find anyplace in PG that supplies unnamed as a default name for a trigger, either. So there's something weird about your schema, and we really need to see a test case ... I've had some restore problems with unnamed triggers on a 8.1 database. It contained some de-activated triggers dating from pre-7.3 era, and I finally managed to clean out the schema with the adddepend contrib utility. http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/adddepend/Attic/ After running this, I had no more problems related to unnamed triggers, but I can't remember the specifics of the errors I had. For adddepend to run, some data were to be removed, too (disabled constraint triggers made possible to insert them at some point in the past). Sorry for the imprecision of the post, hope this helps, -- dim ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
