pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedINSERT ON CONFLICT of "wide" table: target lists can have at most 1664 entries
4+ messages / 3 participants
[nested] [flat]
* INSERT ON CONFLICT of "wide" table: target lists can have at most 1664 entries
@ 2016-12-04 16:32 Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 2 replies; 4+ messages in thread
From: Justin Pryzby @ 2016-12-04 16:32 UTC (permalink / raw)
To: pgsql-general
Our application INSERTs data from external sources, and infrequently UPDATEs
the previously-inserted data (currently, it first SELECTs to determine whether
to UPDATE).
I'm implementing unique indices to allow "upsert" (and pg_repack and..), but
running into a problem when the table has >830 columns (we have some tables
which are at the 1600 column limit, and have previously worked around that
limit using arrays or multiple tables).
I tried to work around the upsert problem by using pygresql inline=True
(instead of default PREPAREd statements) but both have the same issue.
I created a test script which demonstrates the problem (attached).
It seems to me that there's currently no way to "upsert" such a wide table?
I see:
./src/include/access/htup_details.h:#define MaxTupleAttributeNumber 1664 /* 8 * 208 */
./src/backend/parser/parse_node.c- /*
./src/backend/parser/parse_node.c- * Check that we did not produce too many resnos; at the very least we
./src/backend/parser/parse_node.c- * cannot allow more than 2^16, since that would exceed the range of a
./src/backend/parser/parse_node.c: * AttrNumber. It seems safest to use MaxTupleAttributeNumber.
./src/backend/parser/parse_node.c- */
./src/backend/parser/parse_node.c: if (pstate->p_next_resno - 1 > MaxTupleAttributeNumber)
./src/backend/parser/parse_node.c- ereport(ERROR,
./src/backend/parser/parse_node.c- (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
./src/backend/parser/parse_node.c: errmsg("target lists can have at most %d entries",
./src/backend/parser/parse_node.c: MaxTupleAttributeNumber)));
Thanks in advance for any suggestions.
Justin
#! /bin/sh
set -e
n=831
t=wide_upsert
psql -c "DROP TABLE IF EXISTS $t"
cols='id int'
vals='0'
sets='id=0'
for a in `seq -w 0 $n`
do
c="c$a int"
cols="$cols, c$a int"
vals="$vals, \$1"
sets="$sets, c$a=\$1"
done
echo $cols
psql -c "CREATE TABLE $t ($cols, PRIMARY KEY (id))"
set -x
psql -c "PREPARE x AS INSERT INTO $t VALUES ($vals) ON CONFLICT (id) DO UPDATE SET $sets; EXECUTE x(0)"
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Attachments:
wide_upsert.txt (text/plain, 395B) download
#! /bin/sh
set -e
n=831
t=wide_upsert
psql -c "DROP TABLE IF EXISTS $t"
cols='id int'
vals='0'
sets='id=0'
for a in `seq -w 0 $n`
do
c="c$a int"
cols="$cols, c$a int"
vals="$vals, \$1"
sets="$sets, c$a=\$1"
done
echo $cols
psql -c "CREATE TABLE $t ($cols, PRIMARY KEY (id))"
set -x
psql -c "PREPARE x AS INSERT INTO $t VALUES ($vals) ON CONFLICT (id) DO UPDATE SET $sets; EXECUTE x(0)"
* INSERT ON CONFLICT of "wide" table: target lists can have at most 1664 entries
@ 2016-12-04 16:35 Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 0 replies; 4+ messages in thread
From: Justin Pryzby @ 2016-12-04 16:35 UTC (permalink / raw)
To: pgsql-general
Our application INSERTs data from external sources, and infrequently UPDATEs
the previously-inserted data (currently, it first SELECTs to determine whether
to UPDATE).
I'm implementing unique indices to allow "upsert" (and pg_repack and..), but
running into a problem when the table has >830 columns (we have some tables
which are at the 1600 column limit, and have previously worked around that
limit using arrays or multiple tables).
I tried to work around the upsert problem by using pygresql inline=True
(instead of default PREPAREd statements) but both have the same issue.
I created a test script which demonstrates the problem (attached).
It seems to me that there's currently no way to "upsert" such a wide table?
I see:
./src/include/access/htup_details.h:#define MaxTupleAttributeNumber 1664 /* 8 * 208 */
./src/backend/parser/parse_node.c- /*
./src/backend/parser/parse_node.c- * Check that we did not produce too many resnos; at the very least we
./src/backend/parser/parse_node.c- * cannot allow more than 2^16, since that would exceed the range of a
./src/backend/parser/parse_node.c: * AttrNumber. It seems safest to use MaxTupleAttributeNumber.
./src/backend/parser/parse_node.c- */
./src/backend/parser/parse_node.c: if (pstate->p_next_resno - 1 > MaxTupleAttributeNumber)
./src/backend/parser/parse_node.c- ereport(ERROR,
./src/backend/parser/parse_node.c- (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
./src/backend/parser/parse_node.c: errmsg("target lists can have at most %d entries",
./src/backend/parser/parse_node.c: MaxTupleAttributeNumber)));
Thanks in advance for any suggestions.
Justin
#! /bin/sh
set -e
n=831
t=wide_upsert
psql -c "DROP TABLE IF EXISTS $t"
cols='id int'
vals='0'
sets='id=0'
for a in `seq -w 0 $n`
do
c="c$a int"
cols="$cols, c$a int"
vals="$vals, \$1"
sets="$sets, c$a=\$1"
done
echo $cols
psql -c "CREATE TABLE $t ($cols, PRIMARY KEY (id))"
set -x
psql -c "PREPARE x AS INSERT INTO $t VALUES ($vals) ON CONFLICT (id) DO UPDATE SET $sets; EXECUTE x(0)"
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Attachments:
wide_upsert.txt (text/plain, 395B) download
#! /bin/sh
set -e
n=831
t=wide_upsert
psql -c "DROP TABLE IF EXISTS $t"
cols='id int'
vals='0'
sets='id=0'
for a in `seq -w 0 $n`
do
c="c$a int"
cols="$cols, c$a int"
vals="$vals, \$1"
sets="$sets, c$a=\$1"
done
echo $cols
psql -c "CREATE TABLE $t ($cols, PRIMARY KEY (id))"
set -x
psql -c "PREPARE x AS INSERT INTO $t VALUES ($vals) ON CONFLICT (id) DO UPDATE SET $sets; EXECUTE x(0)"
* Re: INSERT ON CONFLICT of "wide" table: target lists can have at most 1664 entries
@ 2016-12-04 16:52 Gmail <robjsargent@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 4+ messages in thread
From: Gmail @ 2016-12-04 16:52 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-general
> On Dec 4, 2016, at 9:32 AM, Justin Pryzby <pryzby@telsasoft.com> wrote:
>
> Our application INSERTs data from external sources, and infrequently UPDATEs
> the previously-inserted data (currently, it first SELECTs to determine whether
> to UPDATE).
>
> I'm implementing unique indices to allow "upsert" (and pg_repack and..), but
> running into a problem when the table has >830 columns (we have some tables
> which are at the 1600 column limit, and have previously worked around that
> limit using arrays or multiple tables).
>
> I tried to work around the upsert problem by using pygresql inline=True
> (instead of default PREPAREd statements) but both have the same issue.
>
> I created a test script which demonstrates the problem (attached).
>
> It seems to me that there's currently no way to "upsert" such a wide table?
Pardon my intrusion here, but I'm really curious what sort of datum has so many attributes?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: INSERT ON CONFLICT of "wide" table: target lists can have at most 1664 entries
@ 2016-12-04 20:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2016-12-04 20:05 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-general
Justin Pryzby <pryzby@telsasoft.com> writes:
> I'm implementing unique indices to allow "upsert" (and pg_repack and..), but
> running into a problem when the table has >830 columns (we have some tables
> which are at the 1600 column limit, and have previously worked around that
> limit using arrays or multiple tables).
Yeah, this is a bug, but fortunately the fix is pretty trivial. See
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=da05d0ebc637a84ba41a172b32552557e...
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2016-12-04 20:05 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-12-04 16:32 INSERT ON CONFLICT of "wide" table: target lists can have at most 1664 entries Justin Pryzby <pryzby@telsasoft.com>
2016-12-04 16:52 ` Gmail <robjsargent@gmail.com>
2016-12-04 20:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-12-04 16:35 INSERT ON CONFLICT of "wide" table: target lists can have at most 1664 entries Justin Pryzby <pryzby@telsasoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox