Thread: PostgreSQL server does not increment a SERIAL internally

PostgreSQL server does not increment a SERIAL internally

From
Matthias Apitz
Date:
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!



Re: PostgreSQL server does not increment a SERIAL internally

From
Sándor Daku
Date:
Hi Matthias,

On Mon, 6 Jul 2020 at 11:43, Matthias Apitz <guru@unixarea.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@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!


Re: PostgreSQL server does not increment a SERIAL internally

From
Matthias Apitz
Date:
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!



Re: PostgreSQL server does not increment a SERIAL internally

From
Adrian Klaver
Date:
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