Brooks's questions

Não estou claro sobre o verdadeiro significado nas definições para funções IMUTÁVEIS, VOLÁTEIS e ESTÁVEIS.

Eu li a documentação, especificamente as definições de cada um.

IMMUTABLE indica que a função não pode modificar o banco de dados e sempre retorna o mesmo resultado quando recebe os mesmos valores de argumento ; ou seja, ele não faz pesquisas de banco de dados ou usa informações não presentes diretamente em sua lista de argumentos. Se esta opção for fornecida, qualquer chamada da função com argumentos constantes pode ser imediatamente substituída pelo valor da função.

STABLE indica que a função não pode modificar o banco de dados e que, em uma única varredura de tabela, ela retornará consistentemente o mesmo resultado para os mesmos valores de argumento , mas que seu resultado pode ser alterado nas instruções SQL. Esta é a seleção apropriada para funções cujos resultados dependem de pesquisas de banco de dados, variáveis ​​de parâmetro (como o fuso horário atual), etc. A família de funções current_timestamp se qualifica como estável, pois seus valores não mudam em uma transação.

VOLATILE indica que o valor da função pode ser alterado mesmo em uma única varredura de tabela, portanto, nenhuma otimização pode ser feita. Relativamente poucas funções de banco de dados são voláteis nesse sentido; alguns exemplos são random(), currval(), timeofday(). Mas observe que qualquer função que tenha efeitos colaterais deve ser classificada como volátil, mesmo que seu resultado seja bastante previsível, para evitar que as chamadas sejam otimizadas; um exemplo é setval().

Minha confusão vem com a condição para IMMUTABLE e STABLE que a função ALWAYS ou CONSISTENTLY retorna o mesmo resultado com os mesmos argumentos.

A definição IMMUTABLE afirma que a função não faz pesquisas de banco de dados ou usa informações não presentes diretamente em sua lista de argumentos. Então, para mim, isso significa que essas funções são usadas para manipular dados fornecidos pelo cliente e não devem ter instruções SELECT... embora isso soe um pouco estranho para mim.

Com STABLE, a definição é semelhante, pois diz que deve retornar consistentemente o mesmo resultado. Então, para mim, isso significa que toda vez que a função é chamada com os mesmos argumentos, ela deve retornar os mesmos resultados (mesmas linhas exatas, todas as vezes).

Então, para mim... isso significa que qualquer função que execute um SELECT em uma tabela ou tabelas que possam ser atualizadas, deve ser apenas volátil.

Mas, novamente... isso não soa bem para mim.

Trazendo isso de volta ao meu caso de uso, estou escrevendo funções que executam instruções SELECT com vários JOINs em tabelas que são constantemente adicionadas, portanto, espera-se que as chamadas de função retornem resultados diferentes cada vez que são chamadas, mesmo com os mesmos argumentos .

Então, isso significa que minhas funções devem ser VOLÁTEIS? Mesmo que a documentação indique relativamente poucas funções de banco de dados são voláteis nesse sentido ?

Obrigada!

Estou tentando criar um índice GIN para pesquisa de texto completo em um campo que pode conter texto em um dos vários idiomas diferentes. Os idiomas são predefinidos e saberemos em qual idioma queremos pesquisar no momento da consulta. Aqui está o esquema que eu tenho até agora ...

CREATE SCHEMA source;
CREATE SCHEMA common;

CREATE TABLE common.lang (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    code TEXT NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

INSERT INTO common.lang (name, code) VALUES
  ('english', 'en'),
  ('arabic', 'ar'),
  ('chinese', 'zh'),
  ('undefined', 'und');

CREATE TABLE source.user (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    screen_name TEXT NOT NULL UNIQUE,
    profile_lang_id INTEGER NOT NULL REFERENCES common.lang(id) ON DELETE CASCADE ON UPDATE CASCADE,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE INDEX source_user_names_idx ON source.user USING GIN (to_tsvector(SELECT common.lang.name FROM source.user INNER JOIN common.lang ON common.lang.id = source.user.profile_lang_id, name || ' ' || screen_name));

Ele relata um erro de sintaxe em ou próximo a "SELECT", o que faz sentido ... eu não acho que poderia apenas calçar uma instrução select lá, mas não tenho certeza de como usar corretamente uma instrução select dentro da chamada to_tsvector .

Eu olhei https://stackoverflow.com/a/21299033/4471711 , no entanto, isso codifica os idiomas, enquanto eu quero que os idiomas sejam extraídos de uma tabela dinamicamente no momento da criação do índice. Obviamente, se/quando adicionamos um idioma, precisaríamos recriar o índice.

Eu também olhei https://stackoverflow.com/a/15135730/4471711 , no entanto, isso não é compatível com a criação de um índice e requer apenas 1 parâmetro para to_tsvector, enquanto pareço precisar passar o idioma e o texto coluna.

Também estou tentando criar um índice de pesquisa de texto completo em um campo mais estilo documento, como abaixo:

CREATE TABLE source.post (
    id BIGSERIAL PRIMARY KEY,
    text TEXT NOT NULL
);

CREATE INDEX status_text_idx ON source.post USING GIN (to_tsvector(common_lang_id, text));

Novamente, com o índice acima, quero usar também o idioma especificado pela chave estrangeira para a tabela lang.

Minhas duas perguntas são:

1. Como uso corretamente a instrução SELECT para pegar o idioma da tabela lang usando sua chave estrangeira na tabela do usuário?

2. E se o lang for "indefinido" como é uma das opções? Eu não me importaria de padronizar o inglês para fins da chamada to_tsvector... Como eu adiciono esse padrão enquanto ainda permito que a tabela do usuário faça referência a "indefinido"?

Estou modelando tokens extraídos de tweets e tenho a estrutura da tabela abaixo:

CREATE TABLE process (
        id SERIAL PRIMARY KEY,
        ...
);

CREATE TABLE tweet (
        id SERIAL PRIMARY KEY,
        process_id INTEGER NOT NULL REFERENCES process(id) ON DELETE CASCADE ON UPDATE CASCADE,
        cleaned_text TEXT,
        process_tweet_id INTEGER NOT NULL,
        constraint tbl_statusid_processid unique(status_id, process_id)
        ...
);

CREATE TABLE token (
    id SERIAL PRIMARY KEY,
    tweet_id INTEGER NOT NULL REFERENCES tweet(id) ON DELETE CASCADE ON UPDATE CASCADE,
    type TEXT,
    value TEXT,
    ...
);

A tabela de tokens possui uma chave estrangeira para a tabela de tweets e a tabela de tweets possui uma chave estrangeira para a tabela de processos.

O campo 'type' na tabela de tokens pode ser 1 de 7 ou 8 valores possíveis (por exemplo, 'LOCATION', 'PERSON', etc.). Os tipos possíveis são estáticos e aplicados em outro lugar dentro do aplicativo, portanto, não há chance de qualquer desvio desses tipos definidos.

Estou tentando criar uma consulta que retornará todos os tweets referenciando um determinado process_id, o número total de tokens referenciando cada tweet, bem como o número de cada TIPO de token referenciando cada tweet (cada tipo tendo sua própria coluna no resultado definir).

  id   |     status_id      | tokencount | loctoken | persontoken 
-------+--------------------+------------+----------+-------------
  6158 | 672636476602122241 |          3 |        2 |           1
 10295 | 674183390430552064 |          4 |        2 |           2

O conjunto de resultados acima é basicamente o que espero obter de volta. Assim, o tweet ID# 6158 tem um total de 3 tokens, 2 dos quais são tokens de localização e 1 é um token de pessoa. O ID do tweet nº 10295 tem um total de 4 tokens, 2 locais e 2 pessoas.

O que tenho até agora é um simples JOIN, mas não tenho certeza de como incluir a contagem de cada tipo de token por tweet...

SELECT      tweet.id, tweet.status_id, count(token.id) AS tokenCount    
FROM        tweet     
INNER JOIN  token on token.tweet_id = tweet.id     
WHERE       tweet.process_id = 5     
GROUP BY    tweet.id;

Achei que poderia simplesmente adicionar count(token.type = 'LOCATION') como abaixo, mas isso simplesmente repetiu a coluna count(token.id).

SELECT      tweet.id, tweet.status_id, count(token.id) AS tokenCount, count(token.type = 'LOCATION) AS locToken, count(token.type = 'PERSON') AS personToken    
FROM        tweet     
INNER JOIN  token on token.tweet_id = tweet.id     
WHERE       tweet.process_id = 5     
GROUP BY    tweet.id;

Alguém pode fornecer a consulta que retornaria o que estou procurando? Esperançosamente, com uma boa explicação da solução?

Também gostaria muito de saber porque count(token.type = 'LOCATION') não funcionou e simplesmente retornou o mesmo número que count(token.id). Eu tentei por um capricho e realmente pensei que iria receber um erro.

Eu tenho um banco de dados PostgreSQL grande (> 700 GB). Estou trabalhando para importá-lo para o AWS RDS. Sei que existem módulos que causarão problemas, estamos lidando com isso separadamente, então, por enquanto, estou apenas trabalhando na migração dos dados para o RDS.

Estou migrando de e para PostgreSQL 9.4.5 usando pg_dump e pg_restore 9.4.5.

Após várias horas de transferência (pg_dump para pg_restore com um formato de diretório), verifiquei o log e percebi que há uma coisa (possivelmente uma tabela?) De propriedade do usuário Postgres que eu desconhecia.

Encontrei a linha no arquivo toc.dat que fez a chamada e descompactei o arquivo .dat.gz para descobrir que ele contém apenas "\." (sem as aspas) nele.

Portanto, minha pergunta é, além de interromper a transferência e recomeçar, existe uma maneira de simplesmente transferir esse comando e arquivo específicos assim que a importação for concluída?

Aqui estão os detalhes relevantes.

O comando pg_dump:

pg_dump -h localhost -p 5432 -d DBNAME -U USERNAME -F d -f /mnt/backup -j 7

O comando pg_restore:

pg_restore --dbname=DBNAME --no-tablespaces --host=HOSTNAME --port=5432 --username=USERNAME --verbose -F d -j 6 /mnt/backup

A seção do arquivo toc.dat. Percebo que o formato do diretório é gerado em formato binário, mas espero que o fato de isso ser identificável signifique que há uma maneira de voltar e corrigir esse erro.

^@^F^@^@^@public^A^A^@^@^@^@^H^@^@^@postgres^@^E^@^@^@false^@^C^@^@^@173^A^A^@^@^@^@    ^@^@^@10393.dat^@W<^@^@^@^A^@^@^@^@^A^@^@^@0^@^E^@^@^@26106^@^N^@^@^@search_name_66^@
^@^@^@TABLE DATA^@^C^@^@^@^@^@^@^@^@^@^@^@^@^@^@]^@^@^@COPY search_name_66 (place_id, search_rank, address_rank, name_vector, centroid) FROM stdin;

O erro real relatado por pg_restore:

pg_restore: [archiver (db)] Error from TOC entry 10393; 0 16787 TABLE DATA spatial_ref_sys postgres

Vejo outras perguntas que foram respondidas negativamente (ou seja, os índices não são transferidos com o padrão pg_restore). No entanto, parece que aconteceu no meu despejo/restauração recente e não tenho certeza se realmente foi transferido ou não.

Migrei meu banco de dados do PostgreSQL 9.3 para 9.4.5 e usei dump/restore para fazer isso.

Abaixo estão os comandos utilizados:

sudo -u postgres pg_dump -h localhost -p 5432 -d nominatim -F d -f dump/postgres/backup -j 20
sudo -u postgres pg_restore --create --dbname=nominatim --exit-on-error -h localhost -p 5432 -F d -j 7 dump/postgres/backup

O despejo e a restauração foram bem-sucedidos (sem erros).

Eu desativei o autovacuum enquanto fazia a restauração e então executei analyze(sem parâmetros) de dentro do psql e enquanto conectado ao banco de dados que foi restaurado.

A primeira coisa que notei é que na versão 9.3, o diretório de dados ocupava cerca de 860 GB, enquanto na versão 9.4 ocupa cerca de 680 GB. 9.4 é muito mais eficiente?

A segunda coisa que notei é que vejo os índices no banco de dados:

nominatim=# \d country_name
                   Table "public.country_name"
            Column             |         Type         | Modifiers 
-------------------------------+---------------------------------    
country_code                  | character varying(2) |   
name                          | hstore               |   
country_default_language_code | character varying(2) |   
partition                     | integer              |  

Indexes:
        "idx_country_name_country_code" btree (country_code)


nominatim=# \d idx_country_name_country_code
    Index "public.idx_country_name_country_code"
    Column    |         Type         |  Definition  
--------------+----------------------+--------------
 country_code | character varying(2) | country_code

btree, for table "public.country_name"


nominatim=# select * from pg_indexes where tablename = 'country_name';
 schemaname |  tablename   |           indexname           | tablespace |                                       indexdef                                        
------------+--------------+-------------------------------+------------+---------------------------------------------------------------------------------------
 public     | country_name | idx_country_name_country_code |            | CREATE INDEX idx_country_name_country_code ON country_name USING btree (country_code)
(1 row)

Ainda preciso reindexar ou meus índices, de alguma forma ou de outra, mudaram ou o que estou vendo é simplesmente a definição do índice? Quaisquer outras dicas sobre como gerenciar melhor o despejo/restauração, verificando meus índices, etc. seriam definitivamente apreciadas.