Add regression tests for SET xxx

Started by Robins Tharakanover 12 years ago10 messages

tharakan@gmail.com

over 12 years ago

1 attachment(s)

Hi,

Please find attached a patch to take code-coverage of SET (SESSION / SEED /
TRANSACTION / DATESTYLE / TIME ZONE) (src/backend/commands/variable.c) from
65% to 82%.

Any and all feedback is welcome.
--
Robins Tharakan

Attachments:

regress_variable.patchapplication/octet-stream; name=regress_variable.patchDownload

diff --git a/src/test/regress/expected/variable.out b/src/test/regress/expected/variable.out
new file mode 100644
index 0000000..fa62672
--- /dev/null
+++ b/src/test/regress/expected/variable.out
@@ -0,0 +1,168 @@
+--
+-- VARIABLE (src/backend/commands/variable.c)
+--
+-- Should fail. Ensure non-existent ROLEs throw error on SET ROLE / SET SESSION AUTHORIZATION
+SET ROLE role_var1;
+ERROR:  role "role_var1" does not exist
+SET SESSION AUTHORIZATION role_var1;
+ERROR:  role "role_var1" does not exist
+/*-- SET SESSION AUTHORIZATION should throw an error when in hung transaction state
+BEGIN TRANSACTION;
+INVALID_COMMAND;
+SET SESSION AUTHORIZATION role_var1;
+ROLLBACK;*/
+BEGIN TRANSACTION;
+INVALID_COMMAND;
+ERROR:  syntax error at or near "INVALID_COMMAND"
+LINE 1: INVALID_COMMAND;
+        ^
+SET ROLE role_var1;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK;
+-- Should work. Valid values / methods used
+SET SEED = 0.5;
+SET SEED TO 0.4;
+SELECT setseed(0.5);
+ setseed 
+---------
+ 
+(1 row)
+
+-- Should fail. SET TRANSACTION NOT DEFERRABLE not possible within a Sub transaction
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
+SAVEPOINT savepoint_var1;
+SET TRANSACTION NOT DEFERRABLE;
+ERROR:  SET TRANSACTION [NOT] DEFERRABLE cannot be called within a subtransaction
+ROLLBACK TO SAVEPOINT savepoint_var1;
+ROLLBACK TRANSACTION;
+-- Should fail. SET TRANSACTION DEFERRABLE not possible within a Sub transaction
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
+SAVEPOINT savepoint_var2;
+SET TRANSACTION DEFERRABLE;
+ERROR:  SET TRANSACTION [NOT] DEFERRABLE cannot be called within a subtransaction
+ROLLBACK TO SAVEPOINT savepoint_var2;
+ROLLBACK TRANSACTION;
+-- Should fail. SET TRANSACTION DEFERRABLE not possible if not first statement of transaction;
+BEGIN TRANSACTION;
+SELECT 1;
+ ?column? 
+----------
+        1
+(1 row)
+
+SET TRANSACTION NOT DEFERRABLE;
+ERROR:  SET TRANSACTION [NOT] DEFERRABLE must be called before any query
+ROLLBACK TRANSACTION;
+-- Should work. Ensure correct messages are displayed for all TRANSACTION ISOLATION LEVELs
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ transaction_isolation 
+-----------------------
+ read committed
+(1 row)
+
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ transaction_isolation 
+-----------------------
+ read uncommitted
+(1 row)
+
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SHOW TRANSACTION ISOLATION LEVEL;
+ transaction_isolation 
+-----------------------
+ repeatable read
+(1 row)
+
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+SHOW TRANSACTION ISOLATION LEVEL;
+ transaction_isolation 
+-----------------------
+ serializable
+(1 row)
+
+ROLLBACK TRANSACTION;
+-- Should fail. Ensure that change in ISOLATION LEVEL should happen before first transaction
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SELECT 1;
+ ?column? 
+----------
+        1
+(1 row)
+
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
+SHOW TRANSACTION ISOLATION LEVEL;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK TRANSACTION;
+-- Should fail. Ensure that change in ISOLATION LEVEL should not happen within a sub-transaction
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SAVEPOINT savepoint_var3;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+ERROR:  SET TRANSACTION ISOLATION LEVEL must not be called in a subtransaction
+ROLLBACK TO SAVEPOINT savepoint_var3;
+ROLLBACK TRANSACTION;
+-- Should fail. Invalid DATESTYLE arguments
+SET DATESTYLE = ISO, SQL;
+ERROR:  invalid value for parameter "DateStyle": "iso, sql"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = SQL, ISO;
+ERROR:  invalid value for parameter "DateStyle": "sql, iso"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = ISO, POSTGRES;
+ERROR:  invalid value for parameter "DateStyle": "iso, postgres"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = POSTGRES, ISO;
+ERROR:  invalid value for parameter "DateStyle": "postgres, iso"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = ISO, GERMAN;
+ERROR:  invalid value for parameter "DateStyle": "iso, german"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = GERMAN, ISO;
+ERROR:  invalid value for parameter "DateStyle": "german, iso"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = DMY, MDY;
+ERROR:  invalid value for parameter "DateStyle": "dmy, mdy"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, DMY;
+ERROR:  invalid value for parameter "DateStyle": "ymd, dmy"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, EURO;
+ERROR:  invalid value for parameter "DateStyle": "ymd, euro"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, MDY;
+ERROR:  invalid value for parameter "DateStyle": "ymd, mdy"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, US;
+ERROR:  invalid value for parameter "DateStyle": "ymd, us"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, NONEURO;
+ERROR:  invalid value for parameter "DateStyle": "ymd, noneuro"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = ISO, DEFAULT;
+ERROR:  syntax error at or near "DEFAULT"
+LINE 1: SET DATESTYLE = ISO, DEFAULT;
+                             ^
+SET DATESTYLE = DEFAULT, ISO;
+ERROR:  syntax error at or near ","
+LINE 1: SET DATESTYLE = DEFAULT, ISO;
+                               ^
+SET DATESTYLE = invalid_keyword;
+ERROR:  invalid value for parameter "DateStyle": "invalid_keyword"
+DETAIL:  Unrecognized key word: "invalid_keyword".
+-- Should work. Valid value for SET TIMEZONE INTERVAL
+SET DATESTYLE = DEFAULT;
+-- Should work. Valid value for SET TIMEZONE INTERVAL
+SET TIME ZONE INTERVAL '+05:30' HOUR TO MINUTE;
+-- Should fail. Invalid values for INTERVAL in SET TIMEZONE INTERVAL
+SET TIME ZONE INTERVAL '1 month +05:30' HOUR TO MINUTE;
+ERROR:  invalid value for parameter "TimeZone": "INTERVAL '@ 1 mon 5 hours 30 mins'"
+DETAIL:  Cannot specify months in time zone interval.
+SET TIME ZONE INTERVAL '1 day +05:30' HOUR TO MINUTE;
+ERROR:  invalid value for parameter "TimeZone": "INTERVAL '@ 1 day 5 hours 30 mins'"
+DETAIL:  Cannot specify days in time zone interval.
+SET TIME ZONE INTERVAL '+05:30:60' HOUR;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 2af28b1..ebf61f2 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -83,7 +83,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
 # ----------
 # Another group of parallel tests
 # ----------
-test: privileges security_label collate matview
+test: privileges security_label collate matview variable
 
 # ----------
 # Another group of parallel tests
diff --git a/src/test/regress/sql/variable.sql b/src/test/regress/sql/variable.sql
new file mode 100644
index 0000000..bba4081
--- /dev/null
+++ b/src/test/regress/sql/variable.sql
@@ -0,0 +1,103 @@
+--
+-- VARIABLE (src/backend/commands/variable.c)
+--
+
+-- Should fail. Ensure non-existent ROLEs throw error on SET ROLE / SET SESSION AUTHORIZATION
+SET ROLE role_var1;
+SET SESSION AUTHORIZATION role_var1;
+
+/*-- SET SESSION AUTHORIZATION should throw an error when in hung transaction state
+BEGIN TRANSACTION;
+INVALID_COMMAND;
+SET SESSION AUTHORIZATION role_var1;
+ROLLBACK;*/
+
+
+BEGIN TRANSACTION;
+INVALID_COMMAND;
+SET ROLE role_var1;
+ROLLBACK;
+
+-- Should work. Valid values / methods used
+SET SEED = 0.5;
+SET SEED TO 0.4;
+SELECT setseed(0.5);
+
+-- Should fail. SET TRANSACTION NOT DEFERRABLE not possible within a Sub transaction
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
+SAVEPOINT savepoint_var1;
+SET TRANSACTION NOT DEFERRABLE;
+ROLLBACK TO SAVEPOINT savepoint_var1;
+ROLLBACK TRANSACTION;
+
+-- Should fail. SET TRANSACTION DEFERRABLE not possible within a Sub transaction
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
+SAVEPOINT savepoint_var2;
+SET TRANSACTION DEFERRABLE;
+ROLLBACK TO SAVEPOINT savepoint_var2;
+ROLLBACK TRANSACTION;
+
+-- Should fail. SET TRANSACTION DEFERRABLE not possible if not first statement of transaction;
+BEGIN TRANSACTION;
+SELECT 1;
+SET TRANSACTION NOT DEFERRABLE;
+ROLLBACK TRANSACTION;
+
+-- Should work. Ensure correct messages are displayed for all TRANSACTION ISOLATION LEVELs
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+
+-- Should fail. Ensure that change in ISOLATION LEVEL should happen before first transaction
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SELECT 1;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+
+-- Should fail. Ensure that change in ISOLATION LEVEL should not happen within a sub-transaction
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SAVEPOINT savepoint_var3;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+ROLLBACK TO SAVEPOINT savepoint_var3;
+ROLLBACK TRANSACTION;
+
+-- Should fail. Invalid DATESTYLE arguments
+SET DATESTYLE = ISO, SQL;
+SET DATESTYLE = SQL, ISO;
+SET DATESTYLE = ISO, POSTGRES;
+SET DATESTYLE = POSTGRES, ISO;
+SET DATESTYLE = ISO, GERMAN;
+SET DATESTYLE = GERMAN, ISO;
+SET DATESTYLE = DMY, MDY;
+SET DATESTYLE = DMY, YMD;
+SET DATESTYLE = YMD, DMY;
+SET DATESTYLE = YMD, EURO;
+SET DATESTYLE = YMD, MDY;
+SET DATESTYLE = YMD, US;
+SET DATESTYLE = YMD, NONEURO;
+SET DATESTYLE = ISO, DEFAULT;
+SET DATESTYLE = DEFAULT, ISO;
+SET DATESTYLE = invalid_keyword;
+
+-- Should work. Valid value for SET TIMEZONE INTERVAL
+SET DATESTYLE TO DEFAULT;
+
+-- Should work. Valid value for SET TIMEZONE INTERVAL
+SET TIME ZONE INTERVAL '+05:30' HOUR TO MINUTE;
+
+-- Should fail. Invalid values for INTERVAL in SET TIMEZONE INTERVAL
+SET TIME ZONE INTERVAL '1 month +05:30' HOUR TO MINUTE;
+SET TIME ZONE INTERVAL '1 day +05:30' HOUR TO MINUTE;
+SET TIME ZONE INTERVAL '+05:30:60' HOUR;
+
+

Szymon Guz

mabewlun@gmail.com

over 12 years ago

In reply to: Robins Tharakan (#1)

1 attachment(s)

Re: Add regression tests for SET xxx

On 26 May 2013 19:56, Robins Tharakan <tharakan@gmail.com> wrote:

Hi,

Please find attached a patch to take code-coverage of SET (SESSION / SEED
/ TRANSACTION / DATESTYLE / TIME ZONE) (src/backend/commands/variable.c)
from 65% to 82%.

Any and all feedback is welcome.
--
Robins Tharakan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,
the patch applies cleanly on code from trunk, however there are failing
tests, diff attached.

regards
Szymon

Attachments:

regression.diffsapplication/octet-stream; name=regression.diffsDownload

*** src/test/regress/expected/variable.out	2013-06-17 13:48:44.207807720 +0200
--- src/test/regress/results/variable.out	2013-06-17 13:49:08.479808370 +0200
***************
*** 128,133 ****
--- 128,136 ----
  SET DATESTYLE = DMY, MDY;
  ERROR:  invalid value for parameter "DateStyle": "dmy, mdy"
  DETAIL:  Conflicting "datestyle" specifications.
+ SET DATESTYLE = DMY, YMD;
+ ERROR:  invalid value for parameter "DateStyle": "dmy, ymd"
+ DETAIL:  Conflicting "datestyle" specifications.
  SET DATESTYLE = YMD, DMY;
  ERROR:  invalid value for parameter "DateStyle": "ymd, dmy"
  DETAIL:  Conflicting "datestyle" specifications.
***************
*** 155,161 ****
  ERROR:  invalid value for parameter "DateStyle": "invalid_keyword"
  DETAIL:  Unrecognized key word: "invalid_keyword".
  -- Should work. Valid value for SET TIMEZONE INTERVAL
! SET DATESTYLE = DEFAULT;
  -- Should work. Valid value for SET TIMEZONE INTERVAL
  SET TIME ZONE INTERVAL '+05:30' HOUR TO MINUTE;
  -- Should fail. Invalid values for INTERVAL in SET TIMEZONE INTERVAL
--- 158,164 ----
  ERROR:  invalid value for parameter "DateStyle": "invalid_keyword"
  DETAIL:  Unrecognized key word: "invalid_keyword".
  -- Should work. Valid value for SET TIMEZONE INTERVAL
! SET DATESTYLE TO DEFAULT;
  -- Should work. Valid value for SET TIMEZONE INTERVAL
  SET TIME ZONE INTERVAL '+05:30' HOUR TO MINUTE;
  -- Should fail. Invalid values for INTERVAL in SET TIMEZONE INTERVAL

======================================================================

Robins Tharakan

tharakan@gmail.com

over 12 years ago

In reply to: Szymon Guz (#2)

1 attachment(s)

Re: Add regression tests for SET xxx

Thanks !

PFA the updated patch. Also remove a trailing whitespace at the end of SQL
script.

--
Robins Tharakan

On 17 June 2013 17:29, Szymon Guz <mabewlun@gmail.com> wrote:

Show quoted text

On 26 May 2013 19:56, Robins Tharakan <tharakan@gmail.com> wrote:

Hi,

Please find attached a patch to take code-coverage of SET (SESSION / SEED
/ TRANSACTION / DATESTYLE / TIME ZONE) (src/backend/commands/variable.c)
from 65% to 82%.

Any and all feedback is welcome.
--
Robins Tharakan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,
the patch applies cleanly on code from trunk, however there are failing
tests, diff attached.

regards
Szymon

Attachments:

regress_variable_v3.patchapplication/octet-stream; name=regress_variable_v3.patchDownload

diff --git a/src/test/regress/expected/variable.out b/src/test/regress/expected/variable.out
new file mode 100644
index 0000000..54ff2ad
--- /dev/null
+++ b/src/test/regress/expected/variable.out
@@ -0,0 +1,171 @@
+--
+-- VARIABLE (src/backend/commands/variable.c)
+--
+-- Should fail. Ensure non-existent ROLEs throw error on SET ROLE / SET SESSION AUTHORIZATION
+SET ROLE role_var1;
+ERROR:  role "role_var1" does not exist
+SET SESSION AUTHORIZATION role_var1;
+ERROR:  role "role_var1" does not exist
+/*-- SET SESSION AUTHORIZATION should throw an error when in hung transaction state
+BEGIN TRANSACTION;
+INVALID_COMMAND;
+SET SESSION AUTHORIZATION role_var1;
+ROLLBACK;*/
+BEGIN TRANSACTION;
+INVALID_COMMAND;
+ERROR:  syntax error at or near "INVALID_COMMAND"
+LINE 1: INVALID_COMMAND;
+        ^
+SET ROLE role_var1;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK;
+-- Should work. Valid values / methods used
+SET SEED = 0.5;
+SET SEED TO 0.4;
+SELECT setseed(0.5);
+ setseed 
+---------
+ 
+(1 row)
+
+-- Should fail. SET TRANSACTION NOT DEFERRABLE not possible within a Sub transaction
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
+SAVEPOINT savepoint_var1;
+SET TRANSACTION NOT DEFERRABLE;
+ERROR:  SET TRANSACTION [NOT] DEFERRABLE cannot be called within a subtransaction
+ROLLBACK TO SAVEPOINT savepoint_var1;
+ROLLBACK TRANSACTION;
+-- Should fail. SET TRANSACTION DEFERRABLE not possible within a Sub transaction
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
+SAVEPOINT savepoint_var2;
+SET TRANSACTION DEFERRABLE;
+ERROR:  SET TRANSACTION [NOT] DEFERRABLE cannot be called within a subtransaction
+ROLLBACK TO SAVEPOINT savepoint_var2;
+ROLLBACK TRANSACTION;
+-- Should fail. SET TRANSACTION DEFERRABLE not possible if not first statement of transaction;
+BEGIN TRANSACTION;
+SELECT 1;
+ ?column? 
+----------
+        1
+(1 row)
+
+SET TRANSACTION NOT DEFERRABLE;
+ERROR:  SET TRANSACTION [NOT] DEFERRABLE must be called before any query
+ROLLBACK TRANSACTION;
+-- Should work. Ensure correct messages are displayed for all TRANSACTION ISOLATION LEVELs
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ transaction_isolation 
+-----------------------
+ read committed
+(1 row)
+
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ transaction_isolation 
+-----------------------
+ read uncommitted
+(1 row)
+
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SHOW TRANSACTION ISOLATION LEVEL;
+ transaction_isolation 
+-----------------------
+ repeatable read
+(1 row)
+
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+SHOW TRANSACTION ISOLATION LEVEL;
+ transaction_isolation 
+-----------------------
+ serializable
+(1 row)
+
+ROLLBACK TRANSACTION;
+-- Should fail. Ensure that change in ISOLATION LEVEL should happen before first transaction
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SELECT 1;
+ ?column? 
+----------
+        1
+(1 row)
+
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
+SHOW TRANSACTION ISOLATION LEVEL;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK TRANSACTION;
+-- Should fail. Ensure that change in ISOLATION LEVEL should not happen within a sub-transaction
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SAVEPOINT savepoint_var3;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+ERROR:  SET TRANSACTION ISOLATION LEVEL must not be called in a subtransaction
+ROLLBACK TO SAVEPOINT savepoint_var3;
+ROLLBACK TRANSACTION;
+-- Should fail. Invalid DATESTYLE arguments
+SET DATESTYLE = ISO, SQL;
+ERROR:  invalid value for parameter "DateStyle": "iso, sql"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = SQL, ISO;
+ERROR:  invalid value for parameter "DateStyle": "sql, iso"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = ISO, POSTGRES;
+ERROR:  invalid value for parameter "DateStyle": "iso, postgres"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = POSTGRES, ISO;
+ERROR:  invalid value for parameter "DateStyle": "postgres, iso"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = ISO, GERMAN;
+ERROR:  invalid value for parameter "DateStyle": "iso, german"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = GERMAN, ISO;
+ERROR:  invalid value for parameter "DateStyle": "german, iso"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = DMY, MDY;
+ERROR:  invalid value for parameter "DateStyle": "dmy, mdy"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = DMY, YMD;
+ERROR:  invalid value for parameter "DateStyle": "dmy, ymd"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, DMY;
+ERROR:  invalid value for parameter "DateStyle": "ymd, dmy"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, EURO;
+ERROR:  invalid value for parameter "DateStyle": "ymd, euro"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, MDY;
+ERROR:  invalid value for parameter "DateStyle": "ymd, mdy"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, US;
+ERROR:  invalid value for parameter "DateStyle": "ymd, us"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, NONEURO;
+ERROR:  invalid value for parameter "DateStyle": "ymd, noneuro"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = ISO, DEFAULT;
+ERROR:  syntax error at or near "DEFAULT"
+LINE 1: SET DATESTYLE = ISO, DEFAULT;
+                             ^
+SET DATESTYLE = DEFAULT, ISO;
+ERROR:  syntax error at or near ","
+LINE 1: SET DATESTYLE = DEFAULT, ISO;
+                               ^
+SET DATESTYLE = invalid_keyword;
+ERROR:  invalid value for parameter "DateStyle": "invalid_keyword"
+DETAIL:  Unrecognized key word: "invalid_keyword".
+-- Should work. Valid value for SET TIMEZONE INTERVAL
+SET DATESTYLE TO DEFAULT;
+-- Should work. Valid value for SET TIMEZONE INTERVAL
+SET TIME ZONE INTERVAL '+05:30' HOUR TO MINUTE;
+-- Should fail. Invalid values for INTERVAL in SET TIMEZONE INTERVAL
+SET TIME ZONE INTERVAL '1 month +05:30' HOUR TO MINUTE;
+ERROR:  invalid value for parameter "TimeZone": "INTERVAL '@ 1 mon 5 hours 30 mins'"
+DETAIL:  Cannot specify months in time zone interval.
+SET TIME ZONE INTERVAL '1 day +05:30' HOUR TO MINUTE;
+ERROR:  invalid value for parameter "TimeZone": "INTERVAL '@ 1 day 5 hours 30 mins'"
+DETAIL:  Cannot specify days in time zone interval.
+SET TIME ZONE INTERVAL '+05:30:60' HOUR;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 2af28b1..ebf61f2 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -83,7 +83,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
 # ----------
 # Another group of parallel tests
 # ----------
-test: privileges security_label collate matview
+test: privileges security_label collate matview variable
 
 # ----------
 # Another group of parallel tests
diff --git a/src/test/regress/sql/variable.sql b/src/test/regress/sql/variable.sql
new file mode 100644
index 0000000..b521448
--- /dev/null
+++ b/src/test/regress/sql/variable.sql
@@ -0,0 +1,101 @@
+--
+-- VARIABLE (src/backend/commands/variable.c)
+--
+
+-- Should fail. Ensure non-existent ROLEs throw error on SET ROLE / SET SESSION AUTHORIZATION
+SET ROLE role_var1;
+SET SESSION AUTHORIZATION role_var1;
+
+/*-- SET SESSION AUTHORIZATION should throw an error when in hung transaction state
+BEGIN TRANSACTION;
+INVALID_COMMAND;
+SET SESSION AUTHORIZATION role_var1;
+ROLLBACK;*/
+
+
+BEGIN TRANSACTION;
+INVALID_COMMAND;
+SET ROLE role_var1;
+ROLLBACK;
+
+-- Should work. Valid values / methods used
+SET SEED = 0.5;
+SET SEED TO 0.4;
+SELECT setseed(0.5);
+
+-- Should fail. SET TRANSACTION NOT DEFERRABLE not possible within a Sub transaction
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
+SAVEPOINT savepoint_var1;
+SET TRANSACTION NOT DEFERRABLE;
+ROLLBACK TO SAVEPOINT savepoint_var1;
+ROLLBACK TRANSACTION;
+
+-- Should fail. SET TRANSACTION DEFERRABLE not possible within a Sub transaction
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
+SAVEPOINT savepoint_var2;
+SET TRANSACTION DEFERRABLE;
+ROLLBACK TO SAVEPOINT savepoint_var2;
+ROLLBACK TRANSACTION;
+
+-- Should fail. SET TRANSACTION DEFERRABLE not possible if not first statement of transaction;
+BEGIN TRANSACTION;
+SELECT 1;
+SET TRANSACTION NOT DEFERRABLE;
+ROLLBACK TRANSACTION;
+
+-- Should work. Ensure correct messages are displayed for all TRANSACTION ISOLATION LEVELs
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+
+-- Should fail. Ensure that change in ISOLATION LEVEL should happen before first transaction
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SELECT 1;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+
+-- Should fail. Ensure that change in ISOLATION LEVEL should not happen within a sub-transaction
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SAVEPOINT savepoint_var3;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+ROLLBACK TO SAVEPOINT savepoint_var3;
+ROLLBACK TRANSACTION;
+
+-- Should fail. Invalid DATESTYLE arguments
+SET DATESTYLE = ISO, SQL;
+SET DATESTYLE = SQL, ISO;
+SET DATESTYLE = ISO, POSTGRES;
+SET DATESTYLE = POSTGRES, ISO;
+SET DATESTYLE = ISO, GERMAN;
+SET DATESTYLE = GERMAN, ISO;
+SET DATESTYLE = DMY, MDY;
+SET DATESTYLE = DMY, YMD;
+SET DATESTYLE = YMD, DMY;
+SET DATESTYLE = YMD, EURO;
+SET DATESTYLE = YMD, MDY;
+SET DATESTYLE = YMD, US;
+SET DATESTYLE = YMD, NONEURO;
+SET DATESTYLE = ISO, DEFAULT;
+SET DATESTYLE = DEFAULT, ISO;
+SET DATESTYLE = invalid_keyword;
+
+-- Should work. Valid value for SET TIMEZONE INTERVAL
+SET DATESTYLE TO DEFAULT;
+
+-- Should work. Valid value for SET TIMEZONE INTERVAL
+SET TIME ZONE INTERVAL '+05:30' HOUR TO MINUTE;
+
+-- Should fail. Invalid values for INTERVAL in SET TIMEZONE INTERVAL
+SET TIME ZONE INTERVAL '1 month +05:30' HOUR TO MINUTE;
+SET TIME ZONE INTERVAL '1 day +05:30' HOUR TO MINUTE;
+SET TIME ZONE INTERVAL '+05:30:60' HOUR;

Szymon Guz

mabewlun@gmail.com

over 12 years ago

In reply to: Robins Tharakan (#3)

Re: Add regression tests for SET xxx

On 18 June 2013 02:33, Robins Tharakan <tharakan@gmail.com> wrote:

Thanks !

PFA the updated patch. Also remove a trailing whitespace at the end of SQL
script.

--
Robins Tharakan

On 17 June 2013 17:29, Szymon Guz <mabewlun@gmail.com> wrote:

On 26 May 2013 19:56, Robins Tharakan <tharakan@gmail.com> wrote:

Hi,

Please find attached a patch to take code-coverage of SET (SESSION /
SEED / TRANSACTION / DATESTYLE / TIME ZONE) (
src/backend/commands/variable.c) from 65% to 82%.

Any and all feedback is welcome.
--
Robins Tharakan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hi,
the patch applies cleanly on code from trunk, however there are failing
tests, diff attached.

regards
Szymon

Hi,
I've checked the patch. Applies cleanly. Tests pass this time :)

Could you add me as a reviewer to commitfest website, set this patch a
reviewed and add this email to the patch history?
I cannot login to the commitfest app, there is some bug with that.

thanks,
Szymon Guz

Michael Paquier

michael.paquier@gmail.com

over 12 years ago

In reply to: Szymon Guz (#4)

Re: Add regression tests for SET xxx

On Tue, Jun 18, 2013 at 7:01 PM, Szymon Guz <mabewlun@gmail.com> wrote:

Could you add me as a reviewer to commitfest website, set this patch a
reviewed and add this email to the patch history?
I cannot login to the commitfest app, there is some bug with that.

You should be able to do it yourself by creating a community account
in postgresql.org.
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Szymon Guz

mabewlun@gmail.com

over 12 years ago

In reply to: Michael Paquier (#5)

Re: Add regression tests for SET xxx

On 18 June 2013 13:10, Michael Paquier <michael.paquier@gmail.com> wrote:

On Tue, Jun 18, 2013 at 7:01 PM, Szymon Guz <mabewlun@gmail.com> wrote:

Could you add me as a reviewer to commitfest website, set this patch a
reviewed and add this email to the patch history?
I cannot login to the commitfest app, there is some bug with that.

You should be able to do it yourself by creating a community account
in postgresql.org.
--
Michael

Yea, I know. Unfortunately there is a bug and currently you cannot login to
commitfest using a new account, or old, if you changed password like I did.
I've got a bug confirmation from Magnus on pgsql-www list.

thanks,
Szymon

Kevin Grittner

kgrittn@ymail.com

over 12 years ago

In reply to: Szymon Guz (#4)

Re: Add regression tests for SET xxx

Szymon Guz <mabewlun@gmail.com> wrote:

I've checked the patch. Applies cleanly. Tests pass this time :)

Could you add me as a reviewer to commitfest website, set this
patch a reviewed and add this email to the patch history?
I cannot login to the commitfest app, there is some bug with
that.

It sounded like you felt this was Ready for Committer, so I set it
that way. Let me know if you don't think it's to that point yet.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Szymon Guz

mabewlun@gmail.com

over 12 years ago

In reply to: Kevin Grittner (#7)

Re: Add regression tests for SET xxx

On 18 June 2013 17:29, Kevin Grittner <kgrittn@ymail.com> wrote:

Szymon Guz <mabewlun@gmail.com> wrote:

I've checked the patch. Applies cleanly. Tests pass this time :)

Could you add me as a reviewer to commitfest website, set this
patch a reviewed and add this email to the patch history?
I cannot login to the commitfest app, there is some bug with
that.

It sounded like you felt this was Ready for Committer, so I set it
that way. Let me know if you don't think it's to that point yet.

Hi Kevin,
yes, that's what I was thinking about.

Thanks,
Szymon

Robins Tharakan

tharakan@gmail.com

over 12 years ago

In reply to: Szymon Guz (#4)

1 attachment(s)

Re: Add regression tests for SET xxx

On 18 June 2013 05:01, Szymon Guz <mabewlun@gmail.com> wrote:

I've checked the patch. Applies cleanly. Tests pass this time :)

Updated the patch:
- Updated ROLE names as per Robert's feedback (regress_xxx)
- Added test to serial_schedule

--
Robins Tharakan

Attachments:

regress_variable_v5.patchapplication/octet-stream; name=regress_variable_v5.patchDownload

diff --git a/src/test/regress/expected/variable.out b/src/test/regress/expected/variable.out
new file mode 100644
index 0000000..205425e
--- /dev/null
+++ b/src/test/regress/expected/variable.out
@@ -0,0 +1,176 @@
+--
+-- VARIABLE (src/backend/commands/variable.c)
+--
+-- Should fail. Ensure non-existent ROLEs throw error on SET ROLE / SET SESSION AUTHORIZATION
+SET ROLE regress_role_var1;
+ERROR:  role "regress_role_var1" does not exist
+SET SESSION AUTHORIZATION regress_role_var1;
+ERROR:  role "regress_role_var1" does not exist
+-- SET SESSION AUTHORIZATION should throw an error when in hung transaction state
+BEGIN TRANSACTION;
+INVALID_COMMAND;
+ERROR:  syntax error at or near "INVALID_COMMAND"
+LINE 1: INVALID_COMMAND;
+        ^
+SET SESSION AUTHORIZATION regress_role_var1;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK;
+-- SET ROLE should throw an error when in hung transaction state
+BEGIN TRANSACTION;
+INVALID_COMMAND;
+ERROR:  syntax error at or near "INVALID_COMMAND"
+LINE 1: INVALID_COMMAND;
+        ^
+SET ROLE regress_role_var1;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK;
+-- Should work. Valid values / methods used
+SET SEED = 0.5;
+SET SEED TO 0.4;
+SELECT setseed(0.5);
+ setseed 
+---------
+ 
+(1 row)
+
+-- Should fail. SET TRANSACTION NOT DEFERRABLE not possible within a Sub transaction
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
+SAVEPOINT savepoint_var1;
+SET TRANSACTION NOT DEFERRABLE;
+ERROR:  SET TRANSACTION [NOT] DEFERRABLE cannot be called within a subtransaction
+ROLLBACK TO SAVEPOINT savepoint_var1;
+ROLLBACK TRANSACTION;
+-- Should fail. SET TRANSACTION DEFERRABLE not possible within a Sub transaction
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
+SAVEPOINT savepoint_var2;
+SET TRANSACTION DEFERRABLE;
+ERROR:  SET TRANSACTION [NOT] DEFERRABLE cannot be called within a subtransaction
+ROLLBACK TO SAVEPOINT savepoint_var2;
+ROLLBACK TRANSACTION;
+-- Should fail. SET TRANSACTION DEFERRABLE not possible if not first statement of transaction;
+BEGIN TRANSACTION;
+SELECT 1;
+ ?column? 
+----------
+        1
+(1 row)
+
+SET TRANSACTION NOT DEFERRABLE;
+ERROR:  SET TRANSACTION [NOT] DEFERRABLE must be called before any query
+ROLLBACK TRANSACTION;
+-- Should work. Ensure correct messages are displayed for all TRANSACTION ISOLATION LEVELs
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ transaction_isolation 
+-----------------------
+ read committed
+(1 row)
+
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ transaction_isolation 
+-----------------------
+ read uncommitted
+(1 row)
+
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SHOW TRANSACTION ISOLATION LEVEL;
+ transaction_isolation 
+-----------------------
+ repeatable read
+(1 row)
+
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+SHOW TRANSACTION ISOLATION LEVEL;
+ transaction_isolation 
+-----------------------
+ serializable
+(1 row)
+
+ROLLBACK TRANSACTION;
+-- Should fail. Ensure that change in ISOLATION LEVEL should happen before first transaction
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SELECT 1;
+ ?column? 
+----------
+        1
+(1 row)
+
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
+SHOW TRANSACTION ISOLATION LEVEL;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK TRANSACTION;
+-- Should fail. Ensure that change in ISOLATION LEVEL should not happen within a sub-transaction
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SAVEPOINT savepoint_var3;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+ERROR:  SET TRANSACTION ISOLATION LEVEL must not be called in a subtransaction
+ROLLBACK TO SAVEPOINT savepoint_var3;
+ROLLBACK TRANSACTION;
+-- Should fail. Invalid DATESTYLE arguments
+SET DATESTYLE = ISO, SQL;
+ERROR:  invalid value for parameter "DateStyle": "iso, sql"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = SQL, ISO;
+ERROR:  invalid value for parameter "DateStyle": "sql, iso"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = ISO, POSTGRES;
+ERROR:  invalid value for parameter "DateStyle": "iso, postgres"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = POSTGRES, ISO;
+ERROR:  invalid value for parameter "DateStyle": "postgres, iso"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = ISO, GERMAN;
+ERROR:  invalid value for parameter "DateStyle": "iso, german"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = GERMAN, ISO;
+ERROR:  invalid value for parameter "DateStyle": "german, iso"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = DMY, MDY;
+ERROR:  invalid value for parameter "DateStyle": "dmy, mdy"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = DMY, YMD;
+ERROR:  invalid value for parameter "DateStyle": "dmy, ymd"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, DMY;
+ERROR:  invalid value for parameter "DateStyle": "ymd, dmy"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, EURO;
+ERROR:  invalid value for parameter "DateStyle": "ymd, euro"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, MDY;
+ERROR:  invalid value for parameter "DateStyle": "ymd, mdy"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, US;
+ERROR:  invalid value for parameter "DateStyle": "ymd, us"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = YMD, NONEURO;
+ERROR:  invalid value for parameter "DateStyle": "ymd, noneuro"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = ISO, DEFAULT;
+ERROR:  syntax error at or near "DEFAULT"
+LINE 1: SET DATESTYLE = ISO, DEFAULT;
+                             ^
+SET DATESTYLE = DEFAULT, ISO;
+ERROR:  syntax error at or near ","
+LINE 1: SET DATESTYLE = DEFAULT, ISO;
+                               ^
+SET DATESTYLE = invalid_keyword;
+ERROR:  invalid value for parameter "DateStyle": "invalid_keyword"
+DETAIL:  Unrecognized key word: "invalid_keyword".
+-- Should work. Valid value for SET TIMEZONE INTERVAL
+SET DATESTYLE TO DEFAULT;
+-- Should work. Valid value for SET TIMEZONE INTERVAL
+SET TIME ZONE INTERVAL '+05:30' HOUR TO MINUTE;
+-- Should fail. Invalid values for INTERVAL in SET TIMEZONE INTERVAL
+SET TIME ZONE INTERVAL '1 month +05:30' HOUR TO MINUTE;
+ERROR:  invalid value for parameter "TimeZone": "INTERVAL '@ 1 mon 5 hours 30 mins'"
+DETAIL:  Cannot specify months in time zone interval.
+SET TIME ZONE INTERVAL '1 day +05:30' HOUR TO MINUTE;
+ERROR:  invalid value for parameter "TimeZone": "INTERVAL '@ 1 day 5 hours 30 mins'"
+DETAIL:  Cannot specify days in time zone interval.
+SET TIME ZONE INTERVAL '+05:30:60' HOUR;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 3e6b306..b3bf57f 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -83,7 +83,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
 # ----------
 # Another group of parallel tests
 # ----------
-test: privileges security_label collate matview
+test: privileges security_label collate matview variable
 
 # ----------
 # Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 3ad289f..cd23fc5 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -139,3 +139,4 @@ test: largeobject
 test: with
 test: xml
 test: stats
+test: variable
diff --git a/src/test/regress/sql/variable.sql b/src/test/regress/sql/variable.sql
new file mode 100644
index 0000000..0071fe0
--- /dev/null
+++ b/src/test/regress/sql/variable.sql
@@ -0,0 +1,101 @@
+--
+-- VARIABLE (src/backend/commands/variable.c)
+--
+
+-- Should fail. Ensure non-existent ROLEs throw error on SET ROLE / SET SESSION AUTHORIZATION
+SET ROLE regress_role_var1;
+SET SESSION AUTHORIZATION regress_role_var1;
+
+-- SET SESSION AUTHORIZATION should throw an error when in hung transaction state
+BEGIN TRANSACTION;
+INVALID_COMMAND;
+SET SESSION AUTHORIZATION regress_role_var1;
+ROLLBACK;
+
+-- SET ROLE should throw an error when in hung transaction state
+BEGIN TRANSACTION;
+INVALID_COMMAND;
+SET ROLE regress_role_var1;
+ROLLBACK;
+
+-- Should work. Valid values / methods used
+SET SEED = 0.5;
+SET SEED TO 0.4;
+SELECT setseed(0.5);
+
+-- Should fail. SET TRANSACTION NOT DEFERRABLE not possible within a Sub transaction
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
+SAVEPOINT savepoint_var1;
+SET TRANSACTION NOT DEFERRABLE;
+ROLLBACK TO SAVEPOINT savepoint_var1;
+ROLLBACK TRANSACTION;
+
+-- Should fail. SET TRANSACTION DEFERRABLE not possible within a Sub transaction
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;
+SAVEPOINT savepoint_var2;
+SET TRANSACTION DEFERRABLE;
+ROLLBACK TO SAVEPOINT savepoint_var2;
+ROLLBACK TRANSACTION;
+
+-- Should fail. SET TRANSACTION DEFERRABLE not possible if not first statement of transaction;
+BEGIN TRANSACTION;
+SELECT 1;
+SET TRANSACTION NOT DEFERRABLE;
+ROLLBACK TRANSACTION;
+
+-- Should work. Ensure correct messages are displayed for all TRANSACTION ISOLATION LEVELs
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+
+-- Should fail. Ensure that change in ISOLATION LEVEL should happen before first transaction
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SELECT 1;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+SHOW TRANSACTION ISOLATION LEVEL;
+ROLLBACK TRANSACTION;
+
+-- Should fail. Ensure that change in ISOLATION LEVEL should not happen within a sub-transaction
+BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SAVEPOINT savepoint_var3;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+ROLLBACK TO SAVEPOINT savepoint_var3;
+ROLLBACK TRANSACTION;
+
+-- Should fail. Invalid DATESTYLE arguments
+SET DATESTYLE = ISO, SQL;
+SET DATESTYLE = SQL, ISO;
+SET DATESTYLE = ISO, POSTGRES;
+SET DATESTYLE = POSTGRES, ISO;
+SET DATESTYLE = ISO, GERMAN;
+SET DATESTYLE = GERMAN, ISO;
+SET DATESTYLE = DMY, MDY;
+SET DATESTYLE = DMY, YMD;
+SET DATESTYLE = YMD, DMY;
+SET DATESTYLE = YMD, EURO;
+SET DATESTYLE = YMD, MDY;
+SET DATESTYLE = YMD, US;
+SET DATESTYLE = YMD, NONEURO;
+SET DATESTYLE = ISO, DEFAULT;
+SET DATESTYLE = DEFAULT, ISO;
+SET DATESTYLE = invalid_keyword;
+
+-- Should work. Valid value for SET TIMEZONE INTERVAL
+SET DATESTYLE TO DEFAULT;
+
+-- Should work. Valid value for SET TIMEZONE INTERVAL
+SET TIME ZONE INTERVAL '+05:30' HOUR TO MINUTE;
+
+-- Should fail. Invalid values for INTERVAL in SET TIMEZONE INTERVAL
+SET TIME ZONE INTERVAL '1 month +05:30' HOUR TO MINUTE;
+SET TIME ZONE INTERVAL '1 day +05:30' HOUR TO MINUTE;
+SET TIME ZONE INTERVAL '+05:30:60' HOUR;

#10

Robert Haas

robertmhaas@gmail.com

over 12 years ago

In reply to: Robins Tharakan (#9)

Re: Add regression tests for SET xxx

On Sun, Jul 7, 2013 at 1:15 PM, Robins Tharakan <tharakan@gmail.com> wrote:

Updated the patch:
- Updated ROLE names as per Robert's feedback (regress_xxx)
- Added test to serial_schedule

Please see my comments on the LOCK tests related to these points and
update accordingly.

+BEGIN TRANSACTION;
+INVALID_COMMAND;
+ERROR:  syntax error at or near "INVALID_COMMAND"
+LINE 1: INVALID_COMMAND;
+        ^
+SET SESSION AUTHORIZATION regress_role_var1;
+ERROR:  current transaction is aborted, commands ignored until end of
transaction block

We have a test for this error message in the "transactions" test, so I
don't think we need another one here. This error is thrown at a very
high level in e.g. exec_simple_query. The only dependence on the
particular command is in IsTransactionExitStmt() - IOW, there's
nothing special about SET, and if we're going to test this for SET
ROLE and SET SESSION AUTHORIZATION, then why not for every single
command we have?

+SET DATESTYLE = ISO, SQL;
+ERROR:  invalid value for parameter "DateStyle": "iso, sql"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = SQL, ISO;
+ERROR:  invalid value for parameter "DateStyle": "sql, iso"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = ISO, POSTGRES;
+ERROR:  invalid value for parameter "DateStyle": "iso, postgres"
+DETAIL:  Conflicting "datestyle" specifications.
+SET DATESTYLE = POSTGRES, ISO;

...et cetera, ad nauseum. While there's something to be said for
completeness, the chances that future maintainers of this code will
update these regression tests with all the new permutations every time
new options are added seem low. I recommend testing a representative
sample of three of these, or so, and skipping the rest.

+SET TIME ZONE INTERVAL '1 month +05:30' HOUR TO MINUTE;
+ERROR:  invalid value for parameter "TimeZone": "INTERVAL '@ 1 mon 5
hours 30 mins'"

Wow, what a terrible error message. This is not your patch's fault,
but maybe we should look at improving that?

I am inclined to think that these tests should be split up and moved
into the files where similar things are being tested already. The SET
TIME ZONE tests probably belong in horology.sql, the SET DATESTYLE
tests with the related tests in interval.sql, and much of the other
stuff in transactions.sql.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers