Re: triggers and execute...

On Tue, Apr 28, 2009 at 11:24 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Apr 28, 2009 at 10:46 PM, David Fetter <david@fetter.org> wrote:
>> On Tue, Apr 28, 2009 at 08:20:34PM -0600, Scott Marlowe wrote:
>>> On Mon, Apr 27, 2009 at 3:24 PM, Richard Broersma
>>> <richard.broersma@gmail.com> wrote:
>>> > On Mon, Apr 27, 2009 at 1:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>> >> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
>>> >>
>>> >> create or replace function page_access_insert_trigger ()
>>> >> returns trigger as $$
>>> >> DECLARE
>>> >>        part text;
>>> >>        q text;
>>> >> BEGIN
>>> >>        part = to_char(new."timestamp",'YYYYMMDD');
>>> >>        q = 'insert into page_access_'||part||' values (new.*)';
>>> >> ...
>>> >>
>>> >> When I create it and try to use it I get this error:
>>> >> ERROR:  NEW used in query that is not in a rule
>>> >> CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"
>>> >
>>> > At this point I don't think that there is a way for this function to
>>> > know the correct table type of new.* since page_access_... is still
>>> > only a concatenated string.  There there a way to cast new.* to the
>>> > correct table type as part of this insert statement?
>>>
>>> Oh man, it just gets worse.  I really need a simple elegant solution
>>> here, because if I try to build the query by hand null inputs make
>>> life a nightmare.  I had built something like this:
>>>
>>> q = 'insert into '||schem||'.page_access_'||part||' values (
>>>                 '||new.paid||',
>>>                 '''||new.timestamp||''',
>>>                 '||new.total_time||',
>>>                 '''||new.http_host||''',
>>>                 '''||new.php_self||''',
>>>                 '''||new.query_string||''',
>>>                 '''||new.remote_addr||''',
>>>                 '''||new.logged_in||''',
>>>                 '||new.uid||',
>>>                 '''||new.http_user_agent||''',
>>>                 '''||new.server_addr||''',
>>>                 '''||new.notes||'''
>>>         )';
>>>         execute q;
>>>
>>> But if any of the fields referenced are null, the whole query string
>>> is now null.  So the next step is to use coalesce to build a query
>>> string?  That get insane very quickly.  There's got to be some
>>> quoting trick or something to let me use new.*, please someone see
>>> this and know what that trick is.
>>
>> Well, you can add in piles of COALESCE, but that way madness lies.
>>
>> Instead, use dollar quoting, the appropriate quote_*() functions, and
>> this:
>>
>> http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
>
> Thanks so much!  I'm off to read up on it.  Dollar quoting, quote()
> and the wiki.  Thanks again.

OK, I wrote a quick test and it's not working.  I've tried a few
combinations here and there but nothing seems to kick it off.

create or replace function page_access_test ()
returns trigger as $$
DECLARE
        var text;
BEGIN
EXECUTE 'SELECT (' ||
         quote_literal(NEW) || '::' || TG_RELID::regclass ||
         ').' || quote_ident(http_host)
         INTO var;
        raise notice '%',var;
END;
$$ language plpgsql;

which generates the error:

ERROR:  column "http_host" does not exist

I'm pretty sure that column exists in the table.  Here's the line for
\d on page_access:

 http_host       | text

I've tried new.http_host, which when http_host='xyz' generates an
ERROR:  type "public.xyz" does not exist

It's late, I'll mess with this tomorrow.  This is really frustrating
me and I feel dirty if I resort to a cron job to create the new table.
 I've tested the basic time to do all the work on my laptop and the
code runs pretty fast there.  So checking to see if the table is there
doesn't seem a particularly expensive select.  It's on a small system
table that stays cached.  My laptop can run the main code loop with
inserts (and lying fsync of course) 1500 times per second.  Without
the check it can run 1700 a second.  We do a dozen a minute.  So
unless our application goes insane and starts inserting data a couple
thousand times faster it's a non-issue.

I want a simple, self sustaining solution that requires no cron jobs
to work.  If someone has a simple dynamic trigger example in any
scripting language like plpgsql, plperl or pltcl please post it.  I
don't want to maintain C triggers for this on a production server.  If
I can't get it working I'll implement the cron job.