Thread: hot_standby_feedback

I have a question about hot_standby_feedback parameter. In my
understanding, if this parameter is on, a long running transaction on
standby will not be canceled even if the transaction conflicts.

So I have primary PostgreSQL and standby PostgreSQL running 9.2.4.

On primary:
create table t1(i int);
insert into t1 values(1),(2),(3);

On standby:
begin;
select * from t1;
 i
---
 1
 2
 3
(3 rows)

On primary:
delete from t1;

On standby:
select * from t1;
 i
---
(0 rows)

On primary:
vacuum verbose t1;
INFO:  vacuuming "public.t1"
INFO:  "t1": removed 3 row versions in 1 pages
INFO:  "t1": found 3 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "t1": truncated 1 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

As you can see vacuum on the primary removes all the rows in t1. I
thought vacuum will not make the page entriely empty because
of the effect of hot_standby_feedback.

After while, on standby:
test=# select * from t1;
FATAL:  terminating connection due to conflict with recovery
DETAIL:  User was holding a relation lock for too long.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.

Again, this is not what I expected. Am I missing something?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

On Thu, Aug 29, 2013 at 2:44 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:
> I have a question about hot_standby_feedback parameter. In my
> understanding, if this parameter is on, a long running transaction on
> standby will not be canceled even if the transaction conflicts.

> As you can see vacuum on the primary removes all the rows in t1. I
> thought vacuum will not make the page entriely empty because
> of the effect of hot_standby_feedback.
>
> After while, on standby:
> test=# select * from t1;
> FATAL:  terminating connection due to conflict with recovery
> DETAIL:  User was holding a relation lock for too long.
> HINT:  In a moment you should be able to reconnect to the database and repeat your command.
>
> Again, this is not what I expected. Am I missing something?

I also expected this behavior, and suffer similar problems with
PostgreSQL 9.1. I reported this as BUG #7546 with a test case and
raised it a few times on the mailing lists, but am no closer to a
solution.