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(at)gmail(dot)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.