0
# PostgreSQL Connection Management
1

2
PostgreSQL-specific connection implementation providing role management, schema handling, and advisory locking capabilities for database migrations.
3

4
## Capabilities
5

6
### PostgreSQL Connection
7

8
Connection wrapper that provides PostgreSQL-specific functionality including role restoration, schema management, and advisory locking.
9

10
```java { .api }
11
/**
12
 * PostgreSQL connection implementation
13
 */
14
public class PostgreSQLConnection extends Connection<PostgreSQLDatabase> {
15
    /**
16
     * Creates a new PostgreSQL connection wrapper
17
     * @param database The PostgreSQL database instance
18
     * @param connection The raw JDBC connection
19
     */
20
    protected PostgreSQLConnection(PostgreSQLDatabase database, java.sql.Connection connection);
21
    
22
    /**
23
     * Checks if this connection is running on AWS RDS
24
     * @return true if running on AWS RDS
25
     */
26
    public boolean isAwsRds();
27
    
28
    /**
29
     * Restores the connection to its original state
30
     * Resets the role to the original user role
31
     * @throws SQLException if role cannot be restored
32
     */
33
    protected void doRestoreOriginalState() throws SQLException;
34
    
35
    /**
36
     * Gets the current schema for this connection
37
     * @return PostgreSQLSchema instance for current schema
38
     * @throws SQLException if current schema cannot be determined
39
     */
40
    public Schema doGetCurrentSchema() throws SQLException;
41
    
42
    /**
43
     * Gets the current schema name or search path
44
     * @return Current search path setting
45
     * @throws SQLException if search path cannot be retrieved
46
     */
47
    protected String getCurrentSchemaNameOrSearchPath() throws SQLException;
48
    
49
    /**
50
     * Changes the current schema by modifying the search path
51
     * @param schema The schema to change to
52
     */
53
    public void changeCurrentSchemaTo(Schema schema);
54
    
55
    /**
56
     * Sets the search path for the connection
57
     * @param schema Comma-separated schema names for search path
58
     * @throws SQLException if search path cannot be set
59
     */
60
    public void doChangeCurrentSchemaOrSearchPathTo(String schema) throws SQLException;
61
    
62
    /**
63
     * Creates a PostgreSQL schema instance
64
     * @param name Schema name
65
     * @return PostgreSQLSchema instance
66
     */
67
    public Schema getSchema(String name);
68
    
69
    /**
70
     * Executes a callable with advisory table locking
71
     * Uses PostgreSQL advisory locks to prevent concurrent access
72
     * @param table The table to lock
73
     * @param callable The operation to execute while locked
74
     * @param <T> Return type of the callable
75
     * @return Result from the callable execution
76
     */
77
    public <T> T lock(Table table, Callable<T> callable);
78
}
79
```
80

81
**Usage Examples:**
82

83
```java
84
import org.flywaydb.database.postgresql.PostgreSQLConnection;
85
import java.util.concurrent.Callable;
86

87
// Connection is typically obtained from PostgreSQLDatabase
88
PostgreSQLConnection connection = (PostgreSQLConnection) database.getConnection();
89

90
// Check AWS RDS status
91
boolean isRds = connection.isAwsRds();
92

93
// Get current schema
94
Schema currentSchema = connection.doGetCurrentSchema();
95
System.out.println("Current schema: " + currentSchema.getName());
96

97
// Change to a different schema
98
Schema targetSchema = connection.getSchema("public");
99
connection.changeCurrentSchemaTo(targetSchema);
100

101
// Execute with advisory locking
102
Table migrationTable = database.getTable("flyway_schema_history");
103
String result = connection.lock(migrationTable, new Callable<String>() {
104
    @Override
105
    public String call() throws Exception {
106
        // Perform migration operations with table locked
107
        return "Migration completed";
108
    }
109
});
110
```
111

112
### Advisory Locking
113

114
PostgreSQL advisory locking implementation that prevents concurrent migrations on the same table.
115

116
```java { .api }
117
/**
118
 * PostgreSQL advisory lock template for safe concurrent operations
119
 */
120
public class PostgreSQLAdvisoryLockTemplate {
121
    /**
122
     * Creates an advisory lock template
123
     * @param configuration Flyway configuration
124
     * @param jdbcTemplate JDBC template for database operations
125
     * @param lockId Unique lock identifier (typically table name hash)
126
     */
127
    public PostgreSQLAdvisoryLockTemplate(Configuration configuration, 
128
                                         JdbcTemplate jdbcTemplate, 
129
                                         int lockId);
130
    
131
    /**
132
     * Executes a callable while holding the advisory lock
133
     * @param callable Operation to execute
134
     * @param <T> Return type
135
     * @return Result from callable execution
136
     */
137
    public <T> T execute(Callable<T> callable);
138
}
139
```
140

141
**Usage Examples:**
142

143
```java
144
import org.flywaydb.database.postgresql.PostgreSQLAdvisoryLockTemplate;
145

146
// Create lock template for a specific table
147
int lockId = "my_table".hashCode();
148
PostgreSQLAdvisoryLockTemplate lockTemplate = 
149
    new PostgreSQLAdvisoryLockTemplate(configuration, jdbcTemplate, lockId);
150

151
// Execute operation with locking
152
String result = lockTemplate.execute(() -> {
153
    // Database operations that need exclusive access
154
    jdbcTemplate.execute("INSERT INTO my_table VALUES (1, 'data')");
155
    return "Operation completed";
156
});
157
```
158

159
## Schema Management
160

161
### Schema Operations
162

163
The connection provides comprehensive schema management capabilities:
164

165
```java
166
// Get current schema with error handling
167
try {
168
    Schema current = connection.doGetCurrentSchema();
169
    if (current.exists()) {
170
        System.out.println("Working with schema: " + current.getName());
171
    }
172
} catch (SQLException e) {
173
    // Handle schema detection errors
174
}
175

176
// Switch schemas safely
177
Schema newSchema = connection.getSchema("new_schema");
178
if (newSchema.exists()) {
179
    connection.changeCurrentSchemaTo(newSchema);
180
} else {
181
    newSchema.create();
182
    connection.changeCurrentSchemaTo(newSchema);
183
}
184
```
185

186
### Search Path Management
187

188
PostgreSQL uses search paths to resolve unqualified object names:
189

190
```java
191
// Get current search path
192
String searchPath = connection.getCurrentSchemaNameOrSearchPath();
193
System.out.println("Current search path: " + searchPath);
194

195
// Set custom search path
196
connection.doChangeCurrentSchemaOrSearchPathTo("app_schema,public");
197

198
// Restore original search path (handled automatically on connection close)
199
```
200

201
## AWS RDS Integration
202

203
The connection automatically detects AWS RDS environments:
204

205
```java
206
PostgreSQLConnection connection = (PostgreSQLConnection) database.getConnection();
207

208
if (connection.isAwsRds()) {
209
    // RDS-specific handling
210
    System.out.println("Running on AWS RDS - using optimized settings");
211
    // Certain operations may behave differently on RDS
212
}
213
```
214

215
## Role Management
216

217
The connection preserves and restores user roles:
218

219
```java
220
// Original role is automatically stored during connection creation
221
// Role changes during migration are automatically restored when connection closes
222

223
// Manual role restoration (typically not needed)
224
connection.doRestoreOriginalState();  // Resets to original role
225
```
226

227
## Error Handling
228

229
Common exceptions and their handling:
230

231
```java
232
try {
233
    Schema schema = connection.doGetCurrentSchema();
234
} catch (FlywayException e) {
235
    // Thrown when search path is empty and current schema cannot be determined
236
    System.err.println("Schema detection failed: " + e.getMessage());
237
    // Consider setting explicit schema in configuration
238
}
239

240
try {
241
    connection.doChangeCurrentSchemaOrSearchPathTo("nonexistent_schema");
242
} catch (FlywaySqlException e) {
243
    // Thrown when schema change fails
244
    System.err.println("Schema change failed: " + e.getMessage());
245
}
246
```