On Friday, December 28, 2018, Mitar <
mmitar@gmail.com> 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?
Because that is the most obvious behavior. Triggers are assumed to outlive their creating session 99.999999999% of the time, or so.
Could there be a
way to get trigger to run in the session where it was declared?
Doesn’t seem likely. Maybe you can use NOTIFY/LISTEN in your temporary triggers and have your active client perform the work after being notified.
What you want is somewhat novel. It’s worth considering whether it’s uncommon because it’s ingenious or because other techniques are more appropriate.
David J.