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

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:

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

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:

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:

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