Implementing Frontend/Backend Protocol TCP/IP

[Raimon Fernandez]

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.







--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Alvaro Herrera-7]

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

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[John R Pierce]

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.



--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Craig Ringer]

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

[Raimon Fernandez]

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.


--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[John R Pierce]

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...






--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Raimon Fernandez]

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


--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Albe Laurenz *EXTERN*]

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

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Craig Ringer]

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

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[John DeSoi]

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.





--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[John DeSoi]

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.





--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Alvaro Herrera-7]

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

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Raimon Fernandez]

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


--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Raimon Fernandez]

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.





--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Alvaro Herrera-7]

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

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Alvaro Herrera-7]

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.

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Raimon Fernandez]

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

> 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.

here is where I'm doing a bad parsing.

how I know where the length ends ?

I know it starts after the S, and for the parsing that I have, always  
the length is 4 chars.

I have to evaluate one by one ?

thanks,

r.


> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> Sent via pgsql-general mailing list (pgsql-general@...)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Alvaro Herrera-7]

Raimon Fernandez wrote:

> how I know where the length ends ?

You count 4 bytes.


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

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Merlin Moncure-2]

On Mon, Oct 26, 2009 at 7:17 PM, John R Pierce <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.

small clarification:

There is only one protocol and it is binary.  For example the length
of datums is never sent as a string.  The protocol is quite
stable...it hasn't changed since 7.4 and there hasn't really been a
big call (some of the quite interesting comments in this thread aside)
for it to change IMO.

The protocol has a binary or text mode, so that user data can be
sent/received in text or binary. Using the binary mode is not stable,
which is what I think you were basically saying.

Now, (self serving pitch here) if you use libpqtypes, you get all the
benefits of binary protocol mode (performance, easy data marshaling)
without having to worry about data format changes between versions
:-D.

merlin

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Raimon Fernandez]

On 27/10/2009, at 15:06, Alvaro Herrera wrote:

> Raimon Fernandez wrote:
>
>> how I know where the length ends ?
>
> You count 4 bytes.

thanks,

I'm parsing now the resulted string as a binarystring and all is  
getting sense ...

thanks for your help,

raimon


> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@...)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general