Bootstrap fails when objects have upper case characters in their names.
footballnotsoccer opened this issue · 1 comments
footballnotsoccer commented
PGSync version: 2.5.0
Postgres version: 14.7
Elasticsearch version: 7.17.12
Redis version: 7.0.12
Python version: 3.10
Problem Description:
When Postgres objects (schema, tables, columns) have mixed case values such as HomeAddress
, bootstrap returns a ERROR:pgsync.base: Exception (psycopg2.errors.InvalidSchemaName) schema "users" does not exist
error. I have a schema Users
, but I do not reference that schema in the schema.
When referencing mixed case objects in SQL, it is necessary to surround those objects with double quotes.
Error Message (if any):
(.venv2) ubuntu@dev:$ bootstrap --verbose --config users-draft.json
pg_settings:
SELECT setting
FROM pg_settings
WHERE name = %(name_1)s
-------------------------------------------------------------------------------
pg_settings:
SELECT setting
FROM pg_settings
WHERE name = %(name_1)s
-------------------------------------------------------------------------------
replication_slots:
SELECT *
FROM PG_REPLICATION_SLOTS
WHERE slot_name = %(slot_name_1)s
AND slot_type = %(slot_type_1)s
AND plugin = %(plugin_1)s
-------------------------------------------------------------------------------
create_replication_slot:
SELECT *
FROM PG_CREATE_LOGICAL_REPLICATION_SLOT(%(PG_CREATE_LOGICAL_REPLICATION_SLOT_1)s, %(PG_CREATE_LOGICAL_REPLICATION_SLOT_2)s)
-------------------------------------------------------------------------------
replication_slots:
SELECT *
FROM PG_REPLICATION_SLOTS
WHERE slot_name = %(slot_name_1)s
AND slot_type = %(slot_type_1)s
AND plugin = %(plugin_1)s
-------------------------------------------------------------------------------
drop_replication_slot:
SELECT *
FROM PG_DROP_REPLICATION_SLOT(%(PG_DROP_REPLICATION_SLOT_1)s)
-------------------------------------------------------------------------------
pg_settings:
SELECT setting
FROM pg_settings
WHERE name = %(name_1)s
-------------------------------------------------------------------------------
- UsersTempCopy.Owner
replication_slots:
SELECT *
FROM PG_REPLICATION_SLOTS
WHERE slot_name = %(slot_name_1)s
AND slot_type = %(slot_type_1)s
AND plugin = %(plugin_1)s
-------------------------------------------------------------------------------
drop_replication_slot:
SELECT *
FROM PG_DROP_REPLICATION_SLOT(%(PG_DROP_REPLICATION_SLOT_1)s)
-------------------------------------------------------------------------------
2023-08-11 23:04:26.916:ERROR:pgsync.base: Exception (psycopg2.errors.InvalidSchemaName) schema "users" does not exist
[SQL:
CREATE OR REPLACE FUNCTION Users.table_notify() RETURNS TRIGGER AS $$
DECLARE
channel TEXT;
old_row JSON;
new_row JSON;
notification JSON;
xmin BIGINT;
_indices TEXT [];
_primary_keys TEXT [];
_foreign_keys TEXT [];
BEGIN
-- database is also the channel name.
channel := CURRENT_DATABASE();
IF TG_OP = 'DELETE' THEN
SELECT primary_keys, indices
INTO _primary_keys, _indices
FROM Users._view
WHERE table_name = TG_TABLE_NAME;
old_row = ROW_TO_JSON(OLD);
old_row := (
SELECT JSONB_OBJECT_AGG(key, value)
FROM JSON_EACH(old_row)
WHERE key = ANY(_primary_keys)
);
xmin := OLD.xmin;
ELSE
IF TG_OP <> 'TRUNCATE' THEN
SELECT primary_keys, foreign_keys, indices
INTO _primary_keys, _foreign_keys, _indices
FROM Users._view
WHERE table_name = TG_TABLE_NAME;
new_row = ROW_TO_JSON(NEW);
new_row := (
SELECT JSONB_OBJECT_AGG(key, value)
FROM JSON_EACH(new_row)
WHERE key = ANY(_primary_keys || _foreign_keys)
);
IF TG_OP = 'UPDATE' THEN
old_row = ROW_TO_JSON(OLD);
old_row := (
SELECT JSONB_OBJECT_AGG(key, value)
FROM JSON_EACH(old_row)
WHERE key = ANY(_primary_keys || _foreign_keys)
);
END IF;
xmin := NEW.xmin;
END IF;
END IF;
-- construct the notification as a JSON object.
notification = JSON_BUILD_OBJECT(
'xmin', xmin,
'new', new_row,
'old', old_row,
'indices', _indices,
'tg_op', TG_OP,
'table', TG_TABLE_NAME,
'schema', TG_TABLE_SCHEMA
);
-- Notify/Listen updates occur asynchronously,
-- so this doesn't block the Postgres trigger procedure.
PERFORM PG_NOTIFY(channel, notification::TEXT);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
]
[parameters: (None,)]
(Background on this error at: https://sqlalche.me/e/14/f405)
Traceback (most recent call last):
File "/home/ubuntu/mp737/.venv2/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/home/ubuntu/mp737/.venv2/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.InvalidSchemaName: schema "users" does not exist
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/ubuntu/mp737/.venv2/lib/python3.10/site-packages/pgsync/base.py", line 1000, in pg_execute
conn.execute(statement, values)
File "/home/ubuntu/mp737/.venv2/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1365, in execute
return self._exec_driver_sql(
File "/home/ubuntu/mp737/.venv2/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1669, in _exec_driver_sql
ret = self._execute_context(
File "/home/ubuntu/mp737/.venv2/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
self._handle_dbapi_exception(
File "/home/ubuntu/mp737/.venv2/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
util.raise_(
File "/home/ubuntu/mp737/.venv2/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
raise exception
File "/home/ubuntu/mp737/.venv2/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/home/ubuntu/mp737/.venv2/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidSchemaName) schema "users" does not exist
toluaina commented
ok, this is a bug. I will fix