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
On Wed, Aug 3, 2016 at 12:37 PM, Joshua Bay <joshuabay93@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.
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Aug 3, 2016 at 3:00 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
On Wed, Aug 3, 2016 at 12:37 PM, Joshua Bay <joshuabay93@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 3 August 2016 at 11:37, Joshua Bay <joshuabay93@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
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 <craig@2ndquadrant.com> wrote:
Show quoted text
On 3 August 2016 at 11:37, Joshua Bay <joshuabay93@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
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.paquier@gmail.com>
wrote:
Show quoted text
On Wed, Aug 3, 2016 at 3:00 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:On Wed, Aug 3, 2016 at 12:37 PM, Joshua Bay <joshuabay93@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
On 4 August 2016 at 01:35, Joshua Bay <joshuabay93@gmail.com> wrote:
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.
Have a look at how logical decoding does it. Check out ReorderBufferCommit
in src/backend/replication/logical/reorderbuffer.c .
Or just write a trivial a logical decoding plugin that only implements the
commit callback and only emits the LSN.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Thu, Aug 4, 2016 at 3:02 AM, Joshua Bay <joshuabay93@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Sorry I forgot to reply. Thanks! using decoding plugins works great
On Wed, Aug 3, 2016 at 8:37 PM, Michael Paquier <michael.paquier@gmail.com>
wrote:
Show quoted text
On Thu, Aug 4, 2016 at 3:02 AM, Joshua Bay <joshuabay93@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