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@xxxxxxxxxxx
