Tag Archives: version control

Pyrseas contributions solicited

Do you use PostgreSQL and truly believe it’s “the world’s most advanced open source database” and that its upcoming 9.3 release will make it even more awesome?

Do you also use Python and believe it’s “an easy to learn, powerful programming language” with “elegant syntax” that makes it an ideal language for developing applications and tools around PostgreSQL, such as Pyrseas?

Then we could use your help. For starters, we want to add support for the MATERIALIZED VIEWs and EVENT TRIGGERs coming up in PG 9.3.

We have also been requested to add the capability to load and maintain “static data” (relatively small, unchanging tables) as part of yamltodb, so that it can be integrated more easily into database version control workflows.

And for the next release, Pyrseas 0.7, we’d like to include the first version of the database augmentation tool which will support declarative implementation of business logic in the database–starting off with audit trail columns. Some work has been done on this already, but it needs integration with the current code and tests.

Or perhaps coding is not your forte, but you’re really good at explaining and documenting technical “stuff”. Then you could give us a hand with revamping the docs, maybe writing a tutorial so that users have a smooth ride using our tools.

Or maybe you have your own ideas as to how improve the PostgreSQL version control experience. We’d love to hear those too.

If you’d like to help, you can fork the code on GitHub, join the mailing list and introduce yourself, or leave a comment below.

A couple of Pyrseas enhancements

Based on feedback from users and contributors, Pyrseas now sports two enhancements.

Multi-line String Formatting

Up to Pyrseas 0.6, long textual elements such as view definitions, function source text and long object comments, would usually be shown in the YAML output as quoted strings with embedded newlines. Here are two examples from the autodoc database:

schema product:
  description: "This schema stores a list of products and information\n about the\
    \ product"
...
schema warehouse:
  view products:
    definition: " SELECT DISTINCT product.product_id, product.product_code, product.product_description\n\
      \   FROM warehouse.inventory\n   JOIN product.product USING (product_id);"

As you can imagine, this was particularly unsatisfactory for complex functions and views. Thanks to preliminary work by Andrey Popp, Pyrseas 0.7 will be able to format these elements in YAML block style. The above elements will be shown as follows:

schema product:
  description: |-
    This schema stores a list of products and information
     about the product
...
schema warehouse:
  view products:
    definition: |2-
       SELECT DISTINCT product.product_id, product.product_code, product.product_description
         FROM warehouse.inventory
         JOIN product.product USING (product_id);

Thanks to testing by Josep Martínez, 0.7 will also properly display and handle such strings even when they include non-ASCII characters such as accented characters. For example, in 0.6, “Martínez” would be shown as “Mart\xEDnez”. In 0.7, the output will be the original UTF-8 string.

Directory of Database Objects

Pyrseas 0.6 has a single format for output by dbtoyaml or input into yamltodb: a single YAML-formatted file. This becomes a problem when your database has hundreds or more tables, functions, etc (let alone 409,994 tables and counting!). Furthermore, as dbtoyaml and yamltodb are intended to assist with database version control, your team may want to store individual object specifications in your version control system, or you may want to diff individual objects.

The 0.7 --directory option to dbtoyaml and yamltodb allows you to split the YAML spec into multiple files in a directory (or folder) tree. For example, using the dbtoyaml -d option on the autodoc database results in the following tree (shown under Linux using ls -RF):

.:
schema.inherit/      schema.public/      schema.warehouse/
schema.inherit.yaml  schema.public.yaml  schema.warehouse.yaml
schema.product/      schema.store/
schema.product.yaml  schema.store.yaml

./schema.inherit:
table.tab1b.yaml  table.tab1.yaml  table.taba.yaml  table.tabb.yaml

./schema.product:
function.worker.yaml  sequence.product_product_id_seq.yaml  table.product.yaml

./schema.public:

./schema.store:
sequence.store_store_id_seq.yaml  table.inventory.yaml  table.store.yaml

./schema.warehouse:
function.worker.yaml                      table.warehouse.yaml
sequence.warehouse_warehouse_id_seq.yaml  view.products.yaml
table.inventory.yaml

As can be seen, each schema gets its own directory wherein are stored each object belonging to that schema. In addition to schemas, the root level also stores non-schema owned objects such as foreign data wrappers and extensions (the latter can be placed in a schema, but are not owned by it).

The directory tree and multi-line string formats are still under review, so I’d like to encourage you to test both enhancements and provide feedback.

PostgreSQL Indexes on Expressions

Pyrseas had its first release a little over a year ago and we now have our first backward compatibility issue. The first release included basic support for traditional indexes, i.e., one or more key columns. For example, given a table test1 with columns col1, col2 and col3, and an index on the last two, dbtoyaml would show (some details omitted):

table test1:
  columns:
  - col1
  - col2
  - col3
  indexes:
    test1_idx:
      columns:
      - col2
      - col3

One of the first issues reported the lack of support for “functional” indexes. I added that but unfortunately, didn’t realize that one can have more than one function or expression and even mix regular columns with expressions. Thus the support was limited to a single expression. Given the first example in the “Indexes on Expressions” documentation, dbtoyaml would show:

table test1:
  columns:
  ...
  indexes:
    test1_lower_col1_idx:
      expression:
        lower(col1)

The original issue was recently re-opened (thanks, Roger) to point out the deficiencies. A fix has been pushed. Thus in the next release, dbtoyaml will support indexes with multiple expressions and even combinations of functions and regular columns. Here is a weird example using the first table. Given CREATE INDEX test1_idx ON test1 (btrim(col3, 'x') NULLS FIRST, col1, lower(col2) DESC), dbtoyaml now outputs:

table test1:
  indexes:
    test1_idx:
      access_method: btree
      keys:
      - btrim(c3, 'x'::text):
          nulls: first
          type: expression
      - col1
      - lower(col2):
          order: desc
          type: expression

So instead of ‘columns’ (or ‘expression’), dbtoyaml outputs ‘keys’. Any key that is an expression is marked with the ‘type’ qualifier.To allow for backward compatibility, yamltodb will continue to accept ‘columns’, so existing YAML specs  with traditional indexes won’t need to be changed. However, if you have an index using an expression, you’ll have to edit as seen above.

Do you have an unusual index?  Try dbtoyaml (from GitHub) on it and let us know if it works (or not).

PostgreSQL Extensions and Pyrseas

Prompted by Peter Eisentraut’s blog post, I’ve finished adding support for PG 9.1 EXTENSIONs to the Pyrseas dbtoyaml and yamltodb utilities. For now, this is only available on GitHub.

In order to deal with procedural languages, which are now created as extensions, the utilities now fetch the pg_catalog schema (previously deemed uninteresting for the purpose of version control).  The output of dbtoyaml from a freshly created 9.1 database (assuming no customizations via template1) is now:

schema pg_catalog:
  description: system catalog schema
  extension plpgsql:
    description: PL/pgSQL procedural language
    version: '1.0'
schema public:
  description: standard public schema

This could be changed easily to exclude pg_catalog (which will now also appear against 8.4 and 9.0 databases) before the next Pyrseas release. Update: The pg_catalog schema will now only be shown if it has something other than a description.

I’m hoping some brave, adventurous or simply interested souls will help test the additions.  Please report any issues on GitHub.

Pyrseas PostgreSQL features: feedback requested

I’ve been considering the missing features of dbtoyaml/yamltodb.  Two of those are PG 9.1 features:  COLLATIONs and EXTENSIONs.  I plan to cover them eventually, but I think I ought to deal first with the remaining pre-9.1 features.

ROLEs (as well as USERs and GROUPs) and TABLESPACEs are not output by pg_dump (the equivalent of dbtoyaml), only by pg_dumpall.  I’m thinking that if I were to add support for ROLEs and TABLESPACEs I’d probably do it with a --cluster option to dbtoyaml, and the output would be something like the following:

database postgres:
  role one:
    createdb: true
    login: true
  role grp:
    roles:
      - one
 tablespace dataspace:
    location: /data/db

This approach could, in theory, produce output for all databases in a cluster, i.e., the databases would be the top nodes in the YAML spec, rather than the schemas as is normally the case. In other words, it would be the equivalent of pg_dumpall --schema-only. However, I suspect that few persons would be interested in that, at least for version control purposes—since different databases may belong to different projects.

On the other hand, I believe DBAs may want dbtoyaml to include “owner” and privlege (GRANT) information. David Fetter specifically asked for GRANTs saying they would be “handy for deployments.”

Owner and privilege information could be shown as follows:

schema public:
  table film:
    owner: jma
    privileges:
      admin:
        - insert
        - update
      jma:
        - all
      viewer:
        - select

An open question is whether some list of roles is necessary, aside from the object-level information.

I’d appreciate readers taking a couple of minutes to leave feedback on any of the above points, particularly on how important they think the features are in their day-to-day work.

Pyrseas/PostgreSQL Feature Matrix

In my last post, I wrote—referring to the state of Pyrseas after version 0.5 is released:

The only gaps left are TABLESPACE, GROUP/ROLE and the EXTENSIONs added in PG 9.1.

I’m afraid I should’ve double checked the list of 9.1 SQL CREATE statements. I missed COLLATIONs. I’ve created a new page, Feature Matrix, that shows the correct picture, which will be updated as subsequent releases are made.

The Phantom of the Database – Part 1

Scenario: A row with “Seven  Samurai” (notice two spaces between the words) as the movie title and 1956 as the release year, in the film table.

Plot: User Alice updates the row to remove the extra space. In the meantime, user Bob updates it to change the title to its phonetic Japanese equivalent “Shichinin no Samurai” and user Carol updates the release date to 1954, the year the movie was first shown in Japan. Whose updates will survive?

If the updates take place sequentially and each user fetches the previously updated row prior to saving his or her change, we should end up with a row with “Shichinin no Samurai” as the title and 1954 as the year.

However, if each user starts viewing the original row in a web browser and the application is incorrectly designed, one or two changes may be overwritten.

For example, my minimalist user interface application even in its more robust incarnation, issues an UPDATE with both the title and the year as received from the user. This was done intentionally, for simplicity, to be corrected later. As a result, if the users press the “Save” button in the sequence shown, both Alice’s correction to the English title and Bob’s change to a Japanese title will be lost because they will be overwritten by Carol’s change.

If you’ve used a centralized VCS, like Subversion, you’re probably familiar with the issue. If our users were developers updating a source file with the movie information, they would (typically) be prevented from overwriting because the VCS would inform the second and third users that they had to merge the changes in prior commits.

So, is it enough if the application only updates the columns that were changed by the users, somewhat like a VCS automatically merging non-conflicting changed lines?

To be continued …