function for setting/getting same timestamp during whole transaction

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:

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>

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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&gt;
http://www.postgresql.org/docs/current/static/functions-datetime.html
<http://www.postgresql.org/docs/8.2/static/functions-datetime.html&gt;

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&lt;http://www.postgresql.org/docs/9.1/static/functions-admin.html&gt;
http://www.postgresql.org/docs/current/static/functions-datetime.html&lt;http://www.postgresql.org/docs/8.2/static/functions-datetime.html&gt;

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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/

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

Miro

2013/2/7 Gurjeet Singh <gurjeet@singh.im>

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.