pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PG 7.4: duplicate rows in violation of unique constraint
6+ messages / 3 participants
[nested] [flat]

* PG 7.4: duplicate rows in violation of unique constraint
@ 2005-02-10 00:07 Steve Lane <slane@soliantconsulting.com>
  2005-02-10 01:26 ` Re: PG 7.4: duplicate rows in violation of unique constraint Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Steve Lane @ 2005-02-10 00:07 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Jesse LaVere <jlavere@soliantconsulting.com>

All:

Puzzling PG 7.4 behaviors. Began yesterday, running under RedHat ES 2.1.

First symptom were many query failures, along with the message that a server
process had crashed, possibly corrupting shared memory. These messages kept
coming. I shut down the postmaster, and did ipcclean.

Started postmaster again, but then found index problems with certain tables.
Records that we knew were there would not be found via index scan queries.
We then tried to reindex, and that failed as follows:

DETAIL:  Table contains duplicated values.

This on a single column with a unique constraint, populated via a sequence!

We searched for and found the duplicates and pulled them into a separate
table, deleted from the original table, then reindexed.

Looking at the duplicates, they look for all the world like multiple copies
of the same row. Clearly the same record, but in each case the one with the
later timestamp had "more" data. So we picked the latest records by
timestamp and brought them back in.

We've found similar instances on other indexes, but let me start there.

Any ideas? Can MVCC "leak" stale rows back into the "live" space?

-- sgl


--------
Steve Lane
Vice President
Soliant Consulting, Inc.
(312) 850-3830 (V)
(312) 850-3930 (F)
slane@soliantconsulting.com




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: PG 7.4: duplicate rows in violation of unique constraint
  2005-02-10 00:07 PG 7.4: duplicate rows in violation of unique constraint Steve Lane <slane@soliantconsulting.com>
@ 2005-02-10 01:26 ` Joshua D. Drake <jd@commandprompt.com>
  2005-02-10 01:46   ` Re: PG 7.4: duplicate rows in violation of unique Steve Lane <slane@soliantconsulting.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Joshua D. Drake @ 2005-02-10 01:26 UTC (permalink / raw)
  To: Steve Lane <slane@soliantconsulting.com>; +Cc: pgsql-admin@postgresql.org, Jesse LaVere <jlavere@soliantconsulting.com>

Steve Lane wrote:

>All:
>
>Puzzling PG 7.4 behaviors. Began yesterday, running under RedHat ES 2.1.'
>  
>
Which version of 7.4?


>First symptom were many query failures, along with the message that a server
>process had crashed, possibly corrupting shared memory. These messages kept
>coming. I shut down the postmaster, and did ipcclean.
>
>Started postmaster again, but then found index problems with certain tables.
>Records that we knew were there would not be found via index scan queries.
>We then tried to reindex, and that failed as follows:
>
>DETAIL:  Table contains duplicated values.
>
>This on a single column with a unique constraint, populated via a sequence!
>
>We searched for and found the duplicates and pulled them into a separate
>table, deleted from the original table, then reindexed.
>
>Looking at the duplicates, they look for all the world like multiple copies
>of the same row. Clearly the same record, but in each case the one with the
>later timestamp had "more" data. So we picked the latest records by
>timestamp and brought them back in.
>
>We've found similar instances on other indexes, but let me start there.
>
>Any ideas? Can MVCC "leak" stale rows back into the "live" space?
>
>-- sgl
>
>
>--------
>Steve Lane
>Vice President
>Soliant Consulting, Inc.
>(312) 850-3830 (V)
>(312) 850-3930 (F)
>slane@soliantconsulting.com
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>  
>


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Attachments:

jd.vcf (text/x-vcard, 283B) download begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:jd@commandprompt.com title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard
^ permalink raw reply [nested|flat] 6+ messages in thread

* Re: PG 7.4: duplicate rows in violation of unique
  2005-02-10 00:07 PG 7.4: duplicate rows in violation of unique constraint Steve Lane <slane@soliantconsulting.com>
  2005-02-10 01:26 ` Re: PG 7.4: duplicate rows in violation of unique constraint Joshua D. Drake <jd@commandprompt.com>
@ 2005-02-10 01:46   ` Steve Lane <slane@soliantconsulting.com>
  2005-02-10 04:50     ` Re: PG 7.4: duplicate rows in violation of unique Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Steve Lane @ 2005-02-10 01:46 UTC (permalink / raw)
  To: Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-admin; Jesse LaVere <jlavere@soliantconsulting.com>

Sorry -- 7.4.5

-- sgl


> From: "Joshua D. Drake" <jd@commandprompt.com>
> Date: Wed, 09 Feb 2005 17:26:12 -0800
> To: Steve Lane <slane@soliantconsulting.com>
> Cc: <pgsql-admin@postgresql.org>, Jesse LaVere <jlavere@soliantconsulting.com>
> Subject: Re: [ADMIN] PG 7.4: duplicate rows in violation of unique constraint
> 
> Steve Lane wrote:
> 
>> All:
>> 
>> Puzzling PG 7.4 behaviors. Began yesterday, running under RedHat ES 2.1.'
>>  
>> 
> Which version of 7.4?
> 
> 
>> First symptom were many query failures, along with the message that a server
>> process had crashed, possibly corrupting shared memory. These messages kept
>> coming. I shut down the postmaster, and did ipcclean.
>> 
>> Started postmaster again, but then found index problems with certain tables.
>> Records that we knew were there would not be found via index scan queries.
>> We then tried to reindex, and that failed as follows:
>> 
>> DETAIL:  Table contains duplicated values.
>> 
>> This on a single column with a unique constraint, populated via a sequence!
>> 
>> We searched for and found the duplicates and pulled them into a separate
>> table, deleted from the original table, then reindexed.
>> 
>> Looking at the duplicates, they look for all the world like multiple copies
>> of the same row. Clearly the same record, but in each case the one with the
>> later timestamp had "more" data. So we picked the latest records by
>> timestamp and brought them back in.
>> 
>> We've found similar instances on other indexes, but let me start there.
>> 
>> Any ideas? Can MVCC "leak" stale rows back into the "live" space?
>> 
>> -- sgl
>> 
>> 
>> --------
>> Steve Lane
>> Vice President
>> Soliant Consulting, Inc.
>> (312) 850-3830 (V)
>> (312) 850-3930 (F)
>> slane@soliantconsulting.com
>> 
>> 
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>  
>> 
> 
> 
> -- 
> Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
> Postgresql support, programming shared hosting and dedicated hosting.
> +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
> PostgreSQL Replicator -- production quality replication for PostgreSQL
> 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: PG 7.4: duplicate rows in violation of unique
  2005-02-10 00:07 PG 7.4: duplicate rows in violation of unique constraint Steve Lane <slane@soliantconsulting.com>
  2005-02-10 01:26 ` Re: PG 7.4: duplicate rows in violation of unique constraint Joshua D. Drake <jd@commandprompt.com>
  2005-02-10 01:46   ` Re: PG 7.4: duplicate rows in violation of unique Steve Lane <slane@soliantconsulting.com>
@ 2005-02-10 04:50     ` Tom Lane <tgl@sss.pgh.pa.us>
  2005-02-10 05:28       ` Re: PG 7.4: duplicate rows in violation of unique Steve Lane <slane@soliantconsulting.com>
  2005-02-10 05:35       ` Re: PG 7.4: duplicate rows in violation of unique Steve Lane <slane@soliantconsulting.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Tom Lane @ 2005-02-10 04:50 UTC (permalink / raw)
  To: Steve Lane <slane@soliantconsulting.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-admin@postgresql.org, Jesse LaVere <jlavere@soliantconsulting.com>

Steve Lane <slane@soliantconsulting.com> writes:
> Sorry -- 7.4.5

I'm wondering if this could be related to this post-7.4.5 fix:

2004-10-13 18:22  tgl

	* contrib/pgstattuple/pgstattuple.c,
	src/backend/access/heap/heapam.c,
	src/backend/utils/adt/ri_triggers.c (REL7_4_STABLE): Repair
	possible failure to update hint bits back to disk, per
	http://archives.postgresql.org/pgsql-hackers/2004-10/msg00464.php. 

What you are describing sure sounds like a foulup in commit status
marking for multiple versions of a row.  I'm not convinced it would be
fixed by the abovementioned patch, but it sounds close.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: PG 7.4: duplicate rows in violation of unique
  2005-02-10 00:07 PG 7.4: duplicate rows in violation of unique constraint Steve Lane <slane@soliantconsulting.com>
  2005-02-10 01:26 ` Re: PG 7.4: duplicate rows in violation of unique constraint Joshua D. Drake <jd@commandprompt.com>
  2005-02-10 01:46   ` Re: PG 7.4: duplicate rows in violation of unique Steve Lane <slane@soliantconsulting.com>
  2005-02-10 04:50     ` Re: PG 7.4: duplicate rows in violation of unique Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-02-10 05:28       ` Steve Lane <slane@soliantconsulting.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Steve Lane @ 2005-02-10 05:28 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-admin; Jesse LaVere <jlavere@soliantconsulting.com>

Tom:

Thanks. What's odd here is that we seemed to have none of them, and then
found a bunch, immediately after processes starting crashing.

This is not the first time this has happened -- we had an outbreak once
before.

It's quite possible that we had these lurking in there for a while, and only
found them during the checks we did after the process crashes.

Does 7.4.6 incorporate the patch for this?

-- sgl


> From: Tom Lane <tgl@sss.pgh.pa.us>
> Date: Wed, 09 Feb 2005 23:50:47 -0500
> To: Steve Lane <slane@soliantconsulting.com>
> Cc: "Joshua D. Drake" <jd@commandprompt.com>, <pgsql-admin@postgresql.org>,
> Jesse LaVere <jlavere@soliantconsulting.com>
> Subject: Re: [ADMIN] PG 7.4: duplicate rows in violation of unique
> 
> Steve Lane <slane@soliantconsulting.com> writes:
>> Sorry -- 7.4.5
> 
> I'm wondering if this could be related to this post-7.4.5 fix:
> 
> 2004-10-13 18:22  tgl
> 
> * contrib/pgstattuple/pgstattuple.c,
> src/backend/access/heap/heapam.c,
> src/backend/utils/adt/ri_triggers.c (REL7_4_STABLE): Repair
> possible failure to update hint bits back to disk, per
> http://archives.postgresql.org/pgsql-hackers/2004-10/msg00464.php.
> 
> What you are describing sure sounds like a foulup in commit status
> marking for multiple versions of a row.  I'm not convinced it would be
> fixed by the abovementioned patch, but it sounds close.
> 
> regards, tom lane




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: PG 7.4: duplicate rows in violation of unique
  2005-02-10 00:07 PG 7.4: duplicate rows in violation of unique constraint Steve Lane <slane@soliantconsulting.com>
  2005-02-10 01:26 ` Re: PG 7.4: duplicate rows in violation of unique constraint Joshua D. Drake <jd@commandprompt.com>
  2005-02-10 01:46   ` Re: PG 7.4: duplicate rows in violation of unique Steve Lane <slane@soliantconsulting.com>
  2005-02-10 04:50     ` Re: PG 7.4: duplicate rows in violation of unique Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-02-10 05:35       ` Steve Lane <slane@soliantconsulting.com>
  1 sibling, 0 replies; 6+ messages in thread

From: Steve Lane @ 2005-02-10 05:35 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-admin; Jesse LaVere <jlavere@soliantconsulting.com>




> From: Tom Lane <tgl@sss.pgh.pa.us>
> Date: Wed, 09 Feb 2005 23:50:47 -0500
> To: Steve Lane <slane@soliantconsulting.com>
> Cc: "Joshua D. Drake" <jd@commandprompt.com>, <pgsql-admin@postgresql.org>,
> Jesse LaVere <jlavere@soliantconsulting.com>
> Subject: Re: [ADMIN] PG 7.4: duplicate rows in violation of unique
> 
> Steve Lane <slane@soliantconsulting.com> writes:
>> Sorry -- 7.4.5
> 
> I'm wondering if this could be related to this post-7.4.5 fix:
> 
> 2004-10-13 18:22  tgl
> 
> * contrib/pgstattuple/pgstattuple.c,
> src/backend/access/heap/heapam.c,
> src/backend/utils/adt/ri_triggers.c (REL7_4_STABLE): Repair
> possible failure to update hint bits back to disk, per
> http://archives.postgresql.org/pgsql-hackers/2004-10/msg00464.php.
> 
> What you are describing sure sounds like a foulup in commit status
> marking for multiple versions of a row.  I'm not convinced it would be
> fixed by the abovementioned patch, but it sounds close.


By the way -- this problem looks very much like one I reported a few months
ago. I'll replay that email here:

> I'm hoping I'm wrong, but I think I'm looking at a corrupted table or
> database. We have a column that's sequence-driven, with a unique index, but
> we're seeing duplicate values in that column. We're also seeing duplicate OIDs
> in the affected table. And we're seeing this ominous message:
> 
> iep_db=# select distinct * into lavere_form_004 from iep_form_004;
> ERROR:  missing chunk number 0 for toast value 42551774

We're getting the same thing tonight -- same "missing chunk number XX for
toast value YYYY."

If this is some form of MVCC "leakage", can that also explain this error?
Would the "leaked" rows somehow be incomplete in the sense of not linking
correctly to their corresponding toast data?

-- sgl




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2005-02-10 05:35 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-10 00:07 PG 7.4: duplicate rows in violation of unique constraint Steve Lane <slane@soliantconsulting.com>
2005-02-10 01:26 ` Joshua D. Drake <jd@commandprompt.com>
2005-02-10 01:46   ` Re: PG 7.4: duplicate rows in violation of unique Steve Lane <slane@soliantconsulting.com>
2005-02-10 04:50     ` Re: PG 7.4: duplicate rows in violation of unique Tom Lane <tgl@sss.pgh.pa.us>
2005-02-10 05:28       ` Re: PG 7.4: duplicate rows in violation of unique Steve Lane <slane@soliantconsulting.com>
2005-02-10 05:35       ` Re: PG 7.4: duplicate rows in violation of unique Steve Lane <slane@soliantconsulting.com>

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