Postgres Pro
Downloads
Home   >   mailing lists

Thread: Can't drop table (repost)

Can't drop table (repost)

From
Jeremy Buchmann
Date:
(This is a repost as I haven't received any answers yet :( )

Hi all,

I have a weird situation here...I'm trying to drop a table, but it tells me
that "Relation 'xxxxxxx' inherits 'mytable'".  So I renamed mytable to
myoldtable and created a new mytable.  Now, the new mytable is missing the
fields I was trying to add, and when I try to drop it, it says "Relation
'xxxxxxx' inherits 'mytable'".  And when I try to drop myoldtable, it says
"Relation 'xxxxxxx' inherits 'myoldtable'".  How do I get out of this mess?

Also, I noticed some tables named like pg_temp.1970.0 and such.  They seem
to contain some junk data from queries or something, but it won't let me
delete them...how can I get rid of them?

I'm using pgsql 6.5.3 on Linux 2.2

Thanks,

-- Jeremy  [jeremy@wellsgaming.com]

Re: Can't drop table (repost)

From
Tom Lane
Date:
Jeremy Buchmann <jeremy@wellsgaming.com> writes:
> I have a weird situation here...I'm trying to drop a table, but it tells me
> that "Relation 'xxxxxxx' inherits 'mytable'".  So I renamed mytable to
> myoldtable and created a new mytable.  Now, the new mytable is missing the
> fields I was trying to add, and when I try to drop it, it says "Relation
> 'xxxxxxx' inherits 'mytable'".  And when I try to drop myoldtable, it says
> "Relation 'xxxxxxx' inherits 'myoldtable'".  How do I get out of this mess?

You can't drop a table that has inheritance children --- you have to
drop the children first.  And renaming tables doesn't affect the
inheritance relationships.

> Also, I noticed some tables named like pg_temp.1970.0 and such.  They seem
> to contain some junk data from queries or something, but it won't let me
> delete them...how can I get rid of them?

Those are temp tables; if a backend crashed while it had a temp table
created, then you'd see the leftover tables.  Offhand I think you won't
be allowed to delete 'em unless you restart the postmaster with the
allow-system-table-mods switch.

> I'm using pgsql 6.5.3 on Linux 2.2

Time to upgrade...

            regards, tom lane

Reason to not upgrade from 6.5 to 7.0 or above

From
bangh
Date:
>
> > I'm using pgsql 6.5.3 on Linux 2.2
>
> Time to upgrade...
>
>                         regards, tom lane
>

I am using 6.5 too. The reason is some data type are gone. e.g. datetime. If do
so, I have to modify my application.

Ciao,
Banghe

Re: Reason to not upgrade from 6.5 to 7.0 or above

From
Tom Lane
Date:
bangh <banghe@baileylink.net> writes:
> I am using 6.5 too. The reason is some data type are
> gone. e.g. datetime. If do so, I have to modify my application.

??  This still works:

regression=# create table foo (f1 datetime);
CREATE

            regards, tom lane

Re: Can't drop table (repost)

From
Jeremy Buchmann
Date:
> Jeremy Buchmann <jeremy@wellsgaming.com> writes:
>> I have a weird situation here...I'm trying to drop a table, but it tells me
>> that "Relation 'xxxxxxx' inherits 'mytable'".  So I renamed mytable to
>> myoldtable and created a new mytable.  Now, the new mytable is missing the
>> fields I was trying to add, and when I try to drop it, it says "Relation
>> 'xxxxxxx' inherits 'mytable'".  And when I try to drop myoldtable, it says
>> "Relation 'xxxxxxx' inherits 'myoldtable'".  How do I get out of this mess?
>
> You can't drop a table that has inheritance children --- you have to
> drop the children first.  And renaming tables doesn't affect the
> inheritance relationships.

Okay great, but how do I know what tables I need to drop if it just gives me
a number?  That 'xxxxxxx' is just some number like '8892659'.  Where is the
mapping from number->name?  And do you think this inheritance "feature"
could be mentioned in the user guide?

>> Also, I noticed some tables named like pg_temp.1970.0 and such.  They seem
>> to contain some junk data from queries or something, but it won't let me
>> delete them...how can I get rid of them?
>
> Those are temp tables; if a backend crashed while it had a temp table
> created, then you'd see the leftover tables.  Offhand I think you won't
> be allowed to delete 'em unless you restart the postmaster with the
> allow-system-table-mods switch.

It is possible that these temp tables could be inherited from the "mytable"
mentioned above...if I removed them with the allow-system-table-mods, would
that fix the pg_inherits table so that mytable could be removed?

--Jeremy

Re: Can't drop table (repost)

From
Tom Lane
Date:
Jeremy Buchmann <jeremy@wellsgaming.com> writes:
> Okay great, but how do I know what tables I need to drop if it just gives me
> a number?  That 'xxxxxxx' is just some number like '8892659'.  Where is the
> mapping from number->name?

select relname from pg_class where oid = '8892659';

More recent versions do provide the relation name instead of number
in that error message, btw.

            regards, tom lane

Re: Reason to not upgrade from 6.5 to 7.0 or above

From
Joel Burton
Date:
On Sat, 17 Feb 2001, bangh wrote:

> > > I'm using pgsql 6.5.3 on Linux 2.2
> >
> > Time to upgrade...
> >
> >                         regards, tom lane
> >
>
> I am using 6.5 too. The reason is some data type are gone. e.g. datetime. If do
> so, I have to modify my application.

Is there anything that datetime did that timestamp doesn't?

Could you

   pg_dump | sed | psql

to change the datetimes to timestamp?

--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

Re: Can't drop table (repost)

From
Jeremy Buchmann
Date:
> Jeremy Buchmann <jeremy@wellsgaming.com> writes:
>> Okay great, but how do I know what tables I need to drop if it just gives me
>> a number?  That 'xxxxxxx' is just some number like '8892659'.  Where is the
>> mapping from number->name?
>
> select relname from pg_class where oid = '8892659';
>
> More recent versions do provide the relation name instead of number
> in that error message, btw.

Okay,

cpcs=> select relname from pg_class where oid = '8892659';
relname
---------------
pg_temp.29453.1
(1 row)

So it is one of those temp tables...now if I delete those tables with the
allow-system-table-mods switch, will that still correct the pg_inherits
table so that the dependency no longer exists?  Or can I remove that by
hand, too?

Thanks,

-- Jeremy  [jeremy@wellsgaming.com]

Re: Can't drop table (repost)

From
Tom Lane
Date:
Jeremy Buchmann <jeremy@wellsgaming.com> writes:
> So it is one of those temp tables...now if I delete those tables with the
> allow-system-table-mods switch, will that still correct the pg_inherits
> table so that the dependency no longer exists?

Yes, you should be okay once you've dropped the leftover temp tables.

BTW, you should consider updating to a newer release in hopes of getting
rid of the bug that's allowing the temp tables to stick around...

            regards, tom lane

Re: Reason to not upgrade from 6.5 to 7.0 or above

From
Anuradha Ratnaweera
Date:

On Wed, 25 Apr 2001, Joel Burton wrote:

> Is there anything that datetime did that timestamp doesn't?

datetime is provided to ensure compatibility with older releases, although
they are not guranteed to exist in future releases (chapter 3.4 users
guide)

> Could you
>
>    pg_dump | sed | psql
>
> to change the datetimes to timestamp?

No. pg_dump will read from the database while psql will modify it and
there can be conflicts. It is safe to

pg_dumpall -c | sed > tmpfile
psql -d template1 -f tmpfile

Anuradha

datetimeField = datetimeField - '30minutes'::timespan in psql 7.0 or above

From
bangh
Date:
Actually, it is not so simple.

How can you do this in psql 7.0 or above?
datetimeField = datetimeField - '30minutes'::timespan ( in psql 6.3, 6.5 you can),

while if you use type timestamp, instead of datetime
timestampField = timestampField - '30minutes'::interval
 you will get error msg.:
No such function 'datetime_stamp' with the specified attributes

--
Banghe



Joel Burton wrote:

>
> Is there anything that datetime did that timestamp doesn't?
>
> Could you
>
>    pg_dump | sed | psql
>
> to change the datetimes to timestamp?
>
> --
> Joel Burton   <jburton@scw.org>
> Director of Information Systems, Support Center of Washington

Re: datetimeField = datetimeField - '30minutes'::timespan in psql 7.0 or above

From
Joel Burton
Date:
On Fri, 2 Mar 2001, bangh wrote:

> Actually, it is not so simple.
>
> How can you do this in psql 7.0 or above?
> datetimeField = datetimeField - '30minutes'::timespan ( in psql 6.3, 6.5 you can),
>
> while if you use type timestamp, instead of datetime
> timestampField = timestampField - '30minutes'::interval
>  you will get error msg.:
> No such function 'datetime_stamp' with the specified attributes
>
> --
> Banghe

> > Is there anything that datetime did that timestamp doesn't?
> >
> > Could you
> >
> >    pg_dump | sed | psql
> >
> > to change the datetimes to timestamp?

Interesting. Works fine w/7.1. I don't have a 7.0 db running anywhere to
see that this fails under 7.0.

BTW, I was being far to simplistic in suggesting that
you should pipe pg_dump to sed to psql. As was pointed out,
redirect pg_dump to a file, sed/perl/edit that, then redirect from that to
psql.

HTH,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

Re: datetimeField = datetimeField - '30minutes'::timespan in psql 7.0 or above

From
Tom Lane
Date:
bangh <banghe@baileylink.net> writes:
> while if you use type timestamp, instead of datetime
> timestampField = timestampField - '30minutes'::interval
>  you will get error msg.:
> No such function 'datetime_stamp' with the specified attributes

I see no such problem in either 7.0 or 7.1 ...

regression=# create table foo1 (f1 timestamp);
CREATE
regression=# insert into foo1 values(now());
INSERT 832069 1
regression=# update foo1 set f1 = f1 - '30minutes'::interval;
UPDATE 1
regression=#

            regards, tom lane