Hello Hackers,
While working on inlining non-SQL SRFs [1] I noticed we don't have tests for when a PL/pgSQL
function requires materialize mode but doesn't have a result TupleDesc. Here is a patch adding tests
for that, as well as some other conditions around SRF calls with `SETOF RECORD` vs `TA
On 7/26/24 11:58, Tom Lane wrote:
> Heikki Linnakangas writes:
>> On 28/06/2024 01:01, Paul Jungwirth wrote:
>>> Another approach I considered is using a separate support request, e.g.
SupportRequestInlineSRF, and
>>> just calling it from inline_set_returning_fun
On 8/14/24 02:16, Peter Eisentraut wrote:
On 12.08.24 23:15, Paul Jungwirth wrote:
On 8/12/24 04:32, Aleksander Alekseev wrote:
[...] This function takes a Datum and the appropriate out function, and returns
a char *. So you
can do this:
(gdb) call format_datum(range_out, $1)
$2
On 8/12/24 04:32, Aleksander Alekseev wrote:
[...] This function takes a Datum and the appropriate out function, and returns
a char *. So you
can do this:
(gdb) call format_datum(range_out, $1)
$2 = 0x59162692d938 "[1,4)"
I assume a patch like this doesn't need documentation. Does it need a te
Hi Hackers,
Often in a debugger I've wanted to way to print Datums, in particular non-trivial ones like range
types. This came up a lot when I was working on multiranges, and I've wished for it lately while
working on UPDATE/DELETE FOR PORTION OF. But all the obvious approaches are inlined func
On 12/4/23 05:50, Tom Lane wrote:
Peter Eisentraut writes:
src/backend/nodes/print.c contains a number of functions that print node
types, mostly to stdout. Most of these are not actually used anywhere
in the code. Are they meant to be inserted into the code ad hoc for
debugging? Is anyone u
On 7/22/24 15:43, Tom Lane wrote:
Isaac Morland writes:
And for when this might be useful, the syntax for it already exists,
although a spurious error message is generated:
odyssey=> select (uw_term).*, count(*) from uw_term group by uw_term;
ERROR: column "uw_term.term_id" must appear in t
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: trunc
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
On 7/6/24 05:04, Andrey M. Borodin wrote:>> On 5 Jul 2024, at 23:46, Paul Jungwirth
wrote:
this commit adds support for all combinations of int2/int4/int8 for all five btree
operators (=/>).
Looks like a nice feature to have.
Would it make sense to do something similar to f
Hi Hackers,
I noticed that this query wasn't using my GiST index:
postgres=# create extension btree_gist;
CREATE EXTENSION
postgres=# create table t (id bigint, valid_at daterange, exclude using gist (id with =, valid_at
with &&));
CREATE TABLE
postgres=# explain select * from t where id = 5;
Hi Hackers,
Here is a proof-of-concept patch to inline set-returning functions (SRFs) besides those written in
SQL. We already try to inline SQL-language functions,[1] but that means you must have a static SQL
query. There is no way to get an inline-able query by dynamically building the sql in
yNumber, we would have a reliable "definition of uniqueness". UNIQUE
GiST indexes would raise an error if they detected a duplicate record.
Incidentally, this would also let us correct the error message about GiST not supporting unique,
fixing the problem you raised here:
On Sun, May
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
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
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 entr
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 att
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 v2-0001-Fix-ON-CONFLICT-DO-NOTHING-UPDATE-for-temporal-in.pat
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 mak
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 th
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
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
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
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 texteq
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
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" s
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 q
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 versi
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 "W
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
co
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 last,
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, a
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 col
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 pa
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 is
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 to
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 context
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 wou
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, ra
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
> comp
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 co
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;
multirange
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 arg
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 other
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 prima
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 wonderin
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 well
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
largely
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, sh
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:
Perhap
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
range
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 pat
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 PostgreSQ
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 q
63 matches
Mail list logo
