foreign_key test is sensitive to the OID counter
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