Re: In which session context is a trigger run? - Mailing list pgsql-general

On 12/28/18 7:56 PM, Mitar wrote:
> Hi!
> 
> On Fri, Dec 28, 2018 at 3:25 PM Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com