SQL Server and Oracle – Setting Up Distributed Transaction Coordinator

You can get the correlation you're looking for in the sys.dm_tran_active_transactions DMV. The column is transaction_uow.

http://msdn.microsoft.com/en-us/library/ms174302.aspx

Wrapping the 3 temp table inserts in one transaction with ISOLATION LEVEL READ UNCOMMITTED seems to have stopped promotion to a distributed transaction and solved my intermittent problem.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRANSACTION;

    DECLARE @TSQL varchar(max)
    SET  @TSQL = 'select c1, c2 from OpenQuery([POSTGRESQL],''
            select c1, c2 from table1 t where 
            t.date>=''''' +CONVERT(VARCHAR(15),@DateFrom,102)+ '''''' + 't.date <=''''' +CONVERT(VARCHAR(15),@DateTo,102) + '''''' +'order by c1'')'

    insert into #temp_table1
    EXEC (@TSQL)

.....etc

COMMIT TRANSACTION;

This was a duplicate question and ultimately gave me the solution. Perhaps, PostgreSQL ODBC does not support distributed transactions - and disabling them on Linked Server Properties is not enough when attempting to use temp tables or attempting to connect to a remote database 3 times -- due to ACID functionality..