On Mon, Feb 12, 2024 at 3:55 AM Peter Eisentraut wrote:
> Have you checked that the generated queries can use indexes and have
> suitable performance? Do you have example execution plans maybe?
This took longer than expected, but I wrote a long blog post about it
here:
https://illuminatedcomput
On Thu, Sep 5, 2024 at 5:09 AM Peter Eisentraut wrote:
>
> On 07.08.24 22:54, Paul Jungwirth wrote:
> > Here are some fixes based on outstanding feedback (some old some new).
>
> I have studied your patches v39-0001 through v39-0004, which correspond
> to what had been reverted plus the new empty
On Thu, Jun 27, 2024 at 11:31 PM Peter Eisentraut wrote:
> Here is an updated patch for this.
I took a look at this. I added some tests and found a few that give
the wrong result (I believe). The new tests are included in the
attached patch, along with the results I expect. Here are the
failures:
On Fri, May 17, 2024 at 12:41 PM Tomas Vondra
wrote:
> I've been looking at GiST to see if there could be a good way to do
> parallel builds - and there might be, if the opclass supports sorted
> builds, because then we could parallelize the sort.
>
> But then I noticed we support this mode only f
Hello,
Here is a patch to improve rowcount estimates for
`UNNEST(some_array_column)`. Today we hard code this to 10, but we
have statistics about array size, so it's easy to use them.
I've seen plans where this would make a difference. If the array has
only 1 or 2 elements, then overestimating th
On Mon, Nov 6, 2023 at 11:07 PM jian he wrote:
> +
> + In a temporal foreign key, the delete/update will use
> + FOR PORTION OF semantics to constrain the
> + effect to the bounds being deleted/updated in the referenced row.
> +
>
> The first "para" sho
Thank you for continuing to review this submission! My changes are in
the v18 patch I sent a few days ago. Details below.
On Sun, Oct 29, 2023 at 5:01 PM jian he wrote:
> * The attached patch makes foreign keys with PERIOD fail if any of the
> foreign key columns is "generated columns".
I don't
On Fri, Sep 8, 2023 at 2:35 AM jian he wrote:
>
> hi.
> the following script makes the server crash (Segmentation fault).
> [snip]
>
> ALTER TABLE temporal_fk_rng2rng
> ADD CONSTRAINT temporal_fk_rng2rng_fk
> FOREIGN KEY (parent_id, PERIOD valid_at)
> REFERENCES temporal_rng
> on update se
On Mon, Jul 10, 2023 at 8:06 AM Paul A Jungwirth
wrote:
>
> On Mon, Jul 10, 2023 at 7:05 AM Peter Eisentraut wrote:
> > I'm not sure what value we would get from testing this with btree_gist,
> > but if we wanted to do that, then adding a new test file to the
> > bt
On Mon, Jul 10, 2023 at 7:05 AM Peter Eisentraut wrote:
> I'm not sure what value we would get from testing this with btree_gist,
> but if we wanted to do that, then adding a new test file to the
> btree_gist sql/ directory would seem reasonable to me.
>
> (I would make the test a little bit bigge
errmsg("cannot match partition key
> to index on column \"%s\" using non-equal operator \"%s\".",
> + NameStr(att->attname),
> get_opname(indexInfo->ii_ExclusionOps[j];
>
> co
On Tue, Jul 4, 2023 at 12:33 AM Richard Guo wrote:
>
> Rebase the patch on HEAD as cfbot reminds.
All of this seems good to me. I can reproduce the problem, tests pass,
and the change is sensible as far as I can tell.
One adjacent thing I noticed is that when we renamed "Result Cache" to
"Memoiz
On Thu, Jan 6, 2022 at 6:45 AM Vik Fearing wrote:
>
> On 1/5/22 11:03 PM, Corey Huinker wrote:
> >
> > There was similar work being done for system periods, which are a bit
> > simpler but require a side (history) table to be created.
>
> This is false. SYSTEM_TIME periods do not need any kind of
On Wed, Jan 5, 2022 at 8:07 AM Peter Eisentraut
wrote:
>
> This patch set looks very interesting.
Thank you for the review!
I'll work on your feedback but in the meantime here are replies to
your questions:
> I'm confused about how to query tables based on application time
> periods. Online, I
On Sat, Sep 18, 2021 at 5:46 PM Corey Huinker
wrote:
> SYSTEM_TIME seems to allow for DATE values in the start_time and end_time
> fields,
> though I cannot imagine how that would ever be practical, unless it were
> somehow
> desirable to reject subsequent updates within a 24 hour timeframe.
>
I
Hi Corey,
Thanks for all the good questions!
> 1. This patch creates a pg_period catalog table, whereas the system
> versioning relies on additions to pg_attribute to identify the start/end
> columns. Initially I thought this was because it was somehow possible to have
> multiple application p
On Thu, Jul 15, 2021 at 6:21 AM Ibrar Ahmed wrote:
> Based on last comments of Paul and David S I am changing the status to
> "Waiting on Author".
I thought the subject was quite out of date, so I sent my last patch
here:
https://www.postgresql.org/message-id/CA%2BrenyUApHgSZF9-nd-a0%2BOPGharLQ
On Tue, May 18, 2021 at 3:00 PM Paul A Jungwirth
wrote:
>
> I suspect I'm missing something in the
> analyze/rewriting phase, because I get this error:
>
> ERROR: no value found for parameter 1
> . . .
>
> I was hoping that transformExpr would do everything I nee
Hello hackers,
I'm wrapping up a patch that adds SQL:2011 FOR PORTION OF syntax and
then uses it to implement CASCADE in temporal foreign keys. The FKs
are implemented as triggers, like ordinary FKs, and the trigger
function makes a call through SPI that does `UPDATE %s FOR PORTION OF
%s FROM $%d
On Thu, Apr 8, 2021 at 7:22 AM David Steele wrote:
>
> Paul, you can submit to the next CF when you are ready with a new patch.
Thanks David! I've made a lot of progress but still need to finish
support for CASCADE on temporal foreign keys. I've been swamped with
other things, but hopefully I can
On Sun, Jul 5, 2020 at 12:11 PM Paul A Jungwirth
wrote:
>
> Just knowing that arrays are
> something we do this for is enough to hunt for clues, but if anyone
> can point me more directly to code that will help me do it for
> multiranges, I'd be appreciative.
It looks lik
On Sat, Apr 11, 2020 at 9:36 AM Paul A Jungwirth
wrote:
> Btw I'm working on typanalyze + selectivity, and it seems like the
> test suite doesn't run those things?
Nevermind, I just had to add `analyze numrange_test` to
src/test/regress/sql/rangetypes.sql. :-) Do you want a se
On Thu, Mar 19, 2020 at 1:42 PM Alvaro Herrera wrote:
>
> On 2020-Mar-16, Paul A Jungwirth wrote:
>
> > On Sat, Mar 14, 2020 at 11:13 AM Paul A Jungwirth
> > wrote:
> > > I think that should fix the cfbot failure.
> >
> > I saw this patch
On Fri, Mar 13, 2020 at 2:39 PM Alvaro Herrera wrote:
> Here's the rebased version.
>
> I just realized I didn't include the API change I proposed in
> https://postgr.es/m/20200306200343.GA625@alvherre.pgsql ...
Thanks for your help with this Alvaro!
I was just adding your changes to my own bran
On Wed, Mar 11, 2020 at 4:39 PM Paul A Jungwirth
wrote:
>
> On Sat, Mar 7, 2020 at 12:20 PM Tom Lane wrote:
> > Alvaro Herrera writes:
> > > [ v11 patches ]
> > The cfbot isn't too happy with this; it's getting differently-ordered
> > results th
On Thu, Mar 12, 2020 at 5:38 AM Alvaro Herrera wrote:
> ... thinking about gist+spgist, I think they could be written
> identically to those for ranges, using the lowest (first) lower bound
> and the higher (last) upper bound.
>
> ... thinking about selectivity, I think the way to write that is to
Thanks everyone for offering some thoughts on this!
Tom Lane wrote:
> have you given any thought to just deciding that ranges and
> multiranges are the same type?
I can see how it might be nice to have just one type to think about.
Still I think keeping them separate makes sense. Other folks hav
On Sun, Jan 19, 2020 at 4:38 PM Tom Lane wrote:
> True for casts involving concrete types, mainly because we'd like
> the identity "value::typename == typename(value)" to hold without
> too much worry about whether the latter is a plain function call
> or a special case. Not sure whether it makes
On Sun, Jan 19, 2020 at 12:10 AM Pavel Stehule wrote:
> Now, I think so name "anymultirange" is not good. Maybe better name is just
> "multirange"
Are you sure? This function exists to be a cast to an anymultirange,
and I thought the convention was to name cast functions after their
destination
On Sat, Jan 18, 2020 at 7:20 AM Pavel Stehule wrote:
> Can be nice to have a polymorphic function
>
> multirange(anymultirange, anyrange) returns anymultirange. This functions
> should to do multirange from $2 to type $1
>
> It can enhance to using polymorphic types and simplify casting.
Thanks
On Fri, Dec 20, 2019 at 10:19 AM Pavel Stehule wrote:
> I had a talk with Paul about possible simplification of designed operators.
> Last message from Paul was - he is working on new version.
Thanks Alvaro & Pavel for helping move this forward. I've added the
casts but they aren't used automati
On Thu, Nov 21, 2019 at 9:21 PM Pavel Stehule wrote:
> I though about it, and I think so cast from multirange to range is useless,
> minimally it should be explicit.
I agree: definitely not implicit. If I think of a good reason for it
I'll add it, but otherwise I'll leave it out.
> On second ha
On Tue, Nov 19, 2019 at 1:17 AM Pavel Stehule wrote:
> Hi
> I tested last patches. I found some issues
Thank you for the review!
> 1. you should not to try patch catversion.
I've seen discussion on pgsql-hackers going both ways, but I'll leave
it out of future patches. :-)
> 2. there is warnin
On Wed, Nov 6, 2019 at 9:31 PM Paul A Jungwirth
wrote:
> I've also added some progress on adding FOR PORTION OF to UPDATE and DELETE
> (mostly UPDATE).
I could use some guidance on where in the query-processing pipeline I
should implement some things here. Basically if you say
U
On Thu, Oct 24, 2019 at 8:45 AM Nikita Glukhov wrote:
> You can simply use jsonb_object_agg() to build a jsonb object from a sequence
> of transformed key-value pairs:
I've even used that function before. :-) I tried
finding it on the JSON functions page but couldn't, so I thought maybe
I was go
On Thu, Oct 24, 2019 at 8:52 AM Tom Lane wrote:
> I think a potential problem is creation of ambiguity where there was
> none before.
I agree that's not nice, and it seems like a new name might be better.
> Also, as the prototype implementation shows, it's not like you
> can't get this functiona
On Mon, Sep 16, 2019 at 5:28 AM Amit Kapila wrote:
> I don't see this function on the master branch. Is this function name
> correct? Are you looking at some different branch?
Sorry about that! You're right, I was on my multirange branch. But I
see the same thing on latest master (but calling h
On Sat, Sep 14, 2019 at 5:13 AM Amit Kapila wrote:
> In general, the hash_range is covered by some of the existing test,
> but I don't which test. See the code coverage report here:
> https://coverage.postgresql.org/src/backend/utils/adt/rangetypes.c.gcov.html
Thanks! I did some experimenting, a
Hello,
I noticed the tests for range types do this:
create table numrange_test2(nr numrange);
create index numrange_test2_hash_idx on numrange_test2 (nr);
Does that need a `using hash`? It seems like that's the intention. We
only use that table for equality comparisions. The script already
creat
On Thu, Sep 5, 2019 at 10:15 AM Jeff Davis wrote:
>
> On Sun, 2019-09-01 at 06:26 -0700, Paul A Jungwirth wrote:
> > @+ and @- and @* (I dunno why but I kind of like it. We already have
> > @> and <@.)
>
> I think I like this proposal best; it reminds me of perl. Tho
> > Btw I have working multirange_{send,recv,in,out} now. . . .
Just about all the other operators are done too, but I wonder what
symbols people like for union and minus? Range uses + for union. I
have working code and tests that adds this:
r + mr = mr
mr + r = mr
mr + mr = mr
But I would like
On Tue, Aug 27, 2019 at 8:52 AM Paul A Jungwirth
wrote:
>
> On Tue, Aug 27, 2019 at 8:23 AM Tom Lane wrote:
> > I seem to recall that we discussed this exact point during development
> > of the range feature, and concluded that this was the behavior we
> > wanted, i
On Tue, Aug 27, 2019 at 8:23 AM Tom Lane wrote:
> > resolve_generic_type(ANYARRAYOID, x, ANYRANGEOID) - this will return
> > an array of the *range type*, but that contracts the normal
> > relationship between anyelement and anyrange. It should return an
> > array of the range's element type.
>
>
Hello,
I was looking at resolve_generic_type to add anymultirange support,
and the range logic doesn't seem correct to me. This function takes 3
type Oids:
- declared_type is the declared type of a function parameter whose
actual type it would like to deduce.
- context_{declared,actual}_type are
On Tue, Aug 20, 2019 at 10:33 PM Jeff Davis wrote:
> > Is there any historical discussion around
> > typemods on range types?
>
> I did find a few references:
Thanks for looking those up! It's very interesting to see some of the
original discussion around range types.
Btw this is true of so much
On Mon, Jul 8, 2019 at 9:46 AM Paul A Jungwirth
wrote:
> - A multirange type is an extra thing you get when you define a range
> (just like how you get a tstzrange[]). Therefore
I've been able to make a little more progress on multiranges the last
few days, but it reminded me
On Fri, Aug 2, 2019 at 1:49 PM Ibrar Ahmed wrote:
> I did some clean-up on this patch. I have also refactored a small portion of
> the code
> to reduce the footprint of the patch. For simplicity, I have divided the
> patch into 6
> patches, now it is easy to review and debug.
> Please follow the
On Tue, Jul 23, 2019 at 3:32 PM Alvaro Herrera wrote:
> Just checking if you've had a chance to make progress on this.
Not a lot. :-) But I should have more time for it the next few weeks
than I did the last few. I do have some code for creating concrete
multirange types (used when you create a c
On Tue, Jul 9, 2019 at 12:24 PM Pavel Stehule wrote:
> út 9. 7. 2019 v 21:10 odesílatel Pavel Stehule
> napsal:
>> I afraid so with generic multiragetype there lot of array infrastructure
>> will be duplicated
>
> on second hand - it is true so classic array concat is not optimal for set of
>
On Tue, Jul 9, 2019 at 12:02 PM Jeff Davis wrote:
> > - Multirange in/out work just like arrays, e.g. '{"[1,3)", "[5,6)"}'
>
> It would be cool to have a better text representation. We could go
> simple like:
>
>'[1,3) [5,6)'
Will that work with all ranges, even user-defined ones? With a
tstz
On Tue, Jul 9, 2019 at 8:51 AM David Fetter wrote:
> > - A multirange type is an extra thing you get when you define a range
> > (just like how you get a tstzrange[]). Therefore
> > - I don't need separate commands to add/drop multirange types. You get
> > one when you define a range type, and
On Mon, Jul 8, 2019 at 10:09 PM Pavel Stehule wrote:
> po 8. 7. 2019 v 18:47 odesílatel Paul A Jungwirth
> napsal:
> I am not against a multirange type, but I miss a explanation why you
> introduce new kind of types and don't use just array of ranges.
Hi Pavel, I'm sorr
On Sat, Jul 6, 2019 at 12:13 PM Jeff Davis wrote:
>
> On Fri, 2019-07-05 at 09:58 -0700, Paul A Jungwirth wrote:
> > user-defined range types. So how about I start on it and see how it
> > goes? I expect I can follow the existing code for range types pretty
> > closely,
On Thu, Jul 4, 2019 at 11:44 AM Alvaro Herrera wrote:
> I think that the functionality in your patch is already integrated in
> Paul's patch for temporal PK/FK elsewhere ... is that correct, or is
> this patch orthogonal to that work?
Hi Vik, I wasn't aware that you had moved your work over to an
On Fri, Jul 5, 2019 at 10:57 AM Paul A Jungwirth
wrote:
> I take it that a multirange contains of *disjoint* ranges,
*consists* of. :-)
On Fri, Jul 5, 2019 at 10:45 AM David Fetter wrote:
> If I understand the cases correctly, the combination of covering_range
> and multi_range types covers all cases. To recap, covering_range_agg
> assigns a weight, possibly 0, to each non-overlapping sub-range. A
> cast from covering_range to mu
On Mon, Jul 1, 2019 at 3:38 PM Jeff Davis wrote:
>
> The patch is implicitly introducing the concept of
> a "multirange" (in this case, an array of ranges),
I meant to say before: this patch always returns a sorted array, and I
think a multirange should always act as if sorted when we stringify i
On Fri, Jul 5, 2019 at 4:31 AM Pavel Stehule wrote:
> The first issue is unstable regress tests - there is a problem with opr_sanity
I would prefer to avoid needing to add anything to opr_sanity really.
A multirange would let me achieve that I think. But otherwise I'll add
the ordering. Thanks!
On Thu, Jul 4, 2019 at 11:34 AM Alvaro Herrera wrote:
>
> I noticed that this patch has a // comment about it segfaulting. Did
> you ever figure that out? Is the resulting code the one you intend as
> final?
Thanks for the review! I haven't revisited it but I'll see if I can
track it down. I co
On Mon, Jul 1, 2019 at 3:38 PM Jeff Davis wrote:
>
> For getting into core though, it should be a more complete set of
> related operations. The patch is implicitly introducing the concept of
> a "multirange" (in this case, an array of ranges), but it's not making
> the concept whole.
>
> What els
On Wed, May 8, 2019 at 9:54 PM Paul A Jungwirth
wrote:
> Here is an initial patch. I'd love to have some feedback! :-)
Here is a v2 rebased off current master. No substantive changes, but
it does fix one trivial git conflict.
After talking with David in Ottawa and hearing a good use-c
On Sun, May 26, 2019 at 7:49 PM Michael Paquier wrote:
> Well, the point I would like to outline is that section 5.11.2 about
> declarative partitioning and 5.11.3 about partitioning with
> inheritance treat about two separate, independent partitioning
> methods. So removing the paragraph from th
On Wed, May 22, 2019 at 8:06 PM Michael Paquier wrote:
> Looking closer, you are adding that:
> +
> +
> + While primary keys are supported on tables using inheritance
> + for partitioning, foreign keys referencing these tables are not
> + supported. (Foreign key refer
On Mon, May 20, 2019 at 10:22 PM Michael Paquier wrote:
> If you could clean up the CF entry, and keep only the open item in the
> list, that would be nice. Thanks.
I withdrew the CF entry; hopefully that is all that needs to be done,
but if I should do anything else let me know.
Thanks,
Paul
On Mon, May 20, 2019 at 10:18 PM Michael Paquier wrote:
> Could you define what is an "inheritance-partitioned" table? I know
> of partitioned tables, inherited tables and tables which make use
> of inheritance for partitioning (hence Inheritance Partitioning), but
> the paragraph you are adding
On Mon, May 20, 2019 at 9:59 PM Amit Langote
wrote:
> Would you like me to edit the wiki to add this to open items?
That would be helpful for sure. Thanks!
Paul
On Mon, May 20, 2019 at 9:44 PM Amit Langote
wrote:
> This sounds more like an open item to me [1], not something that have to
> be postponed until the next CF.
>
> [1] https://wiki.postgresql.org/wiki/PostgreSQL_12_Open_Items
Oh sorry, I already created the CF entry. Should I withdraw it? I'll
a
Hello,
I posted this to the "clean up docs for v12" thread and it was
suggested I make a new thread instead, so here it is. Sorry for the
extra noise! :-)
I noticed the docs at
https://www.postgresql.org/docs/devel/ddl-partitioning.html still say
you can't create a foreign key referencing a parti
On Mon, May 20, 2019 at 9:36 PM Amit Langote
wrote:
> Thanks for the patch. To avoid it getting lost in the discussions of this
> thread, it might be better to post the patch to a separate thread.
Okay, I'll make a new thread and a new CF entry. Thanks!
Hello,
I'm sorry if this is the wrong place for this or it's already been
covered (I did scan though this whole thread and a couple others), but
I noticed the docs at
https://www.postgresql.org/docs/devel/ddl-partitioning.html still say
you can't create a foreign key referencing a partitioned tabl
On Mon, May 6, 2019 at 4:21 PM Paul Jungwirth
wrote:
> I need to write some docs and do
> some cleanup and I'll have a CF entry.
Here is an initial patch. I'd love to have some feedback! :-)
One challenge was handling polymorphism, since I want to have this:
anyrange[] range_agg(anyrange, b
On Tue, Mar 5, 2019 at 12:35 AM David Steele wrote:
> I have marked this patch as targeting PG13 since it is clearly not
> material for PG12. I also added you as the patch author.
Thanks David! Targeting PG13 was my intention, so sorry if I messed up
the commitfest entry.
Here is a new patch re
On Fri, Nov 23, 2018 at 3:41 PM Paul A Jungwirth
wrote:
> Here is a patch for my progress on this so far.
Well this is embarrassing, but my last patch used the mistaken syntax
`PRIMARY KEY (cols, WITHOUT OVERLAPS col)`. Here is a new patch which
uses the correct syntax `PRIMARY KEY (cols,
Here is a patch for my progress on this so far. I'd love some comments
on the general approach, as I've never contributed anything this
involved before. It's not ready for a commitfest, but it would help me
to have some feedback. There are TODO comments with my major
questions.
This patch lets you
On Mon, Oct 29, 2018 at 10:13 AM Paul A Jungwirth
wrote:
> But if those assumptions
> don't hold the simple implementation of 2x AT TIME ZONE might not work
> correctly. I can try it out and see
Okay it looks to me that my suggestion won't work for the general
case. Basica
On Mon, Oct 29, 2018 at 9:58 AM Paul A Jungwirth
wrote:
> This patch adds a 3-arg version for date_trunc(text, timestamptz,
> text). I'm saying it would be nice to also have a 3-arg version for
> date_trunc(text, timestamp, text). It would do the same thing, except
> take a tim
On Mon, Oct 29, 2018 at 9:53 AM Tom Lane wrote:
> We already have date_trunc() for timestamp-without-tz, so I'm a little
> confused as to what you think is/would be missing.
This patch adds a 3-arg version for date_trunc(text, timestamptz,
text). I'm saying it would be nice to also have a 3-arg v
On Mon, Oct 29, 2018 at 9:40 AM Vik Fearing wrote:
> I don't necessarily want to
> display any dates, I could be grouping and aggregating.
I can attest that this patch would be really nice functionality to
have. Grouping is an often-overlooked case where timezones matter.
When grouping by day (e.
On Sun, Oct 21, 2018 at 12:11 PM Heikki Linnakangas wrote:
> On 21/10/2018 21:17, Paul A Jungwirth wrote:
> > 3. Build our own abstractions on top of ranges, and then use those to
> > implement PERIOD-based features.
> +1 on this approach. I think [7] got the model right. If
Hello,
I'm interested in contributing some temporal database functionality to
Postgres, starting with temporal primary and foreign keys. I know some
other folks nearby interested in helping out, too. But before we begin
I'd like to ask the community about complying with the SQL:2011
standard [1] f
On Tue, Jun 5, 2018 at 12:47 PM, Paul A Jungwirth
wrote:
> Also, this may not be very helpful, but I started an extension to
> support temporal foreign keys here:
>
> https://github.com/pjungwir/time_for_keys
>
> It uses intervals, not periods, but maybe you can steal some ide
On Sat, May 26, 2018 at 1:56 PM, Vik Fearing wrote:
> SQL:2011 introduced the concept of a "period". It takes two existing columns
> and basically does the same thing as our range types except there is no new
> storage. I believe if Jeff Davis had given us range types a few years later
> than he
On Fri, May 11, 2018 at 4:48 AM, Paul A Jungwirth
wrote:
> I'm traveling and can't write much more at the moment, but I'll try to
> reply more fully in a week or two.
Sorry it took awhile to continue this discussion! If people are
interested in implementing temporal features
On Tue, May 8, 2018 at 7:13 PM, Peter Eisentraut
wrote:
> On 5/8/18 11:31, Tom Lane wrote:
>> Paul Howells writes:
>>> Has there been or is there any current effort to implement SQL:2011
>>> valid-time support in Postgres?
>>
>> Searching the archives, I can only find "valid-time" appearing in th
84 matches
Mail list logo
