[GENERAL] Tracking down cause of duplicated oid in table.
it seems as though the duplicated row popped up as the result of the vacuum full. Reasoning on that would be that vacuum would be the only thing that would legitimately move tuples around in a table without changing their xmin. In this particular case the vacuum full is run out of crontab at 00:33 via: /usr/local/bin/vacuumdb -z -q -f -d qualitysmith -t admin_report_materialized_view Unfortunately I have not found any indication in our log files that the postgres server underwent anything unusual during this timeframe. No machine reboots, no postgres server restarts. For reference: Feb 22 00:02:59 bigbrother postgres[30550]: [162-1] ERROR: column job_type_id of relation promo_requests does not exist Feb 22 00:04:13 bigbrother postgres[30759]: [162-1] LOG: recycled transaction log file 00440060 Feb 22 00:04:13 bigbrother postgres[30759]: [163-1] LOG: recycled transaction log file 00440061 Feb 22 00:04:13 bigbrother postgres[30759]: [164-1] LOG: recycled transaction log file 0044005F Feb 22 00:05:42 bigbrother postgres[31051]: [162-1] ERROR: column job_type_id of relation promo_requests does not exist Feb 22 00:06:50 bigbrother postgres[31240]: [162-1] ERROR: duplicate key violates unique constraint visitor_referal_code_unique_btree_idx Feb 22 00:09:20 bigbrother postgres[31670]: [162-1] LOG: recycled transaction log file 00440062 Feb 22 00:09:20 bigbrother postgres[31670]: [163-1] LOG: recycled transaction log file 00440063 Feb 22 00:09:20 bigbrother postgres[31670]: [164-1] LOG: recycled transaction log file 00440064 Feb 22 00:24:41 bigbrother postgres[1837]: [162-1] ERROR: duplicate key violates unique constraint visitor_referal_code_unique_btree_idx Feb 22 00:28:45 bigbrother postgres[2520]: [162-1] ERROR: duplicate key violates unique constraint visitor_referal_code_unique_btree_idx Feb 22 00:38:19 bigbrother postgres[4224]: [162-1] ERROR: duplicate key violates unique constraint visitor_referal_code_unique_btree_idx Feb 22 00:39:37 bigbrother postgres[4429]: [162-1] LOG: recycled transaction log file 00440065 Feb 22 00:52:23 bigbrother postgres[6811]: [162-1] ERROR: duplicate key violates unique constraint visitor_referal_code_unique_btree_idx So now I am back to wondering if this problem originated when I think that it did (between 2005/02/22 00:00:00 and 2005/02/22 01:00:00) or if this could have been hiding under the surface for longer than that. I am suspecting a hardware/fs driver/kernel bug here, and I am hoping to be able to pinpoint a time in hopes of being able to find any indicator at all that could tell me where this all started. Thanks for your time. -- Jared Carr RBS Interactive - 89 Glass - QualitySmith [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Getting rid of duplicate tables.
Tom Lane wrote: Jared Carr [EMAIL PROTECTED] writes: Item 2 -- Length: 148 Offset: 6860 (0x1acc) Flags: USED XID: min (46034931) CMIN|XMAX: 2 CMAX|XVAC: 0 Block Id: 27 linp Index: 2 Attributes: 23 Size: 28 infomask: 0x2910 (HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) Item 43 -- Length: 148 Offset: 8044 (0x1f6c) Flags: USED XID: min (8051642) CMIN|XMAX: 46034931 CMAX|XVAC: 2 Block Id: 27 linp Index: 2 Attributes: 23 Size: 28 infomask: 0x2910 (HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED) Well, there's the smoking gun ... somebody marked (27,2) as XMIN_COMMITTED, showing that they thought 46034931 was committed, while someone else marked (27,43) as XMAX_INVALID, showing that they thought 46034931 was aborted. So we have some kind of very-infrequent breakage in transaction commit-state lookup. Or a hardware problem, but I suspect we are looking at a bug. Could you check out what pg_clog has for transaction 46034931? This would be pg_clog/002B (which dates your problem to Dec 29 BTW), byte at offset 39BFC hex or 236540 decimal. I forget which way the bits run within the byte but will look it up if you can get me the value of that byte. Here is the appropriate line (line is used *very* loosely there) 00039BF0 04 10 00 00 44 00 14 44 50 00 10 01 00 40 04 40 [EMAIL PROTECTED]@ 39BFC = 0 Jared Carr ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Getting rid of duplicate tables.
First I wish I knew how this was caused but here is our problem. Sometime in the recent past we got a duplicate table. Here is the result of a pg_dump with a pg_restore for just that table. -- -- TOC entry 59 (OID 11462032) -- Name: order_to_do; Type: TABLE; Schema: public; Owner: www -- Data Pos: 0 -- CREATE TABLE order_to_do ( order_id integer DEFAULT 0 NOT NULL, to_do_id text DEFAULT ''::text NOT NULL, date_time timestamp without time zone DEFAULT '0001-01-01 00:00:00'::timestamp without time zone NOT NULL, csr_id integer DEFAULT 0 NOT NULL, supplement text DEFAULT ''::text NOT NULL ); -- -- TOC entry 60 (OID 11462032) -- Name: order_to_do; Type: TABLE; Schema: public; Owner: www -- Data Pos: 0 -- CREATE TABLE order_to_do ( order_id integer DEFAULT 0 NOT NULL, to_do_id text DEFAULT ''::text NOT NULL, date_time timestamp without time zone DEFAULT '0001-01-01 00:00:00'::timestamp without time zone NOT NULL, csr_id integer DEFAULT 0 NOT NULL, supplement text DEFAULT ''::text NOT NULL ); So somehow it appears that we managed to get an *exact* duplicate of the table well at least a duplicate reference to the table somewhere important. First of all how do we get rid of this extra table? Note: live=# drop table order_to_do; ERROR: duplicate key violates unique constraint pg_class_oid_index Secondly any ideas on how it got there in the first place? Can this just happen? Or did someone here have to accidentally do something to create it. Sorry for not having any idea on what caused this, but thanks for any help you can give. Jared ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
