Re: [GENERAL] Unable to Update a Record
Wang, Mary Y wrote: Richard, Thank you so MUCH. I was able to delete the record by using the OID method that you mentioned in (2). Well, if the column I mentioned in (1) is integer/bigint, then you'll want to do (3) as well and reindex. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Unable to Update a Record
I tried to do (3) as well for reindex. But I got this error: reindex table users; ERROR: Cannot create unique index. Table contains non-unique values. Do you know what does this mean? Thanks in advance. Mary Wang -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Monday, August 01, 2005 11:08 PM To: Wang, Mary Y Cc: Joshua D. Drake; pgsql-general@postgresql.org Subject: Re: [GENERAL] Unable to Update a Record Wang, Mary Y wrote: Richard, Thank you so MUCH. I was able to delete the record by using the OID method that you mentioned in (2). Well, if the column I mentioned in (1) is integer/bigint, then you'll want to do (3) as well and reindex. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Unable to Update a Record
Wang, Mary Y wrote: I tried to do (3) as well for reindex. But I got this error: reindex table users; ERROR: Cannot create unique index. Table contains non-unique values. Do you know what does this mean? Just what it says. Somehow your table has got corrupted, possibly with an old and a new version of the same row available. Take a pg_dump of the entire database (for backup), and then you'll want to search for the duplicates. Something like: SELECT user_id,count(*) FROM users GROUP BY user_id HAVING count(*) 1; Or, to see actual rows: SELECT oid,* FORM users WHERE user_id IN ( SELECT user_id FROM users GROUP BY user_id HAVING count(*) 1 ); Then, you can delete them via their OID. The question is - how did your table get this problem. Check the release-notes for versions more recent than yours and see if anything looks relevant: http://www.postgresql.org/docs/8.0/static/release.html Have you had any crashes? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Unable to Update a Record
Sorry, that I didn't explain my problem very clearly. Anyway, here is the deal: I'm the admin for the database, so, I've all the privileges of updating, deletion, and reviewing and et. When I tried to select based on the bemsid condition, TWO ROWS returned: select * from users where bemsid=949762; user_id | user_name | email | user_pw |realname | status | shell | unix_pw | unix_status | unix_uid | unix_box | add_date | confirm_hash | mail_siteupdates | mail_va | authorized_keys | email_new | people_view_skills | people_resume | timezone | language | third_party | personal_status | bemsid | sensitive_info | reason_access | organization | brass_first_time | mail_sitenews_update | doclinks_sort_order -+---+---+-+ -++---+-+-+--+-- ++--+--+-+-- ---+---++---+--+ --+-+--++--- -+---+--+--+ --+- 4215 | 949762| [EMAIL PROTECTED] | | Hoff, John A | A | /bin/bash | | N |0 | shell1 | 1114441842 | |0 | 0 | | | 0 | | GMT |1 | 1 | uscompany | 949762 || | IDS | 0|0 | 1828 | 949762| [EMAIL PROTECTED] | | Hoff, John A | A | /bin/bash | | A | 436 | shell1 | 1076368047 | |0 | 0 | | | 0 | | GMT |1 | 1 | uscompany | 949762 || | IDS | 0| | D (2 rows) But when I tried select user_id=4215, the result return 0 rows: select * from users where user_id=4215; user_id | user_name | email | user_pw | realname | status | shell | unix_pw | unix_status | unix_uid | unix_box | add_date | confirm_hash | mail_siteupdates | mail_va | authorized_keys | email_new | people_view_skills | people_resume | timezone | language | third_party | personal_status | bemsid | sensitive_info | reason_access | organization | brass_first_time | mail_sitenews_update | doclinks_sort_order -+---+---+-+--++---+ -+-+--+--+--+--+ --+-+-+---+- ---+---+--+--+-+ -+++---+--+- -+--+- (0 rows) I'm really confused. I want to delete user_id=4215 because it is causing me login errors. But I can't select, update, delete that record. I'm not sure if that record really exist. I used the Vaccum, but it didn't help. Thanks in advance. Mary Wang -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Friday, July 29, 2005 5:18 PM To: Wang, Mary Y Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Unable to Update a Record Wang, Mary Y wrote: Hi, I'm running postgressql 7.1.3-2. I've a When I did a select on the table, I was able to see that row. However, when I tried to update that row, I got 'Update 0', I even tried to delete that row, I couldn't. It seems like the database is confused. I did the Vacuum, but still didn't help. It would probably be helpful to see what query you are running, the table information... things like that. Any suggestions? Thanks Mary Wang -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Unable to Update a Record
Wang, Mary Y wrote: Sorry, that I didn't explain my problem very clearly. Anyway, here is the deal: I'm the admin for the database, so, I've all the privileges of updating, deletion, and reviewing and et. When I tried to select based on the bemsid condition, TWO ROWS returned: select * from users where bemsid=949762; user_id | user_name | email | user_pw |realname 4215 | 949762| [EMAIL PROTECTED] | | Hoff, John A | But when I tried select user_id=4215, the result return 0 rows: select * from users where user_id=4215; user_id | user_name | email | user_pw | realname | status | shell | -+--+- (0 rows) I'm really confused. I want to delete user_id=4215 because it is causing me login errors. But I can't select, update, delete that record. I'm not sure if that record really exist. 1. What type is user_id? If it's a text-type, there could be unseen spaces interfering. 2. Try selecting the OID too (SELECT oid,* FROM ...) with your first query, then use that oid in your where clause. Can you see it now? Of course, this assumes you have oids defined for this table. 3. Have you tried re-indexing the table (REINDEX TABLE users) It's possible the index has become corrupted while the data is fine. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Unable to Update a Record
Richard, Thank you so MUCH. I was able to delete the record by using the OID method that you mentioned in (2). Thanks again. Mary Wang -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Monday, August 01, 2005 9:44 AM To: Wang, Mary Y Cc: Joshua D. Drake; pgsql-general@postgresql.org Subject: Re: [GENERAL] Unable to Update a Record Wang, Mary Y wrote: Sorry, that I didn't explain my problem very clearly. Anyway, here is the deal: I'm the admin for the database, so, I've all the privileges of updating, deletion, and reviewing and et. When I tried to select based on the bemsid condition, TWO ROWS returned: select * from users where bemsid=949762; user_id | user_name | email | user_pw | realname 4215 | 949762| [EMAIL PROTECTED] | | Hoff, John A | But when I tried select user_id=4215, the result return 0 rows: select * from users where user_id=4215; user_id | user_name | email | user_pw | realname | status | shell | -+--+- (0 rows) I'm really confused. I want to delete user_id=4215 because it is causing me login errors. But I can't select, update, delete that record. I'm not sure if that record really exist. 1. What type is user_id? If it's a text-type, there could be unseen spaces interfering. 2. Try selecting the OID too (SELECT oid,* FROM ...) with your first query, then use that oid in your where clause. Can you see it now? Of course, this assumes you have oids defined for this table. 3. Have you tried re-indexing the table (REINDEX TABLE users) It's possible the index has become corrupted while the data is fine. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Unable to Update a Record
Title: Message Hi, I'm running postgressql 7.1.3-2. I've a When I did a select on the table, I was able to see that row. However, when I tried to update that row, I got 'Update 0', I even tried to delete that row, I couldn't. It seems like the database is confused. I did the Vacuum, but still didn't help. Any suggestions? ThanksMary Wang
Re: [GENERAL] Unable to Update a Record
Wang, Mary Y wrote: Hi, I'm running postgressql 7.1.3-2. I've a When I did a select on the table, I was able to see that row. However, when I tried to update that row, I got 'Update 0', I even tried to delete that row, I couldn't. It seems like the database is confused. I did the Vacuum, but still didn't help. It would probably be helpful to see what query you are running, the table information... things like that. Any suggestions? Thanks Mary Wang -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Unable to Update a Record
Title: Message Are you sure that your user account has been granted DELETE and UPDATE on that table? If you are sure that the account you were connected with has permissions, then: Give the exact command you did to perform the select. Give the exact result set you got back when you did the select. Give the exact command you did to perform the update. Give the exact command you did to perform the delete. Were there any sort of error messages? From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wang, Mary Y Sent: Friday, July 29, 2005 4:51 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Unable to Update a Record Hi, I'm running postgressql 7.1.3-2. I've a When I did a select on the table, I was able to see that row. However, when I tried to update that row, I got 'Update 0', I even tried to delete that row, I couldn't. It seems like the database is confused. I did the Vacuum, but still didn't help. Any suggestions? Thanks Mary Wang
