Re: HS/SR and smart shutdown

Josh Berkus wrote:
>> I guess that the startup process and the walreceiver should wait
>> for all read only backends to exit in smart shutdown case. It's
>> because those backends might be waiting for the record that conflicts
>> with their queries to be replayed. Is this OK? Or we should kill the
>> startup process and the walreceiver on ahead?
>>
>> If my guess is right, we would need to add new PMState to cancel
>> recovery and replication after all read only connections have died.
>
> How could existing read queries on the slave be waiting on a WAL record?

Imagine that you do this in the master:

begin;
DROP TABLE foo (id int4);
< a lot of other stuff>
commit;

When the DROP is replayed in the standby, the startup process acquires a
lock on table foo, on behalf of the transaction that it's replaying. If
you run "SELECT * FROM foo" in the standby after that, it will block
until the startup process replays the COMMIT record and releases the lock.

This is similar to the deadlock situation in hot standby that was
discussed on the other thread, "Re: pgsql: In HS, Startup process sets
SIGALRM when waiting for buffer pin."

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com