Re: Error on deleting

On Tuesday 30 March 2004 18:20, Indibil wrote:
> Hi:

I'm not sure if you are still interested, but you probably need to subscribe
to the address above (pgsql-general(at)postgresql(dot)org) - the address you were
using looked very strange indeed. You can see the mailing lists at
http://www.postgresql.org/lists.html

> I have a PostgreSQL 7.2.2 database since 2 years working right until now.
> When I try to delete a row from pgAdmin II , I get the message " ....cannot
> uniquely identify, 2 rows match....." If I go on deleting the row, the 2
> ones are deleted. Of Course the 2 rows don't match. They have different
> primary key, OIDs, and so on.

Firstly, you should upgrade to 7.2.4/5 - whatever the last of the 7.2 series
is. Then, check the release notes to see if any bug-fixes to do with your
problem were added in 7.2.3 etc.

> This has happened several times in the last days. If I issue the SQL
> command "Delete ......" no errors occurs.

So what precisely is happening? Have you turned statment logging on in PG to
see what statement fails? If it works as SQL but not from PgAdmin then that
suggests PgAdmin is accessing a row differently to the way you are via SQL.

> Please I need help, because the database is into production, and I am
> receiving callings from angry users which can not delete rows from their
> Visual Basic program ( they access the PostgreSQL database via this
> program, and get the message " the record does not exist" when they are
> trying to delete).

If they're using a VB program, that implies ODBC shows the problem too.

It could be a corrupted index, or even corrupted data if you've had a hardware
fault you've not mentioned. What might be worth doing is:

1. Take a backup (just in case)
2. VACUUM FULL VERBOSE (and see how much gets vacuumed)
3. REINDEX TABLE foo (or whatever)
4. See if the problem still exists

Of course, if you can't identify a pattern to the problem step 4 might be
difficult.

Oh - you'll probably want to ANALYSE too after all this. Full details of all
commands in the reference manuals.

--
Richard Huxton
Archonet Ltd