On 12/28/18 11:44 PM, Mitar wrote:
Hi!
On Fri, Dec 28, 2018 at 9:36 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxx
