Horton

Re: partitioning and identity column

Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>

View thread View on postgr.es
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
To: Peter Eisentraut <peter@eisentraut.org>, Alexander Lakhin <exclusion@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-04-30T10:59:11Z
Lists: pgsql-hackers

Attachments

On Sun, Apr 28, 2024 at 12:29 PM Alexander Lakhin <exclusion@gmail.com>
wrote:

> 27.04.2024 18:00, Alexander Lakhin wrote:
> >
> > Please look also at another script, which produces the same error:
>
> I've discovered yet another problematic case:
> CREATE TABLE tbl1 (a int GENERATED ALWAYS AS IDENTITY, b text)
>      PARTITION BY LIST (a);
> CREATE TABLE tbl2 (b text, a int NOT NULL);
> ALTER TABLE tbl1 ATTACH PARTITION tbl2 DEFAULT;
>
> INSERT INTO tbl2 DEFAULT VALUES;
> ERROR:  no owned sequence found
>
> Though it works with tbl2(a int NOT NULL, b text)...
> Take a look at this too, please.
>

Thanks Alexander for the report.

PFA patch which fixes all the three problems.

I had not fixed getIdentitySequence() to fetch identity sequence associated
with the partition because I thought it would be better to fail with an
error when it's not used correctly. But these bugs show 1. the error is
misleading and unwanted 2. there are more places where adding that logic
to  getIdentitySequence() makes sense. Fixed the function in these patches.
Now callers like transformAlterTableStmt have to be careful not to call the
function on a partition.

I have examined all the callers of getIdentitySequence() and they seem to
be fine. The code related to SetIdentity, DropIdentity is not called for
partitions, errors for which are thrown elsewhere earlier.

-- 
Best Wishes,
Ashutosh Bapat

Commits

  1. Fix assorted bugs related to identity column in partitioned tables

  2. Remove extra check_stack_depth() from dropconstraint_internal()

  3. Support identity columns in partitioned tables

  4. doc: Add Identity Column section under Data Definition chapter

  5. Assert that partition inherits from only one parent in MergeAttributes()

  6. doc: Decorate PostgreSQL with productname tag

  7. Fix prologue of get_partition_ancestors()