Timescale Setup issues

[pfried]

This is a question on how to setup timescale with prisma. Thanks to the post from @janpio I tried to create a similar setup. So this is
related to ##3228

My goal is to ingest IoT data into timescaledb with the help of prisma since the whole raw sql stuff is just bad (I know some stuff will stay raw sql stuff for now, especially timescaledb functionality).

My desired setup

1. Timescale 2.2.0 running on Timescale Forge
2. Prisma / Prisma Client 2.22.0 used by AWS Lambda (will do a lambda layer / package as suggested by the docs).

Following the steps

The first step would be to create a migration which will install the timescale extension since the shadow database will run the migrations too (it is obiously already installed on timescale forge). So the migration.sql file will look like:

CREATE EXTENSION IF NOT EXISTS timescaledb VERSION "2.2.0";

Case 1: Forge Database without shadow database

Try to run the npx prisma migrate dev command on the timescale forge database will result in an error since the command tries to create a database:

Error: Database error
Error querying the database: db error: ERROR: tsdb_admin: database prisma_migrate_shadow_db_ab8fe7fa-8d81-4493-bafe-bf5645e6d2cc is not an allowed database name
HINT: Contact your administrator to configure the "tsdb_admin.allowed_databases"

Following the suggestions in #4571 and here: https://www.prisma.io/docs/concepts/components/prisma-migrate#shadow-database the guess would be that it is not possible to use the timescale forge service for the shadow db. Which leads to:

Case 2: Forge database with shadow database on localhost

My localhost is a postgres 13 instance on windows with the timescale extension available.

Adding a shadow database in the environment and running the npx prisma migrate dev will result in an error telling that the timescaledb extension is already loaded (both instances run 2.2.0):

Error: P3006

Migration `20210505152706_` failed to apply cleanly to the shadow database. 
Error:
Database error
Error querying the database: db error: FEHLER: extension "timescaledb" has already been loaded with another version
DETAIL: The loaded version is "2.2.0".

If the timescale forge database has already has data a reset is suggested and if executed will result in:

Error: P3016

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error: 
Database error
Error querying the database: db error: ERROR: must be owner of view pg_stat_statements
   0: sql_migration_connector::best_effort_reset

Case 3 Local Timescale database

If no shadow database is explicitly given (meaning the migrate command will create one) everything works on the local machine (different to Case 1).

Case 4 Local Timescale database with explicit shadow database on the same server

If I explicitly set a shadow database on my local machine I will end up in the same error as Case 2:

Error: P3006

Migration `20210505152706_` failed to apply cleanly to the shadow database. 
Error:
Database error
Error querying the database: db error: FEHLER: extension "timescaledb" has already been loaded with another version
DETAIL: The loaded version is "2.2.0".

[pfried]

related #8325

[janpio]

So seems the answer here really is why in case 2 and 4 you get this error:

Error querying the database: db error: FEHLER: extension "timescaledb" has already been loaded with another version
DETAIL: The loaded version is "2.2.0"

Did you try what happens when you run CREATE EXTENSION IF NOT EXISTS timescaledb VERSION "2.2.0"; manually on a database that already has the extension enabled? Or with the extesion not enabled?

The error message both times comes from the shadow database whic has a first steps gets all the migration files you have locally applied. One of those commands gives you this error. Do you have CREATE EXTENSION IF NOT EXISTS timescaledb VERSION "2.2.0"; in 2 migration files maybe?

Reminder: TimescaleDB is not officially supported, so this being a bit more painful is expected, sorry.

[pfried]

@janpio

Sorry for not including the details here, as I posted in #8325 I do use a Timescale Cloud Database for both the db and the shadow-db. So both instances do have the extension pre-installed (currently version 2.5.1)

So running CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE; results in only in a message NOTICE: extension "timescaledb" already exists, skipping on both instances.

There is only one migration file with the extension call at the first line.

I noticed that I cannot drop the Extension and activate it in the same session, running

DROP EXTENSION IF EXISTS timescaledb;
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

results in the same error as the prisma migration:

ERROR:  extension "timescaledb" has already been loaded with another version
DETAIL:  The loaded version is "2.5.1".
HINT:  Start a new session and execute CREATE EXTENSION as the first command. Make sure to pass the "-X" flag to psql.
SQL state: 42710

I think that the error is caused by the reset prisma does on the shadow database. Also I think that reset is conditional - I guess Prisma checks if a reset is necessary - because if I run prisma migrate reset every second execution succeeds and does not yield the error.

Thank you for your support on this!

[pfried]

Link to extension docs: https://www.prisma.io/docs/guides/database/developing-with-prisma-migrate/enable-native-database-functions

I am no expert on postgres, I guess the public schema somehow gets recreated or so, maybe I need to dig into the source a bit

[pascalporedda]

Hey @pfried, how did you end up solving your problem? (If you ever did :D)

I came across this issue recently as well, and it's really bugging me as of right now, the migrations simply won't work, no matter what I do

[pfried]

@pascalporedda This is not solved, I have to run the reset command twice for it to work. Other commands do not work due to this error (e.g. prisma migrate)

I do have a quite nice setup with timescale and prisma now so even if timescale is not supported I think it would be worth the effort to tackle this issue (which is basically true for every postgers database as a service offering where certain extensions are pre installed)

[rickbatka]

I have this exact same problem. I have a migration that only contains CREATE EXTENSION IF NOT EXISTS timescaledb;

It fails every second time I run prisma migrate reset!

It must be something to do with the shadow database if it works every other time.

[rickbatka]

I can confirm this is still not resolved. @pascalporedda Did you ever find a more robust workaround?

@janpio The easiest way to reproduce this is to create an empty migration via prisma migrate dev --create-only and add this line to the migration:

CREATE EXTENSION IF NOT EXISTS timescaledb;

Every second call to prisma migrate reset from that point on will fail.

This really does feel like a bug in Prisma's handling of the shadow database, as from a pure SQL perspective that statement should be completely idempotent and safe to run repeatedly on the same server.

[pascalporedda]

@rickbatka Sadly I didn't. Whenever I reset, I run it twice / my script runs it twice for me.

[IlCallo]

Quick workaround is to create a package.json script which re-run the commands if it fails the first time:

{
  "script": {
    "migrate:reset": "prisma migrate reset -f || prisma migrate reset -f"
  }
}

-f forces the command to skip confirmation prompt