Search code examples
databasepostgresqlinsertlimitrecord

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.

Solution

  • 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!