[Postgres-xl-general] Unable to drop a table
Dennis
dennisr at visi.com
Wed Oct 15 15:04:42 PDT 2014
I did look for open transactions but I was not able to see anything. I did see that the DROP commands where waiting but I was not able to determine the PID they are waiting on. The only odd thing that I probably should not have done was kill a process that was loading data to the table that I can not drop via the copy command. Also, I have tried restarting the entire cluster, datanodes, coordinators, gtm and gtm_proxies and re-issuing the DROP command but it always hangs.
Will individually dropping the table on each of the datanodes cause more problems? I have other tables and data on the cluster that I really don’t want to have to reload from a fresh build if I can avoid it.
Below are the pg_stat_activity queries on the coordinators and datanodes.
pg_stat_activity on the coordinators:
xxxxx=# select * from pg_stat_activity;
datid | pid | usesysid | application_name | state | query | waiting | xact_start | query_start |
backend_start | state_change | client_addr | client_hostname | client_port
-------+-------+----------+------------------+--------+---------------------------------+---------+------------+-------------------------------+------
-------------------------+-------------------------------+-------------+-----------------+-------------
16388 | 12578 | 10 | psql | active | select * from pg_stat_activity; | f | | 2014-10-15 16:46:07.994132-05 | 2014-
10-15 16:45:59.521281-05 | 2014-10-15 16:46:07.994137-05 | | | -1
(1 row)
pg_stat_activity on the datanodes:
=============================================
datanode_1:
xxxxx=# select * from pg_stat_activity;
datid | pid | usesysid | application_name | state | query | waiting | xact_start
| query_start | backend_start | state_change | client_addr | client_hostname | client_port
-------+-------+----------+------------------+--------+-------------------------------------------------------+---------+-----------------------------
--+-------------------------------+-------------------------------+-------------------------------+---------------+-----------------+-------------
16388 | 13079 | 10 | pgxc | active | SELECT pg_catalog.pg_table_size('scm.sca_natr_agg_w') | t | 2014-10-15 14:15:45.919364-0
5 | 2014-10-15 14:15:45.926893-05 | 2014-10-15 14:15:46.570739-05 | 2014-10-15 14:15:47.57075-05 | 10.104.101.51 | | 36074
16388 | 19340 | 10 | pgxc | active | drop table sca_natr_agg_w; | t | 2014-10-14 03:11:28.946478-0
5 | 2014-10-14 03:11:28.947538-05 | 2014-10-14 03:08:13.766078-05 | 2014-10-14 03:11:28.966283-05 | 10.104.101.50 | | 47482
16388 | 13319 | 10 | psql | active | select * from pg_stat_activity; | f | 2014-10-15 16:50:09.280613-0
5 | 2014-10-15 16:50:19.929648-05 | 2014-10-15 16:49:29.870162-05 | 2014-10-15 16:50:19.182093-05 | | | -1
(3 rows)
xxxx=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode
| granted | fastpath
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------
------+---------+----------
relation | 16388 | 12615 | | | | | | | | 4/1387 | 13319 | AccessShareLoc
k | t | t
relation | 16388 | 12663 | | | | | | | | 4/1387 | 13319 | AccessShareLoc
k | t | t
relation | 16388 | 540620 | | | | | | | | 3/22 | 19340 | AccessExclusiv
eLock | f | f
relation | 16388 | 540620 | | | | | | | | -1/493948 | | RowExclusiveLo
ck | t | f
relation | 16388 | 540620 | | | | | | | | 2/3110 | 13079 | AccessShareLoc
k | f | f
(5 rows)
============================================
datanode_2:
xxxxx=# select * from pg_stat_activity ;
datid | pid | usesysid | application_name | state | query | waiting | xact_start
| query_start | backend_start | state_change | client_addr | client_hostname | client_port
-------+-------+----------+------------------+--------+-------------------------------------------------------+---------+-----------------------------
--+-------------------------------+-------------------------------+-------------------------------+---------------+-----------------+-------------
16388 | 30195 | 10 | pgxc | active | SELECT pg_catalog.pg_table_size('scm.sca_natr_agg_w') | t | 2014-10-15 14:15:45.919364-0
5 | 2014-10-15 14:15:45.927117-05 | 2014-10-15 14:15:45.92979-05 | 2014-10-15 14:15:47.57038-05 | 10.104.101.51 | | 50667
16388 | 11442 | 10 | pgxc | active | drop table sca_natr_agg_w; | t | 2014-10-14 03:11:28.946478-0
5 | 2014-10-14 03:11:28.947781-05 | 2014-10-14 03:08:13.761195-05 | 2014-10-14 03:11:28.967161-05 | 10.104.101.50 | | 32860
16388 | 31643 | 10 | psql | active | select * from pg_stat_activity ; | f | 2014-10-15 16:55:18.122048-0
5 | 2014-10-15 16:55:20.085135-05 | 2014-10-15 16:55:09.423285-05 | 2014-10-15 16:55:19.382355-05 | | | -1
(3 rows)
xxxxx=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode
| granted | fastpath
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------
------+---------+----------
relation | 16388 | 12615 | | | | | | | | 4/1389 | 13319 | AccessShareLoc
k | t | t
relation | 16388 | 12663 | | | | | | | | 4/1389 | 13319 | AccessShareLoc
k | t | t
relation | 16388 | 540620 | | | | | | | | 3/22 | 19340 | AccessExclusiv
eLock | f | f
relation | 16388 | 540620 | | | | | | | | -1/493948 | | RowExclusiveLo
ck | t | f
relation | 16388 | 540620 | | | | | | | | 2/3110 | 13079 | AccessShareLoc
k | f | f
(5 rows)
==============================================================
datanode_3
xxxx=# select * from pg_stat_activity;
datid | pid | usesysid | application_name | state | query | waiting | xact_start
| query_start | backend_start | state_change | client_addr | client_hostname | client_port
-------+-------+----------+------------------+--------+-------------------------------------------------------+---------+-----------------------------
--+-------------------------------+-------------------------------+-------------------------------+---------------+-----------------+-------------
16388 | 18627 | 10 | pgxc | active | drop table sca_natr_agg_w; | t | 2014-10-14 03:11:28.946478-0
5 | 2014-10-14 03:11:28.947075-05 | 2014-10-14 03:11:01.967545-05 | 2014-10-14 03:11:28.966593-05 | 10.104.101.50 | | 43175
16388 | 32679 | 10 | pgxc | active | SELECT pg_catalog.pg_table_size('scm.sca_natr_agg_w') | t | 2014-10-15 14:15:45.919364-0
5 | 2014-10-15 14:15:45.926707-05 | 2014-10-15 14:15:46.927144-05 | 2014-10-15 14:15:47.570103-05 | 10.104.101.51 | | 40219
16388 | 1438 | 10 | psql | active | select * from pg_stat_activity; | f |
| 2014-10-15 16:57:32.239673-05 | 2014-10-15 16:57:19.475661-05 | 2014-10-15 16:57:32.239676-05 | | | -1
(3 rows)
xxxx=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode
| granted | fastpath
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------
------+---------+----------
relation | 16388 | 12615 | | | | | | | | 4/674 | 1438 | AccessShareLoc
k | t | t
relation | 16388 | 12663 | | | | | | | | 4/674 | 1438 | AccessShareLoc
k | t | t
relation | 16388 | 540236 | | | | | | | | 2/16 | 18627 | AccessExclusiv
eLock | f | f
relation | 16388 | 540236 | | | | | | | | -1/493948 | | RowExclusiveLo
ck | t | f
relation | 16388 | 540236 | | | | | | | | 3/3896 | 32679 | AccessShareLoc
k | f | f
(5 rows)
On Oct 15, 2014, at 2:25 PM, Mason Sharp <msharp at translattice.com> wrote:
>
>
> On Wed, Oct 15, 2014 at 3:17 PM, Dennis <dennisr at visi.com> wrote:
> I am not able to drop a particular table while logged in to a coordinator. Other tables I was able to drop with out problem. I am wondering if I can just drop the table from each datanode directly?
>
> You should not have to do that.
>
> There probably was some transaction open on the tables. You could check pg_stat_activity to see, running it via EXECUTE DIRECT on the individual nodes to see.
>
>
>
> Dennis
> ------------------------------------------------------------------------------
> Comprehensive Server Monitoring with Site24x7.
> Monitor 10 servers for $9/Month.
> Get alerted through email, SMS, voice calls or mobile push notifications.
> Take corrective actions from your mobile device.
> http://p.sf.net/sfu/Zoho
> _______________________________________________
> Postgres-xl-general mailing list
> Postgres-xl-general at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/postgres-xl-general
>
>
>
> --
> Mason Sharp
>
> TransLattice - http://www.translattice.com
> Clustered and Distributed Database Solutions
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20141015/f7a409f5/attachment.htm>
More information about the postgres-xl-general
mailing list