| From: | Vincent de Phily <vincent(dot)dephily(at)mobile-devices(dot)fr> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | altering foreign key without a table scan |
| Date: | 2011-08-15 10:31:49 |
| Message-ID: | 2313139.yUPOj5e1or@moltowork |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Thomas Markus | 2011-08-15 11:50:27 | Re: How to tame a gigantic (100+ lines) query in a web app? |
| Previous Message | Sim Zacks | 2011-08-15 08:32:55 | Re: How to tame a gigantic (100+ lines) query in a web app? |