On 2013-04-12 12:27:01 +0000, tarvip@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 8059
> Logged by: Tarvi Pillessaar
> Email address: tarvip@gmail.com
> PostgreSQL version: 9.2.4
> Operating system: linux
> Description:
>
> Very simple example:
>
> postgres@sbox /usr/local/pgsql $ /usr/local/pgsql/bin/psql test
> psql (9.2.4)
> Type "help" for help.
>
> test=# create sequence s;
> CREATE SEQUENCE
> test=# begin;
> BEGIN
> test=# select nextval('s');
> nextval
> ---------
> 1
> (1 row)
>
>
> Now let's crash the cluster:
>
> postgres@sbox /usr/local/pgsql $ pgrep -lf writer
> 13638 postgres: writer process
> 13639 postgres: wal writer process
> postgres@sbox /usr/local/pgsql $ kill -9 13638
> postgres@sbox /usr/local/pgsql $ tail logfile
> HINT: In a moment you should be able to reconnect to the database and
> repeat your command.
> LOG: all server processes terminated; reinitializing
> LOG: database system was interrupted; last known up at 2013-04-12 14:28:26
> EEST
> LOG: database system was not properly shut down; automatic recovery in
> progress
> LOG: redo starts at 0/177C9E0
> LOG: record with zero length at 0/1791888
> LOG: redo done at 0/1791858
> LOG: last completed transaction was at log time 2013-04-12
> 14:29:48.562356+03
> LOG: database system is ready to accept connections
> LOG: autovacuum launcher started
> postgres@sbox /usr/local/pgsql $
Thats caused by the fact that you didn't do anything that forces a WAL
flush since you didn't do any inserts or such. If you would do any
DML in the nextval() calling transaction this shouldn't happen (unless
you use synchronous_commit=off).
Now, there's a legitimate argument to be made that returning the
sequence value to the user kinda persists it. On the other hand, at
least in the first example you haven't even committed the transaction so
there's nothing that could flush the transaction unless we we would
*always* flush nextval() immediately if needs to get new values which
doesn't seem reasonable.
I think a reasonable compromise would be that nextval_internal()
acquires a real xid (by calling GetTopTransactionId()). In that case the
commit would force a WAL flush but there are no forced wal flushes ones
inside the transaction. And it would support async commits.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
