Обсуждение: [GENERAL] WAL
Hi,
if I do something like this:
BEGIN;
UPDATE tbl SET data='something' WHERE pkey='selector';
UPDATE tbl SET data=NULL WHERE pkey='selector';
COMMIT;
Given 'selector' actually exists, I get a separate WAL entry for each of the updates. My question is, does the first update actually hit the data file?
If I am only interested in the first update hitting the WAL, does it make sense to do something like the above in a transaction? Would that help to keep the table small in a high concurrency situation? The table itself has a small fillfactor. So, in most cases there should be enough space to do a HOT update. For that HOT update, is that second update setting data to NULL beneficial or rather adverse?
Thanks,
Torsten
Torsten Förtsch wrote: > if I do something like this: > > BEGIN; > UPDATE tbl SET data='something' WHERE pkey='selector'; > UPDATE tbl SET data=NULL WHERE pkey='selector'; > COMMIT; > > Given 'selector' actually exists, I get a separate WAL entry for each of the updates. My question is, > does the first update actually hit the data file? It should, yes. > If I am only interested in the first update hitting the WAL, does it make sense to do something like > the above in a transaction? Would that help to keep the table small in a high concurrency situation? > The table itself has a small fillfactor. So, in most cases there should be enough space to do a HOT > update. For that HOT update, is that second update setting data to NULL beneficial or rather adverse? How could the second update *not* be WAL logged? Maybe you could explain what you are trying to achieve. Yours, Laurenz Albe
On Mon, Dec 12, 2016 at 12:37 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Torsten Förtsch wrote:
> if I do something like this:
>
> BEGIN;
> UPDATE tbl SET data='something' WHERE pkey='selector';
> UPDATE tbl SET data=NULL WHERE pkey='selector';
> COMMIT;
>
> Given 'selector' actually exists, I get a separate WAL entry for each of the updates. My question is,
> does the first update actually hit the data file?
It should, yes.
> If I am only interested in the first update hitting the WAL, does it make sense to do something like
> the above in a transaction? Would that help to keep the table small in a high concurrency situation?
> The table itself has a small fillfactor. So, in most cases there should be enough space to do a HOT
> update. For that HOT update, is that second update setting data to NULL beneficial or rather adverse?
How could the second update *not* be WAL logged?
Maybe you could explain what you are trying to achieve.
I am working on a logical decoder plugin, something similar to wal2json. I don't need 'something' to be permanently in the database. I just need it in the WAL stream. So, I was thinking that maybe keeping data=NULL permanently in the table might help to keep the table small. This table has only about 500 rows in my current case. But it is updated very often by different processes.
By now I have tried it out for short strings of 'something'. The size of tbl remains fixed and the same with or without the data=NULL update. Also, I ran both version each 3 times for 2000000 transactions. The run time difference is marginal. In the final version 'something' will be a string up to 1kb, I think.
Torsten