Skip site navigation (1) Skip section navigation (2)

Re: PERSISTANT PREPARE (another point of view)

From: Milan Oparnica <milan(dot)opa(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: PERSISTANT PREPARE (another point of view)
Date: 2008-07-13 13:55:14
Message-ID: g5d1fe$1i5l$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-sql
Tom Lane wrote:

> Most people around this project think that the best way to do that is to
> push as much logic as you can into server-side stored procedures.  That
> gives you every advantage that a persistent-prepared-statement feature
> would offer, and more besides:
> 

It's simply to complicated to return recordsets through server-side 
stored procedures. They are obviously designed to do complex data 
manipulation, returning few output variables informing the caller about 
final results. Returning records through sets of user-defined-types is 
memory and performance waste (please see my previous post as reply to 
Steve for more details). Plus it's hard to maintain and make 
improvements to such a system. I hate to see 800 user types made for 
every query we made as stored procedure.

I don't say it couldn't be done through sp but maybe you guys could 
provide us with much easier (and efficient) way to organize and fetch 
common SQL statements. Something between VIEWS and STORED PROCEDURES, 
something like PARAMETERIZED VIEWS or PERSISTENT PREPARED statement.

Either way, it would be a place where we could use only PURE SQL syntax.
I think it's 90% of what any database application does.

 > * you can push procedural logic, as well as SQL, out of the application

Application developing tools usually offer bigger set of functions, 
objects, methods etc. than any DB stored procedure language can. There 
is also debugging, code version control software, team development 
software and lots of other stuff. It's just more efficient to keep the 
logic in the application part of the system. Just compare the IDE 
editors to any DB Admin Tool.


 >
 > * you can improve performance by reducing the number of network round
 > trips needed to accomplish a multi-SQL-statement task
 >
 > 			regards, tom lane
 >

I couldn't agree more. Such tasks are decidedly for SP's. I'm thinking 
about 90% of simple to mid-simple tasks (reports, logins, retriving 
customer and item properties, warehouse inventory and other stuff) that 
are simple matter of SQL or SQL's in a union with few parameters for 
filtering the data.

I see programmers hard-coding such SQL statements in PHP, C++, Delphi or 
VB projects. Why?  Is it to complex to have it implemented in Postgre 
engine? We have PREPARE statement, locally for the user. Is it possible 
to take it globally, for all users, and not to forget it when all 
connections dye?

It is a way to get all of SQL statement out of the application not only 
"as much logic as you can". As a leader of our development team I find 
it HIGHLY (and I mean HIGHLY) DESIRABLE.

Best regards,
Milan Oparnica

In response to

Responses

pgsql-sql by date

Next:From: Anoop GDate: 2008-07-14 05:51:17
Subject: how to perform minus (-) operation in a dynamic query
Previous:From: Milan OparnicaDate: 2008-07-13 12:20:48
Subject: Re: PERSISTANT PREPARE (another point of view)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group