Lists: | pgsql-bugs |
---|
From: | s(dot)proels(at)pharmatechnik(dot)de |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #7567: Sequences not properly replicated |
Date: | 2012-09-25 10:13:05 |
Message-ID: | E1TGS8X-0004KK-RU@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 7567
Logged by: Stefan Pröls
Email address: s(dot)proels(at)pharmatechnik(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | s(dot)proels(at)pharmatechnik(dot)de |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #7567: Sequences not properly replicated |
Date: | 2012-09-25 16:08:47 |
Message-ID: | 21328.1348589327@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
s(dot)proels(at)pharmatechnik(dot)de writes:
> The following bug has been logged on the website:
> Bug reference: 7567
> Logged by: Stefan Prls
> Email address: s(dot)proels(at)pharmatechnik(dot)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
From: | Stefan Pröls <s(dot)proels(at)pharmatechnik(dot)de> |
---|---|
To: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #7567: Sequences not properly replicated |
Date: | 2012-09-26 07:23:52 |
Message-ID: | 5062AD88.4090506@pharmatechnik.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
Am 25.09.2012 18:08, schrieb Tom Lane:
> s(dot)proels(at)pharmatechnik(dot)de writes:
>> The following bug has been logged on the website:
>> Bug reference: 7567
>> Logged by: Stefan Pröls
>> Email address: s(dot)proels(at)pharmatechnik(dot)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