`evolve erase` tries to delete `citext` type #253

peterhirn · 2022-01-07T07:59:17Z

I'm creating a domain type based on citext

create extension if not exists citext;
create domain non_empty_citext as citext check (value ~ '\S');

Running evolve erase fails with

ERROR:  cannot drop type citext because extension citext requires it
HINT:  You can drop extension citext instead.
STATEMENT:  DROP TYPE IF EXISTS "public"."citext" CASCADE
ERROR:  current transaction is aborted, commands ignored until end of transaction block2022-01-07 07:47:50.102 UTC [97] STATEMENT:  SELECT pg_advisory_unlock(12345)

Not sure if I'm doing it wrong or this is a bug.

The text was updated successfully, but these errors were encountered:

lecaillon · 2022-01-07T09:01:27Z

Thanks for the feedback. It seems to be a bug when trying to erase Postgresql extensions after creating a new domain. I'm gonna try to fix it as soon as possible.
Meanwhile, can you confirm you post all the scripts needed to easily reproduce the issue.
Can you tell me what version of Postgresql you use and everything that can help me ?
The fix will be available in evolve 3.1.0-alpha4

peterhirn · 2022-01-07T10:37:37Z

I don't depend on this feature as I can just drop the database. No time pressure.

Versions

evolve (dotnet tool): 3.0.0
postgres (docker): postgres:14.1-alpine3.15

db/V1_0_0__works.sql

create extension if not exists pgcrypto;
create domain non_empty_text as text check (value ~ '\S');

vs.

db/V1_0_0__bug.sql

create extension if not exists citext;

test.ps1

Set-StrictMode -Version Latest
$connection="Server=127.0.0.1;Database=test;User Id=postgres;Password=password"

dotnet evolve migrate postgresql -l db -c "$connection"
if (-not $?) { throw "Migrate failed" }

dotnet evolve erase postgresql -c "$connection"
if (-not $?) { throw "Erase failed" }

lecaillon · 2022-01-08T17:18:30Z

I cannot reproduce your issue using evolve 3.1.0 alpha3. I created a new test Scenario9 that migrate then erase the schema. I use that SQL script:

create extension if not exists citext WITH SCHEMA ${schema};
create domain ${schema}.non_empty_citext as ${schema}.citext check (value ~ '\S');
create table ${schema}.test (
     did    integer PRIMARY KEY,
     name   ${schema}.non_empty_citext
);
insert into ${schema}.test values (1, 'PSG');

And everything works.
Note that I created the extension in a schema to not make my other tests fail. But even without it, it works.

peterhirn · 2022-01-08T18:51:41Z

Interesting. I upgraded to 3.1.0-alpha3 (dotnet tool) but erasing this migration still fails on my system:

create extension if not exists citext;

Servers logs DROP TYPE IF EXISTS "public"."citext" CASCADE

When using a schema I found a different issue. This works reliably (migrate then immediately erase):

create schema if not exists schema1;
create extension if not exists citext with schema schema1;

Running this works for the first execution, but on the second execution migrate fails:

create schema if not exists schema1;
create extension if not exists citext with schema schema1;
create domain schema1.non_empty_citext as schema1.citext check (value ~ '\S');

Error

Error executing script: V1_0_0__test.sql after 9 ms. 42710: type "non_empty_citext" already exists Sql query: --CREATE EXTENSION IF NOT EXISTS citext; -- --CREATE EXTENSION IF NOT EXISTS pgcrypto; -- --CREAT... 42710: type "non_empty_citext" already exists

lecaillon · 2022-01-08T19:05:03Z

Strange. I don't understand why we do not have the same behavior. I use docker alpine latest image in my test. And the same version of Evolve. So ??? Maybe there is some other domain in other schema in your db that use "public"."citext" ?
Try to drop it yourself after Evolve fails to see if it is an Evolve issue or something else

peterhirn · 2022-01-08T19:17:23Z

Yes, very strange. I did drop and recreate the database after every failure, also using docker postgres:14.1-alpine3.15.

Passing the schema as argument eg. dotnet evolve migrate postgresql -s schema1 ... makes this work reliably:

create extension citext with schema schema1;
create domain schema1.non_empty_citext as schema1.citext check (value ~ '\S');

Give me a second, I'll try to reproduce in docker compose.

peterhirn · 2022-01-08T20:10:14Z

Note for future readers: this is just a quick hack, please don't write dockerfiles like this (do proper cleanup, copy script files, don't embed connection strings, etc.).

Dockerfile

FROM debian:11.2-slim

ENV EVOLVE_VERSION="3.1.0-alpha3"
ENV CONNECTION="Server=db;Uid=postgres;Pwd=;Database=postgres"

RUN apt-get update \
    && apt-get install -y --no-install-recommends ca-certificates wget libicu67 \
    && wget -q https://github.com/lecaillon/Evolve/releases/download/${EVOLVE_VERSION}/evolve_${EVOLVE_VERSION}_Linux-64bit.tar.gz \
    && tar -xzvf evolve_${EVOLVE_VERSION}_Linux-64bit.tar.gz -C /usr/local/bin --strip-components 1

RUN mkdir db && echo "create extension if not exists citext;" > db/V1_0_0__test.sql

RUN echo "#!/bin/bash" > test.sh \
    && echo "evolve migrate postgresql -l db -c \"${CONNECTION}\"" >> test.sh \
    && echo "evolve erase postgresql -c \"${CONNECTION}\"" >> test.sh

RUN chmod +x test.sh \
    && cat test.sh

ENTRYPOINT [ "./test.sh" ]

docker-compose.yml

version: "3.9"
services:
  db:
    image: postgres:14.1-alpine3.15
    environment:
      POSTGRES_HOST_AUTH_METHOD: trust

  evolve:
    build: .
    links:
      - db

Run

docker compose rm -f
docker compose up --build

Output

evolve-bug-evolve-1  | Successfully applied migration V1_0_0__test.sql in 21 ms.
evolve-bug-evolve-1  | Database migrated to version 1.0.0. 1 migration(s) applied in 21 ms.
evolve-bug-evolve-1  | Evolve command 'migrate' successfully executed.
evolve-bug-db-1      | 2022-01-08 19:57:23.433 UTC [57] ERROR:  syntax error at or near "LIKE" at character 16
evolve-bug-db-1      | 2022-01-08 19:57:23.433 UTC [57] STATEMENT:  SHOW VARIABLES LIKE '%version%'
evolve-bug-evolve-1  | Evolve initialized.
evolve-bug-evolve-1  | Executing Erase...
evolve-bug-db-1      | 2022-01-08 19:57:23.525 UTC [57] ERROR:  cannot drop type citext because extension citext requires it
evolve-bug-db-1      | 2022-01-08 19:57:23.525 UTC [57] HINT:  You can drop extension citext instead.
evolve-bug-db-1      | 2022-01-08 19:57:23.525 UTC [57] STATEMENT:  DROP TYPE IF EXISTS "public"."citext" CASCADE
evolve-bug-db-1      | 2022-01-08 19:57:23.526 UTC [57] ERROR:  current transaction is aborted, commands ignored until end of transaction block
evolve-bug-db-1      | 2022-01-08 19:57:23.526 UTC [57] STATEMENT:  SELECT pg_advisory_unlock(12345)
evolve-bug-evolve-1  | 25P02: current transaction is aborted, commands ignored until end of transaction block Sql query: SELECT pg_advisory_unlock(12345) 25P02: current transaction is aborted, commands ignored until end of transaction block

lecaillon · 2022-01-08T21:09:05Z

Ok, i reproduce it when no schema is defined, so when the script executes in the schema public.
Btw like you dockerfile ^^

lecaillon · 2022-01-08T21:32:40Z

Ok, think I have a fix. I will release it later tomorrow ;)

lecaillon · 2022-01-09T10:39:52Z

Just published Evolve 3.1.0-alpha4
Happy testing :)

peterhirn · 2022-01-09T12:37:26Z

The docker compose sample produces ~~the same error~~ a different error after upgrading ENV EVOLVE_VERSION="3.1.0-alpha4" 😅

Edit: Extensions are not part of a schema, even if create with create extension citext with schema schema1; see https://www.postgresql.org/docs/current/sql-createextension.html

Remember that the extension itself is not considered to be within any schema: extensions have unqualified names that must be unique database-wide. But objects belonging to the extension can be within schemas.

Therefore dropping the extensions like evolve is now doing with drop extension if exists "schema1"."citext" cascade; is unfortunately invalid

ERROR:  syntax error at or near "."
LINE 1: drop extension if exists "schema1"."citext" cascade;
                                          ^

Edit2: Dropping extensions globally with cascade is (obviously) extremely dangerous.

lecaillon · 2022-01-09T13:34:00Z

Ok, my bad. Was sure i had tested it. Unfortunately this cannot be part of my integration tests but I'm gonna try something else.
And normally no worry about your Edit2 because I only DROP extensions within the schema Evolve is responsible and for the role corresponding to the user that created it:

SELECT  e.extname 
FROM pg_extension  e 
LEFT JOIN pg_namespace n  ON n.oid = e.extnamespace 
LEFT JOIN pg_roles r ON  r.oid = e.extowner 
WHERE  n.nspname='{Name}' AND  r.rolname=current_user;

lecaillon · 2022-01-09T14:08:18Z

Just published Evolve 3.1.0-alpha5. Try me ^^

peterhirn · 2022-01-10T17:07:01Z

New error, stray double-quote 😁

evolve-bug-db-1      | 2022-01-10 16:57:34.649 UTC [57] ERROR:  unterminated quoted identifier at or near "" CASCADE" at character 32
evolve-bug-db-1      | 2022-01-10 16:57:34.649 UTC [57] STATEMENT:  DROP EXTENSION IF EXISTS citext" CASCADE

I only DROP extensions within the schema Evolve is responsible and for the role corresponding to the user that created it

Interesting, thanks for explaining. I didn't look into postgres users and roles at all yet. I'm still sceptical towards dropping extensions, maybe I'll play around with that later.

lecaillon · 2022-01-10T17:24:47Z

Tired of me ^^ Hopefully the 2nd times I added a new test scenario ... works well apparently :(

lecaillon · 2022-01-10T18:09:44Z

Ok understood why my test was successful... And it explains why deletion of a postgresql extension worked with a schema but doesn't work when associated to public.
All of this because during Erase(), Evolve DROP SCHEMA when it owns it... And keep the schema but DROP all objects one by one when it doesn't...

lecaillon · 2022-01-11T08:32:24Z

Evolve 3.1.0-alpha6 is out. What about now ? @peterhirn

peterhirn · 2022-01-11T09:31:34Z

It works! 💪

lecaillon · 2022-01-11T09:38:11Z

Thanks for your help on this one Peter. And thanks for the Dockerfile you post here and there. It is a very good starting point on how to use Evolve in a Docker pipeline

lecaillon added the bug label Jan 7, 2022

lecaillon mentioned this issue Jan 9, 2022

Fix erase public postgresql extension #256

Merged

lecaillon closed this as completed in #256 Jan 9, 2022

lecaillon reopened this Jan 9, 2022

lecaillon mentioned this issue Jan 9, 2022

Fix drop postgresql extensions #257

Merged

lecaillon closed this as completed in #257 Jan 9, 2022

lecaillon reopened this Jan 9, 2022

lecaillon closed this as completed Jan 11, 2022

lecaillon mentioned this issue Feb 13, 2022

Are there any plans to create a docker image? #248

Open

shaunsales mentioned this issue Dec 22, 2022

Error: syntax error at or near "-" at character 30 when running Evolve.Erase() #292

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

`evolve erase` tries to delete `citext` type #253

`evolve erase` tries to delete `citext` type #253

peterhirn commented Jan 7, 2022 •

edited

Loading

lecaillon commented Jan 7, 2022

peterhirn commented Jan 7, 2022

lecaillon commented Jan 8, 2022

peterhirn commented Jan 8, 2022 •

edited

Loading

lecaillon commented Jan 8, 2022 •

edited

Loading

peterhirn commented Jan 8, 2022 •

edited

Loading

peterhirn commented Jan 8, 2022

lecaillon commented Jan 8, 2022

lecaillon commented Jan 8, 2022

lecaillon commented Jan 9, 2022

peterhirn commented Jan 9, 2022 •

edited

Loading

lecaillon commented Jan 9, 2022 •

edited

Loading

lecaillon commented Jan 9, 2022 •

edited

Loading

peterhirn commented Jan 10, 2022

lecaillon commented Jan 10, 2022

lecaillon commented Jan 10, 2022

lecaillon commented Jan 11, 2022

peterhirn commented Jan 11, 2022

lecaillon commented Jan 11, 2022 •

edited

Loading

evolve erase tries to delete citext type #253

evolve erase tries to delete citext type #253

Comments

peterhirn commented Jan 7, 2022 • edited Loading

lecaillon commented Jan 7, 2022

peterhirn commented Jan 7, 2022

lecaillon commented Jan 8, 2022

peterhirn commented Jan 8, 2022 • edited Loading

lecaillon commented Jan 8, 2022 • edited Loading

peterhirn commented Jan 8, 2022 • edited Loading

peterhirn commented Jan 8, 2022

lecaillon commented Jan 8, 2022

lecaillon commented Jan 8, 2022

lecaillon commented Jan 9, 2022

peterhirn commented Jan 9, 2022 • edited Loading

lecaillon commented Jan 9, 2022 • edited Loading

lecaillon commented Jan 9, 2022 • edited Loading

peterhirn commented Jan 10, 2022

lecaillon commented Jan 10, 2022

lecaillon commented Jan 10, 2022

lecaillon commented Jan 11, 2022

peterhirn commented Jan 11, 2022

lecaillon commented Jan 11, 2022 • edited Loading

`evolve erase` tries to delete `citext` type #253

`evolve erase` tries to delete `citext` type #253

peterhirn commented Jan 7, 2022 •

edited

Loading

peterhirn commented Jan 8, 2022 •

edited

Loading

lecaillon commented Jan 8, 2022 •

edited

Loading

peterhirn commented Jan 8, 2022 •

edited

Loading

peterhirn commented Jan 9, 2022 •

edited

Loading

lecaillon commented Jan 9, 2022 •

edited

Loading

lecaillon commented Jan 9, 2022 •

edited

Loading

lecaillon commented Jan 11, 2022 •

edited

Loading