Обсуждение: Creating complex track changes database - challenge!
Hi Guys,
i would like to ask you for help with track changes to my database.
I am new to PosgtreeSQL but i have to learn it quickly because of my boss.
I have to:
1. Keep all changes within table including:
-adding rows
-deleting
-editing
2. Save table with specific state and recover specific state (so go back to previous table versions) including comparing tables.
3. Track all DLL and DML changes with possibility to ho back to previous version.
Any tips will be welcome,
Best,
Jacek
On Mon, Feb 26, 2018 at 11:44 AM Łukasz Jarych <jaryszek@gmail.com> wrote:
I have to:1. Keep all changes within table including:-adding rows-deleting-editing2. Save table with specific state and recover specific state (so go back to previous table versions) including comparing tables.3. Track all DLL and DML changes with possibility to ho back to previous version.
Hi,
I had similar needs long ago, so I wrote this tool I called Squealer, which would transform a specification of a SQL database schema into some PostgreSQL DDL to create a database that implements the same schema in a logical sense, but actually stores all historical rows, and even permits existing rows to have references to (soft-)deleted rows, all while providing modifiable views that simulate the behavior of a regular table as specified in the input schema through generous use of INSTEAD OF triggers. It works somewhat like having version control for your database.
You may find the source code here: https://github.com/mgomezch/squealer Unfortunately, it has hardly any comments, it is completely unmaintained and probably unused anywhere, I have no idea whether it builds with today's libraries, and it does not necessarily break the tradeoffs in this space in a way that fits your use case.
Note there are major caveats with keeping all historical data around forever, and the semantics of querying historical data can get complicated, let alone having current data refer to deleted, historical data. I built this for a very specific use case where this was the right design, but please consider very carefully whether this is what you want.
Storing your database history forever would take a lot of space. Consider whether you can instead keep a record of changes stored outside the database in some cheap cold storage. Also consider just keeping a set of tables with dynamically structured event records (e.g. JSON fields) partitioned by time ranges and retained only temporarily, perhaps even in a separate database. Any such solution will have significant cost and performance impact if your database bears a nontrivial load, so be careful.
You could also just place your database on a PostgreSQL cluster by itself and then keep all WAL segments archived forever, so you could just do point-in-time recovery to any point in the history of your database. The space required would grow very quickly, though, so if you don't really need the full history forever, but only a fixed retention period, you can surely use any of the well-known solutions for PostgreSQL backups that allow for this through WAL archiving and periodic basebackups: e.g. WAL-E, WAL-G, pgBackRest…
If all you need is saving and restoring specific table states, logical dumps with pg_dump should probably be enough for your needs.
Łukasz Jarych schrieb am 26.02.2018 um 11:44: > i would like to ask you for help with track changes to my database. > I am new to PosgtreeSQL but i have to learn it quickly because of my boss. > > I have to: > > 1. Keep all changes within table including: > -adding rows > -deleting > -editing > > 2. Save table with specific state and recover specific state (so go back to previous table versions) including comparingtables. There are several generic auditing triggers that can do that: * http://cjauvin.blogspot.de/2013/05/impossibly-lean-audit-system-for.html * https://eager.io/blog/audit-postgres/ * http://okbob.blogspot.de/2015/01/most-simply-implementation-of-history.html * http://8kb.co.uk/blog/2015/01/19/copying-pavel-stehules-simple-history-table-but-with-the-jsonb-type/ * https://www.garysieling.com/blog/auditing-data-changes-postgres * https://github.com/wingspan/wingspan-auditing * https://wiki.postgresql.org/wiki/Audit_trigger_91plus > 3. Track all DLL and DML changes with possibility to ho back to previous version. That will be very tricky, especially the "go back to previous version" part. But in general DDL changes can be tracked using event triggers. Thomas
Hi Guys,
I have idea already for creating this complex solution.
Please give your notes and tips if you have.
1. Keep all changes within table including:
-adding rows
-deleting
-editing
This can be managed by adding triggers and one additional table where you can have sum up what was changed.
2. Changing DDL of tables:
I think that creating trigger for metadata should solve the problem. How can i do it? I do not know already ...:)
3. Changing tables versioning.
It it is possible to save table (back up or something) to disc - i can check the latest date of change and save table with this date and name.
And create table with all tables changes and version.
And create table with all tables changes and version.
What do you think ?
4. Still problem with creating whole database versioning.
I found very interesting link but i not understand how it is works:
Best,
Jacek
2018-02-26 12:16 GMT+01:00 Łukasz Jarych <jaryszek@gmail.com>:
Hi Manual,thank you very much!Regarding your tool - if it is not supported and it is for specific case - i will not use it but figure out something new. I do not even how to install this .hs files...I thought about creating triggers to have all changes to specific tables.And for each table name (or number) keep changes in one separate table.What do you think about it?If all you need is saving and restoring specific table states, logical dumps with pg_dump should probably be enough for your needs.Can you explain in details how can i use it?What if user add new column? I can save ma table for example as version 3 and come back to version 1 in the future? (without this new column?)Best,Jacek2018-02-26 12:04 GMT+01:00 Manuel Gómez <targen@gmail.com>:On Mon, Feb 26, 2018 at 11:44 AM Łukasz Jarych <jaryszek@gmail.com> wrote:I have to:1. Keep all changes within table including:-adding rows-deleting-editing2. Save table with specific state and recover specific state (so go back to previous table versions) including comparing tables.3. Track all DLL and DML changes with possibility to ho back to previous version.Hi,I had similar needs long ago, so I wrote this tool I called Squealer, which would transform a specification of a SQL database schema into some PostgreSQL DDL to create a database that implements the same schema in a logical sense, but actually stores all historical rows, and even permits existing rows to have references to (soft-)deleted rows, all while providing modifiable views that simulate the behavior of a regular table as specified in the input schema through generous use of INSTEAD OF triggers. It works somewhat like having version control for your database.You may find the source code here: https://github.com/mgomezch/squealer Unfortunately, it has hardly any comments, it is completely unmaintained and probably unused anywhere, I have no idea whether it builds with today's libraries, and it does not necessarily break the tradeoffs in this space in a way that fits your use case. Note there are major caveats with keeping all historical data around forever, and the semantics of querying historical data can get complicated, let alone having current data refer to deleted, historical data. I built this for a very specific use case where this was the right design, but please consider very carefully whether this is what you want.Storing your database history forever would take a lot of space. Consider whether you can instead keep a record of changes stored outside the database in some cheap cold storage. Also consider just keeping a set of tables with dynamically structured event records (e.g. JSON fields) partitioned by time ranges and retained only temporarily, perhaps even in a separate database. Any such solution will have significant cost and performance impact if your database bears a nontrivial load, so be careful.You could also just place your database on a PostgreSQL cluster by itself and then keep all WAL segments archived forever, so you could just do point-in-time recovery to any point in the history of your database. The space required would grow very quickly, though, so if you don't really need the full history forever, but only a fixed retention period, you can surely use any of the well-known solutions for PostgreSQL backups that allow for this through WAL archiving and periodic basebackups: e.g. WAL-E, WAL-G, pgBackRest…If all you need is saving and restoring specific table states, logical dumps with pg_dump should probably be enough for your needs.
There’s https://flywaydb.org/
Also, if you know PHP, Laravel database migrations have worked great for us!
On Feb 26, 2018, at 3:44 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:i would like to ask you for help with track changes to my database.I am new to PosgtreeSQL but i have to learn it quickly because of my boss.I have to:1. Keep all changes within table including:-adding rows-deleting-editing2. Save table with specific state and recover specific state (so go back to previous table versions) including comparing tables.3. Track all DLL and DML changes with possibility to ho back to previous version.
I've got a manual method (though it's probably wise to go with a vendor product), that I will just dump here.
It tracks all configured tables into a single table containing before/after record images in jsonb.
create table aud_audit
(
id serial8,
timestamp timestamptz default now() NOT NULL,
app_user_id int8 NOT NULL,
operation varchar(8) NOT NULL,
table_name varchar(100) NOT NULL,
before_image jsonb,
after_image jsonb,
----
constraint aud_audit_pk primary key(id)
)
;
create or replace function audit_all() returns trigger as
$$
declare
t_before jsonb := NULL;
t_after jsonb := NULL;
t_user_id int8;
begin
begin
t_user_id := current_setting('app.user_id')::int8;
exception
when OTHERS then
t_user_id := -1;
end;
case tg_op
when 'INSERT' then
t_after := row_to_json(new.*);
when 'UPDATE' then
t_before := row_to_json(old.*);
t_after := row_to_json(new.*);
when 'DELETE' then
t_before := row_to_json(old.*);
when 'TRUNCATE' then
t_before := row_to_json(old.*);
end case;
insert into aud_audit
(
app_user_id,
operation,
table_name,
before_image,
after_image
)
values(
t_user_id,
tg_op,
tg_table_name,
t_before,
t_after
);
return
case tg_op
when 'INSERT' then new
when 'UPDATE' then new
when 'DELETE' then old
when 'TRUNCATE' then old
end;
end;
$$
language plpgsql
;
create trigger <table_name>_audit_t01
before insert or update or delete
on <table_name>
for each row execute procedure audit_all()
;
On Mon, Feb 26, 2018 at 7:43 AM, geoff hoffman <geoff@rxmg.com> wrote:
There’s https://flywaydb.org/Also, if you know PHP, Laravel database migrations have worked great for us!On Feb 26, 2018, at 3:44 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:i would like to ask you for help with track changes to my database.I am new to PosgtreeSQL but i have to learn it quickly because of my boss.I have to:1. Keep all changes within table including:-adding rows-deleting-editing2. Save table with specific state and recover specific state (so go back to previous table versions) including comparing tables.3. Track all DLL and DML changes with possibility to ho back to previous version.
Thank you geoff!
I think that i will test http://www.liquibase.org/ this one.
what about setting up trigger to metadata (structural table) to find if column was added for example?
Best,
Jacek
2018-02-26 16:43 GMT+01:00 geoff hoffman <geoff@rxmg.com>:
There’s https://flywaydb.org/Also, if you know PHP, Laravel database migrations have worked great for us!On Feb 26, 2018, at 3:44 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:i would like to ask you for help with track changes to my database.I am new to PosgtreeSQL but i have to learn it quickly because of my boss.I have to:1. Keep all changes within table including:-adding rows-deleting-editing2. Save table with specific state and recover specific state (so go back to previous table versions) including comparing tables.3. Track all DLL and DML changes with possibility to ho back to previous version.
I would personally do that separately: write a bash script & cron job that does a schema dump every hour, and (if there are any changes) commits any changes to your schema repository; then you can use Github or Bitbucket web hooks to do stuff with the changeset when it’s pushed.
On Feb 26, 2018, at 9:36 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:Thank you geoff!I think that i will test http://www.liquibase.org/ this one.what about setting up trigger to metadata (structural table) to find if column was added for example?Best,Jacek2018-02-26 16:43 GMT+01:00 geoff hoffman <geoff@rxmg.com>:There’s https://flywaydb.org/Also, if you know PHP, Laravel database migrations have worked great for us!On Feb 26, 2018, at 3:44 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:i would like to ask you for help with track changes to my database.I am new to PosgtreeSQL but i have to learn it quickly because of my boss.I have to:1. Keep all changes within table including:-adding rows-deleting-editing2. Save table with specific state and recover specific state (so go back to previous table versions) including comparing tables.3. Track all DLL and DML changes with possibility to ho back to previous version.
Thank you goeff.
I need solution like this:
Administrator push button or something like and adding comment (for bitbucket) that after creating update to database.
Now whole database is exported to *.sql file, and commit with text provided by Admin.
Can i connect using bash script to database and take variable inputed by Admin?
Best,
Jacek
2018-02-26 21:45 GMT+01:00 geoff hoffman <geoff@rxmg.com>:
I would personally do that separately: write a bash script & cron job that does a schema dump every hour, and (if there are any changes) commits any changes to your schema repository; then you can use Github or Bitbucket web hooks to do stuff with the changeset when it’s pushed.On Feb 26, 2018, at 9:36 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:Thank you geoff!I think that i will test http://www.liquibase.org/ this one.what about setting up trigger to metadata (structural table) to find if column was added for example?Best,Jacek2018-02-26 16:43 GMT+01:00 geoff hoffman <geoff@rxmg.com>:There’s https://flywaydb.org/Also, if you know PHP, Laravel database migrations have worked great for us!On Feb 26, 2018, at 3:44 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:i would like to ask you for help with track changes to my database.I am new to PosgtreeSQL but i have to learn it quickly because of my boss.I have to:1. Keep all changes within table including:-adding rows-deleting-editing2. Save table with specific state and recover specific state (so go back to previous table versions) including comparing tables.3. Track all DLL and DML changes with possibility to ho back to previous version.
Hi Thiemo,
you can share the repository, maybe when i will go more into PostgreSQL i would help you.
Regarding table versionig.
I am thinking about simple solution:
1. Create query or trigger which will be checking last date of inputed data within Table.
2. Export the table into seperate file/back up/structure using Date_TableName.
3. Have a table where i would complete all history together in one place.
Regarding Database Versioning
Write script which will be exporting whole database into *.sql file when administrator wants. And adding variable with description, for example "Added new table to database".
Next commit this file into bitbucket automatically using bash script and compare results via bitbucket.
What do you think ?
Best,
Jacek
2018-02-27 8:11 GMT+01:00 Thiemo Kellner <thiemo@gelassene-pferde.biz>:
Hi Lukasz
I am working on a generic (reading the information schema and other database metadata), trigger based solution for SCD tables, i. e. tables that keep (or not according to SCD type) history of the data. However, it is not far grown and I am not having much time to advance it so it evolves very slowly. If you are interested, I would open a sourceforge project or the like and we can work on it together.
I am very much surprised that no database I know of supports SCD/historising tables out of the box. In 16 years as ETL pro I have seen reinvented the wheel all the time... maybe PostgreSQL wants to get a head start on this.
Kind regards
Thiemo
Zitat von ?ukasz Jarych <jaryszek@gmail.com>:Hi Guys,
I have idea already for creating this complex solution.
Please give your notes and tips if you have.
1. Keep all changes within table including:
-adding rows
-deleting
-editing
This can be managed by adding triggers and one additional table where you
can have sum up what was changed.
2. Changing DDL of tables:
I think that creating trigger for metadata should solve the problem. How
can i do it? I do not know already ...:)
3. Changing tables versioning.
It it is possible to save table (back up or something) to disc - i can
check the latest date of change and save table with this date and name.
And create table with all tables changes and version.
What do you think ?
4. Still problem with creating whole database versioning.
I found very interesting link but i not understand how it is works:
https://odetocode.com/blogs/scott/archive/2008/02/02/version ing-databases-change-scripts. aspx
Best,
Jacek
2018-02-26 12:16 GMT+01:00 ?ukasz Jarych <jaryszek@gmail.com>:Hi Manual,
thank you very much!
Regarding your tool - if it is not supported and it is for specific case
- i will not use it but figure out something new. I do not even how to
install this .hs files...
I thought about creating triggers to have all changes to specific tables.
And for each table name (or number) keep changes in one separate table.
What do you think about it?
If all you need is saving and restoring specific table states, logicaldumps with pg_dump should probably be enough for your needs.
Can you explain in details how can i use it?
What if user add new column? I can save ma table for example as version 3
and come back to version 1 in the future? (without this new column?)
Best,
Jacek
2018-02-26 12:04 GMT+01:00 Manuel Gómez <targen@gmail.com>:On Mon, Feb 26, 2018 at 11:44 AM ?ukasz Jarych <jaryszek@gmail.com>pgBackRest?
wrote:I have to:
1. Keep all changes within table including:
-adding rows
-deleting
-editing
2. Save table with specific state and recover specific state (so go back
to previous table versions) including comparing tables.
3. Track all DLL and DML changes with possibility to ho back to previous
version.
Hi,
I had similar needs long ago, so I wrote this tool I called Squealer,
which would transform a specification of a SQL database schema into some
PostgreSQL DDL to create a database that implements the same schema in a
logical sense, but actually stores all historical rows, and even permits
existing rows to have references to (soft-)deleted rows, all while
providing modifiable views that simulate the behavior of a regular table as
specified in the input schema through generous use of INSTEAD OF triggers.
It works somewhat like having version control for your database.
You may find the source code here: https://github.com/mgomezch/squealer
Unfortunately, it has hardly any comments, it is completely unmaintained
and probably unused anywhere, I have no idea whether it builds with today's
libraries, and it does not necessarily break the tradeoffs in this space in
a way that fits your use case.
Note there are major caveats with keeping all historical data around
forever, and the semantics of querying historical data can get complicated,
let alone having current data refer to deleted, historical data. I built
this for a very specific use case where this was the right design, but
please consider very carefully whether this is what you want.
Storing your database history forever would take a lot of space.
Consider whether you can instead keep a record of changes stored outside
the database in some cheap cold storage. Also consider just keeping a set
of tables with dynamically structured event records (e.g. JSON fields)
partitioned by time ranges and retained only temporarily, perhaps even in a
separate database. Any such solution will have significant cost and
performance impact if your database bears a nontrivial load, so be careful.
You could also just place your database on a PostgreSQL cluster by itself
and then keep all WAL segments archived forever, so you could just do
point-in-time recovery to any point in the history of your database. The
space required would grow very quickly, though, so if you don't really need
the full history forever, but only a fixed retention period, you can surely
use any of the well-known solutions for PostgreSQL backups that allow for
this through WAL archiving and periodic basebackups: e.g. WAL-E, WAL-G,
If all you need is saving and restoring specific table states, logical
dumps with pg_dump should probably be enough for your needs.
--
+49 (0)1578-772 37 37
+41 (0)78 947 36 21
Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CB EF
------------------------------------------------------------ ----
This message was sent using IMP, the Internet Messaging Program.
Hi Lukasz I am working on a generic (reading the information schema and other database metadata), trigger based solution for SCD tables, i. e. tables that keep (or not according to SCD type) history of the data. However, it is not far grown and I am not having much time to advance it so it evolves very slowly. If you are interested, I would open a sourceforge project or the like and we can work on it together. I am very much surprised that no database I know of supports SCD/historising tables out of the box. In 16 years as ETL pro I have seen reinvented the wheel all the time... maybe PostgreSQL wants to get a head start on this. Kind regards Thiemo Zitat von ?ukasz Jarych <jaryszek@gmail.com>: > Hi Guys, > > I have idea already for creating this complex solution. > > Please give your notes and tips if you have. > > 1. Keep all changes within table including: > -adding rows > -deleting > -editing > > This can be managed by adding triggers and one additional table where you > can have sum up what was changed. > > 2. Changing DDL of tables: > > I think that creating trigger for metadata should solve the problem. How > can i do it? I do not know already ...:) > > 3. Changing tables versioning. > > It it is possible to save table (back up or something) to disc - i can > check the latest date of change and save table with this date and name. > And create table with all tables changes and version. > What do you think ? > > 4. Still problem with creating whole database versioning. > I found very interesting link but i not understand how it is works: > > https://odetocode.com/blogs/scott/archive/2008/02/02/versioning-databases-change-scripts.aspx > > Best, > Jacek > > 2018-02-26 12:16 GMT+01:00 ?ukasz Jarych <jaryszek@gmail.com>: > >> Hi Manual, >> thank you very much! >> >> Regarding your tool - if it is not supported and it is for specific case >> - i will not use it but figure out something new. I do not even how to >> install this .hs files... >> >> I thought about creating triggers to have all changes to specific tables. >> And for each table name (or number) keep changes in one separate table. >> What do you think about it? >> >> If all you need is saving and restoring specific table states, logical >>> dumps with pg_dump should probably be enough for your needs. >> >> >> Can you explain in details how can i use it? >> What if user add new column? I can save ma table for example as version 3 >> and come back to version 1 in the future? (without this new column?) >> >> Best, >> Jacek >> >> >> >> 2018-02-26 12:04 GMT+01:00 Manuel Gómez <targen@gmail.com>: >> >>> On Mon, Feb 26, 2018 at 11:44 AM ?ukasz Jarych <jaryszek@gmail.com> >>> wrote: >>> >>>> I have to: >>>> >>>> 1. Keep all changes within table including: >>>> -adding rows >>>> -deleting >>>> -editing >>>> >>>> 2. Save table with specific state and recover specific state (so go back >>>> to previous table versions) including comparing tables. >>>> >>>> 3. Track all DLL and DML changes with possibility to ho back to previous >>>> version. >>>> >>> >>> Hi, >>> >>> I had similar needs long ago, so I wrote this tool I called Squealer, >>> which would transform a specification of a SQL database schema into some >>> PostgreSQL DDL to create a database that implements the same schema in a >>> logical sense, but actually stores all historical rows, and even permits >>> existing rows to have references to (soft-)deleted rows, all while >>> providing modifiable views that simulate the behavior of a regular table as >>> specified in the input schema through generous use of INSTEAD OF triggers. >>> It works somewhat like having version control for your database. >>> >>> You may find the source code here: https://github.com/mgomezch/squealer >>> Unfortunately, it has hardly any comments, it is completely unmaintained >>> and probably unused anywhere, I have no idea whether it builds with today's >>> libraries, and it does not necessarily break the tradeoffs in this space in >>> a way that fits your use case. >>> >>> Note there are major caveats with keeping all historical data around >>> forever, and the semantics of querying historical data can get complicated, >>> let alone having current data refer to deleted, historical data. I built >>> this for a very specific use case where this was the right design, but >>> please consider very carefully whether this is what you want. >>> >>> Storing your database history forever would take a lot of space. >>> Consider whether you can instead keep a record of changes stored outside >>> the database in some cheap cold storage. Also consider just keeping a set >>> of tables with dynamically structured event records (e.g. JSON fields) >>> partitioned by time ranges and retained only temporarily, perhaps even in a >>> separate database. Any such solution will have significant cost and >>> performance impact if your database bears a nontrivial load, so be careful. >>> >>> You could also just place your database on a PostgreSQL cluster by itself >>> and then keep all WAL segments archived forever, so you could just do >>> point-in-time recovery to any point in the history of your database. The >>> space required would grow very quickly, though, so if you don't really need >>> the full history forever, but only a fixed retention period, you can surely >>> use any of the well-known solutions for PostgreSQL backups that allow for >>> this through WAL archiving and periodic basebackups: e.g. WAL-E, WAL-G, >>> pgBackRest? >>> >>> If all you need is saving and restoring specific table states, logical >>> dumps with pg_dump should probably be enough for your needs. >>> >> >> > -- +49 (0)1578-772 37 37 +41 (0)78 947 36 21 Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.
I attached what I have got so far. I will setup a shared repository these days. Zitat von Thiemo Kellner <thiemo@gelassene-pferde.biz>: > Hi Lukasz > > I am working on a generic (reading the information schema and other > database metadata), trigger based solution for SCD tables, i. e. > tables that keep (or not according to SCD type) history of the data. > However, it is not far grown and I am not having much time to > advance it so it evolves very slowly. If you are interested, I would > open a sourceforge project or the like and we can work on it together. > > I am very much surprised that no database I know of supports > SCD/historising tables out of the box. In 16 years as ETL pro I have > seen reinvented the wheel all the time... maybe PostgreSQL wants to > get a head start on this. > > Kind regards > > Thiemo -- +49 (0)1578-772 37 37 +41 (0)78 947 36 21 Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0x8F70EFD2D972CBEF ---------------------------------------------------------------- This message was sent using IMP, the Internet Messaging Program.
Вложения
Hi Jacek, On Mon, 26 Feb 2018 11:44:13 +0100 Łukasz Jarych <jaryszek@gmail.com> wrote: > Hi Guys, > > i would like to ask you for help with track changes to my database. > I am new to PosgtreeSQL but i have to learn it quickly because of my boss. > > I have to: > > 1. Keep all changes within table including: > -adding rows > -deleting > -editing > > 2. Save table with specific state and recover specific state (so go back to > previous table versions) including comparing tables. depending on your requirements and implementation of these, you might want to have a look at "Developing Time-Oriented Database Applications in SQL" (https://www2.cs.arizona.edu/people/rts/tdbbook.pdf). It is quite thick with 530 pages, but it helped me wrap around my head on a similar requirement. It describes the challenges with tracking and managing these kinds of changes as well as solutions together with SQL examples. Cheers, Christian -- Dr.-Ing. Christian Keil (Principal Researcher) Phone:+49 40 808077-648 Fax:+49 40 808077-556 Mail: keil@dfn-cert.de DFN-CERT Services GmbH, https://www.dfn-cert.de/, Fax: +49 40 808077-556 Sitz / Register: Hamburg, AG Hamburg, HRB 88805, Ust-IdNr.: DE 232129737 Sachsenstrasse 5, 20097 Hamburg, Germany. CEO: Dr. Klaus-Peter Kossakowski 25. DFN-Konferenz "Sicherheit in vernetzten Systemen" am 27./28. Februar 2018 im Grand Hotel Elysee Hamburg 7. DFN-Konferenz "Datenschutz" am 20./21. November 2018 im Grand Hotel Elysee Hamburg
Вложения
You can access code with git clone ssh://<your sourceforge user>@git.code.sf.net/p/pg-scd/code pg-scd-code and browse it at https://sourceforge.net/p/pg-scd/code/ On 02/27/18 08:43, Thiemo Kellner wrote: > I attached what I have got so far. I will setup a shared repository > these days. > > Zitat von Thiemo Kellner <thiemo@gelassene-pferde.biz>: > >> Hi Lukasz >> >> I am working on a generic (reading the information schema and other >> database metadata), trigger based solution for SCD tables, i. e. >> tables that keep (or not according to SCD type) history of the data. >> However, it is not far grown and I am not having much time to advance >> it so it evolves very slowly. If you are interested, I would open a >> sourceforge project or the like and we can work on it together. >> >> I am very much surprised that no database I know of supports >> SCD/historising tables out of the box. In 16 years as ETL pro I have >> seen reinvented the wheel all the time... maybe PostgreSQL wants to >> get a head start on this. >> >> Kind regards >> >> Thiemo > -- +49 (0)1578-772 37 37 +41 (0)78 947 36 21 SIP/iptel.org: thiemo.kellner Öffentlicher PGP-Schlüssel: http://pgp.mit.edu/pks/lookup?op=get&search=0xCA167FB0E717AFFC