PostgreSQL setting parameters are often called GUC, short for the name of the ‘Grand Unified Configuration scheme’ project that introduced them.
Since YugabyteDB reuses PostgreSQL you will find all PostgreSQL settings in YSQL – but they are not all relevant to YugabyteDB.
In addition to the PostgreSQL parameters, YugabyteDB has introduced its own GUCs prefixed by “yb_”.
GUCs can be viewed via the SHOW command in YSQL.
Examples:
yugabyte=# SHOW transaction_read_only;
transaction_read_only
-----------------------
off
(1 row)
yugabyte=# SHOW yb_read_from_followers;
yb_read_from_followers
------------------------
off
(1 row)
GUCs can be set at several different levels, including database, session and user.
Examples:
yugabyte=# CREATE DATABASE hamster;
CREATE DATABASE
yugabyte=# ALTER DATABASE hamster SET yb_read_from_followers = TRUE;
ALTER DATABASE
yugabyte=# CREATE USER panda;
CREATE ROLE
yugabyte=# ALTER USER panda SET yb_read_from_followers = TRUE;
ALTER ROLE
yugabyte=# SET yb_read_from_followers = TRUE;
SET
But I learned something new today and wanted to share.
GUCs can be set within just the scope of the execution of a stored procedure!
Example:
yugabyte=# CREATE OR REPLACE FUNCTION curr_tro_rff ()
yugabyte-# RETURNS VARCHAR
yugabyte-# AS $$
yugabyte$# DECLARE
yugabyte$# curr_tro_rff_l VARCHAR;
yugabyte$# BEGIN
yugabyte$# SELECT current_setting('transaction_read_only') || ', ' || current_setting('yb_read_from_followers') INTO curr_tro_rff_l;
yugabyte$# RETURN curr_tro_rff_l;
yugabyte$# END;
yugabyte$# $$ language 'plpgsql'
yugabyte-# SET transaction_read_only TO ON
yugabyte-# SET yb_read_from_followers TO ON
yugabyte-# ;
CREATE FUNCTION
yugabyte=# BEGIN;
BEGIN
yugabyte=# SHOW transaction_read_only;
transaction_read_only
-----------------------
off
(1 row)
yugabyte=# SHOW yb_read_from_followers;
yb_read_from_followers
------------------------
off
(1 row)
yugabyte=# SELECT curr_tro_rff ();
curr_tro_rff
--------------
on, on
(1 row)
yugabyte=# SHOW transaction_read_only;
transaction_read_only
-----------------------
off
(1 row)
yugabyte=# SHOW yb_read_from_followers;
yb_read_from_followers
------------------------
off
(1 row)
yugabyte=# END;
COMMIT
That’s very cool!
Note the placement of SET commands in the stored procedures. They are out side of the BEGIN and END code block.
If the SET commands were inline with the code, the results would be different…and maybe unexpected.
Example:
yugabyte=# CREATE OR REPLACE FUNCTION curr_tro_rff ()
yugabyte-# RETURNS VARCHAR
yugabyte-# AS $$
yugabyte$# DECLARE
yugabyte$# curr_tro_rff_l VARCHAR;
yugabyte$# BEGIN
yugabyte$# SET transaction_read_only TO ON;
yugabyte$# SET yb_read_from_followers TO ON;
yugabyte$# SELECT current_setting('transaction_read_only') || ', ' || current_setting('yb_read_from_followers') INTO curr_tro_rff_l;
yugabyte$# RETURN curr_tro_rff_l;
yugabyte$# END;
yugabyte$# $$ language 'plpgsql'
yugabyte-# ;
CREATE FUNCTION
yugabyte=# BEGIN;
BEGIN
yugabyte=# SHOW transaction_read_only;
transaction_read_only
-----------------------
off
(1 row)
yugabyte=# SHOW yb_read_from_followers;
yb_read_from_followers
------------------------
off
(1 row)
yugabyte=# SELECT curr_tro_rff ();
curr_tro_rff
--------------
on, on
(1 row)
yugabyte=# SHOW transaction_read_only;
transaction_read_only
-----------------------
on
(1 row)
yugabyte=# SHOW yb_read_from_followers;
yb_read_from_followers
------------------------
on
(1 row)
yugabyte=# END;
COMMIT
In the last example, the GUC parameters retained their updated values even after the function call had ended.
Have Fun!
