Skip site navigation (1) Skip section navigation (2)

Select on pkey returned more than one row

From: Szűcs Gábor <surrano(at)gmail(dot)com>
To: "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org>
Subject: Select on pkey returned more than one row
Date: 2005-06-28 13:05:12
Message-ID: 42C14B08.5060809@gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Dear Gurus,

As always, please point me to the right resource if this issue is already 
addressed.

Version: 7.4.6, Debian "Woody" linux.
Source: max func args increased to 64, but I doubt it matters.

In a pl/pgsql function, called from an AFTER trigger, I receive this message:

   ERROR:  query "SELECT  mennyiseg FROM muvelet_cikk WHERE muvelet= $1  AND
   cikk= $2  AND minoseg= $3  AND mozgasnem= $4 " returned more than one row

Whereas the fields in the WHERE clause are exactly the pkey fields for that 
table:

   "muvelet_cikk_pkey" primary key, btree (muvelet, cikk, minoseg, mozgasnem)

I'm not sure but this may be because of a race condition (one backend 
frequently inserting rows while another backend firing a trigger that causes 
a mass UPDATE on the same tuples. Indeed, we tend to receive deadlocks.)

Are pkey constraints immediate or deferred? May it be the cause of the 
problem? If so, what are the risks of changing the constraint to immediate, 
if it can be done at all?

The table definition in question is as follows. Double-checked, and indeed, 
no rows violate the pkey (and I assume no rows violate it in the transaction 
that throws the error, either -- but indeed, rows are updated or inserted 
into this table from both abovementioned backends)

I'm able to send more information if needed.

TIA,
--
G.

[local]:tir=# \d muvelet_cikk
           Table "public.muvelet_cikk"
   Column   |     Type      |     Modifiers
-----------+---------------+--------------------
  muvelet   | integer       | not null
  cikk      | integer       | not null
  mozgasnem | integer       | not null
  mennyiseg | numeric(14,4) |
  me        | integer       |
  kcikk     | integer       |
  minoseg   | integer       | not null default 1
Indexes:
     "muvelet_cikk_pkey" primary key, btree (muvelet, cikk, minoseg, mozgasnem)
     "muvelet_cikk_cikk_muvelet" btree (cikk, muvelet)
     "muvelet_cikk_mozgasnem_cikk" btree (mozgasnem, cikk)
     "muvelet_cikk_mozgasnem_muvelet" btree (mozgasnem, muvelet)
Check constraints:
     "mennyiseg_me" CHECK ((mennyiseg IS NULL) = (me IS NULL))
Foreign-key constraints:
     "$2" FOREIGN KEY (cikk) REFERENCES cikk(az) ON UPDATE CASCADE
     "$6" FOREIGN KEY (kcikk) REFERENCES kulso_cikk(az)
     "$5" FOREIGN KEY (minoseg) REFERENCES minoseg(az)
     "$1" FOREIGN KEY (muvelet) REFERENCES muvelet(az) ON UPDATE CASCADE ON 
DELETE CASCADE
     "$4" FOREIGN KEY (me) REFERENCES mennyisegi_egyseg(az)
     "$3" FOREIGN KEY (mozgasnem) REFERENCES mozgasnem(az)
Triggers:
     muvelet_cikk_ad AFTER DELETE ON muvelet_cikk FOR EACH ROW EXECUTE 
PROCEDURE muvelet_cikk_ad()
     muvelet_cikk_aiud AFTER INSERT OR DELETE OR UPDATE ON muvelet_cikk FOR 
EACH ROW EXECUTE PROCEDURE muvelet_cikk_aiud()
     muvelet_cikk_biu BEFORE INSERT OR UPDATE ON muvelet_cikk FOR EACH ROW 
EXECUTE PROCEDURE muvelet_cikk_biu()
     muvelet_cikk_noty AFTER INSERT OR DELETE OR UPDATE ON muvelet_cikk FOR 
EACH ROW EXECUTE PROCEDURE muvelet_cikk_noty()

Responses

pgsql-bugs by date

Next:From: Bobi IvanovDate: 2005-06-28 13:07:33
Subject: Re: row number -1 is out of range 0..-1
Previous:From: Michael FuhrDate: 2005-06-28 12:58:44
Subject: Re: row number -1 is out of range 0..-1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group