public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Zornoza Sanchez, Jose Blas <[email protected]>
Cc: Tomasz Szypowski <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: View performance with implicit cast
Date: Tue, 07 Jan 2025 09:52:14 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <PR3P193MB073140BB6374E47E7E6EB87CCC112@PR3P193MB0731.EURP193.PROD.OUTLOOK.COM>
References: <DU0PR04MB94192D3922E05C8E0A18F5F399142@DU0PR04MB9419.eurprd04.prod.outlook.com>
	<[email protected]>
	<DU0PR04MB941961BC101B5B4AFB0C1C1699172@DU0PR04MB9419.eurprd04.prod.outlook.com>
	<PR3P193MB073140BB6374E47E7E6EB87CCC112@PR3P193MB0731.EURP193.PROD.OUTLOOK.COM>

"Zornoza Sanchez, Jose Blas" <[email protected]> writes:
> Hello, in this case both index and view have the same name (test), try a different one...

Yeah.  If you try the example as-presented it fails immediately:

postgres=# create table foo (id int);
CREATE TABLE
postgres=# CREATE VIEW test AS SELECT * FROM foo;
CREATE VIEW
postgres=# CREATE INDEX test ON foo(id);
ERROR:  relation "test" already exists

because you can't put a view named test and an index named test into
the same schema.  (They share the namespace of tables.)  What I think
the OP might have done is something similar to

postgres=# create schema s1;
CREATE SCHEMA
postgres=# create schema s2;
CREATE SCHEMA
postgres=# set search_path to s1, s2;
SET
postgres=# create table s2.foo (id int);
CREATE TABLE
postgres=# CREATE VIEW test AS SELECT * FROM foo;
CREATE VIEW
postgres=# CREATE INDEX test ON foo(id);
CREATE INDEX
postgres=# DROP INDEX test;
ERROR:  "test" is not an index
HINT:  Use DROP VIEW to remove a view.

View test is in schema s1, because that's the default creation schema
with this search_path setting.  But index test is in s2, because
indexes are always put in the same schema as their parent table.
So the CREATE INDEX doesn't fail.  But then the DROP searches the
search_path, and the first "test" it finds is the view s1.test,
so it complains.

			regards, tom lane

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: View performance with implicit cast
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox