Is this behavior intended in the backend?  The problem is that when you 
create a rule on an object that calls a stored function and invoke that 
rule on an insert/update/delete statement your insert/update/delete 
statement will now return a query result to the front end over the FE/BE 
protocol.  (I am not sure this is the exact senerio, but something 
similar).  This means that the user now needs to perform a 
executeQuery() call when using these insert/update/delete statements in 
JDBC because the JDBC driver isn't able to accept a query response when 
issuing a insert/update/delete call.

thanks,
--Barry



-------- Original Message --------
Subject: Re: CallableStatements
Date: Mon, 26 Nov 2001 12:14:32 -0800 (PST)
From: Stuart Robinson <stuart@zapata.org>
To: Rene Pijlman <rene@lab.applinet.nl>
CC: <pgsql-jdbc@postgresql.org>

There are various circumstances where you might want to call a stored
procedure with an executeUpdate method. For example, let's suppose you
have a view that combines a couple of tables and you want an application
you're building to be able to write to it. Since views are read-only, you
would create a rule that intercepts the inserts and updates and fires off
a stored procedure instead. Since the application is doing an insert or an
update, it will use executeUpdate, but the stored procedure will have to
use select and return a result, causing the application to error out.

-Stuart

On Mon, 26 Nov 2001, Rene Pijlman wrote:

 > On Mon, 26 Nov 2001 10:40:52 -0800 (PST), you wrote:
 > >But if you use the executeUpdate method, you'll get an error, because it
 > >isn't expecting a result, no? So, how do you call a stored procedure 
using
 > >executeUpdate?
 >
 > You don't. In the current implementation you need to use a
 > SELECT statement. Why is that a problem?
 >
 > Regards,
 > Renï¿½ Pijlman <rene@lab.applinet.nl>
 >

-- 
Stuart Robinson  [stuart@zapata.org]
http://www.nerdindustries.com
http://www.tzeltal.org


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



Mon, 26 Nov 2001 13:01:37 -0800
Barry Lind <barry@xythos.com>
insert/update/delete statements returning a query response
Barry Lind <barry@xythos.com> writes:
> Is this behavior intended in the backend?  The problem is that when you 
> create a rule on an object that calls a stored function and invoke that 
> rule on an insert/update/delete statement your insert/update/delete 
> statement will now return a query result to the front end over the FE/BE 
> protocol.  (I am not sure this is the exact senerio, but something 
> similar).

If the rule adds SELECT operations to the basic statement then those
SELECT(s) will return results to the frontend.  I think this is
appropriate, perhaps even necessary for some applications of rules.

> This means that the user now needs to perform a 
> executeQuery() call when using these insert/update/delete statements in 
> JDBC because the JDBC driver isn't able to accept a query response when 
> issuing a insert/update/delete call.

I would regard that as a JDBC bug: it should be able to accept a query
response at any time.  It shouldn't have preconceived ideas about what
a given query will produce.

It probably would be a good idea to add some kind of "CALL" or "PERFORM"
statement to the backend, having the same semantics as SELECT except
that the query result is discarded instead of being shipped to the
client.  However, this is largely syntactic sugar with maybe a tiny
bit of performance-improvement rationale.  JDBC should be able to cope
with all the cases that libpq does, and libpq handles this scenario
with aplomb.

			regards, tom lane

Mon, 26 Nov 2001 18:36:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: insert/update/delete statements returning a query response 
What is the FE/BE protocol? (I did a Google search and found references to
it, but no definitions or explanations.) Thanks. (And apologies if this is
a stupid RTFM sort of question.)

-Stuart

On Mon, 26 Nov 2001, Barry Lind wrote:

> Is this behavior intended in the backend?  The problem is that when you
> create a rule on an object that calls a stored function and invoke that
> rule on an insert/update/delete statement your insert/update/delete
> statement will now return a query result to the front end over the FE/BE
> protocol.  (I am not sure this is the exact senerio, but something
> similar).  This means that the user now needs to perform a
> executeQuery() call when using these insert/update/delete statements in
> JDBC because the JDBC driver isn't able to accept a query response when
> issuing a insert/update/delete call.
>
> thanks,
> --Barry


Mon, 26 Nov 2001 16:36:56 -0800 (PST)
Stuart Robinson <stuart@zapata.org>
Re: insert/update/delete statements returning a query response
Doesn't PL/pgSQL already support a PERFORM statement?

-Stuart

On Mon, 26 Nov 2001, Tom Lane wrote:

> Barry Lind <barry@xythos.com> writes:
> > Is this behavior intended in the backend?  The problem is that when you
> > create a rule on an object that calls a stored function and invoke that
> > rule on an insert/update/delete statement your insert/update/delete
> > statement will now return a query result to the front end over the FE/BE
> > protocol.  (I am not sure this is the exact senerio, but something
> > similar).
>
> If the rule adds SELECT operations to the basic statement then those
> SELECT(s) will return results to the frontend.  I think this is
> appropriate, perhaps even necessary for some applications of rules.
>
> > This means that the user now needs to perform a
> > executeQuery() call when using these insert/update/delete statements in
> > JDBC because the JDBC driver isn't able to accept a query response when
> > issuing a insert/update/delete call.
>
> I would regard that as a JDBC bug: it should be able to accept a query
> response at any time.  It shouldn't have preconceived ideas about what
> a given query will produce.
>
> It probably would be a good idea to add some kind of "CALL" or "PERFORM"
> statement to the backend, having the same semantics as SELECT except
> that the query result is discarded instead of being shipped to the
> client.  However, this is largely syntactic sugar with maybe a tiny
> bit of performance-improvement rationale.  JDBC should be able to cope
> with all the cases that libpq does, and libpq handles this scenario
> with aplomb.
>
> 			regards, tom lane
>

-- 
Stuart Robinson  [stuart@zapata.org]
http://www.nerdindustries.com
http://www.tzeltal.org


Mon, 26 Nov 2001 16:39:54 -0800 (PST)
Stuart Robinson <stuart@zapata.org>
Re: insert/update/delete statements returning a query
Stuart Robinson <stuart@zapata.org> writes:
> Doesn't PL/pgSQL already support a PERFORM statement?

Yes.

			regards, tom lane

Mon, 26 Nov 2001 20:05:06 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: insert/update/delete statements returning a query response 
Stuart,

FE/BE = Frontend/Backend protocol.  It is the over the wire protocol 
Postgres uses to talk to clients (jdbc, odbc, libpq, etc.).

It is documented in the Developers Guide, there is a chapter titled 
"Frontend/Backend Protocol".

thanks,
--Barry

Stuart Robinson wrote:

> What is the FE/BE protocol? (I did a Google search and found references to
> it, but no definitions or explanations.) Thanks. (And apologies if this is
> a stupid RTFM sort of question.)
> 
> -Stuart
> 
> On Mon, 26 Nov 2001, Barry Lind wrote:
> 
> 
>>Is this behavior intended in the backend?  The problem is that when you
>>create a rule on an object that calls a stored function and invoke that
>>rule on an insert/update/delete statement your insert/update/delete
>>statement will now return a query result to the front end over the FE/BE
>>protocol.  (I am not sure this is the exact senerio, but something
>>similar).  This means that the user now needs to perform a
>>executeQuery() call when using these insert/update/delete statements in
>>JDBC because the JDBC driver isn't able to accept a query response when
>>issuing a insert/update/delete call.
>>
>>thanks,
>>--Barry
>>
> 



Mon, 26 Nov 2001 18:16:47 -0800
Barry Lind <barry@xythos.com>
Re: insert/update/delete statements returning a query response
OK.  I will fix the jdbc driver in 7.3 to handle this case. 
Unfortunately since the JDBC spec doesn't let me return anything other 
than a row count for inserts/updates/deletes I will just be discarding 
the query result.

thanks,
--Barry

Tom Lane wrote:

> Barry Lind <barry@xythos.com> writes:
> 
>>Is this behavior intended in the backend?  The problem is that when you 
>>create a rule on an object that calls a stored function and invoke that 
>>rule on an insert/update/delete statement your insert/update/delete 
>>statement will now return a query result to the front end over the FE/BE 
>>protocol.  (I am not sure this is the exact senerio, but something 
>>similar).
>>
> 
> If the rule adds SELECT operations to the basic statement then those
> SELECT(s) will return results to the frontend.  I think this is
> appropriate, perhaps even necessary for some applications of rules.
> 
> 
>>This means that the user now needs to perform a 
>>executeQuery() call when using these insert/update/delete statements in 
>>JDBC because the JDBC driver isn't able to accept a query response when 
>>issuing a insert/update/delete call.
>>
> 
> I would regard that as a JDBC bug: it should be able to accept a query
> response at any time.  It shouldn't have preconceived ideas about what
> a given query will produce.
> 
> It probably would be a good idea to add some kind of "CALL" or "PERFORM"
> statement to the backend, having the same semantics as SELECT except
> that the query result is discarded instead of being shipped to the
> client.  However, this is largely syntactic sugar with maybe a tiny
> bit of performance-improvement rationale.  JDBC should be able to cope
> with all the cases that libpq does, and libpq handles this scenario
> with aplomb.
> 
> 			regards, tom lane
> 
> 



Mon, 26 Nov 2001 18:20:03 -0800
Barry Lind <barry@xythos.com>
Re: [HACKERS] insert/update/delete statements returning a query
	response