Re: [HACKERS] GiST support for inet datatypes

2014-04-09 Thread Emre Hasegeli
Tom Lane t...@sss.pgh.pa.us: Committed with some additional documentation work. Thanks for submitting this! Thank you for committing. I had not thought of using different structure for the index. It works faster with my test case, too. I am sending rebased version of the consistent operator

Re: [HACKERS] GiST support for inet datatypes

2014-04-08 Thread Tom Lane
I wrote: [ inet-gist-v6.patch ] Committed with some additional documentation work. Thanks for submitting this! regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] GiST support for inet datatypes

2014-04-08 Thread Robert Haas
On Tue, Apr 8, 2014 at 3:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: [ inet-gist-v6.patch ] Committed with some additional documentation work. Thanks for submitting this! NICE. I'd like to tell you how excited I am about this part: # It also handles a new network # operator inet

Re: [HACKERS] GiST support for inet datatypes

2014-04-08 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: NICE. I'd like to tell you how excited I am about this part: # It also handles a new network # operator inet inet (overlaps, a/k/a is supernet or subnet of), # which is expected to be useful in exclusion constraints. ...but I can't, because my

Re: [HACKERS] GiST support for inet datatypes

2014-04-08 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: On Tue, Apr 8, 2014 at 3:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: [ inet-gist-v6.patch ] Committed with some additional documentation work. Thanks for submitting this! NICE. I'd like to tell you how excited I am about this

Re: [HACKERS] GiST support for inet datatypes

2014-04-04 Thread Andres Freund
Hi, On 2014-03-08 23:40:31 +0200, Emre Hasegeli wrote: Fourth version of the patch attached. It is rebased to the HEAD (8879fa0). Operator name formatting patch rebased on top of it. I will put the selectivity estimation patch to the next commit fest. This version of the patch does not

Re: [HACKERS] GiST support for inet datatypes

2014-04-04 Thread Andreas Karlsson
On 04/04/2014 04:01 PM, Andres Freund wrote: This patch looks like it can be applied much more realistically, but it looks too late for 9.4. I suggest moving it to the next CF? If it does not change the default operator class I do not see anything preventing it from being applied to 9.4, as

Re: [HACKERS] GiST support for inet datatypes

2014-04-04 Thread Andres Freund
On 2014-04-04 16:50:36 +0200, Andreas Karlsson wrote: On 04/04/2014 04:01 PM, Andres Freund wrote: This patch looks like it can be applied much more realistically, but it looks too late for 9.4. I suggest moving it to the next CF? If it does not change the default operator class I do not see

Re: [HACKERS] GiST support for inet datatypes

2014-04-04 Thread Andreas Karlsson
On 03/08/2014 10:40 PM, Emre Hasegeli wrote: Fourth version of the patch attached. It is rebased to the HEAD (8879fa0). Operator name formatting patch rebased on top of it. I will put the selectivity estimation patch to the next commit fest. This version of the patch does not touch to the

Re: [HACKERS] GiST support for inet datatypes

2014-03-03 Thread Robert Haas
On Thu, Feb 27, 2014 at 12:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Florian Pflug f...@phlo.org writes: On Feb27, 2014, at 17:56 , Tom Lane t...@sss.pgh.pa.us wrote: That's not a bug, it's a feature, for much the same reasons that pg_dump tries to minimize explicit schema-qualification. I

Re: [HACKERS] GiST support for inet datatypes

2014-03-03 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Thu, Feb 27, 2014 at 12:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: The value in it is roughly the same as the reason we don't include a version number when dumping CREATE EXTENSION. If you had a default opclass in the source database, you probably

Re: [HACKERS] GiST support for inet datatypes

2014-02-28 Thread Greg Stark
On Thu, Feb 27, 2014 at 5:30 PM, Tom Lane t...@sss.pgh.pa.us wrote: Indeed. The root of the problem here is that we've never really thought about changing a type's default opclass before. I don't think that a two-line change in pg_dump fixes all the issues this will bring up. I think we did

Re: [HACKERS] GiST support for inet datatypes

2014-02-28 Thread Emre Hasegeli
2014-02-27 18:15, Florian Pflug f...@phlo.org: It can be possible to update the new operator class in the new cluster as not default, before restore. After the restore, pg_upgrade can upgrade the btree_gist extension and reset the operator class as the default. pg_upgrade suggests to re-initdb

Re: [HACKERS] GiST support for inet datatypes

2014-02-28 Thread Florian Pflug
On Feb28, 2014, at 15:45 , Emre Hasegeli e...@hasegeli.com wrote: The problem is that pg_dump --binary-upgrade dumps objects in the extension on the old cluster, not just the CREATE EXTENSION statement. pg_upgrade fails to restore them, if the new operator class already exists on the new

Re: [HACKERS] GiST support for inet datatypes

2014-02-28 Thread Emre Hasegeli
2014-02-28 17:30, Florian Pflug f...@phlo.org: Hm, what if we put the new opclass into an extension of its own, say inet_gist, instead of into core? It will work but I do not think it is better than adding it in core as not default. -- Sent via pgsql-hackers mailing list

Re: [HACKERS] GiST support for inet datatypes

2014-02-27 Thread Emre Hasegeli
2014-02-24 17:55, Bruce Momjian br...@momjian.us: pg_upgrade has _never_ modified the old cluster, and I am hesitant to do that now. Can we make the changes in the new cluster, or in pg_dump when in binary upgrade mode? It can be possible to update the new operator class in the new cluster

Re: [HACKERS] GiST support for inet datatypes

2014-02-27 Thread Florian Pflug
On Feb27, 2014, at 11:39 , Emre Hasegeli e...@hasegeli.com wrote: 2014-02-24 17:55, Bruce Momjian br...@momjian.us: pg_upgrade has _never_ modified the old cluster, and I am hesitant to do that now. Can we make the changes in the new cluster, or in pg_dump when in binary upgrade mode? It

Re: [HACKERS] GiST support for inet datatypes

2014-02-27 Thread Tom Lane
Florian Pflug f...@phlo.org writes: Maybe I'm missing something, but isn't the gist of the problem here that pg_dump won't explicitly state the operator class if it's the default? That's not a bug, it's a feature, for much the same reasons that pg_dump tries to minimize explicit

Re: [HACKERS] GiST support for inet datatypes

2014-02-27 Thread Florian Pflug
On Feb27, 2014, at 17:56 , Tom Lane t...@sss.pgh.pa.us wrote: Florian Pflug f...@phlo.org writes: Maybe I'm missing something, but isn't the gist of the problem here that pg_dump won't explicitly state the operator class if it's the default? That's not a bug, it's a feature, for much the

Re: [HACKERS] GiST support for inet datatypes

2014-02-27 Thread Tom Lane
Florian Pflug f...@phlo.org writes: On Feb27, 2014, at 17:56 , Tom Lane t...@sss.pgh.pa.us wrote: That's not a bug, it's a feature, for much the same reasons that pg_dump tries to minimize explicit schema-qualification. I fail to see the value in this for opclasses. It's certainly nice for

Re: [HACKERS] GiST support for inet datatypes

2014-02-26 Thread Emre Hasegeli
2014-02-24 02:44, Andreas Karlsson andr...@proxel.se: Note: The patches do not apply anymore due to changes to src/backend/utils/adt/Makefile. I will fix it on the next version. I see, thanks for the explanation. But I am still not very fond of how that code is written since I find it hard

Re: [HACKERS] GiST support for inet datatypes

2014-02-24 Thread Bruce Momjian
On Wed, Feb 19, 2014 at 06:37:42PM -0500, Tom Lane wrote: Emre Hasegeli e...@hasegeli.com writes: [ cites bug #5705 ] Hm. I had forgotten how thoroughly broken btree_gist's inet and cidr opclasses are. There was discussion at the time of just ripping them out despite the compatibility

Re: [HACKERS] GiST support for inet datatypes

2014-02-23 Thread Andreas Karlsson
On 02/06/2014 06:14 PM, Emre Hasegeli wrote: Third versions of the patches attached. They are rebased to the HEAD. In this versions, the bitncommon function is changed. sys/socket.h included to network_gist.c to be able to compile it on FreeBSD. Geometric mean calculation for partial bucket

Re: [HACKERS] GiST support for inet datatypes

2014-02-20 Thread Emre Hasegeli
2014-02-20 01:37, Tom Lane t...@sss.pgh.pa.us: Perhaps it would be acceptable to drop the btree_gist implementation and teach pg_upgrade to refuse to upgrade if the old database contains any such indexes. I'm not sure that solves the problem, though, because I think pg_upgrade will still

Re: [HACKERS] GiST support for inet datatypes

2014-02-19 Thread Robert Haas
On Mon, Feb 17, 2014 at 3:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Emre Hasegeli e...@hasegeli.com writes: How about only removing the inet and the cidr operator classes from btree_gist. btree-gist-drop-inet-v2.patch does that. I'm not sure which part of no you didn't understand, but to be

Re: [HACKERS] GiST support for inet datatypes

2014-02-19 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Mon, Feb 17, 2014 at 3:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: We should probably expend some thought on a general approach to replacing the default opclass for a datatype, because I'm sure this will come up again. Right now I don't see a

Re: [HACKERS] GiST support for inet datatypes

2014-02-19 Thread Emre Hasegeli
2014-02-19 16:52, Tom Lane t...@sss.pgh.pa.us: Not at all, AFAICS. If it were okay to decide that some formerly-default opclass is no longer default, then having such a command would be better than manually manipulating pg_opclass.opcdefault --- but extension upgrade scripts could certainly

Re: [HACKERS] GiST support for inet datatypes

2014-02-19 Thread Tom Lane
Emre Hasegeli e...@hasegeli.com writes: [ cites bug #5705 ] Hm. I had forgotten how thoroughly broken btree_gist's inet and cidr opclasses are. There was discussion at the time of just ripping them out despite the compatibility hit. We didn't do it, but if we had then life would be simpler

Re: [HACKERS] GiST support for inet datatypes

2014-02-17 Thread Andres Freund
On 2014-02-17 14:40:07 +0200, Emre Hasegeli wrote: 2014-02-07 22:41, Robert Haas robertmh...@gmail.com: Generally, modifying already-release .sql files for extensions is a no-no... I prepared separate patches for btree_gist extension with more options. First one

Re: [HACKERS] GiST support for inet datatypes

2014-02-17 Thread Tom Lane
Emre Hasegeli e...@hasegeli.com writes: 2014-02-17 14:54, Andres Freund and...@2ndquadrant.com: You need to add a file for going from 1.0 to 1.1. Thank you for the notice. I added them to the patches which touch only two of the operator classes. It drops and re-creates operator classes as

Re: [HACKERS] GiST support for inet datatypes

2014-02-17 Thread Emre Hasegeli
2014-02-17 22:16, Tom Lane t...@sss.pgh.pa.us: More generally, it doesn't look to me like these upgrade scripts are complete; shouldn't they be creating some new objects, not just replacing old ones? The actual patches are on the previous mail [1]. I was just trying to solve the problem that

Re: [HACKERS] GiST support for inet datatypes

2014-02-17 Thread Tom Lane
Emre Hasegeli e...@hasegeli.com writes: How about only removing the inet and the cidr operator classes from btree_gist. btree-gist-drop-inet-v2.patch does that. I'm not sure which part of no you didn't understand, but to be clear: you don't get to break existing installations. Assuming that

Re: [HACKERS] GiST support for inet datatypes

2014-02-07 Thread Robert Haas
On Thu, Feb 6, 2014 at 12:14 PM, Emre Hasegeli e...@hasegeli.com wrote: I have misread the name, rename was not necessary. I removed the DEFAULT keywords for the inet and the cidr operator classes from btree_gist--1.0.sql on the inet-gist patch. Generally, modifying already-release .sql files

Re: [HACKERS] GiST support for inet datatypes

2014-02-06 Thread Emre Hasegeli
2014-01-19 12:10, Emre Hasegeli e...@hasegeli.com: 2014-01-19 Andreas Karlsson andr...@proxel.se: I am a bit suspicious about your memcmp based optimization in bitncommon, but it could be good. Have you benchmarked it compared to doing the same thing with a loop? I did, when I was writing

Re: [HACKERS] GiST support for inet datatypes

2014-01-30 Thread Andreas Karlsson
On 01/23/2014 11:22 PM, Emre Hasegeli wrote: inet-gist - I realized that create extension btree_gist fails with the patch. ERROR: could not make operator class gist_inet_ops be default for type inet DETAIL: Operator class inet_ops already is the default. I think making the new

Re: [HACKERS] GiST support for inet datatypes

2014-01-19 Thread Emre Hasegeli
2014-01-19 Andreas Karlsson andr...@proxel.se: Hi, I will review your two patches (gist support + selectivity). This is part 1 of my review. I will look more into the actual GiST implementation in a couple of days, but thought I could provide you with my initial input right away. Thank you

Re: [HACKERS] GiST support for inet datatypes

2014-01-19 Thread Andreas Karlsson
On 01/19/2014 11:10 AM, Emre Hasegeli wrote: I am not convinced an adjacent operator is useful for the inet type, but if it is included it should be indexed just like -|- of ranges. We should try to keep these lists of indexed operators the same. I added it just not to leave negotor field

Re: [HACKERS] GiST support for inet datatypes

2014-01-19 Thread Andreas Karlsson
Here comes part 2 of 2 of the review. inet-gist - In general the code looks good and I think your approach makes sense. Not an expert on GiST though so I would like a second opinion on this. Maybe there is some clever trick which would make the index more efficient, but the design I

Re: [HACKERS] GiST support for inet datatypes

2014-01-18 Thread Andreas Karlsson
Hi, I will review your two patches (gist support + selectivity). This is part 1 of my review. I will look more into the actual GiST implementation in a couple of days, but thought I could provide you with my initial input right away. inet-gist - General: I like the idea of the

Re: [HACKERS] GiST support for inet datatypes

2014-01-05 Thread Emre Hasegeli
2013-12-17 Emre Hasegeli e...@hasegeli.com: Query planner never chooses to use the index for the operators which the index is particularly useful because selectivity estimation functions are missing. I am planning to work on them. Attached patch adds selectivity estimation functions for the

Re: [HACKERS] GiST support for inet datatypes

2013-12-22 Thread David Fetter
On Tue, Dec 17, 2013 at 08:58:13PM +0200, Emre Hasegeli wrote: Hi, Attached patch adds GiST support to the inet datatypes with two new operators. Overlaps operator can be used with exclusion constraints. Is adjacent to operator is just the negator of it. Index uses only the network bits of