[Postgres-xc-bugs] A bug about sub-transaction in data node.

[ZhangJulian <jul...@ou...>]

Hi All,

The reproced steps:
===================================
postgres=# drop table if exists t1;
DROP TABLE
postgres=# create table t1 (c1 int, c2 int) distribute by hash(c1);
CREATE TABLE
postgres=# create or replace function myfun() returns void
language plpgsql
as $$
begin
insert into t1 values(1,1); 
EXCEPTION                   
WHEN serialization_failure OR deadlock_detected OR no_data_found
THEN ROLLBACK;                                                  
END;                                                   
$$;                                                  
CREATE FUNCTION
postgres=# select myfun();
 myfun 
-------
 
(1 row)

postgres=# select * from t1;
 c1 | c2 
----+----
(0 rows)

postgres=# \q
[pgxc@node1 ~]$ pgxc_ctl Psql - coord1 postgres
....
postgres=# select * from t1;
 c1 | c2 
----+----
  1 |  1
(1 row)
============================
You can see in the first session, SELECT return 0 rows, but if you quit the session and open a new session, SELECT will return 1 rows.

I had debugged it for several days, the reason is the "EXCEPTION" block in myfun() will start a new sub-transaction. But when "select myfun();" finished,  in the datanode, the sub-transaction is committed, but its parent transaction is still keeping alive, which cause the next SELECT think the row has not been commited. 
If you close the session, the parent session will be committed, then the SELECT will see the insertted rows. 

I think I had found the reason, but I don't know what is the correct behavior when parent/sub transaction meets the coordinator/datanode.  
1. In the coordinator, the sub-transacton should be committed once the parent transaction is committed? Or they should committed separately?  
2. How datanode know two transactions is parent-child relation?
3. Coordinator should tell datanode to commit child transaction explicitly? 

Thanks!
Julian