Re: Method to pass data between queries in a multi-statementtransaction

On 4/17/19 3:04 PM, Souvik Bhattacherjee wrote:
> Hello,
> 
> I'm trying to pass some values between queries in a multi-statement 
> transaction. For example, consider the following representative 
> multi-statement transaction:
> 
> begin;
> select * from table1 t1, table2 t2 where t1.cid = t2.cid;
> delete from table1 where cid in 
> (values-to-be-populated-from-the-previous-query);
> commit;

https://www.postgresql.org/docs/11/sql-delete.html
"DELETE FROM films
   WHERE producer_id IN (SELECT id FROM producers WHERE name = 'foo');"

> 
> Now, assume that I'm able to get the cid values from table1 that 
> satisfies the equi-join condition and I want to pass those values in the 
> IN condition in the subsequent delete query. Is there a straightforward 
> way to achieve this by modifying the postgresql source code?
> 
> I tried doing this by creating a hash table (by following this example: 
> https://wiki.postgresql.org/wiki/HashTable). The hash key in this case 
> is the current transactionid (which I presume should remain unchanged 
> for the entire duration of the transaction) and the hash value is 
> dynamically allocated. Within the query I can perform a lookup and 
> insert without any problem. However, when I try to do a lookup of the 
> hash value from a different query that did not insert the value 
> originally, I do not get any value. The hash table is able to tell me 
> that the key exists (due to the fact that the key is not dynamically 
> allocated) but doesn't return any value. My guess is that after each 
> query in the multi-statement txn block, the storage for that query is 
> deallocated, which results in the following behaviour.
> 
> The hash table approach (although it didn't work) above, IMO, has the 
> drawback that it needs to be locked since there can be other txns that 
> can try to access the hash table as well.
> 
> The other approach here would be the serialize the values into a file 
> and then read those values later from the subsequent query. However this 
> is not efficient.
> 
> Thus, I'm looking for a method of passing values between queries in a 
> multi-statement block that avoids the disk and does not need locking. I 
> was wondering if there is a way to define a hash table (or any data 
> structure) which is visible only to the current transaction.
> 
> -SB
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com