List Append Only / Append Organized table details in Greenplum

posted May 27, 2015, 3:12 PM by Sachchida Ojha   [ updated May 30, 2015, 1:39 PM ]
The gadget spec URL could not be found
sachi=# SELECT n.nspname AS schema_name ,c.relname AS table_name FROM pg_catalog.pg_appendonly a ,pg_catalog.pg_class c ,pg_catalog.pg_namespace n WHERE c.oid = a.relid AND n.oid = c.relnamespace AND a.compresstype IS NULL ;
 schema_name | table_name 
-------------+------------
 public      | bar
 public      | bar1
(2 rows)

sachi=# \d bar
Append-Only Table "public.bar"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | text    | 
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (a)
The gadget spec URL could not be found
sachi=# \d bar1
Append-Only Table "public.bar1"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | text    | 
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: t
Distributed by: (a)

The gadget spec URL could not be found

sachi=# SELECT n.nspname AS schema_name ,c.relname AS table_name FROM pg_catalog.pg_appendonly a ,pg_catalog.pg_class c ,pg_catalog.pg_namespace n WHERE c.oid = a.relid AND n.oid = c.relnamespace ;
 schema_name | table_name 
-------------+------------
 public      | bar
 public      | bar1
 public      | foo
 public      | sales
 public      | xyz
(5 rows)
The gadget spec URL could not be found
sachi=# \d foo
Append-Only Table "public.foo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | text    | 
Compression Type: zlib
Compression Level: 5
Block Size: 32768
Checksum: t
Distributed by: (a)

sachi=# \d sales
Append-Only Columnar Table "public.sales"
 Column |     Type      | Modifiers 
--------+---------------+-----------
 id     | integer       | 
 date   | date          | 
 amt    | numeric(10,2) | 
Checksum: t
Distributed by: (id)

sachi=# \d xyz
       Append-Only Columnar Table "public.xyz"
  Column   |            Type             | Modifiers 
-----------+-----------------------------+-----------
 date_hour | timestamp without time zone | 
 mv        | character varying(255)      | 
 visits    | numeric                     | 
Checksum: t
Distributed by: (date_hour, mv, visits)

The gadget spec URL could not be found

sachi=# SELECT n.nspname AS schema_name ,c.relname AS table_name, a.compresstype FROM pg_catalog.pg_appendonly a ,pg_catalog.pg_class c ,pg_catalog.pg_namespace n WHERE c.oid = a.relid AND n.oid = c.relnamespace ;
 schema_name | table_name | compresstype 
-------------+------------+--------------
 public      | bar        | 
 public      | bar1       | 
 public      | foo        | zlib
 public      | sales      | quicklz
 public      | xyz        | quicklz
(5 rows)

sachi=# \d pg_catalog.pg_appendonly
    Table "pg_catalog.pg_appendonly"
     Column      |   Type   | Modifiers 
-----------------+----------+-----------
 relid           | oid      | not null
 blocksize       | integer  | not null
 safefswritesize | integer  | not null
 compresslevel   | smallint | not null
 majorversion    | smallint | not null
 minorversion    | smallint | not null
 checksum        | boolean  | not null
 compresstype    | text     | 
 columnstore     | boolean  | 
 segrelid        | oid      | 
 segidxid        | oid      | 
 blkdirrelid     | oid      | 
 blkdiridxid     | oid      | 
 version         | integer  | 
 visimaprelid    | oid      | 
 visimapidxid    | oid      | 
Indexes:
    "pg_appendonly_relid_index" UNIQUE, btree (relid)
The gadget spec URL could not be found
sachi=# \d pg_catalog.pg_class                                                                                                                                    
Table "pg_catalog.pg_class"
     Column     |   Type    | Modifiers 
----------------+-----------+-----------
 relname        | name      | not null
 relnamespace   | oid       | not null
 reltype        | oid       | not null
 relowner       | oid       | not null
 relam          | oid       | not null
 relfilenode    | oid       | not null
 reltablespace  | oid       | not null
 relpages       | integer   | not null
 reltuples      | real      | not null
 reltoastrelid  | oid       | not null
 reltoastidxid  | oid       | not null
 relaosegrelid  | oid       | not null
 relaosegidxid  | oid       | not null
 relhasindex    | boolean   | not null
 relisshared    | boolean   | not null
 relkind        | "char"    | not null
 relstorage     | "char"    | not null
 relnatts       | smallint  | not null
 relchecks      | smallint  | not null
 reltriggers    | smallint  | not null
 relukeys       | smallint  | not null
 relfkeys       | smallint  | not null
 relrefs        | smallint  | not null
 relhasoids     | boolean   | not null
 relhaspkey     | boolean   | not null
 relhasrules    | boolean   | not null
 relhassubclass | boolean   | not null
 relfrozenxid   | xid       | not null
 relacl         | aclitem[] | 
 reloptions     | text[]    | 
Indexes:
    "pg_class_oid_index" UNIQUE, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)
The gadget spec URL could not be found

sachi=# SELECT n.nspname AS schema_name ,c.relname AS table_name, c.reltype,c.relowner,c.relpages, c.relkind,c.relstorage,c.relhasindex,a.compresstype, a.compresslevel,a.columnstore FROM pg_catalog.pg_appendonly a ,pg_catalog.pg_class c ,pg_catalog.pg_namespace n WHERE c.oid = a.relid AND n.oid = c.relnamespace ;
 schema_name | table_name | reltype | relowner | relpages | relkind | relstorage | relhasindex | compresstype | compresslevel | columnstore 
-------------+------------+---------+----------+----------+---------+------------+-------------+--------------+---------------+-------------
 public      | bar        |   37322 |    17146 |        0 | r       | a          | f           |              |             0 | f
 public      | bar1       |   37354 |    17146 |        0 | r       | a          | f           |              |             0 | f
 public      | foo        |   37386 |    17146 |        0 | r       | a          | f           | zlib         |             5 | f
 public      | sales      |   37446 |    17146 |        1 | r       | c          | f           | quicklz      |             1 | t
 public      | xyz        |   37476 |    17146 |        0 | r       | c          | f           | quicklz      |             1 | t
(5 rows)

sachi=# 

The gadget spec URL could not be found
The gadget spec URL could not be found

The gadget spec URL could not be found

The gadget spec URL could not be found

The gadget spec URL could not be found

The gadget spec URL could not be found

Comments