Working with database views against a gdb dataset that uses history tracking.

[JamesArmstrong_]

Not sure if I am posting in appropriate location.  Anyway, I have a postgres enterprise database set up.  The data base uses two schemas:

sde - which contains the geographic data - This data is in a branch version configuration with history and edit tracking activated.

amp - which contains attribute data.

I have been successful in the past in creating database views between these two schemas.  Currently, however, when a db view is created, the view includes multiple instances of features that have an edit history.  It seems that the View does not just use the most current state of a feature class but all past states as well.  For instance, in creating a DB View for Hydrants, the results are 3 instances of the same hydrant - as there are 3 edit instances (including the current state).  Any ideas or suggestions on how to create the DB view to only connect with the last edit.     

thanks in advance for responding.

[RobertKrisher · Accepted solution]

This is because every edit in branch versioning is tracked as an insert into the database. The good news is this means we can query every edit (including deletes!) in the branch versioned table using SQL. For a better understanding of how/why/when to use SQL on branch-versioned data, I recommend you read the following blog. Once you've read that you should then follow the link in the blog to the community page that describes how to write queries that will return the current state of your data in a safe and performant way.

[JamesArmstrong_]

Robert, thanks for the reply.  Though I am a novice at sql coding, I do understand the concepts of what you are saying.   Currently, I have approximately 13 municipality utility databases housed in Postgres using branch versioning. (Each municipality has its own database containing datasets and tables.) Each of these municipal databases have around 8 views each - so over 100.   Mainly, the view is created to provide a join between the geographic data and separate tabular attributes.  As you can see from the attached, I have been creating standard views. These views of course are not set up to filter down to most current state. (Ergo all states are created in the respective views)

Perhaps you could provide some additional insight based on the following questions:

Is the Materialized View created in lieu of a standard view or is it created to work with a standard view?
I sense that it is created as the filter of the Geographic data before being joined to the external tabular data through a standard view.

In the example you provide your post on Branch Version SQL Views, is the WITH t0_ AS (line 4) creating a temporary file?  Is this a required step or can it be by-passed if the selection is * 
Attached is a draft of the sql that I edited based on your posted example.  Comments have been added to point out further questions.

Any advice, suggestion, or insight you can share in regard to the attached is most appreciated.   - James

[RobertKrisher]

I apologize if the blog didn't properly convey the design and intention of this, I will attempt to address your questions here. The materialized view is meant to act as a stand-in for the standard view, this is not to provide additional filtering, but because of the following two reasons:

Evaluating the branch version query for all rows in your database is an expensive operation, so you don't want it evaluated for the entire database any time a user runs a report!

Because data is edited throughout the day, there may be dependencies between your branch version data and other system tables / processes. Materializing the view ensure that your views + reports are always consistent and are unaffected by the edits that occur throughout the day.

t0_ isn't a temporary file that is persisted anywhere, its a common table expression. While you could write a query that doesn't make use of a common table expression in Postgres, our testing has found that using a common table expression in Postgres allows for the planner to create more effective plans that execute in less time that the same queries that don't make use of common table expressions.

[JamesArmstrong_]

Robert, thanks for your response.  Believe me, your explanation and presentation are not an issue, rather it is my limited knowledge and understanding of Postgres SQL that seems to continually be the obstacle.

The several databases and feature classes with which I am working are rather static.  I set them up as Branch Versions for future considerations as well as being able to edit the data through the web services. In fact, at the moment, I am the only one who edits the data. 

'So, I am wondering if I am not making this more complicated than it needs to be.'

The bottom-line requirement is to have database views (geographic data joined to tabular data) for use in a web map, dashboard, etc.  At this time, only a small number of edits may be done to either of the data sets. When edits done to the geographic data, the view should only present the last edit of the geographic feature.

In your opinion, should I proceed in a different direction?  

Thanks for your time on this.  

James

[RobertKrisher]

The views or materialized views I have included in that blog are going to be your best way of capturing this information. The main consideration for the materialized views in THIS use case is going to be performance. The materialized views will be faster for reporting purposes. Period. If you don't want the overhead of creating materialized views AND are willing to let your reports take a little longer to generate, use regular views.

[JamesArmstrong_]

Thanks Robert.   I will keep working at it.  

[JamesArmstrong_]

Robert,  Well after stepping through the script, I was able to successfully apply it and create a Materialized View using your script, returning the latest state of the feature class.   Once the MV was created, I was NOT able to add the join (inner) to connect the tabular data directly in the MV script.  I was, however, able to use the new MV and create a standard view to create the join.  So, it is working...thanks for the help.

One other quick question.  The other script you posted that would update the MV.  I suspect that this script should be placed in the function folder of the database schema, and then use pg-agent to set up a schedule to run.   If otherwise, please advise.

thanks again for your patience.  -- James