1

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?

  • Are you sure you want to use separate tablespaces? I am asking because I haven't yet seen a real usecase for it. – dezso Sep 20 '16 at 13:11
  • Not for every schema. Just 'grouping' schemas/objects (data/index) together. With the function I am able to decide at installation time where to put them. An existing one or a new one. – Marco Sep 20 '16 at 14:02
  • postgresql.org/docs/9.5/static/manage-ag-tablespaces.html have you read this? Please note that this makes live a bit harder when you have to restore the DB on a new machine or build a replica or similar. – dezso Sep 20 '16 at 19:29
  • I know that it makes life a bit harder when you use tablespaces in PostgreSQL. It is just that I have scripts that create all objects for a schema. I need to be sure that the tablespace is present. The CREATE TABLESPACE throws an exception if it already exists and causes the script to stop. With this function I want to work around this problem. – Marco Sep 21 '16 at 6:43
2

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:

  • Pitty that I need to use the dblink. – Marco Sep 26 '16 at 6:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.