From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Miroslav Šimulčík <simulcik(dot)miro(at)gmail(dot)com> |
Cc: | Misa Simic <misa(dot)simic(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: function for setting/getting same timestamp during whole transaction |
Date: | 2013-02-06 10:55:16 |
Message-ID: | CAFj8pRDJkmkudkLrrV37LyXmiy9A31=TLDy823sbchZyF7XY3Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | drew_hunt1976 | 2013-02-06 11:01:20 | Need help understanding WAL and checkpoints |
Previous Message | Alban Hertroys | 2013-02-06 10:39:47 | Re: "explain analyze" a procedure verbosely - to find which statement in it takes longer |
From | Date | Subject | |
---|---|---|---|
Next Message | Dimitri Fontaine | 2013-02-06 11:08:32 | Re: sql_drop Event Trigger |
Previous Message | Miroslav Šimulčík | 2013-02-06 10:39:03 | Re: function for setting/getting same timestamp during whole transaction |