On Wed, Mar 29, 2023 at 12:54 AM Yugo NAGATA <nag...@sraoss.co.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 <nag...@sraoss.co.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?