Re: [HACKERS] Doc patch: Document names of automatically created constraints and indexes
On Sat, Nov 24, 2012 at 6:01 PM, Peter Eisentraut pete...@gmx.net wrote: On Wed, 2012-11-21 at 15:12 -0500, Robert Haas wrote: On Sat, Nov 17, 2012 at 1:22 AM, Peter Eisentraut pete...@gmx.net wrote: On Mon, 2012-11-12 at 11:42 -0600, Karl O. Pinc wrote: Could ALTER TABLE use an option to drop the primary key constraint? I needed to do that, found it was not obvious, and this lead me to try to improve things. That could be useful, I think. But it might open a can of worms. Would the new option be syntactic sugar around ALTER TABLE ... DROP CONSTRAINT put_the_name_of_the_primary_key_here? Yes, I think so. We already have DROP NOT NULL, which is a similar case (except, of course, that it was born more out of necessity, because not-null constraints don't have a name, but that's being worked on). Yeah. As usability issues go I think the lack of this syntax is a fairly minor one, but I confess to having wanted to be able to type ALTER TABLE foo DROP PRIMARY KEY more than once, so I wouldn't argue if someone wanted to go make that happen. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch: Document names of automatically created constraints and indexes
On Wed, 2012-11-21 at 15:12 -0500, Robert Haas wrote: On Sat, Nov 17, 2012 at 1:22 AM, Peter Eisentraut pete...@gmx.net wrote: On Mon, 2012-11-12 at 11:42 -0600, Karl O. Pinc wrote: Could ALTER TABLE use an option to drop the primary key constraint? I needed to do that, found it was not obvious, and this lead me to try to improve things. That could be useful, I think. But it might open a can of worms. Would the new option be syntactic sugar around ALTER TABLE ... DROP CONSTRAINT put_the_name_of_the_primary_key_here? Yes, I think so. We already have DROP NOT NULL, which is a similar case (except, of course, that it was born more out of necessity, because not-null constraints don't have a name, but that's being worked on). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch: Document names of automatically created constraints and indexes
On Wed, 2012-11-21 at 23:56 -0600, Karl O. Pinc wrote: No, wait. If constraint name_of_primary_key is an internal and is to change over time, how do you deal with dropping, now, a primary key constraint that was created, then, before some change to the internal name. And you wouldn't want to accidentally remove a user-created constraint that just happened to have the same name as the internal primary key constraint name, especially in the case where there's a real primary key constraint created under an old naming convention. Internally, dependencies are tracked by OID, not by name, so this isn't a problem. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch: Document names of automatically created constraints and indexes
On Sat, Nov 17, 2012 at 1:22 AM, Peter Eisentraut pete...@gmx.net wrote: On Mon, 2012-11-12 at 11:42 -0600, Karl O. Pinc wrote: Could ALTER TABLE use an option to drop the primary key constraint? I needed to do that, found it was not obvious, and this lead me to try to improve things. That could be useful, I think. But it might open a can of worms. Would the new option be syntactic sugar around ALTER TABLE ... DROP CONSTRAINT put_the_name_of_the_primary_key_here? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch: Document names of automatically created constraints and indexes
On 11/21/2012 02:12:26 PM, Robert Haas wrote: On Sat, Nov 17, 2012 at 1:22 AM, Peter Eisentraut pete...@gmx.net wrote: On Mon, 2012-11-12 at 11:42 -0600, Karl O. Pinc wrote: Could ALTER TABLE use an option to drop the primary key constraint? I needed to do that, found it was not obvious, and this lead me to try to improve things. That could be useful, I think. But it might open a can of worms. Would the new option be syntactic sugar around ALTER TABLE ... DROP CONSTRAINT put_the_name_of_the_primary_key_here? This sounds nice to me, but there's worms left over because the unique index created when PRIMARY KEY is specified would then remain. This the right behavior IMHO, and if everything is spelled out in the documentation no problems should arise. But the user deserves to know how to get rid of the unique index too, so the index's name would need to be documented. Since this is something of an internal matter (?) there might be another worm here. Regards, Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch: Document names of automatically created constraints and indexes
On 11/21/2012 10:00:11 PM, Karl O. Pinc wrote: On 11/21/2012 02:12:26 PM, Robert Haas wrote: On Sat, Nov 17, 2012 at 1:22 AM, Peter Eisentraut pete...@gmx.net wrote: On Mon, 2012-11-12 at 11:42 -0600, Karl O. Pinc wrote: Could ALTER TABLE use an option to drop the primary key constraint? I needed to do that, found it was not obvious, and this lead me to try to improve things. That could be useful, I think. But it might open a can of worms. Would the new option be syntactic sugar around ALTER TABLE ... DROP CONSTRAINT put_the_name_of_the_primary_key_here? This sounds nice to me, No, wait. If constraint name_of_primary_key is an internal and is to change over time, how do you deal with dropping, now, a primary key constraint that was created, then, before some change to the internal name. And you wouldn't want to accidentally remove a user-created constraint that just happened to have the same name as the internal primary key constraint name, especially in the case where there's a real primary key constraint created under an old naming convention. Changes to the primary key constraint naming convention would have to require a db dump/restore on pg upgrade to the new version, or something else that changes the primary key constraint names. (And then I'm not sure what would happen if a user was, before upgrading, using a constraint name that was the new default.) And the changing of the internal constraint name would have had to have always previously caused a name change in existing pg dbs or else dbs created long ago could today have primary key constraint names following old conventions, raising the concern at top. I'm sorry to waste your time if these are obvious issues. Regards, Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch: Document names of automatically created constraints and indexes
On Mon, 2012-11-12 at 11:42 -0600, Karl O. Pinc wrote: Could ALTER TABLE use an option to drop the primary key constraint? I needed to do that, found it was not obvious, and this lead me to try to improve things. That could be useful, I think. But it might open a can of worms. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Doc patch: Document names of automatically created constraints and indexes
Hi, Attached is a patch (against head) which documents the name of the constraint and index created when using PRIMARY KEY with CREATE TABLE, and the name of the index created when using UNIQUE. I haven't read all the docs recently but I don't believe this is presently documented. It's unclear to me that this is the right approach but perhaps this will start a discussion that finds the right approach. The big problem I see is that these are somewhat of an implementation internal while at the same time being something that the user might have to concern themselves with. First, the constraint and index names are in the namespace used by the user so there is potential for collision with user-defined constraints and indexes. Second, the only way (I know of) to remove primary-key-ness is to drop the primary key constraint, by name. This lead me right into another thought: It would be nice to have ALTER TABLE be able to drop the primary key constraint. (Then the user would not need to know the name of the constraint related to primary-key-ness.) However, it is probably more useful to be able to drop the constraint (and attendant foreign key meta-information) separately from the unique index associated with the primary key, if for no other reason than index re-creation can be expensive and missing indexes make bad things happen. This patch is the improvement I could come up with. Someone else can decide to commit or reject, I don't believe I can contribute much more on this at this time. Regards, Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 445ca40..da87574 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -486,6 +486,14 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI primary key constraint defined for the table. (Otherwise it would just be the same constraint listed twice.) /para + + para + The name of the index created when literalUNIQUE/literal is + used is literalreplaceable + class=PARAMETERtablename/replaceable_replaceable + class=PARAMETERcolname/replaceable_key/literal. + /para + /listitem /varlistentry @@ -514,6 +522,13 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI different from other sets of columns named by any unique constraint defined for the same table. /para + + para + The name of the primary key constraint, and the name of the + index which enforces uniqueness, is literalreplaceable + class=PARAMETERtablename/replaceable_pkey/literal. + /para + /listitem /varlistentry -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch: Document names of automatically created constraints and indexes
Karl O. Pinc k...@meme.com writes: Attached is a patch (against head) which documents the name of the constraint and index created when using PRIMARY KEY with CREATE TABLE, and the name of the index created when using UNIQUE. I haven't read all the docs recently but I don't believe this is presently documented. This is not actually correct: it ignores the corner cases where the name would be overlength or conflict with something else. Personally I don't think this should be documented, as it's an implementation detail that we've changed in the past and may change again. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch: Document names of automatically created constraints and indexes
On 11/12/2012 10:40:00 AM, Tom Lane wrote: Karl O. Pinc k...@meme.com writes: Attached is a patch (against head) which documents the name of the constraint and index created when using PRIMARY KEY with CREATE TABLE, and the name of the index created when using UNIQUE. Personally I don't think this should be documented, as it's an implementation detail that we've changed in the past and may change again. Ok. Could ALTER TABLE use an option to drop the primary key constraint? I needed to do that, found it was not obvious, and this lead me to try to improve things. Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers