From: | Souvik Bhattacherjee <kivuosb(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Method to pass data between queries in a multi-statement transaction |
Date: | 2019-04-17 22:04:01 |
Message-ID: | CAANrPSfG+QwrnOAvU8MWShBLqzc_yJsJkfRvmrVQ3Z=EVsdihQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2019-04-17 22:16:24 | Re: Method to pass data between queries in a multi-statement transaction |
Previous Message | pabloa98 | 2019-04-17 21:21:03 | is it possible to create partitioned tables using tables from different schemas |