Re: In which session context is a trigger run?

On 12/28/18 11:44 PM, Mitar wrote:
> Hi!
>
> On Fri, Dec 28, 2018 at 9:36 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.
If you want a more definitive answer then you will need to provide more
information, in the form of:

1) The definition for pg_temp.my_function().

2) A step by step accounting of where the objects are created, in what
sessions and what order.

>
> And yes, global temporary tables would be another approach to solve
> this. But being able to set the session could be another, no? Or are
> sessions linked to OS processes PostgreSQL is using and this is why
> triggers run based on the session in which a change on the table was
> made?

Take a look at:

https://www.postgresql.org/docs/11/mvcc.html

https://www.postgresql.org/docs/11/trigger-definition.html

>
>
> Mitar
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com