Re: Hot Standby 0.2.1

On Tue, Sep 22, 2009 at 10:02 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Heikki Linnakangas escribió:
>> Simon Riggs wrote:
>> > On Mon, 2009-09-21 at 19:42 -0700, Jeff Janes wrote:
>> >> jjanes=# begin;
>> >> BEGIN
>> >> jjanes=# lock table pgbench_history in access exclusive mode;
>> >> LOCK TABLE
>> >> jjanes=# select count(*) from pgbench_history;
>> >> count
>> >> --------
>> >> 519104
>> >> (1 row)
>> >>
>> >> jjanes=# select count(*) from pgbench_history;
>> >> count
>> >> --------
>> >> 527814
>> >> (1 row)
>> >>
>> >> Is this the expected behavior?
>> >
>> > By me, yes. WAL replay does not require a table lock to progress. Any
>> > changes are protected with block-level locks. It does acquire a table
>> > lock and cancel conflicting queries when it is about to replay something
>> > that would cause a query to explode, such as dropping a table, as
>> > explained in docs.
>>
>> That is rather surprising. You can't get that result in a normal server,
>> which I think is enough of a reason to disallow it. If you do LOCK TABLE
>> ACCESS EXCLUSIVE, you wouldn't expect the contents to change under your
>> nose.

Right. I'd rather be denied the lock than have it granted to me but
not do the same thing it does on a primary---prevent all changes to
the locked table.

> I think the fallout from that argument is that WAL replay should hold
> table-level locks (otherwise the workaround to this problem is too
> special-casey). I'm not sure about that. If I really wanted to get
> consistent results, I'd use a serializable transaction.

Unfortunately, isolation level "serializable" is not truly
serializable. Usually it is good enough, but when it isn't good
enough and you need an explicit table lock (a very rare but not
nonexistent situation), I think it should either lock the table in the
manner it would do on the primary, or throw an error. I think that
silently changing the behavior between primary and standby is not a
good thing.

Cheers,

Jeff