Обсуждение: Re: [HACKERS] Sequences....
I've finished that patch for the serial->sequences but I am not happy with it. I don't think belive my proposed idea is the correct approach. I still want to work on this idea, but I'm not sure how to proceed. Here are my thoughts: 1. If I use my proposed idea, everything works great until you dump/reload the table. The dump doesn't store the data type as serial, but as int with a default and a sequence. So when the table gets reloaded, the relationship nolonger exists. (I think I finally understand the dump issues now Tom :)) 2. Tom suggested reference counting the seqence in one of his email's (I know it was for this purpose, but I still liked the idea). I thought about this for a while, and concluded that this is probably not the correct solution since the sequence can be accessed from something else besides the default values. 3. Vadim pointed out that sequences and tables are really seperate entities and now that I understand them better, I agree with him. The serial type is not really a type, but a shortcut to create a int4 data type, a key, and a sequence in one command. I have two current thoughts on how to proceed from here... I want to toss them out, get some more feedback ... rediscover that I haven't really thought them out well enought and do this all over again :) 1. Leave it as it is now. It works, just explain to people that sequences and tables are seperate entities, and the serial type is just a shortcut. 2. Create a new data type serial. I haven't thought idea out much, and need to investigate it some more. I'm thinking it would be binary equivilent with the int4 type, and use most of the existing seqence code, but not put the seqence name entry in the pg_class system table. Does this sound like a feasible idea? Thanks for your input. -Ryan
Wow. Serious effort here. Ryan Bradetich wrote: > 1. Leave it as it is now. It works, just explain to > people that sequences and tables are seperate entities, > and the serial type is just a shortcut. I dislike this approach. It seems that it is hiding detail that is necessary for proper maintence. It isn't that hard to type in the code. IMHO, the shortcut causes more confusion that it helps. So, I propose a third option: 0. Remove the SERIAL construct. > 2. Create a new data type serial. I haven't thought idea > out much, and need to investigate it some more. I'm thinking > it would be binary equivilent with the int4 type, and use > most of the existing seqence code, but not put the seqence > name entry in the pg_class system table. Does this sound > like a feasible idea? This dosn't sound all that bad... but I really wonder what the advantage is. I vote for "0". Sorry to dissappoint. Clark
Ryan Bradetich wrote: > > I've finished that patch for the serial->sequences but I am not happy with it. > > I don't think belive my proposed idea is the correct approach. I still want to work on this > idea, but I'm not sure how to proceed. > > Here are my thoughts: > > 1. If I use my proposed idea, everything works great until you dump/reload the table. The dump > doesn't store the data type as serial, but as int with a default and a sequence. So when the > table gets reloaded, the relationship nolonger exists. (I think I finally understand the dump > issues now Tom :)) Add attnum - attribute number of SERIAL column in table - to new relation: using this pg_dump will know what columns are SERIAL ones and what are not... Vadim
Thus spake Clark Evans
> 0. Remove the SERIAL construct.
Ack! No! Once we ship a feature I think we better be very careful
about dropping it.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
Thus spake Ryan Bradetich
> 2. Create a new data type serial. I haven't thought idea out much, and need to investigate it
> some more. I'm thinking it would be binary equivilent with the int4 type, and use most of the
> existing seqence code, but not put the seqence name entry in the pg_class system table. Does
> this sound like a feasible idea?
I like it. I always wondered why serial wasn't a real datatype. I never
wondered out loud for fear of finding myself with a new project. :-)
If we are thinking about it, I wonder if we could enhance it somewhat.
Under the current system, the attribute values and underlying method
are divorced but if we make serial a first class type then we can look
at the values, perhaps, when getting the next number. For example, if
we add a row and specify a number, the system can note that and skip
that number later. That would certainly fix the dump/restore problem.
Alternatively, maybe we can enforce the serialism of the type. Even
if the user specifies a value, ignore it and put the next number in
anyway. Of course, that just brings us back to the dump/restore
problem so...
Do as above but allow the user to specify a number as long as it is
available and is lower than the next number in the series. When
the restore happens, you need to set the start value to the previous
next value then the inserts can restore with all the old values. You
can even safely insert new records while the restore is happening.
If we decide to leave things more or less as they are, how about a new
flag for sequences and indexes that sets a row as system generated
rather than user specified? We can then set that field when a sequence
or index is generated by the system such as for the serial type or
primary keys. Dump could then be written to ignore these rows on
output. That would deal with the current issue.
Just some random thought from someone who woke up too early.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
> Wow. Serious effort here. > > Ryan Bradetich wrote: > > 1. Leave it as it is now. It works, just explain to > > people that sequences and tables are seperate entities, > > and the serial type is just a shortcut. > > I dislike this approach. It seems that it is hiding detail > that is necessary for proper maintence. It isn't that > hard to type in the code. IMHO, the shortcut causes more > confusion that it helps. So, I propose a third option: > > 0. Remove the SERIAL construct. When they create a serial, we tell them: ltest=> create table testx(x serial);NOTICE: CREATE TABLE will create implicit sequence testx_x_seq forSERIAL column testx.xNOTICE: CREATE TABLE/UNIQUE will create implicit index testx_x_key fortable testxCREATE So it is not so terrible to tell them they have to delete it when finished. We could also add a column to pg_class which tells us this sequence was auto-created from oid 12, and remove it when we delete a table. Or, we could name just try to delete a sequence that has the name of the table with _seq at the end. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
"D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> Thus spake Ryan Bradetich
>> 2. Create a new data type serial. I haven't thought idea out much,
>> and need to investigate it some more. I'm thinking it would be binary
>> equivilent with the int4 type, and use most of the existing seqence
>> code, but not put the seqence name entry in the pg_class system
>> table. Does this sound like a feasible idea?
> I like it.
A binary-equivalent type does seem like a handier way to represent
SERIAL than what we are doing. You still need a way to find the
associated sequence object, though, so a table mapping from
table-and-column to sequence OID is still necessary. (Unless we
were to use the atttypmod field for the column to hold the sequence
object's OID? Seems a tad fragile though, since there's no way to
update an atttypmod field in an existing table.)
I don't like the idea of not putting the sequence name into pg_class.
That would mean that the sequence object is not directly accessible
for housekeeping operations. If you do that, you'd have to invent
all-new ways to do the following:* currval, setval, nextval (yes there are scenarios where a direct nextval on the
sequenceis useful)* dump and reload the sequence in pg_dump
> Alternatively, maybe we can enforce the serialism of the type. Even
> if the user specifies a value, ignore it and put the next number in
> anyway.
I don't like that at *all*.
> Do as above but allow the user to specify a number as long as it is
> available and is lower than the next number in the series.
I think better would be that the sequence value is silently forced to
be at least as large as the inserted number, whenever a specific number
is inserted into a SERIAL field. That would ensure we never generate
duplicates, but not require keeping any extra state.
> If we decide to leave things more or less as they are, how about a new
> flag for sequences and indexes that sets a row as system generated
> rather than user specified? We can then set that field when a sequence
> or index is generated by the system such as for the serial type or
> primary keys.
Yes, it'd be nice to think about fixing up primary-key implicit indexes
while we are at it --- they have some of the same problems as SERIAL ...
regards, tom lane
> "D'Arcy" "J.M." Cain <darcy@druid.net> writes: > > Thus spake Ryan Bradetich > >> 2. Create a new data type serial. I haven't thought idea out much, > >> and need to investigate it some more. I'm thinking it would be binary > >> equivilent with the int4 type, and use most of the existing seqence > >> code, but not put the seqence name entry in the pg_class system > >> table. Does this sound like a feasible idea? > > > I like it. > > A binary-equivalent type does seem like a handier way to represent > SERIAL than what we are doing. You still need a way to find the > associated sequence object, though, so a table mapping from > table-and-column to sequence OID is still necessary. (Unless we > were to use the atttypmod field for the column to hold the sequence > object's OID? Seems a tad fragile though, since there's no way to > update an atttypmod field in an existing table.) atttypmod seems like a perfect idea. We also need a unique type for large objects, so oid's and large objects can be distinguished. We could do both at the same time, and with Thomas's new type coersion stuff, we don't need to create tons of functions for each new type. > > I don't like the idea of not putting the sequence name into pg_class. > That would mean that the sequence object is not directly accessible > for housekeeping operations. If you do that, you'd have to invent > all-new ways to do the following: > * currval, setval, nextval (yes there are scenarios where a > direct nextval on the sequence is useful) > * dump and reload the sequence in pg_dump Yes, let's keep it in pg_class. No reason not to. > > If we decide to leave things more or less as they are, how about a new > > flag for sequences and indexes that sets a row as system generated > > rather than user specified? We can then set that field when a sequence > > or index is generated by the system such as for the serial type or > > primary keys. > > Yes, it'd be nice to think about fixing up primary-key implicit indexes > while we are at it --- they have some of the same problems as SERIAL ... My guess is that 6.5 is too close to be making such sweeping changes, though the pg_dump problems with SERIAL certainly make this an important issue. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Sorry for the igorance, but I'm not quite understanding. Assuming a new SERIAL type is made. What would be the difference between the new type and an OID? Thanks! Clark
> Sorry for the igorance, but I'm not quite > understanding. Assuming a new SERIAL type > is made. What would be the difference > between the new type and an OID? It would only mark the the column as an OID that is used for Serial. The same thing with large objects, so it is an oid, and used for large objects. It allows pg_dump and other programs to understand the use of the oid. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > It would only mark the the column as an OID that is used for Serial. > The same thing with large objects, so it is an oid, and used for large > objects. It allows pg_dump and other programs to understand the use of > the oid. So, the serial column and the OID column would be one and the same? Why is there a sequence problem then? Clark
> Bruce Momjian wrote: > > It would only mark the the column as an OID that is used for Serial. > > The same thing with large objects, so it is an oid, and used for large > > objects. It allows pg_dump and other programs to understand the use of > > the oid. > > So, the serial column and the OID column would be one and > the same? Why is there a sequence problem then? It just marks the oid column as being a serial column. We also have atttypmod, and could easily use that for marking oid's used for serial, and those used for large objects. Would be nice. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> [ several of us like making SERIAL a new data type ]
> My guess is that 6.5 is too close to be making such sweeping changes,
I agree, we should probably not expect to squeeze such a change in for
6.5.
Although we've been hand-waving about how this could be done, I think
it would require either ugly hackery or some nontrivial extensions to
the system. AFAIR, for example, there is no data-type-specific code
that gets executed when NULL is assigned to a column, therefore no
easy way for a SERIAL data type to get control and substitute a suitable
default value. Probably we'd end up still having to use a "DEFAULT"
clause to make that happen. It seems to need some thought, anyway.
regards, tom lane
Clark Evans <clark.evans@manhattanproject.com> writes:
> Sorry for the igorance, but I'm not quite
> understanding. Assuming a new SERIAL type
> is made. What would be the difference
> between the new type and an OID?
The new type would have an identifying OID, namely the OID assigned
to its row in pg_type. This OID would be the data type indicator for
all SERIAL columns.
However, for each SERIAL column there would need to be a sequence
object, and this sequence object would have its *own* unique OID
(the OID assigned to its row in pg_class, IIRC).
To manipulate a SERIAL column you need to be able to look up the OID
of its sequence, so that you can do things to the sequence. I suggested
that storing a copy of the sequence's OID in the column's atttypmod
field would be worthwhile, because it could be accessed directly when
working on the table containing the SERIAL column, without having to do
a lookup in a system table.
I think it'd still be a good idea to have a system table containing the
mapping from SERIAL columns to (OIDs of) their associated sequences.
The atttypmod idea is just a trick to bypass having to do lookups in
this table for the most common operations on a SERIAL column.
regards, tom lane
> > I think it'd still be a good idea to have a system table containing the > mapping from SERIAL columns to (OIDs of) their associated sequences. > The atttypmod idea is just a trick to bypass having to do lookups in > this table for the most common operations on a SERIAL column. But what use would a new system table be, if the atttypmod can do it for us? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> I think it'd still be a good idea to have a system table containing the
>> mapping from SERIAL columns to (OIDs of) their associated sequences.
>> The atttypmod idea is just a trick to bypass having to do lookups in
>> this table for the most common operations on a SERIAL column.
> But what use would a new system table be, if the atttypmod can do it for
> us?
To handle the less common cases --- in particular, the reverse lookup:
given a sequence, is it the implementation of a SERIAL column somewhere?
(DROP SEQUENCE ought to refuse to drop it if so, I think.)
Also, assuming that we want inherited tables to share the parent's
sequence, I suspect there are cases where you need to be able to
find all the tables sharing a given sequence. This would be rather
difficult if the only representation was atttypmod fields. (You
could probably work out something reasonably efficient based on
the assumption that all the tables involved must be related by
inheritance --- but it wouldn't be as easy as a single SELECT,
and it *could not* be done in pure SQL because atttypmod isn't
an SQL concept.)
Basically I think that all this structural information ought to be
explicitly represented in regular SQL data structures where you can
manipulate it (SELECT on it and so forth). We can use atttypmod as an
internal-to-the-implementation cache to avoid the most common lookup
that we'd otherwise need, but it'd be a design mistake not to have the
information represented in a more conventional form.
It might help to compare this issue to index and inheritance
relationships. We have explicit representations in the system tables
of the inherits-from and is-an-index-of relationships; if we did not,
many tasks would become much harder. The backend plays some tricks
internally to avoid constantly having to do fullblown lookups in those
tables, but the tables need to be there anyway. I say we need to add
an explicit representation of the is-sequence-for-SERIAL relationship
for the same reasons, even if we can install an internal shortcut
that's used by some backend operations.
regards, tom lane
Thus spake Tom Lane
> "D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> > Alternatively, maybe we can enforce the serialism of the type. Even
> > if the user specifies a value, ignore it and put the next number in
> > anyway.
>
> I don't like that at *all*.
I'm not entirely crazy about it myself. I included it as an option because
it seemed to follow from the definition of serial number. However, in
practice I imagine that people would find it overly restrictive.
> > Do as above but allow the user to specify a number as long as it is
> > available and is lower than the next number in the series.
>
> I think better would be that the sequence value is silently forced to
> be at least as large as the inserted number, whenever a specific number
> is inserted into a SERIAL field. That would ensure we never generate
> duplicates, but not require keeping any extra state.
I see your point but that could cause problems if you start your sequence
too high. I guess the answer to that is, "Don't do that."
Hmm. Are you suggesting that if I insert a number higher than the next
sequence that the intervening numbers are never available?
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.
"D'Arcy J.M. Cain" wrote:
> Thus spake Tom Lane
> > "D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> > > Alternatively, maybe we can enforce the serialism of the type. Even
> > > if the user specifies a value, ignore it and put the next number in
> > > anyway.
> > I don't like that at *all*.
> I'm not entirely crazy about it myself. I included it as an option because
> it seemed to follow from the definition of serial number. However, in
> practice I imagine that people would find it overly restrictive.
>
Well, I'd do it a little different. If a sequence is bound
to a column, and the user provides a value, throw an error!
This is what I did in Oracle when I implemented system
assigned keys in a large project that I worked on. For
normal operations, this is the way you want it. Any other
way will be a nightmare! (I added the trigger to find the
client application that was being .. let's say .. very bad)
Now... for table loading, you have a different issue:
"D'Arcy J.M. Cain" wrote:
> > > Do as above but allow the user to specify a number as long as it is
> > > available and is lower than the next number in the series.
> > I think better would be that the sequence value is silently forced to
> > be at least as large as the inserted number, whenever a specific number
> > is inserted into a SERIAL field. That would ensure we never generate
> > duplicates, but not require keeping any extra state.
>
> I see your point but that could cause problems if you start your sequence
> too high. I guess the answer to that is, "Don't do that."
>
> Hmm. Are you suggesting that if I insert a number higher than the next
> sequence that the intervening numbers are never available?
If you are loading a table with records that are out of sequence,
then there is a manual issue involved.
Perhaps what is needed is a "bind" function:
FUNCTION bind( TABLE, COLUMN, SEQUENCE ) RETURNS OLD_SEQUENCE;
This procedure binds a table, column to auto-populatewith a given sequence. It returns the old sequence(possibly
null)associated with the TABLE/COLUMN. The column, of course, must be an INT4 'compatible' type,and the SEQUENCE cannot
bebound to any other TABLE/COLUMN,Also, the max(COLUMN) > curval(SEQUENCE) If any of the conditions are false, then the
BINDthrowsan error, i.e., don't force the BIND to work.Bind, of course, could use atttypmod field in pg_attributes.
If a sequence is associated with the TABLE/COLUMN duringdump, then DUMP will automatically treat them together as a
singleunit. If the column appears in an INSERTor an UPDATE, and the bound sequence is not null, thenan error is
issued. Likewise, if nextval('sequence') iscalled on a bound sequence, then an error is issued.
unbind(TABLE,COLUMN) is short for bind(TABLE,COLUMN,NULL);"CREATE TABLE x ( y SERIAL );" becomes short for "CREATE
TABLE x ( y INT4 ); CREATE SEQUENCE xys; BIND(x,y,xys);"
This gives you the best of both worlds. If you want to treat
the sequence, and table/column seperately, unbind them. Otherwise,
you may bind them together. So, if you are going to manually
mess with the column, then you must UNBIND the sequence,
do your altercations, and then REBIND the sequence back
to the table.
Thoughts?
Clark
Caution: Random Thoughts & Trigger Tangent This whole discussion got me to thinking about triggers. Are we making, in this case, a specialized trigger that populates a table column from a sequence on insert? Perhaps it may be instructive to look at the general case for enlightenment. Aside, I really don't like Oracle's trigger concept: "CREATE TRIGGER xxx ON INSERT OF tablename AS" I'd rather see the trigger object as a stand alone block of code that is "bound" to one or more tables. Thus, the above, would be a short hand for: "CREATE TRIGGER xxx AS .... ; BIND xxx TO tablename ON INSERT;" Now.. if you wanted to _way_ generalize this... You can think of "INSERT/UPDATE/DELETE" as mutating actions taken on a table object. What mutating actions does a sequence object have? NEXTVAL So... perhaps the trigger concept could be extended past tables but onto any object that has mutating actions? (you have to excuse the lack of rule system knowledge here) And... if you want go further into the muck, perhaps we could have triggers that have a binding with more than one object.. > FUNCTION bind( TABLE, COLUMN, SEQUENCE ) RETURNS OLD_SEQUENCE; Becomes, FUNCTION bind( SEQUENCE_TRIGGER, TABLE, COLUMN, SEQUENCE ) RETURNS OLD_SEQUENCE; Hmm. Oh well I thought I was going somewhere.... better re-name this a tangent. :) Clark
"D'Arcy" "J.M." Cain <darcy@druid.net> writes:
>> I think better would be that the sequence value is silently forced to
>> be at least as large as the inserted number, whenever a specific number
>> is inserted into a SERIAL field. That would ensure we never generate
>> duplicates, but not require keeping any extra state.
> Hmm. Are you suggesting that if I insert a number higher than the next
> sequence that the intervening numbers are never available?
Right. Seems to me that the cost of keeping track of "holes" in the
assignment sequence would vastly exceed the value of not wasting any
sequence numbers. (Unless you have some brilliant idea for how to do
it with a minimal amount of storage?)
Also, the major real use for loading specific values into a SERIAL
column is for a database dump and reload, where you need to be able
to preserve the original serial number assignments. In this situation,
trying to keep track of "holes" would be counterproductive for two reasons:
1. During the incoming COPY we'd very likely not see the tuples in their original order of creation; so a lot of
cycleswould be wasted keeping track of apparent holes that would get filled in shortly later.
2. After we're done loading, any remaining gaps in the usage of serial numbers very likely reflect tuples that once
existedand were deleted. If we re-use those serial values, we may do fatal damage to the application's logic,
sincewe have then violated the fundamental guarantee of a SERIAL column: never generate any duplicate serial
numbers.
You could get around problem #2 if the extra state needed to keep track
of holes could itself be saved and reloaded by pg_dump. But this is
getting way past the point of being an attractive alternative, and the
implementation no longer looks very much like a SEQUENCE object...
regards, tom lane