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

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.