Skip site navigation (1) Skip section navigation (2)

Re: BUG #5688: ALTER TABLE ALTER col TYPE newtype fails if col is named in an UPDATE OF col trigger

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Daniel Grace" <dgrace(at)wingsnw(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5688: ALTER TABLE ALTER col TYPE newtype fails if col is named in an UPDATE OF col trigger
Date: 2010-10-02 00:07:20
Message-ID: 3930.1285978040@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Daniel Grace" <dgrace(at)wingsnw(dot)com> writes:
> Description:        ALTER TABLE ALTER col TYPE newtype fails if col is named
> in an UPDATE OF col trigger

Hmm, definitely a missed case (and defensive programming saves the day
again).

There are actually two cases as of 9.0 where a trigger could depend on a
column:
	* UPDATE OF said column, as in your example
	* column is used in trigger's WHEN clause

In the first case we could just allow the ALTER to proceed, but in the
second case we'd have to adjust the WHEN expression for the column's new
type, which is something there's not code for now.  And it doesn't look
like we can easily tell which case applies.  I think what we'll have to
do here is just throw a FEATURE_NOT_SUPPORTED error, and maybe add a
TODO item to come back to improve that someday.  (Since you can just
drop the trigger and re-add it after the ALTER, a nicer fix doesn't seem
like a high priority IMO.)

Thanks for the report!  It's too late for 9.0.1 but we'll do something
about this for 9.0.2.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Craig RingerDate: 2010-10-02 02:02:03
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)
Previous:From: stagirusDate: 2010-10-01 22:17:19
Subject: Re: [BUGS] Mapping Hibernate boolean to smallint(Postgresql)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group