pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRunning queries on inherited tables
6+ messages / 3 participants
[nested] [flat]
* Running queries on inherited tables
@ 1999-09-12 16:41 Michael Richards <miker@scifair.acadiau.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Michael Richards @ 1999-09-12 16:41 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-bugs@postgreSQL.org
Hi.
I was fooling with inheritance today. From the page at:
http://www.postgresql.org/docs/user/inherit.htm
It says:
Here the * after cities indicates that the query should be run over cities
and all classes below cities in the inheritance hierarchy. Many of the
commands that we have
already discussed -- SELECT, UPDATE and DELETE -- support this * notation,
as do others, like ALTER TABLE.
So here's what I tried using the example data...
miker=> CREATE TABLE cities (
miker-> name text,
miker-> population float,
miker-> altitude int -- (in ft)
miker-> );
CREATE
miker=>
miker=> CREATE TABLE capitals (
miker-> state char(2)
miker-> ) INHERITS (cities);
CREATE
miker=> insert into cities (name,altitude) VALUES ('Wolfville',69);
INSERT 160729 1
miker=> insert into capitals (name,altitude,state) VALUES
('Halifax',455,'NS');
INSERT 160730 1
miker=> select * from cities*;
name |population|altitude
---------+----------+--------
Wolfville| | 69
Halifax | | 455
(2 rows)
miker=> update cities* set population=222;
ERROR: parser: parse error at or near "*"
I've tried a number of variations on the cities* thing but can only make
it for for select. Is this a bug?
More playing followed... If I alter table on cities and add a column, is
it not expected that the additional col should appear in the tables which
inherit from cities?
miker=> alter table cities add column niceplace bool;
ADD
miker=> select * from cities;
name |population|altitude|niceplace
---------+----------+--------+---------
Wolfville| | 69|
(1 row)
miker=> select * from cities*;
name |population|altitude|niceplace
---------+----------+--------+---------
Wolfville| | 69|
Halifax | | 455|t
(2 rows)
miker=> select niceplace from capitals;
ERROR: attribute 'niceplace' not found
miker=> \d capitals;
Table = capitals
+-----------------------------+----------------------------------+-------+
| Field | Type | Length|
+-----------------------------+----------------------------------+-------+
| name | text | var |
| population | float8 | 8 |
| altitude | int4 | 4 |
| state | char() | 2 |
+-----------------------------+----------------------------------+-------+
Something is positively b0rked here.... Halifax is showing up as having
niceplace=true, yet according to the next select, it doesn't have a column
of that name...
I'm running 6.5.1. If this is not an error on my part, any people can't
reproduce it, I'll submit a bug report...
-Michael
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [BUGS] Running queries on inherited tables
@ 1999-09-12 17:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Richards <miker@scifair.acadiau.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 1999-09-12 17:07 UTC (permalink / raw)
To: Michael Richards <miker@scifair.acadiau.ca>; +Cc: pgsql-sql; pgsql-bugs@postgresql.org
Michael Richards <miker@scifair.acadiau.ca> writes:
> I was fooling with inheritance today. From the page at:
> http://www.postgresql.org/docs/user/inherit.htm
> Here the * after cities indicates that the query should be run over cities
> and all classes below cities in the inheritance hierarchy. Many of the
> commands that we have
> already discussed -- SELECT, UPDATE and DELETE -- support this * notation,
> as do others, like ALTER TABLE.
The doc is wrong here --- UPDATE and DELETE don't support *. They should.
> More playing followed... If I alter table on cities and add a column, is
> it not expected that the additional col should appear in the tables which
> inherit from cities?
You have to say "alter table cities*", I believe, otherwise only cities
is changed. Which is pretty broken --- if inheritance means anything,
then it ought to mean that the alteration is *inherently* applied to all
the child tables too, and you shouldn't have the option. In general,
however, beware that alteration of inheritance structures is pretty
thoroughly broken --- see various complaints in the pghackers archives
(mostly from Chris Bitmead, I think). ALTER TABLE really needs a
reimplementation from the ground up, but I dunno when anyone will get
around to it.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [BUGS] Running queries on inherited tables
@ 1999-09-12 18:41 Michael Richards <miker@scifair.acadiau.ca>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Michael Richards @ 1999-09-12 18:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; pgsql-bugs@postgreSQL.org
On Sun, 12 Sep 1999, Tom Lane wrote:
> You have to say "alter table cities*", I believe, otherwise only cities
> is changed. Which is pretty broken --- if inheritance means anything,
> then it ought to mean that the alteration is *inherently* applied to all
> the child tables too, and you shouldn't have the option. In general,
Would this be a simple change in parsing the statement to see if it has
any children and translate the statement accordingly?
> (mostly from Chris Bitmead, I think). ALTER TABLE really needs a
> reimplementation from the ground up, but I dunno when anyone will get
Considering how often Alter table is used, would it be reasonable to rip
out all the alter table code and just have it do a select into;drop;rename
that would be nice in that dropping/adding columns would be easy,
inheritance would (should) be preserved and it's simple.
Of course I wouldn't want to do this on a 5Gb table...
-Michael
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [BUGS] Running queries on inherited tables
@ 1999-09-13 00:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Richards <miker@scifair.acadiau.ca>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 1999-09-13 00:00 UTC (permalink / raw)
To: Michael Richards <miker@scifair.acadiau.ca>; +Cc: pgsql-sql; pgsql-bugs@postgreSQL.org
Michael Richards <miker@scifair.acadiau.ca> writes:
> On Sun, 12 Sep 1999, Tom Lane wrote:
>> You have to say "alter table cities*", I believe, otherwise only cities
>> is changed. Which is pretty broken --- if inheritance means anything,
>> then it ought to mean that the alteration is *inherently* applied to all
>> the child tables too, and you shouldn't have the option.
> Would this be a simple change in parsing the statement to see if it has
> any children and translate the statement accordingly?
Yes, I think it would be a reasonably localized change, assuming that
no one objected. (I suppose somewhere out there is someone who thinks
the current behavior is a good idea ;-).)
>> (mostly from Chris Bitmead, I think). ALTER TABLE really needs a
>> reimplementation from the ground up, but I dunno when anyone will get
> Considering how often Alter table is used, would it be reasonable to rip
> out all the alter table code and just have it do a select into;drop;rename
That would be a good route to a reimplementation, actually. Want to
have a go at it?
> Of course I wouldn't want to do this on a 5Gb table...
There's probably not much choice. The current implementation avoids
touching the data at all, but that is precisely the source of most of
its bugs and limitations. I think most of the cases that we currently
can't handle would involve changing all the tuples, and at that point
select-into-a-new-table is probably really the preferred technique
compared to trying to do it in-place. (In-place, you'd have to do a
VACUUM to get back the extra 5Gb after the transformation is done,
since you surely don't want to overwrite the old tuples before commit.)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [BUGS] Running queries on inherited tables
@ 1999-09-13 14:49 Michael Richards <miker@scifair.acadiau.ca>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Richards @ 1999-09-13 14:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@postgreSQL.org
On Sun, 12 Sep 1999, Tom Lane wrote:
> > Considering how often Alter table is used, would it be reasonable to rip
> > out all the alter table code and just have it do a select into;drop;rename
>
> That would be a good route to a reimplementation, actually. Want to
> have a go at it?
Sure. I'll wade into the code and see if I can swim. I think I'll first
try to implement ALTER TABLE class_name DELETE COLUMN col_name
Which version do you suggest I work with to come up with patches?
> > Of course I wouldn't want to do this on a 5Gb table...
>
> There's probably not much choice. The current implementation avoids
> touching the data at all, but that is precisely the source of most of
> its bugs and limitations. I think most of the cases that we currently
D'oh. Now that I think about it you'd need 2n the amount of space
anyway... That brings up an intersting point... Does the database do a
rollback if it runs out of space on the device? Considering that a vacuum
is the only way to reclaim space that should mean that all queries
following should fail unless they are selects.
-Michael
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Re: [BUGS] Running queries on inherited tables
@ 1999-09-21 14:57 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Michael Richards <miker@scifair.acadiau.ca>
1 sibling, 0 replies; 6+ messages in thread
From: Herouth Maoz @ 1999-09-21 14:57 UTC (permalink / raw)
To: pgsql-sql
At 02:00 +0200 on 13/09/1999, Tom Lane wrote:
> > Considering how often Alter table is used, would it be reasonable to rip
> > out all the alter table code and just have it do a select into;drop;rename
>
> That would be a good route to a reimplementation, actually. Want to
> have a go at it?
OIDs of all rows will be changed, won't they? Some people rely on OIDs in
their applications.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1999-09-21 14:57 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-12 16:41 Running queries on inherited tables Michael Richards <miker@scifair.acadiau.ca>
1999-09-12 17:07 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-09-12 18:41 ` Michael Richards <miker@scifair.acadiau.ca>
1999-09-13 00:00 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-09-13 14:49 ` Michael Richards <miker@scifair.acadiau.ca>
1999-09-21 14:57 ` Herouth Maoz <herouth@oumail.openu.ac.il>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox