Thread: Renaming constraints

How do I rename constraints? Renaming columns will not rename constraints.

Andi

On Thu, Oct 08, 2009 at 08:24:06PM +0200, Andreas Kalsch wrote:
> How do I rename constraints? Renaming columns will not rename constraints.

BEGIN;
ALTER TABLE foo DROP CONSTRAINT bar;
ALTER TABLE foo ADD CONSTRAINT bluf...;
COMMIT;

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

But with this operation you will recreate the whole index. - I have
found out, that the name of the constraint's index is the same as the
constraint, so that I can simply rename the index.

My problem is that I want to "hook up" a new version of existing tables
into my production system.

1) While recomputing the content of the new tables in the background,
they all have a prefix, in my case '_'. So while I am computing, the
application is still using the old tables.
2) Then I will give the old tables a prefix, e.g. '__'
3) Then I remove the prefix of the new tables.
4) Then I can drop the old tables without blocking the application.

Steps 2) and 3) are executed in one transaction and include just
renaming, no computing, so it will not block the application. In
opposite, 1) and 4) take much more time.

Example:

"BEGIN;

ALTER TABLE area RENAME TO __area;
    ALTER INDEX area_pkey RENAME TO __area_pkey;
...


ALTER TABLE _area RENAME TO area;
    ALTER INDEX _area_pkey RENAME TO area_pkey;
...

COMMIT;

DROP TABLE
    __area,
    __area_name,
    __area_area,
    __area_surface,
    __area_point;"

Do you know a better solution?


David Fetter schrieb:
> On Thu, Oct 08, 2009 at 08:24:06PM +0200, Andreas Kalsch wrote:
>
>> How do I rename constraints? Renaming columns will not rename constraints.
>>
>
> BEGIN;
> ALTER TABLE foo DROP CONSTRAINT bar;
> ALTER TABLE foo ADD CONSTRAINT bluf...;
> COMMIT;
>
> Cheers,
> David.
>

Andreas Kalsch <andreaskalsch@gmx.de> writes:
> But with this operation you will recreate the whole index. - I have
> found out, that the name of the constraint's index is the same as the
> constraint, so that I can simply rename the index.

You'd probably better rename the constraint too to avoid confusion.
Failing anything else, there's always direct UPDATE of the pg_constraint
catalog.

            regards, tom lane

I am currently trying to solve the problem by using different schemas,
but then I have to consider all the GIS stuff. Putting the same things
in different schemas is no problem, but comparing the same type defined
in different schemas will lead to confusion, because Postgres treats
them as different types. So what about moving them to pg_catalog:


SET search_path TO pg_catalog;

CREATE LANGUAGE plpgsql;
CREATE LANGUAGE plpythonu;
\i /usr/share/postgresql-8.3-postgis/lwpostgis.sql
\i /usr/share/postgresql-8.3-postgis/spatial_ref_sys.sql

psql:/usr/share/postgresql-8.3-postgis/lwpostgis.sql:2222: ERROR:
permission denied to create "pg_catalog.geometry_dump"
DETAIL:  System catalog modifications are currently disallowed.
psql:/usr/share/postgresql-8.3-postgis/lwpostgis.sql:2228: ERROR:
current transaction is aborted, commands ignored until end of
transaction block
...


How can I enable system catalog modifications?

Thanks for your help, so far.

Andi


Tom Lane schrieb:
> Andreas Kalsch <andreaskalsch@gmx.de> writes:
>
>> But with this operation you will recreate the whole index. - I have
>> found out, that the name of the constraint's index is the same as the
>> constraint, so that I can simply rename the index.
>>
>
> You'd probably better rename the constraint too to avoid confusion.
> Failing anything else, there's always direct UPDATE of the pg_constraint
> catalog.
>
>             regards, tom lane
>
>
>