Bug #870: subselect returns more than one tuple where not possible

Bors Folgmann (boris(at)folgmann(dot)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 used as an expression.

This is absolutley impossible since pid is SERIAL PRIMARY KEY of the planets table. How is it possible that the select returns more 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 :opid 0 :opresulttype 16 } :args ({ SUBLINK
Jan 10 20:37:03 ra postgres[7992]: [155-5] :subLinkType 4 :useor false :lefthand <> :oper <> :subselect { QUERY :command 1 :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 :varnoold 1 :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 :resjunk false } :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 :varattno 6 :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 :paramkind 12 :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 :ressortgroupref 0 :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 :paramid 3 :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 :varnoold 1 :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 :varattno 1 :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