> On Nov 19, 2026, at 07:28, Masahiko Sawada <[email protected]> wrote:
> 
> On Mon, Nov 16, 2026 at 9:32 PM Manu <[email protected]> wrote:
>> 
>> 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.
> 
> Thank you for the patch. +1 for the idea. Here are some comments:
> 
> ---
> + /* Put srrelid first: index scan keys must be in index column order. */
> + if (OidIsValid(relid))
>  {
> 
> The reordering is harmless but it's not actually required.
> systable_beginscan() maps each scan key to its index column by
> attribute number, so the order in the array doesn't matter. I'm fine
> with keeping it for readability, but I think the comment should be
> reworded (or just dropped).

The reordering is required.

I created a TAP script, see the attachment. The test passes with the 
reordering. But if we revert the reordering, the test fails with this error:
```
[15:08:45.242](0.000s) # psql:<stdin>:1: ERROR:  btree index keys must be 
ordered by attribute
[15:08:45.253](0.011s) not ok 3 - refresh removes the subscription relation
[15:08:45.253](0.000s) 
[15:08:45.253](0.000s) #   Failed test 'refresh removes the subscription 
relation'
```

Then in _bt_preprocess_keys(), I found this check:
```
                        /* check input keys are correctly ordered */
                        if (i < numberOfKeys && inkey->sk_attno < attno)
                                elog(ERROR, "btree index keys must be ordered 
by attribute");
```

Then looking at systable_beginscan(), though it converts attribute numbers to 
index column numbers, it doesn't reorder the keys in the array. Without the 
reordering, the index column numbers are 2 and 1, which violates the above 
check in _bt_preprocess_keys().

But I still agree that the “Put srrelid first” comment is unnecessary.

> ---
> + /*
> + * 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);
> 
> The comments seem too detailed to me. The first half is clear from the
> OidIsValid(relid) check, but I think the fallback part is worth
> keeping. It's not obvious that DROP SUBSCRIPTION can't use this index
> (srsubid is the second column), so it has to fall back to a heap scan.
> 

+1

Other than the nitpicks about the code comments, the patch overall looks good 
to me.

BTW, I put the attached TAP script under src/test/subscription/t/.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/




use strict;
use warnings FATAL => 'all';
use PostgreSQL::Test::Cluster;
use PostgreSQL::Test::Utils;
use Test::More;

my $node_publisher = PostgreSQL::Test::Cluster->new('publisher');
$node_publisher->init(allows_streaming => 'logical');
$node_publisher->start;

my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
$node_subscriber->init;
$node_subscriber->start;

$node_publisher->safe_psql(
	'postgres', qq(
	CREATE TABLE tab (a int);
	CREATE PUBLICATION tap_pub FOR TABLE tab;
));
$node_subscriber->safe_psql('postgres', 'CREATE TABLE tab (a int)');

# With copy_data = false, the relation is ready immediately.
my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
$node_subscriber->safe_psql('postgres',
	"CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr' PUBLICATION tap_pub WITH (copy_data = false)"
);

$node_publisher->safe_psql('postgres',
	'ALTER PUBLICATION tap_pub DROP TABLE tab');

is( $node_subscriber->safe_psql(
		'postgres',
		q(SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel
		  WHERE srsubid = (SELECT oid FROM pg_subscription
		                   WHERE subname = 'tap_sub'))),
	'tab|r',
	'publication change leaves the subscription relation until refresh');

# Refresh calls RemoveSubscriptionRel() with both relation and subscription
# OIDs. We are testing the scan keys orders: (srrelid, srsubid) or 
# (srsubid, srrelid)?
my ($ret, $stdout, $stderr) = $node_subscriber->psql('postgres',
	'ALTER SUBSCRIPTION tap_sub REFRESH PUBLICATION WITH (copy_data = false)');
is($ret, 0, 'refresh publication succeeds with both scan keys')
  or diag($stderr);

is( $node_subscriber->safe_psql(
		'postgres', 'SELECT count(*) FROM pg_subscription_rel'),
	'0',
	'refresh removes the subscription relation');

$node_subscriber->safe_psql('postgres', 'DROP SUBSCRIPTION tap_sub');
$node_subscriber->stop('fast');
$node_publisher->stop('fast');

done_testing();

Reply via email to