pre-MED

On Wed, Oct 29, 2008 at 12:40 PM, David Fetter <david@fetter.org> wrote:

Please find enclosed a WIP patch to add the ability for functions to
see the qualifiers of the query in which they're called. It's not
working just yet, and I'm not sure how best to get it working, but I'd
like to see this as part of 8.4, as SQL/MED is just way too ambitious
given the time frame.

To be more specific, SQL/MED is going to be 8.5. This is an overall
improvement for accessing the predicate.

--
Jonah H. Harris, Senior DBA
myYearbook.com

On Wed, 2008-10-29 at 09:40 -0700, David Fetter wrote:

Folks,

Please find enclosed a WIP patch to add the ability for functions to
see the qualifiers of the query in which they're called. It's not
working just yet, and I'm not sure how best to get it working, but I'd
like to see this as part of 8.4, as SQL/MED is just way too ambitious
given the time frame.

Any tips, hints, pointers, etc. would be much appreciated.

Also, PL/Perl shouldn't be the only language to have this capability.
How might we add similar capabilities to PL/PythonU

I'll look at adding this to pl/pythonu. I have to finish some stuff
there before freeze anyway.

------------------
Hannu

On Wed, 2008-10-29 at 19:17 +0200, Hannu Krosing wrote:

On Wed, 2008-10-29 at 09:40 -0700, David Fetter wrote:

Folks,

Please find enclosed a WIP patch to add the ability for functions to
see the qualifiers of the query in which they're called. It's not
working just yet, and I'm not sure how best to get it working, but I'd
like to see this as part of 8.4, as SQL/MED is just way too ambitious
given the time frame.

Any tips, hints, pointers, etc. would be much appreciated.

Also, PL/Perl shouldn't be the only language to have this capability.
How might we add similar capabilities to PL/PythonU

I'll look at adding this to pl/pythonu. I have to finish some stuff
there before freeze anyway.

Have we tested plpython with version 3?

------------------
Hannu

--

On Wed, Oct 29, 2008 at 12:40 PM, David Fetter <david@fetter.org> wrote:

Also, PL/Perl shouldn't be the only language to have this capability.
How might we add similar capabilities to PL/PythonU and PL/Tcl? To
the rest of the PLs? Would it make any sense to have it in SQL
language functions?

Here's a vote for allowing this in plain SQL.

I use the tablefunc contrib module as a way to build a view of a
specific questionnaire's responses (using Elein's nice model here
[1]: ). Currently, if I then write queries against these views that include WHERE clauses they don't perform very well as the underlying data size grows. I was using the afore-mentioned large view that casts everything to text, but recently I started using separate calls to the crosstab function for each underlying table, then joining them together based on their response ID. This seems to work much better for more complex queries, but I think it would still be beneficial to have access to these qualifiers so I could push down to each subquery the list of response ID's to pull. I don't have access to sample SQL at the moment, but if it is wanted I can try to get that this week.
include WHERE clauses they don't perform very well as the underlying
data size grows. I was using the afore-mentioned large view that casts
everything to text, but recently I started using separate calls to the
crosstab function for each underlying table, then joining them
together based on their response ID. This seems to work much better
for more complex queries, but I think it would still be beneficial to
have access to these qualifiers so I could push down to each subquery
the list of response ID's to pull. I don't have access to sample SQL
at the moment, but if it is wanted I can try to get that this week.

David Blewett

1. http://www.varlena.com/GeneralBits/110.php

On Wed, 2008-10-29 at 10:33 -0700, Joshua D. Drake wrote:

On Wed, 2008-10-29 at 19:17 +0200, Hannu Krosing wrote:

On Wed, 2008-10-29 at 09:40 -0700, David Fetter wrote:

Folks,

Please find enclosed a WIP patch to add the ability for functions to
see the qualifiers of the query in which they're called. It's not
working just yet, and I'm not sure how best to get it working, but I'd
like to see this as part of 8.4, as SQL/MED is just way too ambitious
given the time frame.

Any tips, hints, pointers, etc. would be much appreciated.

Also, PL/Perl shouldn't be the only language to have this capability.
How might we add similar capabilities to PL/PythonU

I'll look at adding this to pl/pythonu. I have to finish some stuff
there before freeze anyway.

Have we tested plpython with version 3?

If you mean python 3.0 , then no, at least I have not tested it yet. not
even 2.6

--
------------------------------------------
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

Hannu Krosing <hannu@krosing.net> writes:

On Wed, 2008-10-29 at 09:40 -0700, David Fetter wrote:

Also, PL/Perl shouldn't be the only language to have this capability.
How might we add similar capabilities to PL/PythonU

I'll look at adding this to pl/pythonu.

I would argue that it's already designed wrong if there's need for
PL-specific implementation effort.

regards, tom lane

"David Blewett" <david@dawninglight.net> writes:

Here's a vote for allowing this in plain SQL.

I use the tablefunc contrib module as a way to build a view of a
specific questionnaire's responses (using Elein's nice model here
[1]). Currently, if I then write queries against these views that
include WHERE clauses they don't perform very well as the underlying
data size grows. I was using the afore-mentioned large view that casts
everything to text, but recently I started using separate calls to the
crosstab function for each underlying table, then joining them
together based on their response ID. This seems to work much better
for more complex queries, but I think it would still be beneficial to
have access to these qualifiers so I could push down to each subquery
the list of response ID's to pull. I don't have access to sample SQL
at the moment, but if it is wanted I can try to get that this week.

Please. Some real use-cases would be very helpful here. I'm
particularly wondering whether the proposed deparse call actually yields
anything that's useful without extensive additional knowledge about
the query ...

regards, tom lane

On Wed, Oct 29, 2008 at 10:23:36PM -0400, Tom Lane wrote:

Hannu Krosing <hannu@krosing.net> writes:

On Wed, 2008-10-29 at 09:40 -0700, David Fetter wrote:

Also, PL/Perl shouldn't be the only language to have this
capability. How might we add similar capabilities to PL/PythonU

I'll look at adding this to pl/pythonu.

I would argue that it's already designed wrong if there's need for
PL-specific implementation effort.

I'm not sure how else to do this. The current implementation returns
char *, which doesn't translate uniformly into the PLs. More
sophisticated implementations--a tree or forest structure including
ANDs and ORs, etc.--are even less uniform to translate into PLs, at
least as far as I can tell.

The way I'm looking at it, this could be added onto SPI at varying
degrees of sophistication, but there would still be PL-specific
bindings for it, each of which would involve a PL-specific
implementation effort.

What big things have I missed here?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Tom Lane wrote:

Hannu Krosing <hannu@krosing.net> writes:

On Wed, 2008-10-29 at 09:40 -0700, David Fetter wrote:

Also, PL/Perl shouldn't be the only language to have this capability.
How might we add similar capabilities to PL/PythonU

I'll look at adding this to pl/pythonu.

I would argue that it's already designed wrong if there's need for
PL-specific implementation effort.

I would agree. This should be a generic API.

Joshua D. Drake

David Fetter <david@fetter.org> writes:

On Wed, Oct 29, 2008 at 10:23:36PM -0400, Tom Lane wrote:

I would argue that it's already designed wrong if there's need for
PL-specific implementation effort.

I'm not sure how else to do this. The current implementation returns
char *, which doesn't translate uniformly into the PLs.

Surely they all have a way to call a SQL function that returns text.

regards, tom lane

On Wed, Oct 29, 2008 at 09:40:00AM -0700, David Fetter wrote:

Folks,

Please find enclosed a WIP patch to add the ability for functions to
see the qualifiers of the query in which they're called. It's not
working just yet, and I'm not sure how best to get it working, but I'd
like to see this as part of 8.4, as SQL/MED is just way too ambitious
given the time frame.

Any tips, hints, pointers, etc. would be much appreciated.

Also, PL/Perl shouldn't be the only language to have this capability.
How might we add similar capabilities to PL/PythonU and PL/Tcl? To
the rest of the PLs? Would it make any sense to have it in SQL
language functions?

Please find attached a patch which works in PL/Perl, the work having
been done by Andrew (RhodiumToad) Gierth. It's not clear to me how
this would be generally surface-able to SQL, though. Any ideas?

CREATE OR REPLACE FUNCTION show_qual()
RETURNS TEXT
LANGUAGE plperl
AS $$
return $_QUAL->{qual_string};
$$;

postgres=# SELECT * FROM show_qual() s(a) where a like '%';
a
------------------
(a ~~ '%'::text)

However, there are little lacun� like this:

SELECT * FROM show_qual() s(a)
WHERE A NOT IN (
SELECT 'foo' UNION ALL SELECT 'bar'
);
a
------------------------
(NOT (hashed subplan))

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Please find attached a patch which works in PL/Perl, the work having
been done by Andrew (RhodiumToad) Gierth. It's not clear to me how
this would be generally surface-able to SQL, though. Any ideas?

CREATE OR REPLACE FUNCTION show_qual()
RETURNS TEXT
LANGUAGE plperl
AS $$
return $_QUAL->{qual_string};
$$;

I thought the point here was to change the basic interface to an SQL
function which PL/perl or any other language could access in the usual
way. All this does is keep the strange, magical interface but then
use PL/perl to put a more normal looking wrapper around it.

...Robert

On Fri, Oct 31, 2008 at 01:37:00PM -0400, Robert Haas wrote:

Please find attached a patch which works in PL/Perl, the work having
been done by Andrew (RhodiumToad) Gierth. It's not clear to me how
this would be generally surface-able to SQL, though. Any ideas?

CREATE OR REPLACE FUNCTION show_qual()
RETURNS TEXT
LANGUAGE plperl
AS $$
return $_QUAL->{qual_string};
$$;

I thought the point here was to change the basic interface to an SQL
function which PL/perl or any other language could access in the
usual way. All this does is keep the strange, magical interface but
then use PL/perl to put a more normal looking wrapper around it.

It's not clear how that would work. If you have ideas, please say :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:

However, there are little lacun� like this:

SELECT * FROM show_qual() s(a)
WHERE A NOT IN (
SELECT 'foo' UNION ALL SELECT 'bar'
);
a
------------------------
(NOT (hashed subplan))

This little lacuna seems to say that if you pass the wrong query to
dblink, it will fail to work, possibly with a very obscure error
message; it will be very difficult to debug, and also very difficult (if
not impossible) to workaround.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Nov 03, 2008 at 10:02:24AM -0300, Alvaro Herrera wrote:

David Fetter wrote:

However, there are little lacun� like this:

SELECT * FROM show_qual() s(a)
WHERE A NOT IN (
SELECT 'foo' UNION ALL SELECT 'bar'
);
a
------------------------
(NOT (hashed subplan))

This little lacuna seems to say that if you pass the wrong query to
dblink, it will fail to work, possibly with a very obscure error
message; it will be very difficult to debug, and also very difficult
(if not impossible) to workaround.

I understand that a full implementation of SQL/MED will involve just
about every part of PostgreSQL, but we've got a case of "le mieux est
l'enemi du bien" with this contrived example, which I included so
people could be fully informed when using this very helpful new
feature. Left out are the very large benefits that, for example,
dblink gets for free.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Alvaro Herrera <alvherre@commandprompt.com> writes:

David Fetter wrote:

However, there are little lacun� like this:

SELECT * FROM show_qual() s(a)
WHERE A NOT IN (
SELECT 'foo' UNION ALL SELECT 'bar'
);
a
------------------------
(NOT (hashed subplan))

This little lacuna seems to say that if you pass the wrong query to
dblink, it will fail to work, possibly with a very obscure error
message; it will be very difficult to debug, and also very difficult (if
not impossible) to workaround.

Yeah. The problem here is that the portions of ruleutils.c that deal
with executable plan trees (as opposed to parser output) have never been
designed or intended to do more than support EXPLAIN --- which means
that "human readable" has been considered not only sufficient but more
important than "100% correct and mechanically re-parseable". The
proposed patch raises the bar enormously. I'm not even real sure what
all the issues would be, but I'm pretty sure that subplans are just the
tip of the iceberg. Occurrences of Params ($n) would definitely be
another failure case, and I also wonder what would happen with join
clauses (containing references to the outer side of a join).

regards, tom lane

David Fetter wrote:

I understand that a full implementation of SQL/MED will involve just
about every part of PostgreSQL, but we've got a case of "le mieux est
l'enemi du bien" with this contrived example, which I included so
people could be fully informed when using this very helpful new
feature. Left out are the very large benefits that, for example,
dblink gets for free.

That's all fine and dandy, as long as the deparser is able to notice
when it's going to emit an invalid qualification, and spits out NULL
instead (does your code handle that case?). If it cannot, then we would
cause a serious regression -- dblink fails to work just because the
query happens to use a construct that does not lend itself to proper
deparsing.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

David Fetter <david@fetter.org> writes:

Left out are the very large benefits that, for example,
dblink gets for free.

I take it that "works reliably" isn't a property you think dblink
needs to have.

regards, tom lane

I wrote:

... I'm not even real sure what
all the issues would be, but I'm pretty sure that subplans are just the
tip of the iceberg.

Another issue is that ruleutils responds to column aliasing, as indeed
it must to generate correct output for rules:

regression=# explain select * from tenk1 a where a.unique1 = 42;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 a (cost=0.00..8.27 rows=1 width=244)
Index Cond: (unique1 = 42)
(2 rows)

regression=# explain select * from tenk1 a(foo) where a.foo = 42;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 a (cost=0.00..8.27 rows=1 width=244)
Index Cond: (foo = 42)
(2 rows)

This would be a bit of a problem for dblink or similar functions, which
would have no way at all to know how the column names that they are
dealing in got renamed.

regards, tom lane