Re: [HACKERS] change of table name - any help

[I'm not convinced this is a -hackers issue so have cross posted to -general in
the expectation followups will go there]

I also didn't feel there was much I could cut from the earlier posts without
losing relevent info, so I didn't. Sorry.

On Fri, 19 Sep 2003, chakkara rangarajan wrote:

> Christoph,
> Thx for your response. We didn't change the search_parth variable. Moreover,
> I tried all combination of drop statements like
>
> Drop table table_name
> Drop table owner.table_name
> Drop table "table_name"
> Drop table "owner.table_name"
>
>...
>
> -----Original Message-----
> From: ch(at)rodos(dot)fzk(dot)de [mailto:ch(at)rodos(dot)fzk(dot)de]
> Sent: Friday, September 19, 2003 2:37 AM
> To: pgsql-hackers(at)postgresql(dot)org; ranga(at)dvdstation(dot)com
>
> > We have a development server running
> >
> > OS - Linux development-server 2.4.20-openmosix-r4 #1 SMP Mon May 19
> 02:32:52
> > PDT 2003 i686 Intel(R) Xeon(TM) CPU 2.40GHz GenuineIntel GNU/Linux
> >
> > Database - PostgreSQL 7.3 on i686-pc-linux-gnu, compiled by GCC gcc
> (GCC)
> > 3.2.1
> >
> > We have a table ctcert_name under postgres DB(postgres schema and
> postgres
> > user is the owner). Suddenly, this object started missing from the DB
> (I am
> > the only
> >
> > Person who connects to that server and did not drop/renamed it). When
> I
> > tried to recreate the same table, the system threw me back an error,
> saying
> > that "postgres.ctcert_name" already exists. I am neither able to drop
> or
> > rename the table.
> >
> > checked the DB logs and there is no drop/rename table statement in
> that.
> >
> > I have the transaction logs, but not able to read, as they are not in
> the
> > human readable format.
> >
> > How can I decipher from the txn logs, if it captures the change
> management.
> >
> > Can somebody please tell me, what cud have gone wrong and is the error
> is
> > reproduceable? What is the solution for this kind of problem.
> >
> Did you change the SEARCH_PATH variable?
> Did I get this right:
> You cannot
> DROP TABLE postgres.ctcert_name ;
> Mind, I left off the enclosing quotes.
> And you cannot
> CREATE TABLE postgres.ctcert_name( ... ) ;
>
> My suspicion is you are using these quotes and you shouldn't.
>

This last would try a create a table called owner.table_name in the current
schema. In fact given that I'm starting from the point of already having a
table named "test" the following shows this as well as a few other points.

test=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------------+-------+-------
...
public | test | table | test
(13 rows)

test=# create table test (a int);
ERROR: Relation 'test' already exists
test=# create table public.test (a int);
ERROR: Relation 'test' already exists
test=# create table "public.test" (a int);
CREATE TABLE
test=# create table "public.test" (a int);
ERROR: Relation 'public.test' already exists
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------------+-------+-------
...
public | public.test | table | test
...
public | test | table | test
(14 rows)

test=#

Also the "all lower case variant" shouldn't make any difference to the unquoted
names since unquoted names get folded to lower case. What would make a
difference is if there was quoted upper case characters used one time but not
another.

However, I suspect I'm telling you thing you already know.

Presumably you've tried using \dt in psql and the table isn't listed but others
in the same schema are?

What about the query:

select c.oid, c.relname, c.relnamespace from pg_class c where relname ilike
'%ctcert%';

followed by:

select oid,* from pg_namespace where oid = ?

where the ? in the second is the relnamespace value in results from the first
query.

It's difficult to see why there would be a pg_class entry with the same name as
you are trying and in the same schema but the relname, relnamespace combination
must be unique. Trying to create a new table that would violate that unique
constraint I imagine would give you that error message.

Bearing in mind the lack of drop table statements logged you should also check
for updates/delete from pg_class. Although because the system thinks there is a
conflict when creating the new table I'd be slightly worried that something's
gone horribly wrong somewhere but then I don't know what data in the system
tables would, validly, make it look like the object existed at the same time as
it didn't

--
Nigel Andrews