This is an automated email from the ASF dual-hosted git repository. tuhaihe pushed a commit to branch main in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit 496632bfc4e27f280db6eff6b99266786e65c55f Author: zhaoxi <[email protected]> AuthorDate: Thu Aug 20 11:20:00 2026 +0800 Keep the pax schedule entries that other tests depend on for timing Two of the entries removed from the PAX schedules were not only test coverage: they also acted as quiescent points that later, timing- sensitive tests rely on. Removing them made those tests fail in CI, so restore them. contrib/pax_storage/src/test/isolation2: terminate_in_gang_creation The schedule comment directly above this entry says the case contains fault injection and must be put in a separate test group. It is also the only removed isolation2 entry that ran before workfile_mgr_test, whose first action is an immediate cluster restart. Without it, workfile_mgr_test's "gpstop -ari" returns exit code 2 and the cluster does not come back, so the following queries report two segments instead of four. Restoring the entry makes pax-ic-isolation2-opt-on pass again. contrib/pax_storage/src/test/regress: parallel_schedule Restore the schedule unchanged, together with the eight test files it references (roleattributes, init_privs, drop_operator, password, dbsize, sysviews, gp_foreign_data and oidjoins). gp_foreign_data ran as a group of its own immediately before the group holding "indexing", and "indexing" runs two CREATE INDEX CONCURRENTLY statements, one of which fails with a division by zero on purpose. With the earlier groups shortened, that statement now reports synchronous replication messages the expected file does not carry. Restoring only gp_foreign_data was not enough, so the whole schedule change is reverted rather than guessed at. The statement-level trimming in src/test/regress, the removal of the 103 storage-independent files under contrib/pax_storage, and the greenplum_schedule reduction are unchanged, so the bulk of the deletion is kept: greenplum_schedule stays at 307 tests. This is kept as a separate commit on purpose, so it can be reverted on its own if the CI evidence changes. --- .../expected/terminate_in_gang_creation.out | 170 ++++++++++++ .../src/test/isolation2/isolation2_schedule | 1 + .../isolation2/sql/terminate_in_gang_creation.sql | 98 +++++++ .../src/test/regress/expected/dbsize.out | 203 +++++++++++++++ .../src/test/regress/expected/drop_operator.out | 61 +++++ .../src/test/regress/expected/gp_foreign_data.out | 84 ++++++ .../src/test/regress/expected/init_privs.out | 12 + .../src/test/regress/expected/oidjoins.out | 285 +++++++++++++++++++++ .../src/test/regress/expected/password.out | 149 +++++++++++ .../src/test/regress/expected/roleattributes.out | 249 ++++++++++++++++++ .../src/test/regress/expected/sysviews.out | 178 +++++++++++++ .../pax_storage/src/test/regress/parallel_schedule | 25 +- .../pax_storage/src/test/regress/sql/dbsize.sql | 72 ++++++ .../src/test/regress/sql/drop_operator.sql | 56 ++++ .../src/test/regress/sql/gp_foreign_data.sql | 55 ++++ .../src/test/regress/sql/init_privs.sql | 10 + .../pax_storage/src/test/regress/sql/oidjoins.sql | 49 ++++ .../pax_storage/src/test/regress/sql/password.sql | 114 +++++++++ .../src/test/regress/sql/roleattributes.sql | 98 +++++++ .../pax_storage/src/test/regress/sql/sysviews.sql | 76 ++++++ 20 files changed, 2033 insertions(+), 12 deletions(-) diff --git a/contrib/pax_storage/src/test/isolation2/expected/terminate_in_gang_creation.out b/contrib/pax_storage/src/test/isolation2/expected/terminate_in_gang_creation.out new file mode 100644 index 00000000000..a45558c9439 --- /dev/null +++ b/contrib/pax_storage/src/test/isolation2/expected/terminate_in_gang_creation.out @@ -0,0 +1,170 @@ +-- start_matchsubs +-- m/seg[0-9] [0-9.]+:\d+/ +-- s/seg[0-9] [0-9.]+:\d+/segN IP:PORT/ +-- m/lock \[\d+,\d+\]/ +-- s/lock \[\d+,\d+\]// +-- m/DETAIL: .* FATAL: reader could not find writer proc entry/ +-- s/DETAIL: .* FATAL: reader could not find writer proc entry/DETAIL: FATAL: reader could not find writer proc entry/ +-- end_matchsubs + +-- skip dtx recovery check to avoid hitting the fault create_gang_in_progress. +SELECT gp_inject_fault_infinite('before_orphaned_check', 'skip', dbid) FROM gp_segment_configuration WHERE role='p' AND content=-1; + gp_inject_fault_infinite +-------------------------- + Success: +(1 row) +ALTER SYSTEM SET gp_dtx_recovery_interval to 5; +ALTER +SELECT pg_reload_conf(); + pg_reload_conf +---------------- + t +(1 row) +SELECT gp_wait_until_triggered_fault('before_orphaned_check', 1, dbid) FROM gp_segment_configuration WHERE role='p' AND content=-1; + gp_wait_until_triggered_fault +------------------------------- + Success: +(1 row) + +-- SIGSEGV issue when freeing gangs +-- +-- When SIGTERM is handled during gang creation we used to trigger +-- a wild pointer access like below backtrace: +-- +-- #0 raise +-- #1 StandardHandlerForSigillSigsegvSigbus_OnMainThread +-- #2 <signal handler called> +-- #3 MemoryContextFreeImpl +-- #4 cdbconn_termSegmentDescriptor +-- #5 DisconnectAndDestroyGang +-- #6 freeGangsForPortal +-- #7 AbortTransaction +-- ... +-- #14 ProcessInterrupts +-- #15 createGang_async +-- #16 createGang +-- #17 AllocateWriterGang + +DROP TABLE IF EXISTS foo; +DROP +CREATE TABLE foo (c1 int, c2 int) DISTRIBUTED BY (c1); +CREATE + +10: BEGIN; +BEGIN + +SELECT gp_inject_fault('create_gang_in_progress', 'reset', 1); + gp_inject_fault +----------------- + Success: +(1 row) +SELECT gp_inject_fault('create_gang_in_progress', 'suspend', 1); + gp_inject_fault +----------------- + Success: +(1 row) + +10&: SELECT * FROM foo a JOIN foo b USING (c2); <waiting ...> + +SELECT gp_wait_until_triggered_fault('create_gang_in_progress', 1, 1); + gp_wait_until_triggered_fault +------------------------------- + Success: +(1 row) + +SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE query = 'SELECT * FROM foo a JOIN foo b USING (c2);'; + pg_terminate_backend +---------------------- + t +(1 row) + +SELECT gp_inject_fault('create_gang_in_progress', 'resume', 1); + gp_inject_fault +----------------- + Success: +(1 row) + +10<: <... completed> +FATAL: terminating connection due to administrator command +server closed the connection unexpectedly + This probably means the server terminated abnormally + before or while processing the request. +10q: ... <quitting> + +DROP TABLE foo; +DROP + +-- Test a bug that if cached idle primary QE is gone (e.g. after kill-9, pg_ctl +-- restart, etc), a new query needs a new created reader gang might fail with +-- error like this: +-- +-- ERROR: failed to acquire resources on one or more segments +-- DETAIL: FATAL: reader could not find writer proc entry +-- DETAIL: lock [0,1260] AccessShareLock 0. Probably because writer gang is gone somehow. Maybe try rerunning. +-- (seg2 127.0.0.1:7004) +-- +-- This is ok since the writer gang is gone, but previously QD code does +-- not reset all gangs (just retry creating the new reader gang) so re-running +-- this query could always fail with the same error since the reader gang would +-- always fail to create. The below test is used to test the fix. + +-- skip FTS probes to avoid segment being marked down on restart +SELECT gp_inject_fault_infinite('fts_probe', 'skip', dbid) FROM gp_segment_configuration WHERE role='p' AND content=-1; + gp_inject_fault_infinite +-------------------------- + Success: +(1 row) +SELECT gp_request_fts_probe_scan(); + gp_request_fts_probe_scan +--------------------------- + t +(1 row) +-- Prevent below pg_ctl restart timeout although the timeout should be enough. +CHECKPOINT; +CHECKPOINT + +-- not recycle idle QEs to avoid the flaky test where restarting primary takes a long time. +11: SET gp_vmem_idle_resource_timeout TO 0; +SET +11: CREATE TABLE foo (c1 int, c2 int) DISTRIBUTED BY (c1); +CREATE +-- ORCA optimizes value scan so there is no additional reader gang in below INSERT. +11: SET optimizer = off; +SET +-- the value scan (reader gang) might be on any segment so restart all segments. +SELECT pg_ctl(datadir, 'restart', 'immediate') FROM gp_segment_configuration WHERE role='p' AND content != -1; + pg_ctl +-------- + OK + OK + OK +(3 rows) +11: INSERT INTO foo values(2),(1); +ERROR: failed to acquire resources on one or more segments +DETAIL: FATAL: reader could not find writer proc entry +DETAIL: lock [0,1260] AccessShareLock 0. Probably because writer gang is gone somehow. Maybe try rerunning. + (seg1 127.0.0.1:7004) +11: INSERT INTO foo values(2),(1); +INSERT 2 +11: DROP TABLE foo; +DROP +11: RESET gp_vmem_idle_resource_timeout; +RESET + +SELECT gp_inject_fault('fts_probe', 'reset', dbid) FROM gp_segment_configuration WHERE role='p' AND content=-1; + gp_inject_fault +----------------- + Success: +(1 row) +SELECT gp_inject_fault_infinite('before_orphaned_check', 'reset', dbid) FROM gp_segment_configuration WHERE role='p' AND content=-1; + gp_inject_fault_infinite +-------------------------- + Success: +(1 row) +2: ALTER SYSTEM RESET gp_dtx_recovery_interval; +ALTER +2: SELECT pg_reload_conf(); + pg_reload_conf +---------------- + t +(1 row) diff --git a/contrib/pax_storage/src/test/isolation2/isolation2_schedule b/contrib/pax_storage/src/test/isolation2/isolation2_schedule index 5f4d57a8e0b..26dd7f852cc 100644 --- a/contrib/pax_storage/src/test/isolation2/isolation2_schedule +++ b/contrib/pax_storage/src/test/isolation2/isolation2_schedule @@ -164,6 +164,7 @@ test: segwalrep/master_wal_switch # test: uao/bad_buffer_on_temp_ao_column # this case contains fault injection, must be put in a separate test group +test: terminate_in_gang_creation test: prepare_limit test: add_column_after_vacuum_skip_drop_column test: vacuum_after_vacuum_skip_drop_column diff --git a/contrib/pax_storage/src/test/isolation2/sql/terminate_in_gang_creation.sql b/contrib/pax_storage/src/test/isolation2/sql/terminate_in_gang_creation.sql new file mode 100644 index 00000000000..7d0ade7e15c --- /dev/null +++ b/contrib/pax_storage/src/test/isolation2/sql/terminate_in_gang_creation.sql @@ -0,0 +1,98 @@ +-- start_matchsubs +-- m/seg[0-9] [0-9.]+:\d+/ +-- s/seg[0-9] [0-9.]+:\d+/segN IP:PORT/ +-- m/lock \[\d+,\d+\]/ +-- s/lock \[\d+,\d+\]// +-- m/DETAIL: .* FATAL: reader could not find writer proc entry/ +-- s/DETAIL: .* FATAL: reader could not find writer proc entry/DETAIL: FATAL: reader could not find writer proc entry/ +-- end_matchsubs + +-- skip dtx recovery check to avoid hitting the fault create_gang_in_progress. +SELECT gp_inject_fault_infinite('before_orphaned_check', 'skip', dbid) + FROM gp_segment_configuration WHERE role='p' AND content=-1; +ALTER SYSTEM SET gp_dtx_recovery_interval to 5; +SELECT pg_reload_conf(); +SELECT gp_wait_until_triggered_fault('before_orphaned_check', 1, dbid) + FROM gp_segment_configuration WHERE role='p' AND content=-1; + +-- SIGSEGV issue when freeing gangs +-- +-- When SIGTERM is handled during gang creation we used to trigger +-- a wild pointer access like below backtrace: +-- +-- #0 raise +-- #1 StandardHandlerForSigillSigsegvSigbus_OnMainThread +-- #2 <signal handler called> +-- #3 MemoryContextFreeImpl +-- #4 cdbconn_termSegmentDescriptor +-- #5 DisconnectAndDestroyGang +-- #6 freeGangsForPortal +-- #7 AbortTransaction +-- ... +-- #14 ProcessInterrupts +-- #15 createGang_async +-- #16 createGang +-- #17 AllocateWriterGang + +DROP TABLE IF EXISTS foo; +CREATE TABLE foo (c1 int, c2 int) DISTRIBUTED BY (c1); + +10: BEGIN; + +SELECT gp_inject_fault('create_gang_in_progress', 'reset', 1); +SELECT gp_inject_fault('create_gang_in_progress', 'suspend', 1); + +10&: SELECT * FROM foo a JOIN foo b USING (c2); + +SELECT gp_wait_until_triggered_fault('create_gang_in_progress', 1, 1); + +SELECT pg_terminate_backend(pid) FROM pg_stat_activity + WHERE query = 'SELECT * FROM foo a JOIN foo b USING (c2);'; + +SELECT gp_inject_fault('create_gang_in_progress', 'resume', 1); + +10<: +10q: + +DROP TABLE foo; + +-- Test a bug that if cached idle primary QE is gone (e.g. after kill-9, pg_ctl +-- restart, etc), a new query needs a new created reader gang might fail with +-- error like this: +-- +-- ERROR: failed to acquire resources on one or more segments +-- DETAIL: FATAL: reader could not find writer proc entry +-- DETAIL: lock [0,1260] AccessShareLock 0. Probably because writer gang is gone somehow. Maybe try rerunning. +-- (seg2 127.0.0.1:7004) +-- +-- This is ok since the writer gang is gone, but previously QD code does +-- not reset all gangs (just retry creating the new reader gang) so re-running +-- this query could always fail with the same error since the reader gang would +-- always fail to create. The below test is used to test the fix. + +-- skip FTS probes to avoid segment being marked down on restart +SELECT gp_inject_fault_infinite('fts_probe', 'skip', dbid) + FROM gp_segment_configuration WHERE role='p' AND content=-1; +SELECT gp_request_fts_probe_scan(); +-- Prevent below pg_ctl restart timeout although the timeout should be enough. +CHECKPOINT; + +-- not recycle idle QEs to avoid the flaky test where restarting primary takes a long time. +11: SET gp_vmem_idle_resource_timeout TO 0; +11: CREATE TABLE foo (c1 int, c2 int) DISTRIBUTED BY (c1); +-- ORCA optimizes value scan so there is no additional reader gang in below INSERT. +11: SET optimizer = off; +-- the value scan (reader gang) might be on any segment so restart all segments. +SELECT pg_ctl(datadir, 'restart', 'immediate') + FROM gp_segment_configuration WHERE role='p' AND content != -1; +11: INSERT INTO foo values(2),(1); +11: INSERT INTO foo values(2),(1); +11: DROP TABLE foo; +11: RESET gp_vmem_idle_resource_timeout; + +SELECT gp_inject_fault('fts_probe', 'reset', dbid) + FROM gp_segment_configuration WHERE role='p' AND content=-1; +SELECT gp_inject_fault_infinite('before_orphaned_check', 'reset', dbid) + FROM gp_segment_configuration WHERE role='p' AND content=-1; +2: ALTER SYSTEM RESET gp_dtx_recovery_interval; +2: SELECT pg_reload_conf(); diff --git a/contrib/pax_storage/src/test/regress/expected/dbsize.out b/contrib/pax_storage/src/test/regress/expected/dbsize.out new file mode 100644 index 00000000000..97c3bf54be5 --- /dev/null +++ b/contrib/pax_storage/src/test/regress/expected/dbsize.out @@ -0,0 +1,203 @@ +SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM + (VALUES (10::bigint), (1000::bigint), (1000000::bigint), + (1000000000::bigint), (1000000000000::bigint), + (1000000000000000::bigint)) x(size); + size | pg_size_pretty | pg_size_pretty +------------------+----------------+---------------- + 10 | 10 bytes | -10 bytes + 1000 | 1000 bytes | -1000 bytes + 1000000 | 977 kB | -977 kB + 1000000000 | 954 MB | -954 MB + 1000000000000 | 931 GB | -931 GB + 1000000000000000 | 909 TB | -909 TB +(6 rows) + +SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM + (VALUES (10::numeric), (1000::numeric), (1000000::numeric), + (1000000000::numeric), (1000000000000::numeric), + (1000000000000000::numeric), + (10.5::numeric), (1000.5::numeric), (1000000.5::numeric), + (1000000000.5::numeric), (1000000000000.5::numeric), + (1000000000000000.5::numeric)) x(size); + size | pg_size_pretty | pg_size_pretty +--------------------+----------------+---------------- + 10 | 10 bytes | -10 bytes + 1000 | 1000 bytes | -1000 bytes + 1000000 | 977 kB | -977 kB + 1000000000 | 954 MB | -954 MB + 1000000000000 | 931 GB | -931 GB + 1000000000000000 | 909 TB | -909 TB + 10.5 | 10.5 bytes | -10.5 bytes + 1000.5 | 1000.5 bytes | -1000.5 bytes + 1000000.5 | 977 kB | -977 kB + 1000000000.5 | 954 MB | -954 MB + 1000000000000.5 | 931 GB | -931 GB + 1000000000000000.5 | 909 TB | -909 TB +(12 rows) + +-- test where units change up +SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM + (VALUES (10239::bigint), (10240::bigint), + (10485247::bigint), (10485248::bigint), + (10736893951::bigint), (10736893952::bigint), + (10994579406847::bigint), (10994579406848::bigint), + (11258449312612351::bigint), (11258449312612352::bigint)) x(size); + size | pg_size_pretty | pg_size_pretty +-------------------+----------------+---------------- + 10239 | 10239 bytes | -10239 bytes + 10240 | 10 kB | -10 kB + 10485247 | 10239 kB | -10239 kB + 10485248 | 10 MB | -10 MB + 10736893951 | 10239 MB | -10239 MB + 10736893952 | 10 GB | -10 GB + 10994579406847 | 10239 GB | -10239 GB + 10994579406848 | 10 TB | -10 TB + 11258449312612351 | 10239 TB | -10239 TB + 11258449312612352 | 10 PB | -10 PB +(10 rows) + +SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM + (VALUES (10239::numeric), (10240::numeric), + (10485247::numeric), (10485248::numeric), + (10736893951::numeric), (10736893952::numeric), + (10994579406847::numeric), (10994579406848::numeric), + (11258449312612351::numeric), (11258449312612352::numeric), + (11528652096115048447::numeric), (11528652096115048448::numeric)) x(size); + size | pg_size_pretty | pg_size_pretty +----------------------+----------------+---------------- + 10239 | 10239 bytes | -10239 bytes + 10240 | 10 kB | -10 kB + 10485247 | 10239 kB | -10239 kB + 10485248 | 10 MB | -10 MB + 10736893951 | 10239 MB | -10239 MB + 10736893952 | 10 GB | -10 GB + 10994579406847 | 10239 GB | -10239 GB + 10994579406848 | 10 TB | -10 TB + 11258449312612351 | 10239 TB | -10239 TB + 11258449312612352 | 10 PB | -10 PB + 11528652096115048447 | 10239 PB | -10239 PB + 11528652096115048448 | 10240 PB | -10240 PB +(12 rows) + +-- Ensure we get the expected results when passing the extremities of bigint +SELECT pg_size_pretty('-9223372036854775808'::bigint), + pg_size_pretty('9223372036854775807'::bigint); + pg_size_pretty | pg_size_pretty +----------------+---------------- + -8192 PB | 8192 PB +(1 row) + +-- pg_size_bytes() tests +SELECT size, pg_size_bytes(size) FROM + (VALUES ('1'), ('123bytes'), ('256 B'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '), + ('1TB'), ('3000 TB'), ('1e6 MB'), ('99 PB')) x(size); + size | pg_size_bytes +----------+-------------------- + 1 | 1 + 123bytes | 123 + 256 B | 256 + 1kB | 1024 + 1MB | 1048576 + 1 GB | 1073741824 + 1.5 GB | 1610612736 + 1TB | 1099511627776 + 3000 TB | 3298534883328000 + 1e6 MB | 1048576000000 + 99 PB | 111464090777419776 +(11 rows) + +-- case-insensitive units are supported +SELECT size, pg_size_bytes(size) FROM + (VALUES ('1'), ('123bYteS'), ('1kb'), ('1mb'), (' 1 Gb'), ('1.5 gB '), + ('1tb'), ('3000 tb'), ('1e6 mb'), ('99 pb')) x(size); + size | pg_size_bytes +----------+-------------------- + 1 | 1 + 123bYteS | 123 + 1kb | 1024 + 1mb | 1048576 + 1 Gb | 1073741824 + 1.5 gB | 1610612736 + 1tb | 1099511627776 + 3000 tb | 3298534883328000 + 1e6 mb | 1048576000000 + 99 pb | 111464090777419776 +(10 rows) + +-- negative numbers are supported +SELECT size, pg_size_bytes(size) FROM + (VALUES ('-1'), ('-123bytes'), ('-1kb'), ('-1mb'), (' -1 Gb'), ('-1.5 gB '), + ('-1tb'), ('-3000 TB'), ('-10e-1 MB'), ('-99 PB')) x(size); + size | pg_size_bytes +-----------+--------------------- + -1 | -1 + -123bytes | -123 + -1kb | -1024 + -1mb | -1048576 + -1 Gb | -1073741824 + -1.5 gB | -1610612736 + -1tb | -1099511627776 + -3000 TB | -3298534883328000 + -10e-1 MB | -1048576 + -99 PB | -111464090777419776 +(10 rows) + +-- different cases with allowed points +SELECT size, pg_size_bytes(size) FROM + (VALUES ('-1.'), ('-1.kb'), ('-1. kb'), ('-0. gb'), + ('-.1'), ('-.1kb'), ('-.1 kb'), ('-.0 gb')) x(size); + size | pg_size_bytes +--------+--------------- + -1. | -1 + -1.kb | -1024 + -1. kb | -1024 + -0. gb | 0 + -.1 | 0 + -.1kb | -102 + -.1 kb | -102 + -.0 gb | 0 +(8 rows) + +-- invalid inputs +SELECT pg_size_bytes('1 AB'); +ERROR: invalid size: "1 AB" +DETAIL: Invalid size unit: "AB". +HINT: Valid units are "bytes", "B", "kB", "MB", "GB", "TB", and "PB". +SELECT pg_size_bytes('1 AB A'); +ERROR: invalid size: "1 AB A" +DETAIL: Invalid size unit: "AB A". +HINT: Valid units are "bytes", "B", "kB", "MB", "GB", "TB", and "PB". +SELECT pg_size_bytes('1 AB A '); +ERROR: invalid size: "1 AB A " +DETAIL: Invalid size unit: "AB A". +HINT: Valid units are "bytes", "B", "kB", "MB", "GB", "TB", and "PB". +SELECT pg_size_bytes('9223372036854775807.9'); +ERROR: bigint out of range +SELECT pg_size_bytes('1e100'); +ERROR: bigint out of range +SELECT pg_size_bytes('1e1000000000000000000'); +ERROR: value overflows numeric format +SELECT pg_size_bytes('1 byte'); -- the singular "byte" is not supported +ERROR: invalid size: "1 byte" +DETAIL: Invalid size unit: "byte". +HINT: Valid units are "bytes", "B", "kB", "MB", "GB", "TB", and "PB". +SELECT pg_size_bytes(''); +ERROR: invalid size: "" +SELECT pg_size_bytes('kb'); +ERROR: invalid size: "kb" +SELECT pg_size_bytes('..'); +ERROR: invalid size: ".." +SELECT pg_size_bytes('-.'); +ERROR: invalid size: "-." +SELECT pg_size_bytes('-.kb'); +ERROR: invalid size: "-.kb" +SELECT pg_size_bytes('-. kb'); +ERROR: invalid size: "-. kb" +SELECT pg_size_bytes('.+912'); +ERROR: invalid size: ".+912" +SELECT pg_size_bytes('+912+ kB'); +ERROR: invalid size: "+912+ kB" +DETAIL: Invalid size unit: "+ kB". +HINT: Valid units are "bytes", "B", "kB", "MB", "GB", "TB", and "PB". +SELECT pg_size_bytes('++123 kB'); +ERROR: invalid size: "++123 kB" diff --git a/contrib/pax_storage/src/test/regress/expected/drop_operator.out b/contrib/pax_storage/src/test/regress/expected/drop_operator.out new file mode 100644 index 00000000000..cc8f5e755ec --- /dev/null +++ b/contrib/pax_storage/src/test/regress/expected/drop_operator.out @@ -0,0 +1,61 @@ +CREATE OPERATOR === ( + PROCEDURE = int8eq, + LEFTARG = bigint, + RIGHTARG = bigint, + COMMUTATOR = === +); +CREATE OPERATOR !== ( + PROCEDURE = int8ne, + LEFTARG = bigint, + RIGHTARG = bigint, + NEGATOR = ===, + COMMUTATOR = !== +); +DROP OPERATOR !==(bigint, bigint); +SELECT ctid, oprcom +FROM pg_catalog.pg_operator fk +WHERE oprcom != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.oprcom); + ctid | oprcom +------+-------- +(0 rows) + +SELECT ctid, oprnegate +FROM pg_catalog.pg_operator fk +WHERE oprnegate != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.oprnegate); + ctid | oprnegate +------+----------- +(0 rows) + +DROP OPERATOR ===(bigint, bigint); +CREATE OPERATOR <| ( + PROCEDURE = int8lt, + LEFTARG = bigint, + RIGHTARG = bigint +); +CREATE OPERATOR |> ( + PROCEDURE = int8gt, + LEFTARG = bigint, + RIGHTARG = bigint, + NEGATOR = <|, + COMMUTATOR = <| +); +DROP OPERATOR |>(bigint, bigint); +SELECT ctid, oprcom +FROM pg_catalog.pg_operator fk +WHERE oprcom != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.oprcom); + ctid | oprcom +------+-------- +(0 rows) + +SELECT ctid, oprnegate +FROM pg_catalog.pg_operator fk +WHERE oprnegate != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.oprnegate); + ctid | oprnegate +------+----------- +(0 rows) + +DROP OPERATOR <|(bigint, bigint); diff --git a/contrib/pax_storage/src/test/regress/expected/gp_foreign_data.out b/contrib/pax_storage/src/test/regress/expected/gp_foreign_data.out new file mode 100644 index 00000000000..ed2680dc3f5 --- /dev/null +++ b/contrib/pax_storage/src/test/regress/expected/gp_foreign_data.out @@ -0,0 +1,84 @@ +-- +-- Test foreign-data wrapper and server management. Cloudberry MPP specific +-- +CREATE FOREIGN DATA WRAPPER dummy OPTIONS (mpp_execute 'coordinator');; +COMMENT ON FOREIGN DATA WRAPPER dummy IS 'useless'; +ALTER FOREIGN DATA WRAPPER dummy OPTIONS (SET mpp_execute 'all segments'); +ERROR: "mpp_execute" of foreign data wrapper is not allowed to be altered +-- CREATE FOREIGN TABLE +CREATE SERVER s0 FOREIGN DATA WRAPPER dummy; +CREATE FOREIGN TABLE ft2 ( + c1 int +) SERVER s0 OPTIONS (delimiter ',', mpp_execute 'a'); -- ERROR +ERROR: "a" is not a valid mpp_execute value +CREATE FOREIGN TABLE ft2 ( + c1 int +) SERVER s0 OPTIONS (delimiter ',', mpp_execute 'any'); +\d+ ft2 + Foreign table "public.ft2" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+---------+--------------+------------- + c1 | integer | | | | | plain | | +Server: s0 +FDW options: (delimiter ',', mpp_execute 'any') + +CREATE FOREIGN TABLE ft3 ( + c1 int +) SERVER s0 OPTIONS (delimiter ',', mpp_execute 'master'); +CREATE FOREIGN TABLE ft4 ( + c1 int +) SERVER s0 OPTIONS (delimiter ',', mpp_execute 'all segments'); +-- Test num_segments option +CREATE SERVER s1 FOREIGN DATA WRAPPER dummy OPTIONS (num_segments '3'); +CREATE FOREIGN TABLE ft5 ( + c1 int +) SERVER s1 OPTIONS (delimiter ',', mpp_execute 'all segments', num_segments '5'); +\d+ ft5 + Foreign table "public.ft5" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+---------+--------------+------------- + c1 | integer | | | | | plain | | +Server: s1 +FDW options: (delimiter ',', mpp_execute 'all segments', num_segments '5') + +-- CREATE FOREIGN TABLE LIKE +CREATE TABLE ft_source_table(a INT, b INT, c INT) DISTRIBUTED BY (b); +CREATE FOREIGN TABLE ft_like (LIKE ft_source_table) SERVER s0; +\d+ ft_like + Foreign table "public.ft_like" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+---------+--------------+------------- + a | integer | | | | | plain | | + b | integer | | | | | plain | | + c | integer | | | | | plain | | +Server: s0 + +-- shoule be null +SELECT * FROM gp_distribution_policy WHERE localoid = 'ft_like'::regclass::oid; + localoid | policytype | numsegments | distkey | distclass +----------+------------+-------------+---------+----------- +(0 rows) + +CREATE FOREIGN TABLE ft_like1 (LIKE ft_source_table) SERVER s0 +OPTIONS (delimiter ',', mpp_execute 'all segments', num_segments '3'); +\d+ ft_like1 + Foreign table "public.ft_like1" + Column | Type | Collation | Nullable | Default | FDW options | Storage | Stats target | Description +--------+---------+-----------+----------+---------+-------------+---------+--------------+------------- + a | integer | | | | | plain | | + b | integer | | | | | plain | | + c | integer | | | | | plain | | +Server: s0 +FDW options: (delimiter ',', mpp_execute 'all segments', num_segments '3') + +SELECT * FROM gp_distribution_policy WHERE localoid = 'ft_like1'::regclass::oid; + localoid | policytype | numsegments | distkey | distclass +----------+------------+-------------+---------+----------- +(0 rows) + +DROP TABLE ft_source_table; +DROP FOREIGN TABLE ft_like; +DROP FOREIGN TABLE ft_like1; +--start_ignore +DROP FOREIGN DATA WRAPPER dummy CASCADE; +--end_ignore diff --git a/contrib/pax_storage/src/test/regress/expected/init_privs.out b/contrib/pax_storage/src/test/regress/expected/init_privs.out new file mode 100644 index 00000000000..292b1a1035b --- /dev/null +++ b/contrib/pax_storage/src/test/regress/expected/init_privs.out @@ -0,0 +1,12 @@ +-- Test initial privileges +-- There should always be some initial privileges, set up by initdb +SELECT count(*) > 0 FROM pg_init_privs; + ?column? +---------- + t +(1 row) + +-- Intentionally include some non-initial privs for pg_dump to dump out +GRANT SELECT ON pg_proc TO CURRENT_USER; +GRANT SELECT (prosrc) ON pg_proc TO CURRENT_USER; +GRANT SELECT (rolname, rolsuper) ON pg_authid TO CURRENT_USER; diff --git a/contrib/pax_storage/src/test/regress/expected/oidjoins.out b/contrib/pax_storage/src/test/regress/expected/oidjoins.out new file mode 100644 index 00000000000..19094e111dc --- /dev/null +++ b/contrib/pax_storage/src/test/regress/expected/oidjoins.out @@ -0,0 +1,285 @@ +-- +-- Verify system catalog foreign key relationships +-- +DO $doblock$ +declare + fk record; + nkeys integer; + cmd text; + err record; +begin + for fk in select * from pg_get_catalog_foreign_keys() + loop + raise notice 'checking % % => % %', + fk.fktable, fk.fkcols, fk.pktable, fk.pkcols; + nkeys := array_length(fk.fkcols, 1); + cmd := 'SELECT ctid'; + for i in 1 .. nkeys loop + cmd := cmd || ', ' || quote_ident(fk.fkcols[i]); + end loop; + if fk.is_array then + cmd := cmd || ' FROM (SELECT ctid'; + for i in 1 .. nkeys-1 loop + cmd := cmd || ', ' || quote_ident(fk.fkcols[i]); + end loop; + cmd := cmd || ', unnest(' || quote_ident(fk.fkcols[nkeys]); + cmd := cmd || ') as ' || quote_ident(fk.fkcols[nkeys]); + cmd := cmd || ' FROM ' || fk.fktable::text || ') fk WHERE '; + else + cmd := cmd || ' FROM ' || fk.fktable::text || ' fk WHERE '; + end if; + if fk.is_opt then + for i in 1 .. nkeys loop + cmd := cmd || quote_ident(fk.fkcols[i]) || ' != 0 AND '; + end loop; + end if; + cmd := cmd || 'NOT EXISTS(SELECT 1 FROM ' || fk.pktable::text || ' pk WHERE '; + for i in 1 .. nkeys loop + if i > 1 then cmd := cmd || ' AND '; end if; + cmd := cmd || 'pk.' || quote_ident(fk.pkcols[i]); + cmd := cmd || ' = fk.' || quote_ident(fk.fkcols[i]); + end loop; + cmd := cmd || ')'; + -- raise notice 'cmd = %', cmd; + for err in execute cmd loop + raise warning 'FK VIOLATION IN %(%): %', fk.fktable, fk.fkcols, err; + end loop; + end loop; +end +$doblock$; +NOTICE: checking pg_proc {pronamespace} => pg_namespace {oid} +NOTICE: checking pg_proc {proowner} => pg_authid {oid} +NOTICE: checking pg_proc {prolang} => pg_language {oid} +NOTICE: checking pg_proc {provariadic} => pg_type {oid} +NOTICE: checking pg_proc {prosupport} => pg_proc {oid} +NOTICE: checking pg_proc {prorettype} => pg_type {oid} +NOTICE: checking pg_proc {proargtypes} => pg_type {oid} +NOTICE: checking pg_proc {proallargtypes} => pg_type {oid} +NOTICE: checking pg_proc {protrftypes} => pg_type {oid} +NOTICE: checking pg_type {typnamespace} => pg_namespace {oid} +NOTICE: checking pg_type {typowner} => pg_authid {oid} +NOTICE: checking pg_type {typrelid} => pg_class {oid} +NOTICE: checking pg_type {typsubscript} => pg_proc {oid} +NOTICE: checking pg_type {typelem} => pg_type {oid} +NOTICE: checking pg_type {typarray} => pg_type {oid} +NOTICE: checking pg_type {typinput} => pg_proc {oid} +NOTICE: checking pg_type {typoutput} => pg_proc {oid} +NOTICE: checking pg_type {typreceive} => pg_proc {oid} +NOTICE: checking pg_type {typsend} => pg_proc {oid} +NOTICE: checking pg_type {typmodin} => pg_proc {oid} +NOTICE: checking pg_type {typmodout} => pg_proc {oid} +NOTICE: checking pg_type {typanalyze} => pg_proc {oid} +NOTICE: checking pg_type {typbasetype} => pg_type {oid} +NOTICE: checking pg_type {typcollation} => pg_collation {oid} +NOTICE: checking pg_attribute {attrelid} => pg_class {oid} +NOTICE: checking pg_attribute {atttypid} => pg_type {oid} +NOTICE: checking pg_attribute {attcollation} => pg_collation {oid} +NOTICE: checking pg_class {relnamespace} => pg_namespace {oid} +NOTICE: checking pg_class {reltype} => pg_type {oid} +NOTICE: checking pg_class {reloftype} => pg_type {oid} +NOTICE: checking pg_class {relowner} => pg_authid {oid} +NOTICE: checking pg_class {relam} => pg_am {oid} +NOTICE: checking pg_class {reltablespace} => pg_tablespace {oid} +NOTICE: checking pg_class {reltoastrelid} => pg_class {oid} +NOTICE: checking pg_class {relrewrite} => pg_class {oid} +NOTICE: checking pg_attrdef {adrelid} => pg_class {oid} +NOTICE: checking pg_attrdef {adrelid,adnum} => pg_attribute {attrelid,attnum} +NOTICE: checking pg_constraint {connamespace} => pg_namespace {oid} +NOTICE: checking pg_constraint {conrelid} => pg_class {oid} +NOTICE: checking pg_constraint {contypid} => pg_type {oid} +NOTICE: checking pg_constraint {conindid} => pg_class {oid} +NOTICE: checking pg_constraint {conparentid} => pg_constraint {oid} +NOTICE: checking pg_constraint {confrelid} => pg_class {oid} +NOTICE: checking pg_constraint {conpfeqop} => pg_operator {oid} +NOTICE: checking pg_constraint {conppeqop} => pg_operator {oid} +NOTICE: checking pg_constraint {conffeqop} => pg_operator {oid} +NOTICE: checking pg_constraint {conexclop} => pg_operator {oid} +NOTICE: checking pg_constraint {conrelid,conkey} => pg_attribute {attrelid,attnum} +NOTICE: checking pg_constraint {confrelid,confkey} => pg_attribute {attrelid,attnum} +NOTICE: checking pg_inherits {inhrelid} => pg_class {oid} +NOTICE: checking pg_inherits {inhparent} => pg_class {oid} +NOTICE: checking pg_index {indexrelid} => pg_class {oid} +NOTICE: checking pg_index {indrelid} => pg_class {oid} +NOTICE: checking pg_index {indcollation} => pg_collation {oid} +NOTICE: checking pg_index {indclass} => pg_opclass {oid} +NOTICE: checking pg_index {indrelid,indkey} => pg_attribute {attrelid,attnum} +NOTICE: checking pg_operator {oprnamespace} => pg_namespace {oid} +NOTICE: checking pg_operator {oprowner} => pg_authid {oid} +NOTICE: checking pg_operator {oprleft} => pg_type {oid} +NOTICE: checking pg_operator {oprright} => pg_type {oid} +NOTICE: checking pg_operator {oprresult} => pg_type {oid} +NOTICE: checking pg_operator {oprcom} => pg_operator {oid} +NOTICE: checking pg_operator {oprnegate} => pg_operator {oid} +NOTICE: checking pg_operator {oprcode} => pg_proc {oid} +NOTICE: checking pg_operator {oprrest} => pg_proc {oid} +NOTICE: checking pg_operator {oprjoin} => pg_proc {oid} +NOTICE: checking pg_opfamily {opfmethod} => pg_am {oid} +NOTICE: checking pg_opfamily {opfnamespace} => pg_namespace {oid} +NOTICE: checking pg_opfamily {opfowner} => pg_authid {oid} +NOTICE: checking pg_opclass {opcmethod} => pg_am {oid} +NOTICE: checking pg_opclass {opcnamespace} => pg_namespace {oid} +NOTICE: checking pg_opclass {opcowner} => pg_authid {oid} +NOTICE: checking pg_opclass {opcfamily} => pg_opfamily {oid} +NOTICE: checking pg_opclass {opcintype} => pg_type {oid} +NOTICE: checking pg_opclass {opckeytype} => pg_type {oid} +NOTICE: checking pg_am {amhandler} => pg_proc {oid} +NOTICE: checking pg_amop {amopfamily} => pg_opfamily {oid} +NOTICE: checking pg_amop {amoplefttype} => pg_type {oid} +NOTICE: checking pg_amop {amoprighttype} => pg_type {oid} +NOTICE: checking pg_amop {amopopr} => pg_operator {oid} +NOTICE: checking pg_amop {amopmethod} => pg_am {oid} +NOTICE: checking pg_amop {amopsortfamily} => pg_opfamily {oid} +NOTICE: checking pg_amproc {amprocfamily} => pg_opfamily {oid} +NOTICE: checking pg_amproc {amproclefttype} => pg_type {oid} +NOTICE: checking pg_amproc {amprocrighttype} => pg_type {oid} +NOTICE: checking pg_amproc {amproc} => pg_proc {oid} +NOTICE: checking pg_language {lanowner} => pg_authid {oid} +NOTICE: checking pg_language {lanplcallfoid} => pg_proc {oid} +NOTICE: checking pg_language {laninline} => pg_proc {oid} +NOTICE: checking pg_language {lanvalidator} => pg_proc {oid} +NOTICE: checking pg_largeobject_metadata {lomowner} => pg_authid {oid} +NOTICE: checking pg_largeobject {loid} => pg_largeobject_metadata {oid} +NOTICE: checking pg_aggregate {aggfnoid} => pg_proc {oid} +NOTICE: checking pg_aggregate {aggtransfn} => pg_proc {oid} +NOTICE: checking pg_aggregate {aggfinalfn} => pg_proc {oid} +NOTICE: checking gp_storage_server {srvowner} => pg_authid {oid} +NOTICE: checking gp_storage_user_mapping {umserver} => gp_storage_server {oid} +NOTICE: checking gp_storage_user_mapping {umuser} => pg_authid {oid} +NOTICE: checking pg_aggregate {aggcombinefn} => pg_proc {oid} +NOTICE: checking pg_aggregate {aggserialfn} => pg_proc {oid} +NOTICE: checking pg_aggregate {aggdeserialfn} => pg_proc {oid} +NOTICE: checking pg_aggregate {aggmtransfn} => pg_proc {oid} +NOTICE: checking pg_aggregate {aggminvtransfn} => pg_proc {oid} +NOTICE: checking pg_aggregate {aggmfinalfn} => pg_proc {oid} +NOTICE: checking pg_aggregate {aggsortop} => pg_operator {oid} +NOTICE: checking pg_aggregate {aggtranstype} => pg_type {oid} +NOTICE: checking pg_aggregate {aggmtranstype} => pg_type {oid} +NOTICE: checking pg_statistic {starelid} => pg_class {oid} +NOTICE: checking pg_statistic {staop1} => pg_operator {oid} +NOTICE: checking pg_statistic {staop2} => pg_operator {oid} +NOTICE: checking pg_statistic {staop3} => pg_operator {oid} +NOTICE: checking pg_statistic {staop4} => pg_operator {oid} +NOTICE: checking pg_statistic {staop5} => pg_operator {oid} +NOTICE: checking pg_statistic {stacoll1} => pg_collation {oid} +NOTICE: checking pg_statistic {stacoll2} => pg_collation {oid} +NOTICE: checking pg_statistic {stacoll3} => pg_collation {oid} +NOTICE: checking pg_statistic {stacoll4} => pg_collation {oid} +NOTICE: checking pg_statistic {stacoll5} => pg_collation {oid} +NOTICE: checking pg_statistic {starelid,staattnum} => pg_attribute {attrelid,attnum} +NOTICE: checking pg_statistic_ext {stxrelid} => pg_class {oid} +NOTICE: checking pg_statistic_ext {stxnamespace} => pg_namespace {oid} +NOTICE: checking pg_statistic_ext {stxowner} => pg_authid {oid} +NOTICE: checking pg_statistic_ext {stxrelid,stxkeys} => pg_attribute {attrelid,attnum} +NOTICE: checking pg_statistic_ext_data {stxoid} => pg_statistic_ext {oid} +NOTICE: checking pg_rewrite {ev_class} => pg_class {oid} +NOTICE: checking pg_trigger {tgrelid} => pg_class {oid} +NOTICE: checking pg_trigger {tgparentid} => pg_trigger {oid} +NOTICE: checking pg_trigger {tgfoid} => pg_proc {oid} +NOTICE: checking pg_trigger {tgconstrrelid} => pg_class {oid} +NOTICE: checking pg_trigger {tgconstrindid} => pg_class {oid} +NOTICE: checking pg_trigger {tgconstraint} => pg_constraint {oid} +NOTICE: checking pg_trigger {tgrelid,tgattr} => pg_attribute {attrelid,attnum} +NOTICE: checking pg_event_trigger {evtowner} => pg_authid {oid} +NOTICE: checking pg_event_trigger {evtfoid} => pg_proc {oid} +NOTICE: checking pg_description {classoid} => pg_class {oid} +NOTICE: checking pg_directory_table {dtrelid} => pg_class {oid} +NOTICE: checking pg_directory_table {dttablespace} => pg_tablespace {oid} +NOTICE: checking pg_cast {castsource} => pg_type {oid} +NOTICE: checking pg_cast {casttarget} => pg_type {oid} +NOTICE: checking pg_cast {castfunc} => pg_proc {oid} +NOTICE: checking pg_enum {enumtypid} => pg_type {oid} +NOTICE: checking pg_namespace {nspowner} => pg_authid {oid} +NOTICE: checking pg_conversion {connamespace} => pg_namespace {oid} +NOTICE: checking pg_conversion {conowner} => pg_authid {oid} +NOTICE: checking pg_conversion {conproc} => pg_proc {oid} +NOTICE: checking pg_depend {classid} => pg_class {oid} +NOTICE: checking pg_depend {refclassid} => pg_class {oid} +NOTICE: checking pg_database {datdba} => pg_authid {oid} +NOTICE: checking pg_database {dattablespace} => pg_tablespace {oid} +NOTICE: checking pg_db_role_setting {setdatabase} => pg_database {oid} +NOTICE: checking pg_db_role_setting {setrole} => pg_authid {oid} +NOTICE: checking pg_tablespace {spcowner} => pg_authid {oid} +NOTICE: checking pg_auth_members {roleid} => pg_authid {oid} +NOTICE: checking pg_auth_members {member} => pg_authid {oid} +NOTICE: checking pg_auth_members {grantor} => pg_authid {oid} +NOTICE: checking pg_tag {tagowner} => pg_authid {oid} +NOTICE: checking pg_tag_description {tdclassid} => pg_class {oid} +NOTICE: checking pg_tag_description {tddatabaseid} => pg_database {oid} +NOTICE: checking pg_tag_description {tagid} => pg_tag {oid} +NOTICE: checking pg_shdepend {dbid} => pg_database {oid} +NOTICE: checking pg_shdepend {classid} => pg_class {oid} +NOTICE: checking pg_shdepend {refclassid} => pg_class {oid} +NOTICE: checking pg_shdescription {classoid} => pg_class {oid} +NOTICE: checking pg_ts_config {cfgnamespace} => pg_namespace {oid} +NOTICE: checking pg_ts_config {cfgowner} => pg_authid {oid} +NOTICE: checking pg_ts_config {cfgparser} => pg_ts_parser {oid} +NOTICE: checking pg_ts_config_map {mapcfg} => pg_ts_config {oid} +NOTICE: checking pg_ts_config_map {mapdict} => pg_ts_dict {oid} +NOTICE: checking pg_ts_dict {dictnamespace} => pg_namespace {oid} +NOTICE: checking pg_ts_dict {dictowner} => pg_authid {oid} +NOTICE: checking pg_ts_dict {dicttemplate} => pg_ts_template {oid} +NOTICE: checking pg_ts_parser {prsnamespace} => pg_namespace {oid} +NOTICE: checking pg_ts_parser {prsstart} => pg_proc {oid} +NOTICE: checking pg_ts_parser {prstoken} => pg_proc {oid} +NOTICE: checking pg_ts_parser {prsend} => pg_proc {oid} +NOTICE: checking pg_ts_parser {prsheadline} => pg_proc {oid} +NOTICE: checking pg_ts_parser {prslextype} => pg_proc {oid} +NOTICE: checking pg_ts_template {tmplnamespace} => pg_namespace {oid} +NOTICE: checking pg_ts_template {tmplinit} => pg_proc {oid} +NOTICE: checking pg_ts_template {tmpllexize} => pg_proc {oid} +NOTICE: checking pg_extension {extowner} => pg_authid {oid} +NOTICE: checking pg_extension {extnamespace} => pg_namespace {oid} +NOTICE: checking pg_extension {extconfig} => pg_class {oid} +NOTICE: checking pg_foreign_data_wrapper {fdwowner} => pg_authid {oid} +NOTICE: checking pg_foreign_data_wrapper {fdwhandler} => pg_proc {oid} +NOTICE: checking pg_foreign_data_wrapper {fdwvalidator} => pg_proc {oid} +NOTICE: checking pg_foreign_server {srvowner} => pg_authid {oid} +NOTICE: checking pg_foreign_server {srvfdw} => pg_foreign_data_wrapper {oid} +NOTICE: checking pg_user_mapping {umuser} => pg_authid {oid} +NOTICE: checking pg_user_mapping {umserver} => pg_foreign_server {oid} +NOTICE: checking pg_compression {compconstructor} => pg_proc {oid} +NOTICE: checking pg_compression {compdestructor} => pg_proc {oid} +NOTICE: checking pg_compression {compcompressor} => pg_proc {oid} +NOTICE: checking pg_compression {compdecompressor} => pg_proc {oid} +NOTICE: checking pg_compression {compvalidator} => pg_proc {oid} +NOTICE: checking pg_compression {compowner} => pg_authid {oid} +NOTICE: checking pg_foreign_table {ftrelid} => pg_class {oid} +NOTICE: checking pg_foreign_table {ftserver} => pg_foreign_server {oid} +NOTICE: checking pg_foreign_table_seg {ftsrelid} => pg_class {oid} +NOTICE: checking pg_foreign_table_seg {ftsserver} => pg_foreign_server {oid} +NOTICE: checking pg_policy {polrelid} => pg_class {oid} +NOTICE: checking pg_policy {polroles} => pg_authid {oid} +NOTICE: checking pg_default_acl {defaclrole} => pg_authid {oid} +NOTICE: checking pg_default_acl {defaclnamespace} => pg_namespace {oid} +NOTICE: checking pg_init_privs {classoid} => pg_class {oid} +NOTICE: checking pg_seclabel {classoid} => pg_class {oid} +NOTICE: checking pg_shseclabel {classoid} => pg_class {oid} +NOTICE: checking pg_collation {collnamespace} => pg_namespace {oid} +NOTICE: checking pg_collation {collowner} => pg_authid {oid} +NOTICE: checking pg_partitioned_table {partrelid} => pg_class {oid} +NOTICE: checking pg_partitioned_table {partdefid} => pg_class {oid} +NOTICE: checking pg_partitioned_table {partclass} => pg_opclass {oid} +NOTICE: checking pg_partitioned_table {partcollation} => pg_collation {oid} +NOTICE: checking pg_partitioned_table {partrelid,partattrs} => pg_attribute {attrelid,attnum} +NOTICE: checking pg_range {rngtypid} => pg_type {oid} +NOTICE: checking pg_range {rngsubtype} => pg_type {oid} +NOTICE: checking pg_range {rngmultitypid} => pg_type {oid} +NOTICE: checking pg_range {rngcollation} => pg_collation {oid} +NOTICE: checking pg_range {rngsubopc} => pg_opclass {oid} +NOTICE: checking pg_range {rngcanonical} => pg_proc {oid} +NOTICE: checking pg_range {rngsubdiff} => pg_proc {oid} +NOTICE: checking pg_transform {trftype} => pg_type {oid} +NOTICE: checking pg_transform {trflang} => pg_language {oid} +NOTICE: checking pg_transform {trffromsql} => pg_proc {oid} +NOTICE: checking pg_transform {trftosql} => pg_proc {oid} +NOTICE: checking pg_sequence {seqrelid} => pg_class {oid} +NOTICE: checking pg_sequence {seqtypid} => pg_type {oid} +NOTICE: checking pg_publication {pubowner} => pg_authid {oid} +NOTICE: checking pg_publication_namespace {pnpubid} => pg_publication {oid} +NOTICE: checking pg_publication_namespace {pnnspid} => pg_namespace {oid} +NOTICE: checking pg_publication_rel {prpubid} => pg_publication {oid} +NOTICE: checking pg_publication_rel {prrelid} => pg_class {oid} +NOTICE: checking pg_subscription {subdbid} => pg_database {oid} +NOTICE: checking pg_subscription {subowner} => pg_authid {oid} +NOTICE: checking pg_subscription_rel {srsubid} => pg_subscription {oid} +NOTICE: checking pg_subscription_rel {srrelid} => pg_class {oid} diff --git a/contrib/pax_storage/src/test/regress/expected/password.out b/contrib/pax_storage/src/test/regress/expected/password.out new file mode 100644 index 00000000000..84752317356 --- /dev/null +++ b/contrib/pax_storage/src/test/regress/expected/password.out @@ -0,0 +1,149 @@ +-- +-- Tests for password types +-- +-- Tests for GUC password_encryption +SET password_encryption = 'novalue'; -- error +ERROR: invalid value for parameter "password_encryption": "novalue" +HINT: Available values: md5, scram-sha-256. +SET password_encryption = true; -- error +ERROR: invalid value for parameter "password_encryption": "true" +HINT: Available values: md5, scram-sha-256. +SET password_encryption = 'md5'; -- ok +SET password_encryption = 'scram-sha-256'; -- ok +-- consistency of password entries +SET password_encryption = 'md5'; +CREATE ROLE regress_passwd1 PASSWORD 'role_pwd1'; +CREATE ROLE regress_passwd2 PASSWORD 'role_pwd2'; +SET password_encryption = 'scram-sha-256'; +CREATE ROLE regress_passwd3 PASSWORD 'role_pwd3'; +CREATE ROLE regress_passwd4 PASSWORD NULL; +-- check list of created entries +-- +-- The scram secret will look something like: +-- SCRAM-SHA-256$4096:E4HxLGtnRzsYwg==$6YtlR4t69SguDiwFvbVgVZtuz6gpJQQqUMZ7IQJK5yI=:ps75jrHeYU4lXCcXI4O8oIdJ3eO8o2jirjruw9phBTo= +-- +-- Since the salt is random, the exact value stored will be different on every test +-- run. Use a regular expression to mask the changing parts. +SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked + FROM pg_authid + WHERE rolname LIKE 'regress_passwd%' + ORDER BY rolname, rolpassword; + rolname | rolpassword_masked +-----------------+--------------------------------------------------- + regress_passwd1 | md5783277baca28003b33453252be4dbb34 + regress_passwd2 | md54044304ba511dd062133eb5b4b84a2a3 + regress_passwd3 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> + regress_passwd4 | +(4 rows) + +-- Rename a role +ALTER ROLE regress_passwd2 RENAME TO regress_passwd2_new; +NOTICE: MD5 password cleared because of role rename +-- md5 entry should have been removed +SELECT rolname, rolpassword + FROM pg_authid + WHERE rolname LIKE 'regress_passwd2_new' + ORDER BY rolname, rolpassword; + rolname | rolpassword +---------------------+------------- + regress_passwd2_new | +(1 row) + +ALTER ROLE regress_passwd2_new RENAME TO regress_passwd2; +-- Change passwords with ALTER USER. With plaintext or already-encrypted +-- passwords. +SET password_encryption = 'md5'; +-- encrypt with MD5 +ALTER ROLE regress_passwd2 PASSWORD 'foo'; +-- already encrypted, use as they are +ALTER ROLE regress_passwd1 PASSWORD 'md5cd3578025fe2c3d7ed1b9a9b26238b70'; +ALTER ROLE regress_passwd3 PASSWORD 'SCRAM-SHA-256$4096:VLK4RMaQLCvNtQ==$6YtlR4t69SguDiwFvbVgVZtuz6gpJQQqUMZ7IQJK5yI=:ps75jrHeYU4lXCcXI4O8oIdJ3eO8o2jirjruw9phBTo='; +SET password_encryption = 'scram-sha-256'; +-- create SCRAM secret +ALTER ROLE regress_passwd4 PASSWORD 'foo'; +-- already encrypted with MD5, use as it is +CREATE ROLE regress_passwd5 PASSWORD 'md5e73a4b11df52a6068f8b39f90be36023'; +-- This looks like a valid SCRAM-SHA-256 secret, but it is not +-- so it should be hashed with SCRAM-SHA-256. +CREATE ROLE regress_passwd6 PASSWORD 'SCRAM-SHA-256$1234'; +-- These may look like valid MD5 secrets, but they are not, so they +-- should be hashed with SCRAM-SHA-256. +-- trailing garbage at the end +CREATE ROLE regress_passwd7 PASSWORD 'md5012345678901234567890123456789zz'; +-- invalid length +CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz'; +-- Changing the SCRAM iteration count +SET scram_iterations = 1024; +CREATE ROLE regress_passwd9 PASSWORD 'alterediterationcount'; +SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked + FROM pg_authid + WHERE rolname LIKE 'regress_passwd%' + ORDER BY rolname, rolpassword; + rolname | rolpassword_masked +-----------------+--------------------------------------------------- + regress_passwd1 | md5cd3578025fe2c3d7ed1b9a9b26238b70 + regress_passwd2 | md5dfa155cadd5f4ad57860162f3fab9cdb + regress_passwd3 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> + regress_passwd4 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> + regress_passwd5 | md5e73a4b11df52a6068f8b39f90be36023 + regress_passwd6 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> + regress_passwd7 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> + regress_passwd8 | SCRAM-SHA-256$4096:<salt>$<storedkey>:<serverkey> + regress_passwd9 | SCRAM-SHA-256$1024:<salt>$<storedkey>:<serverkey> +(9 rows) + +-- An empty password is not allowed, in any form +CREATE ROLE regress_passwd_empty PASSWORD ''; +NOTICE: empty string is not a valid password, clearing password +ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659e0a'; +NOTICE: empty string is not a valid password, clearing password +ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4='; +NOTICE: empty string is not a valid password, clearing password +SELECT rolpassword FROM pg_authid WHERE rolname='regress_passwd_empty'; + rolpassword +------------- + +(1 row) + +-- Test with invalid stored and server keys. +-- +-- The first is valid, to act as a control. The others have too long +-- stored/server keys. They will be re-hashed. +CREATE ROLE regress_passwd_sha_len0 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZI='; +CREATE ROLE regress_passwd_sha_len1 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96RqwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZI='; +CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA='; +-- Check that the invalid secrets were re-hashed. A re-hashed secret +-- should not contain the original salt. +SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed + FROM pg_authid + WHERE rolname LIKE 'regress_passwd_sha_len%' + ORDER BY rolname; + rolname | is_rolpassword_rehashed +-------------------------+------------------------- + regress_passwd_sha_len0 | f + regress_passwd_sha_len1 | t + regress_passwd_sha_len2 | t +(3 rows) + +DROP ROLE regress_passwd1; +DROP ROLE regress_passwd2; +DROP ROLE regress_passwd3; +DROP ROLE regress_passwd4; +DROP ROLE regress_passwd5; +DROP ROLE regress_passwd6; +DROP ROLE regress_passwd7; +DROP ROLE regress_passwd8; +DROP ROLE regress_passwd9; +DROP ROLE regress_passwd_empty; +DROP ROLE regress_passwd_sha_len0; +DROP ROLE regress_passwd_sha_len1; +DROP ROLE regress_passwd_sha_len2; +-- all entries should have been removed +SELECT rolname, rolpassword + FROM pg_authid + WHERE rolname LIKE 'regress_passwd%' + ORDER BY rolname, rolpassword; + rolname | rolpassword +---------+------------- +(0 rows) + diff --git a/contrib/pax_storage/src/test/regress/expected/roleattributes.out b/contrib/pax_storage/src/test/regress/expected/roleattributes.out new file mode 100644 index 00000000000..5e6969b173e --- /dev/null +++ b/contrib/pax_storage/src/test/regress/expected/roleattributes.out @@ -0,0 +1,249 @@ +-- default for superuser is false +CREATE ROLE regress_test_def_superuser; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_def_superuser | f | t | f | f | f | f | f | -1 | | +(1 row) + +CREATE ROLE regress_test_superuser WITH SUPERUSER; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_superuser | t | t | f | f | f | f | f | -1 | | +(1 row) + +ALTER ROLE regress_test_superuser WITH NOSUPERUSER; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_superuser | f | t | f | f | f | f | f | -1 | | +(1 row) + +ALTER ROLE regress_test_superuser WITH SUPERUSER; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_superuser | t | t | f | f | f | f | f | -1 | | +(1 row) + +-- default for inherit is true +CREATE ROLE regress_test_def_inherit; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_def_inherit | f | t | f | f | f | f | f | -1 | | +(1 row) + +CREATE ROLE regress_test_inherit WITH NOINHERIT; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_inherit | f | f | f | f | f | f | f | -1 | | +(1 row) + +ALTER ROLE regress_test_inherit WITH INHERIT; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_inherit | f | t | f | f | f | f | f | -1 | | +(1 row) + +ALTER ROLE regress_test_inherit WITH NOINHERIT; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_inherit | f | f | f | f | f | f | f | -1 | | +(1 row) + +-- default for create role is false +CREATE ROLE regress_test_def_createrole; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_def_createrole | f | t | f | f | f | f | f | -1 | | +(1 row) + +CREATE ROLE regress_test_createrole WITH CREATEROLE; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_createrole | f | t | t | f | f | f | f | -1 | | +(1 row) + +ALTER ROLE regress_test_createrole WITH NOCREATEROLE; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_createrole | f | t | f | f | f | f | f | -1 | | +(1 row) + +ALTER ROLE regress_test_createrole WITH CREATEROLE; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_createrole | f | t | t | f | f | f | f | -1 | | +(1 row) + +-- default for create database is false +CREATE ROLE regress_test_def_createdb; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_def_createdb | f | t | f | f | f | f | f | -1 | | +(1 row) + +CREATE ROLE regress_test_createdb WITH CREATEDB; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_createdb | f | t | f | t | f | f | f | -1 | | +(1 row) + +ALTER ROLE regress_test_createdb WITH NOCREATEDB; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_createdb | f | t | f | f | f | f | f | -1 | | +(1 row) + +ALTER ROLE regress_test_createdb WITH CREATEDB; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_createdb | f | t | f | t | f | f | f | -1 | | +(1 row) + +-- default for can login is false for role +CREATE ROLE regress_test_def_role_canlogin; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +--------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_def_role_canlogin | f | t | f | f | f | f | f | -1 | | +(1 row) + +CREATE ROLE regress_test_role_canlogin WITH LOGIN; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_role_canlogin | f | t | f | f | t | f | f | -1 | | +(1 row) + +ALTER ROLE regress_test_role_canlogin WITH NOLOGIN; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_role_canlogin | f | t | f | f | f | f | f | -1 | | +(1 row) + +ALTER ROLE regress_test_role_canlogin WITH LOGIN; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_role_canlogin | f | t | f | f | t | f | f | -1 | | +(1 row) + +-- default for can login is true for user +CREATE USER regress_test_def_user_canlogin; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +--------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_def_user_canlogin | f | t | f | f | t | f | f | -1 | | +(1 row) + +CREATE USER regress_test_user_canlogin WITH NOLOGIN; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_user_canlogin | f | t | f | f | f | f | f | -1 | | +(1 row) + +ALTER USER regress_test_user_canlogin WITH LOGIN; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_user_canlogin | f | t | f | f | t | f | f | -1 | | +(1 row) + +ALTER USER regress_test_user_canlogin WITH NOLOGIN; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_user_canlogin | f | t | f | f | f | f | f | -1 | | +(1 row) + +-- default for replication is false +CREATE ROLE regress_test_def_replication; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_def_replication | f | t | f | f | f | f | f | -1 | | +(1 row) + +CREATE ROLE regress_test_replication WITH REPLICATION; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_replication | f | t | f | f | f | t | f | -1 | | +(1 row) + +ALTER ROLE regress_test_replication WITH NOREPLICATION; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_replication | f | t | f | f | f | f | f | -1 | | +(1 row) + +ALTER ROLE regress_test_replication WITH REPLICATION; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_replication | f | t | f | f | f | t | f | -1 | | +(1 row) + +-- default for bypassrls is false +CREATE ROLE regress_test_def_bypassrls; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_def_bypassrls | f | t | f | f | f | f | f | -1 | | +(1 row) + +CREATE ROLE regress_test_bypassrls WITH BYPASSRLS; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_bypassrls | f | t | f | f | f | f | t | -1 | | +(1 row) + +ALTER ROLE regress_test_bypassrls WITH NOBYPASSRLS; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_bypassrls | f | t | f | f | f | f | f | -1 | | +(1 row) + +ALTER ROLE regress_test_bypassrls WITH BYPASSRLS; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; + rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil +------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+--------------- + regress_test_bypassrls | f | t | f | f | f | f | t | -1 | | +(1 row) + +-- clean up roles +DROP ROLE regress_test_def_superuser; +DROP ROLE regress_test_superuser; +DROP ROLE regress_test_def_inherit; +DROP ROLE regress_test_inherit; +DROP ROLE regress_test_def_createrole; +DROP ROLE regress_test_createrole; +DROP ROLE regress_test_def_createdb; +DROP ROLE regress_test_createdb; +DROP ROLE regress_test_def_role_canlogin; +DROP ROLE regress_test_role_canlogin; +DROP USER regress_test_def_user_canlogin; +DROP USER regress_test_user_canlogin; +DROP ROLE regress_test_def_replication; +DROP ROLE regress_test_replication; +DROP ROLE regress_test_def_bypassrls; +DROP ROLE regress_test_bypassrls; diff --git a/contrib/pax_storage/src/test/regress/expected/sysviews.out b/contrib/pax_storage/src/test/regress/expected/sysviews.out new file mode 100644 index 00000000000..df070ca4db2 --- /dev/null +++ b/contrib/pax_storage/src/test/regress/expected/sysviews.out @@ -0,0 +1,178 @@ +-- +-- Test assorted system views +-- +-- This test is mainly meant to provide some code coverage for the +-- set-returning functions that underlie certain system views. +-- The output of most of these functions is very environment-dependent, +-- so our ability to test with fixed expected output is pretty limited; +-- but even a trivial check of count(*) will exercise the normal code path +-- through the SRF. +select count(*) >= 0 as ok from pg_available_extension_versions; + ok +---- + t +(1 row) + +select count(*) >= 0 as ok from pg_available_extensions; + ok +---- + t +(1 row) + +-- The entire output of pg_backend_memory_contexts is not stable, +-- we test only the existence and basic condition of TopMemoryContext. +select name, ident, parent, level, total_bytes >= free_bytes + from pg_backend_memory_contexts where level = 0; + name | ident | parent | level | ?column? +------------------+-------+--------+-------+---------- + TopMemoryContext | | | 0 | t +(1 row) + +-- At introduction, pg_config had 23 entries; it may grow +select count(*) > 20 as ok from pg_config; + ok +---- + t +(1 row) + +-- We expect no cursors in this test; see also portals.sql +select count(*) = 0 as ok from pg_cursors; + ok +---- + t +(1 row) + +select count(*) >= 0 as ok from pg_file_settings; + ok +---- + t +(1 row) + +-- There will surely be at least one rule, with no errors. +select count(*) > 0 as ok, count(*) FILTER (WHERE error IS NOT NULL) = 0 AS no_err + from pg_hba_file_rules; + ok | no_err +----+-------- + t | t +(1 row) + +-- There may be no rules, and there should be no errors. +select count(*) >= 0 as ok, count(*) FILTER (WHERE error IS NOT NULL) = 0 AS no_err + from pg_ident_file_mappings; + ok | no_err +----+-------- + t | t +(1 row) + +-- There will surely be at least one active lock +select count(*) > 0 as ok from pg_locks; + ok +---- + t +(1 row) + +-- We expect no prepared statements in this test; see also prepare.sql +select count(*) = 0 as ok from pg_prepared_statements; + ok +---- + t +(1 row) + +-- See also prepared_xacts.sql +select count(*) >= 0 as ok from pg_prepared_xacts; + ok +---- + t +(1 row) + +-- There will surely be at least one SLRU cache +select count(*) > 0 as ok from pg_stat_slru; + ok +---- + t +(1 row) + +-- There must be only one record +select count(*) = 1 as ok from pg_stat_wal; + ok +---- + t +(1 row) + +-- We expect no walreceiver running in this test +select count(*) = 0 as ok from pg_stat_wal_receiver; + ok +---- + t +(1 row) + +-- This is to record the prevailing planner enable_foo settings during +-- a regression test run. +-- GP parallel tests will run another with enable_parallel=on, filter this to pass regression. +SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%' and +name not in ('enable_parallel', + 'enable_answer_query_using_materialized_views', + 'enable_parallel_dedup_semi_join', + 'enable_parallel_dedup_semi_reverse_join', + 'enable_parallel_semi_join'); + name | setting +--------------------------------+--------- + enable_async_append | on + enable_bitmapscan | on + enable_gathermerge | on + enable_groupagg | on + enable_hashagg | on + enable_hashjoin | on + enable_incremental_sort | off + enable_indexonlyscan | on + enable_indexscan | on + enable_lock_optimization | off + enable_material | on + enable_memoize | on + enable_mergejoin | off + enable_nestloop | off + enable_parallel_append | on + enable_parallel_hash | on + enable_partition_pruning | on + enable_partitionwise_aggregate | off + enable_partitionwise_join | off + enable_password_profile | on + enable_presorted_aggregate | on + enable_seqscan | on + enable_sort | on + enable_tidscan | on +(24 rows) + +-- Test that the pg_timezone_names and pg_timezone_abbrevs views are +-- more-or-less working. We can't test their contents in any great detail +-- without the outputs changing anytime IANA updates the underlying data, +-- but it seems reasonable to expect at least one entry per major meridian. +-- (At the time of writing, the actual counts are around 38 because of +-- zones using fractional GMT offsets, so this is a pretty loose test.) +select count(distinct utc_offset) >= 24 as ok from pg_timezone_names; + ok +---- + t +(1 row) + +select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; + ok +---- + t +(1 row) + +-- Let's check the non-default timezone abbreviation sets, too +set timezone_abbreviations = 'Australia'; +select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; + ok +---- + t +(1 row) + +set timezone_abbreviations = 'India'; +select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; + ok +---- + t +(1 row) + diff --git a/contrib/pax_storage/src/test/regress/parallel_schedule b/contrib/pax_storage/src/test/regress/parallel_schedule index 77aadcaa8a8..a5b3f56b963 100644 --- a/contrib/pax_storage/src/test/regress/parallel_schedule +++ b/contrib/pax_storage/src/test/regress/parallel_schedule @@ -21,7 +21,7 @@ test: boolean char name varchar text int2 int4 int8 oid float4 float8 bit numeri # multirangetypes depends on rangetypes # multirangetypes shouldn't run concurrently with type_sanity # ---------- -test: strings numerology point lseg line box path polygon circle date time timetz timestamp timestamptz interval inet macaddr macaddr8 multirangetypes +test: strings numerology point lseg line box path polygon circle date time timetz timestamp timestamptz interval inet macaddr macaddr8 multirangetypes # ---------- # Another group of parallel tests @@ -55,9 +55,9 @@ test: copy copyselect copydml insert test: create_misc create_operator create_procedure # These depend on create_misc and create_operator -test: create_index create_view index_including +test: create_index create_view index_including # PAX not support spgist -# test: create_index_spgist +# test: create_index_spgist # ignore reason: Pax not support gist # test: index_including_gist @@ -66,7 +66,7 @@ test: create_index create_view index_including # Another group of parallel tests # ---------- -test: create_aggregate create_cast select inherit typed_table drop_if_exists create_am hash_func errors infinite_recurse +test: create_aggregate create_cast select inherit typed_table drop_if_exists roleattributes create_am hash_func errors infinite_recurse # ignore reason: # 1. Pax have not implements tuple_insert_speculative and tuple_lock @@ -109,17 +109,17 @@ test: transactions # ---------- # Another group of parallel tests # ---------- -test: privileges security_label collate lock replica_identity rowsecurity object_address tablesample groupingsets identity appendonly_sample aocs_sample +test: privileges init_privs security_label collate lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator password identity appendonly_sample aocs_sample test: matview -# ignore reason: +# ignore reason: # 1. Pax not support trigger # 2. Pax not support parallel_workers # 3. Pax not support brin -# test: generated join_hash brin +# test: generated join_hash brin # ignore reason: Pax not support gin gist spgist indexes -# test: gin gist spgist +# test: gin gist spgist test: gin # ---------- @@ -135,9 +135,9 @@ test: task # ---------- # Another group of parallel tests # ---------- -test: create_table_like alter_generic alter_operator misc async misc_functions tsrf tid collate.icu.utf8 incremental_sort +test: create_table_like alter_generic alter_operator misc async dbsize misc_functions sysviews tsrf tid collate.icu.utf8 incremental_sort # ignore reason: tidrangescan assumes the ctid is organized by heap -# test: tidrangescan +# test: tidrangescan # ignore reason: pax not support incremental matview # test: incremental_matview @@ -205,8 +205,9 @@ test: plancache limit copy2 temp domain rangefuncs prepare conversion truncate a # test: largeobject # ignore reason: Pax not support almost of triggers and have not implements tuple_fetch_row_version -# test: plpgsql returning with +# test: plpgsql returning with +test: gp_foreign_data # ---------- # Another group of parallel tests @@ -218,7 +219,7 @@ test: partition_join partition_prune hash_part indexing partition_aggregate part # event triggers cannot run concurrently with any test that runs DDL # oidjoins is read-only, though, and should run late for best coverage -test: event_trigger +test: event_trigger oidjoins # this test also uses event triggers, so likewise run it by itself test: fast_default diff --git a/contrib/pax_storage/src/test/regress/sql/dbsize.sql b/contrib/pax_storage/src/test/regress/sql/dbsize.sql new file mode 100644 index 00000000000..38b94444007 --- /dev/null +++ b/contrib/pax_storage/src/test/regress/sql/dbsize.sql @@ -0,0 +1,72 @@ +SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM + (VALUES (10::bigint), (1000::bigint), (1000000::bigint), + (1000000000::bigint), (1000000000000::bigint), + (1000000000000000::bigint)) x(size); + +SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM + (VALUES (10::numeric), (1000::numeric), (1000000::numeric), + (1000000000::numeric), (1000000000000::numeric), + (1000000000000000::numeric), + (10.5::numeric), (1000.5::numeric), (1000000.5::numeric), + (1000000000.5::numeric), (1000000000000.5::numeric), + (1000000000000000.5::numeric)) x(size); + +-- test where units change up +SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM + (VALUES (10239::bigint), (10240::bigint), + (10485247::bigint), (10485248::bigint), + (10736893951::bigint), (10736893952::bigint), + (10994579406847::bigint), (10994579406848::bigint), + (11258449312612351::bigint), (11258449312612352::bigint)) x(size); + +SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM + (VALUES (10239::numeric), (10240::numeric), + (10485247::numeric), (10485248::numeric), + (10736893951::numeric), (10736893952::numeric), + (10994579406847::numeric), (10994579406848::numeric), + (11258449312612351::numeric), (11258449312612352::numeric), + (11528652096115048447::numeric), (11528652096115048448::numeric)) x(size); + +-- Ensure we get the expected results when passing the extremities of bigint +SELECT pg_size_pretty('-9223372036854775808'::bigint), + pg_size_pretty('9223372036854775807'::bigint); + +-- pg_size_bytes() tests +SELECT size, pg_size_bytes(size) FROM + (VALUES ('1'), ('123bytes'), ('256 B'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '), + ('1TB'), ('3000 TB'), ('1e6 MB'), ('99 PB')) x(size); + +-- case-insensitive units are supported +SELECT size, pg_size_bytes(size) FROM + (VALUES ('1'), ('123bYteS'), ('1kb'), ('1mb'), (' 1 Gb'), ('1.5 gB '), + ('1tb'), ('3000 tb'), ('1e6 mb'), ('99 pb')) x(size); + +-- negative numbers are supported +SELECT size, pg_size_bytes(size) FROM + (VALUES ('-1'), ('-123bytes'), ('-1kb'), ('-1mb'), (' -1 Gb'), ('-1.5 gB '), + ('-1tb'), ('-3000 TB'), ('-10e-1 MB'), ('-99 PB')) x(size); + +-- different cases with allowed points +SELECT size, pg_size_bytes(size) FROM + (VALUES ('-1.'), ('-1.kb'), ('-1. kb'), ('-0. gb'), + ('-.1'), ('-.1kb'), ('-.1 kb'), ('-.0 gb')) x(size); + +-- invalid inputs +SELECT pg_size_bytes('1 AB'); +SELECT pg_size_bytes('1 AB A'); +SELECT pg_size_bytes('1 AB A '); +SELECT pg_size_bytes('9223372036854775807.9'); +SELECT pg_size_bytes('1e100'); +SELECT pg_size_bytes('1e1000000000000000000'); +SELECT pg_size_bytes('1 byte'); -- the singular "byte" is not supported +SELECT pg_size_bytes(''); + +SELECT pg_size_bytes('kb'); +SELECT pg_size_bytes('..'); +SELECT pg_size_bytes('-.'); +SELECT pg_size_bytes('-.kb'); +SELECT pg_size_bytes('-. kb'); + +SELECT pg_size_bytes('.+912'); +SELECT pg_size_bytes('+912+ kB'); +SELECT pg_size_bytes('++123 kB'); diff --git a/contrib/pax_storage/src/test/regress/sql/drop_operator.sql b/contrib/pax_storage/src/test/regress/sql/drop_operator.sql new file mode 100644 index 00000000000..cc62cfa14e0 --- /dev/null +++ b/contrib/pax_storage/src/test/regress/sql/drop_operator.sql @@ -0,0 +1,56 @@ +CREATE OPERATOR === ( + PROCEDURE = int8eq, + LEFTARG = bigint, + RIGHTARG = bigint, + COMMUTATOR = === +); + +CREATE OPERATOR !== ( + PROCEDURE = int8ne, + LEFTARG = bigint, + RIGHTARG = bigint, + NEGATOR = ===, + COMMUTATOR = !== +); + +DROP OPERATOR !==(bigint, bigint); + +SELECT ctid, oprcom +FROM pg_catalog.pg_operator fk +WHERE oprcom != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.oprcom); + +SELECT ctid, oprnegate +FROM pg_catalog.pg_operator fk +WHERE oprnegate != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.oprnegate); + +DROP OPERATOR ===(bigint, bigint); + +CREATE OPERATOR <| ( + PROCEDURE = int8lt, + LEFTARG = bigint, + RIGHTARG = bigint +); + +CREATE OPERATOR |> ( + PROCEDURE = int8gt, + LEFTARG = bigint, + RIGHTARG = bigint, + NEGATOR = <|, + COMMUTATOR = <| +); + +DROP OPERATOR |>(bigint, bigint); + +SELECT ctid, oprcom +FROM pg_catalog.pg_operator fk +WHERE oprcom != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.oprcom); + +SELECT ctid, oprnegate +FROM pg_catalog.pg_operator fk +WHERE oprnegate != 0 AND + NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.oprnegate); + +DROP OPERATOR <|(bigint, bigint); diff --git a/contrib/pax_storage/src/test/regress/sql/gp_foreign_data.sql b/contrib/pax_storage/src/test/regress/sql/gp_foreign_data.sql new file mode 100644 index 00000000000..a3a4e851605 --- /dev/null +++ b/contrib/pax_storage/src/test/regress/sql/gp_foreign_data.sql @@ -0,0 +1,55 @@ +-- +-- Test foreign-data wrapper and server management. Cloudberry MPP specific +-- + +-- start_ignore +DROP SERVER s0 CASCADE; +DROP SERVER s1 CASCADE; +DROP FOREIGN DATA WRAPPER dummy CASCADE; +-- end_ignore + +CREATE FOREIGN DATA WRAPPER dummy OPTIONS (mpp_execute 'coordinator');; +COMMENT ON FOREIGN DATA WRAPPER dummy IS 'useless'; +ALTER FOREIGN DATA WRAPPER dummy OPTIONS (SET mpp_execute 'all segments'); + +-- CREATE FOREIGN TABLE +CREATE SERVER s0 FOREIGN DATA WRAPPER dummy; +CREATE FOREIGN TABLE ft2 ( + c1 int +) SERVER s0 OPTIONS (delimiter ',', mpp_execute 'a'); -- ERROR +CREATE FOREIGN TABLE ft2 ( + c1 int +) SERVER s0 OPTIONS (delimiter ',', mpp_execute 'any'); +\d+ ft2 +CREATE FOREIGN TABLE ft3 ( + c1 int +) SERVER s0 OPTIONS (delimiter ',', mpp_execute 'master'); +CREATE FOREIGN TABLE ft4 ( + c1 int +) SERVER s0 OPTIONS (delimiter ',', mpp_execute 'all segments'); + +-- Test num_segments option +CREATE SERVER s1 FOREIGN DATA WRAPPER dummy OPTIONS (num_segments '3'); +CREATE FOREIGN TABLE ft5 ( + c1 int +) SERVER s1 OPTIONS (delimiter ',', mpp_execute 'all segments', num_segments '5'); +\d+ ft5 + +-- CREATE FOREIGN TABLE LIKE +CREATE TABLE ft_source_table(a INT, b INT, c INT) DISTRIBUTED BY (b); +CREATE FOREIGN TABLE ft_like (LIKE ft_source_table) SERVER s0; +\d+ ft_like +-- shoule be null +SELECT * FROM gp_distribution_policy WHERE localoid = 'ft_like'::regclass::oid; +CREATE FOREIGN TABLE ft_like1 (LIKE ft_source_table) SERVER s0 +OPTIONS (delimiter ',', mpp_execute 'all segments', num_segments '3'); +\d+ ft_like1 +SELECT * FROM gp_distribution_policy WHERE localoid = 'ft_like1'::regclass::oid; + +DROP TABLE ft_source_table; +DROP FOREIGN TABLE ft_like; +DROP FOREIGN TABLE ft_like1; + +--start_ignore +DROP FOREIGN DATA WRAPPER dummy CASCADE; +--end_ignore diff --git a/contrib/pax_storage/src/test/regress/sql/init_privs.sql b/contrib/pax_storage/src/test/regress/sql/init_privs.sql new file mode 100644 index 00000000000..4a31af27986 --- /dev/null +++ b/contrib/pax_storage/src/test/regress/sql/init_privs.sql @@ -0,0 +1,10 @@ +-- Test initial privileges + +-- There should always be some initial privileges, set up by initdb +SELECT count(*) > 0 FROM pg_init_privs; + +-- Intentionally include some non-initial privs for pg_dump to dump out +GRANT SELECT ON pg_proc TO CURRENT_USER; +GRANT SELECT (prosrc) ON pg_proc TO CURRENT_USER; + +GRANT SELECT (rolname, rolsuper) ON pg_authid TO CURRENT_USER; diff --git a/contrib/pax_storage/src/test/regress/sql/oidjoins.sql b/contrib/pax_storage/src/test/regress/sql/oidjoins.sql new file mode 100644 index 00000000000..8b22e6d10c5 --- /dev/null +++ b/contrib/pax_storage/src/test/regress/sql/oidjoins.sql @@ -0,0 +1,49 @@ +-- +-- Verify system catalog foreign key relationships +-- +DO $doblock$ +declare + fk record; + nkeys integer; + cmd text; + err record; +begin + for fk in select * from pg_get_catalog_foreign_keys() + loop + raise notice 'checking % % => % %', + fk.fktable, fk.fkcols, fk.pktable, fk.pkcols; + nkeys := array_length(fk.fkcols, 1); + cmd := 'SELECT ctid'; + for i in 1 .. nkeys loop + cmd := cmd || ', ' || quote_ident(fk.fkcols[i]); + end loop; + if fk.is_array then + cmd := cmd || ' FROM (SELECT ctid'; + for i in 1 .. nkeys-1 loop + cmd := cmd || ', ' || quote_ident(fk.fkcols[i]); + end loop; + cmd := cmd || ', unnest(' || quote_ident(fk.fkcols[nkeys]); + cmd := cmd || ') as ' || quote_ident(fk.fkcols[nkeys]); + cmd := cmd || ' FROM ' || fk.fktable::text || ') fk WHERE '; + else + cmd := cmd || ' FROM ' || fk.fktable::text || ' fk WHERE '; + end if; + if fk.is_opt then + for i in 1 .. nkeys loop + cmd := cmd || quote_ident(fk.fkcols[i]) || ' != 0 AND '; + end loop; + end if; + cmd := cmd || 'NOT EXISTS(SELECT 1 FROM ' || fk.pktable::text || ' pk WHERE '; + for i in 1 .. nkeys loop + if i > 1 then cmd := cmd || ' AND '; end if; + cmd := cmd || 'pk.' || quote_ident(fk.pkcols[i]); + cmd := cmd || ' = fk.' || quote_ident(fk.fkcols[i]); + end loop; + cmd := cmd || ')'; + -- raise notice 'cmd = %', cmd; + for err in execute cmd loop + raise warning 'FK VIOLATION IN %(%): %', fk.fktable, fk.fkcols, err; + end loop; + end loop; +end +$doblock$; diff --git a/contrib/pax_storage/src/test/regress/sql/password.sql b/contrib/pax_storage/src/test/regress/sql/password.sql new file mode 100644 index 00000000000..53e86b0b6ce --- /dev/null +++ b/contrib/pax_storage/src/test/regress/sql/password.sql @@ -0,0 +1,114 @@ +-- +-- Tests for password types +-- + +-- Tests for GUC password_encryption +SET password_encryption = 'novalue'; -- error +SET password_encryption = true; -- error +SET password_encryption = 'md5'; -- ok +SET password_encryption = 'scram-sha-256'; -- ok + +-- consistency of password entries +SET password_encryption = 'md5'; +CREATE ROLE regress_passwd1 PASSWORD 'role_pwd1'; +CREATE ROLE regress_passwd2 PASSWORD 'role_pwd2'; +SET password_encryption = 'scram-sha-256'; +CREATE ROLE regress_passwd3 PASSWORD 'role_pwd3'; +CREATE ROLE regress_passwd4 PASSWORD NULL; + +-- check list of created entries +-- +-- The scram secret will look something like: +-- SCRAM-SHA-256$4096:E4HxLGtnRzsYwg==$6YtlR4t69SguDiwFvbVgVZtuz6gpJQQqUMZ7IQJK5yI=:ps75jrHeYU4lXCcXI4O8oIdJ3eO8o2jirjruw9phBTo= +-- +-- Since the salt is random, the exact value stored will be different on every test +-- run. Use a regular expression to mask the changing parts. +SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked + FROM pg_authid + WHERE rolname LIKE 'regress_passwd%' + ORDER BY rolname, rolpassword; + +-- Rename a role +ALTER ROLE regress_passwd2 RENAME TO regress_passwd2_new; +-- md5 entry should have been removed +SELECT rolname, rolpassword + FROM pg_authid + WHERE rolname LIKE 'regress_passwd2_new' + ORDER BY rolname, rolpassword; +ALTER ROLE regress_passwd2_new RENAME TO regress_passwd2; + +-- Change passwords with ALTER USER. With plaintext or already-encrypted +-- passwords. +SET password_encryption = 'md5'; + +-- encrypt with MD5 +ALTER ROLE regress_passwd2 PASSWORD 'foo'; +-- already encrypted, use as they are +ALTER ROLE regress_passwd1 PASSWORD 'md5cd3578025fe2c3d7ed1b9a9b26238b70'; +ALTER ROLE regress_passwd3 PASSWORD 'SCRAM-SHA-256$4096:VLK4RMaQLCvNtQ==$6YtlR4t69SguDiwFvbVgVZtuz6gpJQQqUMZ7IQJK5yI=:ps75jrHeYU4lXCcXI4O8oIdJ3eO8o2jirjruw9phBTo='; + +SET password_encryption = 'scram-sha-256'; +-- create SCRAM secret +ALTER ROLE regress_passwd4 PASSWORD 'foo'; +-- already encrypted with MD5, use as it is +CREATE ROLE regress_passwd5 PASSWORD 'md5e73a4b11df52a6068f8b39f90be36023'; + +-- This looks like a valid SCRAM-SHA-256 secret, but it is not +-- so it should be hashed with SCRAM-SHA-256. +CREATE ROLE regress_passwd6 PASSWORD 'SCRAM-SHA-256$1234'; +-- These may look like valid MD5 secrets, but they are not, so they +-- should be hashed with SCRAM-SHA-256. +-- trailing garbage at the end +CREATE ROLE regress_passwd7 PASSWORD 'md5012345678901234567890123456789zz'; +-- invalid length +CREATE ROLE regress_passwd8 PASSWORD 'md501234567890123456789012345678901zz'; + +-- Changing the SCRAM iteration count +SET scram_iterations = 1024; +CREATE ROLE regress_passwd9 PASSWORD 'alterediterationcount'; + +SELECT rolname, regexp_replace(rolpassword, '(SCRAM-SHA-256)\$(\d+):([a-zA-Z0-9+/=]+)\$([a-zA-Z0-9+=/]+):([a-zA-Z0-9+/=]+)', '\1$\2:<salt>$<storedkey>:<serverkey>') as rolpassword_masked + FROM pg_authid + WHERE rolname LIKE 'regress_passwd%' + ORDER BY rolname, rolpassword; + +-- An empty password is not allowed, in any form +CREATE ROLE regress_passwd_empty PASSWORD ''; +ALTER ROLE regress_passwd_empty PASSWORD 'md585939a5ce845f1a1b620742e3c659e0a'; +ALTER ROLE regress_passwd_empty PASSWORD 'SCRAM-SHA-256$4096:hpFyHTUsSWcR7O9P$LgZFIt6Oqdo27ZFKbZ2nV+vtnYM995pDh9ca6WSi120=:qVV5NeluNfUPkwm7Vqat25RjSPLkGeoZBQs6wVv+um4='; +SELECT rolpassword FROM pg_authid WHERE rolname='regress_passwd_empty'; + +-- Test with invalid stored and server keys. +-- +-- The first is valid, to act as a control. The others have too long +-- stored/server keys. They will be re-hashed. +CREATE ROLE regress_passwd_sha_len0 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZI='; +CREATE ROLE regress_passwd_sha_len1 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96RqwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZI='; +CREATE ROLE regress_passwd_sha_len2 PASSWORD 'SCRAM-SHA-256$4096:A6xHKoH/494E941doaPOYg==$Ky+A30sewHIH3VHQLRN9vYsuzlgNyGNKCh37dy96Rqw=:COPdlNiIkrsacU5QoxydEuOH6e/KfiipeETb/bPw8ZIAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA='; + +-- Check that the invalid secrets were re-hashed. A re-hashed secret +-- should not contain the original salt. +SELECT rolname, rolpassword not like '%A6xHKoH/494E941doaPOYg==%' as is_rolpassword_rehashed + FROM pg_authid + WHERE rolname LIKE 'regress_passwd_sha_len%' + ORDER BY rolname; + +DROP ROLE regress_passwd1; +DROP ROLE regress_passwd2; +DROP ROLE regress_passwd3; +DROP ROLE regress_passwd4; +DROP ROLE regress_passwd5; +DROP ROLE regress_passwd6; +DROP ROLE regress_passwd7; +DROP ROLE regress_passwd8; +DROP ROLE regress_passwd9; +DROP ROLE regress_passwd_empty; +DROP ROLE regress_passwd_sha_len0; +DROP ROLE regress_passwd_sha_len1; +DROP ROLE regress_passwd_sha_len2; + +-- all entries should have been removed +SELECT rolname, rolpassword + FROM pg_authid + WHERE rolname LIKE 'regress_passwd%' + ORDER BY rolname, rolpassword; diff --git a/contrib/pax_storage/src/test/regress/sql/roleattributes.sql b/contrib/pax_storage/src/test/regress/sql/roleattributes.sql new file mode 100644 index 00000000000..c961b2d7303 --- /dev/null +++ b/contrib/pax_storage/src/test/regress/sql/roleattributes.sql @@ -0,0 +1,98 @@ +-- default for superuser is false +CREATE ROLE regress_test_def_superuser; + +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_superuser'; +CREATE ROLE regress_test_superuser WITH SUPERUSER; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; +ALTER ROLE regress_test_superuser WITH NOSUPERUSER; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; +ALTER ROLE regress_test_superuser WITH SUPERUSER; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_superuser'; + +-- default for inherit is true +CREATE ROLE regress_test_def_inherit; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_inherit'; +CREATE ROLE regress_test_inherit WITH NOINHERIT; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; +ALTER ROLE regress_test_inherit WITH INHERIT; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; +ALTER ROLE regress_test_inherit WITH NOINHERIT; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_inherit'; + +-- default for create role is false +CREATE ROLE regress_test_def_createrole; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createrole'; +CREATE ROLE regress_test_createrole WITH CREATEROLE; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; +ALTER ROLE regress_test_createrole WITH NOCREATEROLE; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; +ALTER ROLE regress_test_createrole WITH CREATEROLE; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createrole'; + +-- default for create database is false +CREATE ROLE regress_test_def_createdb; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_createdb'; +CREATE ROLE regress_test_createdb WITH CREATEDB; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; +ALTER ROLE regress_test_createdb WITH NOCREATEDB; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; +ALTER ROLE regress_test_createdb WITH CREATEDB; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_createdb'; + +-- default for can login is false for role +CREATE ROLE regress_test_def_role_canlogin; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin'; +CREATE ROLE regress_test_role_canlogin WITH LOGIN; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; +ALTER ROLE regress_test_role_canlogin WITH NOLOGIN; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; +ALTER ROLE regress_test_role_canlogin WITH LOGIN; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_role_canlogin'; + +-- default for can login is true for user +CREATE USER regress_test_def_user_canlogin; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin'; +CREATE USER regress_test_user_canlogin WITH NOLOGIN; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; +ALTER USER regress_test_user_canlogin WITH LOGIN; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; +ALTER USER regress_test_user_canlogin WITH NOLOGIN; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_user_canlogin'; + +-- default for replication is false +CREATE ROLE regress_test_def_replication; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_replication'; +CREATE ROLE regress_test_replication WITH REPLICATION; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; +ALTER ROLE regress_test_replication WITH NOREPLICATION; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; +ALTER ROLE regress_test_replication WITH REPLICATION; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_replication'; + +-- default for bypassrls is false +CREATE ROLE regress_test_def_bypassrls; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls'; +CREATE ROLE regress_test_bypassrls WITH BYPASSRLS; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; +ALTER ROLE regress_test_bypassrls WITH NOBYPASSRLS; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; +ALTER ROLE regress_test_bypassrls WITH BYPASSRLS; +SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls, rolconnlimit, rolpassword, rolvaliduntil FROM pg_authid WHERE rolname = 'regress_test_bypassrls'; + +-- clean up roles +DROP ROLE regress_test_def_superuser; +DROP ROLE regress_test_superuser; +DROP ROLE regress_test_def_inherit; +DROP ROLE regress_test_inherit; +DROP ROLE regress_test_def_createrole; +DROP ROLE regress_test_createrole; +DROP ROLE regress_test_def_createdb; +DROP ROLE regress_test_createdb; +DROP ROLE regress_test_def_role_canlogin; +DROP ROLE regress_test_role_canlogin; +DROP USER regress_test_def_user_canlogin; +DROP USER regress_test_user_canlogin; +DROP ROLE regress_test_def_replication; +DROP ROLE regress_test_replication; +DROP ROLE regress_test_def_bypassrls; +DROP ROLE regress_test_bypassrls; diff --git a/contrib/pax_storage/src/test/regress/sql/sysviews.sql b/contrib/pax_storage/src/test/regress/sql/sysviews.sql new file mode 100644 index 00000000000..ef9042fabd8 --- /dev/null +++ b/contrib/pax_storage/src/test/regress/sql/sysviews.sql @@ -0,0 +1,76 @@ +-- +-- Test assorted system views +-- +-- This test is mainly meant to provide some code coverage for the +-- set-returning functions that underlie certain system views. +-- The output of most of these functions is very environment-dependent, +-- so our ability to test with fixed expected output is pretty limited; +-- but even a trivial check of count(*) will exercise the normal code path +-- through the SRF. + +select count(*) >= 0 as ok from pg_available_extension_versions; + +select count(*) >= 0 as ok from pg_available_extensions; + +-- The entire output of pg_backend_memory_contexts is not stable, +-- we test only the existence and basic condition of TopMemoryContext. +select name, ident, parent, level, total_bytes >= free_bytes + from pg_backend_memory_contexts where level = 0; + +-- At introduction, pg_config had 23 entries; it may grow +select count(*) > 20 as ok from pg_config; + +-- We expect no cursors in this test; see also portals.sql +select count(*) = 0 as ok from pg_cursors; + +select count(*) >= 0 as ok from pg_file_settings; + +-- There will surely be at least one rule, with no errors. +select count(*) > 0 as ok, count(*) FILTER (WHERE error IS NOT NULL) = 0 AS no_err + from pg_hba_file_rules; + +-- There may be no rules, and there should be no errors. +select count(*) >= 0 as ok, count(*) FILTER (WHERE error IS NOT NULL) = 0 AS no_err + from pg_ident_file_mappings; + +-- There will surely be at least one active lock +select count(*) > 0 as ok from pg_locks; + +-- We expect no prepared statements in this test; see also prepare.sql +select count(*) = 0 as ok from pg_prepared_statements; + +-- See also prepared_xacts.sql +select count(*) >= 0 as ok from pg_prepared_xacts; + +-- There will surely be at least one SLRU cache +select count(*) > 0 as ok from pg_stat_slru; + +-- There must be only one record +select count(*) = 1 as ok from pg_stat_wal; + +-- We expect no walreceiver running in this test +select count(*) = 0 as ok from pg_stat_wal_receiver; + +-- This is to record the prevailing planner enable_foo settings during +-- a regression test run. +-- GP parallel tests will run another with enable_parallel=on, filter this to pass regression. +SELECT name, setting FROM pg_settings WHERE name LIKE 'enable%' and +name not in ('enable_parallel', + 'enable_answer_query_using_materialized_views', + 'enable_parallel_dedup_semi_join', + 'enable_parallel_dedup_semi_reverse_join', + 'enable_parallel_semi_join'); + +-- Test that the pg_timezone_names and pg_timezone_abbrevs views are +-- more-or-less working. We can't test their contents in any great detail +-- without the outputs changing anytime IANA updates the underlying data, +-- but it seems reasonable to expect at least one entry per major meridian. +-- (At the time of writing, the actual counts are around 38 because of +-- zones using fractional GMT offsets, so this is a pretty loose test.) +select count(distinct utc_offset) >= 24 as ok from pg_timezone_names; +select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; +-- Let's check the non-default timezone abbreviation sets, too +set timezone_abbreviations = 'Australia'; +select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; +set timezone_abbreviations = 'India'; +select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
