6

Developers encountered issues with sequences in our postgres 9.6 database over the past year, where they stop working, so they have resorted to things like inserting rows with a pid manually created based on the last inserted record's pid, and resetting the sequence using this code:

SELECT pg_catalog.setval(pg_get_serial_sequence('mytable', 'pid'), 
       (SELECT MAX(pid)+1 FROM mytable) );

I am experienced enough to know that we should be able to rely on the database to create our ids, and that creating one's own PIDs based on last max is not a 'best practice' nor safe for all scenarios, though it generally has worked for our daily use.

The reason they stopped working the first time is the well-known cause that a table was restored for example. However, at this point I think the manual code and sequences are stepping on each other, and the sequence resetting code is not solid. It seems obvious that inserting the last max will undermine the sequence, which simply has its own number it increments.

While I am reading up, I would like to know if someone point me in the right direction today to get the sequence back up, and working flawlessly, on an existing table - and have it work even though code is in place that will sometimes insert a pid from code, based on last max. (Longer term, of course, I am fully aware it is best all such code will is dropped - but is there a way to work around that code for now?)

Included in a solution would be a way in postgreSQL 9.6 to have it reset the sequence itself if there is a conflict - not sure if possible, and am bracing for lectures from the more experienced out there - but that's why I am here!

Finally - and this is one disturbing fact that got me here - after resetting the sequence in pg admin I see two pids in the table in pg admin 3 and 4, which also shows in the create script. The 'second' PID column does not show when doing \d in psql, which is good - but I thought it might be relevant.

UPDATE - on that last point, the ghost duplicate PIDs for a table in pg admin were caused because there were two sequences for that same column/table combo, the second created at some point to try and fix broken sequences over time (eg mytable_pid_seq and mytable_pid_seq1). Not sure why/how this was allowed to happen in the database.

  • 1
    setval() will correctly adjust the sequence, but it can't solve the problem of manually inserting a "non-sequence" value. Maybe you should use a trigger that simply ignores any provided value for that column and always uses the sequence to populate it. – a_horse_with_no_name Jul 14 '17 at 7:12
  • If the sequence value isn't useful, then simply don't copy it over to begin with. Use a load into a temp table, and then just INSERT INTO foo(x,y,z) SELECT x,y,z FROM temp; Or, use an ETL script. Or, just drop the surrogate key and use a natural key. Or.. If you're not using the surrogate key for joins even better use a gen_random_uuid() and UUIDs which allows easy merger of tables. – Evan Carroll Jul 14 '17 at 18:55
1

Included in a solution would be a way in postgreSQL 9.6 to have it reset the sequence itself if there is a conflict - not sure if possible, and am bracing for lectures from the more experienced out there - but that's why I am here!

First, the real fix would be for the code to always use the sequence instead of the inconsistent mix of select 1+max(pk) and nextval('seqname').

That being said, as a band-aid solution before a real fix, you could have a trigger on INSERT for each row that always calls nextval on the sequence, so that you're sure that the sequence never lags behind, even when the INSERT itself is missing a nextval call.

If the column has a DEFAULT nextval('seqname') (either set manually or through a SERIAL declaration), and a trigger calls nextval in addition to the nextval of the DEFAULT, that will just advance the sequence by two instead of one. That should not be a problem for your app because sequences can have holes anyway, due to rollback or caching.

Calling setval with SELECT 1+max(pk) from table also works, but only when there aren't other transactions using the sequence concurrently. So doing that in a trigger doesn't sound like a good idea. Typically this sort of adjustement is only ever done after a bulk load (in fact, that's what pg_dump produces when the sequence is owned by the table).

  • I'll accept this as correct, thanks - the same solution and related implications are discussed in comments above, though we decided to skip the bandaid and remove the problematic code and have the database take care of sequences, which of course is the best way to go about it. – csdev Jul 17 '17 at 18:29
2

The reason they stopped working the first time is the well-known cause that a table was restored for example. However, at this point I think the manual code and sequences are stepping on each other, and the sequence resetting code is not solid. It seems obvious that inserting the last max will undermine the sequence, which simply has its own number it increments.

If you're restoring from pg_dump there is no way that can be the problem. Take for instance,

CREATE TABLE foo (id serial PRIMARY KEY);
INSERT INTO foo DEFAULT VALUES;

This generates a table like this,

                         Table "public.foo"
 Column |  Type   |                    Modifiers                     
--------+---------+--------------------------------------------------
 id     | integer | not null default nextval('foo_id_seq'::regclass)
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)

You can see here that the type of id is just int and all the sequence does is default it to nextval. Using pg_dump, this will generate a dump like this (removing comments and permissions)

CREATE TABLE foo (
    id integer NOT NULL
);

CREATE SEQUENCE foo_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE ONLY foo ALTER COLUMN id SET DEFAULT nextval('foo_id_seq'::regclass);

COPY foo (id) FROM stdin;
1
\.

SELECT pg_catalog.setval('foo_id_seq', 1, true);

ALTER TABLE ONLY foo
    ADD CONSTRAINT foo_pkey PRIMARY KEY (id);

That a lot of stuff, but it's the safest way to restore even a simple table. So notice here the call to pg_catalog.setval

SELECT pg_catalog.setval('foo_id_seq', 1, true);

After you merge in other pkids that are potentially higher, you'll have to call that with the new highest value.

SELECT pg_catalog.setval(
  'foo_id_seq',
  (SELECT max(id) FROM foo),
  true
);

Then viola, you're back to working. They're not broken at all.

Important note, this is for serial types. Pg 10 will change this interface if you're using Identity Columns and it will instead be a standardized ALTER TABLE command to change the current value.

  • Thanks for addressing the underlying cause - we sometimes have used other means to restore tables, and I did not know that pg_dump handled it that way, so your answer is informative. However, I am still stuck with code that steps on auto-incremented pid columns today - please feel free to expand on the answer, if you have any advice on reconciling that issue, aside from not setting pids in code. Thanks – csdev Jul 13 '17 at 20:47
  • I'm confused at how this is a problem, set pg_catalog.setval like mentioned above to be above the range of your highest pid. Then the problem is fixed. The next insert will pick up from there. – Evan Carroll Jul 13 '17 at 20:50
  • a_horse_with_no_name's response settles it - that was the part I was looking for some kind of work-around for the short-term, but we decided to do the smart thing, and fix the sequences, and remove the 'manual', code-based inserting of non-sequence ids. Appreciate your sequence info. – csdev Jul 14 '17 at 17:51
  • 1
    "If you're restoring from pg_dump there is no way that can be the problem. " I imported a pg_dump from postgres 10 into postgres 9.3 and observed this issue of serial fields missing defaults. – Amalgovinus Sep 17 '18 at 21:50
  • This was my issue stackoverflow.com/q/49356043/388185 – Amalgovinus Sep 17 '18 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.