Thread: BUG #7567: Sequences not properly replicated
The following bug has been logged on the website:
Bug reference: 7567
Logged by: Stefan Pr=C3=B6ls
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.
=3D=3D=3DMaster:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D
$ 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
=3D=3D=3DSlave:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D
$ 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
=3D=3D=3DMaster:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D
$ 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
=3D=3D=3DSlave:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D
$ 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
=3D=3D=3DMaster:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D
$ 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
=3D=3D=3DSlave:=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D
$ 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
=
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
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