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
> 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 = '';
>
> > 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 ""?
>
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?
[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 = ''; > 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 ""? 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 = ''; 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?
[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?
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
> > "".
>
> 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"('', '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"('', '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?
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
"".
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"('', '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"('', '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?
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
> "".
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"('', '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"('', '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?
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 "". -- 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?
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 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?
[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 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?
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 "" FOREIGN > KEY perso_competences(pcomp_perso_cod) REFERENCES perso(perso_cod) > DETAIL: Found referencing table's trigger. > NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN > KEY perso_position(ppos_perso_cod) REFERENCES perso(perso_cod) > DETAIL: Found referencing table's trigger. > NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN > KEY objet_position(pobj_obj_cod) REFERENCES objets(obj_cod) > DETAIL: Found referencing table's trigger. > NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN > KEY objet_position(pobj_obj_cod) REFERENCES objets(obj_cod) > DETAIL: Found referenced table's DELETE trigger. > NOTICE: converting trigger group into constraint "" 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 "" FOREIGN > KEY perso_objets(perobj_perso_cod) REFERENCES perso(perso_cod) > DETAIL: Found referencing table's trigger. > NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN > KEY perso_objets(perobj_obj_cod) REFERENCES objets(obj_cod) > DETAIL: Found referencing table's trigger. > NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN > KEY perso_objets(perobj_obj_cod) REFERENCES objets(obj_cod) > DETAIL: Found referenced table's DELETE trigger. > NOTICE: converting trigger group into constraint "" 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 "" FOREIGN > KEY messages_dest(dmsg_msg_cod) REFERENCES messages(msg_cod) > DETAIL: Found referencing table's trigger. > NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN > KEY messages_dest(dmsg_msg_cod) REFERENCES messages(msg_cod) > DETAIL: Found referenced table's DELETE trigger. > NOTICE: converting trigger group into constraint "" 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 "" FOREIGN > KEY messages_dest(dmsg_perso_cod) REFERENCES perso(perso_cod) > DETAIL: Found referencing table's trigger. > NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN > KEY messages_exp(emsg_msg_cod) REFERENCES messages(msg_cod) > DETAIL: Found referencing table's trigger. > NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN > KEY messages_exp(emsg_msg_cod) REFERENCES messages(msg_cod) > DETAIL: Found referenced table's DELETE trigger. > NOTICE: converting trigger group into constraint "" 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 "" FOREIGN > KEY messages_exp(emsg_perso_cod) REFERENCES perso(perso_cod) > DETAIL: Found referencing table's trigger. > NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN > KEY perso_competences(pcomp_perso_cod) REFERENCES perso(perso_cod) > DETAIL: Found referencing table's trigger. > NOTICE: ignoring incomplete trigger group for constraint "" 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 "" FOREIGN > KEY perso_position(ppos_pos_cod) REFERENCES
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 "" 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?
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 "" FOREIGN KEY perso_competences(pcomp_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN KEY perso_position(ppos_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN KEY objet_position(pobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN KEY objet_position(pobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: converting trigger group into constraint "" 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 "" FOREIGN KEY perso_objets(perobj_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN KEY perso_objets(perobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN KEY perso_objets(perobj_obj_cod) REFERENCES objets(obj_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: converting trigger group into constraint "" 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 "" FOREIGN KEY messages_dest(dmsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN KEY messages_dest(dmsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: converting trigger group into constraint "" 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 "" FOREIGN KEY messages_dest(dmsg_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN KEY messages_exp(emsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN KEY messages_exp(emsg_msg_cod) REFERENCES messages(msg_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: converting trigger group into constraint "" 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 "" FOREIGN KEY messages_exp(emsg_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN KEY perso_competences(pcomp_perso_cod) REFERENCES perso(perso_cod) DETAIL: Found referencing table's trigger. NOTICE: ignoring incomplete trigger group for constraint "" 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 "" 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 "" 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 "" 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 "" 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 "" FOREIGN KEY or_position(por_pos_cod) REFERENCES positions(pos_cod) DETAIL: Found referenced table's DELETE trigger. NOTICE: ignoring incomplete trigger group for constraint "" FOREIGN KEY or_position(por_pos_cod) REFERENCES positions(pos_cod) DET
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?
[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?
[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?
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 > 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 "" on table "perso_position" Nov 4 16:02:24 sun postgres[12505]: [191-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "" on table "objet_position" Nov 4 16:02:24 sun postgres[12505]: [192-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "" on table "objet_position" Nov 4 16:02:24 sun postgres[12505]: [193-1] NOTICE: converting foreign-key trigger group into constraint "" on table "objet_position" Nov 4 16:02:25 sun postgres[12505]: [194-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "" on table "perso_objets" Nov 4 16:02:25 sun postgres[12505]: [195-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "" on table "perso_objets" Nov 4 16:02:25 sun postgres[12505]: [196-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "" on table "perso_objets" Nov 4 16:02:25 sun postgres[12505]: [197-1] NOTICE: converting foreign-key trigger group into constraint "" on table "perso_objets" Nov 4 16:02:25 sun postgres[12505]: [198-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "" on table "messages_dest" Nov 4 16:02:25 sun postgres[12505]: [199-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "" on table "messages_dest" Nov 4 16:02:25 sun postgres[12505]: [200-1] NOTICE: converting foreign-key trigger group into constraint "" 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 "" on table "messages_dest" Nov 4 16:02:26 sun postgres[12505]: [202-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "" on table "messages_exp" Nov 4 16:02:26 sun postgres[12505]: [203-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "" on table "messages_exp" Nov 4 16:02:26 sun postgres[12505]: [204-1] NOTICE: converting foreign-key trigger group into constraint "" on table "messages_exp" Nov 4 16:02:26 sun postgres[12505]: [205-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "" 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 "" on table "perso_position" Nov 4 16:02:26 sun postgres[12505]: [209-1] NOTICE: converting foreign-key trigger group into constraint "" on table "perso_position" Nov 4 16:02:26 sun postgres[12505]: [210-1] NOTICE: ignoring incomplete foreign-key trigger group for constraint "" on table "objet_position" Nov 4 16:02:26 sun postgres[12505]: [211-1] NOTICE: converting foreign-key trigger group into constraint "" on tab
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
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
> 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 ""
> > for relation "objets" already exists
> > Nov 3 14:44:20 sun postgres[17963]: [189-2] STATEMENT: CREATE CONSTRAINT
> > TRIGGER ""
> > 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"('', '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?
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 ""
AFTER DELETE ON objets
FROM objet_position
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_del"('', 'objet_position',
'objets', 'UNSPECIFIED', 'pobj_obj_cod', 'obj_cod');
--
-- Name: RI_ConstraintTrigger_609094001; Type: TRIGGER; Schema: public; Owner:
delain
--
CREATE CONSTRAINT TRIGGER ""
AFTER UPDATE ON objets
FROM objet_position
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_upd"('', '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
> 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
> >> 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?
[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 "" for
> relation "objets" already exists
> Nov 3 14:44:20 sun postgres[17963]: [189-2] STATEMENT: CREATE CONSTRAINT
> TRIGGER ""
> 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"('', '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?
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
> 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 "" 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 "" for
relation "objets" already exists
Nov 3 14:44:20 sun postgres[17963]: [189-2] STATEMENT: CREATE CONSTRAINT
TRIGGER ""
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"('', '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 "" for
relation "perso_objets" already exists
Nov 3 14:44:20 sun postgres[17963]: [190-2] STATEMENT: CREATE CONSTRAINT
TRIGGER ""
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"('', 'perso_objets', 'objets', 'UNSPECIFIED',
'perobj_obj_cod', 'obj_cod');
Nov 3 14:44:20 sun postgres[17963]: [191-1] ERROR: trigger "" for
relation "objets" already exists
Nov 3 14:44:20 sun postgres[17963]: [191-2] STATEMENT: CREATE CONSTRAINT
TRIGGER ""
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"('', '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 "" for
relation "objets" already exists
Nov 3 14:44:20 sun postgres[17963]: [192-2] STATEMENT: CREATE CONSTRAINT
TRIGGER ""
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"('', '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 "" for
relation "messages" already exists
Nov 3 14:44:20 sun postgres[17963]: [193-2] STATEMENT: CREATE CONSTRAINT
TRIGGER ""
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"('', '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 "" for
relation "messages_dest&
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 "" 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 "" 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
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 "" 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?
[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 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?
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 > 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
