Re: Errors with schema migration and logical replication — expected?

On 12/11/18 2:21 PM, Mike Lissner wrote:
> Reupping this since it was over the weekend and looks like a bug in
> logical replication. My problems are solved, but some very weird things
> happened when doing a schema migration.
>
> On Sun, Dec 9, 2018 at 5:48 PM Mike Lissner
> <mlissner(at)michaeljaylissner(dot)com <mailto:mlissner(at)michaeljaylissner(dot)com>>
> wrote:
>
> On Sun, Dec 9, 2018 at 12:42 PM Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
>
> 1) Using psql have you verified that NOT NULL is set on that
> column on
> the publisher?
>
>
> Yes, on the publisher and the subscriber. That was my first step
> when I saw the log lines about this.
>
> 2) And that the row that failed in the subscriber is in the
> publisher table.
>
>
> Yep, it's there (though it doesn't show a null for that column, and
> I don't know how it ever could have).
>
> 3) That there are no NULL values in the publisher column?
>
>
> This on the publisher:
>
> select * from search_docketentry where recap_sequence_number is null;
>
> returns zero rows, so yeah, no nulls in there (which makes sense
> since they're not allowed).
>
> Whatever the answers to 1), 2) and 3) are the next question is:
>
> 4) Do you want/need recap_sequence_number to be NOT NULL.
>
>
> Yes, and indeed that's how it always has been.
>
> a) If not then you could leave things as they are.
>
>
> Well, I was able to fix this by briefly allowing nulls on the
> subscriber, letting it catch up with the publisher, setting all
> nulls to empty strings (a Django convention), and then disallowing
> nulls again. After letting it catch up, there were 118 nulls on the
> subscriber in this column:

So recap_sequence_number is not actually a number, it is a code?

>
> I appreciate all the responses. I'm scared to say so, but I think
> this is a bug in logical replication. Somehow a null value appeared
> at the subscriber that was never in the publisher.
>
> I also still have this question/suggestion from my first email:
>
> > Is the process for schema migrations documented somewhere beyond
> the above?

Not that I know of. It might help, if possible, to detail the steps in
the migration. Also what program you used to do it. Given that is Django
I am assuming some combination of migrate, makemigrations and/or sqlmigrate.

>
> Thank you again,
>
> Mike
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com