Обсуждение: PostgreSQL server does not increment a SERIAL internally

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@unixarea.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!

El día Montag, Juli 06, 2020 a las 01:58:04 +0200, Sándor Daku escribió:

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

Hello Sándor,

All the tables (~400) are loaded from an export in CSV like format done
from the same Sybase ASE database with this commands for any table:

08:08:00 TRUNCATE TABLE acq_haushalt ;
08:08:00 TRUNCATE TABLE
08:08:01 \COPY acq_haushalt FROM '/home/sisis/guru/sisisDBsrap14/CC-acq_haushalt.load' WITH ( NULL '' , DELIMITER '|'
)
08:08:01 COPY 2862

and afterwards we have to adjust the serials to the highest used value
with the shown command:
> 
> 
> > /* 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;

It's output (for this table) was:

NOTICE:  acq_haushalt hnr 3183

which is correct because it matches the highest value +1 of 'acq_haushalt.hnr'. 

    matthias

-- 
Matthias Apitz, ✉ guru@unixarea.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!

On 7/6/20 2:43 AM, Matthias Apitz 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   */

Is this the complete definition, I'm not seeing 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;

The GREATEST() is redundant, the COALSESCE is going to yield either 0 or 
a number > 0.

>    RAISE NOTICE '% % %', 'acq_haushalt', 'hnr', max_id ;
>    EXECUTE 'ALTER SEQUENCE acq_haushalt_hnr_seq RESTART ' || max_id::text;

You don't need to cast max_id.

>   end if;
> END $$ LANGUAGE plpgsql;
> 


So what are you trying to do with the code below, create a new row or 
something else?

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com