pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16158: Check constraints using SQL functions work incorrectly
4+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2019-12-11 49b83f6474 7ef59c9b18 6bf23e8c6e 3ddd8ee716 af4006f009 Doc: back-patch documentation about limitations of CHECK constraints.
* BUG #16158: Check constraints using SQL functions work incorrectly
@ 2019-12-10 12:27 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 4+ messages in thread
From: PG Bug reporting form @ 2019-12-10 12:27 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: pjander2@gmail.com
The following bug has been logged on the website:
Bug reference: 16158
Logged by: Piotr Jander
Email address: pjander2@gmail.com
PostgreSQL version: 11.5
Operating system: x86_64 GNU/Linux
Description:
Summary: I defined a check constraint `project_limits` using
[SQL functions](https://www.postgresql.org/docs/9.1/xfunc-sql.html).
The constraint can be enforced using the commands
```
alter table "projects" add constraint project_limits check(...) not valid;
alter table "projects" validate constraint project_limits;
```
However, it is not enforced on inserts. Consequently, the database can get
into
an invalid state, as demonstrated below.
The sequence of commands below is complete and self-contained.
Given PostgreSQL 11.5:
```
project_manager=> SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian
6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)
```
We create tables `organizations` and `projects`:
```
project_manager=> create table "organizations" ("id" SERIAL NOT NULL PRIMARY
KEY,"limit" INTEGER NOT NULL);
CREATE TABLE
project_manager=> create table "projects" ("id" SERIAL NOT NULL PRIMARY
KEY,"organization_id" INTEGER NOT NULL);
CREATE TABLE
project_manager=> alter table "projects" add constraint "organizations"
foreign key("organization_id") references "organizations"("id");
ALTER TABLE
```
We define SQL functions `org_limit` and `project_count` which will be used
in
the check constraint:
```
project_manager=> create or replace function org_limit(org_id integer)
returns integer as 'select "limit" from "organizations" where "id" = org_id'
language sql;
CREATE FUNCTION
project_manager=> create or replace function project_count(org_id integer)
returns bigint as 'select count(*) from "projects" where "organization_id" =
org_id' language sql;
CREATE FUNCTION
```
The intended constraint is that there should never be more projects in an
organizations that the organization's limit. Before actually adding the
check
constraint, let us arrive at a state which would violate this constraint
(two
projects in an organization which has a limit of 1):
```
project_manager=> insert into "organizations" ("limit") values (1);
INSERT 0 1
project_manager=> select * from "organizations";
id | limit
----+-------
1 | 1
(1 row)
project_manager=> insert into "projects" ("organization_id") values (1);
INSERT 0 1
project_manager=> insert into "projects" ("organization_id") values (1);
INSERT 0 1
project_manager=> select * from "projects";
id | organization_id
----+-----------------
1 | 1
2 | 1
(2 rows)
```
Now we finally add the contraint (first as not valid and then we attempt
to
validate it):
```
project_manager=> alter table "projects" add constraint project_limits
check(project_count(organization_id) <= org_limit(organization_id)) not
valid;
ALTER TABLE
project_manager=> alter table "projects" validate constraint
project_limits;
ERROR: check constraint "project_limits" is violated by some row
```
Indeed, we can manually verify that the constraint is violated:
```
project_manager=> select project_count(1);
project_count
---------------
2
(1 row)
project_manager=> select org_limit(1);
org_limit
-----------
1
(1 row)
```
After we delete one of the two projects, we can successfully validate the
constraint:
```
project_manager=> delete from "projects" where "id" = 2;
DELETE 1
project_manager=> select * from "projects";
id | organization_id
----+-----------------
1 | 1
(1 row)
project_manager=> alter table "projects" validate constraint
project_limits;
ALTER TABLE
```
Before we attempt to violate the constraint again, we confirm that the
constraint is added to the table:
```
project_manager=> \d "projects";
Table "public.projects"
Column | Type | Collation | Nullable | Default
-----------------+---------+-----------+----------+--------------------------------------
id | integer | | not null |
nextval('projects_id_seq'::regclass)
organization_id | integer | | not null |
Indexes:
"projects_pkey" PRIMARY KEY, btree (id)
Check constraints:
"project_limits" CHECK (project_count(organization_id) <=
org_limit(organization_id))
Foreign-key constraints:
"organizations" FOREIGN KEY (organization_id) REFERENCES
organizations(id)
```
BUG: We can violate the constraint by insert another project.
```
project_manager=> insert into "projects" ("organization_id") values (1);
INSERT 0 1
```
At this point, the `validate constraint` command doesn't catch the
violation
either.
```
project_manager=> alter table "projects" validate constraint
project_limits;
ALTER TABLE
```
Again, we manually verify that the constraint is violated:
```
project_manager=> select project_count(1);
project_count
---------------
2
(1 row)
project_manager=> select org_limit(1);
org_limit
-----------
1
(1 row)
```
To catch the violation, we need to drop the constraint, add it again, and
validate:
```
project_manager=> alter table "projects" drop constraint project_limits;
ALTER TABLE
project_manager=> alter table "projects" add constraint project_limits
check(project_count(organization_id) <= org_limit(organization_id)) not
valid;
ALTER TABLE
project_manager=> alter table "projects" validate constraint
project_limits;
ERROR: check constraint "project_limits" is violated by some row
```
My conjecture is that the bug is due to the use of SQL functions in the
check.
However, the
[docs](https://www.postgresql.org/docs/9.4/ddl-constraints.html)
on constraints do not mention any limitations on using such SQL functions
in
checks. If such limitations exist, they should be mentioned in the docs.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16158: Check constraints using SQL functions work incorrectly
@ 2019-12-10 15:00 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2019-12-10 15:00 UTC (permalink / raw)
To: pjander2@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Dec 10, 2019 at 5:28 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 16158
> Logged by: Piotr Jander
> Email address: pjander2@gmail.com
> PostgreSQL version: 11.5
> Operating system: x86_64 GNU/Linux
> Description:
>
> [...]
>
> My conjecture is that the bug is due to the use of SQL functions in the
> check.
> However, the
> [docs](https://www.postgresql.org/docs/9.4/ddl-constraints.html)
> on constraints do not mention any limitations on using such SQL functions
> in
> checks. If such limitations exist, they should be mentioned in the docs.
>
You should read the most current version of the documentation for a feature
before reporting a bug. In this case the warnings you desired have been
added to more recently versions of the documentation.
https://www.postgresql.org/docs/12/ddl-constraints.html
Short answer, check constraints are designed to work only with data present
on the table upon which they are defined. Use triggers to work with other
tables.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16158: Check constraints using SQL functions work incorrectly
@ 2019-12-10 15:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2019-12-10 15:14 UTC (permalink / raw)
To: pjander2@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> Summary: I defined a check constraint `project_limits` using
> [SQL functions](https://www.postgresql.org/docs/9.1/xfunc-sql.html).
This is not a bug; you've ignored the restriction that check constraints
must be immutable. See the "Notes" at the bottom of this section:
https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS
In the particular case at hand, I think the behavior you're complaining
about stems from the fact that the CHECK condition is evaluated before
the actual row insertion has happened.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #16158: Check constraints using SQL functions work incorrectly
@ 2019-12-10 15:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2019-12-10 15:39 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pjander2@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> You should read the most current version of the documentation for a feature
> before reporting a bug. In this case the warnings you desired have been
> added to more recently versions of the documentation.
Hm, I was thinking that that text had been there for awhile --- certainly
it's not a new restriction. I wonder why we didn't back-patch it?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-12-10 15:39 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-10 12:27 BUG #16158: Check constraints using SQL functions work incorrectly PG Bug reporting form <noreply@postgresql.org>
2019-12-10 15:00 ` David G. Johnston <david.g.johnston@gmail.com>
2019-12-10 15:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-12-10 15:14 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox