Re: In which session context is a trigger run?

On 12/30/18 3:08 AM, Peter J. Holzer wrote:
> On 2018-12-29 13:01:47 -0800, Adrian Klaver wrote:
>> On 12/28/18 11:44 PM, Mitar wrote:
>>> On Fri, Dec 28, 2018 at 9:36 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>>> When you create the temporary function it is 'pinned' to a particular
>>>> session/pg_temp_nn. Running the trigger in another session 'pins' it to
>>>> that session and it is not able to see the posts_temp table in the
>>>> original session.
>>>
>>> Yes. But why is trigger run in that other session? Could there be a
>>> way to get trigger to run in the session where it was declared?
>>
>> Because it is where the temporary table is declared that is important.
> 
> Is there a "not" missing in this sentence? Otherwise I don't understand
> what you mean and suspect you have have misunderstood what Mitar wants.

I will agree I have no idea what Mitar wants, as the examples to date 
have not included enough information. That is why I asked for more 
information.

> 
> As I understand it, what Mitar wants can't work because it clashes with
> the concepts of "sessions" and "transactions".

Hence the links to the sections of the documentation that explain that, 
on the assumption that might help.

> 
> Each session executes transactions sequentially, and the changes
> effected by any transaction become visible to other sessions only after
> the transaction committed.
> 
> If I understood Mitar correctly he wants the trigger to execute in the
> session where it was declared, not in the sessio where the statement was
> executed that triggered the trigger.

There is the additional hitch that the trigger is being declared to use 
a temporary function that is defined in an alias schema pg_temp.

> 
> So we have two sessions A and B. there is a permanent table P and a
> temporary table T in session A. The trigger on P with a temporary
> function) was declared in session A, and we execute an insert statement
> in session B.
> 
> Assuming session A is currently idle (otherwise we would have to block
> until the current transaction in A commits or rolls back), we start a
> new transaction in A which executes the trigger. This would see the
> temporary table in session A.
> 
> But since the transaction in session B hasn't yet committed, it wouldn't
> see the data that the insert statement has just inserted. Since the
> point of an after insert trigger is usually to do something with this
> new data, that would make the trigger useless.
> 
>          hp
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com