Per-statement trigger in Foreign tables in Posgregsql 9.4 (through Foreign-data wrapper)

From: Andrea Martino <ciaccia(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Per-statement trigger in Foreign tables in Posgregsql 9.4 (through Foreign-data wrapper)
Date: 2016-08-03 11:55:54
Message-ID: CANr8AhLWKv2HBBQFNM0ZVGJCRs4ShsVVYVMfdkeQVRfJraeSTw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi everybody,
Yesterday I noticed a strange behaviour, I wonder if it is a bug, a
non-documented feature or just me. If this is the expected behaviour the
documentation should be updated accordingly. This happens on Posgresql 9.4.

Consider the following SQL script, where I insert 4 rows into a table using
a single insert statement and at the end a per-statement trigger is
executed:

create table dbpkg.tmp
(
user_uuid uuid,
role_uuid uuid
);

create table dbpkg.user_role
(
user_uuid uuid,
role_uuid uuid
);

CREATE FUNCTION dbpkg.dummy_fn() RETURNS trigger AS $$
BEGIN
RAISE NOTICE 'DUMMY';
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER insert_trigger
AFTER INSERT ON dbpkg.user_role
FOR STATEMENT EXECUTE PROCEDURE dbpkg.dummy_fn();

-- put some values into the temporary table tmp
insert into dbpkg.tmp(user_uuid, role_uuid)
values (uuid_generate_v4(), uuid_generate_v4()),
(uuid_generate_v4(), uuid_generate_v4()),
(uuid_generate_v4(), uuid_generate_v4()),
(uuid_generate_v4(), uuid_generate_v4());

-- insert all the values from tmp into user_role
insert into dbpkg.user_role(user_uuid, role_uuid)
select user_uuid, role_uuid from dbpkg.tmp;

drop trigger insert_trigger on dbpkg.user_role;
drop function dbpkg.dummy_fn();
drop table dbpkg.user_role;
drop table dbpkg.tmp;

When I execute this locally (i.e. without any foreign table) everything
works great. The per-statement trigger dbpkg.dummy_fn is executed only once.

If otherwise I do the same using a FDW (i.e the dbpkg.user_role table is
declared in a second DB using CREATE FOREIGN TABLE ... SERVER ... OPTIONS
...), the per-statement trigger is executed 4 times, once for every row
inserted.

I don't know the FDW internals, but it looks like the insert select
statement in this case generates more than one insert.

Can someone please shed some light on this?

Thanks in advance
Andrea

Browse pgsql-general by date

  From Date Subject
Next Message Michael Paquier 2016-08-03 12:12:24 Re: [SPAM] Re: [SPAM] Re: WAL directory size calculation
Previous Message Edson F. Lidorio 2016-08-03 11:40:26 My Postgresql is inaccessible in Windows 8.1