Обсуждение: BUG #7567: Sequences not properly replicated

Поиск
Список
Период
Сортировка

BUG #7567: Sequences not properly replicated

От
s.proels@pharmatechnik.de
Дата:
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
 =

Re: BUG #7567: Sequences not properly replicated

От
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

Re: BUG #7567: Sequences not properly replicated

От
Stefan Pröls
Дата:
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