feat: implement generated columns for postgres 12 driver #6469

TheNoim · 2020-07-26T15:53:28Z

The implementation has the potential to make full-text search much faster when using Postgres. You can simply pre-generate all tsvector's.

Example:

export class Post {

    @PrimaryColumn()
    id: number;

    @Column({ unique: true })
    version: number;

    @Column({ default: "My post" })
    name: string;

    @Column()
    text: string;

    @Column()
    tag: string;

    @Column({ generatedType: "STORED", asExpression: "to_tsvector('english', text)" })
    tsv: string[];
}

Note:

My implementation is a bit different from the MySQL implementation. For me, it was important that a generated column only gets dropped and added again if the as expression changes. Maybe I should implement the same mechanic for the MySQL driver.

TheNoim · 2020-07-26T16:22:36Z

Ok, this is a problem. The tests are running on Postgres 9. However, this is a Postgres 12 feature.

TheNoim · 2020-08-13T11:39:13Z

Resolved the merge conflicts and adjusted the ci to also test Postgres 12.

The implementation has the potential to make full text search much faster when using postgres. You can simply pre-generate all tsvector's

Generated columns are only available on postgres version 12+

Currently, there are only tests for postgres 9. This adds postgres 12 as test target

MariaDB will fail with a generated column type

Latest changes in master introduce replication mode. This commit adjust the the pull request typeorm#6469 to this change

Latest changes in master broke the postgres 12 test setup

TheNoim · 2020-10-10T12:55:46Z

Rebased and resolved conflicts.

src/driver/postgres/PostgresDriver.ts

.circleci/config.yml

src/schema-builder/RdbmsSchemaBuilder.ts

.circleci/config.yml

src/driver/postgres/PostgresQueryRunner.ts

…TypeormGeneratedMetadataTable function imnotjames notice this in his review of the pull request

This return of Promise.resolve() has no effect. We can leave it out

Instead of parsing the version string with parseFloat, use the typeorm VersionUtils

src/driver/postgres/PostgresQueryRunner.ts

TheNoim · 2021-05-21T11:39:50Z

It would be cool if this pr gets some love too, after so many prs got merged this week.

pleerock · 2021-11-09T19:27:57Z

Sorry your PR is on the last page of the list, that's why I never reach it, since I always start from the first page 😆 Let's figure out what to do - merge or close it, I'm going to review it.

TheNoim · 2021-11-10T07:49:45Z

Sorry your PR is on the last page of the list, that's why I never reach it, since I always start from the first page 😆 Let's figure out what to do - merge or close it, I'm going to review it.

This is still open: https://github.com/typeorm/typeorm/pull/6469/files#r584112670

pleerock

All comments from previous review should be resolved before this can be merged.

src/driver/postgres/PostgresQueryRunner.ts

test/functional/query-runner/add-column.ts

pleerock · 2021-11-11T13:22:40Z

Tests are failing btw

TheNoim · 2021-11-11T13:24:09Z

Tests are failing btw

Yes, I see it. Will try to solve it as soon as possible.

dunklesToast · 2021-11-11T20:58:08Z

Since tests are passing now, can this land soon?

AlexMesser · 2021-11-14T12:59:28Z

added a new MetadataTableType enum to make metadata table types more stricter and predictable

export enum MetadataTableType {
    VIEW = "VIEW",
    MATERIALIZED_VIEW = "MATERIALIZED_VIEW",
    GENERATED_COLUMN = "GENERATED_COLUMN"
}

implemented insertTypeormMetadataSql() and deleteTypeormMetadataSql() functions in BaseQueryRunner to unify metadata insertion and deletion across all drivers and metadata types
some code refactoring

AlexMesser · 2021-11-14T13:02:49Z

thank you for contribution!

TheNoim · 2021-11-14T16:52:04Z

I am so happy this finally landed on master <3

* feat: implement generated columns for postgres 12 driver The implementation has the potential to make full text search much faster when using postgres. You can simply pre-generate all tsvector's * test: add tests for generated columns in postgres 12 * docs: document generated columns for postgres 12 * fix: check postgres version for generated columns Generated columns are only available on postgres version 12+ * test: add postgres 12 to tests Currently, there are only tests for postgres 9. This adds postgres 12 as test target * test: remove generated column from model MariaDB will fail with a generated column type * test: use non alpine container for postgres 12 * test: skip generated columns test on mariadb * fix: detect generated column change * fix: circle ci postgres version * fix: add replication mode to isGeneratedColumnsSupported() function Latest changes in master introduce replication mode. This commit adjust the the pull request typeorm#6469 to this change * fix: ci testing for postgres 12 Latest changes in master broke the postgres 12 test setup * style: remove SqlServerConnectionOptions generic parameter for createTypeormGeneratedMetadataTable function imnotjames notice this in his review of the pull request * style: remove unnecessary return of Promise.resolve() This return of Promise.resolve() has no effect. We can leave it out * style: fix whitespace issue for config.yml * refactor: use VersionUtils Instead of parsing the version string with parseFloat, use the typeorm VersionUtils * fix: fix failing build After merging the upstream into the pr fork, the build stopped working. The reason why the build fails, is because in the upstream one import is missing and one variable was removed * refactor: replace promise.all() with for loop * refactor: make requested changes * fix: update table name * fix: server version query and escape table column in queries * code refactoring * fixed lint issue * removed "enabledDrivers" from test Co-authored-by: Dmitry Zotov <dmzt08@gmail.com>

Migushthe2nd · 2021-12-05T13:47:13Z

@TheNoim Schema synchronization seems to fail.

query failed: SELECT * FROM "typeorm_metadata"  WHERE "table" = 'theme_previews' AND "name" = 'image720Hash' AND "
schema" = 'typeorm' AND "database" = 'themezer_dev' AND "type" = 'GENERATED_COLUMN'
error: error: relation "typeorm_metadata" does not exist
    at Parser.parseErrorMessage (node_modules\pg-protocol\src\parser.ts:369:69)
    at Parser.handlePacket (node_modules\pg-protocol\src\parser.ts:188:21)
    at Parser.parse (node_modules\pg-protocol\src\parser.ts:103:30)
    at Socket.<anonymous> (node_modules\pg-protocol\src\index.ts:7:48)
    at Socket.emit (node:events:394:28)
    at addChunk (node:internal/streams/readable:312:12)
    at readableAddChunk (node:internal/streams/readable:287:9)
    at Socket.Readable.push (node:internal/streams/readable:226:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 115,
  severity: 'ERROR',
  code: '42P01',
  detail: undefined,
  hint: undefined,
  position: '15',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '1381',
  routine: 'parserOpenTable'
}
query: ROLLBACK
query failed: SELECT * FROM "typeorm_metadata"  WHERE "table" = 'theme_previews' AND "name" = 'image360Hash' AND "
schema" = 'typeorm' AND "database" = 'themezer_dev' AND "type" = 'GENERATED_COLUMN'
error: error: current transaction is aborted, commands ignored until end of transaction block
...

On initial sync the columns seem to be generated correctly in the database and there is no issue. On second sync all generated columns fail. My decorator:
@PrimaryColumn({type: "varchar", generatedType: "STORED", asExpression: "sha256(\"image720File\")"})

Can you replicate this?

TheNoim · 2021-12-05T14:15:09Z

@TheNoim Schema synchronization seems to fail.

query failed: SELECT * FROM "typeorm_metadata"  WHERE "table" = 'theme_previews' AND "name" = 'image720Hash' AND "
schema" = 'typeorm' AND "database" = 'themezer_dev' AND "type" = 'GENERATED_COLUMN'
error: error: relation "typeorm_metadata" does not exist
    at Parser.parseErrorMessage (node_modules\pg-protocol\src\parser.ts:369:69)
    at Parser.handlePacket (node_modules\pg-protocol\src\parser.ts:188:21)
    at Parser.parse (node_modules\pg-protocol\src\parser.ts:103:30)
    at Socket.<anonymous> (node_modules\pg-protocol\src\index.ts:7:48)
    at Socket.emit (node:events:394:28)
    at addChunk (node:internal/streams/readable:312:12)
    at readableAddChunk (node:internal/streams/readable:287:9)
    at Socket.Readable.push (node:internal/streams/readable:226:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
  length: 115,
  severity: 'ERROR',
  code: '42P01',
  detail: undefined,
  hint: undefined,
  position: '15',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '1381',
  routine: 'parserOpenTable'
}
query: ROLLBACK
query failed: SELECT * FROM "typeorm_metadata"  WHERE "table" = 'theme_previews' AND "name" = 'image360Hash' AND "
schema" = 'typeorm' AND "database" = 'themezer_dev' AND "type" = 'GENERATED_COLUMN'
error: error: current transaction is aborted, commands ignored until end of transaction block
...

On initial sync the columns seem to be generated correctly in the database and there is no issue. On second sync all generated columns fail. My decorator: @PrimaryColumn({type: "varchar", generatedType: "STORED", asExpression: "sha256(\"image720File\")"})

Can you replicate this?

I was hoping #4956 solves the issue. But it seems like it doesn't. Maybe I need to look into it a bit further.

TheNoim force-pushed the pg-generated-columns branch from cecb1da to 20863e2 Compare August 13, 2020 11:05

TheNoim added 11 commits October 10, 2020 12:56

feat: implement generated columns for postgres 12 driver

e9acb71

The implementation has the potential to make full text search much faster when using postgres. You can simply pre-generate all tsvector's

test: add tests for generated columns in postgres 12

e9ba0c0

docs: document generated columns for postgres 12

dcbfe93

fix: check postgres version for generated columns

8b0a7af

Generated columns are only available on postgres version 12+

test: add postgres 12 to tests

216c304

Currently, there are only tests for postgres 9. This adds postgres 12 as test target

test: remove generated column from model

12ca711

MariaDB will fail with a generated column type

test: use non alpine container for postgres 12

be0497f

test: skip generated columns test on mariadb

326540a

fix: detect generated column change

818d00f

fix: circle ci postgres version

3964005

fix: add replication mode to isGeneratedColumnsSupported() function

7ff79e2

Latest changes in master introduce replication mode. This commit adjust the the pull request typeorm#6469 to this change

TheNoim force-pushed the pg-generated-columns branch from 20863e2 to 7ff79e2 Compare October 10, 2020 11:12

fix: ci testing for postgres 12

3278244

Latest changes in master broke the postgres 12 test setup

TheNoim force-pushed the pg-generated-columns branch from 5cfbc7d to 3278244 Compare October 10, 2020 12:04

imnotjames self-requested a review October 10, 2020 21:31

imnotjames reviewed Oct 11, 2020

View reviewed changes

TheNoim added 3 commits October 12, 2020 12:58

style: remove SqlServerConnectionOptions generic parameter for create…

ac395e7

…TypeormGeneratedMetadataTable function imnotjames notice this in his review of the pull request

style: remove unnecessary return of Promise.resolve()

6cceda8

This return of Promise.resolve() has no effect. We can leave it out

style: fix whitespace issue for config.yml

e844562

TheNoim requested a review from imnotjames October 13, 2020 13:40

TheNoim added 2 commits January 1, 2021 11:20

Merge branch 'master' into pg-generated-columns

0a1ea4f

refactor: use VersionUtils

7856c5e

Instead of parsing the version string with parseFloat, use the typeorm VersionUtils

pleerock reviewed Feb 22, 2021

View reviewed changes

src/driver/postgres/PostgresQueryRunner.ts Outdated Show resolved Hide resolved

TheNoim mentioned this pull request Mar 30, 2021

MikroORM v5 mikro-orm/mikro-orm#1623

Closed

48 tasks

Merge branch 'master' into pg-generated-columns

4c0acb2

Merge branch 'master' into pg-generated-columns

f120dfc

pleerock requested changes Nov 10, 2021

View reviewed changes

src/driver/postgres/PostgresQueryRunner.ts Outdated Show resolved Hide resolved

src/driver/postgres/PostgresQueryRunner.ts Outdated Show resolved Hide resolved

test/functional/query-runner/add-column.ts Show resolved Hide resolved

TheNoim added 3 commits November 10, 2021 16:37

refactor: replace promise.all() with for loop

cf8e099

refactor: make requested changes

714e46d

fix: update table name

036337d

pleerock requested a review from AlexMesser November 11, 2021 14:49

pleerock assigned AlexMesser Nov 11, 2021

TheNoim added 2 commits November 11, 2021 18:51

Merge remote-tracking branch 'typeorm/master' into pg-generated-columns

1e01de2

fix: server version query and escape table column in queries

13b2658

This was referenced Nov 13, 2021

fix: create typeorm_metatable when running migrations #4956

Merged

test: make tests consistent on machines which have different timezone… #8360

Merged

TheNoim requested a review from pleerock November 13, 2021 12:18

AlexMesser added 3 commits November 13, 2021 21:04

code refactoring

75e3a73

fixed lint issue

05a2a86

removed "enabledDrivers" from test

5f76738

AlexMesser merged commit 91080be into typeorm:master Nov 14, 2021

Migushthe2nd mentioned this pull request Dec 11, 2021

Generated asExpression columns not in RETURNING clause on save #8450

Closed

AlexMesser mentioned this pull request Mar 27, 2022

Support for Stored Computed Columns in Postgres 12 #5244

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feat: implement generated columns for postgres 12 driver #6469

feat: implement generated columns for postgres 12 driver #6469

TheNoim commented Jul 26, 2020 •

edited

TheNoim commented Jul 26, 2020 •

edited

TheNoim commented Aug 13, 2020

TheNoim commented Oct 10, 2020

TheNoim commented May 21, 2021

pleerock commented Nov 9, 2021

TheNoim commented Nov 10, 2021

pleerock left a comment

pleerock commented Nov 11, 2021

TheNoim commented Nov 11, 2021

dunklesToast commented Nov 11, 2021

AlexMesser commented Nov 14, 2021 •

edited

AlexMesser commented Nov 14, 2021

TheNoim commented Nov 14, 2021

Migushthe2nd commented Dec 5, 2021 •

edited

TheNoim commented Dec 5, 2021

feat: implement generated columns for postgres 12 driver #6469

feat: implement generated columns for postgres 12 driver #6469

Conversation

TheNoim commented Jul 26, 2020 • edited

TheNoim commented Jul 26, 2020 • edited

TheNoim commented Aug 13, 2020

TheNoim commented Oct 10, 2020

TheNoim commented May 21, 2021

pleerock commented Nov 9, 2021

TheNoim commented Nov 10, 2021

pleerock left a comment

Choose a reason for hiding this comment

pleerock commented Nov 11, 2021

TheNoim commented Nov 11, 2021

dunklesToast commented Nov 11, 2021

AlexMesser commented Nov 14, 2021 • edited

AlexMesser commented Nov 14, 2021

TheNoim commented Nov 14, 2021

Migushthe2nd commented Dec 5, 2021 • edited

TheNoim commented Dec 5, 2021

TheNoim commented Jul 26, 2020 •

edited

TheNoim commented Jul 26, 2020 •

edited

AlexMesser commented Nov 14, 2021 •

edited

Migushthe2nd commented Dec 5, 2021 •

edited