Re: Minimal logical decoding on standbys

Hi,

On 1/26/23 9:13 PM, Andres Freund wrote:
> Hi,
>
> On 2023-01-26 18:56:10 +0100, Drouvot, Bertrand wrote:
>> - I'm struggling to create a test for btree killtuples as there is a need for rows removal on the table (that could produce a conflict too):
>> Do you've a scenario in mind for this one? (and btw in what kind of WAL record should the conflict be detected in such a case? xl_btree_delete?)
>
> Hm, it might indeed be hard in "modern" postgres. I think you'd need at least
> two concurrent sessions, to prevent on-access pruning on the table.
>
>
> DROP TABLE IF EXISTS indexdel;
> CREATE TABLE indexdel(id int8 primary key);
> INSERT INTO indexdel SELECT generate_series(1, 10000);
> VACUUM indexdel; -- ensure hint bits are set etc
>
> DELETE FROM indexdel;
>
> SELECT pg_current_wal_insert_lsn();
>
> SET enable_indexonlyscan = false;
> -- This scan finds that the index items are dead - but doesn't yet issue a
> -- btree delete WAL record, that only happens when needing space on the page
> -- again.
> EXPLAIN (COSTS OFF, SUMMARY OFF) SELECT id FROM indexdel WHERE id < 10 ORDER BY id ASC;
> SELECT id FROM indexdel WHERE id < 100 ORDER BY id ASC;
>
> -- The insertions into the range of values prev
> INSERT INTO indexdel SELECT generate_series(1, 100);
>
>
> Does generate the btree deletion record, but it also does emit a PRUNE (from
> heapam_index_fetch_tuple() -> heap_page_prune_opt()).
>
> While the session could create a cursor to prevent later HOT cleanup, the
> query would also trigger hot pruning (or prevent the rows from being dead, if
> you declare the cursor before the DELETE). So you'd need overlapping cursors
> in a concurrent session...
>

Thanks for the scenario and explanation!

I agree that a second session would be needed (and so I understand why I was
struggling when trying with a single session ;-) )

> Too complicated.
>

Yeah.

Regards,

--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com