From: | Kirk Wolak <wolakk(at)gmail(dot)com> |
---|---|
To: | Oliver Kohll <oliver(at)agilebase(dot)co(dot)uk> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Interconnected views |
Date: | 2023-06-04 03:40:56 |
Message-ID: | CACLU5mQNAePc6=zLLa74zP+bjMx=Y8HEyhBvVaSUNxaLawddjw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Jun 2, 2023 at 9:36 AM Oliver Kohll <oliver(at)agilebase(dot)co(dot)uk> wrote:
> Hi,
>
> Just wondering, does anyone else create apps which might not have 'big'
> data, but quite complex arrangements of views joining to each other?
>
> If so, do you have scripts to aid refactoring them e.g. drop/recreate/test
> them in the right order etc.?
>
> I'm really impressed with the way Postgres handles these multi-layered
> views. Some of our explain analyze outputs could fill a book each! Tools
> like Depesz' can be very useful. Sometimes a little tweaking or a judicious
> index is necessary, but the performance is very reliable and scalable.
>
> Blog post about it here:
> https://blog.agilebase.co.uk/2023/05/21/refactoring-sql-views/
>
> Oliver
>
Oliver,
I built a couple of such scripts to chase down dependency trees on
views. Based on a Cybertec (Laurenz Albe) article.
https://www.cybertec-postgresql.com/en/tracking-view-dependencies-in-postgresql/
Basically, I hunted down the dependent views (we have a rich hierarchy of
views). Not like yours, maybe 10-15 deep.
But it also impacts us on Column changes...
Anyways, I generate the DROP commands, inside of a single transaction, in
the right order.
Then I apply the CREATE VIEW ... in the reverse order of the drops.
Then I commit the transaction.
I was hours away from implementing something that grabbed the view
definitions, and dropped and recreated.
But that was only useful for the column type changes, and by the time I was
there, we were pretty much done changing them.
I find it a nice feature that I can drop views within a transaction, then
recreate them, in the same transaction.
So that nothing breaks. But we have not tested this under an
"realistically heavy load".
HTH
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Baldwin | 2023-06-04 05:10:11 | Re: Connection error to new pg15 instance |
Previous Message | Wen Yi | 2023-06-04 03:29:20 | Re: Can't send mail to `pgsql-zh-general@lists.postgresql.org` |