Re: Custom Data Type Question

Simon Riggs wrote:
> I'd also love any suggestions as to how we might be able to use a
> similar local-data-cacheing mechanism to work when we specify SQL
> standard FOREIGN KEYs. It would be really cool to say USING LOCAL CACHE
> or some way of avoiding the overhead of all those stored after triggers
> and SPI SELECT statements when we've got checks against tables with only
> a few rows where the values hardly ever change.

Uh, sounds like an enum is a perfect fit. :) This is certainly one of
the use-cases that I've encountered when I wished that I had had an enum
type to use.

> The enum concept departs
> radically from the declarative Referential Integrity concepts that many
> of us are already used to.

I have to challenge this. It's *just another type*. Is using a boolean
type a radical departure from RI just because you're not referencing
some external table with the definitions for true and false in it? After
all, from a functional point of view, booleans are just another
enumerated type.

A major annoyance with SQL has been that it hasn't had a good solution
for this pattern. I've seen any number of broken solutions, from lots of
little mostly-static tables littered all over your data model, to single
big code tables that every other table references, and for which you
need triggers to enforce data integrity because standard RI doesn't
work, to chars and varchars with incorrect ordering or meaningless names
or which suck storage-wise. Don't even get me started on MySQL enums.

The reason that I wanted to do the enum patch was because *all* of those
solutions suck. Requiring a table to represent a small fixed set of
allowable values that a column should take is broken. But because it's
the least ugly solution that we've had using vanilla SQL, it's what
we've used, and dare I suggest that because we've all done it for so
long, we start to think that *not* doing it that way is broken.

Enums, as implemented in the patch, are reasonably efficient, typesafe
and properly ordered. Plus they make your data model look cleaner, your
queries don't need to have lookups anymore and you use less disk space.
Oh, and they also bring you coffee and put out the trash :)

> I'd like to be able to speed things up
> without radical re-design of the database... so a few nicely sprinked
> ALTER TABLE statements would be a much better way of implementing this
> IMHO.

OK, back to what you'd like to do. :)

If your external tables are so small and static, just how long does the
FK check take? Are they really that slow?

I would have thought that it might be difficult to get rid of the FK
check altogether, but perhaps, in the context of a single query (I'm
thinking a bulk insert) you could have some sort of LRU cache. If you
want the cache to stick around, you've got to deal with what happens
when it goes out of date... notifying all the backends etc, and what
happens when one if the other backends was halfway through a
transaction. Maybe you could set this "cached mode" on, but would have
to switch it off before updating the tables in question or something
like that. I dunno. That stuff sounds hard; I found it easier to just
implement my own type ;)

Cheers

Tom