On 12/28/18 11:44 PM, Mitar wrote:
> Hi!
>
> 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.
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.klaver@aklaver.com