Advanced Features

package org.postgresql;

import java.sql.SQLException;

/**
 * Notification received via LISTEN/NOTIFY.
 */
public interface PGNotification {
    /**
     * Returns the notification channel name.
     *
     * @return Channel name
     */
    String getName();

    /**
     * Returns the process ID of the notifying backend.
     *
     * @return Backend process ID
     */
    int getPID();

    /**
     * Returns the notification payload (PostgreSQL 9.0+).
     * Empty string for notifications without payload.
     *
     * @return Payload string
     */
    String getParameter();
}

/**
 * Methods for receiving notifications (on PGConnection).
 */
public interface PGConnection {
    /**
     * Returns notifications received since last call.
     * Non-blocking - returns immediately.
     *
     * @return Array of notifications, or null if none available
     * @throws SQLException if retrieval fails
     */
    PGNotification[] getNotifications() throws SQLException;

    /**
     * Returns notifications with timeout.
     * Blocks until notifications arrive or timeout expires.
     *
     * @param timeoutMillis Timeout in milliseconds
     *                      (0 = block forever, >0 = wait up to specified time)
     * @return Array of notifications, or null if timeout expires
     * @throws SQLException if retrieval fails
     */
    PGNotification[] getNotifications(int timeoutMillis) throws SQLException;
}

import org.postgresql.PGConnection;
import org.postgresql.PGNotification;
import java.sql.*;

// Example 1: Basic LISTEN/NOTIFY
public class ListenNotifyExample {
    public static void setupListener(Connection conn) throws SQLException {
        PGConnection pgConn = conn.unwrap(PGConnection.class);

        // Listen on a channel
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("LISTEN my_channel");
        }

        // Poll for notifications
        while (true) {
            // Check for notifications (non-blocking)
            PGNotification[] notifications = pgConn.getNotifications();

            if (notifications != null) {
                for (PGNotification notification : notifications) {
                    System.out.println("Channel: " + notification.getName());
                    System.out.println("PID: " + notification.getPID());
                    System.out.println("Payload: " + notification.getParameter());
                }
            }

            // Wait a bit before next check
            try {
                Thread.sleep(500);
            } catch (InterruptedException e) {
                break;
            }
        }
    }
}

// Example 2: Blocking notification wait
public class BlockingNotifyExample {
    public static void waitForNotification(Connection conn) throws SQLException {
        PGConnection pgConn = conn.unwrap(PGConnection.class);

        try (Statement stmt = conn.createStatement()) {
            stmt.execute("LISTEN events");
        }

        // Block until notification arrives (or 30 second timeout)
        PGNotification[] notifications = pgConn.getNotifications(30000);

        if (notifications != null) {
            for (PGNotification notification : notifications) {
                System.out.println("Received: " + notification.getParameter());
            }
        } else {
            System.out.println("Timeout - no notifications");
        }
    }
}

// Example 3: Send notifications
public class SendNotifyExample {
    public static void sendNotification(Connection conn, String message)
            throws SQLException {
        String sql = "SELECT pg_notify('my_channel', ?)";

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, message);
            pstmt.executeQuery();
        }
    }

    // Alternative: NOTIFY command
    public static void sendNotifyCommand(Connection conn, String message)
            throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("NOTIFY my_channel, '" + message + "'");
        }
    }
}

// Example 4: Multi-threaded listener
public class ThreadedListenerExample {
    public static void startListenerThread(Connection conn) {
        Thread listenerThread = new Thread(() -> {
            try {
                PGConnection pgConn = conn.unwrap(PGConnection.class);

                try (Statement stmt = conn.createStatement()) {
                    stmt.execute("LISTEN updates");
                }

                while (!Thread.currentThread().isInterrupted()) {
                    PGNotification[] notifications = pgConn.getNotifications(5000);

                    if (notifications != null) {
                        for (PGNotification notification : notifications) {
                            handleNotification(notification);
                        }
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });

        listenerThread.start();
    }

    private static void handleNotification(PGNotification notification) {
        // Process notification
        System.out.println("Update: " + notification.getParameter());
    }
}

package org.postgresql;

import java.sql.SQLException;
import java.sql.Statement;

/**
 * Query cancellation methods.
 */
public interface PGConnection {
    /**
     * Cancels the currently executing query on this connection.
     * Sends cancel request to the backend.
     * Query will abort with an error.
     *
     * @throws SQLException if cancellation fails
     */
    void cancelQuery() throws SQLException;

    /**
     * Returns the backend process ID.
     * Useful for identifying connections in pg_stat_activity.
     *
     * @return Backend PID
     */
    int getBackendPID();
}

/**
 * Standard Statement cancellation (also supported).
 */
public interface Statement {
    /**
     * Cancels the current statement execution.
     * Can be called from another thread.
     *
     * @throws SQLException if cancellation fails
     */
    void cancel() throws SQLException;

    /**
     * Sets query timeout.
     *
     * @param seconds Timeout in seconds (0 = no timeout)
     * @throws SQLException if timeout cannot be set
     */
    void setQueryTimeout(int seconds) throws SQLException;
}

import org.postgresql.PGConnection;
import java.sql.*;

// Example 1: Cancel query from connection
public class CancelQueryExample {
    public static void cancelLongQuery(Connection conn) throws SQLException {
        PGConnection pgConn = conn.unwrap(PGConnection.class);

        // Start query in separate thread
        Thread queryThread = new Thread(() -> {
            try (Statement stmt = conn.createStatement()) {
                stmt.executeQuery("SELECT pg_sleep(60)"); // Long query
            } catch (SQLException e) {
                System.out.println("Query cancelled: " + e.getMessage());
            }
        });

        queryThread.start();

        // Wait a bit, then cancel
        try {
            Thread.sleep(2000);
            pgConn.cancelQuery(); // Cancel the query
        } catch (InterruptedException e) {
            Thread.currentThread().interrupt();
        }
    }
}

// Example 2: Cancel from Statement
public class StatementCancelExample {
    private volatile Statement currentStatement;

    public void executeLongQuery(Connection conn) throws SQLException {
        currentStatement = conn.createStatement();

        try {
            currentStatement.executeQuery("SELECT * FROM huge_table");
        } finally {
            currentStatement = null;
        }
    }

    public void cancelCurrentQuery() throws SQLException {
        if (currentStatement != null) {
            currentStatement.cancel();
        }
    }
}

// Example 3: Query timeout
public class QueryTimeoutExample {
    public static void queryWithTimeout(Connection conn) throws SQLException {
        String sql = "SELECT * FROM large_table";

        try (Statement stmt = conn.createStatement()) {
            stmt.setQueryTimeout(30); // 30 second timeout

            try (ResultSet rs = stmt.executeQuery(sql)) {
                while (rs.next()) {
                    // Process results
                }
            }
        } catch (SQLException e) {
            if (e.getSQLState().equals("57014")) {
                System.out.println("Query timeout exceeded");
            }
            throw e;
        }
    }
}

package org.postgresql;

import java.sql.SQLException;

/**
 * SQL escaping methods on PGConnection.
 */
public interface PGConnection {
    /**
     * Escapes an identifier for safe use in SQL.
     * Adds quotes if necessary and escapes embedded quotes.
     *
     * @param identifier Identifier to escape (table name, column name, etc.)
     * @return Properly escaped identifier
     * @throws SQLException if escaping fails
     */
    String escapeIdentifier(String identifier) throws SQLException;

    /**
     * Escapes a string literal for safe use in SQL.
     * Properly escapes quotes and backslashes.
     * Note: Returns null for null input.
     *
     * @param literal String literal to escape
     * @return Properly escaped literal
     * @throws SQLException if escaping fails
     */
    String escapeLiteral(String literal) throws SQLException;
}

import org.postgresql.PGConnection;
import java.sql.*;

// Example: SQL escaping
public class SQLEscapingExample {
    public static void safeQuery(Connection conn, String tableName,
                                 String columnName, String value)
            throws SQLException {
        PGConnection pgConn = conn.unwrap(PGConnection.class);

        // Escape identifier (table/column names)
        String escapedTable = pgConn.escapeIdentifier(tableName);
        String escapedColumn = pgConn.escapeIdentifier(columnName);

        // Escape literal value
        String escapedValue = pgConn.escapeLiteral(value);

        // Build safe query (for dynamic SQL only - prefer PreparedStatement!)
        String sql = "SELECT * FROM " + escapedTable +
                    " WHERE " + escapedColumn + " = " + escapedValue;

        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            // Process results
        }
    }

    public static void demonstrateEscaping(Connection conn) throws SQLException {
        PGConnection pgConn = conn.unwrap(PGConnection.class);

        // Identifier with special characters
        String table = "user's table";
        System.out.println(pgConn.escapeIdentifier(table));
        // Output: "user's table"

        // Literal with quotes
        String name = "O'Brien";
        System.out.println(pgConn.escapeLiteral(name));
        // Output: 'O''Brien'

        // Identifier that needs quotes
        String column = "Order Date";
        System.out.println(pgConn.escapeIdentifier(column));
        // Output: "Order Date"

        // Simple identifier (no quotes needed)
        String simpleCol = "id";
        System.out.println(pgConn.escapeIdentifier(simpleCol));
        // Output: id
    }
}

package org.postgresql;

import java.util.Map;

/**
 * Server parameter access on PGConnection.
 */
public interface PGConnection {
    /**
     * Returns all server parameters reported by PostgreSQL.
     * PostgreSQL reports GUC_REPORT parameters like server_version,
     * TimeZone, DateStyle, client_encoding, etc.
     *
     * @return Unmodifiable map of parameter names to values
     */
    Map<String, String> getParameterStatuses();

    /**
     * Returns a specific server parameter value.
     *
     * @param parameterName Parameter name (case-insensitive)
     * @return Parameter value, or null if not reported
     */
    String getParameterStatus(String parameterName);
}

import org.postgresql.PGConnection;
import java.sql.*;
import java.util.Map;

// Example: Server parameters
public class ServerParametersExample {
    public static void showServerInfo(Connection conn) throws SQLException {
        PGConnection pgConn = conn.unwrap(PGConnection.class);

        // Get specific parameters
        String version = pgConn.getParameterStatus("server_version");
        String timezone = pgConn.getParameterStatus("TimeZone");
        String encoding = pgConn.getParameterStatus("client_encoding");
        String dateStyle = pgConn.getParameterStatus("DateStyle");

        System.out.println("Server version: " + version);
        System.out.println("Timezone: " + timezone);
        System.out.println("Encoding: " + encoding);
        System.out.println("DateStyle: " + dateStyle);

        // Get all parameters
        Map<String, String> params = pgConn.getParameterStatuses();
        System.out.println("\nAll parameters:");
        for (Map.Entry<String, String> entry : params.entrySet()) {
            System.out.println("  " + entry.getKey() + " = " + entry.getValue());
        }
    }
}

package org.postgresql.jdbc;

/**
 * Specifies which protocol mode is used to execute queries.
 * - SIMPLE: Uses 'Q' execute (no parse, no bind, text mode only)
 * - EXTENDED: Always uses bind/execute messages
 * - EXTENDED_FOR_PREPARED: Extended for prepared statements only
 * - EXTENDED_CACHE_EVERYTHING: Extended with aggressive caching
 *
 * Note: This is primarily for debugging and performance tuning.
 */
public enum PreferQueryMode {
    /**
     * Simple query protocol ('Q' execute).
     * No parse/bind steps, text mode only.
     * Faster for simple queries but limited features.
     */
    SIMPLE,

    /**
     * Extended protocol for prepared statements only.
     * Regular statements use simple protocol.
     * Default behavior for most use cases.
     */
    EXTENDED_FOR_PREPARED,

    /**
     * Always use extended protocol.
     * All queries use parse/bind/execute.
     * Better for complex queries and type handling.
     */
    EXTENDED,

    /**
     * Extended protocol with aggressive caching.
     * Caches all queries, even non-prepared ones.
     * Maximum performance but higher memory usage.
     */
    EXTENDED_CACHE_EVERYTHING;

    /**
     * Parses query mode from string value.
     * Falls back to EXTENDED if mode string doesn't match.
     *
     * @param mode Mode string (case-sensitive)
     * @return PreferQueryMode enum constant
     */
    public static PreferQueryMode of(String mode);

    /**
     * Returns the string value of this mode.
     *
     * @return Lowercase string representation
     */
    public String value();
}

/**
 * Method to get current query mode (on PGConnection).
 */
public interface PGConnection {
    /**
     * Returns the query execution mode for this connection.
     *
     * @return Current query mode
     */
    PreferQueryMode getPreferQueryMode();
}

import org.postgresql.PGConnection;
import org.postgresql.jdbc.PreferQueryMode;
import java.sql.*;

// Example: Check query mode
public class QueryModeExample {
    public static void checkQueryMode(Connection conn) throws SQLException {
        PGConnection pgConn = conn.unwrap(PGConnection.class);

        PreferQueryMode mode = pgConn.getPreferQueryMode();
        System.out.println("Current query mode: " + mode.value());

        // Mode can be set via connection property:
        // jdbc:postgresql://localhost/db?preferQueryMode=simple
    }
}

package org.postgresql.util;

import java.sql.SQLException;
import java.io.Serializable;

/**
 * PostgreSQL-specific SQLException.
 */
public class PSQLException extends SQLException {
    /**
     * Returns detailed server error message.
     *
     * @return ServerErrorMessage with additional details
     */
    public ServerErrorMessage getServerErrorMessage();
}

/**
 * Detailed error/notice message from PostgreSQL server.
 */
public class ServerErrorMessage implements Serializable {
    /**
     * Returns the severity level of the error.
     * Common values: ERROR, FATAL, PANIC, WARNING, NOTICE, DEBUG, INFO, LOG
     */
    public String getSeverity();

    /**
     * Returns SQL state code.
     */
    public String getSQLState();

    /**
     * Returns primary error message.
     */
    public String getMessage();

    /**
     * Returns detail message providing more context.
     */
    public String getDetail();

    /**
     * Returns hint message with suggestions.
     */
    public String getHint();

    /**
     * Returns character position of error in query.
     */
    public int getPosition();

    /**
     * Returns internal query that caused error.
     */
    public String getInternalQuery();

    /**
     * Returns position in internal query.
     */
    public int getInternalPosition();

    /**
     * Returns context information (where error occurred).
     */
    public String getWhere();

    /**
     * Returns source file name where error was detected.
     */
    public String getFile();

    /**
     * Returns source line number.
     */
    public int getLine();

    /**
     * Returns source routine name.
     */
    public String getRoutine();

    /**
     * Returns schema name (for constraint violations).
     */
    public String getSchema();

    /**
     * Returns table name (for constraint violations).
     */
    public String getTable();

    /**
     * Returns column name (for constraint violations).
     */
    public String getColumn();

    /**
     * Returns data type name (for type-related errors).
     */
    public String getDatatype();

    /**
     * Returns constraint name (for constraint violations).
     */
    public String getConstraint();
}

/**
 * SQL State codes enumeration.
 */
public enum PSQLState {
    CONNECTION_FAILURE,
    COMMUNICATION_ERROR,
    UNIQUE_VIOLATION,
    FOREIGN_KEY_VIOLATION,
    CHECK_VIOLATION,
    NOT_NULL_VIOLATION,
    SERIALIZATION_FAILURE,
    DEADLOCK_DETECTED,
    // ... many more

    /**
     * Gets the 5-character SQL state code.
     */
    public String getState();

    /**
     * Checks if SQL state indicates connection error.
     */
    public static boolean isConnectionError(String psqlState);
}

import org.postgresql.util.PSQLException;
import org.postgresql.util.ServerErrorMessage;
import org.postgresql.util.PSQLState;
import java.sql.*;

// Example: Detailed error handling
public class ErrorHandlingExample {
    public static void handleDetailedError(Connection conn) {
        try {
            try (Statement stmt = conn.createStatement()) {
                stmt.execute("INSERT INTO users (email) VALUES ('duplicate@example.com')");
            }
        } catch (SQLException e) {
            if (e instanceof PSQLException) {
                PSQLException psqlEx = (PSQLException) e;
                ServerErrorMessage serverError = psqlEx.getServerErrorMessage();

                System.out.println("SQL State: " + serverError.getSQLState());
                System.out.println("Message: " + serverError.getMessage());
                System.out.println("Detail: " + serverError.getDetail());
                System.out.println("Hint: " + serverError.getHint());
                System.out.println("Position: " + serverError.getPosition());
                System.out.println("Constraint: " + serverError.getConstraint());
                System.out.println("Table: " + serverError.getTable());
                System.out.println("Column: " + serverError.getColumn());

                // Check specific error types
                if ("23505".equals(e.getSQLState())) {
                    System.out.println("Unique constraint violation");
                } else if ("23503".equals(e.getSQLState())) {
                    System.out.println("Foreign key violation");
                }
            }
        }
    }

    public static void handleConnectionError(SQLException e) {
        if (PSQLState.isConnectionError(e.getSQLState())) {
            System.out.println("Connection error - attempt reconnect");
        }
    }
}

package org.postgresql.util;

/**
 * Password utility for encryption.
 */
public class PasswordUtil {
    /**
     * Encodes password with SCRAM-SHA-256.
     *
     * @param password Password to encode
     * @return Encoded password hash
     * @throws SQLException if encoding fails
     */
    public static String encodeScramSha256(char[] password) throws SQLException;

    /**
     * Encodes password with MD5 (legacy, not recommended).
     *
     * @param user Username
     * @param password Password to encode
     * @return MD5 hash
     * @throws SQLException if encoding fails
     */
    public static String encodeMd5(String user, char[] password) throws SQLException;

    /**
     * Encodes password with specified type.
     *
     * @param user Username
     * @param password Password
     * @param encryptionType "md5" or "scram-sha-256"
     * @return Encoded password
     * @throws SQLException if encoding fails
     */
    public static String encodePassword(String user, char[] password,
                                       String encryptionType) throws SQLException;

    /**
     * Generates ALTER USER SQL for password change.
     *
     * @param user Username
     * @param password New password
     * @param encryptionType Encryption type
     * @return ALTER USER SQL command
     * @throws SQLException if generation fails
     */
    public static String genAlterUserPasswordSQL(String user, char[] password,
                                                 String encryptionType) throws SQLException;
}

/**
 * Bytea encoding/decoding.
 */
public class PGbytea {
    /**
     * Converts bytea string to bytes.
     */
    public static byte[] toBytes(byte[] s) throws SQLException;

    /**
     * Converts bytes to PostgreSQL bytea string.
     */
    public static String toPGString(byte[] buf);

    /**
     * Converts to PostgreSQL literal.
     */
    public static String toPGLiteral(Object value);
}

/**
 * Shared timer for managing connection timeouts across all connections.
 * This is used internally by the driver to efficiently manage timeouts
 * without creating a separate timer thread for each connection.
 */
public class SharedTimer {
    /**
     * Creates a new SharedTimer instance.
     */
    public SharedTimer();

    /**
     * Gets the reference count of active users of this timer.
     *
     * @return Number of active references
     */
    public int getRefCount();

    /**
     * Gets the Timer instance, creating it if necessary.
     * Increments the reference count.
     *
     * @return Timer instance for scheduling tasks
     */
    public java.util.Timer getTimer();

    /**
     * Releases the timer, decrementing the reference count.
     * When reference count reaches zero, the timer is cancelled.
     */
    public void releaseTimer();
}

package org.postgresql;

import org.checkerframework.checker.nullness.qual.Nullable;

/**
 * Interface for ref cursor based result sets.
 *
 * @deprecated As of 8.0, this interface is only present for backwards-compatibility.
 *             New code should call getString() on the ResultSet that contains the
 *             refcursor to obtain the underlying cursor name.
 */
@Deprecated
public interface PGRefCursorResultSet {
    /**
     * Returns the name of the ref cursor.
     *
     * @return The cursor name, or null
     * @deprecated As of 8.0, replaced with calling getString() on the ResultSet
     *             that this ResultSet was obtained from.
     */
    @Deprecated
    @Nullable
    String getRefCursor();
}

import org.postgresql.PGRefCursorResultSet;
import java.sql.*;

// Old approach (deprecated) - for backwards compatibility only
public class OldRefCursorExample {
    public static void useOldRefCursor(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // Begin transaction
            stmt.execute("BEGIN");

            // Call function that returns ref cursor
            ResultSet rs = stmt.executeQuery("SELECT my_cursor_function()");

            if (rs.next() && rs instanceof PGRefCursorResultSet) {
                PGRefCursorResultSet refCursorRs = (PGRefCursorResultSet) rs;
                String cursorName = refCursorRs.getRefCursor();

                // Fetch from cursor
                try (Statement cursorStmt = conn.createStatement();
                     ResultSet cursorRs = cursorStmt.executeQuery("FETCH ALL FROM \"" + cursorName + "\"")) {
                    while (cursorRs.next()) {
                        // Process results
                    }
                }
            }

            stmt.execute("COMMIT");
        }
    }
}

// New approach (recommended) - use getString() instead
public class NewRefCursorExample {
    public static void useRefCursor(Connection conn) throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // Begin transaction
            stmt.execute("BEGIN");

            // Call function that returns ref cursor
            try (ResultSet rs = stmt.executeQuery("SELECT my_cursor_function()")) {
                if (rs.next()) {
                    // Get cursor name using standard getString()
                    String cursorName = rs.getString(1);

                    // Fetch from cursor
                    try (Statement cursorStmt = conn.createStatement();
                         ResultSet cursorRs = cursorStmt.executeQuery("FETCH ALL FROM \"" + cursorName + "\"")) {
                        while (cursorRs.next()) {
                            // Process results
                            System.out.println(cursorRs.getString(1));
                        }
                    }
                }
            }

            stmt.execute("COMMIT");
        }
    }
}