Thread: 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 <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

"Karl O. Pinc" <kop@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

On 11/12/2012 10:40:00 AM, Tom Lane wrote:
> "Karl O. Pinc" <kop@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 <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."                -- Robert A. Heinlein

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.

On Sat, Nov 17, 2012 at 1:22 AM, Peter Eisentraut <peter_e@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

On 11/21/2012 02:12:26 PM, Robert Haas wrote:
> On Sat, Nov 17, 2012 at 1:22 AM, Peter Eisentraut <peter_e@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 <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."                -- Robert A. Heinlein

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 <peter_e@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 <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."                -- Robert A. Heinlein

On Wed, 2012-11-21 at 15:12 -0500, Robert Haas wrote:
> On Sat, Nov 17, 2012 at 1:22 AM, Peter Eisentraut <peter_e@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).

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.

On Sat, Nov 24, 2012 at 6:01 PM, Peter Eisentraut <peter_e@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 <peter_e@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