Обсуждение: Feature Request: ALTER FUNCTION (or something like that)
(I know that everyone is focusing on 7.1. This is a question about how hard it would be to plan a feature for 7.2 or later) One of the small annoyances in PG is that I use many functions to handle small details, and these functions are called by views. If I want to improve a function, I have to drop and recreate those views (and all views that depend on them, etc.) If I understand everything, this is because the function OID is used to call the function, not its name. How difficult would it be to either . allow creation of a function using a specific OID? (CREATE FUNCTION USING OID xxxxx ...) or . add a command like ALTER FUNCTION foo(text) TO foo(text) returns text as .... Then, (presumably?) the calling functions and views could just find the function by its old oid. This would seem to require that the new function would take the same parameters (and return the same?) as the old function. This could be handled either as a runtime check by the ALTER command, or it could be left as caveat functioner. Perhaps this is tricky, or perhaps there's already a great solution. (If so, please tell!) If I'm making an idiot of myself, do let me know. Otherwise, is this feasible? I could try my rusty hand at C, but I'd need someone to give me a 2-paragraph game plan on where to start playing with the code. Thanks! (of course, this raises the same question for VIEWs... dropping and creating them is a pain b/c of the same reasons...) -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
Joel Burton <jburton@scw.org> writes:
> . add a command like ALTER FUNCTION foo(text) TO foo(text) returns text as
> ....
This is on the TODO list already, I believe.
> This would seem to require that the new function would take the same
> parameters (and return the same?) as the old function.
If it doesn't take the same parameters then it's not the same function
at all, so that part is a nonissue. We'd have to disallow change of
return type as well.
> Perhaps this is tricky,
Updating pg_proc wouldn't be hard. What's missing is a notification
mechanism that would cause cached copies of the function to get
replaced. A crude first cut could just ignore that issue and still be
extremely useful for development ...
regards, tom lane
On Tue, 27 Mar 2001, Tom Lane wrote: > Joel Burton <jburton@scw.org> writes: > > . add a command like ALTER FUNCTION foo(text) TO foo(text) returns text as > > .... > > This is on the TODO list already, I believe. Yikes. I should have read it more carefully. My apologies. There are so many good things on it. Sadly, no one has claimed this item. (I still didn't see ALTER VIEW, though, which would seem just as nice.) > > This would seem to require that the new function would take the same > > parameters (and return the same?) as the old function. > > If it doesn't take the same parameters then it's not the same function > at all, so that part is a nonissue. We'd have to disallow change of > return type as well. > > > Perhaps this is tricky, > > Updating pg_proc wouldn't be hard. What's missing is a notification > mechanism that would cause cached copies of the function to get > replaced. A crude first cut could just ignore that issue and still be > extremely useful for development ... A crude first cut would be useful for development... even if I had to restart postmaster, it's still much easier than dumping/restoring the whole database, which is the usual solution around here (trying to individually fix each view/table using the function becomes so tricky as one has to track every dependency after that, etc.) Dumping/restoring can be great (three cheers especially for the new formats using the -F switch!), but in the past, I've had one or two complicated views that would dump, but couldn't be restored from the dump. (As was discussed at the time in the list; the culprit was that pg_dump produced a deeply complex expresion with a bazillion nested parentheses; if one removed some of these, pg_restore did just fine.) This has disappeared in the 7.1 code, but I'm still slightly skitting about the dump-restore so very frequently cycle. If I have your ear on the subject, tgl, is there any ugly-but-working hack to update the function by modifying the system tables directly? As always, thanks, -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
Actually, This reminds me of something I have been meaning to ask. All the new performance features are cool, but what I really need are all the ALTER TABLE ... functions implemented. In 7.0.x you could only add columns and foreign keys. You couldn't drop anything or add stuff like CHECK constraints. Has this situation changed for 7.1? Regards, Chris -----Original Message----- From: pgsql-hackers-owner@postgresql.org [mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Joel Burton Sent: Wednesday, 28 March 2001 8:39 AM To: pgsql-hackers@postgresql.org Subject: [HACKERS] Feature Request: ALTER FUNCTION (or something like that) (I know that everyone is focusing on 7.1. This is a question about how hard it would be to plan a feature for 7.2 or later) One of the small annoyances in PG is that I use many functions to handle small details, and these functions are called by views. If I want to improve a function, I have to drop and recreate those views (and all views that depend on them, etc.) If I understand everything, this is because the function OID is used to call the function, not its name. How difficult would it be to either . allow creation of a function using a specific OID? (CREATE FUNCTION USING OID xxxxx ...) or . add a command like ALTER FUNCTION foo(text) TO foo(text) returns text as .... Then, (presumably?) the calling functions and views could just find the function by its old oid. This would seem to require that the new function would take the same parameters (and return the same?) as the old function. This could be handled either as a runtime check by the ALTER command, or it could be left as caveat functioner. Perhaps this is tricky, or perhaps there's already a great solution. (If so, please tell!) If I'm making an idiot of myself, do let me know. Otherwise, is this feasible? I could try my rusty hand at C, but I'd need someone to give me a 2-paragraph game plan on where to start playing with the code. Thanks! (of course, this raises the same question for VIEWs... dropping and creating them is a pain b/c of the same reasons...) -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Joel Burton <jburton@scw.org> writes:
> If I have your ear on the subject, tgl, is there any ugly-but-working hack
> to update the function by modifying the system tables directly?
For interpreted function languages,
UPDATE pg_proc SET prosrc = 'new body' WHERE proname = '...'
will work as long as the function name is unique. (If not, you'd need
to also mention argument types in the WHERE.)
Again, this won't do anything to update cached copies, so backend
restarts might be needed to get the change to take effect.
regards, tom lane
> Actually, > > This reminds me of something I have been meaning to ask. All the new > performance features are cool, but what I really need are all the ALTER > TABLE ... functions implemented. In 7.0.x you could only add columns and > foreign keys. You couldn't drop anything or add stuff like CHECK > constraints. Has this situation changed for 7.1? > Not done in 7.1. I hope for 7.2. -- 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, Pennsylvania19026
On Wed, 28 Mar 2001, Bruce Momjian wrote: > > Actually, > > > > This reminds me of something I have been meaning to ask. All the new > > performance features are cool, but what I really need are all the ALTER > > TABLE ... functions implemented. In 7.0.x you could only add columns and > > foreign keys. You couldn't drop anything or add stuff like CHECK > > constraints. Has this situation changed for 7.1? > > > > Not done in 7.1. I hope for 7.2. If you're curious, Christopher, on the TODO list is a whole page on the DROP COLUMN bit. There's been some headscratching about how we want to DROP COLUMNs (whether to hide them but keep them there, which may be easier to undo, and probably easier to develop, and doesn't require any time to process), or whether to actually recreate w/o the offending column. IIRC, you can change whether a field can be NULL or not by twiddling a field in pg_class. -- Joel Burton <jburton@scw.org> Director of Information Systems, Support Center of Washington
At 3/28/2001 09:52 AM, Joel Burton wrote: >On Wed, 28 Mar 2001, Bruce Momjian wrote: > > > > Actually, > > > > > > This reminds me of something I have been meaning to ask. All the new > > > performance features are cool, but what I really need are all the ALTER > > > TABLE ... functions implemented. In 7.0.x you could only add columns and > > > foreign keys. You couldn't drop anything or add stuff like CHECK > > > constraints. Has this situation changed for 7.1? > > > > > > > Not done in 7.1. I hope for 7.2. > >If you're curious, Christopher, on the TODO list is a whole page on the >DROP COLUMN bit. There's been some headscratching about how we want to >DROP COLUMNs (whether to hide them but keep them there, which may be >easier to undo, and probably easier to develop, and doesn't require any >time to process), or whether to actually recreate w/o the offending >column. You could hide and then delete on a vacuum. On a personal note, one "work around" for altering a table column is to rename the column, then do an update where you set the new columns value to the renamed columns value thus copying its data over to the new column with any casting if necessary. It doesn't always work, but it's one way to try. But then you're left with the renamed column dangling... Selecting the table(with your preferred columns) into a new table can clean it Delete the old table and rename the new table to the old name... You lose the constraints/triggers you had in place though... And you must pray that no one is altering the DB while your doing this...