A few weeks ago there was a discussion on how views should be
treated during dump/reload, and whether or not to change pg_dump
to use CREATE VIEW instead of the current CREATE TABLE/CREATE
RULE sequence. I suggested at that time that the dependencies
issue could be resolved if PostgreSQL would allow for the
creation of function prototypes in conjunction with ALTER
FUNCTION to supply the body of the function later.
Although I don't see it on the TODO list, one of the FAQs on
General is the problem related to triggers or rules which are
based upon functions. If a user executes a DROP/CREATE sequence
to change the function's body, those triggers or rules based upon
the function break, since the OID has changed.
The solution tossed around is to create a SQL command such as
"ALTER FUNCTION" or "CREATE OR REPLACE FUNCTION", etc. Under the
assumption that such a command were to exist, its sole purpose
would be to change the function implementation without changing
the OID.
Now back to pg_dump. Since the temporary solution to eliminating
dependency problems is to dump in OID order, with the current
code, things won't break. But with an ALTER FUNCTION, dumping in
OID order could very well break the schema:
CREATE TABLE employees (key int4);
CREATE FUNCTION numpeople() RETURNS int4 AS'SELECT COUNT(*) FROM employees' LANGUAGE 'plpgsql';
CREATE VIEW AS SELECT numpeople();
CREATE TABLE managers (key int4);
ALTER FUNCTION numpeople() AS 'SELECT COUNT(*) FROM managers' LANGUAGE 'plpgsql';
So what to do?
1) Don't create an ALTER FUNCTION command?
2) Change pg_dump to walk through dependencies?
3) Or devise a manner by which pg_dump can dump objects in a
certain sequence such that dependencies never break?
Any comments?
Mike Mascari
P.S. The reason why I'm asking is I thought I could at least
contribute some trivial SQL commands such as a CREATE FUNCTION
... AS NULL/ALTER FUNCTION, but it seems there are many
consequences to everything.
