View Issue Details

IDProjectCategoryView StatusLast Update
0002322SymmetricDSImprovementpublic2020-09-04 13:26
Reporterrotten Assigned To 
Prioritynormal 
Status newResolutionopen 
Product Version3.7.19 
Summary0002322: need to be able to force sym_tables to a schema
DescriptionNormally I get SymmetricDS to build the sym_* tables in its own schema in my PostgreSQL instances by setting the default search_path for the SymmetricDS database user to just the one schema.

Recently however, I had to add triggers to the tables that SymmetricDS was updating. The functions for those triggers were in a different schema, so I added that second schema (public) to the default search_path for the SymmetricDS database user. The trigger functions are shared with several applications in addition to SymmetricDS.


Everything was fine. ... Until I upgraded to the latest release. The upgrade built a whole new set of tables in the new schema rather than in the one where it has been running these past few years.

I would like to be able to force SymmetricDS to put its tables in a specific schema rather than having it randomly choose a schema from its search_path.



Steps To Reproduce1) # alter user symmds set search_path = symmetricds;

2) Install symmetricds with the symmds user.

3) # alter user symmds set search_path = public,symmetricds;

4) Upgrade symmetricds.

It also seems to put everything in public if I set the search path to:

# alter user symmds set search_path = symmetricds,public;

Additional Information
For now I'm leaving the sym_* tables in public because I need SymmetricDS to be able to run the triggers I added last month as-is.

When this feature is available I'll re-install SymmetricDS on the nodes where it got messed up.

I will also research ways to run my new functions from any schema, or maybe I'll force the schema in the trigger definitions, so that "symmds" doesn't have to have public on its search path to be able to do insert/update/delete on those specific tables.

Tagsdialect: postgresql, install

Relationships

related to 0004524 assignedelong Add support for PostgreSQL search_path 

Activities

tmdoit

2020-01-05 19:38

reporter   ~0001671

On MSSQL I created user with schema "sym", sym_tables are created in defined schema but triggers references to dbo.sym_data, same with functions. which are searched in dbo schema.

tmdoit

2020-01-06 21:13

reporter   ~0001673

According to my previous comment.
1. Triggers reference properly to the default user schema ie. DBName.sym.sym_data
2. SymmetricDS creates properly tables in default schema opposite to functions, here is described problem: https://sourceforge.net/p/symmetricds/discussion/739236/thread/b04441aa30/

Issue History

Date Modified Username Field Change
2015-06-18 16:35 rotten New Issue
2019-04-23 13:20 elong Tag Attached: install
2019-04-23 13:21 elong Tag Attached: postgresql
2019-04-23 16:50 admin Tag Renamed postgresql => dialect: postgresql
2020-01-05 19:38 tmdoit Note Added: 0001671
2020-01-06 21:13 tmdoit Note Added: 0001673
2020-09-04 13:26 elong Relationship added related to 0004524