Re: [WIP] Performance Improvement by reducing WAL for Update Operation - Mailing list pgsql-hackers

On August 27, 2012 7:00 PM Amit Kapila wrote:
On August 27, 2012 5:58 PM Heikki Linnakangas wrote:
On 27.08.2012 15:18, Amit kapila wrote:
>>> I have implemented the WAL Reduction Patch for the case of HOT Update as


>> Let's do it for HOT updates only. Simon & Robert made good arguments on
>> why this is a bad idea for non-HOT updates.

> Okay, I shall do it that way.
> So now I shall send information about all the testing I have done for this
> Patch and then Upload it in CF.

Test Scenario's are below and testcases for same are attached with this mail.

Scenario1:
Recover the data where the field data is updated with different value from an exisitng data of an integer field.
Steps:
1. Start the server, create table, insert one record into the table.
2. update the integer field with other than existing data.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.

Scenario2:
Recover the data where the field data is updated with different value from an exisitng data of char and varchar fields.

Steps:
1. Start the server, create table, insert one record into the table.
2. update both char and varchar fields with other than existing data.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.

Scenario3:
Recover the data where the field data is updated with NULL value from an exisitng data of a field.
Steps:
1. Start the server, create table, insert one record into the table.
2. update a field with NULL value.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.

Scenario4:
Recover the data where the field data is updated with a proper value from an exisitng data of a field where the row
containsNULL data.  
Steps:
1. Start the server, create table, insert one record into the table.
2. update a field with a different value other than existing data.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.

Scenario5:
Recover the data where all fields data is updated with NULL value from an exisitng data of a fields.
Steps:
1. Start the server, create table, insert one record into the table.
2. update all fields with NULL values.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.

Scenario6:
Recover the data of updated field of a table where the table contains a toast table.
Steps:
1. Start the server, create table, insert one record into the table.
2. update a field with a different value other than existing data.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.

Scenario7:
Recover the data of updated field of a table where the row length is less than 128 bytes.
Steps:
1. Start the server, create table, insert one record into the table.
2. update a field with a different value other than existing data.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.

Scenario8:
Recover the data of updated field of a table where the before trigger modifies the tuple before the tuple updates.
Steps:
1. Start the server, create table, insert one record into the table.
2. create a before trigger which modifies the same record.
3. update a field with a different value other than existing data.
4. Shutdown the server immediately.
5. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.

Scenario9:
Recover the data where the update operation fails because of trigger returns NULL.
Steps:
1. Start the server, create table, insert one record into the table.
2. update a field fails as before trigger returns NULL.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The update command shouldn't be effective after recovery also.




With Regards,
Amit Kapila.