Re: operator exclusion constraints
| От | Tom Lane |
|---|---|
| Тема | Re: operator exclusion constraints |
| Дата | |
| Msg-id | 13182.1268285385@sss.pgh.pa.us обсуждение исходный текст |
| Ответ на | Re: operator exclusion constraints (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: operator exclusion constraints
(Greg Stark <gsstark@mit.edu>)
Re: operator exclusion constraints (Jeff Davis <pgsql@j-davis.com>) |
| Список | pgsql-hackers |
Awhile back I wrote:
> * I'm not too satisfied with the behavior of psql's \d:
> regression=# create table foo (f1 int primary key using index tablespace ts1,
> regression(# f2 int, EXCLUDE USING btree (f2 WITH =) using index tablespace ts1,
> regression(# f3 int, EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY DEFERRED);
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
> NOTICE: CREATE TABLE / EXCLUDE will create implicit index "foo_f2_exclusion" for table "foo"
> NOTICE: CREATE TABLE / EXCLUDE will create implicit index "foo_f3_exclusion" for table "foo"
> CREATE TABLE
> regression=# \d foo
> Table "public.foo"
> Column | Type | Modifiers
> --------+---------+-----------
> f1 | integer | not null
> f2 | integer |
> f3 | integer |
> Indexes:
> "foo_pkey" PRIMARY KEY, btree (f1), tablespace "ts1"
> "foo_f2_exclusion" btree (f2), tablespace "ts1"
> "foo_f3_exclusion" btree (f3) DEFERRABLE INITIALLY DEFERRED
> Exclusion constraints:
> "foo_f2_exclusion" EXCLUDE USING btree (f2 WITH =)
> "foo_f3_exclusion" EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY DEFERRED
> regression=#
> This might have been defensible back when the idea was to keep constraints
> decoupled from indexes, but now it just looks bizarre. We should either
> get rid of the "Exclusion constraints:" display and attach the info to
> the index entries, or hide indexes that are attached to exclusion
> constraints. I lean to the former on the grounds of the precedent for
> unique/pkey indexes --- which is not totally arbitrary, since an index
> is usable as a query index regardless of its function as a constraint.
> It's probably a debatable point though.
Attached is a patch against HEAD that folds exclusion constraints into
\d's regular indexes list. With this, the above example produces
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer | not null
f2 | integer |
f3 | integer |
Indexes:
"foo_pkey" PRIMARY KEY, btree (f1), tablespace "ts1"
"foo_f2_exclusion" EXCLUDE USING btree (f2 WITH =), tablespace "ts1"
"foo_f3_exclusion" EXCLUDE USING btree (f3 WITH =) DEFERRABLE INITIALLY DEFERRED
Any objections?
regards, tom lane
? psql
Index: describe.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.240
diff -c -r1.240 describe.c
*** describe.c 11 Mar 2010 04:36:43 -0000 1.240
--- describe.c 11 Mar 2010 05:18:28 -0000
***************
*** 1105,1111 ****
bool hasrules;
bool hastriggers;
bool hasoids;
- bool hasexclusion;
Oid tablespace;
char *reloptions;
char *reloftype;
--- 1105,1110 ----
***************
*** 1128,1135 ****
printfPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
"c.relhastriggers, c.relhasoids, "
! "%s, c.reltablespace, c.relhasexclusion, "
! "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::text END\n"
"FROM pg_catalog.pg_class c\n "
"LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
"WHERE c.oid = '%s'\n",
--- 1127,1134 ----
printfPQExpBuffer(&buf,
"SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, "
"c.relhastriggers, c.relhasoids, "
! "%s, c.reltablespace, "
! "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text
END\n"
"FROM pg_catalog.pg_class c\n "
"LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n"
"WHERE c.oid = '%s'\n",
***************
*** 1207,1216 ****
strdup(PQgetvalue(res, 0, 6)) : 0;
tableinfo.tablespace = (pset.sversion >= 80000) ?
atooid(PQgetvalue(res, 0, 7)) : 0;
! tableinfo.hasexclusion = (pset.sversion >= 90000) ?
! strcmp(PQgetvalue(res, 0, 8), "t") == 0 : false;
! tableinfo.reloftype = (pset.sversion >= 90000 && strcmp(PQgetvalue(res, 0, 9), "") != 0) ?
! strdup(PQgetvalue(res, 0, 9)) : 0;
PQclear(res);
res = NULL;
--- 1206,1213 ----
strdup(PQgetvalue(res, 0, 6)) : 0;
tableinfo.tablespace = (pset.sversion >= 80000) ?
atooid(PQgetvalue(res, 0, 7)) : 0;
! tableinfo.reloftype = (pset.sversion >= 90000 && strcmp(PQgetvalue(res, 0, 8), "") != 0) ?
! strdup(PQgetvalue(res, 0, 8)) : 0;
PQclear(res);
res = NULL;
***************
*** 1545,1571 ****
appendPQExpBuffer(&buf, "i.indisvalid, ");
else
appendPQExpBuffer(&buf, "true as indisvalid, ");
! appendPQExpBuffer(&buf, "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)");
if (pset.sversion >= 90000)
appendPQExpBuffer(&buf,
! ",\n (NOT i.indimmediate) AND "
! "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
! "WHERE conrelid = i.indrelid AND "
! "conindid = i.indexrelid AND "
! "contype IN ('p','u','x') AND "
! "condeferrable) AS condeferrable"
! ",\n (NOT i.indimmediate) AND "
! "EXISTS (SELECT 1 FROM pg_catalog.pg_constraint "
! "WHERE conrelid = i.indrelid AND "
! "conindid = i.indexrelid AND "
! "contype IN ('p','u','x') AND "
! "condeferred) AS condeferred");
else
! appendPQExpBuffer(&buf, ", false AS condeferrable, false AS condeferred");
if (pset.sversion >= 80000)
appendPQExpBuffer(&buf, ", c2.reltablespace");
appendPQExpBuffer(&buf,
! "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n"
"WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
"ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
oid);
--- 1542,1564 ----
appendPQExpBuffer(&buf, "i.indisvalid, ");
else
appendPQExpBuffer(&buf, "true as indisvalid, ");
! appendPQExpBuffer(&buf, "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),\n ");
if (pset.sversion >= 90000)
appendPQExpBuffer(&buf,
! "pg_catalog.pg_get_constraintdef(con.oid, true), "
! "contype, condeferrable, condeferred");
else
! appendPQExpBuffer(&buf,
! "null AS constraintdef, null AS contype, "
! "false AS condeferrable, false AS condeferred");
if (pset.sversion >= 80000)
appendPQExpBuffer(&buf, ", c2.reltablespace");
appendPQExpBuffer(&buf,
! "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n");
! if (pset.sversion >= 90000)
! appendPQExpBuffer(&buf,
! " LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid =
i.indexrelidAND contype IN ('p','u','x'))\n");
! appendPQExpBuffer(&buf,
"WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
"ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
oid);
***************
*** 1580,1625 ****
printTableAddFooter(&cont, _("Indexes:"));
for (i = 0; i < tuples; i++)
{
- const char *indexdef;
- const char *usingpos;
-
/* untranslated index name */
printfPQExpBuffer(&buf, " \"%s\"",
PQgetvalue(result, i, 0));
! /* Label as primary key or unique (but not both) */
! appendPQExpBuffer(&buf,
! strcmp(PQgetvalue(result, i, 1), "t") == 0
! ? " PRIMARY KEY," :
! (strcmp(PQgetvalue(result, i, 2), "t") == 0
! ? " UNIQUE,"
! : ""));
! /* Everything after "USING" is echoed verbatim */
! indexdef = PQgetvalue(result, i, 5);
! usingpos = strstr(indexdef, " USING ");
! if (usingpos)
! indexdef = usingpos + 7;
! appendPQExpBuffer(&buf, " %s", indexdef);
if (strcmp(PQgetvalue(result, i, 3), "t") == 0)
appendPQExpBuffer(&buf, " CLUSTER");
if (strcmp(PQgetvalue(result, i, 4), "t") != 0)
appendPQExpBuffer(&buf, " INVALID");
- if (strcmp(PQgetvalue(result, i, 6), "t") == 0)
- appendPQExpBuffer(&buf, " DEFERRABLE");
-
- if (strcmp(PQgetvalue(result, i, 7), "t") == 0)
- appendPQExpBuffer(&buf, " INITIALLY DEFERRED");
-
printTableAddFooter(&cont, buf.data);
/* Print tablespace of the index on the same line */
if (pset.sversion >= 80000)
add_tablespace_footer(&cont, 'i',
! atooid(PQgetvalue(result, i, 8)),
false);
}
}
--- 1573,1627 ----
printTableAddFooter(&cont, _("Indexes:"));
for (i = 0; i < tuples; i++)
{
/* untranslated index name */
printfPQExpBuffer(&buf, " \"%s\"",
PQgetvalue(result, i, 0));
! /* If exclusion constraint, print the constraintdef */
! if (strcmp(PQgetvalue(result, i, 7), "x") == 0)
! {
! appendPQExpBuffer(&buf, " %s",
! PQgetvalue(result, i, 6));
! }
! else
! {
! const char *indexdef;
! const char *usingpos;
!
! /* Label as primary key or unique (but not both) */
! if (strcmp(PQgetvalue(result, i, 1), "t") == 0)
! appendPQExpBuffer(&buf, " PRIMARY KEY,");
! else if (strcmp(PQgetvalue(result, i, 2), "t") == 0)
! appendPQExpBuffer(&buf, " UNIQUE,");
!
! /* Everything after "USING" is echoed verbatim */
! indexdef = PQgetvalue(result, i, 5);
! usingpos = strstr(indexdef, " USING ");
! if (usingpos)
! indexdef = usingpos + 7;
! appendPQExpBuffer(&buf, " %s", indexdef);
! /* Need these for deferrable PK/UNIQUE indexes */
! if (strcmp(PQgetvalue(result, i, 8), "t") == 0)
! appendPQExpBuffer(&buf, " DEFERRABLE");
+ if (strcmp(PQgetvalue(result, i, 9), "t") == 0)
+ appendPQExpBuffer(&buf, " INITIALLY DEFERRED");
+ }
+
+ /* Add these for all cases */
if (strcmp(PQgetvalue(result, i, 3), "t") == 0)
appendPQExpBuffer(&buf, " CLUSTER");
if (strcmp(PQgetvalue(result, i, 4), "t") != 0)
appendPQExpBuffer(&buf, " INVALID");
printTableAddFooter(&cont, buf.data);
/* Print tablespace of the index on the same line */
if (pset.sversion >= 80000)
add_tablespace_footer(&cont, 'i',
! atooid(PQgetvalue(result, i, 10)),
false);
}
}
***************
*** 1657,1694 ****
PQclear(result);
}
- /* print exclusion constraints */
- if (tableinfo.hasexclusion)
- {
- printfPQExpBuffer(&buf,
- "SELECT r.conname, "
- "pg_catalog.pg_get_constraintdef(r.oid, true)\n"
- "FROM pg_catalog.pg_constraint r\n"
- "WHERE r.conrelid = '%s' AND r.contype = 'x'\n"
- "ORDER BY 1",
- oid);
- result = PSQLexec(buf.data, false);
- if (!result)
- goto error_return;
- else
- tuples = PQntuples(result);
-
- if (tuples > 0)
- {
- printTableAddFooter(&cont, _("Exclusion constraints:"));
- for (i = 0; i < tuples; i++)
- {
- /* untranslated contraint name and def */
- printfPQExpBuffer(&buf, " \"%s\" %s",
- PQgetvalue(result, i, 0),
- PQgetvalue(result, i, 1));
-
- printTableAddFooter(&cont, buf.data);
- }
- }
- PQclear(result);
- }
-
/* print foreign-key constraints (there are none if no triggers) */
if (tableinfo.hastriggers)
{
--- 1659,1664 ----
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Bruce MomjianДата:
Сообщение: Re: Re: Hot Standby query cancellation and Streaming Replication integration
Следующее
От: Josh BerkusДата:
Сообщение: Re: Re: Hot Standby query cancellation and Streaming Replication integration