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

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