Обсуждение: creation of foreign key without checking prior data?
Hi, would it be possible to add a way to create foreign key without checking of prior data? Before you will say it's a bad idea, because then you might get invalid data - i know. You can geet invalid data in column that is checked by foriegn key even now - by temporarily disablnig triggers and/or writing special (or bad) triggers. So, since (as we know) foreign keys are not fault-proof, wouldn't it be good to provide a way to create them without all this time-consuming check? It would come handy for example when loading dumps (disabling fkey for load is bad option, because you need first to create it, before you can disable it, and to create it you need unique constraint on referenced table, which required index - and i don't want index to be created before data is loaded, because it's just too slow. depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
On Thu, Sep 17, 2009 at 11:40 AM, hubert depesz lubaczewski <depesz@depesz.com> wrote: > > So, since (as we know) foreign keys are not fault-proof, wouldn't it be > good to provide a way to create them without all this time-consuming > check? No. If you don't want the behavior of a foreign key then just don't define a foreign key. Load the data, clean it up, then create the foreign key -- Peter Hunsberger
On Thu, Sep 17, 2009 at 12:31:14PM -0500, Peter Hunsberger wrote: > On Thu, Sep 17, 2009 at 11:40 AM, hubert depesz lubaczewski > <depesz@depesz.com> wrote: > > > > So, since (as we know) foreign keys are not fault-proof, wouldn't it be > > good to provide a way to create them without all this time-consuming > > check? > > No. > > If you don't want the behavior of a foreign key then just don't define > a foreign key. Load the data, clean it up, then create the foreign key I think you are missing the point. Data is clean. It's dump. creation of fkey takes time, and i'd like to avoid this delay. depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
On Thu, Sep 17, 2009 at 12:44 PM, hubert depesz lubaczewski <depesz@depesz.com> wrote: > On Thu, Sep 17, 2009 at 12:31:14PM -0500, Peter Hunsberger wrote: >> On Thu, Sep 17, 2009 at 11:40 AM, hubert depesz lubaczewski >> <depesz@depesz.com> wrote: >> > >> > So, since (as we know) foreign keys are not fault-proof, wouldn't it be >> > good to provide a way to create them without all this time-consuming >> > check? >> >> No. >> >> If you don't want the behavior of a foreign key then just don't define >> a foreign key. Load the data, clean it up, then create the foreign key > > I think you are missing the point. Data is clean. It's dump. creation of > fkey takes time, and i'd like to avoid this delay. You can't have a foreign key that doesn't have relational integrity, it is no longer a foreign key. If you don't want the delay then don't define the key, at least until some point at which you can take the delay. If there is never such a time then your operational scenario needs changing, not Postgres... -- Peter Hunsberger
On Thu, Sep 17, 2009 at 01:22:52PM -0500, Peter Hunsberger wrote: > You can't have a foreign key that doesn't have relational integrity, > it is no longer a foreign key. If you don't want the delay then don't > define the key, at least until some point at which you can take the > delay. If there is never such a time then your operational scenario > needs changing, not Postgres... you do realize that having foreign key defined doesn't guarantee integrity? depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
On Thu, Sep 17, 2009 at 08:34:20PM +0200, hubert depesz lubaczewski wrote: > On Thu, Sep 17, 2009 at 01:22:52PM -0500, Peter Hunsberger wrote: > > You can't have a foreign key that doesn't have relational integrity, > > it is no longer a foreign key. > > you do realize that having foreign key defined doesn't guarantee > integrity? The obvious cases would be software bugs and bad hardware. What else? Huh, how about users scribbling over PG's files! Not sure where to classify that but could either happen maliciously or accidentally as the result of trying to clean up. By having an override here you seem to be saying that you ultimately trust yourself more than PG and/or the hardware its running on. I suppose the trade off is time you *may* spend cleaning up later on if this isn't true vs. the time PG *will* spend verifying the constraint now. Interesting trade off, never really considered it before. Sounds valid, though the general mantra here is that PG knows best. Is that always true? -- Sam http://samason.me.uk/