sql: nextval() returns sequence not exists error for column created with GENERATED BY DEFAULT AS IDENTITY

[giangpham712]

Describe the problem

Calling pg_get_serial_sequence for a database column created with GENERATED BY DEFAULT AS IDENTITY returns a sequence name but calling nextval for the returned sequence returns error.

To Reproduce

Create a table using the following

CREATE TABLE public."Animal" (
  "Id" INT8 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  CONSTRAINT "PK_Animal" PRIMARY KEY ("Id" ASC)
)

Running

SELECT pg_get_serial_sequence('"Animal"', 'Id');

returns

pg_get_serial_sequence
--------------------------
  public.Animal_Id_seq
(1 row)

but

running

SELECT nextval('public.Animal_Id_seq');

returns

ERROR: nextval(): relation "public.animal_id_seq" does not exist
SQLSTATE: 42P01

Add any other context about the problem here.
This error is affecting a number of tests in efcore.pg library when running against CockroachDB

Jira issue: CRDB-29954

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: pg_)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}