postgres sequence problems, manually created pids, and proper sequence resetting

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

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.

StackExchange Database Administrators Q#179802, answer score: 6