I recently upgraded from using 7.1 under Cygwin to 7.3.3 on Linux (RH9).
Since I was completely changing infrastructures I did what I thought was a
full dump and restore. When the new system was ready I took the dump and did
some minor edits to replace a database user name to one that made sense. So
far so good.
I did some lightweight testing and everything on the database side (the
application side got ported to a new app server and had to be tweaked)
worked great up until I tried to use a little-used feature of the app. That
portion of the app inserts a row into a table which has a sequence as the
primary key. I got an exception thrown saying that my user was not allowed
to modify the sequence value. I had to go in to the database as the postgres
user and GRANT ALL to the application user to modify the sequence. Now I'm
grumpy because I know there are several other sequences in the database that
I will have to perform the same operation on.
Now that I know what to do, I want to try and understand *why* I have to do
it so that the next time around I can avoid it. When I originally created
the database schema definition I simply defined the primary key as needing a
sequence and allowed PostgreSQL to create it. However in the dump/restore
script the sequence is explicitly created. Could that be the reason that
this occured?
rjsjr