pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Koczan <pjkoczan@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: Legacy foreign keys
Date: Sat, 02 Feb 2008 15:36:15 -0500
Message-ID: <10016.1201984575@sss.pgh.pa.us> (raw)
In-Reply-To: <4544e0330802021143n2d38dce8k4ff9ab271aa35c2a@mail.gmail.com>
References: <4544e0330712101112r6aeccfcckef378a7ea1795e07@mail.gmail.com>
	<20427.1197324492@sss.pgh.pa.us>
	<4544e0330712111901k1fa50c5dgb26287aff018e98c@mail.gmail.com>
	<10859.1197428770@sss.pgh.pa.us>
	<4544e0330712120940i413e3ad6y867fc2a8744f623b@mail.gmail.com>
	<4544e0330801311613t5cc6741cx1464947c2215f198@mail.gmail.com>
	<18279.1201845349@sss.pgh.pa.us>
	<4544e0330802011030l477b0d8ek57909e75ff367128@mail.gmail.com>
	<4544e0330802011539j78ab6d2eofcf58e9442031115@mail.gmail.com>
	<17999.1201909980@sss.pgh.pa.us>
	<4544e0330802021143n2d38dce8k4ff9ab271aa35c2a@mail.gmail.com>

"Peter Koczan" <pjkoczan@gmail.com> writes:
>> If your original database doesn't have all three triggers, then you had
>> a problem already --- the FK constraint wasn't being enforced properly.

> Ah, these were from before I became the main database admin. It was
> probably some previous admin or programmer who thought he could be
> clever and drop the other FK-related triggers.

Or it could just be a long-ago oversight.  In versions before about 7.3,
it was possible to drop and recreate one of the two tables involved in
an FK relationship without any impact on the other table's FK
trigger(s).  Depending on usage patterns you might never notice that
the FK constraint was now only partially enforced.

Hmm, actually the note in the code says that there were two separate
bad things that had to happen to get into this situation:

 * The conversion is complex because a pre-7.3 foreign key involved three
 * separate triggers, which were reported separately in dumps.	While the
 * single trigger on the referencing table adds no new information, we need
 * to know the trigger functions of both of the triggers on the referenced
 * table to build the constraint declaration.  Also, due to lack of proper
 * dependency checking pre-7.3, it is possible that the source database had
 * an incomplete set of triggers resulting in an only partially enforced
 * FK constraint.  (This would happen if one of the tables had been dropped
 * and re-created, but only if the DB had been affected by a 7.0 pg_dump bug
 * that caused loss of tgconstrrelid information.)

			regards, tom lane



view thread (15+ messages)

Message-ID: <10016.1201984575@sss.pgh.pa.us>
Permalink:  ../10016.1201984575@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/10016.1201984575@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, pjkoczan@gmail.com, alvherre@alvh.no-ip.org
  Subject: Re: Legacy foreign keys
  In-Reply-To: <10016.1201984575@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox