Summary, this can be proven not to work even when it "works”. Selling point for views, breaking it subtly like this is dangerous. While this example is quite contrived it illustrates the problems which can View however, the results are however not what we’d expect unless we knew about Success, we can now start playing with full text search. Recreate" solution to this situation it works without even a NOTICE beingĪlter table tt alter column b type tsvector using b::tsvector Now let’s say that a team member want to make use of the new cool text searchįeature merged as part of 8.3 he/she subsequently alters the type of tt.b to Querying our view gives us the following result: Lets start with setting up our table and a view which performs operations onĬreate table tt (a integer, b text) distributed by (a) Ĭreate view ttv as select trim(both ((b || 'a')::text), $$'$$) from tt See below for an example what can happen: The main problem with it is that when it doesn’t work it doesn’t The allure of this solution is that it works quite often, for a certain value This can then be used in the following sequence to automate the process needed Join pg_class c on (d.objoid = c.oid) where c.relname = 'v' Retrieve it even without involving extra tables:Ĭomment on view v is $view$ select c from t $view$ Making anĮxternal app which saves the text in a table is however trivial, the query textĬan even be saved as a comment on the view object itself to make it easy to On a column which is part of a view works, the object is unchanged). Of the view definition but instead work on an object level (this is why rename PostgreSQL doesn’t use, or store, the textual representation Text making up the view, and then drop/recreate it on either side of the ALTER The views which contain references to an attribute which is being altered suchĪ naive implementation (which is often suggested) is to simply save the query The gist of what we all want is: For the database to automatically re-create Thousands of views makes it hard for a DBA to perform requested table changes. This in combination with an installation having hundreds or even View is prohibited, the view must be dropped before the alter table statement The problem statement is simple: altering an object which is referenced in a Without getting closer to a good solution. PostgreSQL (and Greenplum) arise every now and again unfortunately still It will be interesting to see if there are any real-world operational drawbacks to this approach, and seeing if MySQL offers this soon as well.The question of how to handle dependent views during table alteration in MariaDB 10.8, which is still pre-GA, adds a clever solution to the replication problem. That's one of the most common ALTER cases, so this feature is quite nice, but it certainly doesn't solve everything.įor this reason, external tools such as pt-online-schema-change are still pretty essential for MySQL/MariaDB deployments of any non-trivial size. The newer INSTANT algo in MySQL 8 and MariaDB 10.3+ solves this, but it is only usable for a limited subset of alter operations, such as adding a new column. (This is inherently a trade-off of MySQL/MariaDB using logical replication, instead of physical replication of the tx log / WAL.) Historically, native online DDL in MySQL 5.6+ / MariaDB 10+ isn't replication-friendly - despite being non-blocking on the primary, it blocks the replication stream application on replicas, which makes it basically unusable at scale. (The latter can be simply READ COMMITTED to avoid the possibility of serialization failure.)Īnd also of course you can't issue the DELETE on the audit table if the transaction is READ ONLY - but instead of doing that, you could just read out the contents of the audit table (or the max serial), and skip those entries when applying the audit table later. However I think you could avoid the client round-trip by exporting the snapshot from the read-only transaction using pg_export_snapshot() and then, on the concurrent connection, copy in batches by alternating between a read-only transaction opened on this snapshot using SET TRANSACTION SNAPSHOT in which you grab a WITH HOLD cursor on a LIMIT query (which copies the rows into an in-memory region), and a non-read-only transaction which actually writes to the shadow table. Unfortunately this would mean you'd have to round-trip the data being copied through the client into a concurrent non-read-only transaction to write into the shadow table. This is the only way in PostgreSQL to read from a table both without holding any locks, and also without suffering the potential of failed serialization (which would mean restarting the entire copy). Rather than plain SERIALIZABLE, maybe consider a SERIALIZABLE READ ONLY DEFERRABLE transaction to read the data.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |