1Known Issues 2============ 3 4The following summarizes notable deficiencies in the current release 5of the Pyrseas utiltities. For further details please refer to the 6discussions in the `Pyrseas issue tracker 7<https://github.com/perseas/Pyrseas/issues>`_. Suggestions or patches 8to deal with these issues are welcome. 9 10Coverage of Postgres Objects 11---------------------------- 12 13An important Pyrseas objective is to support creating, altering or 14dropping nearly any Postgres object accessible through SQL, including 15adding, modifying or removing any attributes or features of those 16objects. At present, we believe Pyrseas covers roughly over 90% of 17the Postgres object/attribute universe. Please refer to the `Feature 18Matrix <https://pyrseas.wordpress.com/feature-matrix/>`_ for details. 19 20This is a continuing effort since Postgres keeps adding new features 21in each release, such as the table PARTITIONING syntax in PG 10. We 22have documented current limitations in the issue tracker, see, for 23example, issues `135 <https://github.com/perseas/Pyrseas/issues/135>`_ 24and `178 <https://github.com/perseas/Pyrseas/issues/178>`_. Please 25open an issue on the tracker if you find objects or features needing 26additional support. 27 28Object Dependencies 29------------------- 30 31The first releases of :program:`yamltodb` used a generally fixed 32traversal order when generating SQL. This caused problems with 33complex dependencies between objects (e.g., views that depended on 34functions that depended on types). Release 0.8 introduced a 35topological sort of objects based on their dependencies. The 36resulting dependency graph is now used to drive SQL generation. This 37should eliminate most object dependency problems seen with the 38previous architecture. However, certain issues still remain. 39Specifically, if an object depends on a Postgres internally-defined 40object, or on an object defined by a Postgres extension, the Pyrseas 41utilities may not behave as expected (see issue `175 42<https://github.com/perseas/Pyrseas/issues/175>`_ for additional 43discussion). 44 45Object renaming 46--------------- 47 48Pyrseas provides support for generating SQL statements to rename 49various database objects, e.g., ALTER TABLE t1 RENAME TO t2, using an 50'oldname' tag which can be added to objects that support SQL RENAME. 51The tag has to be added manually to a YAML specification for yamltodb 52to act on it and cannot be kept in the YAML file for subsequent runs. 53This is not entirely satisfactory for storing the YAML file in a 54version control system. 55 56Memory utilization 57------------------ 58 59The yamltodb utility compares the existing and input metadata by 60constructing parallel, in-memory representations of the database 61catalogs and the input YAML specification. If the database has a 62large number of objects, e.g., in the thousands of tables, the 63utility's memory usage may be noticeable. 64 65 66Multiline Strings 67----------------- 68 69The text of function source code, view definitions or object COMMENTs 70present a problem when they span multiple lines. The default YAML 71output format is to enclose the entire string in double quotes, to 72show newlines that are part of the text as escaped characters (i.e., 73``\n``) and to break the text into lines with a 74backslash-newline-indentation-backslash pattern. For example:: 75 76 source: "\n SELECT inventory_id\n FROM inventory\n WHERE film_id =\ 77 \ $1\n AND store_id = $2\n AND inventory_in_stock(inventory_id);\n" 78 79This is not very readable, but it does allow YAML to read it back and 80correctly reconstruct the original string. To improve readability, 81Pyrseas 0.7 introduced special processing for these strings. By using 82YAML notation, the same string is represented as follows:: 83 84 source: |2 85 86 SELECT inventory_id 87 FROM inventory 88 WHERE film_id = $1 89 AND store_id = $2 90 AND NOT inventory_in_stock(inventory_id); 91 92However, due to Python 2.x issues with Unicode, the more readable 93format is *only* available if using Python 3.x. 94 95Note also that if your function source code has trailing spaces at the 96end of lines, they would normally be represented in the original 97default format. However, in the interest of readability, 98:program:`dbtoyaml` will remove the trailing spaces from the text. 99 100Index and Partitioning Expressions 101---------------------------------- 102 103Postgres allows users to create `indexes using expressions 104<https://www.postgresql.org/docs/current/static/indexes-expressional.html>`_. 105A user can also mix expressions with regular columns. The Postgres 106catalogs store the index information in a bespoke fashion: an array of 107column numbers where a zero indicates an expression and a list of 108expression trees (an internal format) for the expressions, with 109additional arrays for collation information, operator classes and 110index options such as ``ASC`` or ``DESC``. Although the 111``pg_get_indexdef`` system catalog function can be used to obtain a 112full ``CREATE INDEX`` statement, Pyrseas has chosen to specify each 113column or expresssion separately in the YAML definitions. This has 114not been satisfactory in complex cases (see for example issue `170 115<https://github.com/perseas/Pyrseas/issues/170>`_) and is an area 116requiring further attention. A similar situation exists for table 117partitioning using expresssions. 118