Implementing Frontend/Backend Protocol

Raimon Fernandez wrote:

I don't know if this is the correct mailing list for this ...

We want to implement, or at least, investigate how difficult will be
to talk directly with PostgreSQL via TCP/IP.

We have the original documentation, and in chapter 43 there is some
info.

Do you know where can we find examples (code) to start the project ?

Yes, the src/interfaces/libpq directory contains a working
implementation. There are independent implementations elsewhere (JDBC,
ODBCng, etc).

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

It takes a bit of work to implement, but it is not too difficult.
What is your target implementation language?

Here is source library for Lisp:

http://common-lisp.net/project/postmodern/

John

On Apr 17, 2007, at 5:22 AM, Raimon Fernandez wrote:

We want to implement, or at least, investigate how difficult will
be to talk directly with PostgreSQL via TCP/IP.

We have the original documentation, and in chapter 43 there is some
info.

Do you know where can we find examples (code) to start the project ?

We found some but where not related to TCP/IP.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Hello,

We're investigating again the idea of creating a directly connection
between our app and postgresql, using TCP/IP.

I found some examples in src/interfaces/libpq but they are in C, and
also found interesting the ones from Lisp.

But I can't figure it out almost nothing ...

:-)

So, maybe a practical example would be better:

I can start a connection, but I'm getting an error when I send the
start up sequence.

StartupMessage (F)
Int32
Length of message contents in bytes, including self.

** The total length including the total string => 33

Int32(196608)
The protocol version number. The most significant 16 bits are the
major version number (3
for the protocol described here). The least significant 16 bits are
the minor version number
(0 for the protocol described here).

** 300

The protocol version number is followed by one or more pairs of
parameter name and value
strings. A zero byte is required as a terminator after the last name/
value pair. Parameters can
appear in any order. user is required, others are optional. Each
parameter is specified as:
String
The parameter name. Currently recognized names are:
user
The database user name to connect as. Required; there is no default.
database
The database to connect to. Defaults to the user name.
options
Command-line arguments for the backend. (This is deprecated in favor
of setting indi-
vidual run-time parameters.)
In addition to the above, any run-time parameter that can be set at
backend start time might
be listed. Such settings will be applied during backend start (after
parsing the command-line
options if any). The values will act as session defaults.
String
The parameter value.

Ok, I can't see how I have to separate the name/value, maybe with a
slash / ?

And how I have to separate the next name/value ?

I understand that I have to add a byte 0 at the end of the last name/
value but not between them ?

user/postgresdatabase/scann0

this is what I send:

33300user/postgresdatabase/scann0

thanks,

regards,

r.

On 19/04/2007, at 4:03, John DeSoi wrote:

Hello,

I'm trying to implement the front-end protocol with TCP from REALbasic
to PostgreSQL.

The docs from PostgreSQL, well, I understand almost, but there are
some points that maybe need more clarification.

Anyone have some experience to start making questions ?

:-)

The front-end tool is REALbasic but can be any tool that have TCP/IP
comunication, so here is irrelevant.

Actually I can connect to Postgre Server, get and parse some
parameters, and send some SELECT, but I don't like how I'm doing, so
any guidence or wiki or blog or how-to where I can get more
information, it would be perfect...

thanks for your time,

regards,

r.

Raimon Fernandez wrote:

Hello,

I'm trying to implement the front-end protocol with TCP from
REALbasic to PostgreSQL.

That sounds the most difficult way to do it. Can't you just embed
libpq?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:

I'm trying to implement the front-end protocol with TCP from
REALbasic to PostgreSQL.

That sounds the most difficult way to do it. Can't you just embed
libpq?

yah, seriously. the binary protocol is not considered stable, it can
change in subtle ways in each version. libpq handles the current
version and all previous versions, and exposes all methods.

On Mon, 2009-10-26 at 20:15 -0300, Alvaro Herrera wrote:

Raimon Fernandez wrote:

Hello,

I'm trying to implement the front-end protocol with TCP from
REALbasic to PostgreSQL.

That sounds the most difficult way to do it. Can't you just embed
libpq?

+1

Almost all languages support some kind of C bindings or provide a
dlopen-like mechanism to dynamically call C functions from shared
libraries.

RealBasic appears to have fairly dynamic, dlopen-style bindings. I'm
sure you can find more information in the manual, but here's an example
of some syntax:

http://forums.realsoftware.com/viewtopic.php?t=5050

You'll have to do a bit more work to produce bindings for libpq, though,
especially if you have to produce bindings for any data types (C
structures). If all you have to bind is function calls, and you can
handle any libpq-specific structures as opaque void pointers then it
shouldn't be too hard to just bind the function calls you need.

--
Craig Ringer

On 27/10/2009, at 0:17, John R Pierce wrote:

Alvaro Herrera wrote:

I'm trying to implement the front-end protocol with TCP from
REALbasic to PostgreSQL.

That sounds the most difficult way to do it. Can't you just embed
libpq?

yah, seriously. the binary protocol is not considered stable, it
can change in subtle ways in each version. libpq handles the
current version and all previous versions, and exposes all methods.

Well, if I specify that I'm using the protocol 300 it should work, and
be stable, not ?

REALbasic has plugin for PostgreSQL, but they are synchronous and
freeze the GUI when interacting with PG. This is not a problem
noramlly, as the SELECTS/UPDATES/... are fast enopugh, but sometimes
we need to fetch 1000, 5000 or more rows and the application stops to
respond, I can't have a progressbar because all is freeze, until all
data has come from PG, so we need a better way.

I found someone who created what I'm trying to do, with the same
language, with the same protocol, with the same version, but it's a
comercial app, and we need the source code. The communication is made
through TCP/IP, really fast, and always asynchronous, our application
is always responsive.

I don't know almost nothing about C and implementing it would be too
much work, and maybe we would have the same problem.

Anyway, I'll try to go further with the binary implementation, at
least, as a learn-approach ...

:-)

thanks,

regards,

r.

Raimon Fernandez wrote:

REALbasic has plugin for PostgreSQL, but they are synchronous and
freeze the GUI when interacting with PG. This is not a problem
noramlly, as the SELECTS/UPDATES/... are fast enopugh, but sometimes
we need to fetch 1000, 5000 or more rows and the application stops to
respond, I can't have a progressbar because all is freeze, until all
data has come from PG, so we need a better way.

I would think the better solution would be to get the vendor to fix its
native plugin to support an asynchronous mode. Or, does this RealBasic
support any sort of client-server or multi-task type programming? if
so, have a separate task or thread that does the SQL operations which
your interactice program interfaces with...

On 27/10/2009, at 8:29, John R Pierce wrote:

Raimon Fernandez wrote:

REALbasic has plugin for PostgreSQL, but they are synchronous and
freeze the GUI when interacting with PG. This is not a problem
noramlly, as the SELECTS/UPDATES/... are fast enopugh, but
sometimes we need to fetch 1000, 5000 or more rows and the
application stops to respond, I can't have a progressbar because
all is freeze, until all data has come from PG, so we need a better
way.

I would think the better solution would be to get the vendor to fix
its native plugin to support an asynchronous mode. Or, does this
RealBasic support any sort of client-server or multi-task type
programming? if so, have a separate task or thread that does the
SQL operations which your interactice program interfaces with...

The plugin is from the same company REALbasic, and it's free.

They don't have any plans to support asynchronous mode, maybe only in
the plugin for their own database, REALserver, wich serves a SQLite
database

REALbasic supports threads (multitasking), but also they freeze when
using the plugin and waiting for a complete answer from the plugin
call ...

Thanks,

regards,

raimon

John R Pierce wrote:

yah, seriously. the binary protocol is not considered stable, it can
change in subtle ways in each version. libpq handles the current
version and all previous versions, and exposes all methods.

That's probably not the problem in the original message, but there
are things you can do with the frontend/backend protocol that libpq
does not expose: for example, with the extended query protocol you can
send a "Bind" call that requests that some of the results should be
returned in text format, others in binary.

Yours,
Laurenz Albe

On 27/10/2009 3:20 PM, Raimon Fernandez wrote:

REALbasic has plugin for PostgreSQL, but they are synchronous and
freeze the GUI when interacting with PG. This is not a problem noramlly,
as the SELECTS/UPDATES/... are fast enopugh, but sometimes we need to
fetch 1000, 5000 or more rows and the application stops to respond, I
can't have a progressbar because all is freeze, until all data has come
from PG, so we need a better way.

You're tackling a pretty big project given the problem you're trying to
solve. The ongoing maintenance burden is likely to be significant. I'd
be really, REALLY surprised if it was worth it in the long run.

Can you not do the Pg operations in another thread? libpq is safe to use
in a multi-threaded program so long as you never try to share a
connection, result set, etc between threads. In most cases, you never
want to use any of libpq outside one "database worker" thread, in which
case it's dead safe. You can have your worker thread raise flags / post
events / whatever to notify the main thread when it's done some work.

If that approach isn't palatable to you or isn't suitable in your
environment, another option is to just use a cursor. If you have a big
fetch to do, instead of:

SELECT * FROM customer;

issue:

BEGIN;
DECLARE customer_curs CURSOR FOR SELECT * FROM customer;

... then progressively FETCH blocks of results from the cursor:

FETCH 100 FROM customer_curs;

... until there's nothing left and you can close the transaction or, if
you want to keep using the transaction, just close the cursor.

See:

http://www.postgresql.org/docs/8.4/static/sql-declare.html
http://www.postgresql.org/docs/8.4/static/sql-fetch.html
http://www.postgresql.org/docs/8.4/static/sql-close.html

--
Craig Ringer

On Oct 26, 2009, at 7:17 PM, John R Pierce wrote:

yah, seriously. the binary protocol is not considered stable, it
can change in subtle ways in each version. libpq handles the
current version and all previous versions, and exposes all methods.

I don't think the frontend/backend protocol has changed since version
7.4. All data can be in text format; you don't have to deal with binary.

I have implemented an interface in Lisp. I found it much easier and
more flexible than the foreign function interface with C and libpq.

John DeSoi, Ph.D.

On Oct 27, 2009, at 4:55 AM, Albe Laurenz wrote:

That's probably not the problem in the original message, but there
are things you can do with the frontend/backend protocol that libpq
does not expose: for example, with the extended query protocol you can
send a "Bind" call that requests that some of the results should be
returned in text format, others in binary.

Another protocol feature that I don't think is exposed in libpq is the
ability to limit the maximum number of rows returned by a query. So if
you are executing end user queries, you don't have to worry about
processing a massive result set or somehow parsing the query to add a
limit clause.

John DeSoi, Ph.D.

Raimon Fernandez wrote:

REALbasic has plugin for PostgreSQL, but they are synchronous and
freeze the GUI when interacting with PG. This is not a problem
noramlly, as the SELECTS/UPDATES/... are fast enopugh, but sometimes
we need to fetch 1000, 5000 or more rows and the application stops
to respond, I can't have a progressbar because all is freeze, until
all data has come from PG, so we need a better way.

If you need to fetch large numbers of rows, perhaps it would be better
to use a cursor and fetch a few at a time, moving the progress bar in
the pauses. So instead of

SELECT * FROM sometab;

you would o
DECLARE foo CURSOR FOR SELECT * FROM sometab;

and then, repeatedly,
FETCH 50 FROM foo

Until there are no more rows.

This can still freeze your app in certain cases, but it will be probably
a lot better than what you currently have. And it will be MUCH easier/
cheaper to do than working with the FE/BE protocol yourself.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 27/10/2009, at 14:00, Alvaro Herrera wrote:

Raimon Fernandez wrote:

REALbasic has plugin for PostgreSQL, but they are synchronous and
freeze the GUI when interacting with PG. This is not a problem
noramlly, as the SELECTS/UPDATES/... are fast enopugh, but sometimes
we need to fetch 1000, 5000 or more rows and the application stops
to respond, I can't have a progressbar because all is freeze, until
all data has come from PG, so we need a better way.

If you need to fetch large numbers of rows, perhaps it would be better
to use a cursor and fetch a few at a time, moving the progress bar in
the pauses. So instead of

SELECT * FROM sometab;

you would o
DECLARE foo CURSOR FOR SELECT * FROM sometab;

and then, repeatedly,
FETCH 50 FROM foo

Until there are no more rows.

This can still freeze your app in certain cases, but it will be
probably
a lot better than what you currently have. And it will be MUCH
easier/
cheaper to do than working with the FE/BE protocol yourself.

Yes, I'm aware of this possibility but it's a lot of extra work also.

The initial idea of TCP/IP still remains in my brain ...

:-)

thanks,

raimon

Hello,

As this thread it's alive, I'm going to ask more specific questions:

After sending the satartup sequence, I receive the paramlist. I don't
need to send Authentication as I'm using a Trust user, for making
things easier.

I receive string data, I suppose it's text data.

I can parse the data received, search for a B.

I don't know if it's better to transform the data into Hex.

After the S I found thre char(0) and later the size of the packet, and
later the name + char(0) (separator between value and parameter), the
parameter, and so on.

Why I found those three char(0) after the S and before the packet
length?

Or it's because the Int32 that has 4 bytes ?

thanks,

regards,

raimon

Documentation:
-----------------------------------------------------------------------------
ParameterStatus (B)
Byte1(’S’)
Identifies the message as a run-time parameter status report.
Int32
Length of message contents in bytes, including self.
String
The name of the run-time parameter being reported.
String
The current value of the parameter.

Raimon Fernandez wrote:

After the S I found thre char(0) and later the size of the packet,
and later the name + char(0) (separator between value and
parameter), the parameter, and so on.

Why I found those three char(0) after the S and before the packet
length?

Because the length is an int32. There are 3 zeros because the packet
length is less than 256.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Raimon Fernandez wrote:

I receive string data, I suppose it's text data.

I can parse the data received, search for a B.

You don't search for a B. You search for an S. The B in the
documentation you quote indicates that this message can be sent by the
backend only. You'll notice others have an F (sent by frontend only)
and some have F & B.

Documentation:
-----------------------------------------------------------------------------
ParameterStatus (B)
Byte1(’S’)
Identifies the message as a run-time parameter status report.
Int32
Length of message contents in bytes, including self.
String
The name of the run-time parameter being reported.
String
The current value of the parameter.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.