`DROP TABLE` statements are only executed in the `public` schema

[rodrigo-morales-1]

Prologue

I've noticed that *_pre.sql files have two statements: DROP TABLE and CREATE TABLE. See some examples below.

stackexchange-dump-to-postgres/sql/Users_pre.sql

Lines 1 to 2 in 49d8358

	DROP TABLE IF EXISTS Users CASCADE;
	CREATE TABLE Users (

stackexchange-dump-to-postgres/sql/Badges_pre.sql

Lines 1 to 2 in 49d8358

	DROP TABLE IF EXISTS Badges CASCADE;
	CREATE TABLE Badges (

stackexchange-dump-to-postgres/sql/Tags_pre.sql

Lines 1 to 2 in 49d8358

	DROP TABLE IF EXISTS Tags CASCADE;
	CREATE TABLE Tags (

These files are executed in the public schema because the connection retrieves its configuration from the dbConnectionParam variable which doesn't consider the schema specified by the user.

stackexchange-dump-to-postgres/load_into_pg.py

Lines 185 to 203 in 49d8358

	pre = open("./sql/" + table + "_pre.sql").read()
	post = open("./sql/" + table + "_post.sql").read()
	fk = open("./sql/" + table + "_fk.sql").read()
	except IOError as e:
	six.print_(
	"Could not load pre/post/fk sql. Are you running from the correct path?",
	file=sys.stderr,
	)
	sys.exit(-1)
	try:
	with pg.connect(dbConnectionParam) as conn:
	with conn.cursor() as cur:
	try:
	with open(dbFile, "rb") as xml:
	# Pre-processing (dropping/creation of tables)
	six.print_("Pre-processing ...")
	if pre != "":
	cur.execute(pre)

The problem

This implies that if the user runs the command in a given schema twice, an error will occur because the following occurs

1. The user executes the script and specifies a given schema.
   1. The DROP TABLE is executed in the public schema.
   2. The CREATE TABLE is executed in the public schema.
   3. The table is moved to the given schema.
2. Again, The user executes the script and specifies a given schema
   1. The DROP TABLE is executed in the public schema even though the table exists in the specified schema because it was moved in the previous execution of the command.
   2. The CREATE TABLE is executed in the public schema.
   3. The table is moved from the public schema to the given schema which fails because the DROP TABLE didn't drop the table in the given schema but in the public schema.

Example

The following code blocks shows an example of this happening

$ python load_into_pg.py \
-f ../cardano.meta/Users.xml \
-t Users \
-n foo \
-d stackexchange

This will drop the Users table. Are you sure [y/n]?y
Pre-processing ...
Pre-processing took 0.0 seconds
Processing data ...
Table 'Users' processing took 0.0 seconds
Post processing ...
Post processing took 0.0 seconds

python load_into_pg.py \
-f ../cardano.meta/Users.xml \
-t Users \
-n foo \
-d stackexchange

This will drop the Users table. Are you sure [y/n]?y
Pre-processing ...
Pre-processing took 0.0 seconds
Processing data ...
Table 'Users' processing took 0.0 seconds
Post processing ...
Post processing took 0.0 seconds
Error in dealing with the database.
pg.Error (42P07): ERROR:  relation "users" already exists in schema "foo"

relation "users" already exists in schema "foo"