Re: [HACKERS] Problems with renaming a column
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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Problems with renaming a column
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 [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Problems with renaming a column
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 [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Problems with renaming a column
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] 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 [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Problems with renaming a column
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Problems with renaming a column
Rod Taylor [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Problems with renaming a column
On Mon, 2003-06-02 at 14:00, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] 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 [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
