Re: Core dump on PG 7.1.3 - Mailing list pgsql-general

Ok, here are the results of the queries to GDB ... i could send the core
file along too but i don't know if it would do you any good (does the core
dump only work on the machine on which it was generated?)

#0  ExecEvalVar (variable=0x8237338, econtext=0x8238238, isNull=0x826769c
"") at execQual.c:324
324             tuple_type = slot->ttc_tupleDescriptor;
(gdb) p slot
$1 = (TupleTableSlot *) 0x0
(gdb) p *slot
Cannot access memory at address 0x0
(gdb) p *variable
$2 = {type = T_Var, varno = 65001, varattno = 3, vartype = 23, vartypmod
= -1, varlevelsup = 0, varnoold = 2, varoattno = 11}
(gdb) p *econtext
$3 = {type = T_ExprContext, ecxt_scantuple = 0x82374c0, ecxt_innertuple =
0x0, ecxt_outertuple = 0x0,
  ecxt_per_query_memory = 0x81dc7f8, ecxt_per_tuple_memory = 0x822f030,
ecxt_param_exec_vals = 0x0, ecxt_param_list_info = 0x0,
  ecxt_aggvalues = 0x0, ecxt_aggnulls = 0x0}
(gdb)

As far as the EXPLAIN VERBOSE goes, i can't run it against the database that
crashed until tomorrow morning ... but I did run it against a PG 7.1.3
installation with the exact same schema and data set ... I figure this is
sufficient since I can't imagine that the query plan changes based on
anything machine-specific ... However, since the database doesn't actually
crash when executing it on this machine, i don't know if any of that
information would be included in the EXPLAIN data ...

Am I right in concluding that when a query is run with "EXPLAIN", it doesn't
actually modify the database? (it went way too fast ... plus i never saw my
normal "UPDATE 0 20084" returned like i would if it actually performed the
update .. )

bnmail=# EXPLAIN VERBOSE UPDATE campaign_email
bnmail-# SET campaign_email_status = 2,
bnmail-# campaign_email_last_modified = message_queue.last_modification
bnmail-# FROM message_queue, message_queue_purge_lock
bnmail-# WHERE message_queue.message_id =
message_queue_purge_lock.message_id
bnmail-# AND message_queue.campaign_email_id =
campaign_email.campaign_email_id;
NOTICE:  QUERY DUMP:

{ MERGEJOIN :startup_cost 0.00 :total_cost 2985543.45 :rows 13107000 :width
118 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1700
:restypmod 720900 :resname campaign_email_id :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001 :varattno 1
:vartype 1700 :vartypmod 720900  :varlevelsup 0 :varnoold 1 :varoattno 1}}
{ TARGETENTRY :resdom { RESDOM :resno 2 :restype 1700 :restypmod 720900
:resname campaign_id :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 65001 :varattno 2 :vartype 1700 :vartypmod 720900
:varlevelsup 0 :varnoold 1 :varoattno 2}} { TARGETENTRY :resdom { RESDOM
:resno 3 :restype 1700 :restypmod 720900 :resname patron_id :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001
:varattno 3 :vartype 1700 :vartypmod 720900  :varlevelsup 0 :varnoold 1
:varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 1700
:restypmod 131076 :resname campaign_email_status :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { CONST :consttype 1700
:constlen -1 :constbyval false :constisnull false :constvalue  11 [ 11 0 0 0
0 0 0 0 0 0 32 ] }} { TARGETENTRY :resdom { RESDOM :resno 5 :restype 1184
:restypmod -1 :resname campaign_email_last_modified :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001 :varattno 11
:vartype 1184 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 7}} {
TARGETENTRY :resdom { RESDOM :resno 6 :restype 1043 :restypmod 504 :resname
campaign_email_detail :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 65001 :varattno 4 :vartype 1043 :vartypmod 504
:varlevelsup 0 :varnoold 1 :varoattno 6}} { TARGETENTRY :resdom { RESDOM
:resno 7 :restype 1184 :restypmod -1 :resname impression_date :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001
:varattno 5 :vartype 1184 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 7}} { TARGETENTRY :resdom { RESDOM :resno 8 :restype 1184
:restypmod -1 :resname last_click_through_date :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001 :varattno 6
:vartype 1184 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 8}} {
TARGETENTRY :resdom { RESDOM :resno 9 :restype 1700 :restypmod 720900
:resname num_click_throughs :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 65001 :varattno 7 :vartype 1700
:vartypmod 720900  :varlevelsup 0 :varnoold 1 :varoattno 9}} { TARGETENTRY
:resdom { RESDOM :resno 10 :restype 1184 :restypmod -1 :resname bounce_date
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno
65001 :varattno 8 :vartype 1184 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 10}} { TARGETENTRY :resdom { RESDOM :resno 11 :restype 1184
:restypmod -1 :resname opt_out_date :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 65001 :varattno 9 :vartype 1184
:vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 11}} { TARGETENTRY
:resdom { RESDOM :resno 12 :restype 27 :restypmod -1 :resname ctid :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk true } :expr { VAR :varno 65001
:varattno 10 :vartype 27 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno -1}}) :qpqual <> :lefttree { NESTLOOP :startup_cost 0.00
:total_cost 2969088.19 :rows 1310700 :width 114 :qptargetlist ({ TARGETENTRY
:resdom { RESDOM :resno 1 :restype 1700 :restypmod 720900 :resname <>
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno
65000 :varattno 1 :vartype 1700 :vartypmod 720900  :varlevelsup 0 :varnoold
1 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 1700
:restypmod 720900 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 65000 :varattno 2 :vartype 1700
:vartypmod 720900  :varlevelsup 0 :varnoold 1 :varoattno 2}} { TARGETENTRY
:resdom { RESDOM :resno 3 :restype 1700 :restypmod 720900 :resname <>
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno
65000 :varattno 3 :vartype 1700 :vartypmod 720900  :varlevelsup 0 :varnoold
1 :varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 1043
:restypmod 504 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 65000 :varattno 4 :vartype 1043 :vartypmod 504
:varlevelsup 0 :varnoold 1 :varoattno 6}} { TARGETENTRY :resdom { RESDOM
:resno 5 :restype 1184 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000 :varattno 5
:vartype 1184 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno 7}} {
TARGETENTRY :resdom { RESDOM :resno 6 :restype 1184 :restypmod -1 :resname
<> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 65000 :varattno 6 :vartype 1184 :vartypmod -1  :varlevelsup 0
:varnoold 1 :varoattno 8}} { TARGETENTRY :resdom { RESDOM :resno 7 :restype
1700 :restypmod 720900 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 65000 :varattno 7 :vartype 1700
:vartypmod 720900  :varlevelsup 0 :varnoold 1 :varoattno 9}} { TARGETENTRY
:resdom { RESDOM :resno 8 :restype 1184 :restypmod -1 :resname <> :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000
:varattno 8 :vartype 1184 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 10}} { TARGETENTRY :resdom { RESDOM :resno 9 :restype 1184
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 65000 :varattno 9 :vartype 1184 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 11}} { TARGETENTRY :resdom { RESDOM
:resno 10 :restype 27 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 65000 :varattno 10
:vartype 27 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno -1}} {
TARGETENTRY :resdom { RESDOM :resno 11 :restype 1184 :restypmod -1 :resname
<> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 65001 :varattno 1 :vartype 1184 :vartypmod -1  :varlevelsup 0
:varnoold 2 :varoattno 7}} { TARGETENTRY :resdom { RESDOM :resno 12 :restype
23 :restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 65001 :varattno 2 :vartype 23
:vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 1}} { TARGETENTRY
:resdom { RESDOM :resno 13 :restype 23 :restypmod -1 :resname <> :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 65001
:varattno 3 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno
11}}) :qpqual <> :lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 59.00
:rows 1000 :width 16 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1
:restype 1184 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 2 :varattno 7
:vartype 1184 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 7}} {
TARGETENTRY :resdom { RESDOM :resno 2 :restype 23 :restypmod -1 :resname <>
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno
2 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2
:varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 23
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 2 :varattno 11 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 2 :varoattno 11}}) :qpqual <> :lefttree <>
:righttree <> :extprm () :locprm () :initplan <> :nprm 0  :scanrelid 2
:indxid ( 960076) :indxqual (<>) :indxqualorig (<>) :indxorderdir 1 }
:righttree { INDEXSCAN :startup_cost 0.00 :total_cost 2949.37 :rows 1311
:width 98 :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype
1700 :restypmod 720900 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 1700 :vartypmod
720900  :varlevelsup 0 :varnoold 1 :varoattno 1}} { TARGETENTRY :resdom {
RESDOM :resno 2 :restype 1700 :restypmod 720900 :resname <> :reskey 0
:reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1
:varattno 2 :vartype 1700 :vartypmod 720900  :varlevelsup 0 :varnoold 1
:varoattno 2}} { TARGETENTRY :resdom { RESDOM :resno 3 :restype 1700
:restypmod 720900 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk false } :expr { VAR :varno 1 :varattno 3 :vartype 1700 :vartypmod
720900  :varlevelsup 0 :varnoold 1 :varoattno 3}} { TARGETENTRY :resdom {
RESDOM :resno 4 :restype 1043 :restypmod 504 :resname <> :reskey 0 :reskeyop
0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 6
:vartype 1043 :vartypmod 504  :varlevelsup 0 :varnoold 1 :varoattno 6}} {
TARGETENTRY :resdom { RESDOM :resno 5 :restype 1184 :restypmod -1 :resname
<> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 7 :vartype 1184 :vartypmod -1  :varlevelsup 0 :varnoold 1
:varoattno 7}} { TARGETENTRY :resdom { RESDOM :resno 6 :restype 1184
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 1 :varattno 8 :vartype 1184 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 8}} { TARGETENTRY :resdom { RESDOM
:resno 7 :restype 1700 :restypmod 720900 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 9
:vartype 1700 :vartypmod 720900  :varlevelsup 0 :varnoold 1 :varoattno 9}}
{ TARGETENTRY :resdom { RESDOM :resno 8 :restype 1184 :restypmod -1 :resname
<> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno 1 :varattno 10 :vartype 1184 :vartypmod -1  :varlevelsup 0 :varnoold
1 :varoattno 10}} { TARGETENTRY :resdom { RESDOM :resno 9 :restype 1184
:restypmod -1 :resname <> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk
false } :expr { VAR :varno 1 :varattno 11 :vartype 1184 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 11}} { TARGETENTRY :resdom { RESDOM
:resno 10 :restype 27 :restypmod -1 :resname <> :reskey 0 :reskeyop 0
:ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno -1
:vartype 27 :vartypmod -1  :varlevelsup 0 :varnoold 1 :varoattno -1}})
:qpqual <> :lefttree <> :righttree <> :extprm () :locprm () :initplan <>
:nprm 0  :scanrelid 1 :indxid ( 959439) :indxqual (({ EXPR :typeOid 16
:opType op :oper { OPER :opno 1752 :opid 1718 :opresulttype 16 } :args ({
VAR :varno 1 :varattno 1 :vartype 1700 :vartypmod 720900  :varlevelsup 0
:varnoold 1 :varoattno 1} { EXPR :typeOid 1700  :opType func :oper { FUNC
:funcid 1740 :functype 1700 } :args ({ VAR :varno 65001 :varattno 3 :vartype
23 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 11})})}))
:indxqualorig (({ EXPR :typeOid 16  :opType op :oper { OPER :opno 1752 :opid
1718 :opresulttype 16 } :args ({ EXPR :typeOid 1700  :opType func :oper {
FUNC :funcid 1740 :functype 1700 } :args ({ VAR :varno 65001 :varattno 3
:vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2 :varoattno 11})} { VAR
:varno 1 :varattno 1 :vartype 1700 :vartypmod 720900  :varlevelsup 0
:varnoold 1 :varoattno 1})})) :indxorderdir 0 } :extprm () :locprm ()
:initplan <> :nprm 0  :jointype 0 :joinqual <>} :righttree { INDEXSCAN
:startup_cost 0.00 :total_cost 59.00 :rows 1000 :width 4 :qptargetlist ({
TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname <>
:reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno
3 :varattno 1 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 3
:varoattno 1}}) :qpqual <> :lefttree <> :righttree <> :extprm () :locprm ()
:initplan <> :nprm 0  :scanrelid 3 :indxid ( 960104) :indxqual (<>)
:indxqualorig (<>) :indxorderdir 1 } :extprm () :locprm () :initplan <>
:nprm 0  :jointype 0 :joinqual <> :mergeclauses ({ EXPR :typeOid 16  :opType
op :oper { OPER :opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno
65001 :varattno 12 :vartype 23 :vartypmod -1  :varlevelsup 0 :varnoold 2
:varoattno 1} { VAR :varno 65000 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 3 :varoattno 1})})}
NOTICE:  QUERY PLAN:

Merge Join  (cost=0.00..2985543.45 rows=13107000 width=118)
  ->  Nested Loop  (cost=0.00..2969088.19 rows=1310700 width=114)
        ->  Index Scan using message_queue_pkey on message_queue
(cost=0.00..59.00 rows=1000 width=16)
        ->  Index Scan using campaign_email_pkey on campaign_email
(cost=0.00..2949.37 rows=1311 width=98)
  ->  Index Scan using message_queue_purge_lock_pkey on
message_queue_purge_lock  (cost=0.00..59.00 rows=1000 width=4)

EXPLAIN



> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Tuesday, April 02, 2002 10:20 AM
> To: David Esposito
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Core dump on PG 7.1.3
>
>
> "David Esposito" <dvesposito@newnetco.com> writes:
> > #0  ExecEvalVar (variable=0x8237338, econtext=0x8238238,
> isNull=0x826769c
> > "") at execQual.c:324
> > 324             tuple_type = slot->ttc_tupleDescriptor;
> > (gdb) bt
> > #0  ExecEvalVar (variable=0x8237338, econtext=0x8238238,
> isNull=0x826769c
> > "") at execQual.c:324
>
> Okay ... could we see the results of getting into gdb at this point
> and doing
>
>     p slot
>     p *slot        -- if above shows slot not 0, which I doubt
>     p *variable
>     p *econtext
>
> I'd also like to see the results of EXPLAIN VERBOSE for the problem
> query.
>
>             regards, tom lane