Re: Update columns in the same table in a deferred constraint trigger - Mailing list pgsql-sql

From Andreas Joseph Krogh
Subject Re: Update columns in the same table in a deferred constraint trigger
Date
Msg-id VisenaEmail.40.554886d08120eb61.14781b24f67@tc7-on
Whole thread Raw
In response to Re: Update columns in the same table in a deferred constraint trigger  (Pavel Stehule <pavel.stehule@gmail.com>)
List pgsql-sql
På tirsdag 29. juli 2014 kl. 12:27:32, skrev Pavel Stehule <pavel.stehule@gmail.com>:
 
 
2014-07-29 12:21 GMT+02:00 Andreas Joseph Krogh <andreas@visena.com>:
På tirsdag 29. juli 2014 kl. 12:12:17, skrev Pavel Stehule <pavel.stehule@gmail.com>:
 
 
2014-07-29 12:05 GMT+02:00 Andreas Joseph Krogh <andreas@visena.com>:
På tirsdag 29. juli 2014 kl. 12:01:48, skrev Pavel Stehule <pavel.stehule@gmail.com>:
 
 
2014-07-29 11:59 GMT+02:00 Andreas Joseph Krogh <andreas@visena.com>:
På tirsdag 29. juli 2014 kl. 11:56:17, skrev Pavel Stehule <pavel.stehule@gmail.com>:
Hi
 
2014-07-29 11:52 GMT+02:00 Andreas Joseph Krogh <andreas@visena.com>:
Hi all.
 
I have this simple schema:
 
create table fisk(
    name varchar primary key,
    autofisk varchar
);
 
I want to update the column "autofisk" on commit based the value of "name", so I created this trigger:
 
CREATE OR REPLACE FUNCTION fisk_tf() returns TRIGGER AS $$
BEGIN
    raise notice 'name %', NEW.name;
    NEW.autofisk = NEW.name || CURRENT_TIMESTAMP::text;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE CONSTRAINT TRIGGER fisk_t AFTER INSERT OR UPDATE ON fisk DEFERRABLE INITIALLY DEFERRED
 
It should be BEFORE INSERT OR UPDATE trigger
 
He he, yes - I know that will work, but I need the trigger to be run as a constraint-trigger, on commit, after all the data is populated in other tables and this table.
 
It is not possible - Postgres can change data only before writing
 
Is there a work-around, so I in the trigger can issue for example:
 
update fisk set autofisk = NEW.name || CURRENT_TIMESTAMP::text where name = NEW.name;
 
without it also triggering the trigger?
 
theoretically yes - you can disable triggers via ALTER TABLE DISABLE TRIGGER
 
but then the code will be unmaintainable. Anything else is better than dependency in triggers. You should to think about different solution.
 
Sometimes triggers can be replaced by functions directly called from applications instead DML statements.
 
 
I have tried this but the commit never returns, I think because it recursively triggers the trigger again for that modification.
 
Will temporarily disabeling the trigger inside the trigger (in a transaction) work?
 
I really afraid of this strategy
 
I see, so it boils down to this being impossible at the moment.
I really want this to be at the DML-level so any modification done also updates the "autofisk"-column.
 
Are there any plans to make this work, that being modifying the same table in a trigger running on it where the modification (comming form statements inside the trigger-functino) like what I'm trying will not trigger the trigger?
 
you can use a auxiliary column with information where are from a UPDATE. This information should be used for breaking recursion. But it is not a good solution. You do some too complex.
 
Why you need it?
 
Pavel
 
(this time to the list)
 
How would I use this auxiliary column? As I understand the WHERE-condition in the trigger-definition is not deferred, and evaled only once, or is this not what you propose? Can you make an example of how to use such an auxiliary-column?
 
The reason I need this is that I will concat information from different tables based on information in the table the trigger is installed on. This information is to be updated in a column in the same table of type "tsvector" and used for searching later. I want the tsvector-column to be in the same table to be able to have a multicolumn index and avoid unnecessary JOIN'ing.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

pgsql-sql by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: Update columns in the same table in a deferred constraint trigger
Next
From: Andreas Joseph Krogh
Date:
Subject: Re: Update columns in the same table in a deferred constraint trigger