Re: BUG #13846: INSERT ON CONFLICT consumes sequencersonconflicts

Hi,

On 2016-01-06 15:00:17 -0700, Paul wrote:
> I’m looking at math more like a single Fact table having 500 million
> records, with 10 dimension columns. If INTs were used for the
> dimension columns, that’s 20GB.

> If I had to change those dimension columns to BIGINTs, that’s
> 40GB. This can impact how much you can fit into server memory, where
> olaps like to live, and just slow down moving stuff around between
> memory and disk and over the network and backups, etc.

Row headers, padding & alignment makes that absolutely different in
reality. It's like a sub 20% difference.

And in pretty much all the cases with sufficient insertion rates you're
going to want bigints anyway. If there's few rows it doesn't matter
anyway.

> On a technical note, why is the following flow considered ‘fragile’?

The fragility comes from the fact that it'd only be used in a subset of
cases. E.g. if a BEFORE trigger is present the to-be-inserted rows needs
to be "materialized" to be presented to the trigger.

> 1) Evaluate only columns used in conflict_target
> a. Conflict-resolving Columns with default nextval() increment the corresponding sequencer
> i. And in this case, there were never be conflicts by definition, so
> ON CONFLICT can always be ignored

Wrong. Rows with sequences can very well conflict, there's nothing
forcing sequences to always be used.

Also note that sequence default values aren't in any way different from other
default values, and that relevant pieces of code currently don't know
whether a default value is a nextval or not.

> 2) If conflict, DO UPDATE
> a. If nextval()-defaulted column used in conflict_target, we never get here
> b. Defaults never evaluated
> 3) Else Evaluate remaining columns not used in conflict_target and INSERT
> a. Columns with nextval() increment their corresponding sequencer

Perhaps read the code, and prototype it? I can tell you that it'd be a
significant amount of work, and that I'm personally absolutely not
interested in investing significant amounts time into it. But that
doesn't have to stop *you*.

Anyway, EOD for me.