Python psycopg2.InternalError() Examples

def _retry_on_exceptions(exc):
    if not isinstance(exc, exception.DBError):
        return False
    inn_e = exc.inner_exception
    if not isinstance(inn_e, sqlalchemy.exc.InternalError):
        return False
    return ((
        pymysql and
        isinstance(inn_e.orig, pymysql.err.InternalError) and
        (inn_e.orig.args[0] == pymysql.constants.ER.TABLE_DEF_CHANGED)
    ) or (
        # HACK(jd) Sometimes, PostgreSQL raises an error such as "current
        # transaction is aborted, commands ignored until end of transaction
        # block" on its own catalog, so we need to retry, but this is not
        # caught by oslo.db as a deadlock. This is likely because when we use
        # Base.metadata.create_all(), sqlalchemy itself gets an error it does
        # not catch or something. So this is why this function exists. To
        # paperover I guess.
        psycopg2
        and isinstance(inn_e.orig, psycopg2.InternalError)
        # current transaction is aborted
        and inn_e.orig.pgcode == '25P02'
    )) 

def delta_compress_batch(self, batched):

		for count, url in batched:
			print("Count, url: %s, %s" % (count, url))
			# with db.session_context(override_timeout_ms=1000*60*30) as temp_sess:
			# 	while 1:
			# 		try:
			# 			self.truncate_url_history(temp_sess, url)
			# 			break
			# 		except psycopg2.InternalError:
			# 			temp_sess.rollback()
			# 		except sqlalchemy.exc.OperationalError:
			# 			temp_sess.rollback()
			# 		except Exception:
			# 			temp_sess.rollback()
			# 			traceback.print_exc() 

def test_delete_restrict(self):

        # delete is restrict, must fail
        try:
            self.cur.execute(
                "DELETE FROM {}.referenced_view "
                "WHERE id1 = 1 and id2 = 18".format(
                    self.schema))
            assert(False and "Delete must fail because of referenced key")
        except (IntegrityError, psycopg2.InternalError):
            self.con.rollback()
        else:
            self.con.commit()

        # Two existing feature, delete has failed, so 2 revisions
        self.commit_and_check([("referenced", 2)]) 

def touch_cache(connection: "Connection", query_id: str) -> float:
    """
    'Touch' a cache record and update the cache score for it.

    Parameters
    ----------
    connection : Connection
    query_id : str
        Unique id of the query to touch

    Returns
    -------
    float
        The new cache score
    """
    try:
        return float(connection.fetch(f"SELECT touch_cache('{query_id}')")[0][0])
    except (IndexError, psycopg2.InternalError):
        raise ValueError(f"Query id '{query_id}' is not in cache on this connection.") 

def read_only_read_sa_transaction(conn, deferrable):
    await conn.execute(sa.insert(users).values(id=1, name='test1'))
    t1 = await conn.begin(
        isolation_level='SERIALIZABLE',
        readonly=True,
        deferrable=deferrable
    )

    where = users.c.id == 1

    try:
        await conn.execute(sa.update(users).values({'name': 't'}).where(where))
    except InternalError as e:
        assert e.pgcode == '25006'

    await t1.commit()

    await conn.execute(sa.delete(users))
    assert len(await (await conn.execute(users.select())).fetchall()) == 0 

def run(self, sql,  isolated=False):
    try:
        if isolated:
            self.conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) 
        cursor = self.conn.cursor()
        rows = cursor.execute(sql)
        self.conn.commit()
        return True,  None
    except  psycopg2.ProgrammingError as e:
        self._error_message(e)
        return None,  e
    except psycopg2.InternalError as e:
        self._error_message(e)
        return None,  e
    except psycopg2.DatabaseError as  e:
        self._error_message(e)
        return None,  e                       

  ## Gibt die Spalten eines Abfrageergebnisses zurck
  # @param abfrage string gewnschte SQL-Query
  # @return result array 1-Dim Array der Spaltennamen 

def create_extension(conn, extension_name, test=False):
    # The following error means that pglogical package is not installed into the operating system
    # ERROR:  could not open extension control file "/usr/share/postgresql/9.6/extension/pglogical.control":

    # The following error means that pglogical is installed but not configured correctly
    # ERROR:  pglogical is not in shared_preload_libraries
    cur = conn.cursor()
    try:
        cur.execute("CREATE EXTENSION IF NOT EXISTS %s" % extension_name)
        if not test:
            conn.commit()
    except psycopg2.InternalError as e:
        msg = str(e)
        if msg.find('shared_preload_libraries'):
            return 'InstalledNoSharedLibraries'
        return 'NotInstalled'
    except psycopg2.OperationalError:
        return 'NotInstalled'
    finally:
        if test:
            conn.rollback()
    return True 

def incremental_consolidate(self, batched):

		for count, url in batched:
			with db.session_context(override_timeout_ms=1000*60*30) as temp_sess:
				while 1:
					try:
						self.truncate_url_history(temp_sess, url)
						break
					except psycopg2.InternalError:
						temp_sess.rollback()
					except sqlalchemy.exc.OperationalError:
						temp_sess.rollback()
					except Exception:
						temp_sess.rollback()
						traceback.print_exc() 

def cursor(self):
        """Get a cursor, making sure the connection to the database is established."""
        try:
            cur = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        except psycopg2.InterfaceError:
            self._connect()
            cur = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        try:
            cur.execute('SET search_path TO {},public'.format(self.schema))
        except psycopg2.InternalError:
            self._connect()
            cur = self.conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
            cur.execute('SET search_path TO {},public'.format(self.schema))
        return cur 

def serializable_sa_transaction(conn, conn2):
    isolation_level = 'SERIALIZABLE'
    await conn.execute(sa.insert(users).values(id=1, name='test1'))
    t1 = await conn.begin(isolation_level=isolation_level)

    where = users.c.id == 1
    q_user = users.select().where(where)
    user = await (await conn.execute(q_user)).fetchone()

    assert await (await conn2.execute(q_user)).fetchone() == user

    await conn.execute(sa.update(users).values({'name': 'name2'}).where(where))

    t2 = await conn2.begin(isolation_level=isolation_level)
    assert await (await conn2.execute(q_user)).fetchone() == user

    await t1.commit()

    try:
        await conn2.execute(users.insert().values({'id': 2, 'name': 'test'}))
    except TransactionRollbackError as e:
        assert e.pgcode == '40001'

    try:
        await conn2.execute(users.update().values({'name': 't'}).where(where))
    except InternalError as e:
        assert e.pgcode == '25P02'

    await t2.commit()

    user = dict(await (await conn2.execute(q_user)).fetchone())
    assert user == {'name': 'name2', 'id': 1}

    await conn.execute(sa.delete(users))
    assert len(await (await conn.execute(users.select())).fetchall()) == 0 

def test_transaction_readonly_insert_oldstyle(engine):
    async with engine.acquire() as cur:
        tr = Transaction(cur, IsolationLevel.serializable,
                         readonly=True)

        await tr.begin()
        with pytest.raises(psycopg2.InternalError):
            await cur.execute("insert into tbl values(1, 'data')")
        await tr.rollback() 

def test_transaction_readonly_insert(engine):
    async with engine.cursor() as cur:
        async with Transaction(cur, IsolationLevel.serializable,
                               readonly=True):
            with pytest.raises(psycopg2.InternalError):
                await cur.execute("insert into tbl values(1, 'data')") 

def read(self, sql,  Message=False):
        datensatz = None
        if self.conn != None:
            try:
                column_names = []
                cursor = self.conn.cursor()
                column_names = self.cols(sql)
                cursor.execute(sql)
                self.conn.commit()
                rows = cursor.fetchall()
                
                if len(rows) > 0:
                    datensatz = {}
                    i = 0
                    
                    for col in column_names:
                        result = []
                        for row in rows:
                            result.append(row[i])
                        i = i + 1
                        datensatz.update({col.upper(): result})
                    cursor.close()  
                return datensatz,  None

            except  psycopg2.ProgrammingError as e:
                self._error_message(e)
                return None,  e
            except psycopg2.InternalError as e:
                self._error_message(e)
                return None,  e
            except psycopg2.DatabaseError as  e:
                self._error_message(e)
                return None,  e                

   # do stuff                

  ## Schliesst die DB-Verbindung 

def _cursor(self, name=None):
        """
        Here it happens. We hope every Django db operation using PostgreSQL
        must go through this to get the cursor handle. We change the path.
        """
        if name:
            # Only supported and required by Django 1.11 (server-side cursor)
            cursor = super()._cursor(name=name)
        else:
            cursor = super()._cursor()

        # optionally limit the number of executions - under load, the execution
        # of `set search_path` can be quite time consuming
        if (not get_limit_set_calls()) or not self.search_path_set:
            # Actual search_path modification for the cursor. Database will
            # search schemata from left to right when looking for the object
            # (table, index, sequence, etc.).
            if not self.schema_name:
                raise ImproperlyConfigured("Database schema not set. Did you forget "
                                           "to call set_schema() or set_tenant()?")
            _check_schema_name(self.schema_name)
            public_schema_name = get_public_schema_name()
            search_paths = []

            if self.schema_name == public_schema_name:
                search_paths = [public_schema_name]
            elif self.include_public_schema:
                search_paths = [self.schema_name, public_schema_name]
            else:
                search_paths = [self.schema_name]

            search_paths.extend(EXTRA_SEARCH_PATHS)

            if name:
                # Named cursor can only be used once
                cursor_for_search_path = self.connection.cursor()
            else:
                # Reuse
                cursor_for_search_path = cursor

            # In the event that an error already happened in this transaction and we are going
            # to rollback we should just ignore database error when setting the search_path
            # if the next instruction is not a rollback it will just fail also, so
            # we do not have to worry that it's not the good one
            try:
                search_paths = ['\'{}\''.format(s) for s in search_paths]
                cursor_for_search_path.execute('SET search_path = {0}'.format(','.join(search_paths)))
            except (django.db.utils.DatabaseError, psycopg2.InternalError):
                self.search_path_set = False
            else:
                self.search_path_set = True
            if name:
                cursor_for_search_path.close()
        return cursor 

def test_insert(self):

        self.cur.execute("SELECT COUNT(*) FROM {}.referenced_view".format(
            self.schema))
        assert(self.cur.fetchone()[0] == 2)

        self.cur.execute("SELECT COUNT(*) FROM {}.referencing_view".format(
            self.schema))
        assert(self.cur.fetchone()[0] == 1)

        # insert valid
        self.cur.execute(
            "insert into {}.referencing_view (id, fkid1, fkid2) "
            "values (18,42,4);".format(self.schema))

        self.con.commit()
        self.cur.execute("SELECT COUNT(*) FROM {}.referencing_view".format(
            self.schema))
        assert(self.cur.fetchone()[0] == 2)

        # insert fail unique constraint
        try:
            self.cur.execute(
                "insert into {}.referencing_view (id, fkid1, fkid2) "
                "values (16,1,18);".format(self.schema))
            assert(False and "Insert must fail unique constraint")
        except (IntegrityError, psycopg2.InternalError):
            self.con.rollback()
        else:
            self.con.commit()

        # insert fail foreign key constraint
        try:
            self.cur.execute(
                "insert into {}.referencing_view (id, fkid1, fkid2) "
                "values (19,42,7);".format(self.schema))
            assert(False and "Insert must fail foreign key constraint")
        except (IntegrityError, psycopg2.InternalError):
            self.con.rollback()
        else:
            self.con.commit()

        # 1 existing feature, insert one, so 2 expected revisions
        self.commit_and_check([("referencing", 2)]) 

def _cursor(self, name=None):
        """
        Here it happens. We hope every Django db operation using PostgreSQL
        must go through this to get the cursor handle. We change the path.
        """
        if name:
            # Only supported and required by Django 1.11 (server-side cursor)
            cursor = super()._cursor(name=name)
        else:
            cursor = super()._cursor()

        # optionally limit the number of executions - under load, the execution
        # of `set search_path` can be quite time consuming
        if (not get_limit_set_calls()) or not self.search_path_set:
            # Actual search_path modification for the cursor. Database will
            # search schemas from left to right when looking for the object
            # (table, index, sequence, etc.).
            if not self.schema:
                raise ImproperlyConfigured("Database schema not set. Did you forget to call set_schema()?")
            check_schema_name(self.schema.schema_name)
            search_paths = []

            if self.schema.schema_name == "public":
                search_paths = ["public"]
            elif self.include_public_schema:
                search_paths = [self.schema.schema_name, "public"]
            else:
                search_paths = [self.schema.schema_name]
            search_paths.extend(EXTRA_SEARCH_PATHS)

            if name:
                # Named cursor can only be used once
                cursor_for_search_path = self.connection.cursor()
            else:
                # Reuse
                cursor_for_search_path = cursor

            # In the event that an error already happened in this transaction and we are going
            # to rollback we should just ignore database error when setting the search_path
            # if the next instruction is not a rollback it will just fail also, so
            # we do not have to worry that it's not the good one
            try:
                cursor_for_search_path.execute("SET search_path = {0}".format(",".join(search_paths)))
            except (DatabaseError, psycopg2.InternalError):
                self.search_path_set = False
            else:
                self.search_path_set = True
            if name:
                cursor_for_search_path.close()
        return cursor