Re: PostgreSQL server does not increment a SERIAL internally

Hi Matthias,

On Mon, 6 Jul 2020 at 11:43, Matthias Apitz <guru(at)unixarea(dot)de> wrote:

>
> Hello,
>
> Me and my team passed a full weekend hunting a bug in our Perl written
> software were rows have been inserted with the same id 'acq_haushalt.hnr'
> which should not have been the case because any budget year in that
> table has a single internal number 'hnr'
>
> The table in the 11.4 server is created as:
>
> create table acq_haushalt (
> hnr serial not NULL , /* internal budget year number primary key
> */
> hjahr smallint not NULL , /* budget year */
> stufe smallint not NULL , /* level 0,1,2,3 */
> kurzname char (16) , /* short name for ... */
> ...
> );
>
> We update the serial 'acq_haushalt_hnr_seq' with this statement after
> loading:
>

What does "loading" mean, and why do you reset the sequence after loading?
(And as I can see you setting it to the value it most likely already has.)
My guess is that your problem lurks somewhere here as in certain
circumstances you reset it to an incorrect(previous) value.

> /* table: acq_haushalt */
> DO $$
> DECLARE
> max_id int;
> BEGIN
> if to_regclass('acq_haushalt') is not null then
> SELECT INTO max_id GREATEST(COALESCE(max(hnr), 0),0) + 1 FROM
> acq_haushalt;
> RAISE NOTICE '% % %', 'acq_haushalt', 'hnr', max_id ;
> EXECUTE 'ALTER SEQUENCE acq_haushalt_hnr_seq RESTART ' || max_id::text;
> end if;
> END $$ LANGUAGE plpgsql;
>
>
> Usage in Perl DBI to get the next value for acq_haushalt.hnr:
>
> if ( &getDBDriverName eq 'Pg') {
> $erg = &selectData("SELECT NEXTVAL('acq_haushalt_hnr_seq')",[]);
> if ($erg->{'CountData'} == 0) {
> $newhnr=1;
> }else{
>
> $newhnr=$erg->{'Data'}->[0]->{$erg->{'Fields'}->[0]->{'Name'}};
> }
> } else { .... code block for Sybase ...
>
> }
>
> But the serial was not incremented internally as we could see with
> 'psql' and so more than one row was build and inserted with the same
> number in $newhnr.
>
> What helped was using:
>
> $erg = &selectData("SELECT max(hnr) FROM acq_haushalt",[]);
> if ($erg->{'CountData'} == 0) {
> $newhnr=1;
> }else{
>
> $newhnr=$erg->{'Data'}->[0]->{$erg->{'Fields'}->[0]->{'Name'}}+1;
> }
>

Where do you want to insert this $newhnr?

> What we are doing wrong?
>
> Thanks
>
> matthias
> --
> Matthias Apitz, ✉ guru(at)unixarea(dot)de, http://www.unixarea.de/
> +49-176-38902045
> Public GnuPG key: http://www.unixarea.de/key.pub
> May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
>
>
>