Postgres Sequence out of sync

Question

I'm running a multi-master setup with bucardo and postgres.

I'm finding that some of my table sequences are getting out of sync with each other. Particularly the auto-incremented id.

example:

db1 - table1

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')

The id of the new row is 1

db2 - table1

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')

The id of the new row is 1

The id of the new row on db2 should be 2, because bucardo has replicated the data from db1, but db2's auto increment is based on:

nextval('oauth_sessions_id_seq'::regclass)

And if we check the "oauth_sessions_id_seq" we see the last value as 0.

phew... Make sense?

Anyway, can I do any of the following?

Replicate the session tables with bucardo, so each DB's session is shared?
Manipulate the default auto-increment function above to take into account the max existing items in the table?

If you have any better ideas, please feel free to throw them in. Questions just ask, thanks for any help.

The "correct" solution would be for Bucardo to sync the sequence (which is an object in its own right, freely manipulable) when it syncs the table. No idea if that's possible, though. — IMSoP
– IMSoP, Commented Jan 25, 2015 at 1:38
yeah I agree, I don't know if there is something like public or private tables, because bucardo can't seem to see the sequences table to sync — outrunthewolf
– outrunthewolf, Commented Jan 25, 2015 at 1:44

Community · Accepted Answer · 2017-05-23 12:16:14Z

3

You are going to have to change your id generation method, because there is no Bucardo solution according to this comment in the FAQ.

Can Bucardo replicate DDL?

No, Bucardo relies on triggers, and Postgres does not yet provide DDL triggers or triggers on its system tables.

Since Bucardo uses triggers, it cannot "see" the sequence changes, only the data in tables, which it replicates. Sequences are interesting objects that do not support triggers, but you can manually update them. I suppose you could add something like the code below before the INSERT, but there still might be issues.

SELECT setval('oauth_sessions_id_seq', (SELECT MAX(did) FROM distributors));

See this question for more information.

I am not fully up on all the issues involved, but you could perform the maximum calculation manually and do the insert operation in a re-try loop. I doubt it will work if you are actually doing inserts on both DBs and allowing Bucardo to replicate, but if you can guarantee that only one DB updates at a time, then you could try something like an UPSERT retry loop. See this post for more info. The "guts" of the loop might look like this:

INSERT INTO  distributors (did, dname) 
    VALUES ((SELECT max(did)+1 FROM distributors), 'XYZ Widgets');

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Jan 25, 2015 at 1:59

Dwayne Towell

8,6635 gold badges39 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

outrunthewolf Over a year ago

Great. So, can bucardo regularly copy the sequence tables by ways of scheduling? Or is there a way to change my auto id generation that you know of? I dont expect I can just stick the select statement as the default?

Dwayne Towell Over a year ago

Sequences are not tables as far as I can tell, they have special properties that allow them to be unique in the face of multithreading. There is no trivial way to mirror them across servers. You will need to implement a different method, or do all inserts on the same DB.

outrunthewolf Over a year ago

Fair enough. My auto-increment is set to nextval('oauth_sessions_id_seq'::regclass). Cannot I do something like nextval(max('oauth_sessions.id')) ? That would negate my issue

Dwayne Towell Over a year ago

You can't put an aggregate function in a default expression.

kant312 · Accepted Answer · 2020-06-16 08:41:03Z

0

Irrespective of the DB (PostgreSQL, Oracle, etc.), dynamic sequence was created for each of the table which has the primary key associated with it. Most of the sequences go out of sync whenever a huge import of data is happened or some person has manually modified the sequence of the table.

Solution: The only way we can set back the sequence is by taking the max value of the PK table and set the sequence next val to it.

The below query will list you out all the sequences created in your DB schema:

SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

SELECT MAX('primary_key') from table;

SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

edited Jun 16, 2020 at 8:41

kant312

1,4141 gold badge20 silver badges33 bronze badges

answered Jun 15, 2020 at 13:26

Guru Vamsi

1

Collectives™ on Stack Overflow

Postgres Sequence out of sync

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related