> > CREATE FUNCTION s.f()
> > RETURNS BIGINT
> > EXTERNAL SECURITY DEFINER
> > AS '
> > BEGIN
> > EXECUTE ''CREATE LOCAL TEMP TABLE t (
> > a TEXT NOT NULL,
> > b TEXT
> > ) WITHOUT OIDS ON COMMIT DROP;'';
> > EXECUTE ''CREATE UNIQUE INDEX t_key_udx ON t(a);'';
>
> > INSERT INTO t (a, b) VALUES (''foo''::TEXT, ''bar''::TEXT);
>
> This is not going to work more than once, because the INSERT caches
> a plan that refers to the first-time-through temp table.
>
> You could put the INSERT into an EXECUTE as well. Or use a different
> PL language that doesn't cache plans.
Hrm... this limitation makes temporary tables that drop on commit +
pl/pgsql unusable beyond the 1st transaction. Is there a mechanism to
test to see if a relation in a plan is a temporary table? It seems as
though in pl_exec.c that around 1926 it'd be possible to add a test to
see if the plan uses temporary tables and add a new member to struct
expr telling exec_stmt_execsql to free the plan around line 2016 when
its cleaning up after itself.
For the archives, there are two workarounds for this:
1) Don't use ON COMMIT DROP, instead use ON COMMIT DELETE ROWS. This
preserves the relation thus all cached plans are still valid.
Before creating the temporary table, however, you have to test for
its existence. This came out at about 0.4ms.
2) Use a FOR-IN-EXECUTE statement. It's slower, but works (~1.2ms
instead of 0.2ms. On thousands of inserts a second and it makes a
big difference).
-sc
PS For the sake of completeness, returning the value from CURRVAL()
takes ~0.3ms from pl/pgsql and only ~0.14ms outside of pl/pgsql.
The difference is the runtime cost of using pl/pgsql which is
pretty reasonable given pl/pgsql walks an AST.
--
Sean Chittenden
