passing parameters to multiple statements

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

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:

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

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

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:

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