Re: [HACKERS] CREATE OR REPLACE VIEW bug

[Tom Lane]

Dean Rasheed  writes:
> Attached is a patch enforcing this order and adding some comments to
> make it clear why the order matters here.
> Barring objections I'll back-patch this to 9.4 where WCO was added.

Looks reasonable in a quick once-over.  I didn't test it.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] CREATE OR REPLACE VIEW bug

[Dean Rasheed]

On 17 December 2016 at 15:42, Dean Rasheed  wrote:
> It seems that there is a bug in CREATE OR REPLACE VIEW...
>
> DefineView()/DefineVirtualRelation() will need a little re-jigging to
> do things in the required order.

...and the required order for existing views is

1. Add any new columns
2. Add rules to store the new query
3. Update the view options

because 2 will fail if the view's columns don't match the query's columns.

Attached is a patch enforcing this order and adding some comments to
make it clear why the order matters here.

Barring objections I'll back-patch this to 9.4 where WCO was added.

Regards,
Dean
diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c
new file mode 100644
index c6b0e4f..414507f
--- a/src/backend/commands/view.c
+++ b/src/backend/commands/view.c
@@ -59,15 +59,13 @@ validateWithCheckOption(char *value)
 /*-
  * DefineVirtualRelation
  *
- * Create the "view" relation. `DefineRelation' does all the work,
- * we just provide the correct arguments ... at least when we're
- * creating a view.  If we're updating an existing view, we have to
- * work harder.
+ * Create a view relation and use the rules system to store the query
+ * for the view.
  *-
  */
 static ObjectAddress
 DefineVirtualRelation(RangeVar *relation, List *tlist, bool replace,
-	  List *options)
+	  List *options, Query *viewParse)
 {
 	Oid			viewOid;
 	LOCKMODE	lockmode;
@@ -162,18 +160,13 @@ DefineVirtualRelation(RangeVar *relation
 		checkViewTupleDesc(descriptor, rel->rd_att);
 
 		/*
-		 * The new options list replaces the existing options list, even if
-		 * it's empty.
-		 */
-		atcmd = makeNode(AlterTableCmd);
-		atcmd->subtype = AT_ReplaceRelOptions;
-		atcmd->def = (Node *) options;
-		atcmds = lappend(atcmds, atcmd);
-
-		/*
 		 * If new attributes have been added, we must add pg_attribute entries
 		 * for them.  It is convenient (although overkill) to use the ALTER
 		 * TABLE ADD COLUMN infrastructure for this.
+		 *
+		 * Note that we must do this before updating the query for the view,
+		 * since the rules system requires that the correct view columns be in
+		 * place when defining the new rules.
 		 */
 		if (list_length(attrList) > rel->rd_att->natts)
 		{
@@ -192,9 +185,38 @@ DefineVirtualRelation(RangeVar *relation
 atcmd->def = (Node *) lfirst(c);
 atcmds = lappend(atcmds, atcmd);
 			}
+
+			AlterTableInternal(viewOid, atcmds, true);
+
+			/* Make the new view columns visible */
+			CommandCounterIncrement();
 		}
 
-		/* OK, let's do it. */
+		/*
+		 * Update the query for the view.
+		 *
+		 * Note that we must do this before updating the view options, because
+		 * the new options may not be compatible with the old view query (for
+		 * example if we attempt to add the WITH CHECK OPTION, we require that
+		 * the new view be automatically updatable, but the old view may not
+		 * have been).
+		 */
+		StoreViewQuery(viewOid, viewParse, replace);
+
+		/* Make the new view query visible */
+		CommandCounterIncrement();
+
+		/*
+		 * Finally update the view options.
+		 *
+		 * The new options list replaces the existing options list, even if
+		 * it's empty.
+		 */
+		atcmd = makeNode(AlterTableCmd);
+		atcmd->subtype = AT_ReplaceRelOptions;
+		atcmd->def = (Node *) options;
+		atcmds = list_make1(atcmd);
+
 		AlterTableInternal(viewOid, atcmds, true);
 
 		ObjectAddressSet(address, RelationRelationId, viewOid);
@@ -211,7 +233,7 @@ DefineVirtualRelation(RangeVar *relation
 		ObjectAddress address;
 
 		/*
-		 * now set the parameters for keys/inheritance etc. All of these are
+		 * Set the parameters for keys/inheritance etc. All of these are
 		 * uninteresting for views...
 		 */
 		createStmt->relation = relation;
@@ -224,13 +246,20 @@ DefineVirtualRelation(RangeVar *relation
 		createStmt->if_not_exists = false;
 
 		/*
-		 * finally create the relation (this will error out if there's an
-		 * existing view, so we don't need more code to complain if "replace"
-		 * is false).
+		 * Create the relation (this will error out if there's an existing
+		 * view, so we don't need more code to complain if "replace" is
+		 * false).
 		 */
 		address = DefineRelation(createStmt, RELKIND_VIEW, InvalidOid, NULL,
  NULL);
 		Assert(address.objectId != InvalidOid);
+
+		/* Make the new view relation visible */
+		CommandCounterIncrement();
+
+		/* Store the query for the view */
+		StoreViewQuery(address.objectId, viewParse, replace);
+
 		return address;
 	}
 }
@@ -530,16 +559,7 @@ DefineView(ViewStmt *stmt, const char *q
 	 * aborted.
 	 */
 	address = DefineVirtualRelation(view, viewParse->targetList,
-	stmt->replace, stmt->options);
-
-	/*
-	 * The relation we have just created is not visible to any other commands
-	 * running with the same transaction & command id. So, increment 

[HACKERS] CREATE OR REPLACE VIEW bug

[Dean Rasheed]

It seems that there is a bug in CREATE OR REPLACE VIEW's handling of
WITH CHECK OPTION (noticed while thinking about the recent change to
pg_dump's handling of circular dependencies in views -- d8c05af). If
you use CREATE OR REPLACE VIEW on a view that isn't auto-updatable and
turn it into one that is, and at the same time attempt to add a WITH
CHECK OPTION (which is exactly what pg_dump will now do) it fails:

CREATE TABLE t1 (a int);
CREATE VIEW v1 AS SELECT null::int AS a;
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 WHERE a > 0 WITH CHECK OPTION;

ERROR:  WITH CHECK OPTION is supported only on automatically updatable views
HINT:  Views that do not select from a single table or view are not
automatically updatable.


The problem is that before updating the view's query, DefineView()
calls DefineVirtualRelation() which attempts to add the new check
option to the existing view via the ALTER VIEW mechanism, and that
fails because the new check option isn't valid against the old view
query.

So if we're going to use the ALTER VIEW mechanism to update the view's
options, which is probably the most convenient way to do it,
DefineView()/DefineVirtualRelation() will need a little re-jigging to
do things in the required order.

I'll try to knock up a patch to do that.

Regards,
Dean


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] create or replace view

[Zeugswetter Andreas SB SD]

 Problem is when I want change view (or functions) with a lot of dependecies
 I must drop and recreate all dependent views (or functions) - 
 I want add only one column in view 
 I don't know if solution hard for that.

I do not see how adding a column to a view would invalidate
dependent objects. (Except an object that uses select *, in which case
the writer of the object explicitly states that he can cope with changing 
column count and order). 

Thus I think create or replace should work in this case regardless of 
what definition for create or replace finds a consensus, no ?

Andreas

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] create or replace view

[Stephan Szabo]

On Fri, 15 Nov 2002, Zeugswetter Andreas SB SD wrote:


  Problem is when I want change view (or functions) with a lot of dependecies
  I must drop and recreate all dependent views (or functions) -
  I want add only one column in view
  I don't know if solution hard for that.

 I do not see how adding a column to a view would invalidate
 dependent objects. (Except an object that uses select *, in which case
 the writer of the object explicitly states that he can cope with changing
 column count and order).

I'm not sure, but can all the places that currently save a plan deal with
getting a longer rowtype than expected? I'd guess so due to inheritance,
but we'd have to be absolutely sure. It'd also change the return type
for functions that are defined to return the composite type the view
defines.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[HACKERS] create or replace view

[snpe]

Hello,
  When I change view and change number of column PostgreSQL return error :
'cannot change number of column in view'
Is it too hard set this command
if view exits drop view
and then change view

It is like with return type in function

Now 'or replace' don't help too much

regards

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] create or replace view

[Bruno Wolff III]

On Thu, Nov 14, 2002 at 13:41:18 +,
  snpe [EMAIL PROTECTED] wrote:
 Hello,
   When I change view and change number of column PostgreSQL return error :
 'cannot change number of column in view'
 Is it too hard set this command
 if view exits drop view
 and then change view
 
 It is like with return type in function
 
 Now 'or replace' don't help too much

The create or replace command exists so that you can modify a view in a
way that allows other objects that refer to it to keep working (without
having to recreate those objects). However if you can the number of
columns (and probably any of their types), then these other objects
or going to need to know that things have changed so that you can't
just replace the view.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [HACKERS] create or replace view

[snpe]

On Thursday 14 November 2002 02:41 pm, Bruno Wolff III wrote:
 On Thu, Nov 14, 2002 at 13:41:18 +,

   snpe [EMAIL PROTECTED] wrote:
  Hello,
When I change view and change number of column PostgreSQL return error
  : 'cannot change number of column in view'
  Is it too hard set this command
  if view exits drop view
  and then change view
 
  It is like with return type in function
 
  Now 'or replace' don't help too much

 The create or replace command exists so that you can modify a view in a
 way that allows other objects that refer to it to keep working (without
 having to recreate those objects). However if you can the number of
 columns (and probably any of their types), then these other objects
 or going to need to know that things have changed so that you can't
 just replace the view.

I undestand that, but if I change number of column I want that
'create or replace view' do 'drop view ..; create view ..;'
Why not ?

regards
Haris Peco

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] create or replace view

[Rod Taylor]

On Thu, 2002-11-14 at 11:17, snpe wrote:
 On Thursday 14 November 2002 02:41 pm, Bruno Wolff III wrote:
  On Thu, Nov 14, 2002 at 13:41:18 +,
 
snpe [EMAIL PROTECTED] wrote:
   Hello,
 When I change view and change number of column PostgreSQL return error
   : 'cannot change number of column in view'
   Is it too hard set this command
   if view exits drop view
   and then change view
  
   It is like with return type in function
  
   Now 'or replace' don't help too much
 
  The create or replace command exists so that you can modify a view in a
  way that allows other objects that refer to it to keep working (without
  having to recreate those objects). However if you can the number of
  columns (and probably any of their types), then these other objects
  or going to need to know that things have changed so that you can't
  just replace the view.
 
 I undestand that, but if I change number of column I want that
 'create or replace view' do 'drop view ..; create view ..;'
 Why not ?

Now you've just broken all functions, views, rules, and triggers that
depend on that view to function.

-- 
  Rod Taylor


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] create or replace view

[snpe]

On Thursday 14 November 2002 04:38 pm, Rod Taylor wrote:
 On Thu, 2002-11-14 at 11:17, snpe wrote:
  On Thursday 14 November 2002 02:41 pm, Bruno Wolff III wrote:
   On Thu, Nov 14, 2002 at 13:41:18 +,
  
 snpe [EMAIL PROTECTED] wrote:
Hello,
  When I change view and change number of column PostgreSQL return
error
   
: 'cannot change number of column in view'
   
Is it too hard set this command
if view exits drop view
and then change view
   
It is like with return type in function
   
Now 'or replace' don't help too much
  
   The create or replace command exists so that you can modify a view in a
   way that allows other objects that refer to it to keep working (without
   having to recreate those objects). However if you can the number of
   columns (and probably any of their types), then these other objects
   or going to need to know that things have changed so that you can't
   just replace the view.
 
  I undestand that, but if I change number of column I want that
  'create or replace view' do 'drop view ..; create view ..;'
  Why not ?

 Now you've just broken all functions, views, rules, and triggers that
 depend on that view to function.
But I can simple:
drop view view_name;
create view view_name ...;

I want that 'create or replace view' work drop-create if view exists else only 
create

regards
Haris Peco


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] create or replace view

[Bruno Wolff III]

On Thu, Nov 14, 2002 at 16:49:42 +,
  snpe [EMAIL PROTECTED] wrote:
 
 I want that 'create or replace view' work drop-create if view exists else only 
 create

Why do you want this?

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] create or replace view

[snpe]

On Thursday 14 November 2002 05:01 pm, Bruno Wolff III wrote:
 On Thu, Nov 14, 2002 at 16:49:42 +,

   snpe [EMAIL PROTECTED] wrote:
  I want that 'create or replace view' work drop-create if view exists else
  only create

 Why do you want this?


Why 'create or replace' ?


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] create or replace view

[Bruno Wolff III]

On Thu, Nov 14, 2002 at 17:00:30 +,
  snpe [EMAIL PROTECTED] wrote:
 On Thursday 14 November 2002 05:01 pm, Bruno Wolff III wrote:
  On Thu, Nov 14, 2002 at 16:49:42 +,
 
snpe [EMAIL PROTECTED] wrote:
   I want that 'create or replace view' work drop-create if view exists else
   only create
 
  Why do you want this?
 
 
 Why 'create or replace' ?

Why do you want create or replace to do a drop, then a create if the view
exists but it is being changed in a way that will break any objects that
refer to the old view?

Are you trying to save typing a few characters or what?

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [HACKERS] create or replace view

[snpe]

On Thursday 14 November 2002 05:22 pm, Bruno Wolff III wrote:
 On Thu, Nov 14, 2002 at 17:00:30 +,

   snpe [EMAIL PROTECTED] wrote:
  On Thursday 14 November 2002 05:01 pm, Bruno Wolff III wrote:
   On Thu, Nov 14, 2002 at 16:49:42 +,
  
 snpe [EMAIL PROTECTED] wrote:
I want that 'create or replace view' work drop-create if view exists
else only create
  
   Why do you want this?
 
  Why 'create or replace' ?

 Why do you want create or replace to do a drop, then a create if the view
 exists but it is being changed in a way that will break any objects that
 refer to the old view?

 Are you trying to save typing a few characters or what?
Yes, it is 'create or replace view', not ?


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [HACKERS] create or replace view

[Tom Lane]

snpe [EMAIL PROTECTED] writes:
 On Thursday 14 November 2002 05:22 pm, Bruno Wolff III wrote:
 Are you trying to save typing a few characters or what?

 Yes, it is 'create or replace view', not ?

The statement was not invented to save a few characters of typing.
It was invented to allow people to make internal changes to view
definitions without breaking other objects that refer to the view.

If we made it automatically drop and recreate the view then we'd
be defeating the purpose.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [HACKERS] create or replace view

[Scott Shattuck]

Tom Lane wrote:

snpe [EMAIL PROTECTED] writes:


On Thursday 14 November 2002 05:22 pm, Bruno Wolff III wrote:


Are you trying to save typing a few characters or what?





Yes, it is 'create or replace view', not ?



The statement was not invented to save a few characters of typing.
It was invented to allow people to make internal changes to view
definitions without breaking other objects that refer to the view.

If we made it automatically drop and recreate the view then we'd
be defeating the purpose.


It might just be me but it seems that this discussion is missing the 
point if we believe this request is about saving some characters. I 
don't think it is. I think it's about being able to write simple SQL 
scripts that don't produce errors when you use the syntax below in an 
adminstration or development script and the object doesn't exist:

	drop...
	create...


The accepted syntax in both PG and others for trying to avoiding this 
issue is:

	create or replace

Using this syntax the database script will run without errors, quietly 
adjusting the object definition as required. Perfect. That's what we want.

Now I'm only interpreting here and haven't run into this problem myself 
in PG but it appears from some of the early posts on this subject that 
PG isn't consistent in whether it will allow the change to occur, at 
least with respect to views. Instead, PG apparently tries to help by 
not updating the view if the views' result schema would be different, 
hence the request (perhaps misguided by trying to specify how instead 
of what) to drop/create.


Assuming that's a correct assessment and summary of the problem then 
reviewing the following use cases seems in order:


1. The view doesn't exist.

Action: create the new view


2. The view exists and the change can be determined to be benign, 
presumably because the virtual table schema of the view retains the same 
column specifications (names and types match original specification).

Action: replace the view in situ so that dependencies are ok


3. The view exists but the change isn't benign and it's clear that other 
objects referencing the view are going to have issues since column 
names, types, number, etc. are being changed.

Action 3: drop/create the view. Optionally we might consider doing a 
NOTIFY dependent object references which might also work nicely in 
other areas such as trigger functions etc.


Why drop/create? (or appropriate similar internal operation). A lot of 
reasons actually.

First, this use case, by definition, says the new view's going to break 
other objects -- and that this will be true regardless of whether I use 
create-replace or drop/create. So not allowing create-replace to operate 
as sugar changes nothing in terms of the resulting schema issues upon 
statement completion. It has a big impact on my SQL though, since 
drop/create may throw errors that create-replace won't. So we haven't 
solved a problem by ignoring case #3. Instead we've continued to require 
developers to use a syntax guaranteed to throw errors. Cool.

Second, if there are other objects depending on the view to look a 
certain way, and I'm knowingly changing the view what can you infer? One 
might choose to infer The programmer's an idiot for wanting to break 
his schema like this. I see far too much code written from this 
attitude...it's what I hate about most M$ code. I prefer to infer that 
The programmer's a human being who might just be 10x smarter than 
me...maybe I should let him do his job as he sees fit.

As an aside, this is the UNIX philosophy. Not only do we not try to 
protect you from yourself by taking away all the guns (no command prompt 
etc), we give you a fully loaded semi-automatic weapon (C, shell, etc) 
with the safety off (root) and say Be careful.

soapbox

So, instead of assuming that we know more about what's right than the 
programmer, perhaps we should try assuming that the programmer's next 
SQL script lines will adapt to the new view definition and make the 
appropriate changes -- perhaps via a series of more create or replace 
statements ;). A reasonable developer/DBA should know they're changing 
the view in a way that isn't compatible with previously defined 
dependents, just as they should realize dependencies may exist when they 
alter schema in general. If not, then hey we told you to Be careful.

The create or replace syntax, in my mind anyway, wasn't designed to 
say If you can create, do so. If you can replace, do so. If you have to 
drop, tell the programmer to bite you as implied by many of the posts 
on this thread. It has a different goal, one of making the developer or 
DBA's life easier (which occasionally means saving characters BTW. I 
mean, if people weren't concerned about that how can you explain Unix or 
Perl? ;) ).

If we're concerned with this change from a consistency perspective, look 
at triggers. The programmer drops a function and the triggers relying on 
that function go to hell. 

Re: [HACKERS] create or replace view

[Stephan Szabo]

On Thu, 14 Nov 2002, Scott Shattuck wrote:

 It might just be me but it seems that this discussion is missing the
 point if we believe this request is about saving some characters. I
 don't think it is. I think it's about being able to write simple SQL
 scripts that don't produce errors when you use the syntax below in an
 adminstration or development script and the object doesn't exist:

I think there are two groups of people who have different ideas of what
this functionality is supposed to do. From my understanding of the
discussions on create or replace function, the point really was to do an
in place modification to not need to drop and recreate dependent objects.
Note that afaik you also can't change the return type of a function in a
create or replace if it already exists with a different return type.

The other usage is useful, but I don't think it was the intended way to be
used. I use it that way too, but if I get an error on a create or replace
I do the more involved version (dump dependents if necessary, drop
cascade, create, edit dump, restore).

 If we're concerned with this change from a consistency perspective, look
 at triggers. The programmer drops a function and the triggers relying on
 that function go to hell. Sure, and if we said you can't drop the
 function because triggers might break then it'd parallel what we're
 saying here -- in effect we know better than you do what you want. Or
 to use M$ terminology we know where you want to go today ;).

In fact, afaict 7.3 does exactly this unless you use drop cascade.
I don't think that the past way was particularly easier, with needing to
dump/restore dependent objects in order to make them work again. I think
of it like constraints, as much as you can you enforce the constraint.
It's possible that the next statement will make the sequence
work for the constraint, but you don't wait to find out.

 B. We want to treat people who are interested in PostgreSQL with respect
 at all times, keeping in mind that we communicate with them not only
 through this forum, but through the code we write for them.

This is always true. Even if we forget sometimes. :)



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [HACKERS] create or replace view

[snpe]

On Thursday 14 November 2002 05:45 pm, Tom Lane wrote:
 snpe [EMAIL PROTECTED] writes:
  On Thursday 14 November 2002 05:22 pm, Bruno Wolff III wrote:
  Are you trying to save typing a few characters or what?
 
  Yes, it is 'create or replace view', not ?

 The statement was not invented to save a few characters of typing.
 It was invented to allow people to make internal changes to view
 definitions without breaking other objects that refer to the view.

 If we made it automatically drop and recreate the view then we'd
 be defeating the purpose.

Does it mean that if I will change any object (view or function) I must
drop all dependent objects ?
example :
  I want change (number of columns) view viewa
If viewb depend of viewa, I must drop and create viewa and viewb ?

Does it possible that viewb stay temporary (or always) invalid ?
 recreate viewa will make viewb valid or pgsql return error for viewb ?

regards
Haris Peco

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] create or replace view

[snpe]

On Thursday 14 November 2002 08:01 pm, Stephan Szabo wrote:
 On Thu, 14 Nov 2002, Scott Shattuck wrote:
  It might just be me but it seems that this discussion is missing the
  point if we believe this request is about saving some characters. I
  don't think it is. I think it's about being able to write simple SQL
  scripts that don't produce errors when you use the syntax below in an
  adminstration or development script and the object doesn't exist:

 I think there are two groups of people who have different ideas of what
 this functionality is supposed to do. From my understanding of the
 discussions on create or replace function, the point really was to do an
 in place modification to not need to drop and recreate dependent objects.
 Note that afaik you also can't change the return type of a function in a
 create or replace if it already exists with a different return type.

 The other usage is useful, but I don't think it was the intended way to be
 used. I use it that way too, but if I get an error on a create or replace
 I do the more involved version (dump dependents if necessary, drop
 cascade, create, edit dump, restore).

  If we're concerned with this change from a consistency perspective, look
  at triggers. The programmer drops a function and the triggers relying on
  that function go to hell. Sure, and if we said you can't drop the
  function because triggers might break then it'd parallel what we're
  saying here -- in effect we know better than you do what you want. Or
  to use M$ terminology we know where you want to go today ;).

 In fact, afaict 7.3 does exactly this unless you use drop cascade.
 I don't think that the past way was particularly easier, with needing to
 dump/restore dependent objects in order to make them work again. I think
 of it like constraints, as much as you can you enforce the constraint.
 It's possible that the next statement will make the sequence
 work for the constraint, but you don't wait to find out.

  B. We want to treat people who are interested in PostgreSQL with respect
  at all times, keeping in mind that we communicate with them not only
  through this forum, but through the code we write for them.

 This is always true. Even if we forget sometimes. :)


Problem is when I want change view (or functions) with a lot of dependecies
I must drop and recreate all dependent views (or functions) - I want add only
one column in view 
I don't know if solution hard for that.

regards
Haris Peco

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [HACKERS] create or replace view

[Stephan Szabo]

On Thu, 14 Nov 2002, snpe wrote:

 Problem is when I want change view (or functions) with a lot of dependecies
 I must drop and recreate all dependent views (or functions) - I want add only
 one column in view
 I don't know if solution hard for that.

Well, doing create or replace as a drop/create might very well do the same
thing, and even if it got the same oid, we'd have to be really sure that
nothing would misbehave upon receiving that extra column before allowing
it for purposes of avoiding recreation of dependencies.



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [HACKERS] create or replace view

[Christopher Kings-Lynne]

 Problem is when I want change view (or functions) with a lot of
 dependecies
 I must drop and recreate all dependent views (or functions) - I
 want add only
 one column in view
 I don't know if solution hard for that.

This is definitely something that will cause some anguish in 7.3.  I think
7.4 will need the concept of an invalid object that can be resurrected...

Chris


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] create or replace view

[snpe]

On Thursday 14 November 2002 10:36 pm, Stephan Szabo wrote:
 On Thu, 14 Nov 2002, snpe wrote:
  Problem is when I want change view (or functions) with a lot of
  dependecies I must drop and recreate all dependent views (or functions) -
  I want add only one column in view
  I don't know if solution hard for that.

 Well, doing create or replace as a drop/create might very well do the same
 thing, and even if it got the same oid, we'd have to be really sure that
 nothing would misbehave upon receiving that extra column before allowing
 it for purposes of avoiding recreation of dependencies.


Can PostgreSQL recreate dependecies automaticly or say 'object is not valid'

regards
Haris Peco

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [HACKERS] create or replace view

[Christopher Kings-Lynne]

  Well, doing create or replace as a drop/create might very well
 do the same
  thing, and even if it got the same oid, we'd have to be really sure that
  nothing would misbehave upon receiving that extra column before allowing
  it for purposes of avoiding recreation of dependencies.
 
 
 Can PostgreSQL recreate dependecies automaticly or say 'object is
 not valid'

7.3 doesn't do 'object is not valid'

Chris


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Rene Pijlman]

On Tue, 23 Oct 2001 17:16:06 +0200, you wrote:
CREATE OR DROP VIEW 

Is this for real? If I were a database server I would say to the
client please make up your mind :-)

Regards,
René Pijlman [EMAIL PROTECTED]

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Jean-Michel POURE]

 CREATE OR DROP VIEW
Is this for real? If I were a database server I would say to the
client please make up your mind :-)

I meant DROP IF EXISTS and then CREATE.
This is more simple to implement than CREATE OR REPLACE.

Best regards,
Jean-Michel POURE

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Bruce Momjian]

I have added this to the TODO list:

* Add OR REPLACE clauses to non-FUNCTION object creation

I think there are clearly some other objects that need OR REPLACE.  Not
sure which ones yet.


---

 Dear all,
 
 Would it be possible to implement CREATE OR REPLACE VIEW / TRIGGER in 
 PostgreSQL 7.2?
 
 Alternatively, could someone implement CREATE OR DROP VIEW / TRIGGER? These 
 features are needed for pgAdmin II (we could also provide a patch for 
 PhpPgAdmin). If this cannot be implemented in PostgreSQL, we will go for 
 pseudo-modification solutions (which is definitely not a good solution).
 
 We are also waiting for a proper ALTER table DROP column but we are day 
 dreamers...
 
 Thanks for your help and comprehension.
 Best regards,
 Jean-Michel POURE
 pgAdmin team
 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Tom Lane]

Bruce Momjian [EMAIL PROTECTED] writes:
 Our current CREATE OR REPLACE FUNCTION perserves the OID of the
 function.  Is there similar functionality you need where a simple
 DROP (ignore the error), CREATE will not work?
 
 If possible, it's nice to not have commands whose error codes you ignore.
 That way if you see an error, you know you need to do something about it.

 Folks, is this a valid reason for adding OR REPLACE to all CREATE object
 commands?

Not until we do the necessary legwork.  I spent a good deal of time over
the past week making the various PL modules react to replacement of
pg_proc entries by CREATE OR REPLACE FUNCTION (cf. complaint from Peter
a week or so back).  CREATE OR REPLACE VIEW implies updating cached
query plans, and I'm not sure what CREATE OR REPLACE TRIGGER implies.
But I am pretty sure it's not a trivial question.

In short: put it on the todo list, but note that there are some
implications...

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Bill Studenmund]

On Tue, 23 Oct 2001, Bruce Momjian wrote:

  Dear all,
 
  Would it be possible to implement CREATE OR REPLACE VIEW / TRIGGER in
  PostgreSQL 7.2?

Probably not, it's rather late in the cycle (isn't beta imminent?). Oh,
I'd vote for OR REPLACE as there's already an opt_or_replace
non-terminal in the parser. Adding an optional OR DROP might displease
yacc, and also follows in the same vein as what we have for CREATE
FUNCTION.

  Alternatively, could someone implement CREATE OR DROP VIEW / TRIGGER? These
  features are needed for pgAdmin II (we could also provide a patch for
  PhpPgAdmin). If this cannot be implemented in PostgreSQL, we will go for
  pseudo-modification solutions (which is definitely not a good solution).

 Our current CREATE OR REPLACE FUNCTION perserves the OID of the
 function.  Is there similar functionality you need where a simple
 DROP (ignore the error), CREATE will not work?

If possible, it's nice to not have commands whose error codes you ignore.
That way if you see an error, you know you need to do something about it.

Take care,

Bill


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Bruce Momjian]

 Not until we do the necessary legwork.  I spent a good deal of time over
 the past week making the various PL modules react to replacement of
 pg_proc entries by CREATE OR REPLACE FUNCTION (cf. complaint from Peter
 a week or so back).  CREATE OR REPLACE VIEW implies updating cached
 query plans, and I'm not sure what CREATE OR REPLACE TRIGGER implies.
 But I am pretty sure it's not a trivial question.
 
 In short: put it on the todo list, but note that there are some
 implications...

That's all I needed to know.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Mike Mascari]

Bruce Momjian wrote:
 
Alternatively, could someone implement CREATE OR DROP VIEW / TRIGGER? These
features are needed for pgAdmin II (we could also provide a patch for
PhpPgAdmin). If this cannot be implemented in PostgreSQL, we will go for
pseudo-modification solutions (which is definitely not a good solution).
  
   Our current CREATE OR REPLACE FUNCTION perserves the OID of the
   function.  Is there similar functionality you need where a simple
   DROP (ignore the error), CREATE will not work?
 
  If possible, it's nice to not have commands whose error codes you ignore.
  That way if you see an error, you know you need to do something about it.
 
 Folks, is this a valid reason for adding OR REPLACE to all CREATE object
 commands?

Well, Oracle has CREATE OR REPLACE for:

Views
Functions
Procedures
Triggers
Types
Packages

but not for (at least 8.0.5):

Tables
Indexes
Sequences

At first glance, I'm not sure why Oracle doesn't allow for the
replacement of the non-compiled objects. Perhaps the complexities
involved in enforcing RI was too much. The *major* advantage to
allowing a REPLACE in Oracle is to preserve permissions granted to
various users and groups (aka ROLES). Oracle automatically
recompiles views, functions, procedures, etc. if their underlying
dependencies change:

SQL CREATE TABLE employees (key integer, salary float);

Table created.

SQL CREATE VIEW salaries AS SELECT * FROM employees WHERE salary 
15000;

View created.

SQL SELECT * FROM salaries;

no rows selected

SQL DROP TABLE employees;

Table dropped.

SQL SELECT * FROM salaries;
SELECT * FROM salaries
  *
ERROR at line 1:
ORA-04063: view MASCARM.SALARIES has errors


SQL CREATE TABLE employees (key integer, salary float);

Table created.

SQL SELECT * FROM salaries;

no rows selected

So it seems to me that the major reason is to preserve GRANT/REVOKE
privileges issues against the object in question.

FWIW,

Mike Mascari
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Bill Studenmund]

On Tue, 23 Oct 2001, Bruce Momjian wrote:

  If possible, it's nice to not have commands whose error codes you ignore.
  That way if you see an error, you know you need to do something about it.

 Folks, is this a valid reason for adding OR REPLACE to all CREATE object
 commands?

Sounds good to me. :-)

Take care,

Bill


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Peter Eisentraut]

Bill Studenmund writes:

  Our current CREATE OR REPLACE FUNCTION perserves the OID of the
  function.  Is there similar functionality you need where a simple
  DROP (ignore the error), CREATE will not work?

 If possible, it's nice to not have commands whose error codes you ignore.
 That way if you see an error, you know you need to do something about it.

Technically, it's not an error, it's an exception condition.  This might
make you feel better when consciously ignoring it. ;-)

-- 
Peter Eisentraut   [EMAIL PROTECTED]   http://funkturm.homeip.net/~peter


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Tom Lane]

Jean-Michel POURE [EMAIL PROTECTED] writes:
 Would it be possible to implement CREATE OR REPLACE VIEW / TRIGGER in 
 PostgreSQL 7.2?

We're already vastly overdue for beta.  The time for new feature
requests for 7.2 is past ... especially nontrivial requests.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Jean-Michel POURE]

Dear all,

Would it be possible to implement CREATE OR REPLACE VIEW / TRIGGER in 
PostgreSQL 7.2?

Alternatively, could someone implement CREATE OR DROP VIEW / TRIGGER? These 
features are needed for pgAdmin II (we could also provide a patch for 
PhpPgAdmin). If this cannot be implemented in PostgreSQL, we will go for 
pseudo-modification solutions (which is definitely not a good solution).

We are also waiting for a proper ALTER table DROP column but we are day 
dreamers...

Thanks for your help and comprehension.
Best regards,
Jean-Michel POURE
pgAdmin team

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Bruce Momjian]

 Dear all,
 
 Would it be possible to implement CREATE OR REPLACE VIEW / TRIGGER in 
 PostgreSQL 7.2?
 
 Alternatively, could someone implement CREATE OR DROP VIEW / TRIGGER? These 
 features are needed for pgAdmin II (we could also provide a patch for 
 PhpPgAdmin). If this cannot be implemented in PostgreSQL, we will go for 
 pseudo-modification solutions (which is definitely not a good solution).

Our current CREATE OR REPLACE FUNCTION perserves the OID of the
function.  Is there similar functionality you need where a simple
DROP (ignore the error), CREATE will not work?

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Bruce Momjian]

   Alternatively, could someone implement CREATE OR DROP VIEW / TRIGGER? These
   features are needed for pgAdmin II (we could also provide a patch for
   PhpPgAdmin). If this cannot be implemented in PostgreSQL, we will go for
   pseudo-modification solutions (which is definitely not a good solution).
 
  Our current CREATE OR REPLACE FUNCTION perserves the OID of the
  function.  Is there similar functionality you need where a simple
  DROP (ignore the error), CREATE will not work?
 
 If possible, it's nice to not have commands whose error codes you ignore.
 That way if you see an error, you know you need to do something about it.

Folks, is this a valid reason for adding OR REPLACE to all CREATE object
commands?


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

[Bruce Momjian]

 We are also waiting for a proper ALTER table DROP column but we are day 
 dreamers...

This is a good example of bad management on our parts.  We couldn't
decide between two possible DROP COLUMN implementations, so we now have
the worst result, which is no implementation at all.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]