Horton
Menu

Thread

  1. Feature Request: ALTER FUNCTION (or something like that)

    Joel Burton <jburton@scw.org> — 2001-03-28T00:38:59Z

    (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

  2. Re: Feature Request: ALTER FUNCTION (or something like that)

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-28T00:47:47Z

    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

  3. Re: Feature Request: ALTER FUNCTION (or something like that)

    Joel Burton <jburton@scw.org> — 2001-03-28T03:55:45Z

    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

  4. RE: Feature Request: ALTER FUNCTION (or something like that)

    Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2001-03-28T05:00:41Z

    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

  5. Re: Feature Request: ALTER FUNCTION (or something like that)

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-28T05:32:54Z

    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

  6. Re: Feature Request: ALTER FUNCTION (or something like that)

    Bruce Momjian <pgman@candle.pha.pa.us> — 2001-03-28T12:20:09Z

    > 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, Pennsylvania 19026

  7. Re: Feature Request: ALTER FUNCTION (or something like that)

    Joel Burton <jburton@scw.org> — 2001-03-28T15:52:28Z

    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

  8. Re: Feature Request: ALTER FUNCTION (or something like that)

    Thomas Swan <tswan-lst@ics.olemiss.edu> — 2001-03-28T16:55:35Z

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