Greenplum Database New Features -  Append-Optimized Tables and compaction information

Note: When migrating Greenplum Database from 4.2.x.x to 4.3, append-only tables are migrated to append-optimized tables. GPDB 4.3.2.1 Fixes the problem of incorrect conversion of append-only tables to append-optimized tables during an upgrade from Greenplum Database 4.2.x to a Greenplum Database 4.3.x release prior to 4.3.2.1. The incorrect conversion causes append-optimizied table inconsistencies in the upgraded Greenplum Database system.

sachi=# select * from pg_views where viewname like '%append%';

schemaname | viewname | viewowner | definition

------------+---------------------+-----------+-------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------

gp_toolkit | __gp_is_append_only | gpadmin | SELECT pgc.oid AS iaooid, CASE WHEN (pgao.relid IS NULL) THEN false ELSE true END AS iaotype FROM (pg_class

pgc LEFT JOIN pg_appendonly pgao ON ((pgc.oid = pgao.relid)));

(1 row)

sachi=# \d gp_toolkit.__gp_is_append_only

View "gp_toolkit.__gp_is_append_only"

Column | Type | Modifiers

---------+---------+-----------

iaooid | oid |

iaotype | boolean |

View definition:

SELECT pgc.oid AS iaooid,

CASE

WHEN pgao.relid IS NULL THEN false

ELSE true

END AS iaotype

FROM pg_class pgc

LEFT JOIN pg_appendonly pgao ON pgc.oid = pgao.relid;

sachi=# select * from pg_tables where tablename like '%append%';

schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers

------------+----------------------------+------------+------------+------------+----------+-------------

pg_catalog | pg_appendonly | gpadmin | | t | f | f

pg_catalog | pg_appendonly_alter_column | gpadmin | | t | f | f

(2 rows)

sachi=# select * from pg_catalog.pg_appendonly;

relid | blocksize | safefswritesize | compresslevel | majorversion | minorversion | checksum | compresstype | columnstore | segrelid | segidxid | blkdirrel

id | blkdiridxid | version | visimaprelid | visimapidxid

-------+-----------+-----------------+---------------+--------------+--------------+----------+--------------+-------------+----------+----------+----------

---+-------------+---------+--------------+--------------

(0 rows)

sachi=# \d pg_proc

Table "pg_catalog.pg_proc"

Column | Type | Modifiers

----------------+-----------+-----------

proname | name | not null

pronamespace | oid | not null

proowner | oid | not null

prolang | oid | not null

proisagg | boolean | not null

prosecdef | boolean | not null

proisstrict | boolean | not null

proretset | boolean | not null

provolatile | "char" | not null

pronargs | smallint | not null

prorettype | oid | not null

proiswin | boolean | not null

proargtypes | oidvector | not null

proallargtypes | oid[] |

proargmodes | "char"[] |

proargnames | text[] |

prosrc | text |

probin | bytea |

proacl | aclitem[] |

prodataaccess | "char" | not null

Indexes:

"pg_proc_oid_index" UNIQUE, btree (oid)

"pg_proc_proname_args_nsp_index" UNIQUE, btree (proname, proargtypes, pronamespace)

sachi=# select * from pg_proc where proname like '%append%';

proname | pronamespace | proowner | prolang | proisagg | prosecdef | proisstrict | proretset | provolatile | pronargs | prorettype | proiswin | proarg

types | proallargtypes | proargmodes | proargnames | prosrc | probin | proacl | prodataaccess

--------------+--------------+----------+---------+----------+-----------+-------------+-----------+-------------+----------+------------+----------+-------

------+----------------+-------------+-------------+------------+--------+--------+---------------

array_append | 11 | 10 | 12 | f | f | f | f | i | 2 | 2277 | f | 2277 2

283 | | | | array_push | - | | n

(1 row)

sachi=#