Date: Thu, 20 Sep 2018 19:40:40 -0400
From: Bruce Momjian <bruce@momjian.us>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: transction_timestamp() inside of procedures
Message-ID: <20180920234040.GC29981@momjian.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

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 +