Thread: Problem with partitioning

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

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

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

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