postgres - prevent rule/trigger from calling himself - infinite loop

Say I have the following table:

create table A ( identifier integer not null primary key, title text not null, ... -- other fields );

When executing an UPDATE on A, I necessarily don't want to only update the targeted row, but I also want to apply updates to another row in A. I tried writing a 'rewrite rule' or 'before trigger', but I always end up with an infinite-loop:

create function A(in A, in A) returns void as $$ declare i integer; begin -- do some logic which finds other row (hardcoded in this example) i = 2; -- update old row update A set title = $2.title where identifier = $1.identifier; -- apply updates to other row update A set ... where identifier = i; end; $$ language plpgsql; create rule A as on update to A do instead select A(old, new);

The data on which I tested:

insert into A (identifier, title) values (1, 'old title 1'); insert into A (identifier, title) values (2, 'old title 2'); update A set title = 'new title 1' where identifier = 1;

The same problem also arises when using a 'before trigger', instead of a 'rewrite rule'.

Is there a way to bypass a rule/trigger if needed? I cannot alter table A disable rule A after the first row and alter table A enable rule A before returning, because table A is in use, by ourself.


I managed to do this by creating a dummy inherited table on which the 'inner update' is done, instead on the table directly. This bypasses the trigger/rule.

drop table if exists A cascade; create table A ( identifier serial not null primary key, title text not null ); create table A_ ( ) inherits (A); create or replace function A() returns trigger as $$ declare i integer; begin -- create duplicate row insert into A (title) values (new.title) returning identifier into i; -- update new row update A_ set title = new.title where identifier = i; -- do not propagate update return null; end $$ language plpgsql; create trigger A before update on A for each row execute procedure A(); insert into A (title) values ('old title 1'); insert into A (title) values ('old title 2'); update A set title = 'new title 1' where identifier = 1; select * from A;

-------------Problems Reply------------

To avoid infinite loops in triggers, you need to add an extra where clause to avoid re-affecting a row multiple times:

update foo
set bar = 'baz'
where bar <> 'baz'

The same cannot be done to avoid recursion in rules, because the new queries are tossed in when the original one is (and the new ones are) getting parsed, without any consideration for the individual queries' where clauses.

Category:postgresql Views:1 Time:2011-07-24

Related post

  • Forth language EBNF rule for an infinite loop or if statement 2011-05-19

    Is there an EBNF rule that describes a Forth infinite loop or if statement? --------------Solutions------------- EBNF is used to describe syntax. A loop being infinite or otherwise wouldn't normally fall within what it would describe. As such, you'd

  • Copy constructor invokes an infinite loop 2010-01-28

    I am passing a value to copy constructor as a reference, but an infinite loop is being invoked. Here's my class: class Vector2f{ private: GLfloat x; GLfloat y; public: Vector2f(); Vector2f(const GLfloat _x, const GLfloat _y); Vector2f(const Vector2f

  • Prevent recursive trigger in PostgreSQL 2009-04-02

    How to prevent recursive execution of trigger? Let's say I want to construct a "tree-able" description on chart of account. So what I do is when a new record is inserted/updated, I update the the parent record's down_qty, so this would trigger the up

  • Preventing infinite loop data retrieval with Hibernate 2010-07-15

    I was wondering: Imagine a scenario where e.g. //POJO public class User { private String userName; private String name; private String surname; private List<Blog> blogList; //All getters and setters are found here and assume they're generated.

  • How to prevent infinite looping without ExecutionContext.CallerOrigin in Microsoft Dynamics CRM 2011? 2011-05-16

    When creating a plugin in Microsoft Dynamics CRM 4.0 you could use the following to check the origin of the event that caused the plugin to fire. public void Execute(IPluginExecutionContext context) { if (context.CallerOrigin.GetType() == CallerOrigi

  • Is an INSERT trigger recursive, or cause an infinite loop? 2011-05-20

    I have an INSERT trigger on table. When a row is inserted in table, the INSERT trigger is fired. The trigger's behaviour is such that it will insert another row into the same table. What would be the result of the INSERT statement? Does this INSERT r

  • Can one prevent Chrome causing an infinite loop when displaying alerts on the focus event, without disabling and re-enabling the event? 2011-05-27

    Consider the following code. If I click in cmbMonkeys, it causes an infinite loop of alert messages in Google Chrome. My workaround for cmbPeople works fine. Does anyone know of a another way to prevent infinite loops when displaying alerts on focus

  • JavaScript img onLoad and closures - why does this code trigger an infinite loop? 2010-08-12

    I have some Google Maps JS that plots a number of markers on a map. However, when loading the markers I noticed that the images were not loading in time and so the markers were not being placed. To get around this I modified my function that returned

  • Why does this code trigger an infinite loop? 2011-03-20

    I am writing some simple code to parse a file and return the number of lines but the little red box in eclipse won't go off so I assume I am triggering an infinite loop. Th Text file i am reading has only 10's the code: What am I doing w

  • How do I prevent a Cobol program from going into an infinite loop if no match found on 2 flat files? 2011-03-31

    A Cobol program reads a record from a first flat file and compares it to the first record on a second flat file. However, because the first record from the first flat file does not match any records on the second flat file, the Cobol program goes int

  • Preventing onchange events from getting into an infinite loop 2012-01-12

    I have two text fields, one represents a percentage and the second represents a corresponding amount. Now what I want is, when the percentage changes the amount should change and when the amount changes the percentage should change accordingly. I am

  • How to fix infinite loop in this mod_rewrite rule? 2012-03-24

    I've got the following in an .htaccess. file: RewriteEngine on RewriteBase / RewriteCond %{REQUEST_URI} !^/css(/?|/.+)$ RewriteCond %{REQUEST_URI} !^/js(/?|/.+)$ RewriteCond %{REQUEST_URI} !^/img(/?|/.+)$ RewriteRule !^/handle\.php$ /handle.php [L] I

  • should the Observer Pattern include some infinite loop detection? 2009-06-08

    Quickly going through the GoF and the Head First Design Patterns book, it seems like there is no mentioning of infinite loop detection and handling for the Observer pattern? I think if it is between 2 classes, we can be more careful about the infinit

  • Are compilers allowed to eliminate infinite loops? 2010-02-01

    Can optimizing compiler delete infinite loops, which does not changes any data, like while(1) /* noop */; From analyzing a data flow graph compiler can derive, that such loop is "dead code" without any side effects. Is deleting of infinite loops proh

  • Problems breaking an .htaccess redirect out of an infinite loop 2010-06-29

    So I have asked for help over at my weblog, scoured the internet, and pored over the examples you all have provided on here before, and I still cannot find an answer that works. Simply put, I am trying to take all traffic referred to my site from Sit

  • Event driven architecture-infinite loop 2010-09-10

    I have an event driven architecture where A is waiting for a change from B and B is waiting for a change from C and C is waiting for a change from A, forming a cycle. Now, if B changes, then A fires an event to C, which fires to B, which fires to A,

  • CPU friendly infinite loop 2011-09-13

    Writing an infinite loop is simple: while(true){ //add whatever break condition here } But this will trash the CPU performance. This execution thread will take as much as possible from CPU's power. What is the best way to lower the impact on CPU? Add

  • How to block multiple mod_rewrite passes (or infinite loops) in a .htaccess context 2011-10-17

    I'm working on a website running on a shared Apache v2.2 server, so all configuration is via .htaccess files, and I wanted to use mod_rewrite to map URLs to the filesystem in less-than-completely-straightforward way. Just for example's sake, let's sa

  • Infinite loops in Java 2011-12-20

    Look at the following infinite while loop in Java. It causes a compile-time error for the statement below it. while(true) { System.out.println("inside while"); } System.out.println("while terminated"); //Unreachable statement - compiler-error. The fo

Copyright (C), All Rights Reserved.

processed in 0.510 (s). 11 q(s)