Re: transction_timestamp() inside of procedures

Hi,

On 2018-09-20 19:40:40 -0400, Bruce Momjian wrote:
> This function shows that only clock_timestamp() advances inside a
> procedure, not statement_timestamp() or transaction_timestamp():
> 
> 	CREATE OR REPLACE PROCEDURE test_timestamp () AS $$
> 	DECLARE
> 	        str TEXT;
> 	BEGIN
> 	        WHILE TRUE LOOP
> 	                -- clock_timestamp() is updated on every loop
> 	                SELECT clock_timestamp() INTO str;
> 	                RAISE NOTICE 'clock       %', str;
> 	                SELECT statement_timestamp() INTO str;
> 	                RAISE NOTICE 'statement   %', str;
> 	                SELECT transaction_timestamp() INTO str;
> 	                RAISE NOTICE 'transaction %', str;
> 	                COMMIT;
> 	
> 	                PERFORM pg_sleep(2);
> 	        END LOOP;
> 	END
> 	$$ LANGUAGE plpgsql;
> 
> 	CALL test_timestamp();
> 	NOTICE:  clock       2018-09-20 19:38:22.575794-04
> 	NOTICE:  statement   2018-09-20 19:38:22.575685-04
> 	NOTICE:  transaction 2018-09-20 19:38:22.575685-04
> 
> -->	NOTICE:  clock       2018-09-20 19:38:24.578027-04
> 	NOTICE:  statement   2018-09-20 19:38:22.575685-04
> 	NOTICE:  transaction 2018-09-20 19:38:22.575685-04
> 
> This surprised me since I expected a new timestamp after commit.  Is
> this something we want to change or document?  Are there other
> per-transaction behaviors we should adjust?

ISTM this is an issue that belongs on the open items list. Peter, could
you comment?

Greetings,

Andres Freund