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

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