postgresql_skill

Overview

This skill provides practical, documentation-derived guidance for PostgreSQL: connection management, SQL, schema design, administration, and performance tuning. It focuses on concrete API usage, connection patterns, and operational behaviors you need when building or maintaining PostgreSQL-backed systems. Use it to get concise, actionable instructions and recommended workflows for common PostgreSQL tasks.

How this skill works

The skill inspects common PostgreSQL client behaviors and server features, documenting connection functions, async connection patterns, socket polling, and default connection options. It explains how to construct connection strings and parameter arrays, handle nonblocking connects, check connection status, and manage environment and fallback defaults. It highlights safety considerations (search_path, forking, SSL/GSS negotiation) and tools for enumerating connection parameters.

When to use it

• Establishing or troubleshooting libpq connections from applications.
• Implementing nonblocking/asynchronous connection logic in clients.
• Building secure connection strings and setting connection parameters.
• Tuning or auditing connection-related behavior in production systems.
• Teaching or reviewing PostgreSQL client-side best practices.

Best practices

• Always check PQstatus after obtaining a PGconn before issuing queries.
• Remove publicly writable schemas from search_path at session start to avoid injection risks.
• Prefer PQconnectdbParams (or nonblocking variants) for extensible parameter handling.
• Avoid forking processes with open libpq sockets; exec after fork if needed.
• Use PQconnectStart/PQconnectPoll loop or PQsocketPoll for event-driven connection handling.
• Call PQfinish for every PGconn pointer returned, even on failed attempts.

Example use cases

• Convert blocking connection logic to nonblocking event-loop friendly code with PQconnectStart and PQconnectPoll.
• Build a connection dialog that enumerates PQconndefaults to display available connect options and defaults.
• Harden application startup by setting search_path to an empty value or using set_config after connect.
• Implement a custom timeout for async connects because connect_timeout is ignored with PQconnectPoll.
• Diagnose intermittent connection failures by inspecting PQstatus and polling return states.