Get table metadata for a data map

Get external table storage size

AnalyticDB for PostgreSQL provides the get_oss_table_size(regclass) function to quickly check the storage size of an OSS external table.

SELECT get_oss_table_size('public.oss_foreign_test'::regclass);

Sample output

 get_oss_table_size 
--------------------
     550207488
(1 row)

Get table column information

AnalyticDB for PostgreSQL provides the adbpg_toolkit.get_table_columns_info(regclass) function to quickly retrieve column information for any table.

SELECT * FROM adbpg_toolkit.get_table_columns_info('test_table'::regclass) ;

Sample output

id | attname | atttype | attnotnull | description | is_primary_key | is_foreign_key | is_partition_key 
----+---------+---------+------------+-------------+----------------+----------------+------------------
  1 | a       | integer | f          |             | f              | f              | f
  2 | b       | date    | f          |             | f              | f              | t
(2 rows)

Return parameters

Get table modification history

AnalyticDB for PostgreSQL provides the adbpg_toolkit.get_table_last_operations(regclass) function to retrieve the history of DDL operations on a table.

SELECT * FROM adbpg_toolkit.get_table_last_operations('test_table'::regclass);

Sample output

staactionname | stausename | stasubtype |            statime            
---------------+------------+------------+-------------------------------
 PARTITION     | adbpgadmin | EXCHANGE   | 2024-08-16 10:53:10.329194+08
 CREATE        | adbpgadmin | TABLE      | 2024-08-16 10:45:50.992821+08
(2 rows)

Return parameters

Generate a table DDL

AnalyticDB for PostgreSQL provides the dump_parent_table_ddl(regclass) function to generate the DDL statement for a table. For a partitioned table, this function returns only the structure of the parent table, making it easy to understand or copy the table structure.

SELECT dump_parent_table_ddl('test_table'::regclass);

Sample output

                                                                   dump_parent_table_ddl                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------------
 --
 -- Greenplum Database database dump
 --

 -- Dumped from database version 12.12
 -- Dumped by pg_dump version 12.12

 SET gp_default_storage_options = '';
 SET statement_timeout = 0;
 SET lock_timeout = 0;
 SET idle_in_transaction_session_timeout = 0;
 SET idle_session_timeout = 0;
 SET client_encoding = 'UTF8';
 SET standard_conforming_strings = on;
 SELECT pg_catalog.set_config('search_path', '', false);
 SET check_function_bodies = false;
 SET xmloption = content;
 SET client_min_messages = warning;
 SET row_security = off;

 SET default_tablespace = '';

 --
 -- Name: test_table; Type: TABLE; Schema: public; Owner: adbpgadmin
 --

 CREATE TABLE public.test_table (
     a integer,
     b date
     )
 PARTITION BY LIST (b)
 DISTRIBUTED BY (a);


 ALTER TABLE public.test_table OWNER TO adbpgadmin;

 --
 -- Greenplum Database database dump complete
 --

(39 rows)

Get table statistics

AnalyticDB for PostgreSQL provides the adbpg_toolkit.table_datamap table, which contains statistics for all tables. The database also provides functions to refresh and query these statistics.

                                Table "adbpg_toolkit.table_datamap"
     Column     |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
----------------+---------+-----------+----------+---------+----------+--------------+-------------
 oid            | oid     |           | not null |         | plain    |              | 
 parent_oid     | oid     |           |          |         | plain    |              | 
 schema_name    | text    |           |          |         | extended |              | 
 table_name     | text    |           |          |         | extended |              | 
 size           | bigint  |           |          |         | plain    |              | 
 pretty_size    | text    |           |          |         | extended |              | 
 tuple_count    | bigint  |           |          |         | plain    |              | 
 is_part_parent | boolean |           |          |         | plain    |              | 
Indexes:
    "table_datamap_pkey" PRIMARY KEY, btree (oid)
Distributed by: (oid)
Access method: heap

Column descriptions

• Refresh statistics

  --- Refreshes statistics for a specific table and its child tables and updates table_datamap.
  SELECT adbpg_toolkit.refresh_table('public.test_table');
  
  --- Refreshes statistics for all tables and updates table_datamap.
  SELECT adbpg_toolkit.refresh_datamap();

• Query statistics

  --- Shows statistics for a table and its child tables.
  SELECT * FROM adbpg_toolkit.show_partition_info('public.test_table');