Check constraints on non-immutable keys
in [PgSql]
|
From: Magnus Hagander on 30 Jun 2010 13:37 On Wed, Jun 30, 2010 at 19:16, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > 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. Uh, say what? Are you saying pg_restore should actually remove something from the database schema? And thus no longer be valid for taking database backups? Or are you just saying that it should have the constraints off, load the data, and then somehow create the constraint without having it validate the exinsting data (like the NOCHECK option in MSSQL?) -- 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: Merlin Moncure on 30 Jun 2010 13:44 On Wed, Jun 30, 2010 at 9:47 AM, Magnus Hagander <magnus(a)hagander.net> wrote: > We currently allow this: > > postgres=# create table t(a timestamptz not null primary key, check(a > now())); > NOTICE: �CREATE TABLE / PRIMARY KEY will create implicit index > "t_pkey" for table "t" > CREATE TABLE > > > Which seems very wrong. For one thing, a dump of this database can not > be restored if now() has advanced enough into the future (which it > will eventually). It also makes impossible to do things like SET a=a > on the table. > > Yes, this is clearly a stupidly defined constraint, but why do we allow it? > > Shouldn't we disallow anything that's not IMMUTABLE in a check constraint? suppose you did do this: shouldn't you then also recheck the constraint if the function is create/replaced? merlin -- 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 14:13 Magnus Hagander <magnus(a)hagander.net> writes: > On Wed, Jun 30, 2010 at 19:16, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >> I concur with the thought that the most useful solution might be a way >> to tell pg_restore to remove or disable check constraints. > Uh, say what? Are you saying pg_restore should actually remove > something from the database schema? And thus no longer be valid for > taking database backups? pg_restore, not pg_dump. It's no more unreasonable an idea than the current pg_restore options for selective restores, AFAICS. You can already cause pg_restore to not restore PK and FK constraints, for example, so why not 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 14:16 On Wed, Jun 30, 2010 at 20:13, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Magnus Hagander <magnus(a)hagander.net> writes: >> On Wed, Jun 30, 2010 at 19:16, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >>> I concur with the thought that the most useful solution might be a way >>> to tell pg_restore to remove or disable check constraints. > >> Uh, say what? Are you saying pg_restore should actually remove >> something from the database schema? And thus no longer be valid for >> taking database backups? > > pg_restore, not pg_dump. �It's no more unreasonable an idea than the > current pg_restore options for selective restores, AFAICS. �You can > already cause pg_restore to not restore PK and FK constraints, for > example, so why not check constraints? Oh, sorry, I misread that - I thought you suggested it would do so by default. Clearly, I should've left work about 2 minutes earlier and not bothered you with that response :-) -- 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: Peter Eisentraut on 30 Jun 2010 18:27 On ons, 2010-06-30 at 10:38 -0400, Tom Lane 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. <kibo> "The <search condition> shall simply contain a <boolean value expression> that is retrospectively deterministic." This is then defined in a rather complex manner that ends up disallowing col > now() but allowing col < now(). </kibo> -- 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 |