Yesterday I posted about the results of the EnterpriseDB open source survey. In that post, I said:
I'd like to see the survey again and compare the results to the survey itself.
I discovered that, as of right now, the survey is still online.
Still no information as to when, or if, the entire survey results will be released. That's what I am most interested in.
LewisC
LewisC's An Expert's Guide To Oracle Technology
The datastore in Postgres (which is what Advanced Server is based on) is quite a bit different than the way Oracle stores data. Gone are the usual logical storage components. Data blocks, extents and segments just don't exist in a Postgres database. Actually, segments do exist when a table gets bigger than 1GB but that's a story for a different entry.
Postgres stores data as files and directories. Tablespaces are directories and tables (and indexes) are files inside that directory. Postgres ships with two tablespaces installed by default: pg_default and pg_global. The tablespaces cannot be changed or dropped. pg_global contains objects that are visible across databases (i.e. clusterwide). If you don't have any other tablespaces defined, pg_default is where everything else goes.
If you look at a default installation (I'll be using Windows here but *nix is pretty much the same), Postgres creates a HOME pretty much like Oracle does.
In this directory structure, the "data" directory is the home for data. You don't have to put your data here. During install you can choose a completely different directory and you can create tablespaces to even further separate your data.
The data directory is composed of several other directories:
The base directory is the pg_default tablespace and the global directory is the pg_global tablespace. If we select the base directory, we see that there are more sub-directories beneath that one:
We're getting close to the data now. ;-)
Directories on your system may be named differently than on mine. The directory "1" contains, at least part of, the postgres data dictionary. Directory "11510" also contains part of the data dictionary. There are several system catalogs (which are defined as namespaces) in Postgres and these two directories contain data from those. You can edit the files in the directory with notepad. They are binary files but you can see some of the information. DON'T SAVE THEM!
The pgsql_tmp directory is a working directory for the database. It's usually empty unless a query is running.
If we look at one of these directories, we see that the files are named like the directories that contain them:
All objects in Postgres have a unique object ID. The directory name or file name MAY be the same as the object's ID. That's not always the case though. I haven't quite figured out what conditions make this true but it frequently is the case.
I can run a query, using some of the IDs above, to see what they might be tied to:
And finally, if I create a new tablespace, I can put it where I want it. If I run this command:
create tablespace sample_tblspce location 'c:/temp/extradata';
I will create a new tablespace in my C:\TEMP directory in a subdirectory called extradata. I can then create a table using that tablespace.
create table abc1 (data date) tablespace sample_tblspce;
Now, I'll do a dir command in DOS:
So, when I created the tablespace, I associated a logical name in Postgres with a physical directory in the file system. Postgres did not create the extradata directory but it did create the underlying "12695" directory which is where it actually stores data.
When I created a table in the tablespace, Postgres created a file, 16833. I can re-run my query against pg_class and see the table in the data dictionary:
Well, this has gotten quite a bit longer than I intended. I just wanted to explain that tables in Postgres are stored as files. Same for Indexes. Tablespaces are directories. There are some issues associated with this as well as some benefits. I'll write about those in the future.
LewisC
LewisC's An Expert's Guide To Oracle Technology
An ITToolbox user recently asked a question on the EnterpriseDB discussion group, Oracle equilant UTL_I18N.STRING_TO_RAW IN ENTERPRISEDB.
Basically, Sreenivas asked which functions in EnterpriseDB could be used to implement dbms_crypto, hex_to_raw, string_to_raw, etc. I believe he is using EnterpriseDB Postgres Plus Advanced Server which is the product that gives Oracle Compatibility. The short answer to his question is that right now, there are no compatibility functions for those. The long answer is that you can re-implement that functionality using native PG functionality.
If you look at Sreenivas's message you can see how his existing code works. I posted a simple example in response which I am reposting below. The PG docs suggest that you use PGP based encryption rather than what they call raw encryption. I think it depends on exactly what you're doing, personally. Anyway, raw encryption was closer to what Sreenivas was doing so that was what I based my example on.
I've used DBMS_CRYPTO in Oracle quite a bit but this is my first use of encrypt/decrypt in PG. If you have any suggestions for improving it, I'd like to hear them.
Hi Sreenivas,
I saw your post on the edb forum but and planned to write a blog entry on this topic.
The thing is that there isn't a one to one translation in EDB yet. The easiest thing is to rewrite your procedure and use built-ins that are available.
It is recommended that you use the PGP function in postgres as opposed to the raw encryption functions for better security. However, raw encryption more closely matches what you are trying to do. Below is an example of using raw encryption with AES.
You don't need to convert to hex as you'll be using bytea which is easily converted from and to a string. If you really need international support, check out the pg decode function (which is different from Oracle's decode). http://www.postgresql.org/docs/current/static/funct ions-string.html
Here is a very simple example that you can use to build your procedure:
declare
original_data text := 'I am going to be encrypted';
data bytea;
cipher bytea;
crypto_type text;
encrypted_data bytea;
final_string text;
begin
-- conversion to bytea, could use cast too
data := original_data;
--set cipher key
cipher := 'not a strong key, use pgp instead';
-- select type: aes or blowfish (bf)
crypto_type := 'aes';
-- encrypt the data
select encrypt(data, cipher, crypto_type) into encrypted_data;
dbms_output.put_line('Encrypted: ' || encrypted_data );
-- decrypt the data
select decrypt(encrypted_data, cipher, crypto_type) into final_string;
dbms_output.put_line('Decrypted: ' || final_string );
end;
Hope this helps,
LewisC
LewisC's An Expert's Guide To Oracle Technology
Do you know which open source feature is the most important? Do you know which open source database rocks and which one sucks? Is MySQL better than Postgres? Is Ingres worth considering? How does Firebird compare? Have you used, or have you considered using, an open source database?
Take a survey. It's only 15 questions so it takes just a few minutes.
I'll post a link where you can get the results once they have been compiled and prepared.
BTW, this isn't my survey. I'm just passing on the link.
LewisC
There is a new wiki comparing MySQL to PostgreSQL. Because it's a wiki, hopefully it can be kept updated so that it's current AND accurate. The wiki is MySQL vs PostgreSQL. Personally, I'd like to see this grow into a universal comparison site that the community could keep updated. LewisC
postgresql mysqlFeature comparisons, in addition to being somewhat lame and almost always biased, are very dependent on versions. If you throw in performance considerations, they are also dependent on hardware and configuration. Even so, I like to read comparisons, for historical information if nothing else. Here are some comparisons that I have found. I make no recommendations or even commentary about them. Read through them as if you are a database anthropologist. Dig for the nuggets that increase your existing base of knowledge but remember that a human, probably one with an agenda, put these comparisons together. I have also found that PostgreSQL is most often compared to MySQL and not to one of the large commercial databases such as Oracle or DB2. Update: August 27. A new comparison: PostgreSQL vs Firebird, August 2007 (and hopefully it will remain updated)
Have you ever wondered why you should choose one open source database over another? What features would make the most sense for your Organization? Maybe you're a developer looking to learn a database and can't choose where to start?
The folks at PostgreSQL have put together a wiki, Why PostgreSQL instead of MySQL. It's, by no means, complete at this time but it is a good start. The wiki is not editable by the public but it is open for reading. The wiki entry compares PostgreSQL 8.1 and MySQL 5.0. Some of the points raised are:
