Question Re: Where clause from another table (SQL Remote) in Technology Q&A

Where clause from another table (SQL Remote)

Baron — Thu, 03 Nov 2022 08:54:21 GMT

Is it possible to define articles in a publication and let the where clause refer to columns from a foreign table?

For example I have these 2 tables:

create or replace table employees (
empid int, 
withprovision int, 
empname varchar(10), 
departid int);

create or replace table orders (
orderid int, 
empid int);

I want to let sql remote replicate only the employees who make provision along with their orders, also I want to refer the subscription by from a foreign table.

My scripts looks like this:

create publication dba.mypub (table employees where withprovision = 1 subscribe by departid, table orders where exists (select 1 from employees where withprovision = 1 and orders.empid = employees.empid subscribe by ????);

Table employees works exactly as I want (where clause and subscribe by do what I need).

Table orders: the where clause acts as where 1 = 2 and I don't know how to write the subscribe by...

Is this generally possible with SQL Remote?

Re: Where clause from another table (SQL Remote)

VolkerBarth — Thu, 03 Nov 2022 10:35:40 GMT

One approach is to use a join within your ~~WHERE~~SUBSCRIBE BY subquery, i.e. for say, table order_items, you would use something like

subscribe by
   (select e.departid
    from order_items oi
       inner join orders o on oi.order_id = o.id
       inner join employees e on o.empid = e.empid
    where e.withprovision = 1)

In other words: You can use the same subscribe by condition for all dependent tables but have to join them to the table the subscribe by relates to.

FWIW, this is also (and naturally in a better fashion) documented in the docs:

Disjoint Data Partitions.

Re: Where clause from another table (SQL Remote)

regdomaratzki — Thu, 03 Nov 2022 11:54:32 GMT

A subscribe by subquery should do the trick here.

CREATE TABLE cons.employees (
        empid       bigint NOT NULL DEFAULT GLOBAL AUTOINCREMENT(1000000),
        withprov    integer NOT NULL,
        empname     char(64) NOT NULL,
        deptid      integer NOT NULL,
        PRIMARY KEY (empid)
);

CREATE TABLE cons.orders (
        orderid     bigint NOT NULL DEFAULT GLOBAL AUTOINCREMENT(1000000),
        empid       bigint NOT NULL references cons.employees,
        textcol     char(64) NULL,
        PRIMARY KEY (orderid)
);

CREATE PUBLICATION cons.p1 (
        TABLE cons.employees where withprov=1 subscribe by deptid,
        TABLE cons.orders subscribe by ( select deptid 
`                                          from cons.employees 
                                          where cons.employees.empid = cons.orders.empid 
                                            and cons.employees.withprov = 1 ) 
);

With a subscribe by subquery, you now need to think about what happens when the parent table (employees in this case) is updated in such a way that will change what rows remote database will have in the child table (orders in this case). dbremote will take care of migrating rows on the employee table, because there is an operation in the transaction on that row, but there is no entry in the transaction log for the orders table. You need to write triggers with update publication statements that will insert or delete rows at remote sites on the orders table when you make changes to the withprov or deptid columns.

CREATE TRIGGER bu_deptid_employees BEFORE UPDATE OF deptid ORDER 1 ON cons.employees 
REFERENCING OLD AS old_row NEW AS new_row FOR EACH ROW
BEGIN
  UPDATE cons.orders PUBLICATION cons.p1
     OLD SUBSCRIBE BY old_row.deptid
     NEW SUBSCRIBE BY new_row.deptid
   WHERE cons.orders.empid = new_row.empid
     AND new_row.deptid = 1;
END;

CREATE TRIGGER bu_withprov_employee BEFORE UPDATE OF withprov ORDER 2 ON cons.employees 
REFERENCING OLD AS old_row NEW AS new_row FOR EACH ROW
BEGIN
  UPDATE cons.orders PUBLICATION cons.p1
     OLD SUBSCRIBE BY NULL
     NEW SUBSCRIBE BY new_row.deptid
   WHERE cons.orders.empid = new_row.empid
     AND new_row.withprov = 1;
  UPDATE cons.orders PUBLICATION cons.p1
     OLD SUBSCRIBE BY new_row.deptid
     NEW SUBSCRIBE BY NULL 
   WHERE cons.orders.empid = new_row.empid
     AND new_row.withprov = 0;     
END;

Disclaimer: These two triggers don't work well together when a single update changes both the withprov column and the deptid column. This should be a single trigger that ensures you don't insert or delete the same rows twice. I'll leave it as an exercise to the user to make a single trigger that does this properly.

Reg

Re: Where clause from another table (SQL Remote)

Baron — Fri, 04 Nov 2022 05:36:33 GMT

Actually I have problem even with table EMPLOYEES.

This table is included in a publication in both sides (Cons. and Remote), which means the table replicates in both directions, the creation of publication looks like this:

create publication rem.mypub (table employees);--on remote
create publication cons.mypub (table employees);--variant1 on cons.
create publication cons.mypub (table employees where 1=1);--variant2 on cons.

The problem which I have is as follows (only in case of variant2).

I insert a new employee on rem., then dbremote replicates it to cons., and the next dbremote deletes the same inserted employee on rem.!! but, one moment this is what the log of dbremote says, however the delete statement on rem. will be actually rolled back so that at the end the inserted employee remains in both cons. and rem. (i.e. the end result is OK, but here many questions!!)

Should I trust dbremote that the end result will be always ok? why the second dbremote tries to delete the employee on rem.? how can the rem. manage to roll this (false) delete statement back?
the where clause is simplified to 1=1, because it doesnt matter which condition is it.
in variant1 there is no problem at all, and the cons. knows that it should not return the same employee to remote (in order to avoid echo).

Re: Where clause from another table (SQL Remote)

VolkerBarth — Fri, 04 Nov 2022 05:50:10 GMT

I do not understand why your cons has to different publications/variants for the same table.

How do you create the remotes?

When using the default tool DBXTRACT, publications on cons and remotes should be identical - with the exception of the SUBSCRIBE BY clause - which would be used to send updates from the cons to the remotes only for those rows satisfying the clause... (And you would use that SUBSCRIBE BY clause to "partition data" for remotes, not a WHERE clause. AFAIK, a WHERE clause is focussed on generally excluding/including rows for replication but not to direct whether rows are published to particular remotes or not.)

Re: Where clause from another table (SQL Remote)

Baron — Fri, 04 Nov 2022 05:59:09 GMT

the mentioned two variants are not on the same time, they are just two different cases.

In my case I need the WHERE clause only on the cons., so the publication on rem. and cons. are not identical.

For the sake of simplicity I am posting here with the employees table as example.

Re: Where clause from another table (SQL Remote)

VolkerBarth — Fri, 04 Nov 2022 06:13:11 GMT

Do you use DEFAULT GLOBAL AUTOINCREMENT (or GUIDs) for primary keys? Or could it happen there is a PK conflict when several remotes enter new data?

By default, SQL Remote doesn't send operations back to the origin database, unless there's an UPDATE conflict.

Re: Where clause from another table (SQL Remote)

Baron — Fri, 04 Nov 2022 07:04:08 GMT

the PK in my case consists of 2 columns, one is sequential number (not auto increment but self calculated max+1) and the other is an identifier of the database.

Moreover, in my last test I was using only one rem., so there is no PK conflict.

When I embed my WHERE clause inside the SUBSCRIBE BY clause then I dont have the problem, something like this

create publication cons.mypub (table employees subscribe by (select departid from employees where 1=1));--variant3 on cons.

Re: Where clause from another table (SQL Remote)

VolkerBarth — Fri, 04 Nov 2022 08:00:56 GMT

self calculated max+1

I would very strongly suggest to use AUTOINCREMENTs here, unless you can absolutely make sure PKs are never re-used. PK re-use will usually happen when the maximum PK is deleted, so the next entry gets the same PK as the deleted row before... additionally, the max calculation also bears the risk of PK violations by two parallel select-max-and-insert transactions.

Re: Where clause from another table (SQL Remote)

regdomaratzki — Fri, 04 Nov 2022 09:54:39 GMT

This sounds like it’s working as designed, with one exception that I’ll address later in my response (*).

First, let’s consider the following situation with the employee table, whose schema is exactly the same at the consolidated and remote, but whose publication definitions are slightly different.

CREATE TABLE employees (
  empid    bigint NOT NULL DEFAULT GLOBAL AUTOINCREMENT,
  withprov integer NOT NULL,
  empname  char(64) NOT NULL,
  deptid   integer NOT NULL,
  PRIMARY KEY (empid)
);

SQL Remote definitions at Consolidated:

CREATE PUBLICATION p1 (TABLE employees WHERE withprov=1);
CREATE USER cons IDENTIFIED BY ‘sql';
GRANT PUBLISH TO cons;
CREATE USER rem1 IDENTIFIED BY ‘sql';
GRANT REMOTE TO rem1 TYPE FILE ADDRESS 'rem1';
CREATE SUBSCRIPTION TO p1 FOR rem1;
START SUBSCRIPTION TO p1 FOR rem1;

SQL Remote definitions at Remote:

CREATE PUBLICATION p1 (TABLE employees);
CREATE USER rem1 IDENTIFIED BY ‘sql';
GRANT PUBLISH TO rem1;
CREATE USER cons IDENTIFIED BY ‘sql';
GRANT CONSOLIDATE TO cons TYPE FILE ADDRESS 'cons';
CREATE SUBSCRIPTION TO p1 FOR cons;
START SUBSCRIPTION TO p1 FOR cons;

Next, let’s insert two rows in the remote database.

INSERT INTO employees VALUES (DEFAULT,0,’Reg’,0);
INSERT INTO employees VALUES (DEFAULT,1,’Volker’,0);
COMMIT;

When you run dbremote against rem1, both rows are sent to cons, since the publication at the remote simply says all rows on the employee table replicate. Dbremote runs on the consolidated, picks up the message from rem1, and happily inserts both rows. However, the row for employee ‘Reg’ has a value of withprov=0, and the publication definition in cons says that the rem1 remote database should only have rows where withprov=1, so it determines that rem1 should not have this row and sends a delete for the ‘Reg’ employee to rem1.

(*) The only thing I find odd about your post is that you say the delete on the employee table is rolled back when dbremote attempts to apply it at the remote database. I suspect this is because there is a foreign key relationship to the employee table at the remote, and there are child records associated with the row dbremote is trying to delete.

The key point here is that if a remote sends up a row to the consolidated and the consolidated determines via the where clause or subscribe by clause on the publication that the remote that sent the row should not have that row, a delete is sent back to the remote.

I’d be happy to be proven wrong, but I don’t believe this is related to how primary keys are defined at the consolidated and remote. Given the lack of information on why the delete may have been rolled back when sent down to the remote database, Volker and I both making educated guesses at what the problem might be.

Reg

Re: Where clause from another table (SQL Remote)

regdomaratzki — Fri, 04 Nov 2022 09:56:58 GMT

I think Volker intended to say :

I would very strongly suggest to use GLOBAL AUTOINCREMENTs here, unless you can absolutely make sure PKs are never re-used.

Re: Where clause from another table (SQL Remote)

VolkerBarth — Fri, 04 Nov 2022 11:22:17 GMT

While I certainly would very strongly suggest to use those GLOBAL AUTOINCREMENTs with SQL Remote, I had taken into account that Baron already seems to have a "database identifier", so I was only suggesting to alter the "sequential number" to be an always incrementing (non-global) AUTOINCREMENT at least, assuming changing from a composite two-field PK to a single PK would require to re-setup the complete SQL Remote setup...:

the PK in my case consists of 2 columns, one is sequential number (not auto increment but self calculated max+1) and the other is an identifier of the database.

But I guess I'm not sure whether the (non-global) DEFAULT AUTOINCREMENT would work sufficiently when different values from different databases would be inserted - that might drive the next default increment way higher than necessary for the current database...

Re: Where clause from another table (SQL Remote)

regdomaratzki — Fri, 04 Nov 2022 14:04:25 GMT

I'd missed that it was a two-column primary key. Sorry Volker.

Re: Where clause from another table (SQL Remote)

VolkerBarth — Fri, 04 Nov 2022 15:23:29 GMT

No need to worry, I'm fine, I got the provision in your sample:)

Re: Where clause from another table (SQL Remote)

Baron — Fri, 04 Nov 2022 16:58:18 GMT

Thank you Reg for the reply. Your assumption/explanation sounds very reasonable. The thing what you find odd is also clear to me, since my employees table has a child table (PK-FK relation), so that is not possible to delete the parent.

What I find odd: the phenomen of deleting rows on the rem. happens even if the where clause on cons. is 1=1.

I'll test it once more next week and write the results again her.

Re: Where clause from another table (SQL Remote)

Baron — Fri, 04 Nov 2022 17:01:26 GMT

Thanks Volker, but there is definitely no PK conflict/reuse

Re: Where clause from another table (SQL Remote)

Baron — Mon, 07 Nov 2022 04:25:07 GMT

Hello Reg, with your example my expression above ist not correct:

"...even if the where clause on cons. is 1=1."

I don't know why in my example was deleted even with 1=1

Your example is correct, and I get the same response as you expected, the first employee will be deleted on rem. (as you said, it is according to the design), but I need the following:

Employees with withprov=1 should be replicated among all other remotes, but the employees with withprov = 0 may NOT be deleted on the publisher remote. In other words, each rem. should have all employees inserted locally(regardless whether withprov = 0 or withprov = 1), in addition to all employees withprov=1 (regardless whether inserted locally or on other remotes)!!

With something like this I could solve it:

CREATE PUBLICATION p1 (TABLE employees subscribe by (select '1' from dummy where employees.withprov=1));
CREATE USER cons IDENTIFIED BY ‘sql';
GRANT PUBLISH TO cons;
CREATE USER rem1 IDENTIFIED BY ‘sql';
GRANT REMOTE TO rem1 TYPE FILE ADDRESS 'rem1';
CREATE SUBSCRIPTION TO p1('1') FOR rem1;
START SUBSCRIPTION TO p1('1') FOR rem1;

Re: Where clause from another table (SQL Remote)

VolkerBarth — Mon, 07 Nov 2022 05:38:09 GMT

A subscribe_by value of NULL or an empty string can be used (in confunction with the subscribe_by_remote option set to "On") to leave an entry at the remote it has been created at without publishing it to other remotes. This could do the trick here.

Re: Where clause from another table (SQL Remote)

regdomaratzki — Mon, 07 Nov 2022 12:26:12 GMT

If all rows on the employee table in the consolidated database replicate to all remote database regardless of the value of the withprov column in the table, then IMHO, this column should not be referenced in any way in the publication, either in the where clause or the subscribe by clause. Just let the whole table replicate in both directions.

If the rule is that at the remote database you cannot delete a row in the employee table when withprov=0, then you should implement that in a before delete trigger, and throw an exception if you determine that there is a row in the SYSREMOTEUSER where consolidate='Y' (i.e. we are at a remote database).

It sounds like you are trying to solve an issue with publication design that should be solved using another manner.

Reg

Re: Where clause from another table (SQL Remote)

regdomaratzki — Mon, 07 Nov 2022 12:45:39 GMT

I don't know why in my example was deleted even with 1=1

I would need to see the entire article clause in the publication for the employee table, as well as the data that was sent down as a delete to answer this question.