[GENERAL] Rows missing from table despite FK constraint
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 missing. It is also missing from main database, but not from the 9 PM dump. The main database is in curious state: The row from master table is missing, the row referencing it from slave table is present, and finally the FK constraint on slave is in place. Do you have any ideas on how it could possibly happen? What research could help find the root cause and fix the database? Thanks. -- 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
Re: [GENERAL] Rows missing from table despite FK constraint
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? I suspect it is a bug in PostgreSQL. Does it resemble an already submitted issue? -- 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
Re: [GENERAL] Rows missing from table despite FK constraint
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 suspicious (searching by table name, grepping for error and vacuum, quick scan from when the state was consistent until when it was not). Also, reindex on the master table did not help. Is there anything I could do on logs or the database that could help diagnose the issue? -- 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
Re: [GENERAL] Rows missing from table despite FK constraint
> 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 rows that were present in master table and are gone, not superfluous rows in slave table. 3. The master table has this rule preventing DELETE on it: Rules: master_table_no_delete AS ON DELETE TO master_table DO INSTEAD SELECT no_delete() AS no_delete Where no_delete is: begin raise exception 'Cannot remove rows from the table.'; end; I am pretty confident it was not caused by us or our software. The data is relatively old, the missing piece is small and the no_delete rule is in place. Can it be a side effect of some PG job, such as vacuum etc? Let me know what other information could be helpful. -- 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
Re: [GENERAL] Rows missing from table despite FK constraint
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 version? Is your data on some kind of RAID array? If > so, what type (hardware/software, RAID type)? It's ext3 on a hardware RAID1. The array is in perfect condition, according to its diag tool. The OS is Ubuntu 8.04. The exact PG version is 8.3.8. > 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 > storage is set up to be. You are correct about the first point. It's a write-only table with thousands of inserts daily, and the lost rows were written 7 months ago. -- 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
Re: [GENERAL] Rows missing from table despite FK constraint
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 to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rows missing from table despite FK constraint
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 pg_class where relname = 'attachment';" (returns 922494) 3. Run: pg_filedump -i -f -R 603712 603714 /var/lib/postgresql/8.3/main/base/922438/922494 > myfile 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. -- 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
Re: [GENERAL] Rows missing from table despite FK constraint
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 (header etc.) Item 15 -- Length: 184 Offset: 5496 (0x1578) Flags: NORMAL XMIN: 8124 XMAX: 0 CID|XVAC: 0 Block Id: 79424 linp Index: 15 Attributes: 19 Size: 32 infomask: 0x0903 (HASNULL|HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID) t_bits: [0]: 0xff [1]: 0x79 [2]: 0x07 Has ctid equal (79424,15). How do I run pg_filedump for rows with ctid between (603712,78) and (603714,1)? -- 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
Re: [GENERAL] Rows missing from table despite FK constraint
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
Re: [GENERAL] Rows missing from table despite FK constraint
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rows missing from table despite FK constraint
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: 5246886698902745063 Database cluster state: in production pg_control last modified: Fri 08 Jan 2010 10:20:56 AM CST Latest checkpoint location: 1D6/186B6BA0 Prior checkpoint location:1D6/165DAF60 Latest checkpoint's REDO location:1D6/174C8FB8 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 0/83037806 Latest checkpoint's NextOID: 142180690 Latest checkpoint's NextMultiXactId: 2250472 Latest checkpoint's NextMultiOffset: 5954794 Time of latest checkpoint:Fri 08 Jan 2010 10:18:33 AM CST Minimum recovery ending location: 0/0 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment:16777216 Maximum length of identifiers:64 Maximum columns in an index: 32 Maximum size of a TOAST chunk:1996 Date/time type storage: 64-bit integers Maximum length of locale name:128 LC_COLLATE: en_US.UTF-8 LC_CTYPE: en_US.UTF-8 2 mins around the missing rows: ctid | xmin | attachment_id -+--+--- (603712,67) | 17140362 | 15460680 (603712,69) | 17140363 | 15460871 (603712,71) | 17140364 | 15460681 (603712,73) | 17140368 | 15460872 (603712,75) | 17140369 | 15460682 (603712,78) | 17140373 | 15460873 (603714,1) | 17140379 | 15460685 (603714,3) | 17140380 | 15460473 (603714,5) | 17140381 | 15460875 (603714,7) | 17140382 | 15460686 (603714,9) | 17140383 | 15460474 -- 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
Re: [GENERAL] Rows missing from table despite FK constraint
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
Re: [GENERAL] Rows missing from table despite FK constraint
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 damage be done by vacuum? -- 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
Re: [GENERAL] Rows missing from table despite FK constraint
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 I understand what you say, but the 922494 file has 12 parts (922494, 922494.1 through 922494.11). The file I gave you is, indeed, dumped from 922494.4. -- 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
Re: [GENERAL] Rows missing from table despite FK constraint
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? # 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rows missing from table despite FK constraint
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 looking for the row with another index and with seq scan and could not see it. -- 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
Re: [GENERAL] Rows missing from table despite FK constraint
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 | Description +-+---+- attachment_id | integer | not null | entity_kind| character varying(15) | | entity_id | integer | | attached_by| integer | not null | when_attached | timestamp without time zone | not null | when_uploaded | timestamp without time zone | | file_name | character varying(255) | not null | file_size | integer | not null | hash | character varying(50) | | description| character varying(300) | | thumb | bytea | | target_entity_kind | character varying(15) | | target_entity_id | integer | | file_size_enc | bigint | | hash_enc | character varying(50) | | secure_key | bytea | | status | character varying(50) | | width | integer | | height | integer | | lat| numeric(10,7) | | lon| numeric(10,7) | | created_date | timestamp without time zone | | created_time | integer | | Indexes: "attachment_pkey" PRIMARY KEY, btree (attachment_id) "attachment_by_entity" btree (entity_kind, entity_id) "attachment_by_entity_id" btree (entity_id) "attachment_by_target_entity" btree (target_entity_kind, target_entity_id) "attachment_by_uploaded" btree (when_uploaded) "attachment_by_user" btree (attached_by) "attachment_hash_ix" btree (hash) Foreign-key constraints: "fk8af75923d38260d2" FOREIGN KEY (attached_by) REFERENCES usr(usr_id) Rules: attachment_no_delete AS ON DELETE TO attachment DO INSTEAD SELECT no_delete() AS no_delete Has OIDs: no -- 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
Re: [GENERAL] Rows missing from table despite FK constraint
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 attachment where when_uploaded < '2010-01-01'; count -- 22523642 (1 row) On production database: # explain select count(*) from attachment where when_uploaded < '2010-01-01'; QUERY PLAN -- Aggregate (cost=1794931.20..1794931.21 rows=1 width=0) -> Seq Scan on attachment (cost=0.00..1738076.24 rows=22741985 width=0) Filter: (when_uploaded < '2010-01-01 00:00:00'::timestamp without time zone) (3 rows) # select count(*) from attachment where when_uploaded < '2010-01-01'; count -- 22523639 (1 row) -- 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
Re: [GENERAL] Rows missing from table despite FK constraint
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. Thank you all for help. -- 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
