Postgres-XC Wiki
Register
Advertisement

Show Table Distribution and Replication

The following statement shows each table's distribution/replication status.

 SELECT pg_class.relname, pgxc_class.pclocatortype,pgxc_class.pcattnum FROM
        pg_class, pgxc_class WHERE pg_class.oid = pgxc_class.pcrelid;

The following is better:

 SELECT pg_class.relname relation,
        pgxc_class.pclocatortype distribution,
        pg_attribute.attname attribute
    FROM pg_class, pgxc_class, pg_attribute
    WHERE pg_class.oid = pgxc_class.pcrelid
          and pg_class.oid = pg_attribute.attrelid
          and pgxc_class.pcattnum = pg_attribute.attnum
 UNION
 SELECT pg_class.relname relation,
        pgxc_class.pclocatortype distribution,
        'none' attribute
    FROM pg_class, pgxc_class, pg_attribute
    WHERE pg_class.oid = pgxc_class.pcrelid
          and pg_class.oid = pg_attribute.attrelid
          and pgxc_class.pcattnum = 0
    ;

The result is like:

  relation   | distribution | attribute 
-------------+--------------+-----------
 table_five  | M            | a
 table_four  | H            | a
 table_one   | H            | a
 table_seven | R            | none
 table_six   | N            | none
 table_three | H            | a
 table_two   | H            | a
(7 rows)
Advertisement