On 12/05/2011 03:31 PM, Mike Christensen wrote:
That'll get slow. It'll work and is IMO better than all the other options
you suggested, but I'd probably favour hstore over it.
The hstore module sounds fantastic!
I'm curious as to how these columns are serialized back through the
driver, such as Npgsql. Do I get the values as strings, such as a
comma delimited key/value pair list? Or would I need to do some
custom logic to deserialize them?
It depends on what Npgsql supports, really. The server sends hstore values as text; what the client does with them depends on the client. I don't really do C# and .NET so I'm not the one to turn to for advice on that side. Ideally a hstore would be parsed and converted to a hash map by the database driver. At present I don't know of any that do this natively, though I may well be out of date on this. For PgJDBC there's code around (not AFAIK yet integrated into PgJDBC proper) to do it.
In many (most?) cases you'll want to interact with hstore fields using the hstore-provided types and operators, eg.
SELECT somefield SET hstorecol = hstorecol - "somekey";
See:
http://www.postgresql.org/docs/current/static/hstore.html If you're working via some ORM layer (as it sounds like) you may have to use native queries or explain to it about the hstore types and operators. That's the usual problem when trying to use database-specific not-quite-relational features like hstore through a layer that tries to be db-independent and purely relational. I don't have any experience with Castle ActiveRecord. When I've used hstore with hibernate I've always done it by direct native queries.
--
Craig Ringer