Re: I saw a table that wasn't there

I'm not sure how this problem originated, but the fix you suggested did not
work. Not at first anyway
Here's what transpired:

[s4900tjb> sub]$ su
Password:
[root(at)rdtexas sub]# su - postgres
[postgres(at)rdtexas pgsql]$ pwd
/var/lib/pgsql
[postgres(at)rdtexas pgsql]$ cd base/infosystems
[postgres(at)rdtexas infosystems]$ pwd
/var/lib/pgsql/base/infosystems
[postgres(at)rdtexas infosystems]$ touch outreach
[postgres(at)rdtexas infosystems]$ ls -l o*
-rw------- 1 postgres postgres 8192 Jan 29 14:19
office_telecom_off_tel_id_seq
-rw------- 1 postgres postgres 32768 Nov 13 15:06 oip
-rw------- 1 postgres postgres 16384 Nov 13 15:05 oip_pkey
-rw------- 1 postgres postgres 8192 Apr 10 07:42 ot_lump_sum
-rw------- 1 postgres postgres 16384 Apr 10 07:19 ot_lump_sum_ot_id_key
-rw------- 1 postgres postgres 8192 May 2 06:45 ot_lump_sum_ot_id_seq
-rw------- 1 postgres postgres 0 Apr 9 16:01 ot_types
-rw------- 1 postgres postgres 16384 Apr 9 16:01 ot_types_ot_type_id_key

-rw------- 1 postgres postgres 8192 May 2 06:45 ot_types_ot_type_id_seq

-rw-rw-r-- 1 postgres postgres 0 May 4 11:56 outreach
[postgres(at)rdtexas infosystems]$ psql infosystems
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66]

type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: infosystems

infosystems=> drop table outreach;
ERROR: mdopen: couldn't open outreach_outreach_id_key: No such file or
directory

This was unexpected. Apprarently a key sequence was still floating around out
there. I tried the following:

infosystems=> drop sequence outreach_outreach_id_key;
ERROR: mdopen: couldn't open outreach_outreach_id_key: No such file or
directory
infosystems=> drop sequence outreach_outreach_id_seq;
ERROR: Relation 'outreach_outreach_id_seq' does not exist
infosystems=> create table outreach;
ERROR: parser: parse error at or near ";"

I'd been getting that sort of response, like there was an escape sequence
hiding out within the table name.

infosystems=> create table outreach_outreach_id_seq;
ERROR: parser: parse error at or near ";"
infosystems=> select * from outreach;
ERROR: mdopen: couldn't open outreach_outreach_id_key: No such file or
directory

I then tried the following;

touch employees_employee_id_seq

and then tried:

infosystems=> drop table outreach;
DROP

RAPTURE!!!

Apparently the sequence was the problem. I noted that long sequence names
don't appear in pgAccess and have since been shortening them. This was a
legacy problem.

I realize I need to upgrade to 7.1 and already have plans to within the next
week or two.

Can you recommend any more exhaustive reference on sequences than Momjians
book (I downloaded)? I'm having other problems with them. I'm going to send
email regarding this.

I really appreciate your help. Couldn't have done it without you.

Tom Lane wrote:

> "Tom.Bakken" <Tom(dot)Bakken(at)tx(dot)usda(dot)gov> writes:
> > My problem reminds me of the poem "I saw a man who wasn't there".
> > I created a table some time ago. When I try to drop it it says "no such
> > file or directory". When I run vacuum or copy out it also says it isn't
> > there. However, when I try to create a table with the same name it says
> > the table already exists.
>
> > I'm running Postgres 6.5.2 on Red Hat LINUX 6.1.
>
> I suppose you got into this state by trying to roll back a DROP TABLE.
> Doesn't work in pre-7.1 Postgres --- the physical file is already
> removed as soon as you execute the DROP command.
>
> (a) Create a physical file for the table ("touch
> $PGDATA/base/dbname/tablename") and then you'll be able to drop the
> thing.
>
> (b) Update to 7.1 ASAP.
>
> regards, tom lane

--
Tom Bakken
Information Resource Manager
USDA, Rural Development
101 South Main, Suite 102
Temple, TX 76501

Phone: 254-742-9726 Fax: 254-742-9709
Email: tom(dot)bakken(at)tx(dot)usda(dot)gov