Re: Update columns in the same table in a deferred constraint trigger

2014-07-29 12:49 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:

> På tirsdag 29. juli 2014 kl. 12:40:21, skrev Pavel Stehule <
> pavel(dot)stehule(at)gmail(dot)com>:
>
>
>
> 2014-07-29 12:36 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:
>>
>> På tirsdag 29. juli 2014 kl. 12:27:32, skrev Pavel Stehule <
>> pavel(dot)stehule(at)gmail(dot)com>:
>>
>>
>>
>> 2014-07-29 12:21 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:
>>>
>>> På tirsdag 29. juli 2014 kl. 12:12:17, skrev Pavel Stehule <
>>> pavel(dot)stehule(at)gmail(dot)com>:
>>>
>>>
>>>
>>> 2014-07-29 12:05 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:
>>>>
>>>> På tirsdag 29. juli 2014 kl. 12:01:48, skrev Pavel Stehule <
>>>> pavel(dot)stehule(at)gmail(dot)com>:
>>>>
>>>>
>>>>
>>>> 2014-07-29 11:59 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:
>>>>>
>>>>> På tirsdag 29. juli 2014 kl. 11:56:17, skrev Pavel Stehule <
>>>>> pavel(dot)stehule(at)gmail(dot)com>:
>>>>>
>>>>> Hi
>>>>>
>>>>> 2014-07-29 11:52 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)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
>>
>>
>> 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.
>>
>
> I am thinking so correct solution for this solution is using a function
> instead trigger or redesign a schem
>
>
> I need this function to be called whenever *any* modification (insert or
> update) is done on the main table, how do I accomplish that without using a
> trigger?
>

you will call function instead DML from application everywhere and you will
prohibit direct DML

There are not too much possibilities :(

Pavel

>
> Thanks.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas(at)visena(dot)com
> www.visena.com
> <https://www.visena.com>
>
>