Multi Tenant Postgres Schemas

Databases
1

var row_settings as RowSet=app.gdb.SelectSQL(“SELECT * from “+app.db_schema+”.settings where refno_set=$1”,app.refno_set)

if row_settings.RowCount>0 then
row_settings.EditRow

errors and returns

The RecordSet cannot be edited because the uniqueness of its rows cannot be verified.

This only occurs when I have 2 or more schemas in a multi tenanted scenario. No problems if I have just 1 tenant in the schema.

Is this a known error or am I doing something wrong?

2

have this settings table a id field as primary key?

3

Hi Markus

Yes refno_set is the primary key

4

I’ve encounter some weird things with EditRow
I solved it by using ExecuteSQL with an UPDATE statement…

1 Like
5

Thanks Jean-Yves

That was going to be my next option but will mean a lot of code changes which I’m trying to avoid if possible

6

can you edit this row in pgAdmin?
i remember a bit there was a odd behavior, but it was years ago.

7

Yes. I use Navicat and that works fine. Going to sit down later with the good old fashioned UPDATE statement

8

using sqlexecute and update has resolved the issue so I am guessing editrow doesn’t work when you are using multiple schemas with the same tables

1 Like