Re: [HACKERS] Issue with NULLS LAST, with postgres_fdw sort pushdown
On Thu, Mar 3, 2016 at 12:08 AM, Tom Lane wrote: > Ashutosh Bapat writes: >> On Thu, Mar 3, 2016 at 7:27 AM, Michael Paquier >> wrote: >>> Per explain.c, this looks inconsistent to me. Shouldn't NULLS LAST be >>> applied only if DESC is used in this ORDER BY clause? > >> ... In this case we are constructing a query to be >> sent to the foreign server and it's better not to leave the defaults to be >> interpreted by the foreign server; in case it interprets them in different >> fashion. get_rule_orderby() also explicitly adds these options. > > Yeah, I agree that we don't need to go out of our way to make the query > succinct here. Explicitness is easier and safer too, so why not? +1. So, committed Ashutosh's version. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Issue with NULLS LAST, with postgres_fdw sort pushdown
Ashutosh Bapat writes: > On Thu, Mar 3, 2016 at 7:27 AM, Michael Paquier > wrote: >> Per explain.c, this looks inconsistent to me. Shouldn't NULLS LAST be >> applied only if DESC is used in this ORDER BY clause? > ... In this case we are constructing a query to be > sent to the foreign server and it's better not to leave the defaults to be > interpreted by the foreign server; in case it interprets them in different > fashion. get_rule_orderby() also explicitly adds these options. Yeah, I agree that we don't need to go out of our way to make the query succinct here. Explicitness is easier and safer too, so why not? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Issue with NULLS LAST, with postgres_fdw sort pushdown
On Thu, Mar 3, 2016 at 7:27 AM, Michael Paquier wrote: > On Wed, Mar 2, 2016 at 7:04 PM, Rajkumar Raghuwanshi > wrote: > > On Wed, Mar 2, 2016 at 2:35 PM, Ashutosh Bapat > > wrote: > >> > >> Thanks Rajkumar for your report. Let me know if the attached patch fixes > >> the issue. > > if (pathkey->pk_nulls_first) > appendStringInfoString(buf, " NULLS FIRST"); > +else > +appendStringInfoString(buf, " NULLS LAST"); > Per explain.c, this looks inconsistent to me. Shouldn't NULLS LAST be > applied only if DESC is used in this ORDER BY clause? > I assume that you are referring to show_sortorder_options(). As per PG documentation http://www.postgresql.org/docs/9.4/static/queries-order.html, "By default, null values sort as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order, and NULLS LAST otherwise." What show_sortorder_options() is doing is just trying to avoid printing the defaults, which is arguably fine for an explain output; it leaves defaults to be interpreted by user. In this case we are constructing a query to be sent to the foreign server and it's better not to leave the defaults to be interpreted by the foreign server; in case it interprets them in different fashion. get_rule_orderby() also explicitly adds these options. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
Re: [HACKERS] Issue with NULLS LAST, with postgres_fdw sort pushdown
On Wed, Mar 2, 2016 at 7:04 PM, Rajkumar Raghuwanshi wrote: > On Wed, Mar 2, 2016 at 2:35 PM, Ashutosh Bapat > wrote: >> >> Thanks Rajkumar for your report. Let me know if the attached patch fixes >> the issue. if (pathkey->pk_nulls_first) appendStringInfoString(buf, " NULLS FIRST"); +else +appendStringInfoString(buf, " NULLS LAST"); Per explain.c, this looks inconsistent to me. Shouldn't NULLS LAST be applied only if DESC is used in this ORDER BY clause? -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Issue with NULLS LAST, with postgres_fdw sort pushdown
Thanks Ashutosh. Retested the issue after applying given patch,It is fine now. Thanks & Regards, Rajkumar Raghuwanshi QMG, EnterpriseDB Corporation On Wed, Mar 2, 2016 at 2:35 PM, Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > Thanks Rajkumar for your report. Let me know if the attached patch fixes > the issue. > > The code did not add NULL LAST clause the case when pk_nulls_first is > false in pathkey. PFA the fix for the same. I have also added few tests to > postgres_fdw.sql for few combinations of asc/desc and nulls first/last. > > On Mon, Feb 29, 2016 at 3:49 PM, Rajkumar Raghuwanshi < > rajkumar.raghuwan...@enterprisedb.com> wrote: > >> Hi, >> >> I am testing postgres_fdw sort pushdown feature for PostgreSQL 9.6 DB, >> and I observed below issue. >> >> *Observation: *If giving nulls last option with the order by clause as >> 'desc nulls last', remote query is not considering nulls last and giving >> wrong result in 9.6 version. while in 9.5 it is giving proper result. >> >> for testing, I have a table "fdw_sort_test" in foreign server for which >> postgres_fdw, foreign table created in local server. >> >> db2=# select * from fdw_sort_test ; >> id | name >> +-- >> 1 | xyz >> 3 | >> 2 | abc >> 4 | pqr >> (4 rows) >> >>on version 9.6 : >> >> db1=# select * from fdw_sort_test order by name >> desc nulls last; >> id | name >> +-- >>3 | >>1 | xyz >>4 | pqr >>2 | abc >> (4 rows) >> >> db1=# explain verbose select * from fdw_sort_test >> order by name desc nulls last; >> QUERY >> PLAN >> -- >> -- >> Foreign Scan on public.fdw_sort_test >> (cost=100.00..129.95 rows=561 width=122) >> Output: id, name >> Remote SQL: SELECT id, name FROM >> public.fdw_sort_test ORDER BY name DESC >> (3 rows) >> >> >> on version 9.5 : >> db1=# select * from fdw_sort_test order by name >> desc nulls last; >>id | name >> +-- >> 1 | xyz >> 4 | pqr >> 2 | abc >> 3 | >> (4 rows) >> >> db1=# explain verbose select * from fdw_sort_test >> order by name desc nulls last; >> QUERY >> PLAN >> -- >> >> Sort (cost=152.44..153.85 rows=561 width=122) >> Output: id, name >> Sort Key: fdw_sort_test.name DESC NULLS LAST >> -> Foreign Scan on public.fdw_sort_test >> (cost=100.00..126.83 rows=561 width=122) >> Output: id, name >> Remote SQL: SELECT id, name FROM >> public.fdw_sort_test >> >> *steps to reproduce : * >> >> --connect to sql >> \c postgres postgres >> --create role and database db1, will act as local server >> create role db1 password 'db1' superuser login; >> create database db1 owner=db1; >> grant all on database db1 to db1; >> >> --create role and database db2, will act as foreign server >> create role db2 password 'db2' superuser login; >> create database db2 owner=db2; >> grant all on database db2 to db2; >> >> --connect to db2 and create a table >> \c db2 db2 >> create table fdw_sort_test (id integer, name varchar(50)); >> insert into fdw_sort_test values (1,'xyz'); >> insert into fdw_sort_test values (3,null); >> insert into fdw_sort_test values (2,'abc'); >> insert into fdw_sort_test values (4,'pqr'); >> >> --connect to db1 and create postgres_fdw >> \c db1 db1 >> create extension postgres_fdw; >> create server db2_link_server foreign data wrapper postgres_fdw options >> (host 'db2_machine_ip', dbname 'db2', port 'db_machine_port_no'); >> create user mapping for db1 server db2_link_server options (user 'db2', >> password 'db2'); >> >> --create a foreign table >> create foreign table fdw_sort_test (id integer, name varchar(50)) server >> db2_link_server; >> >> --run the below query and c
Re: [HACKERS] Issue with NULLS LAST, with postgres_fdw sort pushdown
Thanks Rajkumar for your report. Let me know if the attached patch fixes the issue. The code did not add NULL LAST clause the case when pk_nulls_first is false in pathkey. PFA the fix for the same. I have also added few tests to postgres_fdw.sql for few combinations of asc/desc and nulls first/last. On Mon, Feb 29, 2016 at 3:49 PM, Rajkumar Raghuwanshi < rajkumar.raghuwan...@enterprisedb.com> wrote: > Hi, > > I am testing postgres_fdw sort pushdown feature for PostgreSQL 9.6 DB, and > I observed below issue. > > *Observation: *If giving nulls last option with the order by clause as > 'desc nulls last', remote query is not considering nulls last and giving > wrong result in 9.6 version. while in 9.5 it is giving proper result. > > for testing, I have a table "fdw_sort_test" in foreign server for which > postgres_fdw, foreign table created in local server. > > db2=# select * from fdw_sort_test ; > id | name > +-- > 1 | xyz > 3 | > 2 | abc > 4 | pqr > (4 rows) > >on version 9.6 : > > db1=# select * from fdw_sort_test order by name desc > nulls last; > id | name > +-- >3 | >1 | xyz >4 | pqr >2 | abc > (4 rows) > > db1=# explain verbose select * from fdw_sort_test > order by name desc nulls last; > QUERY > PLAN > -- > -- > Foreign Scan on public.fdw_sort_test > (cost=100.00..129.95 rows=561 width=122) > Output: id, name > Remote SQL: SELECT id, name FROM > public.fdw_sort_test ORDER BY name DESC > (3 rows) > > > on version 9.5 : > db1=# select * from fdw_sort_test order by name desc > nulls last; >id | name > +-- > 1 | xyz > 4 | pqr > 2 | abc > 3 | > (4 rows) > > db1=# explain verbose select * from fdw_sort_test > order by name desc nulls last; > QUERY > PLAN > -- > > Sort (cost=152.44..153.85 rows=561 width=122) > Output: id, name > Sort Key: fdw_sort_test.name DESC NULLS LAST > -> Foreign Scan on public.fdw_sort_test > (cost=100.00..126.83 rows=561 width=122) > Output: id, name > Remote SQL: SELECT id, name FROM > public.fdw_sort_test > > *steps to reproduce : * > > --connect to sql > \c postgres postgres > --create role and database db1, will act as local server > create role db1 password 'db1' superuser login; > create database db1 owner=db1; > grant all on database db1 to db1; > > --create role and database db2, will act as foreign server > create role db2 password 'db2' superuser login; > create database db2 owner=db2; > grant all on database db2 to db2; > > --connect to db2 and create a table > \c db2 db2 > create table fdw_sort_test (id integer, name varchar(50)); > insert into fdw_sort_test values (1,'xyz'); > insert into fdw_sort_test values (3,null); > insert into fdw_sort_test values (2,'abc'); > insert into fdw_sort_test values (4,'pqr'); > > --connect to db1 and create postgres_fdw > \c db1 db1 > create extension postgres_fdw; > create server db2_link_server foreign data wrapper postgres_fdw options > (host 'db2_machine_ip', dbname 'db2', port 'db_machine_port_no'); > create user mapping for db1 server db2_link_server options (user 'db2', > password 'db2'); > > --create a foreign table > create foreign table fdw_sort_test (id integer, name varchar(50)) server > db2_link_server; > > --run the below query and checkout the output > select * from fdw_sort_test order by name desc nulls last; > > --check the explain plan > explain plan select * from fdw_sort_test order by name desc nulls last; > > Thanks & Regards, > Rajkumar Raghuwanshi > QMG, EnterpriseDB Corporation > -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company pg_nulls_la
[HACKERS] Issue with NULLS LAST, with postgres_fdw sort pushdown
Hi, I am testing postgres_fdw sort pushdown feature for PostgreSQL 9.6 DB, and I observed below issue. *Observation: *If giving nulls last option with the order by clause as 'desc nulls last', remote query is not considering nulls last and giving wrong result in 9.6 version. while in 9.5 it is giving proper result. for testing, I have a table "fdw_sort_test" in foreign server for which postgres_fdw, foreign table created in local server. db2=# select * from fdw_sort_test ; id | name +-- 1 | xyz 3 | 2 | abc 4 | pqr (4 rows) on version 9.6 : db1=# select * from fdw_sort_test order by name desc nulls last; id | name +-- 3 | 1 | xyz 4 | pqr 2 | abc (4 rows) db1=# explain verbose select * from fdw_sort_test order by name desc nulls last; QUERY PLAN -- -- Foreign Scan on public.fdw_sort_test (cost=100.00..129.95 rows=561 width=122) Output: id, name Remote SQL: SELECT id, name FROM public.fdw_sort_test ORDER BY name DESC (3 rows) on version 9.5 : db1=# select * from fdw_sort_test order by name desc nulls last; id | name +-- 1 | xyz 4 | pqr 2 | abc 3 | (4 rows) db1=# explain verbose select * from fdw_sort_test order by name desc nulls last; QUERY PLAN -- Sort (cost=152.44..153.85 rows=561 width=122) Output: id, name Sort Key: fdw_sort_test.name DESC NULLS LAST -> Foreign Scan on public.fdw_sort_test (cost=100.00..126.83 rows=561 width=122) Output: id, name Remote SQL: SELECT id, name FROM public.fdw_sort_test *steps to reproduce : * --connect to sql \c postgres postgres --create role and database db1, will act as local server create role db1 password 'db1' superuser login; create database db1 owner=db1; grant all on database db1 to db1; --create role and database db2, will act as foreign server create role db2 password 'db2' superuser login; create database db2 owner=db2; grant all on database db2 to db2; --connect to db2 and create a table \c db2 db2 create table fdw_sort_test (id integer, name varchar(50)); insert into fdw_sort_test values (1,'xyz'); insert into fdw_sort_test values (3,null); insert into fdw_sort_test values (2,'abc'); insert into fdw_sort_test values (4,'pqr'); --connect to db1 and create postgres_fdw \c db1 db1 create extension postgres_fdw; create server db2_link_server foreign data wrapper postgres_fdw options (host 'db2_machine_ip', dbname 'db2', port 'db_machine_port_no'); create user mapping for db1 server db2_link_server options (user 'db2', password 'db2'); --create a foreign table create foreign table fdw_sort_test (id integer, name varchar(50)) server db2_link_server; --run the below query and checkout the output select * from fdw_sort_test order by name desc nulls last; --check the explain plan explain plan select * from fdw_sort_test order by name desc nulls last; Thanks & Regards, Rajkumar Raghuwanshi QMG, EnterpriseDB Corporation
