PostgreSQL – How to Create Tablespace if it Does Not Exist

plpgsqlpostgresqlpostgresql-9.4tablespacestransaction

When I create a new schema in Postgres through executing a script in psql I want to put it in a tablespace. If this tablespace does not exist then I want to create it first. Since the default SQL does not have this option I created a function:

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;

Unfortunately when I execute it (select make_tablespace('marco', '/opt/marco', 'marco');) this gives an error:

ERROR: CREATE TABLESPACE cannot be executed from a function or multi-command string

I searched the internet and there seems to be a workaround (some years ago) by using the dblink package. I do not want to install this. Is there today another way? I can return the SQL statement as a string but how to execute it then?

Best Answer

The point here is that Postgres functions are almost but not quite like true stored procedures. Postgres functions (unlike stored procedures) run in the context of an outer transaction. Therefore, you cannot execute commands that cannot run in a transaction block, like VACUUM, CREATE DATABASE, ... or CREATE TABLESPACE. The manual is clear about that:

CREATE TABLESPACE cannot be executed inside a transaction block.

You have to run these commands as singular SQL commands. Postgres currently (up to version 9.6) also doesn't have autonomous transactions, which could be a workaround.

So the only workaround to include the command in a function or transaction is faking an autonomous transaction with dblink, just like you found already.

There are some other minor problems in your code. I suggest:

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;

Major points:

Related answer with code examples and more explanation:

Related Question