Re: BUG #4565: nextval not updated during wal replication, leading to pk violations

"Marc Schablewski" <ms(at)clickware(dot)de> writes:
> A job tried to insert some records into a few tables and complained about a
> PK violation. PK are of type bigint. The column is populated by a sequence
> and a "default nextval()" on the PK column. We found that the sequence's
> currval was lower than the maximum value in the table and that this value
> was already present. Further investigation showed us that the last records
> in the table were inserted on the former master server while taking the
> initial online backup for the replication. It seems that the records got
> replicated but not the currval/nextval of the sequence. When running "select
> nextval()" on the backup database it returned the PK value of the first
> record inserted during that last run on the former master server.

How many sequence values "overlapped" here, exactly? If more than one,
are you by any chance running that sequence with a CACHE setting larger
than one? (See the sequence's cache_value column if you're unsure.)

I can see what might be a race condition between nextval()'s WAL logging
and checkpoint start, but it doesn't seem like it could account for more
than the CACHE setting worth of overlap.

regards, tom lane