Sequences / Replication

And further to my last post - another post in the forums related to this:

https://devon.so/2015/02/06/as-tale-of-sequences-and-postgresql-replication-9/

Thanks.

*Jonathan J. Eastgate*
Chief Technology Officer | simPRO Software Group
Ph: 1300 139 467 +61 7 3147 8777

<http://simprogroup.com/au/email-redirect&gt;

Keep up to date with simPRO at: http://simprogroup.com/blog
The contents of this email are subject to our email disclaimer
<http://simprogroup.com/au/legal/email-confidentiality-notice&gt;.

On Thu, Oct 20, 2016 at 6:03 PM, Jonathan Eastgate <
jonathan.eastgate@simpro.co> wrote:

Hi everyone.

We're seeing some odd behaviour from a PostgreSQL group - one running as
primary and the other as a hot slave using streaming replication.

When a failover event occurs and we switch to the hot slave as primary
sequences in tables jump by 33 - so where the last number allocated in the
sequence was 100 prior to failover once adding the next entry the sequence
will produce the number 133.

https://stackoverflow.com/questions/38450394/postgresql-
sequence-jump-30-or-33-number-with-cache-equals-1

I've found the following post in the forums - but any advice on how to
resolve or counter this would be appreciated.

Thanks in advance.

*Jonathan J. Eastgate*
Chief Technology Officer | simPRO Software Group
Ph: 1300 139 467 +61 7 3147 8777

<http://simprogroup.com/au/email-redirect&gt;

Keep up to date with simPRO at: http://simprogroup.com/blog
The contents of this email are subject to our email disclaimer
<http://simprogroup.com/au/legal/email-confidentiality-notice&gt;.

--
--

Hi. If the explanation given on one of the stackoverflow responses is right, then there's no current solution for that.

But I would go a little bit further: is it a real problem? I mean, even being a bug, it does not disrupts the basic behaviour of the sequences: that each successive call to nextval should return a value higher than the previous call. If your application has some problem with it, I would seriously recommend to review your application, as the sequence value skipping can also happen in normal situations, like when calling nextval inside a transaction which is finally rolled back: sequences are not transactional, so thatthat would skip one or more values.

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe

----- Original Message -----
From: "Jonathan Eastgate" <jonathan.eastgate@simpro.co>
To: "PostgreSql-general" <pgsql-general@postgresql.org>
Sent: Thursday, 20 October, 2016 03:10:53
Subject: Re: [GENERAL] Sequences / Replication

And further to my last post - another post in the forums related to this:

https://devon.so/2015/02/06/as-tale-of-sequences-and-postgresql-replication-9/

Thanks.

*Jonathan J. Eastgate*
Chief Technology Officer | simPRO Software Group
Ph: 1300 139 467 +61 7 3147 8777

<http://simprogroup.com/au/email-redirect&gt;

Keep up to date with simPRO at: http://simprogroup.com/blog
The contents of this email are subject to our email disclaimer
<http://simprogroup.com/au/legal/email-confidentiality-notice&gt;.

On Thu, Oct 20, 2016 at 6:03 PM, Jonathan Eastgate <
jonathan.eastgate@simpro.co> wrote:

Hi everyone.

We're seeing some odd behaviour from a PostgreSQL group - one running as
primary and the other as a hot slave using streaming replication.

When a failover event occurs and we switch to the hot slave as primary
sequences in tables jump by 33 - so where the last number allocated in the
sequence was 100 prior to failover once adding the next entry the sequence
will produce the number 133.

https://stackoverflow.com/questions/38450394/postgresql-
sequence-jump-30-or-33-number-with-cache-equals-1

I've found the following post in the forums - but any advice on how to
resolve or counter this would be appreciated.

Thanks in advance.

*Jonathan J. Eastgate*
Chief Technology Officer | simPRO Software Group
Ph: 1300 139 467 +61 7 3147 8777

<http://simprogroup.com/au/email-redirect&gt;

Keep up to date with simPRO at: http://simprogroup.com/blog
The contents of this email are subject to our email disclaimer
<http://simprogroup.com/au/legal/email-confidentiality-notice&gt;.

--
--

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jonathan Eastgate wrote:

We're seeing some odd behaviour from a PostgreSQL group - one running as primary and the other as a
hot slave using streaming replication.

When a failover event occurs and we switch to the hot slave as primary sequences in tables jump by 33
- so where the last number allocated in the sequence was 100 prior to failover once adding the next
entry the sequence will produce the number 133.

That is working as expected.

When nextval() is called, a number of sequence numbers are reserved (by default one,
you can set this with the CACHE clause of CREATE SEQUENCE). If the transaction is aborted,
these entries won't be used.

So if there were 30 inserting transactions when your server went down that got aborted,
that would explain the behaviour quite nicely.

This should not be a problem.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general