0
# Advanced Features
1

2
This document covers advanced PostgreSQL JDBC driver features including LISTEN/NOTIFY, query cancellation, parameter status, and utility functions.
3

4
## Capabilities
5

6
### LISTEN/NOTIFY (Asynchronous Notifications)
7

8
PostgreSQL's asynchronous notification system for real-time messaging between database clients.
9

10
```java { .api }
11
package org.postgresql;
12

13
import java.sql.SQLException;
14

15
/**
16
 * Notification received via LISTEN/NOTIFY.
17
 */
18
public interface PGNotification {
19
    /**
20
     * Returns the notification channel name.
21
     *
22
     * @return Channel name
23
     */
24
    String getName();
25

26
    /**
27
     * Returns the process ID of the notifying backend.
28
     *
29
     * @return Backend process ID
30
     */
31
    int getPID();
32

33
    /**
34
     * Returns the notification payload (PostgreSQL 9.0+).
35
     * Empty string for notifications without payload.
36
     *
37
     * @return Payload string
38
     */
39
    String getParameter();
40
}
41

42
/**
43
 * Methods for receiving notifications (on PGConnection).
44
 */
45
public interface PGConnection {
46
    /**
47
     * Returns notifications received since last call.
48
     * Non-blocking - returns immediately.
49
     *
50
     * @return Array of notifications, or null if none available
51
     * @throws SQLException if retrieval fails
52
     */
53
    PGNotification[] getNotifications() throws SQLException;
54

55
    /**
56
     * Returns notifications with timeout.
57
     * Blocks until notifications arrive or timeout expires.
58
     *
59
     * @param timeoutMillis Timeout in milliseconds
60
     *                      (0 = block forever, >0 = wait up to specified time)
61
     * @return Array of notifications, or null if timeout expires
62
     * @throws SQLException if retrieval fails
63
     */
64
    PGNotification[] getNotifications(int timeoutMillis) throws SQLException;
65
}
66
```
67

68
**Usage Examples:**
69

70
```java
71
import org.postgresql.PGConnection;
72
import org.postgresql.PGNotification;
73
import java.sql.*;
74

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

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

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

90
            if (notifications != null) {
91
                for (PGNotification notification : notifications) {
92
                    System.out.println("Channel: " + notification.getName());
93
                    System.out.println("PID: " + notification.getPID());
94
                    System.out.println("Payload: " + notification.getParameter());
95
                }
96
            }
97

98
            // Wait a bit before next check
99
            try {
100
                Thread.sleep(500);
101
            } catch (InterruptedException e) {
102
                break;
103
            }
104
        }
105
    }
106
}
107

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

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

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

120
        if (notifications != null) {
121
            for (PGNotification notification : notifications) {
122
                System.out.println("Received: " + notification.getParameter());
123
            }
124
        } else {
125
            System.out.println("Timeout - no notifications");
126
        }
127
    }
128
}
129

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

136
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
137
            pstmt.setString(1, message);
138
            pstmt.executeQuery();
139
        }
140
    }
141

142
    // Alternative: NOTIFY command
143
    public static void sendNotifyCommand(Connection conn, String message)
144
            throws SQLException {
145
        try (Statement stmt = conn.createStatement()) {
146
            stmt.execute("NOTIFY my_channel, '" + message + "'");
147
        }
148
    }
149
}
150

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

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

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

165
                    if (notifications != null) {
166
                        for (PGNotification notification : notifications) {
167
                            handleNotification(notification);
168
                        }
169
                    }
170
                }
171
            } catch (SQLException e) {
172
                e.printStackTrace();
173
            }
174
        });
175

176
        listenerThread.start();
177
    }
178

179
    private static void handleNotification(PGNotification notification) {
180
        // Process notification
181
        System.out.println("Update: " + notification.getParameter());
182
    }
183
}
184
```
185

186
### Query Cancellation
187

188
Cancelling long-running queries.
189

190
```java { .api }
191
package org.postgresql;
192

193
import java.sql.SQLException;
194
import java.sql.Statement;
195

196
/**
197
 * Query cancellation methods.
198
 */
199
public interface PGConnection {
200
    /**
201
     * Cancels the currently executing query on this connection.
202
     * Sends cancel request to the backend.
203
     * Query will abort with an error.
204
     *
205
     * @throws SQLException if cancellation fails
206
     */
207
    void cancelQuery() throws SQLException;
208

209
    /**
210
     * Returns the backend process ID.
211
     * Useful for identifying connections in pg_stat_activity.
212
     *
213
     * @return Backend PID
214
     */
215
    int getBackendPID();
216
}
217

218
/**
219
 * Standard Statement cancellation (also supported).
220
 */
221
public interface Statement {
222
    /**
223
     * Cancels the current statement execution.
224
     * Can be called from another thread.
225
     *
226
     * @throws SQLException if cancellation fails
227
     */
228
    void cancel() throws SQLException;
229

230
    /**
231
     * Sets query timeout.
232
     *
233
     * @param seconds Timeout in seconds (0 = no timeout)
234
     * @throws SQLException if timeout cannot be set
235
     */
236
    void setQueryTimeout(int seconds) throws SQLException;
237
}
238
```
239

240
**Usage Examples:**
241

242
```java
243
import org.postgresql.PGConnection;
244
import java.sql.*;
245

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

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

260
        queryThread.start();
261

262
        // Wait a bit, then cancel
263
        try {
264
            Thread.sleep(2000);
265
            pgConn.cancelQuery(); // Cancel the query
266
        } catch (InterruptedException e) {
267
            Thread.currentThread().interrupt();
268
        }
269
    }
270
}
271

272
// Example 2: Cancel from Statement
273
public class StatementCancelExample {
274
    private volatile Statement currentStatement;
275

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

279
        try {
280
            currentStatement.executeQuery("SELECT * FROM huge_table");
281
        } finally {
282
            currentStatement = null;
283
        }
284
    }
285

286
    public void cancelCurrentQuery() throws SQLException {
287
        if (currentStatement != null) {
288
            currentStatement.cancel();
289
        }
290
    }
291
}
292

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

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

301
            try (ResultSet rs = stmt.executeQuery(sql)) {
302
                while (rs.next()) {
303
                    // Process results
304
                }
305
            }
306
        } catch (SQLException e) {
307
            if (e.getSQLState().equals("57014")) {
308
                System.out.println("Query timeout exceeded");
309
            }
310
            throw e;
311
        }
312
    }
313
}
314
```
315

316
### SQL Escaping
317

318
Safe escaping for identifiers and literals.
319

320
```java { .api }
321
package org.postgresql;
322

323
import java.sql.SQLException;
324

325
/**
326
 * SQL escaping methods on PGConnection.
327
 */
328
public interface PGConnection {
329
    /**
330
     * Escapes an identifier for safe use in SQL.
331
     * Adds quotes if necessary and escapes embedded quotes.
332
     *
333
     * @param identifier Identifier to escape (table name, column name, etc.)
334
     * @return Properly escaped identifier
335
     * @throws SQLException if escaping fails
336
     */
337
    String escapeIdentifier(String identifier) throws SQLException;
338

339
    /**
340
     * Escapes a string literal for safe use in SQL.
341
     * Properly escapes quotes and backslashes.
342
     * Note: Returns null for null input.
343
     *
344
     * @param literal String literal to escape
345
     * @return Properly escaped literal
346
     * @throws SQLException if escaping fails
347
     */
348
    String escapeLiteral(String literal) throws SQLException;
349
}
350
```
351

352
**Usage Examples:**
353

354
```java
355
import org.postgresql.PGConnection;
356
import java.sql.*;
357

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

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

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

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

376
        try (Statement stmt = conn.createStatement();
377
             ResultSet rs = stmt.executeQuery(sql)) {
378
            // Process results
379
        }
380
    }
381

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

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

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

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

400
        // Simple identifier (no quotes needed)
401
        String simpleCol = "id";
402
        System.out.println(pgConn.escapeIdentifier(simpleCol));
403
        // Output: id
404
    }
405
}
406
```
407

408
### Server Parameter Status
409

410
Accessing PostgreSQL server parameters reported to the client.
411

412
```java { .api }
413
package org.postgresql;
414

415
import java.util.Map;
416

417
/**
418
 * Server parameter access on PGConnection.
419
 */
420
public interface PGConnection {
421
    /**
422
     * Returns all server parameters reported by PostgreSQL.
423
     * PostgreSQL reports GUC_REPORT parameters like server_version,
424
     * TimeZone, DateStyle, client_encoding, etc.
425
     *
426
     * @return Unmodifiable map of parameter names to values
427
     */
428
    Map<String, String> getParameterStatuses();
429

430
    /**
431
     * Returns a specific server parameter value.
432
     *
433
     * @param parameterName Parameter name (case-insensitive)
434
     * @return Parameter value, or null if not reported
435
     */
436
    String getParameterStatus(String parameterName);
437
}
438
```
439

440
**Usage Examples:**
441

442
```java
443
import org.postgresql.PGConnection;
444
import java.sql.*;
445
import java.util.Map;
446

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

452
        // Get specific parameters
453
        String version = pgConn.getParameterStatus("server_version");
454
        String timezone = pgConn.getParameterStatus("TimeZone");
455
        String encoding = pgConn.getParameterStatus("client_encoding");
456
        String dateStyle = pgConn.getParameterStatus("DateStyle");
457

458
        System.out.println("Server version: " + version);
459
        System.out.println("Timezone: " + timezone);
460
        System.out.println("Encoding: " + encoding);
461
        System.out.println("DateStyle: " + dateStyle);
462

463
        // Get all parameters
464
        Map<String, String> params = pgConn.getParameterStatuses();
465
        System.out.println("\nAll parameters:");
466
        for (Map.Entry<String, String> entry : params.entrySet()) {
467
            System.out.println("  " + entry.getKey() + " = " + entry.getValue());
468
        }
469
    }
470
}
471
```
472

473
### Query Execution Modes
474

475
Control how the driver executes queries (simple vs extended protocol).
476

477
```java { .api }
478
package org.postgresql.jdbc;
479

480
/**
481
 * Specifies which protocol mode is used to execute queries.
482
 * - SIMPLE: Uses 'Q' execute (no parse, no bind, text mode only)
483
 * - EXTENDED: Always uses bind/execute messages
484
 * - EXTENDED_FOR_PREPARED: Extended for prepared statements only
485
 * - EXTENDED_CACHE_EVERYTHING: Extended with aggressive caching
486
 *
487
 * Note: This is primarily for debugging and performance tuning.
488
 */
489
public enum PreferQueryMode {
490
    /**
491
     * Simple query protocol ('Q' execute).
492
     * No parse/bind steps, text mode only.
493
     * Faster for simple queries but limited features.
494
     */
495
    SIMPLE,
496

497
    /**
498
     * Extended protocol for prepared statements only.
499
     * Regular statements use simple protocol.
500
     * Default behavior for most use cases.
501
     */
502
    EXTENDED_FOR_PREPARED,
503

504
    /**
505
     * Always use extended protocol.
506
     * All queries use parse/bind/execute.
507
     * Better for complex queries and type handling.
508
     */
509
    EXTENDED,
510

511
    /**
512
     * Extended protocol with aggressive caching.
513
     * Caches all queries, even non-prepared ones.
514
     * Maximum performance but higher memory usage.
515
     */
516
    EXTENDED_CACHE_EVERYTHING;
517

518
    /**
519
     * Parses query mode from string value.
520
     * Falls back to EXTENDED if mode string doesn't match.
521
     *
522
     * @param mode Mode string (case-sensitive)
523
     * @return PreferQueryMode enum constant
524
     */
525
    public static PreferQueryMode of(String mode);
526

527
    /**
528
     * Returns the string value of this mode.
529
     *
530
     * @return Lowercase string representation
531
     */
532
    public String value();
533
}
534

535
/**
536
 * Method to get current query mode (on PGConnection).
537
 */
538
public interface PGConnection {
539
    /**
540
     * Returns the query execution mode for this connection.
541
     *
542
     * @return Current query mode
543
     */
544
    PreferQueryMode getPreferQueryMode();
545
}
546
```
547

548
**Usage Example:**
549

550
```java
551
import org.postgresql.PGConnection;
552
import org.postgresql.jdbc.PreferQueryMode;
553
import java.sql.*;
554

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

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

563
        // Mode can be set via connection property:
564
        // jdbc:postgresql://localhost/db?preferQueryMode=simple
565
    }
566
}
567
```
568

569
### Exception Details
570

571
PostgreSQL-specific exception information.
572

573
```java { .api }
574
package org.postgresql.util;
575

576
import java.sql.SQLException;
577
import java.io.Serializable;
578

579
/**
580
 * PostgreSQL-specific SQLException.
581
 */
582
public class PSQLException extends SQLException {
583
    /**
584
     * Returns detailed server error message.
585
     *
586
     * @return ServerErrorMessage with additional details
587
     */
588
    public ServerErrorMessage getServerErrorMessage();
589
}
590

591
/**
592
 * Detailed error/notice message from PostgreSQL server.
593
 */
594
public class ServerErrorMessage implements Serializable {
595
    /**
596
     * Returns the severity level of the error.
597
     * Common values: ERROR, FATAL, PANIC, WARNING, NOTICE, DEBUG, INFO, LOG
598
     */
599
    public String getSeverity();
600

601
    /**
602
     * Returns SQL state code.
603
     */
604
    public String getSQLState();
605

606
    /**
607
     * Returns primary error message.
608
     */
609
    public String getMessage();
610

611
    /**
612
     * Returns detail message providing more context.
613
     */
614
    public String getDetail();
615

616
    /**
617
     * Returns hint message with suggestions.
618
     */
619
    public String getHint();
620

621
    /**
622
     * Returns character position of error in query.
623
     */
624
    public int getPosition();
625

626
    /**
627
     * Returns internal query that caused error.
628
     */
629
    public String getInternalQuery();
630

631
    /**
632
     * Returns position in internal query.
633
     */
634
    public int getInternalPosition();
635

636
    /**
637
     * Returns context information (where error occurred).
638
     */
639
    public String getWhere();
640

641
    /**
642
     * Returns source file name where error was detected.
643
     */
644
    public String getFile();
645

646
    /**
647
     * Returns source line number.
648
     */
649
    public int getLine();
650

651
    /**
652
     * Returns source routine name.
653
     */
654
    public String getRoutine();
655

656
    /**
657
     * Returns schema name (for constraint violations).
658
     */
659
    public String getSchema();
660

661
    /**
662
     * Returns table name (for constraint violations).
663
     */
664
    public String getTable();
665

666
    /**
667
     * Returns column name (for constraint violations).
668
     */
669
    public String getColumn();
670

671
    /**
672
     * Returns data type name (for type-related errors).
673
     */
674
    public String getDatatype();
675

676
    /**
677
     * Returns constraint name (for constraint violations).
678
     */
679
    public String getConstraint();
680
}
681

682
/**
683
 * SQL State codes enumeration.
684
 */
685
public enum PSQLState {
686
    CONNECTION_FAILURE,
687
    COMMUNICATION_ERROR,
688
    UNIQUE_VIOLATION,
689
    FOREIGN_KEY_VIOLATION,
690
    CHECK_VIOLATION,
691
    NOT_NULL_VIOLATION,
692
    SERIALIZATION_FAILURE,
693
    DEADLOCK_DETECTED,
694
    // ... many more
695

696
    /**
697
     * Gets the 5-character SQL state code.
698
     */
699
    public String getState();
700

701
    /**
702
     * Checks if SQL state indicates connection error.
703
     */
704
    public static boolean isConnectionError(String psqlState);
705
}
706
```
707

708
**Usage Examples:**
709

710
```java
711
import org.postgresql.util.PSQLException;
712
import org.postgresql.util.ServerErrorMessage;
713
import org.postgresql.util.PSQLState;
714
import java.sql.*;
715

716
// Example: Detailed error handling
717
public class ErrorHandlingExample {
718
    public static void handleDetailedError(Connection conn) {
719
        try {
720
            try (Statement stmt = conn.createStatement()) {
721
                stmt.execute("INSERT INTO users (email) VALUES ('duplicate@example.com')");
722
            }
723
        } catch (SQLException e) {
724
            if (e instanceof PSQLException) {
725
                PSQLException psqlEx = (PSQLException) e;
726
                ServerErrorMessage serverError = psqlEx.getServerErrorMessage();
727

728
                System.out.println("SQL State: " + serverError.getSQLState());
729
                System.out.println("Message: " + serverError.getMessage());
730
                System.out.println("Detail: " + serverError.getDetail());
731
                System.out.println("Hint: " + serverError.getHint());
732
                System.out.println("Position: " + serverError.getPosition());
733
                System.out.println("Constraint: " + serverError.getConstraint());
734
                System.out.println("Table: " + serverError.getTable());
735
                System.out.println("Column: " + serverError.getColumn());
736

737
                // Check specific error types
738
                if ("23505".equals(e.getSQLState())) {
739
                    System.out.println("Unique constraint violation");
740
                } else if ("23503".equals(e.getSQLState())) {
741
                    System.out.println("Foreign key violation");
742
                }
743
            }
744
        }
745
    }
746

747
    public static void handleConnectionError(SQLException e) {
748
        if (PSQLState.isConnectionError(e.getSQLState())) {
749
            System.out.println("Connection error - attempt reconnect");
750
        }
751
    }
752
}
753
```
754

755
### Utility Functions
756

757
Miscellaneous utility functions.
758

759
```java { .api }
760
package org.postgresql.util;
761

762
/**
763
 * Password utility for encryption.
764
 */
765
public class PasswordUtil {
766
    /**
767
     * Encodes password with SCRAM-SHA-256.
768
     *
769
     * @param password Password to encode
770
     * @return Encoded password hash
771
     * @throws SQLException if encoding fails
772
     */
773
    public static String encodeScramSha256(char[] password) throws SQLException;
774

775
    /**
776
     * Encodes password with MD5 (legacy, not recommended).
777
     *
778
     * @param user Username
779
     * @param password Password to encode
780
     * @return MD5 hash
781
     * @throws SQLException if encoding fails
782
     */
783
    public static String encodeMd5(String user, char[] password) throws SQLException;
784

785
    /**
786
     * Encodes password with specified type.
787
     *
788
     * @param user Username
789
     * @param password Password
790
     * @param encryptionType "md5" or "scram-sha-256"
791
     * @return Encoded password
792
     * @throws SQLException if encoding fails
793
     */
794
    public static String encodePassword(String user, char[] password,
795
                                       String encryptionType) throws SQLException;
796

797
    /**
798
     * Generates ALTER USER SQL for password change.
799
     *
800
     * @param user Username
801
     * @param password New password
802
     * @param encryptionType Encryption type
803
     * @return ALTER USER SQL command
804
     * @throws SQLException if generation fails
805
     */
806
    public static String genAlterUserPasswordSQL(String user, char[] password,
807
                                                 String encryptionType) throws SQLException;
808
}
809

810
/**
811
 * Bytea encoding/decoding.
812
 */
813
public class PGbytea {
814
    /**
815
     * Converts bytea string to bytes.
816
     */
817
    public static byte[] toBytes(byte[] s) throws SQLException;
818

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

824
    /**
825
     * Converts to PostgreSQL literal.
826
     */
827
    public static String toPGLiteral(Object value);
828
}
829

830
/**
831
 * Shared timer for managing connection timeouts across all connections.
832
 * This is used internally by the driver to efficiently manage timeouts
833
 * without creating a separate timer thread for each connection.
834
 */
835
public class SharedTimer {
836
    /**
837
     * Creates a new SharedTimer instance.
838
     */
839
    public SharedTimer();
840

841
    /**
842
     * Gets the reference count of active users of this timer.
843
     *
844
     * @return Number of active references
845
     */
846
    public int getRefCount();
847

848
    /**
849
     * Gets the Timer instance, creating it if necessary.
850
     * Increments the reference count.
851
     *
852
     * @return Timer instance for scheduling tasks
853
     */
854
    public java.util.Timer getTimer();
855

856
    /**
857
     * Releases the timer, decrementing the reference count.
858
     * When reference count reaches zero, the timer is cancelled.
859
     */
860
    public void releaseTimer();
861
}
862
```
863

864
### Ref Cursor Support (Deprecated)
865

866
Support for PostgreSQL ref cursors. This interface is deprecated as of driver version 8.0 in favor of using standard JDBC ResultSet operations with cursor names obtained via getString().
867

868
```java { .api }
869
package org.postgresql;
870

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

873
/**
874
 * Interface for ref cursor based result sets.
875
 *
876
 * @deprecated As of 8.0, this interface is only present for backwards-compatibility.
877
 *             New code should call getString() on the ResultSet that contains the
878
 *             refcursor to obtain the underlying cursor name.
879
 */
880
@Deprecated
881
public interface PGRefCursorResultSet {
882
    /**
883
     * Returns the name of the ref cursor.
884
     *
885
     * @return The cursor name, or null
886
     * @deprecated As of 8.0, replaced with calling getString() on the ResultSet
887
     *             that this ResultSet was obtained from.
888
     */
889
    @Deprecated
890
    @Nullable
891
    String getRefCursor();
892
}
893
```
894

895
**Usage Example (Deprecated):**
896

897
```java
898
import org.postgresql.PGRefCursorResultSet;
899
import java.sql.*;
900

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

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

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

915
                // Fetch from cursor
916
                try (Statement cursorStmt = conn.createStatement();
917
                     ResultSet cursorRs = cursorStmt.executeQuery("FETCH ALL FROM \"" + cursorName + "\"")) {
918
                    while (cursorRs.next()) {
919
                        // Process results
920
                    }
921
                }
922
            }
923

924
            stmt.execute("COMMIT");
925
        }
926
    }
927
}
928

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

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

942
                    // Fetch from cursor
943
                    try (Statement cursorStmt = conn.createStatement();
944
                         ResultSet cursorRs = cursorStmt.executeQuery("FETCH ALL FROM \"" + cursorName + "\"")) {
945
                        while (cursorRs.next()) {
946
                            // Process results
947
                            System.out.println(cursorRs.getString(1));
948
                        }
949
                    }
950
                }
951
            }
952

953
            stmt.execute("COMMIT");
954
        }
955
    }
956
}
957
```
958

959
### Best Practices
960

961
1. **Use LISTEN/NOTIFY for:**
962
   - Real-time notifications
963
   - Cache invalidation
964
   - Inter-process communication
965
   - Event-driven architectures
966

967
2. **Always clean up notifications:**
968
   ```java
969
   try (Statement stmt = conn.createStatement()) {
970
       stmt.execute("UNLISTEN *");
971
   }
972
   ```
973

974
3. **Handle query cancellation gracefully:**
975
   ```java
976
   try {
977
       pgConn.cancelQuery();
978
   } catch (SQLException e) {
979
       // Query may have already completed
980
   }
981
   ```
982

983
4. **Use escaping only when PreparedStatement not possible:**
984
   ```java
985
   // Prefer PreparedStatement
986
   PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM ? WHERE id = ?");
987

988
   // Use escaping for dynamic SQL
989
   String table = pgConn.escapeIdentifier(tableName);
990
   ```
991

992
5. **Monitor server parameters for troubleshooting:**
993
   ```java
994
   String version = pgConn.getParameterStatus("server_version");
995
   String timezone = pgConn.getParameterStatus("TimeZone");
996
   ```
997

998
6. **Extract detailed error information:**
999
   ```java
1000
   catch (PSQLException e) {
1001
       ServerErrorMessage details = e.getServerErrorMessage();
1002
       log.error("Error detail: {}", details.getDetail());
1003
       log.error("Hint: {}", details.getHint());
1004
   }
1005
   ```
1006