¿Cómo leer todas las filas de la mesa enorme?

Tengo un problema con el procesamiento de todas las filas de la base de datos (PostgreSQL). org.postgresql.util.PSQLException: Ran out of memory retrieving query results. un error: org.postgresql.util.PSQLException: Ran out of memory retrieving query results. Creo que necesito leer todas las filas en pequeños fragmentos, pero no funciona, solo lee 100 filas (código a continuación). ¿Como hacer eso?

  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; } } 

Use un CURSOR en PostgreSQL o permita que el controlador JDBC maneje esto por usted .

LIMIT y OFFSET se volverán lentos al manejar grandes conjuntos de datos.

La versión corta es, llame a stmt.setFetchSize(50); y conn.setAutoCommitMode(false); para evitar leer todo el ResultSet en la memoria.

Esto es lo que dicen los documentos :

Obtener resultados basados ​​en un cursor

De forma predeterminada, el controlador recostack todos los resultados de la consulta a la vez. Esto puede ser inconveniente para grandes conjuntos de datos, por lo que el controlador JDBC proporciona un medio para basar un ResultSet en un cursor de base de datos y solo recuperar un pequeño número de filas.

Se almacena en caché un pequeño número de filas en el lado del cliente de la conexión y cuando se agota se recupera el siguiente bloque de filas reposicionando el cursor.

Nota:

  • Los ResultSets basados ​​en el cursor no se pueden usar en todas las situaciones. Hay una serie de restricciones que harán que el controlador vuelva silenciosamente a buscar todo el ResultSet a la vez.

  • La conexión al servidor debe estar utilizando el protocolo V3. Este es el valor predeterminado para (y solo es compatible con) las versiones del servidor 7.4 y posteriores.-

  • La conexión no debe estar en el modo de confirmación automática. El servidor cierra los cursores al final de las transacciones, por lo que en el modo de confirmación automática el servidor habrá cerrado el cursor antes de que se pueda obtener algo de él.

  • El extracto se debe crear con un tipo ResultSet de ResultSet.TYPE_FORWARD_ONLY. Este es el valor predeterminado, por lo que no será necesario volver a escribir ningún código para aprovechar esto, pero también significa que no puede desplazarse hacia atrás o saltar en el ResultSet.

  • La consulta dada debe ser una sola statement, no múltiples instrucciones unidas con punto y coma.

Ejemplo 5.2. Establecer el tamaño de búsqueda para activar y desactivar los cursores.

Cambiar el código al modo cursor es tan simple como establecer el tamaño de búsqueda del Statement al tamaño apropiado. Volver a establecer el tamaño de recuperación en 0 hará que todas las filas se guarden en caché (el comportamiento predeterminado).

 // 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(); 

Entonces, la clave del problema es que, de manera predeterminada, Postgres se inicia en el modo “autocommit”, y también necesita / utiliza cursores para poder “buscar” datos (por ejemplo, lee los primeros resultados de 10K, luego el siguiente, luego el siguiente), sin embargo, los cursores solo pueden existir dentro de una transacción. Por lo tanto, el valor predeterminado es leer todas las filas, siempre, en la RAM, y luego permitir que su progtwig comience a procesar “la primera fila de resultados, luego la segunda” después de que haya llegado, por dos razones, no está en una transacción (por lo que los cursores no funciona), y tampoco se ha establecido un tamaño de búsqueda.

Entonces, cómo la herramienta de línea de comandos psql logra una respuesta por FETCH_COUNT (su FETCH_COUNT configuración) para consultas, es “ajustar” sus consultas de selección dentro de una transacción a corto plazo (si una transacción aún no está abierta), para que los cursores puedan funcionar. Puedes hacer algo como eso también 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; } 

Esto proporciona la ventaja de requerir menos memoria RAM y, en mis resultados, parece funcionar en general más rápido, incluso si no necesita guardar la memoria RAM. Extraño. También da la ventaja de que el procesamiento de la primera fila “comienza más rápido” (ya que lo procesa una página a la vez).

Y aquí está cómo hacerlo a la manera de “cursor de postgres en bruto”, junto con el código de demostración completo, aunque en mis experimentos parecía que el modo JDBC, arriba, era ligeramente más rápido por cualquier razón.

Otra opción sería autoCommit modo autoCommit , en cualquier lugar, aunque siempre debe especificar manualmente fetchSize para cada nuevo Statement (o puede establecer un tamaño de búsqueda predeterminado en el string de URL).

Creo que su pregunta es similar a este hilo: Paginación JDBC que contiene soluciones para su necesidad.

En particular, para PostgreSQL, puede usar las palabras clave LIMIT y OFFSET en su solicitud: http://www.petefreitag.com/item/451.cfm

PD: En el código de Java, le sugiero que use PreparedStatement en lugar de simples declaraciones: http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Lo hice como a continuación. No es la mejor manera en que pienso, pero funciona 🙂

  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; } } 

Al menos en mi caso, el problema estaba en el cliente que intenta buscar los resultados.

Quería obtener un .csv con TODOS los resultados.

Encontré la solución al usar

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

(donde dbname el nombre de la base de datos …) y redirigir a un archivo.