From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: In which session context is a trigger run? |
Date: | 2018-12-30 16:56:13 |
Message-ID: | 6b3fc29a-e458-70e2-3b43-cfb98a7fbc69@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 12/30/18 3:08 AM, Peter J. Holzer wrote:
> On 2018-12-29 13:01:47 -0800, Adrian Klaver wrote:
>> On 12/28/18 11:44 PM, Mitar wrote:
>>> On Fri, Dec 28, 2018 at 9:36 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.
>
> Is there a "not" missing in this sentence? Otherwise I don't understand
> what you mean and suspect you have have misunderstood what Mitar wants.
I will agree I have no idea what Mitar wants, as the examples to date
have not included enough information. That is why I asked for more
information.
>
> As I understand it, what Mitar wants can't work because it clashes with
> the concepts of "sessions" and "transactions".
Hence the links to the sections of the documentation that explain that,
on the assumption that might help.
>
> Each session executes transactions sequentially, and the changes
> effected by any transaction become visible to other sessions only after
> the transaction committed.
>
> If I understood Mitar correctly he wants the trigger to execute in the
> session where it was declared, not in the sessio where the statement was
> executed that triggered the trigger.
There is the additional hitch that the trigger is being declared to use
a temporary function that is defined in an alias schema pg_temp.
>
> So we have two sessions A and B. there is a permanent table P and a
> temporary table T in session A. The trigger on P with a temporary
> function) was declared in session A, and we execute an insert statement
> in session B.
>
> Assuming session A is currently idle (otherwise we would have to block
> until the current transaction in A commits or rolls back), we start a
> new transaction in A which executes the trigger. This would see the
> temporary table in session A.
>
> But since the transaction in session B hasn't yet committed, it wouldn't
> see the data that the insert statement has just inserted. Since the
> point of an after insert trigger is usually to do something with this
> new data, that would make the trigger useless.
>
> hp
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2018-12-30 19:27:11 | Re: getting pg_basebackup to use remote destination |
Previous Message | Chuck Martin | 2018-12-30 16:52:10 | Re: getting pg_basebackup to use remote destination |