Re: [HACKERS] Ad-hoc table type?

Not that I'm agreeing with the direction but just as a thinking experiment:

Tom Lane wrote:

pgsql@mohawksoft.com writes:   

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?     

 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?   

If it's a field in a data structure from a language such as Java, it's not a typo.

* What datatype should it have?  ("Always varchar" is just lame.)   

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 database may be portable across different hardware architectures.

* Should it have an index?  If so, should it be unique?   

It might be cool for indexes to automatically appear as they become beneficial (and removed as they become problematic). Unique is a constraint which should be considered separate from whether it should be an index or not. I don't know if it would be useful or not.

* If you keep doing this, you'll soon find yourself reading out 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.   

Introduce variable field-order for tuples? Only provide values if non-null? :-)

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.   

I find the Oracle nested table and data structure support enticing although I do not have experience with it. It seems like it might be a more mature implementation of hstore? If hstore had everything that was required in terms of performance or flexibility, we wouldn't need fixed columns at all?

But yes - I tend to agree that the object persistent layer can be hidden away behind something like the Java object persistence model, automatically doing alter table or providing a configured mapping from a description file. This isn't a problem that needs to be solved at the database layer.

Cheers,
mark

--  Mark Mielke <mark@mielke.cc>