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 sl

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 an

Re: DELETE trigger, direct or indirect?

On 2/16/23 10:28, Dominique Devienne wrote: On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver > wrote: You have two tables with list in their name, so are rows deleted from both. Just to be clear enity_list should actually be entity_list? Also how are e

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 change, so that will

Re: Automatic aggressive vacuum on almost frozen table takes too long

On Thu, Feb 16, 2023 at 7:44 AM Adrian Klaver wrote: > > That is, if I understand it correctly, it says that there were (and > > actually are) 2013128 pages of which 2008230 were skipped, which leaves > > 4898 blocks to be scanned. I.e. it seems that the allocated 1GB > > (autovacuum_work_mem) sho

Re: Vacuum full issue

On 2/16/23 05:18, Rama Krishnan wrote: Hi All, One of my friend project they did vacuum full  last week since one of the table column data type were CLOB after that activity the dev team found out some of the data were missing or corrupt (XML) , please explain me will vacuum full have any lim

Re: Vacuum full issue

On Thu, Feb 16, 2023 at 12:24 PM Rama Krishnan wrote: > One of my friend project they did vacuum full last week since one of the > table column data type were CLOB after that activity the dev team found out > some of the data were missing or corrupt (XML) , please explain me will > vacuum full h

Vacuum full issue

Hi All, One of my friend project they did vacuum full last week since one of the table column data type were CLOB after that activity the dev team found out some of the data were missing or corrupt (XML) , please explain me will vacuum full have any limitation like that? Thanks Ramakrishnan

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 update the other mod

Re: DELETE trigger, direct or indirect?

On 2/16/2023 12:28 PM, Dominique Devienne wrote: On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver wrote: You have two tables with list in their name, so are rows deleted from both. Just to be clear enity_list should actually be entity_list? Also how are entity and enity_list related?

Re: DELETE trigger, direct or indirect?

On Thu, Feb 16, 2023 at 6:51 PM Adrian Klaver wrote: > You have two tables with list in their name, so are rows deleted from > both. Just to be clear enity_list should actually be entity_list? > > Also how are entity and enity_list related? > I have to confess that your questions surprise me a b

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 curren

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

On 2/16/23 09:43, 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 slow to base any RLS p

Re: DELETE trigger, direct or indirect?

On 2/16/23 09:17, Dominique Devienne wrote: That is where I am headed, however it will need more information to determine whether that makes sense or not. OK, I started writing SET NULL won't help, but I'll back up and try to give more info, as requested. Pseudo SQL at this point. c

How to avoid Trigger ping/pong / infinite loop

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 slow to base any RLS policy on. The values are basically lists or maps

Re: DELETE trigger, direct or indirect?

On Thu, Feb 16, 2023 at 5:59 PM Adrian Klaver wrote: > On 2/16/23 08:55, David G. Johnston wrote: > > On Thu, Feb 16, 2023 at 9:46 AM Adrian Klaver > > wrote: > > > > On 2/16/23 05:23, Dominique Devienne wrote: > > > Hi. This is a bit unusual. We have a

Re: DELETE trigger, direct or indirect?

On 2/16/23 08:55, David G. Johnston wrote: On Thu, Feb 16, 2023 at 9:46 AM Adrian Klaver > wrote: On 2/16/23 05:23, Dominique Devienne wrote: > Hi. This is a bit unusual. We have a foreign key between two tables, > with ON DELETE CASCADE, to preser

Re: DELETE trigger, direct or indirect?

On Thu, Feb 16, 2023 at 9:46 AM Adrian Klaver wrote: > On 2/16/23 05:23, Dominique Devienne wrote: > > Hi. This is a bit unusual. We have a foreign key between two tables, > > with ON DELETE CASCADE, to preserve referential integrity. But we > > apparently also need to preserve the severed refere

Re: DELETE trigger, direct or indirect?

On 2/16/23 05:23, Dominique Devienne wrote: Hi. This is a bit unusual. We have a foreign key between two tables, with ON DELETE CASCADE, to preserve referential integrity. But we apparently also need to preserve the severed reference (by natural key, i.e. its name), to later on reconnect the tw

Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?

On 2/16/23 09:47, cen wrote: Hi, I am running the same application (identical codebase) as two separate instances to index (save) different sets of data. Both run PostgreSQL 13. The queries are the same but the content in actual databases is different. One database is around 1TB and the othe

Re: DELETE trigger, direct or indirect?

> On 16/02/2023 14:23 CET Dominique Devienne wrote: > > Hi. This is a bit unusual. We have a foreign key between two tables, with > ON DELETE CASCADE, to preserve referential integrity. But we apparently > also need to preserve the severed reference (by natural key, i.e. its name), > to later on r

Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?

On Thu, Feb 16, 2023 at 8:48 AM cen wrote: > > - does the planner take previous runs of the same query and it's > execution time into account? If not, why? > No, because that isn't how it works. And while I'm no planner expert I'm not imagining any particularly compelling argument for why it wo

Postgres undeterministically uses a bad plan, how to convince it otherwise?

Hi, I am running the same application (identical codebase) as two separate instances to index (save) different sets of data. Both run PostgreSQL 13. The queries are the same but the content in actual databases is different. One database is around 1TB and the other around 300GB. There is a

Re: Automatic aggressive vacuum on almost frozen table takes too long

On 2/15/23 22:57, Mikhail Balayan wrote: Hello, I have a big table in the actively working system, in which nothing is written for a long time, and nothing is read from it. Table size is 15GB (data only), indexes 150GB. Since the table does not change, after a while it crosses the autovacuum_

DELETE trigger, direct or indirect?

Hi. This is a bit unusual. We have a foreign key between two tables, with ON DELETE CASCADE, to preserve referential integrity. But we apparently also need to preserve the severed reference (by natural key, i.e. its name), to later on reconnect the two entities after-the-fact, should the parent row

Automatic aggressive vacuum on almost frozen table takes too long

Hello, I have a big table in the actively working system, in which nothing is written for a long time, and nothing is read from it. Table size is 15GB (data only), indexes 150GB. Since the table does not change, after a while it crosses the autovacuum_freeze_max_age and an aggressive vacuum is tri

Re: Multi-column index: Which column order

On Wed, 2023-02-15 at 22:08 -0600, Ron wrote: > On 2/15/23 21:45, Laurenz Albe wrote: > > On Wed, 2023-02-15 at 10:20 -0600, Ron wrote: > > > On 2/15/23 02:46, Laurenz Albe wrote: > > > > Both are the same. > > > > There is an old myth that says that you should use the more selective > > > > colum

Aggressive vacuum frequency on a table

Hi list, The PostgreSQL documentation says in https://www.postgresql.org/docs/15/routine-vacuuming.html#VACUUM-FOR-STATISTICS : *The maximum time that a table can go unvacuumed is two billion transactions minus the vacuum_freeze_min_age value at the time of the last aggressive vacuum.* *...* *