Re: comunication protocol

On 8/9/07, Omar Bettin <o(dot)bettin(at)tiscali(dot)it> wrote:
> Merlin Moncure wrote:
> > AFAIK, the fastest possible way to get data off the server, skipping
> > all data and text processing is to write a SPI routine, and stream the
> > data out locally to the server. I am doing exactly this in a
> > particular problem that requires high performance and I can tell you
> > that SPI is fast.
> >
> Interesting,do you thing that is possible to implement some send()
> in the interface?

SPI is an interface which allows you to make sql calls from C code
(PostgreSQL allows you to link C code compiled as a .so to the server
and call -- see numerous examples in contrib). The routine you need
to exploit is SPI_getbinval which gives you Datum (essentially a
variant) pointing to the internal binary representation of your field.
In theory you could collect the data into a buffer and send() it off
although thats a lot of work IMO. Also, I would only advise this for
fast dumps from a single table (no joins, etc).

> I have tried ZeosLib and for me is unusable (too slow), I use a
> strong modified PostgresDAC.

I'm suprised -- I know the ZeosLib internals and it's a very thin
layer over libpq. Here is what I suggest:

* turn on statement logging in the server (set log_min_duration_statement)
* make some operations in the app which you suggest are slow -- they
will show up in the log
* 'explain analyze' the query from the psql console

make note of the times and post back (maybe move this thread to the
-performance list)

merlin