3

I've been playing with Postgres for a week or so and I'm seeing if it's possible to set the min/max values for a sequence using an expression. The specific goal is to automate create-scripts for disjoint ranges between servers to avoid key-conflicts in a multi-master setup.

For example, using psql:

CREATE SEQUENCE key_seq MINVALUE (:servernum * :stride)
                        MAXVALUE ((:servernum + 1) * :stride - 1);

This gives me a syntax error. Variables are interpolated verbatim, no calculations happen.

An alternative would be to set a variable based on the output of an expression:

\set minvalue (:servernum * :stride)

But the \set statement doesn't evaluate expressions.

  • 1
    Use \pset in psql. Or use an EXECUTE block in a plpgsql DO block to construct the statement. – Craig Ringer May 27 '16 at 5:28
  • @CraigRinger: Did you mean \gset? – Erwin Brandstetter May 28 '16 at 3:07
  • Er, yes, I did. – Craig Ringer May 29 '16 at 0:44
4

You need \set (not \pset!) to set psql variables. Unlike assignment in a Unix shell the psql assignment is not capable of arithmetic operations. You could use the psql command \! to execute shell commands, but I'll suggest two different approaches:

Option 1: Let Postgres calculate and set new variables with \gset

The manual about \gset:

Sends the current query input buffer to the server and stores the query's output into psql variables

Then you can interpolate the calculated numbers as text and everything works.
psql code:

test=# \set servernum 5
test=# \set stride 300
test=# SELECT :servernum * :stride AS minvalue, (:servernum + 1) * :stride - 1 AS maxvalue \gset
test=# CREATE SEQUENCE key_seq MINVALUE :minvalue MAXVALUE :maxvalue;
CREATE SEQUENCE

A nice blog explaining \gset:

I calculated and set :minvalue and :maxvalue in a single SELECT to optimize performance. Still, the downside of this approach is that you need an extra round trip to the Postgres server.

Option 2: Function with dynamic SQL for repeated use

You could use a DO statement, but (the manual again):

Variable interpolation will not be performed within quoted SQL literals and identifiers.

This would complicate string concatenation. I suggest to create a function and use format() for clean code. If it's for the current session only, you can make it a temporary function by schema-qualifying with pg_temp. So the function is only visible to your current session and dropped at the end of the session. Temporary functions are an undocumented feature - even though suggested by Tom Lane. Details:

So:

test=# CREATE FUNCTION pg_temp.f_my_seq(_seq text, _servernum int, _stride int)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format('CREATE SEQUENCE %I MINVALUE %s MAXVALUE %s'
                 , _seq, _servernum * _stride, (_servernum + 1) * _stride - 1);
END
$func$ LANGUAGE plpgsql;

test=# \set servernum 5
test=# \set stride 300

test=# SELECT pg_temp.f_my_seq('key_seq', :servernum, :stride);

We need dynamic SQL with EXECUTE. The manual about Variable Substitution:

Variable substitution currently works only in SELECT, INSERT, UPDATE, and DELETE commands, because the main SQL engine allows query parameters only in these commands. To use a non-constant name or value in other statement types (generically called utility statements), you must construct the utility statement as a string and EXECUTE it.

Related answer on SO:

  • Yes, the \gset meta-command is what I'm looking for. However I think some of your examples use literal "3" where it should be the :stride or _stride variable. Thanks! – M0les May 28 '16 at 11:37
  • @M0les: Ah, yes. Debug traces. Thanks, fixed. – Erwin Brandstetter May 28 '16 at 14:42

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.