[GENERAL] Problem with partitioning

[JotaComm]

Hello,

I'm working with partitioning and I have one trigger for insert and update
on parent table, but my trigger is not working for update operations. The
problem is: the trigger is not executed for update just for insert. I put a
raise notice message in my function to verify the update operation but the
raise notice isn't printed on screen.

Thank you.

Best regards

[Adrian Klaver]

Post by JotaComm
Hello,
I'm working with partitioning and I have one trigger for insert and
update on parent table, but my trigger is not working for update
operations. The problem is: the trigger is not executed for update just
for insert. I put a raise notice message in my function to verify the
update operation but the raise notice isn't printed on screen.

An answer is contingent on seeing the trigger code.

Post by JotaComm
Thank you.
Best regards
--
JotaComm
http://jotacomm.wordpress.com

[David G. Johnston]

Post by Adrian Klaver

Post by JotaComm
Hello,
I'm working with partitioning and I have one trigger for insert and
update on parent table, but my trigger is not working for update
operations. The problem is: the trigger is not executed for update just
for insert. I put a raise notice message in my function to verify the
update operation but the raise notice isn't printed on screen.

An answer is contingent on seeing the trigger code.

​And the "CREATE TRIGGER" statement itself...

​Try:

\d+ <tablename> on the relevant table and check/show the "Triggers: "
section​.

David J.

[JotaComm]

Post by JotaComm
Hello,

Post by David G. Johnston

Post by Adrian Klaver

Post by JotaComm
Hello,
I'm working with partitioning and I have one trigger for insert and
update on parent table, but my trigger is not working for update
operations. The problem is: the trigger is not executed for update just
for insert. I put a raise notice message in my function to verify the
update operation but the raise notice isn't printed on screen.

An answer is contingent on seeing the trigger code.

​And the "CREATE TRIGGER" statement itself...

​I created a specific trigger and the problem persists. The update
trigger is not executed. My version is 9.4.​

​When I create a trigger for each child table the trigger works very well.​

Post by JotaComm

Post by David G. Johnston
\d+ <tablename> on the relevant table and check/show the "Triggers: "
section​.
David J.

​Best regards​
--
JotaComm
http://jotacomm.wordpress.com

​Thank you.​
--
JotaComm
http://jotacomm.wordpress.com

[Adrian Klaver]

​When I create a trigger for each child table the trigger works very
well.​

Without seeing any code this problem is not going to be solved.

\d+ <tablename> on the relevant table and check/show the
"Triggers: " section​.
David J.
--
JotaComm
http://jotacomm.wordpress.com
--
JotaComm
http://jotacomm.wordpress.com

[JotaComm]

Hello,

​When I create a trigger for each child table the trigger works very
well.​

​​
Without seeing any code this problem is not going to be solved.

​The trigger code:

tg_table_update AFTER UPDATE ON parent_table FOR EACH ROW EXECUTE PROCEDURE
f_table_update()

CREATE OR REPLACE FUNCTION f_table_update()

RETURNS TRIGGER AS $$

BEGIN

RAISE NOTICE 'update';

UPDATE parent_table SET date=now() WHERE column_id=OLD.column_id;

RETURN NULL;

END;

$$ LANGUAGE PLPGSQL;
​

\d+ <tablename> on the relevant table and check/show the
"Triggers: " section​.
David J.

--

JotaComm
http://jotacomm.wordpress.com
--
JotaComm
http://jotacomm.wordpress.com

--
Adrian Klaver

​Thank you​

[Tom Lane]

Post by JotaComm
tg_table_update AFTER UPDATE ON parent_table FOR EACH ROW EXECUTE PROCEDURE
f_table_update()

I'm fairly sure that per-row triggers only fire on events in their table,
ie this would fire on updates to rows in parent_table itself, but not on
updates to rows in the child tables. To get that effect, attach the same
trigger procedure to all the children.

Don't recall offhand what the rules are for per-statement triggers.

regards, tom lane

[Adrian Klaver]

Post by JotaComm
Hello,
​When I create a trigger for each child table the trigger
works very
well.​
​​
Without seeing any code this problem is not going to be solved.
tg_table_update AFTER UPDATE ON parent_table FOR EACH ROW EXECUTE
PROCEDURE f_table_update()
CREATE OR REPLACE FUNCTION f_table_update()
RETURNS TRIGGER AS $$
BEGIN
RAISE NOTICE 'update';
UPDATE parent_table SET date=now() WHERE column_id=OLD.column_id;
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

In addition to what Tom said:

This is an AFTER UPDATE trigger so the original UPDATE already occurred.
Is it is expected that the OLD.column_id would still be around in the table?

The fact that you are not seeing the RAISE NOTICE 'update' could be
explained by logging settings.

Post by JotaComm
​
\d+ <tablename> on the relevant table and check/show the
"Triggers: " section​.
David J.
--
JotaComm
http://jotacomm.wordpress.com
--
JotaComm
http://jotacomm.wordpress.com
--
Adrian Klaver
​Thank you​
--
JotaComm
http://jotacomm.wordpress.com