Re: SELECT returning too many rows (?) [7.4.2]

To summarise:

I believe there to be one row stored, which is the reason why the
constraints on the primary key (id) were met, and that for reasons as
yet unknown a select returns three rows.

An addendum:

To attempt to discover the database wide implications of this, I have
done a pg_dump into a pgsql 7.4.6 database on my FC3 based workstation.
I was expecting a successful import, for it to insert duplicate rows
and for it to error on creating unique indexes (which it does after the
inserts).

On my workstation:

$ pg_dump -Uusername -h databasehost -O processing | psql processing

Thankfully this incident appears localised to this one row. I don't know
why, I don't know what's so special about this row and subsequent
transactions have been successfully made since.

As expected, the pg_dump did contain three rows, which were inserted
into my local database. The unique index did fail because there were
duplicated values. What was weird, and prompted me to reply to myself
was this :

I did a pg_dump which didn't preserve oids. Strangely the new oids for
the 'problem' row appear to change by a factor of 10. Now that's either
some bizarre coincidence or some kind of omen....

processing=# select oid, id from q_certs where id = '224';
oid | id
---------+-----
1246343 | 224
1246353 | 224
1246363 | 224
(3 rows)

Directly previous rows appear to follow sequentially. This becomes more
scattered as I decrement id.

processing=# select oid, id from q_certs where id = '223';
oid | id
---------+-----
1246547 | 223

processing=# select oid, id from q_certs where id = '222';
oid | id
---------+-----
1246546 | 222
processing=# select oid, id from q_certs where id = '221';
oid | id
---------+-----
1246545 | 221

Additional information supplied on request.

I think what I'm going to have to do, save todays back up being
corrupted with this superfluous row and so that this guys order can be
processed is to delete the (all three) entries and add a new row by
hand. This will effectively cut off any attempt to get to the bottom of
this. I'll hang fire for a few hours on this incase any of the pg devel
team happen upon this message.

Cheers,

--
Rob Fielding
rob(at)dsvr(dot)net

www.dsvr.co.uk Development
Designer Servers Business Serve Plc

"I don't pretend to understand Brannigans Law. I merely enforce it"
- Zapp Brannigan