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

2013/2/6 Miroslav Šimulčík <simulcik(dot)miro(at)gmail(dot)com>:
>
>> "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.

probably you cannot initialize variable on start transaction, but you
can add some callback function on

google, postgresql src: RegisterXactCallback

http://grokbase.com/t/postgresql/pgsql-hackers/055a7qgery/adding-callback-support

and some basic introduction to C PostgreSQL development
http://postgres.cz/wiki/C_a_PostgreSQL_-_intern%C3%AD_mechanismy

Regards

Pavel

>
>>
>>
>> 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/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.
>
>
> Thanks for advices. Maybe with some help I will be able to write C function
> that can handle my problem.
>
> Miro
>