Hacker News Reader

Comment by castorp | Hacker News Reader

> Zheap does lead to lots of tricky scenarios. If for any reason you need to access the old copy of the row, you have to fetch it from the separate file. If the transaction that performed that update is rolled back, you need to replace the new version of the row with the old version of the row. This sounds straightforward, but gets really tricky really fast.

This is pretty much what Oracle is doing.

View on HN

I have the same impression. It solves all the autovacuum problems, but has a different set of tradeoffs, e.g. the infamous 'ORA-01555 snapshot too old' when your query tries to read the old version but it has already been cleaned away.

Ah the memories! Yes, the "other file" talked about in these comments is also not an unlimited resource and, depending on implementation specifics, I've found can be more limiting than the current PostgreSQL MVCC approach. In Oracle, I use to see data maintenance related procedures being written that would loop and update some large data set, but intermittently commit progress every so often... this would end up exhausting the undo availability. The more frequent the commits, the faster you'd get to the ORA-01555 before the end of the run. Not saying the procedure was right... just that the pattern was common as was the error.

Also, not too long ago PostgreSQL added Stored Procedure support... which allows for mid-transaction commits. Maybe it will be a case of "what goes around, comes around".

I should note I'm not at all against this Zheap idea, stored procedures, or multiple approaches to MVCC/storage back ends in PostgreSQL... being able to choose my effective pros/cons for a project is really desirable. But, solving the vacuum problem will create new problems.

EnterpriseDB (which started this project) specializes in providing postgresql version that can emulate most of Oracle database functionality, so this is not surprising.

No, it provides a basis to ease your migration from Oracle. There is a lot it doesn't do. Basically, if you have a homegrown app on Oracle it removes a lot of the low hanging fruit to migrate but you aren't running peoplesoft or something on it.

I personally never used it, but worked for a company who at the time used EDB to migrate away from Oracle. They apparently had a tons of stored procedures and they mostly worked, but it is possible they didn't use any of the things you mentioned.

Anyway maybe I should say differently, maybe they don't provide most of the functionality, but their goal is provide database that can help to migrate away from Oracle, and this storage engine that appears to mimic one in Oracle (and I'm guessing has similar tradeoffs) fits that model.

> emulate most of Oracle database functionality

What is the basis for this statement?

* MATCH_RECOGNIZE isn't supported

* reference partitions aren't supported

* TIMESTAMP is incompatible and the default compile options are comically bad

These are the first three things that came to mind and none of them are supported so I stopped looking further.

Its also how MySQL works, using overwrite+undo_log rather than multiple row versions in the same table.

Reply on news.ycombinator.com