Início / dba / Perguntas / 150148
Accepted
Marco
Asked: 2016-09-21 04:44:20 +0800 CST

Como criar um tablespace se ele não existir

  • 772

Quando crio um novo schema no Postgres através da execução de um script em psqlquero colocá-lo em um tablespace. Se esse espaço de tabela não existir, quero criá-lo primeiro. Como o SQL padrão não tem essa opção criei uma função:

CREATE OR REPLACE FUNCTION make_tablespace(tablespace CHARACTER,
                                           directory CHARACTER,
                                           owner CHARACTER)
  RETURNS void
AS
$$
BEGIN
  IF tablespace = '' THEN
    RAISE EXCEPTION 'No tablespace.';
  END IF;
  PERFORM SPCNAME FROM PG_TABLESPACE WHERE SPCNAME=tablespace;
  IF NOT FOUND THEN
    IF directory = '' THEN
      RAISE EXCEPTION 'No directory.';
    END IF;
    IF owner = '' THEN
      RAISE EXCEPTION 'No owner.';
    END IF;
    EXECUTE 'CREATE TABLESPACE '||tablespace||' OWNER '||owner||' LOCATION '''||directory||''';';
    RAISE NOTICE 'Tablespace % created.', tablespace;
  ELSE
    RAISE NOTICE 'Tablespace % already exists.', tablespace;
  END IF;
END $$ LANGUAGE plpgsql;

Infelizmente quando executo ( select make_tablespace('marco', '/opt/marco', 'marco');) dá um erro:

ERRO: CREATE TABLESPACE não pode ser executado a partir de uma função ou string de vários comandos

Pesquisei na internet e parece haver uma solução alternativa (alguns anos atrás) usando o dblinkpacote. Eu não quero instalar isso. Existe hoje outra maneira? Posso retornar a instrução SQL como uma string, mas como executá-la?

postgresql postgresql-9.4

1 respostas

  1. Best Answer

    O ponto aqui é que as funções do Postgres são quase, mas não exatamente, procedimentos armazenados verdadeiros. As funções do Postgres (ao contrário dos procedimentos armazenados) são executadas no contexto de uma transação externa. Portanto, você não pode executar comandos que não podem ser executados em um bloco de transação, como VACUUM, CREATE DATABASE, ... ou CREATE TABLESPACE. O manual é claro sobre isso:

    CREATE TABLESPACEnão pode ser executado dentro de um bloco de transação.

    Você precisa executar esses comandos como comandos SQL singulares. Postgres atualmente (até a versão 9.6) também não possui transações autônomas , o que poderia ser uma solução alternativa.

    Portanto, a única solução alternativa para incluir o comando em uma função ou transação é simular uma transação autônoma com dblink, assim como você já encontrou.

    Existem alguns outros problemas menores em seu código. Eu sugiro:

    CREATE OR REPLACE FUNCTION make_tablespace(tablespace text,
                                           directory text,
                                           owner text)
  RETURNS void AS
$func$
BEGIN
   IF tablespace <> '' THEN  -- catches '' *and* NULL
      -- do nothing
   ELSE
      RAISE EXCEPTION 'No tablespace.';
   END IF;

   IF EXISTS (SELECT 1 FROM pg_tablespace WHERE spcname = tablespace) THEN
      RAISE NOTICE 'Tablespace % already exists.', tablespace;
      EXIT;
   END IF;

   IF directory <> '' THEN
   ELSE
      RAISE EXCEPTION 'No directory.';
   END IF;

   IF owner <> '' THEN
   ELSE
      RAISE EXCEPTION 'No owner.';
   END IF;

   PERFORM dblink_connect('myserver');  -- name of foreign server
   PERFORM dblink_exec(format('CREATE TABLESPACE %I OWNER %I LOCATION %L', tablespace, owner, directory));
   RAISE NOTICE 'Tablespace % created.', tablespace;
   PERFORM dblink_disconnect();
END
$func$  LANGUAGE plpgsql;

    Pontos principais:

    Resposta relacionada com exemplos de código e mais explicações:

    • 2

relate perguntas