Skip site navigation (1) Skip section navigation (2)

BUG #5882: last_value of sequence on replicated properly

From: "lou fridkis" <lfridkis(at)earthlink(dot)net>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5882: last_value of sequence on replicated properly
Date: 2011-02-11 16:39:37
Message-ID: 201102111639.p1BGdbgc098696@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5882
Logged by:          lou fridkis
Email address:      lfridkis(at)earthlink(dot)net
PostgreSQL version: 9.0
Operating system:   linux
Description:        last_value of sequence on replicated properly
Details: 

I am testing pg9 hot standby. I set up a primary and secondary on
separate hosts. I wrote a simple program to insert 10,000 rows into a
table as fast as possible. The table definition is:
\d loutest1
                                    Table "loutest1"
Column |         Type          |                         Modifiers
--------------------
lou_id | integer               | not null default
nextval('loutest1_lou_id_seq'::regclass)
i1     | integer               |
v1     | character varying(10) |

The insert statement is:
insert into loutest1 (i1, v1) VALUES (1, 'hi');

The problem is that the values for loutest1_lou_id_seq are different
after the test:
select * from loutest1_lou_id_seq;

    sequence_name    | last_value | start_value | increment_by | max_value  
        | min_value | cache_value | log_cnt | is_cycled | is_called
-
loutest1_lou_id_seq  |      10143 |           1 |            1 |
9223372036854775807 |         1 |           1 |       0 | f         | t

vs.

select * from pnp.loutest1_lou_id_seq;

    sequence_name    | last_value | start_value | increment_by | max_value  
   | min_value | cache_value | log_cnt | is_cycled | is_called
-
loutest1_lou_id_seq |      10111 |           1 |            1 |
9223372036854775807 |         1 |           1 |      32 | f         | t

The data in the two tables is still identical with the max value of lou_id
being 10111 in both. Any idea what could cause the seq values to differ?
Has
anyone else found anything like this? Any suggestions for solutions? So far
this is not critical, since the secondary's value is bigger. In the case of
a
failover, there would be a gap, but no error.  But, if the secondary were to
be
smaller, it would be critical. Any thoughts?

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-02-11 17:59:45
Subject: Re: BUG #5882: last_value of sequence on replicated properly
Previous:From: Kevin GrittnerDate: 2011-02-11 15:27:52
Subject: Re: BUG #5881: postgres query tuning

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group