PostgreSQL Discussion - Funky template1 problem?

Author

Funky template1 problem?

Jerry LeVan

2005-12-21, 1:23 pm

Qingqing Zhou

2005-12-21, 8:25 pm

"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

Tom Lane

2005-12-21, 8:25 pm

"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
> "Jerry LeVan" <jerry.levan@eku.edu> wrote in message
[color=darkred]
> ... 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Qingqing Zhou

2005-12-21, 8:25 pm

On Wed, 21 Dec 2005, Tom Lane wrote:

>
> I'm sure they are.

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

Regards,
Qingqing

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Richard Huxton

2005-12-22, 3:23 am

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...
& #91;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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links