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.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. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services