Обсуждение: Incorrect mention of number of columns?

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?

Regards
Daniel

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:
>> 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
leasta 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

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.