Re: Time consistency for DDL Available?

[mp...@pivotal.io]

I don't believe there is any way to do this.  If you search for "postgresql 
track ddl changes", you'll likely conclude that there is no simple way to 
do this in postgresql.  There may be a way to do this but I think it will 
require some non trivial amount of hand coding.    If others know 
otherwise, please let us know.  MEP

On Tuesday, September 4, 2018 at 5:22:02 PM UTC-4, Daniel Barale wrote:
>
> Hello all,
>
> Type 2 slowly changing dimensions allow the creation of time consistent 
> data views. When used properly, you can view the data “as it was” at a 
> point in time. So if a query myself today, using a 3 years ago time 
> criteria, I’d be able to see my address then, not where I live now, in case 
> I moved during that period.
>
> My question is, can we do the same with DDL metadata? I mean, is there a 
> system that allows having a table structure at a point in time, and having 
> the same table with a different structure at another point in time? Like 
> for instance, a table T had 3 integer columns in 1/1/2013 and in 12/12/2015 
> have the same 3 integer columns but also a date column added?
>
> Do u get my drift? Does Pivotal have a “tool” or feature to provide that?
>
> Ward regards
>
> Daniel Barale,
>
> Social Security Administration (USA)
>
> Daniel.e.barale@ssa.gov
>

Re: [gpdb-users] Re: Time consistency for DDL Available?

[Andreas Scherbaum <as...@pivotal.io>]

In recent PostgreSQL versions, WAL contains enough information (if
"wal_level" is set to "logical") to decode the raw data back into queries.
That feature is called "Logical Decoding".

https://www.postgresql.org/docs/current/static/logicaldecoding-explanation.html
https://wiki.postgresql.org/wiki/Logical_Decoding_Plugins

There are several plugins available which can decode the streamed data back
into different formats. One of them is "decoder_raw" which generates SQL
queries which can be consumed by another PostgreSQL database.


This allows for filter out DDL changes, and ignore DML.


Regards,
Andreas


On Wed, Sep 5, 2018 at 2:16 PM, <mp...@pivotal.io> wrote:

> I don't believe there is any way to do this.  If you search for
> "postgresql track ddl changes", you'll likely conclude that there is no
> simple way to do this in postgresql.  There may be a way to do this but I
> think it will require some non trivial amount of hand coding.    If others
> know otherwise, please let us know.  MEP
>
> On Tuesday, September 4, 2018 at 5:22:02 PM UTC-4, Daniel Barale wrote:
>>
>> Hello all,
>>
>> Type 2 slowly changing dimensions allow the creation of time consistent
>> data views. When used properly, you can view the data “as it was” at a
>> point in time. So if a query myself today, using a 3 years ago time
>> criteria, I’d be able to see my address then, not where I live now, in case
>> I moved during that period.
>>
>> My question is, can we do the same with DDL metadata? I mean, is there a
>> system that allows having a table structure at a point in time, and having
>> the same table with a different structure at another point in time? Like
>> for instance, a table T had 3 integer columns in 1/1/2013 and in 12/12/2015
>> have the same 3 integer columns but also a date column added?
>>
>> Do u get my drift? Does Pivotal have a “tool” or feature to provide that?
>>
>> Ward regards
>>
>> Daniel Barale,
>>
>> Social Security Administration (USA)
>>
>> Daniel.e.barale@ssa.gov
>>
> --
> You received this message because you are subscribed to the Google Groups
> "Greenplum Users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to gpdb-users+unsubscribe@greenplum.org.
> To post to this group, send email to gpdb-users@greenplum.org.
> Visit this group at https://groups.google.com/a/greenplum.org/group/gpdb-
> users/.
> For more options, visit https://groups.google.com/a/greenplum.org/d/optout
> .
>



-- 

*Andreas Scherbaum*

*Principal Software Engineer*

GoPivotal Deutschland GmbH


<http://www.gopivotal.com/>Hauptverwaltung und Sitz
<http://www.gopivotal.com/>: Am Kronberger Hang 2a, 65824 Schwalbach/Ts.,
Deutschland

Amtsgericht Königstein im Taunus, HRB 8433
Geschäftsführer: Andrew Michael Cohen, Paul Thomas Dacier