Re: Using Ephemeral Named Relation like a temporary table

On Wed, Mar 29, 2023 at 12:54 AM Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> wrote:

> 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(at)sraoss(dot)co(dot)jp>
>

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.

example link, I'm sure there's better out there:
https://oracle-base.com/articles/12c/using-the-table-operator-with-locally-defined-types-in-plsql-12cr1

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?