Re: PostgreSQL server does not increment a SERIAL internally

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