| -- |
| -- Test \dx and \dx+, to display extensions. |
| -- |
| -- We just use plpgsql as an example of an extension here. |
| \dx plpgsql |
| List of installed extensions |
| Name | Version | Schema | Description |
| ---------+---------+------------+------------------------------ |
| plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language |
| (1 row) |
| |
| \dx+ plpgsql |
| Objects in extension "plpgsql" |
| Object description |
| ------------------------------------------- |
| function plpgsql_call_handler() |
| function plpgsql_inline_handler(internal) |
| function plpgsql_validator(oid) |
| language plpgsql |
| (4 rows) |
| |
| -- |
| -- Test extended \du flags |
| -- |
| -- https://github.com/greenplum-db/gpdb/issues/1028 |
| -- |
| -- Problem: the cluster can be initialized with any Unix user |
| -- therefore create specific test roles here, and only |
| -- test the \du output for this role, also drop them afterwards |
| CREATE ROLE test_psql_du_1 WITH SUPERUSER; |
| \du test_psql_du_1 |
| List of roles |
| Role name | Attributes | Member of |
| ----------------+-------------------------+----------- |
| test_psql_du_1 | Superuser, Cannot login | {} |
| |
| DROP ROLE test_psql_du_1; |
| CREATE ROLE test_psql_du_2 WITH SUPERUSER CREATEDB CREATEROLE CREATEEXTTABLE LOGIN CONNECTION LIMIT 5; |
| \du test_psql_du_2 |
| List of roles |
| Role name | Attributes | Member of |
| ----------------+------------------------------------------------------+----------- |
| test_psql_du_2 | Superuser, Create role, Create DB, Ext gpfdist Table+| {} |
| | 5 connections | |
| |
| DROP ROLE test_psql_du_2; |
| -- pg_catalog.pg_roles.rolcreaterextgpfd |
| CREATE ROLE test_psql_du_e1 WITH SUPERUSER CREATEEXTTABLE (type = 'readable', protocol = 'gpfdist'); |
| \du test_psql_du_e1 |
| List of roles |
| Role name | Attributes | Member of |
| -----------------+--------------------------------------------+----------- |
| test_psql_du_e1 | Superuser, Ext gpfdist Table, Cannot login | {} |
| |
| DROP ROLE test_psql_du_e1; |
| CREATE ROLE test_psql_du_e2 WITH SUPERUSER CREATEEXTTABLE (type = 'readable', protocol = 'gpfdists'); |
| \du test_psql_du_e2 |
| List of roles |
| Role name | Attributes | Member of |
| -----------------+--------------------------------------------+----------- |
| test_psql_du_e2 | Superuser, Ext gpfdist Table, Cannot login | {} |
| |
| DROP ROLE test_psql_du_e2; |
| -- pg_catalog.pg_roles.rolcreatewextgpfd |
| CREATE ROLE test_psql_du_e3 WITH SUPERUSER CREATEEXTTABLE (type = 'writable', protocol = 'gpfdist'); |
| \du test_psql_du_e3 |
| List of roles |
| Role name | Attributes | Member of |
| -----------------+------------------------------------------------------------+----------- |
| test_psql_du_e3 | Superuser, Wri Ext gpfdist Table, Cannot login, Bypass RLS | {} |
| |
| DROP ROLE test_psql_du_e3; |
| CREATE ROLE test_psql_du_e4 WITH SUPERUSER CREATEEXTTABLE (type = 'writable', protocol = 'gpfdists'); |
| \du test_psql_du_e4 |
| List of roles |
| Role name | Attributes | Member of |
| -----------------+------------------------------------------------------------+----------- |
| test_psql_du_e4 | Superuser, Wri Ext gpfdist Table, Cannot login, Bypass RLS | {} |
| |
| DROP ROLE test_psql_du_e4; |
| -- pg_catalog.pg_roles.rolcreaterexthttp |
| CREATE ROLE test_psql_du_e5 WITH SUPERUSER CREATEEXTTABLE (type = 'readable', protocol = 'http'); |
| \du test_psql_du_e5 |
| List of roles |
| Role name | Attributes | Member of |
| -----------------+-----------------------------------------+----------- |
| test_psql_du_e5 | Superuser, Ext http Table, Cannot login | {} |
| |
| DROP ROLE test_psql_du_e5; |
| -- does not exist |
| CREATE ROLE test_psql_du_e6 WITH SUPERUSER CREATEEXTTABLE (type = 'writable', protocol = 'http'); |
| ERROR: invalid CREATEEXTTABLE specification. writable http external tables do not exist |
| \du test_psql_du_e6 |
| List of roles |
| Role name | Attributes | Member of |
| -----------+------------+----------- |
| |
| DROP ROLE test_psql_du_e6; |
| ERROR: role "test_psql_du_e6" does not exist |
| -- Test replication and verbose. GPDB specific attributes are mixed with PG attributes. |
| -- Our role describe code is easy to be buggy when we merge with PG upstream code. |
| -- The tests here are used to double-confirm the correctness of our role describe code. |
| CREATE ROLE test_psql_du_e9 WITH SUPERUSER REPLICATION; |
| COMMENT ON ROLE test_psql_du_e9 IS 'test_role_description'; |
| \du test_psql_du_e9 |
| List of roles |
| Role name | Attributes | Member of |
| -----------------+--------------------------------------+----------- |
| test_psql_du_e9 | Superuser, Cannot login, Replication | {} |
| |
| \du+ test_psql_du_e9 |
| List of roles |
| Role name | Attributes | Member of | Description |
| -----------------+--------------------------------------+-----------+----------------------- |
| test_psql_du_e9 | Superuser, Cannot login, Replication | {} | test_role_description |
| |
| DROP ROLE test_psql_du_e9; |
| -- |
| -- Test \d commands. |
| -- |
| -- Create a test schema, with different kinds of relations. To make the |
| -- expected output insensitive to the current username, change the owner. |
| CREATE ROLE test_psql_de_role; |
| NOTICE: resource queue required -- using default resource queue "pg_default" |
| CREATE FOREIGN DATA WRAPPER dummy_wrapper; |
| COMMENT ON FOREIGN DATA WRAPPER dummy_wrapper IS 'useless'; |
| CREATE SERVER dummy_server FOREIGN DATA WRAPPER dummy_wrapper; |
| CREATE SCHEMA test_psql_schema; |
| GRANT CREATE, USAGE ON SCHEMA test_psql_schema TO test_psql_de_role; |
| SET search_path = 'test_psql_schema'; |
| SET ROLE test_psql_de_role; |
| CREATE TABLE d_heap (i int4) with (appendonly = false); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| CREATE TABLE d_ao (i int4) with (appendonly = true, orientation = row); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| CREATE TABLE d_aocs (i int4) with (appendonly = true, orientation = column); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| CREATE VIEW d_view as SELECT 123; |
| CREATE INDEX d_index on d_heap(i); |
| -- Only superuser can create external or foreign tables. |
| RESET ROLE; |
| CREATE FOREIGN TABLE "dE_foreign_table" (c1 integer) |
| SERVER dummy_server; |
| ALTER FOREIGN TABLE "dE_foreign_table" OWNER TO test_psql_de_role; |
| CREATE EXTERNAL TABLE "dE_external_table" (c1 integer) |
| LOCATION ('file://localhost/dummy') FORMAT 'text'; |
| ALTER EXTERNAL TABLE "dE_external_table" OWNER TO test_psql_de_role; |
| -- create table using user defined access method |
| CREATE ACCESS METHOD bogus TYPE TABLE HANDLER heap_tableam_handler; |
| CREATE TABLE d_bogus_heap(a int) USING bogus; |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| ALTER TABLE "d_bogus_heap" OWNER TO test_psql_de_role; |
| -- There's a GPDB-specific Storage column. |
| \d |
| List of relations |
| Schema | Name | Type | Owner | Storage |
| ------------------+-------------------+---------------+-------------------+----------- |
| test_psql_schema | dE_external_table | foreign table | test_psql_de_role | |
| test_psql_schema | dE_foreign_table | foreign table | test_psql_de_role | |
| test_psql_schema | d_ao | table | test_psql_de_role | ao_row |
| test_psql_schema | d_aocs | table | test_psql_de_role | ao_column |
| test_psql_schema | d_bogus_heap | table | test_psql_de_role | bogus |
| test_psql_schema | d_heap | table | test_psql_de_role | heap |
| test_psql_schema | d_view | view | test_psql_de_role | |
| (7 rows) |
| |
| \d+ |
| List of relations |
| Schema | Name | Type | Owner | Storage | Persistence | Size | Description |
| ------------------+-------------------+---------------+-------------------+-----------+-------------+---------+------------- |
| test_psql_schema | dE_external_table | foreign table | test_psql_de_role | | permanent | 0 bytes | |
| test_psql_schema | dE_foreign_table | foreign table | test_psql_de_role | | permanent | 0 bytes | |
| test_psql_schema | d_ao | table | test_psql_de_role | ao_row | permanent | 128 kB | |
| test_psql_schema | d_aocs | table | test_psql_de_role | ao_column | permanent | 128 kB | |
| test_psql_schema | d_bogus_heap | table | test_psql_de_role | bogus | permanent | 0 bytes | |
| test_psql_schema | d_heap | table | test_psql_de_role | heap | permanent | 0 bytes | |
| test_psql_schema | d_view | view | test_psql_de_role | | permanent | 0 bytes | |
| (7 rows) |
| |
| -- The Storage column is not interesting for indexes, so it's omitted with |
| -- \di |
| \di |
| List of relations |
| Schema | Name | Type | Owner | Table |
| ------------------+---------+-------+-------------------+-------- |
| test_psql_schema | d_index | index | test_psql_de_role | d_heap |
| (1 row) |
| |
| \di+ |
| List of relations |
| Schema | Name | Type | Owner | Table | Persistence | Size | Description |
| ------------------+---------+-------+-------------------+--------+-------------+--------+------------- |
| test_psql_schema | d_index | index | test_psql_de_role | d_heap | permanent | 128 kB | |
| (1 row) |
| |
| -- But if tables are shown, too, then it's interesting again. |
| \dti |
| List of relations |
| Schema | Name | Type | Owner | Storage | Table |
| ------------------+--------------+-------+-------------------+-----------+-------- |
| test_psql_schema | d_ao | table | test_psql_de_role | ao_row | |
| test_psql_schema | d_aocs | table | test_psql_de_role | ao_column | |
| test_psql_schema | d_bogus_heap | table | test_psql_de_role | bogus | |
| test_psql_schema | d_heap | table | test_psql_de_role | heap | |
| test_psql_schema | d_index | index | test_psql_de_role | btree | d_heap |
| (5 rows) |
| |
| -- \dE should display both external and foreign tables |
| \dE "dE"* |
| List of relations |
| Schema | Name | Type | Owner |
| ------------------+-------------------+---------------+------------------- |
| test_psql_schema | dE_external_table | foreign table | test_psql_de_role |
| test_psql_schema | dE_foreign_table | foreign table | test_psql_de_role |
| (2 rows) |
| |
| \dE |
| List of relations |
| Schema | Name | Type | Owner |
| ------------------+-------------------+---------------+------------------- |
| test_psql_schema | dE_external_table | foreign table | test_psql_de_role |
| test_psql_schema | dE_foreign_table | foreign table | test_psql_de_role |
| (2 rows) |
| |
| -- \dd should list objects having comments |
| \dd |
| Object descriptions |
| Schema | Name | Object | Description |
| --------+------+--------+------------- |
| (0 rows) |
| |
| create rule dd_notify as on update to d_heap do also notify d_heap; |
| comment on rule dd_notify on d_heap is 'this is a rule'; |
| alter table d_heap add constraint dd_ichk check (i>20); |
| comment on constraint dd_ichk on d_heap is 'this is a constraint'; |
| create operator family dd_opfamily using btree; |
| comment on operator family dd_opfamily using btree is 'this is an operator family'; |
| \dd |
| Object descriptions |
| Schema | Name | Object | Description |
| ------------------+-------------+------------------+---------------------------- |
| test_psql_schema | dd_ichk | table constraint | this is a constraint |
| test_psql_schema | dd_notify | rule | this is a rule |
| test_psql_schema | dd_opfamily | operator family | this is an operator family |
| (3 rows) |
| |
| -- \df+ should list all exec locations |
| CREATE FUNCTION foofunc_exec_on_any() RETURNS int AS 'SELECT 1' LANGUAGE SQL EXECUTE ON ANY; |
| ALTER FUNCTION foofunc_exec_on_any() OWNER TO test_psql_de_role; |
| CREATE FUNCTION foofunc_exec_on_coordinator() RETURNS setof int AS 'SELECT 1' LANGUAGE SQL EXECUTE ON COORDINATOR; |
| ALTER FUNCTION foofunc_exec_on_coordinator() OWNER TO test_psql_de_role; |
| CREATE FUNCTION foofunc_exec_on_all_segments() RETURNS setof int AS 'SELECT 1' LANGUAGE SQL EXECUTE ON ALL SEGMENTS; |
| ALTER FUNCTION foofunc_exec_on_all_segments() OWNER TO test_psql_de_role; |
| CREATE FUNCTION foofunc_exec_on_initplan() RETURNS setof int AS 'SELECT 1' LANGUAGE SQL EXECUTE ON INITPLAN; |
| ALTER FUNCTION foofunc_exec_on_initplan() OWNER TO test_psql_de_role; |
| \df+ foofunc_exec_on_* |
| List of functions |
| Schema | Name | Result data type | Argument data types | Type | Data access | Execute on | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description |
| ------------------+------------------------------+------------------+---------------------+------+--------------+--------------+------------+----------+-------------------+----------+-------------------+----------+-------------+------------- |
| test_psql_schema | foofunc_exec_on_all_segments | SETOF integer | | func | contains sql | all segments | volatile | unsafe | test_psql_de_role | invoker | | sql | SELECT 1 | |
| test_psql_schema | foofunc_exec_on_any | integer | | func | contains sql | any | volatile | unsafe | test_psql_de_role | invoker | | sql | SELECT 1 | |
| test_psql_schema | foofunc_exec_on_coordinator | SETOF integer | | func | contains sql | coordinator | volatile | unsafe | test_psql_de_role | invoker | | sql | SELECT 1 | |
| test_psql_schema | foofunc_exec_on_initplan | SETOF integer | | func | contains sql | initplan | volatile | unsafe | test_psql_de_role | invoker | | sql | SELECT 1 | |
| (4 rows) |
| |
| -- Clean up |
| DROP OWNED BY test_psql_de_role; |
| DROP ROLE test_psql_de_role; |
