Thread: bug in schema export?

i believe i found a bug

    psql> create table test_failure ( id integer primary key );
    psql> alter table test_failure alter id rename to id_new ; # not
relevant, just illustrating the reason how i got to step 2
    psql> alter table test_failure_pkey rename to test_failure_id_new_pkey;

    $ pg_dump --schema-only -ttest_failure -Utest > test.sql

the pkey in the schema is added as 'test_failure_pkey' , not
'test_failure_id_new_pkey';

it happens whether or not you use 'alter table' or 'alter index' on
the pkey name

Jonathan Vanasco <postgres@2xlp.com> writes:
> i believe i found a bug
>     psql> create table test_failure ( id integer primary key );
>     psql> alter table test_failure alter id rename to id_new ; # not
> relevant, just illustrating the reason how i got to step 2
>     psql> alter table test_failure_pkey rename to test_failure_id_new_pkey;

>     $ pg_dump --schema-only -ttest_failure -Utest > test.sql

> the pkey in the schema is added as 'test_failure_pkey' , not
> 'test_failure_id_new_pkey';

You didn't rename the constraint.  If we were going to do anything about
this, it'd be to forbid directly renaming indexes that're owned by
constraints.  However, since we don't currently have a RENAME CONSTRAINT
command, the effect of that would be that you couldn't do it at all ...

In the meantime you can probably do an UPDATE on pg_constraint if you
really care.

            regards, tom lane

On Apr 17, 2007, at 2:51 PM, Tom Lane wrote:

> Jonathan Vanasco <postgres@2xlp.com> writes:
>> i believe i found a bug
>>     psql> create table test_failure ( id integer primary key );
>>     psql> alter table test_failure alter id rename to id_new ; # not
>> relevant, just illustrating the reason how i got to step 2
>>     psql> alter table test_failure_pkey rename to
>> test_failure_id_new_pkey;
>
>>     $ pg_dump --schema-only -ttest_failure -Utest > test.sql
>
>> the pkey in the schema is added as 'test_failure_pkey' , not
>> 'test_failure_id_new_pkey';
>
> You didn't rename the constraint.  If we were going to do anything
> about
> this, it'd be to forbid directly renaming indexes that're owned by
> constraints.  However, since we don't currently have a RENAME
> CONSTRAINT
> command, the effect of that would be that you couldn't do it at
> all ...
>
> In the meantime you can probably do an UPDATE on pg_constraint if you
> really care.


Extremely annoying.

In the example above,
    \d test_failure

will not show a separate constraint named by the old value.  it only
shows the current name of the index.

Either renaming primary keys should be disabled, or that constraint
forced to be visible.

Under the current system, there is no intuitive way for someone to be
aware of an old constraint  on a renamed index like this.

I discovered this while trying to re-merge 2 databases that were
split onto a new bigger cluster and having them complain about
conflicting constraints.


// Jonathan Vanasco

| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
| SyndiClick.com
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
|      FindMeOn.com - The cure for Multiple Web Personality Disorder
|      Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -
|      RoadSound.com - Tools For Bands, Stuff For Fans
|      Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -