Re: drop table bug and how to recover?

Hi sfpug folks,

We're running postgres 7.4.1. I think I may have hit a drop table
bug? I created a table, then dropped it, but now get "relation
already exists" when I try to recreate it. There wasn't anything
weird about the table AFAIK, except that the rowid was defined with
a default nextval of another table's sequence (hence my drop). I
did this directly in the SQL monitor.

I found a few old postings from v.6.5.2 about problems dropping and
recreating a table, either because of the "int8" type, or when
dropping within a transaction and then rolling back. The int8
problem was supposedly fixed upon quitting and re-entering psql.
That doesn't work for me. The transaction problem was supposedly
fixed upon going to the /data/base/database directory, creating
empty files, and then dropping the table for real. I would try
this, but I'm not sure what to create in my /data/base directory; it
contains a bunch of numeric subdirectories that don't mean much to me.

Thanks for any advice!

Myra

Myra,

> We're running postgres 7.4.1.

Well, first you should upgrade as soon as you can; 7.4.1 has a number of bugs
related to database recovery you don't want to live with. Please note that
there are some extra steps required for upgrade 7.4.1-->7.4.5.

> I think I may have hit a drop table
> bug? I created a table, then dropped it, but now get "relation
> already exists" when I try to recreate it. There wasn't anything
> weird about the table AFAIK, except that the rowid was defined with
> a default nextval of another table's sequence (hence my drop). I
> did this directly in the SQL monitor.

Mind cutting and pasting the whole session? This is an unlikely bug as you
have reported it, and it is more likely that something else is going on.

BTW, it is not at all necessary to drop a table to change a default.

--
Josh Berkus
Aglio Database Solutions
San Francisco

After you drop the table, did you get any warning or
error? And then did you check to see if the table
actually existed after dropping it? Try \d in psql
*and* try:
select relname from pg_class where relname='mytable';

I suspect an error slipped by without your notice.

elein

On Wed, Sep 08, 2004 at 09:58:53AM -0700, Myra Chachkin wrote:
> Hi sfpug folks,
>
> We're running postgres 7.4.1. I think I may have hit a drop table
> bug? I created a table, then dropped it, but now get "relation
> already exists" when I try to recreate it. There wasn't anything
> weird about the table AFAIK, except that the rowid was defined with
> a default nextval of another table's sequence (hence my drop). I
> did this directly in the SQL monitor.
>
> I found a few old postings from v.6.5.2 about problems dropping and
> recreating a table, either because of the "int8" type, or when
> dropping within a transaction and then rolling back. The int8
> problem was supposedly fixed upon quitting and re-entering psql.
> That doesn't work for me. The transaction problem was supposedly
> fixed upon going to the /data/base/database directory, creating
> empty files, and then dropping the table for real. I would try
> this, but I'm not sure what to create in my /data/base directory; it
> contains a bunch of numeric subdirectories that don't mean much to me.
>
> Thanks for any advice!
>
> Myra

Hi Josh et al,

On Wed, Sep 08, 2004 at 10:00:12AM -0700, Josh Berkus wrote:
> > We're running postgres 7.4.1.
>
> Well, first you should upgrade as soon as you can; 7.4.1 has a number of bugs
> related to database recovery you don't want to live with. Please note that
> there are some extra steps required for upgrade 7.4.1-->7.4.5.

I know...but thank you for the reminder.

> > I think I may have hit a drop table
> > bug? I created a table, then dropped it, but now get "relation
> > already exists" when I try to recreate it. There wasn't anything
> > weird about the table AFAIK, except that the rowid was defined with
> > a default nextval of another table's sequence (hence my drop). I
> > did this directly in the SQL monitor.
>
> Mind cutting and pasting the whole session? This is an unlikely bug as you
> have reported it, and it is more likely that something else is going on.

Unfortunately I don't have it any more, it was a few days ago. Is there
anything else I can do to troubleshoot?

Myra

Hi Elein,

It's very possible that an error slipped by me. :-(

I did try \d, and got "did not find any relation". And I
get 0 rows when I try the relname query.

Thanks,
Myra

On Wed, Sep 08, 2004 at 10:03:11AM -0700, elein wrote:
> After you drop the table, did you get any warning or
> error? And then did you check to see if the table
> actually existed after dropping it? Try \d in psql
> *and* try:
> select relname from pg_class where relname='mytable';
>
> I suspect an error slipped by without your notice.
>
> elein

Myra,

> Unfortunately I don't have it any more, it was a few days ago. Is there
> anything else I can do to troubleshoot?

Do it again? If you can't repeat it, forget about it?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Hi Josh,

Well, I need that specific tablename...sigh.

Myra

On Wed, Sep 08, 2004 at 10:18:47AM -0700, Josh Berkus wrote:
> > Unfortunately I don't have it any more, it was a few days ago. Is there
> > anything else I can do to troubleshoot?
>
> Do it again? If you can't repeat it, forget about it?

Myra,

> Well, I need that specific tablename...sigh.

I don't understand. If you can't repeat the error, what's stopping you from
creating the table?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Hi Josh,

I can't create the table, Postgres still thinks it exists. And I can't
drop it either.

Maybe I was unclear about recreating the error. I don't have my screen
buffer of what happened the first time I tried this. But the problem of
not being able to drop or recreate the table is extremely repeatable.
I'd like to find a way to recover from this situation so that I can use
the tablename.

Thanks,
Myra

On Wed, Sep 08, 2004 at 10:38:58AM -0700, Josh Berkus wrote:
> Myra,
>
> > Well, I need that specific tablename...sigh.
>
> I don't understand. If you can't repeat the error, what's stopping you from
> creating the table?
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco

Myra,

> Maybe I was unclear about recreating the error. I don't have my screen
> buffer of what happened the first time I tried this. But the problem of
> not being able to drop or recreate the table is extremely repeatable.
> I'd like to find a way to recover from this situation so that I can use
> the tablename.

If you can repeat it, please paste it here. Preferably both drop and create
statements.

--Josh

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Hi all,

Thanks for all your help, I really appreciate it.

I've found my problem (d'oh!) The fear of displaying my broken sql
caused me to look it over extra-carefully and find the error that I
hadn't spotted before. For the record, I had typo'd the table constraint
name to be the same as the table name, causing the "relation already
exists" error. No bug, just PEBCAK.

Myra

On Wed, Sep 08, 2004 at 11:55:42AM -0700, Josh Berkus wrote:
> If you can repeat it, please paste it here. Preferably both drop and create
> statements.

Myra,

> I've found my problem (d'oh!) The fear of displaying my broken sql
> caused me to look it over extra-carefully and find the error that I
> hadn't spotted before. For the record, I had typo'd the table constraint
> name to be the same as the table name, causing the "relation already
> exists" error. No bug, just PEBCAK.

Glad we could help .....

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco