agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedUpdate columns in the same table in a deferred constraint trigger
12+ messages / 2 participants
[nested] [flat]
* Update columns in the same table in a deferred constraint trigger
@ 2014-07-29 09:52 Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Joseph Krogh @ 2014-07-29 09:52 UTC (permalink / raw)
To: pgsql-sql
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 <http://NEW.name;;
NEW.autofisk = NEW.name <http://NEW.name; || CURRENT_TIMESTAMP::text;
RETURN NEW;
END;
$$ LANGUAGE plpgsql; CREATE CONSTRAINT TRIGGER fisk_t AFTER INSERT OR
UPDATE ON fisk DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE fisk_tf(); The problem is that "autofisk" is
never populated: andreak=# begin;
BEGIN
andreak=# insert into fisk(name) values ('a');
INSERT 0 1
andreak=# commit;
NOTICE: name a
COMMIT
andreak=# table fisk;
name | autofisk
------+----------
a |
(1 row) Is it possible to do what I want, namely to update a column in a
table in an AFTER INSERT OR UPDATE constraint trigger on the same table?
Thanks. -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56
963 andreas@visena.com <mailto:andreas@visena.com> www.visena.com
<https://www.visena.com; <https://www.visena.com;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update columns in the same table in a deferred constraint trigger
@ 2014-07-29 09:56 Pavel Stehule <pavel.stehule@gmail.com>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 12+ messages in thread
From: Pavel Stehule @ 2014-07-29 09:56 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
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
Regards
Pavel
> FOR EACH ROW EXECUTE PROCEDURE fisk_tf();
>
> The problem is that "autofisk" is never populated:
> andreak=# begin;
> BEGIN
> andreak=# insert into fisk(name) values ('a');
> INSERT 0 1
> andreak=# commit;
> NOTICE: name a
> COMMIT
> andreak=# table fisk;
> name | autofisk
> ------+----------
> a |
> (1 row)
>
> Is it possible to do what I want, namely to update a column in a table in
> an AFTER INSERT OR UPDATE constraint trigger on the same table?
>
> Thanks.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com
> www.visena.com
> <https://www.visena.com;
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update columns in the same table in a deferred constraint trigger
@ 2014-07-29 09:59 Andreas Joseph Krogh <andreas@visena.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Joseph Krogh @ 2014-07-29 09:59 UTC (permalink / raw)
To: pgsql-sql
På tirsdag 29. juli 2014 kl. 11:56:17, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: Hi 2014-07-29
11:52 GMT+02:00 Andreas Joseph Krogh<andreas@visena.com
<mailto: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 <http://NEW.name;;
NEW.autofisk = NEW.name <http://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. -- Andreas Joseph Krogh CTO / Partner - Visena AS
Mobile: +47 909 56 963 andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com; <https://www.visena.com;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update columns in the same table in a deferred constraint trigger
@ 2014-07-29 10:01 Pavel Stehule <pavel.stehule@gmail.com>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 12+ messages in thread
From: Pavel Stehule @ 2014-07-29 10:01 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
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.
Regards
Pavel
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com
> www.visena.com
> <https://www.visena.com;
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update columns in the same table in a deferred constraint trigger
@ 2014-07-29 10:05 Andreas Joseph Krogh <andreas@visena.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Joseph Krogh @ 2014-07-29 10:05 UTC (permalink / raw)
To: pgsql-sql
På tirsdag 29. juli 2014 kl. 12:01:48, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 11:59
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 11:56:17, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: Hi 2014-07-29
11:52 GMT+02:00 Andreas Joseph Krogh<andreas@visena.com
<mailto: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 <http://NEW.name;;
NEW.autofisk = NEW.name <http://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 <http://NEW.name; ||
CURRENT_TIMESTAMP::text where name = NEW.name; without it also triggering the
trigger? 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?
--Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com> www.visena.com
<https://www.visena.com; <https://www.visena.com;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update columns in the same table in a deferred constraint trigger
@ 2014-07-29 10:12 Pavel Stehule <pavel.stehule@gmail.com>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 12+ messages in thread
From: Pavel Stehule @ 2014-07-29 10:12 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
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
Regards
Pavel
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com
> www.visena.com
> <https://www.visena.com;
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update columns in the same table in a deferred constraint trigger
@ 2014-07-29 10:21 Andreas Joseph Krogh <andreas@visena.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Joseph Krogh @ 2014-07-29 10:21 UTC (permalink / raw)
To: pgsql-sql
På tirsdag 29. juli 2014 kl. 12:12:17, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 12:05
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 12:01:48, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 11:59
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 11:56:17, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: Hi 2014-07-29
11:52 GMT+02:00 Andreas Joseph Krogh<andreas@visena.com
<mailto: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 <http://NEW.name;;
NEW.autofisk = NEW.name <http://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 <http://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? --
Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com> www.visena.com
<https://www.visena.com; <https://www.visena.com;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update columns in the same table in a deferred constraint trigger
@ 2014-07-29 10:27 Pavel Stehule <pavel.stehule@gmail.com>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 12+ messages in thread
From: Pavel Stehule @ 2014-07-29 10:27 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
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
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com
> www.visena.com
> <https://www.visena.com;
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update columns in the same table in a deferred constraint trigger
@ 2014-07-29 10:38 Andreas Joseph Krogh <andreas@visena.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Andreas Joseph Krogh @ 2014-07-29 10:38 UTC (permalink / raw)
To: pgsql-sql
På tirsdag 29. juli 2014 kl. 12:27:32, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 12:21
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 12:12:17, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 12:05
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 12:01:48, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 11:59
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 11:56:17, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: Hi 2014-07-29
11:52 GMT+02:00 Andreas Joseph Krogh<andreas@visena.com
<mailto: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 <http://NEW.name;;
NEW.autofisk = NEW.name <http://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 <http://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 andreas@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update columns in the same table in a deferred constraint trigger
@ 2014-07-29 10:49 Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Joseph Krogh @ 2014-07-29 10:49 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-sql
På tirsdag 29. juli 2014 kl. 12:40:21, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 12:36
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 12:27:32, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 12:21
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 12:12:17, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 12:05
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 12:01:48, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 11:59
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 11:56:17, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: Hi 2014-07-29
11:52 GMT+02:00 Andreas Joseph Krogh<andreas@visena.com
<mailto: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 <http://NEW.name;;
NEW.autofisk = NEW.name <http://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 <http://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? Thanks. --
Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com> www.visena.com
<https://www.visena.com; <https://www.visena.com;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update columns in the same table in a deferred constraint trigger
@ 2014-07-29 11:22 Pavel Stehule <pavel.stehule@gmail.com>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 12+ messages in thread
From: Pavel Stehule @ 2014-07-29 11:22 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
2014-07-29 12:49 GMT+02:00 Andreas Joseph Krogh <andreas@visena.com>:
> På tirsdag 29. juli 2014 kl. 12:40:21, skrev Pavel Stehule <
> pavel.stehule@gmail.com>:
>
>
>
> 2014-07-29 12:36 GMT+02:00 Andreas Joseph Krogh <andreas@visena.com>:
>>
>> 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
>>
>>
>> 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@visena.com
> www.visena.com
> <https://www.visena.com;
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Update columns in the same table in a deferred constraint trigger
@ 2014-07-29 11:31 Andreas Joseph Krogh <andreas@visena.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Andreas Joseph Krogh @ 2014-07-29 11:31 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-sql
På tirsdag 29. juli 2014 kl. 13:22:36, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 12:49
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 12:40:21, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 12:36
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 12:27:32, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 12:21
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 12:12:17, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 12:05
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 12:01:48, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: 2014-07-29 11:59
GMT+02:00 Andreas Joseph Krogh<andreas@visena.com <mailto:andreas@visena.com>>:
På tirsdag 29. juli 2014 kl. 11:56:17, skrev Pavel Stehule <
pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>: Hi 2014-07-29
11:52 GMT+02:00 Andreas Joseph Krogh<andreas@visena.com
<mailto: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 <http://NEW.name;;
NEW.autofisk = NEW.name <http://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 <http://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 :( I see, thanks. -- Andreas
Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com
<mailto:andreas@visena.com> www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2014-07-29 11:31 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-07-29 09:52 Update columns in the same table in a deferred constraint trigger Andreas Joseph Krogh <andreas@visena.com>
2014-07-29 09:56 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-07-29 09:59 ` Andreas Joseph Krogh <andreas@visena.com>
2014-07-29 10:01 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-07-29 10:05 ` Andreas Joseph Krogh <andreas@visena.com>
2014-07-29 10:12 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-07-29 10:21 ` Andreas Joseph Krogh <andreas@visena.com>
2014-07-29 10:27 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-07-29 10:38 ` Andreas Joseph Krogh <andreas@visena.com>
2014-07-29 10:49 Re: Update columns in the same table in a deferred constraint trigger Andreas Joseph Krogh <andreas@visena.com>
2014-07-29 11:22 ` Pavel Stehule <pavel.stehule@gmail.com>
2014-07-29 11:31 ` Andreas Joseph Krogh <andreas@visena.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox