Not using a built in command, but...
If an UPDATE
really can satisfy your requirements as your title implies, then no inserts should be necessary and the primary key must be in sync between the databases.
Your requirements don't allow remote updates, but if you can remotely query the database, then you can join the local table to the remote table to create the statements that will need to be run the remote system. Here is a demonstration:
drop table t1;
drop table t2;
create table t1 as (select level c1, level c2 from dual connect by level <=7);
create table t2 as (select * from t1);
update t2 set c2 = null where c1 in (3,6);
select * from t1;
select * from t2;
select 'UPDATE t2 SET c2 = ' || t1.c2 || ' WHERE c1=' || t1.c1 || ';' sql from t1
join t2 on t1.c1 = t2.c1
where t1.c2 <> t2.c2 OR t2.c2 is null;
If you cannot query the remote database, you can update every row like this:
drop table t1;
create table t1 as (select level c1, level c2 from dual connect by level <=7);
select 'UPDATE t2 SET c2 = ' || t1.c2 || ' WHERE c1=' || t1.c1 || ';' sql from t1;
There are a few problems with your tables. I'll try to address the foreign keys first, since you question asked about them :)
But before that, we should realize that the two sets of tables (the first three you created and the second set, which you created after dropping the first set) are the same. Of course, the definition of Table3
in your second attempt has syntax and logical errors, but the basic idea is:
CREATE TABLE table3 (
"ID" bigint NOT NULL DEFAULT '0',
"DataID" bigint DEFAULT NULL,
"Address" numeric(20) DEFAULT NULL,
"Data" bigint DEFAULT NULL,
PRIMARY KEY ("ID"),
FOREIGN KEY ("DataID") REFERENCES Table1("DataID") on delete cascade on update cascade,
FOREIGN KEY ("Address") REFERENCES Table2("Address") on delete cascade on update cascade
);
This definition tell PostgreSQL roughly the following: "Create a table with four columns, one will be the primary key (PK), the others can be NULL
. If a new row is inserted, check DataID
and Address
: if they contain a non-NULL value (say 27856), then check Table1
for DataID
˙and Table2
for Address
. If there is no such value in those tables, then return an error." This last point which you've seen first:
ERROR: insert or update on table "Table3" violates foreign key constraint
"Table3_DataID_fkey" DETAIL: Key (DataID)=(27856) is not present in table "Table1".
So simple: if there is no row in Table1
where DataID = 27856
, then you can't insert that row into Table3
.
If you need that row, you should first insert a row into Table1
with DataID = 27856
, and only then try to insert into Table3
. If this seems to you not what you want, please describe in a few sentences what you want to achieve, and we can help with a good design.
And now about the other problems.
You define your PKs as
CREATE all_your_tables (
first_column NOT NULL DEFAULT '0',
[...]
PRIMARY KEY ("ID"),
A primary key means that all the items in it are different from each other, that is, the values are UNIQUE
. If you give a static DEFAULT
(like '0'
) to a UNIQUE
column, you will experience bad surprises all the time. This is what you got in your third error message.
Furthermore, '0'
means a text string, but not a number (bigint
or numeric
in your case). Use simply 0
instead (or don't use it at all, as I written above).
And a last point (I may be wrong here): in Table2
, your Address
field is set to numeric(20)
. At the same time, it is the PK of the table. The column name and the data type suggests that this address can change in the future. If this is true, than it is a very bad choice for a PK. Think about the following scenario: you have an address '1234567890454', which has a child in Table3
like
ID DataID Address Data
123 3216547 1234567890454 654897564134569
Now that address happens to change to something other. How do you make your child row in Table3
follow its parent to the new address? (There are solutions for this, but can cause much confusion.) If this is your case, add an ID column to your table, which will not contain any information from the real world, it will simply serve as an identification value (that is, ID) for an address.
Best Answer
As the documentation and the error say, you have to define the foreign table with the
key
column option for all columns that belong to the primary key. Without knowing what the foreign key is, it is impossible to identify the row in the remote table that should be updated or deleted (forINSERT
, that is not necessary).Here is an example for a table definition that sets the
key
option:In this example
id
is the primary key.You can use the
IMPORT FOREIGN SCHEMA
statement to have oracle_fdw define foreign tables for you, which will automatically set thekey
option as appropriate.The ORA-08177 is a different affair. It can be that oracle_fdw has to scan an Oracle table several times during a single statement (for example, if the foreign table scan in on the inner side of a nested loop join), and oracle_fdw has to ensure that the data seen during these scans are consistent.
That would not be guaranteed with the default
READ COMMITTED
transaction isolation level, so oracle_fdw has to useSERIALIZABLE
(which is not serializable, but guarantees read stability).Now Oracle has done a seriously bad job implementing this so-called
SERIALIZABLE
isolation level. It is not downright buggy, because according to the book it is always allowed to throw a serialization error in a serializable transaction, but Oracle hat interpreted this quite freely and throws a serialization error whenever implementing read stability correctly would have proven too cumbersome.For example, if a concurrent
INSERT
causes an index page split, a serializable transaction that tries toINSERT
at the same time will receive a serialization error. This is of course silly. Similarly, any data modification on a table that has received a data modification from a concurrent transaction will result in a serialization error, even if they don't touch the same rows.Your response should be, like with all serialization errors, to retry the transaction and hope for more luck next time.
Because this is a common problem, and the cries for a different solution have become so loud, I have recently pulled a patch that enables you to set an option
isolation_level
on the foreign server. You can set this option toread_committed
to change the isolation level to the unsafe valueREAD COMMITTED
.This is not in a released version yet, but you can safely use Git HEAD if you want to try it. Use this unsafe option at your own risk. For
INSERT
s, nothing can go wrong, but you are not safe from inconsistent query results in the presence of concurrent data modifications.A word of caution: while bulk data modification works with oracle_fdw, it is not very efficient, because there is a round trip between PostgreSQL and Oracle for each affected row. The reason for this is in the foreign data wrapper API. It would be difficult to work around that, and I don't think bulk data modifications to be such an important use case.