Adding upstream version 16.2.upstream/16.2

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 226 insertions, 0 deletions

diff --git a/src/test/regress/expected/reloptions.out b/src/test/regress/expected/reloptions.out
new file mode 100644
index 0000000..b6aef6f
--- /dev/null
+++ b/src/test/regress/expected/reloptions.out
@@ -0,0 +1,226 @@
+-- Simple create
+CREATE TABLE reloptions_test(i INT) WITH (FiLLFaCToR=30,
+	autovacuum_enabled = false, autovacuum_analyze_scale_factor = 0.2);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+                                  reloptions                                  
+------------------------------------------------------------------------------
+ {fillfactor=30,autovacuum_enabled=false,autovacuum_analyze_scale_factor=0.2}
+(1 row)
+
+-- Fail min/max values check
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=2);
+ERROR:  value 2 out of bounds for option "fillfactor"
+DETAIL:  Valid values are between "10" and "100".
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=110);
+ERROR:  value 110 out of bounds for option "fillfactor"
+DETAIL:  Valid values are between "10" and "100".
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = -10.0);
+ERROR:  value -10.0 out of bounds for option "autovacuum_analyze_scale_factor"
+DETAIL:  Valid values are between "0.000000" and "100.000000".
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor = 110.0);
+ERROR:  value 110.0 out of bounds for option "autovacuum_analyze_scale_factor"
+DETAIL:  Valid values are between "0.000000" and "100.000000".
+-- Fail when option and namespace do not exist
+CREATE TABLE reloptions_test2(i INT) WITH (not_existing_option=2);
+ERROR:  unrecognized parameter "not_existing_option"
+CREATE TABLE reloptions_test2(i INT) WITH (not_existing_namespace.fillfactor=2);
+ERROR:  unrecognized parameter namespace "not_existing_namespace"
+-- Fail while setting improper values
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=-30.1);
+ERROR:  value -30.1 out of bounds for option "fillfactor"
+DETAIL:  Valid values are between "10" and "100".
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor='string');
+ERROR:  invalid value for integer option "fillfactor": string
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=true);
+ERROR:  invalid value for integer option "fillfactor": true
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=12);
+ERROR:  invalid value for boolean option "autovacuum_enabled": 12
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled=30.5);
+ERROR:  invalid value for boolean option "autovacuum_enabled": 30.5
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_enabled='string');
+ERROR:  invalid value for boolean option "autovacuum_enabled": string
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor='string');
+ERROR:  invalid value for floating point option "autovacuum_analyze_scale_factor": string
+CREATE TABLE reloptions_test2(i INT) WITH (autovacuum_analyze_scale_factor=true);
+ERROR:  invalid value for floating point option "autovacuum_analyze_scale_factor": true
+-- Fail if option is specified twice
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor=30, fillfactor=40);
+ERROR:  parameter "fillfactor" specified more than once
+-- Specifying name only for a non-Boolean option should fail
+CREATE TABLE reloptions_test2(i INT) WITH (fillfactor);
+ERROR:  invalid value for integer option "fillfactor": true
+-- Simple ALTER TABLE
+ALTER TABLE reloptions_test SET (fillfactor=31,
+	autovacuum_analyze_scale_factor = 0.3);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+                                  reloptions                                  
+------------------------------------------------------------------------------
+ {autovacuum_enabled=false,fillfactor=31,autovacuum_analyze_scale_factor=0.3}
+(1 row)
+
+-- Set boolean option to true without specifying value
+ALTER TABLE reloptions_test SET (autovacuum_enabled, fillfactor=32);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+                                 reloptions                                  
+-----------------------------------------------------------------------------
+ {autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true,fillfactor=32}
+(1 row)
+
+-- Check that RESET works well
+ALTER TABLE reloptions_test RESET (fillfactor);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+                          reloptions                           
+---------------------------------------------------------------
+ {autovacuum_analyze_scale_factor=0.3,autovacuum_enabled=true}
+(1 row)
+
+-- Resetting all values causes the column to become null
+ALTER TABLE reloptions_test RESET (autovacuum_enabled,
+	autovacuum_analyze_scale_factor);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass AND
+       reloptions IS NULL;
+ reloptions 
+------------
+ 
+(1 row)
+
+-- RESET fails if a value is specified
+ALTER TABLE reloptions_test RESET (fillfactor=12);
+ERROR:  RESET must not include values for parameters
+-- Test vacuum_truncate option
+DROP TABLE reloptions_test;
+CREATE TEMP TABLE reloptions_test(i INT NOT NULL, j text)
+	WITH (vacuum_truncate=false,
+	toast.vacuum_truncate=false,
+	autovacuum_enabled=false);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+                    reloptions                    
+--------------------------------------------------
+ {vacuum_truncate=false,autovacuum_enabled=false}
+(1 row)
+
+INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);
+ERROR:  null value in column "i" of relation "reloptions_test" violates not-null constraint
+DETAIL:  Failing row contains (null, null).
+-- Do an aggressive vacuum to prevent page-skipping.
+VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) reloptions_test;
+SELECT pg_relation_size('reloptions_test') > 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT reloptions FROM pg_class WHERE oid =
+	(SELECT reltoastrelid FROM pg_class
+	WHERE oid = 'reloptions_test'::regclass);
+       reloptions        
+-------------------------
+ {vacuum_truncate=false}
+(1 row)
+
+ALTER TABLE reloptions_test RESET (vacuum_truncate);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+         reloptions         
+----------------------------
+ {autovacuum_enabled=false}
+(1 row)
+
+INSERT INTO reloptions_test VALUES (1, NULL), (NULL, NULL);
+ERROR:  null value in column "i" of relation "reloptions_test" violates not-null constraint
+DETAIL:  Failing row contains (null, null).
+-- Do an aggressive vacuum to prevent page-skipping.
+VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) reloptions_test;
+SELECT pg_relation_size('reloptions_test') = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+-- Test toast.* options
+DROP TABLE reloptions_test;
+CREATE TABLE reloptions_test (s VARCHAR)
+	WITH (toast.autovacuum_vacuum_cost_delay = 23);
+SELECT reltoastrelid as toast_oid
+	FROM pg_class WHERE oid = 'reloptions_test'::regclass \gset
+SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
+            reloptions             
+-----------------------------------
+ {autovacuum_vacuum_cost_delay=23}
+(1 row)
+
+ALTER TABLE reloptions_test SET (toast.autovacuum_vacuum_cost_delay = 24);
+SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
+            reloptions             
+-----------------------------------
+ {autovacuum_vacuum_cost_delay=24}
+(1 row)
+
+ALTER TABLE reloptions_test RESET (toast.autovacuum_vacuum_cost_delay);
+SELECT reloptions FROM pg_class WHERE oid = :toast_oid;
+ reloptions 
+------------
+ 
+(1 row)
+
+-- Fail on non-existent options in toast namespace
+CREATE TABLE reloptions_test2 (i int) WITH (toast.not_existing_option = 42);
+ERROR:  unrecognized parameter "not_existing_option"
+-- Mix TOAST & heap
+DROP TABLE reloptions_test;
+CREATE TABLE reloptions_test (s VARCHAR) WITH
+	(toast.autovacuum_vacuum_cost_delay = 23,
+	autovacuum_vacuum_cost_delay = 24, fillfactor = 40);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test'::regclass;
+                   reloptions                    
+-------------------------------------------------
+ {autovacuum_vacuum_cost_delay=24,fillfactor=40}
+(1 row)
+
+SELECT reloptions FROM pg_class WHERE oid = (
+	SELECT reltoastrelid FROM pg_class WHERE oid = 'reloptions_test'::regclass);
+            reloptions             
+-----------------------------------
+ {autovacuum_vacuum_cost_delay=23}
+(1 row)
+
+--
+-- CREATE INDEX, ALTER INDEX for btrees
+--
+CREATE INDEX reloptions_test_idx ON reloptions_test (s) WITH (fillfactor=30);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
+   reloptions    
+-----------------
+ {fillfactor=30}
+(1 row)
+
+-- Fail when option and namespace do not exist
+CREATE INDEX reloptions_test_idx ON reloptions_test (s)
+	WITH (not_existing_option=2);
+ERROR:  unrecognized parameter "not_existing_option"
+CREATE INDEX reloptions_test_idx ON reloptions_test (s)
+	WITH (not_existing_ns.fillfactor=2);
+ERROR:  unrecognized parameter namespace "not_existing_ns"
+-- Check allowed ranges
+CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=1);
+ERROR:  value 1 out of bounds for option "fillfactor"
+DETAIL:  Valid values are between "10" and "100".
+CREATE INDEX reloptions_test_idx2 ON reloptions_test (s) WITH (fillfactor=130);
+ERROR:  value 130 out of bounds for option "fillfactor"
+DETAIL:  Valid values are between "10" and "100".
+-- Check ALTER
+ALTER INDEX reloptions_test_idx SET (fillfactor=40);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx'::regclass;
+   reloptions    
+-----------------
+ {fillfactor=40}
+(1 row)
+
+-- Check ALTER on empty reloption list
+CREATE INDEX reloptions_test_idx3 ON reloptions_test (s);
+ALTER INDEX reloptions_test_idx3 SET (fillfactor=40);
+SELECT reloptions FROM pg_class WHERE oid = 'reloptions_test_idx3'::regclass;
+   reloptions    
+-----------------
+ {fillfactor=40}
+(1 row)
+