Country Codes data lookup broken!

This psycopg2.OperationalError is what's killing me. It points to the database, but the DB itself seems fine when I connect directly.

I understand the frustration when direct database access works, but your application's connection fails, particularly for specific queries. First, a quick note: you mentioned "i'm completely stuck here" – it's generally "I'm". Easy to miss when you're deep in debugging!

The psycopg2.OperationalError: server closed the connection unexpectedly, despite direct PostgreSQL connections succeeding, strongly points to a few common culprits within the application's specific execution context or the database's handling of that context, rather than a general database health issue. Here’s what you should focus on:

1. Statement Timeout: This is the most frequent cause. Your specific country_data_lookup query might be taking longer than expected for certain parameters, exceeding a configured timeout. Check your PostgreSQL server's postgresql.conf for statement_timeout. If it's set (e.g., 30s), a query running longer than that will be terminated by the server, resulting in this error. Also, review your application code or ORM configuration for any client-side query timeouts.
2. Database Connection Pooling: Since you're dealing with a web tool, you should be using a connection pool. Misconfigured or exhausted connection pools can lead to issues. Ensure your psycopg2 connections are properly managed within a pool (e.g., using SQLAlchemy's connection pooling or a direct psycopg2 connection pool manager). Parameters like pool_size, max_overflow, and timeout (for acquiring a connection from the pool) are critical. If the pool is exhausted or handing out stale connections, you'll see unexpected closures.
3. Query Performance: While direct access works, the specific query executed by fetch_country_data might be inefficient for the given query_param, especially if it involves complex LIKE clauses or joins on large datasets. Enable log_min_duration_statement = 0 in your postgresql.conf temporarily to log all queries and their execution times, then look for the one corresponding to your failing lookup. This will help identify if the query itself is the bottleneck and improve overall API performance monitoring.
4. Resource Limits (Subtle): Less likely if other app functions work, but ensure the PostgreSQL user your application connects with doesn't have specific connection or resource limits that are being hit only during this intensive lookup.

Start by aggressively logging the query execution times on both the application side and within PostgreSQL. This will quickly tell you if the query is simply taking too long to complete, causing a timeout.

Okay, the statement_timeout is such a good shout, I'm almost positive that's it. Really can't believe I overlooked that! And honestly, you just saved me like three hours of googling more obscure stuff.