Waiting for 9.3 – Implement SQL-standard LATERAL subqueries.

On 7th of August, Tom Lane committed patch:

Implement SQL-standard LATERAL subqueries.
 
This patch implements the standard syntax of LATERAL attached to a
sub-SELECT in FROM, and also allows LATERAL attached to a function in FROM,
since set-returning function calls are expected to be one of the principal
use-cases.
 
The main change here is a rewrite of the mechanism for keeping track of
which relations are visible for column references while the FROM clause is
being scanned.  The parser "namespace" lists are no longer lists of bare
RTEs, but are lists of ParseNamespaceItem structs, which carry an RTE
pointer as well as some visibility-controlling flags.  Aside from
supporting LATERAL correctly, this lets us get rid of the ancient hacks
that required rechecking subqueries and JOIN/ON and function-in-FROM
expressions for invalid references after they were initially parsed.
Invalid column references are now always correctly detected on sight.
 
In passing, remove assorted parser error checks that are now dead code by
virtue of our having gotten rid of add_missing_from, as well as some
comments that are obsolete for the same reason.  (It was mainly
add_missing_from that caused so much fudging here in the first place.)
 
The planner support for this feature is very minimal, and will be improved
in future patches.  It works well enough for testing purposes, though.
 
catversion bump forced due to new field in RangeTblEntry.

This commit was just a beginning. Since 7th there were four other commits regarding LATERAL, but these were mostly fixes to some functionality glitches:

So, what are those “LATERAL subqueries"?

In most of the examples I saw recently these were showed using subselects, but I personally prefer examples with functions.

I have a table, which has not-empty acl:

$ \z whatever
                              Access privileges
 Schema │   Name   │ TYPE  │   Access privileges   │ COLUMN access privileges
────────┼──────────┼───────┼───────────────────────┼──────────────────────────
 public │ whatever │ TABLE │ depesz=arwdDxt/depesz↵│
        │          │       │ pgdba=arwdDxt/depesz ↵│
        │          │       │ test=r/depesz         │
(1 ROW)

All these privileges are stored in single column in pg_class table:

$ SELECT oid::regclass, relacl FROM pg_class WHERE relname = 'whatever';
   oid    │                           relacl
──────────┼────────────────────────────────────────────────────────────
 whatever │ {depesz=arwdDxt/depesz,pgdba=arwdDxt/depesz,test=r/depesz}
(1 ROW)

Representation of the rights is relatively easy to understand, but parsing it would be quite pointless. There is a way to extract data from this column to something more readable:

$ SELECT * FROM aclexplode('{depesz=arwdDxt/depesz,pgdba=arwdDxt/depesz,test=r/depesz}');
 grantor │ grantee │ privilege_type │ is_grantable
─────────┼─────────┼────────────────┼──────────────
   16384 │   16384 │ INSERT         │ f
   16384 │   16384 │ SELECT         │ f
   16384 │   16384 │ UPDATE         │ f
   16384 │   16384 │ DELETE         │ f
   16384 │   16384 │ TRUNCATE       │ f
   16384 │   16384 │ REFERENCES     │ f
   16384 │   16384 │ TRIGGER        │ f
   16384 │      10 │ INSERT         │ f
   16384 │      10 │ SELECT         │ f
   16384 │      10 │ UPDATE         │ f
   16384 │      10 │ DELETE         │ f
   16384 │      10 │ TRUNCATE       │ f
   16384 │      10 │ REFERENCES     │ f
   16384 │      10 │ TRIGGER        │ f
   16384 │   16514 │ SELECT         │ f
(15 ROWS)

Couple of important points:

  • aclexplode() function returns multiple rows
  • each of these rows contains multiple columns

So, what if I'd want to get list of tables that specific user has “select" privilege to, and I'd like to know who granted this privilege to this user.

This would be simple thing to do:

SELECT
    c.oid::regclass,
    u.username AS grantor
FROM
    pg_class c,
    aclexplode( c.relacl ) AS x
    JOIN pg_user u ON x.grantor = c.usesysid
WHERE
    x.privilege_type = 'SELECT'
    AND x.grantee = 16514;

Problem with this query is very simple – it doesn't work.

When parsing “aclexplode()" Pg will raise error. On 9.1 it looks like this:

ERROR:  FUNCTION expression IN FROM cannot refer TO other relations OF same query level
LINE 6:     aclexplode( c.relacl ) AS x
                        ^

on 9.3:

ERROR:  invalid reference TO FROM-clause entry FOR TABLE "c"
LINE 6:     aclexplode( c.relacl ) AS x
                        ^
HINT:  There IS an entry FOR TABLE "c", but it cannot be referenced FROM this part OF the query.

This problem can be worked around by using subselects, like this:

SELECT
    x.table_name,
    u.usename AS grantor
FROM
    (
        SELECT
            c.oid::regclass AS TABLE_NAME,
            (aclexplode( c.relacl )).*
        FROM
            pg_class c
    ) AS x
    JOIN pg_user u ON x.grantor = u.usesysid
WHERE
    x.privilege_type = 'SELECT'
    AND x.grantee = 16514
;

But this can lead to performance issues – basically writing:

SELECT (FUNCTION()).*

evaluates the function N times, where N is number of returned columns (not rows). I recall reading about it someplace, but can't find it now. Though simple test with raise notice proves that it still works like this even today.

The performance issue can be alleviated by adding one more layer of subselects, but it quickly makes the query ugly.

And here come LATERAL queries. Now, with LATERAL, I can simply:

SELECT
    c.oid::regclass,
    u.grantor
FROM
    pg_class c,
    LATERAL (
        SELECT
            u.usename AS grantor
        FROM
            aclexplode( c.relacl ) AS x
            JOIN pg_user u ON x.grantor = u.usesysid
        WHERE
            x.privilege_type = 'SELECT'
            AND x.grantee = 16514
    ) u

The magic is that I can now use columns from pg_class within “join" on the same level (aclexplode( c.relacl )), and it works.

Pretty cool addition, mentioned at least several times on #postgresql on irc, so I guess it will make some people very happy.

10 thoughts on “Waiting for 9.3 – Implement SQL-standard LATERAL subqueries.”

  1. Would not simply the below code work too? You should not need any subselects.

    SELECT
    c.oid::regclass,
    u.username as grantor
    FROM
    pg_class c,
    LATERAL aclexplode( c.relacl ) as x
    join pg_user u on x.grantor = c.usesysid
    WHERE
    x.privilege_type = ‘SELECT’
    and x.grantee = 16514;

  2. Nevermind, I missed the reference to x and c in on x.grantor = c.usesysid.

  3. Unrelated to the issue of LATERAL, but

    1. why do you use pg_class.oid::regclass instead of pg_class.relname?

    2. It seems aclexplode() is not documented and it was only introduced in 9.0.

  4. 1. because oid::regclass shows sensible values even for tables in other schemata.
    2. maybe – i just use it as an example

  6. This is equivalent to “left apply” or “outer apply” in SQL Server— it will be great to have this in PostgreSQL too. Thank you for writing this article!

  7. amazing. patch and explaination. after many hours searching. thank u

  9. Would it be possible to elaborate on how to prevent a function from being called for each field it returns using one more layer of subselects?

  10. @John:
    Instead of:

    select (function()).*;

    do:

    select (y).x from ( select function() as y ) as x;

Comments are closed.