Re: [Postgres-xc-bugs] Incorrect Out - run a stored procudure with EXCEPTION statement

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

Hi,

In last mail, I pasted all the statements to the psql, which showed the output in disorder. 
I rerun it one statement by one as below for your easy reading. 

postgres=# set client_min_messages TO NOTICE;
SET
Time: 0.201 ms
postgres=# drop table if exists t1;
DROP TABLE
Time: 105.188 ms
postgres=# create table t1 (c1 int, c2 int) distribute by hash(c1);
CREATE TABLE
Time: 88.919 ms
postgres=# insert into t1 values(1,1);
INSERT 0 1
Time: 14.024 ms
postgres=# create or replace function myfun() returns void
postgres-# language plpgsql
postgres-# as $$
postgres$# declare
postgres$# c2_new integer;
postgres$# begin
postgres$# update t1 set c2=c2+1 where c1=1 returning c2 into c2_new;
postgres$# RAISE NOTICE 'c2_new is %', c2_new;
postgres$# --if delete the expection handling, the error will not be reproduced.
postgres$# EXCEPTION
postgres$# WHEN serialization_failure OR deadlock_detected OR no_data_found
postgres$# THEN ROLLBACK;
postgres$# END; 
postgres$# $$;
CREATE FUNCTION
Time: 98.419 ms
postgres=# select * from t1;
 c1 | c2 
----+----
  1 |  1
(1 row)

Time: 1.665 ms
postgres=# select myfun();
NOTICE:  c2_new is 2
 myfun 
-------
 
(1 row)

Time: 17.215 ms
postgres=# select * from t1;
 c1 | c2 
----+----
  1 |  1
(1 row)

Time: 1.173 ms
postgres=# update t1 set c2=c2+1 where c1=1 returning c2;
 c2 
----
  2
(1 row)

UPDATE 1
Time: 9.966 ms
postgres=# select * from t1;
 c1 | c2 
----+----
  1 |  1
  1 |  2
(2 rows)

Time: 1.134 ms

Thanks
Julian

From: jul...@ou...
To: pos...@li...
Date: Mon, 13 Jan 2014 16:25:36 +0800
Subject: [Postgres-xc-bugs] Incorrect Out - run a stored procudure with	EXCEPTION statement





Your name: Julian ZL ZhangYour email address: jul...@ou...
System Configuration:---------------------  Architecture (example: Intel Pentium) : Intel Pentium  Operating System (example: Linux 2.4.18) : 2.6.32-358.el6.x86_64  Postgres-XC version (example: Postgres-XC 1.1devel): Github master  Compiler used (example: gcc 3.3.5): gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3)

Please enter a FULL description of your problem:------------------------------------------------Run a stored procudure with EXCEPTION handling, which leads to the database (2 coordinators + 2 Datanode) in a unkown status, then run some other statements, it is obvious the result is wrong. 


Please describe a way to repeat the problem. Please try to provide aconcise reproducible example, if at all possible:----------------------------------------------------------------------
The reproducing process is attached as a file for you to run it easily. I pasted my output as below:
postgres=# set client_min_messages TO NOTICE;
SET
Time: 0.878 ms
postgres=# drop table if exists t1;
create table t1 (c1 int, c2 int) distribute by hash(c1);
insert into t1 values(1,1);

create or replace function myfun() returns void
language plpgsql
DROP TABLE
Time: 77.960 ms
postgres=# create table t1 (c1 int, c2 int) distribute by hash(c1);
as $$
declare
        c2_new integer;
begin
CREATE TABLE
Time: 73.468 ms
postgres=# insert into t1 values(1,1);
update t1 set c2=c2+1 where c1=1 returning c2 into c2_new;
INSERT 0 1
Time: 12.540 ms
postgres=# 
postgres=# create or replace function myfun() returns void
postgres-# language plpgsql
postgres-# as $$
postgres$# declare
postgres$# c2_new integer;
postgres$# begin
postgres$# update t1 set c2=c2+1 where c1=1 returning c2 into c2_new;
postgres$# RAISE NOTICE 'c2_new is %', c2_new;
postgres$# --if delete the expection handling, the error will not be reproduced.
postgres$# EXCEPTION
postgres$# WHEN serialization_failure OR deadlock_detected OR no_data_found
postgres$# THEN ROLLBACK;
postgres$# END; 
postgres$# $$;

select * from t1;
select myfun();
select * from t1;
CREATE FUNCTION
Time: 67.343 ms
postgres=# 
postgres=# select * from t1;
 c1 | c2 
----+----
  1 |  1
(1 row)

Time: 1.306 ms
postgres=# select myfun();
NOTICE:  c2_new is 2
 myfun 
-------
 
(1 row)

Time: 10.896 ms
postgres=# select * from t1;
 c1 | c2 
----+----
  1 |  1
(1 row)

Time: 0.992 ms
postgres=# update t1 set c2=c2+1 where c1=1 returning c2;
 c2 
----
  2
(1 row)

UPDATE 1
Time: 10.853 ms
postgres=# select * from t1;
 c1 | c2 
----+----
  1 |  1
  1 |  2
(2 rows)

Time: 1.228 ms

If you know how this problem might be fixed, list the solution below:---------------------------------------------------------------------No fix. 


 		 	   		  

------------------------------------------------------------------------------
CenturyLink Cloud: The Leader in Enterprise Cloud Services.
Learn Why More Businesses Are Choosing CenturyLink Cloud For
Critical Workloads, Development Environments & Everything In Between.
Get a Quote or Start a Free Trial Today. 
http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk
_______________________________________________
Postgres-xc-bugs mailing list
Pos...@li...
https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs