Обсуждение: 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/

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    | john.a.hoff@boeing.com |         | Hoff, John A |
A      | /bin/bash |         | N           |        0 | shell1   |
1114441842 |              |                0 |       0 |
|           |                  0 |               | GMT      |        1 |
1 | uscompany                         | 949762 |                |
| IDS          | 0                |                    0 |
    1828 | 949762    | john.a.hoff@boeing.com |         | 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:jd@commandprompt.com]
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/

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    | john.a.hoff@boeing.com |         | 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

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:dev@archonet.com]
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    | john.a.hoff@boeing.com |         | 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

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

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:dev@archonet.com]
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

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