"Rajan Bhide" <rbhide (AT) nulinkinc (DOT) com> writes:
I'm suspicious that that index is corrupted. Does reindexing it make
the problem go away?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Hi Tom,

If you remembered I had posted the reindexing query on the forum last
week.
http://archives.postgresql.org/pgsql...5/msg00102.php

I have external schedule reindexing which DROPs and reCREATEs all the
user-defined indexes on the tables periodically.
Probably I was too early to think the problem have been fixed by
reindexing but seems the problem is still there.
So I have following few queries and I hope I shall get the answers from
all you expert guys here.

1) Is external reindexing required with Postgres 7.4? If yes then why?
2) Whats the overhead of reindexing (DROP and CREATE INDEX) on the live
database?
3) IS there any thing I can add along with reindexing (VACCUM ANALYSE
also already being performed) or
4) Can I avoid reindexing all together and try some thing else?
5) What additional logs should be monitored/enabled to get more help to
resolve this problem?

There is also a PR probably still in open state? Is anybody working on
this issue of PostgreSQL?
BUG #1104: postmaster crashes

Please post your valuable replies.

Thanks,
Rajan Bhide

-----Original Message-----
From: Tom Lane [mailto:tgl (AT) sss (DOT) pgh.pa.us]
Sent: Friday, May 28, 2004 10:32 PM
To: Rajan Bhide
Cc: Bruce Momjian; pgsql-novice (AT) postgresql (DOT) org; Bruno Wolff III; Oliver
Fromme
Subject: Re: [NOVICE] Error msgs from PostgreSQL server : specified item
offset is too large, failed to add item to the page


"Rajan Bhide" <rbhide (AT) nulinkinc (DOT) com> writes:
I'm suspicious that that index is corrupted. Does reindexing it make
the problem go away?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

To enable statistics collection I updated few related entries in
postgresql.conf file but got following msg.

$ 2004-05-28 08:13:01 LOG: could not bind socket for statistics
collector: Cannot assign requested address
2004-05-28 08:13:01 LOG: database system was shut down at 2004-05-28
08:13:00 GMT
2004-05-28 08:13:01 LOG: checkpoint record is at 80/89392010
2004-05-28 08:13:01 LOG: redo record is at 80/89392010; undo record is
at 0/0; shutdown TRUE
2004-05-28 08:13:01 LOG: next transaction ID: 124196777; next OID:
237888262
2004-05-28 08:13:01 LOG: database system is ready

Whats the reason for this?
I am starting Postmaster with -o -I option. (Enabled TCP/IP
communication)

The localhost is avaliable in /etc/hosts file.

$)ifconfig -a
lo0: flags=1000849<UP,LOOPBACK,RUNNING,MULTICAST,IPv4> mtu 8232 index 1
inet 127.0.0.1 netmask ff000000
eri0: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index
2
inet 10.2.5.3 netmask ffff0000 broadcast 10.2.255.255
eri1: flags=1000843<UP,BROADCAST,RUNNING,MULTICAST,IPv4> mtu 1500 index
3
inet 192.168.206.2 netmask ffffff00 broadcast 192.168.206.255

$)nslookup localhost
Server: <undisclosed>
Address: 12.33.234.50

Name: localhost
Address: 127.0.0.1

$)more /etc/hosts
127.0.0.1 localhost


$)more /etc/inet/ipnodes
#
# Internet host table
#
::1 localhost
127.0.0.1 localhost


Thanks,
Rajan Bhide


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Hi Tom, Bruce and other members,


Following are the statistics for the problem : specified item offset is
too large, failed to add item to the page, which I am getting almost
each time with my application.

Can anybody care to analyse and help me resolve this issue.
My App is using : Postgres 7.4.1 and is having 4 tables.
The transaction logfile contains the timestamp. I don't see very
frequent recycling but still fail to understand why the Postgres Server
dies failing to add item to the page error?
What are the reasons to get this error?
My last run was for jes around 30 mins before hitting this error.


cdr_backup=# select * from pg_stat_user_tables;
relid | schemaname | relname | seq_scan |
seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd |
n_tup_del
-----------+--------------------+-------------------------+----------+--
------------+----------+---------------+-----------+-----------+--------
---
17080 | information_schema | sql_languages | 0 |
0 | | | 0 | 0 | 0
276059279 | public | cdr | 337 |
610715 | 0 | 0 | 241738 | 0 | 239736
17095 | information_schema | sql_sizing_profiles | 0 |
0 | | | 0 | 0 | 0
17090 | information_schema | sql_sizing | 0 |
0 | | | 0 | 0 | 0
17070 | information_schema | sql_features | 0 |
0 | | | 0 | 0 | 0
276059289 | public | cdr_archive | 211 |
1408148 | 10657 | 286425 | 239797 | 0 | 237660
276059309 | public | cgf_info | 174 |
174 | 0 | 0 | 1 | 166 | 0
276059299 | public | unacked_archive | 0 |
0 | 258 | 0 | 0 | 0 | 0
17085 | information_schema | sql_packages | 0 |
0 | | | 0 | 0 | 0
17075 | information_schema | sql_implementation_info | 0 |
0 | | | 0 | 0 | 0
(10 rows)



cdr_backup=# select * from pg_stat_user_indexes;
relid | indexrelid | schemaname | relname |
indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
-----------+------------+------------+-----------------+----------------
----------------+----------+--------------+---------------
276059289 | 276059297 | public | cdr_archive |
cdr_archive_accesstime_key | 12 | 24031 | 24031
276059299 | 276059307 | public | unacked_archive |
unacked_archive_accesstime_key | 0 | 0 | 0
276059289 | 276059323 | public | cdr_archive |
access_time_req_idx | 10645 | 262394 | 262394
276059299 | 276059324 | public | unacked_archive |
unacked_access_time_req_idx | 258 | 0 | 0
276059309 | 276059325 | public | cgf_info | cgfaddr_idx


cdr_backup=# select * from pg_statio_user_indexes;
relid | indexrelid | schemaname | relname |
indexrelname | idx_blks_read | idx_blks_hit
-----------+------------+------------+-----------------+----------------
----------------+---------------+--------------
276059289 | 276059297 | public | cdr_archive |
cdr_archive_accesstime_key | 8550 | 804611
276059299 | 276059307 | public | unacked_archive |
unacked_archive_accesstime_key | 9 | 6
276059289 | 276059323 | public | cdr_archive |
access_time_req_idx | 8920 | 792565
276059299 | 276059324 | public | unacked_archive |
unacked_access_time_req_idx | 9 | 264
276059309 | 276059325 | public | cgf_info | cgfaddr_idx

cdr_backup=# select * from pg_statio_user_tables;
relid | schemaname | relname |
heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit |
toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-----------+--------------------+-------------------------+-------------
---+---------------+---------------+--------------+-----------------+---
-------------+----------------+---------------
17095 | information_schema | sql_sizing_profiles |
0 | 0 | | | 0 |
0 | 0 | 0
276059279 | public | cdr |
704411 | 598188 | 7145 | 1469920 | 70
0 | 0 | | | 0 |
0 | 0 | 0
17090 | information_schema | sql_sizing |
0 | 0 | | | 0 |
0 | 0 | 0
276059299 | public | unacked_archive |
0 | 0 | 18 | 270 | 0 |
0 | 9 | 6
17085 | information_schema | sql_packages |
0 | 0 | | | 0 |
0 | 0 | 0
17070 | information_schema | sql_features |
0 | 0 | | | 0 |
0 | 0 | 0
17080 | information_schema | sql_languages |
0 | 0 | | | 0 |
0 | 0 | 0
276059309 | public | cgf_info |
150 | 839 | 634 | 96 | |
228891 | 1576663 | 17470 | 1597176 | 57


Postgres Log File:
======================

2004-06-02 18:54:04 LOG: recycled transaction log file
"000000920000005A"
2004-06-02 18:56:34 LOG: recycled transaction log file
"000000920000005C"
2004-06-02 18:56:34 LOG: recycled transaction log file
"000000920000005B"
2004-06-02 18:56:34 LOG: recycled transaction log file
"000000920000005D"
2004-06-02 18:59:06 LOG: recycled transaction log file
"000000920000005F"
2004-06-02 18:59:06 LOG: recycled transaction log file
"0000009200000060"
2004-06-02 18:59:06 LOG: recycled transaction log file
"000000920000005E"
2004-06-02 19:00:34 LOG: recycled transaction log file
"0000009200000062"
2004-06-02 19:00:34 LOG: recycled transaction log file
"0000009200000063"
2004-06-02 19:00:34 LOG: recycled transaction log file
"0000009200000061"
2004-06-02 19:02:48 LOG: recycled transaction log file
"0000009200000064"
2004-06-02 19:02:48 LOG: recycled transaction log file
"0000009200000065"
2004-06-02 19:02:48 LOG: recycled transaction log file
"0000009200000066"
2004-06-02 19:04:47 LOG: recycled transaction log file
"0000009200000067"
2004-06-02 19:04:47 LOG: recycled transaction log file
"0000009200000068"
2004-06-02 19:04:47 LOG: recycled transaction log file
"0000009200000069"
2004-06-02 19:06:24 LOG: recycled transaction log file
"000000920000006A"
2004-06-02 19:06:24 LOG: recycled transaction log file
"000000920000006B"
2004-06-02 19:06:24 LOG: recycled transaction log file
"000000920000006C"
2004-06-02 19:08:52 LOG: recycled transaction log file
"000000920000006D"
2004-06-02 19:08:52 LOG: recycled transaction log file
"000000920000006E"
2004-06-02 19:08:52 LOG: recycled transaction log file
"000000920000006F"
2004-06-02 19:10:25 LOG: recycled transaction log file
"0000009200000070"
2004-06-02 19:10:25 LOG: recycled transaction log file
"0000009200000071"
2004-06-02 19:10:25 LOG: recycled transaction log file
"0000009200000072"
2004-06-02 19:12:34 LOG: recycled transaction log file
"0000009200000073"
2004-06-02 19:12:34 LOG: recycled transaction log file
"0000009200000074"
2004-06-02 19:12:34 LOG: recycled transaction log file
"0000009200000075"
2004-06-02 19:14:33 LOG: recycled transaction log file
"0000009200000076"
2004-06-02 19:14:33 LOG: recycled transaction log file
"0000009200000077"
2004-06-02 19:14:33 LOG: recycled transaction log file
"0000009200000078"
2004-06-02 19:16:28 LOG: recycled transaction log file
"0000009200000079"
2004-06-02 19:16:28 LOG: recycled transaction log file
"000000920000007A"
2004-06-02 19:16:28 LOG: recycled transaction log file
"000000920000007B"
2004-06-02 19:19:00 LOG: recycled transaction log file
"000000920000007C"
2004-06-02 19:19:00 LOG: recycled transaction log file
"000000920000007D"
2004-06-02 19:19:00 LOG: recycled transaction log file
"000000920000007E"
2004-06-02 19:20:49 LOG: recycled transaction log file
"000000920000007F"
2004-06-02 19:20:49 LOG: recycled transaction log file
"0000009200000080"
2004-06-02 19:20:49 LOG: recycled transaction log file
"0000009200000081"
2004-06-02 19:23:22 LOG: recycled transaction log file
"0000009200000082"
2004-06-02 19:23:22 LOG: recycled transaction log file
"0000009200000083"
2004-06-02 19:23:22 LOG: recycled transaction log file
"0000009200000084"
2004-06-02 19:24:58 LOG: recycled transaction log file
"0000009200000085"
2004-06-02 19:24:58 LOG: recycled transaction log file
"0000009200000086"
2004-06-02 19:24:58 LOG: recycled transaction log file
"0000009200000087"
2004-06-02 19:27:08 LOG: recycled transaction log file
"0000009200000088"
2004-06-02 19:27:08 LOG: recycled transaction log file
"0000009200000089"
2004-06-02 19:27:08 LOG: recycled transaction log file
"000000920000008A"
2004-06-02 19:29:20 LOG: recycled transaction log file
"000000920000008B"
2004-06-02 19:29:20 LOG: recycled transaction log file
"000000920000008C"
2004-06-02 19:29:20 LOG: recycled transaction log file
"000000920000008D"
2004-06-02 19:31:15 LOG: recycled transaction log file
"000000920000008E"
2004-06-02 19:31:15 LOG: recycled transaction log file
"000000920000008F"
2004-06-02 19:31:15 LOG: recycled transaction log file
"0000009200000090"
2004-06-02 19:33:36 WARNING: specified item offset is too large
STATEMENT: INSERT INTO cdr VALUES(18223190812395382, 3232253462, 0, 0,
12, 2, 5323, 241886, '^A|\\000\\000\\000\\000\\000\\000\\000\\000\\
000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\ 000\\000\\000\\000\\00
0\\000\\000\\000\\000\\000\\000\374^AL^B^A^T^F\\00 0\242\265\201\237
\200^A^S\203^HT\\000\231\207\2300$\367\244^N\202^L 192.168.70.1\205^D\\01
2\234^W\240\246^N\202^L192.168.72.2\207^Gmip.com\2 10^B\361!\213^A\3
77\254200\201^L^A"r^Ms\226@H\206^C@H\202^L^A"r^Ms\ 226@H\206^C@H\203^A^\\
204^A:\205^A^B\206 ^D^F^B \\047X-^D\\000\215 ^D^F^B \\04
7X-^D\\000\216^A\\000\217^A\\000\225^A^A\226^F\221\21 1gEEu\227^B^H\\000\
230^A\\000\233^CT\360P\\000\242\265\201\237\200^A^ S\203^HT\\000\231
\207\2300$\370\244^N\202^L192.168.70.1\205^D\\012\ 234^W\241\246^N\202^L1
92.168.72.2\207^Gmip.com\210^B\361!\213^A\377\2542 00\201^L^A"r^Ms\2
26@H\206^C@H\202^L^A"r^Ms\226@H\206^C@H\203^A^\\20 4^A:\205^A^B\206
^D^F^B \\047X-^D\\000\215 ^D^F^B \\047X-^D\\000\216^A\\000\21
7^A\\000\225^A^A\226^F\221\211gEE\205\227^B^H\\000 \230^A\\000\233^CT\360
P\\012\234^W\240\\000\\000\\000\\000\\012\234^W\24 1\\000\\000\\000\
\000');
2004-06-02 19:33:36 PANIC: failed to add item to the page for
"cdr_transid_key"
STATEMENT: INSERT INTO cdr VALUES(18223190812395382, 3232253462, 0, 0,
12, 2, 5323, 241886, '^A|\\000\\000\\000\\000\\000\\000\\000\\000\\
000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\ 000\\000\\000\\000\\00
0\\000\\000\\000\\000\\000\\000\374^AL^B^A^T^F\\00 0\242\265\201\237
\200^A^S\203^HT\\000\231\207\2300$\367\244^N\202^L 192.168.70.1\205^D\\01
2\234^W\240\246^N\202^L192.168.72.2\207^Gmip.com\2 10^B\361!\213^A\3
77\254200\201^L^A"r^Ms\226@H\206^C@H\202^L^A"r^Ms\ 226@H\206^C@H\203^A^\\
204^A:\205^A^B\206 ^D^F^B \\047X-^D\\000\215 ^D^F^B \\04
7X-^D\\000\216^A\\000\217^A\\000\225^A^A\226^F\221\21 1gEEu\227^B^H\\000\
230^A\\000\233^CT\360P\\000\242\265\201\237\200^A^ S\203^HT\\000\231
\207\2300$\370\244^N\202^L192.168.70.1\205^D\\012\ 234^W\241\246^N\202^L1
92.168.72.2\207^Gmip.com\210^B\361!\213^A\377\2542 00\201^L^A"r^Ms\2
26@H\206^C@H\202^L^A"r^Ms\226@H\206^C@H\203^A^\\20 4^A:\205^A^B\206
^D^F^B \\047X-^D\\000\215 ^D^F^B \\047X-^D\\000\216^A\\000\21
7^A\\000\225^A^A\226^F\221\211gEE\205\227^B^H\\000 \230^A\\000\233^CT\360
P\\012\234^W\240\\000\\000\\000\\000\\012\234^W\24 1\\000\\000\\000\
\000');
2004-06-02 19:33:37 LOG: server process (PID 21474) was terminated by
signal 6

Thanks,
Rajan Bhide


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

"Rajan Bhide" <rbhide (AT) nulinkinc (DOT) com> writes:
I've looked at the code fairly carefully and I don't see how this could
happen. Can you provide a self-contained test case that makes it
happen?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

happen?

I would try to build a test program. Currently this is happening with my
production app on longevity run.
But still would like to know the reason to get such error. Any Postgres
document or other links would be helpful.
Is there any help from the stat stuff provided in the earliar mail?
Any fine tuning or changes in postgresql.conf file required?
My app makes 18 conncurrent connection with the Postgres server?
I was using periodic reindexing, but that too did'nt helped much, so I
have disbled it again. Jes doing VACCUM ANALYSE on every table after
every 10 mins.

BTW the source code was compiled on SunOS5.8 using Sun Forte's cc
compiler:
/configure --prefix=/usr/local/pgsql --enable-thread-safety
--without-readline CFLAGS='-D_REENTRANT' CPPFLAGS='-mt'
cc=/export/home/uxapps/workshop/SUNWspro/bin/cc
Does this helps?

Thanks,
Rajan Bhide


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

"Rajan Bhide" <rbhide (AT) nulinkinc (DOT) com> writes:
So would I.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend