Message-ID: <85546caa-0402-382f-4f9d-9f0ff4af6d58@gmail.com>
Date: Fri, 27 Jan 2023 08:49:15 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0)
 Gecko/20100101 Thunderbird/102.6.1
Subject: Re: Minimal logical decoding on standbys
Content-Language: en-US
To: Andres Freund <andres@anarazel.de>
Cc: Robert Haas <robertmhaas@gmail.com>, Thomas Munro
 <thomas.munro@gmail.com>, Alvaro Herrera <alvherre@2ndquadrant.com>,
 Ibrar Ahmed <ibrar.ahmad@gmail.com>, Amit Khandekar
 <amitdkhan.pg@gmail.com>, fabriziomello@gmail.com,
 tushar <tushar.ahuja@enterprisedb.com>,
 Rahila Syed <rahila.syed@2ndquadrant.com>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
References: <05c0c4f3-82eb-bc6d-c876-5a265c05927b@gmail.com>
 <CA+TgmoY8LAaTwAhS+YNQNrM+3K8eqxGvPT3gbJaHER86XShvFw@mail.gmail.com>
 <20230106034036.2m4qnn7ep7b5ipet@awork3.anarazel.de>
 <490206c5-bcde-e558-628a-dc67dae4d439@gmail.com>
 <20230119024617.jzsgpcrgjdc6ysge@awork3.anarazel.de>
 <a343b646-c632-4b9a-0e01-b661075d1d8b@gmail.com>
 <20230124004612.xmryok6tcyk6i4pc@awork3.anarazel.de>
 <ccac1780-797d-72d8-72a0-e9d32abe3d5b@gmail.com>
 <9dfd49e6-6675-526e-0636-0ba09396744e@gmail.com>
 <edde4a5c-4b64-8245-d53a-58c6f223377c@gmail.com>
 <20230126201349.pjdjdbxy4j4dn2vc@awork3.anarazel.de>
From: "Drouvot, Bertrand" <bertranddrouvot.pg@gmail.com>
In-Reply-To: <20230126201349.pjdjdbxy4j4dn2vc@awork3.anarazel.de>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/85546caa-0402-382f-4f9d-9f0ff4af6d58%40gmail.com>
Precedence: bulk

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