Thread: I remember why I suggested CREATE FUNCTION...AS NULL

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.

On Fri, 8 Sep 2000, Mike Mascari wrote:
> 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';

Actually, I think this would still work, because it
doesn't check the table name existance until it's 
used for the first time, not when it's created.

> 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?

Well, I was discussing something related a few weeks (?) ago
for constraints, some kind of system that kept track of
what objects were dependent on what other objects.  Unfortunately,
it's not possible to do it completely without an awful lot
of work because you could have arguments to functions that
turned into objects to be dependent to.

You could limit the effect by defining the functions as NULL
(if you were to do that) in oid order and changing their code
later in the dump.  That way all of the objects are there
but may not be usable at that time.

Mike Mascari <mascarm@mascari.com> writes:
> 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:

Yes, that's been understood all along to be the weak spot of dumping
in OID order.  But you can already break dump-in-OID-order with
existing commands like ALTER TABLE ADD CONSTRAINT.  I think that the
advantages of ALTER FUNCTION are well worth the slightly increased risk
of dump/reload difficulties.

> 2) Change pg_dump to walk through dependencies?

The trouble with that is that dependency analysis is a monstrous job,
and one that would make pg_dump even more fragile and backend-version-
dependent than it is now.  Besides, with ALTER it is possible to create
*circular* dependencies, so even after you'd done the work you'd still
not have a bulletproof solution, just a 99.9% solution instead of a 99%
solution.  (Exact numbers open to debate, obviously, but you see my
point.)

> 3) Or devise a manner by which pg_dump can dump objects in a
> certain sequence such that dependencies never break?

If you've got one, step right up to the plate and swing away ;-).
You might be on the right track with the notion of creating functions
with dummy bodies and then doing ALTER later.  I haven't thought it
through in detail, but maybe something based on that attack could work.
        regards, tom lane

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>> ALTER FUNCTION numpeople() AS 
>> 'SELECT COUNT(*) FROM managers' LANGUAGE 'plpgsql';

> Actually, I think this would still work, because it
> doesn't check the table name existance until it's 
> used for the first time, not when it's created.

But SQL-language function bodies are checked at entry, not only
at first use.  (I consider it a deficiency of plpgsql that it
doesn't do likewise.)
        regards, tom lane