Re: Ad-hoc table type?

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

Tom Lane wrote:
> pgsql(at)mohawksoft(dot)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(at)mielke(dot)cc>