Re: SQL:2011 application time

2022-01-05 Thread Paul A Jungwirth
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

Re: SQL:2011 application time

2022-01-06 Thread Paul A Jungwirth
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

Re: SQL:2011 PERIODS vs Postgres Ranges?

2021-04-08 Thread Paul A Jungwirth
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

Re: range_agg

2019-09-05 Thread Paul A Jungwirth
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

range test for hash index?

2019-09-13 Thread Paul A Jungwirth
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

Re: range test for hash index?

2019-09-15 Thread Paul A Jungwirth
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

Re: range test for hash index?

2019-09-16 Thread Paul A Jungwirth
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

Re: Add json_object(text[], json[])?

2019-10-24 Thread Paul A Jungwirth
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

Re: Add json_object(text[], json[])?

2019-10-24 Thread Paul A Jungwirth
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

Re: Re: SQL:2011 PERIODS vs Postgres Ranges?

2019-03-09 Thread Paul A Jungwirth
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

Re: range_agg

2019-07-23 Thread Paul A Jungwirth
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

Re: SQL:2011 PERIODS vs Postgres Ranges?

2019-08-04 Thread Paul A Jungwirth
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

Re: range_agg

2019-08-17 Thread Paul A Jungwirth
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

Re: range_agg

2019-08-21 Thread Paul A Jungwirth
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

range bug in resolve_generic_type?

2019-08-27 Thread Paul A Jungwirth
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

Re: range bug in resolve_generic_type?

2019-08-27 Thread Paul A Jungwirth
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. > >

Re: range bug in resolve_generic_type?

2019-08-31 Thread Paul A Jungwirth
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

Re: range_agg

2019-09-01 Thread Paul A Jungwirth
> > 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

Re: SQL:2011 PERIODS vs Postgres Ranges?

2019-11-11 Thread Paul A Jungwirth
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

Re: range_agg

2019-11-19 Thread Paul A Jungwirth
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

Re: range_agg

2019-11-22 Thread Paul A Jungwirth
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

Re: range_agg

2019-12-20 Thread Paul A Jungwirth
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

Re: range_agg

2020-01-18 Thread Paul A Jungwirth
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

Re: range_agg

2020-01-19 Thread Paul A Jungwirth
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

Re: range_agg

2020-01-19 Thread Paul A Jungwirth
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

Re: range_agg

2019-05-08 Thread Paul A Jungwirth
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

Re: clean up docs for v12

2019-05-20 Thread Paul A Jungwirth
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

Re: clean up docs for v12

2019-05-20 Thread Paul A Jungwirth
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!

docs about FKs referencing partitioned tables

2019-05-20 Thread Paul A Jungwirth
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

Re: clean up docs for v12

2019-05-20 Thread Paul A Jungwirth
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

Re: docs about FKs referencing partitioned tables

2019-05-20 Thread Paul A Jungwirth
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

Re: docs about FKs referencing partitioned tables

2019-05-20 Thread Paul A Jungwirth
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

Re: clean up docs for v12

2019-05-20 Thread Paul A Jungwirth
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

Re: docs about FKs referencing partitioned tables

2019-05-23 Thread Paul A Jungwirth
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

Re: docs about FKs referencing partitioned tables

2019-05-28 Thread Paul A Jungwirth
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

SQL:2011 PERIODS vs Postgres Ranges?

2018-10-21 Thread Paul A Jungwirth
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

Re: SQL:2011 PERIODS vs Postgres Ranges?

2018-10-21 Thread Paul A Jungwirth
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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Paul A Jungwirth
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.

Re: date_trunc() in a specific time zone

2018-10-29 Thread Paul A Jungwirth
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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Paul A Jungwirth
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

Re: date_trunc() in a specific time zone

2018-10-29 Thread Paul A Jungwirth
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

Re: SQL:2011 PERIODS vs Postgres Ranges?

2018-11-23 Thread Paul A Jungwirth
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

Re: SQL:2011 PERIODS vs Postgres Ranges?

2018-11-23 Thread Paul A Jungwirth
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,

Re: SQL:2011 application time

2023-11-18 Thread Paul A Jungwirth
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

Re: SQL:2011 application time

2023-11-18 Thread Paul A Jungwirth
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

Improve rowcount estimate for UNNEST(column)

2023-11-25 Thread Paul A Jungwirth
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

Re: SQL:2011 application time

2023-09-08 Thread Paul A Jungwirth
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

Re: range_agg

2020-03-11 Thread Paul A Jungwirth
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

Re: range_agg

2020-03-13 Thread Paul A Jungwirth
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

Re: range_agg

2020-03-13 Thread Paul A Jungwirth
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

Re: range_agg

2020-03-14 Thread Paul A Jungwirth
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

Re: range_agg

2020-03-19 Thread Paul A Jungwirth
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

Supporting $n parameters in new syntax

2021-05-18 Thread Paul A Jungwirth
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

Re: Supporting $n parameters in new syntax

2021-05-18 Thread Paul A Jungwirth
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

Re: SQL:2011 application time

2021-09-13 Thread Paul A Jungwirth
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

Re: SQL:2011 application time

2021-10-01 Thread Paul A Jungwirth
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

Re: SQL:2011 PERIODS vs Postgres Ranges?

2021-07-15 Thread Paul A Jungwirth
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

Re: Exclusion constraints on partitioned tables

2023-07-08 Thread Paul A Jungwirth
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

Re: Exclusion constraints on partitioned tables

2023-07-10 Thread Paul A Jungwirth
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

Re: Exclusion constraints on partitioned tables

2023-07-10 Thread Paul A Jungwirth
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

Re: allow sorted builds for btree_gist

2024-05-17 Thread Paul A Jungwirth
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

Re: Check lateral references within PHVs for memoize cache keys

2023-07-07 Thread Paul A Jungwirth
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

Re: SQL:2011 application time

2024-09-05 Thread Paul A Jungwirth
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

Re: SQL:2011 application time

2024-09-15 Thread Paul A Jungwirth
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

Re: Support LIKE with nondeterministic collations

2024-07-26 Thread Paul A Jungwirth
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:

Re: range_agg

2020-07-05 Thread Paul A Jungwirth
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

Re: range_agg

2020-04-11 Thread Paul A Jungwirth
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

Re: range_agg

2019-06-16 Thread Paul A Jungwirth
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

Re: range_agg

2019-07-05 Thread Paul A Jungwirth
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

Re: range_agg

2019-07-05 Thread Paul A Jungwirth
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

Re: range_agg

2019-07-05 Thread Paul A Jungwirth
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!

Re: range_agg

2019-07-05 Thread Paul A Jungwirth
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

Re: range_agg

2019-07-05 Thread Paul A Jungwirth
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

Re: range_agg

2019-07-05 Thread Paul A Jungwirth
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. :-)

Re: Periods

2019-07-05 Thread Paul A Jungwirth
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

Re: range_agg

2019-07-08 Thread Paul A Jungwirth
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,

Re: range_agg

2019-07-09 Thread Paul A Jungwirth
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

Re: range_agg

2019-07-09 Thread Paul A Jungwirth
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

Re: range_agg

2019-07-09 Thread Paul A Jungwirth
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

Re: range_agg

2019-07-09 Thread Paul A Jungwirth
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 >

Re: SQL:2011 Valid-Time Support

2018-05-10 Thread Paul A Jungwirth
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

Re: SQL:2011 Valid-Time Support

2018-06-05 Thread Paul A Jungwirth
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

Re: Periods

2018-06-05 Thread Paul A Jungwirth
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

Re: Periods

2018-06-05 Thread Paul A Jungwirth
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

Foreign key isolation tests

2025-05-16 Thread Paul A Jungwirth
Here are a couple new isolation tests for foreign keys, based on feedback from the Advanced Patch Review session at PGConf.dev. The goal is to show that temporal foreign keys do not violate referential integrity under concurrency. Non-temporal foreign keys use a crosscheck snapshot to avoid this.

Find comment on SearchSysCacheLockedCopy1

2025-05-17 Thread Paul A Jungwirth
Here is a very small comment fix, referencing the wrong function name. There's a lot of suffixes flying around right here. Yours, -- Paul ~{:-) p...@illuminatedcomputing.com v1-0001-Fix-comment-on-SearchSysCacheLockedCopy1.patch Description: Binary data