FAQ
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

Search Discussions

  • Tom Lane at Jun 3, 2004 at 2:19 pm

    "Rajan Bhide" <rbhide@nulinkinc.com> writes:
    2004-06-02 19:33:36 WARNING: specified item offset is too large
    2004-06-02 19:33:36 PANIC: failed to add item to the page for
    "cdr_transid_key"
    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
  • Rajan Bhide at Jun 3, 2004 at 2:35 pm
    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?

    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
  • Tom Lane at Jun 3, 2004 at 2:43 pm

    "Rajan Bhide" <rbhide@nulinkinc.com> writes:
    But still would like to know the reason to get such error.
    So would I.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 3, '04 at 6:09a
activeJun 3, '04 at 2:43p
posts4
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Rajan Bhide: 2 posts Tom Lane: 2 posts

People

Translate

site design / logo © 2022 Grokbase