Re: large numbers of inserts out of memory strategy

On 2017-11-30 14:27:58 -0600, Ted Toth wrote:
> On Thu, Nov 30, 2017 at 11:40 AM, Peter J. Holzer <hjp-pgsql(at)hjp(dot)at> wrote:
> > On 2017-11-30 08:43:32 -0600, Ted Toth wrote:
> >> One thing that is unclear to me is when commits occur while using psql
> >> would you know where in the docs I can find information on this
> >> subject?
> >
> > By default psql enables autocommit which causes an implicit commit after
> > every statement. With a do block I'm not sure whether that means after
> > the do block or after each statement within the do block. I'd just turn
> > autocommit off and add explicit commits wherever I wanted them.
>
> So you think I can turn off autocommit and put BEGIN/COMMITs in a
> large file and then postmaster won't have to parse the whole thing
> when I feed it to it via psql?

No, if you still had one giant do block it would need to parse it
completely. I was thinking of the case where you have many small do
blocks, each with a few insert statements (one do block per "thing"). In
this case it would parse and execute each do block before moving on to
the next. But I wasn't sure whether the default autocommit would mean
one commit after each do block or one commit after each insert statement
(David answered this - thanks), and in any case you probably wouldn't
want to commit after each "thing", so I suggested turning autocommit off
and adding an explicit commit at the end or possibly after every nth
thing.

> > Still: Is there a reason why you use a python script to create an sql
> > script instead of directly issuing the sql queries from your python
> > script?
>
> I already had code that generated JSON so it was relatively easy to
> add code and a cmd line arg to generate SQL instead.

Ok. that sounds like a good reason.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | hjp(at)hjp(dot)at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>