Skip site navigation (1) Skip section navigation (2)

Re: Update join performance issues

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Kevin Kempter" <cs_dba(at)consistentstate(dot)com>, "Andrew Dunstan" <andrew(at)dunslane(dot)net>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Update join performance issues
Date: 2012-04-03 17:49:54
Message-ID: 4F7AF1F20200002500046B3F@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
Andrew Dunstan <andrew(at)dunslane(dot)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

In response to

pgsql-performance by date

Next:From: Thomas KellererDate: 2012-04-03 17:51:21
Subject: Re: Update join performance issues
Previous:From: Andrew DunstanDate: 2012-04-03 17:39:05
Subject: Re: Update join performance issues

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group