Patch for 8.5, transformationHook

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

I am sending small patch, that allows hooking transformation stage of parser.

Isn't this the exact same patch we rejected several months ago?

regards, tom lane

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

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

I am sending small patch, that allows hooking transformation stage of parser.

Isn't this the exact same patch we rejected several months ago?

                       regards, tom lane

What I remember, You had some objections about different behave before
and after loading an library.

In this time I hadn't good arguments, and my proposal was using GUC.
What is maybe wrong. I thing, I found better solution.

We found, so isn't possible raise exception in _PG_init function. But
I can raise warning when library will be loaded in normal runtime. And
I can raise warning (or exception) when every function from library is
called. When library is loaded from configuration
(share_preloaded_libraries), then PostgreSQL's behave will be stable.
So I am able to ensure, so anybody doesn't forgot load any library
based on transformatio hook.

regards
Pavel Stehule

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

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

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

I am sending small patch, that allows hooking transformation stage of parser.

Isn't this the exact same patch we rejected several months ago?

What I remember, You had some objections about different behave before
and after loading an library.

No, I was complaining that a hook right there is useless and expensive.
transformExpr() is executed multiple times per query, potentially a very
large number of times per query; so even testing to see if a hook exists
is not a negligible cost. And I have not seen anything I regard as a
convincing demonstration of use-case that can't be handled as well or
better in some other way.

regards, tom lane

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

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

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

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

I am sending small patch, that allows hooking transformation stage of parser.

Isn't this the exact same patch we rejected several months ago?

What I remember, You had some objections about different behave before
and after loading an library.

No, I was complaining that a hook right there is useless and expensive.
transformExpr() is executed multiple times per query, potentially a very
large number of times per query; so even testing to see if a hook exists
is not a negligible cost.  And I have not seen anything I regard as a
convincing demonstration of use-case that can't be handled as well or
better in some other way.

I will do some performance testing. But effect of empty hook should be
similar to testing some GUC now. But I have to do some metering.
Actually transformExpr contains relative big case now, and empty hook
has similar performance effect as new parser node.

I sent some examples, that helps to people with database migration
(some are obscure, I know - Oracle empty string support - it's +/-
joke, there are more serious samples ). And I am preparing JSON
support as example of some comfortable libraries. Next use case should
be in enhancing of db-link functions.

http://archives.postgresql.org/pgsql-hackers/2009-03/msg01239.php

regards
Pavel Stehule

Hello

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

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

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

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

I am sending small patch, that allows hooking transformation stage of parser.

Isn't this the exact same patch we rejected several months ago?

What I remember, You had some objections about different behave before
and after loading an library.

No, I was complaining that a hook right there is useless and expensive.
transformExpr() is executed multiple times per query, potentially a very
large number of times per query; so even testing to see if a hook exists
is not a negligible cost.  And I have not seen anything I regard as a
convincing demonstration of use-case that can't be handled as well or
better in some other way.

                       regards, tom lane

I did some tests based on pgbench.

The test base was initialised with scaling factor 10. I tested high
transaction number with single client. Result is not clean, but
doesn't show significant slowness for patched parser. In both cases
pbbench and postresql was installed on single computer.

First I tested on 4years old notebook prestigio nobile 156 (Pentium M, 1.6).

I tested pgbench (-t 100000) with/without switch -S

without patch 6950+/-13 (-S) 660 +/- 11
patched 6879+/-30 672 +/- 21
--------------------------------------------------
diff -1.02% +1.79%

Next test I did on Dell 830 Core(TM)2 Duo 2.4

withhout patch 9253+/-47 (-S) 209 +/- 4
patched 9299+/-14 214+/- 1
---------------------------------------------------
diff +0.49% +2.33%

Result: The most worst case - pgbench -S -t100000 is 1% slower then
unpatched code (on older computer). With some more similar to normal
traffic, the patched code was 2% faster.

I don't know why patched code should be faster - but this is result
from pgbench - on linux fedora 10, Intel, without GUI

I though about different position of hook, but only in this place the
hook is useful (because expressions are recursive). Elsewhere the hook
hasn't sense :(. So transformationHook doesn't do significant
slowness.

Other possibility is an callback, or some, but I dislike it.

Regards
Pavel Stehule

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

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

No, I was complaining that a hook right there is useless and expensive.
transformExpr() is executed multiple times per query, potentially a very
large number of times per query; so even testing to see if a hook exists
is not a negligible cost.

I did some tests based on pgbench.

The queries done by pgbench are completely trivial and do not stress
parser performance. Even if they did (consider cases likw an IN with a
few thousand list items), the parser is normally not a bottleneck
compared to transaction overhead, network round trips, and pgbench
itself.

I though about different position of hook, but only in this place the
hook is useful (because expressions are recursive).

As I keep saying, a hook there is useless, at least by itself. You
have no control over the grammar and no ability to modify what the
rest of the system understands. The only application I can think of is
to fool with the transformation of FuncCall nodes, which you could do in
a much lower-overhead way by hooking into transformFuncCall. Even that
seems pretty darn marginal for real-world problems.

regards, tom lane

2009/4/18 Tom Lane <tgl@sss.pgh.pa.us>:

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

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

No, I was complaining that a hook right there is useless and expensive.
transformExpr() is executed multiple times per query, potentially a very
large number of times per query; so even testing to see if a hook exists
is not a negligible cost.

I did some tests based on pgbench.

The queries done by pgbench are completely trivial and do not stress
parser performance.  Even if they did (consider cases likw an IN with a
few thousand list items), the parser is normally not a bottleneck
compared to transaction overhead, network round trips, and pgbench
itself.

I though about different position of hook, but only in this place the
hook is useful (because expressions are recursive).

As I keep saying, a hook there is useless, at least by itself.  You
have no control over the grammar and no ability to modify what the
rest of the system understands.

There are lot of things, that should be done with current grammar only
on transformation stage. Currently pg do it now. There are lot of
pseudo functions, that are specially transformed: least, greatest,
coalesce. After hooking we should do some similar work from outer
libraries.

 The only application I can think of is

to fool with the transformation of FuncCall nodes, which you could do in
a much lower-overhead way by hooking into transformFuncCall.  Even that
seems pretty darn marginal for real-world problems.

FuncCall should be. The base what I want is possible via
transformFuncCall. Probably we cannot emulate Oracle's empty string
behave, but it wasn't important :).

regards
Pavel Stehule

On Saturday 18 April 2009 18:09:00 Pavel Stehule wrote:

There are lot of things, that should be done with current grammar only
on transformation stage. Currently pg do it now. There are lot of
pseudo functions, that are specially transformed: least, greatest,
coalesce. After hooking we should do some similar work from outer
libraries.

There are surely other ways to accomplish this than an expression
transformation hook. Adding a property or two to the function definition to
do what you want could do it.

2009/4/19 Peter Eisentraut <peter_e@gmx.net>:

On Saturday 18 April 2009 18:09:00 Pavel Stehule wrote:

There are lot of things, that should be done with current grammar only
on transformation stage. Currently pg do it now. There are lot of
pseudo functions, that are specially transformed: least, greatest,
coalesce. After hooking we should do some similar work from outer
libraries.

There are surely other ways to accomplish this than an expression
transformation hook.  Adding a property or two to the function definition to
do what you want could do it.

should you describe it little bit more?

regards
Pavel

On Sunday 19 April 2009 20:47:37 Pavel Stehule wrote:

2009/4/19 Peter Eisentraut <peter_e@gmx.net>:

On Saturday 18 April 2009 18:09:00 Pavel Stehule wrote:

There are lot of things, that should be done with current grammar only
on transformation stage. Currently pg do it now. There are lot of
pseudo functions, that are specially transformed: least, greatest,
coalesce. After hooking we should do some similar work from outer
libraries.

There are surely other ways to accomplish this than an expression
transformation hook. Adding a property or two to the function definition
to do what you want could do it.

should you describe it little bit more?

The question we should be asking is, what is it that prevents us from
implementing least, greatest, and coalesce in user space now? And then design
a solution for that, if we wanted to pursue this. Instead of writing
transformation hooks and then force every problem to fit that solution.

2009/4/20 Peter Eisentraut <peter_e@gmx.net>:

On Sunday 19 April 2009 20:47:37 Pavel Stehule wrote:

2009/4/19 Peter Eisentraut <peter_e@gmx.net>:

On Saturday 18 April 2009 18:09:00 Pavel Stehule wrote:

There are lot of things, that should be done with current grammar only
on transformation stage. Currently pg do it now. There are lot of
pseudo functions, that are specially transformed: least, greatest,
coalesce. After hooking we should do some similar work from outer
libraries.

There are surely other ways to accomplish this than an expression
transformation hook.  Adding a property or two to the function definition
to do what you want could do it.

should you describe it little bit more?

The question we should be asking is, what is it that prevents us from
implementing least, greatest, and coalesce in user space now?  And then design
a solution for that, if we wanted to pursue this.  Instead of writing
transformation hooks and then force every problem to fit that solution.

I don't believe so is possible to find other general solution. (or
better I didn't find any other solution). Tom has true,
transformationHook on expression is expensive. I thing, so hook on
function should be simple and fast - not all transformation's should
be simple defined via property - classic sample is "decode" like
functions, it needs procedural code.

regards
Pavel Stehule

2009/4/18 Tom Lane <tgl@sss.pgh.pa.us>:

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

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

No, I was complaining that a hook right there is useless and expensive.
transformExpr() is executed multiple times per query, potentially a very
large number of times per query; so even testing to see if a hook exists
is not a negligible cost.

I did some tests based on pgbench.

The queries done by pgbench are completely trivial and do not stress
parser performance.  Even if they did (consider cases likw an IN with a
few thousand list items), the parser is normally not a bottleneck
compared to transaction overhead, network round trips, and pgbench
itself.

I though about different position of hook, but only in this place the
hook is useful (because expressions are recursive).

As I keep saying, a hook there is useless, at least by itself.  You
have no control over the grammar and no ability to modify what the
rest of the system understands.  The only application I can think of is
to fool with the transformation of FuncCall nodes, which you could do in
a much lower-overhead way by hooking into transformFuncCall.  Even that
seems pretty darn marginal for real-world problems.

Hello

I am sending modified patch - it hooking parser via transformFuncCall

regards
Pavel Stehule

On Monday 20 April 2009 09:52:05 Pavel Stehule wrote:

I don't believe so is possible to find other general solution. (or
better I didn't find any other solution). Tom has true,
transformationHook on expression is expensive. I thing, so hook on
function should be simple and fast - not all transformation's should
be simple defined via property - classic sample is "decode" like
functions, it needs procedural code.

I find this all a bit premature, given that you haven't clearly defined what
sort of user-visible functionality you hope to end up implementing. Which
makes it hard to argue why this or that approach might be better.

Peter Eisentraut <peter_e@gmx.net> writes:

I find this all a bit premature, given that you haven't clearly defined what
sort of user-visible functionality you hope to end up implementing.

That sums up my reaction too --- this looks like a solution in search of
a problem. The hook itself might be relatively harmless as long as it's
not in a performance-critical place, but I think people would tend to
contort their thinking to match what they can do with the hook rather
than think about what an ideal solution might be.

I'm also concerned that a hook like this is not usable unless there are
clear conventions about how multiple shared libraries should hook into
it simultaneously. The other hooks we have mostly aren't intended for
purposes that might need concurrent users of the hook, but it's hard
to argue that the case won't come up if this hook actually gets used.

regards, tom lane

2009/4/20 Peter Eisentraut <peter_e@gmx.net>:

On Monday 20 April 2009 09:52:05 Pavel Stehule wrote:

I don't believe so is possible to find other general solution. (or
better I didn't find any other solution). Tom has true,
transformationHook on expression is expensive. I thing, so hook on
function should be simple and fast - not all transformation's should
be simple defined via property - classic sample is "decode" like
functions, it needs procedural code.

I find this all a bit premature, given that you haven't clearly defined what
sort of user-visible functionality you hope to end up implementing.  Which
makes it hard to argue why this or that approach might be better.

a) it allows procedural setting for parameter's transformation and checking

like fce(int, varchar, int, varchar, ....), fce(int, int, int,
varchar, varchar, varchar) ... there should be hundred patterns

b) it allows constructors for data types (ANSI SQL)

datatype(typefield1[, typefiedl2[, typefiedl3[, ...]]]) returns type

c) it allows named parameters with different syntax

like Oracle fcecall(a => 10, b => 30), like Informix fcecall(a = 10, b = 30)

d) with patch that allows named parameters with PostgreSQL syntax
(value AS name) it allows "smart parameters" - name isn't name of
variable, but label like SQL/XML

xmlforest(user_name, user_name AS "user name")

I hope so this is enough :)

Regards
Pavel

2009/4/20 Tom Lane <tgl@sss.pgh.pa.us>:

Peter Eisentraut <peter_e@gmx.net> writes:

I find this all a bit premature, given that you haven't clearly defined what
sort of user-visible functionality you hope to end up implementing.

That sums up my reaction too --- this looks like a solution in search of
a problem.  The hook itself might be relatively harmless as long as it's
not in a performance-critical place, but I think people would tend to
contort their thinking to match what they can do with the hook rather
than think about what an ideal solution might be.

see mail to Peter, please

I'm also concerned that a hook like this is not usable unless there are
clear conventions about how multiple shared libraries should hook into
it simultaneously.  The other hooks we have mostly aren't intended for
purposes that might need concurrent users of the hook, but it's hard
to argue that the case won't come up if this hook actually gets used.

I though about it. The first rule is probably - handler have to work
as filter, and should be (if is possible) independent on order. It is
very similar to triggers.

regards
Pavel Stehule

On Mon, Apr 20, 2009 at 8:45 AM, Pavel Stehule<pavel.stehule@gmail.com> wrote:

2009/4/18 Tom Lane <tgl@sss.pgh.pa.us>:

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

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

No, I was complaining that a hook right there is useless and expensive.
transformExpr() is executed multiple times per query, potentially a very
large number of times per query; so even testing to see if a hook exists
is not a negligible cost.

I did some tests based on pgbench.

The queries done by pgbench are completely trivial and do not stress
parser performance.  Even if they did (consider cases likw an IN with a
few thousand list items), the parser is normally not a bottleneck
compared to transaction overhead, network round trips, and pgbench
itself.

I though about different position of hook, but only in this place the
hook is useful (because expressions are recursive).

As I keep saying, a hook there is useless, at least by itself.  You
have no control over the grammar and no ability to modify what the
rest of the system understands.  The only application I can think of is
to fool with the transformation of FuncCall nodes, which you could do in
a much lower-overhead way by hooking into transformFuncCall.  Even that
seems pretty darn marginal for real-world problems.

I am sending modified patch - it hooking parser via transformFuncCall

I am reviewing this patch. It seems to me upon rereading the thread
that the objections Tom and Peter had to inserting a hook into
transformExpr() mostly still apply to a hook in transformFuncCall():
namely, that there's no proof that putting a hook here is actually
useful. I think we should apply the same criteria to this that we
have to some other patches that have been rejected (like the
extensible-rmgr patch Simon submitted for CommitFest 2008-11), namely,
requiring that the extension mechanism be submitted together with at
least two examples of how it can be used to interesting and useful
things, bundled as one or more contrib modules.

There is some discussion on this thread of things that you think that
this patch can be used to do, but I think it would be much easier to
see whether it's (a) possible and (b) not too ugly to do those things
if you reduce them to code.

...Robert

Robert Haas <robertmhaas@gmail.com> writes:

I think we should apply the same criteria to this that we
have to some other patches that have been rejected (like the
extensible-rmgr patch Simon submitted for CommitFest 2008-11), namely,
requiring that the extension mechanism be submitted together with at
least two examples of how it can be used to interesting and useful
things, bundled as one or more contrib modules.

I wouldn't necessarily insist on actual contrib modules. But fully
worked-out example uses would certainly go a long way toward proving
that the hook is good for something. In previous cases we've sometimes
found out that a proposed hook definition isn't quite right after we
try to use it.

regards, tom lane

Hello

2009/7/25 Robert Haas <robertmhaas@gmail.com>:

On Mon, Apr 20, 2009 at 8:45 AM, Pavel Stehule<pavel.stehule@gmail.com> wrote:

2009/4/18 Tom Lane <tgl@sss.pgh.pa.us>:

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

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

No, I was complaining that a hook right there is useless and expensive.
transformExpr() is executed multiple times per query, potentially a very
large number of times per query; so even testing to see if a hook exists
is not a negligible cost.

I did some tests based on pgbench.

The queries done by pgbench are completely trivial and do not stress
parser performance.  Even if they did (consider cases likw an IN with a
few thousand list items), the parser is normally not a bottleneck
compared to transaction overhead, network round trips, and pgbench
itself.

I though about different position of hook, but only in this place the
hook is useful (because expressions are recursive).

As I keep saying, a hook there is useless, at least by itself.  You
have no control over the grammar and no ability to modify what the
rest of the system understands.  The only application I can think of is
to fool with the transformation of FuncCall nodes, which you could do in
a much lower-overhead way by hooking into transformFuncCall.  Even that
seems pretty darn marginal for real-world problems.

I am sending modified patch - it hooking parser via transformFuncCall

I am reviewing this patch.  It seems to me upon rereading the thread
that the objections Tom and Peter had to inserting a hook into
transformExpr() mostly still apply to a hook in transformFuncCall():
namely, that there's no proof that putting a hook here is actually
useful.  I think we should apply the same criteria to this that we
have to some other patches that have been rejected (like the
extensible-rmgr patch Simon submitted for CommitFest 2008-11), namely,
requiring that the extension mechanism be submitted together with at
least two examples of how it can be used to interesting and useful
things, bundled as one or more contrib modules.

I have in my plan add to contrib JSON support similar to Bauman design:

http://www.mysqludf.org/lib_mysqludf_json/index.php

It's will be sample of "smart" functions. Because this need more then
less work I am waiting on commit.

Other simple intrduction contrib module should be real Oracle decode
function - I sent source code some time ago. But this code needs some
modification. I should send this code if you need it.

Pavel