1

I am trying to do what should be a pretty straightforward insert statement in a postgres database. It is not working, but it's also not erroring out, so I don't know how to troubleshoot. This is the statement:

INSERT INTO my_table (col1, col2) select col1,col2 FROM my_table_temp;

There are around 200m entries in the temp table, and 50m entries in my_table. The temp table has no index or constraints, but both columns in my_table have btree indexes, and col1 has a foreign key constraint.

I ran the first query for about 20 days. Last time I tried a similar insert of around 50m, it took 3 days, so I expected it to take a while, but not a month. Moreover, my_table isn't getting longer. Queried 1 day apart, the following produces the same exact number.

select count(*) from my_table;

So it isn't inserting at all. But it also didn't error out. And looking at system resource usage, it doesn't seem to be doing much of anything at all, the process isn't drawing resources.

Looking at other running queries, nothing else that I have permissions to view is touching either table, and I'm the only one who uses them.

I'm not sure how to troubleshoot since there's no error. It's just not doing anything. Any thoughts about things that might be going wrong, or things to check, would be very helpful.

Improve this question
20
  • 1
    You may be hitting issues with locking due to your btree index Commented Jan 10, 2018 at 16:51
  • Thanks, I wasn't aware that was an issue. Each key in the btree references about 5k rows, so that may be a problem. Do you know of a way to check whether that locking is occurring? I can also try reindexing, but that'll take a while to check. Though probably not a whole month! Commented Jan 10, 2018 at 16:56
  • 1
    I'm not 100% sure that it's your issue, but it seems like a good place to start. Check out this link for some ideas on how to see locks on tables. Commented Jan 10, 2018 at 16:58
  • 1
    Your tag says "postgresql" but the comments above are geared towards Oracle, so I querying the v$session views aren't going to be of much use to you. As far as your INSERT goes, are you sure it is being committed? If the statement is rolled back, you'll not get an error, nor will you get any data inserted. Are you using the psql client, or a GUI? Commented Jan 10, 2018 at 17:08
  • 1
    When you query pg_stat_activity, are any of the active queries in a blocked state? It is remarkable to me that anything could take that long, given the number of rows you're dealing with. If you run EXPLAIN INSERT ... Is the SELECT from the temp table doing a full table scan? Not that that would account for 20 days of execution. Commented Jan 10, 2018 at 18:59

5 Answers 5

Reset to default
2

For the sake of anyone stumbling onto this question in the future:

After a lengthy discussion (see linked discussion from the comments above), the issue turned out to be related to psycopg2 buffering the query in memory.

Another useful note: inserting into a table with indices is slow, so it can help to remove them before bulk loads, and then add them again after.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

In my case it was a TRIGGER on the same table I was updating and it failed without errors.

Deactivated the trigger and the update worked flawlessly.

Improve this answer

1 Comment

this is what happened to me as well, anyone know where to go to see any logs associated with triggers that failed?
0

in my case it was date format issue. i commented date attribute before interting to DB and it worked.

Improve this answer

Comments

0

Try to use the semicolon at the end of your query to get the debug information. In my case case it was failing because it was not able to find the end of insert statement.

Improve this answer

Comments

0

Did not find the root cause. Ended up creating a temp table:


CREATE TEMP TABLE insert_data AS
SELECT ...
FROM ...;

INSERT INTO my_table (...)
SELECT *
FROM insert_data;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.