I have an application that often does insertion into PostgreSQL database, for some tables I have id column with serial type with auto-generated sequences. Running

SELECT nextval(pg_get_serial_sequence('client', 'id'));

on each new session gives 1. What should I do to correct this, or I need to this manually in my app?

For last case, I think get total records and alter sequence, but this is 2 more queries to do, which I better skip.


What you want is how sequences in PostgreSQL generally behave.

You must have some rogue process which is issuing a command such as:

alter sequence client_id_seq restart;

Or something equivalent to that. Figure out what is issuing that command, and make it stop.

If you can't figure out who is sabotaging your sequence, there are a variety of ways to figure it out. One is to crank up your log level by setting log_statement to 'ddl' or 'all', and look in the logs. Another is to make your table and sequence owned by a role which does not normally log into the database, and grant rights to the table and sequence to the normal log-in users. They will then get errors when they try to reset the sequence, because only the owner can do that. (This is a good practice anyway, to have objects owned by special roles which do not log in except when it is necessary to change the schema.)

| improve this answer | |
  • Thanks! This is very helpful, I'll test my db with your suggestions – sigrlami Mar 31 '16 at 17:48
  • because only the owner can do that. And superusers, of course. And SECURITY DEFINER functions owned by either. – Erwin Brandstetter Mar 31 '16 at 23:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.