Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eE9cO-0005RC-FX for pgsql-sql@arkaria.postgresql.org; Mon, 13 Nov 2017 07:57:20 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1eE9cN-0005sO-Ll for pgsql-sql@arkaria.postgresql.org; Mon, 13 Nov 2017 07:57:19 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eE9cL-0005j9-02 for pgsql-sql@postgresql.org; Mon, 13 Nov 2017 07:57:17 +0000 Received: from host3.dynacom.ondsl.gr ([62.103.35.211] helo=smadev.internal.net) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eE9cD-0007MN-8p for pgsql-sql@postgresql.org; Mon, 13 Nov 2017 07:57:15 +0000 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id vAD7v620022743 for ; Mon, 13 Nov 2017 09:57:06 +0200 (EET) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: md5 checksum of a previous row To: pgsql-sql@postgresql.org References: <95a944da-e4f2-2500-b917-6aa825a28f03@stb-datenservice.de> <01d43d07-5ed9-f74c-ae19-8b2f58953f6b@stb-datenservice.de> From: Achilleas Mantzios Message-ID: <05872e2f-7ae9-d90c-6a3a-26eb2953dc30@matrix.gatewaynet.com> Date: Mon, 13 Nov 2017 09:57:06 +0200 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101 Thunderbird/52.3.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------CE3A05FB1D34BF2944D49216" Content-Language: en-US List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------CE3A05FB1D34BF2944D49216 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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) > 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) > 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 --------------CE3A05FB1D34BF2944D49216 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
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
--------------CE3A05FB1D34BF2944D49216--