PostgreSQL Sequences and Drupal

I was using Drupal and after one of my upgrades, something changed in the database (I wish I'd taken better notes of which upgrade and saved a copy of my backup, but I didn't do that quick enough so I've lost the data). In any case, my database sequences no longer matched up with what the database was expecting.

In fact, I was getting the following error:

* warning: pg_query(): Query failed: ERROR: duplicate key value violates unique constraint “node_revisions_pkey” in /path/to/drupal-6.2/includes/database.pgsql.inc on line 139.
* user warning: query: INSERT INTO node_revisions (nid, uid, title, body, teaser, log, timestamp, format) VALUES (0, 1, ‘test’, ‘test test test test test test test test test test test vv’, ‘test test test test test test test test test test test test test vv’, ”, 1294612270, 4) in /path/to/drupal-6.2/includes/common.inc on line 3538.

All my research led me to believe that it was an issue with my sequence not being bound to my column correctly. However, I could see through psql that my column in the table was bound correctly. Finally, tired of debugging on and off over several months, I turned full logging onto my database and saw another error (now, if I'd really looked at the message, I might not have had to do that as I was able to get a clue from this web site: http://www.devissues.com/tag/drupal)

By looking at the sequences and the tables (with the following commands), I discovered that my sequence was reset to a lower value.

tim=# SELECT nextval('node_nid_seq');
 nextval
---------
     150
(1 row)

tim=# SELECT max(nid) FROM node;
 max
-----
 149
(1 row)

tim=# SELECT nextval('node_revisions_vid_seq');
 nextval
---------
     109
(1 row)

tim=# SELECT max(vid) FROM node_revisions;
 max
-----
 144
(1 row)

Looking at the sequence for node_devisions_vid_seq and lookign at the max number of the vid from node_revisions...hey, I've got a mis-match. Resetting the sequence to be 146 solved my problem. Here is the actual SQL:

tim=# ALTER SEQUENCE node_revisions_vid_seq RESTART WITH 146;

And that seems to have fixed my problem for the database.