From: | Matthias Apitz <guru(at)unixarea(dot)de> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | PostgreSQL server does not increment a SERIAL internally |
Date: | 2020-07-06 09:43:36 |
Message-ID: | 20200706094336.GA318@sh4-5.1blu.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
/* 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;
}
What we are doing wrong?
Thanks
matthias
--
Matthias Apitz, ✉ guru(at)unixarea(dot)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!
From | Date | Subject | |
---|---|---|---|
Next Message | Niels Jespersen | 2020-07-06 11:19:38 | Using Postgres jdbc driver with Oracle SQL Developer |
Previous Message | Laurenz Albe | 2020-07-06 08:15:29 | Re: survey: psql syntax errors abort my transactions |