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

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