Wasp•4mo ago

❓ PostgreSQL Insert Appears Stuck — No Error, Just Hanging / Deadlocking

Hi all, I'm dealing with a persistent issue when trying to insert rows into my new PostgreSQL table (KeywordInsight). My insert statements don’t fail or throw any errors — they simply hang indefinitely. This behavior persists even for single-row inserts. It works on dev side and I deployed and the table is also visible on prod side but empty. ✅ Setup Python app using psycopg2 and a connection pool (SimpleConnectionPool) The database and other tables work fine Insert uses this structure (note: it's string-interpolated, not parameterized):

INSERT INTO "KeywordInsight" (
    "keyword", "volume12m", "volume12mNL", "volume12mBE", "volumeLastMonth",
    "listings", "sponsoredListings", "medianPrice", "medianReview", "medianReviews", "extraFilterOptions"
) VALUES (
    'example keyword', 100, 50, 50, 10,
    200, 5, 29.99, 4.5, 25, '{}'
) ON CONFLICT ("keyword") DO NOTHING;

INSERT INTO "KeywordInsight" (
    "keyword", "volume12m", "volume12mNL", "volume12mBE", "volumeLastMonth",
    "listings", "sponsoredListings", "medianPrice", "medianReview", "medianReviews", "extraFilterOptions"
) VALUES (
    'example keyword', 100, 50, 50, 10,
    200, 5, 29.99, 4.5, 25, '{}'
) ON CONFLICT ("keyword") DO NOTHING;

model KeywordInsight {
  id                 Int       @id @default(autoincrement())
  keyword            String    @unique

  volume12m          Int       @default(0)
  volume12mNL        Int       @default(0)
  volume12mBE        Int       @default(0)
  volumeLastMonth    Int       @default(0)
  listings           Int
  sponsoredListings  Int
  medianPrice        Float
  medianReview       Float
  medianReviews       Int
  extraFilterOptions String?   // JSON string (geëncodeerd), consistent met rest van systeem

  updatedAt          DateTime  @updatedAt
}

model KeywordInsight {
  id                 Int       @id @default(autoincrement())
  keyword            String    @unique

  volume12m          Int       @default(0)
  volume12mNL        Int       @default(0)
  volume12mBE        Int       @default(0)
  volumeLastMonth    Int       @default(0)
  listings           Int
  sponsoredListings  Int
  medianPrice        Float
  medianReview       Float
  medianReviews       Int
  extraFilterOptions String?   // JSON string (geëncodeerd), consistent met rest van systeem

  updatedAt          DateTime  @updatedAt
}

❗ Symptoms When this query runs, no result returns, and it never finishes. I see the query listed in pg_stat_activity in state = 'active', but it stays there for hours. Even if I forcibly kill the PID via pg_terminate_backend, it comes back on re-run. This only happens on this table — other inserts (same connection style) work fine. 🧪 What I Tried Killing all lingering sessions using pg_stat_activity Restarting the script, terminals, and the machine itself Checking for locks via pg_locks, pg_stat_activity, etc. Using ON CONFLICT DO NOTHING in hopes it would skip collisions gracefully

7 Replies

kapa.ai•4mo ago

Hi @Sven. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave: ---------------------- I notice your question is about a PostgreSQL issue that appears to be unrelated to Wasp, which is a Rails-like framework for React, Node.js, and Prisma. While I can see you're having trouble with PostgreSQL inserts hanging, I'm specifically tuned to answer questions about the Wasp framework. The knowledge sources provided don't contain information about debugging general PostgreSQL issues outside of the Wasp context. If you're using Wasp with PostgreSQL and experiencing issues, I'd be happy to help with that specific integration. For example, Wasp provides commands like wasp start db which runs PostgreSQL in a Docker container, and there are some known issues with IPv4/IPv6 connectivity that might be relevant. Would you like me to help with a Wasp-specific PostgreSQL issue instead? Or perhaps you could clarify if your question is related to using PostgreSQL within a Wasp application? ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ---------------------- Sorry @Sven, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist: Perhaps try again in 15mins?

SvenOP•4mo ago

✅ Context: SalesData Inserts Work Perfectly To clarify: I have another function in my codebase that inserts into the SalesData table using this— same Python connection pool, same manual f-string SQL, same connection/commit logic — and this works reliably without hanging. Here’s the key structure that works:

cursor.execute(
    f"INSERT INTO \"SalesData\" (\"productId\", \"period\", \"avgPrice\", \"avgStock\", \"totalSales\", \"totalRevenue\") "
    f"VALUES ({product_id}, NOW(), {float(currentprice)}, {float(currentstock)}, {float(total_sales)}, {float(total_sales) * float(currentprice)});"
)
connection.commit()

cursor.execute(
    f"INSERT INTO \"SalesData\" (\"productId\", \"period\", \"avgPrice\", \"avgStock\", \"totalSales\", \"totalRevenue\") "
    f"VALUES ({product_id}, NOW(), {float(currentprice)}, {float(currentstock)}, {float(total_sales)}, {float(total_sales) * float(currentprice)});"
)
connection.commit()

Filip•4mo ago

Hey @Sven, this is a strange one. Let's figure it out. First, can I ask you to try the raw insert query over the database directly (without Wasp or Python). If this hangs, we know it's a database problem and can focus on that. If it succeeds, we'll take a look at the code.

SvenOP•4mo ago

it gets deadlocked here to

SvenOP•4mo ago

or like nothing happens... i ran:

SELECT pid, usename, application_name, backend_type, query_start, state, query
FROM pg_stat_activity
WHERE query ILIKE '%KeywordInsight%'
   OR backend_type ILIKE '%app%';

SELECT pid, usename, application_name, backend_type, query_start, state, query
FROM pg_stat_activity
WHERE query ILIKE '%KeywordInsight%'
   OR backend_type ILIKE '%app%';

and this is what it gave:

  pid  | usename  | application_name |  backend_type  |          query_start          | state  | query  
  
 28722 | postgres | psql             | client backend | 2025-05-19 18:49:51.104894+00 | active | SELECT pid, usename, application_name, backend_type, query_start, state, query         FROM pg_stat_activity

  pid  | usename  | application_name |  backend_type  |          query_start          | state  | query  
  
 28722 | postgres | psql             | client backend | 2025-05-19 18:49:51.104894+00 | active | SELECT pid, usename, application_name, backend_type, query_start, state, query         FROM pg_stat_activity

also when i proxy the db and look at it through prisma studio it doesnt show anything and infinitly fetches rows for table, al ldat ais their because app works. trying to alter the table name doesnt work too... so i think it is not the code.. Anyone thst can help?

Filip•4mo ago

Ok, glad to hear it's not Wasp, but sad to hear you've got an error I think I can figure it out, I'll just need a dump of your entire database to reproduce it (or a connection string to production). You can share this with me privately if you're comfortable. If not, can you create a small minimum reproduction database and share that?

SvenOP•4mo ago

thanks for being able to help me!

Gaming

Programming

❓ PostgreSQL Insert Appears Stuck — No Error, Just Hanging / Deadlocking

Did you find this page helpful?