WIP: hooking parser

Pavel Stehule <pavel.stehule@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

2009/2/11 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@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

Pavel Stehule <pavel.stehule@gmail.com> writes:

2009/2/11 Tom Lane <tgl@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

2009/2/11 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

2009/2/11 Tom Lane <tgl@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

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.

2009/2/12 Peter Eisentraut <peter_e@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

Peter Eisentraut <peter_e@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

Tom Lane wrote:

Peter Eisentraut <peter_e@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.

Peter Eisentraut wrote:

Tom Lane wrote:

Peter Eisentraut <peter_e@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

2009/2/13 Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>:

Peter Eisentraut wrote:

Tom Lane wrote:

Peter Eisentraut <peter_e@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 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 ...

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 <peter_e@gmx.net>:

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/

2009/2/16 Sam Mason <sam@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.

On Mon, Feb 16, 2009 at 03:21:12PM +0100, Pavel Stehule wrote:

2009/2/16 Sam Mason <sam@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/

Pavel Stehule <pavel.stehule@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

2009/2/16 Sam Mason <sam@samason.me.uk>:

On Mon, Feb 16, 2009 at 03:21:12PM +0100, Pavel Stehule wrote:

2009/2/16 Sam Mason <sam@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

On Mon, Feb 16, 2009 at 04:40:23PM +0100, Pavel Stehule wrote:

2009/2/16 Sam Mason <sam@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/

2009/2/16 Sam Mason <sam@samason.me.uk>:

On Mon, Feb 16, 2009 at 04:40:23PM +0100, Pavel Stehule wrote:

2009/2/16 Sam Mason <sam@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

On Mon, Feb 16, 2009 at 08:03:42PM +0100, Pavel Stehule wrote:

2009/2/16 Sam Mason <sam@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/

2009/2/16 Sam Mason <sam@samason.me.uk>:

On Mon, Feb 16, 2009 at 08:03:42PM +0100, Pavel Stehule wrote:

2009/2/16 Sam Mason <sam@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.

2009/2/16 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@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

Pavel Stehule wrote:

2009/2/16 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@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.

Peter Eisentraut <peter_e@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

2009/2/18 Peter Eisentraut <peter_e@gmx.net>:

Pavel Stehule wrote:

2009/2/16 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@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

On Wed, Feb 18, 2009 at 10:30:12AM -0500, Tom Lane wrote:

Peter Eisentraut <peter_e@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/

On Thu, Feb 19, 2009 at 06:29:25PM +0000, Sam Mason wrote:

On Wed, Feb 18, 2009 at 10:30:12AM -0500, Tom Lane wrote:

Peter Eisentraut <peter_e@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

Sam Mason <sam@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

On Thu, Feb 19, 2009 at 02:02:06PM -0500, Tom Lane wrote:

Sam Mason <sam@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/

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.

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

Pavel Stehule <pavel.stehule@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!