O'Reilly logo

Troubleshooting PostgreSQL by Hans-Jürgen Schönig

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Procedures and indexing

In this section, you will learn the basic pitfalls of indexing and procedures. One of the most common issues is that people forget to define their procedures. In PostgreSQL, there are four types of procedures:

  • VOLATILE
  • STABLE
  • IMMUTABLE
  • [NOT] LEAKPROOF

A volatile procedure may return different outputs for the same input parameters within the same transaction:

test=# SELECT random(), random();
      random       |      random       
-------------------+-------------------
 0.906597905792296 | 0.368819046299905
(1 row)

The random() function is supposed to return different values all the time. This is the core purpose of a random generator. Logically, this has implications when it comes to indexing:

SELECT * FROM tab WHERE field = random();

Can PostgreSQL ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required