Re: [HACKERS] Most efficient way for libPQ .. PGresult serialization
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 <cr...@2ndquadrant.com> wrote: > > On 19 August 2016 at 03:08, Joshua Bay <joshuaba...@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 >
Re: [HACKERS] Most efficient way for libPQ .. PGresult serialization
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 <cr...@2ndquadrant.com> wrote: > On 19 August 2016 at 22:16, Joshua Bay <joshuaba...@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 >
Re: [HACKERS] Most efficient way for libPQ .. PGresult serialization
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 <is...@sraoss.co.jp> wrote: > > On 18 August 2016 at 10:05, Joshua Bay <joshuaba...@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 >
Re: [HACKERS] Way to access LSN (for each transaction) by directly talking to postgres?
Sorry I forgot to reply. Thanks! using decoding plugins works great On Wed, Aug 3, 2016 at 8:37 PM, Michael Paquier <michael.paqu...@gmail.com> wrote: > On Thu, Aug 4, 2016 at 3:02 AM, Joshua Bay <joshuaba...@gmail.com> wrote: > > Could you please tell me how I can get LSN of each transaction at decoder > > plugin? > > Craig already gave you hints, but here are more. You will need to hack > your own plugin. You could just use the one in contrib/test_decoding, > remove most of its code, and use the commit callback to issue the LSN > you are interested in. Note as well that when using > pg_logical_slot_peek_changes or pg_logical_slot_get_changes, you can > get a LSN location. Using test_decoding as a base, that's not a > complicated effort. > -- > Michael >
[HACKERS] Most efficient way for libPQ .. PGresult serialization
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
Re: [HACKERS] Way to access LSN (for each transaction) by directly talking to postgres?
Thanks Michael, Could you please tell me how I can get LSN of each transaction at decoder plugin? On Wed, Aug 3, 2016 at 2:08 AM, Michael Paquier <michael.paqu...@gmail.com> wrote: > On Wed, Aug 3, 2016 at 3:00 PM, Michael Paquier > <michael.paqu...@gmail.com> wrote: > > On Wed, Aug 3, 2016 at 12:37 PM, Joshua Bay <joshuaba...@gmail.com> > wrote: > >> Could you please let me know if there is a way to get LSN of each > >> transaction by directly communicating with Postgres server and NOT by > >> accessing logs. > > > > Logical decoding is one way. > > And I just saw your other message... What I just meant here is that if > you use a decoder plugin that just emits information at transaction > begin/commit you can directly get this information. There is no need > to directly look at the WAL logs, the server does it for you. And it > offers a good cover regarding the information that has already been > consumed or not. > > (Btw, avoid sending emails across multiple mailing lists, particularly > pgsql-committers which is not aimed for that). > -- > Michael >
Re: [HACKERS] Way to access LSN (for each transaction) by directly talking to postgres?
Thanks for responses! The problem I wanted to solve was to find the (global) order of commits across the postgres cluster. So, my attempt was to use the LSN. On Wed, Aug 3, 2016 at 9:47 AM, Craig Ringer <cr...@2ndquadrant.com> wrote: > > > On 3 August 2016 at 11:37, Joshua Bay <joshuaba...@gmail.com> wrote: > >> Hi, >> >> Could you please let me know if there is a way to get LSN of each >> transaction by directly communicating with Postgres server and NOT by >> accessing logs. >> > > > To what end? What problem are you trying to solve? > > What LSN, exactly? The LSN of the first write and xid allocation? The LSN > of the commit record? What if it's a complex commit like with prepared > xacts? > > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
[HACKERS] Way to access LSN (for each transaction) by directly talking to postgres?
Hi, Could you please let me know if there is a way to get LSN of each transaction by directly communicating with Postgres server and NOT by accessing logs. Thanks! Joshua
Re: [COMMITTERS] [HACKERS] Logical decoding
Thanks a lot for your help!! On Tue, Jul 12, 2016 at 4:42 PM, FabrÃzio de Royes Mello < fabriziome...@gmail.com> wrote: > > > On Mon, Jul 11, 2016 at 2:13 AM, Michael Paquier < > michael.paqu...@gmail.com> wrote: > > > > On Mon, Jul 11, 2016 at 2:03 PM, Craig Ringer <cr...@2ndquadrant.com> > wrote: > > > On 9 July 2016 at 01:57, Joshua Bay <joshuaba...@gmail.com> wrote: > > >> and where are this code is in the codebase? > > > > > > src/backend/replication/logical/* > > > src/backend/replication/walsender.c > > > src/backend/access/transam/xlogreader.c > > > src/include/access/xlogreader.h > > > src/include/replication/output_plugin.h > > > contrib/test_decoding/ > > > doc/src/sgml/logicaldecoding.sgml > > > > Some other references: > > https://wiki.postgresql.org/images/7/77/Fosdem-2015-logical-decoding.pdf > > > > And some other examples of plugins: > > https://github.com/leptonix/decoding-json > > https://github.com/xstevens/decoderbufs > > https://github.com/confluentinc/bottledwater-pg (for kafka) > > https://github.com/michaelpq/pg_plugins/tree/master/decoder_raw (wrote > this one) > > > > Nice, also we have wal2json [1]. > > Regards, > > > [1] https://github.com/eulerto/wal2json > > -- > FabrÃzio de Royes Mello > Consultoria/Coaching PostgreSQL > >> Timbira: http://www.timbira.com.br > >> Blog: http://fabriziomello.github.io > >> Linkedin: http://br.linkedin.com/in/fabriziomello > >> Twitter: http://twitter.com/fabriziomello > >> Github: http://github.com/fabriziomello >
[HACKERS] Logical decoding
Hi, I have a question about logical decoding of Postgres. where are the entry points to logical decoding? Specifically, we want to know whether logical decoding happens immediately after commit, or whether there is a polling thread that scans the Write Ahead Log and then dumps to the special table? and where are this code is in the codebase? Thanks, Joshua
