Обсуждение: Serial column has suddenly stopped working

I have been using a serial column in my most important postgres table
for a couple of years.

But today it has suddenly started assigning zero instead of the next
number, which clearly is causing chaos.
I have restarted Postgres, but it has not solved the problem. I cannot
re-assign the serial number because it is tied to other tables.
Anybody had this problem??

I have reached number 14954 which we can't exactly call a high number.

The column description is as follows:

Name   srl
type   bigint
modifiers    default nextval(booking_srl_seq::text)

Any help greatly appreciated.

Malcolm Warren wrote:
> I have been using a serial column in my most important postgres table
> for a couple of years.
>
> But today it has suddenly started assigning zero instead of the next
> number, which clearly is causing chaos.
> I have restarted Postgres, but it has not solved the problem. I cannot
> re-assign the serial number because it is tied to other tables.
> Anybody had this problem??
>
> I have reached number 14954 which we can't exactly call a high number.
>
> The column description is as follows:
>
> Name   srl
> type   bigint
> modifiers    default nextval(booking_srl_seq::text)

What happens if you go SELECT nextval('booking_srl_seq') - do you get
the expected number?

--
   Richard Huxton
   Archonet Ltd

The answer is 3. Mistake of mine. I put in a field of the same name to
display it, and it was also sending 0 with the statement.
Thank you for helping me get to the bottom of it.

Malcolm Warren

Richard Huxton wrote:

> Malcolm Warren wrote:
>
>> Thank you for your reply.
>> Yes, I get the next number and the number goes up one, as it should.
>>
>> The rest of the table seems to function normally incidentally, just
>> the serial column gets filled with 0.
>> By the way my version is 7.3 which I forgot to mention.
>
>
> Hmm - since you say you've dumped/restored to another machine that
> leaves two things I can think of:
>
> 1. Schema issue (maybe it's not seeing the sequence).
> 2. Trigger is altering the value supplied.
> 3. The application isn't sending the query you think it is.
>
> What happens if you manually:
> INSERT INTO my_table (..., srl, ...) VALUES (...,
> nextval('booking_srl_seq',...)
>
> If that works, it's not #2, and it's time to look at the application
> (and turn statement logging on).
>
> --
>   Richard Huxton
>   Archonet Ltd
>
>