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.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? 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.
As I understand it, what Mitar wants can't work because it clashes with
the concepts of "sessions" and "transactions".
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.
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
--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>