0

We have a database that is just over 200 gb in size, with around 430 million rows, a primary key and a spatial index. Every few weeks we get an update which involves around 3 million rows, deleting some old features and updating new ones. This is done offline. We also have a history table, which stores the accumulated deletions from each update. This table is currently around 20 gb and 40 has million rows. Essentially, we first insert into the history table anyting that is marked as to delete in the change table, then delete everything from the main table, that is either to delete or new, and then insert into the new table all the changed records (all offline, to be clear).

If I run this in a single procedure/function a table rewrite seems to be triggered, as I have come back after a few hours and found it failed due to using up the available disk space, around 100 gb. If I run it line by line in a psql console, no such problem. As there are no DDL statements and the total number of changed rows is vastly smaller than the total database size, I am puzzled as to what might be causing this amount of temporary disk usage, as the total space required to record the entire change as one transaction would seem to be much less.

Is there some rule of thumb for this or a simple diagnostic query that I can run to see what is happening?

migrated from stackoverflow.com May 2 '14 at 1:37

This question came from our site for professional and enthusiast programmers.

  • What do you think you mean by "a table rewrite"? Can you reproduce the problem by wrapping the individual steps in a single transaction? – Richard Huxton May 1 '14 at 14:39
  • The problem only arises when it is in a single transaction. If I run it line by line, it does not occur. By table rewrite I mean the table gets rewritten such as when you add a column or change a datatype on a column to something smaller. I am assuming the table is being rewritten because of the size of the changes. – John Powell May 1 '14 at 14:42
  • No - that's not what is happening. Did you try it as a simple script wrapped in a transaction or are you just guessing the behaviour will be the same? – Richard Huxton May 1 '14 at 15:55
  • @RichardHuxton. All statements within a Postgres procedure are implicitly run as a single transaction, are they not. What do you mean by a script wrapped in a transaction? – John Powell May 1 '14 at 16:02
  • What do you mean by "offline"? Does that just mean only one person is connected at that time? – jjanes May 1 '14 at 16:05
2

I'd say the difference is that your PL/PgSQL procedure runs in a single transaction.

If you run line by line in psql, unless you explicitly BEGIN and COMMIT, you're running in individual transactions. This can be quite a lot slower, but it also means that autoVACUUM can come along to free and reuse deleted rows, so subsequent UPDATEs can write new row versions into those freed spaces.

If you're in a single transaction, the system has to keep the old row versions around because it needs them in case you ROLLBACK the transaction or hit an error.

So you can't really do what you want in PL/pgSQL alone, unless you're willing to use hacks like dblink. You need to batch your work into a series of smaller transactions and since PostgreSQL doesn't yet support autonomous commit from within a PL/PgSQL function, that means dblink or an external client.

  • Thanks. Is it envisaged that it will be possible to have commits from within a plpgsql function in a future release of Postgres. It would be a useful feature, in certain cases, where transactional integrity is not important. – John Powell May 8 '14 at 8:15
  • Autonomous transactions are a strongly desired feature, yes. It's highly likely that they'll at least be supported within top-level PL/PgSQL procedures eventually; within functions called from other SQL is less sure. There's been recent work on implementing them and everyone knows they're desirable and important - they're also just very, very complicated to get right in PostgreSQL's architecture. – Craig Ringer May 9 '14 at 2:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.