Re: partitioning and identity column

On Mon, Nov 13, 2023 at 3:51 PM Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> On 27.10.23 13:32, Ashutosh Bapat wrote:
> > I think we should fix these anomalies as follows
> > 1. Allow identity columns to be added to the partitioned table
> > irrespective of whether they have partitions of not.
> > 2. Propagate identity property to partitions.
> > 3. Use the same underlying sequence for getting default value of an
> > identity column when INSERTing directly in a partition.
> > 4. Disallow attaching a partition with identity column.
> >
> > 1 will fix inconsistencies in Behaviour 3 and 4. 2 and 3 will fix
> > anomalies in Behaviour 1. 4 will fix Behaviour 2.
>
> This makes sense to me.
>
> Note, here is a writeup about the behavior of generated columns with
> partitioning:
> https://www.postgresql.org/docs/devel/ddl-generated-columns.html. It
> would be useful if we documented the behavior of identity columns
> similarly. (I'm not saying the behavior has to match.)

Yes. Will add the documentation while working on the code.

>
> One thing that's not clear to me is what should happen if you have a
> partitioned table with an identity column and you try to attach a
> partition that has its own identity definition for that column. I
> suppose we shouldn't allow that.

That's point 4 above. We shouldn't allow that case.

> (The equivalent case for generated
> columns is allowed.)
>

There might be some weird behaviour because of that like virtual
columns from the same partition reporting different values based on
the table used in the SELECT clause OR stored generated columns having
different values for two rows with the same underlying columns just
because they were INSERTed into different tables (partitioned vs
partition). That may have some impact on the logical replication. I
haven't tested this myself. Maybe a topic for a separate thread.

--
Best Wishes,
Ashutosh Bapat