Re: Why Postgres doesn't use TID scan?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



>>>>> "Vladimir" == Vladimir Ryabtsev <greatvovan@xxxxxxxxx> writes:

 >> The workaround is to do it like this instead:

 Vladimir> Strange, I tried to do like this, but the first thing came
 Vladimir> into my mind was array_agg() not array():

 Vladimir> delete from log
 Vladimir> where ctid = any(
 Vladimir>     select array_agg(ctid) from (
 Vladimir>         select ctid from log
 Vladimir>         where timestamp < now() at time zone 'pst' - interval '2 month'
 Vladimir>         limit 10
 Vladimir>     ) v);

 Vladimir> This query complained like this:

 Vladimir> ERROR: operator does not exist: tid = tid[]
 Vladimir> LINE 2: where ctid = any(
 Vladimir>                    ^
 Vladimir> HINT: No operator matches the given name and argument
 Vladimir> type(s). You might need to add explicit type casts.

 Vladimir> Which is strange because both array(select ...) and select
 Vladimir> array_agg() ... return the same datatype ctid[].

It's not so strange when you understand what's going on here. The
fundamental issue is that "ANY" has two meanings in PG, one of them
following the SQL standard and one not:

  x <operator> ANY (<subselect>)   -- standard
  x <operator> ANY (<expression>)  -- PG-specific

In the first case, the behavior follows the standard, which makes this a
generalization of IN: specifically, in the standard,

  x IN (select ...)

is just alternative syntax for

  x = ANY (select ...)

Obviously in this form, the result of the subselect is expected to be of
the same type and degree as "x", hence the error since tid and tid[] are
not the same type.

(Because this is the standard form, it's the one chosen when the syntax
is otherwise ambiguous between the two.)

The form x = ANY (somearray) is a PG extension, but because of the
ambiguity, the array can only be specified by something that doesn't
parse as a select. So array() works (as does array[] for the commonly
used case of an explicit list), but if you want to use a select to get
the array value, you have to add some kind of syntax that makes it not
parse as a select, e.g.:

  WHERE ctid = ANY ((select array_agg(...) from ...)::tid[])

In this case the cast forces it to parse as an expression and not a
subquery (it's not enough to just use the parens alone, because PG,
again unlike the SQL standard, allows any number of excess parens around
a subquery).

-- 
Andrew (irc:RhodiumToad)




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux