Bug #870: subselect returns more than one tuple where not possible - Mailing list pgsql-bugs

Bors Folgmann (boris@folgmann.de) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
subselect returns more than one tuple where not possible

Long Description
I'm using postgresql 7.2.2 on RedHat Linux 8.0.
Inside a transaction (PL/pgSQL function) I use a subselect in an if.
        IF (SELECT people - employed FROM planets WHERE pid=cur_pid) < crewCosts * order THEN
                RETURN false;
        END IF;

Sometimes when running concurrent updates, the function fails with ERROR:  More than one tuple returned by a subselect
usedas an expression. 

This is absolutley impossible since pid is SERIAL PRIMARY KEY of the planets table. How is it possible that the select
returnsmore than one tuple? Could it be a bug in MVCC? 

I can not understand the parse tree and plan, but I have added the debug output for you.

greetings,
  boris



Sample Code
Jan 10 20:37:03 ra postgres[7992]: [154] DEBUG:  Abfrage: SELECT  (SELECT people - employed FROM planets WHERE pid= $1
)<  $2  *  $3 
Jan 10 20:37:03 ra postgres[7992]: [155-1] DEBUG:  Parsebaum: { QUERY :command 1  :utility <> :resultRelation 0 :into
<>:isPortal false :isBinary false :isTemp false 
Jan 10 20:37:03 ra postgres[7992]: [155-2]  :hasAggs false :hasSubLinks true :rtable <> :jointree { FROMEXPR :fromlist
<>:quals <>} :rowMarks () :targetList ({ 
Jan 10 20:37:03 ra postgres[7992]: [155-3]  TARGETENTRY :resdom { RESDOM :resno 1 :restype 16 :restypmod -1 :resname
?column?:reskey 0 :reskeyop 0 :ressortgroupref 0 
Jan 10 20:37:03 ra postgres[7992]: [155-4]  :resjunk false } :expr { EXPR :typeOid 16  :opType op :oper { OPER :opno 97
:opid0 :opresulttype 16 } :args ({ SUBLINK 
Jan 10 20:37:03 ra postgres[7992]: [155-5]  :subLinkType 4 :useor false :lefthand <> :oper <> :subselect { QUERY
:command1  :utility <> :resultRelation 0 :into <> 
Jan 10 20:37:03 ra postgres[7992]: [155-6]  :isPortal false :isBinary false :isTemp false :hasAggs false :hasSubLinks
false:rtable ({ RTE :relname planets :relid 29883 
Jan 10 20:37:03 ra postgres[7992]: [155-7]  :subquery <> :alias <> :eref { ATTR :relname planets :attrs ( "pid"
"owner"  "name"   "distance"   "type"   "people" 
Jan 10 20:37:03 ra postgres[7992]: [155-8]  "employed"   "fuel"   "ore"   "ship"   "updated"   "tech_level"
"ship_res"  "people_res"   "fuel_res"   "ore_res"   "growth" 
Jan 10 20:37:03 ra postgres[7992]: [155-9]    "colonized" )} :inh true :inFromCl true :checkForRead true :checkForWrite
false:checkAsUser 0}) :jointree { FROMEXPR 
Jan 10 20:37:03 ra postgres[7992]: [155-10]  :fromlist ({ RANGETBLREF 1 }) :quals { EXPR :typeOid 16  :opType op :oper
{OPER :opno 96 :opid 0 :opresulttype 16 } :args ({ 
Jan 10 20:37:03 ra postgres[7992]: [155-11]  VAR :varno 1 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold1 :varoattno 1} { PARAM :paramkind 12 :paramid 1 
Jan 10 20:37:03 ra postgres[7992]: [155-12]  :paramname \<unnamed> :paramtype 23 })}} :rowMarks () :targetList ({
TARGETENTRY:resdom { RESDOM :resno 1 :restype 23 
Jan 10 20:37:03 ra postgres[7992]: [155-13]  :restypmod -1 :resname ?column? :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunkfalse } :expr { EXPR :typeOid 23  :opType op 
Jan 10 20:37:03 ra postgres[7992]: [155-14]  :oper { OPER :opno 555 :opid 0 :opresulttype 23 } :args ({ VAR :varno 1
:varattno6 :vartype 23 :vartypmod -1  :varlevelsup 0 
Jan 10 20:37:03 ra postgres[7992]: [155-15]  :varnoold 1 :varoattno 6} { VAR :varno 1 :varattno 7 :vartype 23
:vartypmod-1  :varlevelsup 0 :varnoold 1 :varoattno 7})}}) 
Jan 10 20:37:03 ra postgres[7992]: [155-16]  :groupClause <> :havingQual <> :distinctClause <> :sortClause <>
:limitOffset<> :limitCount <> :setOperations <> 
Jan 10 20:37:03 ra postgres[7992]: [155-17]  :resultRelations ()}} { EXPR :typeOid 23  :opType op :oper { OPER :opno
545:opid 0 :opresulttype 23 } :args ({ PARAM 
Jan 10 20:37:03 ra postgres[7992]: [155-18]  :paramkind 12 :paramid 2 :paramname \<unnamed> :paramtype 23 } { PARAM
:paramkind12 :paramid 3 :paramname \<unnamed> 
Jan 10 20:37:03 ra postgres[7992]: [155-19]  :paramtype 21 })})}}) :groupClause <> :havingQual <> :distinctClause <>
:sortClause<> :limitOffset <> :limitCount <> 
Jan 10 20:37:03 ra postgres[7992]: [155-20]  :setOperations <> :resultRelations ()}
Jan 10 20:37:03 ra postgres[7992]: [156-1] DEBUG:  Plan: { RESULT :startup_cost 0.00 :total_cost 0.01 :rows 1 :width 0
:qptargetlist({ TARGETENTRY :resdom { RESDOM :resno 
Jan 10 20:37:03 ra postgres[7992]: [156-2]  1 :restype 16 :restypmod -1 :resname ?column? :reskey 0 :reskeyop 0
:ressortgroupref0 :resjunk false } :expr { EXPR :typeOid 
Jan 10 20:37:03 ra postgres[7992]: [156-3]  16  :opType op :oper { OPER :opno 97 :opid 66 :opresulttype 16 } :args ({
PARAM:paramkind 15 :paramid 0 :paramname <> 
Jan 10 20:37:03 ra postgres[7992]: [156-4]  :paramtype 23 } { EXPR :typeOid 23  :opType op :oper { OPER :opno 545 :opid
171:opresulttype 23 } :args ({ PARAM :paramkind 12 
Jan 10 20:37:03 ra postgres[7992]: [156-5]  :paramid 2 :paramname \<unnamed> :paramtype 23 } { PARAM :paramkind 12
:paramid3 :paramname \<unnamed> :paramtype 21 })})}}) 
Jan 10 20:37:03 ra postgres[7992]: [156-6]  :qpqual <> :lefttree <> :righttree <> :extprm () :locprm ( 0) :initplan ({
SUBPLAN:plan { INDEXSCAN :startup_cost 0.00 
Jan 10 20:37:03 ra postgres[7992]: [156-7]  :total_cost 5.89 :rows 1 :width 8 :qptargetlist ({ TARGETENTRY :resdom {
RESDOM:resno 1 :restype 23 :restypmod -1 :resname 
Jan 10 20:37:03 ra postgres[7992]: [156-8]  ?column? :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr {
EXPR:typeOid 23  :opType op :oper { OPER :opno 555 
Jan 10 20:37:03 ra postgres[7992]: [156-9]  :opid 181 :opresulttype 23 } :args ({ VAR :varno 1 :varattno 6 :vartype 23
:vartypmod-1  :varlevelsup 0 :varnoold 1 :varoattno 
Jan 10 20:37:03 ra postgres[7992]: [156-10]  6} { VAR :varno 1 :varattno 7 :vartype 23 :vartypmod -1  :varlevelsup 0
:varnoold1 :varoattno 7})}}) :qpqual <> :lefttree <> 
Jan 10 20:37:03 ra postgres[7992]: [156-11]  :righttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 1
:indxid( 29885) :indxqual (({ EXPR :typeOid 16  :opType 
Jan 10 20:37:03 ra postgres[7992]: [156-12]  op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1
:varattno1 :vartype 23 :vartypmod -1  :varlevelsup 
Jan 10 20:37:03 ra postgres[7992]: [156-13]  0 :varnoold 1 :varoattno 1} { PARAM :paramkind 12 :paramid 1 :paramname
\<unnamed>:paramtype 23 })})) :indxqualorig (({ EXPR 
Jan 10 20:37:03 ra postgres[7992]: [156-14]  :typeOid 16  :opType op :oper { OPER :opno 96 :opid 65 :opresulttype 16 }
:args({ VAR :varno 1 :varattno 1 :vartype 23 
Jan 10 20:37:03 ra postgres[7992]: [156-15]  :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 1} { PARAM :paramkind
12:paramid 1 :paramname \<unnamed> :paramtype 23 
Jan 10 20:37:03 ra postgres[7992]: [156-16]  })})) :indxorderdir 1 } :planid 0 :rtable ({ RTE :relname planets :relid
29883 :subquery <> :alias <> :eref { ATTR :relname 
Jan 10 20:37:03 ra postgres[7992]: [156-17]  planets :attrs ( "pid"   "owner"   "name"   "distance"   "type"   "people"
 "employed"   "fuel"   "ore"   "ship"   "updated" 
Jan 10 20:37:03 ra postgres[7992]: [156-18]  "tech_level"   "ship_res"   "people_res"   "fuel_res"   "ore_res"
"growth"  "colonized" )} :inh false :inFromCl true 
Jan 10 20:37:03 ra postgres[7992]: [156-19]  :checkForRead true :checkForWrite false :checkAsUser 0}) :setprm ( 0)
:parprm() :slink { SUBLINK :subLinkType 4 :useor false 
Jan 10 20:37:03 ra postgres[7992]: [156-20]  :lefthand <> :oper <> :subselect <>}}) :nprm 1  :resconstantqual <>}
Jan 10 20:37:03 ra postgres[7992]: [157] ERROR:  More than one tuple returned by a subselect used as an expression.
Jan 10 20:37:03 ra postgres[7992]: [158] NOTICE:  Error occurred while executing PL/pgSQL function order_building
Jan 10 20:37:03 ra postgres[7992]: [159] NOTICE:  line 27 at if
Jan 10 20:37:03 ra postgres[7992]: [160] DEBUG:  AbortCurrentTransaction


No file was uploaded with this report