Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What does %% in PL/pgSQL mean?

I was reading over Instagrams sharding solution and I noticed the following line:

SELECT nextval('insta5.table_id_seq') %% 1024 INTO seq_id;

What does the %% in the SELECT line above do? I looked up PostgreSQL and the only thing I found was that %% is utilized when you want to use a literal percent character.

CREATE OR REPLACE FUNCTION insta5.next_id(OUT result bigint) AS $$
DECLARE
   our_epoch bigint := 1314220021721;
   seq_id bigint;
   now_millis bigint;
   shard_id int := 5;
BEGIN
   SELECT nextval('insta5.table_id_seq') %% 1024 INTO seq_id;

   SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
   result := (now_millis - our_epoch) << 23;
   result := result | (shard_id << 10);
   result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;
like image 443
ConfusedDeer Avatar asked Apr 04 '14 14:04

ConfusedDeer


3 Answers

The only place I can think of, where a % would be doubled up in standard Postgres is inside the format() function, commonly used for producing a query string for dynamic SQL. Compare examples here on SO.

The manual:

In addition to the format specifiers described above, the special sequence %% may be used to output a literal % character.

Tricky when using the modulo operator % in a dynamic statement!

I suspect they are running dynamic SQL behind the curtains - which they generalized and simplified for the article. (The schema-qualified name of the sequence is 'insta5.table_id_seq' and the table wouldn't be named "table".) In the process they forgot to "unescape" the modulo operator.
That's what they may actually be running:

EXECUTE format($$SELECT nextval('%I') %% 1024$$, seq_name)
INTO seq_id;
like image 117
Erwin Brandstetter Avatar answered Oct 28 '22 07:10

Erwin Brandstetter


With default installation (on 9.2):

ERROR: operator does not exist: bigint %% integer
SQL state: 42883

So i would say it could be

  • a custom operator
  • or a typo, and they want to write the modulo operator: %
like image 30
pozs Avatar answered Oct 28 '22 08:10

pozs


Looks like an escaped modulo operator to me.

like image 26
Jodrell Avatar answered Oct 28 '22 08:10

Jodrell