Force PostgreSQL to use a different schema in functions
I wanted to make an update function for a schema in a PG SQL database. Below is the test function. It doesn't work because it should never raise a notification, but it will do it on startup test_schema_update('second')
.
CREATE OR REPLACE FUNCTION test_schema_update(my_schema_name VARCHAR(200))
RETURNS void AS
$__marker__$
DECLARE
actualValue varchar(1000);
testValue varchar(1000);
BEGIN
EXECUTE 'SET search_path TO ' || quote_ident(my_schema_name);
testValue := (SELECT max(value) FROM setting WHERE settingkey = 'libraryname');
EXECUTE ('SELECT max(value) FROM setting WHERE settingkey = ''libraryname''')
INTO actualValue;
IF (actualValue != testValue)
THEN
RAISE NOTICE '% != %', actualValue, testValue;
RAISE INFO 'Schema was: %', current_schema();
END IF;
RESET search_path;
END;
$__marker__$ LANGUAGE plpgsql;
test_schema_update('first');
test_schema_update('second');
The problem is PG SQL appears to parse statements SELECT
only once per session and then tables are bound to a specific schema. I wonder what you get Schema was: second
.
So, is there a way to parse the reset, SELECT
or some other way to get around this?
Side note: the whole schema creation function ( ALTER TABLE
, CREATE TABLE
...) works fine. It seems that only affect data manipulation functions ( SELECT
, INSERT
, UPDATE
).
Bypass
Before:
IF (
SELECT max(id) FROM dimtime
)
THEN
INSERT INTO dimtime SELECT * FROM public.src_dimtime;
END IF;
After:
EXECUTE ('
SELECT max(id) FROM dimtime
')
INTO testInt;
IF (testInt IS NULL)
THEN
EXECUTE 'INSERT INTO dimtime SELECT * FROM public.src_dimtime';
END IF;
Edit . The problem occurs in PostgreSQL 9.2, but does not seem to occur in 9.3. Maybe this has been fixed?
source to share
This behavior is to be expected. This is because PL / pgSQL uses cache scheduling for SQL statements using standard prepared statements .
Since each SQL statement and command is first executed in a function, the PL / pgSQL interpreter creates a prepared execution plan (using the SPI dispatcher
SPI_prepare
andSPI_saveplan
). subsequent visits to this expression or command to reuse the prepared plan.
This is also the reason why plpgsql functions are often faster than simple SQL functions for complex operations:
Prepared statements persist for the lifetime of the session , not just transactions (but not valid when changing underlying objects, this is safe when accessed simultaneously). Documentation again:
Once PL / pgSQL has executed an execution plan for a specific command in a function, it will reuse that plan for the life of the database connection . This is usually a performance benefit, but it can lead to some problems if you dynamically change the database schema.
The bold accent is mine.
If you want to "change" the schema of a table name, you will indeed reference a completely different table and have to use dynamic SQL with EXECUTE
that generates a new schedule every time (with all the advantages and disadvantages):
Because PL / pgSQL stores execution plans in this way, SQL statements that directly in a PL / pgSQL function must reference the same tables and columns on each execution; that is, you cannot use a parameter as a table or column name in an SQL command. To work around this, you can create dynamic commands using PL / pgSQL
EXECUTE
- at the cost of building a new execution plan each time it is executed.
Read the chapter information in the manual. It's pretty complete.
Sample code
You don't need dynamic SQL for your added code example, and a single statement will be faster:
INSERT INTO dimtime -- you may want list columns
SELECT * -- here as well
FROM public.src_dimtime
WHERE NOT EXISTS (SELECT 1 FROM dimtime);
source to share