Re: The Data Base System is in recovery mode

Hi!

If needed, I'll try to provoke the error again, first recompiling with
-g. I will probably not find time for it before the week-end, though.

New facts: The problem "moved" when raising the -B from default (64)
to 1000 (-N 100 now). Now, here's what happens:

query: CREATE VIEW wtabmaria AS SELECT p.personid,p.foretag,p.fnamn,p.enamn,p.titel,p.telefon,p.mobil,p.email,p.tidpunkt FROM personer p WHERE true AND low
er(p.enamn)~~lower('Branner%')
ProcessUtility: CREATE VIEW wtabmaria AS SELECT p.personid,p.foretag,p.fnamn,p.enamn,p.titel,p.telefon,p.mobil,p.email,p.tidpunkt FROM personer p WHERE tru
e AND lower(p.enamn)~~lower('Branner%')
query: INSERT INTO pg_rewrite (rulename, ev_type, ev_class, ev_attr, ev_action, ev_qual, is_instead) VALUES ('_RETwtabmaria', 1::char, 6275026::oid, -1::int
2, '({ QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal false :isBinary false :isTemp false :unionall false :distinctClause <> :sortClause
<> :rtable ({ RTE :relname wtabmaria :ref { ATTR :relname *CURRENT* :attrs <>} :relid 6275026 :inh false :inFromCl false :inJoinSet false :skipAcl false} {
RTE :relname wtabmaria :ref { ATTR :relname *NEW* :attrs <>} :relid 6275026 :inh false :inFromCl false :inJoinSet false :skipAcl false} { RTE :relname pers
oner :ref { ATTR :relname p :attrs <>} :relid 820284 :inh false :inFromCl true :inJoinSet true :skipAcl false}) :targetlist ({ TARGETENTRY :resdom { RESDOM
:resno 1 :restype 23 :restypmod -1 :resname personid :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 1 :vartype 23
:vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 1}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 25 :restypmod -1 :resname foretag :reskey 0 :reske
yop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 2 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 2}} { TARGETENTR
Y :resdom { RESDOM :resno 3 :restype 25 :restypmod -1 :resname fnamn :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattn
o 3 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 3}} { TARGETENTRY :resdom { RESDOM :resno 4 :restype 25 :restypmod -1 :resname enamn :r
eskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 4 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 4}
} { TARGETENTRY :resdom { RESDOM :resno 5 :restype 25 :restypmod -1 :resname titel :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :va
rno 3 :varattno 5 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 5}} { TARGETENTRY :resdom { RESDOM :resno 6 :restype 25 :restypmod -1 :re
sname telefon :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 10 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnool
d 3 :varoattno 10}} { TARGETENTRY :resdom { RESDOM :resno 7 :restype 25 :restypmod -1 :resname mobil :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false
} :expr { VAR :varno 3 :varattno 12 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 12}} { TARGETENTRY :resdom { RESDOM :resno 8 :restype
25 :restypmod -1 :resname email :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 13 :vartype 25 :vartypmod -1 :varl
evelsup 0 :varnoold 3 :varoattno 13}} { TARGETENTRY :resdom { RESDOM :resno 9 :restype 1184 :restypmod -1 :resname tidpunkt :reskey 0 :reskeyop 0 :ressortgr
oupref 0 :resjunk false } :expr { VAR :varno 3 :varattno 16 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 16}}) :qual { EXPR :typeOid 1
6 :opType and :oper <> :args ({ CONST :consttype 16 :constlen 1 :constisnull false :constvalue 1 [ 1 0 0 0 ] :constbyval true } { EXPR :typeOid 16 :opTy
pe op :oper { OPER :opno 1209 :opid 0 :opresulttype 16 } :args ({ EXPR :typeOid 25 :opType func :oper { FUNC :funcid 870 :functype 25 :funcisindex false :f
uncsize 0 :func_fcache @ 0x0 :func_tlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 25 :restypmod -1 :resname \\<noname> :reskey 0 :reskeyop 0 :ress
ortgroupref 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 :varattno 4 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 3 :varoattno 4})} { EXPR :typeOid 25 :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 <>} :
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) from wtabmaria order by lower(foretag
)
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: fetch forward 20 from curse
ProcessUtility: fetch forward 20 from curse
CommitTransactionCommand
StartTransactionCommand
query: select distinct personid,foretag, lower(foretag) from wtabmaria order by lower(foretag)
ProcessQuery
Server process (pid 3096) exited with status 139 at Wed Oct 18 09:19:00 2000
Terminating any active server processes...
NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
Server processes were terminated at Wed Oct 18 09:19:01 2000
Reinitializing shared memory and semaphores
DEBUG: Data Base System is starting up at Wed Oct 18 09:19:01 2000
DEBUG: Data Base System was interrupted being in production at Wed Oct 18 01:12:29 2000
DEBUG: Data Base System is in production state at Wed Oct 18 09:19:01 2000

It looks very much like an out-of-memory error, since now, the fetch
is ok, but it fails on the next query, which is a memory hog (~5500
records). I don't know why the progammer initially put the second
select inside the transaction, and I have no put an "abort" after the
"fetch", to see if I might get away with it now. It seems to work
(using -B 2000 too)...

The higher I set '-B', the harder it gets to reproduce the error. I
*think* I might be able to find a value for '-B' where I can just
lower it by one and get it to fail, (given no other users in the
database system).

Schema:

CREATE SEQUENCE "pidseq" start 9700 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
CREATE TABLE "personer" (
"personid" int4 DEFAULT nextval('pidseq'::text) NOT NULL,
"foretag" text,
"fnamn" text,
"enamn" text,
"titel" text,
"adress1" text,
"adress2" text,
"postadress" text,
"land" character(2) DEFAULT 'SE' NOT NULL,
"telefon" text,
"telefax" text,
"mobil" text,
"email" text,
"privemail" text,
"url" text,
"tidpunkt" timestamp DEFAULT now() NOT NULL,
"avdelning" text
);

There are about 5500 records in this table.

/Palle

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Palle Girgensohn <girgen(at)partitur(dot)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

--
Partitur Informationsteknik AB
Wenner-Gren Center +46 8 566 280 02
113 46 Stockholm +46 70 785 86 02
Sweden girgen(at)partitur(dot)se