Re: altering foreign key without a table scan - Mailing list pgsql-general

On Thursday 18 August 2011 13:08:18 Jerry Sievers wrote:
> Vincent de Phily <vincent.dephily@mobile-devices.fr> writes:
> > The thing is, I know there is no violation by existing data, because of
> > the existing fkey. So locking and scaning the table to add the
> > "duplicate" fkey is>
> > not necessary. In a sense, I'm looking for :
> >> ALTER TABLE foo ALTER CONSTRAINT foo_barid_fkey ON UPDATE RESTRICT;
> >
> > I'm guessing/wishfull-thinking that some hackery with the system catalog
> > could emulate that ?
> >
> > I'm currently using postgres 8.3 (we want to upgrade, but it's hard to
> > schedule).
>
> Two things first...
>
> 1. I assume this is same for 8.3
> 2. Someone from Hackers best to answer if this is safe on live system
>    or might require at least a restart.
>
> Your 2 catalog fields of interest are;
> pg_constraint.(confupdtype|confdeltype)
>
> Changing those for the relevant FKs should satisfy your needs.  I am
> not aware of those field values being duplicated anywhere.

Thanks for your answer. Experimenting a bit, those columns seem to have only a
cosmetic impact, meaning that "\d" will show the schema you expect, but the
behaviour remains unchanged (even after restarting postgres).

Digging further however, I found that pg_triggers can be used for my means :


CREATE TABLE tref(id INTEGER PRIMARY KEY);
CREATE TABLE t(id INTEGER PRIMARY KEY,
               refid INTEGER REFERENCES tref(id) ON DELETE RESTRICT);
INSERT INTO tref(id) VALUES (1),(2),(3);
INSERT INTO t(id, refid) VALUES (1,1),(2,NULL),(3,1),(4,2);

-- Cosmetic part: fkey looks updated but behaves the same (DELETE will fail)
                                                                      
UPDATE pg_constraint SET confdeltype ='c' WHERE conname='t_refid_fkey';
\d t
DELETE FROM tref WHERE id=1;

-- Functional part: DELETE will now work (after opening a new connection)
                                                                          
UPDATE pg_trigger SET tgfoid=(SELECT oid FROM pg_proc
                              WHERE proname ='RI_FKey_cascade_del')
   WHERE tgconstrname='t_refid_fkey'
      AND tgfoid=(SELECT oid FROM pg_proc
                  WHERE proname ='RI_FKey_restrict_del');
\c
DELETE FROM tref WHERE id=1;


> Strongly suggest you approach this with caution, as is standard
> advice regarding any manual catalog fiddling.

Of course. The psql script above works in my tests, but I could easily have
missed a side-effect that will comme back to bite me at the worst moment.
Unless someone can confirm that there are no hidden gotcha with this method,
I'll probably wait until our migration to PG9.0 to do those schema changes.


BTW, if anybody picks up the "ALTER CONSTRAINT" feature (low hanging fruit ?)
for the next postgres release, I'll be happy to ship them their
$FAVORITE_REWARD_BEVERAGE in the post :)

--
Vincent de Phily
Mobile Devices
+33 (0) 142 119 325
+353 (0) 85 710 6320

Warning
This message (and any associated files) is intended only for the use of its
intended recipient and may contain information that is confidential, subject
to copyright or constitutes a trade secret. If you are not the intended
recipient you are hereby notified that any dissemination, copying or
distribution of this message, or files associated with this message, is
strictly prohibited. If you have received this message in error, please
notify us immediately by replying to the message and deleting it from your
computer. Any views or opinions presented are solely those of the author
vincent.dephily@mobile-devices.fr and do not necessarily represent those of
the
company. Although the company has taken reasonable precautions to ensure no
viruses are present in this email, the company cannot accept responsibility
for any loss or damage arising from the use of this email or attachments.