Thread: Possible bug in update?

Possible bug in update?

From
Markus Kräutner
Date:
Hi,

My name is Markus Kräutner, I am using PostgreSQL to build a distributed
biological database as part of my diploma thesis.

I hope this is the right forum for my question.

At the end of several bulk inserts the following statements get executed:

begin
update pom_c2996785 set p3=3268837857008 where p3=34804837857008
update pom_c2996795 set p3=3268837857008 where p3=34804837857008
update pom_c2996804 set p3=3268837857008 where p3=34804837857008
commit

When I check the database the last update has not been executed. Manually
executing it in psql does work.
I verified this with 7.3.2, 7.3.4 and 7.4beta2 and have no clue what to do
next. As you can see in the attached logfile it seems the command is
correctly sent to the postmaster, but just gets not executed.
I don't really understand the logfile, so I am lost...

Please help me :)

Thank you in advance, Markus

-------------- DDL -------------------------------------------
CREATE TABLE pom_c2996785 (   p1 bigint NOT NULL,   p2 bigint NOT NULL,   p3 bigint NOT NULL,   p4 smallint DEFAULT 0
NOTNULL,   p5 text DEFAULT ''::text,   p6 text DEFAULT ''::text,   p7 text DEFAULT ''::text,   p8 text DEFAULT
''::text,  p9 text DEFAULT ''::text 
);

CREATE TABLE pom_c2996795 (   p1 bigint NOT NULL,   p2 bigint NOT NULL,   p3 bigint NOT NULL,   p4 smallint DEFAULT 0
NOTNULL,   p5 text DEFAULT ''::text,   p6 text DEFAULT ''::text,   p7 text DEFAULT ''::text,   p8 text DEFAULT ''::text 
);

CREATE TABLE pom_c2996804 (   p1 bigint NOT NULL,   p2 bigint NOT NULL,   p3 bigint NOT NULL,   p4 smallint DEFAULT 0
NOTNULL,   p5 bigint DEFAULT 0,   p6 bigint DEFAULT 0,   p7 bigint DEFAULT 0,   p8 bigint DEFAULT 0,   p9 integer
DEFAULT-1 
);


---------------- excerpt from logfile -d 5 ------------------
DEBUG:  ProcessUtility
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
LOG:  query: begin
DEBUG:  parse tree:
DETAIL:  {QUERY :commandType 5 :querySource 0 :canSetTag true :utilityStmt ?
:resultRelation 0 :into <> :hasAggs false :hasSubLinks false :rtable <>
:jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <>
:distinctClause <> :sortClause <> :limitOffset <> :limitCount <>
:setOperations <> :resultRelations ()}

DEBUG:  rewritten parse tree:
DETAIL:  ({QUERY :commandType 5 :querySource 0 :canSetTag true :utilityStmt ?
:resultRelation 0 :into <> :hasAggs false :hasSubLinks false :rtable <>
:jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <>
:distinctClause <> :sortClause <> :limitOffset <> :limitCount <>
:setOperations <> :resultRelations ()})

DEBUG:  ProcessUtility
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
LOG:  query: update pom_c2996785 set p3=3268837857008 where p3=34804837857008
DEBUG:  parse tree:
DETAIL:  {QUERY :commandType 2 :querySource 0 :canSetTag true :utilityStmt <>
:resultRelation 1 :into <> :hasAggs false :hasSubLinks false :rtable ({RTE
:alias <> :eref {ALIAS :aliasname pom_c2996785 :colnames ("p1" "p2" "p3" "p4"
"p5" "p6" "p7" "p8" "p9")} :rtekind 0 :relid 17731 :inh true :inFromCl false
:checkForRead true :checkForWrite true :checkAsUser 0}) :jointree {FROMEXPR
:fromlist ({RANGETBLREF :rtindex 1}) :quals {OPEXPR :opno 410 :opfuncid 0
:opresulttype 16 :opretset false :args ({VAR :varno 1 :varattno 3 :vartype 20
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3} {CONST :consttype 20
:constlen 8 :constbyval false :constisnull false :constvalue 8 [ -16 54 -1 -95
-89 31 0 0 ]})}} :rowMarks () :targetList ({TARGETENTRY :resdom {RESDOM :resno
3 :restype 20 :restypmod -1 :resname p3 :ressortgroupref 0 :resorigtbl 0
:resorigcol 0 :resjunk false} :expr {CONST :consttype 20 :constlen 8
:constbyval false :constisnull false :constvalue 8 [ -16 86 -21 21 -7 2 0 0
]}}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <>
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}

DEBUG:  rewritten parse tree:
DETAIL:  ({QUERY :commandType 2 :querySource 0 :canSetTag true :utilityStmt <>
:resultRelation 1 :into <> :hasAggs false :hasSubLinks false :rtable ({RTE
:alias <> :eref {ALIAS :aliasname pom_c2996785 :colnames ("p1" "p2" "p3" "p4"
"p5" "p6" "p7" "p8" "p9")} :rtekind 0 :relid 17731 :inh true :inFromCl false
:checkForRead true :checkForWrite true :checkAsUser 0}) :jointree {FROMEXPR
:fromlist ({RANGETBLREF :rtindex 1}) :quals {OPEXPR :opno 410 :opfuncid 0
:opresulttype 16 :opretset false :args ({VAR :varno 1 :varattno 3 :vartype 20
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3} {CONST :consttype 20
:constlen 8 :constbyval false :constisnull false :constvalue 8 [ -16 54 -1 -95
-89 31 0 0 ]})}} :rowMarks () :targetList ({TARGETENTRY :resdom {RESDOM :resno
3 :restype 20 :restypmod -1 :resname p3 :ressortgroupref 0 :resorigtbl 0
:resorigcol 0 :resjunk false} :expr {CONST :consttype 20 :constlen 8
:constbyval false :constisnull false :constvalue 8 [ -16 86 -21 21 -7 2 0 0
]}}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <>
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()})


DEBUG:  plan:
DETAIL:  {INDEXSCAN :startup_cost 0.00 :total_cost 18.07 :plan_rows 6
:plan_width 184
:targetlist ({TARGETENTRY :resdom {RESDOM :resno 1 :restype 20 :restypmod -1
:resname p1 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}
:expr {VAR :varno 1 :varattno 1 :vartype 20 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 1}} {TARGETENTRY :resdom {RESDOM :resno 2 :restype 20
:restypmod -1 :resname p2 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0
:resjunk false} :expr {VAR :varno 1 :varattno 2 :vartype 20 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 2}} {TARGETENTRY :resdom {RESDOM :resno
3 :restype 20 :restypmod -1 :resname p3 :ressortgroupref 0 :resorigtbl 0
:resorigcol 0 :resjunk false} :expr {CONST :consttype 20 :constlen 8
:constbyval false :constisnull false :constvalue 8 [ -16 86 -21 21 -7 2 0 0
]}} {TARGETENTRY :resdom {RESDOM :resno 4 :restype 21 :restypmod -1 :resname
p4 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false} :expr {VAR
:varno 1 :varattno 4 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 4}} {TARGETENTRY :resdom {RESDOM :resno 5 :restype 25 :restypmod -1
:resname p5 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}
:expr {VAR :varno 1 :varattno 5 :vartype 25 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 5}} {TARGETENTRY :resdom {RESDOM :resno 6 :restype 25
:restypmod -1 :resname p6 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0
:resjunk false} :expr {VAR :varno 1 :varattno 6 :vartype 25 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 6}} {TARGETENTRY :resdom {RESDOM :resno
7 :restype 25 :restypmod -1 :resname p7 :ressortgroupref 0 :resorigtbl 0
:resorigcol 0 :resjunk false} :expr {VAR :varno 1 :varattno 7 :vartype 25
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7}} {TARGETENTRY :resdom
{RESDOM :resno 8 :restype 25 :restypmod -1 :resname p8 :ressortgroupref 0
:resorigtbl 0 :resorigcol 0 :resjunk false} :expr {VAR :varno 1 :varattno 8
:vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8}}
{TARGETENTRY :resdom {RESDOM :resno 9 :restype 25 :restypmod -1 :resname p9
:ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false} :expr {VAR
:varno 1 :varattno 9 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 9}} {TARGETENTRY :resdom {RESDOM :resno 10 :restype 27 :restypmod
-1 :resname ctid :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk true}
:expr {VAR :varno 1 :varattno -1 :vartype 27 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno -1}}) :qual <> :lefttree <> :righttree <> :initPlan <>
:extParam () :allParam () :nParamExec 0 :scanrelid 1 :indxid ( 17738)
:indxqual (({OPEXPR :opno 410 :opfuncid 467 :opresulttype 16 :opretset false
:args ({VAR :varno 1 :varattno 1 :vartype 20 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 3} {CONST :consttype 20 :constlen 8 :constbyval false
:constisnull false :constvalue 8 [ -16 54 -1 -95 -89 31 0 0 ]})}))
:indxqualorig (({OPEXPR :opno 410 :opfuncid 467 :opresulttype 16 :opretset
false :args ({VAR :varno 1 :varattno 3 :vartype 20 :vartypmod -1 :varlevelsup
0 :varnoold 1 :varoattno 3} {CONST :consttype 20 :constlen 8 :constbyval false
:constisnull false :constvalue 8 [ -16 54 -1 -95 -89 31 0 0 ]})}))
:indxorderdir 0}

DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
LOG:  query: update pom_c2996795 set p3=3268837857008 where p3=34804837857008
DEBUG:  parse tree:
DETAIL:  {QUERY :commandType 2 :querySource 0 :canSetTag true :utilityStmt <>
:resultRelation 1 :into <> :hasAggs false :hasSubLinks false :rtable ({RTE
:alias <> :eref {ALIAS :aliasname pom_c2996795 :colnames ("p1" "p2" "p3" "p4"
"p5" "p6" "p7" "p8")} :rtekind 0 :relid 17763 :inh true :inFromCl false
:checkForRead true :checkForWrite true :checkAsUser 0}) :jointree {FROMEXPR
:fromlist ({RANGETBLREF :rtindex 1}) :quals {OPEXPR :opno 410 :opfuncid 0
:opresulttype 16 :opretset false :args ({VAR :varno 1 :varattno 3 :vartype 20
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3} {CONST :consttype 20
:constlen 8 :constbyval false :constisnull false :constvalue 8 [ -16 54 -1 -95
-89 31 0 0 ]})}} :rowMarks () :targetList ({TARGETENTRY :resdom {RESDOM :resno
3 :restype 20 :restypmod -1 :resname p3 :ressortgroupref 0 :resorigtbl 0
:resorigcol 0 :resjunk false} :expr {CONST :consttype 20 :constlen 8
:constbyval false :constisnull false :constvalue 8 [ -16 86 -21 21 -7 2 0 0
]}}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <>
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}

DEBUG:  rewritten parse tree:
DETAIL:  ({QUERY :commandType 2 :querySource 0 :canSetTag true :utilityStmt <>
:resultRelation 1 :into <> :hasAggs false :hasSubLinks false :rtable ({RTE
:alias <> :eref {ALIAS :aliasname pom_c2996795 :colnames ("p1" "p2" "p3" "p4"
"p5" "p6" "p7" "p8")} :rtekind 0 :relid 17763 :inh true :inFromCl false
:checkForRead true :checkForWrite true :checkAsUser 0}) :jointree {FROMEXPR
:fromlist ({RANGETBLREF :rtindex 1}) :quals {OPEXPR :opno 410 :opfuncid 0
:opresulttype 16 :opretset false :args ({VAR :varno 1 :varattno 3 :vartype 20
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3} {CONST :consttype 20
:constlen 8 :constbyval false :constisnull false :constvalue 8 [ -16 54 -1 -95
-89 31 0 0 ]})}} :rowMarks () :targetList ({TARGETENTRY :resdom {RESDOM :resno
3 :restype 20 :restypmod -1 :resname p3 :ressortgroupref 0 :resorigtbl 0
:resorigcol 0 :resjunk false} :expr {CONST :consttype 20 :constlen 8
:constbyval false :constisnull false :constvalue 8 [ -16 86 -21 21 -7 2 0 0
]}}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <>
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()})
DEBUG:  plan:
DETAIL:  {INDEXSCAN :startup_cost 0.00 :total_cost 18.07 :plan_rows 6
:plan_width 152
:targetlist ({TARGETENTRY :resdom {RESDOM :resno 1 :restype 20 :restypmod -1
:resname p1 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}
:expr {VAR :varno 1 :varattno 1 :vartype 20 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 1}} {TARGETENTRY :resdom {RESDOM :resno 2 :restype 20
:restypmod -1 :resname p2 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0
:resjunk false} :expr {VAR :varno 1 :varattno 2 :vartype 20 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 2}} {TARGETENTRY :resdom {RESDOM :resno
3 :restype 20 :restypmod -1 :resname p3 :ressortgroupref 0 :resorigtbl 0
:resorigcol 0 :resjunk false} :expr {CONST :consttype 20 :constlen 8
:constbyval false :constisnull false :constvalue 8 [ -16 86 -21 21 -7 2 0 0
]}} {TARGETENTRY :resdom {RESDOM :resno 4 :restype 21 :restypmod -1 :resname
p4 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false} :expr {VAR
:varno 1 :varattno 4 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 4}} {TARGETENTRY :resdom {RESDOM :resno 5 :restype 25 :restypmod -1
:resname p5 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}
:expr {VAR :varno 1 :varattno 5 :vartype 25 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 5}} {TARGETENTRY :resdom {RESDOM :resno 6 :restype 25
:restypmod -1 :resname p6 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0
:resjunk false} :expr {VAR :varno 1 :varattno 6 :vartype 25 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 6}} {TARGETENTRY :resdom {RESDOM :resno
7 :restype 25 :restypmod -1 :resname p7 :ressortgroupref 0 :resorigtbl 0
:resorigcol 0 :resjunk false} :expr {VAR :varno 1 :varattno 7 :vartype 25
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7}} {TARGETENTRY :resdom
{RESDOM :resno 8 :restype 25 :restypmod -1 :resname p8 :ressortgroupref 0
:resorigtbl 0 :resorigcol 0 :resjunk false} :expr {VAR :varno 1 :varattno 8
:vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8}}
{TARGETENTRY :resdom {RESDOM :resno 9 :restype 27 :restypmod -1 :resname ctid
:ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk true} :expr {VAR
:varno 1 :varattno -1 :vartype 27 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno -1}}) :qual <> :lefttree <> :righttree <> :initPlan <> :extParam ()
:allParam () :nParamExec 0 :scanrelid 1 :indxid ( 17770) :indxqual (({OPEXPR
:opno 410 :opfuncid 467 :opresulttype 16 :opretset false :args ({VAR :varno 1
:varattno 1 :vartype 20 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3}
{CONST :consttype 20 :constlen 8 :constbyval false :constisnull false
:constvalue 8 [ -16 54 -1 -95 -89 31 0 0 ]})})) :indxqualorig (({OPEXPR :opno
410 :opfuncid 467 :opresulttype 16 :opretset false :args ({VAR :varno 1
:varattno 3 :vartype 20 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3}
{CONST :consttype 20 :constlen 8 :constbyval false :constisnull false
:constvalue 8 [ -16 54 -1 -95 -89 31 0 0 ]})})) :indxorderdir 0}
DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
LOG:  query: update pom_c2996804 set p3=3268837857008 where p3=34804837857008
DEBUG:  parse tree:
DETAIL:  {QUERY :commandType 2 :querySource 0 :canSetTag true :utilityStmt <>
:resultRelation 1 :into <> :hasAggs false :hasSubLinks false :rtable ({RTE
:alias <> :eref {ALIAS :aliasname pom_c2996804 :colnames ("p1" "p2" "p3" "p4"
"p5" "p6" "p7" "p8" "p9")} :rtekind 0 :relid 17792 :inh true :inFromCl false
:checkForRead true :checkForWrite true :checkAsUser 0}) :jointree {FROMEXPR
:fromlist ({RANGETBLREF :rtindex 1}) :quals {OPEXPR :opno 410 :opfuncid 0
:opresulttype 16 :opretset false :args ({VAR :varno 1 :varattno 3 :vartype 20
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3} {CONST :consttype 20
:constlen 8 :constbyval false :constisnull false :constvalue 8 [ -16 54 -1 -95
-89 31 0 0 ]})}} :rowMarks () :targetList ({TARGETENTRY :resdom {RESDOM :resno
3 :restype 20 :restypmod -1 :resname p3 :ressortgroupref 0 :resorigtbl 0
:resorigcol 0 :resjunk false} :expr {CONST :consttype 20 :constlen 8
:constbyval false :constisnull false :constvalue 8 [ -16 86 -21 21 -7 2 0 0
]}}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <>
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()}

DEBUG:  rewritten parse tree:
DETAIL:  ({QUERY :commandType 2 :querySource 0 :canSetTag true :utilityStmt <>
:resultRelation 1 :into <> :hasAggs false :hasSubLinks false :rtable ({RTE
:alias <> :eref {ALIAS :aliasname pom_c2996804 :colnames ("p1" "p2" "p3" "p4"
"p5" "p6" "p7" "p8" "p9")} :rtekind 0 :relid 17792 :inh true :inFromCl false
:checkForRead true :checkForWrite true :checkAsUser 0}) :jointree {FROMEXPR
:fromlist ({RANGETBLREF :rtindex 1}) :quals {OPEXPR :opno 410 :opfuncid 0
:opresulttype 16 :opretset false :args ({VAR :varno 1 :varattno 3 :vartype 20
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 3} {CONST :consttype 20
:constlen 8 :constbyval false :constisnull false :constvalue 8 [ -16 54 -1 -95
-89 31 0 0 ]})}} :rowMarks () :targetList ({TARGETENTRY :resdom {RESDOM :resno
3 :restype 20 :restypmod -1 :resname p3 :ressortgroupref 0 :resorigtbl 0
:resorigcol 0 :resjunk false} :expr {CONST :consttype 20 :constlen 8
:constbyval false :constisnull false :constvalue 8 [ -16 86 -21 21 -7 2 0 0
]}}) :groupClause <> :havingQual <> :distinctClause <> :sortClause <>
:limitOffset <> :limitCount <> :setOperations <> :resultRelations ()})
DEBUG:  plan:
DETAIL:  {INDEXSCAN :startup_cost 0.00 :total_cost 18.07 :plan_rows 6
:plan_width 60
:targetlist ({TARGETENTRY :resdom {RESDOM :resno 1 :restype 20 :restypmod -1
:resname p1 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}
:expr {VAR :varno 1 :varattno 1 :vartype 20 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 1}} {TARGETENTRY :resdom {RESDOM :resno 2 :restype 20
:restypmod -1 :resname p2 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0
:resjunk false} :expr {VAR :varno 1 :varattno 2 :vartype 20 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 2}} {TARGETENTRY :resdom {RESDOM :resno
3 :restype 20 :restypmod -1 :resname p3 :ressortgroupref 0 :resorigtbl 0
:resorigcol 0 :resjunk false} :expr {CONST :consttype 20 :constlen 8
:constbyval false :constisnull false :constvalue 8 [ -16 86 -21 21 -7 2 0 0
]}} {TARGETENTRY :resdom {RESDOM :resno 4 :restype 21 :restypmod -1 :resname
p4 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false} :expr {VAR
:varno 1 :varattno 4 :vartype 21 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 4}} {TARGETENTRY :resdom {RESDOM :resno 5 :restype 20 :restypmod -1
:resname p5 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false}
:expr {VAR :varno 1 :varattno 5 :vartype 20 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 5}} {TARGETENTRY :resdom {RESDOM :resno 6 :restype 20
:restypmod -1 :resname p6 :ressortgroupref 0 :resorigtbl 0 :resorigcol 0
:resjunk false} :expr {VAR :varno 1 :varattno 6 :vartype 20 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 6}} {TARGETENTRY :resdom {RESDOM :resno
7 :restype 20 :restypmod -1 :resname p7 :ressortgroupref 0 :resorigtbl 0
:resorigcol 0 :resjunk false} :expr {VAR :varno 1 :varattno 7 :vartype 20
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7}} {TARGETENTRY :resdom
{RESDOM :resno 8 :restype 20 :restypmod -1 :resname p8 :ressortgroupref 0
:resorigtbl 0 :resorigcol 0 :resjunk false} :expr {VAR :varno 1 :varattno 8
:vartype 20 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 8}}
{TARGETENTRY :resdom {RESDOM :resno 9 :restype 23 :restypmod -1 :resname p9
:ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk false} :expr {VAR
:varno 1 :varattno 9 :vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1
:varoattno 9}} {TARGETENTRY :resdom {RESDOM :resno 10 :restype 27 :restypmod
-1 :resname ctid :ressortgroupref 0 :resorigtbl 0 :resorigcol 0 :resjunk true}
:expr {VAR :varno 1 :varattno -1 :vartype 27 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno -1}}) :qual <> :lefttree <> :righttree <> :initPlan <>
:extParam () :allParam () :nParamExec 0 :scanrelid 1 :indxid ( 17799)
:indxqual (({OPEXPR :opno 410 :opfuncid 467 :opresulttype 16 :opretset false
:args ({VAR :varno 1 :varattno 1 :vartype 20 :vartypmod -1 :varlevelsup 0
:varnoold 1 :varoattno 3} {CONST :consttype 20 :constlen 8 :constbyval false
:constisnull false :constvalue 8 [ -16 54 -1 -95 -89 31 0 0 ]})}))
:indxqualorig (({OPEXPR :opno 410 :opfuncid 467 :opresulttype 16 :opretset
false :args ({VAR :varno 1 :varattno 3 :vartype 20 :vartypmod -1 :varlevelsup
0 :varnoold 1 :varoattno 3} {CONST :consttype 20 :constlen 8 :constbyval false
:constisnull false :constvalue 8 [ -16 54 -1 -95 -89 31 0 0 ]})}))
:indxorderdir 0}

DEBUG:  ProcessQuery
DEBUG:  CommitTransactionCommand
DEBUG:  StartTransactionCommand
LOG:  query: commit
DEBUG:  parse tree:
DETAIL:  {QUERY :commandType 5 :querySource 0 :canSetTag true :utilityStmt ?
:resultRelation 0 :into <> :hasAggs false :hasSubLinks false :rtable <>
:jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <>
:distinctClause <> :sortClause <> :limitOffset <> :limitCount <>
:setOperations <> :resultRelations ()}

DEBUG:  rewritten parse tree:
DETAIL:  ({QUERY :commandType 5 :querySource 0 :canSetTag true :utilityStmt ?
:resultRelation 0 :into <> :hasAggs false :hasSubLinks false :rtable <>
:jointree <> :rowMarks () :targetList <> :groupClause <> :havingQual <>
:distinctClause <> :sortClause <> :limitOffset <> :limitCount <>
:setOperations <> :resultRelations ()})









Re: Possible bug in update?

From
Jim Mercer
Date:
On Wed, Aug 27, 2003 at 09:17:28PM +0200, Markus Kr?utner wrote:
> At the end of several bulk inserts the following statements get executed:
> 
> begin
> update pom_c2996785 set p3=3268837857008 where p3=34804837857008
> update pom_c2996795 set p3=3268837857008 where p3=34804837857008
> update pom_c2996804 set p3=3268837857008 where p3=34804837857008
> commit

not overly clear from the debug statements, but are all the above lines
also ended with a semi-colon?

-- 
[ Jim Mercer        jim@reptiles.org         +1 416 410-5633 ]
[          I want to live forever, or die trying.            ]


Re: Possible bug in update?

From
Tom Lane
Date:
Markus Kräutner <kraeutne@in.tum.de> writes:
> At the end of several bulk inserts the following statements get executed:

> begin
> update pom_c2996785 set p3=3268837857008 where p3=34804837857008
> update pom_c2996795 set p3=3268837857008 where p3=34804837857008
> update pom_c2996804 set p3=3268837857008 where p3=34804837857008
> commit

> When I check the database the last update has not been executed. Manually 
> executing it in psql does work.

... ergo, the bug is not in the backend, but in whatever non-psql client
software you are using.  Since you haven't mentioned what that is,
further speculation is difficult ...
        regards, tom lane


suspicous looking code in copy.c

From
Dave Cramer
Date:
This just doesn't look right.

line 364               case COPY_NEW_FE:                     while (datasize > 0 && !fe_eof)            ....

line 408        datasize =- avail;


shouldn't it be datasize -= avail ?

Dave            
-- 
Dave Cramer <dave@fastcrypt.com>
fastcrypt



Re: suspicous looking code in copy.c

From
Andreas Pflug
Date:
Dave Cramer wrote:

>This just doesn't look right.
>
>line 364
>                case COPY_NEW_FE:
> 
>                      while (datasize > 0 && !fe_eof)
>             ....
>
>line 408        datasize =- avail;
>
>
>shouldn't it be datasize -= avail ?
>  
>
AFAIR this is a really outdated K&R style of -=  . Compilers *should* 
recognize this (and will throw an ambiguity error if there's no space 
after =- ), but it's better to use the 'newer' style.

Regards,
Andreas




Re: Possible bug in update?

From
Tom Lane
Date:
Markus Kräutner <kraeutne@in.tum.de> writes:
> I managed to construct a sample script which also fails in psql. It seems to 
> be the hash index. If I leave out the "create index" line or use btree 
> instead everything works fine.
> I will use btree now :)

Good idea ;-).  I'll look into it, but I'm not at all surprised to hear
of bugs in the hash-index code.  It's not gotten near as much usage as
btree.
        regards, tom lane


Re: suspicous looking code in copy.c

From
Dave Cramer
Date:
Well, it get's worse, I think after bruce ran pgindent it was turned
into

datasize = -avail;

Dave
On Thu, 2003-08-28 at 08:19, Andreas Pflug wrote:
> Dave Cramer wrote:
> 
> >This just doesn't look right.
> >
> >line 364
> >                case COPY_NEW_FE:
> > 
> >                      while (datasize > 0 && !fe_eof)
> >             ....
> >
> >line 408        datasize =- avail;
> >
> >
> >shouldn't it be datasize -= avail ?
> >  
> >
> AFAIR this is a really outdated K&R style of -=  . Compilers *should* 
> recognize this (and will throw an ambiguity error if there's no space 
> after =- ), but it's better to use the 'newer' style.
> 
> Regards,
> Andreas
> 
-- 
Dave Cramer <dave@fastcrypt.com>
fastcrypt



Re: suspicous looking code in copy.c

From
Tom Lane
Date:
Dave Cramer <dave@fastcrypt.com> writes:
> This just doesn't look right.

Ugh, you're right.
        regards, tom lane


Re: suspicous looking code in copy.c

From
Tom Lane
Date:
Andreas Pflug <pgadmin@pse-consulting.de> writes:
>> shouldn't it be datasize -= avail ?
>> 
> AFAIR this is a really outdated K&R style of -=  . Compilers *should* 
> recognize this (and will throw an ambiguity error if there's no space 
> after =- ), but it's better to use the 'newer' style.

Dave didn't quote it accurately.  It was actually

diff -r1.209 copy.c
435c435
<                               datasize = -avail;
---
>                               datasize -= avail;

which is just plain wrong ... the net effect being that COPY IN BINARY
would fail to cope with fields crossing message packet boundaries.
        regards, tom lane


Re: Possible bug in update?

From
Markus Kräutner
Date:
> > At the end of several bulk inserts the following statements get executed:
> >
> > begin
> > update pom_c2996785 set p3=3268837857008 where p3=34804837857008
> > update pom_c2996795 set p3=3268837857008 where p3=34804837857008
> > update pom_c2996804 set p3=3268837857008 where p3=34804837857008
> > commit
> >
> > When I check the database the last update has not been executed. Manually
> > executing it in psql does work.
>
> ... ergo, the bug is not in the backend, but in whatever non-psql client
> software you are using.  Since you haven't mentioned what that is,
> further speculation is difficult ...

I managed to construct a sample script which also fails in psql. It seems to 
be the hash index. If I leave out the "create index" line or use btree 
instead everything works fine.
I will use btree now :)

Thank you for your help.

Markus