ALTER SCHEMA problem - Mailing list pgsql-bugs

If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to pgsql-ports@postgresql.org.

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs@postgresql.org.

If you not only found the problem but solved it and generated a patch
then e-mail it to pgsql-patches@postgresql.org instead.  Please use the
command "diff -c" to generate the patch.

You may also enter a bug report at http://www.postgresql.org/ instead of
e-mail-ing this form.

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D                        POSTGRESQL BUG REPORT TEMPLATE
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
=3D=3D=3D


Your name        : Andreas Hinz=09
Your email address    : news3@acci.dk


System Configuration
---------------------
  Architecture (example: Intel Pentium)      : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)     : Linux 2.4.21 ELF

  PostgreSQL version (example: PostgreSQL-7.3):   PostgreSQL-7.4beta1

  Compiler used (example:  gcc 2.95.2)        : gcc 3.2.3


Please enter a FULL description of your problem:
------------------------------------------------

Hi,
I am not absolutly sure this is a bug, but consider this:

I am about to create a database with 5 schemas each containing about 70
tables. Importing data via "psql <database> -f <file>.

After import I rename the schema "public" to eg. "base1", create a=20
new schema "public", import the next database etc.

Now the problem is I yse the datatype "serial" which creates then
constraint "default nextval('public.abc_sew'::test)".

When renaming the schema from "public" to "base1" all indexes and
seqenses are renames correct, but not the above "public." in the
constraint.


Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:=20
----------------------------------------------------------------------

createdb test
psql test
CREATE TABLE ta1 (f1 serial, f2 integer);
ALTER SCHEMA public RENAME TO base1;
\d base1.*


If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------


Only by manual "ALTER TABLE ta1 ALTER f1 SET DEFAULT  etc.

But doing this for 5 schemas each having 70 tables is somewhat stupud.

Even via a seperate file with all the "ALTER" is no solution as this is
an unfineshed project with frequent changes on the tables and thus
possible changes in this file.


A posibility to select a default schema with eg. "SET" on import would be
a really nice feature:

SET DEFAULT SCHEMA base1;

CREATE TABLE ....

COPY FROM stdin ....

etc.

--=20
Med venlig hilsen / Best regards / Mit freundlichen Gr=FCssen

Andreas Hinz