Flutterby™! : switching to PostgreSQL 2000-09-22 23:14:14+00

switching to PostgreSQL

2000-09-22 23:14:14+00 by Dan Lyke 5 comments

Okay, the switchover should be complete, I need to tune a few things because response time seems slower, but I think I've got all the AUTO_INCREMENT versus sequences stuff worked out. I might still have a few timestamp issues, though. Boy, I hope triggers is worth all the rest of this...

Theoretically, you could use a trigger on INSERT combined with a sequence to get the same results as an AUTO_INCREMENT. One of my biggest gripes about Oracle is the lack of AUTO_INCREMENT fields but there is probably some very lofty reason as to why sequences work better. They are definitely more useful: you can get a new, unique value on a SELECT whereas AUTO_INCREMENT requires an INSERT to yield the same results. But that's not a reason why you couldn't have BOTH sequences and AUTO_INCREMENTS... BTW, would you not be completely sold on Postgres if they'd integrate Perl for stored procedures?

Actually, PostgreSQL has sequences which you can use to get the same functionality as AUTO_INCREMENT, the issue is that since they're independent of the table it's possible to insert fields that then later get confused with the sequence values. From a software engineering standpoing I wonder about too much splitting of the functionality of an app, when code is spread out among too many resources you get something that has all the issues of Microsoft's Installer Database.

In the system that Andrew and I are working on we are writing the entire thing in Perl and then writing key functions as stored procedures. Yes, this does result in spreading across many resources, but by writing everything in Perl first you have a 'portable' version of the code. In an 'enterprise' environment, like what we have at AMIS, it is reasonable to expect certain things to be there - like a functioning Sybase database of a specific revision and specific Perl modules, etc, etc. The issue with Microsoft Installer is they are depending on too many of these sort of 'prerequisites' which can't be expected to be there. And if you are working on that same project that you showed me, you are expected a helluvalot of different stuff (a good IIS install, a good NT install, a good Perl on NT install, etc.). The real issue is the classic problem that people trying to run such software who are depending on Microsoft aren't cluefull enough to create a good IIS or NT install. I'd call that a catch 22. But still, why does it seem that SQL databases always have either an AUTO_INCREMENT or sequences. Why not both? Sequences are wonderful for doing things like creating unique values for session IDs whereas AUTO_INCREMENT is great for creating a unique key in a table that is guaranteed to be in insert order. I want BOTH, damn it! As far as I know: Oracle, Postgres: sequences Sybase, Informix, MySQL, MS SQL: AUTO_INCREMENT Oracle does have some "hidden" system fields that contain things like a unique record ID and last modify time but those aren't as easy to use and can't be used as foreign keys in other tables.

7.x. And it turns out I have to recompile my Perl install but I can have triggers in Perl... When I looked at the documentation there wasn't enough functionality in 6.5 to make a compelling case to switch from MySQL. On AUTO_INCREMENT versus sequences, what's a reasonable solution in the case that you're importing large databases from another source? It seems to me that having the AUTO_INCREMENT field store MAX(field) is the right solution, but I can see how various coders might not want to special case that. My issue with spreading out functionality ala Microsoft Installer is that I have truoble remembering where the code to make a given change is. If the database functionality isn't enough to handle all of the integrity issues internally then I can't just grep the code for inserts to find the right place to change stuff.