On 2013-04-12 12:27:01 +0000, tar...@gmail.com wrote: > The following bug has been logged on the website: > > Bug reference: 8059 > Logged by: Tarvi Pillessaar > Email address: tar...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs