Обсуждение: Named / preparsed / preplaned(prepared) queries - Feature proposal

Hi all

I have an idea how to extend postgresql feature set.


I sugest named / preparsed / preplamned query  mechanizm where named query is a simpliest form and preplaned query is
themost sofisticated (implementation wise). Ie to have ability to  assign a global and may be persistant NAME (or
alias)for some query for some user. Similar how PREPARE related stuff does, but AFAIK, prepared statement are limited
forthe current user on current connection only.  

with syntax like this:

NAMEDQUERY "select foo from bar where blah=$1" AS "myquery" [PREPARSED | PREPLANED  REPLAN EVERY condition]
[PERSISTENT][FINAL] [FOR USER user1,user2] 


And then  modify user privileges to add privileges to create a NAMEDQUERY, to create it for others -  typicaly  useful
fordatabase administrator and the  privilege of executing NAMED QUERY ONLY for some user - for example guest. 


PERSISTANT tell the server that this named query should survive server restart.

FINAL: if Administrator set a named query as FINAL, no other user is able to override it.

PREPARSED: Rationale behind PREPARSED queries is that in most scenarios on web applications there is a simple not
complicatedselect of data often in heap or memory or in the in memory index where actually fetching that data is faster
thenparsing a query string. So I thing a global cache of preparsed  NAMEDQUERY statements is a good idea and on some
loadscan boost performance significantly. 

PREPLANED: same as the preparsed, but the query plan is cached too. So in frequent simple queries (like common in
ajax/cometapplications where it ofen do some simple select to query a status of session etc), I thing it can remove
significantburden (parsing and planning a query) from CPU . 

yes, I know that a query plan that is superior  now, could be inferior few minutes later. So I suggest a  REPLAN EVERY
condition,where condition can be whatever user like - time interval,   changed N percent of tuples etc etc - Let user
decidewhat mechanizm is best for him.   


This feature can lead to two interested things. First, there is a decoupuling a database from application level and
keepthis two realm relatively separate, if underlying db structure is changed, just redefine named queries and do not
touchapplication. It create some sort of easy to use abstraction layer. It is a bit "objectish" approach, because you
cancreate a selfcontained database with data and method for its manipulation (if named query is  set as PERSISTENT,
should dump/restore and replication solutions do their job on persistant named queries as well)   


Second thing is a powerful, simple, yet easy to use security feature with as much granularity as is possible. For
exampleuser guest, who have set a NAMED QUERY ONLY user privilege, will be limited only to queries that admin of the
databasedefined for him, no matter what.I thing that it could be easy to use privilege sepration mechanizm.  

I use postgresql  in my projects since 6.x branch and I am happy with it, but proposed feature is on my Postgresql
wishlist. 
Any comments wellcome.

PS: Execuse my wrong english.

On 11/06/11 6:07 AM, NTPT wrote:
> I use postgresql  in my projects since 6.x branch and I am happy with it, but proposed feature is on my Postgresql
wishlist.
> Any comments wellcome.

is this based on any existing feature in the SQL specification, or in
other popular database engines?


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

I do not know.  Should it be ?

  I think that features are proposed  because user(s) want  it, need it, because it can be useful for other peoples
too,because with this can people solve their problems and/or improve performance and security of their applications.
Notbecause someone else have something similar... 

# ------------ Původní zpráva ------------
# Od: John R Pierce <pierce@hogranch.com>
# Předmět: Re: [GENERAL] Named / preparsed / preplaned(prepared) queries - Feature
# proposal
# Datum: 06.11.2011 17:35:57
# ----------------------------------------
# On 11/06/11 6:07 AM, NTPT wrote:
# > I use postgresql  in my projects since 6.x branch and I am happy with it, but
# proposed feature is on my Postgresql wishlist.
# > Any comments wellcome.
#
# is this based on any existing feature in the SQL specification, or in
# other popular database engines?
#
#
# --
# john r pierce                            N 37, W 122
# santa cruz ca                         mid-left coast
#
#
# --
# Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
# To make changes to your subscription:
# http://www.postgresql.org/mailpref/pgsql-general
#
#
#

On 11/06/11 1:52 PM, NTPT wrote:
>  I think that features are proposed  because user(s) want  it, need
> it, because it can be useful for other peoples too, because with this
> can people solve their problems and/or improve performance and
> security of their applications. Not because someone else have
> something similar...

well, the functionality you describe could be implemented externally to
postgres in as a binding layer, at least for a specific programming
environment, such as Perl DBI, Java JDBC, python, etc etc.   most of
these bindings already have the idea of prepared statements, so adding
an API to fetch a named statement from a table of such shouldn't be that
difficult. Implementing it this way would be a good way to demonstrate
its utility, iron out design issues, and so forth.     If in fact it
proved to be of general interest, it could then be re-implemented as a
contributed project, or perhaps in the postgres core.

generally, built in functionality in PostgreSQL closely follows existing
SQL database standards and practices, I've not seen or heard of any such
'persistent named prepared statements' before in any database system, so
I suspect the core developers aren't going to go anywhere with this
suggestion, but of course, I can't speak for them.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

Hi,

Dne 6.11.2011 15:07, NTPT napsal(a):
> Hi all
>
> I have an idea how to extend postgresql feature set.
>
>
> I sugest named / preparsed / preplamned query  mechanizm where named
> query is a simpliest form and preplaned query is the most
> sofisticated (implementation wise). Ie to have ability to  assign a
> global and may be persistant NAME (or alias) for some query for some
> user. Similar how PREPARE related stuff does, but AFAIK, prepared
> statement are limited for the current user on current connection
> only.
>
> with syntax like this:
>
> NAMEDQUERY "select foo from bar where blah=$1" AS "myquery"
> [PREPARSED | PREPLANED  REPLAN EVERY condition] [PERSISTENT] [FINAL]
> [FOR USER user1,user2]
>
> And then  modify user privileges to add privileges to create a
> NAMEDQUERY, to create it for others -  typicaly  useful for database
> administrator and the  privilege of executing NAMED QUERY ONLY for
> some user - for example guest.

Are you aware of SQL functions? It seems to me it's almost exactly what
you're describing. See this:

  http://www.postgresql.org/docs/current/static/xfunc-sql.html

You can grant privileges on them, it survives server restart, it
provides the decoupling etc. Plus there are other languages (PL/pgSQL)
that may be handy in some cases.

> PERSISTANT tell the server that this named query should survive
> server restart.
>
> FINAL: if Administrator set a named query as FINAL, no other user is
> able to override it.
>
> PREPARSED: Rationale behind PREPARSED queries is that in most
> scenarios on web applications there is a simple not complicated
> select of data often in heap or memory or in the in memory index
> where actually fetching that data is faster then parsing a query
> string. So I thing a global cache of preparsed  NAMEDQUERY statements
> is a good idea and on some loads can boost performance
> significantly.
>
> PREPLANED: same as the preparsed, but the query plan is cached too.
> So in frequent simple queries (like common in ajax/comet applications
> where it ofen do some simple select to query a status of session
> etc), I thing it can remove significant burden (parsing and planning
> a query) from CPU .
>
> yes, I know that a query plan that is superior  now, could be
> inferior few minutes later. So I suggest a  REPLAN EVERY condition,
> where condition can be whatever user like - time interval,   changed
> N percent of tuples etc etc - Let user decide what mechanizm is best
> for him.

Handling the execution plans is a bit tricky, though. The plans are not
forgotten on disconnect, IIRC, but need to be invalidated in some cases
etc. Leaving this up to the developer is not a good idea, and REPLAN
EVERY actually does not fix the problem, because it's not just about
collecting fresh stats but about values (common vs. uncommon values etc.).

Anyway parsing a query is not a problem, once the needed records is
cache - planning it is much more time consuming I guess. So the PREPARSE
does not make much sense to me and the PREPLANED is already available.

> This feature can lead to two interested things. First, there is a
> decoupuling a database from application level and keep this two realm
> relatively separate, if underlying db structure is changed, just
> redefine named queries and do not touch application. It create some
> sort of easy to use abstraction layer. It is a bit "objectish"
> approach, because you can create a selfcontained database with data
> and method for its manipulation (if named query is  set as
> PERSISTENT, should  dump/restore and replication solutions do their
> job on persistant named queries as well)

As I said before, you can this can be achieved with stored procedures
(SQL, PL/pgSQL, PL/whatever). The procedures are dumped/restored,
replicated etc.

> Second thing is a powerful, simple, yet easy to use security feature
> with as much granularity as is possible. For example user guest, who
> have set a NAMED QUERY ONLY user privilege, will be limited only to
> queries that admin of the database defined for him, no matter what.I
> thing that it could be easy to use privilege sepration mechanizm.

Again. Grant the user privileges on stored procedures, not the tables,
and use SECURITY DEFINER when creating the procedures.

Tomas

John R Pierce <pierce@hogranch.com> writes:
> generally, built in functionality in PostgreSQL closely follows existing
> SQL database standards and practices, I've not seen or heard of any such
> 'persistent named prepared statements' before in any database system, so
> I suspect the core developers aren't going to go anywhere with this
> suggestion, but of course, I can't speak for them.

There have been previous proposals for a shared plan cache, which is
somewhat close to what the OP is suggesting.  The trouble with the idea
is that maintenance of the cache would be a mess, and contention for it
would take a big bite out of whatever performance improvement might be
hoped for.  We do already have session-local plan caching; it's unclear
that it makes sense to go further than that.

            regards, tom lane