On Tue, 2007-03-13 at 00:43, Richard Huxton wrote:
> Josh Berkus wrote:
> > I really don't see any way you could implement UDFs other than EAV that
> > wouldn't be immensely awkward, or result in executing DDL at runtime.
> What's so horrible about DDL at runtime? Obviously, you're only going to
> allow specific additions to specific schemas/tables, but why not?
Well, exclusively locking the table for DDL is not always possible in
production systems. We also shortly had a solution where we added new
columns on the fly, and we had to ditch it... it was simply not working.
The users were usually adding columns in the peek business hours, and in
the same hours it was simply impossible to take an exclusive lock on
that table. I think DDL will actually also exclusively lock parent
tables of FK relationships (I might be mistaken here, but I think I have
seen such lock), which is even worse.
After it caused extensive perceived downtime for hours, we simply
redesigned the feature so that the users need now to announce beforehand
how many different types of new columns they will need and we just
create a few extra of them, and assign them to the users needs as they
need it... the unused columns stay null and hopefully don't have too big
overhead, but it's the simplest solution we found which actually works.
When the user runs out of spare columns, the admin will create some new
spares in quiet hours.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings