pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDuplicate tuples with unique index
13+ messages / 5 participants
[nested] [flat]
* Duplicate tuples with unique index
@ 2000-01-21 15:09 Patrik Kudo <kudo@partitur.se>
0 siblings, 1 reply; 13+ messages in thread
From: Patrik Kudo @ 2000-01-21 15:09 UTC (permalink / raw)
To: pgsql-sql; +Cc: girgen@partitur.se
We've experienced problems with a database duplicating tuples in one of
the
tables. The problem was found during the daily vacuums. Here is an
example:
db=> vacuum ;
NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0.
NOTICE: Index html_text_idx: NUMBER OF INDEX' TUPLES (1193) IS NOT THE
SAME AS HEAP' (1258)
ERROR: Cannot insert a duplicate key into a unique index
db=> vacuum analyze;
NOTICE: CreatePortal: portal <vacuum> already exists
NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0.
NOTICE: Index html_text_idx: NUMBER OF INDEX' TUPLES (1193) IS NOT THE
SAME AS HEAP' (1258)
ERROR: Cannot insert a duplicate key into a unique index
First I found two doubled tuples where the duplicates had different
oid (using "select * from html_text a, html_text b where a.id = b.id and
a.oid != b.oid").
I removed the duplicates and tried with another vacuum. This did not
solve the
problem, so I did the following:
drop index html_text_idx; (this was a unique index...)
db=> select count(*) from html_text;
count
-----
1259
(1 row)
db=> select distinct id from html_text;
... lot of rows ...
(1211 rows)
Since I could not find any duplicates using my first method,
I started looking for duplicate oids...
excerpt from "select id, oid from html_text"
id oid
-----------
3180|667648
3180|667648
3181|676704
3185|668576
3185|668576
3187|673281
3200|672992
3200|672992
3201|699074
3201|699074
3206|672513
3208|680128
3208|680128
3212|674849
3218|679137
3220|674851
3221|680129
3221|680129
This doesn't feel very well...
Has anyone experienced similar problems?
Our config looks like this:
Postgres 6.5.2 (version() = PostgreSQL 6.5.2 on i386-unknown-freebsd3.3,
compiled by cc)
FreeBSD 3.3-RELEASE
2xPII SMP
vinum configured scsi (mirroring)
/Patrik Kudo
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Duplicate tuples with unique index
@ 2000-01-21 16:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Patrik Kudo <kudo@partitur.se>
0 siblings, 2 replies; 13+ messages in thread
From: Tom Lane @ 2000-01-21 16:23 UTC (permalink / raw)
To: Patrik Kudo <kudo@partitur.se>; +Cc: pgsql-sql; girgen@partitur.se
Patrik Kudo <kudo@partitur.se> writes:
> db=> vacuum ;
> NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0.
> NOTICE: Index html_text_idx: NUMBER OF INDEX' TUPLES (1193) IS NOT THE
> SAME AS HEAP' (1258)
> ERROR: Cannot insert a duplicate key into a unique index
Given the reference to pg_statistic, I wonder if you aren't getting
burnt by the recently-discovered bug wherein VACUUM ANALYZE tries to
create pg_statistic tuples that are too long. I recommend (a) patch
vacuum.c (see Dirk Lutzebaeck's nearby message); then (b) drop and
recreate html_text_idx; finally (c) DELETE FROM pg_statistic and
then VACUUM ANALYZE to rebuild it. Good luck!
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Duplicate tuples with unique index
@ 2000-01-24 10:00 Patrik Kudo <kudo@partitur.se>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Patrik Kudo @ 2000-01-24 10:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; girgen@partitur.se
Thanks!
We'll try it out.
Regards,
Patrik Kudo
Tom Lane wrote:
> Given the reference to pg_statistic, I wonder if you aren't getting
> burnt by the recently-discovered bug wherein VACUUM ANALYZE tries to
> create pg_statistic tuples that are too long. I recommend (a) patch
> vacuum.c (see Dirk Lutzebaeck's nearby message); then (b) drop and
> recreate html_text_idx; finally (c) DELETE FROM pg_statistic and
> then VACUUM ANALYZE to rebuild it. Good luck!
>
> regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Duplicate tuples with unique index
@ 2000-01-25 16:03 Palle Girgensohn <girgen@partitur.se>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 13+ messages in thread
From: Palle Girgensohn @ 2000-01-25 16:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Patrik Kudo <kudo@partitur.se>; pgsql-sql
Tom Lane wrote:
>
> Patrik Kudo <kudo@partitur.se> writes:
> > db=> vacuum ;
> > NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0.
> > NOTICE: Index html_text_idx: NUMBER OF INDEX' TUPLES (1193) IS NOT THE
> > SAME AS HEAP' (1258)
> > ERROR: Cannot insert a duplicate key into a unique index
>
> Given the reference to pg_statistic, I wonder if you aren't getting
> burnt by the recently-discovered bug wherein VACUUM ANALYZE tries to
> create pg_statistic tuples that are too long. I recommend (a) patch
> vacuum.c (see Dirk Lutzebaeck's nearby message); then (b) drop and
> recreate html_text_idx; finally (c) DELETE FROM pg_statistic and
> then VACUUM ANALYZE to rebuild it. Good luck!
>
> regards, tom lane
>
> ************
Unfotunately, it doesn't quite work:
pp=> vacuum analyze;
NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0.
VACUUM
pp=> delete from pg_statistic;
ERROR: heap_delete: (am)invalid tid
pp=>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Duplicate tuples with unique index
@ 2000-01-25 16:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Palle Girgensohn <girgen@partitur.se>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2000-01-25 16:26 UTC (permalink / raw)
To: Palle Girgensohn <girgen@partitur.se>; +Cc: Patrik Kudo <kudo@partitur.se>; pgsql-sql
Palle Girgensohn <girgen@partitur.se> writes:
> Unfotunately, it doesn't quite work:
> pp=> vacuum analyze;
> NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0.
> VACUUM
> pp=> delete from pg_statistic;
> ERROR: heap_delete: (am)invalid tid
Ugh. Yup, your pg_statistic table is corrupted all right.
What you may have to do is pg_dump that database, destroydb/createdb,
and reload.
I have a possible shortcut for you, but I *strongly* suggest that you
pg_dump first in case it doesn't work! After making your backup dump,
stop the postmaster and truncate the broken database's pg_statistic
file to zero length:
cp /dev/null .../data/base/yourdb/pg_statistic
Then restart the postmaster and see if you can vacuum analyze the
broken database. If so, you are good to go; if not, it's time to
reload that database.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Duplicate tuples with unique index
@ 2000-01-25 20:08 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Bruce Momjian @ 2000-01-25 20:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Palle Girgensohn <girgen@partitur.se>; Patrik Kudo <kudo@partitur.se>; pgsql-sql
> Palle Girgensohn <girgen@partitur.se> writes:
> > Unfotunately, it doesn't quite work:
>
> > pp=> vacuum analyze;
> > NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0.
> > VACUUM
> > pp=> delete from pg_statistic;
> > ERROR: heap_delete: (am)invalid tid
>
> Ugh. Yup, your pg_statistic table is corrupted all right.
>
> What you may have to do is pg_dump that database, destroydb/createdb,
> and reload.
>
> I have a possible shortcut for you, but I *strongly* suggest that you
> pg_dump first in case it doesn't work! After making your backup dump,
> stop the postmaster and truncate the broken database's pg_statistic
> file to zero length:
> cp /dev/null .../data/base/yourdb/pg_statistic
> Then restart the postmaster and see if you can vacuum analyze the
> broken database. If so, you are good to go; if not, it's time to
> reload that database.
>
Running pg_upgrade is another option.
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Duplicate tuples with unique index
@ 2000-01-26 00:31 Palle Girgensohn <girgen@partitur.se>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Palle Girgensohn @ 2000-01-26 00:31 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Patrik Kudo <kudo@partitur.se>; pgsql-sql
Bruce Momjian wrote:
>
> > Palle Girgensohn <girgen@partitur.se> writes:
> > > Unfotunately, it doesn't quite work:
> >
> > > pp=> vacuum analyze;
> > > NOTICE: Rel pg_statistic: TID 1/93: OID IS INVALID. TUPGONE 0.
> > > VACUUM
> > > pp=> delete from pg_statistic;
> > > ERROR: heap_delete: (am)invalid tid
> >
> > Ugh. Yup, your pg_statistic table is corrupted all right.
> >
> > What you may have to do is pg_dump that database, destroydb/createdb,
> > and reload.
> >
> > I have a possible shortcut for you, but I *strongly* suggest that you
> > pg_dump first in case it doesn't work! After making your backup dump,
> > stop the postmaster and truncate the broken database's pg_statistic
> > file to zero length:
> > cp /dev/null .../data/base/yourdb/pg_statistic
> > Then restart the postmaster and see if you can vacuum analyze the
> > broken database. If so, you are good to go; if not, it's time to
> > reload that database.
> >
>
> Running pg_upgrade is another option.
Nope. pg_upgrade was "disabled in this release because the internal blahblahblah disk layout changed from previous versions". cat /dev/null > pg_statistic didn't help, and the file was already zero
bytes long.
Applied the patches to vacuum.c and hio.c, and pg_dumpall && initdb && psql < dump.sql fixed it.
Thanks for your support!
/Palle
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Duplicate tuples with unique index
@ 2000-01-26 00:33 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Palle Girgensohn <girgen@partitur.se>
0 siblings, 1 reply; 13+ messages in thread
From: Bruce Momjian @ 2000-01-26 00:33 UTC (permalink / raw)
To: Palle Girgensohn <girgen@partitur.se>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Patrik Kudo <kudo@partitur.se>; pgsql-sql
> > > I have a possible shortcut for you, but I *strongly* suggest
> that you > > pg_dump first in case it doesn't work! After making
> your backup dump, > > stop the postmaster and truncate the broken
> database's pg_statistic > > file to zero length: > > cp
> /dev/null .../data/base/yourdb/pg_statistic > > Then restart
> the postmaster and see if you can vacuum analyze the > > broken
> database. If so, you are good to go; if not, it's time to > >
> reload that database. > > > > Running pg_upgrade is another
> option.
>
> Nope. pg_upgrade was "disabled in this release because the
> internal blahblahblah disk layout changed from previous versions".
> cat /dev/null > pg_statistic didn't help, and the file was
> already zero bytes long.
Sorry, you have to edit the script to reenable it.
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Duplicate tuples with unique index
@ 2000-01-26 00:37 Palle Girgensohn <girgen@partitur.se>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Palle Girgensohn @ 2000-01-26 00:37 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Patrik Kudo <kudo@partitur.se>; pgsql-sql
Bruce Momjian wrote:
>
> > Nope. pg_upgrade was "disabled in this release because the
> > internal blahblahblah disk layout changed from previous versions".
>
> Sorry, you have to edit the script to reenable it.
That's OK. I didn't bother to check. the dump/restore sequence was pretty quick.
The layout hasn't really changed since 6.5, right?
Cheers
/Palle
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Duplicate tuples with unique index
@ 2000-01-26 05:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Palle Girgensohn <girgen@partitur.se>
0 siblings, 2 replies; 13+ messages in thread
From: Tom Lane @ 2000-01-26 05:38 UTC (permalink / raw)
To: Palle Girgensohn <girgen@partitur.se>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Patrik Kudo <kudo@partitur.se>; pgsql-sql
Palle Girgensohn <girgen@partitur.se> writes:
>>>> Nope. pg_upgrade was "disabled in this release because the
>>>> internal blahblahblah disk layout changed from previous versions".
>>
>> Sorry, you have to edit the script to reenable it.
> That's OK. I didn't bother to check. the dump/restore sequence was pretty quick.
> The layout hasn't really changed since 6.5, right?
No; the disk layout is the same. There is room for trouble nonetheless.
There is some doubt about whether pg_upgrade will work right under MVCC
semantics: in MVCC, whether a tuple stored in a table is considered
valid or not will depend on whether its creator transaction is marked
committed in pg_log (and, if it is marked with a deletor transaction,
whether the deletor is NOT committed).
pg_upgrade tries to deal with this by copying the old database
installation's pg_log into the new. OK, that ensures that all the
user-table tuples preserve their commit state; but what about tuples
in the system tables? The idea behind pg_upgrade is to paste together
a set of user tables with a set of system tables --- but unless the
transaction number history of the user tables is exactly the same as
the transaction number history of the system tables, there is clearly
a risk that committed tuples will suddenly be considered not-committed
or vice versa. And ordinarily those histories will *not* be identical.
The only reason pg_upgrade has any chance at all of working is that
for efficiency reasons we don't want to go back and consult pg_log
for every single tuple we read. So, there are really six states of
a tuple on disk, which may be described as:
1. I was created by transaction N, but I dunno if it committed
2. I was created by a transaction that definitely committed,
so I'm good; no need to look at pg_log
3. I was created by a transaction that definitely aborted,
so I'm dead; no need to look at pg_log
4. I was deleted by transaction N, but I dunno if it committed
5. I was deleted by a transaction that definitely committed,
so I'm dead; no need to look at pg_log
6. I was deleted by a transaction that definitely aborted,
so I'm still good; no need to look at pg_log
(I'm fuzzing over some fine points that arise when multiple transactions
try to delete the same tuple, but this level of detail will do for now.)
Whenever any backend examines a tuple in state 1 or 4, it will consult
pg_log to discover the state of the source transaction. If the source
transaction has now committed or aborted, the tuple will be rewritten
with the new state (2,3,5,6 as appropriate) so that future readers of
the tuple don't have to look at pg_log again.
Now states 2,3,5,6 do not depend on what pg_log says. Therefore,
pg_upgrade's copy of an old pg_log file into a new database will
work *if and only if* all the tuples in the system tables are in
one of these states, and none of them are in states 1 or 4. We
can survive with user-table tuples that are in the uncertain states,
because the associated pg_log info will be copied over with them.
We cannot afford to have any uncertainly-committed system-table
tuples, because we will overwrite the pg_log data about their status.
Bruce thinks that the pg_upgrade script will ensure that the system-
table tuples are all in frozen states (by VACUUMing them). I don't
trust it worth a dime, myself. Maybe it will work, but it hasn't been
proven in the field. So, if you'd like to try it, by all means do so
--- but make a pg_dump backup first! And let us know whether you have
problems or not!
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Duplicate tuples with unique index
@ 2000-01-26 05:43 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 2000-01-26 05:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Palle Girgensohn <girgen@partitur.se>; Patrik Kudo <kudo@partitur.se>; pgsql-sql
> Bruce thinks that the pg_upgrade script will ensure that the system-
> table tuples are all in frozen states (by VACUUMing them). I don't
> trust it worth a dime, myself. Maybe it will work, but it hasn't been
> proven in the field. So, if you'd like to try it, by all means do so
> --- but make a pg_dump backup first! And let us know whether you have
> problems or not!
I see what you are saying. If a table created as part of the new
pg_dump schema create matches a transaction that aborted in the old
database, the table would be invalid. However, as you mentioned, once
the tuple is marked as committed/rolled back, it doesn't consult.
Basically, I was surprised pg_upgrade worked at all in any of the
releases. It seems too good to be true. However, I have received very
few problem reports about its use, and it does get used.
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [SQL] Duplicate tuples with unique index
@ 2000-01-26 15:07 Palle Girgensohn <girgen@partitur.se>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Palle Girgensohn @ 2000-01-26 15:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Patrik Kudo <kudo@partitur.se>; pgsql-sql
Tom Lane wrote:
>
Thanks for the info. Very interesting reading!
> Bruce thinks that the pg_upgrade script will ensure that the system-
> table tuples are all in frozen states (by VACUUMing them). I don't
> trust it worth a dime, myself. Maybe it will work, but it hasn't been
> proven in the field. So, if you'd like to try it, by all means do so
> --- but make a pg_dump backup first! And let us know whether you have
> problems or not!
I never tried it; I did an old fashion dump+initdb+restore. So, we will not know whether pg_upgrade is OK or not, at least for now. :(
Regards,
Palle
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: [SQL] Duplicate tuples with unique index
@ 2000-01-26 23:03 Iain.Mott@CMIS.CSIRO.AU
0 siblings, 0 replies; 13+ messages in thread
From: Iain.Mott@CMIS.CSIRO.AU @ 2000-01-26 23:03 UTC (permalink / raw)
To: pgsql-sql
Dear List
I'm new to SQL and databases in general. I'm writing an interface to
postgresql in C for a music application.
Presently, my interface returns instances of each query with an index. This
is done in my coding rather than a result of the SQL query itself.
Is there a simple way to ask for an index to each instance of a query in
SQL? (positioned in the first field of the results).
Cheers,
Iain Mott
<http://soundart.tripod.com;
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2000-01-26 23:03 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-21 15:09 Duplicate tuples with unique index Patrik Kudo <kudo@partitur.se>
2000-01-21 16:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-24 10:00 ` Patrik Kudo <kudo@partitur.se>
2000-01-25 16:03 ` Palle Girgensohn <girgen@partitur.se>
2000-01-25 16:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-25 20:08 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-26 00:31 ` Palle Girgensohn <girgen@partitur.se>
2000-01-26 00:33 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-26 00:37 ` Palle Girgensohn <girgen@partitur.se>
2000-01-26 05:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-26 05:43 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-01-26 15:07 ` Palle Girgensohn <girgen@partitur.se>
2000-01-26 23:03 RE: [SQL] Duplicate tuples with unique index Iain.Mott@CMIS.CSIRO.AU
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