[BUG] diff command generates superflous orders

[palevi67]

Describe the bug**

When I run the same pg-schema-diff diff command several times, I get different outputs, including some orders wich updates an already updated table.

Expected behavior**

Results should be the same no matter how many times you repeat the diff command. Also, there should be no superflous orders.

To Reproduce

Run an order similar to this one several times:

pg-schema-diff diff --to-dir=/opt/spark2/spark2-ddl.sql --from-dsn="postgres://SPARK2@localhost/spark2" --disable-plan-validation

And you might get some surplus orders like this one (in addition to the right ones)

ALTER TABLE "public"."zonas" ALTER COLUMN "descripcion" SET DATA TYPE public.citext COLLATE "pg_catalog"."default" using "descripcion"::public.citext;

I'm sure this column has no changes and therefore this is an superflous order.

Context

pg-schema-diff version: v1.0.2

pg-schema-diff usage: CLI

Postgres version: 16.8

[bplunkett-stripe]

Can you pg_dump your schema?

[palevi67]

I cannot disclose the full database schema, but I have extracted some tables for testing purposes. I have been running the command several times, most of the times I get 3 statements, but about once every ten times I get 10 statements ...

difftest.zip

Regards.

[palevi67]

@bplunkett-stripe , have you been able to reproduce the issue? Can I help you some way?

[bplunkett-stripe]

I think the piece of information I need is the state of the column that pg-schema-diff is detecting, and the state it thinks it needs to be

[palevi67]

What you mean by "state of the column" ?

IMHO, or postgresql is not always returning the exactly same DDL data, or pg-schema-diff has some non-deterministic behaviour ...