Thread: [BUGS] BUG #14718: unable to update table with identity column GENERATEDALWAYS

The following bug has been logged on the website:

Bug reference:      14718
Logged by:          zam6ak
Email address:      zam6ak@gmail.com
PostgreSQL version: 10beta1
Operating system:   Windows Server 2012 R2
Description:

I have downloaded a PG10 distro for Windows from BigSQL.

if I try to update table that uses identity column generated always, the
update fails...

If I switch to "generated by default" it works...


-- create tbl1
create table tbl1 ( id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    -- fails --id   bigint GENERATED BY
DEFAULTAS IDENTITY PRIMARY KEY,  -- works name text   NOT NULL               
 
);
-- add some  values
insert into tbl1 (name) values ('Tommy');
-- verify
select * from tbl1; -- 1;"Tommy"

-- now try to update
update tbl1 set name = 'Bobby' where id = 1;

ERROR:  column "id" can only be updated to DEFAULT
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.

********** Error **********

ERROR: column "id" can only be updated to DEFAULT
SQL state: 428C9
Detail: Column "id" is an identity column defined as GENERATED ALWAYS.


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 6/26/17 15:22, zam6ak@gmail.com wrote:
> -- now try to update
> update tbl1 set name = 'Bobby' where id = 1;
> 
> ERROR:  column "id" can only be updated to DEFAULT
> DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.

This is working as expected.  What is the problem?

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 2017-06-30 17:03:22 -0400, Peter Eisentraut wrote:
> On 6/26/17 15:22, zam6ak@gmail.com wrote:
> > -- now try to update
> > update tbl1 set name = 'Bobby' where id = 1;
> > 
> > ERROR:  column "id" can only be updated to DEFAULT
> > DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
> 
> This is working as expected.  What is the problem?

"id" is not being updated in that statement?

- Andres


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Peter Eisentraut schrieb am 30.06.2017 um 23:03:
> On 6/26/17 15:22, zam6ak@gmail.com wrote:
>> -- now try to update
>> update tbl1 set name = 'Bobby' where id = 1;
>>
>> ERROR:  column "id" can only be updated to DEFAULT
>> DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
> 
> This is working as expected.  What is the problem?

I'd say the problem is, that the id column is NOT updated in that statement, only the name.

The identity column is only included in the WHERE clause - it's not changed at all.
Surely that is not the way it's meant to be?




-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Thomas Kellerer <spam_eater@gmx.net> writes:
> Peter Eisentraut schrieb am 30.06.2017 um 23:03:
>> This is working as expected.  What is the problem?

> I'd say the problem is, that the id column is NOT updated in that statement, only the name.

Yeah.  Looks to me like the code that was added to rewriteTargetListIU
for this did not quite understand the meaning of the "apply_default"
flag.
        regards, tom lane


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 6/26/17 15:22, zam6ak@gmail.com wrote:
> -- create tbl1
> create table tbl1 (
>   id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    -- fails
>   --id   bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,  -- works
>   name text   NOT NULL               
> );
> -- add some  values
> insert into tbl1 (name) values ('Tommy');
> -- verify
> select * from tbl1; -- 1;"Tommy"
> 
> -- now try to update
> update tbl1 set name = 'Bobby' where id = 1;
> 
> ERROR:  column "id" can only be updated to DEFAULT
> DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.

Fix committed.  Thanks for the report.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs