Re: JDBC: logical replication and LSN feedback

OK,

good news is I can replicate this on 10RC1

I think this behaviour is by design.

The call is to getLastReceivedLSN(). In fact this is the last received LSN,
The fact that you have received it already is some what confusing but this
is the last one. I can actually see a use case for this where you start the
program and want to restart where you left off.

As for the confusion of lastReceived and lastFlushed below is the protocol
message. My take on this is that we should consider lastFlushed as the most
important. So lets say the client is running. We set lastReceived in the
loop and then somewhere else we flush the data. At some point the client
crashes. When we start we should ask the server for lastFlushed as our
current starting point as that is what we have committed to whichever
process we are sending the data to. Think about the situation where you
send the data into a queue and have not received acknowledgment that the
subscriber on the other end has received the information. You may want to
send it again.

Standby status update (F)
Byte1('r')

Identifies the message as a receiver status update.
Int64

The location of the last WAL byte + 1 received and written to disk in the
standby.
Int64

The location of the last WAL byte + 1 flushed to disk in the standby.
Int64

The location of the last WAL byte + 1 applied in the standby.
Int64

The client's system clock at the time of transmission, as microseconds
since midnight on 2000-01-01.
Byte1

If 1, the client requests the server to reply to this message immediately.
This can be used to ping the server, to test if the connection is still
healthy.

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 21 September 2017 at 08:31, Yason TR <tryasontr(at)gmail(dot)com> wrote:

> Hi Dave,
>
> I am currently on version 9.5.8.
>
> About setAppliedLSN vs. setFlushedLSN: I'll try to find out which one
> is for logical.
>
> Thank you. Kind regards,
>
> Yason TR
>
> On Thu, Sep 21, 2017 at 5:28 PM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
> >
> >
> >
> >
> > On 21 September 2017 at 06:50, Yason TR <tryasontr(at)gmail(dot)com> wrote:
> >>
> >> Hi,
> >>
> >> This is a follow-up from my previous question
> >>
> >> (https://www.postgresql.org/message-id/trinity-903b9111-
> dde7-4901-8130-86924336c456-1505912478894%403c-app-mailcom-bs15).
> >>
> >> I created a git to guide my question, see
> >> https://github.com/yasontr/pg-jdbc-logical-decoding.
> >>
> >> Please follow the install steps on this page. After this, you can run
> >> the application with Maven. This application listens to a replication
> >> slot. See
> >> https://github.com/yasontr/pg-jdbc-logical-decoding/blob/
> master/src/main/java/yasontr/pg_jdbc_logical_decoding/Main.java
> >> for the code of the application.
> >>
> >> The problem I am seeing can be reproduced as following:
> >>
> >> 1) start the application
> >> 2) insert a dummy row to the test table: insert into test(key, value)
> >> values (1, 'test');
> >> 3) now you see in the console:
> >>
> >> event:
> >> {"change":[{"kind":"insert","schema":"public","table":"
> test","columnnames":["key","value"],"columntypes":["int4",
> "text"],"columnvalues":[1,"test"]}]}
> >> last received LSN: LSN{1A/AD2EEB0}
> >>
> >> 4) close the application
> >> 5) start the application again
> >> 6) now you see again the same event again in the console:
> >>
> >> event:
> >> {"change":[{"kind":"insert","schema":"public","table":"
> test","columnnames":["key","value"],"columntypes":["int4",
> "text"],"columnvalues":[1,"test"]}]}
> >> last received LSN: LSN{1A/AD2EEB0}
> >>
> >> As you can see in the code, I am confirming the event with its LSN:
> >>
> >> stream.setAppliedLSN(stream.getLastReceiveLSN());
> >> stream.setFlushedLSN(stream.getLastReceiveLSN());
> >> stream.forceUpdateStatus();
> >
> >
> > Pretty sure you only need one of them. I have to check again which one.
> One
> > if for binary replication and the other is for logical.
> >
> > as for why you are getting the event twice. I don't know but am equally
> > interested in finding out why.
> >
> > What version of postgres is this on ?
> >
> >
> > Dave Cramer
> >
> > davec(at)postgresintl(dot)com
> > www.postgresintl.com
>