Thread: question about fixes in v7.4...
My company is fairly new at Postgresql, but we have hit a problem, where we modify a table using ALTER, and our stored procedures stop working. We have a grasp as to why this happens, but I was wondering if this v7.4 upgrade fixes this issue? Don Sceifers Harvest Info
Attachment
On Wed, 2003-11-19 at 11:17, Don Sceifers wrote: > My company is fairly new at Postgresql, but we have hit a problem, where > we modify a table using ALTER, and our stored procedures stop working. We > have a grasp as to why this happens, but I was wondering if this v7.4 > upgrade fixes this issue? At this time function bodies do not have the dependency tracking capabilities that a majority of the system has, so it is unable to complain when you remove or alter an object it depends on. With SQL functions it should be feasible. For functions that build queries on the fly this will be virtually impossible.
Don Sceifers wrote: > My company is fairly new at Postgresql, but we have hit a problem, where > we modify a table using ALTER, and our stored procedures stop working. We > have a grasp as to why this happens, but I was wondering if this v7.4 > upgrade fixes this issue? This is a known issue. There is no way for us to know what stored tables/columns you are using in your function. You might find that reconnecting to the database after the ALTER will fix the problem. We do cache function code, so reconnecting will erase that cache. If still have problems, please report the ALTER command and function that is causing the problem. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Wed, 2003-11-19 at 22:50, Bruce Momjian wrote:
> Don Sceifers wrote:
> > My company is fairly new at Postgresql, but we have hit a problem, where
> > we modify a table using ALTER, and our stored procedures stop working. We
> > have a grasp as to why this happens, but I was wondering if this v7.4
> > upgrade fixes this issue?
>
> This is a known issue. There is no way for us to know what stored
> tables/columns you are using in your function. You might find that
> reconnecting to the database after the ALTER will fix the problem. We
> do cache function code, so reconnecting will erase that cache.
>
Would it be worth putting in a partial implementation? I'm thinking
specifically about functions that return table types. Could they be
hooked up with dependency information and made to complain?
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> > Don Sceifers wrote:
> > > My company is fairly new at Postgresql, but we have hit a problem,
where
> > > we modify a table using ALTER, and our stored procedures stop working.
We
> > > have a grasp as to why this happens, but I was wondering if this v7.4
> > > upgrade fixes this issue?
Not sure if this applies with Postgres but did you also recompile the stored
procedures? If I remember correctly Sybase and Informix (?) required store
procedured to be recompiled after DDL to tables they used, and Oracle often
invalidates any functions, views, packages and procedures if they have any
relation to the modified table.
----- Original Message -----
From: "Robert Treat" <xzilla@users.sourceforge.net>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: <pgsql-hackers@postgresql.org>
Sent: Thursday, November 20, 2003 7:52 AM
Subject: Re: [HACKERS] question about fixes in v7.4...
> On Wed, 2003-11-19 at 22:50, Bruce Momjian wrote:
> > Don Sceifers wrote:
> > > My company is fairly new at Postgresql, but we have hit a problem,
where
> > > we modify a table using ALTER, and our stored procedures stop working.
We
> > > have a grasp as to why this happens, but I was wondering if this v7.4
> > > upgrade fixes this issue?
> >
> > This is a known issue. There is no way for us to know what stored
> > tables/columns you are using in your function. You might find that
> > reconnecting to the database after the ALTER will fix the problem. We
> > do cache function code, so reconnecting will erase that cache.
> >
>
> Would it be worth putting in a partial implementation? I'm thinking
> specifically about functions that return table types. Could they be
> hooked up with dependency information and made to complain?
>
> Robert Treat
> --
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>