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
vacuum_lazy_truncate.patch
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
standby_truncate_skip_v1.patch
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.
Thanks!
--
Ivan Kartyshov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers