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@xxxxxxxxxxx
