# HG changeset patch
# User Daniele Pizzoni <d.pizzoni@feedstock.com>
# Date 1610961129 -3600
# Mon Jan 18 10:12:09 2021 +0100
# Branch redshift
# Node ID 8ecf65ff2fafadc4195d14e8939f04ae2a5ab833
# Parent d8495101e8c79ed93e153c948deeaedfc3fda580
Add AWS Redshift support
- add a new backend for Redshift (no savepoint rollbacks, compatible row
lock insertion)
- skip nested transactions tests that fail due to missing savepoint support
- fix some test time durations to avoid failing on Redshift
- add a commented Redshift example line to test_databases.ini.
diff -r d8495101e8c7 -r 8ecf65ff2faf test_databases.ini
--- a/test_databases.ini Thu Nov 05 13:54:16 2020 +0000
+++ b/test_databases.ini Mon Jan 18 10:12:09 2021 +0100
@@ -2,3 +2,4 @@
sqlite = sqlite:///:memory:
postgresql = postgresql://postgres@/yoyo_test
mysql = mysql://root@/yoyo_test?unix_socket=/tmp/mysql.sock
+#redshift = redshift://<user>:<password>@<subdomain>.redshift.amazonaws.com:5439/<dbname>
diff -r d8495101e8c7 -r 8ecf65ff2faf yoyo/backends.py
--- a/yoyo/backends.py Thu Nov 05 13:54:16 2020 +0000
+++ b/yoyo/backends.py Mon Jan 18 10:12:09 2021 +0100
@@ -703,6 +703,54 @@
return super(PostgresqlBackend, self).list_tables(schema=current_schema)
+class RedshiftBackend(PostgresqlBackend):
+
+ def list_tables(self):
+ current_schema = self.execute("SELECT current_schema()").fetchone()[0]
+ return super(PostgresqlBackend, self).list_tables(schema=current_schema)
+
+ # Redshift does not support ROLLBACK TO SAVEPOINT
+ def savepoint(self, id):
+ pass
+
+ def savepoint_release(self, id):
+ pass
+
+ def savepoint_rollback(self, id):
+ self.rollback()
+
+ # Redshift does not enforce primary and unique keys
+ def _insert_lock_row(self, pid, timeout, poll_interval=0.5):
+ poll_interval = min(poll_interval, timeout)
+ started = time.time()
+ while True:
+ with self.transaction():
+ # prevents isolation violation errors
+ self.execute("LOCK {}".format(self.lock_table_quoted))
+ cursor = self.execute(
+ "SELECT pid FROM {}".format(self.lock_table_quoted)
+ )
+ row = cursor.fetchone()
+ if not row:
+ self.execute(
+ "INSERT INTO {} (locked, ctime, pid) "
+ "VALUES (1, :when, :pid)".format(
+ self.lock_table_quoted
+ ),
+ {"when": datetime.utcnow(), "pid": pid},
+ )
+ return
+ elif timeout and time.time() > started + timeout:
+ raise exceptions.LockTimeout(
+ "Process {} has locked this database "
+ "(run yoyo break-lock to remove this lock)".format(
+ row[0]
+ )
+ )
+ else:
+ time.sleep(poll_interval)
+
+
class SnowflakeBackend(DatabaseBackend):
driver_module = "snowflake.connector"
diff -r d8495101e8c7 -r 8ecf65ff2faf yoyo/connections.py
--- a/yoyo/connections.py Thu Nov 05 13:54:16 2020 +0000
+++ b/yoyo/connections.py Mon Jan 18 10:12:09 2021 +0100
@@ -22,6 +22,7 @@
from .migrations import default_migration_table
from .backends import PostgresqlBackend
+from .backends import RedshiftBackend
from .backends import SQLiteBackend
from .backends import ODBCBackend
from .backends import OracleBackend
@@ -39,6 +40,7 @@
"mysql+mysqldb": MySQLdbBackend,
"sqlite": SQLiteBackend,
"snowflake": SnowflakeBackend,
+ "redshift": RedshiftBackend,
}
diff -r d8495101e8c7 -r 8ecf65ff2faf yoyo/tests/test_backends.py
--- a/yoyo/tests/test_backends.py Thu Nov 05 13:54:16 2020 +0000
+++ b/yoyo/tests/test_backends.py Mon Jan 18 10:12:09 2021 +0100
@@ -37,6 +37,9 @@
assert rows == []
def test_it_nests_transactions(self, backend):
+ if ("redshift" in backend.uri.scheme):
+ pytest.skip("Nested transactions not supported for Redshift")
+
with backend.transaction():
backend.execute("INSERT INTO yoyo_t values ('A')")
@@ -51,9 +54,28 @@
rows = list(backend.execute("SELECT * FROM yoyo_t").fetchall())
assert rows == [("A",), ("C",)]
+ def test_redshift_nested_transactions(self, backend):
+ if (not "redshift" in backend.uri.scheme):
+ pytest.skip("Redshift only test")
+
+ with backend.transaction():
+ backend.execute("INSERT INTO yoyo_t values ('A')")
+
+ with backend.transaction() as trans:
+ backend.execute("INSERT INTO yoyo_t values ('B')")
+ trans.rollback()
+
+ with backend.transaction() as trans:
+ backend.execute("INSERT INTO yoyo_t values ('C')")
+
+ with backend.transaction():
+ rows = list(backend.execute("SELECT * FROM yoyo_t").fetchall())
+ assert rows == [("C",)]
+
def test_backend_detects_transactional_ddl(self, backend):
expected = {
backends.PostgresqlBackend: True,
+ backends.RedshiftBackend: True,
backends.SQLiteBackend: True,
backends.MySQLBackend: False,
}
diff -r d8495101e8c7 -r 8ecf65ff2faf yoyo/tests/test_migrations.py
--- a/yoyo/tests/test_migrations.py Thu Nov 05 13:54:16 2020 +0000
+++ b/yoyo/tests/test_migrations.py Mon Jan 18 10:12:09 2021 +0100
@@ -638,7 +638,7 @@
assert logged["migration_id"] == "a"
assert logged["operation"] == "apply"
assert logged["created_at_utc"] >= datetime.utcnow() - timedelta(
- seconds=2
+ seconds=3
)
apply_time = logged["created_at_utc"]
@@ -660,7 +660,7 @@
assert logged["migration_id"] == "a"
assert logged["operation"] == "mark"
assert logged["created_at_utc"] >= datetime.utcnow() - timedelta(
- seconds=2
+ seconds=3
)
marked_time = logged["created_at_utc"]
[https://static.feedstock.com/public/email_fs_logo_1.jpg] [https://static.feedstock.com/public/email_iso_1.jpg]
This email (and any attachments) (“Message”) has been sent to you by Feedstock Limited (“Feedstock”). This Message is intended solely for the use of the addressee(s). It is confidential, may be legally privileged and is the property of Feedstock or (in respect of certain attachments only) persons known to Feedstock. You should not copy, forward or disclose this Message without our consent. If you have received this Message in error please immediately delete all copies and notify the sender.
FeedStock’s Privacy Policy has been updated, effective 23 May 2018, to address the requirements under the General Data Protection Regulation (“GDPR”). You are able to read the updated policy here<https://static.feedstock.com/public/FeedStock_Privacy_Policy_180723.pdf>.
Feedstock provides all Messages on an 'as is' and 'as available' basis. Feedstock does not warrant that the contents of this Message are accurate or error free. Feedstock accepts no liability whatsoever for any losses you incur in reliance on this message or due to errors or inaccuracies contained herein. To the fullest extent permitted by law, Feedstock excludes all express or implied representations or warranties.