Problem with partitioning
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
--
JotaComm
http://jotacomm.wordpress.com
On 08/03/2016 10:12 AM, JotaComm wrote:
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.
Thank you.
Best regards
--
JotaComm
http://jotacomm.wordpress.com
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Aug 3, 2016 at 1:16 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
On 08/03/2016 10:12 AM, JotaComm wrote:
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.
2016-08-03 15:20 GMT-03:00 JotaComm <jota.comm@gmail.com>:
2016-08-03 15:15 GMT-03:00 JotaComm <jota.comm@gmail.com>:
Hello,
2016-08-03 14:21 GMT-03:00 David G. Johnston <david.g.johnston@gmail.com>
:On Wed, Aug 3, 2016 at 1:16 PM, Adrian Klaver <adrian.klaver@aklaver.com
wrote:
On 08/03/2016 10:12 AM, JotaComm wrote:
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.
Try:
\d+ <tablename> on the relevant table and check/show the "Triggers: "
section.David J.
Best regards
--
JotaComm
http://jotacomm.wordpress.comThank you.
--
JotaComm
http://jotacomm.wordpress.com
--
JotaComm
http://jotacomm.wordpress.com
Import Notes
Reply to msg id not found: CAA8OQ6__kXyTO0jJ3_p83dv2Lx6gsvMDJkcmEsdebtC8r3Kj9A@mail.gmail.com
On 08/03/2016 11:24 AM, JotaComm wrote:
2016-08-03 15:20 GMT-03:00 JotaComm <jota.comm@gmail.com
<mailto:jota.comm@gmail.com>>:
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.
Try:
\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
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello,
2016-08-03 15:29 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com>:
On 08/03/2016 11:24 AM, JotaComm wrote:
2016-08-03 15:20 GMT-03:00 JotaComm <jota.comm@gmail.com
<mailto:jota.comm@gmail.com>>: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;
Try:
\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
adrian.klaver@aklaver.com
Thank you
--
JotaComm
http://jotacomm.wordpress.com
JotaComm <jota.comm@gmail.com> writes:
The trigger code:
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 08/03/2016 12:11 PM, JotaComm wrote:
Hello,
2016-08-03 15:29 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>>:On 08/03/2016 11:24 AM, JotaComm wrote:
2016-08-03 15:20 GMT-03:00 JotaComm <jota.comm@gmail.com
<mailto:jota.comm@gmail.com>
<mailto:jota.comm@gmail.com <mailto:jota.comm@gmail.com>>>: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;
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.
Try:
\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
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>Thank you
--
JotaComm
http://jotacomm.wordpress.com
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general