1600 Column limit..
Is this set in stone.. or is there somewhere you can change this.. and will changing it cause upgrade problems in the future..
Travis
The reason we have that is we have 23 slots in a shelf (of equipment)
and we have 23 ports in a slot. We are tracking 3 different items per
port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual
items to track of which they are all a single digit. You add into that
some misc. stuff like shelf name and poll_time and it becomes a mess..
I just split them into 3 seperate tables.
Travis
-----Original Message-----
From: Joe Tomcat [mailto:tomcat@mobile.mp]
Sent: Thursday, November 14, 2002 1:35 PM
To: Williams, Travis L, NPONS
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1600 Column limit..
On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote:
Is this set in stone.. or is there somewhere you can change this.. and
will changing it cause upgrade problems in the future..
If you are trying to have 1600 columns in a table, you probably have
made a design mistake in your table design. You should take a look at
some database design books to see if you can change the structure.
Import Notes
Resolved by subject fallback
not really knowing the specifics, it seems you would want to describe
the table this way instead:
table #1
slot_number,shelf_id,port_number,item_type_id
table #2
shelf_id
shelf_name
table #3
item_type_id
item_data
I mean, what happens if some new shelf comes out with 25 slots and a
slot with 30 ports, or a new item per slot is added. You'll be forced
to redesign your schema.
just my $0.02
On Wed, 2002-11-13 at 18:40, Williams, Travis L, NPONS wrote:
The reason we have that is we have 23 slots in a shelf (of equipment)
and we have 23 ports in a slot. We are tracking 3 different items per
port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual
items to track of which they are all a single digit. You add into that
some misc. stuff like shelf name and poll_time and it becomes a mess..
I just split them into 3 seperate tables.Travis
-----Original Message-----
From: Joe Tomcat [mailto:tomcat@mobile.mp]
Sent: Thursday, November 14, 2002 1:35 PM
To: Williams, Travis L, NPONS
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1600 Column limit..On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote:
Is this set in stone.. or is there somewhere you can change this.. and
will changing it cause upgrade problems in the future..
If you are trying to have 1600 columns in a table, you probably have
made a design mistake in your table design. You should take a look at
some database design books to see if you can change the structure.---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Brian Hirt <bhirt@mobygames.com>
Seems like a more effective layout would be
create table tracking (
i_slot int4,
i_port int4,
i_item int4 );
maybe i'm missing somthing, but a 1600 row table is tiny, a 1600 column
table would be very difficult to manage, and poor design.
Gavin
Williams, Travis L, NPONS wrote:
The reason we have that is we have 23 slots in a shelf (of equipment)
and we have 23 ports in a slot. We are tracking 3 different items per
port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual
items to track of which they are all a single digit. You add into that
some misc. stuff like shelf name and poll_time and it becomes a mess..
I just split them into 3 seperate tables.Travis
-----Original Message-----
From: Joe Tomcat [mailto:tomcat@mobile.mp]
Sent: Thursday, November 14, 2002 1:35 PM
To: Williams, Travis L, NPONS
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1600 Column limit..On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote:
Is this set in stone.. or is there somewhere you can change this.. and
will changing it cause upgrade problems in the future..
If you are trying to have 1600 columns in a table, you probably have
made a design mistake in your table design. You should take a look at
some database design books to see if you can change the structure.---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
---------------------------------------------------------
Scanned by Sophos Anti-Virus v3.59TPOS, MIMEDefang v2.19,
and Spam Assassin v2.31 on satchel.bteg.net
On Wed, 13 Nov 2002, Williams, Travis L, NPONS wrote:
Is this set in stone.. or is there somewhere you can change this.. and
will changing it cause upgrade problems in the future..
I'm not sure how easy it'd be to get around the particular number limit
enforced, but at the very least you'd have to increate the block size
from 8k.
we have to have all of that information per shelf.. so we have the columns with the information then each row is a different shelf.. which to start with there are 400.. which could grow to upwards to any number.. but I did split all of this up into seperate tables..
Travis
-----Original Message-----
From: Gavin M. Roy [mailto:gmr@justsportsusa.com]
Sent: Wednesday, November 13, 2002 8:19 PM
To: Williams, Travis L, NPONS; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1600 Column limit..
Seems like a more effective layout would be
create table tracking (
i_slot int4,
i_port int4,
i_item int4 );
maybe i'm missing somthing, but a 1600 row table is tiny, a 1600 column
table would be very difficult to manage, and poor design.
Gavin
Williams, Travis L, NPONS wrote:
The reason we have that is we have 23 slots in a shelf (of equipment)
and we have 23 ports in a slot. We are tracking 3 different items per
port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual
items to track of which they are all a single digit. You add into that
some misc. stuff like shelf name and poll_time and it becomes a mess..
I just split them into 3 seperate tables.Travis
-----Original Message-----
From: Joe Tomcat [mailto:tomcat@mobile.mp]
Sent: Thursday, November 14, 2002 1:35 PM
To: Williams, Travis L, NPONS
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1600 Column limit..On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote:
Is this set in stone.. or is there somewhere you can change this.. and
will changing it cause upgrade problems in the future..
If you are trying to have 1600 columns in a table, you probably have
made a design mistake in your table design. You should take a look at
some database design books to see if you can change the structure.---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
---------------------------------------------------------
Scanned by Sophos Anti-Virus v3.59TPOS, MIMEDefang v2.19,
and Spam Assassin v2.31 on satchel.bteg.net
Import Notes
Resolved by subject fallback
"Williams, Travis L, NPONS" <tlw@att.com> writes:
Is this set in stone.. or is there somewhere you can change this.. and
will changing it cause upgrade problems in the future..
It is not easy to make a large increase in that limit. See the comments
in src/include/access/htup.h for some of the reasons why. You'd also
need a significant increase in block size to be able to do anything
useful. (For instance, if all your columns are int4, you surely can't
fit more than 2K columns in 8K blocksize.) Finally, the system is not
very well optimized for large numbers of columns --- even if you could
physically make things fit, there are various places whose runtime is
O(N^2) in the number of columns.
I agree with the nearby comments that say you should consider a database
redesign. If extremely wide tables were a standard design technique,
we'd not have allowed these limitations to accrue...
regards, tom lane
Hi Williams,
--On Mittwoch, 13. November 2002 22:27 -0500 "Williams, Travis L, NPONS"
<tlw@att.com> wrote:
we have to have all of that information per shelf.. so we have the
columns with the information then each row is a different shelf.. which
to start with there are 400.. which could grow to upwards to any number..
but I did split all of this up into seperate tables.. Travis
Seems you are a bit influenced by Makarenko ;)
To your problem: I see you have all these information
which appears to be in a row - but whats the use other
then the storage? How to find a special Slot and look
for its contents? If you would instead have some related
tables, the job is easy - all slots could be indexed.
Otherwise you could use 1 column and use the array datatype
as well. I dont know if there is a limit in the array datatype,
but I would not expect one.
Regards
Tino
Show quoted text
-----Original Message-----
From: Gavin M. Roy [mailto:gmr@justsportsusa.com]
Sent: Wednesday, November 13, 2002 8:19 PM
To: Williams, Travis L, NPONS; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1600 Column limit..Seems like a more effective layout would be
create table tracking (
i_slot int4,
i_port int4,
i_item int4 );maybe i'm missing somthing, but a 1600 row table is tiny, a 1600 column
table would be very difficult to manage, and poor design.Gavin
Williams, Travis L, NPONS wrote:
The reason we have that is we have 23 slots in a shelf (of equipment)
and we have 23 ports in a slot. We are tracking 3 different items per
port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual
items to track of which they are all a single digit. You add into that
some misc. stuff like shelf name and poll_time and it becomes a mess..
I just split them into 3 seperate tables.Travis
-----Original Message-----
From: Joe Tomcat [mailto:tomcat@mobile.mp]
Sent: Thursday, November 14, 2002 1:35 PM
To: Williams, Travis L, NPONS
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1600 Column limit..On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote:
Is this set in stone.. or is there somewhere you can change this.. and
will changing it cause upgrade problems in the future..
If you are trying to have 1600 columns in a table, you probably have
made a design mistake in your table design. You should take a look at
some database design books to see if you can change the structure.---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)---------------------------------------------------------
Scanned by Sophos Anti-Virus v3.59TPOS, MIMEDefang v2.19,
and Spam Assassin v2.31 on satchel.bteg.net---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
1) What is the "array datatype"? I looked for this under data types and
didn't see it.
2) We have a huge amount of scripts that basically maintain the our
network as a whole. They are wanting to start off by getting out of
using a 20 meg flat file because every time they do an update it takes
forever.. so we are trying to mimic what they have in the file.. then
in the future we can get them to change how their data is structured..
in their datafile they have it set up exactly like the db is.. lotsa
columns.. then they look for different trends in the data.. or they look
for specific values per shelf.. I understand how this could be done with
this information all together.. but getting them to do change thier
scripts is something else.. what I did was break it down into 3 seperate
tables.. with each piece of information in a different one..
Travis
-----Original Message-----
From: Tino Wildenhain [mailto:tino@wildenhain.de]
Sent: Thursday, November 14, 2002 7:44 AM
To: Williams, Travis L, NPONS; Gavin M. Roy;
pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1600 Column limit..
Hi Williams,
--On Mittwoch, 13. November 2002 22:27 -0500 "Williams, Travis L, NPONS"
<tlw@att.com> wrote:
we have to have all of that information per shelf.. so we have the
columns with the information then each row is a different shelf..
which
to start with there are 400.. which could grow to upwards to any
number..
but I did split all of this up into seperate tables.. Travis
Seems you are a bit influenced by Makarenko ;)
To your problem: I see you have all these information
which appears to be in a row - but whats the use other
then the storage? How to find a special Slot and look
for its contents? If you would instead have some related
tables, the job is easy - all slots could be indexed.
Otherwise you could use 1 column and use the array datatype
as well. I dont know if there is a limit in the array datatype,
but I would not expect one.
Regards
Tino
-----Original Message-----
From: Gavin M. Roy [mailto:gmr@justsportsusa.com]
Sent: Wednesday, November 13, 2002 8:19 PM
To: Williams, Travis L, NPONS; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1600 Column limit..Seems like a more effective layout would be
create table tracking (
i_slot int4,
i_port int4,
i_item int4 );maybe i'm missing somthing, but a 1600 row table is tiny, a 1600
column
table would be very difficult to manage, and poor design.
Gavin
Williams, Travis L, NPONS wrote:
The reason we have that is we have 23 slots in a shelf (of equipment)
and we have 23 ports in a slot. We are tracking 3 different items
per
port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual
items to track of which they are all a single digit. You add into
that
some misc. stuff like shelf name and poll_time and it becomes a
mess..
I just split them into 3 seperate tables.
Travis
-----Original Message-----
From: Joe Tomcat [mailto:tomcat@mobile.mp]
Sent: Thursday, November 14, 2002 1:35 PM
To: Williams, Travis L, NPONS
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1600 Column limit..On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote:
Is this set in stone.. or is there somewhere you can change this..
and
will changing it cause upgrade problems in the future..
If you are trying to have 1600 columns in a table, you probably have
made a design mistake in your table design. You should take a look
at
some database design books to see if you can change the structure.
---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
---------------------------------------------------------
Scanned by Sophos Anti-Virus v3.59TPOS, MIMEDefang v2.19,
and Spam Assassin v2.31 on satchel.bteg.net---------------------------(end of
broadcast)---------------------------
Show quoted text
TIP 6: Have you searched our list archives?
Import Notes
Resolved by subject fallback
On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote:
Is this set in stone.. or is there somewhere you can change this.. and will changing it cause upgrade problems in the future..
If you are trying to have 1600 columns in a table, you probably have
made a design mistake in your table design. You should take a look at
some database design books to see if you can change the structure.
Hi,
Maybe a view can help you. You can design your database as needed and let your
applications see what they want to see. You can write some triggers so that
applications can update through the view.
A view with 1600 columns is ugly too, but better than a row with 1600 columns.
Ok - you will have trouble do 1600 selfjoins in one view :-(
Another solution is to offer your applications some stored procedures, which
collects the data.
In a 1600 column table the database will do a linear search for data in a row.
Tommi
Am Donnerstag, 14. November 2002 16:14 schrieb Williams, Travis L, NPONS:
1) What is the "array datatype"? I looked for this under data types and
didn't see it.2) We have a huge amount of scripts that basically maintain the our
network as a whole. They are wanting to start off by getting out of
using a 20 meg flat file because every time they do an update it takes
forever.. so we are trying to mimic what they have in the file.. then
in the future we can get them to change how their data is structured..
in their datafile they have it set up exactly like the db is.. lotsa
columns.. then they look for different trends in the data.. or they look
for specific values per shelf.. I understand how this could be done with
this information all together.. but getting them to do change thier
scripts is something else.. what I did was break it down into 3 seperate
tables.. with each piece of information in a different one..
Travis-----Original Message-----
From: Tino Wildenhain [mailto:tino@wildenhain.de]
Sent: Thursday, November 14, 2002 7:44 AM
To: Williams, Travis L, NPONS; Gavin M. Roy;
pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1600 Column limit..Hi Williams,
--On Mittwoch, 13. November 2002 22:27 -0500 "Williams, Travis L, NPONS"
<tlw@att.com> wrote:
we have to have all of that information per shelf.. so we have the
columns with the information then each row is a different shelf..which
to start with there are 400.. which could grow to upwards to any
number..
but I did split all of this up into seperate tables.. Travis
Seems you are a bit influenced by Makarenko ;)
To your problem: I see you have all these information
which appears to be in a row - but whats the use other
then the storage? How to find a special Slot and look
for its contents? If you would instead have some related
tables, the job is easy - all slots could be indexed.
Otherwise you could use 1 column and use the array datatype
as well. I dont know if there is a limit in the array datatype,
but I would not expect one.Regards
Tino-----Original Message-----
From: Gavin M. Roy [mailto:gmr@justsportsusa.com]
Sent: Wednesday, November 13, 2002 8:19 PM
To: Williams, Travis L, NPONS; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1600 Column limit..Seems like a more effective layout would be
create table tracking (
i_slot int4,
i_port int4,
i_item int4 );maybe i'm missing somthing, but a 1600 row table is tiny, a 1600
column
table would be very difficult to manage, and poor design.
Gavin
Williams, Travis L, NPONS wrote:
The reason we have that is we have 23 slots in a shelf (of equipment)
and we have 23 ports in a slot. We are tracking 3 different itemsper
port.. so we have 23 x 23 which is 529 x 3 which is 1587 individual
items to track of which they are all a single digit. You add intothat
some misc. stuff like shelf name and poll_time and it becomes a
mess..
I just split them into 3 seperate tables.
Travis
-----Original Message-----
From: Joe Tomcat [mailto:tomcat@mobile.mp]
Sent: Thursday, November 14, 2002 1:35 PM
To: Williams, Travis L, NPONS
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 1600 Column limit..On Wed, 2002-11-13 at 17:23, Williams, Travis L, NPONS wrote:
Is this set in stone.. or is there somewhere you can change this..
and
will changing it cause upgrade problems in the future..
If you are trying to have 1600 columns in a table, you probably have
made a design mistake in your table design. You should take a lookat
some database design books to see if you can change the structure.
---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" tomajordomo@postgresql.org)
---------------------------------------------------------
Scanned by Sophos Anti-Virus v3.59TPOS, MIMEDefang v2.19,
and Spam Assassin v2.31 on satchel.bteg.net---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
--
Dr. Eckhardt + Partner GmbH
http://www.epgmbh.de
Tommi Maekitalo <t.maekitalo@epgmbh.de> writes:
In a 1600 column table the database will do a linear search for data
in a row.
Why's that?
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Hi,
Am Freitag, 15. November 2002 11:16 schrieb Neil Conway:
Tommi Maekitalo <t.maekitalo@epgmbh.de> writes:
In a 1600 column table the database will do a linear search for data
in a row.Why's that?
Cheers,
Neil
one thing is to locate rows in a database. This is optimized and we can see
this in the query plan. But what does postgresql do, when he knows the row
and he should decide, which columns to send back? I'm not sure, but I would
suspect, that he just searches for columns with matching names. This can be
done with optimized access of systemtables, but the result are some
column-ids, which has to be located in the row.
Tommi
--
Dr. Eckhardt + Partner GmbH
http://www.epgmbh.de