Re: The Data Base System is in recovery mode - Mailing list pgsql-bugs

Hi!

Here's a traceback from the time it failed, unfortunately without
debugging symbols. I cannot reproduce this error on a fresh
installation on another machine, which puzzles me. I though it might
have to do with the the database beeing dumped/restored to this new
installation, but I found no way to get the data there without
dump/restore. tarring the data/base/dbname dir doesn't work, right?

It is on FreeBSD 4.1.1, an SMP machine. Built with multibyte encoding,
default encoding set to LATIN1, (using the freebsd port).

Current directory is /usr/local/pgsql/bin/
GNU gdb 4.18
Copyright 1998 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB.  Type "show warranty" for details.
This GDB was configured as "i386-unknown-freebsd"...
(no debugging symbols found)...
Core was generated by `postgres'.
Program terminated with signal 11, Segmentation fault.
Reading symbols from /usr/lib/libcrypt.so.2...(no debugging symbols found)...
done.
Reading symbols from /usr/lib/libm.so.2...(no debugging symbols found)...done.
Reading symbols from /usr/lib/libutil.so.3...(no debugging symbols found)...
done.
Reading symbols from /usr/lib/libreadline.so.4...
(no debugging symbols found)...done.
Reading symbols from /usr/lib/libncurses.so.5...(no debugging symbols found)...
done.
Reading symbols from /usr/lib/libc.so.4...(no debugging symbols found)...done.
Reading symbols from /usr/libexec/ld-elf.so.1...(no debugging symbols found)...
done.
#0  0x8136eb2 in GetTemplateEncoding ()
(gdb) bt
#0  0x8136eb2 in GetTemplateEncoding ()
#1  0x8136d66 in pg_mb2wchar_with_len ()
#2  0x810adab in int8_text ()
#3  0x810ae82 in textlike ()
#4  0x812ef14 in fmgr_c ()
#5  0x80a1839 in GetAttributeByName ()
#6  0x80a187f in GetAttributeByName ()
#7  0x80a1b63 in ExecEvalExpr ()
#8  0x80a1c36 in ExecQual ()
#9  0x80a20b7 in ExecScan ()
#10 0x80a701a in ExecSeqScan ()
#11 0x80a084d in ExecProcNode ()
#12 0x80a73f8 in ExecSort ()
#13 0x80a0891 in ExecProcNode ()
#14 0x80a766f in ExecUnique ()
#15 0x80a089d in ExecProcNode ()
#16 0x809fa19 in ExecutorEnd ()
#17 0x809ef6a in ExecutorRun ()
#18 0x80f67aa in ProcessPortal ()
#19 0x80f6827 in ProcessQuery ()
#20 0x80f522c in pg_exec_query_dest ()
#21 0x80f50f5 in pg_plan_query ()
#22 0x80f6202 in PostgresMain ()
#23 0x80deaf8 in PostmasterMain ()
#24 0x80de634 in PostmasterMain ()
#25 0x80dd815 in PostmasterMain ()
#26 0x80dd212 in PostmasterMain ()
#27 0x80afea7 in main ()
#28 0x80639b9 in _start ()
(gdb)


This core dump is not when the database ended up in recovery mode. As
you might remember, I fiddled with the memory settings, and the higher
they went, the more stable the system became.

Here's the postgres log (-d 2) from this occasion:

ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: select relname from pg_class where relname='wtabniclas'
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: drop view wtabniclas
ProcessUtility: drop view wtabniclas
CommitTransactionCommand
StartTransactionCommand
query: CREATE VIEW wtabniclas AS SELECT
p.personid,p.foretag,p.fnamn,p.enamn,p.titel,p.telefon,p.mobil,p.email,p.tidpunktFROM personer p W\ 
HERE true AND lower(p.foretag)~~lower('Taxi%%')
ProcessUtility: CREATE VIEW wtabniclas AS SELECT
p.personid,p.foretag,p.fnamn,p.enamn,p.titel,p.telefon,p.mobil,p.email,p.tidpunktFROM per\ 
soner p WHERE true AND lower(p.foretag)~~lower('Taxi%%')
query: INSERT INTO pg_rewrite (rulename, ev_type, ev_class, ev_attr, ev_action, ev_qual, is_instead) VALUES
('_RETwtabniclas',1::char, 6282\ 
784::oid, -1::int2, '({ QUERY :command 1  :utility <> :resultRelation 0 :into <> :isPortal false :isBinary false
:isTempfalse :unionall fal\ 
se :distinctClause <> :sortClause <> :rtable ({ RTE :relname wtabniclas :ref { ATTR :relname *CURRENT* :attrs <>}
:relid6282784 :inh false \ 
:inFromCl false :inJoinSet false :skipAcl false} { RTE :relname wtabniclas :ref { ATTR :relname *NEW* :attrs <>} :relid
6282784:inh false :\ 
inFromCl false :inJoinSet false :skipAcl false} { RTE :relname personer :ref { ATTR :relname p :attrs <>} :relid 820284
:inhfalse :inFromCl\ 
 true :inJoinSet true :skipAcl false}) :targetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1
:resnamepersonid :res\ 
key 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup0 :varnoold 3\ 
 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 25 :restypmod -1 :resname foretag :reskey 0 :reskeyop
0:ressortgroupref 0 \ 
:resjunk false } :expr { VAR :varno 3 :varattno 2 :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 3 :varoattno 2}}
{TARGETENTRY :resdom\ 
 { RESDOM :resno 3 :restype 25 :restypmod -1 :resname fnamn :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr{ VAR :varno 3 :\ 
varattno 3 :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 3 :varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 4
:restype25 :restypm\ 
od -1 :resname enamn :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 4
:vartype25 :vartypmod -1  :\ 
varlevelsup 0 :varnoold 3 :varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 5 :restype 25 :restypmod -1 :resname
titel:reskey 0 :reskeyo\ 
p 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 5 :vartype 25 :vartypmod -1  :varlevelsup 0
:varnoold3 :varoattno 5}\ 
} { TARGETENTRY :resdom { RESDOM :resno 6 :restype 25 :restypmod -1 :resname telefon :reskey 0 :reskeyop 0
:ressortgroupref0 :resjunk false\ 
 } :expr { VAR :varno 3 :varattno 10 :vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 3 :varoattno 10}} {
TARGETENTRY:resdom { RESDOM :r\ 
esno 7 :restype 25 :restypmod -1 :resname mobil :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno3 :varattno 12 \ 
:vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold 3 :varoattno 12}} { TARGETENTRY :resdom { RESDOM :resno 8 :restype
25:restypmod -1 :res\ 
name email :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 13 :vartype 25
:vartypmod-1  :varlevels\ 
up 0 :varnoold 3 :varoattno 13}} { TARGETENTRY :resdom { RESDOM :resno 9 :restype 1184 :restypmod -1 :resname tidpunkt
:reskey0 :reskeyop 0\ 
 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 16 :vartype 1184 :vartypmod -1  :varlevelsup 0
:varnoold3 :varoattno 16\ 
}}) :qual { EXPR :typeOid 16  :opType and :oper <> :args ({ CONST :consttype 16 :constlen 1 :constisnull false
:constvalue 1 [ 1 0 0 0 ]  :\ 
constbyval true } { EXPR :typeOid 16  :opType op :oper { OPER :opno 1209 :opid 0 :opresulttype 16 } :args ({ EXPR
:typeOid25  :opType func \ 
:oper { FUNC :funcid 870 :functype 25 :funcisindex false :funcsize 0  :func_fcache @ 0x0 :func_tlist ({ TARGETENTRY
:resdom{ RESDOM :resno \ 
1 :restype 25 :restypmod -1 :resname \\<noname> :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR
:varno-1 :varattno 1 \ 
:vartype 25 :vartypmod -1  :varlevelsup 0 :varnoold -1 :varoattno 1}}) :func_planlist <>} :args ({ VAR :varno 3
:varattno2 :vartype 25 :var\ 
typmod -1  :varlevelsup 0 :varnoold 3 :varoattno 2})} { EXPR :typeOid 25  :opType func :oper { FUNC :funcid 870
:functype25 :funcisindex fa\ 
lse :funcsize 0  :func_fcache @ 0x0 :func_tlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 25 :restypmod -1
:resname\\<noname> :resk\ 
ey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno -1 :varattno 1 :vartype 25 :vartypmod -1
:varlevelsup0 :varnoold -\ 
1 :varoattno 1}}) :func_planlist <>
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: begin transaction
ProcessUtility: begin transaction
CommitTransactionCommand
StartTransactionCommand
query: declare curse cursor for select personid,foretag,fnamn,enamn,titel,telefon,mobil,email,tidpunkt, lower(foretag)
fromwtabniclas order\ 
 by lower(foretag)
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: fetch forward 20 from curse
ProcessUtility: fetch forward 20 from curse
CommitTransactionCommand
StartTransactionCommand
query: abort transaction
ProcessUtility: abort transaction
CommitTransactionCommand
StartTransactionCommand
query: select distinct personid,foretag, lower(foretag) from wtabniclas order by lower(foretag)
ProcessQuery
Server process (pid 6757) exited with status 139 at Wed Oct 18 14:51:53 2000
Terminating any active server processes...
Server processes were terminated at Wed Oct 18 14:51:53 2000
Reinitializing shared memory and semaphores
DEBUG:  Data Base System is starting up at Wed Oct 18 14:51:53 2000
DEBUG:  Data Base System was interrupted being in production at Wed Oct 18 14:51:21 2000
DEBUG:  Data Base System is in production state at Wed Oct 18 14:51:53 2000


Hope this helps...

/Palle


Tom Lane <tgl@sss.pgh.pa.us> writes:

> Palle Girgensohn <girgen@partitur.se> writes:
> > I have a problem with postgresl-7.0.2 on Freebsd 4-stable.
>
> > query: declare curse cursor for select personid,foretag,fnamn,enamn,titel,telefon,mobil,email,tidpunkt,
lower(foretag)from wtabmaria order by lower(foretag) 
> > query: fetch forward 20 from curse
> > Server process (pid 15342) exited with status 139 at Tue Oct 17 14:37:20 2000
>
> Hm.  I couldn't duplicate this crash using
> 7.0.2-plus-some-7.0.3-patches.  However I don't recall any bug fixes for
> cursors in the 7.0.* branch.  Could you provide a more complete bug
> report, like the complete schema for the table?  Also, can you provide
> a gdb traceback from the corefile that the crashing backend hopefully
> left behind in the database subdirectory ($PGDATA/data/base/yourdb)?
>
>             regards, tom lane