identity not working with inherited table

On Sat, May 04, 2019 at 12:11:51AM -0500, José Luis Viejo wrote:

I hope this helps to clarify the problem

https://stackoverflow.com/questions/55979456/identity-not-working-with-inherited-table

If Stack overflow goes away, then we would lost this information when
referring to the archives of PostgreSQL mailing lists, and not
everybody is willing to follow the information on the link. Could you
send directly to this thread what you think the actual problem is,
what you are noticing, and what you would expect? Using SQL queries
is of course recommended.
--
Michael

Hi Michael

I'm using PostgreSQL 11 latest version, having problems with an identity as
PK that inherits a table.

assume you have simple parent table like:

CREATE TABLE test7
(
id_t7 int GENERATED always AS IDENTITY PRIMARY KEY,
folio int GENERATED always AS IDENTITY,
client int
);

with any inherited table like:

CREATE TABLE test7_detail1(
-- uuid uuid DEFAULT uuid_generate_v4(), <-- fiddle doesn't support it
in_process boolean,
id_corte integer,
ts_captura timestamp(6) without time zone DEFAULT
(now())::timestamp without time zone) INHERITS (test7);

if I try insert like:

insert into test7_detail1 (client,in_process, id_corte)values (20797,'t',101)

it returns:

ERROR: null value in column "id_t7" violates not-null constraint
DETAIL: Failing row contains (null, null, 20797, t, 101, 2019-05-03
22:27:54.823894).

here <https://www.db-fiddle.com/f/iYkASiKbr8t1oEg1U2MCcX/2&gt; is the fiddle

thanks in advance, best regards

jose

On Sun, May 5, 2019 at 4:31 AM Michael Paquier <michael@paquier.xyz> wrote:

Seems like pilot error ( rtfm )?...

On Sun, May 5, 2019 at 12:50 PM José Luis Viejo <jlviejo@gmail.com> wrote:

I'm using PostgreSQL 11 latest version, having problems with an identity as PK that inherits a table.

https://www.postgresql.org/docs/11/sql-createtable.html says in the
description of the inherits cluase of create table:

"If a column in the parent table is an identity column, that property
is not inherited. A column in the child table can be declared identity
column if desired."

So

ERROR: null value in column "id_t7" violates not-null constraint
DETAIL: Failing row contains (null, null, 20797, t, 101, 2019-05-03 22:27:54.823894).

Seems to be working exactly as documented, not-null inherited, identity not.

F.O.

Em dom, 5 de mai de 2019 às 08:16, Francisco Olarte
<folarte@peoplecall.com> escreveu:

"If a column in the parent table is an identity column, that property
is not inherited. A column in the child table can be declared identity
column if desired."

Although, it is documented, it seems inconsistent with sequence
behavior (child table inherits sequences). Since sequences are
inherited I think identity columns (that use implicit sequences)
should be inherited as well. It seems an oversight in the current
implementation, however, I see it as a new feature.

--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Euler:

On Mon, May 6, 2019 at 3:46 AM Euler Taveira <euler@timbira.com.br> wrote:

Em dom, 5 de mai de 2019 às 08:16, Francisco Olarte
<folarte@peoplecall.com> escreveu:

"If a column in the parent table is an identity column, that property
is not inherited. A column in the child table can be declared identity
column if desired."

Although, it is documented, it seems inconsistent with sequence
behavior (child table inherits sequences). Since sequences are
inherited I think identity columns (that use implicit sequences)
should be inherited as well. It seems an oversight in the current
implementation, however, I see it as a new feature.

I'm not sure what you mean by "inherits sequence", may be you mean
"inherits default values" ( specifically on serial ) ?

I do not know the reason, but that is what the docs are for. You hit a
glitch, you RTFM a bit before spamming the bugs list. Then you
consider it a short coming and ask about it / request a feature on
the general / hackers list where more knowledgeable people may give
you some insight ( they tend to be busy, and may see it and dismiss it
as "another one who does not bother to read the docs ).

If you see it as a feature, why do you report it as a bug?

Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

And, given your sig, you should have done it. I'm just an ocasional
user and found it in the docs in about a dozen seconds.

Francisco Olarte.

Em seg, 6 de mai de 2019 às 14:05, Francisco Olarte
<folarte@peoplecall.com> escreveu:

If you see it as a feature, why do you report it as a bug?

First of all, I'm not José (who wrote the bug report). I see "inherits
identity columns" by child tables as a *new* feature (minor issue, no
bug fix, no backpatch).

And, given your sig, you should have done it. I'm just an ocasional
user and found it in the docs in about a dozen seconds.

What does my signature have to do with the discussion?

--
Euler Taveira Timbira -
http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

Due to several errors interpreting the messages in this thread I sent
some comments to Euler Taveria which were inapropiate. Please ignore
them, and all my messages in this thread. Sorry for the noise.

Francisco Olarte Sanz.

On 2019-05-06 03:46, Euler Taveira wrote:

Em dom, 5 de mai de 2019 às 08:16, Francisco Olarte
<folarte@peoplecall.com> escreveu:

"If a column in the parent table is an identity column, that property
is not inherited. A column in the child table can be declared identity
column if desired."

Although, it is documented, it seems inconsistent with sequence
behavior (child table inherits sequences). Since sequences are
inherited I think identity columns (that use implicit sequences)
should be inherited as well. It seems an oversight in the current
implementation, however, I see it as a new feature.

Some of the semantics of this would not be entirely clear. Who would
own the sequence? What happens when you drop the identity property from
the inheritance root or an inheritance child? It's probably doable, but
it would need some serious thinking.

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