4. PostgreSQL depuis C# et Java

Les programmes de cette leçon sont dans code/postgresql-aurora/csharp et code/postgresql-aurora/java. Chaque exemple est un nom sur la ligne de commande, comme l04-connect. check.sh construit les deux, crée une base learn neuve avec le modèle de la leçon 2, exécute chaque exemple et compare sa sortie à un fichier de expected/.

	.NET	Java
Pilote	Npgsql 10.0.3	Pilote JDBC de PostgreSQL 42.7.13, « pgjdbc »
Pool	intégré à Npgsql	aucun dans le pilote ; HikariCP 7.1.0
ORM	EF Core avec le fournisseur Npgsql 10.0.3	Spring Data ou Hibernate, pas dans cette leçon
Équivalent SQL Server	`Microsoft.Data.SqlClient`	Microsoft JDBC Driver for SQL Server

Exécuter les exemples

Les commandes s’exécutent dans code/postgresql-aurora, parce que les exemples COPY lisent ../ladybugdb/data/ga/package_refs.csv. server.sh est un script Bash : sous Windows, lance-le depuis Git Bash.

# Git Bash : le serveur, et la base de la leçon 2
bash server.sh start
bash server.sh psql -d postgres -c "CREATE DATABASE learn"
bash server.sh psql -q -d learn -c '\ir schema.sql'

# PowerShell : les programmes
dotnet run --project csharp -c Release -- l04-connect
mvn -f java/pom.xml package
java "-Duser.timezone=America/Toronto" -jar java/target/pg.jar l04-connect

Dans PowerShell, mets l’option -D entre guillemets : sans guillemets, PowerShell coupe -Duser.timezone=America/Toronto au point, et Java répond « Could not find or load main class .timezone=… ».

bash server.sh start
bash server.sh psql -d postgres -c "CREATE DATABASE learn"
bash server.sh psql -q -d learn -c '\ir schema.sql'

dotnet run --project csharp -c Release -- l04-connect
mvn -f java/pom.xml package
java -Duser.timezone=America/Toronto -jar java/target/pg.jar l04-connect

bash server.sh start
bash server.sh psql -d postgres -c "CREATE DATABASE learn"
bash server.sh psql -q -d learn -c '\ir schema.sql'

dotnet run --project csharp -c Release -- l04-connect
mvn -f java/pom.xml package
java -Duser.timezone=America/Toronto -jar java/target/pg.jar l04-connect

J’ai exécuté ces commandes sous Windows 11 le 2026-09-15 ; les commandes Linux et macOS sont à vérifier sur ces systèmes, et la CI construit les programmes sur les trois. Les programmes se connectent à localhost:5432 en tant que postgres. Les variables d’environnement PG_CONNECTION, une chaîne de connexion Npgsql, et PG_JDBC_URL, une URL JDBC, les dirigent ailleurs. -Duser.timezone est expliqué plus bas.

Se connecter

En .NET, une NpgsqlDataSource contient une chaîne de connexion et son pool. Une application en crée une par base et la garde, souvent comme singleton dans l’injection de dépendances ; les objets NpgsqlConnection en viennent (Db.cs, lignes 8-13, L04.cs, lignes 10-21) :

    public static string ConnectionString =>
        Environment.GetEnvironmentVariable("PG_CONNECTION")
        ?? "Host=localhost;Port=5432;Username=postgres;Password=learn;Database=learn;Application Name=learn-csharp";

    public static NpgsqlDataSource DataSource(string? extra = null) =>
        NpgsqlDataSource.Create(extra is null ? ConnectionString : $"{ConnectionString};{extra}");

    public static async Task Connect()
    {
        // Un NpgsqlDataSource par base pour toute l'application : c'est lui qui possède le pool de connexions
        await using var dataSource = Db.DataSource();
        await using var command = dataSource.CreateCommand(
            "SELECT current_user, current_setting('server_version'), (SELECT count(*) FROM ci.runs)");
        await using var reader = await command.ExecuteReaderAsync();
        await reader.ReadAsync();
        Console.WriteLine($"user: {reader.GetString(0)}");
        Console.WriteLine($"server: {reader.GetString(1)}");
        Console.WriteLine($"runs: {reader.GetInt64(2)}");
    }

user: postgres
server: 18.6 (Debian 18.6-1.pgdg13+2)
runs: 125

En Java, DriverManager ouvre une nouvelle connexion à chaque appel, à partir d’une URL (L04.java, lignes 35-49) :

    static void connect() throws SQLException {
        try (Connection connection = DriverManager.getConnection(url());
             Statement statement = connection.createStatement();
             ResultSet rs = statement.executeQuery(
                     "SELECT current_user, current_setting('server_version'), (SELECT count(*) FROM ci.runs),"
                             + " current_setting('TimeZone')")) {
            rs.next();
            System.out.println("driver: " + connection.getMetaData().getDriverVersion());
            // Le pilote règle le TimeZone de la session sur le fuseau horaire par défaut de la JVM
            System.out.println("JVM time zone: " + java.util.TimeZone.getDefault().getID() + ", session TimeZone: " + rs.getString(4));
            System.out.println("user: " + rs.getString(1));
            System.out.println("server: " + rs.getString(2));
            System.out.println("runs: " + rs.getLong(3));
        }
    }

driver: 42.7.13
JVM time zone: America/Toronto, session TimeZone: America/Toronto
user: postgres
server: 18.6 (Debian 18.6-1.pgdg13+2)
runs: 125

La deuxième ligne est la première différence entre les pilotes. pgjdbc règle le TimeZone de la session sur le fuseau horaire par défaut de la JVM : il l’envoie dans le message de démarrage de la connexion, à côté de DateStyle (ConnectionFactoryImpl.java, lignes 458-464). Chaque timestamptz que le serveur met en texte pour cette connexion est dans ce fuseau. check.sh fixe le fuseau de la JVM avec -Duser.timezone=America/Toronto, pour que la sortie soit la même sur toutes les machines ; l’exercice 2 le change. Npgsql n’envoie pas le fuseau du client : la session garde celui du serveur par défaut, UTC dans l’image, sauf si la chaîne de connexion règle Timezone.

Application Name et ApplicationName nomment la connexion dans pg_stat_activity : les exemples de pools plus bas s’en servent pour compter leurs propres sessions.

Paramètres

Les marqueurs propres à PostgreSQL sont positionnels : $1, $2. Npgsql les accepte tels quels, et aussi @name, qu’il réécrit en $1 avant d’envoyer la requête. JDBC utilise ?, que pgjdbc réécrit en $1 (L04.cs, lignes 27-53, L04.java, lignes 53-67) :

        // Paramètres positionnels : $1, $2 sont ce que PostgreSQL comprend lui-même
        await using (var command = dataSource.CreateCommand("""
            SELECT run_id, started_at FROM ci.runs
            WHERE workflow_name = $1 AND started_at >= $2
            ORDER BY started_at, run_id
            LIMIT 2
            """))
        {
            command.Parameters.Add(new() { Value = "Rust course examples" });
            command.Parameters.Add(new() { Value = new DateTime(2026, 9, 13, 18, 0, 0, DateTimeKind.Utc) });
            await using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                var startedAt = reader.GetDateTime(1);
                Console.WriteLine($"{reader.GetInt64(0)} {startedAt:O} Kind={startedAt.Kind}");
            }
        }

        // Paramètres nommés : Npgsql réécrit @os en $1 avant d'envoyer la requête
        await using (var command = dataSource.CreateCommand("""
            SELECT count(*) FROM ci.jobs WHERE labels[1] = @os AND duration > @longer_than
            """))
        {
            command.Parameters.AddWithValue("os", "windows-latest");
            command.Parameters.AddWithValue("longer_than", TimeSpan.FromMinutes(2));
            Console.WriteLine($"windows jobs over 2 minutes: {await command.ExecuteScalarAsync()}");
        }

34776807003 2026-09-13T19:09:08.0000000Z Kind=Utc
34777004019 2026-09-13T19:12:56.0000000Z Kind=Utc
windows jobs over 2 minutes: 8

            // Les marqueurs ? de JDBC ; le pilote les envoie comme $1, $2
            try (PreparedStatement ps = connection.prepareStatement("""
                    SELECT run_id, started_at FROM ci.runs
                    WHERE workflow_name = ? AND started_at >= ?
                    ORDER BY started_at, run_id
                    LIMIT 2
                    """)) {
                ps.setString(1, "Rust course examples");
                ps.setObject(2, OffsetDateTime.of(2026, 9, 13, 18, 0, 0, 0, ZoneOffset.UTC));
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        System.out.println(rs.getLong(1) + " " + rs.getObject(2, OffsetDateTime.class));
                    }
                }
            }

34776807003 2026-09-13T19:09:08Z
34777004019 2026-09-13T19:12:56Z

Les valeurs voyagent à part du texte SQL, dans le message Bind du protocole : pas d’injection SQL, et pas de guillemets à mal échapper. TimeSpan correspond à interval, et le paramètre nommé @longer_than est comparé à la colonne virtuelle duration de la leçon 2.

Listes : un tableau, pas IN

WHERE x IN (@a, @b, @c) demande un paramètre par valeur, et une instruction différente pour chaque longueur de liste. PostgreSQL prend toute la liste comme un seul paramètre tableau (L04.cs, lignes 55-66, L04.java, lignes 69-78) :

        // Un paramètre tableau remplace une liste de valeurs : = ANY ($1), pas IN (...)
        await using (var command = dataSource.CreateCommand("""
            SELECT labels[1], count(*) FROM ci.jobs WHERE labels[1] = ANY ($1) GROUP BY 1 ORDER BY 1
            """))
        {
            command.Parameters.Add(new() { Value = new[] { "macos-latest", "windows-latest" } });
            await using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                Console.WriteLine($"{reader.GetString(0)}: {reader.GetInt64(1)}");
            }
        }

            // Un paramètre tableau : = ANY (?)
            try (PreparedStatement ps = connection.prepareStatement(
                    "SELECT labels[1], count(*) FROM ci.jobs WHERE labels[1] = ANY (?) GROUP BY 1 ORDER BY 1")) {
                ps.setArray(1, connection.createArrayOf("text", new String[] {"macos-latest", "windows-latest"}));
                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        System.out.println(rs.getString(1) + ": " + rs.getLong(2));
                    }
                }
            }

macos-latest: 34
windows-latest: 35

Npgsql fait correspondre un string[] à text[] ; en JDBC, Connection.createArrayOf construit le tableau. Les équivalents SQL Server sont un paramètre table ou STRING_SPLIT.

DateTime, DateTimeOffset et fuseaux horaires

Npgsql décide du type PostgreSQL d’un DateTime d’après son Kind, comme le décrit sa documentation des dates et heures : Utc est envoyé comme timestamptz, Unspecified et Local comme timestamp sans fuseau horaire. Comparé à une colonne timestamptz, un timestamp est converti par le serveur, dans le fuseau horaire de la session (L04.cs, lignes 68-94) :

        // DateTime.Kind décide du type du paramètre : Utc part en timestamptz, Unspecified et Local en timestamp,
        // que le serveur convertit en timestamptz dans le fuseau horaire de la session
        foreach (var options in new[] { "Timezone=UTC", "Timezone=America/Toronto" })
        {
            await using var zoned = Db.DataSource(options);
            foreach (var kind in new[] { DateTimeKind.Utc, DateTimeKind.Unspecified })
            {
                await using var command = zoned.CreateCommand("SELECT count(*) FROM ci.runs WHERE started_at >= $1");
                command.Parameters.Add(new() { Value = new DateTime(2026, 9, 14, 0, 0, 0, kind) });
                Console.WriteLine($"{options}, Kind={kind}: {await command.ExecuteScalarAsync()} runs since 2026-09-14 00:00");
            }
        }
        await using (var command = dataSource.CreateCommand("SELECT count(*) FROM ci.runs WHERE started_at >= $1"))
        {
            var toronto = new DateTimeOffset(2026, 9, 14, 0, 0, 0, TimeSpan.FromHours(-4));
            command.Parameters.Add(new() { Value = toronto });
            try
            {
                await command.ExecuteScalarAsync();
            }
            catch (ArgumentException e)
            {
                Console.WriteLine($"{e.GetType().Name}: {e.Message}");
            }
            command.Parameters[0].Value = toronto.ToUniversalTime();
            Console.WriteLine($"DateTimeOffset in UTC: {await command.ExecuteScalarAsync()} runs since 2026-09-14 00:00 in Toronto");
        }

Timezone=UTC, Kind=Utc: 82 runs since 2026-09-14 00:00
Timezone=UTC, Kind=Unspecified: 82 runs since 2026-09-14 00:00
Timezone=America/Toronto, Kind=Utc: 82 runs since 2026-09-14 00:00
Timezone=America/Toronto, Kind=Unspecified: 59 runs since 2026-09-14 00:00
ArgumentException: Cannot write DateTimeOffset with Offset=-04:00:00 to PostgreSQL type 'timestamp with time zone', only offset 0 (UTC) is supported.  (Parameter 'value')
DateTimeOffset in UTC: 59 runs since 2026-09-14 00:00 in Toronto

Le même DateTime, minuit le 14 septembre, compte 82 exécutions dans trois cas, et 59 quand il est Unspecified et que la session est à Toronto : minuit à Toronto est 04:00 UTC, et 23 exécutions ont commencé entre les deux. L’exercice 2 de la leçon 2 trouvait les mêmes 23 exécutions.
La requête est correcte dans les quatre cas. Le compte dépend d’un Kind qui vient de l’endroit où le DateTime a été créé, et d’un réglage de session. Envoie des valeurs Kind=Utc aux colonnes timestamptz.
Un DateTimeOffset avec un décalage non nul est refusé avant tout envoi : timestamptz ne stocke pas de décalage (leçon 2), et Npgsql ne veut pas en supprimer un en silence. Convertis avec ToUniversalTime().

En Java, OffsetDateTime garde son décalage jusqu’au serveur, et le serveur compare des instants (L04.java, lignes 80-87) :

            // Un OffsetDateTime garde son décalage ; le serveur compare des instants
            try (PreparedStatement ps = connection.prepareStatement("SELECT count(*) FROM ci.runs WHERE started_at >= ?")) {
                ps.setObject(1, OffsetDateTime.of(2026, 9, 14, 0, 0, 0, 0, ZoneOffset.ofHours(-4)));
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();
                    System.out.println("runs since 2026-09-14 00:00 in Toronto: " + rs.getLong(1));
                }
            }

runs since 2026-09-14 00:00 in Toronto: 59

En lecture, reader.GetDateTime renvoie un DateTime avec Kind=Utc, et rs.getObject(2, OffsetDateTime.class) un OffsetDateTime en UTC, quel que soit le fuseau de la session : les premières lignes des deux sorties ci-dessus. Seule une valeur mise en texte par le serveur, comme rs.getString, suit le TimeZone de la session.

Types des paramètres

Un paramètre a un type, et PostgreSQL ne convertit pas une chaîne en nombre en silence (L04.cs, lignes 96-108, L04.java, lignes 89-95) :

        // Une valeur qui ne correspond pas au type de la colonne est une erreur de PostgreSQL, pas de Npgsql
        await using (var command = dataSource.CreateCommand("SELECT count(*) FROM ci.runs WHERE run_id = $1"))
        {
            command.Parameters.Add(new() { Value = "34852867099" });
            try
            {
                await command.ExecuteScalarAsync();
            }
            catch (PostgresException e)
            {
                Console.WriteLine($"{e.SqlState}: {e.MessageText}");
            }
        }

42883: operator does not exist: bigint = text

            // setString lie un varchar : PostgreSQL n'a pas d'opérateur bigint = varchar
            try (PreparedStatement ps = connection.prepareStatement("SELECT count(*) FROM ci.runs WHERE run_id = ?")) {
                ps.setString(1, "34852867099");
                ps.executeQuery();
            } catch (PSQLException e) {
                System.out.println(e.getSQLState() + ": " + e.getServerErrorMessage().getMessage());
            }

42883: operator does not exist: bigint = character varying

Npgsql envoie une string comme text, pgjdbc envoie setString comme varchar, et aucun des deux types n’a d’opérateur = avec bigint. SQL Server convertirait la chaîne implicitement. La correction est le bon type .NET ou Java ; la propriété stringtype=unspecified de pgjdbc envoie les chaînes sans type et laisse le serveur déduire le type, ce qui cache aussi les erreurs. L’erreur 42883 est undefined_function dans la liste des codes d’erreur : un opérateur est une fonction.

Pools

Ouvrir une connexion PostgreSQL démarre un processus serveur, le « un processus par connexion » de la leçon 1 : une connexion TCP, une authentification, un fork. Un pool garde les connexions ouvertes et les redistribue (L04.cs, lignes 111-153) :

    public static async Task Pool()
    {
        foreach (var pooling in new[] { true, false })
        {
            await using var dataSource = Db.DataSource($"Pooling={pooling}");
            var backends = new HashSet<int>();
            for (var i = 0; i < 20; i++)
            {
                await using var connection = await dataSource.OpenConnectionAsync();
                backends.Add(connection.ProcessID);
            }
            Console.WriteLine($"Pooling={pooling}: 20 opens, {backends.Count} server process(es)");
        }

        // Cinq connexions tenues en même temps sont cinq processus serveur
        await using (var dataSource = Db.DataSource("Application Name=learn-pool"))
        {
            var connections = new List<NpgsqlConnection>();
            for (var i = 0; i < 5; i++)
            {
                connections.Add(await dataSource.OpenConnectionAsync());
            }
            foreach (var connection in connections)
            {
                await connection.CloseAsync();
            }
            await using var command = dataSource.CreateCommand("""
                SELECT state, count(*) FROM pg_stat_activity
                WHERE application_name = 'learn-pool' GROUP BY state ORDER BY state
                """);
            await using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                Console.WriteLine($"after closing: {reader.GetInt64(1)} {reader.GetString(0)}");
            }
        }
        await using (var dataSource = Db.DataSource())
        {
            await using var command = dataSource.CreateCommand(
                "SELECT count(*) FROM pg_stat_activity WHERE application_name = 'learn-pool'");
            Console.WriteLine($"after disposing the data source: {await command.ExecuteScalarAsync()}");
        }
    }

Pooling=True: 20 opens, 1 server process(es)
Pooling=False: 20 opens, 20 server process(es)
after closing: 1 active
after closing: 4 idle
after disposing the data source: 0

Avec le pool, 20 ouvertures et fermetures utilisent un seul processus serveur ; sans, 20.
Cinq connexions tenues en même temps sont cinq processus. Fermées, elles restent ouvertes sur le serveur, idle, dans le pool. La seule session active est la connexion qui exécute la requête sur pg_stat_activity elle-même.
Libérer la source de données ferme ses connexions.

Le pool de Npgsql est actif par défaut, par source de données, avec au plus 100 connexions (Maximum Pool Size) et un minimum de 0 ; les connexions inactives au-delà du minimum sont fermées après 300 secondes (paramètres de la chaîne de connexion). SqlClient a les mêmes valeurs par défaut, 100 et 0.

pgjdbc n’a pas de pool à lui : chaque DriverManager.getConnection est un nouveau processus. HikariCP est le pool habituel (L04.java, lignes 107-142) :

    static void pool() throws SQLException {
        Set<Integer> backends = new HashSet<>();
        for (int i = 0; i < 20; i++) {
            try (Connection connection = DriverManager.getConnection(url())) {
                backends.add(backendPid(connection));
            }
        }
        System.out.println("DriverManager: 20 connections, " + backends.size() + " server process(es)");

        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(url().replace("learn-java", "learn-hikari"));
        config.setMaximumPoolSize(5);
        try (HikariDataSource pool = new HikariDataSource(config)) {
            backends.clear();
            for (int i = 0; i < 20; i++) {
                try (Connection connection = pool.getConnection()) {
                    backends.add(backendPid(connection));
                }
            }
            System.out.println("HikariCP: 20 connections, " + backends.size() + " server process(es)");
            // HikariCP ouvre des connexions en arrière-plan jusqu'à minimumIdle, qui vaut maximumPoolSize par défaut
            try (Connection connection = pool.getConnection()) {
                long deadline = System.currentTimeMillis() + 10_000;
                long sessions;
                do {
                    try (Statement statement = connection.createStatement();
                         ResultSet rs = statement.executeQuery(
                                 "SELECT count(*) FROM pg_stat_activity WHERE application_name = 'learn-hikari'")) {
                        rs.next();
                        sessions = rs.getLong(1);
                    }
                } while (sessions < 5 && System.currentTimeMillis() < deadline && sleep(100));
                System.out.println("sessions opened by the pool: " + sessions);
            }
        }
    }

DriverManager: 20 connections, 20 server process(es)
HikariCP: 20 connections, 1 server process(es)
sessions opened by the pool: 5

Le minimumIdle de HikariCP vaut par défaut maximumPoolSize, d’après son README : un pool de 5 ouvre 5 sessions en arrière-plan dès son démarrage, utilisées ou non. Npgsql ouvre les connexions à la demande.

Un pool rencontre max_connections

Le serveur a aussi une limite, max_connections, 100 par défaut (L04.cs, lignes 308-331) :

    public static async Task TooMany()
    {
        // Sans pool : chaque Open est un nouveau processus serveur, jusqu'à atteindre max_connections (100 par défaut)
        await using var dataSource = Db.DataSource("Pooling=false");
        var connections = new List<NpgsqlConnection>();
        try
        {
            for (var i = 0; i < 200; i++)
            {
                connections.Add(await dataSource.OpenConnectionAsync());
            }
        }
        catch (PostgresException e)
        {
            Console.WriteLine($"{e.SqlState}: {e.MessageText}");
        }
        finally
        {
            foreach (var connection in connections)
            {
                await connection.DisposeAsync();
            }
        }
    }

53300: sorry, too many clients already

La taille maximale par défaut du pool de Npgsql est le nombre maximal de connexions par défaut du serveur. Une instance de l’application à pleine charge peut toutes les prendre ; deux instances avec les réglages par défaut peuvent en demander 200. Dimensionne les pools d’après la limite du serveur : la somme sur toutes les instances de l’application, plus les administrateurs, doit rester en dessous. Les connexions sont des processus coûteux, et quelques dizaines de connexions bien utilisées font généralement mieux que des centaines de connexions inactives.

Instructions préparées

Une instruction préparée est analysée et planifiée une fois, puis exécutée avec de nouveaux paramètres. Les deux pilotes ne préparent sur le serveur qu’après quelques exécutions d’une instruction (L04.cs, lignes 155-170, L04.java, lignes 154-171) :

    public static async Task Prepare()
    {
        // Npgsql ne prépare une instruction que sur demande, ou après Auto Prepare Min Usages exécutions (5 par défaut)
        // quand Max Auto Prepare est supérieur à 0
        await using var dataSource = Db.DataSource("Max Auto Prepare=10");
        await using var connection = await dataSource.OpenConnectionAsync();
        await using var count = new NpgsqlCommand(
            "SELECT count(*) FROM pg_prepared_statements WHERE statement ~ 'ci\\.jobs WHERE'", connection);
        for (var i = 1; i <= 6; i++)
        {
            await using var command = new NpgsqlCommand("SELECT count(*) FROM ci.jobs WHERE run_id = $1", connection);
            command.Parameters.Add(new() { Value = 34852867099L });
            await command.ExecuteScalarAsync();
            Console.WriteLine($"after {i} execution(s): {await count.ExecuteScalarAsync()} prepared statement(s)");
        }
    }

    static void prepare() throws SQLException {
        // Le pilote passe à une instruction préparée nommée côté serveur à la prepareThreshold-ième exécution (5 par défaut)
        try (Connection connection = DriverManager.getConnection(url());
             PreparedStatement count = connection.prepareStatement(
                     "SELECT count(*) FROM pg_prepared_statements WHERE statement ~ 'ci\\.jobs WHERE'")) {
            count.unwrap(org.postgresql.PGStatement.class).setPrepareThreshold(0);
            for (int i = 1; i <= 6; i++) {
                try (PreparedStatement ps = connection.prepareStatement("SELECT count(*) FROM ci.jobs WHERE run_id = ?")) {
                    ps.setLong(1, 34852867099L);
                    ps.executeQuery().close();
                }
                try (ResultSet rs = count.executeQuery()) {
                    rs.next();
                    System.out.println("after " + i + " execution(s): " + rs.getLong(1) + " prepared statement(s)");
                }
            }
        }
    }

after 1 execution(s): 0 prepared statement(s)
after 2 execution(s): 0 prepared statement(s)
after 3 execution(s): 0 prepared statement(s)
after 4 execution(s): 0 prepared statement(s)
after 5 execution(s): 1 prepared statement(s)
after 6 execution(s): 1 prepared statement(s)

Les deux sorties sont identiques : l’instruction apparaît dans pg_prepared_statements à la cinquième exécution.

Npgsql ne prépare pas automatiquement sauf si Max Auto Prepare est au-dessus de 0 ; sa valeur par défaut est 0. Alors une instruction exécutée Auto Prepare Min Usages fois, 5 par défaut, est préparée. NpgsqlCommand.Prepare() prépare explicitement.
pgjdbc fait passer un PreparedStatement à une instruction nommée côté serveur à la prepareThreshold-ième exécution, 5 par défaut (propriétés de connexion). La requête de comptage règle son propre seuil à 0, pour n’être jamais préparée et ne pas se compter elle-même.

Une instruction préparée appartient à une session, et une connexion de pool la garde d’une ouverture à l’autre : c’est pourquoi les pools comptent ici. La leçon 5 revient sur les plans génériques et personnalisés.

Chargements en masse : COPY et lots

La leçon 1 chargeait des fichiers avec COPY … FROM un chemin sur le serveur. Depuis un programme, COPY … FROM STDIN envoie les lignes par la connexion. Npgsql les écrit dans le format binaire de PostgreSQL, typées (L04.cs, lignes 172-208) :

    static IEnumerable<string[]> PackageRefs() =>
        File.ReadLines("../ladybugdb/data/ga/package_refs.csv").Skip(1).Select(line => line.Split(','));

    public static async Task Copy()
    {
        await using var dataSource = Db.DataSource();
        await using var connection = await dataSource.OpenConnectionAsync();
        await using (var create = new NpgsqlCommand("""
            DROP TABLE IF EXISTS ga.package_refs_import;
            CREATE TABLE ga.package_refs_import (LIKE ga.package_refs INCLUDING ALL);
            """, connection))
        {
            await create.ExecuteNonQueryAsync();
        }

        // COPY ... FROM STDIN au format binaire : le client envoie les lignes, typées, en un seul flux
        await using (var writer = await connection.BeginBinaryImportAsync(
            "COPY ga.package_refs_import (project, package, version) FROM STDIN (FORMAT binary)"))
        {
            foreach (var fields in PackageRefs())
            {
                await writer.StartRowAsync();
                await writer.WriteAsync(fields[0], NpgsqlDbType.Text);
                await writer.WriteAsync(fields[1], NpgsqlDbType.Text);
                await writer.WriteAsync(fields[2], NpgsqlDbType.Text);
            }
            Console.WriteLine($"copied: {await writer.CompleteAsync()} rows");
        }

        await using var compare = new NpgsqlCommand("""
            SELECT (SELECT count(*) FROM (TABLE ga.package_refs EXCEPT TABLE ga.package_refs_import) AS d),
                   (SELECT count(*) FROM (TABLE ga.package_refs_import EXCEPT TABLE ga.package_refs) AS d)
            """, connection);
        await using var reader = await compare.ExecuteReaderAsync();
        await reader.ReadAsync();
        Console.WriteLine($"rows only in the server-side load: {reader.GetInt64(0)}, only in the client copy: {reader.GetInt64(1)}");
    }

copied: 478 rows
rows only in the server-side load: 0, only in the client copy: 0

Le CopyManager de pgjdbc transmet le fichier CSV en texte, et le serveur l’analyse (L04.java, lignes 181-186) :

            // COPY ... FROM STDIN : le fichier est sur le client, le serveur analyse le CSV
            try (BufferedReader csv = Files.newBufferedReader(Path.of("../ladybugdb/data/ga/package_refs.csv"))) {
                long rows = connection.unwrap(PGConnection.class).getCopyAPI()
                        .copyIn("COPY ga.package_refs_import FROM STDIN (FORMAT csv, HEADER match)", csv);
                System.out.println("copied: " + rows + " rows");
            }

copied: 478 rows
rows only in the server-side load: 0, only in the client copy: 0

Les deux copies correspondent au chargement côté serveur de la leçon 2, ligne pour ligne : TABLE t est un raccourci pour SELECT * FROM t, et EXCEPT dans les deux sens ne trouve aucune différence. COPY FROM STDIN est le SqlBulkCopy de SQL Server, et c’est aussi la façon dont les données atteignent un serveur géré qui n’a pas de système de fichiers à lire, comme Aurora.

l04-timings compare trois façons d’insérer les 478 lignes sur une connexion. check.sh ne le compare pas, puisque les temps dépendent de la machine ; une exécution sur la machine de l’auteur, contre le conteneur local (L04.cs, lignes 249-306) :

round 1: 478 INSERT commands: 1040 ms
round 1: one NpgsqlBatch of 478 INSERTs: 7 ms
round 1: binary COPY of 478 rows: 56 ms
round 2: 478 INSERT commands: 1005 ms
round 2: one NpgsqlBatch of 478 INSERTs: 5 ms
round 2: binary COPY of 478 rows: 49 ms

478 commandes INSERT font 478 allers-retours, et chacune valide seule. Un NpgsqlBatch envoie toutes les instructions en un aller-retour, dans une transaction implicite. À 478 lignes courtes, le lot est le plus rapide ici ; cette exécution ne mesure pas où COPY le dépasse, et un réseau entre client et serveur multiplie le coût de chaque aller-retour. L’exercice 1 charge les mêmes lignes avec un lot dans les deux langages.

Clés générées

RETURNING (leçon 3) renvoie ce que le serveur a généré. L’API portable de JDBC pour cela est Statement.RETURN_GENERATED_KEYS (L04.java, lignes 199-241) :

    static void generatedKeys() throws SQLException {
        try (Connection connection = DriverManager.getConnection(url())) {
            try (Statement statement = connection.createStatement()) {
                statement.execute("""
                        DROP TABLE IF EXISTS ci.notes;
                        CREATE TABLE ci.notes (
                            note_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                            run_id  bigint NOT NULL REFERENCES ci.runs,
                            body    text NOT NULL,
                            created timestamptz NOT NULL DEFAULT '2026-09-15 00:00:00+00'
                        );
                        """);
            }
            // RETURN_GENERATED_KEYS : le pilote ajoute RETURNING * à l'INSERT
            try (PreparedStatement ps = connection.prepareStatement(
                    "INSERT INTO ci.notes (run_id, body) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS)) {
                ps.setLong(1, 34852867099L);
                ps.setString(2, "first deployment of the day");
                ps.executeUpdate();
                try (ResultSet keys = ps.getGeneratedKeys()) {
                    keys.next();
                    int columns = keys.getMetaData().getColumnCount();
                    StringBuilder line = new StringBuilder("generated keys:");
                    for (int c = 1; c <= columns; c++) {
                        line.append(' ').append(keys.getMetaData().getColumnName(c)).append('=').append(keys.getString(c));
                    }
                    System.out.println(line);
                }
            }
            // Nommer les colonnes ne demande que RETURNING note_id
            try (PreparedStatement ps = connection.prepareStatement(
                    "INSERT INTO ci.notes (run_id, body) VALUES (?, ?)", new String[] {"note_id"})) {
                ps.setLong(1, 34852867099L);
                ps.setString(2, "second note");
                ps.executeUpdate();
                try (ResultSet keys = ps.getGeneratedKeys()) {
                    keys.next();
                    System.out.println("generated keys: " + keys.getMetaData().getColumnCount() + " column, note_id="
                            + keys.getLong("note_id"));
                }
            }
        }
    }

generated keys: note_id=1 run_id=34852867099 body=first deployment of the day created=2026-09-14 20:00:00-04
generated keys: 1 column, note_id=2

pgjdbc ajoute RETURNING * à l’instruction (Parser.java, lignes 371-386), donc les « clés » sont toutes les colonnes de la ligne, created compris ; nommer les colonnes ne demande que celles-là. created vaut 2026-09-15 00:00:00+00, affiché 2026-09-14 20:00:00-04 par getString : la session est à Toronto, le fuseau de la JVM. En C#, écris RETURNING note_id dans le SQL et lis-le avec ExecuteScalarAsync.

EF Core

Le fournisseur EF Core de Npgsql fait correspondre les tableaux et listes .NET de types primitifs aux tableaux PostgreSQL, et traduit le LINQ qui les utilise. Un contexte sur la table ga.iconic_chords du cours (L04Ef.cs, lignes 6-34 et lignes 90-108) :

// Mappée sur la table de sql/schema.sql : tableaux natifs, sans convertisseur de valeurs
public class IconicChord
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Artist { get; set; } = "";
    public short[] PitchClasses { get; set; } = [];
    public short[]? GuitarVoicing { get; set; }
    public List<string> AlternateNames { get; set; } = [];
}

public class ChordsContext : DbContext
{
    public DbSet<IconicChord> Chords => Set<IconicChord>();

    protected override void OnConfiguring(DbContextOptionsBuilder options) => options.UseNpgsql(Db.ConnectionString);

    protected override void OnModelCreating(ModelBuilder model) =>
        model.Entity<IconicChord>(entity =>
        {
            entity.ToTable("iconic_chords", "ga");
            entity.Property(c => c.Id).HasColumnName("chord_id");
            entity.Property(c => c.Name).HasColumnName("name");
            entity.Property(c => c.Artist).HasColumnName("artist");
            entity.Property(c => c.PitchClasses).HasColumnName("pitch_classes");
            entity.Property(c => c.GuitarVoicing).HasColumnName("guitar_voicing");
            entity.Property(c => c.AlternateNames).HasColumnName("alternate_names");
        });
}

    public static async Task Query()
    {
        await using var db = new ChordsContext();
        var withE = db.Chords
            .Where(c => c.PitchClasses.Contains((short)4) && c.GuitarVoicing != null)
            .OrderBy(c => c.Name)
            .Select(c => new { c.Name, c.GuitarVoicing, FirstAlias = c.AlternateNames[0] });
        Console.WriteLine(withE.ToQueryString());
        Console.WriteLine();
        foreach (var chord in await withE.ToListAsync())
        {
            Console.WriteLine($"{chord.Name}: [{string.Join(", ", chord.GuitarVoicing!)}] alias {chord.FirstAlias}");
        }
        Console.WriteLine();

        var aliases = db.Chords.Where(c => c.AlternateNames.Any(a => EF.Functions.ILike(a, "%beatles%"))).Select(c => c.Name);
        Console.WriteLine(aliases.ToQueryString());
        Console.WriteLine(string.Join(", ", await aliases.OrderBy(n => n).ToListAsync()));
    }

SELECT i.name AS "Name", i.guitar_voicing AS "GuitarVoicing", i.alternate_names[1] AS "FirstAlias"
FROM ga.iconic_chords AS i
WHERE 4 = ANY (i.pitch_classes) AND i.guitar_voicing IS NOT NULL
ORDER BY i.name

Hendrix Chord: [0, 7, 6, 7, 8, 0] alias Purple Haze Chord
James Bond Chord: [0, 2, 1, 0, 0, 0] alias Spy Chord

SELECT i.name
FROM ga.iconic_chords AS i
WHERE EXISTS (
    SELECT 1
    FROM unnest(i.alternate_names) AS a(value)
    WHERE a.value ILIKE '%beatles%')
A Hard Day's Night Chord, Blackbird Chord

Contains sur un tableau devient = ANY, et AlternateNames[0] devient alternate_names[1] : le fournisseur décale l’indice, puisque les tableaux PostgreSQL commencent à 1 (correspondance des tableaux).
Any avec EF.Functions.ILike devient un EXISTS sur unnest. ILIKE est le LIKE insensible à la casse de PostgreSQL : la collation par défaut est sensible à la casse, contrairement aux collations _CI_ habituelles de SQL Server.

Le modèle de Guitar Alchemist sur PostgreSQL

Le MusicalKnowledgeDbContext de Guitar Alchemist, rencontré à la leçon 2, stocke les mêmes listes comme des chaînes jointes, par des convertisseurs de valeurs écrits pour SQLite. L04Ef.cs copie son entité et sa configuration, réduites aux propriétés qui comptent ici, et construit deux contextes : la configuration de GA, et la même entité sans convertisseurs (lignes 36-86, lignes 110-170) :

// EF Core construit un modèle une fois par type de contexte : une classe par configuration
public abstract class GaStyleContext(bool withConverters) : DbContext
{
    public DbSet<CachedIconicChord> IconicChords => Set<CachedIconicChord>();

    protected override void OnConfiguring(DbContextOptionsBuilder options) => options.UseNpgsql(Db.ConnectionString);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema(withConverters ? "ef_ga" : "ef_native");
        modelBuilder.Entity<CachedIconicChord>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.HasIndex(e => e.Name);
            if (!withConverters)
            {
                return;
            }
            entity.Property(e => e.PitchClasses).HasConversion(
                v => string.Join(',', v),
                v => v.Split(',', StringSplitOptions.RemoveEmptyEntries).Select(int.Parse).ToList()
            );
            entity.Property(e => e.GuitarVoicing).HasConversion(
                v => v != null ? string.Join(',', v) : null,
                v => !string.IsNullOrEmpty(v)
                    ? v.Split(',', StringSplitOptions.RemoveEmptyEntries).Select(int.Parse).ToList()
                    : null
            );
            entity.Property(e => e.AlternateNames).HasConversion(
                v => string.Join('|', v),
                v => v.Split('|', StringSplitOptions.RemoveEmptyEntries).ToList()
            );
        });
    }
}

public class GaConvertersContext() : GaStyleContext(withConverters: true);

public class GaNativeContext() : GaStyleContext(withConverters: false);

    public static async Task GaModel()
    {
        await using (var db = new GaConvertersContext())
        {
            Console.WriteLine("-- GA's configuration, with value converters");
            Console.WriteLine(db.Database.GenerateCreateScript().Trim());
            Console.WriteLine();
        }
        await using (var db = new GaNativeContext())
        {
            Console.WriteLine("-- the same entity, without converters");
            Console.WriteLine(db.Database.GenerateCreateScript().Trim());
            Console.WriteLine();
        }

        await using (var db = new GaNativeContext())
        {
            await db.Database.ExecuteSqlRawAsync("DROP SCHEMA IF EXISTS ef_native CASCADE");
            await db.Database.ExecuteSqlRawAsync(db.Database.GenerateCreateScript());
            db.IconicChords.Add(new CachedIconicChord
            {
                Name = "Hendrix Chord",
                PitchClasses = [4, 8, 11, 2, 7],
                GuitarVoicing = [0, 7, 6, 7, 8, 0],
                AlternateNames = ["Purple Haze Chord", "E7#9"],
                LastUpdated = DateTime.Now,
            });
            try
            {
                await db.SaveChangesAsync();
            }
            catch (DbUpdateException e)
            {
                Console.WriteLine($"{e.GetType().Name}: {e.Message}");
                Console.WriteLine($"  {e.InnerException!.GetType().Name}: {e.InnerException.Message}");
            }
        }

        // Une chaîne vide dans une liste : le tableau la garde, la chaîne jointe de GA la perd au retour
        foreach (var db in new GaStyleContext[] { new GaNativeContext(), new GaConvertersContext() })
        {
            await using (db)
            {
                if (db is GaConvertersContext)
                {
                    await db.Database.ExecuteSqlRawAsync("DROP SCHEMA IF EXISTS ef_ga CASCADE");
                    await db.Database.ExecuteSqlRawAsync(db.Database.GenerateCreateScript());
                }
                db.IconicChords.Add(new CachedIconicChord
                {
                    Name = "Hendrix Chord",
                    PitchClasses = [4, 8, 11, 2, 7],
                    AlternateNames = ["Purple Haze Chord", "", "E7#9"],
                    LastUpdated = DateTime.UtcNow,
                });
                await db.SaveChangesAsync();
                var saved = await db.IconicChords.AsNoTracking().SingleAsync();
                Console.WriteLine($"{db.GetType().Name}: 3 alternate names saved, {saved.AlternateNames.Count} read back, LastUpdated.Kind={saved.LastUpdated.Kind}");
            }
        }
    }

-- GA's configuration, with value converters
DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'ef_ga') THEN
        CREATE SCHEMA ef_ga;
    END IF;
END $EF$;


CREATE TABLE ef_ga."IconicChords" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NOT NULL,
    "PitchClasses" text NOT NULL,
    "GuitarVoicing" text,
    "AlternateNames" text NOT NULL,
    "LastUpdated" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_IconicChords" PRIMARY KEY ("Id")
);


CREATE INDEX "IX_IconicChords_Name" ON ef_ga."IconicChords" ("Name");

-- the same entity, without converters
DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = 'ef_native') THEN
        CREATE SCHEMA ef_native;
    END IF;
END $EF$;


CREATE TABLE ef_native."IconicChords" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NOT NULL,
    "PitchClasses" integer[] NOT NULL,
    "GuitarVoicing" integer[],
    "AlternateNames" text[] NOT NULL,
    "LastUpdated" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_IconicChords" PRIMARY KEY ("Id")
);


CREATE INDEX "IX_IconicChords_Name" ON ef_native."IconicChords" ("Name");

DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.
  ArgumentException: Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array, range, or multirange. (Parameter 'value')
GaNativeContext: 3 alternate names saved, 3 read back, LastUpdated.Kind=Utc
GaConvertersContext: 3 alternate names saved, 2 read back, LastUpdated.Kind=Utc

Le schéma. Avec les convertisseurs, les listes sont des colonnes text ; sans, integer[] et text[], que la base peut vérifier, indexer et interroger. EF Core construit un modèle une fois par type de contexte, c’est pourquoi chaque configuration est sa propre classe.
DateTime.Now. LastUpdated correspond à timestamp with time zone, et Npgsql refuse un DateTime avec Kind=Local pour ce type depuis Npgsql 6. SQLite l’acceptait. Un modèle déplacé de SQLite ou SQL Server vers PostgreSQL trouve ses appels à DateTime.Now au premier SaveChanges.
Le nom vide. Trois noms alternatifs enregistrés, le deuxième vide : le tableau en rend trois, le convertisseur deux. Split('|', StringSplitOptions.RemoveEmptyEntries) supprime l’entrée vide, sur n’importe quelle base. C’est un constat sur le code de GA, pas sur PostgreSQL, et le tableau natif n’a pas le problème.

Erreurs

Une erreur du serveur arrive comme PostgresException dans Npgsql, et PSQLException dans pgjdbc, avec le SqlState à cinq caractères de l’annexe des codes d’erreur. Teste le code, pas le message : 23505 est unique_violation dans tous les langages, alors que le message suit lc_messages. Npgsql définit aussi des constantes comme PostgresErrorCodes.UniqueViolation.

Le DETAIL d’une erreur peut contenir des données, comme le « Failing row contains … » de la leçon 2. Npgsql le masque sauf si la chaîne de connexion règle Include Error Detail=true ; l’exercice 1 montre les deux.

Sur Aurora

À vérifier : rien dans cette section n’a été exécuté sur AWS. Npgsql et pgjdbc se connectent à Aurora PostgreSQL comme à n’importe quel serveur PostgreSQL. Ce qui change est autour de la connexion.

TLS est exigé par défaut. La page de sécurité d’Aurora PostgreSQL donne la valeur par défaut de rds.force_ssl comme 1 (activé) pour Aurora PostgreSQL 17 et plus, et 0 pour 16 et avant ; une connexion sans TLS est refusée avec no pg_hba.conf entry for host …, SSL off. Le conteneur local a ssl désactivé. Les deux pilotes utilisent prefer par défaut, qui utilise TLS quand le serveur le propose mais ne vérifie pas le certificat. Contre Aurora, utilise SSL Mode=VerifyFull dans Npgsql et sslmode=verify-full dans pgjdbc, avec le paquet d’autorités de certification RDS comme racine de confiance.

Mots de passe ou jetons IAM. Avec l’authentification IAM à la base, le mot de passe est un jeton généré par le SDK AWS à partir d’identifiants IAM. AWS lui donne « a lifetime of 15 minutes », précise qu’il « doesn’t affect the session after it is established », et que sa taille « is generally about 1 KB but can be larger ». Un pool ouvre de nouvelles connexions toute la journée, donc le jeton doit être renouvelé :

En .NET, NpgsqlDataSourceBuilder.UsePeriodicPasswordProvider appelle une fonction sur minuterie, « the recommended way to fetch a rotating access token » selon son commentaire de documentation ; la fonction appellerait le RDSAuthTokenGenerator du SDK AWS pour .NET.
En Java, AWS documente le plugin d’authentification IAM de l’AWS Advanced JDBC Wrapper, qui enveloppe pgjdbc.

max_connections dépend de l’instance. Sa valeur par défaut sur Aurora PostgreSQL est LEAST({DBInstanceClassMemory/9531392},5000), d’après performances et mise à l’échelle d’Aurora PostgreSQL, qui recommande aussi de garder « at least 3 more connections to support AWS automation ». Le 53300 de la section sur les pools arrive à ce nombre, quel qu’il soit ; l’arithmétique des tailles de pools est la même.

RDS Proxy et l’épinglage. RDS Proxy partage les connexions à la base entre les connexions clientes aux limites des transactions. Une connexion cliente dont l’état de session a changé est épinglée à sa connexion à la base jusqu’à ce qu’elle se déconnecte. La liste des conditions d’épinglage pour PostgreSQL d’AWS croise les pilotes de cette leçon à trois endroits :

« Using SET commands » épingle. Un réglage de session comme le fuseau horaire a sa place dans la chaîne de connexion (Timezone, ou le fuseau de la JVM pour pgjdbc, envoyé au démarrage), ou dans la requête d’initialisation du proxy. Que RDS Proxy épingle sur des paramètres envoyés dans le message de démarrage est à vérifier.
« Using PREPARE, DISCARD, DEALLOCATE, or EXECUTE commands to manage prepared statements » épingle. La préparation automatique des pilotes utilise les messages du protocole, pas ces commandes SQL ; la page ne dit pas si cela épingle, donc c’est à vérifier.
« If you use connection pooling libraries with DISCARD ALL query configured as a reset query, RDS Proxy pins your client connection on release. » Npgsql réinitialise une connexion de pool avec DISCARD ALL quand elle retourne au pool, sauf si elle a des instructions préparées (NpgsqlConnector.cs, lignes 2752-2766). No Reset On Close=true désactive la réinitialisation, « at the cost of leaking state ». Derrière RDS Proxy, un pool dans l’application et un pool dans le proxy sont deux couches qui font le même travail.

Writer et readers. La leçon 3 a décrit le cluster endpoint et le reader endpoint. Un programme peut tenir une source de données par endpoint, ou donner les deux hôtes à une seule source de données et laisser le pilote choisir selon le rôle : l’exercice 3 l’essaie sur le serveur local. Pour un basculement rapide en Java, AWS maintient l’AWS Advanced JDBC Wrapper, qui suit la topologie du cluster au lieu de s’appuyer sur le DNS.

À retenir

Une NpgsqlDataSource par base, gardée pendant toute la vie de l’application ; en Java, un pool HikariCP autour de pgjdbc.
Utilise les vrais types des paramètres. Une chaîne comparée à un bigint est l’erreur 42883, pas une conversion.
Envoie les valeurs DateTime avec Kind=Utc aux timestamptz ; convertis les DateTimeOffset en UTC. pgjdbc règle le fuseau horaire de la session d’après la JVM.
Maximum Pool Size vaut 100 par défaut et max_connections 100 : dimensionne les pools d’après la limite du serveur.
Les deux pilotes préparent les instructions sur le serveur après 5 exécutions, Npgsql seulement quand Max Auto Prepare est réglé.
COPY FROM STDIN et les lots chargent les lignes en un aller-retour ; un INSERT par ligne paie un aller-retour par ligne.
Le fournisseur EF Core fait correspondre les tableaux nativement ; des convertisseurs de valeurs écrits pour SQLite perdent de l’information et cachent les problèmes de DateTime.Kind.
Sur Aurora : TLS exigé à partir de la version 17, jetons IAM valides 15 minutes, max_connections d’après la mémoire de l’instance, et une réinitialisation DISCARD ALL qui épingle les connexions de RDS Proxy.

Exercices

Charge package_refs.csv dans une copie vide de ga.package_refs en un seul lot, avec la première ligne du fichier ajoutée de nouveau à la fin : un NpgsqlBatch en C#, addBatch et executeBatch en Java. Combien de lignes contient la table ensuite ? Que te dit chaque pilote sur le doublon ?

Solution

L04.cs, lignes 210-246 :

    public static async Task ExerciseBatch()
    {
        await using var dataSource = Db.DataSource();
        await using var connection = await dataSource.OpenConnectionAsync();
        await using (var create = new NpgsqlCommand("""
            DROP TABLE IF EXISTS ga.package_refs_batch;
            CREATE TABLE ga.package_refs_batch (LIKE ga.package_refs INCLUDING ALL);
            """, connection))
        {
            await create.ExecuteNonQueryAsync();
        }

        // Un seul NpgsqlBatch : tous les INSERT partent au serveur en un aller-retour, dans une transaction implicite
        var rows = PackageRefs().ToList();
        rows.Add(rows[0]);
        await using var batch = new NpgsqlBatch(connection);
        foreach (var fields in rows)
        {
            var command = new NpgsqlBatchCommand("INSERT INTO ga.package_refs_batch VALUES ($1, $2, $3)");
            foreach (var field in fields)
            {
                command.Parameters.Add(new() { Value = field });
            }
            batch.BatchCommands.Add(command);
        }
        try
        {
            await batch.ExecuteNonQueryAsync();
        }
        catch (PostgresException e)
        {
            Console.WriteLine($"{e.SqlState}: {e.MessageText}");
            Console.WriteLine(e.Detail);
        }
        await using var count = new NpgsqlCommand("SELECT count(*) FROM ga.package_refs_batch", connection);
        Console.WriteLine($"rows in the table: {await count.ExecuteScalarAsync()}");
    }

23505: duplicate key value violates unique constraint "package_refs_batch_pkey"
Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information.
rows in the table: 0

L04.java, lignes 243-277 :

    static void exerciseBatch() throws Exception {
        try (Connection connection = DriverManager.getConnection(url() + "&reWriteBatchedInserts=true")) {
            try (Statement statement = connection.createStatement()) {
                statement.execute("""
                        DROP TABLE IF EXISTS ga.package_refs_batch;
                        CREATE TABLE ga.package_refs_batch (LIKE ga.package_refs INCLUDING ALL);
                        """);
            }
            List<String> lines = Files.readAllLines(Path.of("../ladybugdb/data/ga/package_refs.csv"));
            List<String> rows = new java.util.ArrayList<>(lines.subList(1, lines.size()));
            rows.add(rows.get(0));
            connection.setAutoCommit(false);
            try (PreparedStatement ps = connection.prepareStatement("INSERT INTO ga.package_refs_batch VALUES (?, ?, ?)")) {
                for (String row : rows) {
                    String[] fields = row.split(",");
                    ps.setString(1, fields[0]);
                    ps.setString(2, fields[1]);
                    ps.setString(3, fields[2]);
                    ps.addBatch();
                }
                ps.executeBatch();
                connection.commit();
            } catch (BatchUpdateException e) {
                connection.rollback();
                SQLException cause = e.getNextException();
                System.out.println(cause.getSQLState() + ": " + ((PSQLException) cause).getServerErrorMessage().getMessage());
            }
            connection.setAutoCommit(true);
            try (Statement statement = connection.createStatement();
                 ResultSet rs = statement.executeQuery("SELECT count(*) FROM ga.package_refs_batch")) {
                rs.next();
                System.out.println("rows in the table: " + rs.getLong(1));
            }
        }
    }

23505: duplicate key value violates unique constraint "package_refs_batch_pkey"
rows in the table: 0

Zéro ligne dans les deux. Un NpgsqlBatch s’exécute dans une transaction implicite : l’échec de la 479e instruction annule les 478 qui la précèdent. En Java, setAutoCommit(false) et rollback() font la même chose explicitement ; en autocommit, chaque instruction du lot validerait seule.
reWriteBatchedInserts=true laisse pgjdbc réécrire le lot en instructions INSERT … VALUES (…), (…) à plusieurs lignes. L’erreur arrive comme une BatchUpdateException, dont getNextException() porte l’erreur du serveur.
Npgsql masque le détail. Avec Include Error Detail=true dans PG_CONNECTION, la deuxième ligne devient Key (project, package)=(AllProjects.AppHost/AllProjects.AppHost.csproj, Aspire.Dashboard.Sdk.win-x64) already exists., ce que j’ai exécuté le 2026-09-15 mais que check.sh ne compare pas.

Exécute l04-generated-keys avec la JVM dans le fuseau horaire de Tokyo. Avant de l’exécuter, prédis la valeur de created qu’il affiche. Quel appel du programme afficherait la même chose dans tous les fuseaux horaires ?

Solution

java "-Duser.timezone=Asia/Tokyo" -jar java/target/pg.jar l04-generated-keys

java -Duser.timezone=Asia/Tokyo -jar java/target/pg.jar l04-generated-keys

java -Duser.timezone=Asia/Tokyo -jar java/target/pg.jar l04-generated-keys

generated keys: note_id=1 run_id=34852867099 body=first deployment of the day created=2026-09-15 09:00:00+09
generated keys: 1 column, note_id=2

La ligne contient le même instant, 2026-09-15 00:00:00+00. pgjdbc a réglé le TimeZone de la session sur Asia/Tokyo, et getString renvoie le texte que le serveur a formaté dans ce fuseau : 09:00 le 15 septembre, là où Toronto donnait 20:00 le 14 septembre. keys.getObject("created", OffsetDateTime.class) renvoie l’instant comme un OffsetDateTime en UTC, quel que soit le fuseau de la JVM. Un rapport côté serveur qui met les dates en texte, comme to_char(created, 'YYYY-MM-DD'), change de jour avec la JVM qui l’exécute. Je ne l’ai exécuté que sous Windows.

Ton application tournera sur un cluster Aurora avec un writer et un reader endpoint. Contre le serveur local, demande à chaque pilote une connexion à un standby, puis à un standby s’il y en a un et au primaire sinon. Que font les pilotes, et de quoi une chaîne de connexion a-t-elle besoin pour cela ?

Solution

L04.cs, lignes 333-361 :

    public static async Task Target()
    {
        // Dans une chaîne de connexion, Target Session Attributes exige plusieurs hôtes, comme les endpoints writer et reader d'Aurora
        try
        {
            Db.DataSource("Target Session Attributes=standby");
        }
        catch (NotSupportedException e)
        {
            Console.WriteLine($"one host: {e.GetType().Name}: {e.Message}");
        }

        // Une source de données multi-hôtes vérifie le rôle de chaque serveur avant de donner une connexion ; le serveur local est un primaire
        await using var multiHost = new NpgsqlDataSourceBuilder(Db.ConnectionString).BuildMultiHost();
        foreach (var target in new[] { TargetSessionAttributes.ReadWrite, TargetSessionAttributes.PreferStandby, TargetSessionAttributes.Standby })
        {
            var dataSource = multiHost.WithTargetSession(target);
            try
            {
                await using var connection = await dataSource.OpenConnectionAsync();
                await using var command = new NpgsqlCommand("SELECT pg_is_in_recovery()", connection);
                Console.WriteLine($"{target}: connected, pg_is_in_recovery() = {await command.ExecuteScalarAsync()}");
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine($"{target}: {e.GetType().Name}: {e.Message}");
            }
        }
    }

one host: NotSupportedException: Target Session Attributes other then Any is only supported with multiple hosts
ReadWrite: connected, pg_is_in_recovery() = False
PreferStandby: connected, pg_is_in_recovery() = False
Standby: NpgsqlException: No suitable host was found.

L04.java, lignes 279-292 :

    static void target() {
        // targetServerType : avant de donner une connexion, le pilote vérifie le rôle du serveur, comme il le ferait pour chaque
        // hôte d'un cluster Aurora ; le serveur local est un primaire
        for (String target : List.of("primary", "preferSecondary", "secondary")) {
            try (Connection connection = DriverManager.getConnection(url() + "&targetServerType=" + target);
                 Statement statement = connection.createStatement();
                 ResultSet rs = statement.executeQuery("SELECT pg_is_in_recovery()")) {
                rs.next();
                System.out.println(target + ": connected, pg_is_in_recovery() = " + rs.getBoolean(1));
            } catch (SQLException e) {
                System.out.println(target + ": " + e.getSQLState() + ": " + e.getMessage());
            }
        }
    }

primary: connected, pg_is_in_recovery() = false
preferSecondary: connected, pg_is_in_recovery() = false
secondary: 08001: Could not find a server with specified targetServerType: secondary

Npgsql refuse Target Session Attributes avec un seul hôte dans une chaîne de connexion, avant de se connecter (NpgsqlConnectionStringBuilder.cs, lignes 1508-1514). Plusieurs hôtes vont dans Host, comme Host=writer-endpoint,reader-endpoint. BuildMultiHost() construit une source de données sur eux, et WithTargetSession en dérive une source de données pour un rôle. D’après plusieurs hôtes et basculement, Npgsql « detects whether a server is a primary or a standby by occasionally querying pg_is_in_recovery() », et vérifie de nouveau toutes les Host Recheck Seconds, 10 par défaut.
pgjdbc accepte un seul hôte et vérifie le rôle du serveur à la connexion : targetServerType=secondary ne trouve pas de standby, SQLSTATE 08001. Plusieurs hôtes vont dans l’URL, jdbc:postgresql://writer-endpoint,reader-endpoint/learn.
Les deux ne trouvent pas de standby sur le primaire local, et les deux se replient sur lui avec prefer-standby et preferSecondary.

Sur Aurora, le reader endpoint pointe déjà vers les réplicas, et une source de données distincte par endpoint est la conception la plus simple. La sélection par rôle sert quand les deux endpoints sont dans une seule chaîne de connexion, et pendant un basculement, quand le reader endpoint « might direct connections to the new primary DB instance », selon la page des endpoints. Qu’une Aurora Replica réponde vrai à pg_is_in_recovery(), comme un standby PostgreSQL, est à vérifier.

Sources

Npgsql : documentation, utilisation de base, paramètres de la chaîne de connexion, gestion des dates et heures, plusieurs hôtes et basculement, fournisseur EF Core et correspondance des tableaux ; sources à v10.0.3 : NpgsqlConnector.cs, NpgsqlConnectionStringBuilder.cs, NpgsqlDataSourceBuilder.cs
Pilote JDBC de PostgreSQL : propriétés de connexion, CopyManager ; sources à REL42.7.13 : ConnectionFactoryImpl.java, Parser.java
HikariCP
Documentation de PostgreSQL 18 : pg_stat_activity, max_connections, pg_prepared_statements, codes d’erreur
.NET et Java : DateTime, DateTimeOffset, DriverManager, OffsetDateTime
Amazon Aurora : sécurité et TLS, authentification IAM à la base, performances et mise à l’échelle, RDS Proxy et épinglage, endpoints, AWS Advanced JDBC Wrapper
Guitar Alchemist au commit 32f143c : MusicalKnowledgeDbContext.cs