Re: BUG #13844: Logical decoding bug with subxact + row locking

On Tue, Jan  5, 2016 at 03:32:49AM +0000, jarred@webriots.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      13844
> Logged by:          Jarred Ward
> Email address:      jarred@webriots.com
> PostgreSQL version: 9.4.5
> Operating system:   Linux
> Description:
>
> This is a pretty serious logical decoding bug that bit us pretty hard
> in production.
>
> Run the following code:
>
>
> ----------------------------------------------------------------------------
>     CREATE TABLE foo(a SERIAL PRIMARY KEY, b TEXT);
>
>     SELECT pg_create_logical_replication_slot('regression_slot',
> 'test_decoding');
>
>     INSERT INTO foo (b) VALUES ('john');
>
>     BEGIN;
>     SELECT * FROM foo WHERE b = 'john' FOR UPDATE;
>     SAVEPOINT sp1;
>     INSERT INTO foo (b) VALUES ('jane');
>     ROLLBACK TO SAVEPOINT sp1;
>     INSERT INTO foo (b) VALUES ('doe');
>     END;
>
>     SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL,
> NULL);
>
> ----------------------------------------------------------------------------
>
> The last select above should return the decoded WAL, but instead it
> returns:
>
>
> ----------------------------------------------------------------------------
>     ERROR:  subxact logged without previous toplevel record
>
> ----------------------------------------------------------------------------
>
> And the process is disconnected with no way to recover the replication
> slot without dropping and recreating.
>
> If SELECT statement in the transaction is changed the following:
>
>
> ----------------------------------------------------------------------------
>     SELECT * FROM foo WHERE b = 'john' FOR UPDATE;
>
> ----------------------------------------------------------------------------
>
> to remove the row level locking the correct decoded change set is
> returned.

I can confirm this behavior.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription                             +