jgrocha's questions

Para serialcolunas usadas no Postgresql < 10, gerenciamos a sequência pelo nome. Conseguimos redefinir uma sequência com:

SELECT setval('table_id_seq', (SELECT MAX(id) FROM table));

A partir da versão 10, usando colunas de identidade, não há necessidade de usar o nome da sequência. Muito legal.

ALTER TABLE table ALTER COLUMN id RESTART WITH 1000;

Como defino a coluna de identidade para ser a max(id)sem saber o nome da sequência?

Tanto quanto posso ver na sintaxe ALTER TABLE , não há como ter uma subconsulta para calcular o início da sequência.

Eu gostaria de escrever algo como:

ALTER TABLE table ALTER COLUMN id RESTART WITH (SELECT MAX(id) FROM table);

O problema

Estou tentando remodelar uma tabela com muitas colunas. Estou tentando fazer isso independentemente da tabela específica, então estou tentando fazer isso para qualquer tabela.

Vamos usar uma tabela muito simples foo.

CREATE TABLE foo (id int, a text, b text, c text);
INSERT INTO foo VALUES (1, 'ant', 'cat', 'chimp'), (2, 'grape', 'mint', 'basil');

select * from foo;

| id|  a  |  b |  c  |
|---|-----|----|-----|
|  1| ant | cat|chimp|
|  2|grape|mint|basil|

Eu quero transformar a coluna ae bas clinhas.

Esta consulta funciona (para esta tabela específica):

SELECT id,
       unnest(array['a', 'b', 'c']) AS colname,
       unnest(array[a, b, c]) AS colvalue
FROM foo;

|id|colname|colvalue|
|--|-------|--------|
| 1|  a    |  ant   |
| 1|  b    |  cat   |
| 1|  c    | chimp  |
| 2|  a    | grape  |
| 2|  b    | mint   |
| 2|  c    | basil  |

Mas eu quero torná-lo genérico para qualquer tabela com muitas colunas.

O que eu já fiz

Para obter todas as colunas que quero transformar em linhas, posso usar:

SELECT column_name FROM information_schema.columns WHERE table_schema = 'public' AND table_name   = 'foo' and column_name ~ '^[a-z]$';

Então, usando a consulta anterior, posso fazer o seguinte:

WITH tablecolumns AS (SELECT array_agg( column_name ) as cols FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'foo' and column_name ~ '^[a-z]$') 
select id, 
       unnest( tablecolumns.cols ) AS colname,
       unnest( array[a, b, c] ) AS colvalue
FROM foo, tablecolumns;

Mas não consigo substituir array[a, b, c]por algo dinâmico. Se eu usar:

WITH tablecolumns AS (SELECT array_agg( column_name ) as cols FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'foo' and column_name ~ '^[a-z]$') 
select id, 
       unnest( tablecolumns.cols ) AS colname,
       unnest( tablecolumns.cols ) AS colvalue
FROM foo, tablecolumns;

o resultado não são os valores das colunas, mas apenas o nome das colunas.

Pergunta

Como posso unnestos valores das colunas?

Eu quero ter vários usuários capazes de criar esquemas e tabelas. Esses esquemas (e tabelas) devem estar disponíveis para todos. Eu gostaria de configurar o banco de dados, com todas as permissões em vigor (e permissões futuras usando ALTER DEFAULT PRIVILEGES). Os usuários não devem ter que alterar nenhuma permissão posteriormente.

Os esquemas são criados usando um software cliente, QGIS neste caso. Assim, os usuários só podem emitir arquivos CREATE SCHEMA newschemaname.

O problema é que, mesmo que os usuários pertençam ao mesmo grupo, eles não conseguem ver os esquemas uns dos outros.

Não é possível acessar os esquemas de outros

Estou usando dois papéis para teste. Usuários admin1e admin2, ambos pertencentes a grp_admin:

CREATE ROLE grp_admin NOLOGIN;
-- one user should be able to create db and extensions; these permissions are not inherited
CREATE ROLE admin1 WITH PASSWORD 'test2k19' LOGIN inherit SUPERUSER CREATEDB CREATEROLE; 
GRANT grp_admin TO admin1;
CREATE ROLE admin2 WITH PASSWORD 'test2k19' LOGIN inherit; 
GRANT grp_admin TO admin2;

Como admin1, criei um novo banco de dados e concedi algumas permissões (e também permissão para objetos futuros):

CREATE DATABASE people;
\c people

-- To create schemas and tables
GRANT ALL PRIVILEGES ON DATABASE people TO grp_admin;

ALTER DEFAULT PRIVILEGES GRANT select, INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO grp_admin;
ALTER DEFAULT PRIVILEGES GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO grp_admin;
ALTER DEFAULT PRIVILEGES GRANT EXECUTE ON FUNCTIONS TO grp_admin;

Se, como usuário, admin1eu crio um novo esquema e tabela, o outro admin2não tem acesso a ele:

create schema archive;
create table archive.vip ( thename varchar);
insert into archive.vip values ('Fernão de Magalhães');

Como admin2:

select * from archive.vip;
ERROR:  42501: permission denied for schema archive
LINE 1: select * from archive.vip;

Capaz de acessar os esquemas de outros

Se eu usar authorization grp_adminao criar o esquema, funciona. Como usuário admin1:

create schema future authorization grp_admin;
create table future.vip ( thename varchar);
insert into future.vip values ('Robot 5126');

Como usuário admin2:

select * from future.vip;
  thename   
------------
 Robot 5126

O problema

Como os usuários estão criando esquemas com o QGIS (um aplicativo cliente), emitindo apenas CREATE SCHEMA newschemaname, os usuários não podem compartilhar esquemas e tabelas entre si.

Como posso ter esse segundo admin2acessando os admin1esquemas criados? (Sem emitir permissões GRANT após cada esquema criado).

Para uma determinada tabela, quero ter uma cópia de todos os registros anteriores em meu banco de dados. Eu só insiro dados na tabela.

Tenho uma coluna adicional seqpara saber qual é o registro ativo .

Então, eu uso seq = 0para o registro atual. Cada registro com seq < 0é um antigo.

Esta é a tabela (eu omiti algumas colunas para simplificar)

CREATE TABLE public.machine (
    id character varying(25) NOT NULL,
    seq integer NOT NULL,
    vendor character varying(50),
    model character varying(50),
    CONSTRAINT pk_machine PRIMARY KEY (id, seq) );

Deixe-me mostrar alguns registros:

"cat-1" 0   "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -1  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -2  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -3  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -4  "Caterpillar"   "D3K2 TIER 4 FINAL"
"cat-1" -5  "Caterpillar"   "D3K2 TIER 4 FINAL"

Se eu precisar inserir um novo registro, eu faço:

UPDATE machine SET seq = seq-1 where id = 'cat-1';

e depois:

insert into machine (id, seq, vendor, model) values ( 'cat-1', 0, 'Caterpillar', 'D3K2 TIER 4 FINAL');

Mas o dele não funciona. Algumas vezes, a atualização falha com o erro:

ERROR:  duplicate key value violates unique constraint "pk_machine"
DETAIL:  Key (id, seq)=(cat-2, 0) already exists.

Se a atualização mudar linha por linha, a cada vez, ela poderá quebrar. Se a atualização for feita na ordem correta (começando pelo número mínimo), a atualização funcionará.

Como faço a ordem de atualização por seq descendente?

Eu escrevi um gatilho para isso, mas ele falha algumas vezes.

CREATE TRIGGER make_history
    BEFORE INSERT
    ON public.machine
    FOR EACH ROW
    EXECUTE PROCEDURE public.history();

CREATE OR REPLACE FUNCTION history() RETURNS TRIGGER AS $$
BEGIN
    UPDATE machine SET seq = seq-1 where id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;