BUG: pg_class.relchecks overflow, making table undroppable
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
This thread has been committed, so CI has stopped here. Anything below is the last result it produced.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253927psql -h localhost -U postgresBuilt from patchset v7 (message #7), September 27, 2026 at 12:48 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253927_7 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253927_7 && git checkout t253927_7Patchset v7 (message #7) is on t253927_7
Hi,
There's probably no user that finds good use for this many check
constraints, but users *can* create > 2^15 CHECK constraints on a
table, and doing so will overflow the int16 (sqltype int2) column in
which the relation's count of those is stored.
This causes various issues, such as possible WARNING spam to users
that need to load that relation into relcache, and an inability to
drop the relation because dropping the table requires the constraints
to be dropped first, and dropping a constraint decrements the counter
that has a check is in place to avoid the counter ever dropping below
zero (with an error if you try to decrement non-positive counter
values).
I think we should forbid creating such large amounts of CHECK
constraints (as attached, backpatch-safe), or drop the "relchecks"
field wholesale/replace it with a 'haschecks' field.
Also attached is an SQL script that shows the issue, and which doesn't
fail in the unsafe manner once PG is patched with the attached patch.
Note: the attached patch does not solve any issues in existing
databases with overflowed relchecks fields.
Kind regards,
Matthias van de Meent
Databricks (https://www.databricks.com)
Hi, sending v2 to the list as well. This version changes the limit
check from `>=` to `>`, allowing the documented maximum of 32,767
CHECK constraints.
On Thu, Sep 24, 2026 at 10:34 PM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
Show quoted text
Hi,
There's probably no user that finds good use for this many check
constraints, but users *can* create > 2^15 CHECK constraints on a
table, and doing so will overflow the int16 (sqltype int2) column in
which the relation's count of those is stored.This causes various issues, such as possible WARNING spam to users
that need to load that relation into relcache, and an inability to
drop the relation because dropping the table requires the constraints
to be dropped first, and dropping a constraint decrements the counter
that has a check is in place to avoid the counter ever dropping below
zero (with an error if you try to decrement non-positive counter
values).I think we should forbid creating such large amounts of CHECK
constraints (as attached, backpatch-safe), or drop the "relchecks"
field wholesale/replace it with a 'haschecks' field.Also attached is an SQL script that shows the issue, and which doesn't
fail in the unsafe manner once PG is patched with the attached patch.Note: the attached patch does not solve any issues in existing
databases with overflowed relchecks fields.Kind regards,
Matthias van de Meent
Databricks (https://www.databricks.com)
On Thu, Sep 24, 2026 at 07:03:45PM +0200, Matthias van de Meent wrote:
This causes various issues, such as possible WARNING spam to users
that need to load that relation into relcache, and an inability to
drop the relation because dropping the table requires the constraints
to be dropped first, and dropping a constraint decrements the counter
that has a check is in place to avoid the counter ever dropping below
zero (with an error if you try to decrement non-positive counter
values).I think we should forbid creating such large amounts of CHECK
constraints (as attached, backpatch-safe), or drop the "relchecks"
field wholesale/replace it with a 'haschecks' field.Also attached is an SQL script that shows the issue, and which doesn't
fail in the unsafe manner once PG is patched with the attached patch.
Ahah, fun one! That's in the same line as the recent trigger fix in
b99b74144f9f where catalogs could overflow. Even if it's something
that people would not do in practice, this deserves a backpatch. Will
look at that later..
--
Michael
Hi,
On Fri, Sep 25, 2026 at 07:59:22AM +0900, Michael Paquier wrote:
On Thu, Sep 24, 2026 at 07:03:45PM +0200, Matthias van de Meent wrote:
This causes various issues, such as possible WARNING spam to users
that need to load that relation into relcache, and an inability to
drop the relation because dropping the table requires the constraints
to be dropped first, and dropping a constraint decrements the counter
that has a check is in place to avoid the counter ever dropping below
zero (with an error if you try to decrement non-positive counter
values).I think we should forbid creating such large amounts of CHECK
constraints (as attached, backpatch-safe), or drop the "relchecks"
field wholesale/replace it with a 'haschecks' field.Also attached is an SQL script that shows the issue, and which doesn't
fail in the unsafe manner once PG is patched with the attached patch.Ahah, fun one! That's in the same line as the recent trigger fix in
b99b74144f9f where catalogs could overflow. Even if it's something
that people would not do in practice, this deserves a backpatch.
+1
Just a few comments:
=== 1
It needs a rebase due to 926627bf902
=== 2
+ ereport(ERROR,
+ errmsg("too many check constraints on relation \"%s\"",
+ RelationGetQualifiedRelationName(rel)));I think ERRCODE_PROGRAM_LIMIT_EXCEEDED would be appropriate here?
=== 3
numchecks++;
+
+ if (numchecks >= PG_INT16_MAX)
+ ereport(ERROR,
+ errmsg("too many check constraints on relation \"%s\"",
+ RelationGetQualifiedRelationName(rel)));I wonder if it wouldn't make more sense to check numchecks >= PG_INT16_MAX before
calling StoreRelCheck()? That would avoid inserting the constraint, recording its
dependencies and invoking the post create hook for an object that will be rejected.
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
On Fri, Sep 25, 2026 at 4:41 PM Bertrand Drouvot
<bertranddrouvot.pg@gmail.com> wrote:
numchecks++; + + if (numchecks >= PG_INT16_MAX) + ereport(ERROR, + errmsg("too many check constraints on relation \"%s\"", + RelationGetQualifiedRelationName(rel)));
Hi.
In MergeWithExistingConstraint, we have
```
else if (pg_add_s16_overflow(con->coninhcount, 1,
&con->coninhcount))
ereport(ERROR,
errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("too many inheritance parents"));
```
here, we can also use pg_add_s16_overflow?
On Fri, Sep 25, 2026 at 08:41:38AM +0000, Bertrand Drouvot wrote:
Just a few comments:
=== 1
It needs a rebase due to 926627bf902
=== 2
+ ereport(ERROR, + errmsg("too many check constraints on relation \"%s\"", + RelationGetQualifiedRelationName(rel)));I think ERRCODE_PROGRAM_LIMIT_EXCEEDED would be appropriate here?
An error record would be appropriate, yes. That's reachable with SQL.
numchecks++; + + if (numchecks >= PG_INT16_MAX) + ereport(ERROR, + errmsg("too many check constraints on relation \"%s\"", + RelationGetQualifiedRelationName(rel)));I wonder if it wouldn't make more sense to check numchecks >= PG_INT16_MAX before
calling StoreRelCheck()? That would avoid inserting the constraint, recording its
dependencies and invoking the post create hook for an object that will be rejected.
Yeah, let's do that. That's unlikely but it would just be a waste and
that's just switching the order of things.
If somebody is willing to send an updated patch, feel free, I don't
have the energy for that today.. Jian also mentioned
pg_add_s16_overflow(), which is a good idea for this case. :D
--
Michael
On Fri, 25 Sept 2026 at 11:05, Michael Paquier <michael@paquier.xyz> wrote:
On Fri, Sep 25, 2026 at 08:41:38AM +0000, Bertrand Drouvot wrote:
Just a few comments:
=== 1
It needs a rebase due to 926627bf902
Done
=== 2
+ ereport(ERROR, + errmsg("too many check constraints on relation \"%s\"", + RelationGetQualifiedRelationName(rel)));I think ERRCODE_PROGRAM_LIMIT_EXCEEDED would be appropriate here?
Done.
numchecks++; + + if (numchecks >= PG_INT16_MAX) + ereport(ERROR, + errmsg("too many check constraints on relation \"%s\"", + RelationGetQualifiedRelationName(rel)));I wonder if it wouldn't make more sense to check numchecks >= PG_INT16_MAX before
calling StoreRelCheck()? That would avoid inserting the constraint, recording its
dependencies and invoking the post create hook for an object that will be rejected.Yeah, let's do that. That's unlikely but it would just be a waste and
that's just switching the order of things.
Also done. Thanks for the fast replies.
Attached v3:
* Add errcode(PROGRAM_LIMIT_EXCEEDED) to the ereports.
* Use pg_add_s16_overflow() to detect the overflows.
This includes changing the type of local numchecks variables to
int16. SetRelationNumChecks's signature is unchanged.
* Move the overflow checks to before StoreRelCheck.
This saves one dirty tuple in catalog tables when that overflow happens.
Kind regards,
Matthias van de Meent
Databricks (https://www.databricks.com)
On Fri, Sep 25, 2026 at 02:33:57PM +0200, Matthias van de Meent wrote:
Also done. Thanks for the fast replies.
Attached v3:
* Add errcode(PROGRAM_LIMIT_EXCEEDED) to the ereports.
* Use pg_add_s16_overflow() to detect the overflows.
This includes changing the type of local numchecks variables to
int16. SetRelationNumChecks's signature is unchanged.
* Move the overflow checks to before StoreRelCheck.
This saves one dirty tuple in catalog tables when that overflow happens.
That looks pretty good here, so applied and backpatched as the
consequences of keeping an overflowed counter can be really annoying.
--
Michael