Re: Using Ephemeral Named Relation like a temporary table

Hi

st 29. 3. 2023 v 6:54 odesílatel Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp> napsal:

> 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?
>

The idea looks pretty good. I think it can be very useful. I am not sure if
this design is intuitive. If I remember well, the Oracle's has similar
features, and can be nice if we use the same or more similar syntax
(although I am not sure how it can be implementable)? I think so PL/SQL
design has an advantage, because you don't need to solve the scope of the
cursor's assigned table.

OPEN curs INTO TABLE tmp_accounts; -- it looks little bit strange. I miss
info, so tmp_accounts is not normal table

what about

OPEN curs INTO CURSOR TABLE xxx;

or

OPEN curs FOR CURSOR TABLE xxx

Regards

Pavel

>
> Regards,
> Yugo Nagata
>
> --
> Yugo NAGATA <nagata(at)sraoss(dot)co(dot)jp>
>