Most efficient way for libPQ .. PGresult serialization

On 18 August 2016 at 10:05, Joshua Bay <joshuabay93@gmail.com> wrote:

Hi,

I was trying to implement a middleware that lies between client and
postgres.

So, this middleware is supposed to run query with libpq, do its job on
them, and then serialize the result of query, and send it to the client !
(client deserializes to PGresult)

I could simply iterate over rows and columns but than that would be slow.
I also found that query results consist of 3 parts (PGresult, tuples, data
blocks).

Could I please get some pointers ? :)

Take a look at the code for PgBouncer and PgPool-II. Both implement
PostgreSQL protocol proxies you could use as starting points.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 18 August 2016 at 10:05, Joshua Bay <joshuabay93@gmail.com> wrote:

Hi,

I was trying to implement a middleware that lies between client and
postgres.

So, this middleware is supposed to run query with libpq, do its job on
them, and then serialize the result of query, and send it to the client !
(client deserializes to PGresult)

I could simply iterate over rows and columns but than that would be slow.
I also found that query results consist of 3 parts (PGresult, tuples, data
blocks).

Could I please get some pointers ? :)

Take a look at the code for PgBouncer and PgPool-II. Both implement
PostgreSQL protocol proxies you could use as starting points.

This one is based on Pgpool-II.

https://github.com/treasure-data/prestogres

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

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

Thanks,
But I don't think my question was clear enough.

I already managed the connection pooling, and what I need is to serialize
the result.

If PGresult was a contiguous block, I could have just create buffer and
call memcpy for serialization, but structure of result seems much more
complicated.

So, I was asking if there is an easy way to achieve serialization

Thanks!

On Thu, Aug 18, 2016 at 1:33 AM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

On 19 August 2016 at 03:08, Joshua Bay <joshuabay93@gmail.com> wrote:

Thanks,
But I don't think my question was clear enough.

I already managed the connection pooling, and what I need is to serialize
the result.

If PGresult was a contiguous block, I could have just create buffer and
call memcpy for serialization, but structure of result seems much more
complicated.

So, I was asking if there is an easy way to achieve serialization

It's wire format is a serialization. That's kind of the point.

I don't understand what you're trying to do here, so it's hard to give a
better answer.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Oh I see.
I just read more on use cases PgBouncer, but seems like it can't be used
for my project.
The reason is that I need to have my middleware to have full control over
each transaction.
That is it must be able to decide if it's going to commit or abort a single
query (reason why libpq is used in the middleware), and it must be able to
decide when to send back the result. Also it does things like load
balancing with it's algorithm.

So, what middleware does is (simplied, ignoring other details)
1. listens to query and does load balancing
2. execute query on behalf of client to server with libpq (does not have to
be libpq).
3. serialize the result and send it back

And the #3 is why I asked for ways to serialize PGresult (of libpq)

Client app will deserialize the result and thus be able to interpret
PGresult as if it used libpq itself.

Thanks!

On Thu, Aug 18, 2016 at 9:05 PM, Craig Ringer <craig@2ndquadrant.com> wrote:

On 19 August 2016 at 22:16, Joshua Bay <joshuabay93@gmail.com> wrote:

Oh I see.
I just read more on use cases PgBouncer, but seems like it can't be used
for my project.
The reason is that I need to have my middleware to have full control over
each transaction.
That is it must be able to decide if it's going to commit or abort a
single query (reason why libpq is used in the middleware), and it must be
able to decide when to send back the result. Also it does things like load
balancing with it's algorithm.

So, what middleware does is (simplied, ignoring other details)
1. listens to query and does load balancing
2. execute query on behalf of client to server with libpq (does not have
to be libpq).
3. serialize the result and send it back

And the #3 is why I asked for ways to serialize PGresult (of libpq)

Client app will deserialize the result and thus be able to interpret
PGresult as if it used libpq itself.

Surely the app should just use libpq, and your middleware should be a proxy?

Like, say, PgPool-II?

Otherwise you'll have to extract all the results handling parts of libpq
into some smaller cut-down library and graft on
serialization/deserialization code. There's nothing built-in for that
,since the natural and logical serialization for query results is the
PostgreSQL wire protocol.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

No, it can be anything else.

Please correctly me if I'm wrong, but to me, PgPool-II looks like a proxy
server that forwards all the data without interpretation. Can I intercept
in the middle and control the flow between client and server? For e.g, I
need control when the result of transaction is sent back to the result?

On Sat, Aug 20, 2016 at 2:39 AM, Craig Ringer <craig@2ndquadrant.com> wrote: