[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