There's More to Flash Than SSDs

For those of you considering using SSDs on your servers, you may want to also consider the ioDrive products from Fusion-io.

This company makes enterprise-class storage on a board populated with flash and managed by their own supposedly-sophisticated drivers. The board + drivers are meant to get around the problems of SSDs, such as write-failures. They make both SLC and MLC products, at different sizes, to meet different budgets and purposes.

I tried them about 3 years ago on a database server (not Postgres). The real-world speed was excellent, yet disappointing in that it performed as well as our high-end RAID-10 from HP. We were looking for even more speed, and were promised that, but did not see it in our trials.
Caveats:
• This was using their first attempt at Windows drivers.
• We may not have tuned the settings properly.
In the end, we chose to keep our RAID for the time being.

So, while I can't specifically recommend their products, I certainly suggest considering them. They have drivers for Linux and Windows, but apparently their rumored Mac OS X drivers never came to fruition.

Other benefits beyond speed include size, power, and heat. An internal board with flash saves on all three, compared to a RAID made of either discs or SSDs.

As an aside, this company is also known for having put The Woz to work as their "Chief Scientist". This may be the closest he's come to a regular job since leaving Apple.

Query for Listing of Columns in a Table in Postgres 9

Here's my code for getting a list of a table's columns' name and data type.

One important trick here is skipping columns which have been dropped. The definition of a dropped column remains, so the query needs to filter that out with "attisdropped = False".

SELECT attname::varchar as "Column Name", pg_type.typname::varchar as "Data Type", pg_attribute.attnum as "Position in Table"
FROM pg_attribute, pg_class, pg_type
WHERE attrelid = pg_class.oid
AND pg_attribute.attisdropped = False
AND relname = 'YourTableNameGoesHere'
AND attnum > 0
AND atttypid = pg_type.oid

That code took a lot of googling and trial and error. Hope it helps save you same time and trouble.

Update as of 2011-04-29

Here's another approach I found here:

SELECT ordinal_position, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'YourSchemaNameGoesHere' -- Default is usually 'public'.
AND table_name = 'YourTableNameGoesHere'
ORDER BY ordinal_position;

"information_schema" is a set of meta-data defined by the SQL standard. So the code at the top of this post is Postgres-specific while the last block of code should work in any database complying with the standard.

By the way, if you see gaps in the sequence of the ordinal position numbers, that means columns were deleted from your table. Example: 1, 2, 3, 5, 6 means the 4th added column was deleted from this table's definition.

Passing a UUID Value to a Function in PL/pgSQL

When writing a PL/pgSQL function in Postgres 9.0.x, I ran into a Catch-22 with UUIDs. I tried to write this function to log changes to rows in any table in this "history_" table:

CREATE OR REPLACE FUNCTION make_history_( varchar, varchar, varchar, varchar, varchar, varchar, text ) RETURNS boolean AS $$

BEGIN
  INSERT INTO history_ ( app_user_name_, table_name_, column_name_, affected_uuid_, old_value_, new_value_, notes_, triggered_ )
  VALUES ( $1, $2, $3, $4, $5, $6, $7, True );
  RETURN True;
END;

LANGUAGE plpgsql;

The problem is that our programming uses long hex strings as a representation of the actual 128-bit value that is a UUID. We have no way to actually represent the 128 bits in our source code. So Postgres is generally very kind about accepting a hex string in place of an actual 128-bit value. If we pass Postgres a hex string, Postgres transforms the string into an actual 128-bit value, and then stores those 128 bits efficiently in the database. For SELECT statements, vice-versa, generating a hex string sent as text to the client. This works so well in Postgres that we programmers begin to think of the hex string as the actual UUID – but it is not.

Where this UUID<-->HexString translation broke down for me was passing a hex string into the function above. Notice the 4th argument. The hex string is passed in as varchar, but then we turn around and save it to a table where the column "affected_uuid" is actually of type "uuid". The PL/pgSQL interpreter is a little overeager, and reports a data type mismatch error. It notices that we are passing a varchar, but saving to a column of a different type. I suppose this is a bug in PL/pgSQL, since we can do this with plain SQL in Postgres.

Whether bug or feature, I found a workaround. Declare a variable in your PL/pgSQL code, make it of type "uuid". Assign the argument's value to that variable. Lastly, use that variable in the INSERT command rather than the $x argument itself.


CREATE OR REPLACE FUNCTION make_history_( varchar, varchar, varchar, varchar, varchar, varchar, text ) RETURNS boolean AS $$
DECLARE
  uuid_arg uuid;
BEGIN
  uuid_arg := $4;
  INSERT INTO history_ ( app_user_name_, table_name_, column_name_, affected_uuid_, old_value_, new_value_, notes_, triggered_ )
  VALUES ( $1, $2, $3, uuid_arg, $5, $6, $7, True );
  RETURN True;
END;

LANGUAGE plpgsql;

Update:
Folks on the Postgres General mailing list taught me another workaround -- casting. The standard CAST command or Postgres' own double colon syntax can be used.

CAST( $4 AS uuid )

$4::uuid

So we can write that VALUES line this way:
  VALUES ( $1, $2, $3, CAST( $4 AS uuid ), $5, $6, $7, True );