Thread: passing parameters to multiple statements
I'm planning to use multiple statements via libpq. Before starting coding I'm trying to understand are there any limitations on passing parameters. E.g. would the following work:
PQexecParams(conn, "BEGIN;INSERT INTO tbl VALUES($1,$2);SELECT lastval();SELECT * INTO AUDIT FROM (SELECT $3, 'tbl action', lastval());COMMIT;", 3, ...);
Thank you!
Hello 2009/11/16 Konstantin Izmailov <pgfizm@gmail.com>: > I'm planning to use multiple statements via libpq. Before starting coding > I'm trying to understand are there any limitations on passing parameters. > E.g. would the following work: > PQexecParams(conn, "BEGIN;INSERT INTO tbl VALUES($1,$2);SELECT > lastval();SELECT * INTO AUDIT FROM (SELECT $3, 'tbl action', > lastval());COMMIT;", 3, ...); > sorry, it is not direct reply on your question, but why don't you use simply stored procedure? btw. It looks like case for trigger did you know RETURNING clause? your code is very cumbrous Regards Pavel Stehule > Thank you!
Konstantin Izmailov wrote: > I'm planning to use multiple statements via libpq. Before starting coding > I'm trying to understand are there any limitations on passing parameters. > E.g. would the following work: > PQexecParams(conn, "BEGIN;INSERT INTO tbl VALUES($1,$2);SELECT > lastval();SELECT * INTO AUDIT FROM (SELECT $3, 'tbl action', > lastval());COMMIT;", 3, ...); No, because PQexecParams doesn't accept multiple SQL statements. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
Some companies have policy to stay DB agnostic, i.e. use standard SQL only. This is why they want to use multiple statements, not stored procedures.
I'm not familiar with RETURNING. Is this SQL standard? Can it be used for inserting a row and returning back primary key for the inserted row in one statement?
If PQexecParams does not support multiple statements, it needs to be extended for the support, or new function created for the purpose. If I do the change in libpq, may I submit the code to community?
Thank you!
Konstantin
On Mon, Nov 16, 2009 at 2:11 PM, <manitou-sig@sd-9404.dedibox.fr> wrote:
No, because PQexecParams doesn't accept multiple SQL statements.Konstantin Izmailov wrote:
> I'm planning to use multiple statements via libpq. Before starting coding
> I'm trying to understand are there any limitations on passing parameters.
> E.g. would the following work:
> PQexecParams(conn, "BEGIN;INSERT INTO tbl VALUES($1,$2);SELECT
> lastval();SELECT * INTO AUDIT FROM (SELECT $3, 'tbl action',
> lastval());COMMIT;", 3, ...);
Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov wrote: > Some companies have policy to stay DB agnostic, i.e. use standard > SQL only. That's called shooting yourself in the head. Unless you have a very, very specific, business-critical reason to pay this huge cost, you should never attempt it. That some companies have silly, self-destructive policies is not a reason for anybody not working there to pay attention to same. More details on why it's so inevitably expensive below: http://people.planetpostgresql.org/dfetter/index.php?/archives/32-Portability-Part-I.html http://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html 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 iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, 17 Nov 2009 20:16:36 -0800 David Fetter <david@fetter.org> wrote: > On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov > wrote: > > Some companies have policy to stay DB agnostic, i.e. use standard > > SQL only. > That's called shooting yourself in the head. I'm a small fish. I use just Free software and still I think that departing from agnosticity has its cost even if you don't have to pay license costs. Especially if you did it without knowing it or with no reason. Many times departing from agnostic code is caused by: - lack of knowledge of standards/more than one DB - early optimization It's just a matter of where you're going to compromise and why, but you've to do it consciously. eg. a lot of code could run on mysql and postgresql as well at no cost, but many people just ignore there is something else other than mysql. That's shooting yourself in the head without even knowing the reason. -- Ivan Sergio Borgonovo http://www.webthatworks.it
2009/11/18 Ivan Sergio Borgonovo <mail@webthatworks.it>: > On Tue, 17 Nov 2009 20:16:36 -0800 > David Fetter <david@fetter.org> wrote: > >> On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov >> wrote: >> > Some companies have policy to stay DB agnostic, i.e. use standard >> > SQL only. > >> That's called shooting yourself in the head. > > I'm a small fish. I use just Free software and still I think that > departing from agnosticity has its cost even if you don't have to > pay license costs. > Especially if you did it without knowing it or with no reason. > Many times departing from agnostic code is caused by: > - lack of knowledge of standards/more than one DB > - early optimization > > It's just a matter of where you're going to compromise and why, but > you've to do it consciously. > > eg. a lot of code could run on mysql and postgresql as well at no > cost, but many people just ignore there is something else other than > mysql. > That's shooting yourself in the head without even knowing the reason. Sorry, but David has true. I understand, so management is happy, when could to save some money. But it is very wrong for customers - and for programmers too. Only very trivial application should be designed generally and with same SQL code for all database engines. Why: a) you cannot use a stored procedures - it should have very significant impact on effectivity b) you cannot use a fulltext function - if you use LIKE, then your application is dead on bigger data. c) you cannot use a triggers - then all audit and check logic have to be processed on client part - your application will be monolithic and heavy. This is very significant, because fixing bugs and enhancing is more expensive. When your application isn't trivial, then is very good to use decomposition, identify database layer API and creating and maintaining separate modules for different databases. Using common code for all engines is very expensive - then you don't develop, then you searching common space - but it is very difficult. It is true, so db engines shared some functionality now, but they doesn't shared same bugs. And you have to put all together. Debugging, fixing of this applications is very very expensive. Pavel > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Wed, 18 Nov 2009 11:38:46 +0100 Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2009/11/18 Ivan Sergio Borgonovo <mail@webthatworks.it>: > > On Tue, 17 Nov 2009 20:16:36 -0800 > > David Fetter <david@fetter.org> wrote: > > > >> On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov > >> wrote: > >> > Some companies have policy to stay DB agnostic, i.e. use > >> > standard SQL only. > > > >> That's called shooting yourself in the head. > > > > I'm a small fish. I use just Free software and still I think that > > departing from agnosticity has its cost even if you don't have to > > pay license costs. > > Especially if you did it without knowing it or with no reason. > > Many times departing from agnostic code is caused by: > > - lack of knowledge of standards/more than one DB > > - early optimization > > > > It's just a matter of where you're going to compromise and why, > > but you've to do it consciously. > > > > eg. a lot of code could run on mysql and postgresql as well at no > > cost, but many people just ignore there is something else other > > than mysql. > > That's shooting yourself in the head without even knowing the > > reason. > Sorry, but David has true. I understand, so management is happy, I didn't say he was wrong. As usual it is a matter of knowledge and trade off. How can you say what's better if: - you don't know what is standard - you don't know the performance impact of writing something in a dialect of SQL rather than in a standard way One thing is saying you accept the need of breaking compatibility for some DB another is saying that pursuing writing standard code is reckless since it makes all projects too complex and bloated. Ignorance and dogmatism are strict relatives, but I'd say the former is the root of the later. In fact what I generally observe is: - we just know [this] (ignorance) - this *looks* like it will run faster/be easier to write if we write it this way - we tried it on another DB and it performed really bad/was really complicated to rewrite - everything else other than [this] is bad, why should we care (dogmatism) Depending on the domain of the application the DB may not be such a critical part of the overall, and still many things may easily be written in a way that is independent from the DB. In my experience you may end up writing 90% of code that could easily be written in a standard way and with no appreciable difference in costs (performance/coding). Writing stuff in a way that it will make cheaper porting code may even protect you from the changes in the DB you chose as a target. A policy that mandates the use of portable SQL code for any part of any application that you're going to write in a company is equally insane as a policy that mandates to write all code for all applications in python and make them in such a way that they could be automatically translated in any language whose name start with p ;) But I think such kind of policy is rarer than the programmers that know more than a couple of SQL dialects. I don't think companies with such an high level of dogmatism can survive enough long to get involved in something that is not trivial, while it is far more frequent to see applications that don't have such an high coupling with the DB that still are dependent on it just for lack of knowledge of SQL. -- Ivan Sergio Borgonovo http://www.webthatworks.it
2009/11/18 Ivan Sergio Borgonovo <mail@webthatworks.it>: > On Wed, 18 Nov 2009 11:38:46 +0100 > Pavel Stehule <pavel.stehule@gmail.com> wrote: > >> 2009/11/18 Ivan Sergio Borgonovo <mail@webthatworks.it>: >> > On Tue, 17 Nov 2009 20:16:36 -0800 >> > David Fetter <david@fetter.org> wrote: >> > >> >> On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov >> >> wrote: >> >> > Some companies have policy to stay DB agnostic, i.e. use >> >> > standard SQL only. >> > >> >> That's called shooting yourself in the head. >> > >> > I'm a small fish. I use just Free software and still I think that >> > departing from agnosticity has its cost even if you don't have to >> > pay license costs. >> > Especially if you did it without knowing it or with no reason. >> > Many times departing from agnostic code is caused by: >> > - lack of knowledge of standards/more than one DB >> > - early optimization >> > >> > It's just a matter of where you're going to compromise and why, >> > but you've to do it consciously. >> > >> > eg. a lot of code could run on mysql and postgresql as well at no >> > cost, but many people just ignore there is something else other >> > than mysql. >> > That's shooting yourself in the head without even knowing the >> > reason. > >> Sorry, but David has true. I understand, so management is happy, > > I didn't say he was wrong. > > As usual it is a matter of knowledge and trade off. > How can you say what's better if: > - you don't know what is standard > - you don't know the performance impact of writing something in a > dialect of SQL rather than in a standard way > Standard is good. And I prefere standard everywhere, where is possible. But standard has some parts, thats are not respected and not well implemented. For example - stored procedures and fulltext. I had to posibility to see some applications developed by programmers without knowleadge of these. Nothing good. When I ask, why they wrote it, they replyed so needs support for T-SQL and Oracle. Any dogmatism is wrong - yes. But minimally me - and probably David has very bad experience with design ala "all sql code for all databases". And I have good experience with different strategy - early decomposition and separation application and database (engine specific) layer. Nothing less, nothing more. Regards Pavel > One thing is saying you accept the need of breaking compatibility > for some DB another is saying that pursuing writing standard code is > reckless since it makes all projects too complex and bloated. > > Ignorance and dogmatism are strict relatives, but I'd say the former > is the root of the later. > > In fact what I generally observe is: > - we just know [this] (ignorance) > - this *looks* like it will run faster/be easier to write if we write > it this way > - we tried it on another DB and it performed really bad/was really > complicated to rewrite > - everything else other than [this] is bad, why should we care > (dogmatism) > > Depending on the domain of the application the DB may not be such a > critical part of the overall, and still many things may easily be > written in a way that is independent from the DB. > > In my experience you may end up writing 90% of code that could easily > be written in a standard way and with no appreciable difference in > costs (performance/coding). > > Writing stuff in a way that it will make cheaper porting code may > even protect you from the changes in the DB you chose as a target. > > A policy that mandates the use of portable SQL code for any part of > any application that you're going to write in a company is equally > insane as a policy that mandates to write all code for all > applications in python and make them in such a way that they could > be automatically translated in any language whose name start with > p ;) > > But I think such kind of policy is rarer than the programmers that > know more than a couple of SQL dialects. > > I don't think companies with such an high level of dogmatism can > survive enough long to get involved in something that is not > trivial, while it is far more frequent to see applications that > don't have such an high coupling with the DB that still are dependent > on it just for lack of knowledge of SQL. > > -- > Ivan Sergio Borgonovo > http://www.webthatworks.it > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Wed, 18 Nov 2009 14:39:05 +0100 Pavel Stehule <pavel.stehule@gmail.com> wrote: > Standard is good. And I prefere standard everywhere, where is [snip] > Any dogmatism is wrong - yes. But minimally me - and probably David > has very bad experience with design ala "all sql code for all > databases". And I have good experience with different strategy - > early decomposition and separation application and database (engine > specific) layer. Nothing less, nothing more. [snip] I'm not competing on the technical position of the issue, yours is very respected. I'd like to put the accent on the "social" part of it and on the fall back on our beloved DB. I think there are far more people knowing just one DB and badly than companies with such strict (insane) policies and... well PostgreSQL is very standard compliant. I'd beat another horse ;) -- Ivan Sergio Borgonovo http://www.webthatworks.it
Konstantin Izmailov, 17.11.2009 17:33: > This is why they want to use multiple statements Which is not portable as well. Actually the only database I know which permits sending more than one statement in "one string" is SQL Server... Thomas
Konstantin Izmailov wrote: > Some companies have policy to stay DB agnostic, i.e. use standard SQL only. Good luck with that. For example, querying the lastval of a sequence, as your sample code does, already falls outside of standard SQL, AFAIK. > If PQexecParams does not support multiple statements, it needs to be > extended for the support, or new function created for the purpose. If I do > the change in libpq, may I submit the code to community? That change would not be in libpq but in the server. libpq doesn't parse SQL statements. I wish I could say that in future versions you'd be likely to use the DO clause to the effect of grouping statements in one SQL block, in a modern and supported way (DO is in the 8.5 alpha release). Unfortunately DO doesn't seem to accept parameters, which makes it less useful than it could be otherwise. Personally I know I won't really be able to use the DO blocks if I can't pass parameters to them like if it was a single INSERT or UPDATE statement, the use cases being the same to me. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
Ok, I accept reasoning that DB agnostic development is propbably a bad idea.
The question should probably be re-introduced as "Stored Procedures against multiple statements in Postgres".
Here is my client opinion:
"SP’s have their place, as with any development, there’s many reasons for and against any method and there are many methods. I’ve always taken the view – use the right tool for the job.
If you always use stored procedures, you end up with tons and organising them is a nightmare, they are good if you need to reuse the SQL <<<cut>>>
Yes especially flexibility – we don’t want to have a sp for every insert on every table in our db that inserts then returns the currValue and I would like to create a temp db then query that as a second query instead of using retain connection which will give me better performance – only one db connection attempt as opposed to two and I don’t want to create 50 sp’s to do it on each table. I think their good enough reasons, business or otherwise.
I notice this attitude a lot in postgres community – it’s like the reasoning for not allowing cross db queries – “blah blah should have designed db better blah blah”, what they don’t realise is, that some people might want to have an archive db or warehouse and to get data into it would be a lot easier with cross db queries. <<<cut>>>"
Anyway, here is what I understood:
1. If client app needs support for multiple statements with parameters in PostgreSQL, I have to provide a software layer above libpq that includes parser, metadata cache, etc.
2. "BEGIN; INSERT ...; SELECT lastval(); COMMIT;" would work but is not portable because of lastval().
3. No change is needed in libpq since parser can split the multiple statements in the layer above.
Thank you for the valuable discussion!
Konstantin
On Tue, Nov 17, 2009 at 9:16 PM, David Fetter <david@fetter.org> wrote:
On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov wrote:That's called shooting yourself in the head.
> Some companies have policy to stay DB agnostic, i.e. use standard
> SQL only.
Unless you have a very, very specific, business-critical reason to pay
this huge cost, you should never attempt it. That some companies have
silly, self-destructive policies is not a reason for anybody not
working there to pay attention to same.
More details on why it's so inevitably expensive below:
http://people.planetpostgresql.org/dfetter/index.php?/archives/32-Portability-Part-I.html
http://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html
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
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
2009/11/18 Konstantin Izmailov <pgfizm@gmail.com>:
> Ok, I accept reasoning that DB agnostic development is propbably a bad idea.
>
> The question should probably be re-introduced as "Stored Procedures against
> multiple statements in Postgres".
>
> Here is my client opinion:
> "SP’s have their place, as with any development, there’s many reasons for
> and against any method and there are many methods. I’ve always taken the
> view – use the right tool for the job.
>
> If you always use stored procedures, you end up with tons and organising
> them is a nightmare, they are good if you need to reuse the SQL <<<cut>>>
>
> Yes especially flexibility – we don’t want to have a sp for every insert on
> every table in our db that inserts then returns the currValue and I would
> like to create a temp db then query that as a second query instead of using
> retain connection which will give me better performance – only one db
> connection attempt as opposed to two and I don’t want to create 50 sp’s to
> do it on each table. I think their good enough reasons, business or
> otherwise.
>
> I notice this attitude a lot in postgres community – it’s like the reasoning
> for not allowing cross db queries – “blah blah should have designed db
> better blah blah”, what they don’t realise is, that some people might want
> to have an archive db or warehouse and to get data into it would be a lot
> easier with cross db queries. <<<cut>>>"
>
> Anyway, here is what I understood:
>
> 1. If client app needs support for multiple statements with parameters in
> PostgreSQL, I have to provide a software layer above libpq that includes
> parser, metadata cache, etc.
>
> 2. "BEGIN; INSERT ...; SELECT lastval(); COMMIT;" would work but is not
> portable because of lastval().
>
> 3. No change is needed in libpq since parser can split the multiple
> statements in the layer above.
>
> Thank you for the valuable discussion!
>
> Konstantin
there are lot of myth about stored procedures.
a) wrap every SQL statement to procedure is technique used on T-SQL and Sybase.
b) on Oracle and DB2 procedures are used together with views
c) if you use trigger, you don't need some strange multistatments.
d) what is more readable code (on client)?
d1:
BEGIN;
insert into table students values($1,$2,$3);
insert into audit values('students', lastval(), .....)
COMMIT;
or
d2:
SELECT register_new_student($1,$2,$3);
thats all;
try to look on http://www.postgres.cz/index.php/PL/pgSQL_%28en%29
mainly on
http://www.postgres.cz/index.php/PL/pgSQL_%28en%29#Recommendation_for_design_of_saved_procedures_in_PL.2FpqSQL_language
Best regards
Pavel Stehule
>
>
>
>
>
>
>
> On Tue, Nov 17, 2009 at 9:16 PM, David Fetter <david@fetter.org> wrote:
>>
>> On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov wrote:
>> > Some companies have policy to stay DB agnostic, i.e. use standard
>> > SQL only.
>>
>> That's called shooting yourself in the head.
>>
>> Unless you have a very, very specific, business-critical reason to pay
>> this huge cost, you should never attempt it. That some companies have
>> silly, self-destructive policies is not a reason for anybody not
>> working there to pay attention to same.
>>
>> More details on why it's so inevitably expensive below:
>>
>>
>> http://people.planetpostgresql.org/dfetter/index.php?/archives/32-Portability-Part-I.html
>>
>> http://people.planetpostgresql.org/dfetter/index.php?/archives/33-Portability-Part-II.html
>>
>> 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
>> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
>>
>> Remember to vote!
>> Consider donating to Postgres: http://www.postgresql.org/about/donate
>
>