WIP: long transactions on hot standby feedback replica / proof of concept

From: i(dot)kartyshov(at)postgrespro(dot)ru
To: pgsql-hackers(at)postgresql(dot)org
Subject: WIP: long transactions on hot standby feedback replica / proof of concept
Date: 2017-09-04 11:04:01
Message-ID: c9374921e50a5e8fb1ecf04eb8c6ebc3@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Our clients complain about this issue and therefore I want to raise the
discussion and suggest several solutions to this problem:

I. Why does PG use Fatal when Error is enough to release lock that rises
lock conflict?
"If (RecoveryConflictPending && DoingCommandRead)"

II. Do we really need to truncate the table on hot standby exactly at
the same time when truncate on master occurs?

In my case conflict happens when the autovacuum truncates table tbl1 on
master while backend on replica is performing a long transaction
involving the same table tbl1. This happens because truncate takes an
AccessExclusiveLock. To tackle this issue we have several options:

1. We can postpone the truncate on the master until all the replicas
have finished their transactions (in this case, feedback requests to the
master should be sent frequently)
Patch 1

2. Maybe there is an option somehow not to send AccessExclusiveLock and
not to truncate table on the replica right away. We could try to wait a
little and truncate tbl1 on replica again.

Here is a patch that makes replica skip truncate WAL record if some
transaction using the same table is already running on replica. And it
also forces master to send truncate_wal to replica with actual table
length every time autovacuum starts.
Patch 2

In this case the transaction which is running for several hours won’t be
interrupted because of truncate. Even if for some reason we haven’t
truncated this table tbl1 right away, nothing terrible will happen. The
next truncate wal record will reduce the file size by the actual length
(if some inserts or updates have been performed on master).

If you have any ideas or concerns about suggested solution I’ll be glad
to hear them.

Ivan Kartyshov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

Attachment Content-Type Size
vacuum_lazy_truncate.patch text/x-diff 629 bytes
standby_truncate_skip_v1.patch text/x-diff 3.7 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2017-09-04 11:11:55 Re: Release Note changes
Previous Message amul sul 2017-09-04 10:38:45 Re: [POC] hash partitioning