Generated (stored) columns are treated as column with default value, which breaks versioned schema views

[georgeboot]

I created a baseline from my existing database, and tried pgroll out by adding a simple migration:

# yaml-language-server: $schema=https://raw.githubusercontent.com/xataio/pgroll/main/schema.json
operations:
  - create_table:
      name: virtual_collections
      columns:
        - name: id
          type: uuid
          pk: true
          default: public.uuid_generate_v7()
        - name: name
          type: varchar(255)

Then I ran: pgroll start pgroll/01_create_virtual_collections.yaml. This errors:

Failed to start migration: unable to create view: pq: cannot use column reference in DEFAULT expression

As it turns out, this is because in the pgroll.migrations table, the resulting_schema of the baseline migration contains:

"spatial_extent": {
  "name": "spatial_extent",
  "type": "public.geometry",
  "unique": false,
  "default": "public.st_envelope(spatial)",
  "nullable": false,
  "postgrestype": "base"
}

However, then I check it in postgres:

SELECT table_schema, is_nullable, column_default, is_generated, generation_expression
FROM information_schema.columns
WHERE table_name = 'dataset_coverages' 
AND column_name = 'spatial_extent';

gives:

table_schema	is_nullable	column_default	is_generated	generation_expression
public	NO		ALWAYS	st_envelope(spatial)
public_01_create_dataset_coverages	YES		NEVER

When the schema contains a table with a generated column, all subsequent migrations will fail creating with the above error because views/tables cant use column names in their default value.

[andriusjurkus]

I have similar issue. I before have tested 0.13.0 and there were no such errors. So it is something between 0.13.0 and 0.14.0

[gulcin]

Hello @georgeboot thank you for reporting this and thanks @andriusjurkus for the additional context. I’ll keep you posted once we’ve had a chance to look into it.

[georgeboot]

I can confirm the issue does not happen on 0.13.0

[andrew-farries]

We just released v0.14.3 which aims to resolve this issue with GENERATED columns.

Please try it out and see if the issue reported here is fixed 🙏

[andriusjurkus]

Looks like solved, need to to do new baseline tho.