Re: Moving to postgresql and some ignorant questions

"Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)com> writes:

> If I am reading this right, does this mean it is probably better to
> leave fsync as "fsync=off" on production machines?

Only if you can reconstruct your data from other sources in the case of a
server crash or power failure. I wouldn't recommend it.

> Also, is COMMIT automatic for my queries? In some minor testing I did (not
> scientific I did at all) some queries through Perl did not update the
> database at all. I had "fsync=off" in my conf file, and there was no COMMIT
> etc in my SQL, just plain SQL. So I am a bit confused. What's a good
> starting point?

psql runs in autocommit mode by default. If you want multiple queries in a
transaction you have to issue a BEGIN statement. Drivers may do various things
by default.

>> Grouping more work into a single transaction makes the delay for the fsync at
>> COMMIT time less of a problem.
>
>
> Agree. That's what I am trying to do. Include as many UPDATEs etc into
> the same TRANSACTION block, but my worry is when I read about
> autocommit and how it is enabled by default in postgresql 8.
> Specifying explicit BEGIN and COMMIT blocks should only commit when I
> want the DB to, or will each UPDATE in the middle of this block get
> executed?
>
> Sorry if this is a naive question. I am reading up as much as I can.

If you issue a BEGIN then nothing gets committed until you issue a COMMIT. If
anything happens in the meantime then everything you've done since the BEGIN
disappears.

For batch work like loading then it makes sense to handle a 100-1000 records
per transaction. But for most purposes you want to group things together based
on what you want to happen if an error occurs. Group together into a single
transaction precisely the changes that you want to be committed together or
rolled back together. Don't structure your program around the performance
issues.

For the remaining questions I would say you need to experiment. Perhaps others
will have more ideas.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com