Re: Ad-hoc table type?

> What you're talking about is a document based database like
> StrokeDB, CouchDB. With hstore you don't need to parse content of
> 'aggregate' column, it provides necessary methods. Also, we tried
> to speedup selects using indexes. Probably, we need to refresh our
> interest to hstore, do you have any actual proposals ?

Proposals, not at this point. I'm trying to decide (a) if I have the time
and (b) do I do it with Postgres or SQLite. The hstore module, as I said,
looks really cool, I've contemplated something like it. I have a module
provides a set of accessors for an XML text column that works similarly,
but it parses the XML on each access and the application has to create the
XML. (I have XML creation modules for Java, PHP, C++, and standard C
bindings.)

It is more a conflict of data ideology, IMHO. There is a class of data
that is logically on the same level as other data, but is forced into a
secondary storage methodology. It isn't a pressing need as there are work
arounds, but don't you think a cleaner interface make sense? Also, what is
the overhead for the secondary storage mechanism? I think it would make
the life of application developers easier.

>
> Oleg
>
> On Sun, 28 Sep 2008, pgsql(at)mohawksoft(dot)com wrote:
>
>>> pgsql(at)mohawksoft(dot)com writes:
>>>> Something like this:
>>>
>>>> create adhoc table foo ();
>>>
>>>> insert into foo (name, rank, serial) values ('joe', 'sargent', '42');
>>>
>>>> In an "ad-hoc" table type, when an insert is made, and a column is not
>>>> found, then a new varchar column is added.
>>>
>>>> I know the idea has a lot of holes, and is probably a bad idea, but it
>>>> answers an important problem of easily mapping programmatic types to a
>>>> database.
>>>
>>> Seems like a table with one contrib/hstore column might be more
>>> relevant
>>> to this guy's idea of how to do database design.
>>>
>>
>> That's actually a very cool module, I hadn't seen it before. I've
>> considered writing something like it, but more XML centric, but I'm not
>> sure it answers the concept.
>>
>> I'm not sure if you have dealt with web site sessions and object
>> persistence crap, but its a pain to get up and running and improving
>> performance is a drag. Web guys tend to know very little about databases
>> and tend, sadly, not to be very inquisitive about such things.
>>
>> Web session and user attribute objects are typically stored in a
>> database
>> as XML, JSON, or some other aggregated format in a single column
>> (hstore).
>> That works great for when you just need to access the data by the key,
>> but
>> if you want to "use" the data outside the web application for something
>> like OLAP, you have to decide which attributes reside in the aggregate
>> column or get promoted to a full fledged column. That's why you'll see
>> tables with username, passwdhash, email, etc. in addition to an
>> aggregated
>> column of things like screen template, age, etc.
>>
>> So, how do you have a table of a generally arbitrary number of columns
>> without creating some sort of aggregate column? With an aggregate
>> column,
>> the data isn't on the same level as real column data, so you need to
>> parse
>> the aggregate to extract a value, and you have to do that for each
>> value.
>> On top of that, you then have to explain your aggregate strategy to the
>> web guys.
>>
>> Being able to insert arbitrary named values, and extracting them
>> similarly, IMHO works "better" and more naturally than some external
>> aggregate system built on a column. I know it is a little "outside the
>> box" thinking, what do you think?
>>
>>
>
> Regards,
> Oleg
> _____________________________________________________________
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>