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(dot)klaver(at)aklaver(dot)com