Incorrect mention of number of columns?

On Fri, Nov 3, 2023 at 5:09 PM Daniel Westermann (DWE)
<daniel.westermann@dbi-services.com> wrote:

Hi,

from the limits page in the docs: https://www.postgresql.org/docs/current/limits.html

"...but a tuple of 1600 bigint columns would consume 12800 bytes and would therefore not fit inside a heap page".

Creating a table with 1600 bigint columns does work with a 8k blocksize:

$ cat gen_tab.sh
#!/bin/bash
AMOUNT_OF_COLS=$1
DATA_TYPE=$2
STATEMENT="create table t ( "
for (( i=1 ; i<${AMOUNT_OF_COLS} ; i++ ));
do
STATEMENT+=" col${i} ${DATA_TYPE},"
done
STATEMENT+="col${AMOUNT_OF_COLS} ${DATA_TYPE} );"
echo ${STATEMENT}

.$ /gen_tab.sh 1600 bigint > a.sql

$ psql -f a.sql
CREATE TABLE

$ psql -c "\d t"
...
col1598 | bigint | | |
col1599 | bigint | | |
col1600 | bigint | | |

Am I missing something?

It will fail when you try to insert data in it.

//Magnus

"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:

Creating a table with 1600 bigint columns does work with a 8k blocksize:

Yeah, but populating it would not (unless many of the columns were
NULL).

regards, tom lane

"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:

Creating a table with 1600 bigint columns does work with a 8k blocksize:

Yeah, but populating it would not (unless many of the columns were
NULL).

Ok, but then should the documentation be more precise? It seems a bit odd to let users create such a table without at least a warning.

Regards
Daniel

"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:

Yeah, but populating it would not (unless many of the columns were
NULL).

Ok, but then should the documentation be more precise? It seems a bit odd to let users create such a table without at least a warning.

Given the impact of NULLs, and the fact that usually tables have some
variable-width columns, I doubt that a creation-time warning could be
accurate enough to be useful.

regards, tom lane

"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:

Yeah, but populating it would not (unless many of the columns were
NULL).

Ok, but then should the documentation be more precise? It seems a bit odd to let users create such a table without at least a warning.

Given the impact of NULLs, and the fact that usually tables have some
variable-width columns, I doubt that a creation-time warning could be
accurate enough to be useful.

I am not speaking about a warning at creation time, but rather a warning in the docs. Something like: Although a table with e.g. 1600 bigint columns can be created, creating a tuple of more than 8160 bytes will fail.

Regards
Daniel

On Fri, Nov 3, 2023 at 05:04:46PM +0000, Daniel Westermann (DWE) wrote:

"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:

Yeah, but populating it would not (unless many of the columns were
NULL).

Ok, but then should the documentation be more precise? It seems a bit odd to

let users create such a table without at least a warning.

Given the impact of NULLs, and the fact that usually tables have some
variable-width columns, I doubt that a creation-time warning could be
accurate enough to be useful.

I am not speaking about a warning at creation time, but rather a warning in the
docs. Something like: Although a table with e.g. 1600 bigint columns can be
created, creating a tuple of more than 8160 bytes will fail.

It is in the "limits" docs:

https://www.postgresql.org/docs/current/limits.html

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.