Re: Is a SERIAL column a "black box", or not?

On Mon, May 01, 2006 at 06:43:00PM -0700, elein wrote:
> On Mon, May 01, 2006 at 07:47:06PM -0400, Tom Lane wrote:
> > "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> > > I think a big point that's being missed here is that SERIAL *is* trying
> > > to be simple. If you need something more sophisticated or complex you
> > > shouldn't be using SERIAL at all, you should be doing the stuff
> > > yourself, by hand.
> >
> > I agree with this point in the abstract, but one important proviso is
> > that it has to be *possible* to do it by hand. One good thing about
> > the "SERIAL is just a macro" approach is that it keeps us honest about
> > making sure that SERIAL isn't exploiting any weird internal behaviors
> > that are hard to duplicate for handmade sequence defaults. We've
> > already broken that to some extent by having the hidden dependency,
> > and that in turn means that fairly-reasonable expectations like
> > "pg_get_serial_sequence should find the column's associated sequence"
> > don't work on handmade sequences. I don't want to go much further in
> > that direction. If there's a usability problem we're trying to solve
> > for SERIALs, we should make sure the problem gets solved for handmade
> > sequences too.
> >
> > regards, tom lane
>
> I agree with Tom's proviso and add one of my own, mentioned earlier.
> It should be easy to use a sequence w/alter sequence almost all of
> the time. The majority of the crowd should be able to use SERIAL in
> the majority of cases. One reason I am adamant about this is the
> v. useful dependencies that are (should be) set between the table
> and the sequence when it is declared as a SERIAL.

I agree that we shouldn't be arbitrarily removing functionality from
SERIALs that would exist with a hand-grown sequence unless there's good
reason.

I'm wondering if it would be best to essentially promote SERIALs to
being their own type of object? So instead of relying on a naming
convention or pg_get_serial_sequence to then make calls that touch the
underlying sequence (which probably shouldn't be directly accessible),
create functions/syntax that allows the required operations on a SERIAL
itself, such as table.column.nextval(), or nextval(table.column).

Another way to look at this is how we handle VIEWS. Viwes are
implimented under-the-covers as a rule and some hidden table, yet we
don't support (or even allow?) people mucking with the stuff that's
under the hood. I think it would be best from a user standpoint if we
took the same approach with SERIAL, as long as we provide most of the
power that users would have from going the manual sequence route (I say
most because there's probably some oddball cases that wouldn't make
sense supporting, such as two SERIALS operating off the same sequence).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461