Come leggere tutte le righe dal tavolo enorme?

Ho un problema con l’elaborazione di tutte le righe dal database (PostgreSQL). Ottengo un errore: org.postgresql.util.PSQLException: Ran out of memory retrieving query results. Penso di aver bisogno di leggere tutte le righe in piccoli pezzi, ma non funziona – legge solo 100 righe (codice sotto). Come farlo?

  int i = 0; Statement s = connection.createStatement(); s.setMaxRows(100); // bacause of: org.postgresql.util.PSQLException: Ran out of memory retrieving query results. ResultSet rs = s.executeQuery("select * from " + tabName); for (;;) { while (rs.next()) { i++; // do something... } if ((s.getMoreResults() == false) && (s.getUpdateCount() == -1)) { break; } } 

Usa un CURSOR in PostgreSQL o lascia che sia il driver JDBC a gestirlo per te .

LIMIT e OFFSET si rallentano durante la gestione di set di dati di grandi dimensioni.

La versione breve è, call stmt.setFetchSize(50); e conn.setAutoCommitMode(false); per evitare di leggere l’intero ResultSet in memoria.

Ecco cosa dicono i documenti :

Ottenere risultati basati su un cursore

Per impostazione predefinita, il driver raccoglie tutti i risultati per la query contemporaneamente. Ciò può essere sconveniente per insiemi di dati di grandi dimensioni, pertanto il driver JDBC fornisce un mezzo per basare un ResultSet su un cursore di database e recuperare solo un numero limitato di righe.

Un piccolo numero di righe viene memorizzato nella cache sul lato client della connessione e quando viene esaurito il successivo blocco di righe viene recuperato riposizionando il cursore.

Nota:

  • I ResultSet basati su cursore non possono essere utilizzati in tutte le situazioni. Esiste una serie di restrizioni che renderanno il driver silenziosamente in grado di recuperare l’intero ResultSet in una sola volta.

  • La connessione al server deve utilizzare il protocollo V3. Questa è l’impostazione predefinita per (ed è supportata solo da) versioni del server 7.4 e successive.-

  • La connessione non deve essere in modalità di autocommit. Il back-end chiude i cursori alla fine delle transazioni, quindi in modalità di autocommit il backend avrà chiuso il cursore prima che tutto possa essere recuperato da esso.-

  • L’istruzione deve essere creata con un tipo ResultSet di ResultSet.TYPE_FORWARD_ONLY. Questa è l’impostazione predefinita, quindi non è necessario riscrivere alcun codice per sfruttare questo vantaggio, ma significa anche che non è ansible scorrere all’indietro o altrimenti saltare nel ResultSet.-

  • La query fornita deve essere una singola istruzione, non più istruzioni legate insieme al punto e virgola.

Esempio 5.2. Impostazione della dimensione di recupero per triggersre e distriggersre i cursori.

Cambiare il codice in modalità cursore è semplice come impostare la dimensione di recupero dell’istruzione nella dimensione appropriata. L’impostazione della dimensione di recupero su 0 causerà la memorizzazione di tutte le righe nella cache (il comportamento predefinito).

 // make sure autocommit is off conn.setAutoCommit(false); Statement st = conn.createStatement(); // Turn use of the cursor on. st.setFetchSize(50); ResultSet rs = st.executeQuery("SELECT * FROM mytable"); while (rs.next()) { System.out.print("a row was returned."); } rs.close(); // Turn the cursor off. st.setFetchSize(0); rs = st.executeQuery("SELECT * FROM mytable"); while (rs.next()) { System.out.print("many rows were returned."); } rs.close(); // Close the statement. st.close(); 

Quindi, il nodo del problema è che, per impostazione predefinita, Postgres si avvia in modalità “autoCommit”, e inoltre ha bisogno / utilizza i cursori per essere in grado di “pagina” attraverso i dati (es: leggere i primi risultati 10K, quindi il successivo, poi il prossimo), tuttavia i cursori possono esistere solo all’interno di una transazione. Quindi l’impostazione predefinita è di leggere tutte le righe, sempre, nella RAM, e quindi consentire al programma di avviare l’elaborazione “la prima riga del risultato, poi la seconda” dopo che è arrivata, per due motivi, non è in una transazione (quindi cursori non funziona), e anche una dimensione di recupero non è stata impostata.

Quindi, il modo in cui lo strumento della riga di comando psql raggiunge la risposta in batch (la sua impostazione FETCH_COUNT ) per le query, è quello di “avvolgere” le query selezionate all’interno di una transazione a breve termine (se una transazione non è ancora aperta), in modo che i cursori possano funzionare. Puoi fare qualcosa del genere anche con JDBC:

  static void readLargeQueryInChunksJdbcWay(Connection conn, String originalQuery, int fetchCount, ConsumerWithException consumer) throws SQLException { boolean originalAutoCommit = conn.getAutoCommit(); if (originalAutoCommit) { conn.setAutoCommit(false); // start temp transaction } try (Statement statement = conn.createStatement()) { statement.setFetchSize(fetchCount); ResultSet rs = statement.executeQuery(originalQuery); while (rs.next()) { consumer.accept(rs); // or just do you work here } } finally { if (originalAutoCommit) { conn.setAutoCommit(true); // reset it, also ends (commits) temp transaction } } } @FunctionalInterface public interface ConsumerWithException { void accept(T t) throws E; } 

Ciò offre il vantaggio di richiedere meno RAM e, nei miei risultati, sembra essere globalmente più veloce, anche se non è necessario salvare la RAM. Strano. Offre inoltre il vantaggio che l’elaborazione della prima riga “si avvia più rapidamente” (poiché la elabora una pagina alla volta).

Ed ecco come fare il “cursore raw postgres”, insieme al codice demo completo, anche se nei miei esperimenti sembrava che il modo JDBC, sopra, fosse leggermente più veloce per qualsiasi motivo.

Un’altra opzione sarebbe quella di autoCommit modalità autoCommit , ovunque, anche se devi sempre specificare manualmente un fetchSize per ogni nuova istruzione (oppure puoi impostare una dimensione di recupero predefinita nella stringa dell’URL).

Penso che la tua domanda sia simile a questo thread: Paginazione JDBC che contiene soluzioni per le tue necessità.

In particolare, per PostgreSQL, puoi utilizzare le parole chiave LIMIT e OFFSET nella tua richiesta: http://www.petefreitag.com/item/451.cfm

PS: nel codice Java, ti suggerisco di usare PreparedStatement invece di semplici istruzioni: http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html

L’ho fatto come sotto Non è il modo migliore di pensare, ma funziona 🙂

  Connection c = DriverManager.getConnection("jdbc:postgresql://...."); PreparedStatement s = c.prepareStatement("select * from " + tabName + " where id > ? order by id"); s.setMaxRows(100); int lastId = 0; for (;;) { s.setInt(1, lastId); ResultSet rs = s.executeQuery(); int lastIdBefore = lastId; while (rs.next()) { lastId = Integer.parseInt(rs.getObject(1).toString()); // ... } if (lastIdBefore == lastId) { break; } } 

Per caso nel mio caso il problema era sul client che cerca di recuperare i risultati.

Volevo ottenere un .csv con TUTTI i risultati.

Ho trovato la soluzione usando

 psql -U postgres -d dbname -c "COPY (SELECT * FROM T) TO STDOUT WITH DELIMITER ','" 

(dove dbname il nome del db …) e reindirizzamento a un file.