public inbox for [email protected]  

help / color / mirror / Atom feed

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

Thanks for the explanation, but what about the reported problem.
How can I force the view to use both indexes?


-----Original Message-----
From: Tom Lane <[email protected]> 
Sent: Tuesday, January 7, 2025 3:52 PM
To: Zornoza Sanchez, Jose Blas <[email protected]>
Cc: Tomasz Szypowski <[email protected]>; [email protected]
Subject: Re: View performance with implicit cast

"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: <DU0PR04MB941950CC9F69A0F3B0FE877B99112@DU0PR04MB9419.eurprd04.prod.outlook.com>

* 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