Guys,
just following up on the issue...
For no apparent reason the row became visible in seq scans. After
another REINDEX everything appears to be correct. I don't know what
the issue was and I'm rather uncomfortable about how it appeared and
went away, but anyway it seems to have been resolved.
2010/1/8 Alban Hertroys :
> Did you turn off seqscans in the postgres.conf?
Seq scan is enabled.
> Could you try a "REINDEX TABLE attachment" again in case you somehow
> reindexed the wrong index or table?
How about this test?
On a dump from before the rows were gone:
# select count(*) from
Alban Hertroys writes:
> You seem to know what you're doing, but just in case we missed something as
> this is strange enough to have even the devs scratching their heads. The rows
> are there, so it _has_ to be an index or a transaction visibility issue...
The successful fetch-by-ctid test see
On 8 Jan 2010, at 18:28, Tom Lane wrote:
>> # select attachment_id from attachment where ctid = '(603713,1)';
>> attachment_id
>> ---
>> 15460683
>> (1 row)
>
>> # select attachment_id from attachment where attachment_id = 15460683;
>> attachment_id
>> ---
>> (0 rows)
Konrad Garus writes:
> 2010/1/8 Tom Lane :
>> What the above says is that you have a
>> corrupt index on attachment_id, which you should be able to fix via
>> REINDEX.
> This is not correct. The dumps are made with pg_dump. We did reindex
> on the table. I also tried looking for the row with anot
2010/1/8 Adrian Klaver :
> This looks a lot like this thread:
> http://archives.postgresql.org/pgsql-general/2009-12/msg00726.php
>
> Could we see the schema and indexes for this table?
Table "public.attachment"
Column |Type | Modifiers
On 01/08/2010 09:31 AM, Konrad Garus wrote:
2010/1/8 Tom Lane:
Oh, so the row *is* there.
Right. I'm happy to see it.
What the above says is that you have a
corrupt index on attachment_id, which you should be able to fix via
REINDEX.
This is not correct. The dumps are made with pg_dump.
2010/1/8 Tom Lane :
> Oh, so the row *is* there.
Right. I'm happy to see it.
> What the above says is that you have a
> corrupt index on attachment_id, which you should be able to fix via
> REINDEX.
This is not correct. The dumps are made with pg_dump. We did reindex
on the table. I also tried l
Konrad Garus writes:
> 2010/1/8 Tom Lane :
>> Just to confirm, if you try to select any of these rows by ctid, ie
>> select * from tablename where ctid = '(603713,1)';
>> you get nothing? What *should* happen is that you get the row if you
>> mention offset 1, 3, or 5, but nothing if you s
2010/1/8 Tom Lane :
> Just to confirm, if you try to select any of these rows by ctid, ie
> select * from tablename where ctid = '(603713,1)';
> you get nothing? What *should* happen is that you get the row if you
> mention offset 1, 3, or 5, but nothing if you say 2 or 4.
How about this?
Konrad Garus writes:
> 2010/1/8 Alvaro Herrera :
>> I'm a bit surprised by the block numbers in the block header vs. t_self ...
>> I would have guessed that they come from a different segment (and
>> the numbers seem to match, as 603713 % 131072 = 79425), but Konrad
>> doesn't seem to be using the
2010/1/8 Alvaro Herrera :
> I'm a bit surprised by the block numbers in the block header vs. t_self ...
> I would have guessed that they come from a different segment (and
> the numbers seem to match, as 603713 % 131072 = 79425), but Konrad
> doesn't seem to be using the foo.4 file.
I am not sure
Konrad Garus escribió:
> 2010/1/8 Tom Lane :
> > So, no wraparound problem ... odder and odder. Could we see the whole
> > -i -f printout for that block? You trimmed some of it before,
> > particularly the block header.
>
> Attached.
>
> Since data on disk looks correct, is it possible to diagn
2010/1/8 Tom Lane :
> So, no wraparound problem ... odder and odder. Could we see the whole
> -i -f printout for that block? You trimmed some of it before,
> particularly the block header.
Attached.
Since data on disk looks correct, is it possible to diagnose it on a
higher level? Could the dam
Konrad Garus writes:
> Latest checkpoint's NextXID: 0/83037806
So, no wraparound problem ... odder and odder. Could we see the whole
-i -f printout for that block? You trimmed some of it before,
particularly the block header.
regards, tom lane
--
Sent via pgs
Just a reminder - these rows are over 6 months old and were lost at
night when the system was lightly used.
--
Konrad Garus
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2010/1/8 Tom Lane :
> Also, what are the XMINs of the non-missing tuples in the adjacent
> blocks?
# /usr/lib/postgresql/8.3/bin/pg_controldata /var/lib/postgresql/8.3/main/
pg_control version number:833
Catalog version number: 200711281
Database system identifier:
I wrote:
> Huh. Nothing obviously wrong with the data ... maybe an xid wraparound
> issue? What's your current XID counter? (pg_controldata is the easiest
> way to answer that)
Also, what are the XMINs of the non-missing tuples in the adjacent
blocks?
regards, tom lane
Konrad Garus writes:
> 2010/1/8 Tom Lane :
>> So which of these rows are invisible? According to the flags
>> items 1, 3 and 5 should be visible while 2 and 4 are dead versions
>> (of 3 and 5 respectively).
> All 3 are invisible, and at the same time they are the only 3 rows
> missing from the t
2010/1/8 Tom Lane :
> So which of these rows are invisible? According to the flags
> items 1, 3 and 5 should be visible while 2 and 4 are dead versions
> (of 3 and 5 respectively).
All 3 are invisible, and at the same time they are the only 3 rows
missing from the table.
--
Konrad Garus
--
Se
Konrad Garus writes:
> OK, I got it. Attached is the dump of the missing block.
So which of these rows are invisible? According to the flags
items 1, 3 and 5 should be visible while 2 and 4 are dead versions
(of 3 and 5 respectively).
regards, tom lane
--
Sent via pgsq
OK, I got it. Attached is the dump of the missing block.
--
Konrad Garus
missing_block
Description: Binary data
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2010/1/8 Konrad Garus :
> I'm unsure about it, because the resulting file does not seem to have
> the rows I saw listed for block 603712 or 603714. I checked by text in
> VARCHAR columns.
I must've done something wrong.
I found that row listed at:
Block 603712 **
How shall I do it? Is this correct:
1. Run:
select ctid, * from attachment where ...
on the table with such a WHERE clause that includes rows around the
missing ones. ctid around missing rows seems to be (603712,78) and
(603714,1). Note that 603713 is missing.
2. Run:
select relfilenode from p
Konrad Garus writes:
> 2010/1/8 Tom Lane :
>> Do you know that the rows disappeared recently?
> Yes. They are present in dump from 9 PM and missing from dump from 1
> AM. It must've happened within this 4-hour window.
Hm. It would be interesting to see if you can find the place where the
rows h
2010/1/8 Tom Lane :
> Do you know that the rows disappeared recently?
Yes. They are present in dump from 9 PM and missing from dump from 1
AM. It must've happened within this 4-hour window.
--
Konrad Garus
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes t
Konrad Garus writes:
> 2010/1/8 Alban Hertroys :
>> I get the impression the data you lost and the data around it hasn't been
>> written to in a long time; it wouldn't surprise me if your problem would
>> have been caused by a bad sector on a disk, but that depends on how reliable
>> your stora
2010/1/8 Alban Hertroys :
> You seem to have lost the actual data, not the index entries pointing to it,
> or a sequential scan (eg. pg_dump) would still have found your rows.
I agree.
> What kind of file-system is the affected table on? - and while we're at it,
> what OS/Distribution and vers
On 7 Jan 2010, at 11:12, Konrad Garus wrote:
> Hello,
>
> We use PG 8.3. We use pg_dump and pg_restore overnight to create
> ...
You seem to have lost the actual data, not the index entries pointing to it, or
a sequential scan (eg. pg_dump) would still have found your rows.
> Do you have any i
> So, question is: did you disable triggers sometime on the referenced table?
No, at least not intentionally. More information:
1. Missing are 3 rows added quickly one after another over half a
year ago. They were lost this week. That is the only corruption I am
aware of.
2. The problem is row
Le 07/01/2010 11:12, Konrad Garus a écrit :
> Hello,
>
> We use PG 8.3. We use pg_dump and pg_restore overnight to create
> copies of main database for reporting etc. One dump/restore runs at 9
> PM, another at 11 PM.
>
> Today I discovered that the restore at 11 PM failed to recreate a
> foreign
2010/1/7 Albe Laurenz :
> If that's really the case, it sounds like curruption.
>
> Is there anything in the server logs?
I'm pretty sure it is corruption. With FK in place I don't see any
other way how it would be possible.
The log is too large to read through, but I haven't spotted anything
su
Konrad Garus wrote:
> We use PG 8.3. We use pg_dump and pg_restore overnight to create
> copies of main database for reporting etc. One dump/restore runs at 9
> PM, another at 11 PM.
>
> Today I discovered that the restore at 11 PM failed to recreate a
> foreign key constraint, because one row fro
One more bit of information. The master table has exactly 3 rows
missing. They were all inserted half a year ago within one minute and
no other rows have been inserted in between them.
Is it possible that we lost a consistent piece of data (like a block
or a page)? What can I do to track it down?
Hello,
We use PG 8.3. We use pg_dump and pg_restore overnight to create
copies of main database for reporting etc. One dump/restore runs at 9
PM, another at 11 PM.
Today I discovered that the restore at 11 PM failed to recreate a
foreign key constraint, because one row from master table was missi
35 matches
Mail list logo
