pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedPGSQL bug - "Column ??? is an identity column defined as GENERATED ALWAYS.",
8+ messages / 5 participants
[nested] [flat]
* PGSQL bug - "Column ??? is an identity column defined as GENERATED ALWAYS.",
@ 2021-08-18 15:06 Petr Hybler <petr.hybler@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Petr Hybler @ 2021-08-18 15:06 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello, I would love to report a bug for the version:
PostgreSQL 11.13 (Debian 11.13-1.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
After the update, I cannot execute batch inserts. See the example below:
The table structure is as follows:
CREATE TABLE sample_table (
id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
name varchar(255) NOT NULL,
description text NOT NULL,
CONSTRAINT sample_table_pk PRIMARY KEY (id)
);
When I try to insert a single value, it works OK:
INSERT INTO sample_table (id, name, description)VALUES (DEFAULT, 'John
Doe', 'Test description');
However, when inserting multiple values, it fails:
INSERT INTO sample_table (id, name, description)VALUES (DEFAULT, 'John
Doe', 'Test description')
, (DEFAULT, 'Jane Eod', 'Not working');
Why? If I omit the DEFAULT value and PK (=id), it works great.
INSERT INTO sample_table (name, description)VALUES ('John Doe', 'Test
description')
, ('Jane Eod', 'Not working');
This worked perfectly previously.... I would appreciate if you can fix it
because I cannot do any bulkCreate operation now and I believe there will
be a lot of applications impacted
P.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PGSQL bug - "Column ??? is an identity column defined as GENERATED ALWAYS.",
@ 2021-08-18 19:27 Peter Eisentraut <peter.eisentraut@enterprisedb.com>
parent: Petr Hybler <petr.hybler@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Eisentraut @ 2021-08-18 19:27 UTC (permalink / raw)
To: Petr Hybler <petr.hybler@gmail.com>; pgsql-bugs@lists.postgresql.org
On 18.08.21 17:06, Petr Hybler wrote:
> |CREATE TABLE sample_table ( id int8 NOT NULL GENERATED ALWAYS AS
> IDENTITY, name varchar(255) NOT NULL, description text NOT NULL,
> CONSTRAINT sample_table_pk PRIMARY KEY (id) );|
>
> When I try to insert a single value, it works OK:
>
> |INSERT INTO sample_table (id, name, description) VALUES (DEFAULT, 'John
> Doe', 'Test description');|
>
> However, when inserting multiple values, it fails:
>
> |INSERT INTO sample_table (id, name, description) VALUES (DEFAULT, 'John
> Doe', 'Test description') , (DEFAULT, 'Jane Eod', 'Not working');|
This has been fixed in PostgreSQL 14.
^ permalink raw reply [nested|flat] 8+ messages in thread
* PGSQL bug - "Column ??? is an identity column defined as GENERATED ALWAYS.",
@ 2021-08-18 21:15 David G. Johnston <david.g.johnston@gmail.com>
parent: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2021-08-18 21:15 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; +Cc: Petr Hybler <petr.hybler@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Wednesday, August 18, 2021, Peter Eisentraut <peter.eisentraut@
enterprisedb.com> wrote:
> On 18.08.21 17:06, Petr Hybler wrote:
>
>> |CREATE TABLE sample_table ( id int8 NOT NULL GENERATED ALWAYS AS
>> IDENTITY, name varchar(255) NOT NULL, description text NOT NULL, CONSTRAINT
>> sample_table_pk PRIMARY KEY (id) );|
>>
>> When I try to insert a single value, it works OK:
>>
>> |INSERT INTO sample_table (id, name, description) VALUES (DEFAULT, 'John
>> Doe', 'Test description');|
>>
>> However, when inserting multiple values, it fails:
>>
>> |INSERT INTO sample_table (id, name, description) VALUES (DEFAULT, 'John
>> Doe', 'Test description') , (DEFAULT, 'Jane Eod', 'Not working');|
>>
>
> This has been fixed in PostgreSQL 14.
>
>
The OP is reporting a regression, saying it is fixed in v14 isn’t a useful
response. Is it also fixed in v11.14?
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PGSQL bug - "Column ??? is an identity column defined as GENERATED ALWAYS.",
@ 2021-08-18 21:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2021-08-18 21:54 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>; Petr Hybler <petr.hybler@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wednesday, August 18, 2021, Peter Eisentraut <peter.eisentraut@
> enterprisedb.com> wrote:
>> This has been fixed in PostgreSQL 14.
> The OP is reporting a regression, saying it is fixed in v14 isn’t a useful
> response. Is it also fixed in v11.14?
The OP would have to provide some evidence that there's actually any
regression. AFAIK that code was like that since IDENTITY columns were
introduced. v14 does improve matters, but we judged the fix too invasive
to risk back-patching.
BTW, the v11 error message points out a simple workaround, which
seems to do the trick:
regression=# CREATE TABLE sample_table (
id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
name varchar(255) NOT NULL,
description text NOT NULL,
CONSTRAINT sample_table_pk PRIMARY KEY (id)
);
CREATE TABLE
regression=# INSERT INTO sample_table (id, name, description)VALUES (DEFAULT, 'John
Doe', 'Test description')
, (DEFAULT, 'Jane Eod', 'Not working');
ERROR: cannot insert into column "id"
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
HINT: Use OVERRIDING SYSTEM VALUE to override.
regression=# INSERT INTO sample_table (id, name, description) OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'John
Doe', 'Test description')
, (DEFAULT, 'Jane Eod', 'Not working');
INSERT 0 2
regression=# table sample_table;
id | name | description
----+----------+------------------
1 | John +| Test description
| Doe |
2 | Jane Eod | Not working
(2 rows)
Yeah, per spec you shouldn't have to say OVERRIDING SYSTEM VALUE
for this case, but it didn't seem worth the risk of back-patching
to improve that in stable branches.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PGSQL bug - "Column ??? is an identity column defined as GENERATED ALWAYS.",
@ 2021-08-19 04:17 Petr Hybler <petr.hybler@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Petr Hybler @ 2021-08-19 04:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Peter Eisentraut <peter.eisentraut@enterprisedb.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Is there a possibility to have that fixed rather than using the workaround?
The problem is with ORM frameworks where there is no possibility to use
this clause OVERRIDING SYSTEM VALUE ...
Thank you
P.
On Wed, Aug 18, 2021 at 11:54 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Wednesday, August 18, 2021, Peter Eisentraut <peter.eisentraut@
> > enterprisedb.com> wrote:
> >> This has been fixed in PostgreSQL 14.
>
> > The OP is reporting a regression, saying it is fixed in v14 isn’t a
> useful
> > response. Is it also fixed in v11.14?
>
> The OP would have to provide some evidence that there's actually any
> regression. AFAIK that code was like that since IDENTITY columns were
> introduced. v14 does improve matters, but we judged the fix too invasive
> to risk back-patching.
>
> BTW, the v11 error message points out a simple workaround, which
> seems to do the trick:
>
> regression=# CREATE TABLE sample_table (
> id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
> name varchar(255) NOT NULL,
> description text NOT NULL,
> CONSTRAINT sample_table_pk PRIMARY KEY (id)
> );
> CREATE TABLE
>
> regression=# INSERT INTO sample_table (id, name, description)VALUES
> (DEFAULT, 'John
> Doe', 'Test description')
> , (DEFAULT, 'Jane Eod', 'Not working');
> ERROR: cannot insert into column "id"
> DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
> HINT: Use OVERRIDING SYSTEM VALUE to override.
>
> regression=# INSERT INTO sample_table (id, name, description) OVERRIDING
> SYSTEM VALUE VALUES (DEFAULT, 'John
> Doe', 'Test description')
> , (DEFAULT, 'Jane Eod', 'Not working');
> INSERT 0 2
>
> regression=# table sample_table;
> id | name | description
> ----+----------+------------------
> 1 | John +| Test description
> | Doe |
> 2 | Jane Eod | Not working
> (2 rows)
>
>
> Yeah, per spec you shouldn't have to say OVERRIDING SYSTEM VALUE
> for this case, but it didn't seem worth the risk of back-patching
> to improve that in stable branches.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PGSQL bug - "Column ??? is an identity column defined as GENERATED ALWAYS.",
@ 2021-08-19 04:51 David G. Johnston <david.g.johnston@gmail.com>
parent: Petr Hybler <petr.hybler@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2021-08-19 04:51 UTC (permalink / raw)
To: Petr Hybler <petr.hybler@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Peter Eisentraut <peter.eisentraut@enterprisedb.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Wed, Aug 18, 2021 at 9:18 PM Petr Hybler <petr.hybler@gmail.com> wrote:
> Is there a possibility to have that fixed rather than using the workaround?
>
If you can prove the regression (in what v11 patch release - i.e., before
13 - did your code work, show your work please) we'd be at least willing to
consider something that could be back-patched to restore the old behavior
(which shouldn't have changed in a minor release anyway). But at the
moment we haven't observed this (I personally haven't tested though, the
bug reporter is expected to help out here).
The problem is with ORM frameworks where there is no possibility to use
> this clause OVERRIDING SYSTEM VALUE ...
>
As there is an ORM involved, the claim of this being a regression in
PostgreSQL is indeed worthy of doubt, as it may be the ORM that is at fault
here.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PGSQL bug - "Column ??? is an identity column defined as GENERATED ALWAYS.",
@ 2021-08-19 12:43 Philip Semanchuk <philip@americanefficient.com>
parent: Petr Hybler <petr.hybler@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Philip Semanchuk @ 2021-08-19 12:43 UTC (permalink / raw)
To: Petr Hybler <petr.hybler@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; Peter Eisentraut <peter.eisentraut@enterprisedb.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
> On Aug 19, 2021, at 12:17 AM, Petr Hybler <petr.hybler@gmail.com> wrote:
>
> Is there a possibility to have that fixed rather than using the workaround? The problem is with ORM frameworks where there is no possibility to use this clause OVERRIDING SYSTEM VALUE ...
>
Depending on your ORM, it may be possible to alter how it constructs this particular statement. For instance, SQLAlchemy 1.3 has a hook that we use to change the syntax it generates for autoincrement PK columns.
https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#postgresql-10-identity-columns
>
> On Wed, Aug 18, 2021 at 11:54 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > On Wednesday, August 18, 2021, Peter Eisentraut <peter.eisentraut@
> > enterprisedb.com> wrote:
> >> This has been fixed in PostgreSQL 14.
>
> > The OP is reporting a regression, saying it is fixed in v14 isn’t a useful
> > response. Is it also fixed in v11.14?
>
> The OP would have to provide some evidence that there's actually any
> regression. AFAIK that code was like that since IDENTITY columns were
> introduced. v14 does improve matters, but we judged the fix too invasive
> to risk back-patching.
>
> BTW, the v11 error message points out a simple workaround, which
> seems to do the trick:
>
> regression=# CREATE TABLE sample_table (
> id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
> name varchar(255) NOT NULL,
> description text NOT NULL,
> CONSTRAINT sample_table_pk PRIMARY KEY (id)
> );
> CREATE TABLE
>
> regression=# INSERT INTO sample_table (id, name, description)VALUES (DEFAULT, 'John
> Doe', 'Test description')
> , (DEFAULT, 'Jane Eod', 'Not working');
> ERROR: cannot insert into column "id"
> DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
> HINT: Use OVERRIDING SYSTEM VALUE to override.
>
> regression=# INSERT INTO sample_table (id, name, description) OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'John
> Doe', 'Test description')
> , (DEFAULT, 'Jane Eod', 'Not working');
> INSERT 0 2
>
> regression=# table sample_table;
> id | name | description
> ----+----------+------------------
> 1 | John +| Test description
> | Doe |
> 2 | Jane Eod | Not working
> (2 rows)
>
>
> Yeah, per spec you shouldn't have to say OVERRIDING SYSTEM VALUE
> for this case, but it didn't seem worth the risk of back-patching
> to improve that in stable branches.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: PGSQL bug - "Column ??? is an identity column defined as GENERATED ALWAYS.",
@ 2021-08-19 12:58 Petr Hybler <petr.hybler@gmail.com>
parent: Philip Semanchuk <philip@americanefficient.com>
0 siblings, 0 replies; 8+ messages in thread
From: Petr Hybler @ 2021-08-19 12:58 UTC (permalink / raw)
To: Philip Semanchuk <philip@americanefficient.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; Peter Eisentraut <peter.eisentraut@enterprisedb.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
I am using Sequelize - but I figured a way with bulkCreate there is an
option to list fields I wanna use in the query, so I list the entire table
down except the PK (Generated Identity column) ... anyway, thx for the
response guys, much appreciated
On Thu, Aug 19, 2021 at 2:43 PM Philip Semanchuk <
philip@americanefficient.com> wrote:
>
>
> > On Aug 19, 2021, at 12:17 AM, Petr Hybler <petr.hybler@gmail.com> wrote:
> >
> > Is there a possibility to have that fixed rather than using the
> workaround? The problem is with ORM frameworks where there is no
> possibility to use this clause OVERRIDING SYSTEM VALUE ...
> >
>
> Depending on your ORM, it may be possible to alter how it constructs this
> particular statement. For instance, SQLAlchemy 1.3 has a hook that we use
> to change the syntax it generates for autoincrement PK columns.
>
>
> https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#postgresql-10-identity-columns
>
>
> >
> > On Wed, Aug 18, 2021 at 11:54 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > > On Wednesday, August 18, 2021, Peter Eisentraut <peter.eisentraut@
> > > enterprisedb.com> wrote:
> > >> This has been fixed in PostgreSQL 14.
> >
> > > The OP is reporting a regression, saying it is fixed in v14 isn’t a
> useful
> > > response. Is it also fixed in v11.14?
> >
> > The OP would have to provide some evidence that there's actually any
> > regression. AFAIK that code was like that since IDENTITY columns were
> > introduced. v14 does improve matters, but we judged the fix too invasive
> > to risk back-patching.
> >
> > BTW, the v11 error message points out a simple workaround, which
> > seems to do the trick:
> >
> > regression=# CREATE TABLE sample_table (
> > id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
> > name varchar(255) NOT NULL,
> > description text NOT NULL,
> > CONSTRAINT sample_table_pk PRIMARY KEY (id)
> > );
> > CREATE TABLE
> >
> > regression=# INSERT INTO sample_table (id, name, description)VALUES
> (DEFAULT, 'John
> > Doe', 'Test description')
> > , (DEFAULT, 'Jane Eod', 'Not working');
> > ERROR: cannot insert into column "id"
> > DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
> > HINT: Use OVERRIDING SYSTEM VALUE to override.
> >
> > regression=# INSERT INTO sample_table (id, name, description) OVERRIDING
> SYSTEM VALUE VALUES (DEFAULT, 'John
> > Doe', 'Test description')
> > , (DEFAULT, 'Jane Eod', 'Not working');
> > INSERT 0 2
> >
> > regression=# table sample_table;
> > id | name | description
> > ----+----------+------------------
> > 1 | John +| Test description
> > | Doe |
> > 2 | Jane Eod | Not working
> > (2 rows)
> >
> >
> > Yeah, per spec you shouldn't have to say OVERRIDING SYSTEM VALUE
> > for this case, but it didn't seem worth the risk of back-patching
> > to improve that in stable branches.
> >
> > regards, tom lane
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2021-08-19 12:58 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-08-18 15:06 PGSQL bug - "Column ??? is an identity column defined as GENERATED ALWAYS.", Petr Hybler <petr.hybler@gmail.com>
2021-08-18 19:27 ` Peter Eisentraut <peter.eisentraut@enterprisedb.com>
2021-08-18 21:15 ` David G. Johnston <david.g.johnston@gmail.com>
2021-08-18 21:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-08-19 04:17 ` Petr Hybler <petr.hybler@gmail.com>
2021-08-19 04:51 ` David G. Johnston <david.g.johnston@gmail.com>
2021-08-19 12:43 ` Philip Semanchuk <philip@americanefficient.com>
2021-08-19 12:58 ` Petr Hybler <petr.hybler@gmail.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