Обсуждение: [SQL] md5 checksum of a previous row

Поиск
Список
Период
Сортировка

[SQL] md5 checksum of a previous row

От
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

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

Re: [SQL] md5 checksum of a previous row

От
Achilleas Mantzios
Дата:
You just need :
- to define the way to find the previous row
- an BEFORE INSERT trigger to compute the checksum, read up some pg/plsql or SQL to write your function. To read the row as a whole just use the table name without column in the select
- two rules (update / delete) to do nothing

On 13/11/2017 08:15, Iaam Onkara wrote:
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

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


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

Re: [SQL] md5 checksum of a previous row

От
Iaam Onkara
Дата:
The real question is how to get whole previous row not just a column  from the previous row.

Thanks,
Onkara

On Mon, Nov 13, 2017 at 1:25 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
You just need :
- to define the way to find the previous row
- an BEFORE INSERT trigger to compute the checksum, read up some pg/plsql or SQL to write your function. To read the row as a whole just use the table name without column in the select
- two rules (update / delete) to do nothing


On 13/11/2017 08:15, Iaam Onkara wrote:
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

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


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

Re: [SQL] md5 checksum of a previous row

От
"MS (direkt)"
Дата:
Hi,

you can easily join the preceeding row, e.g.

select sub.id, sub.created_at, preceedingid, m2.* from (
select m.id, m.created_at, lag(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=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

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; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer

Re: [SQL] md5 checksum of a previous row

От
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.stoecker@stb-datenservice.de> wrote:
Hi,

you can easily join the preceeding row, e.g.

select sub.id, sub.created_at, preceedingid, m2.* from (
select m.id, m.created_at, lag(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=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

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; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer

Re: [SQL] md5 checksum of a previous row

От
"MS (direkt)"
Дата:
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.stoecker@stb-datenservice.de> wrote:
Hi,

you can easily join the preceeding row, e.g.

select sub.id, sub.created_at, preceedingid, m2.* from (
select m.id, m.created_at, lag(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=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

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; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer 


-- 
Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer

Re: [SQL] md5 checksum of a previous row

От
Iaam Onkara
Дата:
you will have to still specify 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."

Thanks,
Onkara

On Mon, Nov 13, 2017 at 1:42 AM, MS (direkt) <martin.stoecker@stb-datenservice.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.stoecker@stb-datenservice.de> wrote:
Hi,

you can easily join the preceeding row, e.g.

select sub.id, sub.created_at, preceedingid, m2.* from (
select m.id, m.created_at, lag(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=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

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; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer 


-- 
Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer

Re: [SQL] md5 checksum of a previous row

От
Thomas Kellerer
Дата:
> But how do I use lag function or something like lag to read the previous record as whole.

You can reference the whole row by using the table name:

select created_at,       value,      value - lag(value, 1, 0.0) over(order by created_at) as delta,
md5(lag(test::text)over(order by created_at)) as the_row
 
FROM test
ORDER BY created_at;

The table reference "test" returns the whole row, e.g. something like: 
  (bbf35815-479b-4b1b-83c5-5e248aa0a17f,52.00,,"2017-11-13 08:45:16.17231",B) 

that can be cast to text and then you can apply the md5() function on the result. 
It does include the parentheses and the commas, but as it does that for every
row in a consistent manner, it shouldn't matter.

> 2. insert the computed checksum in the current row

That can also be done using the above technique, something like: 
  new.hash := md5(new::text);

Thomas






-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] md5 checksum of a previous row

От
Iaam Onkara
Дата:
Thank you Thomas that 'test::txt' was the answer I was looking for. :)

Apparently the documentation for lag function https://www.postgresql.org/docs/9.6/static/functions-window.html wasn't very informative on the use of 'test::txt' at least for me

Best Regards,
Onkara

On Mon, Nov 13, 2017 at 1:49 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
> But how do I use lag function or something like lag to read the previous record as whole.

You can reference the whole row by using the table name:

select created_at,
       value,
       value - lag(value, 1, 0.0) over(order by created_at) as delta,
       md5(lag(test::text) over(order by created_at)) as the_row
FROM test
ORDER BY created_at;

The table reference "test" returns the whole row, e.g. something like:

   (bbf35815-479b-4b1b-83c5-5e248aa0a17f,52.00,,"2017-11-13 08:45:16.17231",B)

that can be cast to text and then you can apply the md5() function on the result.
It does include the parentheses and the commas, but as it does that for every
row in a consistent manner, it shouldn't matter.

> 2. insert the computed checksum in the current row

That can also be done using the above technique, something like:

   new.hash := md5(new::text);

Thomas






--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] md5 checksum of a previous row

От
Achilleas Mantzios
Дата:
On 13/11/2017 09:47, Iaam Onkara wrote:
you will have to still specify 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.stoecker@stb-datenservice.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.stoecker@stb-datenservice.de> wrote:
Hi,

you can easily join the preceeding row, e.g.

select sub.id, sub.created_at, preceedingid, m2.* from (
select m.id, m.created_at, lag(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=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

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; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer 


-- 
Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer 


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

Re: [SQL] md5 checksum of a previous row

От
Achilleas Mantzios
Дата:
+1 , cool!

On 13/11/2017 09:49, Thomas Kellerer wrote:
>> But how do I use lag function or something like lag to read the previous record as whole.
> You can reference the whole row by using the table name:
>
> select created_at,
>         value,
>         value - lag(value, 1, 0.0) over(order by created_at) as delta,
>         md5(lag(test::text) over(order by created_at)) as the_row
> FROM test
> ORDER BY created_at;
>
> The table reference "test" returns the whole row, e.g. something like:
>
>     (bbf35815-479b-4b1b-83c5-5e248aa0a17f,52.00,,"2017-11-13 08:45:16.17231",B)
>
> that can be cast to text and then you can apply the md5() function on the result.
> It does include the parentheses and the commas, but as it does that for every
> row in a consistent manner, it shouldn't matter.
>
>> 2. insert the computed checksum in the current row
> That can also be done using the above technique, something like:
>
>     new.hash := md5(new::text);
>
> Thomas
>
>
>
>
>
>

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



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] md5 checksum of a previous row

От
Iaam Onkara
Дата:
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

On Mon, Nov 13, 2017 at 1:57 AM, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote:
On 13/11/2017 09:47, Iaam Onkara wrote:
you will have to still specify 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.stoecker@stb-datenservice.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.stoecker@stb-datenservice.de> wrote:
Hi,

you can easily join the preceeding row, e.g.

select sub.id, sub.created_at, preceedingid, m2.* from (
select m.id, m.created_at, lag(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=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

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; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer 


-- 
Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer 


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

Re: [SQL] md5 checksum of a previous row

От
Achilleas Mantzios
Дата:
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

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@matrix.gatewaynet.com> wrote:
On 13/11/2017 09:47, Iaam Onkara wrote:
you will have to still specify 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.stoecker@stb-datenservice.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.stoecker@stb-datenservice.de> wrote:
Hi,

you can easily join the preceeding row, e.g.

select sub.id, sub.created_at, preceedingid, m2.* from (
select m.id, m.created_at, lag(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=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

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; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer 


-- 
Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
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

Re: [SQL] md5 checksum of a previous row

От
Iaam Onkara
Дата:
@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?

On Nov 13, 2017 2:14 AM, "Achilleas Mantzios" <achill@matrix.gatewaynet.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

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@matrix.gatewaynet.com> wrote:
On 13/11/2017 09:47, Iaam Onkara wrote:
you will have to still specify 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.stoecker@stb-datenservice.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.stoecker@stb-datenservice.de> wrote:
Hi,

you can easily join the preceeding row, e.g.

select sub.id, sub.created_at, preceedingid, m2.* from (
select m.id, m.created_at, lag(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=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

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; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer 


-- 
Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
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

Re: [SQL] md5 checksum of a previous row

От
Achilleas Mantzios
Дата:
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@matrix.gatewaynet.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

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@matrix.gatewaynet.com> wrote:
On 13/11/2017 09:47, Iaam Onkara wrote:
you will have to still specify 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.stoecker@stb-datenservice.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.stoecker@stb-datenservice.de> wrote:
Hi,

you can easily join the preceeding row, e.g.

select sub.id, sub.created_at, preceedingid, m2.* from (
select m.id, m.created_at, lag(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=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

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; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer 


-- 
Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
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