[Solved, request clarification] Adding a unique constraint causes failing query

Hey all, I'm looking to migrate a Laravel application to Neon. While running migrations I run into the following error:

SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block (Connection: pgsql, SQL: alter table "users" add constraint "users_email_unique" unique ("email"))

SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block (Connection: pgsql, SQL: alter table "users" add constraint "users_email_unique" unique ("email"))

The queries being executed are:

create table "users" ("id" bigserial not null primary key, "name" varchar(255) not null, "email" varchar(255) null, "type" varchar(255) not null default 'REGULAR', "discord_snowflake" varchar(255) null, "discord_avatar" varchar(255) null, "remember_token" varchar(100) null, "created_at" timestamp(0) without time zone null, "updated_at" timestamp(0) without time zone null);
alter table "users" add constraint "users_email_unique" unique ("email");

create table "users" ("id" bigserial not null primary key, "name" varchar(255) not null, "email" varchar(255) null, "type" varchar(255) not null default 'REGULAR', "discord_snowflake" varchar(255) null, "discord_avatar" varchar(255) null, "remember_token" varchar(100) null, "created_at" timestamp(0) without time zone null, "updated_at" timestamp(0) without time zone null);
alter table "users" add constraint "users_email_unique" unique ("email");

The queries are being executed in a transaction. Running these queries separately in the console works just fine. Am I doing something wrong here? Thanks!

9 Replies

genetic-orangeOP•16mo ago

As I was writing this post up I found out it has to do with pooled vs non-pooled connections. Running the migrations in a pooled connection just doesn't work (if anyone could explain this that would be awesome), but running it through a non-pooled one works just fine

protestant-coral•16mo ago

what are you using to manage your migrations

genetic-orangeOP•16mo ago

I use Laravel’s built-in migrations Each migration is run within a transaction by default From what I gather, creating a table and altering it within a transaction on a pooled connection just doesn’t play nice See https://github.com/laravel/framework/blob/6ce800fdafdb0ed17ae62c65a78f8ced9b10003d/src/Illuminate/Database/Migrations/Migration.php#L19 And https://github.com/laravel/framework/blob/6ce800fdafdb0ed17ae62c65a78f8ced9b10003d/src/Illuminate/Database/Migrations/Migrator.php#L406

GitHub

framework/src/Illuminate/Database/Migrations/Migration.php at 6ce80...

The Laravel Framework. Contribute to laravel/framework development by creating an account on GitHub.

GitHub

framework/src/Illuminate/Database/Migrations/Migrator.php at 6ce800...

The Laravel Framework. Contribute to laravel/framework development by creating an account on GitHub.

genetic-orange•14mo ago

@SereninSparks Did you disable transaction in migration? I was forced to use non pooled connection because of this issue.

genetic-orangeOP•14mo ago

I ended up making a separate connection in my database.php for non-pooled and specifically use that for Artisan commands where needed The connection string then uses a separate environment variable. Something like DB_URL_UNPOOLED

genetic-orange•14mo ago

So you ran php artisan migrate --database=unpooled on your server? Did you use Laravel Octane? Is it worth to enable database.session_persist to persist database connection per request if pooled connection is used?

genetic-orangeOP•14mo ago

Pretty much, though I used it in a serverless environment I used Octane but haven’t played around enough to answer that for you

genetic-orange•14mo ago

Are you using bref.sh or Laravel Vapor? I am thinking to use different value DB_URL for cli and web instead of creating new connection in config/database.php with new environment variable. I want to avoid adding extra secret in Vapor because of AWS billing charges and I have multiple Laravel projects running right now.

genetic-orangeOP•14mo ago

I’m using Bref and I store the secrets in a SecretString in ParameterStore As far as I’m aware that should be sufficient and doesn’t cost nearly as much

Gaming

Programming

[Solved, request clarification] Adding a unique constraint causes failing query

Did you find this page helpful?