Hi Matthias,
On Mon, 6 Jul 2020 at 11:43, Matthias Apitz <guru@xxxxxxxxxxx> 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@xxxxxxxxxxx, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
