change of table name - any help

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.

Regards, Christoph

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"

I don't know what is the problem. Somebody in this esteemed group suggested
me to write to Mr. Tom lane (since he understand the catalog very well) and
I did that and waiting for his response on this issue.
Rgds
--Ranga

-----Original Message-----
From: ch@rodos.fzk.de [mailto:ch@rodos.fzk.de]
Sent: Friday, September 19, 2003 2:37 AM
To: pgsql-hackers@postgresql.org; ranga@dvdstation.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.

Regards, Christoph

[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@rodos.fzk.de [mailto:ch@rodos.fzk.de]
Sent: Friday, September 19, 2003 2:37 AM
To: pgsql-hackers@postgresql.org; ranga@dvdstation.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