CODEWITHSUNDEEP
pythonpsycopg2
Damian Russak
Damian Russak

Reputation: 478

How to execute multiple independent statements at the same time in Python/psycopg2?

I have three materialized views in PostgreSQL that take very long time to refresh (more than a few hours each) and I need to refresh them every day.

I'm currently doing this using a Python script that refreshes the views one after the other but it takes thrice the amount of time compared to manually refreshing them in pgAdmin (where I can run all three refreshes simultaneously in different tabs).

This is how my code looks like right now:

import psycopg2
config = {'connection details'}
conn = psycopg2.connect(**config)
cur = conn.cursor()

# This is the part that I want to run simultaneously
cur.execute('REFRESH MATERIALIZED VIEW gsam.mv_hist_wip_data')
cur.execute('REFRESH MATERIALIZED VIEW gsam.mv_hist_ver_data')
cur.execute('REFRESH MATERIALIZED VIEW gsam.mv_hist_verda_data')

conn.close()

How can I execute three REFRESH MATERIALIZED VIEW statements at the same time using Python and psycopg2?

Upvotes: 2

Views: 4647

Answers (1)

Amit Nanaware
Amit Nanaware

Reputation: 3356

you can use multiprocessing pool please check the document here. And check the below example

import psycopg2
from multiprocessing import Pool

def main():
    p = Pool(processes=3)
    view_names = ['mv_hist_wip_data','mv_hist_ver_data', 'mv_hist_verda_data']
    result = p.map(refresh_view, view_names)

def  refresh_view(view_name):

    config = {'connection details'}
    conn = psycopg2.connect(**config)
    cur = conn.cursor()
    # This is the part that I want to run simultaneously
    cur.execute('REFRESH MATERIALIZED VIEW gsam.%s', (view_name,))
    conn.close()

Upvotes: 6

Related Questions