Обсуждение: Can't drop table (repost)
(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]
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
> > > 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
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
> 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
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
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
> 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]
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
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
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
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
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