Thread: PostgreSQL Object-Oriented Database?

On Mon, Apr 27, 2009 at 3:00 PM, Robert Pepersack
<RPepersack@mdinsurance.state.md.us> wrote:
> Hi,
>
> Thanks in advance for your help.
>
> I have a lot of experience with object-oriented programming and relational
> databases, but I'm new to PostgreSQL.
>
> My agency has a contractor that created a PostgreSQL database that he calls
> "object-oriented".  I noticed that the contractor has more than one value in
> a column separated by commas.  In the relational world, this obviously
> violates first normal form.  When I asked about it, he said that it's
> because PostgreSQL is an "object-oriented database".  I'm very skeptical.
>
> Can someone tell me if this guy is right?

Where I am from, it is very wrong approach. But most importantly, is
what is the search key in his queries. If that text field is, and he
uses loads of "LIKE" statements, than yes - he obviously doesn't know
databases well enough to use them properly.




--
GJ

In response to "Robert Pepersack" <RPepersack@mdinsurance.state.md.us>:

> Hi,
>
> Thanks in advance for your help.
>
> I have a lot of experience with object-oriented programming and relational databases, but I'm new to PostgreSQL.
>
> My agency has a contractor that created a PostgreSQL database that he calls "object-oriented".  I noticed that the
contractorhas more than one value in a column separated by commas.  In the relational world, this obviously violates
firstnormal form.  When I asked about it, he said that it's because PostgreSQL is an "object-oriented database".  I'm
veryskeptical. 
>
> Can someone tell me if this guy is right?

PostgreSQL supports an array type for any base column type.  It's not
always the _best_ way to do things, but (in my experience) it's sometimes
better to use an array column than to do an additional one-to-many table.

Whether or not it's good design is highly dependent on the nature of
the data.

In psql, do \d on the table, if the field type is postfixed with a
[], then it's an array datatype:
http://www.postgresql.org/docs/8.3/static/arrays.html

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On Mon, Apr 27, 2009 at 7:00 AM, Robert Pepersack
<RPepersack@mdinsurance.state.md.us> wrote:

> My agency has a contractor that created a PostgreSQL database that he calls
> "object-oriented".

I might be incorrect in my thinking about what makes PostgreSQL
"Object-Relational", but my understanding is that table inheritance is
the feature that behind this claim.

Basically its horizontal table partitioning with a few nice features added.


> When I asked about it, he said that it's because PostgreSQL is an "object-oriented database".  I'm very skeptical.

I would be skeptical too.  You can't point the finger at PostgreSQL
for a person's good or bad choice in schema design.
--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Apr 27, 2009, at 7:00 AM, Robert Pepersack wrote:
> My agency has a contractor that created a PostgreSQL database that
> he calls "object-oriented".  I noticed that the contractor has more
> than one value in a column separated by commas.  In the relational
> world, this obviously violates first normal form.  When I asked
> about it, he said that it's because PostgreSQL is an "object-
> oriented database".  I'm very skeptical.

Without knowing the details of the design, it's hard to comment on the
design's strengths or weaknesses.  However, there is nothing about
PostgreSQL that requires any different design than one would use on
any relational database.

Arrays are, sometimes, a good solution for particular problems, and
PostgreSQL has very fluent array functionality, but nothing about
PostgreSQL requires their use versus more traditional relational
designs.

In response to "Robert Pepersack" <RPepersack@mdinsurance.state.md.us>:

> I read the document on array data types.  Do they have anything at all to do with PostgreSQL being "object-oriented"?

If you want to be pedantic, not really.  Technically, Postgres isn't
"object-oriented", it's "object-relational".

But then again, C isn't considered to be object-oriented, but I've
seen some very clever object-oriented code written in C.  Of course,
there are languages that have object-oriented syntax as more of the
core of their design, which usually is what's meant by saying that
a language is "object-oriented".

Going from that statement, you could argue that PostgreSQL is very
object-oriented.  Arrays are the least of the objecty features in
the system: stored procedures, triggers and table inheritance are
much more objectivy than arrays, although arrays could arguably
be a part of Postgres' object friendliness.

Looking for a more concise, more to-the-point answer?  Ask a
salesperson, I'm they'll tell you whatever you want to hear.

> Also, these comma-delimited fields make creating reports with our reporting tool impossible.

Sounds like your reporting tool is horribly limited.  Of course,
if you knew that you'd be using this reporting tool, then it was
your database designer's fault for not considering this limitation.
If you chose the reporting tool after the database was designed, then
it was a poor decision on your part.

If you're looking for someone to blame (and it seems like you are)
then you should just pick someone and start making up reasons.  That's
what politicians do with great success.

Honestly ... what are you attempting to accomplish with this thread?
It seems to me that you're trying get the people on this mailing list
to help you justify being angry with your database designer.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Bill Moran wrote:
> In response to "Robert Pepersack" <RPepersack@mdinsurance.state.md.us>:
>
>
>> I read the document on array data types.  Do they have anything at all to do with PostgreSQL being
"object-oriented"?
>>
>
> If you want to be pedantic, not really.  Technically, Postgres isn't
> "object-oriented", it's "object-relational".
>
> But then again, C isn't considered to be object-oriented, but I've
> seen some very clever object-oriented code written in C.  Of course,
> there are languages that have object-oriented syntax as more of the
> core of their design, which usually is what's meant by saying that
> a language is "object-oriented".
>
> Going from that statement, you could argue that PostgreSQL is very
> object-oriented.  Arrays are the least of the objecty features in
> the system: stored procedures, triggers and table inheritance are
> much more objectivy than arrays, although arrays could arguably
> be a part of Postgres' object friendliness.
>
> Looking for a more concise, more to-the-point answer?  Ask a
> salesperson, I'm they'll tell you whatever you want to hear.
>
>
>> Also, these comma-delimited fields make creating reports with our reporting tool impossible.
>>
>
> Sounds like your reporting tool is horribly limited.  Of course,
> if you knew that you'd be using this reporting tool, then it was
> your database designer's fault for not considering this limitation.
> If you chose the reporting tool after the database was designed, then
> it was a poor decision on your part.
>
> If you're looking for someone to blame (and it seems like you are)
> then you should just pick someone and start making up reasons.  That's
> what politicians do with great success.
>
> Honestly ... what are you attempting to accomplish with this thread?
> It seems to me that you're trying get the people on this mailing list
> to help you justify being angry with your database designer.
>
It seems to me he's quite legitimately trying to determine if there is
more to his database designer's claim that these
comma separated fields being "object-oriented", than he might think
otherwise. PostgreSQL's (not very meaningful or helpful, IMO)
characterization of itself as an "object-relational database system" no
doubt leads to his very reasonable query whether he should
be taking something more into account than normal relational database
design principles.

I think it's uncalled for to be attacking him or his motives.

Eric

In response to Eric Schwarzenbach <subscriber@blackbrook.org>:

> Bill Moran wrote:
> > In response to "Robert Pepersack" <RPepersack@mdinsurance.state.md.us>:
> >
> >
> >> I read the document on array data types.  Do they have anything at all to do with PostgreSQL being
"object-oriented"?
> >>
> >
> > If you want to be pedantic, not really.  Technically, Postgres isn't
> > "object-oriented", it's "object-relational".
> >
> > But then again, C isn't considered to be object-oriented, but I've
> > seen some very clever object-oriented code written in C.  Of course,
> > there are languages that have object-oriented syntax as more of the
> > core of their design, which usually is what's meant by saying that
> > a language is "object-oriented".
> >
> > Going from that statement, you could argue that PostgreSQL is very
> > object-oriented.  Arrays are the least of the objecty features in
> > the system: stored procedures, triggers and table inheritance are
> > much more objectivy than arrays, although arrays could arguably
> > be a part of Postgres' object friendliness.
> >
> > Looking for a more concise, more to-the-point answer?  Ask a
> > salesperson, I'm they'll tell you whatever you want to hear.
> >
> >
> >> Also, these comma-delimited fields make creating reports with our reporting tool impossible.
> >>
> >
> > Sounds like your reporting tool is horribly limited.  Of course,
> > if you knew that you'd be using this reporting tool, then it was
> > your database designer's fault for not considering this limitation.
> > If you chose the reporting tool after the database was designed, then
> > it was a poor decision on your part.
> >
> > If you're looking for someone to blame (and it seems like you are)
> > then you should just pick someone and start making up reasons.  That's
> > what politicians do with great success.
> >
> > Honestly ... what are you attempting to accomplish with this thread?
> > It seems to me that you're trying get the people on this mailing list
> > to help you justify being angry with your database designer.
>
> It seems to me he's quite legitimately trying to determine if there is
> more to his database designer's claim that these
> comma separated fields being "object-oriented", than he might think
> otherwise. PostgreSQL's (not very meaningful or helpful, IMO)
> characterization of itself as an "object-relational database system" no
> doubt leads to his very reasonable query whether he should
> be taking something more into account than normal relational database
> design principles.

It's possible.

> I think it's uncalled for to be attacking him or his motives.

If that's the case, then I'm the one who will look like a arse.

However, he's being very accusational of someone who isn't here to
defend.  He's also providing no evidence for or against what he
apparently wants us to resolve (i.e. he's posted no schema information,
not even a partial schema).  He's also asked a question that (really)
has no answer.  Is PostgreSQL object-oriented?  That's like asking if
those tires are gasoline or battery powered.  object-oriented is an
approach to programming, not a definition of a language.  I've seen
many a program written in an "object oriented language" that was
basically a functionally designed program.  Language features really
mean little after the code is already written.

PostgreSQL _certainly_ has a slew of features that make it conducive
to object oriented design.  If that's the answer he's looking for,
then there it is.

Reading between the lines, the original question was: "This guy is
making my life difficult, and he claims it's for this reason.  Is
that reason valid?"  And I fall back on my earlier statement: if
you're looking for someone to blame, just pick someone.  Hell, blame
me for all I care.  You wouldn't be the first person.  I mean, if he
really wants to learn if PG is OO or not, what the does his reporting
software have to do with the discussion?

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On Mon, Apr 27, 2009 at 2:03 PM, Bill Moran <wmoran@potentialtech.com> wrote:
> Reading between the lines, the original question was: "This guy is
> making my life difficult, and he claims it's for this reason."

I read the question more as "Did we hire some database contractor who
has no idea what he's doing?"

If the contractor is trying to sell him on something, giving the
reason that "it's that way because PG is object-oriented," then this:

> Is PostgreSQL object-oriented?  That's like asking if
> those tires are gasoline or battery powered.

is the answer to his question.
I.E. it sounds like the contractor either doesn't want to explain an
unusual design decision, or is clueless, and either way is trying to
use buzzwords to shut the client up.  I think the OP was asking us
whether the contractor's logic made sense--to make sure it really
isn't just "that's how things are done with PG" before calling him on
it.

In my opinion, it sounds like the OP should have a long talk with his
contractor about what exactly is going on, to figure out the real
reason behind these design decisions.  Maybe they're good design
decisions (we can't tell and weren't asked) but it doesn't sound like
the contractor is telling the whole story.



On Mon, Apr 27, 2009 at 2:03 PM, Bill Moran <wmoran@potentialtech.com> wrote:
> In response to Eric Schwarzenbach <subscriber@blackbrook.org>:
>
>> Bill Moran wrote:
>> > In response to "Robert Pepersack" <RPepersack@mdinsurance.state.md.us>:
>> >
>> >
>> >> I read the document on array data types.  Do they have anything at all to do with PostgreSQL being
"object-oriented"?
>> >>
>> >
>> > If you want to be pedantic, not really.  Technically, Postgres isn't
>> > "object-oriented", it's "object-relational".
>> >
>> > But then again, C isn't considered to be object-oriented, but I've
>> > seen some very clever object-oriented code written in C.  Of course,
>> > there are languages that have object-oriented syntax as more of the
>> > core of their design, which usually is what's meant by saying that
>> > a language is "object-oriented".
>> >
>> > Going from that statement, you could argue that PostgreSQL is very
>> > object-oriented.  Arrays are the least of the objecty features in
>> > the system: stored procedures, triggers and table inheritance are
>> > much more objectivy than arrays, although arrays could arguably
>> > be a part of Postgres' object friendliness.
>> >
>> > Looking for a more concise, more to-the-point answer?  Ask a
>> > salesperson, I'm they'll tell you whatever you want to hear.
>> >
>> >
>> >> Also, these comma-delimited fields make creating reports with our reporting tool impossible.
>> >>
>> >
>> > Sounds like your reporting tool is horribly limited.  Of course,
>> > if you knew that you'd be using this reporting tool, then it was
>> > your database designer's fault for not considering this limitation.
>> > If you chose the reporting tool after the database was designed, then
>> > it was a poor decision on your part.
>> >
>> > If you're looking for someone to blame (and it seems like you are)
>> > then you should just pick someone and start making up reasons.  That's
>> > what politicians do with great success.
>> >
>> > Honestly ... what are you attempting to accomplish with this thread?
>> > It seems to me that you're trying get the people on this mailing list
>> > to help you justify being angry with your database designer.
>>
>> It seems to me he's quite legitimately trying to determine if there is
>> more to his database designer's claim that these
>> comma separated fields being "object-oriented", than he might think
>> otherwise. PostgreSQL's (not very meaningful or helpful, IMO)
>> characterization of itself as an "object-relational database system" no
>> doubt leads to his very reasonable query whether he should
>> be taking something more into account than normal relational database
>> design principles.
>
> It's possible.
>
>> I think it's uncalled for to be attacking him or his motives.
>
> If that's the case, then I'm the one who will look like a arse.
>
> However, he's being very accusational of someone who isn't here to
> defend.  He's also providing no evidence for or against what he
> apparently wants us to resolve (i.e. he's posted no schema information,
> not even a partial schema).  He's also asked a question that (really)
> has no answer.  Is PostgreSQL object-oriented?  That's like asking if
> those tires are gasoline or battery powered.  object-oriented is an
> approach to programming, not a definition of a language.  I've seen
> many a program written in an "object oriented language" that was
> basically a functionally designed program.  Language features really
> mean little after the code is already written.
>
> PostgreSQL _certainly_ has a slew of features that make it conducive
> to object oriented design.  If that's the answer he's looking for,
> then there it is.
>
> Reading between the lines, the original question was: "This guy is
> making my life difficult, and he claims it's for this reason.  Is
> that reason valid?"  And I fall back on my earlier statement: if
> you're looking for someone to blame, just pick someone.  Hell, blame
> me for all I care.  You wouldn't be the first person.  I mean, if he
> really wants to learn if PG is OO or not, what the does his reporting
> software have to do with the discussion?

Is there any sane reason to use an array column, besides performance
(the values can be read in less disk seeks than in a
table-with-a-foreign-key scenario)?

Greetings
marcin

Hi Robert,

Inheritance normally are defined in the Object Orientated environment.
I however have a different perspective regarding when a database is
defined as OO.

One of the most common OO-databases that I am familiar with is
Intersystems Cache.

If for example we look at how PG create inheritance compared to Cache
(abstract wise):
PG:
CREATE TABLE Person
   <Person details>;
CREATE TABLE Student INHERIT Person
   <Student details>;

Cache:
   CREATE TABLE Person
   <Person details>;
   CREATE TABLE Student INHERIT Person
   <Student details>;

Nothing different in the design, however if we look at a query:
PG: SELECT * FROM Person
In this scenario PG will return a recordset that includes all records
from Person and Student as a UNION between Person and Student.
SELECT * FROM Student
PG will return a recordset that only include Students.

Now consider Cache:
SELECT * FROM Person
Cache will return a record set of everybody defined in the person datatable.
SELECT * FROM Student
The way Cache defines inheritance it will actually brings back a
recordset that is translated into:
SELECT * FROM Student, Person WHERE Person.person_id = Student.person_id
it therefore implies that a student cannot exist if it is not a person.

If we look at how OO is defined in programming languages, it is clear
that Cache follow a design nearer to programming language implementation.

So from my perspective, PG is not really a OO DB.  We can however
through design make it to emulate OO, but it is not forced.

My 2cents worth.

Johan Nel
Pretoria, South Africa.

Robert Pepersack wrote:
> Hi,
>
> Thanks in advance for your help.
>
> I have a lot of experience with object-oriented programming and
> relational databases, but I'm new to PostgreSQL.
>
> My agency has a contractor that created a PostgreSQL database that he
> calls "object-oriented".  I noticed that the contractor has more than
> one value in a column separated by commas.  In the relational world,
> this obviously violates first normal form.  When I asked about it, he
> said that it's because PostgreSQL is an "object-oriented database".  I'm
> very skeptical.
>
> Can someone tell me if this guy is right?
>
> Thanks.

On Mon, 2009-04-27 at 20:37 +0200, marcin mank wrote:
> Is there any sane reason to use an array column, besides performance
> (the values can be read in less disk seeks than in a
> table-with-a-foreign-key scenario)?

Yes.

http://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/


I don't actually talk about array columns in the above but it does
discuss all the ways (except) for an array to deal with that problem. As
I recall the major downside to an array is that you can add or delete an
element, you must update the whole array. Plus you need to use specific
array functions to work with the array versus just a nice join.

Sincerely,

Joshua D. Drake


--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997

if your experience started with a DB that supported Object Relational types such as
http://infolab.stanford.edu/~ullman/fcdb/oracle/or-objects.html

then it is reasonable to assume you would want to maintain those capabilities
can you provide us one testcase we could try to work thru
(using concrete Object Relational types)
?
Martin
__________________________________________
Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung / Note de déni et de confidentialité

This message is confidential. If you should not be the intended receiver, then we ask politely to report. Each unauthorized forwarding or manufacturing of a copy is inadmissible. This message serves only for the exchange of information and has no legal binding effect. Due to the easy manipulation of emails we cannot take responsibility over the the contents.
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Date: Mon, 27 Apr 2009 13:41:19 -0400
> From: subscriber@blackbrook.org
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] PostgreSQL Object-Oriented Database?
>
> Bill Moran wrote:
> > In response to "Robert Pepersack" <RPepersack@mdinsurance.state.md.us>:
> >
> >
> >> I read the document on array data types. Do they have anything at all to do with PostgreSQL being "object-oriented"?
> >>
> >
> > If you want to be pedantic, not really. Technically, Postgres isn't
> > "object-oriented", it's "object-relational".
> >
> > But then again, C isn't considered to be object-oriented, but I've
> > seen some very clever object-oriented code written in C. Of course,
> > there are languages that have object-oriented syntax as more of the
> > core of their design, which usually is what's meant by saying that
> > a language is "object-oriented".
> >
> > Going from that statement, you could argue that PostgreSQL is very
> > object-oriented. Arrays are the least of the objecty features in
> > the system: stored procedures, triggers and table inheritance are
> > much more objectivy than arrays, although arrays could arguably
> > be a part of Postgres' object friendliness.
> >
> > Looking for a more concise, more to-the-point answer? Ask a
> > salesperson, I'm they'll tell you whatever you want to hear.
> >
> >
> >> Also, these comma-delimited fields make creating reports with our reporting tool impossible.
> >>
> >
> > Sounds like your reporting tool is horribly limited. Of course,
> > if you knew that you'd be using this reporting tool, then it was
> > your database designer's fault for not considering this limitation.
> > If you chose the reporting tool after the database was designed, then
> > it was a poor decision on your part.
> >
> > If you're looking for someone to blame (and it seems like you are)
> > then you should just pick someone and start making up reasons. That's
> > what politicians do with great success.
> >
> > Honestly ... what are you attempting to accomplish with this thread?
> > It seems to me that you're trying get the people on this mailing list
> > to help you justify being angry with your database designer.
> >
> It seems to me he's quite legitimately trying to determine if there is
> more to his database designer's claim that these
> comma separated fields being "object-oriented", than he might think
> otherwise. PostgreSQL's (not very meaningful or helpful, IMO)
> characterization of itself as an "object-relational database system" no
> doubt leads to his very reasonable query whether he should
> be taking something more into account than normal relational database
> design principles.
>
> I think it's uncalled for to be attacking him or his motives.
>
> Eric
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Rediscover Hotmail®: Get quick friend updates right in your inbox. Check it out.

Robert Pepersack wrote:

> My agency has a contractor that created a PostgreSQL database that he
> calls "object-oriented".  I noticed that the contractor has more than
> one value in a column separated by commas.  In the relational world,
> this obviously violates first normal form.  When I asked about it, he
> said that it's because PostgreSQL is an "object-oriented database".
> I'm very skeptical.

What kind of "comma-separated"?  If it's an array (i.e. perhaps a column
declared as text[]), then maybe it's not that wrong, as has been said
elsewhere on the thread (but I would agree with you that if there's no
specific reason to do things that way, it's probably less right than
your contractor wants to admit).  If it is actually a single text column
with comma separated values, and the splitting is done client-side, I'd
hazard that you're probably right in placing blame in the contractor.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.