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

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(dot)klaver(at)aklaver(dot)com