pg can create duplicated index without any errors even warnning
postgres=# create table t (a int, b int);
CREATE TABLE
postgres=# create index m on t(a);
CREATE INDEX
postgres=# create index m2 on t(a);
CREATE INDEX
postgres=# \d t
Table "demo.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | integer | | |
Indexes:
"m" btree (a)
"m2" btree (a)
is this by design?
On Mon, Aug 5, 2019 at 7:34 PM Alex <zhihui.fan1213@gmail.com> wrote:
is this by design?
Yes. Being able to do this is useful for several reasons. For example,
it's useful to be able to create a new, equivalent index before
dropping the original when the original is bloated. (You could use
REINDEX instead, but that has some disadvantages that you might want
to avoid.)
Questions like this are better suited to the pgsql-general list.
--
Peter Geoghegan
On Mon, Aug 05, 2019 at 08:16:11PM -0700, Peter Geoghegan wrote:
Yes. Being able to do this is useful for several reasons. For example,
it's useful to be able to create a new, equivalent index before
dropping the original when the original is bloated. (You could use
REINDEX instead, but that has some disadvantages that you might want
to avoid.)
REINDEX CONCURRENTLY recently added to v12 relies on that heavily
actually, so as you can finish with the same index definition twice in
the state of swapping both index definitions.
--
Michael
