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

Or just add pid to table3...

> That's an application requirement. So pid cannot be added at will to
table3.

How much development and maintenance effort are you willing to spend here
to gain what is likely to amount to only a bit of efficiency? Many things
are possible if you are going to modify the server code but why add grief?

> How much development / maintenance effort do you anticipate for
implementing this feature? This is something that my application would need
so I'm willing to dedicate some time to it.

By the way, I figured that the hash table deallocation issue could be
resolved (although in an inefficient way) by serializing the data to a
string and then copying that value into the hash table during insertion.
However the hash table is still visible to all the transactions I suppose
and as a result needs to be locked. Just wanted to let you know that I have
initialized the hash table within the PostgresMain() method and the hash
table is declared as an extern variable, which I anticipate to be accessed
by many methods. How difficult is it to make a hash table (or any data
structure) private to the current transaction so that I do not have to
resort to locking?

-SB

On Thu, Apr 18, 2019 at 5:34 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Thu, Apr 18, 2019 at 9:03 AM Souvik Bhattacherjee <kivuosb(at)gmail(dot)com>
> wrote:
>
>> Thanks Michel.
>>
>> However this only works if a is an unique attribute in the table that
>> would help us to identify tuples that participated in the join. Consider
>> the following join:
>>
>> insert into table3 (id, level, empname, salary)
>> (select t0.cid, t0.level, t1.empname, t2.salary from table0 t0, table1
>> t1, table2 t2 where t0.cid = t1.cid and t1.pid = t2.pid);
>>
>> Now if I want to delete those tuples from table2 that satisfied the join
>> condition, I need to execute the join again with additional attributes.
>>
>
> Or just add pid to table3...
>
>> Also note that based on query plan, i.e. whether table0 and table1 were
>> joined first followed by table1 and table2, we have to execute one
>> additional join to get the tuples in table2 that satisfied the join
>> condition (t1.pid = t2.pid).
>>
>
> ???
>
>> Getting that information while the query is executed may not be
>> difficult. There are other use cases in my application that require me to
>> transfer the data from one query to the next within a transaction.
>>
>
> There may be some that benefit to some degree but its likely that you can
> write the application and queries in such a way to avoid a hard requirement.
>
> Thus, what I'm looking for here is way to store the information and then
>> pass that information to the next query efficiently.
>>
> For example, is it possible to define a struct of my choice, private to
>> the current transaction, that would store the data and then pass it around
>> to the next query in the transaction without having to materialize that
>> struct (or deal with concurrency issues as in the hash table approach
>> mentioned earlier) .
>>
>
> How much development and maintenance effort are you willing to spend here
> to gain what is likely to amount to only a bit of efficiency? Many things
> are possible if you are going to modify the server code but why add grief?
>
> David J.
>
>