CODEWITHSUNDEEP
databasepostgresqlinsertlimitrecord
Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

Postgresql inserts stop at random number of records

I am developing a test application that requires me to insert 1 million records in a Postgresql database but at random points the insert stops and if I try to restart the insertion process, the application refuses to populate the table with more records. I've read that databases have a size cap, which is around 4 Gb, but I'm sure my database didn't even come close to this value.

So, what other reasons could be for why insertion stopped?

It happened a few times, once capping at 170872 records, another time at 25730 records.

I know the question might sound silly but I can't find any other reasons for why it stops inserting.

Thanks in advance!

JUST A QUICK UPDATE:

Indeed the problem isn't the database cap, here are the official figures for PostgreSQL:

- Maximum Database Size                 Unlimited
- Maximum Table Size                    32 TB
- Maximum Row Size                      1.6 TB
- Maximum Field Size                    1 GB
- Maximum Rows per Table                Unlimited
- Maximum Columns per Table             250 - 1600 depending on column types
- Maximum Indexes per Table             Unlimited

Update:

Error in log file:

2012-03-26 12:30:12 EEST WARNING: there is no transaction in progress

So I'm looking up for an answer that fits this issue. If you can give any hints I would be very grateful.

Upvotes: 2

Views: 1186

Answers (4)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

I found out what was the problem with my insert command, and although it might seem funny it's one of those things you never thought could go wrong.

My application is developed in Django and has a command that simply calls for the file that does the insert operations into the tables.

i.e. in the command line terminal I just write:

time python manage.py populate_sql

The reason for which I use the time command is because I want to see how long it takes for the insertion to execute. Well, the problem was here. That time command issued an error, a Out of memory error which stopped the insertion into the database. I found this little code while running the command with the --verbose option which lets you see all the details of the command.

I would like to thank you all for your answers, for the things that I have learned from them and for the time you used trying to help me.

EDIT:

If you have a Django application in which you make a lot of operations with the database, then my advice to you is to set the 'DEBUG' variable in settings.py to 'FALSE' because it eats up a lot of your memory in time.

So,

DEBUG = False

And in the end, thank you again for the support Richard Huxton!

Upvotes: 0

Richard Huxton
Richard Huxton

Reputation: 22893

OK - if you're getting "no transaction in progress" that means you're issuing a commit/rollback but outside of an explicit transaction. If you don't issue a "BEGIN" then each statement gets its own transaction.

This is unlikely to be the cause of the problem.

Something is causing the inserts to stop, and you've still not told us what. You said earlier you weren't getting any errors inside the application. That shouldn't be possible if PostgreSQL is returning an error you should be picking it up in the application.

It's difficult to be more helpful without more accurate information. Every statement you send to PostgreSQL will return a status code. If you get an error inside a multi-statement transaction then all the statements in that transaction will be rolled back. You've either got some confused transaction control in the application or it is falling down for some other reason.

Upvotes: 1

wildplasser
wildplasser

Reputation: 44250

One of the possibilities is that the OP is using ssl, and the ssl_renegotiation_limit is reached. In any case: set the log_connections / log_disconnections to "On" and check the logfile.

Upvotes: 0

Richard Huxton
Richard Huxton

Reputation: 22893

I've read that databases have a size cap, which is around 4 Gb

I rather doubt that. It's certainly not true about PostgreSQL.

[...]at random points the insert stops and if I try to restart the insertion process, the application refuses to populate the table with more records

Again, I'm afraid I doubt this. Unless your application has become self aware it's refusing to do nothing. It might be crashing, or locking, or waiting for something to happen though.

I know the question might sound silly but I can't find any other reasons for why it stops inserting.

I don't think you've looked hard enough. Obvious things to check:

  1. Are you getting any errors in the PostgreSQL logs?
  2. If not, are you sure you're logging errors? Issue a bad query to check.
  3. Are you getting any errors in the application?
  4. If not,. are you sure you're checking? Again, check
  5. What is/are the computer(s) up to? How much CPU/RAM/Disk IO is in use? Any unusual activity?
  6. Any unusual locks begin taken (check the pg_locks view).

If you asked the question having checked the above then there's someone who'll be able to help. Probably though, you'll figure it out yourself once you've got the facts in front of you.

Upvotes: 4

Related Questions