> "As fast as possible" and "PL/PgSQL function" don't go that well together.
> PL/PgSQL is well and good for a great many jobs, but I doubt this is one of
> them.
>
Yes, I know. It was just example to demostrate functionality I need.
If you're willing to spend the time to do it, consider writing a simple C
> extension function to do this job. It'll be a heck of a lot faster, though
> you'd need to be pretty careful about handing subtransactions.
>
I don't know much about writing C extensions. Are there any good resources
explaining this topic in deep? I also need some tips on how to ensure that
variable will be cleared at the start/end of transaction.
> Alternately, you might be able to use a custom GUC from a rather smaller
> PL/PgSQL function. At transaction start, issue:
>
> set_config('myapp.trigger_time', '', 't');
>
This is problem with using custom GUC - clearing variable at transaction
start. Without clearing it's not sufficient solution (see my response to
Pavel's mail). I don't want to do clearing from application and as far as i
know there is not "transaction start" trigger.
>
> to define the var and make sure that subsequent current_setting() calls
> will not report an error. Then in your trigger, check the value and set it
> if it's empty:
>
> current_setting('myapp.trigger_time')
>
> followed by a:
>
> set_config('myapp.trigger_time',clock_timestamp::text,'t')
>
> if it's empty. I haven't tested this approach. You could avoid the need
> for the initial set_config by using a BEGIN ... EXCEPTION block to trap the
> error, but this uses subtransactions and would affect performance quite
> significantly.
>
> http://www.postgresql.org/docs/current/static/functions-admin.html<http://www.postgresql.org/docs/9.1/static/functions-admin.html>
> http://www.postgresql.org/docs/current/static/functions-datetime.html<http://www.postgresql.org/docs/8.2/static/functions-datetime.html>
>
> Custom GUCs don't seem to appear in the pg_settings view or be output by
> the pg_show_all_settings() function the view is based on, so I don't think
> you can use an EXISTS test on pg_settings as an alternative. Run the
> set_config on transaction start, or consider implementing a C function to
> do the job.
>
Thanks for advices. Maybe with some help I will be able to write C function
that can handle my problem.
Miro
