Thread: In which session context is a trigger run?
Hi! It seems to me that triggers on a table are run in the session context of the user who made a change in the table, but not of the user who defined the trigger? So I create a temporary function: CREATE OR REPLACE FUNCTION pg_temp.my_function() And a temporary table: CREATE TEMPORARY TABLE posts_temp ( "id" CHAR(17) PRIMARY KEY, ); 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. It seems that "my_function" is called in the session of the user who modified the table, and not the user who created the function (pg_temp.my_function above) or the user who added the trigger. Is there a way to modify/configure this? Switch the session? Is there some other way that I cold define a trigger which runs for a time of my session and runs and interact with temporary objects inside of my session, when data on regular tables is modified? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
On 12/28/18 12:47 PM, Mitar wrote: > Hi! > > It seems to me that triggers on a table are run in the session context > of the user who made a change in the table, but not of the user who > defined the trigger? > > So I create a temporary function: > > CREATE OR REPLACE FUNCTION pg_temp.my_function() And it does what? > > And a temporary table: > > CREATE TEMPORARY TABLE posts_temp ( > "id" CHAR(17) PRIMARY KEY, > ); > > 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. It seems that "my_function" is called in the session of the Temp tables can not be seen by other sessions. > user who modified the table, and not the user who created the function > (pg_temp.my_function above) or the user who added the trigger. > > Is there a way to modify/configure this? Switch the session? Is there > some other way that I cold define a trigger which runs for a time of > my session and runs and interact with temporary objects inside of my > session, when data on regular tables is modified? > > > Mitar > -- Adrian Klaver adrian.klaver@aklaver.com
Hi! On Fri, Dec 28, 2018 at 12:57 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > CREATE OR REPLACE FUNCTION pg_temp.my_function() > > And it does what? Copies/transforms data from posts into posts_temp. > > 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. It seems that "my_function" is called in the session of the > > Temp tables can not be seen by other sessions. Sure, but why is a temporary function used as a temporary trigger made from my session not run inside my session? Then it could see a temporary table made in my session. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
On 12/28/18 12:59 PM, Mitar wrote: > Hi! > > On Fri, Dec 28, 2018 at 12:57 PM Adrian Klaver > <adrian.klaver@aklaver.com> wrote: >>> CREATE OR REPLACE FUNCTION pg_temp.my_function() >> >> And it does what? > > Copies/transforms data from posts into posts_temp. > >>> 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. It seems that "my_function" is called in the session of the >> >> Temp tables can not be seen by other sessions. > > Sure, but why is a temporary function used as a temporary trigger made There is no such thing as a temporary trigger. > 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? > > > Mitar > -- Adrian Klaver adrian.klaver@aklaver.com
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. > > 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
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
Hi! 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? And yes, global temporary tables would be another approach to solve this. But being able to set the session could be another, no? Or are sessions linked to OS processes PostgreSQL is using and this is why triggers run based on the session in which a change on the table was made? Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
On 12/28/18 11:44 PM, Mitar wrote: > Hi! > > 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. If you want a more definitive answer then you will need to provide more information, in the form of: 1) The definition for pg_temp.my_function(). 2) A step by step accounting of where the objects are created, in what sessions and what order. > > And yes, global temporary tables would be another approach to solve > this. But being able to set the session could be another, no? Or are > sessions linked to OS processes PostgreSQL is using and this is why > triggers run based on the session in which a change on the table was > made? Take a look at: https://www.postgresql.org/docs/11/mvcc.html https://www.postgresql.org/docs/11/trigger-definition.html > > > Mitar > -- Adrian Klaver adrian.klaver@aklaver.com
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/>
Attachment
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@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. 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@aklaver.com
On Friday, December 28, 2018, Mitar <mmitar@gmail.com> 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?
Because that is the most obvious behavior. Triggers are assumed to outlive their creating session 99.999999999% of the time, or so.
Could there be a
way to get trigger to run in the session where it was declared?
Doesn’t seem likely. Maybe you can use NOTIFY/LISTEN in your temporary triggers and have your active client perform the work after being notified.
What you want is somewhat novel. It’s worth considering whether it’s uncommon because it’s ingenious or because other techniques are more appropriate.
David J.
On 2018-12-30 08:56:13 -0800, Adrian Klaver wrote:
> On 12/30/18 3:08 AM, Peter J. Holzer wrote:
> > 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.
Yeah, but that would be neatly solved by the magic teleportation of the
trigger execution. (I think this is actually how he got the idea that
executing the trigger in a different session would solve his problem.)
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/>
Attachment
Hi! On Sun, Dec 30, 2018 at 9:58 PM David G. Johnston <david.g.johnston@gmail.com> wrote: > Doesn’t seem likely. Maybe you can use NOTIFY/LISTEN in your temporary triggers and have your active client perform thework after being notified. Yes, this is what I ended up doing. I signal the client and then the client dispatches the call inside the correct client connection/session. It seems to work fine. Probably latency it is adding is also not too big a problem for me. I will see through time. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m
Hi! On Mon, Dec 31, 2018 at 2:58 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote: > On 2018-12-30 08:56:13 -0800, Adrian Klaver wrote: > > On 12/30/18 3:08 AM, Peter J. Holzer wrote: > > > 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. > > Yeah, but that would be neatly solved by the magic teleportation of the > trigger execution. (I think this is actually how he got the idea that > executing the trigger in a different session would solve his problem.) Thanks for understanding well what I am asking. Yes, it seems some form of sending around triggers would be needed. I do not really care if those triggers would run after transaction is committed. So they should be some REALLY AFTER triggers. :-) For now I am using NOTIFY/LISTEN to send information to the client which then dispatches it to current connection/session. It allows me also to batch multiple trigger calls together. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m