2

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?

Improve this question
4
  • Are you sure you want to use separate tablespaces? I am asking because I haven't yet seen a real usecase for it. Commented Sep 20, 2016 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. Commented Sep 20, 2016 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. Commented Sep 20, 2016 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. Commented Sep 21, 2016 at 6:43

1 Answer 1

Reset to default
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:

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.