Re: SQL:2011 application time

2025-01-28 Thread Paul Jungwirth
On 1/27/25 07:47, Peter Eisentraut wrote: > On 24.01.25 03:55, Tom Lane wrote: >> I've now run an exhaustive search through the last three months of >> buildfarm runs, and found just one additional instance of the same >> failure. The three matches are >> >> https://buildfarm.postgresql.org/cgi-

Re: SQL:2011 application time

2025-01-27 Thread Peter Eisentraut
On 24.01.25 00:20, Paul Jungwirth wrote: What about caching the FK's query plan? Could the RESTRICT test ever reuse the constraint oid from the NO ACTION tests just above it? I'm pretty sure that's not supposed to happen, but if it used a plan generated from the NO ACTION SQL, it would exhibit

Re: SQL:2011 application time

2025-01-27 Thread Peter Eisentraut
On 24.01.25 03:55, Tom Lane wrote: Paul Jungwirth writes: On 1/23/25 15:28, Tom Lane wrote: I've only noticed the two, but I did not mount an aggressive search. It's possible that there were failures before 1772d554b0, since I now see that the diff is in a test case that is older than that.

Re: SQL:2011 application time

2025-01-23 Thread Tom Lane
Paul Jungwirth writes: > On 1/23/25 15:28, Tom Lane wrote: >> I've only noticed the two, but I did not mount an aggressive search. >> It's possible that there were failures before 1772d554b0, since I >> now see that the diff is in a test case that is older than that. > Okay, I'll keep in mind tha

Re: SQL:2011 application time

2025-01-23 Thread Paul Jungwirth
On 1/23/25 15:28, Tom Lane wrote: Paul Jungwirth writes: I can't find a regression.diffs in the second link. Is there one? I can't tell if it's the same failure as in the first link as not. It is the same, but the diff is buried in some other file, probably regress_log_027_stream_regress.

Re: SQL:2011 application time

2025-01-23 Thread Tom Lane
Paul Jungwirth writes: > I can't find a regression.diffs in the second link. Is there one? I can't > tell if it's the same > failure as in the first link as not. It is the same, but the diff is buried in some other file, probably regress_log_027_stream_regress. > I ran installcheck-parallel on

Re: SQL:2011 application time

2025-01-23 Thread Paul Jungwirth
On 1/23/25 07:31, Peter Eisentraut wrote: On 22.01.25 05:00, Tom Lane wrote: Peter Eisentraut writes: I have committed the fix for foreign key NO ACTION (patch 0002, this did not require patch 0001). That commit seems to be causing occasional buildfarm failures: https://buildfarm.postgresql

Re: SQL:2011 application time

2025-01-23 Thread Peter Eisentraut
On 21.01.25 19:52, Peter Eisentraut wrote: On 12.01.25 00:19, Paul Jungwirth wrote: On 1/4/25 13:39, Paul Jungwirth wrote: These updates fix a problem in the unaccent contrib module. When I added a new parameter to get_func_namespace, I changed a call there. Then I when took out that parameter

Re: SQL:2011 application time

2025-01-23 Thread Peter Eisentraut
On 22.01.25 05:00, Tom Lane wrote: Peter Eisentraut writes: I have committed the fix for foreign key NO ACTION (patch 0002, this did not require patch 0001). That commit seems to be causing occasional buildfarm failures: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=indri&dt=2025-0

Re: SQL:2011 application time

2025-01-21 Thread Tom Lane
Peter Eisentraut writes: > I have committed the fix for foreign key NO ACTION (patch 0002, this did > not require patch 0001). That commit seems to be causing occasional buildfarm failures: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=indri&dt=2025-01-22%2001%3A29%3A35 https://buildf

Re: SQL:2011 application time

2025-01-21 Thread Peter Eisentraut
On 12.01.25 00:19, Paul Jungwirth wrote: On 1/4/25 13:39, Paul Jungwirth wrote: These updates fix a problem in the unaccent contrib module. When I added a new parameter to get_func_namespace, I changed a call there. Then I when took out that parameter, I didn't update the extension again. Othe

Re: SQL:2011 application time

2024-12-10 Thread Peter Eisentraut
On 07.12.24 20:29, Paul Jungwirth wrote: These five patches all look good to me. Note that my tests already include a section for REPLICA IDENTITY FULL, which passed. But the subscriber was using a SeqScan to look up tuples to update. Here are the steps (mostly just because it was confusing

Re: SQL:2011 application time

2024-12-07 Thread Paul Jungwirth
On 12/4/24 03:15, Peter Eisentraut wrote: >> I have attached three patches that show how that could be done. (This would work in conjunction >> with your new tests. (Although now we could also test GiST with replica identity full?)) >> >> The comment block for IsIndexUsableForReplicaIdentityFu

Re: SQL:2011 application time

2024-12-05 Thread vignesh C
On Wed, 4 Dec 2024 at 16:45, Peter Eisentraut wrote: > > On 26.11.24 13:18, Peter Eisentraut wrote: > > I think this is the right idea, but after digging around a bit more, I > > think more could/should be done. > > > > After these changes, the difference between > > get_equal_strategy_number_for_

Re: SQL:2011 application time

2024-12-04 Thread Paul Jungwirth
On 12/4/24 03:15, Peter Eisentraut wrote: I did some more work on this approach, with the attached patches resulting.  This is essentially what I'm describing above, which in turn is a variation of your patch v45-0001-Fix-logical- replication-for-temporal-tables.patch, with your tests added at t

Re: SQL:2011 application time

2024-12-04 Thread Peter Eisentraut
On 26.11.24 13:18, Peter Eisentraut wrote: I think this is the right idea, but after digging around a bit more, I think more could/should be done. After these changes, the difference between get_equal_strategy_number_for_am() and get_equal_strategy_number() is kind of pointless.  We should re

Re: SQL:2011 application time

2024-11-26 Thread Peter Eisentraut
On 22.11.24 01:30, Paul Jungwirth wrote: -   return get_equal_strategy_number_for_am(am); +   /* For GiST indexes we need to ask the opclass what strategy number to use. */ +   if (am == GIST_AM_OID) +   return GistTranslateStratnum(opclass, RTEqualStrategyNumber); +  

Re: SQL:2011 application time

2024-11-21 Thread Nathan Bossart
On Thu, Nov 21, 2024 at 01:56:36PM +0100, Peter Eisentraut wrote: > Ok, I have committed the fix for this, and I'll continue working through the > rest of the patches. nitpick: I think this one needs a pgindent [0]. [0] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=koel&dt=2024-11-21%2

Re: SQL:2011 application time

2024-11-21 Thread Peter Eisentraut
On 14.11.24 18:25, Paul Jungwirth wrote: On 11/13/24 02:11, Peter Eisentraut wrote: I have committed the documentation patches v43-0001-Add-WITHOUT-OVERLAPS-and-PERIOD-to-ALTER-TABLE-r.patch v43-0002-Update-conexclop-docs-for-WITHOUT-OVERLAPS.patch Thanks! For the logical replication fixes

Re: SQL:2011 application time

2024-11-14 Thread Paul Jungwirth
On 11/14/24 10:14, Matthias van de Meent wrote: I called this issue out earlier this year: amcanunique implies btree-style uniqueness, and allows CREATE UNIQUE INDEX. However, that IndexAmRoutine field seems to be ignored for indexes that are created to back temporal unique constraints, which thu

Re: SQL:2011 application time

2024-11-14 Thread Matthias van de Meent
On Wed, 13 Nov 2024, 11:11 Peter Eisentraut, wrote: > This conditional is really hard to understand: > > + /* > +* The AM must support uniqueness, and the index must in fact be > unique. > +* If we have a WITHOUT OVERLAPS constraint (identified by > uniqueness + > +*

Re: SQL:2011 application time

2024-11-14 Thread Paul Jungwirth
Just sharing my progress here since it's been a week: On 11/6/24 17:03, Paul Jungwirth wrote: On 11/4/24 13:16, Sam Gabrielsson wrote: Foreign key violation errors are incorrectly raised in a few cases for a temporal foreign key with default ON UPDATE NO ACTION. Test is based on the commited v3

Re: SQL:2011 application time

2024-11-14 Thread Paul Jungwirth
On 11/13/24 02:11, Peter Eisentraut wrote: I have committed the documentation patches v43-0001-Add-WITHOUT-OVERLAPS-and-PERIOD-to-ALTER-TABLE-r.patch v43-0002-Update-conexclop-docs-for-WITHOUT-OVERLAPS.patch Thanks! For the logical replication fixes v43-0003-Fix-logical-replication-for-temp

Re: SQL:2011 application time

2024-11-13 Thread Peter Eisentraut
A quick comment on the patch v43-0005-Add-UPDATE-DELETE-FOR-PORTION-OF.patch regarding the code in transformForPortionOfClause() and the additions you made to lsyscache.c: What you are doing is taking a type OID and a function OID and then converting them back to name and namespace and then

Re: SQL:2011 application time

2024-11-13 Thread Peter Eisentraut
I committed a few fixes in this area today. Has everything here been addressed? On 16.08.24 04:12, jian he wrote: On Thu, Aug 8, 2024 at 4:54 AM Paul Jungwirth wrote: Rebased to e56ccc8e42. I only applied to 0001-0003. in create_table.sgml, I saw the WITHOUT OVERLAPS change is mainly in

Re: SQL:2011 application time

2024-11-13 Thread Peter Eisentraut
I have committed the documentation patches v43-0001-Add-WITHOUT-OVERLAPS-and-PERIOD-to-ALTER-TABLE-r.patch v43-0002-Update-conexclop-docs-for-WITHOUT-OVERLAPS.patch For the logical replication fixes v43-0003-Fix-logical-replication-for-temporal-tables.patch can you summarize what the issues cu

Re: SQL:2011 application time

2024-11-06 Thread Paul Jungwirth
On 11/4/24 13:16, Sam Gabrielsson wrote: Foreign key violation errors are incorrectly raised in a few cases for a temporal foreign key with default ON UPDATE NO ACTION. Test is based on the commited v39 patches (used a snapshot version of PG18 devel available from PGDG). Thank you for the repo

Re: SQL:2011 application time

2024-11-05 Thread Sam Gabrielsson
Foreign key violation errors are incorrectly raised in a few cases for a temporal foreign key with default ON UPDATE NO ACTION. Test is based on the commited v39 patches (used a snapshot version of PG18 devel available from PGDG). If there exists a single referencing row for a foreign key (wit

Re: SQL:2011 application time

2024-09-17 Thread Peter Eisentraut
On 05.09.24 14:09, 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 range check plus vari

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: 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-05 Thread Peter Eisentraut
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 range check plus various minor fixes. This looks good to me now

Re: SQL:2011 application time

2024-08-15 Thread jian he
On Thu, Aug 8, 2024 at 4:54 AM Paul Jungwirth wrote: > > Rebased to e56ccc8e42. I only applied to 0001-0003. in create_table.sgml, I saw the WITHOUT OVERLAPS change is mainly in table_constraint. but we didn't touch alter_table.sgml. Do we also need to change alter_table.sgml correspondingly? +

Re: SQL:2011 application time

2024-08-06 Thread jian he
On Tue, Aug 6, 2024 at 10:02 AM jian he wrote: > > On Fri, Aug 2, 2024 at 1:09 AM Paul Jungwirth > wrote: > > > > On 7/25/24 08:52, Paul Jungwirth wrote: > > > Here is a patch moving the not-empty check into > > > check_exclusion_or_unique_constraint. That is a more > > > logical place for it th

Re: SQL:2011 application time

2024-08-05 Thread jian he
On Fri, Aug 2, 2024 at 1:09 AM Paul Jungwirth wrote: > > On 7/25/24 08:52, Paul Jungwirth wrote: > > Here is a patch moving the not-empty check into > > check_exclusion_or_unique_constraint. That is a more > > logical place for it than ExecConstraints, since WITHOUT OVERLAPS is part > > of the i

Re: SQL:2011 application time

2024-07-24 Thread jian he
On Wed, Jul 24, 2024 at 12:08 AM Paul Jungwirth wrote: > > On 7/18/24 11:39, Paul Jungwirth wrote: > > So I swapped in the &&& patch, cleaned it up, and added tests. But > > something is wrong. After I get > > one failure from an empty, I keep getting failures, even though the table > > is empty

Re: SQL:2011 application time

2024-07-23 Thread Paul Jungwirth
On 7/18/24 11:39, Paul Jungwirth wrote: So I swapped in the &&& patch, cleaned it up, and added tests. But something is wrong. After I get one failure from an empty, I keep getting failures, even though the table is empty: regression=# truncate temporal_rng cascade; NOTICE:  truncate cascades t

Re: SQL:2011 application time

2024-07-17 Thread Paul Jungwirth
On 7/9/24 00:15, jian he wrote: **Option 2**: Add a new operator, called &&&, that works like && except an empty range *does* overlap another empty range. Empty ranges should still not overlap anything else. This would fix the exclusion constraint. You could add `(5, 'empty')` once but not twic

Re: SQL:2011 application time

2024-07-09 Thread jian he
On Thu, Jun 6, 2024 at 4:56 AM Paul Jungwirth wrote: > > On 5/21/24 11:27, Isaac Morland wrote: > > On Tue, 21 May 2024 at 13:57, Robert Haas > > wrote: > > > > What I think is less clear is what that means for temporal primary > > keys. As Paul pointed out u

Re: SQL:2011 application time

2024-06-28 Thread Robert Haas
On Thu, Jun 27, 2024 at 5:56 PM Paul Jungwirth wrote: > I did add a relperiods column, but I have a mostly-complete branch here (not > included in the > patches) that does without. Not maintaining that new column is simpler for > sure. The consequence is > that the relcache must scan for WITHOUT

Re: SQL:2011 application time

2024-06-12 Thread Matthias van de Meent
On Wed, 5 Jun 2024 at 22:57, Paul Jungwirth wrote: > > On Thu, May 9, 2024 at 5:44 PM Matthias van de Meent > wrote: > > Additionally, because I can't create my own non-constraint-backing > > unique GIST indexes, I can't pre-create my unique constraints > > CONCURRENTLY as one could do for t

Re: SQL:2011 application time

2024-06-12 Thread Robert Haas
On Wed, Jun 5, 2024 at 4:56 PM Paul Jungwirth wrote: > **Option 2**: Add a new operator, called &&&, that works like && except an > empty range *does* > overlap another empty range. Empty ranges should still not overlap anything > else. This would fix the > exclusion constraint. You could add `(

Re: SQL:2011 application time

2024-06-05 Thread Paul Jungwirth
On Thu, May 9, 2024 at 5:44 PM Matthias van de Meent wrote: > Additionally, because I can't create my own non-constraint-backing > unique GIST indexes, I can't pre-create my unique constraints > CONCURRENTLY as one could do for the non-temporal case We talked about this a bit at pgconf.dev. I w

Re: SQL:2011 application time

2024-06-05 Thread Paul Jungwirth
On 5/21/24 11:27, Isaac Morland wrote: On Tue, 21 May 2024 at 13:57, Robert Haas mailto:robertmh...@gmail.com>> wrote: What I think is less clear is what that means for temporal primary keys. As Paul pointed out upthread, in every other case, a temporal primary key is at least as uni

Re: SQL:2011 application time

2024-05-21 Thread Jeff Davis
On Tue, 2024-05-21 at 13:57 -0400, Robert Haas wrote: > What I think is less clear is what that means for temporal primary > keys. Right. My message was specifically a response to the concern that there was some kind of design flaw in the range types or exclusion constraints mechanisms. I don't

Re: SQL:2011 application time

2024-05-21 Thread Isaac Morland
On Tue, 21 May 2024 at 13:57, Robert Haas wrote: What I think is less clear is what that means for temporal primary > keys. As Paul pointed out upthread, in every other case, a temporal > primary key is at least as unique as a regular primary key, but in > this case, it isn't. And someone might r

Re: SQL:2011 application time

2024-05-21 Thread Robert Haas
On Thu, May 16, 2024 at 7:22 PM Jeff Davis wrote: > An empty range does not "bypass" the an exclusion constraint. The > exclusion constraint has a documented meaning and it's enforced. > > Of course there are situations where an empty range doesn't make a lot > of sense. For many domains zero does

Re: SQL:2011 application time

2024-05-16 Thread Jeff Davis
On Mon, 2024-05-13 at 12:11 +0200, Peter Eisentraut wrote: > Some of these issues might be design flaws in the underlying > mechanisms, > like range types and exclusion constraints.  Like, if you're supposed > to > use this for scheduling but you can use empty ranges to bypass > exclusion > cons

Re: SQL:2011 application time

2024-05-16 Thread Peter Eisentraut
On 15.05.24 11:39, Peter Eisentraut wrote: Attached are the individual revert patches.  I'm supplying these here mainly so that future efforts can use those instead of the original patches, since that would have to redo all the conflict resolution and also miss various typo fixes etc. that were

Re: SQL:2011 application time

2024-05-14 Thread Michael Paquier
On Tue, May 14, 2024 at 01:33:46PM +0800, jian he wrote: > thanks for the idea, I roughly played around with it, seems doable. > but the timing seems not good, reverting is a good idea. Please note that this is still an open item, and that time is running short until beta1. A revert seems to be t

Re: SQL:2011 application time

2024-05-13 Thread jian he
On Tue, May 14, 2024 at 7:30 AM Paul Jungwirth wrote: > > On 5/13/24 03:11, Peter Eisentraut wrote: > > It looks like we missed some of these fundamental design questions early > > on, and it might be too > > late now to fix them for PG17. > > > > For example, the discussion on unique constraints

Re: SQL:2011 application time

2024-05-13 Thread Paul Jungwirth
On 5/13/24 03:11, Peter Eisentraut wrote: It looks like we missed some of these fundamental design questions early on, and it might be too late now to fix them for PG17. For example, the discussion on unique constraints misses that the question of null values in unique constraints itself is co

Re: SQL:2011 application time

2024-05-13 Thread Peter Eisentraut
On 03.04.24 07:30, Paul Jungwirth wrote: But is it *literally* unique? Well two identical keys, e.g. (5, '[Jan24,Mar24)') and (5, '[Jan24,Mar24)'), do have overlapping ranges, so the second is excluded. Normally a temporal unique index is *more* restrictive than a standard one, since it forbids

Re: SQL:2011 application time

2024-05-12 Thread Paul Jungwirth
On 5/12/24 08:51, Paul Jungwirth wrote: On 5/12/24 05:55, Matthias van de Meent wrote:   > pg=# CREATE UNIQUE INDEX ON temporal_testing USING gist (id, valid_during);   > ERROR:  access method "gist" does not support unique indexes To me that error message seems correct. The programmer hasn't s

Re: SQL:2011 application time

2024-05-12 Thread Paul Jungwirth
On 5/5/24 20:01, jian he wrote: hi. I hope I understand the problem correctly. my understanding is that we are trying to solve a corner case: create table t(a int4range, b int4range, primary key(a, b WITHOUT OVERLAPS)); insert into t values ('[1,2]','empty'), ('[1,2]','empty'); I think the entr

Re: SQL:2011 application time

2024-05-12 Thread Paul Jungwirth
On 5/12/24 05:55, Matthias van de Meent wrote: > pg=# CREATE UNIQUE INDEX ON temporal_testing USING gist (id, valid_during); > ERROR: access method "gist" does not support unique indexes To me that error message seems correct. The programmer hasn't said anything about the special temporal

Re: SQL:2011 application time

2024-05-12 Thread Matthias van de Meent
On Sun, 12 May 2024 at 05:26, Paul Jungwirth wrote: > On 5/9/24 17:44, Matthias van de Meent wrote: > > I haven't really been following this thread, but after playing around > > a bit with the feature I feel there are new gaps in error messages. I > > also think there are gaps in the functionality

Re: SQL:2011 application time

2024-05-11 Thread Paul Jungwirth
On 5/9/24 17:44, Matthias van de Meent wrote: I haven't really been following this thread, but after playing around a bit with the feature I feel there are new gaps in error messages. I also think there are gaps in the functionality regarding the (lack of) support for CREATE UNIQUE INDEX, and att

Re: SQL:2011 application time

2024-05-11 Thread Paul Jungwirth
On 5/11/24 17:00, jian he wrote: I hope I understand the problem correctly. my understanding is that we are trying to solve a corner case: create table t(a int4range, b int4range, primary key(a, b WITHOUT OVERLAPS)); insert into t values ('[1,2]','empty'), ('[1,2]','empty'); but we still not

Re: SQL:2011 application time

2024-05-11 Thread jian he
On Mon, May 6, 2024 at 11:01 AM jian he wrote: > > On Wed, May 1, 2024 at 12:39 AM Paul Jungwirth > wrote: > > > > On 4/30/24 09:24, Robert Haas wrote: > > > Peter, could you have a look at > > > http://postgr.es/m/47550967-260b-4180-9791-b224859fe...@illuminatedcomputing.com > > > and express an

Re: SQL:2011 application time

2024-05-10 Thread Peter Eisentraut
I have committed the v2-0001-Fix-ON-CONFLICT-DO-NOTHING-UPDATE-for-temporal-in.patch from this (confusingly, there was also a v2 earlier in this thread), and I'll continue working on the remaining items. On 09.05.24 06:24, Paul Jungwirth wrote: Here are a couple new patches, rebased to e305f7

Re: SQL:2011 application time

2024-05-09 Thread Matthias van de Meent
Hi, I haven't really been following this thread, but after playing around a bit with the feature I feel there are new gaps in error messages. I also think there are gaps in the functionality regarding the (lack of) support for CREATE UNIQUE INDEX, and attaching these indexes to constraints. pg=#

Re: SQL:2011 application time

2024-05-08 Thread Paul Jungwirth
Here are a couple new patches, rebased to e305f715, addressing Peter's feedback. I'm still working on integrating jian he's suggestions for the last patch, so I've omitted that one here. On 5/8/24 06:51, Peter Eisentraut wrote: About v2-0001-Fix-ON-CONFLICT-DO-NOTHING-UPDATE-for-temporal-in.pat

Re: SQL:2011 application time

2024-05-08 Thread Peter Eisentraut
On 30.04.24 18:39, Paul Jungwirth wrote: On 4/30/24 09:24, Robert Haas wrote: Peter, could you have a look at http://postgr.es/m/47550967-260b-4180-9791-b224859fe...@illuminatedcomputing.com and express an opinion about whether each of those proposals are (a) good or bad ideas and (b) whether th

Re: SQL:2011 application time

2024-05-05 Thread jian he
On Wed, May 1, 2024 at 12:39 AM Paul Jungwirth wrote: > > On 4/30/24 09:24, Robert Haas wrote: > > Peter, could you have a look at > > http://postgr.es/m/47550967-260b-4180-9791-b224859fe...@illuminatedcomputing.com > > and express an opinion about whether each of those proposals are (a) > > good

Re: SQL:2011 application time

2024-05-01 Thread jian he
On Wed, May 1, 2024 at 12:39 AM Paul Jungwirth wrote: > > On 4/30/24 09:24, Robert Haas wrote: > > Peter, could you have a look at > > http://postgr.es/m/47550967-260b-4180-9791-b224859fe...@illuminatedcomputing.com > > and express an opinion about whether each of those proposals are (a) > > good

Re: SQL:2011 application time

2024-04-30 Thread Paul Jungwirth
On 4/30/24 09:24, Robert Haas wrote: Peter, could you have a look at http://postgr.es/m/47550967-260b-4180-9791-b224859fe...@illuminatedcomputing.com and express an opinion about whether each of those proposals are (a) good or bad ideas and (b) whether they need to be fixed for the current releas

Re: SQL:2011 application time

2024-04-30 Thread Robert Haas
On Fri, Apr 26, 2024 at 3:41 PM Paul Jungwirth wrote: > On 4/26/24 12:25, Robert Haas wrote: > > I think this thread should be added to the open items list. > > Thanks! I sent a request to pgsql-www to get edit permission. I didn't > realize there was a wiki page > tracking things like this. I ag

Re: SQL:2011 application time

2024-04-26 Thread Paul Jungwirth
On 4/26/24 12:25, Robert Haas wrote: I think this thread should be added to the open items list. Thanks! I sent a request to pgsql-www to get edit permission. I didn't realize there was a wiki page tracking things like this. I agree it needs to be fixed if we want to include the feature. You

Re: SQL:2011 application time

2024-04-26 Thread Robert Haas
On Wed, Apr 3, 2024 at 1:30 AM Paul Jungwirth wrote: > I found some problems with temporal primary keys and the idea of uniqueness, > especially around the > indisunique column. Here are some small fixes and a proposal for a larger > fix, which I think we need > but I'd like some feedback on. I

Re: SQL:2011 application time

2024-04-14 Thread jian he
On Wed, Apr 3, 2024 at 1:30 PM Paul Jungwirth wrote: > > On 3/24/24 00:38, Peter Eisentraut wrote:> I have committed the patches > > v33-0001-Add-temporal-FOREIGN-KEYs.patch and > > v33-0002-Support-multiranges-in-temporal-FKs.patch > > (together). > > Hi Hackers, > > I found some problems with t

Re: SQL:2011 application time

2024-04-02 Thread Paul Jungwirth
On 3/24/24 00:38, Peter Eisentraut wrote:> I have committed the patches v33-0001-Add-temporal-FOREIGN-KEYs.patch and v33-0002-Support-multiranges-in-temporal-FKs.patch (together). Hi Hackers, I found some problems with temporal primary keys and the idea of uniqueness, especially around the in

Re: SQL:2011 application time

2024-03-25 Thread jian he
On Sun, Mar 24, 2024 at 1:42 AM Paul Jungwirth wrote: > > v33 attached with minor changes. > > Okay, added those tests too. Thanks! > > Rebased to 697f8d266c. > hi. minor issues I found in v33-0003. there are 29 of {check_amproc_signature?.*false} only one {check_amproc_signature(procform->ampro

Re: SQL:2011 application time

2024-03-24 Thread Peter Eisentraut
On 23.03.24 18:42, Paul Jungwirth wrote: Now this is a long chain of reasoning to say rangetypes are safe. I added a comment. Note it doesn't apply to arbitrary types, so if we support those eventually we should just require a recheck always, or alternately use equals, not containedby. (That wo

Re: SQL:2011 application time

2024-03-22 Thread jian he
On Fri, Mar 22, 2024 at 11:49 PM Peter Eisentraut wrote: > > On 22.03.24 01:35, Paul Jungwirth wrote: > > > 1. In ri_triggers.c ri_KeysEqual, you swap the order of arguments to > > ri_AttributesEqual(): > > > > > > - if (!ri_AttributesEqual(riinfo->ff_eq_oprs[i], > > RIAttType(rel, at

Re: SQL:2011 application time

2024-03-22 Thread Peter Eisentraut
On 22.03.24 01:35, Paul Jungwirth wrote: > 1. In ri_triggers.c ri_KeysEqual, you swap the order of arguments to ri_AttributesEqual(): > > -   if (!ri_AttributesEqual(riinfo->ff_eq_oprs[i], RIAttType(rel, attnums[i]), > -   oldvalue, newvalue)) > +  

Re: SQL:2011 application time

2024-03-21 Thread jian he
On Fri, Mar 22, 2024 at 8:35 AM Paul Jungwirth wrote: > > Your patch had a lot of other noisy changes, e.g. > whitespace and reordering lines. If there are other things you intended to > add to the tests, can you > describe them? i think on update restrict, on delete restrict cannot be deferred,

Re: SQL:2011 application time

2024-03-21 Thread Peter Eisentraut
On 20.03.24 17:21, Paul Jungwirth wrote: On 3/20/24 03:55, jian he wrote: hi. minor cosmetic issues, other than that, looks good. *pk_period = (indexStruct->indisexclusion); to *pk_period = indexStruct->indisexclusion; ... > if (with_period && !fkconstraint->fk_with_period) ereport(ERROR, (err

Re: SQL:2011 application time

2024-03-21 Thread jian he
with foreign key "no action", in a transaction, we can first insert foreign key data, then primary key data. also the update/delete can fail at the end of transaction. based on [1] explanation about the difference between "no action" and "restrict". I only refactor the v31-0002-Support-multiranges

Re: SQL:2011 application time

2024-03-20 Thread jian he
hi. minor cosmetic issues, other than that, looks good. *pk_period = (indexStruct->indisexclusion); to *pk_period = indexStruct->indisexclusion; if (with_period) { if (!fkconstraint->fk_with_period) ereport(ERROR, (errcode(ERRCODE_INVALID_FOREIGN_KEY), errmsg("foreign key uses PERIOD on the refe

Re: SQL:2011 application time

2024-03-19 Thread Peter Eisentraut
On 16.03.24 22:37, Paul A Jungwirth wrote: Here is a new patch series addressing the last few feedback emails from Peter & Jian He. It mostly focuses on the FKs patch, trying to get it really ready to commit, I have committed the test changes (range and date format etc.). The FOREIGN KEY patch

Re: SQL:2011 application time

2024-03-19 Thread jian he
On Tue, Mar 19, 2024 at 6:49 AM Paul Jungwirth wrote: > > Rebased to 846311051e. > Hi, I just found out some minor issues. + * types matching the PERIOD element. periodprocoid is a GiST support function to + * aggregate multiple PERIOD element values into a single value + * (whose return type ne

Re: SQL:2011 application time

2024-03-17 Thread jian he
one more minor issue related to error reporting. I've only applied v28, 0001 to 0005. -- (parent_id, valid_at) REFERENCES [implicit] -- FOREIGN KEY part should specify PERIOD CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, CONSTRAINT temporal_fk_rng2rng_pk

Re: SQL:2011 application time

2024-03-17 Thread jian he
Hi, minor issues from 1 to 0005. + + referencedagg + aggregates referenced rows' WITHOUT OVERLAPS +part + 13 + comparing with surrounding items, maybe need to add `(optional)`? I think the explanation is not good as explained in referencedagg entry below:

Re: SQL:2011 application time

2024-03-13 Thread jian he
in GetOperatorFromWellKnownStrategy: *strat = GistTranslateStratnum(opclass, instrat); if (*strat == InvalidStrategy) { HeapTuple tuple; tuple = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass)); if (!HeapTupleIsValid(tuple)) elog(ERROR, "cache lookup failed for operator class %u", opclass); erepo

Re: SQL:2011 application time

2024-03-11 Thread jian he
On Mon, Mar 11, 2024 at 3:46 PM Peter Eisentraut wrote: > > A few general comments on the tests: > > - In the INSERT commands, specify the column names explicitly. This > makes the tests easier to read (especially since the column order > between the PK and the FK table is sometimes different). >

Re: SQL:2011 application time

2024-03-11 Thread jian he
+ + If the last column is marked with PERIOD, + it is treated in a special way. + While the non-PERIOD columns are treated normally + (and there must be at least one of them), + the PERIOD column is not compared for equality. + Instead the constraint is considered

Re: SQL:2011 application time

2024-03-11 Thread Peter Eisentraut
On 01.03.24 22:56, Paul Jungwirth wrote: On 3/1/24 12:38, Paul Jungwirth wrote: On 2/29/24 13:16, Paul Jungwirth wrote: Here is a v26 patch series to fix a cfbot failure in sepgsql. Rebased to 655dc31046. v27 attached, fixing some cfbot failures from headerscheck+cpluspluscheck. Sorry for th

Re: SQL:2011 application time

2024-02-29 Thread Paul Jungwirth
On 2/13/24 21:00, jian he wrote: Hi more minor issues. + FindFKComparisonOperators( + fkconstraint, tab, i, fkattnum, + &old_check_ok, &old_pfeqop_item, + pktypoid[i], fktypoid[i], opclasses[i], + is_temporal, false, + &pfeqoperators[i], &ppeqoperators[i], &ffeqoperators[i]); + } + if (is_tempor

Re: SQL:2011 application time

2024-02-13 Thread jian he
Hi more minor issues. + FindFKComparisonOperators( + fkconstraint, tab, i, fkattnum, + &old_check_ok, &old_pfeqop_item, + pktypoid[i], fktypoid[i], opclasses[i], + is_temporal, false, + &pfeqoperators[i], &ppeqoperators[i], &ffeqoperators[i]); + } + if (is_temporal) { + pkattnum[numpks] = pkperiod

Re: SQL:2011 application time

2024-02-12 Thread Peter Eisentraut
I have done a review of the temporal foreign key patches in this patch series (0002 and 0003, v24). The patch set needs a rebase across c85977d8fef. I was able to do it manually, but it's a bit tricky, so perhaps you can post a new set to help future reviews. (Also, the last (0007) patch has so

Re: SQL:2011 application time

2024-02-01 Thread jian he
On Mon, Jan 29, 2024 at 8:00 AM jian he wrote: > > I fixed your tests, some of your tests can be simplified, (mainly > primary key constraint is unnecessary for the failed tests) > also your foreign key patch test table, temporal_rng is created at > line 141, and we use it at around line 320. > it

Re: SQL:2011 application time

2024-01-28 Thread jian he
I fixed your tests, some of your tests can be simplified, (mainly primary key constraint is unnecessary for the failed tests) also your foreign key patch test table, temporal_rng is created at line 141, and we use it at around line 320. it's hard to get the definition of temporal_rng. I drop the t

Re: SQL:2011 application time

2024-01-24 Thread Peter Eisentraut
On 24.01.24 23:06, Paul Jungwirth wrote: On 1/24/24 08:32, Peter Eisentraut wrote: > On 18.01.24 04:59, Paul Jungwirth wrote: >> Here are new patches consolidating feedback from several emails. > > I have committed 0001 and 0002 (the primary key support). Thanks Peter! I noticed the comment

Re: SQL:2011 application time

2024-01-24 Thread Peter Eisentraut
On 18.01.24 04:59, Paul Jungwirth wrote: Here are new patches consolidating feedback from several emails. I have committed 0001 and 0002 (the primary key support). The only significant tweak I did was the error messages in GetOperatorFromWellKnownStrategy(), to make the messages translatable

Re: SQL:2011 application time

2024-01-21 Thread Peter Smith
2024-01 Commitfest. Hi, This patch has a CF status of "Needs Review" [1], but it seems there were CFbot test failures last time it was run [2]. Please have a look and post an updated version if necessary. == [1] https://commitfest.postgresql.org/46/4308/ [2] https://cirrus-ci.com/github/postg

Re: SQL:2011 application time

2024-01-13 Thread jian he
On Thu, Jan 11, 2024 at 10:44 PM Peter Eisentraut wrote: > > On 31.12.23 09:51, Paul Jungwirth wrote: > > On Wed, Dec 6, 2023 at 12:59 AM Peter Eisentraut > > wrote: > > > > > > On 02.12.23 19:41, Paul Jungwirth wrote: > > > > So what do you think of this idea instead?: > > > > > > > > We co

Re: SQL:2011 application time

2024-01-11 Thread Peter Eisentraut
On 31.12.23 09:51, Paul Jungwirth wrote: On Wed, Dec 6, 2023 at 12:59 AM Peter Eisentraut wrote: > > On 02.12.23 19:41, Paul Jungwirth wrote: > > So what do you think of this idea instead?: > > > > We could add a new (optional) support function to GiST that translates > > "well-known" str

Re: SQL:2011 application time

2024-01-08 Thread vignesh C
On Sat, 6 Jan 2024 at 05:50, Paul Jungwirth wrote: > > Getting caught up on reviews from November and December: > > On 11/19/23 22:57, jian he wrote: > > > > I believe the following part should fail. Similar tests on > > src/test/regress/sql/generated.sql. line begin 347. > > > > drop table

Re: SQL:2011 application time

2024-01-08 Thread jian he
On Tue, Jan 9, 2024 at 2:54 AM Paul Jungwirth wrote: > > On 1/8/24 06:54, jian he wrote: > > On Fri, Jan 5, 2024 at 1:06 PM jian he wrote: > > > > range_intersect returns the intersection of two ranges. > > I think here we are doing the opposite. > > names the main SQL function "range_not_in

  1   2   >