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

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. Here is code:

CREATE OR REPLACE FUNCTION get_my_timestamp (
IN in_initial_timestamp TIMESTAMPTZ
) RETURNS TIMESTAMPTZ AS
$$
DECLARE
v_ret TIMESTAMPTZ;
BEGIN
--check temp table existence
PERFORM
1
FROM
pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
WHERE
c.relkind IN ('r','') AND
c.relname = 'timestamp_storage' AND
pg_catalog.pg_table_is_visible(c.oid) AND
n.nspname LIKE 'pg_temp%';
IF NOT FOUND THEN
CREATE TEMP TABLE timestamp_storage (
my_timestamp TIMESTAMPTZ
) ON COMMIT DELETE ROWS;
END IF;
--select timestamp
SELECT
my_timestamp
INTO
v_ret
FROM
timestamp_storage;
IF NOT FOUND THEN
INSERT INTO timestamp_storage(my_timestamp)
VALUES (in_initial_timestamp)
RETURNING my_timestamp
INTO v_ret;
END IF;

RETURN v_ret;
END;
$$ LANGUAGE plpgsql;

Example:
begin;
select get_my_timestamp(clock_timestamp());
get_my_timestamp
----------------------------
2013-02-06 11:07:33.698+01
select get_my_timestamp(clock_timestamp());
get_my_timestamp
----------------------------
2013-02-06 11:07:33.698+01
commit;
select get_my_timestamp(clock_timestamp());
get_my_timestamp
----------------------------
2013-02-06 11:09:02.406+01

Is there any more effective way of accomplishing this? Maybe in different
language.

Regards,
Miroslav Simulcik

Hi,

I dont have access to pg at this moment... But:

BEGIN;

SELECT now();

SELECT clock_timestamp();

SELECT now();

SELECT pg_sleep(100);

SELECT now();

cCOMMIT;

Now() should always return the same, very first, result...

On Wednesday, February 6, 2013, 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. Here is code:
>
> CREATE OR REPLACE FUNCTION get_my_timestamp (
> IN in_initial_timestamp TIMESTAMPTZ
> ) RETURNS TIMESTAMPTZ AS
> $$
> DECLARE
> v_ret TIMESTAMPTZ;
> BEGIN
> --check temp table existence
> PERFORM
> 1
> FROM
> pg_catalog.pg_class c
> JOIN pg_catalog.pg_namespace n
> ON n.oid = c.relnamespace
> WHERE
> c.relkind IN ('r','') AND
> c.relname = 'timestamp_storage' AND
> pg_catalog.pg_table_is_visible(c.oid) AND
> n.nspname LIKE 'pg_temp%';
> IF NOT FOUND THEN
> CREATE TEMP TABLE timestamp_storage (
> my_timestamp TIMESTAMPTZ
> ) ON COMMIT DELETE ROWS;
> END IF;
> --select timestamp
> SELECT
> my_timestamp
> INTO
> v_ret
> FROM
> timestamp_storage;
> IF NOT FOUND THEN
> INSERT INTO timestamp_storage(my_timestamp)
> VALUES (in_initial_timestamp)
> RETURNING my_timestamp
> INTO v_ret;
> END IF;
>
> RETURN v_ret;
> END;
> $$ LANGUAGE plpgsql;
>
> Example:
> begin;
> select get_my_timestamp(clock_timestamp());
> get_my_timestamp
> ----------------------------
> 2013-02-06 11:07:33.698+01
> select get_my_timestamp(clock_timestamp());
> get_my_timestamp
> ----------------------------
> 2013-02-06 11:07:33.698+01
> commit;
> select get_my_timestamp(clock_timestamp());
> get_my_timestamp
> ----------------------------
> 2013-02-06 11:09:02.406+01
>
> Is there any more effective way of accomplishing this? Maybe in different
> language.
>
> Regards,
> Miroslav Simulcik
>

This is not what I'm looking for. now() returns transaction start time. I
need to set my own time anytime in transaction and then use that time later.

Miro

2013/2/6 Misa Simic <misa(dot)simic(at)gmail(dot)com>

> Hi,
>
>
> I dont have access to pg at this moment... But:
>
> BEGIN;
>
> SELECT now();
>
> SELECT clock_timestamp();
>
> SELECT now();
>
> SELECT pg_sleep(100);
>
> SELECT now();
>
> cCOMMIT;
>
>
>
> Now() should always return the same, very first, result...
>
>
>
>
> On Wednesday, February 6, 2013, 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. Here is code:
>>
>> CREATE OR REPLACE FUNCTION get_my_timestamp (
>> IN in_initial_timestamp TIMESTAMPTZ
>> ) RETURNS TIMESTAMPTZ AS
>> $$
>> DECLARE
>> v_ret TIMESTAMPTZ;
>> BEGIN
>> --check temp table existence
>> PERFORM
>> 1
>> FROM
>> pg_catalog.pg_class c
>> JOIN pg_catalog.pg_namespace n
>> ON n.oid = c.relnamespace
>> WHERE
>> c.relkind IN ('r','') AND
>> c.relname = 'timestamp_storage' AND
>> pg_catalog.pg_table_is_visible(c.oid) AND
>> n.nspname LIKE 'pg_temp%';
>> IF NOT FOUND THEN
>> CREATE TEMP TABLE timestamp_storage (
>> my_timestamp TIMESTAMPTZ
>> ) ON COMMIT DELETE ROWS;
>> END IF;
>> --select timestamp
>> SELECT
>> my_timestamp
>> INTO
>> v_ret
>> FROM
>> timestamp_storage;
>> IF NOT FOUND THEN
>> INSERT INTO timestamp_storage(my_timestamp)
>> VALUES (in_initial_timestamp)
>> RETURNING my_timestamp
>> INTO v_ret;
>> END IF;
>>
>> RETURN v_ret;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> Example:
>> begin;
>> select get_my_timestamp(clock_timestamp());
>> get_my_timestamp
>> ----------------------------
>> 2013-02-06 11:07:33.698+01
>> select get_my_timestamp(clock_timestamp());
>> get_my_timestamp
>> ----------------------------
>> 2013-02-06 11:07:33.698+01
>> commit;
>> select get_my_timestamp(clock_timestamp());
>> get_my_timestamp
>> ----------------------------
>> 2013-02-06 11:09:02.406+01
>>
>> Is there any more effective way of accomplishing this? Maybe in different
>> language.
>>
>> Regards,
>> Miroslav Simulcik
>>
>

2013/2/6 Miroslav Šimulčík <simulcik(dot)miro(at)gmail(dot)com>:
> This is not what I'm looking for. now() returns transaction start time. I
> need to set my own time anytime in transaction and then use that time later.
>
> Miro
>
>
> 2013/2/6 Misa Simic <misa(dot)simic(at)gmail(dot)com>
>>
>> Hi,
>>
>>
>> I dont have access to pg at this moment... But:
>>
>> BEGIN;
>>
>> SELECT now();
>>
>> SELECT clock_timestamp();
>>
>> SELECT now();
>>
>> SELECT pg_sleep(100);
>>
>> SELECT now();
>>
>> cCOMMIT;
>>
>>
>>
>> Now() should always return the same, very first, result...
>>
>>
>>
>>
>> On Wednesday, February 6, 2013, 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. Here is code:
>>>
>>> CREATE OR REPLACE FUNCTION get_my_timestamp (
>>> IN in_initial_timestamp TIMESTAMPTZ
>>> ) RETURNS TIMESTAMPTZ AS
>>> $$
>>> DECLARE
>>> v_ret TIMESTAMPTZ;
>>> BEGIN
>>> --check temp table existence
>>> PERFORM
>>> 1
>>> FROM
>>> pg_catalog.pg_class c
>>> JOIN pg_catalog.pg_namespace n
>>> ON n.oid = c.relnamespace
>>> WHERE
>>> c.relkind IN ('r','') AND
>>> c.relname = 'timestamp_storage' AND
>>> pg_catalog.pg_table_is_visible(c.oid) AND
>>> n.nspname LIKE 'pg_temp%';
>>> IF NOT FOUND THEN
>>> CREATE TEMP TABLE timestamp_storage (
>>> my_timestamp TIMESTAMPTZ
>>> ) ON COMMIT DELETE ROWS;
>>> END IF;
>>> --select timestamp
>>> SELECT
>>> my_timestamp
>>> INTO
>>> v_ret
>>> FROM
>>> timestamp_storage;
>>> IF NOT FOUND THEN
>>> INSERT INTO timestamp_storage(my_timestamp)
>>> VALUES (in_initial_timestamp)
>>> RETURNING my_timestamp
>>> INTO v_ret;
>>> END IF;
>>>
>>> RETURN v_ret;
>>> END;
>>> $$ LANGUAGE plpgsql;
>>>
>>> Example:
>>> begin;
>>> select get_my_timestamp(clock_timestamp());
>>> get_my_timestamp
>>> ----------------------------
>>> 2013-02-06 11:07:33.698+01
>>> select get_my_timestamp(clock_timestamp());
>>> get_my_timestamp
>>> ----------------------------
>>> 2013-02-06 11:07:33.698+01
>>> commit;
>>> select get_my_timestamp(clock_timestamp());
>>> get_my_timestamp
>>> ----------------------------
>>> 2013-02-06 11:09:02.406+01
>>>
>>> Is there any more effective way of accomplishing this? Maybe in different
>>> language.

probably you can use a little bit cheaper session variables

test to system tables is slower then trapping error - just try to read
from tmp and when a read fails, then create table

probably C trigger can be very effective, possible to use this
technique - http://postgres.cz/wiki/Funkce_rownum%28%29 (sorry, it is
in Czech language)

Regards

Pavel Stehule

>>>
>>> Regards,
>>> Miroslav Simulcik
>
>

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

>
> probably you can use a little bit cheaper session variables
>

I rejected session variables, because they don't get cleared at the end of
transaction if somebody set value on session level. So I can't decide if
new transaction started.

this is good (variable is cleared at the end of transaction):
begin;
set local test.value to 123;
show test.value;
test.value
------------
123
commit;
show test.value; --cleared => transaction ended
test.haha
-----------

but this is bad:
begin;
set local test.value to 123;
show test.value;
test.value
------------
123
set test.value to 456;
commit;
show test.value; --not cleared
test.haha
-----------
456

> test to system tables is slower then trapping error - just try to read
> from tmp and when a read fails, then create table
>

Ok I will try reading from temp table directly with error trapping and
compare times.

> probably C trigger can be very effective, possible to use this
> technique - http://postgres.cz/wiki/Funkce_rownum%28%29 (sorry, it is
> in Czech language)
>

I'm from Slovakia so I don't have problem with czech language, but I'm not
sure how to do it in C function without using temp table, because I need to
clear variable at the end/start of transaction. Any hints?

Miro

> "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.

>
> 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.
>

Thanks for advices. Maybe with some help I will be able to write C function
that can handle my problem.

Miro

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
>

Removing -hackers and adding -general

On Wed, Feb 6, 2013 at 7:26 AM, Miroslav Šimulčík
<simulcik(dot)miro(at)gmail(dot)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/

Nice. This solves problem with clearing of session variables. Thank you

Miro

2013/2/7 Gurjeet Singh <gurjeet(at)singh(dot)im>

> Removing -hackers and adding -general
>
>
> On Wed, Feb 6, 2013 at 7:26 AM, Miroslav Šimulčík <simulcik(dot)miro(at)gmail(dot)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/
>
>

On Wed, Feb 6, 2013 at 7:26 AM, Miroslav Šimulčík
<simulcik(dot)miro(at)gmail(dot)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 might be confused here, but I think the point is that if you pass
"true" as the third argument to set_config, the setting lasts only for
the duration of the current transaction, like SET LOCAL. Which I
think solves your problem.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> I might be confused here, but I think the point is that if you pass
> "true" as the third argument to set_config, the setting lasts only for
> the duration of the current transaction, like SET LOCAL. Which I
> think solves your problem.

Yes, but at the end of transaction it is reset to the value it has before
transaction (session level value), which can be nonempty. I wrote example
about this problem lately in this thread.