Thread: Problems with renaming a column
As you can see below, after a rename the check constraint still refers to 'col' and not 'newname' as pg_constraint.consrc is not updated. Of course, this functions fine (conbin is still valid) but when it comes time to do a pg_dump, the database is dumped using the old column name. It seems this is a problem in 7.3 as well. I believe the solution is to outright remove consrc, and enable the interface to request a text version of conbin on the fly. test=# CREATE TABLE test (col integer check(col > 2)); CREATE TABLE test=# test=# ALTER TABLE test RENAME COLUMN col TO newname; ALTER TABLE test=# test=# \d test Table "public.test"Column | Type | Modifiers ---------+---------+-----------newname | integer | Check Constraints: "test_col" CHECK (col > 2) -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Rod Taylor <rbt@rbt.ca> writes:
> As you can see below, after a rename the check constraint still refers
> to 'col' and not 'newname' as pg_constraint.consrc is not updated.
The same issue has always existed with regard to pg_attrdef.adsrc.
pg_dump ought to be using the binary column not the source column, just
as it does for default expressions.
> It seems this is a problem in 7.3 as well. I believe the solution is to
> outright remove consrc, and enable the interface to request a text
> version of conbin on the fly.
I do not think we need to remove the column.
regards, tom lane
On Mon, 2003-06-02 at 14:00, Tom Lane wrote: > Rod Taylor <rbt@rbt.ca> writes: > > As you can see below, after a rename the check constraint still refers > > to 'col' and not 'newname' as pg_constraint.consrc is not updated. > > The same issue has always existed with regard to pg_attrdef.adsrc. > pg_dump ought to be using the binary column not the source column, just > as it does for default expressions. Figured that, and I'll make the change. > > It seems this is a problem in 7.3 as well. I believe the solution is to > > outright remove consrc, and enable the interface to request a text > > version of conbin on the fly. > > I do not think we need to remove the column. Is it ok that the consrc column is not synch'd with conbin? What does it provide if it doesn't match? At very least we should be discouraging it's use so the pgadmin, phppgadmin, etc. folks know not to be using it. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Rod Taylor <rbt@rbt.ca> writes:
>> I do not think we need to remove the column.
> Is it ok that the consrc column is not synch'd with conbin? What does
> it provide if it doesn't match?
Documentation of the original form of the constraint, perhaps?
> At very least we should be discouraging it's use so the pgadmin,
> phppgadmin, etc. folks know not to be using it.
It would be a good idea for the system catalog descriptions to note that
decompiling the binary form is better for tools to do. When you're just
scavenging through the catalogs by hand, though, I think the source
forms are convenient to have.
The binary forms have their own disadvantages, btw, although I think the
addition of dependency tracking has mitigated the worst ones. You may
care to consult the archives for prior discussions of adsrc vs. adbin.
regards, tom lane
> Which ones are missing, and should we really be looking at creating a > pg_definition_schema instead? Missing: Database, schema, table, domain, cast, conversion, function... Maybe a definition schema might be better.....dunno...it would need to use the pg_get_*def functions anyway methinks. Chris
On Wed, 2003-06-04 at 22:28, Christopher Kings-Lynne wrote: > > Which ones are missing, and should we really be looking at creating a > > pg_definition_schema instead? > > Missing: > > Database, schema, table, domain, cast, conversion, function... > > Maybe a definition schema might be better.....dunno...it would need to use > the pg_get_*def functions anyway methinks. As an interface writer, do you prefer dealing with functions like pg_get_constraintdef() or a view like the information schema provides? The function doesn't easily allow determination of items such as the ON UPDATE or ON DELETE type (statement parsing is required), but the information schema gives the information in a segregated manner. The pg_get_*def() functions seem to have been created primarily for psql and pg_dump. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
> > As an interface writer, do you prefer dealing with functions like > > pg_get_constraintdef() or a view like the information schema provides? > > > > I would think it is easier to get the information from the information > schema. That's most like what we're doing now getting the information > from the pg_* tables and istm it's easier to browse the information The information schema is not appropriate for the task, but an information like schema would probably be best. Won't happen for this release, but I'm willing to take a look at it for the next. > (He's on holiday for the next few days btw which is why I'm chiming in) I see.. Thanks. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
On Thu, 2003-06-05 at 11:11, Rod Taylor wrote:
> On Wed, 2003-06-04 at 22:28, Christopher Kings-Lynne wrote:
> > > Which ones are missing, and should we really be looking at creating
> a
> > > pg_definition_schema instead?
> >
> > Missing:
> >
> > Database, schema, table, domain, cast, conversion, function...
> >
> > Maybe a definition schema might be better.....dunno...it would need to
> use
> > the pg_get_*def functions anyway methinks.
yeah, i would think it would, but isn't the point of the information
schema to help hide the back end tech?
>
> As an interface writer, do you prefer dealing with functions like
> pg_get_constraintdef() or a view like the information schema provides?
>
I would think it is easier to get the information from the information
schema. That's most like what we're doing now getting the information
from the pg_* tables and istm it's easier to browse the information
schema than dig through function definitions. To be fair Chris tends to
hack on pg_dump at a much deeper level than I on either pg_dump or psql,
so he might be more familiar with the functions and have a different
viewpoint.
> The function doesn't easily allow determination of items such as the ON
> UPDATE or ON DELETE type (statement parsing is required), but the
> information schema gives the information in a segregated manner.
>
Well, the biggest pain in the arse I had to deal with in phpPgAdmin was
the handling of permissions, specifically due to having to parse through
the relacl information. I was so glad when Chris fixed up my half
working implementation.
> The pg_get_*def() functions seem to have been created primarily for psql
> and pg_dump.
yeah. again Chris tends to hack on pg_dump so he might see it
differently than I (and I haven't looked at psql in months).
(He's on holiday for the next few days btw which is why I'm chiming in)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL