JohnMerlino's questions

Estou usando o ExecutorService com um pool de threads fixo de 50 e um pool de conexão de banco de dados fixo de 50, usando HikariCP. Cada thread de trabalho processa um pacote (um "relatório"), verifica se ele é válido (onde cada relatório deve ter um unit_id, hora, latitude e longitude exclusivos), obtém uma conexão db do pool de conexões e, em seguida, insere o relatório no a tabela de relatórios. A restrição de exclusividade é criada com postgresql e chamada de "reports_uniqueness_index". Quando eu tenho alto volume, recebo uma tonelada do seguinte erro:

org.postgresql.util.PSQLException: ERROR: duplicate key value 
  violates unique constraint "reports_uniqueness_index"

Aqui está o que eu acredito que seja o problema. Antes da inserção do banco de dados, realizo uma verificação para determinar se já existe um relatório na tabela com o mesmo unit_id, hora, latitude e longitude. Caso contrário, o relatório é válido e eu realizo a inserção. Porém, acho que por estar usando simultaneidade tenho 50 threads cada verificando ao mesmo tempo se o relatório é válido e como nenhum deles foi inserido ainda, cada thread pensa que tem um relatório válido e quando vai inseri-los em mesmo momento, é quando o postgresql gera o erro.

Gostaria de uma solução que não gerasse latência com a simultaneidade. Tenho tentado evitar o uso de instrução sincronizada ou um bloqueio reentrante porque as inserções de banco de dados precisam ocorrer o mais rápido possível. Esta é a inserção aqui:

 private boolean save(){
        Connection conn=null;
        Statement stmt=null;
        int status=0;
        DbConnectionPool dbPool = DbConnectionPool.getInstance();
        String sql = = "INSERT INTO reports"
        sql += " (unit_id, time, time_secs, latitude, longitude, speed, created_at)";
        sql += " values (...)";
        try {
            conn = dbPool.getConnection();
            stmt = conn.createStatement();
            status = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            return false;
        } finally {
            try {
                if (stmt != null)  
                {  
                    stmt.close();
                }  

                if (conn != null)  
                {  
                    conn.close();  
                }  
            } catch(SQLException e){}
        }

        if(status > 0){
            return true;            
        }

        return false;
}

Uma solução que pensei foi usar o próprio objeto Class como um objeto de bloqueio:

synchronized(Report.class) {
    status = stmt.executeUpdate(sql);
}

Mas isso atrasará a inserção de outros segmentos. Existe uma solução melhor?

Eu tenho uma tabela de unidades e uma tabela de relatórios. Uma unidade tem muitos relatórios. Portanto, a tabela de relatórios possui uma chave estrangeira unit_id que corresponde ao id da unidade. Portanto, há uma restrição de chave estrangeira na tabela de relatórios.

Comecei a perceber que às vezes os relatórios precisam ser criados mesmo que a unidade ainda não exista para eles. Então, quando tentei criar um relatório sem uma unidade, obviamente recebi este erro:

 ERROR: insert or update on table "reports" violates foreign key constraint "reports_unit_id_fkey"
  Detail: Key (unit_id)=(7295) is not present in table "units".

Minha pergunta é se eu remover essa restrição de chave estrangeira e permitir que os relatórios sejam criados sem uma unidade, isso afetará o desempenho quando eu tentar obter todos os relatórios associados a uma unidade?

SELECT * FROM reports WHERE unit_id=7295;

A única outra opção que tenho é inserir os relatórios em alguma tabela de órfãos, mas teria que copiar esses órfãos quando a unidade for criada, o que é um trabalho adicional.

Existe uma maneira de obter uma data e hora de quando um determinado banco de dados foi criado no Postgresql?

O \listcomando não fornece um carimbo de data/hora.

No PostgreSQL, uso a função now()and current_timestampe não vejo diferença:

# SELECT now(), current_timestamp;
              now               |              now               
--------------------------------+--------------------------------
 04/20/2014 19:44:27.215557 EDT | 04/20/2014 19:44:27.215557 EDT
(1 row)

Estou esquecendo de algo?

Estou vendo um exemplo de subconsulta correlacionada com aliases de um livro do PostgreSQL:

bpsimple=# SELECT * FROM orderinfo o,
bpsimple=# (SELECT * FROM customer c WHERE town = 'Bingham') c
bpsimple=# WHERE c.customer_id = o.customer_id;

Por que o calias é usado duas vezes aqui? O que está entre parênteses não é suficiente?

Estou lendo o livro de receitas do postgresql admin. Alguns dos parâmetros do servidor de banco de dados exigem uma reinicialização e, com um banco de dados ocupado, pode ser necessário reiniciar rapidamente. Há várias coisas a fazer para acelerar a reinicialização: 1) emitir um ponto de verificação normal antes do ponto de verificação de desligamento, 2) liberar todos os shared_buffers sujos no disco, 3) registrar o conteúdo do cache do banco de dados antes do desligamento e, em seguida, aquecer o cache novamente imediatamente após reiniciar. Portanto, o livro parece recomendar:

psql -c "CHECKPOINT"
psql -c "select pg_cache_save('mycache')"
pg_ctl -D datadir -m immediate restart
psql -c "select pg_cache_warm('mycache')"

Ele também mostrou esse método para:

pg_ctl -D datadir restart -m fast

Qual devo usar ao reiniciar um banco de dados ocupado que recebe inserções com muita frequência? Uma das opções acima ou existe um método melhor?

A consulta a seguir obtém o último relatório (onde latitude, longitude e segundos são <> 0) associado às unidades especificadas:

SELECT 
    reports.* 
FROM
    reports 
    INNER JOIN 
        units 
        ON units.id = reports.unit_id 
WHERE 
    reports.unit_id IN (1111, 1112, 1113) 
    AND 
    (
        reports.id = 
        (
            SELECT reports.id 
            FROM reports
            WHERE reports.unit_id = units.id
            AND
            reports.time_secs != 0
            AND 
            reports.latitude != 0.0
            AND
            reports.longitude != 0.0
            ORDER BY time desc
            LIMIT 1
        )
    )

Essa consulta levou vários minutos para ser executada e gostaria de saber se há uma otimização que eu possa fazer nela.

Eu tenho uma tabela de relatórios que tem literalmente milhões, senão centenas de milhões de registros de relatórios, que datam de 2011. Acho que está diminuindo exponencialmente minhas consultas. Eu só quero manter os relatórios dos últimos 6 meses. Eu tenho uma coluna de tempo na tabela de relatórios que fornece a hora atual para que eu possa usá-la para medir relatórios com mais de 6 meses. Então, estou pensando em executar algum tipo de trabalho cron no meu servidor ubuntu no qual o servidor postgresql está instalado. Mas estou procurando alguma orientação sobre como fazer isso.