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