Thread: pointer to feature comparisons, please
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
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/freeware/mysql-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
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
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/cd/B14117_01/appdev.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
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!
>-----Original Message----- >From: pgsql-general-owner@postgresql.org >[mailto:pgsql-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/cd/B14117_01/appdev.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
> 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...
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!
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.
On 06/13/07 17:23, PFC wrote: > 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 Foreign keys that silently, automatic DELETE records? Did I read that correctly? > besides checking the referencing column on insert/update... If you just > implement a constraint, you only get half the functionality. But when I define a FK *constraint*, that's all I *want*! -- 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!
Kevin Hunter wrote: > 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)? ok consider the following scenario: you have table A with a check constraint saying the a given column depends on the result of some arbitrary queries on table B (maybe something as simple as "this flag can only be set to true if there are at least 5 items of this kind in table B). So after a while you added some data to both tables and A has set that column to true for a few rows. Now you manipulate B in a way that woudl cause maybe one constraint to fail in A IF and only IF it get's reevaluated. One day later your server decides to commit suicide - so you get the spare one dig out the backup from the day before and try to restore that dump. First you will get the schema restored from the backup - next all the data will be put in into the tables and then ? "Booooom". The third major step in restoring the database is readding all constraints - if the CHECK constraint get's readded it will get evaluated by the database fully (ie row-by-row) - but because your data is inconsistent you will get a constraint violation on the now inconsistent data ... The trigger would avoid that restore problem because it would not be invoked in a similiar fashion (ie they "trigger" on UPDATE/DELETE/INSERT which are already done at that point of the restore). That is just one of the most obvious failures - trying to implement that kind of "arbitrary complex query based foreign-key like" constraint is always very difficult. > >> 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/cd/B14117_01/appdev.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? well - the main point why the trigger is better is because it will cause you less operational issues, what it cannot guarantee you either is that you cannot manipulate table B in a way that would violate the checking logic of your constraint in table A (well you could use a trigger on A to validate that in the other direction - but that opens up another big can of worms). The best way is to design your schema in a way that you can use real foreign key constraints to enforce various things or CHECK constraints that only deal with data in the very same row(ie neither do queries on other tables nor depend on OTHER rows than the one that is manipulated in the same table). Stefan
>>> The DELETE should block, no?
>> Why ?
>> Foreign keys put an ON DELETE trigger on the referenced table
>
> Foreign keys that silently, automatic DELETE records?
> Did I read that correctly?
Isn't that the point of ON DELETE CASCADE ?
>> besides checking the referencing column on insert/update... If you just
>> implement a constraint, you only get half the functionality.
>
> But when I define a FK *constraint*, that's all I *want*!
When you add a REFERENCE foo( bar ) foreign key and don't specify ON
DELETE clause, I believe you get ON DELETE NO ACTION, which means the
referenced row can't be deleted if there are referencing rows... so when
you have a FK constraint, it actually constrains two tables, and pg uses
share locking to ensure race conditions can't cause an inconsistent
database.
On 06/14/07 02:24, PFC wrote: > >>>> The DELETE should block, no? >>> Why ? >>> Foreign keys put an ON DELETE trigger on the referenced table >> >> Foreign keys that silently, automatic DELETE records? >> Did I read that correctly? > > Isn't that the point of ON DELETE CASCADE ? Where'd that come from? Did I miss something in the thread? >>> besides checking the referencing column on insert/update... If you >>> just implement a constraint, you only get half the functionality. >> >> But when I define a FK *constraint*, that's all I *want*! > > When you add a REFERENCE foo( bar ) foreign key and don't specify ON > DELETE clause, I believe you get ON DELETE NO ACTION, which means the > referenced row can't be deleted if there are referencing rows... so > when you have a FK constraint, it actually constrains two tables, and pg > uses share locking to ensure race conditions can't cause an inconsistent > database. -- 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!