Re: [HACKERS] Most efficient way for libPQ .. PGresult serialization

[Joshua Bay]

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  wrote:

>
> On 19 August 2016 at 03:08, Joshua Bay  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
>

Re: [HACKERS] Most efficient way for libPQ .. PGresult serialization

[Joshua Bay]

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  wrote:

> On 19 August 2016 at 22:16, Joshua Bay  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
>

Re: [HACKERS] Most efficient way for libPQ .. PGresult serialization

[Craig Ringer]

On 19 August 2016 at 22:16, Joshua Bay  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

Re: [HACKERS] Most efficient way for libPQ .. PGresult serialization

[Craig Ringer]

On 19 August 2016 at 03:08, Joshua Bay  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

Re: [HACKERS] Most efficient way for libPQ .. PGresult serialization

[Joshua Bay]

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  wrote:

> > On 18 August 2016 at 10:05, Joshua Bay  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
>

Re: [HACKERS] Most efficient way for libPQ .. PGresult serialization

[Tatsuo Ishii]

> On 18 August 2016 at 10:05, Joshua Bay  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

Re: [HACKERS] Most efficient way for libPQ .. PGresult serialization

[Craig Ringer]

On 18 August 2016 at 10:05, Joshua Bay  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

[HACKERS] Most efficient way for libPQ .. PGresult serialization

[Joshua Bay]

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 ? :)

Thanks,
Joshua