Foreign table inserts don't use the remote sequence
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a set of applications accessing two different PostgreSQL 9.6 DBs on the same server. Due to some application limitations, one application accesses a handful of tables via FDW in one DB to the other.
Something like this:DB1.fdw_table_a -> DB2.table_a
fdw_table_a is only used for inserts of log data. This table has an id column, which is a bigint sequence. The sequence exists in DB1 (on the foreign table) and in DB2 (the "real" table). This works as it should and all is well.
Now there's a need to have another application (again with limited access capabilities) perform inserts into the "real" table, DB2.table_a. In testing, I can see some inconsistencies in the id column, but no obvious issues have appeared.
I can see in the customer-facing environments that the DB1 FDW sequence is used as expected, but when inserts start directly on the DB2 'real' table, that sequence will start at 1 (as it has never been used).
Are there other things we should be considering in this environment?
Are there some issues that could arise from overlap in these two sequences inserting into the table?
postgresql postgresql-9.6 postgres-fdw
edited Nov 18 '18 at 12:54
Laurenz Albe
53.3k103052
asked Nov 16 '18 at 19:46
EddEdd
534
add a comment |
I have a set of applications accessing two different PostgreSQL 9.6 DBs on the same server. Due to some application limitations, one application accesses a handful of tables via FDW in one DB to the other.
Something like this:DB1.fdw_table_a -> DB2.table_a
fdw_table_a is only used for inserts of log data. This table has an id column, which is a bigint sequence. The sequence exists in DB1 (on the foreign table) and in DB2 (the "real" table). This works as it should and all is well.
Now there's a need to have another application (again with limited access capabilities) perform inserts into the "real" table, DB2.table_a. In testing, I can see some inconsistencies in the id column, but no obvious issues have appeared.
I can see in the customer-facing environments that the DB1 FDW sequence is used as expected, but when inserts start directly on the DB2 'real' table, that sequence will start at 1 (as it has never been used).
Are there other things we should be considering in this environment?
Are there some issues that could arise from overlap in these two sequences inserting into the table?
postgresql postgresql-9.6 postgres-fdw
edited Nov 18 '18 at 12:54
Laurenz Albe
53.3k103052
asked Nov 16 '18 at 19:46
EddEdd
534
add a comment |
I have a set of applications accessing two different PostgreSQL 9.6 DBs on the same server. Due to some application limitations, one application accesses a handful of tables via FDW in one DB to the other.
Something like this:DB1.fdw_table_a -> DB2.table_a
fdw_table_a is only used for inserts of log data. This table has an id column, which is a bigint sequence. The sequence exists in DB1 (on the foreign table) and in DB2 (the "real" table). This works as it should and all is well.
Now there's a need to have another application (again with limited access capabilities) perform inserts into the "real" table, DB2.table_a. In testing, I can see some inconsistencies in the id column, but no obvious issues have appeared.
I can see in the customer-facing environments that the DB1 FDW sequence is used as expected, but when inserts start directly on the DB2 'real' table, that sequence will start at 1 (as it has never been used).
Are there other things we should be considering in this environment?
Are there some issues that could arise from overlap in these two sequences inserting into the table?
postgresql postgresql-9.6 postgres-fdw
edited Nov 18 '18 at 12:54
Laurenz Albe
53.3k103052
asked Nov 16 '18 at 19:46
EddEdd
534
I have a set of applications accessing two different PostgreSQL 9.6 DBs on the same server. Due to some application limitations, one application accesses a handful of tables via FDW in one DB to the other.
Something like this:DB1.fdw_table_a -> DB2.table_a
fdw_table_a is only used for inserts of log data. This table has an id column, which is a bigint sequence. The sequence exists in DB1 (on the foreign table) and in DB2 (the "real" table). This works as it should and all is well.
Now there's a need to have another application (again with limited access capabilities) perform inserts into the "real" table, DB2.table_a. In testing, I can see some inconsistencies in the id column, but no obvious issues have appeared.
I can see in the customer-facing environments that the DB1 FDW sequence is used as expected, but when inserts start directly on the DB2 'real' table, that sequence will start at 1 (as it has never been used).
Are there other things we should be considering in this environment?
Are there some issues that could arise from overlap in these two sequences inserting into the table?
postgresql postgresql-9.6 postgres-fdw
postgresql postgresql-9.6 postgres-fdw
edited Nov 18 '18 at 12:54
Laurenz Albe
53.3k103052
asked Nov 16 '18 at 19:46
EddEdd
534
edited Nov 18 '18 at 12:54
Laurenz Albe
53.3k103052
asked Nov 16 '18 at 19:46
EddEdd
534
edited Nov 18 '18 at 12:54
Laurenz Albe
53.3k103052
edited Nov 18 '18 at 12:54
Laurenz Albe
53.3k103052
edited Nov 18 '18 at 12:54
Laurenz Albe
53.3k103052
53.3k103052
asked Nov 16 '18 at 19:46
EddEdd
534
asked Nov 16 '18 at 19:46
EddEdd
534
asked Nov 16 '18 at 19:46
EddEdd
534
534
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The sequence only gets used if you omit the id column in the INSERT statement. But postgres_fdw will never omit a column, as you can see from the execution plan.
One way to solve the problem is to use a foreign table that does not contain the id column. Then any insert into that foreign table will use the sequence to populate that column.
answered Nov 18 '18 at 12:51
Laurenz AlbeLaurenz Albe
53.3k103052
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53344416%2fforeign-table-inserts-dont-use-the-remote-sequence%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The sequence only gets used if you omit the id column in the INSERT statement. But postgres_fdw will never omit a column, as you can see from the execution plan.
One way to solve the problem is to use a foreign table that does not contain the id column. Then any insert into that foreign table will use the sequence to populate that column.
answered Nov 18 '18 at 12:51
Laurenz AlbeLaurenz Albe
53.3k103052
add a comment |
The sequence only gets used if you omit the id column in the INSERT statement. But postgres_fdw will never omit a column, as you can see from the execution plan.
One way to solve the problem is to use a foreign table that does not contain the id column. Then any insert into that foreign table will use the sequence to populate that column.
answered Nov 18 '18 at 12:51
Laurenz AlbeLaurenz Albe
53.3k103052
add a comment |
The sequence only gets used if you omit the id column in the INSERT statement. But postgres_fdw will never omit a column, as you can see from the execution plan.
One way to solve the problem is to use a foreign table that does not contain the id column. Then any insert into that foreign table will use the sequence to populate that column.
answered Nov 18 '18 at 12:51
Laurenz AlbeLaurenz Albe
53.3k103052
The sequence only gets used if you omit the id column in the INSERT statement. But postgres_fdw will never omit a column, as you can see from the execution plan.
One way to solve the problem is to use a foreign table that does not contain the id column. Then any insert into that foreign table will use the sequence to populate that column.
answered Nov 18 '18 at 12:51
Laurenz AlbeLaurenz Albe
53.3k103052
answered Nov 18 '18 at 12:51
Laurenz AlbeLaurenz Albe
53.3k103052
answered Nov 18 '18 at 12:51
Laurenz AlbeLaurenz Albe
53.3k103052
answered Nov 18 '18 at 12:51
Laurenz AlbeLaurenz Albe
53.3k103052
53.3k103052
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53344416%2fforeign-table-inserts-dont-use-the-remote-sequence%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
