Thread: Ticket 118: Exclusion constraints
Hi, At least, I finally did it :) This patch adds support for exclusion constraint (9.0 new feature). All examples from Magnus's talk (Beyond Unique) work with this patch. Anyway, this patch needs more work: 1. Displayed in both Constraints and Indexes I don't know why but each exclusion constraint is displayed two times. In the indexes node (where it doesn't belong), and in the constraints node (where it belongs). Any idea why? should not be too hard to find out, but I'm unable to find it right now. 2. Need an icon I copied the unique constraint icon, but we really need another one for this specific constraint. 3. Probably some misunderstanding of the feature Should we propose all operators for the columns? or just the ones related to 1. the operator class 2. the column's type 3. something else? actually, I propose all of them. But I don't think this is what we should do. In Magnus's talk, I see that only GiST is supported right now as an index method. In PostgreSQL fine manual, it says that, to be usable, an index should provide the amgettuple method. And I see three of them support this method. Who's wrong? :) Patch attached. Comments welcomed :) -- Guillaume http://www.postgresql.fr http://dalibo.com
Attachment
On Sat, Jul 10, 2010 at 22:49, Guillaume Lelarge <guillaume@lelarge.info> wrote: > Hi, > > At least, I finally did it :) > > This patch adds support for exclusion constraint (9.0 new feature). All > examples from Magnus's talk (Beyond Unique) work with this patch. > > Anyway, this patch needs more work: > > 1. Displayed in both Constraints and Indexes > > I don't know why but each exclusion constraint is displayed two times. > In the indexes node (where it doesn't belong), and in the constraints > node (where it belongs). Any idea why? should not be too hard to find > out, but I'm unable to find it right now. How do we do it with Primary Keys? It's the same thing - both an index and a constraint. There should be some code to hide it, thus similar code should be needed here. > 2. Need an icon > > I copied the unique constraint icon, but we really need another one for > this specific constraint. > > 3. Probably some misunderstanding of the feature > > Should we propose all operators for the columns? or just the ones > related to 1. the operator class 2. the column's type 3. something else? > actually, I propose all of them. But I don't think this is what we > should do. You need to show only commutative operators. That may be we need to filter on oid=oprcom in pg_operator - I'm not sure of that though, I didn't really investigate, but it looks like a reasonable thing. And yes, it should definitely be filtered on the column types. > In Magnus's talk, I see that only GiST is supported right now as an > index method. In PostgreSQL fine manual, it says that, to be usable, an > index should provide the amgettuple method. And I see three of them > support this method. Who's wrong? :) Trust The Fine Manual. Always remember that some of those things may also have changed since my talk :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Le 12/07/2010 12:13, Magnus Hagander a écrit : > On Sat, Jul 10, 2010 at 22:49, Guillaume Lelarge <guillaume@lelarge.info> wrote: >> Hi, >> >> At least, I finally did it :) >> >> This patch adds support for exclusion constraint (9.0 new feature). All >> examples from Magnus's talk (Beyond Unique) work with this patch. >> >> Anyway, this patch needs more work: >> >> 1. Displayed in both Constraints and Indexes >> >> I don't know why but each exclusion constraint is displayed two times. >> In the indexes node (where it doesn't belong), and in the constraints >> node (where it belongs). Any idea why? should not be too hard to find >> out, but I'm unable to find it right now. > > How do we do it with Primary Keys? It's the same thing - both an index > and a constraint. There should be some code to hide it, thus similar > code should be needed here. > Actually, I tried to understand how it worked for primary keys and unique indexes. So far, I'm still wondering how it could hide them. >> 2. Need an icon >> >> I copied the unique constraint icon, but we really need another one for >> this specific constraint. >> >> 3. Probably some misunderstanding of the feature >> >> Should we propose all operators for the columns? or just the ones >> related to 1. the operator class 2. the column's type 3. something else? >> actually, I propose all of them. But I don't think this is what we >> should do. > > You need to show only commutative operators. > > That may be we need to filter on oid=oprcom in pg_operator - I'm not > sure of that though, I didn't really investigate, but it looks like a > reasonable thing. > > And yes, it should definitely be filtered on the column types. > > >> In Magnus's talk, I see that only GiST is supported right now as an >> index method. In PostgreSQL fine manual, it says that, to be usable, an >> index should provide the amgettuple method. And I see three of them >> support this method. Who's wrong? :) > > Trust The Fine Manual. > > Always remember that some of those things may also have changed since > my talk :-) > Sure. Thanks for your comments, I'll work on this at a later time. -- Guillaume http://www.postgresql.fr http://dalibo.com
On Mon, 12 Jul 2010 12:13:03 +0200, Magnus Hagander <magnus@hagander.net> wrote: > On Sat, Jul 10, 2010 at 22:49, Guillaume Lelarge <guillaume@lelarge.info> > wrote: >> Hi, >> >> At least, I finally did it :) >> >> This patch adds support for exclusion constraint (9.0 new feature). All >> examples from Magnus's talk (Beyond Unique) work with this patch. >> >> Anyway, this patch needs more work: >> >> 1. Displayed in both Constraints and Indexes >> >> I don't know why but each exclusion constraint is displayed two times. >> In the indexes node (where it doesn't belong), and in the constraints >> node (where it belongs). Any idea why? should not be too hard to find >> out, but I'm unable to find it right now. > > How do we do it with Primary Keys? It's the same thing - both an index > and a constraint. There should be some code to hide it, thus similar > code should be needed here. > Fixed. >> 2. Need an icon >> >> I copied the unique constraint icon, but we really need another one for >> this specific constraint. >> >> 3. Probably some misunderstanding of the feature >> >> Should we propose all operators for the columns? or just the ones >> related to 1. the operator class 2. the column's type 3. something else? >> actually, I propose all of them. But I don't think this is what we >> should do. > > You need to show only commutative operators. > > That may be we need to filter on oid=oprcom in pg_operator - I'm not > sure of that though, I didn't really investigate, but it looks like a > reasonable thing. > > And yes, it should definitely be filtered on the column types. > Done. The filter is a simple "(oprleft=<column's type OID> OR oprright=<column's type OID>) AND oprcom > 0". The list is much lighter now :) >> In Magnus's talk, I see that only GiST is supported right now as an >> index method. In PostgreSQL fine manual, it says that, to be usable, an >> index should provide the amgettuple method. And I see three of them >> support this method. Who's wrong? :) > > Trust The Fine Manual. > > Always remember that some of those things may also have changed since > my talk :-) > OK. So here is version 2 of this patch. Still lacks the icon. I would draw one if I had any idea on what to draw :) -- Guillaume http://www.postgresql.fr http://dalibo.com
Attachment
On Tue, Jul 20, 2010 at 1:02 PM, guillaume <guillaume@lelarge.info> wrote: > So here is version 2 of this patch. Still lacks the icon. I would draw one > if I had any idea on what to draw :) Constraint icon with a cross through it? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company
On Tue, 20 Jul 2010 13:05:30 +0100, Dave Page <dpage@pgadmin.org> wrote: > On Tue, Jul 20, 2010 at 1:02 PM, guillaume <guillaume@lelarge.info> wrote: > >> So here is version 2 of this patch. Still lacks the icon. I would draw >> one >> if I had any idea on what to draw :) > > Constraint icon with a cross through it? > Like this one? yeah, I know, I'm no designer :) -- Guillaume http://www.postgresql.fr http://dalibo.com
Attachment
20 July 2010 15:41, guillaume <guillaume@lelarge.info> wrote: > On Tue, 20 Jul 2010 13:05:30 +0100, Dave Page <dpage@pgadmin.org> wrote: >> On Tue, Jul 20, 2010 at 1:02 PM, guillaume <guillaume@lelarge.info> > wrote: >> >>> So here is version 2 of this patch. Still lacks the icon. I would draw >>> one >>> if I had any idea on what to draw :) >> >> Constraint icon with a cross through it? >> > > Like this one? yeah, I know, I'm no designer :) > > > -- > Guillaume > http://www.postgresql.fr > http://dalibo.com > It could be clearer. Deep orange with a red cross is difficult to visually work out. Thom
On Tue, Jul 20, 2010 at 4:07 PM, Thom Brown <thombrown@gmail.com> wrote: > 20 July 2010 15:41, guillaume <guillaume@lelarge.info> wrote: >> On Tue, 20 Jul 2010 13:05:30 +0100, Dave Page <dpage@pgadmin.org> wrote: >>> On Tue, Jul 20, 2010 at 1:02 PM, guillaume <guillaume@lelarge.info> >> wrote: >>> >>>> So here is version 2 of this patch. Still lacks the icon. I would draw >>>> one >>>> if I had any idea on what to draw :) >>> >>> Constraint icon with a cross through it? >>> >> >> Like this one? yeah, I know, I'm no designer :) >> >> >> -- >> Guillaume >> http://www.postgresql.fr >> http://dalibo.com >> > > It could be clearer. Deep orange with a red cross is difficult to > visually work out. I have a nice package for building icons. I'll have a play with it later. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company
On Tue, 20 Jul 2010 16:09:22 +0100, Dave Page <dpage@pgadmin.org> wrote: > On Tue, Jul 20, 2010 at 4:07 PM, Thom Brown <thombrown@gmail.com> wrote: >> 20 July 2010 15:41, guillaume <guillaume@lelarge.info> wrote: >>> On Tue, 20 Jul 2010 13:05:30 +0100, Dave Page <dpage@pgadmin.org> wrote: >>>> On Tue, Jul 20, 2010 at 1:02 PM, guillaume <guillaume@lelarge.info> >>> wrote: >>>> >>>>> So here is version 2 of this patch. Still lacks the icon. I would draw >>>>> one >>>>> if I had any idea on what to draw :) >>>> >>>> Constraint icon with a cross through it? >>>> >>> >>> Like this one? yeah, I know, I'm no designer :) >>> >> >> It could be clearer. Deep orange with a red cross is difficult to >> visually work out. > > I have a nice package for building icons. I'll have a play with it later. Great, thanks :) -- Guillaume http://www.postgresql.fr http://dalibo.com
On Tue, Jul 20, 2010 at 4:34 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote: >> I have a nice package for building icons. I'll have a play with it > later. > > Great, thanks :) What about this? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company
Attachment
On Wed, 21 Jul 2010 09:39:18 +0100, Dave Page <dpage@pgadmin.org> wrote: > On Tue, Jul 20, 2010 at 4:34 PM, Guillaume Lelarge > <guillaume@lelarge.info> wrote: >>> I have a nice package for building icons. I'll have a play with it >> later. >> >> Great, thanks :) > > What about this? Seems good enough for me. Thanks. Can I have the .xpm file, so that I can try it with pgAdmin? -- Guillaume http://www.postgresql.fr http://dalibo.com
On 21 July 2010 09:39, Dave Page <dpage@pgadmin.org> wrote: > On Tue, Jul 20, 2010 at 4:34 PM, Guillaume Lelarge > <guillaume@lelarge.info> wrote: >>> I have a nice package for building icons. I'll have a play with it >> later. >> >> Great, thanks :) > > What about this? > > > -- Looks like something out of a box of Quality Street, and are those arrows intentionally misaligned? Other than that, it looks good. Thom
On Wed, Jul 21, 2010 at 9:46 AM, Thom Brown <thombrown@gmail.com> wrote: > On 21 July 2010 09:39, Dave Page <dpage@pgadmin.org> wrote: >> On Tue, Jul 20, 2010 at 4:34 PM, Guillaume Lelarge >> <guillaume@lelarge.info> wrote: >>>> I have a nice package for building icons. I'll have a play with it >>> later. >>> >>> Great, thanks :) >> >> What about this? >> >> >> -- > > Looks like something out of a box of Quality Street, and are those > arrows intentionally misaligned? Other than that, it looks good. That's how the original constraints icon was designed. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company
On Wed, Jul 21, 2010 at 9:46 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > On Wed, 21 Jul 2010 09:39:18 +0100, Dave Page <dpage@pgadmin.org> wrote: >> On Tue, Jul 20, 2010 at 4:34 PM, Guillaume Lelarge >> <guillaume@lelarge.info> wrote: >>>> I have a nice package for building icons. I'll have a play with it >>> later. >>> >>> Great, thanks :) >> >> What about this? > > Seems good enough for me. Thanks. Can I have the .xpm file, so that I can > try it with pgAdmin? Attached. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company
Attachment
Le 21/07/2010 11:07, Dave Page a écrit : > On Wed, Jul 21, 2010 at 9:46 AM, Guillaume Lelarge > <guillaume@lelarge.info> wrote: >> On Wed, 21 Jul 2010 09:39:18 +0100, Dave Page <dpage@pgadmin.org> wrote: >>> On Tue, Jul 20, 2010 at 4:34 PM, Guillaume Lelarge >>> <guillaume@lelarge.info> wrote: >>>>> I have a nice package for building icons. I'll have a play with it >>>> later. >>>> >>>> Great, thanks :) >>> >>> What about this? >> >> Seems good enough for me. Thanks. Can I have the .xpm file, so that I can >> try it with pgAdmin? > > Attached. > OK, better than my icon. I added it to my git branch. Thanks. -- Guillaume http://www.postgresql.fr http://dalibo.com