agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedfunctions allowed in CHECK constraints
6+ messages / 4 participants
[nested] [flat]
* functions allowed in CHECK constraints
@ 2018-02-09 08:37 Achilleas Mantzios <itdev@itdevel.internal.net>
0 siblings, 1 reply; 6+ messages in thread
From: Achilleas Mantzios @ 2018-02-09 08:37 UTC (permalink / raw)
To: pgsql-sql
Hello,
I just tried specifying a function in a check constraint, (not being able to use a subquery or otherwise referencing rows in another (parent) table) and this worked. However reading the docs do not
point to any such direction, on the contrary docs say that subselects are not allowed.
e.g. this fails as expected :
alter table crew_eval_rankset_rank ADD CONSTRAINT ranksetid_chk CHECK((SELECT NOT appliestoall FROM crew_eval_rankset cer WHERE cer.id=ranksetid));
ERROR: cannot use subquery in check constraint
while this works unexpectedly :
create function crew_eval_ranksets_check_appliestoall (ranksetid int) RETURNS BOOLEAN LANGUAGE sql AS
$$
SELECT NOT appliestoall FROM crew_eval_rankset cer WHERE cer.id=ranksetid
$$;
alter table crew_eval_rankset_rank ADD CONSTRAINT ranksetid_chk CHECK(crew_eval_ranksets_check_appliestoall(ranksetid));
insert into crew_eval_rankset(setname,appliestoall) VALUES('all ranks','t');
insert into crew_eval_rankset_rank (ranksetid , rankid) VALUES(3,70);
ERROR: new row for relation "crew_eval_rankset_rank" violates check constraint "ranksetid_chk"
So, what's the point in forbidding the use of subselects if one can use functions? And OTOH if effectively doing so is bad for some reason, why let it happen with a function?
Basically, I tried this after reading : https://stackoverflow.com/questions/21791675/foreign-key-constraint-with-some-column-values-residing... , I wouldn't have thought
doing it after reading the docs.
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 6+ messages in thread
* functions allowed in CHECK constraints
@ 2018-02-09 09:04 Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Achilleas Mantzios @ 2018-02-09 09:04 UTC (permalink / raw)
To: pgsql-sql
Hello,
I just tried specifying a function in a check constraint, (not being able to use a subquery or otherwise referencing rows in another (parent) table) and this worked. However reading the docs do not
point to any such direction, on the contrary docs say that subselects are not allowed.
e.g. this fails as expected :
alter table crew_eval_rankset_rank ADD CONSTRAINT ranksetid_chk CHECK((SELECT NOT appliestoall FROM crew_eval_rankset cer WHERE cer.id=ranksetid));
ERROR: cannot use subquery in check constraint
while this works unexpectedly :
create function crew_eval_ranksets_check_appliestoall (ranksetid int) RETURNS BOOLEAN LANGUAGE sql AS
$$
SELECT NOT appliestoall FROM crew_eval_rankset cer WHERE cer.id=ranksetid
$$;
alter table crew_eval_rankset_rank ADD CONSTRAINT ranksetid_chk CHECK(crew_eval_ranksets_check_appliestoall(ranksetid));
insert into crew_eval_rankset(setname,appliestoall) VALUES('all ranks','t');
insert into crew_eval_rankset_rank (ranksetid , rankid) VALUES(3,70);
ERROR: new row for relation "crew_eval_rankset_rank" violates check constraint "ranksetid_chk"
So, what's the point in forbidding the use of subselects if one can use functions? And OTOH if effectively doing so is bad for some reason, why let it happen with a function?
Basically, I tried this after reading : https://stackoverflow.com/questions/21791675/foreign-key-constraint-with-some-column-values-residing... , I wouldn't have thought
doing it after reading the docs.
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: functions allowed in CHECK constraints
@ 2018-02-09 11:23 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 6+ messages in thread
From: Achilleas Mantzios @ 2018-02-09 11:23 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
On 09/02/2018 11:04, Achilleas Mantzios wrote:
> Hello,
well it seems the intuition by reading the docs was correct, although not exactly explicit. A Trigger is the way to do this.
> ....
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: functions allowed in CHECK constraints
@ 2018-02-09 13:58 David G. Johnston <david.g.johnston@gmail.com>
parent: Achilleas Mantzios <itdev@itdevel.internal.net>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2018-02-09 13:58 UTC (permalink / raw)
To: itdev@itdevel.internal.net; +Cc: pgsql-sql
On Fri, Feb 9, 2018 at 1:37 AM, Achilleas Mantzios <
itdev@itdevel.internal.net> wrote:
>
> So, what's the point in forbidding the use of subselects if one can use
> functions? And OTOH if effectively doing so is bad for some reason, why let
> it happen with a function?
Short answer, the system treats the function as a black-box and doesn't
know that you've used it to circumvent its prohibition on subselects. The
prohibition still is in place though. If you use a function you are
expected to ensure you do not violate any of the restrictions yourself.
The reason it is bad is because the query is only run at row insertion time
- and if the external data that the subquery references changes the check
constraint can become invalidated. If that happens at minimum a
dump/restore will fail.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: functions allowed in CHECK constraints
@ 2018-02-09 14:06 David G. Johnston <david.g.johnston@gmail.com>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2018-02-09 14:06 UTC (permalink / raw)
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql@lists.postgresql.org
On Fri, Feb 9, 2018 at 4:23 AM, Achilleas Mantzios <
achill@matrix.gatewaynet.com> wrote:
> On 09/02/2018 11:04, Achilleas Mantzios wrote:
>
>> Hello,
>>
> well it seems the intuition by reading the docs was correct, although not
> exactly explicit. A Trigger is the way to do this.
>
>> ....
>>
>
>
I answered this on the first email you sent. Functions are black-boxes so
its up to the author to ensure that the restrictions on check constraints
remain met when using them.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: functions allowed in CHECK constraints
@ 2018-02-09 14:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2018-02-09 14:32 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Feb 9, 2018 at 1:37 AM, Achilleas Mantzios <
> itdev@itdevel.internal.net> wrote:
>> So, what's the point in forbidding the use of subselects if one can use
>> functions? And OTOH if effectively doing so is bad for some reason, why let
>> it happen with a function?
> Short answer, the system treats the function as a black-box and doesn't
> know that you've used it to circumvent its prohibition on subselects. The
> prohibition still is in place though. If you use a function you are
> expected to ensure you do not violate any of the restrictions yourself.
Actually, I'd say that the possibility to use a function is a workaround
for the fact that you can't write a sub-select directly ;-). The latter
is more of an implementation restriction than anything else, stemming
from the fact that we don't run CHECK expressions through the planner.
Now, it's certainly true that you can use a sub-select to write a
non-immutable CHECK expression and thereby shoot yourself in the foot.
But you can shoot yourself in the foot that way without sub-selects,
too. We do not try to enforce that CHECK expressions are immutable.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2018-02-09 14:32 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-02-09 08:37 functions allowed in CHECK constraints Achilleas Mantzios <itdev@itdevel.internal.net>
2018-02-09 13:58 ` David G. Johnston <david.g.johnston@gmail.com>
2018-02-09 14:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-02-09 09:04 functions allowed in CHECK constraints Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-02-09 11:23 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-02-09 14:06 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox