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

function for setting/getting same timestamp during whole transaction

From

Miroslav Šimulčík

Date:

06 February 2013, 13:19:59

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

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

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

From

Misa Simic

Date:

06 February 2013, 13:32:59

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

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

From

Miroslav Šimulčík

Date:

06 February 2013, 13:39:11

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.simic@gmail.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

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

From

Pavel Stehule

Date:

06 February 2013, 13:56:06

2013/2/6 Miroslav Šimulčík <simulcik.miro@gmail.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.simic@gmail.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
>
>

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

From

Miroslav Šimulčík

Date:

06 February 2013, 15:06:22

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

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

From

Craig Ringer

Date:

06 February 2013, 15:06:29

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

--  Craig Ringer                   http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

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

From

Miroslav Šimulčík

Date:

06 February 2013, 15:26:46

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

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

From

Pavel Stehule

Date:

06 February 2013, 15:59:40

2013/2/6 Miroslav Šimulčík <simulcik.miro@gmail.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
>

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

From

Robert Haas

Date:

08 February 2013, 21:03:54

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

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

From

Miroslav Šimulčík

Date:

09 February 2013, 12:13:28

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.