Explanation on the GUC "standard_conforming_strings“ in VMware Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article is to explain the GUC "standard_conforming_strings". The GUC "standard_conforming_strings" is set to "on" by default in Greenplum Data Base (GPDB) 5.

In most cases, you do not need to care about this. However, if you are interested in the effect of this GUC on upgrading from GPDB 4.3.x, please refer to this article.

Environment

Product Version: 5.25

Resolution

The main effect of setting this GUC to on is to ignore the escape string '\'.

For example, if we set standard_conforming_strings to 'off', the query SELECT '\\A' returns '\A' as the escape string '\' is recognized. If you set it to on, the query SELECT '\\A' just returns '\\A'. In that case, if you want to get the same result, we can modify this query to SELECT E'\\A'.

Below is the test:

gpadmin=# show standard_conforming_strings;
 standard_conforming_strings 
-----------------------------
 on
(1 row)

gpadmin=# select '\\a'
gpadmin-# ;
 ?column? 
----------
 \\a
(1 row)

gpadmin=# select E'\\a'                                                                                                   ;
 ?column? 
----------
 \a
(1 row)

gpadmin=# set standard_conforming_strings=off;
SET
gpadmin=# select '\\a';
WARNING:  nonstandard use of \\ in a string literal
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
 ?column? 
----------
 \a
(1 row)

gpadmin=# select E'\\a';
 ?column? 
----------
 \a
(1 row)

Since standard_conforming_strings is set to 'on' from 'off' in GPDB 5, you might be worried about it before the upgrade if you have many jobs using the escape string '\' .

Workaround

One workaround is to change the GUC to 'off' temporarily after the upgrade, and set it back to 'on' when all jobs are modified.