BUG #7567: Sequences not properly replicated

Started by Stefan Prölsover 13 years ago3 messagesbugs
Jump to latest
#1Stefan Pröls
s.proels@pharmatechnik.de

The following bug has been logged on the website:

Bug reference: 7567
Logged by: Stefan Pröls
Email address: s.proels@pharmatechnik.de
PostgreSQL version: 9.1.3
Operating system: OpenSUSE 11.4 32-Bit
Description:

Scenario:
1 Master, 1 Hot-Standby Slave, asynchronous streaming replication, both
servers PostgreSQL 9.1.3.

===Master:================================================================================================================================
$ CREATE SEQUENCE t_seq;

$ SELECT * FROM t_seq;
sequence_name | last_value | start_value | increment_by | max_value
| min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
t_seq | 1 | 1 | 1 |
9223372036854775807 | 1 | 1 | 1 | f | f

===Slave:=================================================================================================================================
$ SELECT * FROM t_seq;
sequence_name | last_value | start_value | increment_by | max_value
| min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
t_seq | 1 | 1 | 1 |
9223372036854775807 | 1 | 1 | 0 | f | t

===Master:================================================================================================================================
$ SELECT nextval('t_seq');
nextval
---------
1

$ SELECT * FROM t_seq;
sequence_name | last_value | start_value | increment_by | max_value
| min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
t_seq | 1 | 1 | 1 |
9223372036854775807 | 1 | 1 | 0 | f | t

===Slave:=================================================================================================================================
$ SELECT * FROM t_seq;
sequence_name | last_value | start_value | increment_by | max_value
| min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
t_seq | 1 | 1 | 1 |
9223372036854775807 | 1 | 1 | 0 | f | t

===Master:================================================================================================================================
$ SELECT nextval('t_seq');
nextval
---------
2

$ SELECT * FROM t_seq;
sequence_name | last_value | start_value | increment_by | max_value
| min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
t_seq | 2 | 1 | 1 |
9223372036854775807 | 1 | 1 | 32 | f | t

===Slave:=================================================================================================================================
$ SELECT * FROM t_seq;
sequence_name | last_value | start_value | increment_by | max_value
| min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
t_seq | 34 | 1 | 1 |
9223372036854775807 | 1 | 1 | 0 | f | t

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Stefan Pröls (#1)
Re: BUG #7567: Sequences not properly replicated

s.proels@pharmatechnik.de writes:

The following bug has been logged on the website:
Bug reference: 7567
Logged by: Stefan Pröls
Email address: s.proels@pharmatechnik.de
PostgreSQL version: 9.1.3
Operating system: OpenSUSE 11.4 32-Bit
Description:

Scenario:
1 Master, 1 Hot-Standby Slave, asynchronous streaming replication, both
servers PostgreSQL 9.1.3.

The fact that the slave sees the last_value as ahead of the master is
not a bug; you'd have the same last_value on the master if it were to
crash and restart. (This is due to a tradeoff made long ago to reduce
the amount of WAL traffic created by nextval: only one WAL record is
written per 32 nextval's, and that record claims last_value is 32 ahead
of what it previously was.)

There is a known bug, fixed in 9.1.5, associated with sequences that
have been ALTER'd ... but that's not what you're showing here.

regards, tom lane

#3Stefan Pröls
s.proels@pharmatechnik.de
In reply to: Tom Lane (#2)
Re: BUG #7567: Sequences not properly replicated

Am 25.09.2012 18:08, schrieb Tom Lane:

s.proels@pharmatechnik.de writes:

The following bug has been logged on the website:
Bug reference: 7567
Logged by: Stefan Pröls
Email address: s.proels@pharmatechnik.de
PostgreSQL version: 9.1.3
Operating system: OpenSUSE 11.4 32-Bit
Description:
Scenario:
1 Master, 1 Hot-Standby Slave, asynchronous streaming replication, both
servers PostgreSQL 9.1.3.

The fact that the slave sees the last_value as ahead of the master is
not a bug; you'd have the same last_value on the master if it were to
crash and restart. (This is due to a tradeoff made long ago to reduce
the amount of WAL traffic created by nextval: only one WAL record is
written per 32 nextval's, and that record claims last_value is 32 ahead
of what it previously was.)

There is a known bug, fixed in 9.1.5, associated with sequences that
have been ALTER'd ... but that's not what you're showing here.

regards, tom lane

Ah, I see. Thank you for explaining this!

Best regards,
Stefan Pröls

________________________________

PHARMATECHNIK GmbH und Co. KG
Münchner Strasse 15
D-82319 Starnberg

Sitz der Gesellschaft: Starnberg
HRA: 64434, HRB: 66369, Amtsgericht München
Geschäftsführer: Dr. Detlef Graessner, Dr. Mathias Schindl, Stephan Jörgens