Thread: Column name's length
Hi, Is there anyway you can increase the length of a column's name? The length for one of my column is 21 characters and the server choke on that when i'm trying to create the table. I'm using version 6.4.2. Below is the actual error message i got: postgres=> create table timeclockdepartment ( postgres-> timeclockdepartmentid serial , postgres-> name varchar (50) ); pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally beforeor while pr ocessing the request. We have lost the connection to the backend, so further processing is impossible. Terminating. I know i could have made the name much shorter, but this program was written way before i was here and for Microsoft SQL server and i'm now trying to move it to postgresql and encounter this problem. Thank you very much for any reply. ----------------- Thinh Pham IS Manager Priority EMS Inc. mailto:tpham@priority.net
[Charset iso-8859-1 unsupported, filtering to ASCII...] > Hi, > > Is there anyway you can increase the length of a column's name? The length > for one of my column is 21 characters and the server choke on that when i'm > trying to create the table. I'm using version 6.4.2. Below is the actual > error message i got: > > postgres=> create table timeclockdepartment ( > postgres-> timeclockdepartmentid serial , > postgres-> name varchar (50) ); > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally before or > while pr > ocessing the request. > We have lost the connection to the backend, so further processing is > impossible. > Terminating. The 6.5beta error message of the same statement is:test=> create table timeclockdepartment (test-> timeclockdepartmentidserial ,name varchar (50) );ERROR: CREATE TABLE/SERIAL implicit sequence name must be less than 32characters Sum of lengths of 'timeclockdepartment' and'timeclockdepartmentid' must be less than 27 Serial creates it's own table, that is the sum of the table name and column name. We have not figured out a workaround for this. -- 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
I recompiled my server with support for longer (128 character max) table names. (This was a bit tricky because you have to change things in two places.) 32 character limits are rather low. 64 or 128 are more reasonable. (Yes, I know this is controversial. The problem I was having is analogous to the SERIAL problem: table names are made by concatenating together a few fields. Each of thee fields can reasonably be 15 or 20 characters. So while 32 characters is fine for a single human generated identifier, it is not long enough for other purposes.) -Z-
> I recompiled my server with support for longer (128 character max) table > names. (This was a bit tricky because you have to change things in two > places.) 32 character limits are rather low. 64 or 128 are more reasonable. > (Yes, I know this is controversial. The problem I was having is analogous > to the SERIAL problem: table names are made by concatenating together a few > fields. Each of thee fields can reasonably be 15 or 20 characters. So while > 32 characters is fine for a single human generated identifier, it is not > long enough for other purposes.) And I assume it is working fine? -- 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
> And I assume it is working fine? Yes. It fixes the column length too I think. The following is a psql interaction: ----- ssmldb=> create table more_than_64_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb (more_than_32_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb boolean ) ; CREATE ssmldb=> select * from more_than_64_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb ; more_than_32_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb --------------------------------------------- (0 rows) ssmldb=> insert into more_than_64_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb ( more_than_32_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb ) values ( true ) ; INSERT 25353 1 ssmldb=> select * from more_than_64_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb ; ^Mmore_than_32_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb --------------------------------------------- t (1 row) ----- (I did some tests to make sure all the characters are significant. They are.) Here are the two diffs that up the "name size" from 32 characters to 256 characters. (Once I get bit, I try to fix things real good so I don't get bit again :-)) ----- diff postgresql-6.4.2/src/include/postgres_ext.h postgres-build/src/include/postgres_ext.h 34c34 < #define NAMEDATALEN 32 --- > #define NAMEDATALEN 256 37c37 < #define OIDNAMELEN 36 --- > #define OIDNAMELEN 260 ----- diff postgresql-6.4.2/src/include/storage/buf_internals.h postgres-build/src/include/storage/buf_internals.h 87c87 < #define PADDED_SBUFDESC_SIZE 128 --- > #define PADDED_SBUFDESC_SIZE 1024 ----- After this, I did a clean build. I am pretty sure this produces a Postgres that *is not* binary compatible with existing databases. You have to do the dump/reload thing. (We discovered this issue very early on so I was able to trash all my existing databases and start from scratch.) I do not know what performance or space usage impact this has. If it is not too terrible, this would be a great change to see in a future version of Postgres. -Z-
> > And I assume it is working fine? > > Yes. It fixes the column length too I think. The following is a psql > interaction: > ----- > ssmldb=> create table > more_than_64_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb > (more_than_32_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb boolean ) ; > CREATE > ssmldb=> select * from > more_than_64_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb > ; > more_than_32_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb > --------------------------------------------- > (0 rows) > ssmldb=> insert into > more_than_64_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb > ( more_than_32_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb ) values ( true ) ; > INSERT 25353 1 > ssmldb=> select * from > more_than_64_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb > ; > ^Mmore_than_32_bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb > --------------------------------------------- > t > (1 row) > ----- > (I did some tests to make sure all the characters are significant. They > are.) > > Here are the two diffs that up the "name size" from 32 characters to 256 > characters. (Once I get bit, I try to fix things real good so I don't get > bit again :-)) > ----- > diff postgresql-6.4.2/src/include/postgres_ext.h postgres-build/src/include/postgres_ext.h > 34c34 > < #define NAMEDATALEN 32 > --- > > #define NAMEDATALEN 256 > 37c37 > < #define OIDNAMELEN 36 > --- > > #define OIDNAMELEN 260 > ----- > diff postgresql-6.4.2/src/include/storage/buf_internals.h postgres-build/src/include/storage/buf_internals.h > 87c87 > < #define PADDED_SBUFDESC_SIZE 128 > --- > > #define PADDED_SBUFDESC_SIZE 1024 > ----- PADDED_SBUFDESC_SIZE is gone in 6.5beta, meaning we probably now have things tuned for NAMEDATALEN directly. > > After this, I did a clean build. I am pretty sure this produces a Postgres > that *is not* binary compatible with existing databases. You have to do the > dump/reload thing. (We discovered this issue very early on so I was able to > trash all my existing databases and start from scratch.) > > I do not know what performance or space usage impact this has. If it is not > too terrible, this would be a great change to see in a future version of > Postgres. Yes, I was thinking this too. -- 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