Re: smallserial / serial2 - Mailing list pgsql-hackers

<div class="WordSection1"><p class="MsoPlainText">Hey Tom,<p class="MsoPlainText"> <p class="MsoPlainText">I'm sure
thereare plenty of useful tables with <= 32k rows in them? I have a table for customers that uses a smallint (since
thecustomer id is referenced all over the place)- due to the nature of our product, we’re never going to have more than
32kcustomers, but I still want the benefit of the sequence.<p class="MsoPlainText"> <p class="MsoPlainText">And since
serial4and serial8 are simply pseudo-types- effectively there for convenience, I’d argue that it should simply be there
forcompleteness- just because it may be less used, doesn’t mean it shouldn’t be convenient?<p class="MsoPlainText"> <p
class="MsoPlainText">Also,in another case, I’m using it in a small table used to constrain a bigger table- eg:<p
class="MsoPlainText"> <pclass="MsoPlainText"><span style="font-size:9.0pt;font-family:"Lucida Console"">create table
stuff(</span><p class="MsoPlainText"><span style="font-size:9.0pt;font-family:"Lucida Console"">       id serial2
unique</span><pclass="MsoPlainText"><span style="font-size:9.0pt;font-family:"Lucida Console"">);</span><p
class="MsoPlainText"><spanstyle="font-size:9.0pt;font-family:"Lucida Console""> </span><p class="MsoPlainText"><span
style="font-size:9.0pt;font-family:"LucidaConsole"">create table data (</span><p class="MsoPlainText"><span
style="font-size:9.0pt;font-family:"LucidaConsole"">       id serial8 unique,</span><p class="MsoPlainText"><span
style="font-size:9.0pt;font-family:"LucidaConsole"">       stuff smallint not null,</span><p class="MsoPlainText"><span
style="font-size:9.0pt;font-family:"LucidaConsole"">       foreign key(stuff) references stuff(id) on update cascade on
deleterestrict</span><p class="MsoPlainText"><span style="font-size:9.0pt;font-family:"Lucida Console"">);</span><p
class="MsoPlainText"> <pclass="MsoPlainText">Where our “data” table has ~700 million rows right now.<p
class="MsoPlainText"> <pclass="MsoPlainText">And yes- I guess there's nothing to stop me from using a smallint in the
datatable (thus getting the size savings), and reference a int in the stuff table, but it seems like bad form to me to
havea foreign key constraint between two different types.<p class="MsoPlainText"> <p class="MsoPlainText">Mike<p
class="MsoPlainText"> <pclass="MsoPlainText">-----Original Message-----<br />From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
<br/>Sent: Thursday, April 21, 2011 10:26 AM<br />To: Mike Pultz<br />Cc: pgsql-hackers@postgresql.org<br />Subject:
Re:[HACKERS] smallserial / serial2 <p class="MsoPlainText"> <p class="MsoPlainText">"Mike Pultz" <<a
href="mailto:mike@mikepultz.com"><spanstyle="color:windowtext;text-decoration:none">mike@mikepultz.com</span></a>>
writes:<pclass="MsoPlainText">> I use tables all the time that have sequences on smallint's; I'd like <p
class="MsoPlainText">>to simplify my create files by not having to create the sequence <p class="MsoPlainText">>
first,but I also don't want to give up those 2 bytes per column!<p class="MsoPlainText"> <p class="MsoPlainText">A
sequencethat can only go to 32K doesn't seem all that generally useful ...<p class="MsoPlainText"> <p
class="MsoPlainText">Areyou certain that you're really saving anything?  More likely than not, the "saved" 2 bytes are
goingto disappear into alignment padding of a later column or of the whole tuple.  Even if it really does help for your
case,that's another reason to doubt that it's generally useful.<p class="MsoPlainText"> <p
class="MsoPlainText">                                               regards, tom lane</div>