From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Is the Halloween problem an issue in Postgres |
Date: | 2020-12-02 22:35:05 |
Message-ID: | 4fcb3891-5496-3b14-7cc1-f42f08a3c09a@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 12/2/20 4:23 PM, raf wrote:
> On Wed, Dec 02, 2020 at 02:08:41PM -0800, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
>> On 12/2/20 2:02 PM, Thomas Kellerer wrote:
>>> guyren(at)icloud(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2020-12-02 23:27:36 | pg_dump of partitioned table not working. |
Previous Message | raf | 2020-12-02 22:23:42 | Re: Is the Halloween problem an issue in Postgres |