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

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