Обсуждение: Dynamic pgplsql triggers

Поиск
Список
Период
Сортировка

Dynamic pgplsql triggers

От
"Worky Workerson"
Дата:
I'm trying to trigger a whole bunch of partitions at once (initial DB
setup) using the same plpgsql trigger.  The trigger is basically the
merge trigger in the docs (i.e. UPDATE IF NOT FOUND RETURN NEW ...).

I need to use the TG_RELNAME variable within the "UPDATE" in the
trigger so that I can use the same function to trigger all of the
partitions (correct?), the problem is that I can't quite figure out
how.  I figure that I will have to use EXECUTE on a string that I
build up, right?  The problem that I'm having with this approach is
that some of the columns of NEW don't have a text conversion, and I'm
getting an error whenever the trigger fires.  Is there a way around
this and/or a better way to trigger a bunch of partitions with the
same function?

Thanks!
-Worky

Re: Dynamic pgplsql triggers

От
"Merlin Moncure"
Дата:
On 7/31/06, Worky Workerson <worky.workerson@gmail.com> wrote:
> I'm trying to trigger a whole bunch of partitions at once (initial DB
> setup) using the same plpgsql trigger.  The trigger is basically the
> merge trigger in the docs (i.e. UPDATE IF NOT FOUND RETURN NEW ...).
>
> I need to use the TG_RELNAME variable within the "UPDATE" in the
> trigger so that I can use the same function to trigger all of the
> partitions (correct?), the problem is that I can't quite figure out
> how.  I figure that I will have to use EXECUTE on a string that I
> build up, right?  The problem that I'm having with this approach is
> that some of the columns of NEW don't have a text conversion, and I'm
> getting an error whenever the trigger fires.  Is there a way around
> this and/or a better way to trigger a bunch of partitions with the
> same function?

I don't think it's possible.  however, what is possible and achieves
roughly the same affect is to query the system catalogs  (or
information schema) and via dynamic sql cut trigger
funtions/procedures by looping the results of your query.  non-dynamic
sql will usually be a bit faster than dynamic as a bonus, the only
downsie is you are creating a lot of functions, albeit in easy to
manage fashion.  If you are really clever, you can put your trigger
functions in a special schema for organizational purposes.

to do this the 'functional' way:

create or replace function create_trigger_for_table(table_name text,
schema_name text) returns void as
$$
  begin
    excecute 'create or replace function ' -- and so forth
  end;
$$;

and to invoke the function:

select create_trigger_for_table(table_name , schema_name )  from
information_schema.tables -- and so forth

regards,
merlin

Re: Dynamic pgplsql triggers

От
"Worky Workerson"
Дата:
I have scanned the archives and found the following message from 2004
dealing with v7.4, however it doesn't solve all my problems:

http://archives.postgresql.org/pgsql-sql/2004-07/msg00208.php

Here is a brief example.  I have a table db with a merge trigger given
below, shamelessly stolen from Example 36-1 in the docs.  When an
insert occurs, the number_seen is updated if the id/content are the
same, otherwise the new record is inserted.

-- Note that id is not unique
CREATE TABLE db (
    id INTEGER,
    content BYTEA,
    number_seen INTEGER
);

CREATE OR REPLACE FUNCTION merge_db() RETURNS TRIGGER AS $$
  BEGIN
    UPDATE db SET number_seen = number_seen + NEW.number_seen
      WHERE id = NEW.id AND content = NEW.content;
    IF FOUND THEN
      --Update row
      RETURN NULL;
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER db_merge_db BEFORE INSERT ON db FOR EACH ROW EXECUTE
PROCEDURE merge_db();

This works like a charm.  My 'db' table is getting rather large,
however, and I started to partition it based on the non-unique column
'id'

CREATE TABLE db_1 (
  CONSTRAINT partition_id_1 CHECK (id =1);
) INHERITS db;

CREATE TABLE db_2 .... db_100

However now I am at a loss as to how to create a single trigger
function that I can use to trigger all of the partition tables.
Basically, how do I set the db name to be dynamic?  I tried the naive
approach of just replacing 'db' with TG_RELNAME, i.e.:

    UPDATE TG_RELNAME SET number_seen = number_seen + NEW.number_seen
      WHERE id = NEW.id AND content = NEW.content;

but of course this gives me an error about $1, which is what I
figured, given that the table name can't be a parameter (right?):
'ERROR:  syntax error at or near "$1" at character 9'

So then I tried to make the statement dynamic, i.e.:

    EXECUTE 'UPDATE ' || TG_RELNAME || '
      SET number_seen = number_seen + NEW.number_seen
      WHERE id = NEW.id AND content = NEW.content;

but this gives the error: 'ERROR:  NEW used in query that is not in a
rule'.  This seems a little confusing as NEW should be visible to
trigger functions, but I assume that it has something to do with the
EXECUTE and how the planner couldn't pre-plan the SQL.

So I plod on, and try and make all the NEW args dynamic, i.e.:

    EXECUTE 'UPDATE ' || TG_RELNAME || '
      SET number_seen = number_seen + ' || NEW.number_seen || '
      WHERE id = ' || NEW.id || ' AND content = ' || NEW.content;

However now I get the error: 'ERROR:  operator does not exist:  bytea
|| ip4'.  I think I understand what is going on ... that bytea doesn't
have a text representation, right?

So I'm not quite sure where to go from here.  How do I make the UPDATE
statement in the trigger function operate against the table on which
the trigger was fired, while at the same time passing in the values in
NEW?

Any help would be greatly appreciated.

Thanks!



CREATE OR REPLACE FUNCTION merge_db() RETURNS TRIGGER AS $$
  BEGIN
    UPDATE db SET number_seen = number_seen + NEW.number_seen
      WHERE id = NEW.id AND content = NEW.content;
    IF FOUND THEN
      --Update row
      RETURN NULL;
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE PLPGSQL;


On 7/31/06, Worky Workerson <worky.workerson@gmail.com> wrote:
> I'm trying to trigger a whole bunch of partitions at once (initial DB
> setup) using the same plpgsql trigger.  The trigger is basically the
> merge trigger in the docs (i.e. UPDATE IF NOT FOUND RETURN NEW ...).
>
> I need to use the TG_RELNAME variable within the "UPDATE" in the
> trigger so that I can use the same function to trigger all of the
> partitions (correct?), the problem is that I can't quite figure out
> how.  I figure that I will have to use EXECUTE on a string that I
> build up, right?  The problem that I'm having with this approach is
> that some of the columns of NEW don't have a text conversion, and I'm
> getting an error whenever the trigger fires.  Is there a way around
> this and/or a better way to trigger a bunch of partitions with the
> same function?
>
> Thanks!
> -Worky
>

Re: Dynamic pgplsql triggers

От
"Worky Workerson"
Дата:
> I don't think it's possible.  however, what is possible and achieves
> roughly the same affect is to query the system catalogs  (or
> information schema) and via dynamic sql cut trigger
> funtions/procedures by looping the results of your query.  non-dynamic
> sql will usually be a bit faster than dynamic as a bonus, the only
> downsie is you are creating a lot of functions, albeit in easy to
> manage fashion.  If you are really clever, you can put your trigger
> functions in a special schema for organizational purposes.

Thanks for that hint.  I've been thinking about that ... I do similar
things to create all and trigger the partitions.  And, since I have a
regular naming for the partitions, I don't have to go to the catalogs.
 It seems a little excessive to have to create a different function
for each of the triggers, however, when each one is basically the
same.