On 31 Jul 2003, Mauri Sahlberg wrote: > Either we have found a bug in Postgres (which I seriously doubt) or we > are being stupid clever enough way to not notice it. > > We have five complex "transactions" that are executed thru pq++/c++ > interface. If we run them one by one everything goes fine. But if I > run them in parallel - in separate processes - all but the first one > claiming the lock for "ryhmalaiset"-table will fail. And they will > fail as soon as the first one is finished by trying to insert > duplicate row in the shared table. Incidentally this row would also be > the very first row they are trying to insert. They all run the same code > but with different data. > > Each transaction fails in the following insert: > LOOP > SELECT * INTO r FROM kelaaryhma(aToimiala, aKielikoodi, pKoodi); > > SELECT INTO pRyhmalaiset * FROM ryhmalaiset > WHERE > toimiala = aToimiala AND > ryhma = pKoodi AND > kuukausi = aKuukausi AND > vuosi = aVuosi; > > IF NOT FOUND THEN > aMessage:= pKoodi::VARCHAR(16)||''/''||aKoodi::VARCHAR(16); > RAISE NOTICE ''ins-ryhmalaiset %'', aMessage; > INSERT INTO ryhmalaiset > (toimiala, ryhma, jasen, kuukausi, vuosi, ajettu) > VALUES > (aToimiala, pKoodi, aKoodi, aKuukausi, aVuosi, > ''now''::timestamp); > END IF; > ... > > Where (toimiala, ryhma, kuukausi, jasen, vuosi) are primary index > fields. The "IF NOT FOUND" should ensure that no duplicate inserts are > attempted but somehow it just fails when two or more processess are > run.
The second transaction won't see the row inserted by the first transaction until it commits (at best). Both transactions can think there are no matching rows. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]