Re: md5 checksum of a previous row

On 13/11/2017 16:11, Iaam Onkara wrote:
> @Achilleas I don't think I can avoid using lag. As inside the before insert  trigger I will need to read the previous row. Or am I misunderstanding you?
It will be a FOR EACH ROW trigger, you'll have to write inside smth like :

select md5(test::text) INTO tmplastmd5 from test ORDER BY created_at DESC LIMIT 1;
NEW.lastmd5 := tmplastmd5;

and you are set.

One thing you gotta make sure is on the monotony of created_at. I'd say make it a UNIQUE CONSTRAINT. (a problem you'd have also with the window function version as well)

>
> On Nov 13, 2017 2:14 AM, "Achilleas Mantzios" <achill(at)matrix(dot)gatewaynet(dot)com <mailto:achill(at)matrix(dot)gatewaynet(dot)com>> wrote:
>
> On 13/11/2017 10:01, Iaam Onkara wrote:
>> Yes obviously I will need an extra column to store the checksum of the previous row. The difficultly I was having was not knowing how to read the whole of the previous row, which I just learned
>> from Thomas and here is the updated fiddle http://www.sqlfiddle.com/#!17/69843/20 <http://www.sqlfiddle.com/#%2117/69843/20>
>
> And since you are disabling deletes, updates, your best bet is to try and write the last committed row's md5 inside the trigger we were talking about in the very first post, in which you won't
> even need lag() .
>
>>
>> On Mon, Nov 13, 2017 at 1:57 AM, Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com <mailto:achill(at)matrix(dot)gatewaynet(dot)com>> wrote:
>>
>> On 13/11/2017 09:47, Iaam Onkara wrote:
>>> you will have to still specify m2.id <http://m2.id> and m2.created_at but having to hard code the column names is not ideal as any schema change will require a change in the query. Hence
>>> my comment earlier "Seems to me this should be a  first class function in PostgreSQL, but its not."
>>
>> lag() does not work with record type, only anyelement.
>> What keeps you from writing a trigger and doing smth like :
>> select md5(test::text)  from test ORDER BY created_at DESC LIMIT 1;
>> This will do the md5 on the whole row.
>> You should have an extra col to store that.
>>
>>>
>>> Thanks,
>>> Onkara
>>>
>>> On Mon, Nov 13, 2017 at 1:42 AM, MS (direkt) <martin(dot)stoecker(at)stb-datenservice(dot)de <mailto:martin(dot)stoecker(at)stb-datenservice(dot)de>> wrote:
>>>
>>> select m2.* from .... will do the job in my example
>>>
>>>
>>> Am 13.11.2017 um 08:39 schrieb Iaam Onkara:
>>>> Thanks that is very helpful.
>>>>
>>>> Now is there a way to fetch previous record without having to specify the different column names? Seems to me this should be a  first class function in PostgreSQL, but its not.
>>>>
>>>> On Mon, Nov 13, 2017 at 1:31 AM, MS (direkt) <martin(dot)stoecker(at)stb-datenservice(dot)de <mailto:martin(dot)stoecker(at)stb-datenservice(dot)de>> wrote:
>>>>
>>>> Hi,
>>>>
>>>> you can easily join the preceeding row, e.g.
>>>>
>>>> select sub.id <http://sub.id>, sub.created_at, preceedingid, m2.* from (
>>>> select m.id <http://m.id>, m.created_at, lag(m.id <http://m.id>) over(order by m.created_at) as preceedingid from test m
>>>> order by m.created_at) as sub
>>>> left join test m2 on m2.id <http://m2.id>=sub.preceedingid order by sub.created_at;
>>>>
>>>> Regards, Martin
>>>>
>>>>
>>>> Am 13.11.2017 um 07:15 schrieb Iaam Onkara:
>>>>> Hi,
>>>>>
>>>>> I have a requirement to create an tamper proof chain of records for audit purposes. The pseudo code is as follows
>>>>>
>>>>> before_insert:
>>>>> 1. compute checksum of previous row (or conditionally selected row)
>>>>> 2. insert the computed checksum in the current row
>>>>> 3. using on-update or on-delete trigger raise error to prevent update/delete of any row.
>>>>>
>>>>> Here are the different options that I have tried using lag and md5 functions
>>>>>
>>>>> http://www.sqlfiddle.com/#!17/69843/2 <http://www.sqlfiddle.com/#%2117/69843/2>
>>>>>
>>>>> CREATE TABLE test
>>>>>     ("id" uuid DEFAULT uuid_generate_v4() NOT NULL,
>>>>>      "value" decimal(5,2) NOT NULL,
>>>>>      "delta" decimal(5,2),
>>>>> "created_at" timestamp default current_timestamp,
>>>>>      "words" text,
>>>>> CONSTRAINT pid PRIMARY KEY (id)
>>>>>     )
>>>>> ;
>>>>>
>>>>> INSERT INTO test
>>>>>     (value, words)
>>>>> VALUES
>>>>>     (51.0, 'A'),
>>>>>     (52.0, 'B'),
>>>>>     (54.0, 'C'),
>>>>>     (57.0, 'D')
>>>>> ;
>>>>>
>>>>> select
>>>>>   created_at, value,
>>>>>   value - lag(value, 1, 0.0) over(order by created_at) as delta,
>>>>> md5(lag(words,1,words) over(order by created_at)) as the_word,
>>>>> md5(textin(record_out(test))) as Hash
>>>>> FROM test
>>>>>   ORDER BY created_at;
>>>>>
>>>>> But how do I use lag function or something like lag to read the previous record as whole.
>>>>>
>>>>> Thanks,
>>>>> Onkara
>>>>> PS: This was earlier posted in 'pgsql-in-general' mailing list, but I think this is a more appropriate list, if I am wrong I am sorry
>>>>
>>>> --
>>>> Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g>; Tel.+49 / 221 / 9544 010 <tel:+49%20221%209544010>
>>>> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>>>>
>>>>
>>>
>>> --
>>> Widdersdorfer Str. 415, 50933 Köln <https://maps.google.com/?q=Widdersdorfer+Str.+415,+50933+K%C3%B6ln&entry=gmail&source=g>; Tel.+49 / 221 / 9544 010 <tel:+49%20221%209544010>
>>> HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
>>>
>>>
>>
>> --
>> Achilleas Mantzios
>> IT DEV Lead
>> IT DEPT
>> Dynacom Tankers Mgmt
>>
>>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt