小工具-Greenplum数据库中AO表和非AO表获取




  • --执行说明
    /*该脚本是用来获取数据库中每个 schema 下是否是 AO 表,如果有 AO 表,将会存储于临时表
    *tab_aotable中,如果是非 AO 表,那么将会存储于临时表 tab_naotable 中,由于存储非AO
    *表和AO表都是存储于临时表中的,因此在会话退出后,临时表将会自动销毁,如果需要获取,
    *请重新执行以下语句
    */

    drop table if exists tab_aotable;
    drop table if exists tab_naotable;

    create temporary table tab_aotable(table_oid oid,table_name text,aotable text,cond char(1)) distributed by (table_oid);

    create temporary table tab_naotable(table_oid oid,table_name text,naotable text,cond char(1)) distributed by (table_oid);

    create or replace function f_get_aotable()
    returns void
    as
    $$
    declare
    v_list_toid oid;
    v_list_tname text;
    v_sql text;
    v_is_ao_tablename text;
    v_table_oid oid;
    v_table_name text;
    v_is_ao_table text;
    cur1 CURSOR FOR
    SELECT a.oid,
    c.nspname ||'.' || b.tablename
    FROM pg_class a,
    pg_tables b,
    pg_namespace c
    WHERE a.relname = b.tablename
    AND c.oid = a.relnamespace
    AND a.relname !~ '^pg|^gp|^_|^sql';
    begin
    open cur1;
    loop
    fetch cur1 into v_list_toid,v_list_tname;
    exit when not found;
    v_sql = 'SELECT relid,
    t1.nspname||''.''||t1.tablename,
    t1.reloptions
    FROM pg_appendonly t,
    (SELECT a.oid,
    a.reloptions,
    b.tablename,
    c.nspname
    FROM pg_class a,
    pg_tables b,
    pg_namespace c
    WHERE a.relname = b.tablename
    AND c.oid = a.relnamespace
    AND a.relname !~ ''^pg|^gp|^_|^SQL'') t1
    WHERE t.relid = t1.oid
    AND t1.oid = ' ||v_list_toid ;

    execute v_sql into v_table_oid,v_table_name,v_is_ao_table;
    if v_table_oid is not null then
    insert into tab_aotable values(v_table_oid,v_table_name,v_is_ao_table,'1');
    else
    insert into tab_naotable values(v_list_toid,v_list_tname,'no ao table','0');
    end if;
    end loop;
    raise notice 'PLPGSQL Exec Successfully';
    close cur1;
    end;
    $$
    language plpgsql;

    /*以下为执行示例

    --删除和创建存储AO和非AO表的临时表
    postgres=# drop table if exists tab_aotable;
    NOTICE: table "tab_aotable" does not exist, skipping
    DROP TABLE
    postgres=# drop table if exists tab_naotable;
    NOTICE: table "tab_naotable" does not exist, skipping
    DROP TABLE
    postgres=#
    postgres=# create temporary table tab_aotable(table_oid oid,table_name text,aotable text,cond char(1)) distributed by (table_oid);
    CREATE TABLE
    postgres=#
    postgres=# create temporary table tab_naotable(table_oid oid,table_name text,naotable text,cond char(1)) distributed by (table_oid);
    CREATE TABLE
    --创建函数此处略,仅作为查询已经创建好该函数
    postgres=# \df f_get_aotable();
    List of functions
    Schema | Name | Result data type | Argument data types | Type
    --------+---------------+------------------+---------------------+--------
    public | f_get_aotable | void | | normal
    (1 row)

    --执行函数
    postgres=# select * from f_get_aotable();
    NOTICE: PLPGSQL exec successfully
    f_get_aotable
    ---------------

    (1 row)
    --查看AO表
    postgres=# SELECT * FROM tab_aotable ;
    table_oid | table_name | aotable | cond
    -----------+--------------------------------+-------------------+------
    33337 | public.tab_sales_1_prt_returns | {appendonly=true} | 1
    33324 | public.tab_sales_1_prt_sales | {appendonly=true} | 1
    33227 | public.tab_t2 | {appendonly=true} | 1
    33314 | public.tab_sales | {appendonly=true} | 1
    (4 rows)
    --查看非AO表
    postgres=# select * from tab_naotable ;
    table_oid | table_name | naotable | cond
    -----------+------------------------+-------------+------
    32783 | s2.tab_product_57 | no ao table | 0
    32799 | s2.tab_product_60 | no ao table | 0
    32815 | s2.tab_product_64 | no ao table | 0
    32831 | s2.tab_product_68 | no ao table | 0

    */

    Speak Your Mind

    *

    京ICP备14059771号-2