Postgres Can't Add Foreign Key Constraint
I have a table with about 220 million records :( and I need to add a foreign key constraint.
My command looks something like this:
ALTER TABLE events
ADD CONSTRAINT events_visitor_id_fkey
FOREIGN KEY (visitor_id)
REFERENCES visitors(id)
ON DELETE CASCADE;
It's been running for probably an hour now.
I ran this before hand:
set maintenance_work_mem='1GB';
What's the fastest way to do this, and about how long should it take. The table it references is only 25 million.
I'm running it on an RDS instance of db.r3.large (15 GB of RAM).
EDIT:
Just cancelled the command and got this:
ERROR: canceling statement due to user request
CONTEXT: SQL statement "SELECT fk."visitor_id" FROM ONLY "public"."events" fk LEFT OUTER JOIN ONLY "public"."visitors" pk ON ( pk."id" OPERATOR(pg_catalog.=) fk."visitor_id") WHERE pk."id" IS NULL AND (fk."visitor_id" IS NOT NULL)"
postgresql postgresql-9.3 foreign-key
asked Jan 8 '16 at 21:37
SamSam
2521311
bumped to the homepage by Community♦ 9 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a table with about 220 million records :( and I need to add a foreign key constraint.
My command looks something like this:
ALTER TABLE events
ADD CONSTRAINT events_visitor_id_fkey
FOREIGN KEY (visitor_id)
REFERENCES visitors(id)
ON DELETE CASCADE;
It's been running for probably an hour now.
I ran this before hand:
set maintenance_work_mem='1GB';
What's the fastest way to do this, and about how long should it take. The table it references is only 25 million.
I'm running it on an RDS instance of db.r3.large (15 GB of RAM).
EDIT:
Just cancelled the command and got this:
ERROR: canceling statement due to user request
CONTEXT: SQL statement "SELECT fk."visitor_id" FROM ONLY "public"."events" fk LEFT OUTER JOIN ONLY "public"."visitors" pk ON ( pk."id" OPERATOR(pg_catalog.=) fk."visitor_id") WHERE pk."id" IS NULL AND (fk."visitor_id" IS NOT NULL)"
postgresql postgresql-9.3 foreign-key
asked Jan 8 '16 at 21:37
SamSam
2521311
bumped to the homepage by Community♦ 9 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
As this doesn't write anything but just reads every row, this shouldn't take hours - maybe it's just waiting for a lock: wiki.postgresql.org/wiki/Lock_Monitoring
– a_horse_with_no_name
Jan 8 '16 at 21:51
@a_horse_with_no_name there doesn't appear to be any locks. The DB is a dump instance with no other connections and no other queries or processes being run.
– Sam
Jan 8 '16 at 21:59
I mean there doesn't appear to be any non granted locks.
– Sam
Jan 8 '16 at 22:08
add a comment |
I have a table with about 220 million records :( and I need to add a foreign key constraint.
My command looks something like this:
ALTER TABLE events
ADD CONSTRAINT events_visitor_id_fkey
FOREIGN KEY (visitor_id)
REFERENCES visitors(id)
ON DELETE CASCADE;
It's been running for probably an hour now.
I ran this before hand:
set maintenance_work_mem='1GB';
What's the fastest way to do this, and about how long should it take. The table it references is only 25 million.
I'm running it on an RDS instance of db.r3.large (15 GB of RAM).
EDIT:
Just cancelled the command and got this:
ERROR: canceling statement due to user request
CONTEXT: SQL statement "SELECT fk."visitor_id" FROM ONLY "public"."events" fk LEFT OUTER JOIN ONLY "public"."visitors" pk ON ( pk."id" OPERATOR(pg_catalog.=) fk."visitor_id") WHERE pk."id" IS NULL AND (fk."visitor_id" IS NOT NULL)"
postgresql postgresql-9.3 foreign-key
asked Jan 8 '16 at 21:37
SamSam
2521311
I have a table with about 220 million records :( and I need to add a foreign key constraint.
My command looks something like this:
ALTER TABLE events
ADD CONSTRAINT events_visitor_id_fkey
FOREIGN KEY (visitor_id)
REFERENCES visitors(id)
ON DELETE CASCADE;
It's been running for probably an hour now.
I ran this before hand:
set maintenance_work_mem='1GB';
What's the fastest way to do this, and about how long should it take. The table it references is only 25 million.
I'm running it on an RDS instance of db.r3.large (15 GB of RAM).
EDIT:
Just cancelled the command and got this:
ERROR: canceling statement due to user request
CONTEXT: SQL statement "SELECT fk."visitor_id" FROM ONLY "public"."events" fk LEFT OUTER JOIN ONLY "public"."visitors" pk ON ( pk."id" OPERATOR(pg_catalog.=) fk."visitor_id") WHERE pk."id" IS NULL AND (fk."visitor_id" IS NOT NULL)"
postgresql postgresql-9.3 foreign-key
postgresql postgresql-9.3 foreign-key
asked Jan 8 '16 at 21:37
SamSam
2521311
asked Jan 8 '16 at 21:37
SamSam
2521311
edited Jan 8 '16 at 22:32
Sam
asked Jan 8 '16 at 21:37
SamSam
2521311
asked Jan 8 '16 at 21:37
SamSam
2521311
asked Jan 8 '16 at 21:37
SamSam
2521311
2521311
bumped to the homepage by Community♦ 9 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 9 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
As this doesn't write anything but just reads every row, this shouldn't take hours - maybe it's just waiting for a lock: wiki.postgresql.org/wiki/Lock_Monitoring
– a_horse_with_no_name
Jan 8 '16 at 21:51
@a_horse_with_no_name there doesn't appear to be any locks. The DB is a dump instance with no other connections and no other queries or processes being run.
– Sam
Jan 8 '16 at 21:59
I mean there doesn't appear to be any non granted locks.
– Sam
Jan 8 '16 at 22:08
add a comment |
1
As this doesn't write anything but just reads every row, this shouldn't take hours - maybe it's just waiting for a lock: wiki.postgresql.org/wiki/Lock_Monitoring
– a_horse_with_no_name
Jan 8 '16 at 21:51
@a_horse_with_no_name there doesn't appear to be any locks. The DB is a dump instance with no other connections and no other queries or processes being run.
– Sam
Jan 8 '16 at 21:59
I mean there doesn't appear to be any non granted locks.
– Sam
Jan 8 '16 at 22:08
1
1
As this doesn't write anything but just reads every row, this shouldn't take hours - maybe it's just waiting for a lock: wiki.postgresql.org/wiki/Lock_Monitoring
– a_horse_with_no_name
Jan 8 '16 at 21:51
As this doesn't write anything but just reads every row, this shouldn't take hours - maybe it's just waiting for a lock: wiki.postgresql.org/wiki/Lock_Monitoring
– a_horse_with_no_name
Jan 8 '16 at 21:51
@a_horse_with_no_name there doesn't appear to be any locks. The DB is a dump instance with no other connections and no other queries or processes being run.
– Sam
Jan 8 '16 at 21:59
@a_horse_with_no_name there doesn't appear to be any locks. The DB is a dump instance with no other connections and no other queries or processes being run.
– Sam
Jan 8 '16 at 21:59
I mean there doesn't appear to be any non granted locks.
– Sam
Jan 8 '16 at 22:08
I mean there doesn't appear to be any non granted locks.
– Sam
Jan 8 '16 at 22:08
add a comment |
1 Answer
1
active
oldest
votes
I tried again and it ended up completing in around half an hour. Not sure what was going on before.
answered Jan 9 '16 at 3:10
SamSam
2521311
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f125702%2fpostgres-cant-add-foreign-key-constraint%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
I tried again and it ended up completing in around half an hour. Not sure what was going on before.
answered Jan 9 '16 at 3:10
SamSam
2521311
add a comment |
I tried again and it ended up completing in around half an hour. Not sure what was going on before.
answered Jan 9 '16 at 3:10
SamSam
2521311
add a comment |
I tried again and it ended up completing in around half an hour. Not sure what was going on before.
answered Jan 9 '16 at 3:10
SamSam
2521311
I tried again and it ended up completing in around half an hour. Not sure what was going on before.
answered Jan 9 '16 at 3:10
SamSam
2521311
answered Jan 9 '16 at 3:10
SamSam
2521311
answered Jan 9 '16 at 3:10
SamSam
2521311
answered Jan 9 '16 at 3:10
SamSam
2521311
2521311
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f125702%2fpostgres-cant-add-foreign-key-constraint%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
1
As this doesn't write anything but just reads every row, this shouldn't take hours - maybe it's just waiting for a lock: wiki.postgresql.org/wiki/Lock_Monitoring
– a_horse_with_no_name
Jan 8 '16 at 21:51
@a_horse_with_no_name there doesn't appear to be any locks. The DB is a dump instance with no other connections and no other queries or processes being run.
– Sam
Jan 8 '16 at 21:59
I mean there doesn't appear to be any non granted locks.
– Sam
Jan 8 '16 at 22:08