Обсуждение: Funky template1 problem?

Hi,

I have a bunch of small dbs (levan, pictures, and a couple of others).

I have had these db's over many upgrades and I have just noticed that
template1 seems to be a bit munged...

testuser=> \c template1
You are now connected to database "template1".
template1=> \d
              List of relations
Schema |      Name      |   Type   | Owner
--------+----------------+----------+-------
public | lo_shadow      | table    | levan
public | my_description | table    | levan
public | person         | table    | levan
public | person_id_seq  | sequence | levan
public | pics           | table    | levan
public | pics_ident_seq | sequence | levan
(6 rows)

This is the same as...
template1=> \c pictures
You are now connected to database "pictures".
pictures=> \d
              List of relations
Schema |      Name      |   Type   | Owner
--------+----------------+----------+-------
public | lo_shadow      | table    | levan
public | my_description | table    | levan
public | person         | table    | levan
public | person_id_seq  | sequence | levan
public | pics           | table    | levan
public | pics_ident_seq | sequence | levan
(6 rows)

I found this out when I tried to create a new user
testuser for testing a totally none privileged user.
When I recreate the testuser db using template1
I found the tables from the picture db were in
the newly created db.

Is it safe to simple drop the db's from the template1
db?  ( I have no idea of how the tables became part
of template1.)

Jerry

"Jerry LeVan" <jerry.levan@eku.edu> wrote in message
news:DF90ADCC-643E-4EA7-AB6C-423815301EF5@eku.edu...
> Hi,
>
> I have a bunch of small dbs (levan, pictures, and a couple of others).
>
> I have had these db's over many upgrades and I have just noticed that
> template1 seems to be a bit munged...
>
> testuser=> \c template1
> You are now connected to database "template1".
> template1=> \d
>              List of relations
> Schema |      Name      |   Type   | Owner
> --------+----------------+----------+-------
> public | lo_shadow      | table    | levan
> public | my_description | table    | levan
> public | person         | table    | levan
> public | person_id_seq  | sequence | levan
> public | pics           | table    | levan
> public | pics_ident_seq | sequence | levan
> (6 rows)
>

Can you show the relfilenode (the physical file name of the relation) of
these tables like this:

select relname, relfilenode from pg_class, pg_namespace where relnamespace =
pg_namespace.oid and nspname = 'public';

And check if they are really in the template1?

Regards,
Qingqing

"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
> "Jerry LeVan" <jerry.levan@eku.edu> wrote in message
>> I have had these db's over many upgrades and I have just noticed that
>> template1 seems to be a bit munged...

> ... And check if they are really in the template1?

I'm sure they are.  Probably Jerry just mistakenly created the tables
while connected to template1 at some point.  I see no evidence here
of anything but pilot error.

            regards, tom lane

On Wed, 21 Dec 2005, Tom Lane wrote:

> > "Jerry LeVan" <jerry.levan@eku.edu> wrote in message
> >> I have had these db's over many upgrades and I have just noticed that
> >> template1 seems to be a bit munged...
>
> I'm sure they are.

Oh right -- since CREATE DATABASE just copied the whole directory.

Regards,
Qingqing

Jerry LeVan wrote:
> Hi,
>
> I have a bunch of small dbs (levan, pictures, and a couple of others).
>
> I have had these db's over many upgrades and I have just noticed that
> template1 seems to be a bit munged...
[snip]
> I found this out when I tried to create a new user
> testuser for testing a totally none privileged user.
> When I recreate the testuser db using template1
> I found the tables from the picture db were in
> the newly created db.

Yep - that's a feature. It applies to functions etc. too.

> Is it safe to simple drop the db's from the template1
> db?  ( I have no idea of how the tables became part
> of template1.)

Deleting the tables is fine. You should be able to drop the template1
database altogether and recreate it using template0 as it's template.
That's why there's two of them, for exactly this circumstance. Have a
look in the mailing-list archives for details.

--
   Richard Huxton
   Archonet Ltd