Horton

Thread

Commits

  1. Doc: back-patch documentation about limitations of CHECK constraints.

  1. BUG #16158: Check constraints using SQL functions work incorrectly

    The Post Office <noreply@postgresql.org> — 2019-12-10T12:27:13Z

    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.

  2. Re: BUG #16158: Check constraints using SQL functions work incorrectly

    David G. Johnston <david.g.johnston@gmail.com> — 2019-12-10T15:00:24Z

    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.

  3. Re: BUG #16158: Check constraints using SQL functions work incorrectly

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-12-10T15:14:31Z

    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

  4. Re: BUG #16158: Check constraints using SQL functions work incorrectly

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-12-10T15:39:59Z

    "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