Hi,

On a subscriber, every dropped relation scans all of
pg_subscription_rel: RemoveSubscriptionRel() uses a heap scan although
the (srrelid, srsubid) primary key serves the lookup by relid.  A table
with a text column and an identity key pays it three times (the table,
its TOAST table and its sequence).  The attached patch uses the index
when relid is given; DROP SUBSCRIPTION keeps the heap scan.

With 100,000 rows in pg_subscription_rel, dropping a schema of 1,000
such tables takes 2.8 s on master and 0.23 s patched, and a transaction
with an ON COMMIT DROP temporary table runs 45M instructions instead of
2.5M.  With an empty catalog the index costs 0.6% more instructions.
If several subscriptions have the table in a non-ready state, the error
now names the lowest OID rather than the first one in heap order.  The
setup is synthetic; measurements.txt and the script are attached.

Manu
From c1f46d8cc37cd39e125a3cd1dab10b4023bda913 Mon Sep 17 00:00:00 2001
From: Manuel Reyes Bravo <[email protected]>
Date: Mon, 16 Nov 2026 19:41:27 -0300
Subject: [PATCH v1] Use an index scan in RemoveSubscriptionRel()

heap_drop_with_catalog() calls RemoveSubscriptionRel() for every
relation it drops, including TOAST tables and sequences, and the
function scanned all of pg_subscription_rel each time, even though the
primary key index on (srrelid, srsubid) serves a lookup by relid.  On a
subscriber with many rows in pg_subscription_rel, every DROP TABLE and
every ON COMMIT DROP temporary table paid for that scan.  With 100,000
rows, dropping 1,000 tables with a text column and an identity primary
key took 66 billion instructions instead of 2.3 billion.

Use the index whenever relid is given.  DROP SUBSCRIPTION passes only a
subscription OID, which this index cannot serve, so it keeps scanning
the heap.  When several subscriptions have the relation in a state
other than ready, the error now names the one with the lowest OID
instead of the first one found in the heap.

Discussion:
---
 src/backend/catalog/pg_subscription.c | 28 ++++++++++++++++-----------
 1 file changed, 17 insertions(+), 11 deletions(-)

diff --git a/src/backend/catalog/pg_subscription.c b/src/backend/catalog/pg_subscription.c
index 60c15b2..50005ce 100644
--- a/src/backend/catalog/pg_subscription.c
+++ b/src/backend/catalog/pg_subscription.c
@@ -520,34 +520,40 @@ void
 RemoveSubscriptionRel(Oid subid, Oid relid)
 {
 	Relation	rel;
-	TableScanDesc scan;
+	SysScanDesc scan;
 	ScanKeyData skey[2];
 	HeapTuple	tup;
 	int			nkeys = 0;
 
 	rel = table_open(SubscriptionRelRelationId, RowExclusiveLock);
 
-	if (OidIsValid(subid))
+	/* Put srrelid first: index scan keys must be in index column order. */
+	if (OidIsValid(relid))
 	{
 		ScanKeyInit(&skey[nkeys++],
-					Anum_pg_subscription_rel_srsubid,
+					Anum_pg_subscription_rel_srrelid,
 					BTEqualStrategyNumber,
 					F_OIDEQ,
-					ObjectIdGetDatum(subid));
+					ObjectIdGetDatum(relid));
 	}
 
-	if (OidIsValid(relid))
+	if (OidIsValid(subid))
 	{
 		ScanKeyInit(&skey[nkeys++],
-					Anum_pg_subscription_rel_srrelid,
+					Anum_pg_subscription_rel_srsubid,
 					BTEqualStrategyNumber,
 					F_OIDEQ,
-					ObjectIdGetDatum(relid));
+					ObjectIdGetDatum(subid));
 	}
 
-	/* Do the search and delete what we found. */
-	scan = table_beginscan_catalog(rel, nkeys, skey);
-	while (HeapTupleIsValid(tup = heap_getnext(scan, ForwardScanDirection)))
+	/*
+	 * Do the search and delete what we found.  Use the (srrelid, srsubid)
+	 * index when relid is given; otherwise (DROP SUBSCRIPTION) there is no
+	 * suitable index, so fall back to a heap scan.
+	 */
+	scan = systable_beginscan(rel, SubscriptionRelSrrelidSrsubidIndexId,
+							  OidIsValid(relid), NULL, nkeys, skey);
+	while (HeapTupleIsValid(tup = systable_getnext(scan)))
 	{
 		Form_pg_subscription_rel subrel;
 
@@ -584,7 +590,7 @@ RemoveSubscriptionRel(Oid subid, Oid relid)
 
 		CatalogTupleDelete(rel, &tup->t_self);
 	}
-	table_endscan(scan);
+	systable_endscan(scan);
 
 	table_close(rel, RowExclusiveLock);
 }
-- 
2.56.1

RemoveSubscriptionRel() by index: measurements
==============================================

Base: master 7fc2f0423320e02beb8c44b863f1827604c47c75, with and without
v1-0001.  Builds: -O2 -g, no assertions, same configure for both.
Host: Ryzen AI 9 HX 370 (Zen 5 cores 0-3,12-15 pinned), Linux 7.3.
Method: both builds run on byte-identical clones of the same cluster,
alternating per round (m p, p m, ...), 6 rounds.  Instructions are
instructions:u of the backend (perf stat -p) for the measured
statements only; ms is the client's wall clock.  "change" is the
median of patched against master with a bootstrap 95% CI.

Subscriber setup (script: subrel-measure.py.txt, op tpl): K rows in
pg_subscription_rel = K/1000 subscriptions (connect = false) x 1000
tables, state 'r', added with binary_upgrade_add_sub_rel_state() in a
single-user -b session, as pg_upgrade does.

Scans of pg_subscription_rel per DROP TABLE (op census, K=100000):
  table "text + identity PK": master 3 heap scans, patched 3 index scans
  (the table, its TOAST table, its identity sequence)
  table "int": master 1 heap scan, patched 1 index scan

1) DROP SCHEMA ... CASCADE of 1000 tables (id int GENERATED ALWAYS AS
   IDENTITY PRIMARY KEY, v text).  op drop.
2) One session runs 300 transactions of
     BEGIN; CREATE TEMP TABLE t (id int, v text) ON COMMIT DROP;
     INSERT INTO t SELECT g, 'x' FROM generate_series(1, 10) g; COMMIT;
   reported per transaction.  op tempi.  The ms of this run are
   dominated by the commit's WAL flush and are noisy (the run ended
   with the noise sentinel above its threshold); the instruction
   counts do not depend on that.

op          size metric            master     patch         change [95% CI] n
drop           0 instr (M)          2,297     2,310    +0.6%   [+0.5..+0.6] 6/6
drop           0 ms                   222       231    +4.0%  [-3.0..+12.8] 6/6
drop        1000 instr (M)          2,942     2,317   -21.2% [-21.3..-21.2] 6/6
drop        1000 ms                   259       241    -7.2%  [-11.3..+1.0] 6/6
drop       10000 instr (M)          8,706     2,319   -73.4% [-73.4..-73.4] 6/6
drop       10000 ms                   487       222   -54.3% [-56.1..-49.7] 6/6
drop      100000 instr (M)         66,330     2,320   -96.5% [-96.5..-96.5] 6/6
drop      100000 ms                 2,766       230   -91.7% [-91.9..-91.3] 6/6
tempi       1000 instr/tx (M)       2.961     2.545   -14.0% [-14.0..-14.0] 6/6
tempi       1000 ms/tx              8.401     6.819   -18.8%  [-33.8..+8.0] 6/6
tempi      10000 instr/tx (M)       6.804     2.546   -62.6% [-62.6..-62.6] 6/6
tempi      10000 ms/tx              7.753     7.873    +1.6% [-36.1..+36.9] 6/6
tempi     100000 instr/tx (M)        45.2     2.547   -94.4% [-94.4..-94.4] 6/6
tempi     100000 ms/tx               10.5     8.470   -19.5%  [-44.7..+9.7] 6/6

Reading: the patched cost is flat in K.  With K = 0 the index scan costs
about 13M instructions more per 3000 lookups (~4,400 per dropped
relation); master's heap scan is cheaper only while pg_subscription_rel
is (nearly) empty.  The ms difference at K = 0 is within noise.

Not measured: ALTER SUBSCRIPTION ... REFRESH PUBLICATION timing (it goes
through the same function with both keys; correctness covered by the
existing subscription TAP tests, which fail if the keys are put in the
wrong order); other platforms.
#!/usr/bin/env python3
"""Requires production builds (configure without --enable-cassert): instruction 
counts
are not meaningful with assertions.  Needs perf (instructions:u), pgbench, and 
the
builds' bin directories.  Example, 6 rounds alternating master (m) and patched 
(p):
  python3 subrel-measure.py.txt tpl 100000 m=/path/master/bin
  python3 subrel-measure.py.txt drop 100000 6 m=/path/master/bin 
p=/path/patched/bin
"""
"""Real-operation measurements for RemoveSubscriptionRel() on a subscriber with 
a
large pg_subscription_rel.  Production builds only (no cassert): instruction
counts are meaningless with assertions.

Builds are install prefixes given as NAME=BINDIR (e.g. m=/x/i-m/bin 
p=/x/i-p/bin).
Every measurement alternates the builds by round (m p, p m, ...) on clones of 
the
same catalog template, so both builds see byte-identical catalogs.

  b1.py tpl K BUILD=BINDIR            template: K rows in pg_subscription_rel
                                      (K/1000 subscriptions x 1000 tables, or 
one
                                      subscription x K tables if K < 1000; 
state r,
                                      added with 
binary_upgrade_add_sub_rel_state in -b)
  b1.py census K B=BIN ...            pg_subscription_rel scans per DROP of one 
typical
                                      table (text + identity PK) and of one 
plain table
  b1.py drop K ROUNDS B=BIN ...       DROP SCHEMA ... CASCADE of 1000 typical 
tables:
                                      wall clock (psql \\timing) + 
instructions:u of the backend
  b1.py temp K ROUNDS SECS B=BIN ...  pgbench, 1 client, BEGIN; CREATE TEMP 
TABLE ...
                                      ON COMMIT DROP; INSERT; COMMIT -> tps
  b1.py equiv K B=BIN ...             same rows removed by every build: DROP 
TABLE (0, relid),
                                      sequence in state i, table in state d 
(must ERROR),
                                      DROP SUBSCRIPTION (subid, 0); with and 
without
                                      ignore_system_indexes; prints the 
surviving rows' hash
  b1.py tempi K ROUNDS NTX B=BIN ...  one session runs NTX transactions of the 
same temp table
                                      pattern: instructions:u and ms per 
transaction
  b1.py refresh K ROUNDS B=BIN ...    ALTER SUBSCRIPTION ... REFRESH 
PUBLICATION that
                                      removes 500 relations (local publisher)
Output: one TSV line per measurement on stdout:  op build K round metric value
B1_ROUND0=R starts at round R+1 (keeps the m/p alternation across split runs).
Work dir: $B1_WORK (default ./subrel-work).
"""
import os, re, shutil, signal, subprocess, sys, time

W = os.environ.get('B1_WORK', os.path.abspath('subrel-work'))
SOCK = os.path.join(W, 'sock')
PORT = 55441
SUB = ("CREATE SUBSCRIPTION {} CONNECTION 'dbname=none' PUBLICATION p "
       "WITH (connect = false, slot_name = NONE, enabled = false, create_slot = 
false)")


def sh(cmd, **kw):
    return subprocess.run(cmd, check=True, text=True, capture_output=True, 
**kw).stdout


def builds(args):
    return [tuple(a.split('=', 1)) for a in args]


def start(bindir, d, extra=()):
    os.makedirs(SOCK, exist_ok=True)
    opts = f"-p {PORT} -k {SOCK} -c listen_addresses= " + ' '.join(extra)
    sh([f'{bindir}/pg_ctl', '-D', d, '-o', opts, '-l', d + '.log', '-w', 
'start'])


def stop(bindir, d):
    sh([f'{bindir}/pg_ctl', '-D', d, '-m', 'fast', '-w', 'stop'])


def psql(bindir, sql, db='postgres'):
    r = subprocess.run([f'{bindir}/psql', '-X', '-q', '-At', '-v', 
'ON_ERROR_STOP=1', '-h', SOCK,
                        '-p', str(PORT), '-d', db], input=sql, text=True, 
capture_output=True)
    if r.returncode:
        sys.exit(f'psql: {r.stderr[:800]}')
    return r.stdout


def single(bindir, d, sql, binary_upgrade=False):
    cmd = [f'{bindir}/postgres', '--single', '-j', '-D', d, 'postgres']
    if binary_upgrade:
        cmd.insert(2, '-b')
    r = subprocess.run(cmd, input=sql, text=True, capture_output=True)
    if 'ERROR' in r.stdout + r.stderr or r.returncode:
        sys.exit(f'single-user: {(r.stdout + r.stderr)[:800]}')
    return r.stdout


def tpl_dir(k):
    return os.path.join(W, f'tpl-{k}')


def tpl(k, bindir):
    d = tpl_dir(k)
    shutil.rmtree(d, ignore_errors=True)
    os.makedirs(W, exist_ok=True)
    sh([f'{bindir}/initdb', '-D', d, '-A', 'trust', '--no-sync'])
    with open(os.path.join(d, 'postgresql.conf'), 'a') as f:
        f.write('wal_level = logical\nmax_wal_size = 4GB\n')
    start(bindir, d)
    nsub = -(-k // 1000)                     # K < 1000: one subscription with 
K tables
    ntab = min(k, 1000) if k else 1000
    psql(bindir, ''.join(f'CREATE TABLE ks_t{i} (id int);\n' for i in range(1, 
1001)) +
         ''.join(SUB.format(f'ks_s{j}') + ';\n' for j in range(1, nsub + 1)))
    stop(bindir, d)
    if nsub:
        single(bindir, d, ''.join(
            f"SELECT count(binary_upgrade_add_sub_rel_state('ks_s{j}', c.oid, 
'r', NULL)) "
            f"FROM pg_class c WHERE c.relname ~ '^ks_t[0-9]+$' "
            f"AND substr(c.relname, 5)::int <= {ntab};\n" for j in range(1, 
nsub + 1)),
            binary_upgrade=True)
    start(bindir, d)
    n = psql(bindir, 'VACUUM; CHECKPOINT; SELECT count(*) FROM 
pg_subscription_rel;').split()[-1]
    stop(bindir, d)
    print(f'tpl\t-\t{k}\t-\tsubrel_rows\t{n}', flush=True)


def clone(k, tag):
    d = os.path.join(W, f'cell-{tag}')
    if os.path.exists(os.path.join(d, 'postmaster.pid')):    # left running by 
an aborted run
        
subprocess.run([os.path.join(os.path.dirname(os.path.abspath(sys.argv[-1].split('=',
 1)[1])),
                                     'pg_ctl'), '-D', d, '-m', 'immediate', 
'-w', 'stop'],
                       capture_output=True)
    shutil.rmtree(d, ignore_errors=True)
    sh(['cp', '-a', '--reflink=auto', tpl_dir(k), d])
    return d


def order(bs, r):
    return bs if r % 2 == 0 else list(reversed(bs))


TYPICAL = 'CREATE TABLE {}.t{} (id int GENERATED ALWAYS AS IDENTITY PRIMARY 
KEY, v text)'


def census(k, bs):
    for name, bindir in bs:
        d = clone(k, name)
        start(bindir, d)
        psql(bindir, 'CREATE SCHEMA c; ' + TYPICAL.format('c', 1) + '; CREATE 
TABLE c.plain (id int);')
        for what in ('c.t1', 'c.plain'):
            out = psql(bindir, f"BEGIN; DROP TABLE {what}; SELECT 
coalesce(seq_scan, 0) || ' ' || "
                               f"coalesce(idx_scan, 0) FROM 
pg_stat_xact_sys_tables "
                               f"WHERE relname = 'pg_subscription_rel'; 
COMMIT;")
            seq, idx = out.split()[-2:]
            print(f'census\t{name}\t{k}\t-\t{what}:seq_scan\t{seq}', flush=True)
            print(f'census\t{name}\t{k}\t-\t{what}:idx_scan\t{idx}', flush=True)
        stop(bindir, d)


class Session:
    """An interactive psql whose backend can be counted with perf stat -p."""

    def __init__(self, bindir):
        self.p = subprocess.Popen([f'{bindir}/psql', '-X', '-q', '-At', '-v', 
'ON_ERROR_STOP=1', '-h', SOCK,
                                   '-p', str(PORT), '-d', 'postgres'], 
stdin=subprocess.PIPE,
                                  stdout=subprocess.PIPE, 
stderr=subprocess.STDOUT, text=True, bufsize=1)
        self.pid = int(self.run('SELECT pg_backend_pid();')[-1])

    def run(self, sql):
        self.p.stdin.write(sql + '\n\\echo __done__\n')
        self.p.stdin.flush()
        out = []
        for line in self.p.stdout:
            line = line.rstrip('\n')
            if line == '__done__':
                return out
            if 'ERROR' in line:
                sys.exit(f'session: {line}')
            out.append(line)
        sys.exit('session: psql exited')

    def close(self):
        self.p.stdin.close()
        self.p.wait()


def counted(bindir, sql):
    """Run sql in a fresh session; return (ms, instructions:u of its 
backend)."""
    s = Session(bindir)
    perf = subprocess.Popen(['perf', 'stat', '-x,', '-e', 'instructions:u', 
'-p', str(s.pid)],
                            stderr=subprocess.PIPE, text=True)
    time.sleep(0.5)
    out = s.run('\\timing on\n' + sql + '\n\\timing off')
    perf.send_signal(signal.SIGINT)
    err = perf.communicate()[1]
    s.close()
    ms = sum(float(m) for m in re.findall(r'^Time: ([0-9.]+) ms', 
'\n'.join(out), re.M))
    ins = [int(l.split(',')[0]) for l in err.splitlines() if 'instructions' in 
l and l[0].isdigit()]
    if not ins:
        sys.exit(f'perf: {err[:400]}')
    return ms, ins[0]


def drop(k, rounds, bs):
    r0 = int(os.environ.get('B1_ROUND0', '0'))      # first round index (to 
split long runs)
    for r in range(r0, r0 + rounds):
        for name, bindir in order(bs, r):
            d = clone(k, name)
            start(bindir, d)
            psql(bindir, 'CREATE SCHEMA d;\n' + ''.join(TYPICAL.format('d', i) 
+ ';\n' for i in range(1, 1001)))
            psql(bindir, 'VACUUM pg_class, pg_attribute, pg_depend, pg_type, 
pg_constraint, '
                         'pg_index, pg_sequence, pg_attrdef, 
pg_subscription_rel; CHECKPOINT;')
            ms, ins = counted(bindir, 'DROP SCHEMA d CASCADE;')
            n = psql(bindir, "SELECT count(*) FROM pg_namespace WHERE nspname = 
'd';").split()[-1]
            if n != '0':
                sys.exit('drop: schema still there')
            print(f'drop\t{name}\t{k}\t{r + 1}\tms\t{ms:.1f}', flush=True)
            print(f'drop\t{name}\t{k}\t{r + 1}\tinstructions_M\t{ins / 
1e6:.1f}', flush=True)
            stop(bindir, d)


DUMP = ("SELECT md5(string_agg(s.subname || ' ' || r.srrelid::regclass::text || 
' ' || r.srsubstate::text, "
        "E'\\n' ORDER BY 1)) || ' ' || count(*) FROM pg_subscription_rel r "
        "JOIN pg_subscription s ON s.oid = r.srsubid;")


def equiv(k, bs):
    for name, bindir in bs:
        for isi in ('off', 'on'):
            d = clone(k, f'{name}-eq')
            start(bindir, d)
            psql(bindir, ''.join(f'CREATE TABLE e{i} (id int GENERATED ALWAYS 
AS IDENTITY PRIMARY KEY, '
                                 f'v text);\n' for i in range(1, 7)) +
                 'CREATE SEQUENCE eseq;\n' + SUB.format('s_a') + ';\n' + 
SUB.format('s_b') + ';\n')
            stop(bindir, d)
            add = ("SELECT binary_upgrade_add_sub_rel_state('{}', 
'{}'::regclass, '{}', NULL);\n")
            sql = ''.join(add.format(sb, f'e{i}', 'r') for sb in ('s_a', 's_b') 
for i in range(1, 6))
            sql += add.format('s_a', 'eseq', 'i') + add.format('s_b', 'e6', 'd')
            single(bindir, d, sql, binary_upgrade=True)
            start(bindir, d, ['-c', f'ignore_system_indexes={isi}'])
            before = psql(bindir, DUMP).strip()
            psql(bindir, 'DROP TABLE e1; DROP TABLE e2, e3; DROP SEQUENCE 
eseq;')
            r = subprocess.run([f'{bindir}/psql', '-X', '-q', '-At', '-h', 
SOCK, '-p', str(PORT),
                                '-d', 'postgres', '-c', 'DROP TABLE e6'], 
text=True, capture_output=True)
            err = r.stderr.strip().replace('\n', ' | ')
            mid = psql(bindir, DUMP).strip()
            psql(bindir, 'DROP SUBSCRIPTION s_b;')
            after = psql(bindir, DUMP).strip()
            print(f'equiv\t{name}\t{k}\tisi={isi}\tbefore\t{before}', 
flush=True)
            print(f'equiv\t{name}\t{k}\tisi={isi}\tdrop_e6\t{err}', flush=True)
            print(f'equiv\t{name}\t{k}\tisi={isi}\tafter_drops\t{mid}', 
flush=True)
            print(f'equiv\t{name}\t{k}\tisi={isi}\tafter_dropsub\t{after}', 
flush=True)
            stop(bindir, d)


TEMP_SQL = """BEGIN;
CREATE TEMP TABLE t (id int, v text) ON COMMIT DROP;
INSERT INTO t SELECT g, 'x' FROM generate_series(1, 10) g;
COMMIT;
"""


def temp(k, rounds, secs, bs):
    f = os.path.join(W, 'temp.sql')
    with open(f, 'w') as fh:
        fh.write(TEMP_SQL)
    r0 = int(os.environ.get('B1_ROUND0', '0'))      # first round index (to 
split long runs)
    for r in range(r0, r0 + rounds):
        for name, bindir in order(bs, r):
            d = clone(k, name)
            start(bindir, d)
            sh([f'{bindir}/pgbench', '-n', '-h', SOCK, '-p', str(PORT), '-c', 
'1', '-T', '3', '-f', f,
                'postgres'])                                     # warm-up, not 
counted
            out = sh([f'{bindir}/pgbench', '-n', '-h', SOCK, '-p', str(PORT), 
'-c', '1', '-T', str(secs),
                      '-f', f, 'postgres'])
            tps = re.search(r'tps = ([0-9.]+)', out).group(1)
            failed = re.search(r'number of failed transactions: (\d+)', out)
            if failed and failed.group(1) != '0':
                sys.exit('temp: failed transactions')
            print(f'temp\t{name}\t{k}\t{r + 1}\ttps\t{tps}', flush=True)
            stop(bindir, d)


def tempi(k, rounds, ntx, bs):
    one = TEMP_SQL.replace('\n', ' ')
    for r in range(int(os.environ.get('B1_ROUND0', '0')), 
int(os.environ.get('B1_ROUND0', '0')) + rounds):
        for name, bindir in order(bs, r):
            d = clone(k, name)
            start(bindir, d)
            psql(bindir, one * 20)                               # warm-up, not 
counted
            ms, ins = counted(bindir, one * ntx)
            print(f'tempi\t{name}\t{k}\t{r + 1}\tms_per_tx\t{ms / ntx:.3f}', 
flush=True)
            print(f'tempi\t{name}\t{k}\t{r + 1}\tinstructions_M_per_tx\t{ins / 
1e6 / ntx:.3f}', flush=True)
            stop(bindir, d)


def refresh(k, rounds, bs, nrel=500):
    r0 = int(os.environ.get('B1_ROUND0', '0'))      # first round index (to 
split long runs)
    for r in range(r0, r0 + rounds):
        for name, bindir in order(bs, r):
            d = clone(k, name)
            start(bindir, d, ['-c', 'max_logical_replication_workers=4', '-c', 
'max_replication_slots=10'])
            psql(bindir, 'CREATE DATABASE pub;')
            tabs = ''.join(f'CREATE TABLE r{i} (id int PRIMARY KEY);\n' for i 
in range(1, nrel + 1))
            psql(bindir, tabs + 'CREATE PUBLICATION pr FOR ALL TABLES;\n'
                 "SELECT pg_create_logical_replication_slot('s_ref', 
'pgoutput');", db='pub')
            psql(bindir, tabs + f"CREATE SUBSCRIPTION s_ref CONNECTION 
'host={SOCK} port={PORT} dbname=pub' "
                 "PUBLICATION pr WITH (create_slot = false, slot_name = 
's_ref', copy_data = false);")
            n = psql(bindir, "SELECT count(*) FROM pg_subscription_rel r JOIN 
pg_subscription s "
                             "ON s.oid = r.srsubid WHERE s.subname = 
's_ref';").split()[-1]
            if n != str(nrel):
                sys.exit(f'refresh: {n} rows for s_ref')
            psql(bindir, 'DROP PUBLICATION pr; CREATE PUBLICATION pr;', 
db='pub')
            psql(bindir, 'VACUUM pg_subscription_rel; CHECKPOINT;')
            ms, ins = counted(bindir, 'ALTER SUBSCRIPTION s_ref REFRESH 
PUBLICATION WITH (copy_data = false);')
            n = psql(bindir, "SELECT count(*) FROM pg_subscription_rel r JOIN 
pg_subscription s "
                             "ON s.oid = r.srsubid WHERE s.subname = 
's_ref';").split()[-1]
            if n != '0':
                sys.exit(f'refresh: {n} rows left')
            print(f'refresh\t{name}\t{k}\t{r + 1}\tms\t{ms:.1f}', flush=True)
            print(f'refresh\t{name}\t{k}\t{r + 1}\tinstructions_M\t{ins / 
1e6:.1f}', flush=True)
            psql(bindir, 'ALTER SUBSCRIPTION s_ref DISABLE; ALTER SUBSCRIPTION 
s_ref SET (slot_name = NONE); '
                         'DROP SUBSCRIPTION s_ref;')
            stop(bindir, d)


def main():
    a = sys.argv[1:]
    if not a:
        sys.exit(__doc__)
    op, k = a[0], int(a[1])
    if op == 'tpl':
        tpl(k, a[2].split('=', 1)[1])
    elif op == 'census':
        census(k, builds(a[2:]))
    elif op == 'drop':
        drop(k, int(a[2]), builds(a[3:]))
    elif op == 'temp':
        temp(k, int(a[2]), int(a[3]), builds(a[4:]))
    elif op == 'equiv':
        equiv(k, builds(a[2:]))
    elif op == 'tempi':
        tempi(k, int(a[2]), int(a[3]), builds(a[4:]))
    elif op == 'refresh':
        refresh(k, int(a[2]), builds(a[3:]))
    else:
        sys.exit(__doc__)


if __name__ == '__main__':
    main()

Reply via email to