Thread: How Can I use OO concepts?
Hi ALL I´m trying to test OO concepts using postgres. I took an Oracle example, extracted from http://savtechno.com/ViewOfORDBMS.html As I know Oracle has more OO mature concepts, and Postgres? Where is OO into PostgreSQL (weak point in table?) How can I do the same as Oracle using Postgres? Please correct me. Oracle OO --------------------------------------------------- CREATE TYPE Employee AS OBJECT ( Name VARCHAR2(20), Salary NUMBER(6,2) ) NOT FINAL; CREATE TYPE Programmer UNDER Employee ( Language VARCHAR2(12), Project VARCHAR2(30) ); CREATE TYPE Representative UNDER Employee ( Region VARCHAR2(30) ); CREATE TABLE employees OF Employee; CREATE TABLE programmers OF Programmer; CREATE TABLE representatives OF Representative; INSERT INTO employees VALUES (Employee('Sylvia Karsen', 30000.00)); INSERT INTO programmers VALUES (Programmer('William Helprin', 40000.00, 'C++', 'Seestorm')); INSERT INTO representatives VALUES (Representative('Akiko Yokomoto', 50000.00, 'Asia')); The "Programmer" and "Representative" subtypes inherit all the attributes from the "Employee" supertype. A request for "employees" objects of the "Employee" type means also request for objects of subtypes, namely "programmers" and "representatives". For example, the result of the following SQL statement: ORACLE Query Output SELECT e.Name FROM employees e; Would be: Name -------------------- Sylvia Karsen William Helprin Akiko Yokomoto My PostgreSQL Solution ???? OO??? I think not. CREATE TABLE Employee ( Name VARCHAR(20), Salary NUMERIC(6,2) ); CREATE TABLE Programmer ( Language VARCHAR(12), Project VARCHAR(30) )INHERITS (Employee); CREATE TABLE Representative ( Region VARCHAR(30) ) INHERITS (Employee); CREATE TABLE employees AS SELECT * FROM Employee; CREATE TABLE programmers AS SELECT * FROM Programmer; CREATE TABLE representatives AS SELECT * FROM Representative; INSERT INTO employees VALUES ('Sylvia Karsen', 3000.00); INSERT INTO programmers VALUES ('William Helprin', 400.00, 'C++', 'Seestorm'); INSERT INTO representatives VALUES ('Akiko Yokomoto', 500.00, 'Asia'); ORACLE Query Output SELECT e.Name FROM employees e; Would be: Name -------------------- Sylvia Karsen
On Wed, 2005-11-23 at 16:47 +0000, flavio@gral.org.br wrote: > Hi ALL > > I´m trying to test OO concepts using postgres. I took an Oracle example, extracted from > http://savtechno.com/ViewOfORDBMS.html > As I know Oracle has more OO mature concepts, and Postgres? Where is OO into PostgreSQL > (weak point in table?) > > How can I do the same as Oracle using Postgres? Please correct me. ... > > My PostgreSQL Solution ???? OO??? I think not. > > CREATE TABLE Employee ( > Name VARCHAR(20), > Salary NUMERIC(6,2) > ); > CREATE TABLE Programmer ( > Language VARCHAR(12), > Project VARCHAR(30) > )INHERITS (Employee); > CREATE TABLE Representative ( > Region VARCHAR(30) > ) INHERITS (Employee); The mixed-case table names are a bad idea. They are automatically folded to lower-case unless double-quoted; it just causes confusion. You have already created tables, so the next three lines are not needed: > CREATE TABLE employees AS SELECT * FROM Employee; > CREATE TABLE programmers AS SELECT * FROM Programmer; > CREATE TABLE representatives AS SELECT * FROM Representative; (you could create views, but why bother?) > INSERT INTO employees VALUES ('Sylvia Karsen', 3000.00); > INSERT INTO programmers VALUES ('William Helprin', 400.00, 'C++', 'Seestorm'); > INSERT INTO representatives VALUES ('Akiko Yokomoto', 500.00, 'Asia'); Insert directly into the tables you created first. > ORACLE Query Output > SELECT e.Name FROM employees e; > Would be: > > Name > -------------------- > Sylvia Karsen In PostgreSQL, that returns all records in the hierarchy. This is how it goes in PostgreSQL: junk=# CREATE TABLE Employee ( junk(# Name VARCHAR(20), junk(# Salary NUMERIC(6,2) junk(# ); CREATE TABLE junk=# CREATE TABLE Programmer ( junk(# Language VARCHAR(12), junk(# Project VARCHAR(30) junk(# )INHERITS (Employee); CREATE TABLE junk=# CREATE TABLE Representative ( junk(# Region VARCHAR(30) junk(# ) INHERITS (Employee); CREATE TABLE junk=# INSERT INTO employee VALUES ('Sylvia Karsen', 3000.00); INSERT 0 1 junk=# INSERT INTO programmer VALUES ('William Helprin', 400.00, 'C++', 'Seestorm'); INSERT 0 1 junk=# INSERT INTO representative VALUES ('Akiko Yokomoto', 500.00, 'Asia'); INSERT 0 1 junk=# SELECT e.Name FROM employee e; name ----------------- Sylvia Karsen William Helprin Akiko Yokomoto (3 rows) junk=# SELECT e.Name FROM ONLY employee e; name --------------- Sylvia Karsen (1 row) junk=# -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html
flavio@gral.org.br writes:
> CREATE TABLE Employee (
> Name VARCHAR(20),
> Salary NUMERIC(6,2)
> );
> CREATE TABLE Programmer (
> Language VARCHAR(12),
> Project VARCHAR(30)
> )INHERITS (Employee);
> CREATE TABLE Representative (
> Region VARCHAR(30)
> ) INHERITS (Employee);
>
> CREATE TABLE employees AS SELECT * FROM Employee;
> CREATE TABLE programmers AS SELECT * FROM Programmer;
> CREATE TABLE representatives AS SELECT * FROM Representative;
>
> INSERT INTO employees VALUES ('Sylvia Karsen', 3000.00);
> INSERT INTO programmers VALUES ('William Helprin', 400.00, 'C++', 'Seestorm');
> INSERT INTO representatives VALUES ('Akiko Yokomoto', 500.00, 'Asia');
>
> ORACLE Query Output
> SELECT e.Name FROM employees e;
This is the wrong handling of it. You're inheriting from the wrong
tables. There was no need to have pairs of tables for this.
CREATE TABLE Employees (
Name VARCHAR(20),
Salary NUMERIC(6,2)
);
CREATE TABLE Programmers (
Language VARCHAR(12),
Project VARCHAR(30)
)INHERITS (Employees);
CREATE TABLE Representatives (
Region VARCHAR(30)
) INHERITS (Employees);
INSERT INTO employees VALUES ('Sylvia Karsen', 3000.00);
INSERT INTO programmers VALUES ('William Helprin', 400.00, 'C++', 'Seestorm');
INSERT INTO representatives VALUES ('Akiko Yokomoto', 500.00, 'Asia');
select * from employees;
That has the expected output...
/* cbbrowne@[local]/dba2 tqual_test=*/ select * from employees;
name | salary
-----------------+---------
Sylvia Karsen | 3000.00
William Helprin | 400.00
Akiko Yokomoto | 500.00
(3 rows)
--
select 'cbbrowne' || '@' || 'acm.org';
http://cbbrowne.com/info/spiritual.html
"Markets can remain irrational longer than you can remain solvent."
-- J. M. Keynes
How does Postgres internally handle inheritance under the following scenario? Using sample tables similar to a previous post: CREATE TABLE employee(id primary key, name varchar, salary numeric(6,2)); CREATE TABLE programmer(language varchar, project varchar) INHERITS (employee); CREATE TABLE representative (region varchar) INHERITS (employee); Let's say for example's sake, there are 10 million rows of PROGRAMMER data but only 100 rows of representative data. Will a query (select, update, insert, etc) on the REPRESENTATIVE table take a performance hit because of this? It seems like the child-table is really not concrete. It seems like it is just a "pointer" to certain rows in the parent table which is then able to discern which inherited "table-type" it is. Thanks, Aaron
"Announce" <truthhurts@insightbb.com> writes:
> How does Postgres internally handle inheritance under the following
> scenario?
> Using sample tables similar to a previous post:
> CREATE TABLE employee(id primary key, name varchar, salary numeric(6,2));
> CREATE TABLE programmer(language varchar, project varchar) INHERITS
> (employee);
> CREATE TABLE representative (region varchar) INHERITS (employee);
> Let's say for example's sake, there are 10 million rows of PROGRAMMER data
> but only 100 rows of representative data. Will a query (select, update,
> insert, etc) on the REPRESENTATIVE table take a performance hit because of
> this?
No.
> It seems like the child-table is really not concrete.
What makes you think that?
In this example, queries against EMPLOYEE take a performance hit due to
the existence of the child tables, because they end up scanning all
three tables. Queries directly against a child table do not notice the
inheritance relationship at all.
regards, tom lane
hi tom can you explain why querying EMPLOYEE will scan all three tables? how are inherited table data stored? is there all data in the 2 "child" tables PROGRAMMER and REPRESENTATIVE? i'm currently looking into the inheritance thing for our system here, too. i always thought the fields belonging to the inherited main table is stored in the main table and the additional fields in the child table... thanks, thomas ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Announce" <truthhurts@insightbb.com> Cc: <pgsql-novice@postgresql.org> Sent: Monday, November 28, 2005 7:00 AM Subject: Re: [NOVICE] How does PG Inheritance work? > "Announce" <truthhurts@insightbb.com> writes: >> How does Postgres internally handle inheritance under the following >> scenario? >> Using sample tables similar to a previous post: > >> CREATE TABLE employee(id primary key, name varchar, salary >> numeric(6,2)); >> CREATE TABLE programmer(language varchar, project varchar) INHERITS >> (employee); >> CREATE TABLE representative (region varchar) INHERITS (employee); > >> Let's say for example's sake, there are 10 million rows of PROGRAMMER >> data >> but only 100 rows of representative data. Will a query (select, update, >> insert, etc) on the REPRESENTATIVE table take a performance hit because >> of >> this? > > No. > >> It seems like the child-table is really not concrete. > > What makes you think that? > > In this example, queries against EMPLOYEE take a performance hit due to > the existence of the child tables, because they end up scanning all > three tables. Queries directly against a child table do not notice the > inheritance relationship at all. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
<me@alternize.com> writes:
> i'm currently looking into the inheritance thing for our system here, too. i
> always thought the fields belonging to the inherited main table is stored in
> the main table and the additional fields in the child table...
There might be systems out there that do it that way, but not Postgres.
Each table is completely independent as far as storage and indexes go.
The inheritance association is implemented by having the planner change
a query that scans a parent table to also scan its child tables. You
can see this happening if you examine the query plan with EXPLAIN:
regression=# create table p(f1 int);
CREATE TABLE
regression=# create index pi on p(f1);
CREATE INDEX
regression=# explain select * from p where f1 = 42;
QUERY PLAN
--------------------------------------------------------------
Index Scan using pi on p (cost=0.00..29.53 rows=11 width=4)
Index Cond: (f1 = 42)
(2 rows)
regression=# create table c(f2 text) inherits(p);
CREATE TABLE
regression=# create index ci on c(f1);
CREATE INDEX
regression=# explain select * from p where f1 = 42;
QUERY PLAN
---------------------------------------------------------------------------
Result (cost=0.00..49.08 rows=17 width=4)
-> Append (cost=0.00..49.08 rows=17 width=4)
-> Index Scan using pi on p (cost=0.00..29.53 rows=11 width=4)
Index Cond: (f1 = 42)
-> Index Scan using ci on c p (cost=0.00..19.54 rows=6 width=4)
Index Cond: (f1 = 42)
(6 rows)
regards, tom lane
Thanks for all of your help on this forum, Tom. This really helps to clear
things up.
I guess I thought that the child tables weren't 'concrete' because it
appeared that operations such as primary key uniqueness across the related
tables was being managed by the parent table for both the parent and all of
its children.
Unless what you said is in the docs under inheritance (I could have missed
it), it would be useful to give scenarios such as that and go into that
amount of detail when describing exactly how inheritance works in Postgres.
-Aaron
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Monday, November 28, 2005 12:01 AM
To: Announce
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] How does PG Inheritance work?
"Announce" <truthhurts@insightbb.com> writes:
> How does Postgres internally handle inheritance under the following
> scenario?
> Using sample tables similar to a previous post:
> CREATE TABLE employee(id primary key, name varchar, salary numeric(6,2));
> CREATE TABLE programmer(language varchar, project varchar) INHERITS
> (employee);
> CREATE TABLE representative (region varchar) INHERITS (employee);
> Let's say for example's sake, there are 10 million rows of PROGRAMMER data
> but only 100 rows of representative data. Will a query (select, update,
> insert, etc) on the REPRESENTATIVE table take a performance hit because of
> this?
No.
> It seems like the child-table is really not concrete.
What makes you think that?
In this example, queries against EMPLOYEE take a performance hit due to
the existence of the child tables, because they end up scanning all
three tables. Queries directly against a child table do not notice the
inheritance relationship at all.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date: 11/27/2005
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/184 - Release Date: 11/27/2005