« New Category - Scalability & Performance | Main | Data Sportability »
May 23, 2008
PostgreSQL Rename Sequence Bug/Feature Fix 
In PostgreSQL ( < 8.3 I believe ), if you rename a sequence you don't fully rename it
This is recorded in bug 3619 http://archives.postgresql.org/pgsql-bugs/2007-09/msg00092.php
While the sequence will be renamed as an object, the metadata won't...
PG stores the sequence name both as the object , and as 'sequence_name' in the sequence itself ( viewable via 'SELECT * FROM sequence' ; i think its in the postgres db catalog too )
Most people won't run into an issue... until you use an ORM which is likely to pull the old sequence name from PG.
after much searching and trying, i found a fix. note: this only works for SMALL databases:
$ pg_dump -U$owner $db > $db.sql
$ psql -Upostgres
psql> DROP DATABASE $db;
psql> CREATE DATABASE $db WITH OWNER = $owner;
psql> \q
$ psql -U$owner $db < $db.sql
yes, that's the amazing fix -- backup up, dropping, and restoring the database. i'm sure there's a better fix out there somewhere -- i just couldn't find it with 2 hours of searching and syntax attempts.
If you can find a better way, LET ME KNOW and I'll update this reference.
Posted by Jonathan at May 23, 2008 4:08 PM
Comments
Post a comment
Thanks for signing in, . Now you can comment. (sign out)
(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)