Обсуждение: Missing row after update
Hi there. I am new to Postgresql but very familiar with RDBMS. We are running 8.4 on windows serer 2008 r2. I was updating a record through pgAdmin and was being an a$$ and pressed the run button multiple times and then the record just disappeared. Update query: update jaas_login set password=md5(login_name || ':' || realm_name || ':Password123'), has_logged_in = false, modified=now(), failed_attempts=0 where login_name like 'CompanyAdmin@%'; I cannot find the record at all so tried to re-insert it and the query just runs and runs and runs. We thought we had backups of this DB but all we have are dump files and I really really don't want to have to restore if I can avoid it. I find it hard to believe that a db as advanced as postgresql will just "lose" data and there has to be some explaination for where the record is. Any help would really save my butt. Thanks Dinsdale
On 06/07/2012 06:57 AM, Dinsdale wrote: > Hi there. I am new to Postgresql but very familiar with RDBMS. We are > running 8.4 on windows serer 2008 r2. I was updating a record through > pgAdmin and was being an a$$ and pressed the run button multiple times > and then the record just disappeared. Update query: > > update jaas_login set password=md5(login_name || ':' || realm_name || > ':Password123'), has_logged_in = false, modified=now(), > failed_attempts=0 where login_name like 'CompanyAdmin@%'; So just to be clear the above was what was run multiple times? > > I cannot find the record at all so tried to re-insert it and the query > just runs and runs and runs. Select * from jaas_login where login_name like CompanyAdmin@%'; shows nothing? What is your INSERT query? > > We thought we had backups of this DB but all we have are dump files > and I really really don't want to have to restore if I can avoid it. I > find it hard to believe that a db as advanced as postgresql will just > "lose" data and there has to be some explaination for where the record > is. I tend to doubt it just disappeared. > > Any help would really save my butt. Thanks > > Dinsdale > -- Adrian Klaver adrian.klaver@gmail.com
On 06/07/2012 06:57 AM, Dinsdale wrote: > Hi there. I am new to Postgresql but very familiar with RDBMS. We are > running 8.4 on windows serer 2008 r2. I was updating a record through > pgAdmin and was being an a$$ and pressed the run button multiple times > and then the record just disappeared. Update query: > > update jaas_login set password=md5(login_name || ':' || realm_name || > ':Password123'), has_logged_in = false, modified=now(), > failed_attempts=0 where login_name like 'CompanyAdmin@%'; > > I cannot find the record at all so tried to re-insert it and the query > just runs and runs and runs. > 8.4.what? Perhaps you encountered this: http://www.databasesoup.com/2012/03/postgres-update-release-data-loss-and.html Does reindexing that table help? Cheers, Steve
On Jun 7, 12:44 pm, adrian.kla...@gmail.com (Adrian Klaver) wrote: > On 06/07/2012 06:57 AM, Dinsdale wrote: > > Hi there. I am new to Postgresql but very familiar with RDBMS. We are > > running 8.4 on windows serer 2008 r2. I was updating a record through > > pgAdmin and was being an a$$ and pressed the run button multiple times > > and then the record just disappeared. Update query: > > > > update jaas_login set password=md5(login_name || ':' || realm_name || > > ':Password123'), has_logged_in = false, modified=now(), > > failed_attempts=0 where login_name like 'CompanyAdmin@%'; > > So just to be clear the above was what was run multiple times? > > > > > I cannot find the record at all so tried to re-insert it and the query > > just runs and runs and runs. > > Select * from jaas_login where login_name like CompanyAdmin@%'; shows > nothing? > > What is your INSERT query? > > > > We thought we had backups of this DB but all we have are dump files > > and I really really don't want to have to restore if I can avoid it. I > > find it hard to believe that a db as advanced as postgresql will just > > "lose" data and there has to be some explaination for where the record > > is. > > I tend to doubt it just disappeared. > > > > Any help would really save my butt. Thanks > > > > Dinsdale > > > > -- > Adrian Klaver > adrian.kla...@gmail.com > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general I tried every select query I could think of including with no where clause and simply searching manually for the record but it was just not there. I had someone else verify this as well. I didn't think it could just disappear either. We finally decided to use the dump file and re-create the database but just before that I tried a backup and got an error about a missing attribute on relid xxxx. Someone with more Postgres experience said that the table was corrupted and i should just rebuild, so we did. Please note that I CANNOT reproduce this in our QA! I think maybe the issue noted by steve below may be the problem.
On 06/07/2012 11:23 AM, Dinsdale wrote: > On Jun 7, 12:44 pm, adrian.kla...@gmail.com (Adrian Klaver) wrote: >> On 06/07/2012 06:57 AM, Dinsdale wrote: >> > Hi there. I am new to Postgresql but very familiar with RDBMS. We are >> > running 8.4 on windows serer 2008 r2. I was updating a record through >> > pgAdmin and was being an a$$ and pressed the run button multiple times >> > and then the record just disappeared. Update query: >> > >> > update jaas_login set password=md5(login_name || ':' || realm_name || >> > ':Password123'), has_logged_in = false, modified=now(), >> > failed_attempts=0 where login_name like 'CompanyAdmin@%'; >> >> So just to be clear the above was what was run multiple times? >> >> > >> > I cannot find the record at all so tried to re-insert it and the query >> > just runs and runs and runs. >> >> Select * from jaas_login where login_name like CompanyAdmin@%'; shows >> nothing? >> >> What is your INSERT query? >> > >> > We thought we had backups of this DB but all we have are dump files >> > and I really really don't want to have to restore if I can avoid it. I >> > find it hard to believe that a db as advanced as postgresql will just >> > "lose" data and there has to be some explaination for where the record >> > is. >> >> I tend to doubt it just disappeared. >> > >> > Any help would really save my butt. Thanks >> > >> > Dinsdale >> > >> >> -- >> Adrian Klaver >> adrian.kla...@gmail.com >> >> -- >> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) >> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general > > I tried every select query I could think of including with no where > clause and simply searching manually for the record but it was just > not there. I had someone else verify this as well. I didn't think it > could just disappear either. We finally decided to use the dump file > and re-create the database but just before that I tried a backup and > got an error about a missing attribute on relid xxxx. Someone with > more Postgres experience said that the table was corrupted and i > should just rebuild, so we did. So what is the difference between a backup and restoring from a dump file? When you rebuilt the database did you use the latest 8.4.x version? > > Please note that I CANNOT reproduce this in our QA! I think maybe the > issue noted by steve below may be the problem. > -- Adrian Klaver adrian.klaver@gmail.com
On Jun 7, 2:48 pm, adrian.kla...@gmail.com (Adrian Klaver) wrote: > On 06/07/2012 11:23 AM, Dinsdale wrote: > > > > > > > On Jun 7, 12:44 pm, adrian.kla...@gmail.com (Adrian Klaver) wrote: > >> On 06/07/2012 06:57 AM, Dinsdale wrote: > >> > Hi there. I am new to Postgresql but very familiar with RDBMS. We are > >> > running 8.4 on windows serer 2008 r2. I was updating a record through > >> > pgAdmin and was being an a$$ and pressed the run button multiple times > >> > and then the record just disappeared. Update query: > > >> > update jaas_login set password=md5(login_name || ':' || realm_name || > >> > ':Password123'), has_logged_in = false, modified=now(), > >> > failed_attempts=0 where login_name like 'CompanyAdmin@%'; > > >> So just to be clear the above was what was run multiple times? > > >> > I cannot find the record at all so tried to re-insert it and the query > >> > just runs and runs and runs. > > >> Select * from jaas_login where login_name like CompanyAdmin@%'; shows > >> nothing? > > >> What is your INSERT query? > > >> > We thought we had backups of this DB but all we have are dump files > >> > and I really really don't want to have to restore if I can avoid it. I > >> > find it hard to believe that a db as advanced as postgresql will just > >> > "lose" data and there has to be some explaination for where the record > >> > is. > > >> I tend to doubt it just disappeared. > > >> > Any help would really save my butt. Thanks > > >> > Dinsdale > > >> -- > >> Adrian Klaver > >> adrian.kla...@gmail.com > > >> -- > >> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > >> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general > > > I tried every select query I could think of including with no where > > clause and simply searching manually for the record but it was just > > not there. I had someone else verify this as well. I didn't think it > > could just disappear either. We finally decided to use the dump file > > and re-create the database but just before that I tried a backup and > > got an error about a missing attribute on relid xxxx. Someone with > > more Postgres experience said that the table was corrupted and i > > should just rebuild, so we did. > > So what is the difference between a backup and restoring from a dump file? > > When you rebuilt the database did you use the latest 8.4.x version? > > > > > Please note that I CANNOT reproduce this in our QA! I think maybe the > > issue noted by steve below may be the problem. > > -- > Adrian Klaver > adrian.kla...@gmail.com > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general I didn't change the version of the server, I just restored the data from a pg_dump file (*.db). Comparing a backup file (*.backup) to a dump file, they seem to be two different methods of "backing up" data. The dump file is a sql script file to re-create the database that also includes copy commands to insert data afterwards. Anyway, we are back to where we were before the issue, that's all I care about. This isn't even my software I'm supporting! :-) Thanks for the responses, Dinsdale