Обсуждение: BUG #16158: Check constraints using SQL functions work incorrectly
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.
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.
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.
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
"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