Check constraints on non-immutable keys
in [PgSql]
|
From: Tom Lane on 30 Jun 2010 11:49 Robert Haas <robertmhaas(a)gmail.com> writes: > My scintillating contribution to this discussion is the observation > that unrestorable dumps suck. No doubt, but is this a real problem in practice? I can't recall many field complaints about it. And the ones I do recall wouldn't have been prevented by a check as stupid as "are there immutable functions in here". I still say that what such a check is likely to do is encourage people to mis-label mutable functions as immutable ... which will cause them a lot of *other* headaches. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 30 Jun 2010 10:38 Magnus Hagander <magnus(a)hagander.net> writes: > Shouldn't we disallow anything that's not IMMUTABLE in a check constraint? I think you'd get too many howls of pain ... also, such a restriction is likely contrary to SQL spec. The example seems to me to be in the category of "so don't do that" rather than something that we need to save users from. Yes, it's stupid, but for example the very similar check of "a < now()" would be perfectly safe and arguably useful. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Richard Huxton on 30 Jun 2010 13:17 On 30/06/10 18:11, Magnus Hagander wrote: > On Wed, Jun 30, 2010 at 18:33, Richard Huxton<dev(a)archonet.com> wrote: >> >> IMHO The real solution would be something that could strip/rewrite the >> constraint on restore rather than trying to prevent people being stupid >> though. People *will* just tag their functions as immutable to get them to >> work. > > Are you sure? The people most likely to "just tag their functions as > immutable", are the same ones most unlikely to know *how to do that*. > At least for what I think is the majority case - which is calling > builtin functions. People just cut and paste this stuff from ancient blog entries. Understanding is not necessary. Hell, I do it sometimes if I'm dealing with something like LDAP where I don't really have a deep knowledge of the situation. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 30 Jun 2010 13:16 Robert Haas <robertmhaas(a)gmail.com> writes: > On Wed, Jun 30, 2010 at 11:49 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >> I can't recall many >> field complaints about it. �And the ones I do recall wouldn't have been >> prevented by a check as stupid as "are there immutable functions in >> here". > Hopefully there aren't too many ways to get data into a table that > doesn't satisfy its check constraint - what else are you thinking of? Nobody is talking about having bypassed a check constraint --- the problem here is what if the "same" constraint condition is true today and false tomorrow. The cases that I can recall were not directly about time passing, but rather about check constraints that were designed to examine the contents of other tables or other rows in the same table. Functions that do that are properly declared STABLE not VOLATILE, but they'd still be rejected by Magnus' proposed restriction. The problem is that people would be *very* likely to just mark them IMMUTABLE rather than understand that what they're trying is fundamentally unreliable. That would cause them other problems, and they'd still be at risk of their dumps not reloading. I concur with the thought that the most useful solution might be a way to tell pg_restore to remove or disable check constraints. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Magnus Hagander on 30 Jun 2010 13:11 On Wed, Jun 30, 2010 at 18:33, Richard Huxton <dev(a)archonet.com> wrote: > On 30/06/10 17:11, Robert Haas wrote: >> >> On Wed, Jun 30, 2010 at 11:49 AM, Tom Lane<tgl(a)sss.pgh.pa.us> �wrote: >>> >>> Robert Haas<robertmhaas(a)gmail.com> �writes: >>>> >>>> My scintillating contribution to this discussion is the observation >>>> that unrestorable dumps suck. >>> >>> No doubt, but is this a real problem in practice? >> >> Magnus tells me that that was what prompted his original email. > > I've done it. Luckily only with a small and fully functioning database so I > could drop the constraint and re-dump it. > > Had a "recent_date" domain that was making sure new diary-style entries had > a plausible date. Of course, two years later my dump can no longer restore > the oldest record :-( > > IMHO The real solution would be something that could strip/rewrite the > constraint on restore rather than trying to prevent people being stupid > though. People *will* just tag their functions as immutable to get them to > work. Are you sure? The people most likely to "just tag their functions as immutable", are the same ones most unlikely to know *how to do that*. At least for what I think is the majority case - which is calling builtin functions. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: Look-behind regular expressions Next: [HACKERS] Check constraints on non-immutable keys |