identity not working with inherited table

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

identity not working with inherited table

José Luis Viejo
Hi

I hope this helps to clarify the problem 

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

Thanks for great job

Best regards

Jose 
Reply | Threaded
Open this post in threaded view
|

Re: identity not working with inherited table

Michael Paquier-2
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

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: identity not working with inherited table

José Luis Viejo
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 is the fiddle

thanks in advance, best regards

jose


On Sun, May 5, 2019 at 4:31 AM Michael Paquier <[hidden email]> wrote:
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
Reply | Threaded
Open this post in threaded view
|

Re: identity not working with inherited table

Francisco Olarte
Seems like pilot error ( rtfm )?...

On Sun, May 5, 2019 at 12:50 PM José Luis Viejo <[hidden email]> 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.


Reply | Threaded
Open this post in threaded view
|

Re: identity not working with inherited table

Euler Taveira
Em dom, 5 de mai de 2019 às 08:16, Francisco Olarte
<[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: identity not working with inherited table

Francisco Olarte
Euler:

On Mon, May 6, 2019 at 3:46 AM Euler Taveira <[hidden email]> wrote:

> Em dom, 5 de mai de 2019 às 08:16, Francisco Olarte
> <[hidden email]> 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.


Reply | Threaded
Open this post in threaded view
|

Re: identity not working with inherited table

Euler Taveira
Em seg, 6 de mai de 2019 às 14:05, Francisco Olarte
<[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: identity not working with inherited table

Francisco Olarte
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.


Reply | Threaded
Open this post in threaded view
|

Re: identity not working with inherited table

Peter Eisentraut-6
In reply to this post by Euler Taveira
On 2019-05-06 03:46, Euler Taveira wrote:

> Em dom, 5 de mai de 2019 às 08:16, Francisco Olarte
> <[hidden email]> 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