pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: pg_dump error... Follow up
13+ messages / 3 participants
[nested] [flat]
* Re: pg_dump error... Follow up
@ 2005-09-07 14:49 Adam Witney <awitney@sgul.ac.uk>
0 siblings, 1 reply; 13+ messages in thread
From: Adam Witney @ 2005-09-07 14:49 UTC (permalink / raw)
To: Adam Witney <awitney@sgul.ac.uk>; pgsql-admin
On 7/9/05 10:50 am, "Adam Witney" <awitney@sgul.ac.uk> wrote:
>
> Hi,
>
> I just ran into an error from pg_dump:
>
> /usr/local/pgsql/bin/pg_dump -F p my_db
>
> pg_dump: ERROR: unexpected chunk number 3292 (expected 5) for toast value
> 144391872
> pg_dump: SQL command to dump the contents of table "measured_bioassay_base"
> failed: PQendcopy() failed.
> pg_dump: Error message from server: ERROR: unexpected chunk number 3292
> (expected 5) for toast value 144391872
> pg_dump: The command was: COPY public.measured_bioassay_base (bioassay_id,
> name, owner_id, writer_id, readers_id, is_deleted, delete_time, expt_id,
> protocol_application_id, source_bioassay_id, filename, fileheader, format,
> date_uploaded) TO stdout;
>
> Any ideas whats happened here? It looks like one of my tables hasn't backed
> up correctly. This table has a text field which contains large amounts of
> data per field. The dump file is much smaller than last nights dump.
>
> (PostgreSQL 7.4.8 on i686-pc-linux-gnu, compiled by GCC 2.95.4)
I think I have found the offending row in measured_bioassay_base... The
entry in its toast table looks like this
bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where chunk_id = 144391872;
chunk_id | chunk_seq | length
-----------+-----------+--------
144391872 | 0 | 1998
144391872 | 1 | 1998
144391872 | 2 | 1998
144391872 | 3 | 1998
144391872 | 4 | 1998
7625296 | 3292 | 24
7625297 | 3292 | 24
7625298 | 3292 | 24
7625299 | 3292 | 24
7625308 | 3292 | 19
7625309 | 3292 | 16
7625310 | 3292 | 16
7625311 | 3292 | 20
7625320 | 3292 | 20
7625321 | 3292 | 19
7625322 | 3292 | 19
7625323 | 3292 | 20
7625332 | 3292 | 19
7625333 | 3292 | 19
7625334 | 3292 | 19
7625335 | 3292 | 19
7625344 | 3292 | 20
7625345 | 3292 | 20
7625346 | 3292 | 18
7625347 | 3292 | 18
7625356 | 3292 | 19
7625357 | 3292 | 19
7625358 | 3292 | 18
7625359 | 3292 | 20
7625368 | 3292 | 19
7625369 | 3292 | 20
7625370 | 3292 | 20
7625371 | 3292 | 20
7625380 | 3292 | 19
7625381 | 3292 | 19
7625382 | 3292 | 19
7625383 | 3292 | 19
144391872 | 37 | 1998
144391872 | 38 | 1998
144391872 | 39 | 1998
.. 200 more rows..
I can delete the row in measured_bioassay_base and reload the data for it,
but i'd like to know if it is a symptom of a bigger problem, any ideas how
this might have happened?
Thanks for any help
Adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_dump error... Follow up
@ 2005-09-07 17:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Adam Witney <awitney@sgul.ac.uk>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2005-09-07 17:06 UTC (permalink / raw)
To: Adam Witney <awitney@sgul.ac.uk>; +Cc: pgsql-admin; Michael Fuhr <mike@fuhr.org>
Adam Witney <awitney@sgul.ac.uk> writes:
> I think I have found the offending row in measured_bioassay_base... The
> entry in its toast table looks like this
> bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
> pg_toast.pg_toast_134401982 where chunk_id = 144391872;
> chunk_id | chunk_seq | length
> -----------+-----------+--------
> 144391872 | 0 | 1998
> 144391872 | 1 | 1998
> 144391872 | 2 | 1998
> 144391872 | 3 | 1998
> 144391872 | 4 | 1998
> 7625296 | 3292 | 24
> 7625297 | 3292 | 24
> 7625298 | 3292 | 24
> 7625299 | 3292 | 24
> 7625308 | 3292 | 19
This is pretty wacko, because as far as I can see there is nothing wrong
with the index at all --- in particular, no gap in the chunk id/seq
series here, according to pg_filedump. (The last two words of each
index item are the chunk_id and chunk_seq, and we are looking at
LSB-first data)
Item 102 -- Length: 16 Offset: 6544 (0x1990) Flags: USED
Block Id: 165390 linp Index: 4 Size: 16
Has Nulls: 0 Has Varwidths: 0
1990: 02000e86 04001000 c03e9b08 00000000 .........>......
Item 103 -- Length: 16 Offset: 6528 (0x1980) Flags: USED
Block Id: 165391 linp Index: 1 Size: 16
Has Nulls: 0 Has Varwidths: 0
1980: 02000f86 01001000 c03e9b08 01000000 .........>......
Item 104 -- Length: 16 Offset: 6512 (0x1970) Flags: USED
Block Id: 165391 linp Index: 2 Size: 16
Has Nulls: 0 Has Varwidths: 0
1970: 02000f86 02001000 c03e9b08 02000000 .........>......
Item 105 -- Length: 16 Offset: 6496 (0x1960) Flags: USED
Block Id: 165391 linp Index: 3 Size: 16
Has Nulls: 0 Has Varwidths: 0
1960: 02000f86 03001000 c03e9b08 03000000 .........>......
Item 106 -- Length: 16 Offset: 6480 (0x1950) Flags: USED
Block Id: 165391 linp Index: 4 Size: 16
Has Nulls: 0 Has Varwidths: 0
1950: 02000f86 04001000 c03e9b08 04000000 .........>......
Item 107 -- Length: 16 Offset: 6464 (0x1940) Flags: USED
Block Id: 165392 linp Index: 1 Size: 16
Has Nulls: 0 Has Varwidths: 0
1940: 02001086 01001000 c03e9b08 05000000 .........>......
Item 108 -- Length: 16 Offset: 6448 (0x1930) Flags: USED
Block Id: 165392 linp Index: 2 Size: 16
Has Nulls: 0 Has Varwidths: 0
1930: 02001086 02001000 c03e9b08 06000000 .........>......
Item 109 -- Length: 16 Offset: 6432 (0x1920) Flags: USED
Block Id: 165392 linp Index: 3 Size: 16
Has Nulls: 0 Has Varwidths: 0
1920: 02001086 03001000 c03e9b08 07000000 .........>......
Item 110 -- Length: 16 Offset: 6416 (0x1910) Flags: USED
Block Id: 165392 linp Index: 4 Size: 16
Has Nulls: 0 Has Varwidths: 0
1910: 02001086 04001000 c03e9b08 08000000 .........>......
What I think is going on is that you have a corrupted copy of this index
page sitting in shared buffers. It's not been marked dirty, else it'd
have been written out by a checkpoint and we could see the damage with
pg_filedump. This implies that the problem would go away by itself if
you simply shut down and restarted the database.
However, since you've been so helpful so far ;-) I wonder whether there
is any chance of getting an image of the bad page in memory before you
do that. We are presumably looking at the results of an unintentional
memory stomp somewhere in the code, and it's possible that a dump of the
damaged page would give a clue how it happened.
The only simple way I can think of for getting that info involves gdb,
but I don't suppose you are using a copy built with --enable-debug ...
anyone have other ideas?
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_dump error... Follow up
@ 2005-09-07 17:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2005-09-07 17:12 UTC (permalink / raw)
To: Adam Witney <awitney@sgul.ac.uk>; pgsql-admin; Michael Fuhr <mike@fuhr.org>
I wrote
> Adam Witney <awitney@sgul.ac.uk> writes:
>> I think I have found the offending row in measured_bioassay_base... The
>> entry in its toast table looks like this
>> bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
>> pg_toast.pg_toast_134401982 where chunk_id = 144391872;
>> chunk_id | chunk_seq | length
>> -----------+-----------+--------
>> 144391872 | 0 | 1998
>> 144391872 | 1 | 1998
>> 144391872 | 2 | 1998
>> 144391872 | 3 | 1998
>> 144391872 | 4 | 1998
>> 7625296 | 3292 | 24
>> 7625297 | 3292 | 24
>> 7625298 | 3292 | 24
>> 7625299 | 3292 | 24
>> 7625308 | 3292 | 19
> This is pretty wacko, because as far as I can see there is nothing wrong
> with the index at all --- in particular, no gap in the chunk id/seq
> series here, according to pg_filedump.
Actually, it's also possible that the index is fine and the problem is
in the underlying toast table ... which would be unfortunate, because
it'd mean that there's no easy way out like a REINDEX or database
restart.
We can check this by seeing if the data looks the same using the CTIDs
that we can see in the index. Please try the same query as above, ie,
select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ...
using these WHERE conditions:
ctid = '(165390,4)'
ctid = '(165391,1)'
ctid = '(165391,2)'
ctid = '(165391,3)'
ctid = '(165391,4)'
ctid = '(165392,1)'
ctid = '(165392,2)'
ctid = '(165392,3)'
ctid = '(165392,4)'
It'd also be interesting to look at pg_filedump data for pages 165390
through 165392 of the toast table itself (not the index) --- see -R
option of pg_filedump to limit the range of pages.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_dump error... Follow up
@ 2005-09-08 09:32 Adam Witney <awitney@sgul.ac.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 13+ messages in thread
From: Adam Witney @ 2005-09-08 09:32 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin; Michael Fuhr <mike@fuhr.org>
On 7/9/05 6:12 pm, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> I wrote
>> Adam Witney <awitney@sgul.ac.uk> writes:
>>> I think I have found the offending row in measured_bioassay_base... The
>>> entry in its toast table looks like this
>
>>> bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
>>> pg_toast.pg_toast_134401982 where chunk_id = 144391872;
>>> chunk_id | chunk_seq | length
>>> -----------+-----------+--------
>>> 144391872 | 0 | 1998
>>> 144391872 | 1 | 1998
>>> 144391872 | 2 | 1998
>>> 144391872 | 3 | 1998
>>> 144391872 | 4 | 1998
>>> 7625296 | 3292 | 24
>>> 7625297 | 3292 | 24
>>> 7625298 | 3292 | 24
>>> 7625299 | 3292 | 24
>>> 7625308 | 3292 | 19
>
>> This is pretty wacko, because as far as I can see there is nothing wrong
>> with the index at all --- in particular, no gap in the chunk id/seq
>> series here, according to pg_filedump.
>
> Actually, it's also possible that the index is fine and the problem is
> in the underlying toast table ... which would be unfortunate, because
> it'd mean that there's no easy way out like a REINDEX or database
> restart.
>
> We can check this by seeing if the data looks the same using the CTIDs
> that we can see in the index. Please try the same query as above, ie,
> select chunk_id, chunk_seq, length(chunk_data) from
> pg_toast.pg_toast_134401982 where ...
> using these WHERE conditions:
>
> ctid = '(165390,4)'
> ctid = '(165391,1)'
> ctid = '(165391,2)'
> ctid = '(165391,3)'
> ctid = '(165391,4)'
> ctid = '(165392,1)'
> ctid = '(165392,2)'
> ctid = '(165392,3)'
> ctid = '(165392,4)'
>
> It'd also be interesting to look at pg_filedump data for pages 165390
> through 165392 of the toast table itself (not the index) --- see -R
> option of pg_filedump to limit the range of pages.
A REINDEX on the toast table did not fix the problem. Here is the output
from your queries above:
bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165390,4)';
chunk_id | chunk_seq | length
-----------+-----------+--------
144391872 | 0 | 1998
(1 row)
bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165391,1)';
chunk_id | chunk_seq | length
-----------+-----------+--------
144391872 | 1 | 1998
(1 row)
bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165391,2)';
chunk_id | chunk_seq | length
-----------+-----------+--------
144391872 | 2 | 1998
(1 row)
bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165391,3)';
chunk_id | chunk_seq | length
-----------+-----------+--------
144391872 | 3 | 1998
(1 row)
bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165391,4)';
chunk_id | chunk_seq | length
-----------+-----------+--------
144391872 | 4 | 1998
(1 row)
bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165392,1)';
chunk_id | chunk_seq | length
----------+-----------+--------
7625296 | 3292 | 24
(1 row)
bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165392,2)';
chunk_id | chunk_seq | length
----------+-----------+--------
7625297 | 3292 | 24
(1 row)
bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165392,3)';
chunk_id | chunk_seq | length
----------+-----------+--------
7625298 | 3292 | 24
(1 row)
bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from
pg_toast.pg_toast_134401982 where ctid = '(165392,4)';
chunk_id | chunk_seq | length
----------+-----------+--------
7625299 | 3292 | 24
(1 row)
Unfortunately the pg_filedump you requested gives an error:
pg_filedump -R 165390 165392 134401986
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 3.0
*
* File: 134401986.1
* Options used: -R 165390 165392
*
* Dump created on: Thu Sep 8 10:33:47 2005
*******************************************************************
Error: Premature end of file encountered.
(there is a .1 file also for this table, do I have to stick the two together
or something?)
Thanks again
Adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_dump error... Follow up
@ 2005-09-08 13:38 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Adam Witney <awitney@sgul.ac.uk>
1 sibling, 1 reply; 13+ messages in thread
From: Alvaro Herrera @ 2005-09-08 13:38 UTC (permalink / raw)
To: Adam Witney <awitney@sgul.ac.uk>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin; Michael Fuhr <mike@fuhr.org>
On Thu, Sep 08, 2005 at 10:32:41AM +0100, Adam Witney wrote:
> Unfortunately the pg_filedump you requested gives an error:
>
> pg_filedump -R 165390 165392 134401986
>
> *******************************************************************
> * PostgreSQL File/Block Formatted Dump Utility - Version 3.0
> *
> * File: 134401986.1
> * Options used: -R 165390 165392
> *
> * Dump created on: Thu Sep 8 10:33:47 2005
> *******************************************************************
> Error: Premature end of file encountered.
>
> (there is a .1 file also for this table, do I have to stick the two together
> or something?)
No, rather tell pg_filedump to use the second segment (.1 file) and
substract the total number of blocks in the first segment. If my math
is right there are 131072 blocks of 8kB per 1GB segment, so to dump
block 165390 you need to ask for block 34318 in the second segment:
pg_filedump -R 34318 34320 134401986.1
--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"Et put se mouve" (Galileo Galilei)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_dump error... Follow up
@ 2005-09-08 13:50 Adam Witney <awitney@sgul.ac.uk>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 13+ messages in thread
From: Adam Witney @ 2005-09-08 13:50 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin; Michael Fuhr <mike@fuhr.org>
On 8/9/05 2:38 pm, "Alvaro Herrera" <alvherre@alvh.no-ip.org> wrote:
> On Thu, Sep 08, 2005 at 10:32:41AM +0100, Adam Witney wrote:
>
>> Unfortunately the pg_filedump you requested gives an error:
>>
>> pg_filedump -R 165390 165392 134401986
>>
>> *******************************************************************
>> * PostgreSQL File/Block Formatted Dump Utility - Version 3.0
>> *
>> * File: 134401986.1
>> * Options used: -R 165390 165392
>> *
>> * Dump created on: Thu Sep 8 10:33:47 2005
>> *******************************************************************
>> Error: Premature end of file encountered.
>>
>> (there is a .1 file also for this table, do I have to stick the two together
>> or something?)
>
> No, rather tell pg_filedump to use the second segment (.1 file) and
> substract the total number of blocks in the first segment. If my math
> is right there are 131072 blocks of 8kB per 1GB segment, so to dump
> block 165390 you need to ask for block 34318 in the second segment:
>
> pg_filedump -R 34318 34320 134401986.1
Ah thanks.....
pg_filedump-3.0/pg_filedump -R 34318 34320 134401986.1
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 3.0
*
* File: 134401986.1
* Options used: -R 34318 34320
*
* Dump created on: Thu Sep 8 14:49:05 2005
*******************************************************************
Block 34318 ********************************************************
<Header> -----
Block Offset: 0x10c1c000 Offsets: Lower 36 (0x0024)
Block: Size 8192 Version 1 Upper 272 (0x0110)
LSN: logid 16 recoff 0x2b0ab37c Special 8192 (0x2000)
Items: 4 Free Space: 236
Length (including item array): 40
<Data> ------
Item 1 -- Length: 2034 Offset: 6156 (0x180c) Flags: USED
Item 2 -- Length: 2034 Offset: 4120 (0x1018) Flags: USED
Item 3 -- Length: 1811 Offset: 2308 (0x0904) Flags: USED
Item 4 -- Length: 2034 Offset: 272 (0x0110) Flags: USED
Block 34319 ********************************************************
<Header> -----
Block Offset: 0x10c1e000 Offsets: Lower 36 (0x0024)
Block: Size 8192 Version 1 Upper 48 (0x0030)
LSN: logid 16 recoff 0x2b0ad4d0 Special 8192 (0x2000)
Items: 4 Free Space: 12
Length (including item array): 40
<Data> ------
Item 1 -- Length: 2034 Offset: 6156 (0x180c) Flags: USED
Item 2 -- Length: 2034 Offset: 4120 (0x1018) Flags: USED
Item 3 -- Length: 2034 Offset: 2084 (0x0824) Flags: USED
Item 4 -- Length: 2034 Offset: 48 (0x0030) Flags: USED
Block 34320 ********************************************************
<Header> -----
Block Offset: 0x10c20000 Offsets: Lower 68 (0x0044)
Block: Size 8192 Version 1 Upper 232 (0x00e8)
LSN: logid 16 recoff 0x2d0fedcc Special 8192 (0x2000)
Items: 12 Free Space: 164
Length (including item array): 72
<Data> ------
Item 1 -- Length: 672 Offset: 7520 (0x1d60) Flags: USED
Item 2 -- Length: 668 Offset: 6852 (0x1ac4) Flags: USED
Item 3 -- Length: 656 Offset: 6196 (0x1834) Flags: USED
Item 4 -- Length: 652 Offset: 5544 (0x15a8) Flags: USED
Item 5 -- Length: 700 Offset: 4844 (0x12ec) Flags: USED
Item 6 -- Length: 652 Offset: 4192 (0x1060) Flags: USED
Item 7 -- Length: 668 Offset: 3524 (0x0dc4) Flags: USED
Item 8 -- Length: 656 Offset: 2868 (0x0b34) Flags: USED
Item 9 -- Length: 684 Offset: 2184 (0x0888) Flags: USED
Item 10 -- Length: 648 Offset: 1536 (0x0600) Flags: USED
Item 11 -- Length: 652 Offset: 884 (0x0374) Flags: USED
Item 12 -- Length: 652 Offset: 232 (0x00e8) Flags: USED
*** End of Requested Range Encountered. Last Block Read: 34320 ***
I have deleted the offending row in the table and now pg_dump does not give
the error, so it seems to be fixed.
I do have a copy of the toast.pg_toast_134401982 files 134401986 and
134401986.1 if you want me to do any further analysis for you?
Thanks again for your help
Adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_dump error... Follow up
@ 2005-09-08 14:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Adam Witney <awitney@sgul.ac.uk>
1 sibling, 0 replies; 13+ messages in thread
From: Tom Lane @ 2005-09-08 14:01 UTC (permalink / raw)
To: Adam Witney <awitney@sgul.ac.uk>; +Cc: pgsql-admin; Michael Fuhr <mike@fuhr.org>
Adam Witney <awitney@sgul.ac.uk> writes:
> Unfortunately the pg_filedump you requested gives an error:
> Error: Premature end of file encountered.
> (there is a .1 file also for this table, do I have to stick the two together
> or something?)
Yeah. Each file contains 131072 pages, so the correct place to look
is 165390-131072 in the .1 file, ie
pg_filedump -R 34318 34320 134401986.1
(I need to look at the pg_filedump code, too, because it seems like it
ought to be able to do that for itself...)
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_dump error... Follow up
@ 2005-09-08 14:22 Tom Lane <tgl@sss.pgh.pa.us>
parent: Adam Witney <awitney@sgul.ac.uk>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2005-09-08 14:22 UTC (permalink / raw)
To: Adam Witney <awitney@sgul.ac.uk>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-admin; Michael Fuhr <mike@fuhr.org>
Adam Witney <awitney@sgul.ac.uk> writes:
> Ah thanks.....
> pg_filedump-3.0/pg_filedump -R 34318 34320 134401986.1
Could we see this with -i -f options please? At this level of detail
there's nothing obviously wrong ...
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_dump error... Follow up
@ 2005-09-08 14:46 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2005-09-08 14:46 UTC (permalink / raw)
To: Adam Witney <awitney@sgul.ac.uk>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-admin; Michael Fuhr <mike@fuhr.org>
Adam Witney <awitney@sgul.ac.uk> writes:
> Here you go....
> pg_filedump-3.0/pg_filedump -i -f -R 34318 34320 134401986.1
Thanks. What it looks like to me is that block 34320 (really 165392)
is data from some other file altogether. It's evidently still Postgres
heap data, but instead of having 3 non-null columns as any toast row
ought to have, these rows have 77 columns many of which are nulls.
They've got OIDs, too. Possibly you can work out which table these
rows really belong to. It looks like this ought to be block 415664
of whatever table it belongs to (which would make it block 22448 of
the xxx.3 file of that table, if I did the math right).
So the diagnosis is that somebody wrote a data block to the wrong offset
in the wrong file. Whether this is the fault of Postgres, the kernel,
or the disk drive is difficult to say. We've seen a number of cases in
which table pages got overwritten with data that was obviously of
non-Postgres origin, and in those cases we could blame the kernel or
disk drive with a clear conscience. In this case, since the bogus data
is Postgres data, it could be that it's a bug lurking within Postgres
itself --- or it could be that it's like those past cases.
It might be worth your while to run some memory and disk drive tests.
There's no particular reason to suspect a hardware fault more than a
software one, but this is at least something simple to do. Check for
availability of kernel updates, too.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_dump error... Follow up
@ 2005-09-08 15:26 Adam Witney <awitney@sgul.ac.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Adam Witney @ 2005-09-08 15:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-admin; Michael Fuhr <mike@fuhr.org>
On 8/9/05 3:46 pm, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
> Adam Witney <awitney@sgul.ac.uk> writes:
>> Here you go....
>
>> pg_filedump-3.0/pg_filedump -i -f -R 34318 34320 134401986.1
>
> Thanks. What it looks like to me is that block 34320 (really 165392)
> is data from some other file altogether. It's evidently still Postgres
> heap data, but instead of having 3 non-null columns as any toast row
> ought to have, these rows have 77 columns many of which are nulls.
> They've got OIDs, too. Possibly you can work out which table these
> rows really belong to. It looks like this ought to be block 415664
> of whatever table it belongs to (which would make it block 22448 of
> the xxx.3 file of that table, if I did the math right).
I only have one file with a .3 in that database... Or could it be from a
different database altogether? (although none of the others get much updates
at all)
Thanks again
adam
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_dump error... Follow up
@ 2005-09-08 15:56 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Adam Witney <awitney@sgul.ac.uk>
0 siblings, 2 replies; 13+ messages in thread
From: Alvaro Herrera @ 2005-09-08 15:56 UTC (permalink / raw)
To: Adam Witney <awitney@sgul.ac.uk>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin; Michael Fuhr <mike@fuhr.org>
On Thu, Sep 08, 2005 at 04:26:16PM +0100, Adam Witney wrote:
> On 8/9/05 3:46 pm, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
>
> > Adam Witney <awitney@sgul.ac.uk> writes:
> >> Here you go....
> >
> >> pg_filedump-3.0/pg_filedump -i -f -R 34318 34320 134401986.1
> >
> > Thanks. What it looks like to me is that block 34320 (really 165392)
> > is data from some other file altogether. It's evidently still Postgres
> > heap data, but instead of having 3 non-null columns as any toast row
> > ought to have, these rows have 77 columns many of which are nulls.
> > They've got OIDs, too. Possibly you can work out which table these
> > rows really belong to. It looks like this ought to be block 415664
> > of whatever table it belongs to (which would make it block 22448 of
> > the xxx.3 file of that table, if I did the math right).
>
> I only have one file with a .3 in that database...
How many columns does that table have?
> Or could it be from a different database altogether? (although none of
> the others get much updates at all)
It's not impossible ...
To Tom: could this be caused by a WAL recovery that wrote a page image
to the wrong table? I guess it is very unlikely because the CRC of the
WAL record would likely not match, but it's an idea.
--
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"At least to kernel hackers, who really are human, despite occasional
rumors to the contrary" (LWN.net)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_dump error... Follow up
@ 2005-09-08 15:59 Adam Witney <awitney@sgul.ac.uk>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 0 replies; 13+ messages in thread
From: Adam Witney @ 2005-09-08 15:59 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin; Michael Fuhr <mike@fuhr.org>
> How many columns does that table have?
It has 77 columns. INSERTS are always done on both this table and the table
that had the toast table error within the same transaction if that matters?
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: pg_dump error... Follow up
@ 2005-09-08 16:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 0 replies; 13+ messages in thread
From: Tom Lane @ 2005-09-08 16:28 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Adam Witney <awitney@sgul.ac.uk>; pgsql-admin; Michael Fuhr <mike@fuhr.org>
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> To Tom: could this be caused by a WAL recovery that wrote a page image
> to the wrong table? I guess it is very unlikely because the CRC of the
> WAL record would likely not match, but it's an idea.
I don't see any reason to think that WAL recovery would be more likely
to cause this than normal operation.
If it is a Postgres bug, my thoughts were pointing in the direction of a
race condition that somehow allows the tag (identifier) of a buffer to
be changed before it gets written out. I don't see any way that could
happen ... but given the very small number of reports of such problems
(i.e. 1) it would definitely have to be induced by some really
low-probability event, such as a race condition with a very narrow
window.
Or it might not be our bug. We've certainly seen previous cases in
which either the kernel or the disk drive wrote the wrong data --- it
could be that this is the same thing, only it happened that the "wrong
data" was some other Postgres data that happened to be passing through
the system at about the same time.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2005-09-08 16:28 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-07 14:49 Re: pg_dump error... Follow up Adam Witney <awitney@sgul.ac.uk>
2005-09-07 17:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-07 17:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-08 09:32 ` Adam Witney <awitney@sgul.ac.uk>
2005-09-08 13:38 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-09-08 13:50 ` Adam Witney <awitney@sgul.ac.uk>
2005-09-08 14:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-08 14:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-08 14:46 Re: pg_dump error... Follow up Tom Lane <tgl@sss.pgh.pa.us>
2005-09-08 15:26 ` Adam Witney <awitney@sgul.ac.uk>
2005-09-08 15:56 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-09-08 15:59 ` Adam Witney <awitney@sgul.ac.uk>
2005-09-08 16:28 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox