Обсуждение: BUG #3597: CREATE OR REPLACE VIEW
The following bug has been logged online:
Bug reference: 3597
Logged by: Luiz K. Matsumura
Email address: luiz@planit.com.br
PostgreSQL version: 8.2.4
Operating system: Fedora core 3
Description: CREATE OR REPLACE VIEW
Details:
scenario:
CREATE TABLE table1
(
id serial NOT NULL,
col1 character varying(30),
CONSTRAINT pk_table1 PRIMARY KEY (id)
);
CREATE TABLE table2
(
fk_table1 integer,
type1 character(3),
id serial NOT NULL,
CONSTRAINT pk_table2 PRIMARY KEY (id)
);
CREATE TABLE table3
(
id serial NOT NULL,
type2 integer,
fk_table1 integer,
CONSTRAINT pk_table3 PRIMARY KEY (id)
);
CREATE VIEW view1 AS
SELECT table1.id,
table1.col1,
table2.type1,
NULL AS type2
FROM table1
JOIN table2 ON table2.fk_table1 = table1.id
UNION ALL
SELECT table1.id,
table1.col1,
NULL AS type1,
table3.type2
FROM table1
JOIN table3 ON table3.fk_table1 = table1.id;
When we do a command Create or Replace View that change columns of previous
view we got a error.
Ex.:
CREATE OR REPLACE VIEW view1 AS
SELECT table1.id,
table1.col1,
table2.type1,
NULL AS type2
FROM table1
JOIN table2 ON table2.fk_table1 = table1.id
UNION ALL
SELECT table1.id,
table1.col1,
NULL::character(3) AS type1,
table3.type2
FROM table1
JOIN table3 ON table3.fk_table1 = table1.id;
ERROR: cannot change data type of view column "type1"
Luiz K. Matsumura wrote: > When we do a command Create or Replace View that change columns of previous > view we got a error. Right. You can't change the data types of an existing view. You'll have to drop and recreate it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote: > Luiz K. Matsumura wrote: > >> When we do a command Create or Replace View that change columns of previous >> view we got a error. >> > > Right. You can't change the data types of an existing view. You'll have > to drop and recreate it. > > But, with the 'replace' command, this isn't implicit ? If they found a view, replace the existing view with the new one (on the other words, drop and create again?) -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda.
Luiz K. Matsumura wrote: > Heikki Linnakangas wrote: >> Luiz K. Matsumura wrote: >> >>> When we do a command Create or Replace View that change columns of >>> previous >>> view we got a error. >> >> Right. You can't change the data types of an existing view. You'll have >> to drop and recreate it. >> > But, with the 'replace' command, this isn't implicit ? > If they found a view, replace the existing view with the new one (on the > other words, drop and create again?) Replacing is not exactly the same thing as dropping and recreating it. If the view has dependencies, you can't drop it without dropping the dependent objects first, and likewise you can't change its datatypes because it would affect the dependent objects as well (hence the limitation on CREATE OR REPLACE VIEW). But you can replace the definition CREATE OR REPLACE VIEW, even when there's dependencies. For example: CREATE VIEW foo AS SELECT 10::integer; CREATE VIEW bar AS SELECT * FROM foo; CREATE OR REPLACE VIEW foo AS SELECT 'foobar'::text; -- Fails, can't change data type of an existing view DROP VIEW foo; -- Fails because 'bar' depends on foo CREATE OR REPLACE VIEW foo AS SELECT 20::integer; -- Succeeds. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
> Luiz K. Matsumura wrote:
>> But, with the 'replace' command, this isn't implicit ?
>> If they found a view, replace the existing view with the new one (on the
>> other words, drop and create again?)
> Replacing is not exactly the same thing as dropping and recreating it.
> If the view has dependencies, you can't drop it without dropping the
> dependent objects first, and likewise you can't change its datatypes
> because it would affect the dependent objects as well (hence the
> limitation on CREATE OR REPLACE VIEW).
Right. And the reason this appears to be a data type change is that
"NULL" is not length-constrained, so the type computed for the first
UNION's output is just bpchar (ie, unconstrained-length character)
rather than character(3) which is what you get in the second case.
regards, tom lane
On Tue, 2007-09-04 at 07:42 +0000, Luiz K. Matsumura wrote: > When we do a command Create or Replace View that change columns of > previous view we got a error. Right. Many folks consider views to be a sort of API to the database. Using views to provide an API substantially insulates client code from implementation changes because the logic "behind" a view can change without changing the interface. So, although the prohibition against changing the column specification is occasionally irksome, I have grown to appreciate the protection from wanton changes to the API that this prohibition provides. Here are two tricks that may help you (and which you may already know): 1) Views can be renamed. When you do this, nearly all intra-cluster references to it are updated. So, if your view is /solely/ for use within the database, this allows you to deprecate a view. 2) Views can depend on views. So, consider your view1 as version1, then create view2 which depends on view1 and merely recasts the type1 column as character(3). New code can use view2 and old code can be migrated to view2 as needed. -Reece -- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
Wow, I learn a lot about views now Sorry for my confusion. You are right, my reasoning is very limited. Thanks Heikki , Tom and Reece by yours answers. Tom Lane wrote: > "Heikki Linnakangas" <heikki@enterprisedb.com> writes: > >> Luiz K. Matsumura wrote: >> >>> But, with the 'replace' command, this isn't implicit ? >>> If they found a view, replace the existing view with the new one (on the >>> other words, drop and create again?) >>> > > >> Replacing is not exactly the same thing as dropping and recreating it. >> If the view has dependencies, you can't drop it without dropping the >> dependent objects first, and likewise you can't change its datatypes >> because it would affect the dependent objects as well (hence the >> limitation on CREATE OR REPLACE VIEW). >> > > Right. And the reason this appears to be a data type change is that > "NULL" is not length-constrained, so the type computed for the first > UNION's output is just bpchar (ie, unconstrained-length character) > rather than character(3) which is what you get in the second case. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > > -- Luiz K. Matsumura Plan IT Tecnologia Informática Ltda.