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

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

> Dmitriy Igrishin wrote:
> > Sent: Thursday, June 20, 2013 5:09 PM
> > To: PostgreSQL Hackers
> > Subject: [HACKERS] Frontend/backend protocol improvements proposal
> (request).
> >
> > Hackers,
> >
> > While developing a C++ client library for Postgres I felt lack of extra
> > information in command tags in the CommandComplete (B) message
> > for the following commands:
> > PREPARE;
> > DEALLOCATE;
> > DECLARE;
> > CLOSE;
> > LISTEN;
> > UNLISTEN;
> > SET;
> > RESET.
> > Namely, for example, users of my library can prepare statements by using
> > protocol directly or via PREPARE command. Since the protocol does not
> > supports prepared statement deallocation, I wrote a wrapper over
> DEALLOCATE
> > command. The library knows about all prepared statements and
> > invalidates them automatically when user performs deallocate() wrapper.
> > But users can go with DEALLOCATE command directly and in these cases
> > I need to query the database to get the list of currently prepared
> statements
> > whenever CommandComplete message with DEALLOCATE command tag
> > is consumed. Moreover, I need to do it *synchronously* and this breaks
> > asynchronous API.
> > I propose to include name of the object in the CommandComplete (B)
> > message for the above commands.
>
> That would be a change in the protocol, so it's not likely to happen
> soon. There is a page where proposed changes to the wire protocol
> are collected: http://wiki.postgresql.org/wiki/Todo#Wire_Protocol_Changes

Well, even if this proposal moves to the TODO, it would be nice.

>
>
> 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?
>
Nothing wrong if the user wants to deal with scary and cumbersome code.
As library author, I want to help people make things simpler.
To understand me, please look at the pseudo C++ code below.

// A class designed to work with prepared statements
class Prepared_statement {
public:
// Methods to generate a Bind message, like
Prepared_statement* bind(Position, Value);
// ... and more
// Methods to send Execute message, like
void execute();
void execute_async();
};

class Connection {
public:
// many stuff ...
void close();

Prepared_statement* prepare(Name, Query);
void prepare_async(Statement);

// Make yet another instance of prepared statement.
Prepared_statement* prepared_statement(Name);

// etc.
};

The Connection class is a factory for Prepared_statement instances.
As you can see, the Connection::prepare() returns new instance of
*synchronously* prepared statement. Next, the user can bind values
and execute the statement, like this:

void f(Connection* cn)
{
// Prepare unnamed statement and execute it.
cn->prepare("SELECT $1::text")->bind(0, "Albe")->execute();
// Ps: don't worry about absence of delete; We are using smart pointers
:-)
}

But there is a another possible case:

void f(Connection* cn)
{
Prepared_statement* ps = cn->prepare("SELECT $1::text");
cn->close(); // THIS SHOULD invalidate all Prepared_statement instances
...
ps->bind(0, "Albe"); // ... to throw the exception here
}

Moreover, consider:

void f(Connection* cn)
{
Prepared_statement* ps1 = cn->prepare("ps1", "SELECT $1::text");
cn->deallocate("ps1"); // THIS invalidates ps1 object...
ps1->bind(0, "Albe"); // ... to throw the exception here

Prepared_statement* ps2 = cn->prepare("ps2", "SELECT $1::text");
cn->perform("DEALLOCATE ps2"); // THIS SHOULD ALSO invalidate ps2
object...
ps2->bind(0, "Albe"); // ... to throw the exception here
}

In the latter case when the user deallocates named prepared statement
directly,
the implementation of Connection can invalidates the prepared statement
(ps2) by
analyzing and parsing CommandComplete command tag to get it's name.

And please note, that the user can send DEALLOCATE asynchronously. And
there is
only two ways to get the prepared statement (or another session object's)
name:
1) Parse the SQL command which the user is attempts to send;
2) Just get it from CommandComplete command tag.

I beleive that the 1) is a 100% bad idea.

PS: this C++11 library is not publicaly available yet, but I hope it will
this year.

--
// Dmitriy.