On 02/06/2013 06:19 PM, Miroslav Šimulčík wrote:
Hi all,
I have deferred constraint update trigger in which I need to set same timestamp to all modified rows. The time needs to be the time of first invocation of this trigger fuction in transaciton. My intention is to set commit time to rows modified in transaction.
So I need function that will store and return given timestamp on first call in transaction and on subsequent calls will return stored timestamp. This function have to be as fast as possible to minimize the inpact on performance of trigger.
I have created a plpgsql function that uses temporal table for this task. On first invocation in transaction row with timestamp is inserted and on commit deleted. What I don't like is overhead with checks on table existence on each invocation.
"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.
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.
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');
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.htmlhttp://www.postgresql.org/docs/current/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.
-- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
