Thread: Error on deleting
Hi: 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. This has happened several times in the last days. If I issue the SQL command "Delete ......" no errors occurs. 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). Thanks in advance, and sorry for my poor english. Indibil
It's rumoured that Indibil once said: > Hi: > > 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. Hmm, can you re-create this with logging set to errors + sql please? Then send the logfile... Regards, Dave
Hi David: I have another server with PostgreSQL 7.3 so, I dumped the PostgreSQL 7.2 database and restored it in 7.3. With pgAdmin_II I get the same error when I try to delete a record from the table "escritos". This table has a primary key with two fields: "cod_ficha", "cod_estado". If, for example, I select the records: cod_ficha cod_estado ------------ --------------- 2003000001 0001 2003000001 0023 When I try to delete only one of them, I get the message: "The selected record could not be uniquely identified, 2 records match, and will all be deleted if you proceed. Do you wish to continue?". The last lines in the pg_adminII.log are: SELECT count(*) AS count FROM public.escritos WHERE cod_ficha=2003000001 DELETE public.escritos WHERE cod_ficha=2003000001 I think the problem is in the DELETE statement because the WHERE condition must be: ....... WHERE cod_ficha=2003000001 AND cod_estado='0023' Whith pgAdmin III there is no problem on deleting because it issues the following deleting statement: DELETE public.escritos WHERE oid=169858256 Thanks a lot for your interest David, Indibil Dave Page wrote: > It's rumoured that Indibil once said: >> Hi: >> >> 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. > > Hmm, can you re-create this with logging set to errors + sql please? Then > send the logfile... > Regards, Dave > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend
Hi Indibil Yes, that is what I suspected was the problem. pgAdmin II cannot properly recognise multi-column primary keys. I would normally suggest upgrading to pgAdmin III, but as you said your app was running on PostgreSQL 7.2 I did not bother. Unfortunately the only ways around this are going to be: 1) Grab the pgAdmin II source code and fix it (not an easy task which is why I never did it). 2) Upgrade your server to 7.4.2 and using pgAdmin III. 3) Manually delete rows using an explicit SQL command. Regards, Dave. > -----Original Message----- > From: Indibil [mailto:indibil@webmail.co.za] > Sent: 31 March 2004 17:08 > To: pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] Error on deleting > > Hi David: > > I have another server with PostgreSQL 7.3 so, I dumped the > PostgreSQL 7.2 database and restored it in 7.3. With > pgAdmin_II I get the same error when I try to delete a record > from the table "escritos". This table has a primary key with > two fields: "cod_ficha", "cod_estado". If, for example, I > select the records: > > cod_ficha cod_estado > ------------ --------------- > 2003000001 0001 > 2003000001 0023 > > When I try to delete only one of them, I get the message: > "The selected record could not be uniquely identified, 2 > records match, and will all be deleted if you proceed. Do you > wish to continue?". > > The last lines in the pg_adminII.log are: > SELECT count(*) AS count FROM public.escritos WHERE > cod_ficha=2003000001 DELETE public.escritos WHERE cod_ficha=2003000001 > > I think the problem is in the DELETE statement because the > WHERE condition must be: ....... WHERE cod_ficha=2003000001 > AND cod_estado='0023' > > Whith pgAdmin III there is no problem on deleting because it > issues the following deleting statement: > DELETE public.escritos WHERE oid=169858256 > > > Thanks a lot for your interest David, > > Indibil > > > Dave Page wrote: > > > It's rumoured that Indibil once said: > >> Hi: > >> > >> 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. > > > > Hmm, can you re-create this with logging set to errors + > sql please? > > Then send the logfile... > > Regards, Dave > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > > ---------------------------(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 >