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?
Import Notes
Reference msg id not found: e1829b8d-4f7c-4ee0-8820-d09907a4a5f4@Spark
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 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.
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 | 7It'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.