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$
This looks like a slightly more flexible version of the Oracle pl/sql table type.
For those not familiar, PL/SQL can have record types, and in-memory collections of records types, and you can either build up multiple records in a collection manually, or you can bulk-collect them from a query. Then, you can later reference that collection in a regular SQL query with FROM TABLE(collection_name). It's a neat system for certain types of workloads.
My first take is there are likely customers out there that will want this. However, those customers will want to manually add/delete rows from the ENR, so we'll want a way to do that.
I haven't looked at ENRs in a while, when would the memory from that ENR get freed?
