| Lists: | pgsql-general |
|---|
| From: | "Ian Harding" <ianh(at)tpchd(dot)org> |
|---|---|
| To: | <pgsql-general(at)postgresql(dot)org> |
| Subject: | Can't use subselect in check constraint |
| Date: | 2001-12-19 17:31:11 |
| Message-ID: | sc205e73.079@mail.tpchd.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
Is this something that will change anytime soon? I could not find anything about it in the todo...
Thanks!
Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: iharding(at)tpchd(dot)org
| From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
|---|---|
| To: | Ian Harding <ianh(at)tpchd(dot)org> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Can't use subselect in check constraint |
| Date: | 2001-12-19 18:18:56 |
| Message-ID: | 200112191818.fBJIIuS00427@candle.pha.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
> Is this something that will change anytime soon? I could not find anything about it in the todo...
My understanding is that check constraints can't be queries; at least I
have never seen them.
--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | "Ian Harding" <ianh(at)tpchd(dot)org> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Can't use subselect in check constraint |
| Date: | 2001-12-19 19:02:32 |
| Message-ID: | 2052.1008788552@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
"Ian Harding" <ianh(at)tpchd(dot)org> writes:
> Is this something that will change anytime soon?
It's not high-priority in my mind. The difficulty is the lack of a
planning step for constraint expressions. Maybe we could fix this
when we redesign querytrees; right now I think there's too much cruft
in the way (memory leaks, state kept in the querytree, etc etc)
regards, tom lane
| From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | Ian Harding <ianh(at)tpchd(dot)org>, <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Can't use subselect in check constraint |
| Date: | 2001-12-20 21:38:39 |
| Message-ID: | 20011220133458.C82911-100000@megazone23.bigpanda.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
On Wed, 19 Dec 2001, Tom Lane wrote:
> "Ian Harding" <ianh(at)tpchd(dot)org> writes:
> > Is this something that will change anytime soon?
>
> It's not high-priority in my mind. The difficulty is the lack of a
> planning step for constraint expressions. Maybe we could fix this
> when we redesign querytrees; right now I think there's too much cruft
> in the way (memory leaks, state kept in the querytree, etc etc)
Also, AFAICS, check constraints with subselects are constraints on the
other tables named in the subselect as well and it could be on a
case which the current check constraints don't fire (for example
on delete from a table where a set value function is used, like
check (select count(*) from foo)>col1) or something like that).
| From: | "Gregory Wood" <gregw(at)com-stock(dot)com> |
|---|---|
| To: | "PostgreSQL-General" <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: Can't use subselect in check constraint |
| Date: | 2001-12-20 21:46:29 |
| Message-ID: | 046601c1899f$f4e46a20$7889ffcc@comstock.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
> > Is this something that will change anytime soon? I could not find
anything about it in the todo...
>
> My understanding is that check constraints can't be queries; at least I
> have never seen them.
Considering that you could potentially have to reevaluate each CHECK
constraint every time you updated any table in the database, I would think
it would be a *real* bad idea to allow that. There is no way to
automatically create the triggers needed to satisfy "When should I check the
constraint?"
If it can't be constructed as a check against a literal value or values, or
a foreign key constraint, it should be up to the user to maintain that
consistency check with the appropriate triggers.
Greg
| From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
|---|---|
| To: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
| Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Ian Harding <ianh(at)tpchd(dot)org>, pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Can't use subselect in check constraint |
| Date: | 2001-12-20 21:54:52 |
| Message-ID: | 200112202154.fBKLsq724811@candle.pha.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-general |
>
> On Wed, 19 Dec 2001, Tom Lane wrote:
>
> > "Ian Harding" <ianh(at)tpchd(dot)org> writes:
> > > Is this something that will change anytime soon?
> >
> > It's not high-priority in my mind. The difficulty is the lack of a
> > planning step for constraint expressions. Maybe we could fix this
> > when we redesign querytrees; right now I think there's too much cruft
> > in the way (memory leaks, state kept in the querytree, etc etc)
>
> Also, AFAICS, check constraints with subselects are constraints on the
> other tables named in the subselect as well and it could be on a
> case which the current check constraints don't fire (for example
> on delete from a table where a set value function is used, like
> check (select count(*) from foo)>col1) or something like that).
It this a TODO item.
--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026