7.3.2 strange behavior (long) - Mailing list pgsql-admin

Hello,

Firstly I apologise for my terrible english.
we are using postgresql 7.2.x (currently 7.2.4)
on our production server for last year (and also
  we are on suse 7.2).

Last night i was working on the project for
porting our application on pgsql 7.3.2 when a strange
thing happen. The machine hangs (not only postgres) after
I submit via psql this query "update coop_uni set msg_quote=''"
to the server. The structure of this table is:

coop2003=> \d coop_uni
                  Table "public.coop_uni"
     Column    |           Type           |   Modifiers
--------------+--------------------------+---------------
  prefisso     | character varying(6)     |
  telefono     | character varying(20)    |
  cap          | character varying(6)     |
  cod_com      | integer                  |
  cod_prov     | integer                  |
  citta        | character(35)            |
  nome_cognome | character varying(70)    |
  capoluo      | integer                  |
  estrazione   | timestamp with time zone |
  id           | integer                  |
  rand         | double precision         |
  num_estra    | integer                  | default 0
  occupato     | boolean                  | default false
  tipo         | integer                  |
  strato       | integer                  |
  sample       | integer                  |
  msg_quote    | character varying(20)    |
Indexes: id_tipo_uni_idx btree (id, tipo),
          iduni_idx btree (id),
          pre_tel_idx btree (prefisso, telefono),
          prov_capo_rand_idx btree (cod_prov, capoluo, rand),
          strato_idx btree (tipo, strato, sample)


follow the log of postgres during crash:

[snip]

2003-03-19 23:48:20 [3840]   LOG:  query: select oid,* from coop_uni ;
2003-03-19 23:48:20 [3840]   DEBUG:  ProcessQuery
2003-03-19 23:48:23 [3840]   DEBUG:  CommitTransactionCommand
2003-03-19 23:48:23 [3840]   LOG:  duration: 3.048701 sec
2003-03-19 23:48:35 [3840]   DEBUG:  StartTransactionCommand
2003-03-19 23:48:35 [3840]   LOG:  query: select oid,* from coop_uni order by oid;
2003-03-19 23:48:35 [3840]   DEBUG:  ProcessQuery
2003-03-19 23:48:39 [3840]   DEBUG:  CommitTransactionCommand
2003-03-19 23:48:39 [3840]   LOG:  duration: 4.403131 sec
^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@
^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@
^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@
^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@
^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@
^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@
^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@
^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@
^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@
^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@þþ
þþþþþþþþþþeggggggÿoýefÿÿÿÿ^@^@^@^@^@^X~_@ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿþþþþþþþÿþþþþþþþÿÿfÿÿiÿÿÿÿýýfýýff
^@^@^@^@^@Ø~Q@^@^@^@^@^@^T~T@^@^@^@^@À~Yá@^@^@^@^@^@Óý@þþþþfýýþ   3111957
ÿþþÿþþiý^@^@^@^@^@Àj@ýýýýýþþeist professionale per il commercio      ef~G~G~Ggÿýimresa
aýýþþþþýýrigianale       ^@^@^@^@^@| ~C@^@^@^@^@^@^W°@ýýþþþþþ÷amministrazione
fÿeÿþþþþjþþþþþþþþþþþþþeggggggÿeý^@^@^@^@^@8~_@hfÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿþþþþþþþÿþþþþþþþÿÿfÿÿiÿÿÿÿý^@^@^@^@^@|
~C@÷jýýffþþ^@^@^@^@À~Tá@^@^@^@^@~@Qç@þþfýýþÿþ   3071962 þÿþþhýýý^@^@^@^@^@^@l@ragioneria
     þþþþþeefli~Lgÿýþþufficio þþþþýýýý^@^@^@^@^@\~R@^@^@^@^@^@^W°@amministrazione
þþþþþ÷fÿeÿþþþþeþþþþþþþþþþþþþeggggggÿiýhf^@^@^@^@^@,~_@ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿþþþþþþþÿþþþþþþþÿÿ
fÿÿiÿÿÿÿý÷g^@^@^@^@^@\~R@ýýeeþþþþ^@^@^@^@^@~Yá@^@^@^@^@~@^@×@eýýþÿþþÿ


When i try to manually reboot the box
I have another problem. the machine hangs during
the boot with this message:

"Unable to handle kernel paging request at virtual address dc7d00
printing eip:
c01ad272
* pde = 17edb...
  ....
  Oops:  000
  CPU: 0

  ...."

I've tried again and after the third manual reboot
the machine boot correctly. After that I start postgres7.3.2.
This is the log:

DEBUG:  /opt/postgres-7.3.2/bin/postmaster: PostmasterMain: initial environ dump:
2003-03-19 23:56:09 [601]    DEBUG:  -----------------------------------------
2003-03-19 23:56:09 [601]    DEBUG:     PWD=/var/lib/pgsql
2003-03-19 23:56:09 [601]    DEBUG:     PAGER=less
2003-03-19 23:56:09 [601]    DEBUG:     HOSTNAME=suino
2003-03-19 23:56:09 [601]    DEBUG:     LESSCLOSE=lessclose.sh %s %s
2003-03-19 23:56:09 [601]    DEBUG:     LS_OPTIONS=-N --color=tty -T 0

[snip]

2003-03-19 23:56:09 [601]    DEBUG:     LC_COLLATE=POSIX
2003-03-19 23:56:09 [601]    DEBUG:     _=/opt/postgres-7.3.2/bin/postmaster
2003-03-19 23:56:09 [601]    DEBUG:  -----------------------------------------
2003-03-19 23:56:09 [601]    DEBUG:  FindExec: found "/opt/postgres-7.3.2/bin/postgres"
using argv[0]
2003-03-19 23:56:09 [601]    DEBUG:  invoking IpcMemoryCreate(size=1466368)
2003-03-19 23:56:09 [601]    DEBUG:  FindExec: found "/opt/postgres-7.3.2/bin/postmaster"
using argv[0]
2003-03-19 23:56:09 [605]    LOG:  database system was interrupted at 2003-03-19 23:49:59 CET
2003-03-19 23:56:09 [605]    LOG:  checkpoint record is at 0/7FDABC0
2003-03-19 23:56:09 [605]    LOG:  redo record is at 0/7FDABC0; undo record is at 0/0;
shutdown FALSE
2003-03-19 23:56:09 [605]    LOG:  next transaction id: 1446; next oid: 222355
2003-03-19 23:56:09 [605]    LOG:  database system was not properly shut down; automatic
recovery in progress
2003-03-19 23:56:09 [605]    LOG:  redo starts at 0/7FDAC00
2003-03-19 23:56:20 [605]    LOG:  ReadRecord: record with zero length at 0/93508AC
2003-03-19 23:56:20 [605]    LOG:  redo done at 0/9350870
2003-03-19 23:56:24 [605]    LOG:  recycled transaction log file 0000000000000006
2003-03-19 23:56:24 [605]    LOG:  database system is ready


Anyways the db design is very simple
(no trigger, no fks, and there are only few
store procs written in plpgsql)


The developing box is a "small" machine
(celeron 1.2GHZ,ram 384MB, no scsi disks and
PGDATA is on one ide disk), the kernel version
is 2.4.20

I wanted to ask to you if this problem can have had
to a hardware issue or to something else.


Andrea Suisani


other info:

- sickpig@suino:postgres7.3.2 $ pg_config --configure
'--prefix=/opt/postgres-7.3.2/' '--with-perl' '--with-tcl' '--with-tk' '--enable-syslog'

- the file $PGDATA/postgresql.conf is the one that come from intallation without any
modification

- cat $PGDATA/postmaster.opts:
/opt/postgres-7.3.2/bin/postmaster '-p' '5433' '-d' '2' '-i'