Como ler todas as linhas da enorme mesa?

Eu tenho um problema com o processamento de todas as linhas do database (PostgreSQL). Eu recebo um erro: org.postgresql.util.PSQLException: Ran out of memory retrieving query results. Eu acho que preciso ler todas as linhas em pequenos pedaços, mas não funciona – lê apenas 100 linhas (código abaixo). Como fazer isso?

  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 um CURSOR no PostgreSQL ou deixe o driver JDBC tratar disso para você .

LIMIT e OFFSET ficarão lentos ao lidar com grandes conjuntos de dados.

A versão curta é, chame stmt.setFetchSize(50); e conn.setAutoCommitMode(false); para evitar ler todo o ResultSet na memory.

Veja o que os documentos dizem:

Obtendo resultados com base em um cursor

Por padrão, o driver coleta todos os resultados da consulta de uma só vez. Isso pode ser inconveniente para grandes conjuntos de dados, portanto, o driver JDBC fornece um meio de basear um ResultSet em um cursor de database e buscar apenas um pequeno número de linhas.

Um pequeno número de linhas é armazenado em cache no lado do cliente da conexão e, quando esgotado, o próximo bloco de linhas é recuperado reposicionando o cursor.

Nota:

  • Os ResultSets baseados no cursor não podem ser usados ​​em todas as situações. Há uma série de restrições que farão com que o driver volte silenciosamente para buscar o ResultSet inteiro de uma só vez.

  • A conexão com o servidor deve estar usando o protocolo V3. Este é o padrão para (e é suportado apenas por) versões do servidor 7.4 e posterior.-

  • A conexão não deve estar no modo de confirmação automática. O back-end fecha os cursores no final das transactions, portanto, no modo de confirmação automática, o back-end terá fechado o cursor antes que qualquer coisa possa ser obtida dele.-

  • A declaração deve ser criada com um tipo ResultSet de ResultSet.TYPE_FORWARD_ONLY. Este é o padrão, então nenhum código precisará ser reescrito para tirar vantagem disso, mas também significa que você não pode rolar para trás ou pular no ResultSet.-

  • A consulta dada deve ser uma única instrução, não várias instruções encadeadas junto com ponto e vírgula.

Exemplo 5.2 Configurando o tamanho da busca para ativar e desativar os cursores.

Alterar código para o modo de cursor é tão simples quanto definir o tamanho da busca da declaração para o tamanho apropriado. Definir o tamanho da busca de volta para 0 fará com que todas as linhas sejam armazenadas em cache (o comportamento padrão).

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

Assim, o ponto crucial do problema é que, por padrão, o Postgres começa no modo “autoCommit”, e também precisa / usa cursores para “paginar” os dados (ex: ler os primeiros 10K resultados, depois o próximo, depois o próximo), no entanto cursores só podem existir dentro de uma transação. Portanto, o padrão é ler todas as linhas, sempre, na RAM e permitir que o programa inicie o processamento da “primeira linha do resultado, depois da segunda” depois que todas elas chegarem, por duas razões: não está em uma transação não funciona), e também não foi definido um tamanho de busca.

Então, como a ferramenta de linha de comando psql alcança resposta em lote (sua configuração FETCH_COUNT ) para consultas, é ” FETCH_COUNT ” suas consultas selecionadas em uma transação de curto prazo (se uma transação ainda não estiver aberta), para que os cursores funcionem. Você pode fazer algo parecido com o 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; } 

Isso dá a vantagem de exigir menos memory RAM e, nos meus resultados, parece ser mais rápido, mesmo que você não precise salvar a memory RAM. Esquisito. Também oferece o benefício de que o processamento da primeira linha “comece mais rápido” (desde que processe uma página por vez).

E aqui está como fazer o caminho do “raw postgres cursor”, juntamente com o código demo completo, embora em meus experimentos parecesse que o caminho JDBC, acima, era um pouco mais rápido por qualquer motivo.

Outra opção seria autoCommit modo autoCommit , em todos os lugares, embora você ainda precise sempre especificar manualmente um fetchSize para cada nova instrução (ou definir um tamanho de busca padrão na string de URL).

Eu acho que sua pergunta é semelhante a este segmento: JDBC Pagination, que contém soluções para sua necessidade.

Em particular, para o PostgreSQL, você pode usar as palavras-chave LIMIT e OFFSET em sua solicitação: http://www.petefreitag.com/item/451.cfm

PS: Em código Java, sugiro que você use PreparedStatement em vez de instruções simples: http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Eu fiz como abaixo. Não é a melhor maneira que eu penso, mas 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; } } 

Pelo menos no meu caso, o problema estava no cliente que tenta buscar os resultados.

Queria obter um arquivo .csv com TODOS os resultados.

Eu encontrei a solução usando

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

(onde dbname o nome do db …) e redirecionando para um arquivo.