Re: In which session context is a trigger run?

On 12/28/18 7:56 PM, Mitar wrote:
> Hi!
>
> On Fri, Dec 28, 2018 at 3:25 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>>> Sure, but why is a temporary function used as a temporary trigger made
>>
>> There is no such thing as a temporary trigger.
>
> A trigger defined using a temporary function gets deleted once a
> function gets deleted, which is at the end of the session. Thus, it is
> a temporary trigger. Feel free to try it. Create a function in pg_temp
> and then define a trigger, disconnect, and you will see that trigger
> is deleted as well.

That is because the function is temporary and when the session ends the
function is dropped and it cascades to the trigger. The important part
to note is pg_temp.* is an alias to whatever pg_temp_nn the temporary
objects are created in. This is why what you want to do is not working.
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. Postgres does not have global temp tables at this
time. There have been rumblings about making that happen, but I do not
what the status of that is.

>
>>> from my session not run inside my session? Then it could see a
>>> temporary table made in my session.
>>
>> Except that is not what your OP stated:
>>
>> "And I add it to a regular table as a trigger:
>>
>> CREATE TRIGGER posts_insert AFTER INSERT ON posts REFERENCING NEW
>> TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION
>> pg_temp.my_function();
>>
>> When a row is added to "posts" table outside of my session, function
>> "my_function" is called, but it seems it cannot access "posts_temp"
>> table."
>>
>> So are you talking about another case now?
>
> No. Still the same case. I have a regular table "posts" and a
> temporary table "posts_temp". I want to create a trigger on "posts"
> which calls "my_function". "my_function" then copies data from "posts"
> to "posts_temp". The problem is that if "posts" is modified in another
> session, the trigger cannot access "posts_temp". I wonder if there is
> a way to call "my_function" inside the same temporary context /
> session where it was defined, because in that same session also
> "posts_temp" was defined.
>
>
> Mitar
>

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