pgpoke.py

#!/usr/bin/env python
#
# Usage: ./pgpoke.py <dsn>
#
import psycopg2
from sys import argv
from time import sleep
from datetime import datetime
import logging

logging.basicConfig(format='%(asctime)s %(message)s')

"""
CREATE TABLE poke_table (
    id serial PRIMARY KEY,
    seqno integer NOT NULL, 
    tstamp TIMESTAMP NOT NULL)
"""
    
def main():
    seqno = 0
    while True:
        try:
            conn = psycopg2.connect(argv[1], connect_timeout=5)
            while True:
                tstamp = datetime.utcnow()
                cur = conn.cursor()
                cur.execute("SET statement_timeout = 5")
                cur.execute("SELECT seqno, tstamp FROM poke_table ORDER BY id DESC LIMIT 1")
                rows = cur.fetchall()
                if len(rows) == 0:
                    logging.error("No rows returned")
                else:
                    prev_seqno, prev_tstamp = rows[0]
                    if seqno - prev_seqno != 1:
                        logging.error(f"Sequence number mismatch - current: {seqno}, previous: {prev_seqno}")
                logging.error(f"    Trying to insert sequence number {seqno}")
                #cur.execute("set synchronous_commit = off")
                cur.execute("INSERT INTO poke_table(seqno, tstamp) VALUES (%(seqno)s, %(tstamp)s)", {
                    'seqno': seqno,
                    'tstamp': tstamp
                })
                conn.commit()
                logging.error(f"Sucessfully inserted sequence number {seqno}")
                seqno += 1
        except Exception as exc:
            logging.error(f"Exception occured: {exc}")
            #sleep(1)
        

if __name__ == '__main__':
    main()