Renaming constraints

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:

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

2009/10/8 Tom Lane <tgl@sss.pgh.pa.us>

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

If it's only a matter of the entry in the pg_constraint catalog being
changed, couldn't a more intuitive SQL-style instruction be included in a
future release?

Such as:

ALTER TABLE my_table RENAME CONSTRAINT my_constraint TO your_constraint;

For backwards compatibility, omission of a keyword after "RENAME" could just
default to meaning "COLUMN" like it currently does.

Thom Brown

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: