Thread: Is the Halloween problem an issue in Postgres
The Halloween problem is that it is a challenge for the database if you’re updating a field that is also in the WHERE clause of the same query.
I just saw a presentation from someone about how in SQL Server he recommended writing changes to a temp table and then writing them to the table as being much more efficient.
Does Postgres handle this problem efficiently, or should we follow a similar strategy?
I just saw a presentation from someone about how in SQL Server he recommended writing changes to a temp table and then writing them to the table as being much more efficient.
Does Postgres handle this problem efficiently, or should we follow a similar strategy?
guyren@icloud.com writes:
> The Halloween problem is that it is a challenge for the database if you’re updating a field that is also in the WHERE
clauseof the same query.
> I just saw a presentation from someone about how in SQL Server he recommended writing changes to a temp table and
thenwriting them to the table as being much more efficient.
That's nonsense as far as Postgres is concerned.
regards, tom lane
guyren@icloud.com schrieb am 02.12.2020 um 21:27: > The Halloween problem is that it is a challenge for the database if > you’re updating a field that is also in the WHERE clause of the same > query. > > I just saw a presentation from someone about how in SQL Server he > recommended writing changes to a temp table and then writing them to > the table as being much more efficient. It sounds strange to me, that this _is_ actually a problem. Why exactly is that a problem in SQL Server? And what are the consequences if you do it nevertheless.
On 12/2/20 2:02 PM, Thomas Kellerer wrote: > guyren@icloud.com schrieb am 02.12.2020 um 21:27: >> The Halloween problem is that it is a challenge for the database if >> you’re updating a field that is also in the WHERE clause of the same >> query. >> >> I just saw a presentation from someone about how in SQL Server he >> recommended writing changes to a temp table and then writing them to >> the table as being much more efficient. > > It sounds strange to me, that this _is_ actually a problem. > > Why exactly is that a problem in SQL Server? Yeah that was a new one to me. A quick search found: https://www.sqlshack.com/the-halloween-problem-in-sql-server-and-suggested-solutions/ > And what are the consequences if you do it nevertheless. > > > -- Adrian Klaver adrian.klaver@aklaver.com
On Wed, Dec 02, 2020 at 11:02:07PM +0100, Thomas Kellerer <shammat@gmx.net> wrote: > guyren@icloud.com schrieb am 02.12.2020 um 21:27: > > The Halloween problem is that it is a challenge for the database if > > you’re updating a field that is also in the WHERE clause of the same > > query. > > > > I just saw a presentation from someone about how in SQL Server he > > recommended writing changes to a temp table and then writing them to > > the table as being much more efficient. > > It sounds strange to me, that this _is_ actually a problem. > > Why exactly is that a problem in SQL Server? > And what are the consequences if you do it nevertheless. According to wikipedia, this problem was discovered on Halloween day, 1976. I find it hard to believe that any database would still exhibit that behaviour 44 years later. cheers, raf
On Wed, Dec 02, 2020 at 02:08:41PM -0800, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 12/2/20 2:02 PM, Thomas Kellerer wrote: > > guyren@icloud.com schrieb am 02.12.2020 um 21:27: > > > The Halloween problem is that it is a challenge for the database if > > > you’re updating a field that is also in the WHERE clause of the same > > > query. > > > > > > I just saw a presentation from someone about how in SQL Server he > > > recommended writing changes to a temp table and then writing them to > > > the table as being much more efficient. > > > > It sounds strange to me, that this _is_ actually a problem. > > > > Why exactly is that a problem in SQL Server? > > Yeah that was a new one to me. A quick search found: > > https://www.sqlshack.com/the-halloween-problem-in-sql-server-and-suggested-solutions/ > > > And what are the consequences if you do it nevertheless. It looks like the anser is no (unless I've misunderstood the problem): create table a (id serial not null primary key, a integer not null, b integer not null); create index a_a on a(a); insert into a (a, b) values (1, 2); insert into a (a, b) values (2, 3); insert into a (a, b) values (3, 4); insert into a (a, b) values (4, 5); insert into a (a, b) values (5, 6); insert into a (a, b) values (6, 7); update a set a = a + 1 where a < 4; select * from a order by id; drop table a cascade; results in: id | a | b ----+---+--- 1 | 2 | 2 2 | 3 | 3 3 | 4 | 4 4 | 4 | 5 5 | 5 | 6 6 | 6 | 7 It's the same with or without the index on a(a). cheers, raf
On 12/2/20 4:23 PM, raf wrote: > On Wed, Dec 02, 2020 at 02:08:41PM -0800, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > >> On 12/2/20 2:02 PM, Thomas Kellerer wrote: >>> guyren@icloud.com schrieb am 02.12.2020 um 21:27: >>>> The Halloween problem is that it is a challenge for the database if >>>> you’re updating a field that is also in the WHERE clause of the same >>>> query. >>>> >>>> I just saw a presentation from someone about how in SQL Server he >>>> recommended writing changes to a temp table and then writing them to >>>> the table as being much more efficient. >>> It sounds strange to me, that this _is_ actually a problem. >>> >>> Why exactly is that a problem in SQL Server? >> Yeah that was a new one to me. A quick search found: >> >> https://www.sqlshack.com/the-halloween-problem-in-sql-server-and-suggested-solutions/ >> >>> And what are the consequences if you do it nevertheless. > It looks like the anser is no (unless I've misunderstood the problem): > > create table a (id serial not null primary key, a integer not null, b integer not null); > create index a_a on a(a); > insert into a (a, b) values (1, 2); > insert into a (a, b) values (2, 3); > insert into a (a, b) values (3, 4); > insert into a (a, b) values (4, 5); > insert into a (a, b) values (5, 6); > insert into a (a, b) values (6, 7); > update a set a = a + 1 where a < 4; > select * from a order by id; > drop table a cascade; > > results in: > > id | a | b > ----+---+--- > 1 | 2 | 2 > 2 | 3 | 3 > 3 | 4 | 4 > 4 | 4 | 5 > 5 | 5 | 6 > 6 | 6 | 7 > > It's the same with or without the index on a(a). The Halloween Problem does not seem to cause the statement to fail, but to run slowly. -- Angular momentum makes the world go 'round.