0

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.

closed as off-topic by Michael Green, Max Vernon, RolandoMySQLDBA, dezso, Andriy M Apr 1 '16 at 9:11

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Michael Green, Max Vernon, RolandoMySQLDBA, dezso, Andriy M
If this question can be reworded to fit the rules in the help center, please edit the question.

4

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.)

  • 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.