Pivotal Knowledge Base

Follow

Fail to drop resource queue with name contains unrecognized characters

Environment

Pivotal Greenplum (GPDB) 4.x

Symptom

When attempting to drop a resource queue, it fails with "does not exist" error.

Error Message:

gpadmin=# select * from pg_resqueue;
rsqname | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit
------------+---------------+--------------+---------------+--------------------
pg_default | 20 | -1 | f | 0
myqueue1  | 14 | -1 | f | 0
(2 rows)
gpadmin=# drop resource queue myqueue1;
ERROR: resource queue "myqueue1" does not exist
gpadmin=# select * from pg_resqueue where rsqname='myqueue1';
rsqname | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit
---------+---------------+--------------+---------------+--------------------
(0 rows)

Cause

The resource queue name actually contains some unrecognized characters in the tail.   

RCA 

1. The problematic resource queue can be selected out by matching with a wildcard character. And the problem exists on master and all segments.

gpadmin=# select *, gp_segment_id from pg_resqueue where rsqname like 'myqueue1%';
rsqname | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit | gp_segment_id
-----------+---------------+--------------+---------------+--------------------+---------------
myqueue1  | 14 | -1 | f | 0 | -1
gpadmin=# select *, gp_segment_id from gp_dist_random('pg_resqueue') where rsqname like 'myqueue1%';
rsqname | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit | gp_segment_id
-----------+---------------+--------------+---------------+--------------------+---------------
myqueue1 | 14 | -1 | f | 0 | 5
myqueue1 | 14 | -1 | f | 0 | 6
myqueue1 | 14 | -1 | f | 0 | 0
myqueue1 | 14 | -1 | f | 0 | 3
myqueue1 | 14 | -1 | f | 0 | 2
myqueue1 | 14 | -1 | f | 0 | 7
myqueue1 | 14 | -1 | f | 0 | 1
myqueue1 | 14 | -1 | f | 0 | 4
(8 rows)

2. By setting client encoding to some other character set (like "latin1"), the weird characters could be shown.

myqueue1-1.png

Resolution

Follow the steps to resolve this issue:

1. Dump out the problematic entry from pg_resqueue to a data file

gpadmin=# copy (select * from pg_resqueue where rsqname like 'myqueue1%') to '/tmp/myqueue1.sql';
COPY 1 [gpadmin@smdw temp]$ cat /tmp/myqueue1.sql
myqueue1  14 -1 f 0

2. Modify the data file to make the "drop resource queue" statement by using name inside the file.

For the above example, just add "drop resource queue" in the beginning and replace "14 -1 f 0" with a ';'.

NOTE: don't touch characters (even tab or whitespace) before "14 -1 f 0"

[gpadmin@smdw temp]$ cat /tmp/drop_myqueue1.sql
drop resource queue myqueue1  ; 

3. Run the modified SQL script file

[gpadmin@smdw temp]$ psql -f myqueue1.sql
DROP QUEUE 

Comments

Powered by Zendesk