On Mon, Mar 14, 2016 at 8:43 PM, Peter Geoghegan wrote:
> Does this work with amcheck? Maybe it works with bt_index_check(), but
> not bt_index_parent_check()? I think that you need to make sure that
> _bt_compare() knows about this, too. That's because it isn't good
> enough to let a truncated in
On Mon, Mar 14, 2016 at 8:43 PM, Peter Geoghegan wrote:
> * I think the comparison logic may have a bug.
>
> Does this work with amcheck? Maybe it works with bt_index_check(), but
> not bt_index_parent_check()? I think that you need to make sure that
> _bt_compare() knows about this, too. That's b
On Mon, Mar 14, 2016 at 8:43 PM, Peter Geoghegan wrote:
>
> Does this work with amcheck? Maybe it works with bt_index_check(), but
> not bt_index_parent_check()? I think that you need to make sure that
> _bt_compare() knows about this, too. That's because it isn't good
> enough to let a truncated
On Wed, Sep 16, 2015 at 8:53 AM, Nicolas Barbier
wrote:
> After thinking about it a bit more, it indeed seems never useful to
> have f3 in the internal nodes if it is not part of the columns that
> determine the UNIQUE property. It could as well be pushed out of the
> internal nodes and only appea
2015-09-16 Rod Taylor :
> 2015-09-15 Anastasia Lubennikova :
>
>> - We have a table tbl(f1, f2, f3, f4).
>> - We want to have an unique index on (f1,f2).
>> - We want to have an index on (f1, f2, f3) which allow us to use index for
>> complex "where" clauses.
>
> Can someone write a query where F3
On 16 September 2015 at 14:03, José Luis Tallón
wrote:
> On 09/15/2015 06:57 PM, Anastasia Lubennikova wrote:
>
> Proposal Clarification.
> I see that discussion become too complicated. So, I'd like to clarify
> what we are talking about.
>
> [snip]
> What are we doing now:
> CREATE UNIQUE INDEX
On 09/15/2015 06:57 PM, Anastasia Lubennikova wrote:
Proposal Clarification.
I see that discussion become too complicated. So, I'd like to clarify
what we are talking about.
[snip]
What are we doing now:
CREATE UNIQUE INDEX on tbl(f1,f2);
CREATE INDEX on tbl(f1, f2, f3, f4);
[snip]
Suggestion
On 16 September 2015 at 10:38, Rod Taylor wrote:
>
>
> On Tue, Sep 15, 2015 at 12:57 PM, Anastasia Lubennikova <
> a.lubennik...@postgrespro.ru> wrote:
>
>>
>> Proposal Clarification.
>> I see that discussion become too complicated. So, I'd like to clarify
>> what we are talking about.
>>
>> We a
On Tue, Sep 15, 2015 at 12:57 PM, Anastasia Lubennikova <
a.lubennik...@postgrespro.ru> wrote:
>
> Proposal Clarification.
> I see that discussion become too complicated. So, I'd like to clarify
> what we are talking about.
>
> We are discussing 2 different improvements of index.
> The one is "pa
Proposal Clarification.
I see that discussion become too complicated. So, I'd like to clarify
what we are talking about.
We are discussing 2 different improvements of index.
The one is "partially unique index" and the other "index with included
columns".
Let's look at example.
- We have a
On 15 September 2015 at 23:51, Nicolas Barbier
wrote:
> 2015-09-15 David Rowley :
>
> > I'm also a bit confused where f3 comes in here. If it's UNIQUE on (f1,f2)
> > and we include f4. Where's f3?
>
> Columns f1, f2, f3 are in the internal nodes of the tree (i.e., they
> are used to find the ulti
2015-09-15 David Rowley :
> I'm also a bit confused where f3 comes in here. If it's UNIQUE on (f1,f2)
> and we include f4. Where's f3?
Columns f1, f2, f3 are in the internal nodes of the tree (i.e., they
are used to find the ultimate leaf nodes). f4 is only in the leaf
nodes. If f4 are typically
On 09/15/2015 12:45 PM, Anastasia Lubennikova wrote:
> 15.09.2015 12:11, Vik Fearing:
>> On 09/15/2015 10:57 AM, David Rowley wrote:
I agree, that form
> CREATE UNIQUE INDEX i ON t (f1, f2, f3) INCLUDE (f4)
> is clear. f4 will be used in row compare and actually planner will
> be a
Seems, final form is
CREATE INDEX idx ON tbl (f1, f2, f3) [UNIQUE ON (f1, f2)] [INCLUDE (f4)]
f1, f2, f3 are participated in index row comparence (btre, gist etc)
f1, f2 are participated in unique constrain and it gives warranty for
(f1, f2, f3[, f4]) uniqueness. Now supported by Btree only
f
On 15 September 2015 at 22:20, Anastasia Lubennikova <
a.lubennik...@postgrespro.ru> wrote:
> Hm, I think that it would be quite clear to set it to zero for non-unique
> indexes.
> *(nunique == 0)* is equal to *(indisunique==false)*.
>
> But maybe I've missed some reason why we should to save *ind
15.09.2015 12:11, Vik Fearing:
On 09/15/2015 10:57 AM, David Rowley wrote:
I agree, that form
CREATE UNIQUE INDEX i ON t (f1, f2, f3) INCLUDE (f4)
is clear. f4 will be used in row compare and actually planner will be able
to use it as unique index (f1, f2, f3) with additional f4 or as
as unique
15.09.2015 12:18, David Rowley:
On 12 September 2015 at 00:45, Anastasia Lubennikova
mailto:a.lubennik...@postgrespro.ru>>
wrote:
I've started work on a patch that allows to combine covering and
unique functionality.
Great to hear someone is working on this!
Thank you! It looks lik
On 12 September 2015 at 00:45, Anastasia Lubennikova <
a.lubennik...@postgrespro.ru> wrote:
> I've started work on a patch that allows to combine covering and unique
> functionality.
>
Great to hear someone is working on this!
> Next issue is pg_index changes.
> Now there's only a boolean flag
On 09/15/2015 10:57 AM, David Rowley wrote:
>> I agree, that form
>> > CREATE UNIQUE INDEX i ON t (f1, f2, f3) INCLUDE (f4)
>> > is clear. f4 will be used in row compare and actually planner will be able
>> > to use it as unique index (f1, f2, f3) with additional f4 or as
>> > as unique index (f1,
On 15 September 2015 at 18:16, Teodor Sigaev wrote:
> CREATE INDEX ... ON table (f1, f2, f3) UNIQUE(f1, f2) INCLUDE(f4);
>>
>
> I don't see an advantage this form. What is f3 column? just order? and f4
> will not be used for compare? At least now it requires additional checks
> that UNIQUE() fie
Why not normal syntax with optional INCLUDE ?
CREATE UNIQUE INDEX ON table (f1,f2,f3) INCLUDE (f4)
That's not the same thing. Then you're including f3 in the unique
constraint, which you may not want for uniqueness purposes, but may want
in the index for sorting. But then saying that
CREATE INDEX ... ON table (f1, f2, f3) UNIQUE(f1, f2) INCLUDE(f4);
I don't see an advantage this form. What is f3 column? just order? and
f4 will not be used for compare? At least now it requires additional
checks that UNIQUE() fields are the same as first columns in definition.
Non ordering
On 15/09/15 09:44, Jim Nasby wrote:
On 9/14/15 1:50 PM, Thomas Munro wrote:
CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}}
INDEX ON
table_name (column_name1, column_name2 ...);
I would use the first (simple) syntax and just throw an err
On 14 September 2015 at 23:12, Oleg Bartunov wrote:
>
>
>
> On Tue, Sep 15, 2015 at 12:44 AM, Jim Nasby
> wrote:
>
>> On 9/14/15 1:50 PM, Thomas Munro wrote:
>>
>>> CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}}
>>> INDEX ON
>>> table_name (colum
On 14 September 2015 at 22:44, Jim Nasby wrote:
> On 9/14/15 1:50 PM, Thomas Munro wrote:
>
>> CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}}
>> INDEX ON
>> table_name (column_name1, column_name2 ...);
>>
>>
>> I would use the first (simpl
On Tue, Sep 15, 2015 at 12:44 AM, Jim Nasby
wrote:
> On 9/14/15 1:50 PM, Thomas Munro wrote:
>
>> CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}}
>> INDEX ON
>> table_name (column_name1, column_name2 ...);
>>
>>
>> I would use the first (si
On 9/14/15 1:50 PM, Thomas Munro wrote:
CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}}
INDEX ON
table_name (column_name1, column_name2 ...);
I would use the first (simple) syntax and just throw an error if the
user tries to skip
It surprised me that you can INCLUDE extra columns on non-UNIQUE
indexes, since you could just add them as regular indexed columns for
the same effect. It looks like when you do that in SQL Server, the
extra columns are only stored on btree leaf pages and so can't be used
for searching or orderin
On Tue, Sep 15, 2015 at 6:08 AM, Teodor Sigaev wrote:
> CREATE INDEX index ON table (c1, c2, c3) UNIQUE ON (c1, c3);
>>>
>>> CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}} INDEX ON
>>> table_name (column_name1, column_name2 ...);
>>>
>>
>> I would use the first (simple) syntax and j
CREATE INDEX index ON table (c1, c2, c3) UNIQUE ON (c1, c3);
CREATE [UNIQUE {ON FIRST {COLUMN | n_unique_column COLUMNS}} INDEX ON
table_name (column_name1, column_name2 ...);
I would use the first (simple) syntax and just throw an error if the
user tries to skip a column on the UNIQUE clause.
On 9/11/15 7:45 AM, Anastasia Lubennikova wrote:
This idea has obvious restriction. We can set unique only for first
index columns.
There is no clear way to maintain following index.
CREATE INDEX index ON table (c1, c2, c3) UNIQUE ON (c1, c3);
So I suggest following syntax:
CREATE [UNIQUE {ON FI
31 matches
Mail list logo
