Re: Error msgs from PostgreSQL server : specified item offset is too large, failed to add item to the page - Mailing list pgsql-novice

From Rajan Bhide
Subject Re: Error msgs from PostgreSQL server : specified item offset is too large, failed to add item to the page
Date
Msg-id FF851C7EEB75954F9BCFB5CA117AB1ECFC3234@delta.nulinkinc.com
Whole thread Raw
In response to Error msgs from PostgreSQL server : specified item offset is too large, failed to add item to the page  ("Rajan Bhide" <rbhide@nulinkinc.com>)
Responses Re: Error msgs from PostgreSQL server : specified item offset is too large, failed to add item to the page
List pgsql-novice
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
|        0 |            0 |             0
 276059309 |  276059320 | public     | cgf_info        | cgf_info_pkey
|        0 |            0 |             0
 276059279 |  276059322 | public     | cdr             | transid_req_idx
|        0 |            0 |             0
 276059279 |  276059287 | public     | cdr             | cdr_transid_key
|        0 |            0 |             0



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
|           317 |           48
 276059309 |  276059320 | public     | cgf_info        | cgf_info_pkey
|           317 |           48
 276059279 |  276059322 | public     | cdr             | transid_req_idx
|          3524 |       734912
 276059279 |  276059287 | public     | cdr             | cdr_transid_key
|          3621 |       735008


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
|            153 |             83 |           178
     17075 | information_schema | sql_implementation_info |
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 |                 |
|                |
 276059289 | public             | cdr_archive             |
228891 |       1576663 |         17470 |      1597176 |              57
|            164 |             85 |           178
(10 rows)


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\\000\242\265\201\237
\200^A^S\203^HT\\000\231\207\2300$\367\244^N\202^L192.168.70.1\205^D\\01
2\234^W\240\246^N\202^L192.168.72.2\207^Gmip.com\210^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\211gEEu\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\254200\201^L^A"r^Ms\2
26@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 \\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\241\\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\\000\242\265\201\237
\200^A^S\203^HT\\000\231\207\2300$\367\244^N\202^L192.168.70.1\205^D\\01
2\234^W\240\246^N\202^L192.168.72.2\207^Gmip.com\210^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\211gEEu\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\254200\201^L^A"r^Ms\2
26@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 \\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\241\\000\\000\\000\
\000');
2004-06-02 19:33:37 LOG:  server process (PID 21474) was terminated by
signal 6

Thanks,
Rajan Bhide


pgsql-novice by date:

Previous
From: Alain-Serge Nagni
Date:
Subject: Postgresql Novice problem
Next
From: Tom Lane
Date:
Subject: Re: Error msgs from PostgreSQL server : specified item offset is too large, failed to add item to the page