BUG #16158: Check constraints using SQL functions work incorrectly - Mailing list pgsql-bugs

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.