Re: lag() default value ignored for some window partition depending on table records count? - Mailing list pgsql-bugs

From Tom Lane
Subject Re: lag() default value ignored for some window partition depending on table records count?
Date
Msg-id 276587.1651012554@sss.pgh.pa.us
Whole thread Raw
In response to lag() default value ignored for some window partition depending on table records count?  (Loïc Revest <l.revest@apc.fr>)
List pgsql-bugs
=?UTF-8?B?TG/Dr2MgUmV2ZXN0?= <l.revest@apc.fr> writes:
> Long story short:
> - We track history data in a partitioned table which boils down to  "key
> fields + a JSONB data column", the latter being a list of date/numeric
> values pair ;
> - For every of this date/numeric couple within their "window partitioning",
> we need to determine the preceding value, thus the use of lag(<value>, 1,
> 0::numeric), since it's relevant for us here to get the very first record
> of the partition having "0.00" as its preceding value;
> - Except that the "first" row of some partitions gets NULL instead of
> 0::numeric as the result of lag(<value>, 1, 0::numeric), while other get
> 0::numeric as expected;

Hm ...

> Attached is the test case database script we've been able to reproduce the
> "issue" one hundred percent of the time on, but without the data - even
> after compression this weights ∼8.5Mb. Data that I'd happily share would
> anyone points me towards the customary way to do it here with such "big" a
> file.

With no data, this is pretty pointless.  Maybe you could make a script
to fill the tables with some dummy data that reproduces the problem?

            regards, tom lane



pgsql-bugs by date:

Previous
From: Loïc Revest
Date:
Subject: lag() default value ignored for some window partition depending on table records count?
Next
From: "David G. Johnston"
Date:
Subject: Re: lag() default value ignored for some window partition depending on table records count?