Re: Update join performance issues

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема Re: Update join performance issues
Дата
Msg-id 4F7AF1F20200002500046B3F@gw.wicourts.gov
обсуждение исходный текст
Ответ на Re: Update join performance issues  (Andrew Dunstan <andrew@dunslane.net>)
Список pgsql-performance
Andrew Dunstan <andrew@dunslane.net> wrote:

> Why is test_one in the from clause? update joins whatever is in
> the from clause to the table being updated. You almost never need
> it repeated in the from clause.

This is actually one of the nastier "gotchas" in converting from
Sybase ASE or MS SQL Server to PostgreSQL -- there are syntactically
identical UPDATE statements with very different semantics when a
FROM clause is used in an UPDATE statement.  You need to do what the
OP was showing to use an alias with the target table under those
other products.

I suppose it might be possible to generate a warning when it appears
that someone is making this mistake, but it wouldn't be easy and
would probably not be worth the carrying cost.  The test would need
to be something like:

(1)  The relation which is the target of the UPDATE has no alias.
(2)  There is a FROM clause which included the target relation (with
     an alias).
(3)  There aren't any joining references between the UPDATE target
     and the relation(s) in the FROM clause.

-Kevin

В списке pgsql-performance по дате отправления:

Предыдущее
От: Andrew Dunstan
Дата:
Сообщение: Re: Update join performance issues
Следующее
От: Thomas Kellerer
Дата:
Сообщение: Re: Update join performance issues