pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Creating a materialized view causing blocks
2+ messages / 2 participants
[nested] [flat]

* Creating a materialized view causing blocks
@ 2020-12-30 21:38  John Scalia <jayknowsunix@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: John Scalia @ 2020-12-30 21:38 UTC (permalink / raw)
  To: pgsql-admin

Over last evening, one of my colleagues had begun recreating a materialized view. From looking at his code, it was only doing some selects, like one would expect, however, several API clients ended up being blocked by this, and I’m attempting to understand why. I should note that a couple of the tables used here, contained more than 100 million rows, and I’m wondering if the system simply gave up trying to keep the view’s data consistency on par, and if a more than a non-exclusive read block was then imposed. The API’s involved basically kept getting respawned, and at one point there were more than 4000 attempted simultaneous connections. Not a lot of fun, but fortunately for me, the application team only called the other DBA to fix the issue. I’d still like to know what happened.
—
Jay

Sent from my iPad




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Creating a materialized view causing blocks
@ 2020-12-31 17:30  MichaelDBA <MichaelDBA@sqlexec.com>
  parent: John Scalia <jayknowsunix@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: MichaelDBA @ 2020-12-31 17:30 UTC (permalink / raw)
  To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin

Hi John,

Not really following you here...  You said:

"...the application team only called the other DBA to fix the issue. I’d still like to know what happened."

Unless I'm missing something here, why don't you ask this "other DBA" what he/she did to "fix" the problem?

Also, not understanding this statement from you:
"...if the system simply gave up trying to keep the view’s data consistency on par..."

It almost seems as if you think the MV keeps itself updated automatically.  That is not how MVs work at the present time in the PG world.  You have to manually refresh them.

Look forward to your response.

Regards,
Michael Vitale


John Scalia wrote on 12/30/2020 4:38 PM:
> Over last evening, one of my colleagues had begun recreating a materialized view. From looking at his code, it was only doing some selects, like one would expect, however, several API clients ended up being blocked by this, and I’m attempting to understand why. I should note that a couple of the tables used here, contained more than 100 million rows, and I’m wondering if the system simply gave up trying to keep the view’s data consistency on par, and if a more than a non-exclusive read block was then imposed. The API’s involved basically kept getting respawned, and at one point there were more than 4000 attempted simultaneous connections. Not a lot of fun, but fortunately for me, the application team only called the other DBA to fix the issue. I’d still like to know what happened.
> —
> Jay
>
> Sent from my iPad
>






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2020-12-31 17:30 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-30 21:38 Creating a materialized view causing blocks John Scalia <jayknowsunix@gmail.com>
2020-12-31 17:30 ` MichaelDBA <MichaelDBA@sqlexec.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox