Strange behavior: row won't delete

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 in db 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 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?

----- "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!