| -- pg_regress should ensure that this default value applies; however |
| -- we can't rely on any specific default value of vacuum_cost_delay |
| SHOW datestyle; |
| DateStyle |
| --------------- |
| Postgres, MDY |
| (1 row) |
| |
| -- SET to some nondefault value |
| SET vacuum_cost_delay TO 40; |
| SET datestyle = 'ISO, YMD'; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 40ms |
| (1 row) |
| |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| -- SET LOCAL has no effect outside of a transaction |
| SET LOCAL vacuum_cost_delay TO 50; |
| WARNING: SET LOCAL can only be used in transaction blocks |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 40ms |
| (1 row) |
| |
| SET LOCAL datestyle = 'SQL'; |
| WARNING: SET LOCAL can only be used in transaction blocks |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| -- SET LOCAL within a transaction that commits |
| BEGIN; |
| SET LOCAL vacuum_cost_delay TO 50; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 50ms |
| (1 row) |
| |
| SET LOCAL datestyle = 'SQL'; |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| SQL, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------- |
| 08/13/2006 12:34:56 PDT |
| (1 row) |
| |
| COMMIT; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 40ms |
| (1 row) |
| |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| -- SET should be reverted after ROLLBACK |
| BEGIN; |
| SET vacuum_cost_delay TO 60; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 60ms |
| (1 row) |
| |
| SET datestyle = 'German'; |
| SHOW datestyle; |
| DateStyle |
| ------------- |
| German, DMY |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------- |
| 13.08.2006 12:34:56 PDT |
| (1 row) |
| |
| ROLLBACK; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 40ms |
| (1 row) |
| |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| -- Some tests with subtransactions |
| BEGIN; |
| SET vacuum_cost_delay TO 70; |
| SET datestyle = 'MDY'; |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, MDY |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| SAVEPOINT first_sp; |
| SET vacuum_cost_delay TO 80.1; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 80100us |
| (1 row) |
| |
| SET datestyle = 'German, DMY'; |
| SHOW datestyle; |
| DateStyle |
| ------------- |
| German, DMY |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------- |
| 13.08.2006 12:34:56 PDT |
| (1 row) |
| |
| ROLLBACK TO first_sp; |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, MDY |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| SAVEPOINT second_sp; |
| SET vacuum_cost_delay TO '900us'; |
| SET datestyle = 'SQL, YMD'; |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| SQL, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------- |
| 08/13/2006 12:34:56 PDT |
| (1 row) |
| |
| SAVEPOINT third_sp; |
| SET vacuum_cost_delay TO 100; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 100ms |
| (1 row) |
| |
| SET datestyle = 'Postgres, MDY'; |
| SHOW datestyle; |
| DateStyle |
| --------------- |
| Postgres, MDY |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------------ |
| Sun Aug 13 12:34:56 2006 PDT |
| (1 row) |
| |
| ROLLBACK TO third_sp; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 900us |
| (1 row) |
| |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| SQL, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------- |
| 08/13/2006 12:34:56 PDT |
| (1 row) |
| |
| ROLLBACK TO second_sp; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 70ms |
| (1 row) |
| |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, MDY |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| ROLLBACK; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 40ms |
| (1 row) |
| |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| -- SET LOCAL with Savepoints |
| BEGIN; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 40ms |
| (1 row) |
| |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| SAVEPOINT sp; |
| SET LOCAL vacuum_cost_delay TO 30; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 30ms |
| (1 row) |
| |
| SET LOCAL datestyle = 'Postgres, MDY'; |
| SHOW datestyle; |
| DateStyle |
| --------------- |
| Postgres, MDY |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------------ |
| Sun Aug 13 12:34:56 2006 PDT |
| (1 row) |
| |
| ROLLBACK TO sp; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 40ms |
| (1 row) |
| |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| ROLLBACK; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 40ms |
| (1 row) |
| |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| -- SET LOCAL persists through RELEASE (which was not true in 8.0-8.2) |
| BEGIN; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 40ms |
| (1 row) |
| |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| SAVEPOINT sp; |
| SET LOCAL vacuum_cost_delay TO 30; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 30ms |
| (1 row) |
| |
| SET LOCAL datestyle = 'Postgres, MDY'; |
| SHOW datestyle; |
| DateStyle |
| --------------- |
| Postgres, MDY |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------------ |
| Sun Aug 13 12:34:56 2006 PDT |
| (1 row) |
| |
| RELEASE SAVEPOINT sp; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 30ms |
| (1 row) |
| |
| SHOW datestyle; |
| DateStyle |
| --------------- |
| Postgres, MDY |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------------ |
| Sun Aug 13 12:34:56 2006 PDT |
| (1 row) |
| |
| ROLLBACK; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 40ms |
| (1 row) |
| |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| -- SET followed by SET LOCAL |
| BEGIN; |
| SET vacuum_cost_delay TO 40; |
| SET LOCAL vacuum_cost_delay TO 50; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 50ms |
| (1 row) |
| |
| SET datestyle = 'ISO, DMY'; |
| SET LOCAL datestyle = 'Postgres, MDY'; |
| SHOW datestyle; |
| DateStyle |
| --------------- |
| Postgres, MDY |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------------ |
| Sun Aug 13 12:34:56 2006 PDT |
| (1 row) |
| |
| COMMIT; |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 40ms |
| (1 row) |
| |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, DMY |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| -- |
| -- Test RESET. We use datestyle because the reset value is forced by |
| -- pg_regress, so it doesn't depend on the installation's configuration. |
| -- |
| SET datestyle = iso, ymd; |
| SHOW datestyle; |
| DateStyle |
| ----------- |
| ISO, YMD |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------ |
| 2006-08-13 12:34:56-07 |
| (1 row) |
| |
| RESET datestyle; |
| SHOW datestyle; |
| DateStyle |
| --------------- |
| Postgres, MDY |
| (1 row) |
| |
| SELECT '2006-08-13 12:34:56'::timestamptz; |
| timestamptz |
| ------------------------------ |
| Sun Aug 13 12:34:56 2006 PDT |
| (1 row) |
| |
| -- Test some simple error cases |
| SET seq_page_cost TO 'NaN'; |
| ERROR: invalid value for parameter "seq_page_cost": "NaN" |
| SET vacuum_cost_delay TO '10s'; |
| ERROR: 10000 ms is outside the valid range for parameter "vacuum_cost_delay" (0 .. 100) |
| SET no_such_variable TO 42; |
| ERROR: unrecognized configuration parameter "no_such_variable" |
| -- Test "custom" GUCs created on the fly (which aren't really an |
| -- intended feature, but many people use them). |
| SHOW custom.my_guc; -- error, not known yet |
| ERROR: unrecognized configuration parameter "custom.my_guc" |
| SET custom.my_guc = 42; |
| SHOW custom.my_guc; |
| custom.my_guc |
| --------------- |
| 42 |
| (1 row) |
| |
| RESET custom.my_guc; -- this makes it go to empty, not become unknown again |
| SHOW custom.my_guc; |
| custom.my_guc |
| --------------- |
| |
| (1 row) |
| |
| SET custom.my.qualified.guc = 'foo'; |
| SHOW custom.my.qualified.guc; |
| custom.my.qualified.guc |
| ------------------------- |
| foo |
| (1 row) |
| |
| SET custom."bad-guc" = 42; -- disallowed because -c cannot set this name |
| ERROR: invalid configuration parameter name "custom.bad-guc" |
| DETAIL: Custom parameter names must be two or more simple identifiers separated by dots. |
| SHOW custom."bad-guc"; |
| ERROR: unrecognized configuration parameter "custom.bad-guc" |
| SET special."weird name" = 'foo'; -- could be allowed, but we choose not to |
| ERROR: invalid configuration parameter name "special.weird name" |
| DETAIL: Custom parameter names must be two or more simple identifiers separated by dots. |
| SHOW special."weird name"; |
| ERROR: unrecognized configuration parameter "special.weird name" |
| -- Check what happens when you try to set a "custom" GUC within the |
| -- namespace of an extension. |
| SET plpgsql.extra_foo_warnings = true; -- allowed if plpgsql is not loaded yet |
| LOAD 'plpgsql'; -- this will throw a warning and delete the variable |
| WARNING: invalid configuration parameter name "plpgsql.extra_foo_warnings", removing it |
| DETAIL: "plpgsql" is now a reserved prefix. |
| SET plpgsql.extra_foo_warnings = true; -- now, it's an error |
| ERROR: invalid configuration parameter name "plpgsql.extra_foo_warnings" |
| DETAIL: "plpgsql" is a reserved prefix. |
| SHOW plpgsql.extra_foo_warnings; |
| ERROR: unrecognized configuration parameter "plpgsql.extra_foo_warnings" |
| -- |
| -- Test DISCARD TEMP |
| -- |
| CREATE TEMP TABLE reset_test ( data text ) ON COMMIT DELETE ROWS; |
| SELECT relname FROM pg_class WHERE relname = 'reset_test'; |
| relname |
| ------------ |
| reset_test |
| (1 row) |
| |
| DISCARD TEMP; |
| SELECT relname FROM pg_class WHERE relname = 'reset_test'; |
| relname |
| --------- |
| (0 rows) |
| |
| -- |
| -- Test DISCARD ALL |
| -- |
| -- do changes |
| DECLARE foo CURSOR WITH HOLD FOR SELECT 1; |
| PREPARE foo AS SELECT 1; |
| LISTEN foo_event; |
| SET vacuum_cost_delay = 13; |
| CREATE TEMP TABLE tmp_foo (data text) ON COMMIT DELETE ROWS; |
| CREATE ROLE regress_guc_user; |
| SET SESSION AUTHORIZATION regress_guc_user; |
| -- look changes |
| SELECT pg_listening_channels(); |
| pg_listening_channels |
| ----------------------- |
| foo_event |
| (1 row) |
| |
| SELECT name FROM pg_prepared_statements; |
| name |
| ------ |
| foo |
| (1 row) |
| |
| SELECT name FROM pg_cursors; |
| name |
| ------ |
| foo |
| (1 row) |
| |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 13ms |
| (1 row) |
| |
| SELECT relname from pg_class where relname = 'tmp_foo'; |
| relname |
| --------- |
| tmp_foo |
| (1 row) |
| |
| SELECT current_user = 'regress_guc_user'; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| -- discard everything |
| DISCARD ALL; |
| NOTICE: command without clusterwide effect |
| HINT: Consider alternatives as DEALLOCATE ALL, or DISCARD TEMP if a clusterwide effect is desired. |
| -- look again |
| SELECT pg_listening_channels(); |
| pg_listening_channels |
| ----------------------- |
| (0 rows) |
| |
| SELECT name FROM pg_prepared_statements; |
| name |
| ------ |
| (0 rows) |
| |
| SELECT name FROM pg_cursors; |
| name |
| ------ |
| (0 rows) |
| |
| SHOW vacuum_cost_delay; |
| vacuum_cost_delay |
| ------------------- |
| 0 |
| (1 row) |
| |
| SELECT relname from pg_class where relname = 'tmp_foo'; |
| relname |
| --------- |
| (0 rows) |
| |
| SELECT current_user = 'regress_guc_user'; |
| ?column? |
| ---------- |
| f |
| (1 row) |
| |
| DROP ROLE regress_guc_user; |
| -- |
| -- search_path should react to changes in pg_namespace |
| -- |
| set search_path = foo, public, not_there_initially; |
| select current_schemas(false); |
| current_schemas |
| ----------------- |
| {public} |
| (1 row) |
| |
| create schema not_there_initially; |
| select current_schemas(false); |
| current_schemas |
| ------------------------------ |
| {public,not_there_initially} |
| (1 row) |
| |
| drop schema not_there_initially; |
| select current_schemas(false); |
| current_schemas |
| ----------------- |
| {public} |
| (1 row) |
| |
| reset search_path; |
| -- |
| -- Tests for function-local GUC settings |
| -- |
| set work_mem = '3MB'; |
| create function report_guc(text) returns text as |
| $$ select current_setting($1) $$ language sql |
| set work_mem = '1MB'; |
| select report_guc('work_mem'), current_setting('work_mem'); |
| report_guc | current_setting |
| ------------+----------------- |
| 1MB | 3MB |
| (1 row) |
| |
| alter function report_guc(text) set work_mem = '2MB'; |
| select report_guc('work_mem'), current_setting('work_mem'); |
| report_guc | current_setting |
| ------------+----------------- |
| 2MB | 3MB |
| (1 row) |
| |
| alter function report_guc(text) reset all; |
| select report_guc('work_mem'), current_setting('work_mem'); |
| report_guc | current_setting |
| ------------+----------------- |
| 3MB | 3MB |
| (1 row) |
| |
| -- SET LOCAL is restricted by a function SET option |
| create or replace function myfunc(int) returns text as $$ |
| begin |
| set local work_mem = '2MB'; |
| return current_setting('work_mem'); |
| end $$ |
| language plpgsql |
| set work_mem = '1MB'; |
| select myfunc(0), current_setting('work_mem'); |
| myfunc | current_setting |
| --------+----------------- |
| 2MB | 3MB |
| (1 row) |
| |
| alter function myfunc(int) reset all; |
| select myfunc(0), current_setting('work_mem'); |
| myfunc | current_setting |
| --------+----------------- |
| 2MB | 2MB |
| (1 row) |
| |
| set work_mem = '3MB'; |
| -- but SET isn't |
| create or replace function myfunc(int) returns text as $$ |
| begin |
| set work_mem = '2MB'; |
| return current_setting('work_mem'); |
| end $$ |
| language plpgsql |
| set work_mem = '1MB'; |
| select myfunc(0), current_setting('work_mem'); |
| myfunc | current_setting |
| --------+----------------- |
| 2MB | 2MB |
| (1 row) |
| |
| set work_mem = '3MB'; |
| -- it should roll back on error, though |
| create or replace function myfunc(int) returns text as $$ |
| begin |
| set work_mem = '2MB'; |
| perform 1/$1; |
| return current_setting('work_mem'); |
| end $$ |
| language plpgsql |
| set work_mem = '1MB'; |
| select myfunc(0); |
| ERROR: division by zero |
| CONTEXT: SQL statement "SELECT 1/$1" |
| PL/pgSQL function myfunc(integer) line 4 at PERFORM |
| select current_setting('work_mem'); |
| current_setting |
| ----------------- |
| 3MB |
| (1 row) |
| |
| select myfunc(1), current_setting('work_mem'); |
| myfunc | current_setting |
| --------+----------------- |
| 2MB | 2MB |
| (1 row) |
| |
| -- check current_setting()'s behavior with invalid setting name |
| select current_setting('nosuch.setting'); -- FAIL |
| ERROR: unrecognized configuration parameter "nosuch.setting" |
| select current_setting('nosuch.setting', false); -- FAIL |
| ERROR: unrecognized configuration parameter "nosuch.setting" |
| select current_setting('nosuch.setting', true) is null; |
| ?column? |
| ---------- |
| t |
| (1 row) |
| |
| -- after this, all three cases should yield 'nada' |
| set nosuch.setting = 'nada'; |
| select current_setting('nosuch.setting'); |
| current_setting |
| ----------------- |
| nada |
| (1 row) |
| |
| select current_setting('nosuch.setting', false); |
| current_setting |
| ----------------- |
| nada |
| (1 row) |
| |
| select current_setting('nosuch.setting', true); |
| current_setting |
| ----------------- |
| nada |
| (1 row) |
| |
| -- Normally, CREATE FUNCTION should complain about invalid values in |
| -- function SET options; but not if check_function_bodies is off, |
| -- because that creates ordering hazards for pg_dump |
| create function func_with_bad_set() returns int as $$ select 1 $$ |
| language sql |
| set default_text_search_config = no_such_config; |
| NOTICE: text search configuration "no_such_config" does not exist |
| ERROR: invalid value for parameter "default_text_search_config": "no_such_config" |
| set check_function_bodies = off; |
| create function func_with_bad_set() returns int as $$ select 1 $$ |
| language sql |
| set default_text_search_config = no_such_config; |
| NOTICE: text search configuration "no_such_config" does not exist |
| select func_with_bad_set(); |
| ERROR: invalid value for parameter "default_text_search_config": "no_such_config" |
| reset check_function_bodies; |
| set default_with_oids to f; |
| -- Should not allow to set it to true. |
| set default_with_oids to t; |
| ERROR: tables declared WITH OIDS are not supported |
| SET "request.header.user.agent" = 'curl/7.29.0'; |
| SHOW "request.header.user.agent"; |
| request.header.user.agent |
| --------------------------- |
| curl/7.29.0 |
| (1 row) |
| |
| -- Test function with SET search_path |
| create schema n1; |
| create type ty1 as (i int); |
| CREATE OR REPLACE FUNCTION n1.drop_table(v_schema character varying, v_table character varying) RETURNS text |
| AS $$ |
| BEGIN |
| EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(v_schema) || '.' || quote_ident(v_table) || ';'; |
| RETURN '0'; |
| EXCEPTION |
| WHEN OTHERS THEN |
| RETURN SQLSTATE; |
| END; |
| $$ LANGUAGE plpgsql |
| SECURITY DEFINER |
| SET search_path = n1, pg_temp; |
| select n1.drop_table('public','t1'); |
| NOTICE: table "t1" does not exist, skipping |
| drop_table |
| ------------ |
| 0 |
| (1 row) |
| |
| -- After funtion drop table, public schema is still in search_path |
| create table public.t1(i ty1); |
| NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry data distribution key for this table. |
| HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. |
| drop table public.t1; |
| drop type public.ty1; |
| drop function n1.drop_table(v_schema character varying, v_table character varying); |
| drop schema n1; |
| -- Test GUC categories and flag patterns |
| SELECT pg_settings_get_flags(NULL); |
| pg_settings_get_flags |
| ----------------------- |
| |
| (1 row) |
| |
| SELECT pg_settings_get_flags('does_not_exist'); |
| pg_settings_get_flags |
| ----------------------- |
| |
| (1 row) |
| |
| CREATE TABLE tab_settings_flags AS SELECT name, category, |
| 'EXPLAIN' = ANY(flags) AS explain, |
| 'NO_RESET' = ANY(flags) AS no_reset, |
| 'NO_RESET_ALL' = ANY(flags) AS no_reset_all, |
| 'NOT_IN_SAMPLE' = ANY(flags) AS not_in_sample, |
| 'RUNTIME_COMPUTED' = ANY(flags) AS runtime_computed |
| FROM pg_show_all_settings() AS psas, |
| pg_settings_get_flags(psas.name) AS flags; |
| -- Developer GUCs should be flagged with GUC_NOT_IN_SAMPLE: |
| SELECT name FROM tab_settings_flags |
| WHERE category = 'Developer Options' AND NOT not_in_sample |
| ORDER BY 1; |
| name |
| ------------------------------------------- |
| allow_dml_directory_table |
| aqumv_allow_foreign_table |
| create_restartpoint_on_ckpt_record_replay |
| enable_parallel_dedup_semi_join |
| enable_parallel_dedup_semi_reverse_join |
| enable_parallel_semi_join |
| gp_appendonly_compaction_segfile_limit |
| gp_appendonly_insert_files |
| gp_appendonly_insert_files_tuples_range |
| gp_autostats_allow_nonowner |
| gp_autostats_lock_wait |
| gp_autostats_mode |
| gp_autostats_mode_in_functions |
| gp_autostats_on_change_threshold |
| gp_enable_runtime_filter_pushdown |
| gp_reraise_signal |
| optimizer_control |
| optimizer_enable_orderedagg |
| (18 rows) |
| |
| -- Most query-tuning GUCs are flagged as valid for EXPLAIN. |
| -- default_statistics_target is an exception. |
| SELECT name FROM tab_settings_flags |
| WHERE category ~ '^Query Tuning' AND NOT explain |
| ORDER BY 1; |
| name |
| ------------------------------------------------------ |
| enable_groupagg |
| gp_adjust_selectivity_for_outerjoins |
| gp_dynamic_partition_pruning |
| gp_enable_agg_distinct |
| gp_enable_agg_distinct_pruning |
| gp_enable_agg_pushdown |
| gp_enable_ao_indexscan |
| gp_enable_direct_dispatch |
| gp_enable_fast_sri |
| gp_enable_minmax_optimization |
| gp_enable_multiphase_agg |
| gp_enable_multiphase_limit |
| gp_enable_predicate_propagation |
| gp_enable_preunique |
| gp_enable_refresh_fast_path |
| gp_enable_relsize_collection |
| gp_enable_runtime_filter |
| gp_enable_sort_limit |
| gp_motion_cost_per_row |
| gp_segments_for_planner |
| gp_statistics_pullup_from_child_partition |
| gp_statistics_use_fkeys |
| optimizer |
| optimizer_array_expansion_threshold |
| optimizer_cte_inlining_bound |
| optimizer_damping_factor_filter |
| optimizer_damping_factor_groupby |
| optimizer_damping_factor_join |
| optimizer_dpe_stats |
| optimizer_enable_associativity |
| optimizer_enable_dynamicbitmapscan |
| optimizer_enable_dynamicindexonlyscan |
| optimizer_enable_dynamicindexscan |
| optimizer_enable_right_outer_join |
| optimizer_force_three_stage_scalar_dqa |
| optimizer_join_arity_for_associativity_commutativity |
| optimizer_join_order |
| optimizer_join_order_threshold |
| optimizer_metadata_caching |
| optimizer_nestloop_factor |
| optimizer_penalize_broadcast_threshold |
| optimizer_push_group_by_below_setop_threshold |
| optimizer_sort_factor |
| (43 rows) |
| |
| -- Runtime-computed GUCs should be part of the preset category. |
| SELECT name FROM tab_settings_flags |
| WHERE NOT category = 'Preset Options' AND runtime_computed |
| ORDER BY 1; |
| name |
| ------ |
| (0 rows) |
| |
| -- Preset GUCs are flagged as NOT_IN_SAMPLE. |
| SELECT name FROM tab_settings_flags |
| WHERE category = 'Preset Options' AND NOT not_in_sample |
| ORDER BY 1; |
| name |
| ------------------------ |
| gp_max_partition_level |
| (1 row) |
| |
| -- NO_RESET implies NO_RESET_ALL. |
| SELECT name FROM tab_settings_flags |
| WHERE no_reset AND NOT no_reset_all |
| ORDER BY 1; |
| name |
| ------ |
| (0 rows) |
| |
| DROP TABLE tab_settings_flags; |
