Thread: Is the Halloween problem an issue in Postgres

Is the Halloween problem an issue in Postgres

From
guyren@icloud.com
Date:
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?

Re: Is the Halloween problem an issue in Postgres

From
Tom Lane
Date:
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



Re: Is the Halloween problem an issue in Postgres

From
Thomas Kellerer
Date:
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.




Re: Is the Halloween problem an issue in Postgres

From
Adrian Klaver
Date:
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



Re: Is the Halloween problem an issue in Postgres

From
raf
Date:
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




Re: Is the Halloween problem an issue in Postgres

From
raf
Date:
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




Re: Is the Halloween problem an issue in Postgres

From
Ron
Date:
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.