Re: Ad-hoc table type?

Not that I'm agreeing with the direction but just as a thinking experiment:<br /><br /> Tom Lane wrote: <blockquote
cite="mid:22562.1222649186@sss.pgh.pa.us"type="cite"><pre wrap=""><a class="moz-txt-link-abbreviated"
href="mailto:pgsql@mohawksoft.com">pgsql@mohawksoft.com</a>writes: </pre><blockquote type="cite"><pre wrap="">Being
ableto 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?   </pre></blockquote><pre wrap="">
I'm failing to see the point.  Allowing columns to spring into existence
without any forethought seems to me to be all minuses and no pluses
worth mentioning.

* What if the column name is just a typo? </pre></blockquote><br /> If it's a field in a data structure from a language
suchas Java, it's not a typo.<br /><br /><blockquote cite="mid:22562.1222649186@sss.pgh.pa.us" type="cite"><pre
wrap="">*What datatype should it have?  ("Always varchar" is just lame.) </pre></blockquote><br /> SQLite uses "always
varchar"and it doesn't seem to be a problem. For simpler numbers like "0", the text form can be more compact, and the
databasemay be portable across different hardware architectures.<br /><br /><blockquote
cite="mid:22562.1222649186@sss.pgh.pa.us"type="cite"><pre wrap="">* Should it have an index?  If so, should it be
unique?</pre></blockquote><br /> It might be cool for indexes to automatically appear as they become beneficial (and
removedas they become problematic). Unique is a constraint which should be considered separate from whether it should
bean index or not. I don't know if it would be useful or not.<br /><br /><blockquote
cite="mid:22562.1222649186@sss.pgh.pa.us"type="cite"><pre wrap="">* If you keep doing this, you'll soon find yourself
readingout
 
unbelievably wide tables (lots of columns), which won't be especially
easy or efficient to process on either the backend or the client side.
Plus you might run into the max-columns-per-tuple limit. </pre></blockquote><br /> Introduce variable field-order for
tuples?Only provide values if non-null? :-)<br /><br /><blockquote cite="mid:22562.1222649186@sss.pgh.pa.us"
type="cite"><prewrap="">If you've expended enough thought to be sure that the column is not just
 
a typo, ISTM that you can afford to enter an ALTER TABLE ADD COLUMN
command to tell the database the results of your genius.

I do see the point that switching from "member of an hstore column" to
"real database column" is pretty painful, but I don't see that "allow
columns to spring into existence" solves that in any meaningful way.
Is there some other way we could address such conversions?

BTW, I think it is (or should be) possible to create an index on
hstore->'mycol', so at least one of the reasons why you should *need*
to switch to a "real" database column seems bogus. </pre></blockquote><br /> I find the Oracle nested table and data
structuresupport enticing although I do not have experience with it. It seems like it might be a more mature
implementationof hstore? If hstore had everything that was required in terms of performance or flexibility, we wouldn't
needfixed columns at all?<br /><br /> But yes - I tend to agree that the object persistent layer can be hidden away
behindsomething like the Java object persistence model, automatically doing alter table or providing a configured
mappingfrom a description file. This isn't a problem that needs to be solved at the database layer.<br /><br />
Cheers,<br/> mark<br /><br /><pre class="moz-signature" cols="72">-- 
 
Mark Mielke <a class="moz-txt-link-rfc2396E" href="mailto:mark@mielke.cc"><mark@mielke.cc></a>
</pre>