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
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 p
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
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
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
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
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
On 5/8/24 07:44, Bruce Momjian wrote:
On Wed, May 8, 2024 at 02:29:34PM +0200, Peter Eisentraut wrote:
Yes, David is correct here on all points. I like his suggestion to
clarify the language here also. If you need a patch from me let me know,
but I assume it's something a committer can just
On 5/7/24 08:23, David G. Johnston wrote:
On Tue, May 7, 2024 at 7:54 AM Bruce Momjian mailto:br...@momjian.us>> wrote:
In the two marked lines, it says "if one side of the foreign key uses
PERIOD, the other side must too." However, looking at the example
queries, it seems like if
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
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.
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
On 3/23/24 10:04, Paul Jungwirth wrote:
Perhaps if the previous collation was nondeterministic we should force a
re-check.
Here is a patch implementing this. It was a bit more fuss than I expected, so maybe someone has a
better way.
We have had nondeterministic collations since v12, so
Dear hackers,
I was looking at how foreign keys deal with collations, and I came across this comment about not
re-checking a foreign key if the column type changes in a compatible way:
* Since we require that all collations share the same notion of
* equality (which they do, because
On 2/13/24 21:00, jian he wrote:
Hi
more minor issues.
+ FindFKComparisonOperators(
+ fkconstraint, tab, i, fkattnum,
+ _check_ok, _pfeqop_item,
+ pktypoid[i], fktypoid[i], opclasses[i],
+ is_temporal, false,
+ [i], [i], [i]);
+ }
+ if (is_temporal) {
+ pkattnum[numpks] = pkperiodattnum;
+
On 1/15/24 02:37, Peter Eisentraut wrote:
In this updated patch set, I have also added the treatment of the Constraint type. (I also noted
that the manual read/write functions for the Constraint type are out-of-sync again, so simplifying
this would be really helpful.) I have also added commit
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_intersect" and the internal
> function as "range_not_intersect_internal"
Hello,
On 11/26/23 12:22, Tom Lane wrote:
> Yes, this regression test is entirely unacceptable; the numbers will
> not be stable enough. Even aside from the different-settings issue,
> you can't rely on ANALYZE deriving exactly the same stats every time.
> Usually what we try to do is devise a
On Thu, Nov 23, 2023 at 1:08 AM Peter Eisentraut wrote:
> After further thought, I think the right solution is to change
> btree_gist (and probably also btree_gin) to use the common RT* strategy
> numbers.
Okay. That will mean bumping the version of btree_gist, and you must be running that
On 11/9/23 05:47, Peter Eisentraut wrote:
I went over the patch
v17-0001-Add-temporal-PRIMARY-KEY-and-UNIQUE-constraints.patch in more
detail
Thanks Peter!
I'm about halfway through jian he's last two emails. I'll address your
feedback also. I wanted to reply to this without waiting though:
On 9/25/23 14:00, Peter Eisentraut wrote:
Looking through the tests in v16-0001:
+-- PK with no columns just WITHOUT OVERLAPS:
+CREATE TABLE temporal_rng (
+ valid_at tsrange,
+ CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS)
+);
+ERROR: syntax error at or near
Hi Peter et al,
On 9/1/23 12:56, Paul Jungwirth wrote:
On 9/1/23 11:30, Peter Eisentraut wrote:
I think the WITHOUT OVERLAPS clause should be per-column, so that
something like UNIQUE (a WITHOUT OVERLAPS, b, c WITHOUT OVERLAPS)
would be possible. Then the WITHOUT OVERLAPS clause would
On 9/7/23 18:24, jian he wrote:
for a range primary key, is it fine to expect it to be unique, not
null and also not overlap? (i am not sure how hard to implement it).
-
quote from 7IWD2-02-Foundation-2011-12.pdf. 4.18.3.2 Unique
On 9/1/23 03:50, Vik Fearing wrote:
On 9/1/23 11:30, Peter Eisentraut wrote:
1) If I write UNIQUE (a, b, c WITHOUT OVERLAPS), does the WITHOUT
OVERLAPS clause attach to the last column, or to the whole column
list? In the SQL standard, you can only have one period and it has to
be listed
On 1/24/23 06:38, Ronan Dunklau wrote:
I've taken a look at the patch, and I'm not sure why you keep the restriction
on the Gist operator being of the RTEqualStrategyNumber strategy. I don't
think we have any other place where we expect those strategy numbers to
match. For hash it's different,
On 12/15/22 16:12, Tom Lane wrote:
This patch also requires the matching constraint columns to use equality
comparisons (`(foo WITH =)`), so it is really equivalent to the existing
b-tree rule.
That's not quite good enough: you'd better enforce that it's the same
equality operator (and same
Hello Hackers,
I'm trying to get things going again on my temporal tables work, and
here is a small patch to move that forward.
It lets you create exclusion constraints on partitioned tables, similar
to today's rules for b-tree primary keys & unique constraints:
just as we permit a PK on a
Hello,
I noticed a few places in the new foreign key code where a comment says
"the ON DELETE SET NULL/DELETE clause". I believe it should say "ON
DELETE SET NULL/DEFAULT".
These comments were added in d6f96ed94e7, "Allow specifying column list
for foreign key ON DELETE SET actions." Here
On 3/10/22 14:07, Chapman Flack wrote:
When I apply this patch, I get a func.sgml with two entries for
range_intersect_agg(anymultirange).
Arg, fixed.
In range_agg_transfn, you've changed the message in the "must be called
with a range or multirange"; that seems like another good candidate
On 3/1/22 13:33, Chapman Flack wrote:
I think the 4 lines should suffice, but it looks like this patch was
generated from a rebase of the old one (with three lines) that ended up
putting the new 'range_agg' entry ahead of 'max' in func.sgml, which
position is now baked into the 4 lines of
On 2/26/22 17:13, Chapman Flack wrote:
This applies (with some fuzz) and passes installcheck-world, but a rebase
is needed, because 3 lines of context aren't enough to get the doc changes
in the right place in the aggregate function table. (I think generating
the patch with 4 lines of context
Here is a patch adding range_agg(anymultirange). Previously range_agg
only accepted anyrange.
Here is a bug report from last month requesting this addition:
https://www.postgresql.org/message-id/CAOC8YUcOtAGscPa31ik8UEMzgn8uAWA09s6CYOGPyP9_cBbWTw%40mail.gmail.com
As that message points out,
On 10/27/20 7:11 AM, Ibrar Ahmed wrote:
I have spent some more time on the patch and did a lot of cleanup along
with some fixes, compilation errors, and warnings.
Thank you for taking a look at this! I've been swamped with ordinary
work and haven't had a chance to focus on it for a while, but
Thanks Alvaro!
On Mon, Mar 23, 2020 at 4:33 PM Alvaro Herrera
wrote:
>
> Thinking about the on-disk representation, can we do better than putting
> the contained ranges in long-varlena format, including padding; also we
> include the type OID with each element. Sounds wasteful. A more
>
On 3/4/20 1:57 PM, Alvaro Herrera wrote:
I noticed while going over the multirange types patch that it adds a
pointless typiofunc cached OID to a struct used for I/O functions'
fn_extra. It seems to go completely unused, so I checked range types
(which this was cribbed from) and indeed, it is
Thanks for looking at this again!
On 3/4/20 1:33 PM, Alvaro Herrera wrote:
I came across an interesting thing, namely multirange_canonicalize()'s
use of qsort_arg with a callback of range_compare(). range_compare()
calls range_deserialize() (non-trivial parsing) for each input range;
On 11/21/19 1:06 AM, Pavel Stehule wrote:
2. I don't like introduction "safe" operators - now the basic operators
are doubled, and nobody without documentation will use @* operators.
It is not intuitive. I think is better to map this functionality to
basic operators +- * and implement it just
On 10/25/19 6:40 AM, Andrew Dunstan wrote:
json{b}_build_object and json{b}_build_array are designed for creating
nested json{b}. Not sure if they would work for your purpose.
Thanks for the suggestion! I looked at these a bit, but they only work
if you have a known-ahead-of-time number of
Hello,
I noticed that our existing 2-param json{,b}_object functions take
text[] for both keys and values, so they are only able to build
one-layer-deep JSON objects. I'm interested in adding json{,b}_object
functions that take text[] for the keys and json{,b}[] for the values.
It would
Hi Ibrar,
On 8/6/19 3:26 AM, Ibrar Ahmed wrote:
- Why we are not allowing any other datatype other than ranges in the
primary key. Without that there is no purpose of a primary key.
A temporal primary key always has at least one ordinary column (of any
type), so it is just a traditional
On 7/9/19 11:24 PM, David Fetter wrote:
I seem to recall that the usual convention (at least in math) is
to use intervals that are generally represented as open on the
infinity side, but that might not fit how we do things.
I think it does, unless I'm misunderstanding?
Oh, I was just
On 7/9/19 12:01 PM, Alvaro Herrera wrote:
On 2019-Jul-08, Paul A Jungwirth wrote:
- You can subscript a multirange like you do an array (? This could be
a function instead.)
Note that we already have a patch in the pipe to make subscripting an
extensible operation, which would fit pretty
I suspect that if you build it, the will come, "they" being anyone who
has to schedule coverage, check usage of a resource over time, etc. Is
this something you want help with at some level? Coding, testing,
promoting...
You might be right. :-) Most of this is done already, since it was
On 5/3/19 6:41 PM, David Fetter wrote:
This suggests two different ways to extend ranges over aggregation:
one which is a union of (in general) disjoint intervals, two others
are a union of intervals, each of which has a weight.
. . .
I think the cases above, or at least the first two of them,
On 5/4/19 3:11 PM, Corey Huinker wrote:
One question is how to aggregate ranges that would leave gaps and/or
overlaps. So in my extension there is a one-param version that forbids
gaps & overlaps, but I let you permit them by passing extra parameters,
so the signature is:
Hello,
I wrote an extension to add a range_agg function with similar behavior
to existing *_agg functions, and I'm wondering if folks would like to
have it in core? Here is the repo: https://github.com/pjungwir/range_agg
I'm also working on a patch for temporal foreign keys, and having
On 2/25/19 4:21 AM, Ibrar Ahmed wrote:
Great, to hear that you are working on that. Do you think I can help you
with this? I did some groundwork to make it possible. I can help in
coding/reviewing or even can take lead if you want to.
Hi Ibrar,
I'd love some help with this! I submitted my
On 2/22/19 11:31 AM, Euler Taveira wrote:
Em sex, 22 de fev de 2019 às 15:41, Ibrar Ahmed
escreveu:
While working on another PostgreSQL feature, I was thinking that we could use a
temporal table in PostgreSQL. Some existing databases offer this. I searched
for any discussion on the
Hi Jeff,
Thanks for sharing your thoughts and encouragement! :-)
> The model in [7] is
> based heavily on pack/unpack operators, and it's hard for me to see
> how those fit into SQL. Also, the pack/unpack operators have some
> theoretical weirdness that the book does not make clear*.
>
> *: My
49 matches
Mail list logo