Pages

Monday, February 2, 2015

template_fdw

I wrote a template foreign data wrapper. It is very simple FDW, that doesn't allow any DML and SELECT operation over table. It is based on Andrew Dunstan's blackhole FDW. What is benefit of this strange data wrapper? I wrote this for possibility to check plpgsql code that uses temporary tables. plpgsql_check cannot do a static validation of functions that uses temporary tables.

I have a function test:
CREATE OR REPLACE FUNCTION public.test()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE r record;
BEGIN
  BEGIN
    DELETE FROM foo; -- temp table
  EXCEPTION WHEN OTHERS THEN
    CREATE TEMP TABLE foo(a int, b int);
  END;
  INSERT INTO foo VALUES(10,20);
  FOR r IN SELECT * FROM foo
  LOOP
    RAISE NOTICE '% %', r.a, r.b;
  END LOOP;
END;
$function$
This code I cannot to verify with plpgsql_check due dependency on temp table foo:
postgres=# select plpgsql_check_function('test()', fatal_errors := false);
                           plpgsql_check_function                           
----------------------------------------------------------------------------
 error:42P01:5:SQL statement:relation "foo" does not exist
 Query: DELETE FROM foo
 --                 ^
 error:42P01:9:SQL statement:relation "foo" does not exist
 Query: INSERT INTO foo VALUES(10,20)
 --                 ^
 error:42P01:10:FOR over SELECT rows:relation "foo" does not exist
 Query: SELECT * FROM foo
 --                   ^
 error:55000:12:RAISE:record "r" is not assigned yet
 Detail: The tuple structure of a not-yet-assigned record is indeterminate.
 Context: SQL statement "SELECT r.a"
 error:55000:12:RAISE:record "r" is not assigned yet
 Detail: The tuple structure of a not-yet-assigned record is indeterminate.
 Context: SQL statement "SELECT r.b"
(15 rows)

I can create persistent table foo. But if I forgot to drop this table, I can have lot of problems, but some one can be invisible. So I created "template storage" that disallow any DML or SELECT. This decrease a risk and I can have these fake tables persistent:
CREATE SERVER template FOREIGN DATA WRAPPER template_fdw;
CREATE FOREIGN TABLE foo(a int, b int) SERVER template;

postgres=# SELECT * FROM foo;
ERROR:  cannot read from table "public.foo"
DETAIL:  Table is template.
HINT:  Create temp table by statement "CREATE TEMP TABLE foo(LIKE public.foo INCLUDING ALL);"

-- but EXPLAIN is valid
postgres=# EXPLAIN SELECT * FROM foo;
                      QUERY PLAN                       
-------------------------------------------------------
 Foreign Scan on foo  (cost=0.00..0.00 rows=0 width=8)
(1 row)

-- and now I can do static validation
postgres=# select plpgsql_check_function('test()', fatal_errors := false);
 plpgsql_check_function 
------------------------
(0 rows)

No comments:

Post a Comment