postgres-xc-bugs — Bug report

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

[鈴木 幸市 <ko...@in...>]

Year, please let me try.   At present, XC puts a message when SAVEPOINT is invoked.   It is internal code.

Thanks.
---
Koichi Suzuki

2014/06/17 21:57、ZhangJulian <jul...@ou...<mailto:jul...@ou...>> のメール：

oh, I see,thanks!

How about adding some error thrown once it goes to the sub-transaction code?

________________________________
From: ko...@in...<mailto:ko...@in...>
To: jul...@ou...<mailto:jul...@ou...>
CC: pos...@li...<mailto:pos...@li...>
Subject: Re: [Postgres-xc-bugs] A bug about sub-transaction in data node.
Date: Tue, 17 Jun 2014 11:03:56 +0000

Unfortunately, sub transaction is not supported by XC so far,   As you may know, this is the main reason why XC has several restriction in functions, which is originated by subtransaction nature in SPI module.

Regards;
---
Koichi Suzuki

2014/06/17 19:19、ZhangJulian <jul...@ou...<mailto: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
------------------------------------------------------------------------------
HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
Find What Matters Most in Your Big Data with HPCC Systems
Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
Leverages Graph Analysis for Fast Processing & Easy Data Exploration
http://p.sf.net/sfu/hpccsystems_______________________________________________
Postgres-xc-bugs mailing list
Pos...@li...<mailto:Pos...@li...>
https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs

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

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

oh, I see,thanks!

How about adding some error thrown once it goes to the sub-transaction code?

From: ko...@in...
To: jul...@ou...
CC: pos...@li...
Subject: Re: [Postgres-xc-bugs] A bug about sub-transaction in data node.
Date: Tue, 17 Jun 2014 11:03:56 +0000






Unfortunately, sub transaction is not supported by XC so far,   As you may know, this is the main reason why XC has several restriction in functions, which is originated by subtransaction nature in SPI module.



Regards;

---
Koichi Suzuki




2014/06/17 19:19、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



------------------------------------------------------------------------------

HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions

Find What Matters Most in Your Big Data with HPCC Systems

Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.

Leverages Graph Analysis for Fast Processing & Easy Data Exploration

http://p.sf.net/sfu/hpccsystems_______________________________________________

Postgres-xc-bugs mailing list

Pos...@li...

https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs








 		 	   		  

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

[鈴木 幸市 <ko...@in...>]

Unfortunately, sub transaction is not supported by XC so far,   As you may know, this is the main reason why XC has several restriction in functions, which is originated by subtransaction nature in SPI module.

Regards;
---
Koichi Suzuki

2014/06/17 19:19、ZhangJulian <jul...@ou...<mailto: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
------------------------------------------------------------------------------
HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions
Find What Matters Most in Your Big Data with HPCC Systems
Open Source. Fast. Scalable. Simple. Ideal for Dirty Data.
Leverages Graph Analysis for Fast Processing & Easy Data Exploration
http://p.sf.net/sfu/hpccsystems_______________________________________________
Postgres-xc-bugs mailing list
Pos...@li...
https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs

[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
 		 	   		  

Re: [Postgres-xc-bugs] parallel make problem: ar: pqexpbuffer.o: No such file or directory

[Koichi S. <koi...@gm...>]

After you get an error, did you try serial make?   Depending on the
timing and duration of compilation, it does fail sometimes.   You
should run usual make to fix the issue and them make with -j option.
-j30 is huge.    How many cores do you have in your environment?
I'm using -j8 with five core VM environment.

Regards;
---
Koichi Suzuki


2014-05-13 3:15 GMT+09:00 Jacek Wielemborek <d3...@gm...>:
> Hi guys,
>
> In the attachment there's the log for my failed attempt to run make -j30.
>
> Please remember to CC me in the reply as I might not be on the list anymore.
>
> Yours,
> Jacek Wielemborek
> ------------------------------------------------------------------------------
> "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
> Instantly run your Selenium tests across 300+ browser/OS combos.
> Get unparalleled scalability from the best Selenium testing platform available
> Simple to use. Nothing to install. Get started now for free."
> http://p.sf.net/sfu/SauceLabs
> _______________________________________________
> Postgres-xc-bugs mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>

[Postgres-xc-bugs] parallel make problem: ar: pqexpbuffer.o: No such file or directory

[Jacek W. <d3...@gm...>]

Hi guys,

In the attachment there's the log for my failed attempt to run make -j30.

Please remember to CC me in the reply as I might not be on the list anymore.

Yours,
Jacek Wielemborek

Re: [Postgres-xc-bugs] Trigger (before or after) delete problem - ERROR: type "a" does not exist

[Koichi S. <koi...@gm...>]

Sorry that I did not reply long.   Maybe this is a bug.   What version
did you use?    If you used 1.2, there could be some remaining
conflict with native PG code in schema qualification.

Simpler reproduction will be helpful.

Best;
---
Koichi Suzuki


2014-03-18 4:22 GMT+09:00 André Toshio Nogueira Nishitani <ato...@gm...>:
> Hi,
>
> I don't know if this is a bug, I just messed up something or I forgot about
> some PGXC restriction, but I can't create a trigger (before or after) delete
> in a schema other then the public schema.
> The hosts configuration is the following:
>
> HOST 1:
> - GTM
> - COORDINATOR
> - DATANODE 1
>
> HOST 2:
> - GTM PROXY
> - DATANODE 2
>
> I believe the error can be replicated using the refint.example and setting a
> schema different from public.
> I tried functions other then check_foreign_key() and check_primary_key(),
> but the result was the same.
> These are the refint.example I used to test and the result.
>
> refint.example:
> ----------------------------------------------------------------------------
> --Column ID of table A is primary key:
>
> CREATE TABLE test.A (
>     ID    int4 not null
> );
> CREATE UNIQUE INDEX AI ON test.A (ID);
>
> --Columns REFB of table B and REFC of C are foreign keys referenting ID of
> A:
>
> CREATE TABLE test.B (
>     REFB    int4
> );
> CREATE INDEX BI ON test.B (REFB);
>
> CREATE TABLE test.C (
>     REFC    int4
> );
> CREATE INDEX CI ON test.C (REFC);
>
> --Trigger for table A:
>
> CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON test.A FOR EACH ROW
> EXECUTE PROCEDURE
> check_foreign_key (2, 'cascade', 'id', 'test.b', 'refb', 'test.c', 'refc');
> /*
> 2    - means that check must be performed for foreign keys of 2 tables.
> cascade    - defines that corresponding keys must be deleted.
> ID    - name of primary key column in triggered table (A). You may
>       use as many columns as you need.
> B    - name of (first) table with foreign keys.
> REFB    - name of foreign key column in this table. You may use as many
>       columns as you need, but number of key columns in referenced
>       table (A) must be the same.
> C    - name of second table with foreign keys.
> REFC    - name of foreign key column in this table.
> */
>
> --Trigger for table B:
>
> CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON test.B FOR EACH ROW
> EXECUTE PROCEDURE
> check_primary_key ('refb', 'test.a', 'id');
>
> /*
> REFB    - name of foreign key column in triggered (B) table. You may use as
>       many columns as you need, but number of key columns in referenced
>       table must be the same.
> A    - referenced table name.
> ID    - name of primary key column in referenced table.
> */
>
> --Trigger for table C:
>
> CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON test.C FOR EACH ROW
> EXECUTE PROCEDURE
> check_primary_key ('refc', 'test.a', 'id');
>
> -- Now try
>
> INSERT INTO test.A VALUES (10);
> INSERT INTO test.A VALUES (20);
> INSERT INTO test.A VALUES (30);
> INSERT INTO test.A VALUES (40);
> INSERT INTO test.A VALUES (50);
>
> INSERT INTO test.B VALUES (1);    -- invalid reference
> INSERT INTO test.B VALUES (10);
> INSERT INTO test.B VALUES (30);
> INSERT INTO test.B VALUES (30);
>
> INSERT INTO test.C VALUES (11);    -- invalid reference
> INSERT INTO test.C VALUES (20);
> INSERT INTO test.C VALUES (20);
> INSERT INTO test.C VALUES (30);
>
> DELETE FROM test.A WHERE ID = 10;
> DELETE FROM test.A WHERE ID = 20;
> DELETE FROM test.A WHERE ID = 30;
>
> SELECT * FROM test.A;
> SELECT * FROM test.B;
> SELECT * FROM test.C;
> ----------------------------------------------------------------------------
>
>
> and the output was:
> ----------------------------------------------------------------------------
> CREATE TABLE
> CREATE INDEX
> CREATE TABLE
> CREATE INDEX
> CREATE TABLE
> CREATE INDEX
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> psql:refint.example:66: ERROR:  tuple references non-existent key
> DETAIL:  Trigger "bt" found tuple referencing non-existent key in "test.a".
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> psql:refint.example:71: ERROR:  tuple references non-existent key
> DETAIL:  Trigger "ct" found tuple referencing non-existent key in "test.a".
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> psql:refint.example:76: ERROR:  type "a" does not exist
> psql:refint.example:77: ERROR:  type "a" does not exist
> psql:refint.example:78: ERROR:  type "a" does not exist
>  id
> ----
>  40
>  50
>  10
>  20
>  30
> (5 rows)
>
>  refb
> ------
>    10
>    30
>    30
> (3 rows)
>
>  refc
> ------
>    20
>    20
>    30
> (3 rows)
> ----------------------------------------------------------------------------
>
> Regards,
> --
> André Toshio Nogueira Nishitani.
>
> Mestrando em Ciências da Computação
> ICMC - USP - São Carlos
>
> Sent from my IQTELL Virtual Workspace
>
> ------------------------------------------------------------------------------
> Learn Graph Databases - Download FREE O'Reilly Book
> "Graph Databases" is the definitive new guide to graph databases and their
> applications. Written by three acclaimed leaders in the field,
> this first edition is now available. Download your free book today!
> http://p.sf.net/sfu/13534_NeoTech
> _______________________________________________
> Postgres-xc-bugs mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>

Re: [Postgres-xc-bugs] Trigger (before or after) delete problem - ERROR: type "table_name" does not exist

[Koichi S. <koi...@gm...>]

I think all your e-mail post as delivered.   Sorry taking long to respond.

You sent three e-mail right?

Regards;
---
Koichi Suzuki


2014-03-18 21:11 GMT+09:00 André Toshio Nogueira Nishitani
<ato...@gm...>:
> Hi,
>
> I tried to send an e-mail before, but it seems there waas a problem with the
> body format so I'm resending it.
> I don't know if this is a bug, I just messed up something or I forgot about
> some PGXC restriction, but I can't create a trigger (before or after) delete
> in a schema other then the public schema.
> I created a TRIGGER on a DELETE event, but every time it should be called
> there was an error saying: ERROR:  type "table_name" does not exist.
> I believe the error can be replicated using the refint.example and setting a
> schema different from public.
> I tried functions other then check_foreign_key() and check_primary_key(),
> thinking that maybe they were causing it, but the result was the same.
> The refint.example I used to test and the output are attached.
> The hosts configuration is the following:
>
> HOST 1:
> - GTM
> - COORDINATOR
> - DATANODE 1
>
> HOST 2:
> - GTM PROXY
> - DATANODE 2
>
> --
> André Toshio Nogueira Nishitani.
>
> Mestrando em Ciências da Computação
> ICMC - USP - São Carlos
>
> Sent from my IQTELL Virtual Workspace
>
>
> ________________________________
> From: André Toshio Nogueira Nishitani (ato...@gm...)
> Sent: Monday, March 17, 2014 04:22 PM
> To: pos...@li...
> Subject: Trigger (before or after) delete problem - ERROR: type "a" does not
> exist
>
> Hi,
>
> I don't know if this is a bug, I just messed up something or I forgot about
> some PGXC restriction, but I can't create a trigger (before or after) delete
> in a schema other then the public schema.
> The hosts configuration is the following:
>
> HOST 1:
> - GTM
> - COORDINATOR
> - DATANODE 1
>
> HOST 2:
> - GTM PROXY
> - DATANODE 2
>
> I believe the error can be replicated using the refint.example and setting a
> schema different from public.
> I tried functions other then check_foreign_key() and check_primary_key(),
> but the result was the same.
> These are the refint.example I used to test and the result.
>
> refint.example:
> ----------------------------------------------------------------------------
> --Column ID of table A is primary key:
>
> CREATE TABLE test.A (
>     ID    int4 not null
> );
> CREATE UNIQUE INDEX AI ON test.A (ID);
>
> --Columns REFB of table B and REFC of C are foreign keys referenting ID of
> A:
>
> CREATE TABLE test.B (
>     REFB    int4
> );
> CREATE INDEX BI ON test.B (REFB);
>
> CREATE TABLE test.C (
>     REFC    int4
> );
> CREATE INDEX CI ON test.C (REFC);
>
> --Trigger for table A:
>
> CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON test.A FOR EACH ROW
> EXECUTE PROCEDURE
> check_foreign_key (2, 'cascade', 'id', 'test.b', 'refb', 'test.c', 'refc');
> /*
> 2    - means that check must be performed for foreign keys of 2 tables.
> cascade    - defines that corresponding keys must be deleted.
> ID    - name of primary key column in triggered table (A). You may
>       use as many columns as you need.
> B    - name of (first) table with foreign keys.
> REFB    - name of foreign key column in this table. You may use as many
>       columns as you need, but number of key columns in referenced
>       table (A) must be the same.
> C    - name of second table with foreign keys.
> REFC    - name of foreign key column in this table.
> */
>
> --Trigger for table B:
>
> CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON test.B FOR EACH ROW
> EXECUTE PROCEDURE
> check_primary_key ('refb', 'test.a', 'id');
>
> /*
> REFB    - name of foreign key column in triggered (B) table. You may use as
>       many columns as you need, but number of key columns in referenced
>       table must be the same.
> A    - referenced table name.
> ID    - name of primary key column in referenced table.
> */
>
> --Trigger for table C:
>
> CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON test.C FOR EACH ROW
> EXECUTE PROCEDURE
> check_primary_key ('refc', 'test.a', 'id');
>
> -- Now try
>
> INSERT INTO test.A VALUES (10);
> INSERT INTO test.A VALUES (20);
> INSERT INTO test.A VALUES (30);
> INSERT INTO test.A VALUES (40);
> INSERT INTO test.A VALUES (50);
>
> INSERT INTO test.B VALUES (1);    -- invalid reference
> INSERT INTO test.B VALUES (10);
> INSERT INTO test.B VALUES (30);
> INSERT INTO test.B VALUES (30);
>
> INSERT INTO test.C VALUES (11);    -- invalid reference
> INSERT INTO test.C VALUES (20);
> INSERT INTO test.C VALUES (20);
> INSERT INTO test.C VALUES (30);
>
> DELETE FROM test.A WHERE ID = 10;
> DELETE FROM test.A WHERE ID = 20;
> DELETE FROM test.A WHERE ID = 30;
>
> SELECT * FROM test.A;
> SELECT * FROM test.B;
> SELECT * FROM test.C;
> ----------------------------------------------------------------------------
>
>
> and the output was:
> ----------------------------------------------------------------------------
> CREATE TABLE
> CREATE INDEX
> CREATE TABLE
> CREATE INDEX
> CREATE TABLE
> CREATE INDEX
> CREATE TRIGGER
> CREATE TRIGGER
> CREATE TRIGGER
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> psql:refint.example:66: ERROR:  tuple references non-existent key
> DETAIL:  Trigger "bt" found tuple referencing non-existent key in "test.a".
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> psql:refint.example:71: ERROR:  tuple references non-existent key
> DETAIL:  Trigger "ct" found tuple referencing non-existent key in "test.a".
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> psql:refint.example:76: ERROR:  type "a" does not exist
> psql:refint.example:77: ERROR:  type "a" does not exist
> psql:refint.example:78: ERROR:  type "a" does not exist
>  id
> ----
>  40
>  50
>  10
>  20
>  30
> (5 rows)
>
>  refb
> ------
>    10
>    30
>    30
> (3 rows)
>
>  refc
> ------
>    20
>    20
>    30
> (3 rows)
> ----------------------------------------------------------------------------
>
> Regards,
> --
> André Toshio Nogueira Nishitani.
>
> Mestrando em Ciências da Computação
> ICMC - USP - São Carlos
>
> Sent from my IQTELL Virtual Workspace
>
> ------------------------------------------------------------------------------
> Learn Graph Databases - Download FREE O'Reilly Book
> "Graph Databases" is the definitive new guide to graph databases and their
> applications. Written by three acclaimed leaders in the field,
> this first edition is now available. Download your free book today!
> http://p.sf.net/sfu/13534_NeoTech
> _______________________________________________
> Postgres-xc-bugs mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>

Re: [Postgres-xc-bugs] Trigger (before or after) delete problem - ERROR: type "table_name" does not exist

[André T. N. N. <ato...@gm...>]

CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TABLE
CREATE INDEX
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
psql:refint.example:66: ERROR:  tuple references non-existent key
DETAIL:  Trigger "bt" found tuple referencing non-existent key in "test.a".
INSERT 0 1
INSERT 0 1
INSERT 0 1
psql:refint.example:71: ERROR:  tuple references non-existent key
DETAIL:  Trigger "ct" found tuple referencing non-existent key in "test.a".
INSERT 0 1
INSERT 0 1
INSERT 0 1
psql:refint.example:76: ERROR:  type "a" does not exist
psql:refint.example:77: ERROR:  type "a" does not exist
psql:refint.example:78: ERROR:  type "a" does not exist
 id
----
 40
 50
 10
 20
 30
(5 rows)

 refb
------
   10
   30
   30
(3 rows)

 refc
------
   20
   20
   30
(3 rows) 

Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.

[Koichi S. <koi...@gm...>]

Hi Ashutosh;

I'm afraid this patch is still pending for three weeks.   Could you
respond if this looks nice and good to go?

Regards;
---
Koichi Suzuki


2014-01-16 17:00 GMT+09:00 ZhangJulian <jul...@ou...>:
> Hi Ashutosh,
>
> How about fixing it as the attached patch?
>
> Thanks
> Julian
> ________________________________
> Date: Wed, 15 Jan 2014 08:49:33 +0530
>
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
> From: ash...@en...
> To: jul...@ou...
> CC: pos...@li...
>
> As you have spotted already changing make_ands_implicit() is not desirable,
> since it has not got modified in many revisions of PostgreSQL and has many
> callers which comply with the current behaviour.
>
> The queries you mentioned are correctly inferring the datanode where to ship
> the query. I think you will have to worry about queries like SELECT c1, c2,
> c3, c4 FROM public.t1 WHERE ((((c1 = 1) AND (c2 = 2)) AND (c3 = 3)) AND (c4
> = 4)), which might not be shipped to only required datanodes.
>
> I do not have the right solutions at hand, but the existing ones are not
> enough. You might want to continue experimenting more.
>
>
> On Tue, Jan 14, 2014 at 12:31 PM, ZhangJulian <jul...@ou...>
> wrote:
>
> Hi Ashutosh,
>
> I tested two more scenerios with the fix:
> postgres=# create table t1 (c1 int, c2 int, c3 int, c4 int);
> CREATE TABLE
> postgres=# explain verbose select * from t1 where c1=1 and c2=2 and c3=3 and
> c4=4;
>                                                     QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------
>  Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
>    Output: t1.c1, t1.c2, t1.c3, t1.c4
>    Node/s: datanode1
>    Remote query: SELECT c1, c2, c3, c4 FROM public.t1 WHERE ((((c1 = 1) AND
> (c2 = 2)) AND (c3 = 3)) AND (c4 = 4))
> (4 rows)
> postgres=# explain verbose select * from t1 where c1=1 and c2=2 and (c3=3 or
> c4=4);
>                                                    QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------
>  Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
>    Output: t1.c1, t1.c2, t1.c3, t1.c4
>    Node/s: datanode1
>    Remote query: SELECT c1, c2, c3, c4 FROM public.t1 WHERE (((c1 = 1) AND
> (c2 = 2)) AND ((c3 = 3) OR (c4 = 4)))
> (4 rows)
>
> For a complex predicate as "where c1=1 and c2=2 and c3=3 and c4=4", it will
> be parsed to "WHERE ((((c1 = 1) AND (c2 = 2)) AND (c3 = 3)) AND (c4 = 4))",
> that is:
>  BoolExpr
>         BoolExpr
>                OpExpr (c1=1)
>                OpExpr (c2=2)
>         OpExpr (c3=3)
>  OpExpr (c4=4)
>
> Do you mean I should fix it in another way by updating make_ands_implicit()
> to return the list with all AND legs? It should be a better way, but there
> are several invocations to make_ands_implicit(), I can not understand all of
> the callers and have the confidence to fix it as it. :)
>
> The callers to make_ands_implicit():
> make_ands_implicit(Expr *) : List *
>     ATAddCheckConstraint(List * *, AlteredTableInfo *, Relation, Constraint
> *, bool, bool, LOCKMODE) : void
>     convert_EXISTS_to_ANY(PlannerInfo *, Query *, Node * *, List * *) :
> Query *
>     cost_subplan(PlannerInfo *, SubPlan *, Plan *) : void
>     DefineIndex(RangeVar *, char *, Oid, Oid, char *, char *, List *, Expr
> *, List *, List *, bool, bool, bool, bool, bool, bool, bool, bool, bool,
> bool) : Oid
>     ExecRelCheck(ResultRelInfo *, TupleTableSlot *, EState *) : const char *
>     get_relation_constraints(PlannerInfo *, Oid, RelOptInfo *, bool) : List
> *
>     pgxc_find_dist_equijoin_qual(List *, List *, Node *) : Expr *
>     pgxc_find_distcol_expr(Index, AttrNumber, Node *) : Expr *
>     preprocess_expression(PlannerInfo *, Node *, int) : Node *
>     RelationGetIndexPredicate(Relation) : List *
>     set_append_rel_size(PlannerInfo *, RelOptInfo *, Index, RangeTblEntry *)
> : void
>     TriggerEnabled(EState *, ResultRelInfo *, Trigger *, TriggerEvent,
> Bitmapset *, HeapTuple, HeapTuple) : bool
>     validateCheckConstraint(Relation, HeapTuple) : void
>
>
> Thanks
> Julian
>
> ________________________________
> Date: Mon, 13 Jan 2014 15:11:00 +0530
>
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
> From: ash...@en...
> To: jul...@ou...
> CC: pos...@li...
>
> Ok, good observation.
>
> BTW, with your fix, we are still in problem if there are four conditions
> ANDED e.g. A and B and C and D.
>
> What should happen, and which actually happens before starting the planning
> phase, is all the ANDs are flattened into a list so that the above tree
> looks like
> AND
>  | -> args A, B, C, D
>
> So, a better solution seems to be that we should pass the quals to
> pgxc_find_dist_qual(), by converting them into and ANDed list of args.
>
>
> On Mon, Jan 13, 2014 at 2:43 PM, ZhangJulian <jul...@ou...>
> wrote:
>
> Hi Ashutosh，
>
> The parser will parse "where c1=1 and c2=1 and c3=1" to:
> BoolExpr
>     |--BoolExpr
>     |        |-OpExpr (c1=1)
>     |        |-OpExpr (c2=1)
>     |
>     |--OpExpr (c3=1)
>
> make_ands_implicit() will translate it to a list containing two elements:
> 1. BoolExpr
>           |-OpExpr (c1=1)
>           |-OpExpr (c2=1)
>
>  2. OpExpr (c3=1)
> But it will not further split the first element to two OpExpr and add them
> to the parent list.
>
> Thanks
> Julian
>
> ________________________________
> Date: Mon, 13 Jan 2014 14:19:48 +0530
>
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
> From: ash...@en...
> To: jul...@ou...
> CC: pos...@li...
>
> Hi Julian,
> Can you please send a patch after taking care of my comments in my previous
> mail. I am pasting them here for your quick reference,
> --
> In function pgxc_find_distcol_expr() there is code
>
> 828     /* Convert the qualification into List if it's not already so */
> 829     if (!IsA(quals, List))
> 830         lquals = make_ands_implicit((Expr *)quals);
> 831     else
> 832         lquals = (List *)quals;
>
> which takes care of the quals which are not in the List form. This part of
> the code should have taken care of the case you mentioned. It seems that for
> some reason, this part is not converting the ANDed quals into List. Can you
> please check why? May be you want to step through make_ands_implicit()
> (using gdb or some debugger) to see what's happening there.
>
>
>
>
> On Mon, Jan 13, 2014 at 2:16 PM, ZhangJulian <jul...@ou...>
> wrote:
>
> Hi Ashutosh,
>
> I am so sorry that I had sent a patch with wrong format, it is because I
> edited it after Git Diff.
> I regenerated a patch as the attached file and verified that it can be
> applied.
>
> Thanks
> Julian
>
> ________________________________
> From: jul...@ou...
> To: ash...@en...
> Date: Sat, 11 Jan 2014 07:52:34 +0800
> CC: pos...@li...
>
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
>
> Hi Ashutosh,
>
> I am at home and can not connect to the office to debug it today. I will
> double check the problem on Monday.
>
> I remember when I debugged it, the proplem is,
> Parser will parse "where c1=1 and c2=1 and c3=1" to  "WHERE (((c1 = 1) AND
> (c2 = 1)) AND (c3 = 1))", which is a list containing two elements:
> element 1:  ((c1 = 1) AND (c2 = 1)) which is a BoolExpr;
> element 2:  (c3 = 1)
> For the element 1, it would not split it into two simple predidates, so the
> c1 = 1 is missed.
>
> If I just move the c1 = 1 to the last position, the query can generate the
> correct plan.
>
> Thanks
> Julian
>
> ________________________________
> Date: Fri, 10 Jan 2014 16:48:47 +0530
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
> From: ash...@en...
> To: jul...@ou...
> CC: pos...@li...
>
> Hi Julian,
> I looked at the patch and tried to apply the patch, but there is an error
> [ashutosh@ubuntu coderoot]git apply
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:10: trailing whitespace.
>         if (and_clause((Node *) qual_expr))
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:11: trailing whitespace.
>         {
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:12: trailing whitespace.
>             List *sub_lquals = ((BoolExpr *) qual_expr)->args;
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:13: trailing whitespace.
>             distcol_expr = pgxc_find_distcol_expr(varno, attrNum,
> sub_lquals);
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:14: trailing whitespace.
>             if (distcol_expr != NULL)
> fatal: corrupt patch at line 20
>
> Did you use git diff for creating the patch?
>
> BTW, I looked at the patch. The patch seems to be applied to function
> pgxc_find_distcol_expr() (functions name usually appears along with the
> diff, if you are using git diff).
>
> In this function there is code
>
> 828     /* Convert the qualification into List if it's not already so */
> 829     if (!IsA(quals, List))
> 830         lquals = make_ands_implicit((Expr *)quals);
> 831     else
> 832         lquals = (List *)quals;
>
> which takes care of the quals which are not in the List form. This part of
> the code should have taken care of the case you mentioned. It seems that for
> some reason, this part is not converting the ANDed quals into List. Can you
> please check why? May be you want to step through make_ands_implicit()
> (using gdb or some debugger) to see what's happening there.
>
>
>
>
> On Fri, Jan 10, 2014 at 3:38 PM, ZhangJulian <jul...@ou...>
> wrote:
>
>
> Your name                  : Julian ZL Zhang
> Your 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:
> ------------------------------------------------
> I had opened the bug at http://sourceforge.net/p/postgres-xc/bugs/467/ just
> descripte it more detailedly here.
> Different predicate order results in different plan, the query should be
> shipped only to one datanode, but in the first example, two data nodes are
> involved.
>
> Please describe a way to repeat the problem. Please try to provide a
> concise reproducible example, if at all possible:
> ----------------------------------------------------------------------
> postgres=# create table t1 (c1 int, c2 int, c3 int) distribute by hash(c1);
> CREATE TABLE
> postgres=# explain verbose select * from t1 where c1=1 and c2=1 and c3=1;
> QUERY PLAN
> --------------------------------------------------------------------------------
> Data Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
> Output: t1.c1, t1.c2, t1.c3
> Node/s: datanode1, datanode2
> Remote query: SELECT c1, c2, c3 FROM benchmarksql.t1 WHERE (((c1 = 1) AND
> (c2 = 1)) AND (c3 = 1))
> (4 rows)
> postgres=# explain verbose select * from t1 where c2=1 and c3=1 and c1=1;
> QUERY PLAN
> --------------------------------------------------------------------------------
> Data Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
> Output: t1.c1, t1.c2, t1.c3
> Node/s: datanode1
> Remote query: SELECT c1, c2, c3 FROM benchmarksql.t1 WHERE (((c2 = 1) AND
> (c3 = 1)) AND (c1 = 1))
> (4 rows)
>
>
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
> I made a fix as the append file, which can resolve this issue, but I am not
> sure if it is correct.
> When I run MAKE CHECK, 3 test cases failed. The results are appended too.
> But when I run MAKE CHECK without the fix, there are also some test cases
> failed.
> I do not know if the failed test cases are related to the fix.
>
> Thanks
> Julian
>
> ------------------------------------------------------------------------------
> 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
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
> ------------------------------------------------------------------------------
> 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
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
> ------------------------------------------------------------------------------
> 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
>

[Postgres-xc-bugs] datanode doesn't comes up due to recovery issue

[Anand S. <ana...@gm...>]

Hi ,

Below is the setup I have on same host, with latest postgres-xc version 1.1

1 gtm
3 coordinator node
3 data node

Created table on datanode3 using "to node (datanode3)".  Performed
select,insert and killed datanode3 process using kill -9. I was able to
perform select, insert on same table when datanode3 was down. Brought
datanode3 up and everything cleared. While performing same step again but
this time rather than bringing up datanode3 I brought down full pg-xc
(gtm,datanode,cordinatornode) and tried starting pg-xc, Eveything came up
except datanode3 which was complaining about two phase commit (primary,
secondary log are not in sync).



-- 
Regards,
Anand Sharma

Re: [Postgres-xc-bugs] ERROR: Cannot create index whose evaluation cannot be enforced to remote nodes

[Ying He <yin...@ya...>]

Thanks for the help.





On Friday, January 17, 2014 1:22 AM, Koichi Suzuki <koi...@gm...> wrote:
 
Inherits option from t2 to t3 enforces the column a as the
distribution column.   Current XC does not allow primary key (or
unique constraint) to a column other than the distribution key.

Regards;
---
Koichi Suzuki



2014/1/17 Ying He <yin...@ya...>:
> hi, All,
>
> I setup the following for testing:
>
> host1 has gtm master
> host2 has gtm slave
> host3 has gtm_proxy1, coordinator1, datanode1
> host4 has gtm_proxy2, coordinator2, datanode2
>
> once it is up and running, i connect to coordinator1 to do the following:
>
> create table t2
> (a int) DISTRIBUTE BY REPLICATION ;
>
> CREATE TABLE t3
> ( b int not null CONSTRAINT PK_t3 primary key,
> c int)
> INHERITS(t2);
>
> will produce
> ERROR:  Cannot create index whose evaluation cannot be enforced to remote
> nodes
>
> looks like the INHERITS is not supported.
>
> Please help if you see anything wrong. Thank you.
>
> best,
> Ying
>
>
> ------------------------------------------------------------------------------
> 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

>

Re: [Postgres-xc-bugs] ERROR: Cannot create index whose evaluation cannot be enforced to remote nodes

[Koichi S. <koi...@gm...>]

Inherits option from t2 to t3 enforces the column a as the
distribution column.   Current XC does not allow primary key (or
unique constraint) to a column other than the distribution key.

Regards;
---
Koichi Suzuki


2014/1/17 Ying He <yin...@ya...>:
> hi, All,
>
> I setup the following for testing:
>
> host1 has gtm master
> host2 has gtm slave
> host3 has gtm_proxy1, coordinator1, datanode1
> host4 has gtm_proxy2, coordinator2, datanode2
>
> once it is up and running, i connect to coordinator1 to do the following:
>
> create table t2
> (a int) DISTRIBUTE BY REPLICATION ;
>
> CREATE TABLE t3
> ( b int not null CONSTRAINT PK_t3 primary key,
> c int)
> INHERITS(t2);
>
> will produce
> ERROR:  Cannot create index whose evaluation cannot be enforced to remote
> nodes
>
> looks like the INHERITS is not supported.
>
> Please help if you see anything wrong. Thank you.
>
> best,
> Ying
>
>
> ------------------------------------------------------------------------------
> 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
>

Re: [Postgres-xc-bugs] ERROR: Cannot create index whose evaluation cannot be enforced to remote nodes

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

Hi,

You should create t3 ... distribute by replication or distribute by hash(b), if you want define a primary key on column b.

postgres=# create table t2
(a int) DISTRIBUTE BY REPLICATION ;
CREATE TABLE
Time: 86.475 ms
postgres=# CREATE TABLE t3
( b int not null CONSTRAINT PK_t3 primary key,
c int)
INHERITS(t2) distribute by replication;
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pk_t3" for table "t3"
CREATE TABLE
Time: 174.161 ms
postgres=# CREATE TABLE t4
( b int not null CONSTRAINT PK_t4 primary key,
c int)
INHERITS(t2) distribute by hash(b);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pk_t4" for table "t4"
CREATE TABLE
Time: 177.739 ms


Thanks
Julian

Date: Thu, 16 Jan 2014 12:54:06 -0800
From: yin...@ya...
To: pos...@li...
Subject: [Postgres-xc-bugs] ERROR: Cannot create index whose evaluation	cannot be enforced to remote nodes

hi, All,
I setup the following for testing:host1 has gtm master 
host2 has gtm slave 
host3 has gtm_proxy1, coordinator1, datanode1
host4 has gtm_proxy2, coordinator2, datanode2

once it is up and running, i connect to coordinator1 to do the following:

create table t2
(a int) DISTRIBUTE BY REPLICATION ;

CREATE TABLE t3
( b int not null CONSTRAINT PK_t3 primary key,
c int)
INHERITS(t2);

will produce
ERROR:  Cannot create index whose evaluation cannot be enforced to remote nodes

looks like the INHERITS is not supported.

Please help if you see anything wrong. Thank you.

best,
Ying

------------------------------------------------------------------------------
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 		 	   		  

[Postgres-xc-bugs] ERROR: Cannot create index whose evaluation cannot be enforced to remote nodes

[Ying He <yin...@ya...>]

hi, All,

I setup the following for testing:
host1 has gtm master 
host2 has gtm slave 
host3 has gtm_proxy1, coordinator1, datanode1
host4 has gtm_proxy2, coordinator2, datanode2

once it is up and running, i connect to coordinator1 to do the following:

create table t2
(a int) DISTRIBUTE BY REPLICATION ;

CREATE TABLE t3
( b int not null CONSTRAINT PK_t3 primary key,
c int)
INHERITS(t2);

will produce
ERROR:  Cannot create index whose evaluation cannot be enforced to remote nodes

looks like the INHERITS is not supported.

Please help if you see anything wrong. Thank you.

best,
Ying

[Postgres-xc-bugs] ERROR: Cannot create index whose evaluation cannot be enforced to remote nodes

[Ying He <yin...@ya...>]

hi, All,

I setup the following for testing:
host1 has gtm master 
host2 has gtm slave 
host3 has gtm_proxy1, coordinator1, datanode1
host4 has gtm_proxy2, coordinator2, datanode2

once it is up and running, i connect to coordinator1 to do the following:

create table t2
(a int) DISTRIBUTE BY REPLICATION ;

CREATE TABLE t3
( b int not null CONSTRAINT PK_t3 primary key,
c int)
INHERITS(t2);

will produce
ERROR:  Cannot create index whose evaluation cannot be enforced to remote nodes

looks like the INHERITS is not supported.

Please help if you see anything wrong. Thank you.

best,
Ying

Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.

[Ashutosh B. <ash...@en...>]

As you have spotted already changing make_ands_implicit() is not desirable,
since it has not got modified in many revisions of PostgreSQL and has many
callers which comply with the current behaviour.

The queries you mentioned are correctly inferring the datanode where to
ship the query. I think you will have to worry about queries like SELECT
c1, c2, c3, c4 FROM public.t1 WHERE ((((c1 = 1) AND (c2 = 2)) AND (c3 = 3))
AND (c4 = 4)), which might not be shipped to only required datanodes.

I do not have the right solutions at hand, but the existing ones are not
enough. You might want to continue experimenting more.


On Tue, Jan 14, 2014 at 12:31 PM, ZhangJulian
<jul...@ou...>wrote:

> Hi Ashutosh,
>
> I tested two more scenerios with the fix:
> postgres=# create table t1 (c1 int, c2 int, c3 int, c4 int);
> CREATE TABLE
> postgres=# explain verbose select * from t1 where c1=1 and c2=2 and c3=3
> and c4=4;
>                                                     QUERY
> PLAN
>
> ------------------------------------------------------------------------------------------------------------------
>  Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
>    Output: t1.c1, t1.c2, t1.c3, t1.c4
>    Node/s: *datanode1*
>    Remote query: SELECT c1, c2, c3, c4 FROM public.t1 WHERE ((((c1 = 1)
> AND (c2 = 2)) AND (c3 = 3)) AND (c4 = 4))
> (4 rows)
> postgres=# explain verbose select * from t1 where c1=1 and c2=2 and (c3=3
> or c4=4);
>                                                    QUERY
> PLAN
>
> -----------------------------------------------------------------------------------------------------------------
>  Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
>    Output: t1.c1, t1.c2, t1.c3, t1.c4
>    Node/s: *datanode1*
>    Remote query: SELECT c1, c2, c3, c4 FROM public.t1 WHERE (((c1 = 1) AND
> (c2 = 2)) AND ((c3 = 3) OR (c4 = 4)))
> (4 rows)
>
> For a complex predicate as "where c1=1 and c2=2 and c3=3 and c4=4", it
> will be parsed to "WHERE *((((*c1 = 1) AND (c2 = 2)) AND (c3 = 3)) AND
> (c4 = 4))", that is:
>  BoolExpr
>         BoolExpr
>                OpExpr (c1=1)
>                OpExpr (c2=2)
>         OpExpr (c3=3)
>  OpExpr (c4=4)
>
> Do you mean I should fix it in another way by updating
> make_ands_implicit() to return the list with all AND legs? It should be a
> better way, but there are several invocations to make_ands_implicit(), I
> can not understand all of the callers and have the confidence to fix it as
> it. :)
>
> The callers to make_ands_implicit():
> make_ands_implicit(Expr *) : List *
>     ATAddCheckConstraint(List * *, AlteredTableInfo *, Relation,
> Constraint *, bool, bool, LOCKMODE) : void
>     convert_EXISTS_to_ANY(PlannerInfo *, Query *, Node * *, List * *) :
> Query *
>     cost_subplan(PlannerInfo *, SubPlan *, Plan *) : void
>     DefineIndex(RangeVar *, char *, Oid, Oid, char *, char *, List *, Expr
> *, List *, List *, bool, bool, bool, bool, bool, bool, bool, bool, bool,
> bool) : Oid
>     ExecRelCheck(ResultRelInfo *, TupleTableSlot *, EState *) : const char
> *
>     get_relation_constraints(PlannerInfo *, Oid, RelOptInfo *, bool) :
> List *
>     pgxc_find_dist_equijoin_qual(List *, List *, Node *) : Expr *
>     pgxc_find_distcol_expr(Index, AttrNumber, Node *) : Expr *
>     preprocess_expression(PlannerInfo *, Node *, int) : Node *
>     RelationGetIndexPredicate(Relation) : List *
>     set_append_rel_size(PlannerInfo *, RelOptInfo *, Index, RangeTblEntry
> *) : void
>     TriggerEnabled(EState *, ResultRelInfo *, Trigger *, TriggerEvent,
> Bitmapset *, HeapTuple, HeapTuple) : bool
>     validateCheckConstraint(Relation, HeapTuple) : void
>
>
> Thanks
> Julian
>
> ------------------------------
> Date: Mon, 13 Jan 2014 15:11:00 +0530
>
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
> From: ash...@en...
> To: jul...@ou...
> CC: pos...@li...
>
> Ok, good observation.
>
> BTW, with your fix, we are still in problem if there are four conditions
> ANDED e.g. A and B and C and D.
>
> What should happen, and which actually happens before starting the
> planning phase, is all the ANDs are flattened into a list so that the above
> tree looks like
> AND
>  | -> args A, B, C, D
>
> So, a better solution seems to be that we should pass the quals to
> pgxc_find_dist_qual(), by converting them into and ANDed list of args.
>
>
> On Mon, Jan 13, 2014 at 2:43 PM, ZhangJulian <jul...@ou...>wrote:
>
> Hi Ashutosh，
>
> The parser will parse "where c1=1 and c2=1 and c3=1" to:
> BoolExpr
>     |--BoolExpr
>     |        |-OpExpr (c1=1)
>     |        |-OpExpr (c2=1)
>     |
>     |--OpExpr (c3=1)
>
> make_ands_implicit() will translate it to a list containing two elements:
> 1. BoolExpr
>           |-OpExpr (c1=1)
>           |-OpExpr (c2=1)
>
>  2. OpExpr (c3=1)
> But it will not further split the first element to two OpExpr and add them
> to the parent list.
>
> Thanks
> Julian
>
> ------------------------------
> Date: Mon, 13 Jan 2014 14:19:48 +0530
>
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
> From: ash...@en...
> To: jul...@ou...
> CC: pos...@li...
>
> Hi Julian,
> Can you please send a patch after taking care of my comments in my
> previous mail. I am pasting them here for your quick reference,
> --
> In function pgxc_find_distcol_expr() there is code
>
> 828     /* Convert the qualification into List if it's not already so */
> 829     if (!IsA(quals, List))
> 830         lquals = make_ands_implicit((Expr *)quals);
> 831     else
> 832         lquals = (List *)quals;
>
> which takes care of the quals which are not in the List form. This part of
> the code should have taken care of the case you mentioned. It seems that
> for some reason, this part is not converting the ANDed quals into List. Can
> you please check why? May be you want to step through make_ands_implicit()
> (using gdb or some debugger) to see what's happening there.
>
>
>
>
> On Mon, Jan 13, 2014 at 2:16 PM, ZhangJulian <jul...@ou...>wrote:
>
> Hi Ashutosh,
>
> I am so sorry that I had sent a patch with wrong format, it is because I
> edited it after Git Diff.
> I regenerated a patch as the attached file and verified that it can be
> applied.
>
> Thanks
> Julian
>
> ------------------------------
> From: jul...@ou...
> To: ash...@en...
> Date: Sat, 11 Jan 2014 07:52:34 +0800
> CC: pos...@li...
>
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
>
> Hi Ashutosh,
>
> I am at home and can not connect to the office to debug it today. I will
> double check the problem on Monday.
>
> I remember when I debugged it, the proplem is,
> Parser will parse "where c1=1 and c2=1 and c3=1" to  "WHERE (((c1 = 1)
> AND (c2 = 1)) AND (c3 = 1))", which is a list containing two elements:
> element 1:  ((c1 = 1) AND (c2 = 1)) which is a BoolExpr;
> element 2:  (c3 = 1)
> For the element 1, it would not split it into two simple predidates, so
> the c1 = 1 is missed.
>
> If I just move the c1 = 1 to the last position, the query can generate the
> correct plan.
>
> Thanks
> Julian
>
> ------------------------------
> Date: Fri, 10 Jan 2014 16:48:47 +0530
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
> From: ash...@en...
> To: jul...@ou...
> CC: pos...@li...
>
> Hi Julian,
> I looked at the patch and tried to apply the patch, but there is an error
> [ashutosh@ubuntu coderoot]git apply
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:10: trailing
> whitespace.
>         if (and_clause((Node *) qual_expr))
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:11: trailing
> whitespace.
>         {
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:12: trailing
> whitespace.
>             List *sub_lquals = ((BoolExpr *) qual_expr)->args;
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:13: trailing
> whitespace.
>             distcol_expr = pgxc_find_distcol_expr(varno, attrNum,
> sub_lquals);
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:14: trailing
> whitespace.
>             if (distcol_expr != NULL)
> fatal: corrupt patch at line 20
>
> Did you use git diff for creating the patch?
>
> BTW, I looked at the patch. The patch seems to be applied to function
> pgxc_find_distcol_expr() (functions name usually appears along with the
> diff, if you are using git diff).
>
> In this function there is code
>
> 828     /* Convert the qualification into List if it's not already so */
> 829     if (!IsA(quals, List))
> 830         lquals = make_ands_implicit((Expr *)quals);
> 831     else
> 832         lquals = (List *)quals;
>
> which takes care of the quals which are not in the List form. This part of
> the code should have taken care of the case you mentioned. It seems that
> for some reason, this part is not converting the ANDed quals into List. Can
> you please check why? May be you want to step through make_ands_implicit()
> (using gdb or some debugger) to see what's happening there.
>
>
>
>
> On Fri, Jan 10, 2014 at 3:38 PM, ZhangJulian <jul...@ou...>wrote:
>
>
> Your name                  : Julian ZL Zhang
> Your 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:
> ------------------------------------------------
> I had opened the bug at http://sourceforge.net/p/postgres-xc/bugs/467/ just
> descripte it more detailedly here.
> Different predicate order results in different plan, the query should be
> shipped only to one datanode, but in the first example, two data nodes are
> involved.
>
> Please describe a way to repeat the problem. Please try to provide a
> concise reproducible example, if at all possible:
> ----------------------------------------------------------------------
>  postgres=# create table t1 (c1 int, c2 int, c3 int) distribute by
> hash(c1);
> CREATE TABLE
> postgres=# explain verbose select * from t1 where c1=1 and c2=1 and c3=1;
> QUERY PLAN
>
> --------------------------------------------------------------------------------
> Data Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
> Output: t1.c1, t1.c2, t1.c3
> Node/s: datanode1, datanode2
> Remote query: SELECT c1, c2, c3 FROM benchmarksql.t1 WHERE (((c1 = 1) AND
> (c2 = 1)) AND (c3 = 1))
> (4 rows)
> postgres=# explain verbose select * from t1 where c2=1 and c3=1 and c1=1;
> QUERY PLAN
>
> --------------------------------------------------------------------------------
> Data Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
> Output: t1.c1, t1.c2, t1.c3
> Node/s: datanode1
> Remote query: SELECT c1, c2, c3 FROM benchmarksql.t1 WHERE (((c2 = 1) AND
> (c3 = 1)) AND (c1 = 1))
> (4 rows)
>
>
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
> I made a fix as the append file, which can resolve this issue, but I am
> not sure if it is correct.
> When I run MAKE CHECK, 3 test cases failed. The results are appended too.
> But when I run MAKE CHECK without the fix, there are also some test cases
> failed.
> I do not know if the failed test cases are related to the fix.
>
> Thanks
> Julian
>
>
> ------------------------------------------------------------------------------
> 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
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
> ------------------------------------------------------------------------------
> 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
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>



-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.

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

Hi Ashutosh,

I tested two more scenerios with the fix:
postgres=# create table t1 (c1 int, c2 int, c3 int, c4 int);
CREATE TABLE
postgres=# explain verbose select * from t1 where c1=1 and c2=2 and c3=3 and c4=4;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: t1.c1, t1.c2, t1.c3, t1.c4
   Node/s: datanode1
   Remote query: SELECT c1, c2, c3, c4 FROM public.t1 WHERE ((((c1 = 1) AND (c2 = 2)) AND (c3 = 3)) AND (c4 = 4))
(4 rows)
postgres=# explain verbose select * from t1 where c1=1 and c2=2 and (c3=3 or c4=4);
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
   Output: t1.c1, t1.c2, t1.c3, t1.c4
   Node/s: datanode1
   Remote query: SELECT c1, c2, c3, c4 FROM public.t1 WHERE (((c1 = 1) AND (c2 = 2)) AND ((c3 = 3) OR (c4 = 4)))
(4 rows)

For a complex predicate as "where c1=1 and c2=2 and c3=3 and c4=4", it will be parsed to "WHERE ((((c1 = 1) AND (c2 = 2)) AND (c3 = 3)) AND (c4 = 4))", that is:
 BoolExpr
        BoolExpr
               OpExpr (c1=1)
               OpExpr (c2=2)
        OpExpr (c3=3)
 OpExpr (c4=4)

Do you mean I should fix it in another way by updating make_ands_implicit() to return the list with all AND legs? It should be a better way, but there are several invocations to make_ands_implicit(), I can not understand all of the callers and have the confidence to fix it as it. :)

The callers to make_ands_implicit():
make_ands_implicit(Expr *) : List *
    ATAddCheckConstraint(List * *, AlteredTableInfo *, Relation, Constraint *, bool, bool, LOCKMODE) : void
    convert_EXISTS_to_ANY(PlannerInfo *, Query *, Node * *, List * *) : Query *
    cost_subplan(PlannerInfo *, SubPlan *, Plan *) : void
    DefineIndex(RangeVar *, char *, Oid, Oid, char *, char *, List *, Expr *, List *, List *, bool, bool, bool, bool, bool, bool, bool, bool, bool, bool) : Oid
    ExecRelCheck(ResultRelInfo *, TupleTableSlot *, EState *) : const char *
    get_relation_constraints(PlannerInfo *, Oid, RelOptInfo *, bool) : List *
    pgxc_find_dist_equijoin_qual(List *, List *, Node *) : Expr *
    pgxc_find_distcol_expr(Index, AttrNumber, Node *) : Expr *
    preprocess_expression(PlannerInfo *, Node *, int) : Node *
    RelationGetIndexPredicate(Relation) : List *
    set_append_rel_size(PlannerInfo *, RelOptInfo *, Index, RangeTblEntry *) : void
    TriggerEnabled(EState *, ResultRelInfo *, Trigger *, TriggerEvent, Bitmapset *, HeapTuple, HeapTuple) : bool
    validateCheckConstraint(Relation, HeapTuple) : void


Thanks
Julian

Date: Mon, 13 Jan 2014 15:11:00 +0530
Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.
From: ash...@en...
To: jul...@ou...
CC: pos...@li...

Ok, good observation.

BTW, with your fix, we are still in problem if there are four conditions ANDED e.g. A and B and C and D.

What should happen, and which actually happens before starting the planning phase, is all the ANDs are flattened into a list so that the above tree looks like 

AND
 | -> args A, B, C, D

So, a better solution seems to be that we should pass the quals to pgxc_find_dist_qual(), by converting them into and ANDed list of args.



On Mon, Jan 13, 2014 at 2:43 PM, ZhangJulian <jul...@ou...> wrote:




Hi Ashutosh，

The parser will parse "where c1=1 and c2=1 and c3=1" to:
BoolExpr
    |--BoolExpr
    |        |-OpExpr (c1=1)

    |        |-OpExpr (c2=1)
    |
    |--OpExpr (c3=1)

make_ands_implicit() will translate it to a list containing two elements:
1. BoolExpr
          |-OpExpr (c1=1)
          |-OpExpr (c2=1)
  

 2. OpExpr (c3=1)
But it will not further split the first element to two OpExpr and add them to the parent list. 

Thanks
Julian

Date: Mon, 13 Jan 2014 14:19:48 +0530
Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.

From: ash...@en...
To: jul...@ou...
CC: pos...@li...


Hi Julian,
Can you please send a patch after taking care of my comments in my previous mail. I am pasting them here for your quick reference,
--
In function pgxc_find_distcol_expr() there is code 



828     /* Convert the qualification into List if it's not already so */
829     if (!IsA(quals, List))
830         lquals = make_ands_implicit((Expr *)quals);

831     else
832         lquals = (List *)quals;

which
 takes care of the quals which are not in the List form. This part of 
the code should have taken care of the case you mentioned. It seems that
 for some reason, this part is not converting the ANDed quals into List.
 Can you please check why? May be you want to step through 
make_ands_implicit() (using gdb or some debugger) to see what's 
happening there.





On Mon, Jan 13, 2014 at 2:16 PM, ZhangJulian <jul...@ou...> wrote:





Hi Ashutosh,

I am so sorry that I had sent a patch with wrong format, it is because I edited it after Git Diff. 
I regenerated a patch as the attached file and verified that it can be applied.



Thanks
Julian

From: jul...@ou...
To: ash...@en...


Date: Sat, 11 Jan 2014 07:52:34 +0800
CC: pos...@li...
Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.






Hi Ashutosh,

I am at home and can not connect to the office to debug it today. I will double check the problem on Monday. 

I remember when I debugged it, the proplem is,
Parser will parse "where c1=1 and c2=1 and c3=1" to  "WHERE (((c1 = 1) AND (c2 = 1)) AND (c3 = 1))", which is a list containing two elements:


element 1:  ((c1 = 1) AND (c2 = 1)) which is a BoolExpr;
element 2:  (c3 = 1)
For the element 1, it would not split it into two simple predidates, so the c1 = 1 is missed. 



If I just move the c1 = 1 to the last position, the query can generate the correct plan. 

Thanks
Julian

Date: Fri, 10 Jan 2014 16:48:47 +0530
Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.


From: ash...@en...
To: jul...@ou...
CC: pos...@li...



Hi Julian,
I looked at the patch and tried to apply the patch, but there is an error
[ashutosh@ubuntu coderoot]git apply /mnt/hgfs/tmp/20140110_more_datanode_involved.patch 
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:10: trailing whitespace.



        if (and_clause((Node *) qual_expr))
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:11: trailing whitespace.
        {
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:12: trailing whitespace.


            List *sub_lquals = ((BoolExpr *) qual_expr)->args;

/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:13: trailing whitespace.
            distcol_expr = pgxc_find_distcol_expr(varno, attrNum, sub_lquals);
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:14: trailing whitespace.



            if (distcol_expr != NULL)
fatal: corrupt patch at line 20

Did you use git diff for creating the patch?

BTW, I looked at the patch. The patch seems to be applied to function pgxc_find_distcol_expr() (functions name usually appears along with the diff, if you are using git diff).




In this function there is code 

828     /* Convert the qualification into List if it's not already so */
829     if (!IsA(quals, List))
830         lquals = make_ands_implicit((Expr *)quals);



831     else
832         lquals = (List *)quals;

which takes care of the quals which are not in the List form. This part of the code should have taken care of the case you mentioned. It seems that for some reason, this part is not converting the ANDed quals into List. Can you please check why? May be you want to step through make_ands_implicit() (using gdb or some debugger) to see what's happening there.







On Fri, Jan 10, 2014 at 3:38 PM, ZhangJulian <jul...@ou...> wrote:






 

Your 
name                  : Julian ZL Zhang
Your 
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:
------------------------------------------------
I had opened the bug at http://sourceforge.net/p/postgres-xc/bugs/467/ just descripte it more detailedly here. 



Different predicate order results in different plan, the 
query should be shipped only to one datanode, but in the first example, two data 
nodes are involved.

 

Please 
describe a way to repeat the problem. Please try to provide a
concise 
reproducible example, if at all possible:
----------------------------------------------------------------------

postgres=# create table t1 (c1 int, c2 int, c3 int) 
distribute by hash(c1);
CREATE TABLE
postgres=# explain verbose select * 
from t1 where c1=1 and c2=1 and c3=1;
QUERY PLAN 

--------------------------------------------------------------------------------
Data 
Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
Output: 
t1.c1, t1.c2, t1.c3
Node/s: datanode1, datanode2
Remote query: SELECT c1, 
c2, c3 FROM benchmarksql.t1 WHERE (((c1 = 1) AND (c2 = 1)) AND (c3 = 1))
(4 
rows)
postgres=# explain verbose select * from t1 where c2=1 
and c3=1 and c1=1;
QUERY 
PLAN
--------------------------------------------------------------------------------
Data 
Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
Output: 
t1.c1, t1.c2, t1.c3
Node/s: datanode1
Remote query: SELECT c1, c2, c3 FROM 
benchmarksql.t1 WHERE (((c2 = 1) AND (c3 = 1)) AND (c1 = 1))
(4 
rows)





If 
you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
I 
made a fix as the append file, which can resolve this issue, but I am not sure if it is correct. 
When I run MAKE CHECK, 3 test cases failed. The results are appended too. 
But when I run MAKE CHECK without the fix, there are also some test cases failed. 



I do not know if the failed test cases are related to the fix. 

Thanks
Julian 
 		 	   		  

------------------------------------------------------------------------------

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




-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

 		 	   		  

------------------------------------------------------------------------------
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 		 	   		  


-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

 		 	   		  


-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

 		 	   		  

Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.

[Ashutosh B. <ash...@en...>]

Ok, good observation.

BTW, with your fix, we are still in problem if there are four conditions
ANDED e.g. A and B and C and D.

What should happen, and which actually happens before starting the planning
phase, is all the ANDs are flattened into a list so that the above tree
looks like
AND
 | -> args A, B, C, D

So, a better solution seems to be that we should pass the quals to
pgxc_find_dist_qual(), by converting them into and ANDed list of args.


On Mon, Jan 13, 2014 at 2:43 PM, ZhangJulian <jul...@ou...>wrote:

> Hi Ashutosh，
>
> The parser will parse "where c1=1 and c2=1 and c3=1" to:
> BoolExpr
>     |--BoolExpr
>     |        |-OpExpr (c1=1)
>     |        |-OpExpr (c2=1)
>     |
>     |--OpExpr (c3=1)
>
> make_ands_implicit() will translate it to a list containing two elements:
> 1. BoolExpr
>           |-OpExpr (c1=1)
>           |-OpExpr (c2=1)
>
>  2. OpExpr (c3=1)
> But it will not further split the first element to two OpExpr and add them
> to the parent list.
>
> Thanks
> Julian
>
> ------------------------------
> Date: Mon, 13 Jan 2014 14:19:48 +0530
>
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
> From: ash...@en...
> To: jul...@ou...
> CC: pos...@li...
>
> Hi Julian,
> Can you please send a patch after taking care of my comments in my
> previous mail. I am pasting them here for your quick reference,
> --
> In function pgxc_find_distcol_expr() there is code
>
> 828     /* Convert the qualification into List if it's not already so */
> 829     if (!IsA(quals, List))
> 830         lquals = make_ands_implicit((Expr *)quals);
> 831     else
> 832         lquals = (List *)quals;
>
> which takes care of the quals which are not in the List form. This part of
> the code should have taken care of the case you mentioned. It seems that
> for some reason, this part is not converting the ANDed quals into List. Can
> you please check why? May be you want to step through make_ands_implicit()
> (using gdb or some debugger) to see what's happening there.
>
>
>
>
> On Mon, Jan 13, 2014 at 2:16 PM, ZhangJulian <jul...@ou...>wrote:
>
> Hi Ashutosh,
>
> I am so sorry that I had sent a patch with wrong format, it is because I
> edited it after Git Diff.
> I regenerated a patch as the attached file and verified that it can be
> applied.
>
> Thanks
> Julian
>
> ------------------------------
> From: jul...@ou...
> To: ash...@en...
> Date: Sat, 11 Jan 2014 07:52:34 +0800
> CC: pos...@li...
>
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
>
> Hi Ashutosh,
>
> I am at home and can not connect to the office to debug it today. I will
> double check the problem on Monday.
>
> I remember when I debugged it, the proplem is,
> Parser will parse "where c1=1 and c2=1 and c3=1" to  "WHERE (((c1 = 1)
> AND (c2 = 1)) AND (c3 = 1))", which is a list containing two elements:
> element 1:  ((c1 = 1) AND (c2 = 1)) which is a BoolExpr;
> element 2:  (c3 = 1)
> For the element 1, it would not split it into two simple predidates, so
> the c1 = 1 is missed.
>
> If I just move the c1 = 1 to the last position, the query can generate the
> correct plan.
>
> Thanks
> Julian
>
> ------------------------------
> Date: Fri, 10 Jan 2014 16:48:47 +0530
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
> From: ash...@en...
> To: jul...@ou...
> CC: pos...@li...
>
> Hi Julian,
> I looked at the patch and tried to apply the patch, but there is an error
> [ashutosh@ubuntu coderoot]git apply
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:10: trailing
> whitespace.
>         if (and_clause((Node *) qual_expr))
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:11: trailing
> whitespace.
>         {
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:12: trailing
> whitespace.
>             List *sub_lquals = ((BoolExpr *) qual_expr)->args;
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:13: trailing
> whitespace.
>             distcol_expr = pgxc_find_distcol_expr(varno, attrNum,
> sub_lquals);
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:14: trailing
> whitespace.
>             if (distcol_expr != NULL)
> fatal: corrupt patch at line 20
>
> Did you use git diff for creating the patch?
>
> BTW, I looked at the patch. The patch seems to be applied to function
> pgxc_find_distcol_expr() (functions name usually appears along with the
> diff, if you are using git diff).
>
> In this function there is code
>
> 828     /* Convert the qualification into List if it's not already so */
> 829     if (!IsA(quals, List))
> 830         lquals = make_ands_implicit((Expr *)quals);
> 831     else
> 832         lquals = (List *)quals;
>
> which takes care of the quals which are not in the List form. This part of
> the code should have taken care of the case you mentioned. It seems that
> for some reason, this part is not converting the ANDed quals into List. Can
> you please check why? May be you want to step through make_ands_implicit()
> (using gdb or some debugger) to see what's happening there.
>
>
>
>
> On Fri, Jan 10, 2014 at 3:38 PM, ZhangJulian <jul...@ou...>wrote:
>
>
> Your name                  : Julian ZL Zhang
> Your 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:
> ------------------------------------------------
> I had opened the bug at http://sourceforge.net/p/postgres-xc/bugs/467/ just
> descripte it more detailedly here.
> Different predicate order results in different plan, the query should be
> shipped only to one datanode, but in the first example, two data nodes are
> involved.
>
> Please describe a way to repeat the problem. Please try to provide a
> concise reproducible example, if at all possible:
> ----------------------------------------------------------------------
>  postgres=# create table t1 (c1 int, c2 int, c3 int) distribute by
> hash(c1);
> CREATE TABLE
> postgres=# explain verbose select * from t1 where c1=1 and c2=1 and c3=1;
> QUERY PLAN
>
> --------------------------------------------------------------------------------
> Data Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
> Output: t1.c1, t1.c2, t1.c3
> Node/s: datanode1, datanode2
> Remote query: SELECT c1, c2, c3 FROM benchmarksql.t1 WHERE (((c1 = 1) AND
> (c2 = 1)) AND (c3 = 1))
> (4 rows)
> postgres=# explain verbose select * from t1 where c2=1 and c3=1 and c1=1;
> QUERY PLAN
>
> --------------------------------------------------------------------------------
> Data Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
> Output: t1.c1, t1.c2, t1.c3
> Node/s: datanode1
> Remote query: SELECT c1, c2, c3 FROM benchmarksql.t1 WHERE (((c2 = 1) AND
> (c3 = 1)) AND (c1 = 1))
> (4 rows)
>
>
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
> I made a fix as the append file, which can resolve this issue, but I am
> not sure if it is correct.
> When I run MAKE CHECK, 3 test cases failed. The results are appended too.
> But when I run MAKE CHECK without the fix, there are also some test cases
> failed.
> I do not know if the failed test cases are related to the fix.
>
> Thanks
> Julian
>
>
> ------------------------------------------------------------------------------
> 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
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
> ------------------------------------------------------------------------------
> 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
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>



-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

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 		 	   		  

Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.

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

Hi Ashutosh，

The parser will parse "where c1=1 and c2=1 and c3=1" to:
BoolExpr
    |--BoolExpr
    |        |-OpExpr (c1=1)
    |        |-OpExpr (c2=1)
    |
    |--OpExpr (c3=1)

make_ands_implicit() will translate it to a list containing two elements:
1. BoolExpr
          |-OpExpr (c1=1)
          |-OpExpr (c2=1)
  
 2. OpExpr (c3=1)
But it will not further split the first element to two OpExpr and add them to the parent list. 

Thanks
Julian

Date: Mon, 13 Jan 2014 14:19:48 +0530
Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.
From: ash...@en...
To: jul...@ou...
CC: pos...@li...

Hi Julian,
Can you please send a patch after taking care of my comments in my previous mail. I am pasting them here for your quick reference,
--
In function pgxc_find_distcol_expr() there is code 


828     /* Convert the qualification into List if it's not already so */
829     if (!IsA(quals, List))
830         lquals = make_ands_implicit((Expr *)quals);

831     else
832         lquals = (List *)quals;

which
 takes care of the quals which are not in the List form. This part of 
the code should have taken care of the case you mentioned. It seems that
 for some reason, this part is not converting the ANDed quals into List.
 Can you please check why? May be you want to step through 
make_ands_implicit() (using gdb or some debugger) to see what's 
happening there.





On Mon, Jan 13, 2014 at 2:16 PM, ZhangJulian <jul...@ou...> wrote:




Hi Ashutosh,

I am so sorry that I had sent a patch with wrong format, it is because I edited it after Git Diff. 
I regenerated a patch as the attached file and verified that it can be applied.


Thanks
Julian

From: jul...@ou...
To: ash...@en...

Date: Sat, 11 Jan 2014 07:52:34 +0800
CC: pos...@li...
Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.





Hi Ashutosh,

I am at home and can not connect to the office to debug it today. I will double check the problem on Monday. 

I remember when I debugged it, the proplem is,
Parser will parse "where c1=1 and c2=1 and c3=1" to  "WHERE (((c1 = 1) AND (c2 = 1)) AND (c3 = 1))", which is a list containing two elements:

element 1:  ((c1 = 1) AND (c2 = 1)) which is a BoolExpr;
element 2:  (c3 = 1)
For the element 1, it would not split it into two simple predidates, so the c1 = 1 is missed. 


If I just move the c1 = 1 to the last position, the query can generate the correct plan. 

Thanks
Julian

Date: Fri, 10 Jan 2014 16:48:47 +0530
Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.

From: ash...@en...
To: jul...@ou...
CC: pos...@li...


Hi Julian,
I looked at the patch and tried to apply the patch, but there is an error
[ashutosh@ubuntu coderoot]git apply /mnt/hgfs/tmp/20140110_more_datanode_involved.patch 
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:10: trailing whitespace.


        if (and_clause((Node *) qual_expr))
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:11: trailing whitespace.
        {
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:12: trailing whitespace.

            List *sub_lquals = ((BoolExpr *) qual_expr)->args;

/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:13: trailing whitespace.
            distcol_expr = pgxc_find_distcol_expr(varno, attrNum, sub_lquals);
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:14: trailing whitespace.


            if (distcol_expr != NULL)
fatal: corrupt patch at line 20

Did you use git diff for creating the patch?

BTW, I looked at the patch. The patch seems to be applied to function pgxc_find_distcol_expr() (functions name usually appears along with the diff, if you are using git diff).



In this function there is code 

828     /* Convert the qualification into List if it's not already so */
829     if (!IsA(quals, List))
830         lquals = make_ands_implicit((Expr *)quals);


831     else
832         lquals = (List *)quals;

which takes care of the quals which are not in the List form. This part of the code should have taken care of the case you mentioned. It seems that for some reason, this part is not converting the ANDed quals into List. Can you please check why? May be you want to step through make_ands_implicit() (using gdb or some debugger) to see what's happening there.






On Fri, Jan 10, 2014 at 3:38 PM, ZhangJulian <jul...@ou...> wrote:





 

Your 
name                  : Julian ZL Zhang
Your 
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:
------------------------------------------------
I had opened the bug at http://sourceforge.net/p/postgres-xc/bugs/467/ just descripte it more detailedly here. 


Different predicate order results in different plan, the 
query should be shipped only to one datanode, but in the first example, two data 
nodes are involved.

 

Please 
describe a way to repeat the problem. Please try to provide a
concise 
reproducible example, if at all possible:
----------------------------------------------------------------------

postgres=# create table t1 (c1 int, c2 int, c3 int) 
distribute by hash(c1);
CREATE TABLE
postgres=# explain verbose select * 
from t1 where c1=1 and c2=1 and c3=1;
QUERY PLAN 

--------------------------------------------------------------------------------
Data 
Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
Output: 
t1.c1, t1.c2, t1.c3
Node/s: datanode1, datanode2
Remote query: SELECT c1, 
c2, c3 FROM benchmarksql.t1 WHERE (((c1 = 1) AND (c2 = 1)) AND (c3 = 1))
(4 
rows)
postgres=# explain verbose select * from t1 where c2=1 
and c3=1 and c1=1;
QUERY 
PLAN
--------------------------------------------------------------------------------
Data 
Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
Output: 
t1.c1, t1.c2, t1.c3
Node/s: datanode1
Remote query: SELECT c1, c2, c3 FROM 
benchmarksql.t1 WHERE (((c2 = 1) AND (c3 = 1)) AND (c1 = 1))
(4 
rows)





If 
you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
I 
made a fix as the append file, which can resolve this issue, but I am not sure if it is correct. 
When I run MAKE CHECK, 3 test cases failed. The results are appended too. 
But when I run MAKE CHECK without the fix, there are also some test cases failed. 


I do not know if the failed test cases are related to the fix. 

Thanks
Julian 
 		 	   		  

------------------------------------------------------------------------------

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




-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

 		 	   		  

------------------------------------------------------------------------------
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 		 	   		  


-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

 		 	   		  

Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.

[Ashutosh B. <ash...@en...>]

Hi Julian,
Can you please send a patch after taking care of my comments in my previous
mail. I am pasting them here for your quick reference,
--
In function pgxc_find_distcol_expr() there is code

828     /* Convert the qualification into List if it's not already so */
829     if (!IsA(quals, List))
830         lquals = make_ands_implicit((Expr *)quals);
831     else
832         lquals = (List *)quals;

which takes care of the quals which are not in the List form. This part of
the code should have taken care of the case you mentioned. It seems that
for some reason, this part is not converting the ANDed quals into List. Can
you please check why? May be you want to step through make_ands_implicit()
(using gdb or some debugger) to see what's happening there.




On Mon, Jan 13, 2014 at 2:16 PM, ZhangJulian <jul...@ou...>wrote:

> Hi Ashutosh,
>
> I am so sorry that I had sent a patch with wrong format, it is because I
> edited it after Git Diff.
> I regenerated a patch as the attached file and verified that it can be
> applied.
>
> Thanks
> Julian
>
> ------------------------------
> From: jul...@ou...
> To: ash...@en...
> Date: Sat, 11 Jan 2014 07:52:34 +0800
> CC: pos...@li...
>
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
>
> Hi Ashutosh,
>
> I am at home and can not connect to the office to debug it today. I will
> double check the problem on Monday.
>
> I remember when I debugged it, the proplem is,
> Parser will parse "where c1=1 and c2=1 and c3=1" to  "WHERE (((c1 = 1)
> AND (c2 = 1)) AND (c3 = 1))", which is a list containing two elements:
> element 1:  ((c1 = 1) AND (c2 = 1)) which is a BoolExpr;
> element 2:  (c3 = 1)
> For the element 1, it would not split it into two simple predidates, so
> the c1 = 1 is missed.
>
> If I just move the c1 = 1 to the last position, the query can generate the
> correct plan.
>
> Thanks
> Julian
>
> ------------------------------
> Date: Fri, 10 Jan 2014 16:48:47 +0530
> Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more
> than needed.
> From: ash...@en...
> To: jul...@ou...
> CC: pos...@li...
>
> Hi Julian,
> I looked at the patch and tried to apply the patch, but there is an error
> [ashutosh@ubuntu coderoot]git apply
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:10: trailing
> whitespace.
>         if (and_clause((Node *) qual_expr))
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:11: trailing
> whitespace.
>         {
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:12: trailing
> whitespace.
>             List *sub_lquals = ((BoolExpr *) qual_expr)->args;
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:13: trailing
> whitespace.
>             distcol_expr = pgxc_find_distcol_expr(varno, attrNum,
> sub_lquals);
> /mnt/hgfs/tmp/20140110_more_datanode_involved.patch:14: trailing
> whitespace.
>             if (distcol_expr != NULL)
> fatal: corrupt patch at line 20
>
> Did you use git diff for creating the patch?
>
> BTW, I looked at the patch. The patch seems to be applied to function
> pgxc_find_distcol_expr() (functions name usually appears along with the
> diff, if you are using git diff).
>
> In this function there is code
>
> 828     /* Convert the qualification into List if it's not already so */
> 829     if (!IsA(quals, List))
> 830         lquals = make_ands_implicit((Expr *)quals);
> 831     else
> 832         lquals = (List *)quals;
>
> which takes care of the quals which are not in the List form. This part of
> the code should have taken care of the case you mentioned. It seems that
> for some reason, this part is not converting the ANDed quals into List. Can
> you please check why? May be you want to step through make_ands_implicit()
> (using gdb or some debugger) to see what's happening there.
>
>
>
>
> On Fri, Jan 10, 2014 at 3:38 PM, ZhangJulian <jul...@ou...>wrote:
>
>
> Your name                  : Julian ZL Zhang
> Your 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:
> ------------------------------------------------
> I had opened the bug at http://sourceforge.net/p/postgres-xc/bugs/467/ just
> descripte it more detailedly here.
> Different predicate order results in different plan, the query should be
> shipped only to one datanode, but in the first example, two data nodes are
> involved.
>
> Please describe a way to repeat the problem. Please try to provide a
> concise reproducible example, if at all possible:
> ----------------------------------------------------------------------
>  postgres=# create table t1 (c1 int, c2 int, c3 int) distribute by
> hash(c1);
> CREATE TABLE
> postgres=# explain verbose select * from t1 where c1=1 and c2=1 and c3=1;
> QUERY PLAN
>
> --------------------------------------------------------------------------------
> Data Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
> Output: t1.c1, t1.c2, t1.c3
> Node/s: datanode1, datanode2
> Remote query: SELECT c1, c2, c3 FROM benchmarksql.t1 WHERE (((c1 = 1) AND
> (c2 = 1)) AND (c3 = 1))
> (4 rows)
> postgres=# explain verbose select * from t1 where c2=1 and c3=1 and c1=1;
> QUERY PLAN
>
> --------------------------------------------------------------------------------
> Data Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
> Output: t1.c1, t1.c2, t1.c3
> Node/s: datanode1
> Remote query: SELECT c1, c2, c3 FROM benchmarksql.t1 WHERE (((c2 = 1) AND
> (c3 = 1)) AND (c1 = 1))
> (4 rows)
>
>
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
> I made a fix as the append file, which can resolve this issue, but I am
> not sure if it is correct.
> When I run MAKE CHECK, 3 test cases failed. The results are appended too.
> But when I run MAKE CHECK without the fix, there are also some test cases
> failed.
> I do not know if the failed test cases are related to the fix.
>
> Thanks
> Julian
>
>
> ------------------------------------------------------------------------------
> 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
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
> ------------------------------------------------------------------------------
> 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
>



-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.

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

Hi Ashutosh,

I am at home and can not connect to the office to debug it today. I will double check the problem on Monday. 

I remember when I debugged it, the proplem is,
Parser will parse "where c1=1 and c2=1 and c3=1" to  "WHERE (((c1 = 1) AND (c2 = 1)) AND (c3 = 1))", which is a list containing two elements:
element 1:  ((c1 = 1) AND (c2 = 1)) which is a BoolExpr;
element 2:  (c3 = 1)
For the element 1, it would not split it into two simple predidates, so the c1 = 1 is missed. 

If I just move the c1 = 1 to the last position, the query can generate the correct plan. 

Thanks
Julian

Date: Fri, 10 Jan 2014 16:48:47 +0530
Subject: Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.
From: ash...@en...
To: jul...@ou...
CC: pos...@li...

Hi Julian,
I looked at the patch and tried to apply the patch, but there is an error
[ashutosh@ubuntu coderoot]git apply /mnt/hgfs/tmp/20140110_more_datanode_involved.patch 
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:10: trailing whitespace.

        if (and_clause((Node *) qual_expr))
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:11: trailing whitespace.
        {
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:12: trailing whitespace.
            List *sub_lquals = ((BoolExpr *) qual_expr)->args;

/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:13: trailing whitespace.
            distcol_expr = pgxc_find_distcol_expr(varno, attrNum, sub_lquals);
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:14: trailing whitespace.

            if (distcol_expr != NULL)
fatal: corrupt patch at line 20

Did you use git diff for creating the patch?

BTW, I looked at the patch. The patch seems to be applied to function pgxc_find_distcol_expr() (functions name usually appears along with the diff, if you are using git diff).


In this function there is code 

828     /* Convert the qualification into List if it's not already so */
829     if (!IsA(quals, List))
830         lquals = make_ands_implicit((Expr *)quals);

831     else
832         lquals = (List *)quals;

which takes care of the quals which are not in the List form. This part of the code should have taken care of the case you mentioned. It seems that for some reason, this part is not converting the ANDed quals into List. Can you please check why? May be you want to step through make_ands_implicit() (using gdb or some debugger) to see what's happening there.





On Fri, Jan 10, 2014 at 3:38 PM, ZhangJulian <jul...@ou...> wrote:




 

Your 
name                  : Julian ZL Zhang
Your 
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:
------------------------------------------------
I had opened the bug at http://sourceforge.net/p/postgres-xc/bugs/467/ just descripte it more detailedly here. 

Different predicate order results in different plan, the 
query should be shipped only to one datanode, but in the first example, two data 
nodes are involved.

 

Please 
describe a way to repeat the problem. Please try to provide a
concise 
reproducible example, if at all possible:
----------------------------------------------------------------------

postgres=# create table t1 (c1 int, c2 int, c3 int) 
distribute by hash(c1);
CREATE TABLE
postgres=# explain verbose select * 
from t1 where c1=1 and c2=1 and c3=1;
QUERY PLAN 

--------------------------------------------------------------------------------
Data 
Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
Output: 
t1.c1, t1.c2, t1.c3
Node/s: datanode1, datanode2
Remote query: SELECT c1, 
c2, c3 FROM benchmarksql.t1 WHERE (((c1 = 1) AND (c2 = 1)) AND (c3 = 1))
(4 
rows)
postgres=# explain verbose select * from t1 where c2=1 
and c3=1 and c1=1;
QUERY 
PLAN
--------------------------------------------------------------------------------
Data 
Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
Output: 
t1.c1, t1.c2, t1.c3
Node/s: datanode1
Remote query: SELECT c1, c2, c3 FROM 
benchmarksql.t1 WHERE (((c2 = 1) AND (c3 = 1)) AND (c1 = 1))
(4 
rows)





If 
you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
I 
made a fix as the append file, which can resolve this issue, but I am not sure if it is correct. 
When I run MAKE CHECK, 3 test cases failed. The results are appended too. 
But when I run MAKE CHECK without the fix, there are also some test cases failed. 

I do not know if the failed test cases are related to the fix. 

Thanks
Julian 
 		 	   		  

------------------------------------------------------------------------------

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




-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

 		 	   		  

Re: [Postgres-xc-bugs] Query are shipped to the data nodes more than needed.

[Ashutosh B. <ash...@en...>]

Hi Julian,
I looked at the patch and tried to apply the patch, but there is an error
[ashutosh@ubuntu coderoot]git apply
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:10: trailing whitespace.
        if (and_clause((Node *) qual_expr))
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:11: trailing whitespace.
        {
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:12: trailing whitespace.
            List *sub_lquals = ((BoolExpr *) qual_expr)->args;
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:13: trailing whitespace.
            distcol_expr = pgxc_find_distcol_expr(varno, attrNum,
sub_lquals);
/mnt/hgfs/tmp/20140110_more_datanode_involved.patch:14: trailing whitespace.
            if (distcol_expr != NULL)
fatal: corrupt patch at line 20

Did you use git diff for creating the patch?

BTW, I looked at the patch. The patch seems to be applied to function
pgxc_find_distcol_expr() (functions name usually appears along with the
diff, if you are using git diff).

In this function there is code

828     /* Convert the qualification into List if it's not already so */
829     if (!IsA(quals, List))
830         lquals = make_ands_implicit((Expr *)quals);
831     else
832         lquals = (List *)quals;

which takes care of the quals which are not in the List form. This part of
the code should have taken care of the case you mentioned. It seems that
for some reason, this part is not converting the ANDed quals into List. Can
you please check why? May be you want to step through make_ands_implicit()
(using gdb or some debugger) to see what's happening there.




On Fri, Jan 10, 2014 at 3:38 PM, ZhangJulian <jul...@ou...>wrote:

>
> Your name                  : Julian ZL Zhang
> Your 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:
> ------------------------------------------------
> I had opened the bug at http://sourceforge.net/p/postgres-xc/bugs/467/ just
> descripte it more detailedly here.
> Different predicate order results in different plan, the query should be
> shipped only to one datanode, but in the first example, two data nodes are
> involved.
>
> Please describe a way to repeat the problem. Please try to provide a
> concise reproducible example, if at all possible:
> ----------------------------------------------------------------------
>  postgres=# create table t1 (c1 int, c2 int, c3 int) distribute by
> hash(c1);
> CREATE TABLE
> postgres=# explain verbose select * from t1 where c1=1 and c2=1 and c3=1;
> QUERY PLAN
>
> --------------------------------------------------------------------------------
> Data Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
> Output: t1.c1, t1.c2, t1.c3
> Node/s: datanode1, datanode2
> Remote query: SELECT c1, c2, c3 FROM benchmarksql.t1 WHERE (((c1 = 1) AND
> (c2 = 1)) AND (c3 = 1))
> (4 rows)
> postgres=# explain verbose select * from t1 where c2=1 and c3=1 and c1=1;
> QUERY PLAN
>
> --------------------------------------------------------------------------------
> Data Node Scan on "REMOTE_FQS_QUERY" (cost=0.00..0.00 rows=0 width=0)
> Output: t1.c1, t1.c2, t1.c3
> Node/s: datanode1
> Remote query: SELECT c1, c2, c3 FROM benchmarksql.t1 WHERE (((c2 = 1) AND
> (c3 = 1)) AND (c1 = 1))
> (4 rows)
>
>
> If you know how this problem might be fixed, list the solution below:
> ---------------------------------------------------------------------
> I made a fix as the append file, which can resolve this issue, but I am
> not sure if it is correct.
> When I run MAKE CHECK, 3 test cases failed. The results are appended too.
> But when I run MAKE CHECK without the fix, there are also some test cases
> failed.
> I do not know if the failed test cases are related to the fix.
>
> Thanks
> Julian
>
>
> ------------------------------------------------------------------------------
> 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
>
>


-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: [Postgres-xc-bugs] A bug and related fix: Dead lock when testing TPCC on PGXC by HammerDB

[Michael P. <mic...@gm...>]

On Fri, Jan 10, 2014 at 10:24 AM, ZhangJulian
<jul...@ou...> wrote:
> I am very glad to know the fix could pass your review. I will continue
> focusing and working on the amazing product.
>
> It is the first time I wrote the mail to the mail group, next time, I will
> append the regression test result and the patch as the attachments.  :)
Yeah actually I personally think that you have provided enough for
this bug and I am amazed to see that many details for your first bug
report on this mailing list. Having a patch (though not directly
attached to the mail), an analysis AND test case is something that you
barely see when bugs are reported. So don't feel discouraged and keep
up with the nice efforts!

Regards,
-- 
Michael