Thread: altering foreign key without a table scan
Hi list, as part of a db schema update, I'd like to alter the "on update" property of a fkey, for example going from : > ALTER TABLE ONLY foo ADD CONSTRAINT foo_barid_fkey FOREIGN KEY (barid) > REFERENCES bar(id) ON UPDATE CASCADE ON DELETE CASCADE; to : > ALTER TABLE ONLY foo ADD CONSTRAINT foo_barid_fkey FOREIGN KEY (barid) > REFERENCES bar(id) ON UPDATE RESTRICT ON DELETE CASCADE; I understand I can create the new fkey and drop the old one, but this requires a scan of the table (to check that no existing data violates the new fkey) which, on this large, heavily-updated, no-downtime table I can't really aford. 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). Thanks in advance. -- Vincent de Phily
Vincent de Phily <vincent.dephily@mobile-devices.fr> writes: > Hi list, > > as part of a db schema update, I'd like to alter the "on update" property of a > fkey, for example going from : >> ALTER TABLE ONLY foo ADD CONSTRAINT foo_barid_fkey FOREIGN KEY (barid) >> REFERENCES bar(id) ON UPDATE CASCADE ON DELETE CASCADE; > to : >> ALTER TABLE ONLY foo ADD CONSTRAINT foo_barid_fkey FOREIGN KEY (barid) >> REFERENCES bar(id) ON UPDATE RESTRICT ON DELETE CASCADE; > > I understand I can create the new fkey and drop the old one, but this requires > a scan of the table (to check that no existing data violates the new fkey) > which, on this large, heavily-updated, no-downtime table I can't really aford. > > 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. Strongly suggest you approach this with caution, as is standard advice regarding any manual catalog fiddling. HTH > > Thanks in advance. > > -- > Vincent de Phily > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 305.321.1144
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.
Vincent de Phily <vincent.dephily@mobile-devices.fr> writes:
> On Thursday 18 August 2011 13:08:18 Jerry Sievers wrote:
>> 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 :
IIRC, there are fields of pg_constraint that are copied into the
pg_trigger rows for the supporting triggers, so as to save one catalog
lookup at run time. If you diddle one manually, you'd better diddle
both.
regards, tom lane
On Friday 19 August 2011 11:52:50 Tom Lane wrote: > Vincent de Phily <vincent.dephily@mobile-devices.fr> writes: > > 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 > > : > IIRC, there are fields of pg_constraint that are copied into the > pg_trigger rows for the supporting triggers, so as to save one catalog > lookup at run time. If you diddle one manually, you'd better diddle > both. Some relid values are indeed duplicated in pg_constraint and pg_trigger, but it doesn't look like I need to fiddle with those ? I'm only touching pg_trigger.tgfoid and pg_constraint.confdeltype/confupdtype (which indeed seem to say the same thing in a different way). Do you know if there is something else I've missed ? Thanks. -- Vincent de Phily
Vincent de Phily <vincent.dephily@mobile-devices.fr> writes:
> On Friday 19 August 2011 11:52:50 Tom Lane wrote:
>> IIRC, there are fields of pg_constraint that are copied into the
>> pg_trigger rows for the supporting triggers, so as to save one catalog
>> lookup at run time. If you diddle one manually, you'd better diddle
>> both.
> Some relid values are indeed duplicated in pg_constraint and pg_trigger, but
> it doesn't look like I need to fiddle with those ?
> I'm only touching pg_trigger.tgfoid and pg_constraint.confdeltype/confupdtype
> (which indeed seem to say the same thing in a different way). Do you know if
> there is something else I've missed ?
Yeah, that seems to be it except for the deferrable/deferred fields,
which match up in the obvious way. I had been thinking the RI triggers
avoided doing a lookup in pg_constraint, but that was mistaken. (I
think we used to store all that info in tgargs, but we evidently don't
anymore.)
regards, tom lane
On Friday 19 August 2011 12:55:01 Tom Lane wrote: > Vincent de Phily <vincent.dephily@mobile-devices.fr> writes: > > On Friday 19 August 2011 11:52:50 Tom Lane wrote: > >> IIRC, there are fields of pg_constraint that are copied into the > >> pg_trigger rows for the supporting triggers, so as to save one catalog > >> lookup at run time. If you diddle one manually, you'd better diddle > >> both. > > > > Some relid values are indeed duplicated in pg_constraint and pg_trigger, > > but it doesn't look like I need to fiddle with those ? > > > > I'm only touching pg_trigger.tgfoid and > > pg_constraint.confdeltype/confupdtype (which indeed seem to say the > > same thing in a different way). Do you know if there is something else > > I've missed ? > > Yeah, that seems to be it except for the deferrable/deferred fields, > which match up in the obvious way. I had been thinking the RI triggers > avoided doing a lookup in pg_constraint, but that was mistaken. (I > think we used to store all that info in tgargs, but we evidently don't > anymore.) Thanks, I'll look into applying those next week then. If I never get back to the list about it, it'll mean that it worked without issues :) -- Vincent de Phily