[HACKERS] Current syslogger filename
Hello, While going through the TODO items on the wiki I have come across the following proposal: Provide a way to query the log collector subprocess to determine the name of the currently active log file Current log files when rotating? http://archives.postgresql.org/pgsql-general/2008-11/msg00418.php Upon reading the related conversations, there doesn't seem to be a consensus on how to implement this feature. If it is still relevant, would you suggest a way to pass filename or time(which filename is generated from) of the file from syslogger to inquiring postgres instance. Regards, Kerem -- 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] (PATCH) Adding CORRESPONDING to Set Operations
This explain plan doesn't look right to me: test=# explain select a,b,c from one intersect corresponding by (a,c) select a,b,c from two; QUERY PLAN - HashSetOp Intersect (cost=0.00..117.00 rows=200 width=8) - Append (cost=0.00..97.60 rows=3880 width=8) - Subquery Scan on *SELECT* 3 (cost=0.00..48.80 rows=1940 width=8) - Seq Scan on one (cost=0.00..29.40 rows=1940 width=8) - Subquery Scan on *SELECT* 4 (cost=0.00..48.80 rows=1940 width=8) - Seq Scan on two (cost=0.00..29.40 rows=1940 width=8) (6 rows) In the current implementation, select a,b,c from one intersect corresponding by (a,c) select a,b,c from two; is translated to equivalent select a, c from (select a,b,c from one) intersect select a, c from (select a,b,c from two); Methinks that's the reason for this explain output. Corresponding is currently implemented in the parse/analyze phase. If it were to be implemented in the planning phase, explain output would likely be as you expect it to be. If I do the same thing without the corresponding...: test=# explain select a,b,c from one intersect select a,b,c from two; QUERY PLAN -- HashSetOp Intersect (cost=0.00..126.70 rows=200 width=12) - Append (cost=0.00..97.60 rows=3880 width=12) - Subquery Scan on *SELECT* 1 (cost=0.00..48.80 rows=1940 width=12) - Seq Scan on one (cost=0.00..29.40 rows=1940 width=12) - Subquery Scan on *SELECT* 2 (cost=0.00..48.80 rows=1940 width=12) - Seq Scan on two (cost=0.00..29.40 rows=1940 width=12) (6 rows) So it looks like it's now seeing the two tables as the 3rd and 4th tables, even though there are only 2 tables in total. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company Regards, Kerem KAT -- 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] (PATCH) Adding CORRESPONDING to Set Operations
On Mon, Nov 14, 2011 at 15:32, Tom Lane t...@sss.pgh.pa.us wrote: Kerem Kat kerem...@gmail.com writes: Corresponding is currently implemented in the parse/analyze phase. If it were to be implemented in the planning phase, explain output would likely be as you expect it to be. It's already been pointed out to you that doing this at parse time is unacceptable, because of the implications for reverse-listing of rules (views). regards, tom lane I am well aware of that thank you. Regards, Kerem KAT -- 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] (PATCH) Adding CORRESPONDING (NULL error)
Hi, Union with NULL error persists without the corresponding patch. Here is the output from postgres without the patch: SELECT a FROM (SELECT 1 a) foo UNION SELECT a FROM (SELECT NULL a) foo2; ERROR: failed to find conversion function from unknown to integer It is thrown from parse_coerce.c:coerce_type method. I will try to dig deep on it. Regards, Kerem KAT On Thu, Oct 27, 2011 at 15:45, Erik Rijkers e...@xs4all.nl wrote: (pgsql 9.2devel (25 oct) with your latest CORRESPONDING patch; linux x86_64 GNU/Linux 2.6.18-274.3.1.el5) Hi, here is another peculiarity, which I think is a bug: -- first without CORRESPONDING: $ psql -Xaf null.sql select 1 a , 2 b union all select null a, 4 b ; a | b ---+--- 1 | 2 | 4 (2 rows) -- then with CORRESPONDING: select 1 a , 2 b union all corresponding select null a, 4 b ; psql:null.sql:9: ERROR: failed to find conversion function from unknown to integer If the null value is in a table column the error does not occur: drop table if exists t1; create table t1 (a int, b int); insert into t1 values (1,2); drop table if exists t2; create table t2 (a int, b int); insert into t2 values (null,2); select a,b from t1 union all corresponding select a,b from t2 ; a | b ---+--- 1 | 2 | 2 (2 rows) I'm not sure it is actually a bug; but it seems an unneccessary error. thanks, Erik Rijkers -- 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] (PATCH) Adding CORRESPONDING (NULL error)
On Thu, Oct 27, 2011 at 23:20, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: Kerem Kat kerem...@gmail.com writes: Union with NULL error persists without the corresponding patch. Here is the output from postgres without the patch: SELECT a FROM (SELECT 1 a) foo UNION SELECT a FROM (SELECT NULL a) foo2; ERROR: failed to find conversion function from unknown to integer Yeah, this is a longstanding issue that is not simple to fix without introducing other unpleasantnesses. It is not something you should try to deal with at the same time as implementing CORRESPONDING. BTW, just to clarify: although that case fails, the case Erik was complaining of does work in unmodified Postgres: regression=# select 1 a , 2 b union all select null a, 4 b ; a | b ---+--- 1 | 2 | 4 (2 rows) and I agree with him that it should still work with CORRESPONDING. Even though the behavior of unlabeled NULLs is less than perfect, we definitely don't want to break cases that work now. I suspect the failure means that you tried to postpone too much work to plan time. You do have to match up the columns honestly at parse time and do the necessary type coercions on them then. regards, tom lane That is by design, because CORRESPONDING is implemented as subqueries: select 1 a , 2 b union all corresponding select null a, 4 b ; is equivalent to SELECT a, b FROM ( SELECT 1 a, 2 b ) foo UNION ALL SELECT a, b FROM ( SELECT null a, 4 b ) foo2; which gives the same error in unpatched postgres. Regards, Kerem KAT -- 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] (PATCH) Adding CORRESPONDING to Set Operations
On Mon, Oct 24, 2011 at 20:52, Erik Rijkers e...@xs4all.nl wrote:
On Wed, October 19, 2011 15:01, Kerem Kat wrote:
Adding CORRESPONDING to Set Operations
Initial patch, filename: corresponding_clause_v2.patch
I had a quick look at the behaviour of this patch.
Btw, the examples in your email were typoed (one select is missing):
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING 4 b, 5 d, 6 c, 7 f;
should be:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING select 4 b, 5 d, 6 c, 7 f;
and
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) 4 b, 5 d, 6 c, 7 f;
should be:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) select 4 b, 5 d, 6 c, 7 f;
Yes you are correct, mea culpa.
But there is also a small bug, I think: the order in the CORRESPONDING BY
list should be followed,
according to the standard (foundation, p. 408):
2) If corresponding column list is specified, then let SL be a select
list of those column
names explicitly appearing in the corresponding column list in the order
that these
column names appear in the corresponding column list. Every column name
in the
corresponding column list shall be a column name of both T1 and T2.
That would make this wrong, I think:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c,b) select 5 d, 6 c, 7 f, 4 b ;
b | c
---+---
2 | 3
4 | 6
(2 rows)
i.e., I think it should show columns in the order c, b (and not b, c); the
order of the
CORRESPONDING BY phrase.
(but maybe I'm misreading the text of the standard; I find it often difficult
to follow)
It wasn't a misread, I checked the draft, in my version same
explanation is at p.410.
I have corrected the ordering of the targetlists of subqueries. And
added 12 regression
tests for column list ordering. Can you confirm that the order has
changed for you?
Thanks,
Erik Rijkers
Regards,
Kerem KAT
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***
*** 1225,1230
--- 1225,1233
primaryEXCEPT/primary
/indexterm
indexterm zone=queries-union
+primaryCORRESPONDING/primary
+ /indexterm
+ indexterm zone=queries-union
primaryset union/primary
/indexterm
indexterm zone=queries-union
***
*** 1241,1249
The results of two queries can be combined using the set operations
union, intersection, and difference. The syntax is
synopsis
! replaceablequery1/replaceable UNION optionalALL/optional replaceablequery2/replaceable
! replaceablequery1/replaceable INTERSECT optionalALL/optional replaceablequery2/replaceable
! replaceablequery1/replaceable EXCEPT optionalALL/optional replaceablequery2/replaceable
/synopsis
replaceablequery1/replaceable and
replaceablequery2/replaceable are queries that can use any of
--- 1244,1252
The results of two queries can be combined using the set operations
union, intersection, and difference. The syntax is
synopsis
! replaceablequery1/replaceable UNION optionalALL/optional optionalCORRESPONDING optionalBY (replaceableselect_list/replaceable)/optional/optional replaceablequery2/replaceable
! replaceablequery1/replaceable INTERSECT optionalALL/optional optionalCORRESPONDING optionalBY (replaceableselect_list/replaceable)/optional/optional replaceablequery2/replaceable
! replaceablequery1/replaceable EXCEPT optionalALL/optional optionalCORRESPONDING optionalBY (replaceableselect_list/replaceable)/optional/optional replaceablequery2/replaceable
/synopsis
replaceablequery1/replaceable and
replaceablequery2/replaceable are queries that can use any of
***
*** 1283,1288
--- 1286,1299
/para
para
+ literalCORRESPONDING/ returns all columns that are in both replaceablequery1/ and replaceablequery2/ with the same name.
+ /para
+
+ para
+ literalCORRESPONDING BY/ returns all columns in the column list that are also in both replaceablequery1/ and replaceablequery2/ with the same name.
+ /para
+
+ para
In order to calculate the union, intersection, or difference of two
queries, the two queries must be quoteunion compatible/quote,
which means that they return the same number of columns and
*** a/doc/src/sgml/sql.sgml
--- b/doc/src/sgml/sql.sgml
***
*** 859,865
[ WHERE replaceable class=PARAMETERcondition/replaceable ]
[ GROUP BY replaceable class=PARAMETERexpression/replaceable [, ...] ]
[ HAVING replaceable class=PARAMETERcondition/replaceable [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] replaceable class=PARAMETERselect/replaceable ]
[ ORDER BY replaceable class=parameterexpression/replaceable [ ASC | DESC | USING replaceable class=parameteroperator/replaceable ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { replaceable class=PARAMETERcount/replaceable | ALL } ]
[ OFFSET replaceable class=PARAMETERstart/replaceable ]
--- 859,865
[ WHERE replaceable class=PARAMETERcondition/replaceable ]
[ GROUP
[HACKERS] (PATCH) Adding CORRESPONDING to Set Operations
Adding CORRESPONDING to Set Operations
Initial patch, filename: corresponding_clause_v2.patch
This patch adds CORRESPONDING clause to set operations according to
SQL20nn standard draft as Feature F301, CORRESPONDING in query
expressions
Corresponding clause either contains a BY(...) clause or not. If it
doesn't have a BY(...) clause the usage is as follows.
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING 4 b, 5 d, 6 c, 7 f;
with output:
b c
-
2 3
4 6
i.e. matching column names are filtered and are only output from the
whole set operation clause.
If we introduce a BY(...) clause, then column names are further
intersected with that BY clause:
SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) 4 b, 5 d, 6 c, 7 f;
with output:
b
--
2
4
This patch compiles and tests successfully with master branch.
It has been tested only on Pardus Linux i686 ( Kernel 2.6.37.6 #1 SMP
i686 i686 i386 GNU/Linux)
This patch includes documentation and add one regression file.
This patch addresses the following TODO item:
SQL Commands: Add CORRESPONDING BY to UNION/INTERSECT/EXCEPT
Best Regards,
Kerem KAT
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***
*** 1225,1230
--- 1225,1233
primaryEXCEPT/primary
/indexterm
indexterm zone=queries-union
+primaryCORRESPONDING/primary
+ /indexterm
+ indexterm zone=queries-union
primaryset union/primary
/indexterm
indexterm zone=queries-union
***
*** 1241,1249
The results of two queries can be combined using the set operations
union, intersection, and difference. The syntax is
synopsis
! replaceablequery1/replaceable UNION optionalALL/optional replaceablequery2/replaceable
! replaceablequery1/replaceable INTERSECT optionalALL/optional replaceablequery2/replaceable
! replaceablequery1/replaceable EXCEPT optionalALL/optional replaceablequery2/replaceable
/synopsis
replaceablequery1/replaceable and
replaceablequery2/replaceable are queries that can use any of
--- 1244,1252
The results of two queries can be combined using the set operations
union, intersection, and difference. The syntax is
synopsis
! replaceablequery1/replaceable UNION optionalALL/optional optionalCORRESPONDING optionalBY (replaceableselect_list/replaceable)/optional/optional replaceablequery2/replaceable
! replaceablequery1/replaceable INTERSECT optionalALL/optional optionalCORRESPONDING optionalBY (replaceableselect_list/replaceable)/optional/optional replaceablequery2/replaceable
! replaceablequery1/replaceable EXCEPT optionalALL/optional optionalCORRESPONDING optionalBY (replaceableselect_list/replaceable)/optional/optional replaceablequery2/replaceable
/synopsis
replaceablequery1/replaceable and
replaceablequery2/replaceable are queries that can use any of
***
*** 1283,1288
--- 1286,1299
/para
para
+ literalCORRESPONDING/ returns all columns that are in both replaceablequery1/ and replaceablequery2/ with the same name.
+ /para
+
+ para
+ literalCORRESPONDING BY/ returns all columns in the column list that are also in both replaceablequery1/ and replaceablequery2/ with the same name.
+ /para
+
+ para
In order to calculate the union, intersection, or difference of two
queries, the two queries must be quoteunion compatible/quote,
which means that they return the same number of columns and
*** a/doc/src/sgml/sql.sgml
--- b/doc/src/sgml/sql.sgml
***
*** 859,865
[ WHERE replaceable class=PARAMETERcondition/replaceable ]
[ GROUP BY replaceable class=PARAMETERexpression/replaceable [, ...] ]
[ HAVING replaceable class=PARAMETERcondition/replaceable [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] replaceable class=PARAMETERselect/replaceable ]
[ ORDER BY replaceable class=parameterexpression/replaceable [ ASC | DESC | USING replaceable class=parameteroperator/replaceable ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { replaceable class=PARAMETERcount/replaceable | ALL } ]
[ OFFSET replaceable class=PARAMETERstart/replaceable ]
--- 859,865
[ WHERE replaceable class=PARAMETERcondition/replaceable ]
[ GROUP BY replaceable class=PARAMETERexpression/replaceable [, ...] ]
[ HAVING replaceable class=PARAMETERcondition/replaceable [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] [ CORRESPONDING [ BY ( replaceable class=PARAMETERexpression/replaceable ) ] ] replaceable class=PARAMETERselect/replaceable ]
[ ORDER BY replaceable class=parameterexpression/replaceable [ ASC | DESC | USING replaceable class=parameteroperator/replaceable ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { replaceable class=PARAMETERcount/replaceable | ALL } ]
[ OFFSET replaceable class=PARAMETERstart/replaceable ]
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***
*** 2507,2512
--- 2507,2513
Re: [HACKERS] Adding CORRESPONDING to Set Operations
CORRESPONDING clause take 2
After realizing that modifying prepunion.c to include a custom subquery
is not easy(incomprehensible to me) as it sounds and turning into a
hassle after making several uninformed changes, I decided to go with
modifying analyze.c.
The incomprehensible part is constructing a custom subquery as a
SubqueryScan.
Anyway I managed to implement the clause as a Subquery in analyze.c.
In the method transformSetOperationTree, if the node is a setoperation and
contains a corresponding clause, i.e. CORRESPONDING, or CORRESPONDING
BY(columns...),
we determine the common column names. Column ordering in select statements
are not important to the CORRESPONDING. With the common column names
in hand, we create a RangeSubselect node accordingly and replace the original
statement op-larg with the new RangeSubselect. RangeSubselect in turn has the
original op-larg as a from clause. We do the same to op-rarg too.
There were no changes done in prepunion.c
There are documentation changes and one regression test in the patch.
Best Regards,
Kerem KAT
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
***
*** 1225,1230
--- 1225,1233
primaryEXCEPT/primary
/indexterm
indexterm zone=queries-union
+primaryCORRESPONDING/primary
+ /indexterm
+ indexterm zone=queries-union
primaryset union/primary
/indexterm
indexterm zone=queries-union
***
*** 1241,1249
The results of two queries can be combined using the set operations
union, intersection, and difference. The syntax is
synopsis
! replaceablequery1/replaceable UNION optionalALL/optional replaceablequery2/replaceable
! replaceablequery1/replaceable INTERSECT optionalALL/optional replaceablequery2/replaceable
! replaceablequery1/replaceable EXCEPT optionalALL/optional replaceablequery2/replaceable
/synopsis
replaceablequery1/replaceable and
replaceablequery2/replaceable are queries that can use any of
--- 1244,1252
The results of two queries can be combined using the set operations
union, intersection, and difference. The syntax is
synopsis
! replaceablequery1/replaceable UNION optionalALL/optional optionalCORRESPONDING optionalBY (replaceableselect_list/replaceable)/optional/optional replaceablequery2/replaceable
! replaceablequery1/replaceable INTERSECT optionalALL/optional optionalCORRESPONDING optionalBY (replaceableselect_list/replaceable)/optional/optional replaceablequery2/replaceable
! replaceablequery1/replaceable EXCEPT optionalALL/optional optionalCORRESPONDING optionalBY (replaceableselect_list/replaceable)/optional/optional replaceablequery2/replaceable
/synopsis
replaceablequery1/replaceable and
replaceablequery2/replaceable are queries that can use any of
***
*** 1283,1288
--- 1286,1299
/para
para
+ literalCORRESPONDING/ returns all columns that are in both replaceablequery1/ and replaceablequery2/ with the same name.
+ /para
+
+ para
+ literalCORRESPONDING BY/ returns all columns in the column list that are also in both replaceablequery1/ and replaceablequery2/ with the same name.
+ /para
+
+ para
In order to calculate the union, intersection, or difference of two
queries, the two queries must be quoteunion compatible/quote,
which means that they return the same number of columns and
*** a/doc/src/sgml/sql.sgml
--- b/doc/src/sgml/sql.sgml
***
*** 859,865
[ WHERE replaceable class=PARAMETERcondition/replaceable ]
[ GROUP BY replaceable class=PARAMETERexpression/replaceable [, ...] ]
[ HAVING replaceable class=PARAMETERcondition/replaceable [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] replaceable class=PARAMETERselect/replaceable ]
[ ORDER BY replaceable class=parameterexpression/replaceable [ ASC | DESC | USING replaceable class=parameteroperator/replaceable ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { replaceable class=PARAMETERcount/replaceable | ALL } ]
[ OFFSET replaceable class=PARAMETERstart/replaceable ]
--- 859,865
[ WHERE replaceable class=PARAMETERcondition/replaceable ]
[ GROUP BY replaceable class=PARAMETERexpression/replaceable [, ...] ]
[ HAVING replaceable class=PARAMETERcondition/replaceable [, ...] ]
! [ { UNION | INTERSECT | EXCEPT } [ ALL ] [ CORRESPONDING [ BY ( replaceable class=PARAMETERexpression/replaceable ) ] ] replaceable class=PARAMETERselect/replaceable ]
[ ORDER BY replaceable class=parameterexpression/replaceable [ ASC | DESC | USING replaceable class=parameteroperator/replaceable ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { replaceable class=PARAMETERcount/replaceable | ALL } ]
[ OFFSET replaceable class=PARAMETERstart/replaceable ]
*** a/src/backend/nodes/copyfuncs.c
--- b/src/backend/nodes/copyfuncs.c
***
*** 2507,2512
--- 2507,2513
COPY_NODE_FIELD(lockingClause
Re: [HACKERS] Postgresql parser
On Tue, Sep 27, 2011 at 11:44, andurkar andurkarad10.c...@coep.ac.in wrote: Hello, Currently I am working on Postgresql... I need to study the gram.y and scan.l parser files...since I want to do some qery modification. Can anyone please help me to understand the files. What should I do ? Is there any documentation available ? Regards, Aditi. What kind of modifications do you want to do? regards, Kerem KAT -- 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] Adding CORRESPONDING to Set Operations
I am looking into perpunion.c and analyze.c There is a catch inserting subqueries for corresponding in the planner. Parser expects to see equal number of columns in both sides of the UNION query. If there is corresponding however we cannot guarantee that. Target columns, collations and types for the SetOperationStmt are determined in the parser. If we pass the column number equality checks, it is not clear that how one would proceed with the targetlist generation loop which is a forboth for two table's columns. One way would be filtering the columns in the parser anyway and inserting subqueries in the planner but it leads to the previous problem of column ordering and view definition mess-up, and it would be too much bloat methinks. I can guess what needs to be done in prepunion.c, but I need a waypointer for the parser. tom lane: Thanks for your description regards Kerem KAT On Fri, Sep 23, 2011 at 07:40, Tom Lane t...@sss.pgh.pa.us wrote: Kerem Kat kerem...@gmail.com writes: While testing I noticed that ordering is incorrect in my implementation. At first I thought that removing mismatched entries from ltargetlist and rtargetlist would be enough, it didn't seem enough so I added rtargetlist sorting. I don't think you can get away with changing the targetlists of the UNION subqueries; you could break their semantics. Consider for instance select distinct a, b, c from t1 union corresponding select b, c from t2; If you discard the A column from t1's output list then it will deliver a different set of rows than it should, because the DISTINCT is considering the wrong set of values. One possible way to fix that is to introduce a level of sub-select, as if the query had been written select b, c from (select distinct a, b, c from t1) ss1 union select b, c from (select b, c from t2) ss2; However, the real problem with either type of hackery is that these machinations will be visible in the parsed query, which means for example that a view defined as create view v1 as select distinct a, b, c from t1 union corresponding select b, c from t2; would come out looking like the transformed version rather than the original when it's dumped, or even just examined with tools such as psql's \d+. I think this is bad style. It's certainly ugly to expose your implementation shortcuts to the user like that, and it also can cause problems down the road: if in the future we think of some better way to implement CORRESPONDING, we've lost the chance to do so for any stored views that got transformed this way. (There are several places in Postgres now that take such shortcuts, and all of them were mistakes that we need to clean up someday, IMO.) So I think that as far as the parser is concerned, you just want to store the CORRESPONDING clause more or less as-is, and not do too much more than verify that it's valid. The place to actually implement it is in the planner (see prepunion.c). Possibly the add-a-level-of-subselect approach will work, but you want to do that querytree transformation at plan time not parse time. regards, tom lane
Re: [HACKERS] Adding CORRESPONDING to Set Operations
On Sat, Sep 24, 2011 at 18:49, Tom Lane t...@sss.pgh.pa.us wrote: Kerem Kat kerem...@gmail.com writes: There is a catch inserting subqueries for corresponding in the planner. Parser expects to see equal number of columns in both sides of the UNION query. If there is corresponding however we cannot guarantee that. Well, you certainly need the parse analysis code to be aware of CORRESPONDING's effects. But I think you can confine the changes to adjusting the computation of a SetOperationStmt's list of output column types. It might be a good idea to also add a list of output column names to SetOperationStmt, and get rid of the logic that digs down into the child queries when we need to know the output column names. In the parser while analyzing SetOperationStmt, larg and rarg needs to be transformed as subqueries. SetOperationStmt can have two fields representing larg and rarg with projected columns according to corresponding: larg_corresponding, rarg_corresponding. Planner uses _corresponding ones if query is a corresponding query, view-definition-generator uses larg and rarg which represent the query user entered. Comments? Target columns, collations and types for the SetOperationStmt are determined in the parser. If we pass the column number equality checks, it is not clear that how one would proceed with the targetlist generation loop which is a forboth for two table's columns. Obviously, that logic doesn't work at all for CORRESPONDING, so you'll need to have a separate code path to deduce the output column list in that case. If the output column list to be determined at that stage it needs to be filtered and ordered. In that case aren't we breaking the non-modification of user query argument? note: I am new to this list, am I asking too much detail? regards, Kerem KAT -- 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] Adding CORRESPONDING to Set Operations
On Sat, Sep 24, 2011 at 19:51, Tom Lane t...@sss.pgh.pa.us wrote: Kerem Kat kerem...@gmail.com writes: In the parser while analyzing SetOperationStmt, larg and rarg needs to be transformed as subqueries. SetOperationStmt can have two fields representing larg and rarg with projected columns according to corresponding: larg_corresponding, rarg_corresponding. Why? CORRESPONDING at a given set-operation level doesn't affect either sub-query, so I don't see why you'd need a different representation for the sub-queries. In the planner to construct a subquery out of SetOperationStmt or RangeTblRef, a new RangeTblRef is needed. To create a RangeTableRef, parser state is needed and planner assumes root-parse-rtable be not modified after generating simple_rte_array. SELECT a,b,c FROM t is larg SELECT a,b FROM (SELECT a,b,c FROM t) is larg_corresponding SELECT d,a,b FROM t is rarg SELECT a,b FROM (SELECT d,a,b FROM t); is rarg_corresponding In the planner choose _corresponding ones if the query has corresponding. SELECT a,b FROM (SELECT a,b,c FROM t) UNION SELECT a,b FROM (SELECT d,a,b FROM t); Obviously, that logic doesn't work at all for CORRESPONDING, so you'll need to have a separate code path to deduce the output column list in that case. If the output column list to be determined at that stage it needs to be filtered and ordered. In that case aren't we breaking the non-modification of user query argument? No. All that you're doing is correctly computing the lists of the set-operation's output column types (and probably names too). These are internal details that needn't be examined when printing the query, so they won't affect ruleutils.c. note: I am new to this list, am I asking too much detail? Well, I am beginning to wonder if you should choose a smaller project for your first venture into patching Postgres. regards, Kerem KAT -- 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] Adding CORRESPONDING to Set Operations
I delved into the code without waiting for comments from the list just to learn something about postgresql internals. And I have finished the CORRESPONDING, now CORRESPONDING BY is being tested. I will also write documentation and regression tests. Yes Robert, you are correct. Having used SQL 20nn standard draft as a guide, a brief explanation can be provided as such: Shorter version: column name lists are intersected. Short version: In the set operation queries, which are queries containing INTERSECT, EXCEPT or UNION, a CORRESPONDING clause can be used to project the resulting columns to only columns contained in both sides of the query. There is also and addition of BY(col1, col2, ...) to the clause which projects the columns to its own list. An example query would clarifiy. SELECT 1 a, 2 b UNION CORRESPONDING SELECT 3 a; a -- 1 3 SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, c) SELECT 4 a, 5 c a c -- 1 3 4 5 On Thu, Sep 22, 2011 at 16:20, Robert Haas robertmh...@gmail.com wrote: On Sun, Sep 18, 2011 at 5:39 AM, Kerem Kat kerem...@gmail.com wrote: I am new to postgresql code, I would like to start implementing easyish TODO items. I have read most of the development guidelines, faqs, articles by Greg Smith (Hacking Postgres with UDFs, Adding WHEN to triggers). The item I would like to implement is adding CORRESPONDING [BY (col1[,col2,...]])] to INTERSECT and EXCEPT operators. Can anyone comment on how much effort this item needs? This seems reasonably tricky for a first project, but maybe not out of reach if you are a skilled C hacker. It's certainly more complicated than my first patch: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a0b76dc662efde6e02921c2d16e06418483b7534 I guess the first question that needs to be answered here is ... what exactly is this syntax supposed to do? A little looking around suggests that EXCEPT CORRESPONDING is supposed to make the correspondence run by column names rather than by column positions, and if you further add BY col1, ... then it restricts the comparison to those columns. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] Adding CORRESPONDING to Set Operations
While testing I noticed that ordering is incorrect in my implementation. At first I thought that removing mismatched entries from ltargetlist and rtargetlist would be enough, it didn't seem enough so I added rtargetlist sorting. SELECT 1 a, 2 b, 3 c UNION CORRESPONDING 4 b, 5 a, 6 c; returns incorrectly: a b c 1 2 3 4 5 6 Correct: a b c 1 2 3 5 4 6 In the analyze.c:transfromSetOperationStmt, I tried to sort rtargetlist before the forboth(ltl, ltargetlist, rtl,rtargetlist) to no avail. Sorted column names are in correct order in rtargetlist, but query is executed as if rtargetlist is never sorted. Where the targetlist gets the column ordering? Apparently not while targetlist is being lappend'ed (?). regards, Kerem KAT On Thu, Sep 22, 2011 at 17:03, Kerem Kat kerem...@gmail.com wrote: I delved into the code without waiting for comments from the list just to learn something about postgresql internals. And I have finished the CORRESPONDING, now CORRESPONDING BY is being tested. I will also write documentation and regression tests. Yes Robert, you are correct. Having used SQL 20nn standard draft as a guide, a brief explanation can be provided as such: Shorter version: column name lists are intersected. Short version: In the set operation queries, which are queries containing INTERSECT, EXCEPT or UNION, a CORRESPONDING clause can be used to project the resulting columns to only columns contained in both sides of the query. There is also and addition of BY(col1, col2, ...) to the clause which projects the columns to its own list. An example query would clarifiy. SELECT 1 a, 2 b UNION CORRESPONDING SELECT 3 a; a -- 1 3 SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(a, c) SELECT 4 a, 5 c a c -- 1 3 4 5 On Thu, Sep 22, 2011 at 16:20, Robert Haas robertmh...@gmail.com wrote: On Sun, Sep 18, 2011 at 5:39 AM, Kerem Kat kerem...@gmail.com wrote: I am new to postgresql code, I would like to start implementing easyish TODO items. I have read most of the development guidelines, faqs, articles by Greg Smith (Hacking Postgres with UDFs, Adding WHEN to triggers). The item I would like to implement is adding CORRESPONDING [BY (col1[,col2,...]])] to INTERSECT and EXCEPT operators. Can anyone comment on how much effort this item needs? This seems reasonably tricky for a first project, but maybe not out of reach if you are a skilled C hacker. It's certainly more complicated than my first patch: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a0b76dc662efde6e02921c2d16e06418483b7534 I guess the first question that needs to be answered here is ... what exactly is this syntax supposed to do? A little looking around suggests that EXCEPT CORRESPONDING is supposed to make the correspondence run by column names rather than by column positions, and if you further add BY col1, ... then it restricts the comparison to those columns. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] Adding CORRESPONDING to Set Operations
Is it feasible to implement the CORRESPONDING [BY (expr_list)] statement in set operations by the following changes: i) In analyze.c:transformSetOperationStmt after parsing left and right queries as subnodes to a set operation tree, a) CORRESPONDING: Find matching column targets from both statements, eliminate unmatching targets and proceed. b) CORRESPONDING BY (expr_list): Verify expr_list columns exist in both select statements. Eliminate unmatched column names to expr_list and proceed. ii) Instead of elimination set TargetEntry-resjunk = true for unwanted output columns. Thank you for your attention, Any comments are welcome. Kerem KAT On Sun, Sep 18, 2011 at 12:39, Kerem Kat kerem...@gmail.com wrote: Hello, I am new to postgresql code, I would like to start implementing easyish TODO items. I have read most of the development guidelines, faqs, articles by Greg Smith (Hacking Postgres with UDFs, Adding WHEN to triggers). The item I would like to implement is adding CORRESPONDING [BY (col1[,col2,...]])] to INTERSECT and EXCEPT operators. Can anyone comment on how much effort this item needs? regards, kerem kat.
[HACKERS] Adding CORRESPONDING to Set Operations
Hello, I am new to postgresql code, I would like to start implementing easyish TODO items. I have read most of the development guidelines, faqs, articles by Greg Smith (Hacking Postgres with UDFs, Adding WHEN to triggers). The item I would like to implement is adding CORRESPONDING [BY (col1[,col2,...]])] to INTERSECT and EXCEPT operators. Can anyone comment on how much effort this item needs? regards, kerem kat.
