From: Tom Lane <tgl@sss.pgh.pa.us>
To: Loïc Revest <l.revest@apc.fr>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: lag() default value ignored for some window partition depending on table records count?
Date: Tue, 26 Apr 2022 18:35:54 -0400
Message-ID: <276587.1651012554@sss.pgh.pa.us> (raw)
In-Reply-To: <CABkOrLve_F5PyQNL+BhnUCT1FRVF5QV6mfVAuhrharF_Ka+9eQ@mail.gmail.com>
References: <CABkOrLve_F5PyQNL+BhnUCT1FRVF5QV6mfVAuhrharF_Ka+9eQ@mail.gmail.com>
=?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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, l.revest@apc.fr, pgsql-bugs@lists.postgresql.org
Subject: Re: lag() default value ignored for some window partition depending on table records count?
In-Reply-To: <276587.1651012554@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox