#!/usr/bin/env python3 """Run it against a master server and a patched server (any build), then diff the two output files; they must be identical. Use autovacuum = off on both: autovacuum can change the physical order in which CloneFkReferenced() visits constraints, which changes names on master itself. """ """Name-equivalence oracle for foreign keys that reference a partitioned table. For every case it builds a fresh schema on a running server, creates the foreign key through one of several paths, and prints the sorted list (conrelid::regclass, confrelid::regclass, conname) of every constraint in that schema. Run it once against master and once against the patch; the outputs must be identical (diff empty). oracle.py run --port P --host H --bindir B --out FILE [--only-n LIST] [--sample K] oracle.py list # print the case matrix and its size Axes: n partitions of the referenced table: 1 2 9 10 11 99 100 101 1000 depth 1 (leaves under p) or 2 (p -> groups of up to 10 -> leaves) name short (default name f_a_fkey), len61, len62, len63, len63_1 (63 bytes ending in "_1") occ names already taken in the schema: none, s1 (_1), s3 (hole at _3), blk (_1.._10) fks 1, or 2 foreign keys from the same table with the same columns path addfk (ALTER TABLE ADD FOREIGN KEY), inline (CREATE TABLE ... REFERENCES), attach (ATTACH PARTITION after the FK exists), partof (CREATE TABLE ... PARTITION OF after the FK exists), restore (pg_dump of the schema, drop, restore with psql), detach (the referencing table is a partition of fp and is detached after the FK exists: the detached table gets its own referenced-side rows, one per partition of p) Exhaustive for n <= 101; n = 1000 runs a deterministic sample (--sample, default 40). """ import argparse, itertools, os, random, subprocess, sys NS = [1, 2, 9, 10, 11, 99, 100, 101, 1000] DEPTHS = [1, 2] NAMES = ['short', 'len61', 'len62', 'len63', 'len63_1'] OCCS = ['none', 's1', 's3', 'blk'] FKS = [1, 2] PATHS = ['addfk', 'inline', 'attach', 'partof', 'restore', 'detach'] NAMEDATALEN = 64 def make_object_name(name1, label): """makeObjectName(name1, NULL, label) for ASCII names (src/backend/commands/indexcmds.c).""" avail = NAMEDATALEN - 1 - (len(label) + 1) return name1[:avail] + '_' + label def base_name(kind): if kind == 'short': return None # let the server choose f_a_fkey if kind == 'len63_1': return ('k' * 61 + '_1') return 'n' * int(kind[3:]) def cases(): return list(itertools.product(NS, DEPTHS, NAMES, OCCS, FKS, PATHS)) def select(all_cases, sample, only_n, only_path=None): small = [c for c in all_cases if c[0] <= 101] big = [c for c in all_cases if c[0] > 101] rnd = random.Random(20261116) big = sorted(rnd.sample(big, min(sample, len(big)))) sel = small + big if only_n: sel = [c for c in sel if c[0] in only_n] if only_path: sel = [c for c in sel if c[5] in only_path] return sel def ident(c): return 'c_%d_d%d_%s_%s_f%d_%s' % c def partition_ddl(sch, n, depth, skip_last=False): """DDL for p and its partitions. If skip_last, the last leaf (depth 1) or the last group (depth 2) is left out and returned as (standalone create, attach, create-as-partition-of).""" st = [f'CREATE TABLE {sch}.p (id int PRIMARY KEY) PARTITION BY RANGE (id)'] later = None if depth == 1: for i in range(n): q = f'CREATE TABLE {sch}.p{i} PARTITION OF {sch}.p FOR VALUES FROM ({i}) TO ({i + 1})' if skip_last and i == n - 1: later = (f'CREATE TABLE {sch}.p{i} (id int PRIMARY KEY)', f'ALTER TABLE {sch}.p ATTACH PARTITION {sch}.p{i} FOR VALUES FROM ({i}) TO ({i + 1})', q) else: st.append(q) else: groups = [list(range(g, min(g + 10, n))) for g in range(0, n, 10)] for gi, leaves in enumerate(groups): lo, hi = leaves[0], leaves[-1] + 1 g = [f'CREATE TABLE {sch}.g{gi} PARTITION OF {sch}.p FOR VALUES FROM ({lo}) TO ({hi}) ' f'PARTITION BY RANGE (id)'] g += [f'CREATE TABLE {sch}.p{i} PARTITION OF {sch}.g{gi} FOR VALUES FROM ({i}) TO ({i + 1})' for i in leaves] if skip_last and gi == len(groups) - 1: standalone = [f'CREATE TABLE {sch}.g{gi} (id int PRIMARY KEY) PARTITION BY RANGE (id)'] standalone += [f'CREATE TABLE {sch}.p{i} PARTITION OF {sch}.g{gi} ' f'FOR VALUES FROM ({i}) TO ({i + 1})' for i in leaves] later = (';\n'.join(standalone), f'ALTER TABLE {sch}.p ATTACH PARTITION {sch}.g{gi} FOR VALUES FROM ({lo}) TO ({hi})', ';\n'.join(g)) else: st += g return st, later def occupy(sch, base, occ): if occ == 'none': return [] ks = {'s1': [1], 's3': [3], 'blk': list(range(1, 11))}[occ] st = [f'CREATE TABLE {sch}.occ (x int)'] for k in ks: st.append(f'ALTER TABLE {sch}.occ ADD CONSTRAINT "{make_object_name(base, str(k))}" CHECK (x > {k})') return st def fk_clause(nm, j, sch): b = base_name(nm) if b is None: return f'FOREIGN KEY (a) REFERENCES {sch}.p' # the second FK gets its own explicit name: same base minus the last byte, plus "x" name = b if j == 0 else b[:-1] + 'x' return f'CONSTRAINT "{name}" FOREIGN KEY (a) REFERENCES {sch}.p' def case_sql(c): n, depth, nm, occ, fks, path = c sch = ident(c) base = base_name(nm) or ('fp_a_fkey' if path == 'detach' else 'f_a_fkey') st = [f'DROP SCHEMA IF EXISTS {sch} CASCADE', f'CREATE SCHEMA {sch}'] st += occupy(sch, base, occ) pst, later = partition_ddl(sch, n, depth, skip_last=path in ('attach', 'partof')) st += pst fkcl = [fk_clause(nm, j, sch) for j in range(fks)] if path == 'detach': st.append(f'CREATE TABLE {sch}.fp (a int) PARTITION BY LIST (a)') st.append(f'CREATE TABLE {sch}.f PARTITION OF {sch}.fp DEFAULT') st += [f'ALTER TABLE {sch}.fp ADD {x}' for x in fkcl] st.append(f'ALTER TABLE {sch}.fp DETACH PARTITION {sch}.f') return sch, st if path == 'inline': st.append(f'CREATE TABLE {sch}.f (a int, {", ".join(fkcl)})') else: st.append(f'CREATE TABLE {sch}.f (a int)') st += [f'ALTER TABLE {sch}.f ADD {x}' for x in fkcl] if path == 'attach': st += [later[0], later[1]] elif path == 'partof': st += [later[2]] return sch, st def listing(sch): return (f"SELECT conrelid::regclass::text || ' ' || confrelid::regclass::text || ' ' || conname " f"FROM pg_constraint WHERE connamespace = '{sch}'::regnamespace ORDER BY 1") def psql(a, sql): r = subprocess.run([os.path.join(a.bindir, 'psql'), '-X', '-q', '-At', '-v', 'ON_ERROR_STOP=1', '-h', a.host, '-p', str(a.port), '-d', a.db], input=sql, capture_output=True, text=True) if r.returncode: sys.exit(f'psql failed: {r.stderr[:500]}') return r.stdout def run(a): sel = select(cases(), a.sample, a.only_n, a.only_path) with open(a.out, 'w') as f: for i, c in enumerate(sel): sch, st = case_sql(c) psql(a, ';\n'.join(st) + ';\n') if c[5] == 'restore': dump = subprocess.run([os.path.join(a.bindir, 'pg_dump'), '-h', a.host, '-p', str(a.port), '-n', sch, a.db], capture_output=True, text=True, check=True).stdout psql(a, f'DROP SCHEMA {sch} CASCADE;\n') psql(a, dump) rows = psql(a, listing(sch) + ';\n') f.write(f'== {sch}\n{rows}') psql(a, f'DROP SCHEMA {sch} CASCADE;\n') if i % 50 == 0: print(f'{i + 1}/{len(sel)} {sch}', flush=True) with open(a.out, 'a') as f: f.write(f'== done: {len(sel)} cases\n') print(f'done: {len(sel)} cases -> {a.out}') def main(): ap = argparse.ArgumentParser() sp = ap.add_subparsers(dest='cmd', required=True) r = sp.add_parser('run') r.add_argument('--host', required=True) r.add_argument('--port', type=int, required=True) r.add_argument('--bindir', required=True) r.add_argument('--db', default='postgres') r.add_argument('--out', required=True) r.add_argument('--sample', type=int, default=40) r.add_argument('--only-n', type=lambda s: [int(x) for x in s.split(',')]) r.add_argument('--only-path', type=lambda s: s.split(',')) sp.add_parser('list') a = ap.parse_args() if a.cmd == 'list': sel = select(cases(), 40, None) print(f'full product: {len(cases())}; selected: {len(sel)} ' f'({sum(1 for c in sel if c[0] <= 101)} exhaustive n<=101, ' f'{sum(1 for c in sel if c[0] > 101)} sampled n=1000)') else: run(a) if __name__ == '__main__': main()