Re: function for setting/getting same timestamp during whole transaction

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