Re: ALTER SCHEMA problem

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(at)postgresql(dot)org(dot)

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs(at)postgresql(dot)org(dot)

If you not only found the problem but solved it and generated a patch
then e-mail it to pgsql-patches(at)postgresql(dot)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.

=========================================================================
=== POSTGRESQL BUG REPORT TEMPLATE
=========================================================================
===

Your name : Andreas Hinz
Your email address : news3(at)acci(dot)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
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:
----------------------------------------------------------------------

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.

--
Med venlig hilsen / Best regards / Mit freundlichen Grüssen

Andreas Hinz

Can someone comment on this?

---------------------------------------------------------------------------

Andreas Hinz wrote:
> 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(at)postgresql(dot)org(dot)
>
> To report any other bug, fill out the form below and e-mail it to
> pgsql-bugs(at)postgresql(dot)org(dot)
>
> If you not only found the problem but solved it and generated a patch
> then e-mail it to pgsql-patches(at)postgresql(dot)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.
>
> =========================================================================
> === POSTGRESQL BUG REPORT TEMPLATE
> =========================================================================
> ===
>
>
> Your name : Andreas Hinz
> Your email address : news3(at)acci(dot)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
> 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:
> ----------------------------------------------------------------------
>
> 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.
>
> --
> Med venlig hilsen / Best regards / Mit freundlichen Gr?ssen
>
> Andreas Hinz
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Can someone comment on this?

This is unfixable as long as nextval() and friends depend on string
parameters to represent table references. There are suggestions in
our archives about how we might move to a more Oracle-like syntax
(ie, table.nextval), which would expose the table reference in a way
that could track renamings. But no one seems to have gotten really
excited about making it happen.

regards, tom lane

I also find something magic when using sequence.
select nextval('seq_test');
and
select nextval('"seq_test"');
both refer to the same sequence: seq_test.

If I want to use a sequence with name: SEQ_TEST,
I have to write it as:
select nextval('"SEQ_TEST"');

So single quotes '...' here not like those in WHERE clause.

And I think ORACLE's syntax is better.

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
news:9363(dot)1061099071(at)sss(dot)pgh(dot)pa(dot)us(dot)(dot)(dot)
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Can someone comment on this?
>
> This is unfixable as long as nextval() and friends depend on string
> parameters to represent table references. There are suggestions in
> our archives about how we might move to a more Oracle-like syntax
> (ie, table.nextval), which would expose the table reference in a way
> that could track renamings. But no one seems to have gotten really
> excited about making it happen.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

On Tue, Aug 19, 2003 at 21:24:50 +0800,
Chris M <chris(at)none(dot)none> wrote:
> I also find something magic when using sequence.
> select nextval('seq_test');
> and
> select nextval('"seq_test"');
> both refer to the same sequence: seq_test.
>
> If I want to use a sequence with name: SEQ_TEST,
> I have to write it as:
> select nextval('"SEQ_TEST"');
>
> So single quotes '...' here not like those in WHERE clause.

That depends on your point of view. As far as what gets passed to the nextval
function 's work just like they do in the where clause. However the value
gets treated like the strings used to represent identifiers in SQL where
"s are used to quote identifier names.

> And I think ORACLE's syntax is better.

At some point someone will probably implement the Oracle syntax.