transction_timestamp() inside of procedures

Bruce Momjian <bruce@momjian.us>

From: Bruce Momjian <bruce@momjian.us>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2018-09-20T23:40:40Z
Lists: pgsql-hackers
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?

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +


Commits

  1. Advance transaction timestamp for intra-procedure transactions.