Thread: DELETE ... USING LATERAL

Is it intentional that LATERAL elements in a USING clause of a DELETE
statement can't reference the table declared in the FROM clause?

Here's a somewhat contrived example. Suppose I have a table with one
jsonb column:

    create table int_arrays (int_array jsonb);
    insert into int_arrays values ('[1]'), ('[1, 2]'), ('[3, 4, 5]'),
('[1, 1, 1]');

If I want to delete every row whose array contains a value greater
than one, I would expect the following query to work:

    delete from int_arrays using jsonb_array_each(int_array) _ (val)
where val::integer > 1;

But that fails with:

    ERROR:  invalid reference to FROM-clause entry for table "int_arrays"
    LINE 1: delete from int_arrays using jsonb_array_each(int_array) _ (...
                                                          ^
    HINT:  There is an entry for table "int_arrays", but it cannot be
referenced from this part of the query.

So, ok, fine, the FROM and USING clauses are different scopes or
something. Except that doesn't quite explain the situation, because
you can't reuse the FROM table name in the USING clause:

    # delete from int_arrays using int_arrays;
    ERROR:  table name "int_arrays" specified more than once

Can someone shed some light on the situation here? Is there a reason
that LATERAL elements in the USING clause must be prevented from
accessing the FROM table or is the restriction just emergent behavior?

Nikhil

Nikhil Benesch <nikhil.benesch@gmail.com> writes:
> Is it intentional that LATERAL elements in a USING clause of a DELETE
> statement can't reference the table declared in the FROM clause?

Hmm ... doesn't work for UPDATE, either.

My mental model of these things is that the target table is cross-joined
to the additional tables as though by a comma in FROM, so that what
you have here ought to work much like

select * from int_arrays, jsonb_array_each(int_array) _ (val)
where val::integer > 1;

Clearly it's not doing so as far as the LATERAL scoping is concerned.
Maybe we are adding the target table to the query after the additional
tables, not before them?

Not sure I'd call this a bug exactly, but maybe there's room for
improvement.  Or maybe there is an actual semantic issue that
I'm not seeing right away.

            regards, tom lane

On Mon, Oct 4, 2021 at 1:48 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> My mental model of these things is that the target table is cross-joined
> to the additional tables as though by a comma in FROM [...]

Mine as well.

I just managed to dredge up some history here though. Turns out you
explicitly disabled this feature for 9.4 to make room for a future
feature to allow left-joining the target table [0]. Is support for
that feature still desired/planned? (If it's been permanently
abandoned for whatever reason, then maybe it's safe just to revert
158b7fa?)

[0]: https://github.com/postgres/postgres/commit/158b7fa6a34006bdc70b515e14e120d3e896589b

Nikhil

Nikhil Benesch <nikhil.benesch@gmail.com> writes:
> On Mon, Oct 4, 2021 at 1:48 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> My mental model of these things is that the target table is cross-joined
>> to the additional tables as though by a comma in FROM [...]

> Mine as well.

> I just managed to dredge up some history here though. Turns out you
> explicitly disabled this feature for 9.4 to make room for a future
> feature to allow left-joining the target table [0]. Is support for
> that feature still desired/planned? (If it's been permanently
> abandoned for whatever reason, then maybe it's safe just to revert
> 158b7fa?)

Ah-hah, I wondered whether we hadn't thought about this already,
but I'd not gotten around to researching it.

Not sure what to tell you about the state of the idea that the
target table could be re-specified in FROM/USING.  I'm hesitant
to close the door on it permanently, because people do periodically
wish to be able to left-join the target to something else.  But
the fact that no one's done anything about it for years suggests
that it's not that high on anyone's wish list.

            regards, tom lane

Michael Lewis <mlewis@entrata.com> writes:
> On Mon, Oct 4, 2021, 10:30 AM Nikhil Benesch <nikhil.benesch@gmail.com>
> wrote:
>> # delete from int_arrays using int_arrays;
>> ERROR:  table name "int_arrays" specified more than once

>> Don't you need to use an alias for the table in the using clause?

You could, but then you'd be creating a self-join on the target table
(and would need to add suitable WHERE clauses to constrain that join).
This might be the best near-term workaround, but it does seem ugly
and inefficient.

            regards, tom lane

On Mon, Oct 4, 2021 at 3:21 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Not sure what to tell you about the state of the idea that the
> target table could be re-specified in FROM/USING.  I'm hesitant
> to close the door on it permanently, because people do periodically
> wish to be able to left-join the target to something else.  But
> the fact that no one's done anything about it for years suggests
> that it's not that high on anyone's wish list.

Makes sense. Thanks for the insight. Sounds like the status quo is just fine.

Nikhil