Re: [HACKERS] WIP: hooking parser
Tom Lane wrote: How about introducing a varchar2 type as in Oracle? Maybe. I think right now we don't allow input functions to decide that a non-null input string should be converted to a NULL, but that might be fixable. It'd still be an ugly mess though, since I suspect you'd have to introduce a whole structure of varchar2 functions/operators paralleling text. For example, what is Oracle's handling of || ? AFAICS they can't be standards compliant there, which means you need a varchar2-specific nonstrict implementation of ||, and then to make that work the way Oracle users would expect, varchar2-ness rather than text-ness would have to propagate through anything else that might be done to a column before it reaches the ||. Curiously enough, Oracle has it so that || of null arguments treats the arguments as empty string. It's beyond comprehension. But yeah, a varchar2 type with a full set of functions and operators could work. If you choose not to bother with supporting the char type. -- 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] WIP: hooking parser
Curiously enough, Oracle has it so that || of null arguments treats the arguments as empty string. It's beyond comprehension. what is result of '' || '' ? Pavel But yeah, a varchar2 type with a full set of functions and operators could work. If you choose not to bother with supporting the char type. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] WIP: hooking parser
Pavel Stehule pavel.steh...@gmail.com writes: Curiously enough, Oracle has it so that || of null arguments treats the arguments as empty string. It's beyond comprehension. what is result of '' || '' ? Well the result of this is NULL of course (which is the same as '') What's more puzzling is what the answer to 'foo' || NULL is... -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] WIP: hooking parser
On Wed, Feb 18, 2009 at 10:30:12AM -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: I'd be quite interested to support some kind of hook to deal with this Oracle null issue. It would be a great help for porting projects. However, doing this properly is probably more complex and needs further thought. I'd suggest writing a type of regression test first for Oracle null behavior and then evaluating any kind of hook or hack against that. AFAIK, the Oracle behavior is just about entirely unrelated to the parser --- it's a matter of runtime comparison behavior. It is certainly *not* restricted to literal NULL/'' constants, which is the only case that a parser hack can deal with. How about introducing a varchar2 type as in Oracle? It would be a bit of a fiddle going through all the operators and functions making sure that versions existed to cast things back again but seems possible. Not sure how fragile user code would be with it though, I'm mainly worried about it trying to convert things back to TEXT automatically and the resulting change in semantics. Any ideas about good ways to go? -- Sam http://samason.me.uk/ -- 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] WIP: hooking parser
On Thu, Feb 19, 2009 at 06:29:25PM +, Sam Mason wrote: On Wed, Feb 18, 2009 at 10:30:12AM -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: I'd be quite interested to support some kind of hook to deal with this Oracle null issue. It would be a great help for porting projects. However, doing this properly is probably more complex and needs further thought. I'd suggest writing a type of regression test first for Oracle null behavior and then evaluating any kind of hook or hack against that. AFAIK, the Oracle behavior is just about entirely unrelated to the parser --- it's a matter of runtime comparison behavior. It is certainly *not* restricted to literal NULL/'' constants, which is the only case that a parser hack can deal with. How about introducing a varchar2 type as in Oracle? It would be a bit of a fiddle going through all the operators and functions making sure that versions existed to cast things back again but seems possible. Not sure how fragile user code would be with it though, I'm mainly worried about it trying to convert things back to TEXT automatically and the resulting change in semantics. Any ideas about good ways to go? Could you define a type/domain for varchar2 mapping it to varchar. There does not seem to be anything else that needs to be done. Cheers, Ken -- 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] WIP: hooking parser
Sam Mason s...@samason.me.uk writes: On Wed, Feb 18, 2009 at 10:30:12AM -0500, Tom Lane wrote: AFAIK, the Oracle behavior is just about entirely unrelated to the parser --- it's a matter of runtime comparison behavior. It is certainly *not* restricted to literal NULL/'' constants, which is the only case that a parser hack can deal with. How about introducing a varchar2 type as in Oracle? Maybe. I think right now we don't allow input functions to decide that a non-null input string should be converted to a NULL, but that might be fixable. It'd still be an ugly mess though, since I suspect you'd have to introduce a whole structure of varchar2 functions/operators paralleling text. For example, what is Oracle's handling of || ? AFAICS they can't be standards compliant there, which means you need a varchar2-specific nonstrict implementation of ||, and then to make that work the way Oracle users would expect, varchar2-ness rather than text-ness would have to propagate through anything else that might be done to a column before it reaches the ||. 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] WIP: hooking parser
On Thu, Feb 19, 2009 at 02:02:06PM -0500, Tom Lane wrote: Sam Mason s...@samason.me.uk writes: On Wed, Feb 18, 2009 at 10:30:12AM -0500, Tom Lane wrote: AFAIK, the Oracle behavior is just about entirely unrelated to the parser --- it's a matter of runtime comparison behavior. It is certainly *not* restricted to literal NULL/'' constants, which is the only case that a parser hack can deal with. How about introducing a varchar2 type as in Oracle? Maybe. I think right now we don't allow input functions to decide that a non-null input string should be converted to a NULL, but that might be fixable. It seems like the most horrible failure of encapsulation. I don't know the code well enough to comment, but I've already realized that I misinterpreted the docs. They say that the type's input_function is called for NULL values, but because it's strict this obviously doesn't normally affect things. I was hence assuming that it was OK for the function to return NULL for arbitrary inputs, ah well. It'd still be an ugly mess though, since I suspect you'd have to introduce a whole structure of varchar2 functions/operators paralleling text. [and later] to make that work the way Oracle users would expect, varchar2-ness rather than text-ness would have to propagate through anything else that might be done to a column before it reaches the ||. Yes, I'm somewhat prone to understatement and that's what my fiddle comment was about. The only way I could see it working was to keep it as varchar2 for a long as possible, which is why I was wondering if PG would ever have a tendency to auto-magically convert it back to a TEXT breaking things for the user. For example, what is Oracle's handling of || ? AFAICS they can't be standards compliant there, which means you need a varchar2-specific nonstrict implementation of || Didn't think about the non-strict append operator though, that's 'orrible! -- Sam http://samason.me.uk/ -- 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] WIP: hooking parser
Pavel Stehule wrote: 2009/2/16 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: Next sample of parser hook using: attachment contains module that transform every empty string to null. I am not sure, if this behave is exactly compatible with Oracle, Surely a parser hook like this would have nothing whatsoever to do with Oracle's behavior. regards, tom lane it's maybe too much simple :). It is sample that have to show possibility. I'd be quite interested to support some kind of hook to deal with this Oracle null issue. It would be a great help for porting projects. However, doing this properly is probably more complex and needs further thought. I'd suggest writing a type of regression test first for Oracle null behavior and then evaluating any kind of hook or hack against that. -- 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] WIP: hooking parser
Peter Eisentraut pete...@gmx.net writes: I'd be quite interested to support some kind of hook to deal with this Oracle null issue. It would be a great help for porting projects. However, doing this properly is probably more complex and needs further thought. I'd suggest writing a type of regression test first for Oracle null behavior and then evaluating any kind of hook or hack against that. AFAIK, the Oracle behavior is just about entirely unrelated to the parser --- it's a matter of runtime comparison behavior. It is certainly *not* restricted to literal NULL/'' constants, which is the only case that a parser hack can deal with. There's some interesting comments here: http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null 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] WIP: hooking parser
2009/2/18 Peter Eisentraut pete...@gmx.net: Pavel Stehule wrote: 2009/2/16 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: Next sample of parser hook using: attachment contains module that transform every empty string to null. I am not sure, if this behave is exactly compatible with Oracle, Surely a parser hook like this would have nothing whatsoever to do with Oracle's behavior. regards, tom lane it's maybe too much simple :). It is sample that have to show possibility. I'd be quite interested to support some kind of hook to deal with this Oracle null issue. It would be a great help for porting projects. However, doing this properly is probably more complex and needs further thought. I'd suggest writing a type of regression test first for Oracle null behavior and then evaluating any kind of hook or hack against that + 1 regards Pavel -- 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] WIP: hooking parser
Next sample of parser hook using:
attachment contains module that transform every empty string to null.
I am not sure, if this behave is exactly compatible with Oracle, but
for first iteration it is good.
postgres=# select length('') is null;
?column?
--
t
(1 row)
I thing, so this should be used for emulation of some constructors too.
Regards
Pavel Stehule
2009/2/13 Peter Eisentraut pete...@gmx.net:
Heikki Linnakangas wrote:
And on top of that, decode() is supposed to do short-circuit evaluation of
the arguments.
Then the only solution is to hack it right into the parser.
There is an existing decode() function however ...
/*-
*
* null.c
*
*
* Copyright (c) 2008-2009, PostgreSQL Global Developent Group
*
* IDENTIFICATION
*$PostgreSQL: pgsql/contrib/auto_explain/auto_explain.c,v 1.4
2009/01/05 13:35:38 tgl Exp $
*
*-
*/
#include postgres.h
#include fmgr.h
#include parser/parse_expr.h
PG_MODULE_MAGIC;
/* Saved hook value */
static ParseExprTransform_hook_type prev_transformExpr = NULL;
void_PG_init(void);
void_PG_fini(void);
static Node * transformEmptyStr(ParseState *pstate, Node *expr);
/*
* Module load callback
*/
void
_PG_init(void)
{
/* Install hooks. */
prev_transformExpr = ParseExprTransform_hook;
ParseExprTransform_hook = transformEmptyStr;
}
/*
* Module unload callback
*/
void
_PG_fini(void)
{
/* Uninstall hooks. */
ParseExprTransform_hook = prev_transformExpr;
}
/*
* Decode transform hook. When I diagnose decode func call, I transform it.
*/
Node *
transformEmptyStr(ParseState *pstate, Node *expr)
{
A_Const *c = (A_Const *) expr;
if (c-val.type == T_String *c-val.val.str == '\0')
{
A_Const *n = makeNode(A_Const);
n-val.type = T_Null;
n-location = c-location;
expr = (Node *) n;
}
if (prev_transformExpr)
return prev_transformExpr(pstate, expr);
else
return standard_transformExpr(pstate, expr);
}
--
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] WIP: hooking parser
On Mon, Feb 16, 2009 at 02:35:54PM +0100, Pavel Stehule wrote:
attachment contains module that transform every empty string to null.
Why would anyone ever want to do this? This would appear to break all
sorts of things in very non-obvious ways:
SELECT CASE s WHEN '' THEN 'empty string' ELSE s END FROM foo;
UPDATE foo SET s = NULL WHERE s = '';
would no longer do the expected thing. It would only do the expected
thing (in my eyes) when strings of zero length were actually being
inserted into the database. Like:
INSERT INTO foo (s) VALUES ('');
UPDATE foo SET s = '' WHERE s = 'empty string';
Or am I missing something obvious?
--
Sam http://samason.me.uk/
--
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] WIP: hooking parser
2009/2/16 Sam Mason s...@samason.me.uk:
On Mon, Feb 16, 2009 at 02:35:54PM +0100, Pavel Stehule wrote:
attachment contains module that transform every empty string to null.
Why would anyone ever want to do this? This would appear to break all
sorts of things in very non-obvious ways:
I agree, so this behave is strange - but Oracle does it.
so normal query in Oracle for empty value looks like
select * from people where surname is null;
and some application expect transformation from '' to null.
http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/
so these modules (decode, oraemptystr) decrease differences between
PostgreSQL and Oracle.
Regards
Pavel Stehule
p.s. I am not Oracle expert, I expect so here are more qualified men.
SELECT CASE s WHEN '' THEN 'empty string' ELSE s END FROM foo;
UPDATE foo SET s = NULL WHERE s = '';
would no longer do the expected thing. It would only do the expected
thing (in my eyes) when strings of zero length were actually being
inserted into the database. Like:
INSERT INTO foo (s) VALUES ('');
UPDATE foo SET s = '' WHERE s = 'empty string';
Or am I missing something obvious?
--
Sam http://samason.me.uk/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
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] WIP: hooking parser
On Mon, Feb 16, 2009 at 03:21:12PM +0100, Pavel Stehule wrote: 2009/2/16 Sam Mason s...@samason.me.uk: On Mon, Feb 16, 2009 at 02:35:54PM +0100, Pavel Stehule wrote: attachment contains module that transform every empty string to null. Why would anyone ever want to do this? This would appear to break all sorts of things in very non-obvious ways: I agree, so this behave is strange - but Oracle does it. so normal query in Oracle for empty value looks like select * from people where surname is null; and some application expect transformation from '' to null. http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/ that's pretty grim! rant I'd agree with the comment saying A string variable that can't be set empty is like a number variable that can't be set zero. Oracle have just thrown out (or, at best, rewritten) the inductive base case for strings. For numbers you (logically) start with a Zero and a Succ (successor function) and model numbers as an arbitrary number of applications of Succ to Zero (2 is (Succ (Succ Zero))). For strings, you start with an empty string and an append function (hi being (Append (Append '' \h) \i)). /rant so these modules (decode, oraemptystr) decrease differences between PostgreSQL and Oracle. wouldn't it be better/easier to extend something like pgpool to transform Oracle style SQL code to PG style code? You'd certainly be able to get it more complete in reasonable amounts of time, but performance would suffer when you went to look up table definitions to check the types of various things. -- Sam http://samason.me.uk/ -- 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] WIP: hooking parser
Pavel Stehule pavel.steh...@gmail.com writes: Next sample of parser hook using: attachment contains module that transform every empty string to null. I am not sure, if this behave is exactly compatible with Oracle, Surely a parser hook like this would have nothing whatsoever to do with Oracle's behavior. 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] WIP: hooking parser
2009/2/16 Sam Mason s...@samason.me.uk: On Mon, Feb 16, 2009 at 03:21:12PM +0100, Pavel Stehule wrote: 2009/2/16 Sam Mason s...@samason.me.uk: On Mon, Feb 16, 2009 at 02:35:54PM +0100, Pavel Stehule wrote: attachment contains module that transform every empty string to null. Why would anyone ever want to do this? This would appear to break all sorts of things in very non-obvious ways: I agree, so this behave is strange - but Oracle does it. so normal query in Oracle for empty value looks like select * from people where surname is null; and some application expect transformation from '' to null. http://www.thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/ that's pretty grim! rant I'd agree with the comment saying A string variable that can't be set empty is like a number variable that can't be set zero. Oracle have just thrown out (or, at best, rewritten) the inductive base case for strings. For numbers you (logically) start with a Zero and a Succ (successor function) and model numbers as an arbitrary number of applications of Succ to Zero (2 is (Succ (Succ Zero))). For strings, you start with an empty string and an append function (hi being (Append (Append '' \h) \i)). /rant so these modules (decode, oraemptystr) decrease differences between PostgreSQL and Oracle. wouldn't it be better/easier to extend something like pgpool to transform Oracle style SQL code to PG style code? You'd certainly be able to get it more complete in reasonable amounts of time, but performance would suffer when you went to look up table definitions to check the types of various things. then you should to rewrite complete PostgreSQL parser :) and performance will be worse (you have to parse query string two times). For this transformation you need query's semantic tree and access to dictionary (some caches) . Lot of things should by done via extensibility features of PostgreSQL. Sure - you can do this things difficult outside of PostgreSQL or simply via parser's hook. These samples are only for Oracle. But I am sure, so this technique should be used for different databases too. Example. Informix uses convention for named params like paramname = value. PostgreSQL 8.5 will use syntax paramname AS value. So you need change app. code. With hook I am able transform transparently Informix syntax to PostgreSQL syntax without significant increase of load or complexity. regards Pavel Stehule -- Sam http://samason.me.uk/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] WIP: hooking parser
On Mon, Feb 16, 2009 at 04:40:23PM +0100, Pavel Stehule wrote: 2009/2/16 Sam Mason s...@samason.me.uk: On Mon, Feb 16, 2009 at 03:21:12PM +0100, Pavel Stehule wrote: so these modules (decode, oraemptystr) decrease differences between PostgreSQL and Oracle. wouldn't it be better/easier to extend something like pgpool to transform Oracle style SQL code to PG style code? You'd certainly be able to get it more complete in reasonable amounts of time, but performance would suffer when you went to look up table definitions to check the types of various things. then you should to rewrite complete PostgreSQL parser :) and performance will be worse (you have to parse query string two times). Yes, there'd be a few thousand lines of code to write. Note that you only need to parse things twice, planning only needs to be done by PG, so it shouldn't be too bad. It'll add maybe a millisecond or so to query execution times, with most of that time spent going off to find table and function definitions from the real database. For this transformation you need query's semantic tree and access to dictionary (some caches) . Lot of things should by done via extensibility features of PostgreSQL. Sure - you can do this things difficult outside of PostgreSQL or simply via parser's hook. But to do it properly inside PG would be difficult; how would your hooks know to transform: SELECT s FROM foo WHERE s IS NULL; into: SELECT s FROM foo WHERE (s = '' OR s IS NULL); that all looks a bit tricky to me. Hum... actually it's not. All you need to do is to rewrite any string reference s into NULLIF(s,''). That would tank performance as indexes wouldn't be used most of the time, but never mind. These samples are only for Oracle. But I am sure, so this technique should be used for different databases too. Example. Informix uses convention for named params like paramname = value. PostgreSQL 8.5 will use syntax paramname AS value. So you need change app. code. With hook I am able transform transparently Informix syntax to PostgreSQL syntax without significant increase of load or complexity. That would be a *much* bigger change; you're actually changing PG's parser there and not just modifying the parse tree. If it was done externally it would be a much easier thing to do. -- Sam http://samason.me.uk/ -- 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] WIP: hooking parser
2009/2/16 Sam Mason s...@samason.me.uk: On Mon, Feb 16, 2009 at 04:40:23PM +0100, Pavel Stehule wrote: 2009/2/16 Sam Mason s...@samason.me.uk: On Mon, Feb 16, 2009 at 03:21:12PM +0100, Pavel Stehule wrote: so these modules (decode, oraemptystr) decrease differences between PostgreSQL and Oracle. wouldn't it be better/easier to extend something like pgpool to transform Oracle style SQL code to PG style code? You'd certainly be able to get it more complete in reasonable amounts of time, but performance would suffer when you went to look up table definitions to check the types of various things. then you should to rewrite complete PostgreSQL parser :) and performance will be worse (you have to parse query string two times). Yes, there'd be a few thousand lines of code to write. Note that you only need to parse things twice, planning only needs to be done by PG, so it shouldn't be too bad. It'll add maybe a millisecond or so to query execution times, with most of that time spent going off to find table and function definitions from the real database. Hello But to do it properly inside PG would be difficult; how would your hooks know to transform: SELECT s FROM foo WHERE s IS NULL; into: SELECT s FROM foo WHERE (s = '' OR s IS NULL); I don't need it. Oracle store NULL without ''. So expression some IS NULL is stable. that all looks a bit tricky to me. Hum... actually it's not. All you need to do is to rewrite any string reference s into NULLIF(s,''). That would tank performance as indexes wouldn't be used most of the time, but never mind. look to source what I do. It' just simple. But you have to emulate Oracle behave everywhere. Then all is simple, because Oracle doesn't know ''. That would be a *much* bigger change; you're actually changing PG's parser there and not just modifying the parse tree. If it was done externally it would be a much easier thing to do. No I don't do it. Loadable modules are really external. I need only hook inside parser. Regards Pavel Stehule -- Sam http://samason.me.uk/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] WIP: hooking parser
On Mon, Feb 16, 2009 at 08:03:42PM +0100, Pavel Stehule wrote:
2009/2/16 Sam Mason s...@samason.me.uk:
But to do it properly inside PG would be difficult; how would your hooks
know to transform:
SELECT s FROM foo WHERE s IS NULL;
into:
SELECT s FROM foo WHERE (s = '' OR s IS NULL);
I don't need it. Oracle store NULL without ''. So expression some IS
NULL is stable.
OK, I was under the impression that you wanted general Oracle
compatibility from PG. Apparently this isn't the case.
that all looks a bit tricky to me. Hum... actually it's not. All you
need to do is to rewrite any string reference s into NULLIF(s,'').
That would tank performance as indexes wouldn't be used most of the
time, but never mind.
look to source what I do. It' just simple. But you have to emulate
Oracle behave everywhere. Then all is simple, because Oracle doesn't
know ''.
Yes, I read your code. You'll still get zero length strings back from
things like substring('hello world',1,0) and not a NULL as I expect
you'd get back from Oracle.
[ context removed by Pavel; but the example was supporting Informix
style named parameters by PG ]
That would be a *much* bigger change; you're actually changing PG's
parser there and not just modifying the parse tree. If it was done
externally it would be a much easier thing to do.
No I don't do it. Loadable modules are really external. I need only
hook inside parser.
As far as I checked, your code gets passed some subset of the parse
tree. For the parser to have a chance of getting the code to your hook
it would need to be considered valid syntax. Informix style named
parameters isn't considered valid by PG's parser and hence the user will
get an error before the hook would get a chance to rewrite the parse
tree and make it valid. This is basically what Tom was alluding to
here:
http://archives.postgresql.org/pgsql-hackers/2009-02/msg00574.php
--
Sam http://samason.me.uk/
--
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] WIP: hooking parser
2009/2/16 Sam Mason s...@samason.me.uk:
On Mon, Feb 16, 2009 at 08:03:42PM +0100, Pavel Stehule wrote:
2009/2/16 Sam Mason s...@samason.me.uk:
But to do it properly inside PG would be difficult; how would your hooks
know to transform:
SELECT s FROM foo WHERE s IS NULL;
into:
SELECT s FROM foo WHERE (s = '' OR s IS NULL);
I don't need it. Oracle store NULL without ''. So expression some IS
NULL is stable.
OK, I was under the impression that you wanted general Oracle
compatibility from PG. Apparently this isn't the case.
that all looks a bit tricky to me. Hum... actually it's not. All you
need to do is to rewrite any string reference s into NULLIF(s,'').
That would tank performance as indexes wouldn't be used most of the
time, but never mind.
look to source what I do. It' just simple. But you have to emulate
Oracle behave everywhere. Then all is simple, because Oracle doesn't
know ''.
Yes, I read your code. You'll still get zero length strings back from
things like substring('hello world',1,0) and not a NULL as I expect
you'd get back from Oracle.
my sample is very simple - full emulation needs maybe 100 lines more,
but it is possible. After finishing transformation is possible to get
rusult type and I can do some really easy alchemy and wrap funccall
for some text functions and replace simple string with NULL. Similar
game is playing now when you use variadic function or function with
defaults arguments.
[ context removed by Pavel; but the example was supporting Informix
style named parameters by PG ]
That would be a *much* bigger change; you're actually changing PG's
parser there and not just modifying the parse tree. If it was done
externally it would be a much easier thing to do.
No I don't do it. Loadable modules are really external. I need only
hook inside parser.
As far as I checked, your code gets passed some subset of the parse
tree. For the parser to have a chance of getting the code to your hook
it would need to be considered valid syntax. Informix style named
parameters isn't considered valid by PG's parser and hence the user will
get an error before the hook would get a chance to rewrite the parse
tree and make it valid. This is basically what Tom was alluding to
here:
Sure. I need some basic functionality, PostgreSQL have to support
named params. But for example, Informix style named params are valid
now (for bison stage).
There are two etaps - Bison parsing - and transformation. And with
wrapping transformation I am able do it. Is paradox so I am able to do
it with Oracle or Informix syntax and not with planned PostgreSQL
syntax now (in this moment). When I find some functionality, that I
can use, then module is really simple - like decode implementation. It
is only transformation to specific CASE statement (specific, because I
have to use IS NOT DISTINCT operator). But without this base
functionality, I should to use C functions. It is only some code more.
This solution isn't absolutely general - It's not able emulate full
SQL/XML syntax - but current func_call rules are very simple. On
second hand It can support smart functions, that knows their source -
like some SQL/XML functions does.
http://archives.postgresql.org/pgsql-hackers/2009-02/msg00574.php
--
Sam http://samason.me.uk/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
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] WIP: hooking parser
2009/2/16 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: Next sample of parser hook using: attachment contains module that transform every empty string to null. I am not sure, if this behave is exactly compatible with Oracle, Surely a parser hook like this would have nothing whatsoever to do with Oracle's behavior. regards, tom lane it's maybe too much simple :). It is sample that have to show possibility. regards Pavel Stehule -- 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] WIP: hooking parser
Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: I think what you want here is some way to define a function that takes an arbitrary number of arguments of arbitrary type and let the function figure everything out. I see no reason why this can't be a variant on CREATE FUNCTION, except that of course you need to figure out some API and function resolution details. We've already got variadic any functions --- the problem is to tell the parser what the function's result type will be, given a particular parameter list. I agree that hooking transformExpr is not exactly the most ideal way to attack that from a performance or complexity standpoint. What is the defined return type logic for the decode() function anyway? If you want the full CASE-like resolution logic, it might be very hard to fit that into a general system. -- 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] WIP: hooking parser
Peter Eisentraut wrote: Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: I think what you want here is some way to define a function that takes an arbitrary number of arguments of arbitrary type and let the function figure everything out. I see no reason why this can't be a variant on CREATE FUNCTION, except that of course you need to figure out some API and function resolution details. We've already got variadic any functions --- the problem is to tell the parser what the function's result type will be, given a particular parameter list. I agree that hooking transformExpr is not exactly the most ideal way to attack that from a performance or complexity standpoint. What is the defined return type logic for the decode() function anyway? If you want the full CASE-like resolution logic, it might be very hard to fit that into a general system. And on top of that, decode() is supposed to do short-circuit evaluation of the arguments. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] WIP: hooking parser
2009/2/13 Heikki Linnakangas heikki.linnakan...@enterprisedb.com: Peter Eisentraut wrote: Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: I think what you want here is some way to define a function that takes an arbitrary number of arguments of arbitrary type and let the function figure everything out. I see no reason why this can't be a variant on CREATE FUNCTION, except that of course you need to figure out some API and function resolution details. We've already got variadic any functions --- the problem is to tell the parser what the function's result type will be, given a particular parameter list. I agree that hooking transformExpr is not exactly the most ideal way to attack that from a performance or complexity standpoint. What is the defined return type logic for the decode() function anyway? If you want the full CASE-like resolution logic, it might be very hard to fit that into a general system. And on top of that, decode() is supposed to do short-circuit evaluation of the arguments. yes, you should to look so this work do transform hook very vell regards Pavel -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] WIP: hooking parser
Heikki Linnakangas wrote: And on top of that, decode() is supposed to do short-circuit evaluation of the arguments. Then the only solution is to hack it right into the parser. There is an existing decode() function however ... -- 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] WIP: hooking parser
On Wednesday 11 February 2009 12:05:03 Pavel Stehule wrote: It works. And I thing, this should to solve lot of special task related to increase compatibility with Oracle, Informix, or it could be usefull for some others (json support). postgres=# load 'decode'; LOAD postgres=# select decode(null::integer,2,'ahoj',3,'Petr',1,'Pavel',null, 'jaja'); decode jaja (1 row) I think what you want here is some way to define a function that takes an arbitrary number of arguments of arbitrary type and let the function figure everything out. I see no reason why this can't be a variant on CREATE FUNCTION, except that of course you need to figure out some API and function resolution details. But it doesn't have to be a completely different concept like a binary plugin. -- 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] WIP: hooking parser
2009/2/12 Peter Eisentraut pete...@gmx.net: On Wednesday 11 February 2009 12:05:03 Pavel Stehule wrote: It works. And I thing, this should to solve lot of special task related to increase compatibility with Oracle, Informix, or it could be usefull for some others (json support). postgres=# load 'decode'; LOAD postgres=# select decode(null::integer,2,'ahoj',3,'Petr',1,'Pavel',null, 'jaja'); decode jaja (1 row) I think what you want here is some way to define a function that takes an arbitrary number of arguments of arbitrary type and let the function figure everything out. I see no reason why this can't be a variant on CREATE FUNCTION, except that of course you need to figure out some API and function resolution details. But it doesn't have to be a completely different concept like a binary plugin. Actually I need add some metada to parameter list, Question is, what is more simple and more readable - descriptive or procedural solution. And what we are able to implement. example DECODE(any1, any2, (asany1, asany2).,(asany2)+) Actually I thing so with some hook of parser transform stage we should to this task more simply. I found next sample, that should be solved via hook - emulation of Oracle behave '' is null. Regards Pavel -- 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] WIP: hooking parser
Peter Eisentraut pete...@gmx.net writes: I think what you want here is some way to define a function that takes an arbitrary number of arguments of arbitrary type and let the function figure everything out. I see no reason why this can't be a variant on CREATE FUNCTION, except that of course you need to figure out some API and function resolution details. We've already got variadic any functions --- the problem is to tell the parser what the function's result type will be, given a particular parameter list. I agree that hooking transformExpr is not exactly the most ideal way to attack that from a performance or complexity standpoint. 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
[HACKERS] WIP: hooking parser
Hello
some years ago there was some plans about parser's extensibility. I am
able write bison extensions, but I thing, so lot of work should be
done via hooking of transform stage.
I did small example - real implementation of Oracle's decode function.
It's based on hooking transformExpr function.
It works. And I thing, this should to solve lot of special task
related to increase compatibility with Oracle, Informix, or it could
be usefull for some others (json support).
postgres=# load 'decode';
LOAD
postgres=# select
decode(null::integer,2,'ahoj',3,'Petr',1,'Pavel',null, 'jaja');
decode
jaja
(1 row)
postgres=# select decode(3,2,'ahoj',3,'Petr',1,'Pavel',null, 'jaja');
decode
Petr
(1 row)
postgres=# select decode(6,2,'ahoj',3,'Petr',1,'Pavel',null, 'jaja');
decode
(1 row)
postgres=# select decode(6,2,'ahoj',3,'Petr',1,'Pavel',null, 'jaja', 'Milos');
decode
Milos
(1 row)
Any ideas, notes?
regards
Pavel Stehule
/*-
*
* decode.c
*
*
* Copyright (c) 2008-2009, PostgreSQL Global Developent Group
*
* IDENTIFICATION
*$PostgreSQL: pgsql/contrib/auto_explain/auto_explain.c,v 1.4
2009/01/05 13:35:38 tgl Exp $
*
*-
*/
#include postgres.h
#include fmgr.h
#include catalog/namespace.h
#include nodes/makefuncs.h
#include nodes/nodeFuncs.h
#include catalog/pg_type.h
#include parser/parse_coerce.h
#include parser/parse_expr.h
PG_MODULE_MAGIC;
/* Saved hook value */
static ParseExprTransform_hook_type prev_transformExpr = NULL;
void_PG_init(void);
void_PG_fini(void);
static Node * transformDecode(ParseState *pstate, Node *expr);
/*
* Module load callback
*/
void
_PG_init(void)
{
/* Install hooks. */
prev_transformExpr = ParseExprTransform_hook;
ParseExprTransform_hook = transformDecode;
}
/*
* Module unload callback
*/
void
_PG_fini(void)
{
/* Uninstall hooks. */
ParseExprTransform_hook = prev_transformExpr;
}
/*
* Decode transform hook. When I diagnose decode func call, I transform it.
*/
Node *
transformDecode(ParseState *pstate, Node *expr)
{
if (IsA(expr, FuncCall))
{
FuncCall *fnc = (FuncCall *) expr;
char*schemaname;
char*funcname;
DeconstructQualifiedName(fnc-funcname, schemaname, funcname);
if (schemaname != NULL strncmp(schemaname, pg_catalog, 10)
!= 0)
goto not_decode_func;
if (strncmp(funcname, decode, 6) == 0)
{
CaseExpr *newc = makeNode(CaseExpr);
int pos = 0;
ListCell*l;
int def_pos;
int nargs = list_length(fnc-args);
CaseTestExpr *placeholder = NULL;
CaseWhen *neww = NULL;
List*newargs = NIL;
List*resultexprs = NIL;
Node*defresult = NULL;
Oid ptype;
def_pos = nargs % 2 == 0 ? nargs - 1: -1;
foreach(l, fnc-args)
{
/* first param, generate placeholder */
if (pos == 0)
{
Node *arg = transformExpr(pstate, (Node
*) lfirst(l));
if (exprType(arg) == UNKNOWNOID)
arg =
coerce_to_common_type(pstate, arg, TEXTOID, DECODE);
placeholder = makeNode(CaseTestExpr);
placeholder-typeId = exprType(arg);
placeholder-typeMod = exprTypmod(arg);
newc-arg = (Expr *) arg;
}
/* searched value, generate CaseWhen node */
if (pos % 2 != 0 pos != def_pos)
{
Node *warg;
Node *expr = (Node *) lfirst(l);
neww = makeNode(CaseWhen);
neww-location = exprLocation(expr);
warg = (Node *) makeA_Expr(AEXPR_NOT,
NIL,
Re: [HACKERS] WIP: hooking parser
Pavel Stehule pavel.steh...@gmail.com writes: some years ago there was some plans about parser's extensibility. I am able write bison extensions, but I thing, so lot of work should be done via hooking of transform stage. This strikes me as next door to useless, because it can only handle things that look like valid expressions to the existing grammar. So pretty much all you can do is weird sorts of functions, which are already accommodated at less effort with existing features such as function overloading. A hook check in that particular place is not going to have negligible performance impact, since it's going to be hit tens or hundreds or thousands of times per query rather than just once. So it's going to require more than a marginal use case to persuade me we ought to have it. 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] WIP: hooking parser
2009/2/11 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: some years ago there was some plans about parser's extensibility. I am able write bison extensions, but I thing, so lot of work should be done via hooking of transform stage. This strikes me as next door to useless, because it can only handle things that look like valid expressions to the existing grammar. So pretty much all you can do is weird sorts of functions, which are already accommodated at less effort with existing features such as function overloading. Usually we don't need change syntax. But we need to control of coercion stage. I afraid so function overloading is bad when there lot of combination, and polymorphic functions are not enough. for some cases we need more polymorphic types - anyelement1, anyelement2, anyarray1, ... A hook check in that particular place is not going to have negligible performance impact, since it's going to be hit tens or hundreds or thousands of times per query rather than just once. So it's going to require more than a marginal use case to persuade me we ought to have it. Because this stage isn't repeated (I don't expect bigger performance impact), it's similar to other's hooks. But, sure, wrong hook should do strange things. It's risk. + argument - it increase customisability and allows gentle syntax tuning. Function decode is first sample from today morning. regards Pavel Stehule 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] WIP: hooking parser
Pavel Stehule pavel.steh...@gmail.com writes: 2009/2/11 Tom Lane t...@sss.pgh.pa.us: This strikes me as next door to useless, because it can only handle things that look like valid expressions to the existing grammar. So pretty much all you can do is weird sorts of functions, which are already accommodated at less effort with existing features such as function overloading. Usually we don't need change syntax. But we need to control of coercion stage. I afraid so function overloading is bad when there lot of combination, and polymorphic functions are not enough. for some cases we need more polymorphic types - anyelement1, anyelement2, anyarray1, ... Well, then we should go fix those things. A hook function whose purpose is to fundamentally change query semantics strikes me as a very dangerous thing anyway, because your queries either stop working or suddenly do something completely different if the hook happens not to be loaded. The hooks we've accepted to date are intended for either monitoring or experimentation with planner behavior, neither of which will change query semantics. 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] WIP: hooking parser
2009/2/11 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2009/2/11 Tom Lane t...@sss.pgh.pa.us: This strikes me as next door to useless, because it can only handle things that look like valid expressions to the existing grammar. So pretty much all you can do is weird sorts of functions, which are already accommodated at less effort with existing features such as function overloading. Usually we don't need change syntax. But we need to control of coercion stage. I afraid so function overloading is bad when there lot of combination, and polymorphic functions are not enough. for some cases we need more polymorphic types - anyelement1, anyelement2, anyarray1, ... Well, then we should go fix those things. I am for it, and I doing on it. A hook function whose purpose is to fundamentally change query semantics strikes me as a very dangerous thing anyway, because your queries either stop working or suddenly do something completely different if the hook happens not to be loaded. The hooks we've accepted to date are intended for either monitoring or experimentation with planner behavior, neither of which will change query semantics. I agree, and I understand well this risk. But still it is better and wide used than custom patching. Look on executor hook. There are only three cases - useful cases. It is some corner, that is far for general using (integrating into core) and too sugar for ignore it for ever. It's possibility, nothing less, nothing more. regards Pavel Stehule 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
