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'