PostgreSQL and the temporary functions

In my previous article I’ve used the builtin function now() to select from a history table only those rows that are “current“.

A “brilliant next idea” that came to my mind was: “if I can redefine now() in my temporary schema (one for each session) I can browse that table as if I were either in the past or in the future, thus opening my solution to a number of other applications“.

So I’ve tried to go this path.

create or replace temporary function now( out ts timestamp )
volatile language plpgsql as $l0$
begin
  ts = '2017-01-01 00:00:00'::timestamp;
end;
$l0$;

It doesn’t work: there’s no such a command like “create temporary function ...” in PostgreSQL!
So I’ve tried a different syntax.

create or replace function pg_temp.now( out ts timestamp )
volatile language plpgsql as $l0$
begin
  ts = '2017-01-01 00:00:00'::timestamp;
end;
$l0$;

This works indeed. Let’s check.

tmp1=# select * from now();
              now              
-------------------------------
 2018-05-30 10:48:19.093572+02
(1 row)

Time: 0,311 ms
tmp1=# select * from pg_temp.now();
         ts          
---------------------
 2017-01-01 00:00:00
(1 row)

Time: 0,790 ms

It works. In order to “mask” the builtin with the temporary I should change the search_path like this:

tmp1=# show search_path ;
   search_path   
-----------------
 "$user", public
(1 row)

Time: 0,354 ms
tmp1=# set search_path to "$user", pg_temp, public;
SET
Time: 0,382 ms

But …

tmp1=# select * from now();
              now              
-------------------------------
 2018-05-30 10:49:54.077509+02
(1 row)

Time: 0,201 ms
tmp1=# select * from pg_temp.now();
         ts          
---------------------
 2017-01-01 00:00:00
(1 row)

Time: 0,628 ms

It doesn’t work as expected! What? Is this a bug? Of course … it is not!

I’ve found the answer with some search.
There’s an explicit reference to such a case in the PostgreSQL general mailing list, by Tom Lane himself back in 2008. I quote (with slight edits):

You can [create a temporary function] today, as long as you don’t mind schema-qualifying uses of the function!
That’s intentional because of the risk of trojan horses.

I am not really sure that it will protect from evil behaviors: an evil temporary table can mask a good one and change the behavior of an application. And if I want to avoid any masking trick involving the search_path, I would always schema qualify all object references. Wouldn’t I?

Anyway, this means that my trick won’t ever work, not in the way I’ve devised. I have a number of other ideas here involving either the do statement or temporary tables or maybe something else …

Advertisements

2 thoughts on “PostgreSQL and the temporary functions”

  1. Hi there,
    I have come across a similar one. Needed to create a temporary foreign tables for ETL jobs by concurrent processes. However “create temporary foreign table …” does not exist too. Yet you can create one like this “create foreign table pg_temp. …” to exactly the same effect. And even better – “set search_path” just works.
    Example:

    drop foreign table if exists pg_temp.pets;
    create foreign table pg_temp.pets — there is no risk of conflicting names
    (
    petname text,
    animal text
    )
    server external_file
    options
    (
    format ‘csv’,
    filename ‘/media/data/foreign_data/pets.txt’,
    delimiter E’\t’,
    encoding ‘UTF8’,
    header ‘true’
    );

    set search_path to “$user”, pg_temp, public;
    select * from pets; — works fine

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s