Обсуждение: Re: [HACKERS] function for setting/getting same timestamp during whole transaction
Re: [HACKERS] function for setting/getting same timestamp during whole transaction
От
Gurjeet Singh
Дата:
Removing -hackers and adding -general On Wed, Feb 6, 2013 at 7:26 AM, Miroslav =C5=A0imul=C4=8D=C3=ADk <simulcik.miro@gmail.com>wrote: > > > >> Alternately, you might be able to use a custom GUC from a rather smalle= r >> 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. > I think you can do it by using 2 GUCs. Put this at the beginning of your trigger: if current_setting(my_var.now) !=3D now() then -- We're executing this code for the first time in this transaction set_config(my_var.now, now()) set_config(my_var.my_ts, clock_timestamp) end; -- Go on happily use my_var.my_ts to stamp your data. HTH, --=20 Gurjeet Singh http://gurjeet.singh.im/
Re: [HACKERS] function for setting/getting same timestamp during whole transaction
От
Miroslav Šimulčík
Дата:
Nice. This solves problem with clearing of session variables. Thank you
Miro
2013/2/7 Gurjeet Singh <gurjeet@singh.im>
Removing -hackers and adding -general
On Wed, Feb 6, 2013 at 7:26 AM, Miroslav Šimulčík <simulcik.miro@gmail.com> wrote:
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.
I think you can do it by using 2 GUCs.
Put this at the beginning of your trigger:
if current_setting(my_var.now) != now() then
-- We're executing this code for the first time in this transaction
set_config(my_var.now, now())
set_config(my_var.my_ts, clock_timestamp)
end;
-- Go on happily use my_var.my_ts to stamp your data.
HTH,
--
Gurjeet Singh
http://gurjeet.singh.im/