On Sun, 2009-09-20 at 10:08 -0700, Jeff Davis wrote:
> On Sun, 2009-09-20 at 13:01 -0400, Tom Lane wrote:
> > The current infrastructure for deferred uniqueness requires that the
> > thing actually be a constraint, with an entry in pg_constraint that
> > can carry the deferrability options. So unl
Jeff Davis writes:
> So, do I still omit it from information_schema?
My thought is yes --- any representation of it within information_schema
would be so inaccurate/incomplete as to be worse than useless, IMO.
Peter might have a different idea though ...
regards, tom lane
On Sun, 2009-09-20 at 13:01 -0400, Tom Lane wrote:
> The current infrastructure for deferred uniqueness requires that the
> thing actually be a constraint, with an entry in pg_constraint that
> can carry the deferrability options. So unless we want to rethink
> that, this might be a sufficient rea
BTW, I just thought of an issue that might change some of these
conclusions: what about supporting deferred constraint checking,
as we just recently managed to do for straight UNIQUE constraints?
I don't say that this has to be in the first cut, but it's something
we ought to try to leave room for
On Sun, 2009-09-20 at 12:31 -0400, Tom Lane wrote:
> > T1: inserts into index
> > T2: inserts into index
> > T1: checks index for conflicts, finds T2
> > T2: checks index for conflicts, finds T1
>
> You get a deadlock failure, because both transactions will wait for each
> other. So what? It's a
Jeff Davis writes:
> On Sat, 2009-09-19 at 18:00 -0400, Tom Lane wrote:
>> Well, you can't do it *exactly* the same way btree does, but what
>> I would envision is first insert the index tuple and then do a
>> dirty-snapshot search for conflicting tuples. The interlock against
>> conflicting conc
On Sat, 2009-09-19 at 23:15 -0400, Robert Haas wrote:
> I was wondering if we couldn't introduce a dummy tuple name similar to
> OLD and NEW, called, say, OTHER. Then instead of writing a =, you
> could write a = OTHER.a ... or perhaps a = OTHER.b ... although that
> might also open the door to mo
On Sat, Sep 19, 2009 at 2:51 PM, Jeff Davis wrote:
> On Sat, 2009-09-19 at 14:05 -0400, Tom Lane wrote:
>> What about them? It's not clear why you think this requires anything
>> special.
>
> >From a syntax standpoint, I need to represent one operator for every
> index column involved in the cons
On Sat, 2009-09-19 at 18:00 -0400, Tom Lane wrote:
> Well, you can't do it *exactly* the same way btree does, but what
> I would envision is first insert the index tuple and then do a
> dirty-snapshot search for conflicting tuples. The interlock against
> conflicting concurrent inserts doesn't nee
Jeff Davis writes:
> On Sat, 2009-09-19 at 16:43 -0400, Tom Lane wrote:
>> I don't understand why this isn't handled exactly the way unique
>> constraints are done now. Frankly, the amount of added complexity you
>> propose below is enough to make me want to reject the patch forthwith;
>> given t
On Sat, 2009-09-19 at 16:43 -0400, Tom Lane wrote:
> I don't understand why this isn't handled exactly the way unique
> constraints are done now. Frankly, the amount of added complexity you
> propose below is enough to make me want to reject the patch forthwith;
> given that it's going to be a rel
Jeff Davis writes:
> The design is that one backend needs to be able to see values being
> inserted by other backends before commit.
I don't understand why this isn't handled exactly the way unique
constraints are done now. Frankly, the amount of added complexity you
propose below is enough to m
On Sat, 2009-09-19 at 15:26 -0400, Tom Lane wrote:
> I haven't read the patch, but this whole discussion sounds to me like
> it means you're trying to plug things in at the wrong level. Indexes
> generally don't care where the values they are storing came from ---
> whether it's a simple column or
Jeff Davis writes:
> On Sat, 2009-09-19 at 14:05 -0400, Tom Lane wrote:
>> What about them? It's not clear why you think this requires anything
>> special.
>> From a syntax standpoint, I need to represent one operator for every
> index column involved in the constraint. So, if there's a function
On Sat, 2009-09-19 at 14:05 -0400, Tom Lane wrote:
> What about them? It's not clear why you think this requires anything
> special.
>From a syntax standpoint, I need to represent one operator for every
index column involved in the constraint. So, if there's a functional
index on ((a||b)::circle)
Jeff Davis writes:
> There's an important unresolved question with this patch that I need to
> address, which just came to light: what about functional/expression
> indexes?
What about them? It's not clear why you think this requires anything
special.
regards, tom lane
I think we have a reasonable consensus around the name "operator
exclusion constraints", Robert Haas's suggestion. I am OK with that
name, and it got support from David Fetter and Tom Lane. As David Fetter
said, it's useful for the name to hint at the API.
Peter had some reasonable objections to t
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Wed, Sep 16, 2009 at 09:45:52AM -0700, Jeff Davis wrote:
> On Wed, 2009-09-16 at 15:11 +0200, to...@tuxteam.de wrote:
> > One question: does the operator have to be reflexive? I.e. "A op A holds
> > for all A"?
>
> I don't think that reflexivity is
On Wed, Sep 16, 2009 at 3:14 AM, Peter Eisentraut wrote:
> On Tue, 2009-09-15 at 12:37 -0400, Robert Haas wrote:
>> Instead of calling these generalized index constraints, I wonder if we
>> oughtn't to be calling them something like "don't-overlap constraints"
>> (that's a bad name, but something
On Tue, 2009-09-15 at 22:52 +1000, Brendan Jurd wrote:
> I'm just getting started reviewing this version now. I noticed that
> your patch seems to have been generated by git.
Ok, I now have a public git repo on git.postgresql.org, and I rebased my
patch before I pushed it.
See updates in my "gen
On Wed, 2009-09-16 at 10:14 +0300, Peter Eisentraut wrote:
> What they should be called is generalized unique constraints, without
> reference to "index". Because what they generalize is the operator by
> which uniqueness is determined.
How about GUC, for short? ;-)
Do you think that Tomás's su
On Wed, 2009-09-16 at 15:11 +0200, to...@tuxteam.de wrote:
> One question: does the operator have to be reflexive? I.e. "A op A holds
> for all A"?
I don't think that reflexivity is a strict requirement. You could make
this a constraint over a boolean attribute such that false conflicts
with true
On Tue, Sep 15, 2009 at 7:02 PM, Joshua Tolley wrote:
> On Tue, Sep 15, 2009 at 05:52:35PM -0400, Tom Lane wrote:
>> Jeff Davis writes:
>> > On Tue, 2009-09-15 at 14:42 -0700, Jeff Davis wrote:
>> >> operator constraints
>> >> operator exclusion constraints
>> >> operator conflict constraints
>>
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Tue, Sep 15, 2009 at 10:28:28AM -0700, Jeff Davis wrote:
> On Tue, 2009-09-15 at 13:16 -0400, Robert Haas wrote:
> > Uhh so what happens if I create an index constraint using the
> > +(integer, integer) operator?
>
> You can use any operator th
On Tue, 2009-09-15 at 12:22 -0700, Jeff Davis wrote:
> I don't like using the word "unique" in the description, I think it
> only
> adds to the confusion.
It would emphasize that a unique constraint is a common special case of
the feature.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@p
On Tue, 2009-09-15 at 12:37 -0400, Robert Haas wrote:
> Instead of calling these generalized index constraints, I wonder if we
> oughtn't to be calling them something like "don't-overlap constraints"
> (that's a bad name, but something along those lines). They're not
> really general at all, excep
On Tue, Sep 15, 2009 at 05:52:35PM -0400, Tom Lane wrote:
> Jeff Davis writes:
> > On Tue, 2009-09-15 at 14:42 -0700, Jeff Davis wrote:
> >> operator constraints
> >> operator exclusion constraints
> >> operator conflict constraints
> >> conflict operator constraints
> >> operator index constraint
Jeff Davis writes:
> On Tue, 2009-09-15 at 14:42 -0700, Jeff Davis wrote:
>> operator constraints
>> operator exclusion constraints
>> operator conflict constraints
>> conflict operator constraints
>> operator index constraints
>> index constraints
>> generalized index constraints
>> something els
On Tue, 2009-09-15 at 14:42 -0700, Jeff Davis wrote:
> operator constraints
> operator exclusion constraints
> operator conflict constraints
> conflict operator constraints
> operator index constraints
> index constraints
> generalized index constraints
> something else?
Just to add a couple more
On Tue, 2009-09-15 at 12:49 -0700, David Fetter wrote:
> > I like this much better. Maybe "index operator constraints" or "operator
> > index constraints"?
>
> The word, "index" goes to implementation details, which may change.
Ok, let's vote on a name then:
operator constraints
operator exclusi
On Tue, Sep 15, 2009 at 12:22:46PM -0700, Jeff Davis wrote:
> On Tue, 2009-09-15 at 12:03 -0700, David Fetter wrote:
> > * "operator-based constraints"
> > A little math-ier, but talks about the API rather than details of
> > the server implementation.
>
> I like this much better. Maybe "i
Jeff Davis writes:
> On Tue, 2009-09-15 at 14:49 -0400, Tom Lane wrote:
>> Does it behave sanely for operators that are non-commutative, such
>> as '>'? (I'm not even very sure that I know what "sanely" would be
>> in such a case.)
> If you try it, my current patch won't stop you. Maybe I should
On Tue, 2009-09-15 at 12:03 -0700, David Fetter wrote:
> Interesting :) I take it op1..opN (it's opN, not op2, right?) need to
> commute?
Yeah, it's opN.
And they should commute, but my current patch won't stop you. I think I
should stop that though, it's pretty difficult to think of a good
use-
On Tue, 2009-09-15 at 14:49 -0400, Tom Lane wrote:
> Does it behave sanely for operators that are non-commutative, such
> as '>'? (I'm not even very sure that I know what "sanely" would be
> in such a case.)
One of the requirements is commutativity (I called it "symmetry" in the
docs, for some re
On Tue, Sep 15, 2009 at 3:03 PM, David Fetter wrote:
> * "operator-based constraints"
> A little math-ier, but talks about the API rather than details of
> the server implementation.
Or operator-exclusion constraints? Operator-based exclusion constraints?
I'm feeling exclusive.
...Robert
On Tue, Sep 15, 2009 at 11:31:48AM -0700, Jeff Davis wrote:
> On Tue, 2009-09-15 at 13:48 -0400, Robert Haas wrote:
> > So it allows us to create constraints of the following form?
> >
> > For all A in the index, there exists no B in the index such that the
> > given operator (which must be a bina
Jeff Davis writes:
> On Tue, 2009-09-15 at 13:16 -0400, Robert Haas wrote:
>> Uhh so what happens if I create an index constraint using the
>> +(integer, integer) operator?
> You can use any operator that has an index search strategy. Overlaps is
> probably the most useful, but you could imag
On Tue, 2009-09-15 at 13:48 -0400, Robert Haas wrote:
> So it allows us to create constraints of the following form?
>
> For all A in the index, there exists no B in the index such that the
> given operator (which must be a binary operator returning boolean)
> holds of A and B.
Yes. And it's slig
On Tue, Sep 15, 2009 at 1:28 PM, Jeff Davis wrote:
> On Tue, 2009-09-15 at 13:16 -0400, Robert Haas wrote:
>> Uhh so what happens if I create an index constraint using the
>> +(integer, integer) operator?
>
> You can use any operator that has an index search strategy. Overlaps is
> probably th
On Tue, 2009-09-15 at 13:16 -0400, Robert Haas wrote:
> Uhh so what happens if I create an index constraint using the
> +(integer, integer) operator?
You can use any operator that has an index search strategy. Overlaps is
probably the most useful, but you could imagine other operators, like a
2009/9/16 Robert Haas :
> On Tue, Sep 15, 2009 at 1:14 PM, Brendan Jurd wrote:
>> 2009/9/16 Robert Haas :
>>> On Tue, Sep 15, 2009 at 12:54 PM, Jeff Davis wrote:
I don't want to call them "don't overlap constraints", because it's not
limited to a non-overlapping constraint.
>>>
>>> Oh.
On Tue, Sep 15, 2009 at 1:14 PM, Brendan Jurd wrote:
> 2009/9/16 Robert Haas :
>> On Tue, Sep 15, 2009 at 12:54 PM, Jeff Davis wrote:
>>> I don't want to call them "don't overlap constraints", because it's not
>>> limited to a non-overlapping constraint.
>>
>> Oh. What else can you do with it?
>
2009/9/16 Robert Haas :
> On Tue, Sep 15, 2009 at 12:54 PM, Jeff Davis wrote:
>> I don't want to call them "don't overlap constraints", because it's not
>> limited to a non-overlapping constraint.
>
> Oh. What else can you do with it?
Anything that there is an operator for.
Cheers,
BJ
--
Sent
On Tue, Sep 15, 2009 at 12:54 PM, Jeff Davis wrote:
> On Tue, 2009-09-15 at 12:37 -0400, Robert Haas wrote:
>> Instead of calling these generalized index constraints, I wonder if we
>> oughtn't to be calling them something like "don't-overlap constraints"
>> (that's a bad name, but something along
On Tue, 2009-09-15 at 12:37 -0400, Robert Haas wrote:
> Instead of calling these generalized index constraints, I wonder if we
> oughtn't to be calling them something like "don't-overlap constraints"
> (that's a bad name, but something along those lines). They're not
> really general at all, excep
2009/9/16 Robert Haas :
> Instead of calling these generalized index constraints, I wonder if we
> oughtn't to be calling them something like "don't-overlap constraints"
> (that's a bad name, but something along those lines). They're not
> really general at all, except compared to uniqueness const
On Tue, Sep 15, 2009 at 12:18 PM, Jeff Davis wrote:
> On Tue, 2009-09-15 at 22:52 +1000, Brendan Jurd wrote:
>> I'm just getting started reviewing this version now. I noticed that
>> your patch seems to have been generated by git. Are you hosting this
>> work on a public repo somewhere that I ca
On Tue, 2009-09-15 at 08:08 -0600, Joshua Tolley wrote:
> Perhaps the tuple that caused the violation as well, like UNIQUE index
> violations already do? Even if we know what constraint has been tripped, we
> might not know what value did it.
Or, even better, include both tuples. With these new co
On Tue, 2009-09-15 at 23:21 +1000, Brendan Jurd wrote:
> How about also including the name of the constraint (or index) that
> was violated? I could imagine this error message being frustrating
> for someone who had a table with multiple index constraints, as they
> wouldn't know which one had rai
On Tue, 2009-09-15 at 22:52 +1000, Brendan Jurd wrote:
> I'm just getting started reviewing this version now. I noticed that
> your patch seems to have been generated by git. Are you hosting this
> work on a public repo somewhere that I can pull from?
I just requested a public repo. I will publi
On Tue, Sep 15, 2009 at 11:21:14PM +1000, Brendan Jurd wrote:
> 2009/9/15 Jeff Davis :
> > Attached is the latest version.
> >
>
> The new error message for a conflict is:
>
> ERROR: index constraint violation detected
> DETAIL: tuple conflicts with existing data
>
> How about also including t
2009/9/15 Jeff Davis :
> Attached is the latest version.
>
The new error message for a conflict is:
ERROR: index constraint violation detected
DETAIL: tuple conflicts with existing data
How about also including the name of the constraint (or index) that
was violated? I could imagine this erro
2009/9/15 Jeff Davis :
> Attached is the latest version.
Hi Jeff,
I'm just getting started reviewing this version now. I noticed that
your patch seems to have been generated by git. Are you hosting this
work on a public repo somewhere that I can pull from? Also I think
the committers generally
On Sun, 2009-09-13 at 19:08 +1000, Brendan Jurd wrote:
> Any update on this patch?
Attached is the latest version.
Changes:
* Merged with HEAD
* Changed from storing the information in pg_index to pg_constraint.
This required rewriting a large portion of the patch, so it's not a
clean
On Sun, 2009-09-13 at 19:08 +1000, Brendan Jurd wrote:
> The September CF starts in a couple of days, so this patch is in
> danger of missing the boat.
Thanks for keeping track. I accomplished a significant amount today, so
there's still hope for 9/15.
I will most likely just focus on the core fu
2009/8/21 Brendan Jurd :
> 2009/8/21 Jeff Davis :
>> On Fri, 2009-08-21 at 12:23 +1000, Brendan Jurd wrote:
>>> The current behaviour seems to be predicated on the unique constraint
>>> being an integral part of the index itself. While this might be true
>>> from a system catalog point of view (pg
On Fri, Aug 21, 2009 at 12:23:15PM +1000, Brendan Jurd wrote:
> 2009/8/21 Jeff Davis :
> > If they include indexes and not constraints, I think we should
> > follow the same policy as unique constraints, and create the index
> > and the constraint.
> >
> > The behavior seems a little strange to me,
Alvaro Herrera writes:
> NOTICEs is what we do with index creation on primary key, unique
> indexes, and sequences on serial columns, and I think they are seen as
> just noise by everyone except novices. Do we want to add more?
> Maybe they should be INFO, so that they are shown to the client bu
Brendan Jurd escribió:
> I would be fine with a NOTICE in the former case, so something like
> this would be cool
>
> # CREATE TABLE foo (LIKE bar INCLUDING INDEXES);
> NOTICE: INCLUDING INDEXES will also include any constraints on those indexes.
> HINT: Specify EXCLUDING CONSTRAINTS to omit them
Hi,
Le 21 août 09 à 06:04, Jeff Davis a écrit :
There is not much of a problem with backwards compatibility. LIKE is
shorthand (not stored in catalogs), so it doesn't affect
pg_dump/restore. And hopefully there aren't a lot of apps out there
creating tables dynamically using the LIKE syntax.
I
2009/8/21 Jeff Davis :
> On Fri, 2009-08-21 at 12:23 +1000, Brendan Jurd wrote:
>> The current behaviour seems to be predicated on the unique constraint
>> being an integral part of the index itself. While this might be true
>> from a system catalog point of view (pg_index.indisunique), if a user
On Fri, 2009-08-21 at 12:23 +1000, Brendan Jurd wrote:
> This may be an opportunity to fix it.
>
> The current behaviour seems to be predicated on the unique constraint
> being an integral part of the index itself. While this might be true
> from a system catalog point of view (pg_index.indisuniq
On Fri, Aug 21, 2009 at 3:23 AM, Brendan Jurd wrote:
> They'd expect it to create an index sans the unique
> constraint. Ignoring the user's intention and copying the index as-is
> (including the unique constraint) would be unfriendly.
>
> Unless the SQL spec demands that we do so?
There are no i
2009/8/21 Jeff Davis :
> If they include indexes and not constraints, I think we should follow
> the same policy as unique constraints, and create the index and the
> constraint.
>
> The behavior seems a little strange to me, but that's the current
> behavior for unique indexes.
This may be an opp
On Fri, 2009-08-21 at 11:14 +1000, Brendan Jurd wrote:
> As an aside, Jeff, have you considered how this feature would interact
> with CREATE TABLE ... LIKE parent_table [ { INCLUDING | EXCLUDING } {
> DEFAULTS | CONSTRAINTS | INDEXES } ] ... }? What if someone asks to
> include indexes but not co
2009/8/21 Heikki Linnakangas :
> Jeff Davis wrote:
>> I'm leaning toward not allowing it at CREATE TABLE time.
>
> Seems reasonable to me too.
>
+1
There are plenty of other things to do with tables that you can't mix
directly into a CREATE TABLE statement (grant permissions, create
triggers, cha
Jeff Davis wrote:
> On Thu, 2009-08-20 at 11:47 +0300, Heikki Linnakangas wrote:
>> That sounds like the constraint is based on an existing index, but there
>> can't be any existing indexes on a table that hasn't been created yet.
>> If this creates the index, then the syntax needs to support speci
On Thu, 2009-08-20 at 11:47 +0300, Heikki Linnakangas wrote:
> That sounds like the constraint is based on an existing index, but there
> can't be any existing indexes on a table that hasn't been created yet.
> If this creates the index, then the syntax needs to support specifying
> index access me
Jeff Davis wrote:
> I'm going to try to get this patch ready for the 9-15 commitfest. Here
> are a few design updates:
>
> (1) Language:
>
> I think that the new language should be a table constraint, and I think
> there's a consensus on that. The specific language I have in mind is:
>
> CREAT
I'm going to try to get this patch ready for the 9-15 commitfest. Here
are a few design updates:
(1) Language:
I think that the new language should be a table constraint, and I think
there's a consensus on that. The specific language I have in mind is:
CREATE TABLE (
...,
INDEX CONSTR
On Fri, 2009-07-17 at 09:51 +1000, Brendan Jurd wrote:
> I like that idea ... although how would this interact (if at all) with
> the existing pg_index.isunique flag? Would it become deprecated in
> favour of using indconstrats, or would you actually look at switching
> isunique to TRUE if somebod
2009/7/17 Jeff Davis :
> Another idea that I thought about is that:
>
> ALTER TABLE foo ADD UNIQUE (a, b) USING foo_idx;
>
> could be a shorthand for:
>
> ALTER TABLE foo ADD INDEX CONSTRAINT (a =, b =) USING foo_idx;
>
> The benefit is that it could go over GiST indexes or hash indexes, not
>
On Thu, 2009-07-16 at 15:22 +1000, Brendan Jurd wrote:
> I had a play around with the feature in psql. I think the syntax is
> okay, but using "ALTER TABLE ... ADD" as you mentioned upthread could
> be a better option.
Ok, I think we're pretty much settled on that option then.
Another idea that
2009/7/15 Jeff Davis :
> Updated patch attached.
>
> Changes:
> * Added syntax support:
> CREATE INDEX foo_idx ON foo ... (a CONSTRAINT =, b CONSTRAINT &&);
> * More aggressively clear the shared memory entries to avoid
> unnecessary checks
> * Code cleanup
>
> TODO:
> * When adding const
Updated patch attached.
Changes:
* Added syntax support:
CREATE INDEX foo_idx ON foo ... (a CONSTRAINT =, b CONSTRAINT &&);
* More aggressively clear the shared memory entries to avoid
unnecessary checks
* Code cleanup
TODO:
* When adding constraint to table with data already in it,
On Sat, 2009-07-11 at 19:06 -0400, Tom Lane wrote:
> > Is it possible to re-add amgettuple to GIN, and just set the cost high
> We wouldn't have deleted it if it were practical to make it work.
Can you elaborate a little?
Following the thread, I see:
http://archives.postgresql.org/pgsql-hackers
Jeff Davis writes:
> Is it possible to re-add amgettuple to GIN, and just set the cost high
> so it's not chosen by the planner? Or is there some reason this is
> fundamentally a bad idea (or won't work at all)?
We wouldn't have deleted it if it were practical to make it work.
Right now this patch does not support GIN because GIN doesn't support
amgettuple.
It could be made to support GIN by doing a bitmap index scan, manually
fetching the next tuple (or, if it's lossy, the next one on the page),
checking it against the snapshot, and then rechecking it to make sure it
s
Tom Lane wrote:
> ... I think it might be interesting to turn
> around Jeff's syntax sketch and provide a way to say that a CONSTRAINT
> declaration should depend on some previously added index, eg
> something like
>
> ALTER TABLE tab ADD CONSTRAINT UNIQUE (col1, col2) USING index
>
Is th
On Tue, 2009-07-07 at 13:22 -0400, Tom Lane wrote:
> Also, if hash indexes were a realistic alternative to btree for this,
> we'd already have come up against the problem that the CONSTRAINT syntax
> doesn't provide any way to specify what kind of index you want to use
> underneath the constraint.
On Tue, 2009-07-07 at 14:57 -0400, Tom Lane wrote:
> I don't think this even approximates the need --- in particular it's not
> clear what the semantics of combination across different index columns
> are. I assume you've hot-wired it so that several BTEqualStrategyNumber
> columns will work like
Jeff Davis writes:
> On Tue, 2009-07-07 at 18:36 +0100, Simon Riggs wrote:
>> On Tue, 2009-07-07 at 13:22 -0400, Tom Lane wrote:
>> It is likely to be useful in the future to allow an index with N
>> columns, yet which can provide uniqueness with < N of those columns.
>> This capability is known a
On Tue, 2009-07-07 at 18:36 +0100, Simon Riggs wrote:
> On Tue, 2009-07-07 at 13:22 -0400, Tom Lane wrote:
> It is likely to be useful in the future to allow an index with N
> columns, yet which can provide uniqueness with < N of those columns.
> This capability is known as covered indexes and will
On Tue, Jul 7, 2009 at 6:22 PM, Tom Lane wrote:
>
> This seems a bit pointless. There is certainly not any use case for a
> constraint without an enforcement mechanism (or at least none the PG
> community is likely to consider legitimate ;-)). And it's not very
> realistic to suppose that you'd c
On Tue, 2009-07-07 at 13:22 -0400, Tom Lane wrote:
> Jeff Davis writes:
> > On Mon, 2009-07-06 at 18:27 +0100, Simon Riggs wrote:
> >> In many cases, people add unique indexes solely to allow replication to
> >> work correctly. The index itself may never be used, especially in high
> >> volume ap
On Tue, 2009-07-07 at 13:22 -0400, Tom Lane wrote:
> ALTER TABLE tab ADD CONSTRAINT UNIQUE (col1, col2) USING index
This would be very useful, though perhaps only because we do not have
REINDEX CONCURRENTLY.
It is likely to be useful in the future to allow an index with N
columns, yet which can
Jeff Davis writes:
> On Mon, 2009-07-06 at 18:27 +0100, Simon Riggs wrote:
>> In many cases, people add unique indexes solely to allow replication to
>> work correctly. The index itself may never be used, especially in high
>> volume applications.
> Interesting. Maybe we should at least try to le
CREATE INDEX test_idx ON test USING gist
(i CONSTRAINT =, c CONSTRAINT &&);
which would avoid the need for updating the catalog, of course.
Hmm, looks like "index"-fied table's constrains
--
Teodor Sigaev E-mail: teo...@sigaev.ru
On Mon, 2009-07-06 at 18:27 +0100, Simon Riggs wrote:
> In many cases, people add unique indexes solely to allow replication to
> work correctly. The index itself may never be used, especially in high
> volume applications.
Interesting. Maybe we should at least try to leave room for this feature
t
On Mon, Jul 6, 2009 at 6:20 PM, Jeff Davis wrote:
> On Mon, 2009-07-06 at 17:02 +0100, Greg Stark wrote:
>> On Mon, Jul 6, 2009 at 4:57 PM, Jeff Davis wrote:
>> >
>> > Exactly, you already know my use case ;) My goal is a "temporal key",
>> > where you can't have overlapping intervals of time, e.g.
On Mon, 2009-07-06 at 08:50 -0700, Jeff Davis wrote:
> On Mon, 2009-07-06 at 11:56 +0100, Simon Riggs wrote:
> > I think it will be useful to separate the concepts of a constraint from
> > the concept of an index. It seems possible to have a UNIQUE constraint
> > that doesn't help at all in locati
On Mon, 2009-07-06 at 17:02 +0100, Greg Stark wrote:
> On Mon, Jul 6, 2009 at 4:57 PM, Jeff Davis wrote:
> >
> > Exactly, you already know my use case ;) My goal is a "temporal key",
> > where you can't have overlapping intervals of time, e.g. the constraint
> > "nobody can be two places at the sam
On Mon, Jul 6, 2009 at 4:57 PM, Jeff Davis wrote:
>
> Exactly, you already know my use case ;) My goal is a "temporal key",
> where you can't have overlapping intervals of time, e.g. the constraint
> "nobody can be two places at the same time".
Incidentally to handle non-overlapping ranges you don
On Mon, 2009-07-06 at 07:30 -0700, David Fetter wrote:
> > It would be useful to see a real example of what this can be used
> > for.
>
> Constraints like "these intervals can't overlap" would be one. It's
> handy in calendaring applications, for example.
Exactly, you already know my use case ;)
On Mon, 2009-07-06 at 11:56 +0100, Simon Riggs wrote:
> I think it will be useful to separate the concepts of a constraint from
> the concept of an index. It seems possible to have a UNIQUE constraint
> that doesn't help at all in locating rows, just in proving that the rows
> are unique.
That wou
On Mon, 2009-07-06 at 12:28 +0100, Greg Stark wrote:
> He only needs to handle inserts for the period they're actively being
> inserted into the index. Once they're in the index he'll find them
> using the index scan. In other words this is all a proxy for the way
> btree locks index pages while it
On Mon, Jul 06, 2009 at 11:56:41AM +0100, Simon Riggs wrote:
> On Sun, 2009-07-05 at 17:28 -0700, Jeff Davis wrote:
> > This is a follow up to my old proposal here:
> >
> > http://archives.postgresql.org/pgsql-hackers/2008-06/msg00404.php
> >
>
> > Any input is appreciated (design problems, impl
On Mon, Jul 6, 2009 at 11:56 AM, Simon Riggs wrote:
> How will you cope with a large COPY? Surely there can be more than one
> concurrent insert from any backend?
He only needs to handle inserts for the period they're actively being
inserted into the index. Once they're in the index he'll find the
On Sun, 2009-07-05 at 17:28 -0700, Jeff Davis wrote:
> This is a follow up to my old proposal here:
>
> http://archives.postgresql.org/pgsql-hackers/2008-06/msg00404.php
>
> Any input is appreciated (design problems, implementation, language
> ideas, or anything else). I'd like to get it into s
This is a follow up to my old proposal here:
http://archives.postgresql.org/pgsql-hackers/2008-06/msg00404.php
Top pointed out a few problems here:
http://archives.postgresql.org/pgsql-hackers/2008-06/msg00427.php
Here are my updated answers:
1. Not a problem with the new design, which checks
100 matches
Mail list logo
