Postgres array column dropped and re-added during synchronization when no change is made.

[edcolvin]

Issue Description

When using Postgres, if a length is specified for an array of type varchar and synchronization is enabled, Typeorm will drop and re-add the column on startup even if there are no schema changes.

If the length property is removed, the behavior will stop occurring.

Expected Behavior

When there are no changes to the column, Typeorm should recognize the current database schema matches the column configuration, and not drop and re-add the column during synchronization.

Actual Behavior

When synchronizing on startup, Typeorm issues SQL queries to drop and re-add the column, even though nothing has changed.

From the console output at startup:

query: ALTER TABLE "test"."user" DROP COLUMN "roles"
query: ALTER TABLE "test"."user" ADD "roles" character varying(64) array NOT NULL

Steps to Reproduce

1. Configure Typeorm with a Postgres connection.
2. Enable synchronization.
3. Configure an entity with a column of type string[] and options set to { array: true, type: "character varying", length: 64 }.
4. Launch the program to create the new column, and then restart it to trigger synchronization again.

 @Column({
    array: true,
    type: "character varying",
    length: 64,
  })
  roles: string[];

For a small example, see: https://github.com/edcolvin/array-sync-issue

My Environment

Dependency	Version
Operating System	CentOS 7
Node.js version	v12.16.3
Typescript version	4.0.5
TypeORM version	0.2.28

Additional Context

Tested using Postgres:11 Docker image
PostgreSQL 11.9 (Debian 11.9-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

Relevant Database Driver(s)

• aurora-data-api
• aurora-data-api-pg
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time, and I know how to start.
• Yes, I have the time, but I don't know how to start. I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[alexandrucancescu]

Any luck fixing this? I have the same behaviour. My project is pretty simple in terms of database and I don't want to complicate it with migrations, as synchronise did a good job until now.

@Entity()
export default class ModelAccount implements WithId {

	@Column("char",{
		array: true,
		length: 2,
		name: "banned_countries",
		default: ()=>"array[]::char(2)[]",
	})
	bannedCountries: string[];
}

Every time I run it with synchronize : true the logs show:

query: ALTER TABLE "model_account" DROP COLUMN "banned_countries"
query: ALTER TABLE "model_account" ADD "banned_countries" character(2) array NOT NULL DEFAULT array[]::char(2)[]

[edcolvin]

@alexandrucancescu - I haven't had much time to dig into this. I looked at it a little bit, and I believe the problem is occurring in PostGresQueryRunner.loadTables:

// check only columns that have length property
if (this.driver.withLengthColumnTypes.indexOf(tableColumn.type as ColumnType) !== -1 && dbColumn["character_maximum_length"]) {
    const length = dbColumn["character_maximum_length"].toString();
    tableColumn.length = !this.isDefaultColumnLength(table, tableColumn, length) ? length : "";
}

The loadTables method relies on the value returned by Postgres for character_maximum_length in the information_schema.columns view, but that value is null for array types.

So, I think the answer is probably to check if the column is an array, and if so, get the length from somewhere else. It looks like the length can be obtained from the pg_attribute systems catalog in the atttypmod column. (Thanks to this Stackoverflow answer.) But I don't know if that's the best way to do it.

[edcolvin]

It also occurs to me to wonder why that column is null for arrays. I tested the last three major postgres versions and got the same result.

[alexandrucancescu]

Nice find. As a workaround, I changed my char(2)[] column into a json column, that typeorm is converting to/from a js array, as performance on that specific column is not that important for me, and I don't do queries on it. If you ever manage to find a fix can you please update this issue?

Regarding what you found, maybe typeorm doesn't consider a fixed size char array as a column with length as it only analyses the final column type (array), and not the subtype of the array (char(2)). Just a guess. Thanks for investing your time into this!

[kevinlul]

I've also replicated this issue and I wonder if it's related to #2712?

[edcolvin]

@kevinlul - I don't think it's related to the issue you mention, that is a Mysql issue and this looks to be specific to Postgres and how Typeorm gets information on array columns.

I looked at your workaround, and that is worth mentioning, if someone runs into this: Use json instead. You could also just turn of synchronization, which is only really useful for dev, or setup a migration or other method to reimport your test data on startup.

[kevinlul]

Hi Ed, I'm not sure what you mean. I am using Postgres and since we are in the early stages, I had synchronization enabled for rapid development. I was using Postgres arrays as they seemed to be the natural match for storing an array after all but I discovered the same pattern as you did with the array columns being dropped and recreated at startup. I found this issue after investigating and simply implemented Alexandru's workaround with the json column type and we haven't had problems with that yet.

[edcolvin]

I just meant I don't think it's related to the MySQL issue you linked, as it seems to be a product of how Typeorm queries Postgres and how Postgres reports array columns.

I started work on a pull request for this issue. I'm not sure if my fix is the best way, but maybe I can get some feedback on it. I don't see how it would break anything else, so at worst it is simply not very elegant. Once it's ready to go I'll link it here.