PostgreSQL server does not increment a SERIAL internally

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?

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