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, Jan 25 '15 at 01: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, Jan 25 '15 at 01:44

score 3 · Accepted Answer · edited May 23 '17 at 12:16

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 '17 at 12:16

Community

1
1

answered Jan 25 '15 at 01:59

Dwayne Towell

8,154
4
36
49

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? – outrunthewolf Jan 25 '15 at 02:15
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. – Dwayne Towell Jan 25 '15 at 03:07
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 – outrunthewolf Jan 25 '15 at 04:50
You can't put an aggregate function in a default expression. – Dwayne Towell Jan 25 '15 at 04:53

score 0 · Answer 2 · edited Jun 16 '20 at 08:41

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);

Postgres Sequence out of sync

2 Answers2

Related