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

On Sun, Dec 9, 2018 at 8:43 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 12/9/18 8:03 AM, Mike Lissner wrote:
> >
> > The above seems to be the crux of the problem, how did NULL get into
> > the
> > column data?
> >
> >
> > I agree. My queries are generated by Django (so I never write SQL
> > myself), but:
> >
> > - the column has always been NOT NULL for its entire lifetime
>
> The lifetime being since the migration did this?:
>
> ALTER TABLE "search_docketentry" ADD COLUMN "recap_sequence_number"
> varchar(50) DEFAULT '' NOT NULL;
> ALTER TABLE "search_docketentry" ALTER COLUMN "recap_sequence_number"
> DROP DEFAULT;
>

"Lifetime" meaning that there was never a time when this column allowed
nulls.

> Also does the column recap_sequence_number appear in any other tables.
> Just wondering if the error was on another table?
>

Good idea, but no. This column only exists in one table.

> > - we don't send *any* SQL commands to the replica yet, so that's not a
> > factor (for now it's just a live backup)
> > - the publisher now has a NOT NULL constraint on that column. I never
> > had to clear out null values to put it in place. I assume that if that
>
> This part confuses me. You seem to imply that the column existed before
> the migration and you just added a NOT NULL constraint. The migration
> shows the column being created with a NOT NULL constraint.
>

Sorry, what I mean is that if *somehow* the master had null values in that
column at some point, which I don't know how would even be possible because
it only came into existence with the command above — if somehow that
happened, I'd know, because I wouldn't have been *able* to add a NULL
constraint without first fixing the data in that column, which I never did.

My contention is that for all these reasons, there should *never* have been
a null value in that column on master.

>
> > column ever had a null value and I tried to run a DDL to add a null
> > constraint, the DDL would have failed, right?
> >
> > Something feels wrong here, the more I think about it.
>
> A start would be to figure out what generated?:
>
> failing row contains (48064261, 2018-12-07 04:48:40.388377+00,
> 2018-12-07 04:48:40.388402+00, null, 576, , 4571214, null, null)
>

Yes, I completely agree. I can't think of any way that that should have
ever been created.

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