Thread: Using Ephemeral Named Relation like a temporary table

Hello,


Temporary tables are often used to store transient data in
batch processing and the contents can be accessed multiple
times. However, frequent use of temporary tables has a problem
that the system catalog tends to bloat. I know there has been
several proposals to attack this problem, but I would like to
propose a new one.

The idea is to use Ephemeral Named Relation (ENR) like a
temporary table. ENR information is not stored into the system
catalog, but in QueryEnvironment, so it never bloat the system
catalog.

Although we cannot perform insert, update or delete on ENR,
I wonder it could be beneficial if we need to reference to a
result of a query multiple times in a batch processing.

The attached is a concept patch. This adds a new syntax
"OPEN cursor INTO TABLE tablename" to pl/pgSQL, that stores
a result of the cursor query into a ENR with specified name.
However, this is a tentative interface to demonstrate the
concept of feature.

Here is an example;

postgres=# \sf fnc
CREATE OR REPLACE FUNCTION public.fnc()
 RETURNS TABLE(sum1 integer, avg1 integer, sum2 integer, avg2 integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
 sum1 integer;
 sum2 integer;
 avg1 integer;
 avg2 integer;
 curs CURSOR FOR SELECT aid, bid, abalance FROM pgbench_accounts
                   WHERE abalance BETWEEN 100 AND 200;
BEGIN
 OPEN curs INTO TABLE tmp_accounts;
 SELECT count(abalance) , avg(abalance) INTO sum1, avg1
   FROM tmp_accounts;
 SELECT count(bbalance), avg(bbalance) INTO sum2, avg2
   FROM tmp_accounts a, pgbench_branches b WHERE a.bid = b.bid;
 RETURN QUERY SELECT sum1,avg1,sum2,avg2;
END;
$function$

postgres=# select fnc();
        fnc         
--------------------
 (541,151,541,3937)
(1 row)

As above, we can use the same query result for multiple
aggregations, and also join it with other tables.

What do you think of using ENR for this way?

Regards,
Yugo Nagata

--
Yugo NAGATA <nagata@sraoss.co.jp>

Hello,


Temporary tables are often used to store transient data in
batch processing and the contents can be accessed multiple
times. However, frequent use of temporary tables has a problem
that the system catalog tends to bloat. I know there has been
several proposals to attack this problem, but I would like to
propose a new one.

The idea is to use Ephemeral Named Relation (ENR) like a
temporary table. ENR information is not stored into the system
catalog, but in QueryEnvironment, so it never bloat the system
catalog.

Although we cannot perform insert, update or delete on ENR,
I wonder it could be beneficial if we need to reference to a
result of a query multiple times in a batch processing.

The attached is a concept patch. This adds a new syntax
"OPEN cursor INTO TABLE tablename" to pl/pgSQL, that stores
a result of the cursor query into a ENR with specified name.
However, this is a tentative interface to demonstrate the
concept of feature.

Here is an example;

postgres=# \sf fnc
CREATE OR REPLACE FUNCTION public.fnc()
 RETURNS TABLE(sum1 integer, avg1 integer, sum2 integer, avg2 integer)
 LANGUAGE plpgsql
AS $function$
DECLARE
 sum1 integer;
 sum2 integer;
 avg1 integer;
 avg2 integer;
 curs CURSOR FOR SELECT aid, bid, abalance FROM pgbench_accounts
                   WHERE abalance BETWEEN 100 AND 200;
BEGIN
 OPEN curs INTO TABLE tmp_accounts;
 SELECT count(abalance) , avg(abalance) INTO sum1, avg1
   FROM tmp_accounts;
 SELECT count(bbalance), avg(bbalance) INTO sum2, avg2
   FROM tmp_accounts a, pgbench_branches b WHERE a.bid = b.bid;
 RETURN QUERY SELECT sum1,avg1,sum2,avg2;
END;
$function$

postgres=# select fnc();
        fnc         
--------------------
 (541,151,541,3937)
(1 row)

As above, we can use the same query result for multiple
aggregations, and also join it with other tables.

What do you think of using ENR for this way?

Regards,
Yugo Nagata

--
Yugo NAGATA <nagata@sraoss.co.jp>