yamltodb fails on cross-schema foreign keys

[darthunix]

yamltodb fails on generating sql for tables that have foreign keys reffer on tables from different namespaces. For example shema1.table1(smoid) reffers with foreign key to shema2.table2(smoid) by smoid. yamltodb failse with

Traceback (most recent call last):
  File "/home/VIVEYA/denis/venv/Pyrseas/bin/yamltodb", line 9, in <module>
    load_entry_point('Pyrseas==0.7.2', 'console_scripts', 'yamltodb')()
  File "/home/VIVEYA/denis/venv/Pyrseas/lib/python3.5/site-packages/Pyrseas-0.7.2-py3.5.egg/pyrseas/yamltodb.py", line 47, in main
    stmts = db.diff_map(inmap)
  File "/home/VIVEYA/denis/venv/Pyrseas/lib/python3.5/site-packages/Pyrseas-0.7.2-py3.5.egg/pyrseas/database.py", line 364, in diff_map
    stmts.append(self.db.constraints.diff_map(self.ndb.constraints))
  File "/home/VIVEYA/denis/venv/Pyrseas/lib/python3.5/site-packages/Pyrseas-0.7.2-py3.5.egg/pyrseas/dbobject/constraint.py", line 556, in diff_map
    stmts.append(self[(sch, tbl, cns)].diff_map(inconstr))
  File "/home/VIVEYA/denis/venv/Pyrseas/lib/python3.5/site-packages/Pyrseas-0.7.2-py3.5.egg/pyrseas/dbobject/constraint.py", line 235, in diff_map
    (self.ref_col_idxs!= infk.ref_col_idxs)
AttributeError: 'ForeignKey' object has no attribute 'ref_col_idxs'

It seems that yamltodb expects only foreign keys from the same namespace...

test_dmp.txt test_yaml.txt

[jmafc]

It's strange because we've had a functional test based on autodoc (https://github.com/perseas/Pyrseas/blob/master/tests/functional/autodoc-schema.sql) for over three years now. Anyway, I'll take a look.

[jmafc]

What is the command that you executed that caused that stack trace? Were you using yamltodb against the database from which test_dmp.txt was created and using test_yaml.txt as the input YAML spec? If that is the case, I'm unable to reproduce the problem (using Python 3.5.2, Pyrseas 0.7.2, PG 9.4.8, psycopg2 2.6.2). I also invoked yamltodb against a different database and didn't get an error.

[darthunix]

Hello! Thanks for a quick answer. I can reproduce this bug on different postgres servers and different schemas. Current schema example is the simplest possible to reproduce the problem.

Environment:

1. PG server - PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit
2. Python 3.5.2
3. Pyrseas 0.7.2 (pulled from github's master branch)

Actions:

1. Install Pyrseas 0.7.2 from github into Python's 3.5.2 virtual env. Now I have dbtoyaml and yamltodb working correctly in venv.
2. Create testdb on PG server (address: 192.168.5.182, structure: 2 schemas, 2 tables - as easy as possible).

createdb -h 192.168.5.182 -U postgres --owner=postgres testdb
psql -h 192.168.5.182 -U postgres testdb < /tmp/create_testdb_sql.txt

create_testdb_sql.txt

3. Create yaml file describing created testdb dbtoyaml -H 192.168.5.182 -U postgres testdb > /tmp/testdb_yaml.txt testdb_yaml.txt
4. Generate diff SQL code, comparing testdb and testdb_yaml.txt (output should be empty, nothing changed). yamltodb -H 192.168.5.182 -U postgres testdb /tmp/testdb_yaml.txt

At this moment I get the errors

Traceback (most recent call last):
  File "/home/VIVEYA/denis/venv/Pyrseas_test/bin/yamltodb", line 9, in <module>
    load_entry_point('Pyrseas==0.7.2', 'console_scripts', 'yamltodb')()
  File "/home/VIVEYA/denis/venv/Pyrseas_test/lib/python3.5/site-packages/Pyrseas-0.7.2-py3.5.egg/pyrseas/yamltodb.py", line 47, in main
    stmts = db.diff_map(inmap)
  File "/home/VIVEYA/denis/venv/Pyrseas_test/lib/python3.5/site-packages/Pyrseas-0.7.2-py3.5.egg/pyrseas/database.py", line 364, in diff_map
    stmts.append(self.db.constraints.diff_map(self.ndb.constraints))
  File "/home/VIVEYA/denis/venv/Pyrseas_test/lib/python3.5/site-packages/Pyrseas-0.7.2-py3.5.egg/pyrseas/dbobject/constraint.py", line 552, in diff_map
    stmts.append(self[(sch, tbl, cns)].diff_map(inconstr))
  File "/home/VIVEYA/denis/venv/Pyrseas_test/lib/python3.5/site-packages/Pyrseas-0.7.2-py3.5.egg/pyrseas/dbobject/constraint.py", line 235, in diff_map
    (self.ref_col_idxs!= infk.ref_col_idxs)
AttributeError: 'ForeignKey' object has no attribute 'ref_col_idxs'

I have looked at tests from your comment. Yes, it is strange, that they have passed correctly and my example breaks... Butthe fact is that there is some problem with cross-schema foreign keys. And I have shown here a very simple exaple. I have tryed and debuged another variant shema1.table1(id2) -> shema2.table2(id2), shema1.table1(id1) -> shema1.table2(id1)... there were even more interesting errors in yamltodb. yamltodb was trying to use id2 on the same schema1, but not on schema2. But it is the next problem, first lets try to solve the simplest one))

[jmafc]

A quick reply (I'll test this tomorrow): I suspect the problem is in the master branch, which although it says is 0.7.2, actually is 0.8dev. I tested against branch r0.7 HEAD, which is 0.7.2 plus recent fixes.

[darthunix]

Thanks, checkout to r0.7 fixed this problem! But I have tested my development db and got new errors with yamltodb. I'll analyze them and open a new issue. This issue can be closed)) Thanks for help!

[jmafc]

@vayerx : The problem was introduced by change a7090d2. I know you did something in the following commit (afd5fe3) to fix some of the tests, but apparently something slipped by. If you have some time to look at this, I would appreciate it.

[vayerx]

@jmafc , I'm on vacation at the moment and don't have access to my development machine. I'll be able to have a look after 12 days.

[jmafc]

@vayerx Enjoy! I'll try to check this out in the meantime.

[jmafc]

@vayerx I've commited 8d5d06e which fixes the AttributeError exception. However, yamltodb continues to output (even after the changes have been applied):

ALTER TABLE schema1.table1 DROP CONSTRAINT table1_fk2;

ALTER TABLE schema1.table1 ADD CONSTRAINT table1_fk2 FOREIGN KEY (smoid) REFERENCES schema2.table2 (smoid) ON UPDATE CASCADE ON DELETE CASCADE;

I suspect this is because of the "TODO: compare column names", so I'll leave this open for the time being.

[jmafc]

@darthunix I re-tested this in the master branch after merging PRs #147 and #148 and I no longer see the extraneous ALTER TABLE statements. When you have a chance, please verify so that we may close this as well.

[darthunix]

I have tested master on my project with a very wierd schema and it works ok. No errors, output is equal to r0.7. So you can close this issue. I am switching to master to stabilize r0.7 and make master the only dev branch.