foreign_key test is sensitive to the OID counter
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:t253630psql -h localhost -U postgresBuilt from patchset v4 (message #4), September 02, 2026 at 01:07 AM.
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 t253630_4 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 t253630_4 && git checkout t253630_4Patchset v4 (message #4) is on t253630_4
While running installcheck against a long-lived cluster, I got a diff
in the foreign_key test:
@@ -2045,11 +2045,11 @@
ORDER BY oid::regclass::text;
conname | conenforced | convalidated
------------+-------------+--------------
+ selffk_3 | t | f
+ selffk_3_1 | t | f
selffk | t | f
selffk_1 | t | f
selffk_2 | t | f
- selffk_3 | t | f
- selffk_3_1 | t | f
(5 rows)At first I thought the planner patch I was working on had caused an
unexpected plan diff, and wasted some time chasing that. But it
turned out to be the "ORDER BY oid::regclass::text" in these queries.
These OIDs are constraint OIDs, not relation OIDs, so the regclass
cast just renders them as numeric strings, and comparing those as text
depends on the number of digits. Once the OID counter crosses a power
of ten while the test runs, '1000006' sorts before '999987' and the
output order flips.
I plan to push the attached patch to fix it. Any thoughts?
- Richard
On Tue, Sep 1, 2026 at 4:32 PM Richard Guo <guofenglinux@gmail.com> wrote:
I plan to push the attached patch to fix it. Any thoughts?
SELECT conname, convalidated, conrelid::regclass FROM pg_constraint
-WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY
oid::regclass::text;
+WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY oid;Perhaps what they wanted at the time was
WHERE conrelid::regclass::text like 'fk_partitioned_fk%' ORDER BY
conrelid::regclass::text;
in https://git.postgresql.org/cgit/postgresql.git/commit/?id=6beb38cfc9ddd4cd3d2eb5402981ebdd69a618b4
we use ``::regclass::text COLLATE "C"`` is stable the tests.
IMHO, Using
ORDER BY conrelid::regclass::text COLLATE "C"
is more future-proof.
Richard Guo <guofenglinux@gmail.com> writes:
These OIDs are constraint OIDs, not relation OIDs, so the regclass
cast just renders them as numeric strings, and comparing those as text
depends on the number of digits. Once the OID counter crosses a power
of ten while the test runs, '1000006' sorts before '999987' and the
output order flips.
Right, that's bogus.
I plan to push the attached patch to fix it. Any thoughts?
This is only somewhat less bogus, though: it'd still fail at OID
wraparound. How about sorting by the constraint name?
regards, tom lane
On Tue, Sep 1, 2026 at 11:48 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
This is only somewhat less bogus, though: it'd still fail at OID
wraparound. How about sorting by the constraint name?
Good point. I didn't take OID wraparound into consideration. I think
sorting by conname works, as the connames are unique within each of
these queries.
I noticed that several other test files use COLLATE "C" when sorting
by names, so that the order is independent of the database's default
collation. So I end up using ORDER BY conname COLLATE "C" in v2.
- Richard
On Tue, Sep 1, 2026 at 6:05 PM jian he <jian.universality@gmail.com> wrote:
IMHO, Using
ORDER BY conrelid::regclass::text COLLATE "C"
is more future-proof.
Hm, I don't think so. In most of the queries the WHERE clause is
literally conrelid = 'something'::regclass, and sorting by a constant
is no sort at all. In the other queries the conrelid is not even
unique in the output.
- Richard
Richard Guo <guofenglinux@gmail.com> writes:
I noticed that several other test files use COLLATE "C" when sorting
by names, so that the order is independent of the database's default
collation. So I end up using ORDER BY conname COLLATE "C" in v2.
That looks good to me.
regards, tom lane
On Wed, Sep 2, 2026 at 10:22 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Richard Guo <guofenglinux@gmail.com> writes:
I noticed that several other test files use COLLATE "C" when sorting
by names, so that the order is independent of the database's default
collation. So I end up using ORDER BY conname COLLATE "C" in v2.
That looks good to me.
Thanks! Pushed.
- Richard