Thread: Strange behavior: row won't delete
HI. I made a small alteration to a table (added a column). Now when I do: vacuum analyze TABLENAME or delete from TABLENAME where id = 99 Nothing happens! The carriage return means the my shell cursor goes to the next line, but it just stays there. I thought something may be happening silently but it has been sitting this way since an hour. How can I debug this? Nothing in the logs at all.
Phoenix Kiula <phoenix.kiula@gmail.com> writes:
> Now when I do:
> vacuum analyze TABLENAME
> or
> delete from TABLENAME where id = 99
> Nothing happens! The carriage return means the my shell cursor goes to
> the next line, but it just stays there.
Did you forget the semicolon?
regards, tom lane
On Tuesday 03 March 2009, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: > HI. I made a small alteration to a table (added a column). > > Now when I do: > > vacuum analyze TABLENAME > > or > > delete from TABLENAME where id = 99 > > Nothing happens! The carriage return means the my shell cursor goes to > the next line, but it just stays there. I thought something may be > happening silently but it has been sitting this way since an hour. > > How can I debug this? Nothing in the logs at all. commit the transaction where you altered the table. It has an open lock on the table. -- Even a sixth-grader can figure out that you can’t borrow money to pay off your debt
On Wed, Mar 4, 2009 at 12:10 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Phoenix Kiula <phoenix.kiula@gmail.com> writes: >> Now when I do: >> vacuum analyze TABLENAME >> or >> delete from TABLENAME where id = 99 >> Nothing happens! The carriage return means the my shell cursor goes to >> the next line, but it just stays there. > > Did you forget the semicolon? Cute. But no :) myuser=# delete from visitcount where id = 99;
> commit the transaction where you altered the table. It has an open lock on > the table. =# commit; WARNING: there is no transaction in progress COMMIT Time: 0.282 ms So no, there's nothing pending.
Although when I try this: select pg_class.relname,pg_locks.* from pg_class,pg_locks where pg_class.relfilenode=pg_locks.relation; There are many rows! How can I get rid of these open locks?
----- "Phoenix Kiula" <phoenix.kiula@gmail.com> wrote: > > commit the transaction where you altered the table. It has an open > lock on > > the table. > > > > =# commit; > > WARNING: there is no transaction in progress > COMMIT > Time: 0.282 ms > > > So no, there's nothing pending. > > -- Are you connected to the right database?. I have been in that situation, looking at the log for db A and doing things indb B. Adrian Klaver aklaver@comcast.net
On Wed, Mar 4, 2009 at 12:36 AM, Adrian Klaver <aklaver@comcast.net> wrote: > > Are you connected to the right database?. I have been in that situation, looking at the log for db A and doing things indb B. Thanks. I only have one database, so yes I am connected to it. I have the lock file in /tmp: .s.PGSQL.5432.lock Should I delete this file?
----- "Phoenix Kiula" <phoenix.kiula@gmail.com> wrote: > On Wed, Mar 4, 2009 at 12:36 AM, Adrian Klaver <aklaver@comcast.net> > wrote: > > > > Are you connected to the right database?. I have been in that > situation, looking at the log for db A and doing things in db B. > > > Thanks. I only have one database, so yes I am connected to it. > > I have the lock file in /tmp: .s.PGSQL.5432.lock > > Should I delete this file? No, that is the lock file for the entire cluster. Adrian Klaver aklaver@comcast.net
I guess my question is, how should I remove all pending locks on a table so that I can get on with the rest of the stuff? I mean, even if I can now find an offending RULE on the table, I cannot replace or remove it. ' Thanks for any pointers!
Phoenix Kiula <phoenix.kiula@gmail.com> writes:
> How can I get rid of these open locks?
Close the transactions that are holding them. Look into
pg_stat_activity and pg_prepared_xacts.
regards, tom lane
On Wed, Mar 4, 2009 at 1:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Phoenix Kiula <phoenix.kiula@gmail.com> writes: >> How can I get rid of these open locks? > > Close the transactions that are holding them. Look into > pg_stat_activity and pg_prepared_xacts. Thanks for this. But can I simply delete all the pg_locks table? Or delete all rows in pg_stat_activity? In my case the _xacts table is empty. Ideally I don't want to lose pg_stat_activity. I just want to change a RULE on a table. Is there any place I can do that?
On Wed, Mar 4, 2009 at 1:23 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: > On Wed, Mar 4, 2009 at 1:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Phoenix Kiula <phoenix.kiula@gmail.com> writes: >>> How can I get rid of these open locks? >> >> Close the transactions that are holding them. Look into >> pg_stat_activity and pg_prepared_xacts. > > > Thanks for this. But can I simply delete all the pg_locks table? Or > delete all rows in pg_stat_activity? In my case the _xacts table is > empty. Ideally I don't want to lose pg_stat_activity. I just want to > change a RULE on a table. Is there any place I can do that? > I found 232 rows in pg_stat_activity of offending "current_query". How can I delete them? ---- =# delete from pg_stat_activity where current_query like 'UPDATE visitcount%'; ERROR: cannot delete from a view HINT: You need an unconditional ON DELETE DO INSTEAD rule ---- Thanks!
Phoenix Kiula <phoenix.kiula@gmail.com> writes: > I guess my question is, how should I remove all pending locks on a > table so that I can get on with the rest of the stuff? > > I mean, even if I can now find an offending RULE on the table, I > cannot replace or remove it. ' You're off on the wrong track. Locks are held by transactions until the transaction commits. You need to find the transactions which are holding these locks and either commit or roll them back. You look in pg_locks to see what locks transactions are holding. In particular look for rows with "granted" set to "t", especially locks on relations and especially ExclusiveLocks. Then you take the pid of those transactions and look in pg_stat_activity to see what they're up to. If they say "<idle in transaction>" then they're waiting for the client to do something. If they stay that way for any length of time while holding locks which block other transactions that's bad. Alternately if you see a query in pg_stat_transaction which is taking a long time to run you might check whether you have a bad plan or a bad query running while holding locks effectively doing the same thing. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services!