sql-runner fails on IF EXISTS statements

[gabrielcrowdtilt]

For example, web-incremental step 00-preparation: https://github.com/snowplow/snowplow/blob/master/5-data-modeling/sql-runner/redshift/sql/web-incremental/00-preparation/00-preparation.sql#L25

Gives this output

2015/10/07 04:12:30 EXECUTING 00-preparation (in step 00-preparation @ snowplow): /opt/snowplow/sql_runner/analytics/sql/web-incremental/00-preparation/00-preparation.sql
2015/10/07 04:12:30 FAILURE: 00-preparation (step 00-preparation @ target snowplow), ERROR: ERROR #3F000 schema "snplw_temp" does not exist: 
2015/10/07 04:12:30 
TARGET INITIALIZATION FAILURES:
QUERY FAILURES:
* Query 00-preparation /opt/snowplow/sql_runner/analytics/sql/web-incremental/00-preparation/00-preparation.sql (in step 00-preparation @ target snowplow), ERROR:
  - ERROR #3F000 schema "snplw_temp" does not exist: 

Can sql-runner be made to handle this edge case. It would also allow one to create playbooks for all initial schema and table setup.

[alexanderdean]

/cc @bogaert

[alexanderdean]

Using psql:

db00002=# drop schema if exists foobar cascade;
INFO:  Schema "foobar" does not exist and will be skipped
DROP SCHEMA

It has to be a bug in the underlying Golang Postgres driver. We could try to upgrade to v3 of that driver and see if the problem goes away; if not then I think we have to raise a bug with the driver maintainers.

[jbeemster]

Hey @alexanderdean not sure where to go with this ticket as we have this command in our integration-test with no issues.

https://github.com/snowplow/sql-runner/blob/master/integration-tests/postgres-sql/good/1.sql#L3

[alexanderdean]

I have seen this issue when running Snowplow playbooks - this may be a Postgres vs Redshift issue?

[jbeemster]

Quite possible! I will try running this against a Redshift target to re-create.

[alexanderdean]

Thanks Josh!

[jbeemster]

This fails against a Redshift Target with v2 and v3.

[jbeemster]

Further on this it is not just the DROP SCHEMA IF EXISTS command but also just a CREATE SCHEMA command. Seems to have issues working with schemas in general.

[alexanderdean]

Can you reproduce with psql, or is this only a SQL Runner issue?

[jbeemster]

Can confirm that this is only an issue with SQL Runner, running the same playbook from CLI using psql works exactly as it should. Should we raise an issue with them about this?

[alexanderdean]

That's super weird. It must be a bug in the Golang PG v2 driver. Can we try upgrading to the v3, see if the problem goes away?

[jbeemster]

Did that already sadly.

[alexanderdean]

Let's create a separate ticket to upgrade to v3 of the PG driver in this release anyway...

[jbeemster]

SQL Runner fails when running both of the following commands:

DROP SCHEMA IF EXISTS any_schema_here CASCADE;

-- OR

CREATE SCHEMA any_schema_here;

I tried running both of these lines against a fresh Redshift cluster and they both failed with the: ERROR #3F000 schema "any_schema_here" does not exist:

From psql to the same cluster everything runs as expected.

[andrioni]

We also faced the same issue here, but IIRC doing the schema manipulation queries as the only query in a separate step worked, at least on Redshift, but I might be misremembering.

[iaingray]

I've just run into this bug too on 0.4.0.

• ERROR #3F000 schema "staging" does not exist.

When running DROP SCHEMA IF EXISTS followed by CREATE SCHEMA then commands which depend on the schema.

I can confirm that moving the schema DROP/CREATE into its own step is an effective workaround.

The scripts work ok when run through Navicat, so it's a sql-runner specific thing.

[esquire900]

In case anybody keeps hitting this problem like happend with us: the only way that seemed to solve this was to move

CREATE SCHEMA IF NOT EXISTS snplw_temp;

from the top of 00-preparation to the end of 08-track-queries.

[alexanderdean]

Thanks for sharing Simon!

[alexanderdean]

Pushing back