SEO

Hello List,

Short version: I want pointers to feature comparisons of Postgres vs
Oracle. Can the list help?

Long version:

I'm working with a student on a project for school. I'm trying to
teach "right" methods of thinking and doing things, such as making
the database/data model the authoritative source rather than adding
code to the application layer.

I originally had him code his project for Postgres, but for reasons
beyond our control we've had to move to Oracle. In designing the
schema we have need of a constraint that checks values in other
tables. The way that I currently know how to do this in Postgres is
with PLpgSQL functions. Then I add something like

CONSTRAINT away_team_is_playing CHECK ( NOT teamIsPlaying
( awayteamid, timeid ) )

to the table schema. No big deal, except that it seems Oracle can't
use anything other than a simple column constraint. He can't use any
custom functions like he could in Postgres, and we've yet to find a
solution to do what he needs.

I didn't immediately find anything last night on the postgresql.org
website, or a wider Google search.

So, motivation aside, what I'm wanting is a couple of pointers to
feature comparisons of Postgres vs Oracle. What else is going to
bite him while he works on this project? Would be handy to have this
reference since neither of us are really DB wizards. (Besides!
Isn't it good to tout what Postgres does better than it's
competition? :-) )

Thanks,

Kevin

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Jun 13, 8:57 am, hunt...@earlham.edu (Kevin Hunter) wrote:
> So, motivation aside, what I'm wanting is a couple of pointers to
> feature comparisons of Postgres vs Oracle. What else is going to
> bite him while he works on this project? Would be handy to have this
> reference since neither of us are really DB wizards. (Besides!
> Isn't it good to tout what Postgres does better than it's
> competition? :-) )

This might help a bit on the SQL side:

Comparison of different SQL implementations
http://troels.arvin.dk/db/rdbms/

http://troels.arvin.dk/db/rdbms/ is where I go when I have to figure out
how to cope with someone's MySQL mess [this week: it lets you put an
arbitrary integer into a boolean column? seriously?]; it's also handy for
comparing against Oracle.

There is a helpful table
http://www-css.fnal.gov/dsg/external...-vs-pgsql.html I refer
to sometimes. It's from March of 2005 so several pieces are out of date.

Kevin Kline's "SQL in a Nutshell" also has some helpful suggestions on
syntax differences between the major SQL dialects, but it's even older.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

Kevin Hunter wrote:

[...]
> I originally had him code his project for Postgres, but for reasons
> beyond our control we've had to move to Oracle. In designing the schema
> we have need of a constraint that checks values in other tables. The
> way that I currently know how to do this in Postgres is with PLpgSQL
> functions. Then I add something like
>
> CONSTRAINT away_team_is_playing CHECK ( NOT teamIsPlaying( awayteamid,
> timeid ) )
>
> to the table schema. No big deal, except that it seems Oracle can't use
> anything other than a simple column constraint. He can't use any custom
> functions like he could in Postgres, and we've yet to find a solution to
> do what he needs.

well doing it that way is usually not a good idea at all (you cannot
actually use arbitrary queries in a CHECK constraint in pg either -
using a function to hide that is cheating the database - oracle might
actually be more(!) clever here not less ...). this why you can get into
all kind of weird situations with losing the integrity of your data or
running into serious issues during dump/restore for example.

What you need to do here is to use a trigger.


Stefan

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

At 3:26p -0400 on 13 Jun 2007, Stefan Kaltenbrunner wrote:
>> The way that I currently know how to do this in Postgres is with
>> PLpgSQL functions. Then I add something like
>>
>> CONSTRAINT away_team_is_playing CHECK ( NOT teamIsPlaying
>> ( awayteamid, timeid ) )
>>
>> to the table schema.
>
> well doing it that way is usually not a good idea at all (you
> cannot actually use arbitrary queries in a CHECK constraint in pg
> either - using a function to hide that is cheating the database -
> oracle might actually be more(!) clever here not less ...). this
> why you can get into all kind of weird situations with losing the
> integrity of your data or running into serious issues during dump/
> restore for example.

I was /hoping/ for a response like this! Thanks! Okay. I'll bite.
Why can't they be used in general? Is it the same problem that the
trigger has (below)?

> What you need to do here is to use a trigger.

From online docs regarding Oracle, this is not 100% safe either:

(http://download-east.oracle.com/docs...ev.101/b10795/
adfns_co.htm)
'To enforce this rule without integrity constraints, you can use a
trigger to query the department table and test that each new
employee's department is valid. But this method is less reliable than
the integrity constraint. SELECT in Oracle Database uses "consistent
read", so the query might miss uncommitted changes from other
transactions.'

It seems to me that there are certain situations where, especially in
a highly normalized data model, that you'd /have/ to have multiple
checks of even other tables. What theory am I missing if this is not
the case?

(I'm curious as well for another project on which I'm working that
does use pg and currently uses a function in just this fashion.)

Thanks,

Kevin

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 06/13/07 15:02, Kevin Hunter wrote:
[snip]
>
> 'To enforce this rule without integrity constraints, you can use a
> trigger to query the department table and test that each new employee's
> department is valid. But this method is less reliable than the integrity
> constraint. SELECT in Oracle Database uses "consistent read", so the
> query might miss uncommitted changes from other transactions.'

Isn't it *supposed* to mis UNcommitted changes from other transactions?

--
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

>-----Original Message-----
>From: pgsql-general-owner@postgresql.org
>[mailtogsql-general-owner@postgresql.org] On Behalf Of Kevin Hunter
>Sent: woensdag 13 juni 2007 22:03
>To: Stefan Kaltenbrunner
>Cc: PostgreSQL General List
>Subject: Re: [GENERAL] pointer to feature comparisons, please
>
>At 3:26p -0400 on 13 Jun 2007, Stefan Kaltenbrunner wrote:
>>> The way that I currently know how to do this in Postgres is with
>>> PLpgSQL functions. Then I add something like
>>>
>>> CONSTRAINT away_team_is_playing CHECK ( NOT teamIsPlaying (
>>> awayteamid, timeid ) )
>>>
>>> to the table schema.
>>
>> well doing it that way is usually not a good idea at all (you cannot
>> actually use arbitrary queries in a CHECK constraint in pg either -
>> using a function to hide that is cheating the database -
>oracle might
>> actually be more(!) clever here not less ...). this why you can get
>> into all kind of weird situations with losing the integrity of your
>> data or running into serious issues during dump/ restore for example.
>
>I was /hoping/ for a response like this! Thanks! Okay. I'll bite.
>Why can't they be used in general? Is it the same problem
>that the trigger has (below)?
>
>> What you need to do here is to use a trigger.
>
> From online docs regarding Oracle, this is not 100% safe either:
>
>(http://download-east.oracle.com/docs...ev.101/b10795/
>adfns_co.htm)
>'To enforce this rule without integrity constraints, you can
>use a trigger to query the department table and test that each
>new employee's department is valid. But this method is less
>reliable than the integrity constraint. SELECT in Oracle
>Database uses "consistent read", so the query might miss
>uncommitted changes from other transactions.'

For constraints, you don't want that to happen obviously...
In fact, if you run serializable the problems are even bigger.

In Oracle you should use SELECT FOR UPDATE for such constraints. They do
interfere with concurrency a bit, but you can in fact guarentee you
constraints (to a certain better point). It does require a lot of
thought nevertheless and its troublesome to get right.

In PostGreSQL there are more limitations to guarenteeing such
constraint. You can go a long with with SELECT FOR SHARE, but you can
run into problems when using serializable isolation. It's a bit better
on concurrency (it seems), but cannot enforce the constraint up to the
level Oracle can.

It's a tricky subject, it requires a lot of work for a single
constraint. Also you must be very aware of the limitations of such
constructs, since many are impossible to guarentee at this point in
time. In general, the world is less concerned with it.

>It seems to me that there are certain situations where,
>especially in a highly normalized data model, that you'd
>/have/ to have multiple checks of even other tables. What
>theory am I missing if this is not the case?
>
>(I'm curious as well for another project on which I'm working
>that does use pg and currently uses a function in just this fashion.)

They should use triggers. Also sometimes it possible to transform the
database schema in a way that you can enforce the constraint with
build-in (foreign key) constraints.

The general problem with these type of constraints is that they are
assumed to be true at ALL times. However it is possible to violate the
constraint, contradicting the assumption we just made. For triggers
there do not exist such assumptions.
Unless the database is going to support constraints with subqueries
(which is very hard to achieve and quite involved), we cannot rely on
the assuption that constraints are always true. In addition, don't
expect this type of support anytime soon on any opensource/commercial
database.

- Joris


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

> Isn't it *supposed* to mis UNcommitted changes from other transactions?

Well, if the "uncommited change" is a DELETE of the row that allowed the
constraint check to pass, then when this delete is commited, your data is
no longer consistent.

Consider this :

CREATE TABLE A( attributes INT[], CHECK( is_valid_attributes( attributes
)) )

CREATE TABLE valid_attributes ( attribute_id INTEGER )

You want to check that A.attributes is an array of values, the only
allowed values being stored in valid_attributes table. If you delete a row
in valid_attributes, many rows in A can become invalid unless you use some
form of trigger on valid_attributes which would start to look a lot like a
foreign key ON DELETE trigger. If you insert stuff in A while concurrently
deleting a row in valid_attributes, you have problems. This is why foreign
key checks take share locks on referenced tables...


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On 06/13/07 16:59, PFC wrote:
>> Isn't it *supposed* to mis UNcommitted changes from other transactions?
>
> Well, if the "uncommited change" is a DELETE of the row that allowed
> the constraint check to pass, then when this delete is commited, your
> data is no longer consistent.

The DELETE should block, no?

--
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

On Thu, 14 Jun 2007 00:09:20 +0200, Ron Johnson <ron.l.johnson@cox.net>
wrote:

> On 06/13/07 16:59, PFC wrote:
>>> Isn't it *supposed* to mis UNcommitted changes from other transactions?
>> Well, if the "uncommited change" is a DELETE of the row that
>> allowed the constraint check to pass, then when this delete is
>> commited, your data is no longer consistent.
>
> The DELETE should block, no?

Why ?

Foreign keys put an ON DELETE trigger on the referenced table besides
checking the referencing column on insert/update... If you just implement
a constraint, you only get half the functionality.



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly