Re: error in trigger creation

On 4/21/24 14:21, Tom Lane wrote: Adrian Klaver writes: On 4/21/24 11:20, yudhi s wrote: So in this case i was wondering if "event trigger" can cause any additional threat and thus there is no such privilege like "create trigger" exist in postgres and so it should b

Re: error in trigger creation

Adrian Klaver writes: > On 4/21/24 11:20, yudhi s wrote: >> So in this case i was wondering if "event trigger" can cause any >> additional threat and thus there is no such privilege like "create >> trigger" exist in postgres and so it should be tre

Re: error in trigger creation

On 4/21/24 11:20, yudhi s wrote: On Sun, Apr 21, 2024 at 8:13 PM Tom Lane <mailto:t...@sss.pgh.pa.us>> wrote: So do you mean , we should not create the event trigger using the "security definer" , rather have the super user do this each time we have to creat

Re: error in trigger creation

child table? > Yes all the child tables have foreign keys indexed. Again I don't want to divert the main topic(trigger creation) . I will provide the exact test case how it puts exclusive lock and runs longer as scans all child partitions (which must be using indexes though) but as the nu

Re: error in trigger creation

On Sun, Apr 21, 2024 at 2:58 PM yudhi s wrote: > the partition drop from parent is taking longer as it scans all the > partitions of the child table > Does the relevant supporting index exist on the child table?

Re: error in trigger creation

titions using pg_partman, then create the below event trigger which will add the foreign key to the new partitions for all new future partitions. And we are planning to create such an event trigger for all such child tables that are partitioned and having FK's. CREATE OR REPLACE FUNCTION add_partiti

Re: error in trigger creation

irst create the >> >> function from super user and then execute the grant? But doesn't that >> mean, >> >> each time we want to create a new event trigger we have to be again >> >> dependent on the "super user" to modify the security definer function? >

Re: error in trigger creation

t doesn't that > mean, > >> each time we want to create a new event trigger we have to be again > >> dependent on the "super user" to modify the security definer function? > > > Dynamic SQL. See “execute” in plpgsql. > > You might as well just give t

Re: error in trigger creation

< >>> david.g.johns...@gmail.com> wrote: >>> >>>> On Sunday, April 21, 2024, yudhi s wrote: >>>> >>>>> so that it will be able to assign the privilege, so we will be able to >>>>> create the event trigger without need to run the ev

Re: error in trigger creation

, April 21, 2024, yudhi s wrote: >>> >>>> so that it will be able to assign the privilege, so we will be able to >>>> create the event trigger without need to run the event trigger script from >>>> super user itself? >>>> >>> >>&g

Re: error in trigger creation

"David G. Johnston" writes: > On Sunday, April 21, 2024, yudhi s wrote: >> Are you saying something like below, in which we first create the >> function from super user and then execute the grant? But doesn't that mean, >> each time we want to create a new ev

Re: error in trigger creation

On Sunday, April 21, 2024, yudhi s wrote: > On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Sunday, April 21, 2024, yudhi s wrote: >> >>> so that it will be able to assign the privilege, so we will be able to

Re: error in trigger creation

On Sun, Apr 21, 2024 at 1:55 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Sunday, April 21, 2024, yudhi s wrote: > >> so that it will be able to assign the privilege, so we will be able to >> create the event trigger without need to run the event trig

Re: error in trigger creation

On Sunday, April 21, 2024, yudhi s wrote: > so that it will be able to assign the privilege, so we will be able to > create the event trigger without need to run the event trigger script from > super user itself? > Write a security-definer function owned by superuser and gr

error in trigger creation

Hi All, We are seeing privilege issues while creating event triggers. It says the user "*must be a superuser to create an event trigger*". So my question is , if we have application user as "app_user" which is responsible for creating database objects in schema "app_sch

Re: Question on trigger

> (mainly for each row trigger) created on them. > > And also the bulk DML/array based insert (which inserts multiple rows in > one short or one batch) , in those cases it seems the trigger will not > make that happen as it will force it

Re: Question on trigger

On Sat, 13 Apr 2024 at 21:44, Adrian Klaver wrote: > On 4/13/24 00:03, veem v wrote: > > Thank you Adrian. > > > > So it seems the heavy DML tables will see an impact if having triggers > > (mainly for each row trigger) created on them. > > > > And also

Re: Question on trigger

On 4/13/24 00:03, veem v wrote: Thank you Adrian. So it seems the heavy DML tables will see an impact if having triggers (mainly for each row trigger) created on them. And also the bulk DML/array based insert (which inserts multiple rows in one short or one batch) , in those cases it seems

Re: Question on trigger

Thank you Adrian. So it seems the heavy DML tables will see an impact if having triggers (mainly for each row trigger) created on them. And also the bulk DML/array based insert (which inserts multiple rows in one short or one batch) , in those cases it seems the trigger will not make that happen

Re: Question on trigger

downside of such audit trigger setup for all the tables? Will it impact the bulk data insert/update/delete OR slowdown of any of the DML operations significantly (and thus will not be advisable to use for all tables but selected ones)? Triggers are overhead in Postgres as they where in Oracle

Question on trigger

Hi, We used to use Oracle database in which we had audit triggers(something as below) mandated for all tables by the control team. Now we are going to use the postgresql 15.4 database for one of our applications. So,wanted to understand if there exists any downside of such audit trigger setup

Re: Me again with an insert trigger problem

On 2/27/24 14:38, Thiemo Kellner wrote: Am 27.02.2024 um 23:20 schrieb Adrian Klaver: I am not sure, what you want me to show with your test case. And I am not sure whether I could not make myself clear. Please bear with me if I try to make things clearer with an example. Your comment

Re: Me again with an insert trigger problem

Am 27.02.2024 um 23:20 schrieb Adrian Klaver: On 2/27/24 14:11, Thiemo Kellner wrote: It is a habit of mine to pad conditions in the where clause. This way, it is easy to comment/uncomment parts of the clause for testing purposes. Coming from Oracle, I missed that using "true" is also

Re: Me again with an insert trigger problem

On 2/27/24 14:11, Thiemo Kellner wrote: Am 27.02.2024 um 21:42 schrieb Adrian Klaver: Also not sure what this: select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME    from NODE⠒V   where 1 = 1     and ID = new.NODE⠒ID     and 1 = 1; is supposed to be

Re: Me again with an insert trigger problem

Am 27.02.2024 um 21:42 schrieb Adrian Klaver: Also not sure what this: select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME   from NODE⠒V where 1 = 1    and ID = new.NODE⠒ID    and 1 = 1; is supposed to be doing especially the 1 = 1 tests? The

Re: Me again with an insert trigger problem

Thanks. 27.02.2024 19:09:50 Adrian Klaver : > > On 2/27/24 9:49 AM, Thiemo Kellner wrote: >> Hi >> >> I am surprised that my before insert trigger function does not insert any >> rows into NODE_GOOD. >> >> I was under the impression tha

Re: Me again with an insert trigger problem

On 2/27/24 9:49 AM, Thiemo Kellner wrote: Hi I am surprised that my before insert trigger function does not insert any rows into NODE_GOOD. I was under the impression that the trigger function would do the insert with the new and possibly adapted values. In my case, to me at least

Me again with an insert trigger problem

Hi I am surprised that my before insert trigger function does not insert any rows into NODE_GOOD. I was under the impression that the trigger function would do the insert with the new and possibly adapted values. In my case, to me at least, it is very simple. Only records of node type "

Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)

On Thu, Feb 22, 2024 at 5:06 PM Erik Wienhold wrote: > On 2024-02-22 22:14 +0100, Vick Khera wrote: > > On Wed, Feb 21, 2024 at 4:27 PM Tom Lane wrote: > > > > > For the moment, I think the only feasible solution is for your trigger > > > function to set t

Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)

On 2024-02-22 22:14 +0100, Vick Khera wrote: > On Wed, Feb 21, 2024 at 4:27 PM Tom Lane wrote: > > > For the moment, I think the only feasible solution is for your trigger > > function to set the search path it needs by adding a "SET search_path > > = whatever&quo

Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)

On Wed, Feb 21, 2024 at 4:27 PM Tom Lane wrote: > For the moment, I think the only feasible solution is for your trigger > function to set the search path it needs by adding a "SET search_path > = whatever" clause to the function's CREATE command. The error is

Re: How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)

Vick Khera writes: > I've created a database which my vendor (Supabase) cannot > dump/restore/upgrade. Ultimately, it comes down to this trigger statement, > and the fact that the underlying operations needed to perform the `IS > DISTINCT FROM` comparison in the WHEN clause need

How to schema-qualify "IS DISTINCT FROM" on trigger definition? (I created a db that cannot be dump/restored)

I've created a database which my vendor (Supabase) cannot dump/restore/upgrade. Ultimately, it comes down to this trigger statement, and the fact that the underlying operations needed to perform the `IS DISTINCT FROM` comparison in the WHEN clause need to be found in the `public` schema. During

Re: Trigger to Count Number of Logical Replication Table Changes.

Hi The only thing that can be established is that if the record does not exist, the trigger will not be updating anything in the table "tst.time_audit_tbl" for the condition "table_name = CONCAT(TG_TABLE_SCHEMA, '.', TG_TABLE_NAME)" Maybe, checking before the UPDATE, if the r

Trigger to Count Number of Logical Replication Table Changes.

(but it seems less efficient to me). I need the counter to increase after initial sync as well as after regular logical replication sync. Triggers not to work without ENABLE ALWAYS. In addition, If I try trigger that is "FOR EACH STATEMENT" it works only for initial sync and not f

Re: Detection of which attributes should get set in update trigger

have a view, that is a join over 4 tables (the setup of the rule question). I want to update the central table over the view (by an instead-of trigger). How can I determine, whether an attribute should get set to NULL, "new.XYZ is null" or whether it should be

Re: Detection of which attributes should get set in update trigger

On Friday, November 10, 2023, Thiemo Kellner wrote: > Hi all > > I have a view, that is a join over 4 tables (the setup of the rule > question). I want to update the central table over the view (by an > instead-of trigger). How can I determine, whether an attribute should ge

Detection of which attributes should get set in update trigger

Hi all I have a view, that is a join over 4 tables (the setup of the rule question). I want to update the central table over the view (by an instead-of trigger). How can I determine, whether an attribute should get set to NULL, "new.XYZ is null" or whether it should be

Re: Trigger functions and parallelism

On Mon, 02 Oct 2023 13:07:19 -0400 Tom Lane wrote: > "Karl O. Pinc" writes: > > What I'm looking for is some general statements about > > parallel safety for trigger functions. I don't know > > enough about parallelism to even know if it applies >

Re: Trigger functions and parallelism

"Karl O. Pinc" writes: > What I'm looking for is some general statements about > parallel safety for trigger functions. I don't know > enough about parallelism to even know if it applies > to data modification statements. It doesn't, which is why the docs a

Trigger functions and parallelism

safety for trigger functions. I don't know enough about parallelism to even know if it applies to data modification statements. It seems to me that, because raising any sort of exception affects transaction state, that triggers which validate data integrity and raise errors must be PARALLEL

What changes to a table cannot be detected by a trigger?

Triggers can be set for insert, update, delete, and truncate operations. Alter table operations, like adding or dropping a column from a table, do not cause an update trigger to fire but can be captured by an event trigger. Are there any situations where the data in a table can be changed

Re: event trigger clarification

executed by each user action, when invoked in a function > attached > > to a ddl_command_end event trigger." > > When some ddl command is executed within a block, I would like to know > if the > > event trigger fires when the line is executed or at commit time. >

Re: event trigger clarification

ommands returns a list of DDL >> commands >> executed by each user action, when invoked in a function attached to a >> ddl_command_end event trigger." >> When some ddl command is executed within a block, I would like to know if >> the event trigger

Re: event trigger clarification

On Wednesday, August 30, 2023, Marc Millas wrote: > Hi, > the doc v15 states: " pg_event_trigger_ddl_commands returns a list of DDL > commands > executed by each user action, when invoked in a function attached to a > ddl_command_end event trigger." > When some dd

Re: event trigger clarification

> On 30/08/2023 17:12 CEST Marc Millas wrote: > > the doc v15 states: " pg_event_trigger_ddl_commands returns a list of DDL > commands executed by each user action, when invoked in a function attached > to a ddl_command_end event trigger." > When some ddl command i

event trigger clarification

Hi, the doc v15 states: " pg_event_trigger_ddl_commands returns a list of DDL commands executed by each user action, when invoked in a function attached to a ddl_command_end event trigger." When some ddl command is executed within a block, I would like to know if the event trigger

Re: Dynamically accessing columns from a row type in a trigger

Well, some weeks ago, I read an article about that. The more efficient approach (in pgplsql) is to use hstore. With a similar topic, at the end, I created a group of functions that store in an internal table, data structure for each table under the same generic trigger. I also store, primary key

Re: Dynamically accessing columns from a row type in a trigger

So the less obvious solution that works is to create a temporary table. A little verbose, but I get to keep the types. `CREATE TEMPORARY TABLE _ ON COMMIT DROP AS SELECT OLD.*;` _ as a table name makes things a little easier to type. Rhys Peace & Love | Live Long & Prosper If the

Re: Dynamically accessing columns from a row type in a trigger

omething along the lines of: > > > > `EXECUTE format('SELECT * FROM %1$I.sometable WHERE pk = $1', myschma) > > USING OLD['pk_col']`. > > > > I reckon I may have to look at just generating a trigger function per > > table, or maybe look into using TG_ARGS. So t

Re: Dynamically accessing columns from a row type in a trigger

On Sun, Aug 13, 2023 at 11:27 AM Rhys A.D. Stewart wrote: > > Hey Adrian, > > Thanks for your response. I don't think I explained myself clearly. > pk_col is not the column name. pk_col is a variable that holds the > name of a column. This is one trigger for several t

Re: Dynamically accessing columns from a row type in a trigger

On Sat, Aug 12, 2023 at 1:10 PM Rhys A.D. Stewart wrote: > Am I missing out on a simpler or more elegant solution? > > No, you are not (at least among SQL and pl/pgsql. SQL is strongly and statically typed. Circumventing that has a cost, but at least you do have tools at hand when you find the

Re: Dynamically accessing columns from a row type in a trigger

On 8/12/23 20:21, Rhys A.D. Stewart wrote: Hey Adrian, Thanks for your response. I don't think I explained myself clearly. pk_col is not the column name. pk_col is a variable that holds the name of a column. This is one trigger for several tables with varying structures. So pk_col would

Re: Dynamically accessing columns from a row type in a trigger

Hey Adrian, Thanks for your response. I don't think I explained myself clearly. pk_col is not the column name. pk_col is a variable that holds the name of a column. This is one trigger for several tables with varying structures. So pk_col would be a column specific to the current TG_TABLE_NAME

Re: Dynamically accessing columns from a row type in a trigger

On 8/12/23 13:09, Rhys A.D. Stewart wrote: Greetings all, I am writing a trigger and would like to know how to dynamically access a column from the "OLD" variable. pk_col is the column name from the table. I've come up with either doing this: EXECUTE format('SELECT $1.%1$

Re: Dynamically accessing columns from a row type in a trigger

On 8/12/23 13:09, Rhys A.D. Stewart wrote: Greetings all, I am writing a trigger and would like to know how to dynamically access a column from the "OLD" variable. pk_col is the column name from the table. I've come up with either doing this: EXECUTE format('SELECT $1.%1$

Dynamically accessing columns from a row type in a trigger

Greetings all, I am writing a trigger and would like to know how to dynamically access a column from the "OLD" variable. pk_col is the column name from the table. I've come up with either doing this: EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD; which looks a bit

Re: Trigger Function question

I've just finish to do the same thing. For my needs, I decided to create a table with the information I need for each view (yes I set trigger on view not on table). anyway, hstore is more performant rather than jsonb but both of them could treat NEW and OLD as hashmap (or associative array) Il

Re: Trigger Function question

> On Jul 10, 2023, at 11:46, DAVID ROTH wrote: > > Is there a way to get new.* into a jsonb column? The to_jsonb() function accepts a row type like NEW.*, and returns a JSONB object with the keys as column names.

Re: Trigger Function question

> > Is there a way to get new.* into a jsonb column? > select json_object_agg(js.key, js.value) from json_each_text(row_to_json(new.*)) js Marcos

Re: Trigger Function question

I saw your message about a "few" columns and missed the new.* notation. Is there a way to get new.* into a jsonb column? > On 07/10/2023 2:38 PM EDT Christophe Pettus wrote: > > > > On Jul 10, 2023, at 11:37, DAVID ROTH wrote: > > > > Thanks for t

Re: Trigger Function question

I was hoping that NEW could be treated as a record or as an arrayy similar to pg_argv. > On 07/10/2023 2:31 PM EDT Christophe Pettus wrote: > > > > On Jul 10, 2023, at 11:29, DAVID ROTH wrote: > > > > I want to use a single trigger function to log multiple tabl

Re: Trigger Function question

> On Jul 10, 2023, at 11:37, DAVID ROTH wrote: > > Thanks for the example. I have a test trigger now that does that but my > application needs all of the columns. I'm not quite sure I understanding. Logging NEW.* and OLD.* *does* get all the columns, without having to sp

Re: Trigger Function question

Thanks for the example. I have a test trigger now that does that but my application needs all of the columns. > On 07/10/2023 2:31 PM EDT Christophe Pettus wrote: > > > > On Jul 10, 2023, at 11:29, DAVID ROTH wrote: > > > > I want to use a single trigger func

Re: Trigger Function question

> On Jul 10, 2023, at 11:29, DAVID ROTH wrote: > > I want to use a single trigger function to log multiple tables and the tables > have different columns. I can get the names of the columns from the catalog. > But I have not been able to figure out how to get NEW.x when

Re: Trigger Function question

I want to use a single trigger function to log multiple tables and the tables have different columns. I can get the names of the columns from the catalog. But I have not been able to figure out how to get NEW.x when x is not known until run time. > On 07/10/2023 2:23 PM EDT Christo

Re: Trigger Function question

> On Jul 10, 2023, at 11:20, DAVID ROTH wrote: > > In a trigger function, is there a way to get a list of all of the columns in > the triggering table? You can get the table that the trigger fired on with TG_TABLE_SCHEMA and TG_TABLE_NAME, and then query the system catalogs t

Trigger Function question

In a trigger function, is there a way to get a list of all of the columns in the triggering table? I want to be able to use a single trigger function with multiple tables. Thanks

Re: event trigger should provide more details

.GA26924%40alvherre.pgsql). > If I cannot get such info from pg_event_trigger_ddl_commands, I may need to > maintain schema snapshots myself and diff the old and new snapshots upon an > alter table/view event. Which way should I go? Thanks a lot. Right off the bat, I would combi

Re: event trigger should provide more details

2023 at 2:42 PM Erik Wienhold wrote: > > On 30/05/2023 22:23 CEST Lian Jiang wrote: > > > > I plan to create an event trigger to detect schema change (e.g. > add/remove > > a column, change column type), and write it into a separate table (e.g. > > EVENTS). Then a

Re: event trigger should provide more details

> On 30/05/2023 22:23 CEST Lian Jiang wrote: > > I plan to create an event trigger to detect schema change (e.g. add/remove > a column, change column type), and write it into a separate table (e.g. > EVENTS). Then a process periodically reads this table to send schema change

event trigger should provide more details

hi, I plan to create an event trigger to detect schema change (e.g. add/remove a column, change column type), and write it into a separate table (e.g. EVENTS). Then a process periodically reads this table to send schema change notification. However, the event trigger <https://www.postgresql.

Re: Trigger questions

On Thu, May 4, 2023 at 7:04 AM Justin wrote: > > > On Thu, May 4, 2023 at 9:49 AM DAVID ROTH wrote: > >> 1) Can I create a trigger on a view? >> 2) Do triggers cascade? >> >> Say I have an insert trigger on a table. >> And, I have an insert trigger on

Re: Trigger questions

On 5/4/23 07:03, Justin wrote: On Thu, May 4, 2023 at 9:49 AM DAVID ROTH <mailto:adapt...@comcast.net>> wrote: __ 1) Can I create a trigger on a view? 2) Do triggers cascade? Say I have an insert trigger on a table. And, I have an insert trigger on a view that r

Re: Trigger questions

On Thu, May 4, 2023 at 9:49 AM DAVID ROTH wrote: > 1) Can I create a trigger on a view? > 2) Do triggers cascade? > > Say I have an insert trigger on a table. > And, I have an insert trigger on a view that references this table > If I do an insert on the view, will both tri

Trigger questions

1) Can I create a trigger on a view? 2) Do triggers cascade? Say I have an insert trigger on a table. And, I have an insert trigger on a view that references this table If I do an insert on the view, will both triggers fire?

Re: wiki.postgres ​ Tighten trigger permission checks already resolved

jian he writes: > The following Todo item seems already resolved in pg15. > https://wiki.postgresql.org/wiki/Todo#Triggers >> Tighten trigger permission checks >> - Security leak with trigger functions? >> <http://archives.postgresql.org/pgsql-hackers/2006-12/ms

wiki.postgres ​ Tighten trigger permission checks already resolved

Hi. The following Todo item seems already resolved in pg15. https://wiki.postgresql.org/wiki/Todo#Triggers > Tighten trigger permission checks > >- Security leak with trigger functions? ><http://archives.postgresql.org/pgsql-hackers/2006-12/msg00564.php> > > Bu

Re: alter table rename column can event trigger capture new column name

On Mon, Apr 24, 2023 at 10:46 AM jian he wrote: > > hi. > alter table t1 rename col1 to id. > > Is it possible to use an event trigger to capture the new column name > "id"? > > > sorry for the noise. I figured it out. substring(_object_identity FROM (

alter table rename column can event trigger capture new column name

hi. alter table t1 rename col1 to id. Is it possible to use an event trigger to capture the new column name "id"?

Re: Call a Normal function inside a Trigger Function

EATE OR REPLACE FUNCTION public.child_fnc(token character varying) > RETURNS void > LANGUAGE plpgsql > AS $function$ > BEGIN > IF token = 'yes' THEN > RAISE NOTICE 'Child'; > END IF; > END; > $function$ > ; > > > CRE

Re: Call a Normal function inside a Trigger Function

>> Hello, >>> >>> Is it possible to call a function inside a trigger function ? >>> Any idea or link are welcome. Thanks in advance >>> >> >> sure, there is not any limit. >> > CREATE OR REPLACE FUNCTION allow_update(d date) RETURNS boo

Re: Call a Normal function inside a Trigger Function

= 'yes' THEN RAISE NOTICE 'Child'; END IF; END; $function$ ; CREATE OR REPLACE FUNCTION public.parent_fnc() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN RAISE NOTICE 'Id is %', NEW.id; RAISE NOTICE 'Fld_1 is %', NEW.fld_1; RAISE NOTICE

Re: Call a Normal function inside a Trigger Function

Can I have an example please? Or a link On Sun, 16 Apr 2023, 17:08 Pavel Stehule, wrote: > Hi > > > ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès > napsal: > >> Hello, >> >> Is it possible to call a function inside a trigger function ? >> Any i

Re: Call a Normal function inside a Trigger Function

Hi ne 16. 4. 2023 v 16:15 odesílatel FOUTE K. Jaurès napsal: > Hello, > > Is it possible to call a function inside a trigger function ? > Any idea or link are welcome. Thanks in advance > sure, there is not any limit. Regards Pavel > > Best Regards > -- > Jaurès FOUTE >

Re: Call a Normal function inside a Trigger Function

> On 16/04/2023 16:18 CEST FOUTE K. Jaurès wrote: > > Is it possible to call a function inside a trigger function ? > Any idea or link are welcome. Thanks in advance Depends on what you want to do with the return value. Use PERFORM to ignore the result. [0] Use SELECT INTO to han

Call a Normal function inside a Trigger Function

Hello, Is it possible to call a function inside a trigger function ? Any idea or link are welcome. Thanks in advance Best Regards -- Jaurès FOUTE

Re: Getting the exact SQL from inside an event trigger

cuted. I didn't fully understand all the detail, but did notice that it maintains a stack of statements for the current execution, presumably so it knows if the trigger has been called already, e.g. in the case of a DO block. This indicates to me that I can't do what I would like in pure PLPGSQL triggers. -Joe

Re: Getting the exact SQL from inside an event trigger

On Thu, Mar 02, 2023 at 11:12:37AM +, Joe Wildish wrote: > We are using event triggers to capture DDL for subsequent replay on a logical > replica. This might be a bit different answer from what you expect, but have you seen pgl_ddl_deploy project? Best regards, depesz

Getting the exact SQL from inside an event trigger

whatever it finds. We have declared a trigger on the ddl_command_end event for this purpose. We can get the SQL from running current_query() inside the trigger; oddly, the pg_event_trigger_ddl_commands() function does have an attribute called "command", typed as "

Re: How to avoid Trigger ping/pong / infinite loop

On Thu, Feb 16, 2023 at 9:43 AM Dominique Devienne wrote: > Hi. I have a large "legacy" code base that write information necessary for > Row-Level-Security in a highly denormalized custom had-hoc text format for > values, in key-value pairs in a table, which would be either impossible or > too

Re: How to avoid Trigger ping/pong / infinite loop

On 2/16/23 10:21, Dominique Devienne wrote: On Thu, Feb 16, 2023 at 6:58 PM Adrian Klaver > wrote: "... which would be either impossible or too slow to base any RLS policy on." and "At time point, changing the legacy code base is not really

Re: DELETE trigger, direct or indirect?

ng the old name. How? Again how would you determine where the action started? Deleting a Foo cascades to the _member assoc-table. If the cascade is SET NULL, then I can have an UPDATE TRIGGER on _member. I no longer care where the action started. What happens if an entity_list v

Re: How to avoid Trigger ping/pong / infinite loop

>> Are there techniques for situations like this? Just have two triggers, one for each column, and ensure that if your trigger doesn’t change the value then it doesn’t do an update on the other column. Each time you do update both triggers will run but only one will make a chan

Re: How to avoid Trigger ping/pong / infinite loop

On Thu, Feb 16, 2023 at 10:43 AM Dominique Devienne wrote: > Are there techniques for situations like this? > > This question is not too far from my earlier question, in the sense that a > trigger would need to know the context in which it was triggered, i.e. > directly (then u

Re: DELETE trigger, direct or indirect?

in how would you determine where the action started? Deleting a Foo cascades to the _member assoc-table. If the cascade is SET NULL, then I can have an UPDATE TRIGGER on _member. I no longer care where the action started. > Then an INSERT trigger on entity could locate any (indexed)

Re: DELETE trigger, direct or indirect?

action started? > Deleting a Foo cascades to the _member assoc-table. If the cascade is SET NULL, then I can have an UPDATE TRIGGER on _member. I no longer care where the action started. > > Then an INSERT trigger on entity could locate any (indexed) "stashed" > > entity nam

Re: How to avoid Trigger ping/pong / infinite loop

On Thu, Feb 16, 2023 at 6:58 PM Adrian Klaver wrote: > > "... which would be either impossible or too slow to base any RLS policy > on." > > and > > "At time point, changing the legacy code base is not really an option..." > > seem to be at odds. > I don't see why you say that. So is the

Re: How to avoid Trigger ping/pong / infinite loop

he normalized and denormalized information (necessarily duplicated then), server-side via triggers, w/o running into infinite loops. A VIEW over both sets of data? Are there techniques for situations like this? This question is not too far from my earlier question, in the sense that a trigger

Re: DELETE trigger, direct or indirect?

and enity(entity)_list the same for a given entity? transfering the old entity name to it thanks to an UPDATE on entity_name, thus preserving the old name. How? Again how would you determine where the action started? Then an INSERT trigger on entity could locate any (indexed) "stashed

  1   2   3   4   5   6   7   >