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 ?

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