PostgreSQL server does not increment a SERIAL internally

[Matthias Apitz <guru@xxxxxxxxxxx>]

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:

/* 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;
     }

What we are doing wrong?

Thanks

	matthias
-- 
Matthias Apitz, ✉ guru@xxxxxxxxxxx, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!