Re: [HACKERS] CREATE OR REPLACE VIEW/TRIGGER

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

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
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> 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

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
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

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
mascarm@mascari.com

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 peter_e@gmx.net http://funkturm.homeip.net/~peter