Re: [HACKERS] Frontend/backend protocol improvements proposal (request).

2013/6/25 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>

> Dmitriy Igrishin wrote:
> >>>> While developing a C++ client library for Postgres I felt lack of
> extra
> >>>> information in command tags in the CommandComplete (B) message [...]
>
> >>> It seems like bad design to me to keep a list of prepared statements
> >>> on the client side when it is already kept on the server side
> >>> (accessible with the pg_prepared_statements view).
> >>>
> >>> What's wrong with the following:
> >>> If the user wants to deallocate an individual prepared statement,
> >>> just send "DEALLOCATE <statement name>" to the server. If the
> >>> statement does not exist, the server will return an error.
> >>> If the user wants to deallocate all statements, just send
> >>> "DEALLOCATE ALL".
> >>> Why do you need to track prepared statements on the client side?
>
> > Thats great, but there is a some problem -- the *another* statement with
> the same
> > name (and moreover with same parameters!) can be prepared after
> deallocating.
> > And this can result in bugs. So, the client-side allocated "pointer to
> the remote
> > statement" must be invalidated immediatly after deallocating.
>
> I understand the problem now.
> I pondered a bit over your design, and I came up with a different
> idea how to represent prepared statements in a C++ library.
>
Thanks for thinking about it, Albe!

>
> First, a prepared statement is identified by its name.
> To make the relationship between a PreparedStatement object
> and the PostgreSQL prepared statement unique, I suggest that
> the prepared statement name should not be exposed to the
> library user. It should be a private property that is
> set in the initializer in a unique fashion (for example, it
> could be a string representation of the memory address
> of the object).
> That way, there can never be a name collision. That should take
> care of the problem.
>
In fact something like was implemented in very early versions of my
library. There are some reasons why I've redesigned the library:

1) If the user does not specify the name of the prepared statement (or
specify it as "") it is considered as unnamed prepared statement -- a one of
the important concepts of the frontend/backend protocol, which is a base of
my current design.
The unnamed prepared statements are very useful since they are deallocated
authomatically when the backend receives the next Parse message with
empty name.

2) Meaningful names of the named prepared statements (as any other database
objects) may be useful while debugging the application. Imagine the memory
addresses (or any other surrogate names) in the Postgres logs...

Hence, the name() method should be public and name().empty() means
unnamed prepared statement.

> Of course somebody could find out what the statement name is and
> manually issue a DEALLOCATE, but that would only cause future
> use of the prepared statement to fail with an error, which I
> think is ok.
> Also, if somebody uses SQL PREPARE to create a prepared statement
> whose name collides with one of the automatically chosen names,
> they get what they deserve in my opinion.
> It might be useful to warn users.
>

> I also wouldn't provide a deallocate() method. A deallocated
> prepared statement is useless. I think that it would be more
> logical to put that into the destructor method.
> If somebody wants to get rid of the prepared statement
> ahead of time, they can destroy the object.
>
I've also considered this approach and there are some reasons why I don't
implemented the prepared statement class this way:

1) There are Describe message in the protocol. Thus, any prepared statement
can be also described this way:
Prepared_statement* pst1 = connection->describe("name");
Prepared_statement* pst2 = connection->describe("name"); // pst2 points
to the same remote object
Think about the pst as a pointer to the remote object (prepared statement).
Since each statement can be described multiple times, the deleting one of
them
should not result in deallocating the prepared statement by the backend.

2) The best way to inform the user about errors in the modern C++ are
exceptions.
The dellocate operation (as any other query to the database) can be result
in
throwing some exception. But descructors should not throw. (If you are
familiar with
C++ well you should know about the gotchas when destructors throw.)
So, there are deallocate() method which seems to me ok.

Btw, by the reason 2) there are no any transaction RAII classes as in some
other libraries,
because the ROLLBACK command should be executed in the destructor and may
throw.

> Does that make sense?
>
Thanks again for suggestions, Albe!

--
// Dmitriy.