Guessing future postgresql features

Hello

I am preparing a presentation about postgresql for GoOpen2008 [1] in
Norway. I am trying to guess some of the 'main' new features we could
expect to see in the next versions of postgresql.

After reading different documents on Internet, I have this list which I
plan to include in my presentation. Does anyone disagree with it?  

* Auto-tuning / auto-configuration
* Easy update-in-place - 'pgmigrator'
* More SQL99 and SQL2003 features
* Update-in-place optimizations which enhance OLTP performance
* Auto partitioning / Dynamic partitioning
* External tables interfaces (SQL/MED compliant)
* More exotic datatypes
* More query optimizer improvements
* Elimination of vacuum
* Improve XML support
* Pre-parsing phase that converts non-ISO syntax to supported syntax.

Thanks in advance for your feedback.

[1] http://friprog.no/ez/index.php?/nor/English

regards,
--

-- 
 Rafael Martinez, <r.m.guerrero <at> usit.uio.no>
 Center for Information Technology Services
 University of Oslo, Norway

Re: Cast as compound type

David Fetter wrote:
> I'd like to take a whack at making set-returning functions returning
> SETOF RECORD a little more fun to use.  Let's imagine that we have a
> table foo and a function returning SETOF RECORD that can return foos.
> The call might look something like:
>
> SELECT a, b, c
> FROM f(ROW OF foo)
> WHERE ...;
>
> This would make it much easier and less error-prone to use SETOF
> RECORD.
>   
David, it sounds like you really want to declare the return type of the 
function?  In your above example, you want to say that, in this 
particular invocation, function f() returns a SETOF foo's.  Is that correct?

If you were to create function that returns a RECORD (not a SETOF 
RECORD), you would call it like this:

    SELECT * FROM f() AS (column1 type1, column2 type2, column3 type3);

In your case, I think you want to declare the return type using an 
explicitly defined composite type (possibly a table row); which would 
imply syntax such as:

    SELECT * FROM f() AS (foo);
       or
    SELECT * FROM f() AS (foo.*);

How embarrassing: optimization of a one-shot query doesn't work

While testing the changes I was making to Pavel's EXECUTE USING patch
to ensure that parameter values were being provided to the planner,
it became painfully obvious that the planner wasn't actually *doing*
anything with them.  For example

	execute 'select count(*) from foo where x like $1' into c using $1;

wouldn't generate an indexscan when $1 was of the form 'prefix%'.

Some investigation showed that the planner is using the passed values
for estimation purposes, but not for any purposes where the value *must*
be correct (not only this LIKE-optimization, but constraint exclusion,
for instance).  The reason is that the parameter values are made
available to estimate_expression_value but not to eval_const_expressions.
This is a thinko in a cleanup patch I made early in 8.3 development:
http://archives.postgresql.org/pgsql-committers/2007-02/msg00352.php
I said to myself "eval_const_expressions doesn't need any context,
because a constant expression's value must be independent of context,
so I can avoid changing its API".  Silly me.

The implication of this is that 8.3 is significantly worse than 8.2
in optimizing unnamed statements in the extended-Query protocol;
a feature that JDBC, at least, relies on.

The fix is simple: add PlannerInfo to eval_const_expressions's
parameter list, as was done for estimate_expression_value.  I am
slightly hesitant to do this in a stable branch, since it would break
any third-party code that might be calling that function.  I doubt there
is currently any production-grade code doing so, but if anyone out there
is actively using those planner hooks we put into 8.3, it's conceivable

Re: Guessing future postgresql features

"Rafael Martinez" <r.m.guerrero <at> usit.uio.no> writes:

> * Update-in-place optimizations which enhance OLTP performance
> * Improve XML support

These made it into 8.3. There could be more enhancements and more XML support
but you're probably looking at the stuff which already made 8.3.

> * Elimination of vacuum

Nobody's really proposed eliminating vacuum altogether but we're slowly
chipping away at it. One of the projects on the table for 8.4 would make it
much more efficient, especially for large tables which are partly static. So i
would say "faster vacuum with reduced impact" rather than "elimination"

> * Auto-tuning / auto-configuration
> * Easy update-in-place - 'pgmigrator'
> * Auto partitioning / Dynamic partitioning
> * External tables interfaces (SQL/MED compliant)
> * More exotic datatypes

All of these are on the table to one degree or another. But there's no
guarantees.

> * More SQL99 and SQL2003 features
> * More query optimizer improvements

These are pretty safe bets. I think Tom's already done some optimizer
improvements :)

Re: How embarrassing: optimization of a one-shot query doesn't work

* Tom Lane (tgl <at> sss.pgh.pa.us) wrote:
> The fix is simple: add PlannerInfo to eval_const_expressions's
> parameter list, as was done for estimate_expression_value.  I am
> slightly hesitant to do this in a stable branch, since it would break
> any third-party code that might be calling that function.  I doubt there
> is currently any production-grade code doing so, but if anyone out there
> is actively using those planner hooks we put into 8.3, it's conceivable
> this would affect them.
> 
> Still, the performance regression here is bad enough that I think there
> is little choice.  Comments/objections?

I agree that we should update stable to fix this performance regression,
given the gravity of it.  I also feel that we need to do so ASAP, to
minimize the risk of people being affected by it.  The longer we wait on
it the more likely someone will write something which is affected.

The constraint-exclusion not being used will be a huge impact and
problem for people moving partitioned-data with dynamic pl/pgsql
generation functions to 8.3.

Robert, I'm suprised you weren't affected, or have you just not noticed
yet due to your fancy new-to-you hardware? ;)

	Stephen

Re: Guessing future postgresql features

On Mon, Mar 31, 2008 at 7:02 PM, Rafael Martinez
<r.m.guerrero <at> usit.uio.no> wrote:
>  * Auto-tuning / auto-configuration

Perhaps.  Though, people have been saying they were going to do it
since 2001, and yet nothing substantial exists.

>  * Easy update-in-place - 'pgmigrator'

This should be upgrade-in-place, not update-in-place

>  * More SQL99 and SQL2003 features

Likely.

>  * Update-in-place optimizations which enhance OLTP performance

Postgres does not have update-in-place.  I would just say OLTP
performance enhancements.

>  * Auto partitioning / Dynamic partitioning

Likely.

>  * External tables interfaces (SQL/MED compliant)

It would be nice to see distributed/federated code integrated into the
core, but I don't see it happening anytime soon.

>  * More exotic datatypes

Re: [HACKERS] How embarrassing: optimization of a one-shot query doesn't work

Stephen Frost <sfrost <at> snowman.net> writes:
> * Tom Lane (tgl <at> sss.pgh.pa.us) wrote:
>> Still, the performance regression here is bad enough that I think there
>> is little choice.  Comments/objections?

> I agree that we should update stable to fix this performance regression,
> given the gravity of it.  I also feel that we need to do so ASAP, to
> minimize the risk of people being affected by it.  The longer we wait on
> it the more likely someone will write something which is affected.

In the normal course of events I'd expect that we'd put out 8.3.2
in a month or so.  I'm not quite convinced that this issue is worth
speeding the cycle all by itself.  We've done that for data-loss
issues but never before for a mere performance problem.

The main reason I wanted to make some noise about this is to find out
if anyone is actually trying to call eval_const_expressions (or
relation_excluded_by_constraints, which it turned out needed to change
also) from 8.3 add-on code.  If anyone squawks we could think about a
faster update ...

			regards, tom lane

--

-- 
Sent via pgsql-jdbc mailing list (pgsql-jdbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Re: [badalex <at> gmail.com: Re: [BUGS] Problem identifying constraints which should not be inherited]

On Mon, Mar 31, 2008 at 2:36 AM, NikhilS <nikkhils <at> gmail.com> wrote:
> Hi Alex,

> I was taking a look at this patch to add the pg_dump related changes. Just
> wanted to give you a heads up as this patch crashes if we run "make
> installcheck". Seems there is an issue introduced in the CREATE TABLE
> REFERENCES code path due to your patch (this is without my pg_dump changes
> just to be sure).  Looks like some memory overwrite issue. The trace is as
> follows:

Ouch, sorry i did not reply sooner... been out with the flu.  Oddly
enough make check and make installcheck worked great on my 64 bit box.
 But on my laptop(32 bits) make check lights up like a christmas tree.
 Which is why I did not notice the problem. :(

Attached is a patch that fixes the problem... (it was debugging from
an earlier version)

--

-- 
Sent via pgsql-patches mailing list (pgsql-patches <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Re: [HACKERS] How embarrassing: optimization of a one-shot query doesn't work

Tom Lane wrote:
> The main reason I wanted to make some noise about this is to find out
> if anyone is actually trying to call eval_const_expressions (or
> relation_excluded_by_constraints, which it turned out needed to change
> also) from 8.3 add-on code.  If anyone squawks we could think about a
> faster update ...
>
> 			
>   

That assumes that someone working on using the planner hooks will read 
this thread - which might be reasonable - I guess they number of likely 
users is fairly small. But if they might miss it then it would be best 
to fix it ASAP, ISTM.

cheers

andrew

--

-- 
Sent via pgsql-jdbc mailing list (pgsql-jdbc <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Re: [GENERAL] Connection to PostgreSQL Using Certificate: Wrong Permissions on Private Key File

"korry" <korry.douglas <at> enterprisedb.com> writes:
> Hmmm... I'm not crazy about libpq printing error messages to stderr.  

Me neither, feel free to submit a patch.

The basic problem here is that the obvious fix involves feeding
the message to a PQnoticeProcessor callback, but these messages
occur during connection setup and there's no way to have called
PQsetNoticeProcessor yet.

So I think you've got to invent some green-field API if you want
to improve it, and that means nothing will happen out in the
real world for three to five years 

			regards, tom lane

--

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers