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.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx
