Check constraints on non-immutable keys
in [PgSql]
|
From: Tom Lane on 30 Jun 2010 11:02 Magnus Hagander <magnus(a)hagander.net> writes: > On Wed, Jun 30, 2010 at 16:38, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >> 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 > In that case, should we at least throw a warning? I don't see a reason to do that. If we could distinguish actually problematic cases from safe cases, it would be helpful, but we can't. Moreover, throwing a warning would encourage people to do actively *unsafe* things to suppress the warning --- like marking functions as immutable when they really aren't. 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 12:33 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. -- 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: Magnus Hagander on 30 Jun 2010 10:40 On Wed, Jun 30, 2010 at 16:38, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > 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. Really? That sounds strange, but I can't claim to k now the 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 In that case, should we at least throw a warning? > stupid, but for example the very similar check of "a < now()" > would be perfectly safe and arguably useful. Well, things like timezone settings could come in effect there as well... -- 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
From: Robert Haas on 30 Jun 2010 12:11 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 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? Short of direct system catalog manipulation with malice aforethought, redefining a function to return different results after the fact is the only other case I can think of, and I'd propose we block that somehow too if I could figure out how. > 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. If it does, those headaches are their fault, whereas this one, at least as I see it, is our fault. The fact that you can injure yourself badly with a sharp knife is not an excuse for someone to hand it to you pointy-end-first. I think it would be useful to have check constraints that are only enforced on new data, and allowing immutable functions there would make sense. But I can't think of any reasonable use case for having a non-immutable check constraint of the type we have now. Can you? Besides breaking pg_dump, it can also potentially foul up constraint exclusion. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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: Robert Haas on 30 Jun 2010 11:31 On Wed, Jun 30, 2010 at 11:02 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Magnus Hagander <magnus(a)hagander.net> writes: >> On Wed, Jun 30, 2010 at 16:38, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >>> 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 > >> In that case, should we at least throw a warning? > > I don't see a reason to do that. �If we could distinguish actually > problematic cases from safe cases, it would be helpful, but we can't. > > Moreover, throwing a warning would encourage people to do actively > *unsafe* things to suppress the warning --- like marking functions > as immutable when they really aren't. My scintillating contribution to this discussion is the observation that unrestorable dumps suck. A lot. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
|
Next
|
Last
Pages: 1 2 3 4 Prev: Look-behind regular expressions Next: [HACKERS] Check constraints on non-immutable keys |