Thread: Select on pkey returned more than one row

Select on pkey returned more than one row

From
Szűcs Gábor
Date:
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
nullmozgasnem | 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)
REFERENCESkulso_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()


Re: Select on pkey returned more than one row

From
Szűcs Gábor
Date:
Dear Gurus,

I'm not sure if this mail reached the list members (at least, it reached the
archive). If there's some formal problem that prevents delivery, please
point me to the appropriate form's description.

Yours,
--
G.


On 2005.06.28. 15:05, Szűcs Gábor wrote:
> 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()
>



Re: Select on pkey returned more than one row

From
Tom Lane
Date:
Szűcs Gábor <surrano@gmail.com> writes:
>> Version: 7.4.6, Debian "Woody" linux.
>> Source: max func args increased to 64, but I doubt it matters.

Well, it might, if you were sloppy about making sure that the software
had been completely rebuilt (including plpgsql and any other add-on
libraries you might be using).  You generally want to do a "make clean"
and full rebuild after modifying any configuration values.
        regards, tom lane