1

Original goal: create a constraint to ensure that only non-overlapping subnets exist in a particular Postgres table.

From reading the docs carefully, I could get as far as this:

create table subnets (
subnet cidr,
exclude (subnet with &&)
);

But this doesn't work. It produces the inscrutable message:

ERROR:  operator &&(inet,inet) is not a member of operator family "network_ops"
DETAIL:  The exclusion operator must be related to the index operator class for the constraint.

Despite reading up on the doc sections about operator classes and indexes and index types, I retained the feeling that I was missing an entire explanatory section that is taken for granted. I still don't know what IS an operator class, really, nor an operator family.

I did find posted on a mailing list a code snippet that led to the following working code:

create table subnets (
subnet cidr,
exclude using gist (subnet inet_ops with &&)
);

But I can't truly understand what the "using gist" is for, nor the "inet_ops."

I know that "using gist" relates to types of indexes. I know an index is automatically created for a "unique" constraint, and I guess that an index may also be automatically created for an "exclusion" constraint. The only documentation about "operater classes" all relates to indexes, not constraints.

For this or future queries where I want an exclusion constraint, how can I determine what operator class and access method should be specified for the constraint to work?

Note that even with the working code in hand I'm unable to find why it's "gist" and not something else, and why it's "inet_ops" and not "network_ops" or nothing. \doS and the queries listed in the operator class documentation were unenlightening.

Another error I produced was also unenlightening:

vagrant=# create table subnets (
subnet cidr,
exclude using gist (subnet with &&)
);
ERROR:  data type cidr has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

(This question is based on the premise that I should not need to resort to copying and pasting incantations; a thorough review of the documentation plus carefully reading error messages should let me resolve problems. With Postgres, this has always been true before now.)

  • I think you need to separate these questions out: one of them is a duplicate of dba.stackexchange.com/a/201553/2639 – Evan Carroll May 4 '18 at 3:08
  • 1
    @EvanCarroll, so perhaps my question should have been: how on earth can you tell what operator class should be used to allow an exclusion constraint to work correctly, and also how should the access method be chosen for the exclusion constraint to work correctly? The error messages are confusing enough that I'm not sure exactly how to state my question, but unfortunately the linked question is not helpful either. (It's helpful in making code work but not in understanding it.) – Wildcard May 4 '18 at 3:14
  • I'm working on your problem but you can't just make this a compound question by introducing material that is already covered elsewhere. Either make it abstract, or make it concrete but don't make it a dupe or I'll vote to close. – Evan Carroll May 4 '18 at 3:44
  • @EvanCarroll, sorry, I rolled back and immediately started editing the question to improve it, but was interrupted. I've now finished; is it more clear? – Wildcard May 4 '18 at 4:02
  • 1
    I was able to form an exclusion constraint for CIDR using only a btree index, but I did this before exclusion constraints were a thing. I used a trigger than checked the neighbours of the new row. – Jasen May 4 '18 at 4:53
2

PostgreSQL Indexes consist of two parts, from the docs on Interfacing Extensions To Indexes

  • Index Methods (Access Method), for those you're talking about brin, btree, gin, gist, hash, sp-gist (you can see this with \dA+ in psql.)
  • Operator Class

GIST is the Index Method. For why GIST or SP-GIST is required on EXCLUSION CONSTRAINTS see this post. The Operator Class you need is inet_ops. The problem you're encountering is for cidr and inet two GIST operator classes are provided, and the wrong one is the default,

  • the operator class gist_cidr_ops is the default. It's part of the "Additional Supplied Modules" and most distros package it as -contrib. It is provided by the btree_gist extension (source)
  • the operator class inet_ops is core, and not the default.

How would you know this? You probably wouldn't. As far as the access type, good luck. The docs themselves while distributed with PostgreSQL are sorely lacking here, though they do touch on this,

For historical reasons, the inet_ops operator class is not the default class for types inet and cidr. To use it, mention the class name in CREATE INDEX, for example

CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops);

The best you could do is submit a doc patch to btree_gist where this should probably also be documented.

  • Thanks, this helps a bit but I'm still adrift on several points. Regarding cidr and inet, I tried changing the data type of the table to inet instead of cidr and it made no difference to the error messages I got (which is why I omitted that byroad from the question). So it doesn't seem that it was only the data type that caused the issue. (?) – Wildcard May 4 '18 at 4:20
  • @Wildcard see the update. – Evan Carroll May 4 '18 at 9:02
  • Thanks! So if I've got it straight now, the default operator class for data type cidr for access method btree is network_ops, which doesn't include the && operator (and more to the point, btree indexes can't support exclusion constraints, so it wouldn't work even if network_ops did include &&)—and there is no default operator class for data type cidr for access method gist. So gist is required for any exclusion constraint, and for inet or cidr data types the inet_ops operator class must be specified as it's not the default for historical reasons. – Wildcard May 13 '18 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.