Thread: insert/update/delete statements returning a query response
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, becauseit> >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
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> writes:
> Doesn't PL/pgSQL already support a PERFORM statement?
Yes.
regards, tom lane
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 >> >
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 > >
Re: insert/update/delete statements returning a query response
From
"Zeugswetter Andreas SB SD"
Date:
> 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. Since this behavior is essential to the rule system, imho the actual source of problems is, that PostgreSQL does not have "real stored procedures" == functions that do not have a return value or set (C lingo: void func_a(x)). The usual view rule that needs enhanced processing intelligence would then call a stored procedure and not a function. The easy way out would be to write rules with instead actions, that call insert/update/delete statemants directly. This often works for the more common cases. Andreas
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
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