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

On Tue, May 02, 2006 at 12:00:42PM -0500, Jim C. Nasby wrote:
> 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).

This is not what I meant. I meant that most things should be able to be
done by a combination of a SERIAL column definition plus ALTER SERIAL.
But there are other reasons to have sequences as stand alone objects.

And don't get me started on how you cannot create a select rule.
In that case the code to prevent proper use of create rules is probably
as extensive as the code to implement views.

--elein

> --
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>