yamltodb 0.7.1 attempts to create already existing table when postgres casts are involved #86

Closed
squareproton opened this Issue Mar 10, 2014 · 5 comments

Comments

Projects
None yet
2 participants
@squareproton

To reproduce execute the following sql and build test_cast.yml with dbtoyaml.

DROP SCHEMA IF EXISTS test_cast CASCADE;
CREATE SCHEMA test_cast;
SET search_path TO test_cast;

CREATE TABLE t1 (
    id integer
);

CREATE VIEW v1 AS
SELECT * FROM t1;

CREATE FUNCTION v1_to_t1(v1) RETURNS t1 LANGUAGE plpgsql AS $_$
DECLARE
    o t1;
BEGIN
    o := ROW($1.id)::t1;
    RETURN o;
END
$_$;

CREATE CAST (v1 AS t1) WITH FUNCTION v1_to_t1(v1);

Drop the view DROP VIEW v1 CASCADE; which will remove the function and cast declaration.

Running yamltodb -o /dev/stdout test_cast test_cast.yml yeilds

SET check_function_bodies = false;

CREATE TABLE test_cast.t1 (
    id integer);

ALTER TABLE test_cast.t1 OWNER TO unit;

CREATE FUNCTION test_cast.v1_to_t1(test_cast.v1) RETURNS test_cast.t1
    LANGUAGE plpgsql
    AS $_$
DECLARE
    o t1;
BEGIN
    o := ROW($1.id)::t1;
    RETURN o;
END
$_$;

ALTER FUNCTION test_cast.v1_to_t1(test_cast.v1) OWNER TO unit;

CREATE VIEW test_cast.v1 AS
    SELECT t1.id
   FROM test_cast.t1;

ALTER VIEW test_cast.v1 OWNER TO unit;

CREATE CAST (test_cast.v1 AS test_cast.t1)
    WITH FUNCTION test_cast.v1_to_t1(test_cast.v1);

This will fail for two reasons.

  1. The table t1 already exists and hasn't changed.
  2. The function v1_to_t1 cannot be created before v1 as it uses the v1 type in its declaration.

Tested on pyrseas v0.7.1 and postgres 9.2.6.

@jmafc jmafc added the yamltodb label Mar 11, 2014

@jmafc

This comment has been minimized.

Show comment Hide comment
@jmafc

jmafc Mar 11, 2014

Member

For my own edification, I'm curious about the purpose of the cast in a realistic case, since in the test case it's essentially a no-op.

Member

jmafc commented Mar 11, 2014

For my own edification, I'm curious about the purpose of the cast in a realistic case, since in the test case it's essentially a no-op.

@jmafc

This comment has been minimized.

Show comment Hide comment
@jmafc

jmafc Mar 11, 2014

Member

The problem is in the Function.create routine in pyrseas/dbobject/function.py. We check whether a function has a dependent_table and then go ahead and create it. Obviously, we first need to check whether the table exists.

Member

jmafc commented Mar 11, 2014

The problem is in the Function.create routine in pyrseas/dbobject/function.py. We check whether a function has a dependent_table and then go ahead and create it. Obviously, we first need to check whether the table exists.

@squareproton

This comment has been minimized.

Show comment Hide comment
@squareproton

squareproton Mar 12, 2014

The test case is a stripped down and variable renamed version of my actual usage. I'm casting a view to a table so I can reuse some functions which have the table type in their definition. I've got a fair few views which are essentially SELECT table.*, something_else, ...

I mucked around with trying to get a cast without having to resort to the heavyweight DIY solution but I couldn't find a invocation of ROW() constructors and casts which did it. It wouldn't surprise me at all if there was a better way of doing this.

The test case is a stripped down and variable renamed version of my actual usage. I'm casting a view to a table so I can reuse some functions which have the table type in their definition. I've got a fair few views which are essentially SELECT table.*, something_else, ...

I mucked around with trying to get a cast without having to resort to the heavyweight DIY solution but I couldn't find a invocation of ROW() constructors and casts which did it. It wouldn't surprise me at all if there was a better way of doing this.

@jmafc

This comment has been minimized.

Show comment Hide comment
@jmafc

jmafc Mar 19, 2014

Member

I've tried a possible solution, namely, in SchemaDict.link_refs, save references to both the schema and the table in the Function dependent_table attribute, and then use that in Function.create to determine whether to call Table.create, but it causes a regression in a couple of FunctionToSqlTestCase tests. Still thinking about another solution.

Member

jmafc commented Mar 19, 2014

I've tried a possible solution, namely, in SchemaDict.link_refs, save references to both the schema and the table in the Function dependent_table attribute, and then use that in Function.create to determine whether to call Table.create, but it causes a regression in a couple of FunctionToSqlTestCase tests. Still thinking about another solution.

@jmafc

This comment has been minimized.

Show comment Hide comment
@jmafc

jmafc Oct 13, 2017

Member

@squareproton This has been fixed in the current master branch, as a result of the dependency tracking changes. yamltodb now produces CREATE VIEW, CREATE FUNCTION and CREATE CAST in that order (with the CREATE TABLE). I will try to add a test to further verify this and avoid future regressions, but perhaps you can try it on your actual examples.

Member

jmafc commented Oct 13, 2017

@squareproton This has been fixed in the current master branch, as a result of the dependency tracking changes. yamltodb now produces CREATE VIEW, CREATE FUNCTION and CREATE CAST in that order (with the CREATE TABLE). I will try to add a test to further verify this and avoid future regressions, but perhaps you can try it on your actual examples.

@jmafc jmafc added this to the Release 0.8 milestone Oct 13, 2017

@jmafc jmafc closed this Oct 13, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment