Re: [HACKERS] smallserial / serial2
Yup- it's attached. Mike From: Brar Piening [mailto:b...@gmx.de] Sent: Tuesday, June 07, 2011 6:58 PM To: Mike Pultz Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] smallserial / serial2 On Wed, 20 Apr 2011 21:27:27 -0400, Mike Pultz mailto:m...@mikepultz.com m...@mikepultz.com wrote: Can this be added? Probably not - since it's not a complete patch ;-) I tried to test this one but was unable to find a complete version of the patch in my local mail archives and in the official archives (http://archives.postgresql.org/message-id/023001cbffc3$46f77840$d4e668c0$@m ikepultz.com) Could you please repost it for testing? Regards, Brar 20110607_serial2.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] smallserial / serial2
Sorry, forgot the documentation- I guess that stuff doesn't magically happen! New patch attached. Mike From: Brar Piening [mailto:b...@gmx.de] Sent: Tuesday, June 07, 2011 6:58 PM To: Mike Pultz Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] smallserial / serial2 On Wed, 20 Apr 2011 21:27:27 -0400, Mike Pultz mailto:m...@mikepultz.com m...@mikepultz.com wrote: Can this be added? Probably not - since it's not a complete patch ;-) I tried to test this one but was unable to find a complete version of the patch in my local mail archives and in the official archives (http://archives.postgresql.org/message-id/023001cbffc3$46f77840$d4e668c0$@m ikepultz.com) Could you please repost it for testing? Regards, Brar 20110607_serial2_v2.diff Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] smallserial / serial2
Hey Tom, I'm sure there are plenty of useful tables with = 32k rows in them? I have a table for customers that uses a smallint (since the customer id is referenced all over the place)- due to the nature of our product, we're never going to have more than 32k customers, but I still want the benefit of the sequence. And since serial4 and serial8 are simply pseudo-types- effectively there for convenience, I'd argue that it should simply be there for completeness- just because it may be less used, doesn't mean it shouldn't be convenient? Also, in another case, I'm using it in a small table used to constrain a bigger table- eg: create table stuff ( id serial2 unique ); create table data ( id serial8 unique, stuff smallint not null, foreign key(stuff) references stuff(id) on update cascade on delete restrict ); Where our data table has ~700 million rows right now. And yes- I guess there's nothing to stop me from using a smallint in the data table (thus getting the size savings), and reference a int in the stuff table, but it seems like bad form to me to have a foreign key constraint between two different types. Mike -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, April 21, 2011 10:26 AM To: Mike Pultz Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] smallserial / serial2 Mike Pultz mailto:m...@mikepultz.com m...@mikepultz.com writes: I use tables all the time that have sequences on smallint's; I'd like to simplify my create files by not having to create the sequence first, but I also don't want to give up those 2 bytes per column! A sequence that can only go to 32K doesn't seem all that generally useful ... Are you certain that you're really saving anything? More likely than not, the saved 2 bytes are going to 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. regards, tom lane
[HACKERS] smallserial / serial2
I use tables all the time that have sequences on smallint's; I'd like to simplify my create files by not having to create the sequence first, but I also don't want to give up those 2 bytes per column! Can this be added? Mike --- postgresql-9.0.4/src/backend/parser/parse_utilcmd.c 2011-04-14 23:15:53.0 -0400 +++ postgresql-9.0.4.new/src/backend/parser/parse_utilcmd.c 2011-04-20 21:10:26.0 -0400 @@ -280,8 +280,15 @@ { char *typname = strVal(linitial(column-typeName-names)); - if (strcmp(typname, serial) == 0 || - strcmp(typname, serial4) == 0) + if (strcmp(typname, smallserial) == 0 || + strcmp(typname, serial2) == 0) + { + is_serial = true; + column-typeName-names = NIL; + column-typeName-typeOid = INT2OID; + } + else if (strcmp(typname, serial) == 0 || +strcmp(typname, serial4) == 0) { is_serial = true; column-typeName-names = NIL;