dw8547's questions

Nós estamos correndo:

user@primary/client-n:~$ psql -d database -c "SELECT version();"                                                                   
version
---------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.7 (Ubuntu 10.7-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
(1 row)

sobre:

user@primary/client-n:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.6 LTS
Release:        16.04
Codename:       xenial

e ter uma configuração com um primário e dois clientes de replicação de streaming configurados com:

user@client-n:~$ psql -d postgres -c "SELECT name, setting FROM pg_settings WHERE name IN ( 'hot_standby', 'hot_standby_feedback', 'max_standby_streaming_delay' );"
           name             | setting 
----------------------------+---------
hot_standby                 | on
hot_standby_feedback        | on
max_standby_streaming_delay | 150000
(3 rows)

Temos apenas um banco de dados (além dos padrões) e uma tabela no banco de dados. Aproximadamente 3 a 4 vezes por dia nos deparamos com uma situação especial de autovácuo que é descrita na documentação como:

[...] (autovacuum) não retornará o espaço para o sistema operacional, exceto no caso especial em que uma ou mais páginas no final de uma tabela ficam totalmente livres e um bloqueio de tabela exclusivo pode ser facilmente obtido

Estamos monitorando pg_lockse pudemos observar o daemon autovacuum fazendo o bloqueio ACCESS EXCLUSIVEno nível da tabela, o que, por sua vez, leva a uma série de processos bloqueados nos clientes, conforme ilustrado com as entradas de log abaixo:

Primário:

...
2019-06-04 05:59:29.154 BST [8998-1] LOG:  automatic vacuum of table "database.schema.table": index scans: 1
...

Cliente 1:

...
2019-06-04 05:59:03.660 BST [21167-858] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG:  process 21167 still waiting for AccessShareLock on relation 16390 of database 16388 after 1000.222 ms
2019-06-04 05:59:03.660 BST [21167-859] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL:  Process holding the lock: 2741. Wait queue: 21167, 1215, 26415.
2019-06-04 05:59:03.660 BST [21167-860] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT:  SELECT ...
2019-06-04 05:59:03.730 BST [1215-51] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG:  process 1215 still waiting for AccessShareLock on relation 16390 of database 16388 after 1000.188 ms at character 15
2019-06-04 05:59:03.730 BST [1215-52] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL:  Process holding the lock: 2741. Wait queue: 21167, 1215, 26415.
2019-06-04 05:59:03.730 BST [1215-53] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT:  SELECT ...
...
2019-06-04 05:59:19.975 BST [22242-4569] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG:  process 22242 still waiting for AccessShareLock on relation 16390 of database 16388 after 1000.281 ms at character 15
2019-06-04 05:59:19.975 BST [22242-4570] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL:  Process holding the lock: 2741. Wait queue: 21167, 1215, 26415, 2423, 1289, 24009, 22441, 2640, 1843, 1056, 23336, 28060, 1860, 1134, 19419, 14649, 2721, 29540, 20138, 22242.
2019-06-04 05:59:19.975 BST [22242-4571] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT:  SELECT...
...

E o processo segurando o bloqueio:

postgres=# SELECT pid, backend_type, wait_event_type, wait_event FROM pg_stat_activity WHERE pid = 2741;
 pid  | backend_type | wait_event_type |   wait_event
------+--------------+-----------------+----------------
 2741 | startup      | Activity        | RecoveryWalAll
(1 row)

Cliente 2:

...
2019-06-04 06:00:08.964 BST [16153-1] [PostgreSQL JDBC Driver@ip_address(port):role@database] | FATAL:  terminating connection due to conflict with recovery
2019-06-04 06:00:08.964 BST [16153-2] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL:  User was holding a relation lock for too long.
2019-06-04 06:00:08.964 BST [16153-3] [PostgreSQL JDBC Driver@ip_address(port):role@database] | HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2019-06-04 06:00:09.964 BST [5747-537] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG:  process 5747 still waiting for AccessShareLock on relation 16390 of database 16388 after 1000.248 ms
2019-06-04 06:00:09.964 BST [5747-538] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL:  Process holding the lock: 12709. Wait queue: 5747, 19765, 16036, 14617, 12280, 14513, 14728, 15398, 27611, 14542, 15948, 23398, 5853, 5098, 4324, 10760, 23480, 30192, 15300, 16228.
2019-06-04 06:00:09.964 BST [5747-539] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT:  SELECT ...
2019-06-04 06:00:09.975 BST [19765-6847] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG:  process 19765 still waiting for AccessShareLock on relation 16390 of database 16388 after 1000.180 ms
2019-06-04 06:00:09.975 BST [19765-6848] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL:  Process holding the lock: 12709. Wait queue: 5747, 19765, 16036, 14617, 12280, 14513, 14728, 15398, 27611, 14542, 15948, 23398, 5853, 5098, 4324, 10760, 23480, 30192, 15300, 16228.
2019-06-04 06:00:09.975 BST [19765-6849] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT:  SELECT ...
...
2019-06-04 06:01:25.487 BST [15873-1] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG:  process 15873 still waiting for AccessShareLock on relation 16390 of database 16388 after 1000.218 ms at character 15
2019-06-04 06:01:25.487 BST [15873-2] [PostgreSQL JDBC Driver@ip_address(port):role@database] | DETAIL:  Process holding the lock: 12709. Wait queue: 5747, 19765, 16036, 14617, 12280, 14513, 14728, 15398, 27611, 14542, 15948, 23398, 5853, 5098, 4324, 10760, 23480, 30192, 15300, 16228, 16127, 16285, 15873.
2019-06-04 06:01:25.487 BST [15873-3] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT:  SELECT ...
...
2019-06-04 06:01:29.160 BST [16127-6] [PostgreSQL JDBC Driver@ip_address(port):role@database] | LOG:  process 16127 acquired AccessShareLock on relation 16390 of database 16388 after 8560.748 ms at character 15
2019-06-04 06:01:29.160 BST [16127-7] [PostgreSQL JDBC Driver@ip_address(port):role@database] | STATEMENT:  SELECT ...
...

E o processo segurando o cadeado, novamente:

postgres=# SELECT pid, backend_type, wait_event_type, wait_event FROM pg_stat_activity WHERE pid = 2741;
 pid  | backend_type | wait_event_type |   wait_event
------+--------------+-----------------+----------------
12709 | startup      | Activity        | RecoveryWalAll
(1 row)

As consultas bloqueadas nos clientes levam à latência da API de 10 a 20 segundos e, ocasionalmente, a um alto número de respostas 5xx. Nossa equipe de SRE foi encarregada de reduzir a latência da API durante esses incidentes e estamos procurando maneiras de resolver isso, o que entendemos ser uma situação de nicho. No momento, estamos experimentando recovery_min_apply_delay = 120sno cliente 1 (daí as entradas de log posteriores) para que os dois clientes não bloqueiem ao mesmo tempo. Isso reduziu um pouco o número de respostas erradas e diminuiu um pouco os picos de latência. Não temos certeza sobre como resolver esse problema completamente e, de fato, se é possível fazê-lo. Agradeceríamos seu conselho. Encontramos este post relacionado , mas, infelizmente, também não foi resolvido.

Nós estamos correndo:

user@host:~$ psql -d database -c "SELECT version();"                                                                   
version
---------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.7 (Ubuntu 10.7-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
(1 row)

sobre:

user@host:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.6 LTS
Release:    16.04
Codename:   xenial

e ter a seguinte configuração:

database=# \d+ schema.table
                                                                  Table "schema.table"
           Column            |            Type             | Collation | Nullable |                     Default                     | Storage  | Stats target | Description
-----------------------------+-----------------------------+-----------+----------+-------------------------------------------------+----------+--------------+-------------
 column_1                    | bigint                      |           | not null | nextval('table_id_seq'::regclass)               | plain    |              |
 column_2                    | character varying           |           | not null |                                                 | extended |              |
 column_3                    | character varying           |           | not null |                                                 | extended |              |
 column_4                    | character varying           |           | not null |                                                 | extended |              |
 column_5                    | timestamp without time zone |           | not null |                                                 | plain    |              |
 column_6                    | timestamp without time zone |           |          |                                                 | plain    |              |
 column_7                    | character varying           |           | not null |                                                 | extended |              |
 column_8                    | jsonb                       |           | not null |                                                 | extended |              |
 column_9                    | jsonb                       |           |          |                                                 | extended |              |
 column_10                   | character varying           |           | not null |                                                 | extended |              |
 column_11                   | character varying           |           | not null |                                                 | extended |              |
 column_12                   | character varying           |           |          |                                                 | extended |              |
 column_13                   | character varying           |           |          |                                                 | extended |              |
 column_14                   | timestamp with time zone    |           | not null |                                                 | plain    |              |
 column_15                   | timestamp with time zone    |           | not null |                                                 | plain    |              |
Indexes:
    "table_pkey" PRIMARY KEY, btree ( column_1 )
    "table_idx_1" btree ( column_11)
    "table_idx_2" btree ( column_4, column_2, column_7, column_5, column_6 )
    "table_idx_3" btree ( column_7, column_11, column_15 )
    "table_idx_4" btree ( column_7, column_11, column_14 )
    "table_idx_5" btree ( column_7, column_11, column_5 )
    "table_idx_6" btree ( column_7, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER ), column_5 )
    "table_idx_7" btree ( column_15 )
    "table_idx_8" btree ( column_4, column_2, column_7, column_5, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER ) )
    "table_idx_9" btree ( column_4, column_2, column_7, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER) )
    "table_idx_a" btree ( column_7, column_4, column_2, ( ( column_8 ->> 'string_1'::TEXT )::INTEGER), ( ( column_8 ->> 'string_2'::TEXT )::INTEGER ) ) WHERE column_7::TEXT = 'string_3'::TEXT
Check constraints:
    "table_check_constraints" CHECK ( lower( column_10::TEXT ) <> 'string_4'::TEXT OR column_9 IS NOT NULL AND column_6 IS NOT NULL )

Autovacuum está ligado e configurado com:

user@host:~$ psql -d database -c "SELECT name, setting, pending_restart FROM pg_settings WHERE NAME ILIKE '%autovacuum%' ORDER BY name;"
                name                 |  setting              | pending_restart
-------------------------------------+-----------------------+-----------------
 autovacuum                          | on                    | f
 autovacuum_analyze_scale_factor     | 0.002                 | f
 autovacuum_analyze_threshold        | 10                    | f
 autovacuum_freeze_max_age           | 200000000             | f
 autovacuum_max_workers              | 5                     | f
 autovacuum_multixact_freeze_max_age | 400000000             | f
 autovacuum_naptime                  | 30                    | f
 autovacuum_vacuum_cost_delay        | 10                    | f
 autovacuum_vacuum_cost_limit        | 1000                  | f
 autovacuum_vacuum_scale_factor      | 0.001                 | f
 autovacuum_vacuum_threshold         | 25                    | f
 autovacuum_work_mem                 | -1                    | f
 log_autovacuum_min_duration         | 0 (env 1) /-1 (env 2) | f
(13 rows)

A seguinte sequência de eventos ocorreu no ambiente 1 , durante o qual autovacuumestava ligado e configurado como acima:

1. Todas as noites VACUUM (VERBOSE, ANALYZE)do banco de dados adicionado.
2. Algum tempo passa durante o qual o inchaço está no nível operacional normal.
3. Nightly VACUUM (VERBOSE, ANALYZE)do banco de dados é removido.
4. O índice table_idx_8que inclui uma coluna de tipo de dados JSONB é adicionado.
5. O índice table_idx_9que inclui uma coluna de tipo de dados JSONB é adicionado.
6. O surto de crescimento do inchaço começa e continua por 2 dias até atingir o pico.
7. VACUUM (VERBOSE, FULL)de mesa.
8. Bloat retorna aos níveis operacionais normais e permanece lá.

O tamanho do banco de dados (GB) ficou assim no ambiente 1 durante esta sequência de eventos:

E é assim que o bloat (GB) se parecia em ambiente 1 :

O número de linhas ativas no ambiente 1 :

O número de linhas mortas no ambiente 1 :

A seguinte sequência de eventos ocorreu em ambiente 2 , durante todo o qual autovacuumestava ligado e configurado como acima:

1. Todas as noites VACUUM (VERBOSE, ANALYZE)do banco de dados adicionado.
2. Algum tempo passa durante o qual o inchaço está no nível operacional normal.
3. Todas as noitesVACUUM (VERBOSE, ANALYZE)do banco de dados é removido.
4. Índicetable_idx_8que inclui uma coluna de tipo de dados JSONB é adicionado.
5. Índicetable_idx_9que inclui uma coluna de tipo de dados JSONB é adicionado.
6. O surto de crescimento do inchaço começa e continua por 2 dias até atingir o pico e derrubar o DB (disco cheio).
7. TRUNCATE TABLE schema.table.
8. A tabela schema.table é preenchida novamente.
9. O inchaço não se estabiliza e cresce até atingir o pico novamente.
10. TRUNCATE TABLE schema.tableantes que o disco encha novamente.
11. VACUUM (VERBOSE, FULL) do banco de dados.
12. A tabela schema.table é preenchida novamente.
13. Bloat continua a crescer!

O tamanho do banco de dados (GB) no ambiente 2 ficou assim durante esta sequência de eventos:

E esta é a aparência do bloat (GB) no ambiente 2 :

A única diferença entre esses dois ambientes é que eles são especificados de forma ligeiramente diferente (sendo 2 menos poderosos). Durante essas sequências de eventos, os volumes de gravação/leitura permaneceram inalterados em cada ambiente. Estamos usando essa consulta para medir o inchaço em bytes.

Eu verifiquei os logs do PostgreSQL, os logs de monitoramento e os logs de confirmação (Git) e identifiquei a adição dos dois índices como o gatilho para o inchaço, mas:

1. Isso está certo? A adição de um índice pode desencadear um surto de crescimento tão inchado?
2. Por que adicionar os índices acionou o inchaço, se foi?
3. Por que o ambiente 1 se estabilizou e o ambiente 2 não?
4. Como podemos estabilizar o ambiente 2?

Qualquer ajuda para responder a essas perguntas seria muito apreciada e escusado será dizer que estou feliz em fornecer qualquer outra informação que eu tenha perdido que possa ser útil.

Estou tentando encontrar referências para qual a diferença entre EXECUTEe ALLestá em:

GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION | ALL FUNCTIONS IN SCHEMA }...

mas tudo o que posso encontrar é o que os documentos dizem:

EXECUTE Permite o uso da função especificada e o uso de quaisquer operadores implementados sobre a função. Este é o único tipo de privilégio aplicável a funções. (Esta sintaxe também funciona para funções agregadas.)

Por exemplo, eu sei disso para esquemas ALL = CREATE + USAGE, mas eu queria verificar isso em referência a funções:

Este é o único tipo de privilégio aplicável a funções.

no trecho acima dos documentos implica que para funções ALL = EXECUTEe que:

GRANT ALL ON ALL FUNCTIONS...

é equivalente a:

GRANT EXECUTE ON ALL FUNCTIONS...

Estamos limpando/padronizando contas de usuário/aplicativo de banco de dados no sistema que tem uma mistura de contas que foram criadas usando comandos diferentes em momentos diferentes por indivíduos diferentes.

Temos uma situação em que, para algumas contas, o atributo de data de expiração da senha foi definido explicitamente como infinito e, para algumas, não conforme:

postgres=# \du+                                                                                    List of roles
    Role name     |                         Attributes                         | Member of |                                       Description
------------------+------------------------------------------------------------+-----------+-----------------------------------------------------------------------------------------
 user_1           |                                                            | {}        |
 user_2           |                                                            | {}        |
 user_3           | Password valid until infinity                              | {}        |
 user_4           | Password valid until infinity                              | {}        |

de modo a:

postgres=# SELECT * FROM pg_shadow;
     usename    | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil | useconfig
 ---------------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+-----------
  user_1        |    12345 | f           | f        | f       | f            | md5_foo                             |          |
  user_2        |    12346 | f           | f        | f       | f            | md5_foo                             |          |
  user_3        |    12347 | f           | f        | f       | f            | md5_bar                             | infinity |
  user_4        |    12348 | f           | f        | f       | f            | md5_bar                             | infinity |
 (4 rows)

e:

postgres=# SELECT * FROM pg_roles;
     rolname    | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid
 ---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
  user_1        | f        | f          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 12345
  user_1        | f        | f          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 12346
  user_1        | f        | f          | f             | f           | t           | f              |           -1 | ********    | infinity      | f            |           | 12347
  user_1        | f        | f          | f             | f           | t           | f              |           -1 | ********    | infinity      | f            |           | 12348
 (4 rows)

Ex: user_1e user_2foram criados com:

CREATE USER user_1/2 WITH ENCRYPTED PASSWORD 'foo';

considerando que user_3e user_4foram criados com:

CREATE USER user_3/4 WITH ENCRYPTED PASSWORD 'bar' VALID UNTIL 'infinity';

Queremos redefinir o VALID UNTILatributo para que:

postgres=# \du+                                                                                    List of roles
     Role name     |                         Attributes                         | Member of |                                       Description
 ------------------+------------------------------------------------------------+-----------+-----------------------------------------------------------------------------------------
  user_1           |                                                            | {}        |
  user_2           |                                                            | {}        |
  user_3           |                                                            | {}        |
  user_4           |                                                            | {}        |

Tentamos, sem sucesso:

1. ALTER ROLE user_1/2 WITH VALID UNTIL NULL;
2. ALTER ROLE user_1/2 WITH VALID UNTIL '';
3. ALTER ROLE user_1/2 WITH VALID UNTIL DEFAULT;

Portanto, a pergunta é: é possível redefinir o atributo de função de data de expiração de senha para NULL/ DEFAULT, de preferência sem precisar recriar a função?

Eu estou correndo:

version
-----------------------------------------------------------------------------------------------------
PostgreSQL 9.4.11 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
(1 row)

e:

pgAdmin III 1.22.2

na mesma:

Description: Ubuntu 14.04.5 LTS
Release: 14.04

Posso abrir o postgresql.confarquivo de configuração com, pgAdminmas não o pg_hba.confarquivo de configuração.

Ambos os arquivos estão no mesmo diretório ( /etc/postgresql/9.4/main):

-rw-r----- 1 postgres postgres  4641 Jun 10  2016 pg_hba.conf
-rw-r--r-- 1 postgres postgres 21501 Jun 10  2016 postgresql.conf

mas notei que os dois arquivos de configuração têm permissões diferentes. Eu não modifiquei as permissões de pg_hba.confpara torná-las diferentes das de postgresql.conf.

As permissões desses arquivos são diferentes por padrão? Existe algum motivo para pg_hba.confter permissões de leitura mais restritas? Posso ir em frente e modificar as permissões de pg_hba.conf?

Eu tenho uma tabela deflatorque é definida como:

               Table "deflator"
    Column   |       Type        | Modifiers
-------------+-------------------+-----------
country_code | smallint          | not null
country_name | character varying | not null
year         | smallint          | not null
deflator     | numeric           |
source       | character varying |

A saída de exemplo desta tabela se parece com:

country_code | country_name  | year | deflator | source
-------------+---------------+------+----------+----------
           1 | country_1     | 2016 |       12 | source_1
           1 | country_1     | 2015 |       11 | source_2
           1 | country_1     | 2014 |       10 | source_2
           2 | country_2     | 2016 |       15 | source_1
           2 | country_2     | 2015 |       14 | source_1
           2 | country_2     | 2014 |       13 | source_2
           3 | country_3     | 2016 |       18 | source_1
           3 | country_3     | 2015 |       17 | source_2
           3 | country_3     | 2014 |       16 | source_3
(9 rows)

Eu uso a seguinte consulta para dinamizar a tabela se eu excluir a colunasource :

SELECT
    *
FROM CROSSTAB (
    'SELECT
        country_code
        , country_name
        , year
        , deflator
     FROM dimension.master_oecd_deflator
     ORDER BY 1;'
     , $$ VALUES ('2014'::TEXT), ('2015'::TEXT), ('2016'::TEXT) $$
) AS "ct" (
    "country_code" SMALLINT
    , "country_name" TEXT
    , "2014" NUMERIC
    , "2015" NUMERIC
    , "2016" NUMERIC
);

A consulta acima me dá:

country_code |   country_name    | 2016 | 2015 | 2014 |
-------------+-------------------+------+--- --+------+
           1 | country_1         | 12   | 11   | 10   |
           2 | country_2         | 15   | 14   | 13   |
           3 | country_3         | 18   | 17   | 16   |

Mas como a fonte do deflator varia de ano para ano para cada país, quero incluir a sourcecoluna no pivô para que meu resultado desejado fique assim:

country_code |   country_name    | 2016 | 2016_source | 2015 | 2015_source | 2014 | 2014_source
-------------+-------------------+------+-------------+------+-------------+------+------------
           1 | country_1         | 12   | source_1    | 11   | source_2    | 10   | source_2
           2 | country_2         | 15   | source_1    | 14   | source_1    | 13   | source_2
           3 | country_3         | 18   | source_1    | 17   | source_2    | 16   | source_3

Como faço para modificar esta consulta para me dar a saída desejada? (Com a fonte de cada ano listada ao lado do ano). Isso é mesmo possível?

Existe uma maneira de dividir uma longa linha de código PL/pgSQL em várias linhas? Meu contexto é uma função de gatilho onde eu registro inserções em uma tabela conforme:

INSERT INTO insert_log (log_time, description)
VALUES (
    now()
    , 'A description. Made up of 3 semi long sentences. That I want to split, in the code, not in the log table, over 3 lines for readability.'
);

Eu tenho uma função chamada update_totalque se parece com isso:

CREATE OR REPLACE FUNCTION update_total (table_name CHARACTER VARYING, year CHARACTER(4), donor_type CHARACTER VARYING, donor_code CHARACTER(5)) RETURNS VOID AS $$

DECLARE

table_name ALIAS FOR $1;
year ALIAS FOR $2;
donor_type ALIAS FOR $3;
donor_code ALIAS FOR $4;
query_statement TEXT;

BEGIN

    query_statement :=
    '
    UPDATE gha.' || table_name  || '
    SET "' || year || '" = "' || year ||'_total"
    FROM gha.' || table_name || ' "' || year || '",
    (
        SELECT
        donor_type
        , COALESCE(SUM("' || year || '"), 0) AS "' || year ||'_total"
        FROM gha.' || table_name || '
        WHERE donor_type = ''' || donor_type || '''
        GROUP BY donor_type
    ) "new_data"
    WHERE
    gha.' || table_name || '.donor_code = ' || CAST(donor_code AS INT) || ';'
    ;

    EXECUTE query_statement;

END;
$$ LANGUAGE plpgsql;

Ele atualiza uma linha em uma tabela que contém um valor agregado e eu chamo, por exemplo, assim:

SELECT update_total ('bilateral_oda_dac_1', '1990', 'Multilateral', '20002');

A tabela que ele atualiza é configurada para que existam colunas para cada ano em um intervalo, ou seja:

   Column   |       Type        
------------+-------------------
 donor_code | smallint           
 donor_name | character varying 
 donor_type | character varying  
 1990       | numeric            
 1991       | numeric      
 1992       | numeric  
...

Com esta função só posso atualizar o valor agregado em uma coluna por vez, mas gostaria de poder atualizar um intervalo de colunas de uma só vez, algo como:

CREATE FUNCTION update_all_total ()
    RETURNS void LANGUAGE plpgsql AS

$BODY$
BEGIN

FOR i IN 1990 .. 1995
LOOP
   PERFORM update_total ('bilateral_oda_dac_1', i, 'DAC', '20001');
END LOOP;

END;
$BODY$;

(Usei esta postagem para começar: https://stackoverflow.com/questions/11164409/sql-call-function-multiple-times-in-a-loop-postgres-8-3 ). O código cortado acima não funciona porque a função que estou usando usa uma string como entrada e estou passando um número inteiro. Como posso configurar o loop para passar strings para a função que estou chamando dentro dele?

Estou precisando trabalhar com tabelas cujos nomes começam com números. Por exemplo, no crs_1973esquema existe uma tabela chamada 2015_04_21. Eu sei que quando o nome da tabela é formado por números, devo aspas duplas para trabalhar com ela, por exemplo, SELECT * FROM crs_1973."2015_04_21";(isso funciona para mim). No entanto, quero extrair informações sobre esta tabela do PostgreSQL information_schema. Especificamente, preciso dos tipos de dados das colunas na tabela 2015_04_21,

Eu tentei esta consulta com uma tabela cujo nome começa com uma letra e produz o resultado desejado:

SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'some_table_name_that_starts_with_a_letter'
ORDER  BY ordinal_position;

Porém, quando executo essa consulta com as tabelas cujos nomes iniciam com um número, isso não ocorre. Por exemplo:

WHERE  table_name = 'crs_1973.2015_04_21'

não funciona. Nem faz:

WHERE  table_name = 'crs_1973'.'2015_04_21'
WHERE  table_name = 'crs_1973'."2015_04_21"
WHERE  table_name = 'crs_1973'.'"2015_04_21"'
...

etc. Como faço essa consulta funcionar para um nome de tabela que começa com um número? Isso é possível?

Existem tabelas chamadas "2015_04_21" em vários esquemas diferentes, então preciso passar um nome de tabela qualificado para a consulta.

Eu criei a tabela donorno esquema referenceconforme:

CREATE TABLE reference.donor (
    donor_code smallint PRIMARY KEY,
    donor_name character varying NOT NULL,
    donor_type smallint REFERENCES reference.donor_type (type_id),
    alpha_2_code char(2) REFERENCES reference.iso_3166_1 (alpha_2_code)
);

Eu preenchi a tabela conforme:

INSERT INTO reference.donor (donor_code, donor_name, donor_type, alpha_2_code)
SELECT donor_code, donor_name, donor_type, alpha_2_code
FROM reference.donor_template;

Quando eu corro:

\dt+ reference.*

dentro do psql eu vejo a reference.donortabela:

                          List of relations
  Schema   |      Name      | Type  |  Owner   | Size  | Description 
-----------+----------------+-------+----------+-------+-------------
 reference | donor          | table | postgres | 16 kB | 
 reference | donor_template | table | postgres | 16 kB | 
 reference | donor_type     | table | postgres | 16 kB | 
 reference | iso_3166_1     | table | postgres | 48 kB | 
(4 rows)

Mas quando executo \dt+ donor*(ou \dt(+)) não vejo a reference.donortabela:

                          List of relations
  Schema   |      Name      | Type  |  Owner   | Size  | Description 
-----------+----------------+-------+----------+-------+-------------
 oecd_cl   | donor          | table | postgres | 16 kB | 
 reference | donor_template | table | postgres | 16 kB | 
 reference | donor_type     | table | postgres | 16 kB | 
(3 rows)

Por que só consigo ver a reference.donortabela se eu executar \dt+ reference.*ou \dt+ *.donor?
Eu estava esperando \dt(ou \dt+) exibi-lo, mas isso não acontece.

My search_pathinclui o esquema referencee o usuário postgrestem todas as permissões no esquema referencee todas as tabelas no esquema conforme:

GRANT ALL ON ALL TABLES IN SCHEMA reference TO postgres;

Só para esclarecer, tenho duas donortabelas, mas elas estão em dois esquemas diferentes, ou seja, oecd.donor& reference.donor. (Posso ver oecd.donorsem problemas quando uso \dt(+)dentro do psql).

Desejo instalar o PostgreSQL em um servidor que executa o Ubuntu 14.04.2 LTS.

Entendo que tenho duas opções:

1. Instale o PostgreSQL usando a distribuição do código-fonte
2. Instale o PostgreSQL usando apt-get install postgresql postgresql-contribestas instruções .

Qual opção devo usar?

• Haverá diferença no resultado?
• Ambas as opções resultarão na mesma instalação e configuração (supondo que eu escolha a instalação padrão com a opção nº 1)?
• Existe uma situação em que eu preferiria a opção nº 1 à opção nº 2 e vice-versa?