PostgreSQL 9.2.3 system queries

1. check child tables which inherit a base partitioned table

	SELECT 
	pg_inherits.*
	, c.relname AS child
	, p.relname AS parent
	FROM
	pg_inherits JOIN pg_class AS c ON (inhrelid=c.oid)
	JOIN pg_class as p ON (inhparent=p.oid) 
	where true 
	and p.relname = 'bsms_in'
	--    and c.relname = %(inherited_table)s
	;
 inhrelid | inhparent | inhseqno |      child       | parent  
----------+-----------+----------+------------------+---------
 51790061 |   8753326 |        1 | bsms_in_p2014w02 | bsms_in
 52461300 |   8753326 |        1 | bsms_in_p2014w03 | bsms_in
 53187892 |   8753326 |        1 | bsms_in_p2014w04 | bsms_in
(3 rows)

2. How to drop old PostgreSQL table which sequence is used by some tables that inherited its structure. Example of the issue:

dragkh@aegir.darkstar.org:5433::aws_bsms=[Mon Apr 14 01:08:56 EEST 2014]# drop table bsms_in_old_big;
ERROR:  cannot drop table bsms_in_old_big column id because other objects depend on it
DETAIL:  default for table bsms_in_p2014w01 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w02 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w03 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w04 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w05 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w06 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w07 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w08 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w09 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w10 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w11 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w12 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w13 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w14 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w15 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w16 column id depends on sequence bsms_in_id_seq
default for table bsms_in_p2014w17 column id depends on sequence bsms_in_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Time: 9.258 ms

Get The list of the sequences and the corresponding tables that are their “OWNERS”

SELECT s.relname as sequence_name,  
       n.nspname as sequence_schema,  
       t.relname as related_table, 
       a.attname as related_column 
  FROM pg_class s, pg_depend d, pg_class t, pg_attribute a, pg_namespace n 
  WHERE s.relkind     = 'S' 
    AND n.oid         = s.relnamespace 
    AND d.objid       = s.oid 
    AND d.refobjid    = t.oid 
    AND (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) order by sequence_name;

Free the sequences : more info about “owned by” here : http://www.postgresql.org/docs/9.2/static/sql-altersequence.html Specifying OWNED BY NONE removes any existing association, making the sequence “free-standing”.

dragkh@aegir.darkstar.org:5433::aws_bsms=[Mon Apr 14 01:25:00 EEST 2014]# ALTER SEQUENCE bsms_in_id_seq OWNED BY NONE;
ALTER SEQUENCE
Time: 1.085 ms
dragkh@aegir.darkstar.org:5433::aws_bsms=[Mon Apr 14 01:25:13 EEST 2014]# drop table bsms_in_old_big;
DROP TABLE
Time: 18.084 ms

Slackre4Life