wrong message when trying to create an already existing index

On 03/10/2018 07:00 AM, legrand legrand wrote:

Hello,
When trying to create an already existing index (in pg 9.5)

SQL> create index if not exists NEWINDEX on SCHEMA.TABLE(COL);

relation "NEWINDEX" already exists, skipping

message speaks about relation (and not index)

https://www.postgresql.org/docs/10/static/catalog-pg-class.html

"The catalog pg_class catalogs tables and most everything else that has
columns or is otherwise similar to a table. This includes indexes (but
see also pg_index), sequences (but see also pg_sequence), views,
materialized views, composite types, and TOAST tables; see relkind.
Below, when we mean all of these kinds of objects we speak of
“relations”. Not all columns are meaningful for all relation types."

Would it be possible that this message reports the correct object type ?
Regards
PAscal

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

--
Adrian Klaver
adrian.klaver@aklaver.com

I thougth that thoses messages where using relation's relkind:
r = ordinary table,
i = index,
S = sequence,
t = TOAST table,
v = view,
m = materialized view,
c = composite type,
f = foreign table,
p = partitioned table

wouldn't it be easier to read for beginners ?

Regards
PAscal

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On Sat, Mar 10, 2018 at 10:54 AM, legrand legrand <
legrand_legrand@hotmail.com> wrote:

I thougth that thoses messages where using relation's relkind:
r = ordinary table,
i = index,
S = sequence,
t = TOAST table,
v = view,
m = materialized view,
c = composite type,
f = foreign table,
p = partitioned table

wouldn't it be easier to read for beginners ?

Regards
PAscal

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-
f1843780.html

*>message speaks about relation (and not index)>Would it be possible that
this message reports the correct object type ?>I thougth that thoses
messages where using relation's relkind:>wouldn't it be easier to read for
beginners ?PostgreSQL is a "relational" database, and as such _all_ objects
in the database are considered _relations_, even indexes. Therefore, the
error message is correct, because_relation_ NEWINDEX already exists. I
believe that the code is generic as the clause "IF EXISTS" checks against
pg_classfor other _relations_ as defined in relkind, and therefore reports
a generic message as"relation _relname_ already exists"To report on a
specific relation type would be redundant, because you already knowfrom
your SQL statement what type/relkind of relation you are trying to
CREATE.IE: SQL> create index if not exists NEWINDEX on
SCHEMA.TABLE(COL); ^^^^^*

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

OK, that noted !
thank you for the quick answers

Regards
PAscal

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

legrand legrand <legrand_legrand@hotmail.com> writes:

I thougth that thoses messages where using relation's relkind:
..
wouldn't it be easier to read for beginners ?

I doubt it would be an improvement. Consider this example:

regression=# create table t1 (f1 int);
CREATE TABLE
regression=# create materialized view mv1 as select * from t1;
SELECT 0
regression=# create index mv1 on t1 (f1);
ERROR: relation "mv1" already exists

You seem to be proposing that the error should read either

ERROR: index "mv1" already exists

which would be a lie, or

ERROR: materialized view "mv1" already exists

which while accurate seems to me to be *more* confusing not less.
A person who did not understand that these relation types all
share the same namespace would probably not get enlightened
this way. Using the generic term "relation" is just as accurate,
and it might help somebody understand that the problem is exactly
that relations of different types share the same namespace.

regards, tom lane

regression=# create index mv1 on t1 (f1);

...

ERROR: materialized view "mv1" already exists

Is in fact the one I prefer ;^)

I come from a DBMS world where Tables and Indexes do not share the same name
space,
and have to change my mind !

Thanks you Tom for pointing that.

Regards
PAscal

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html