Thread: Problems with renaming a column

Problems with renaming a column

From
Rod Taylor
Date:
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

Re: Problems with renaming a column

From
Tom Lane
Date:
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


Re: Problems with renaming a column

From
Rod Taylor
Date:
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

Re: Problems with renaming a column

From
Tom Lane
Date:
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


Re: Problems with renaming a column

From
"Christopher Kings-Lynne"
Date:
> 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



Re: Problems with renaming a column

From
Rod Taylor
Date:
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

Re: Problems with renaming a column

From
Rod Taylor
Date:
> > 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

Re: Problems with renaming a column

From
Robert Treat
Date:
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