3

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.)

1
  • To amplify on the question: If there were an official document somewhere, "introduction to operator classes" that clearly defined the terms, I would avidly read it. But the only explanation given in the actual docs is, "An index definition can specify an operator class for each column of an index. The operator class identifies the operators to be used by the index for that column." I can't even tell whether this is a definition or an example of an operator class; are all operator classes associated with specific columns and specific indexes, or...?
    – Wildcard
    May 4 '18 at 4:10
4

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.

1
  • 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.