Re: pg_dump and inserts
От | Bruce Momjian |
---|---|
Тема | Re: pg_dump and inserts |
Дата | |
Msg-id | 200302141940.h1EJe9C29265@candle.pha.pa.us обсуждение исходный текст |
Ответ на | Re: pg_dump and inserts (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
Attached is a patch the more clearly handles autocommit in pg_dump. I had already fixed pg_dump for doing autocommit while dumping, but didn't handle setting autocommit on restore. I focused on the initial script file startup, every \\connect, pg_restore, and pg_dumpall. I think I got them all. New pg_dump output is: -- -- PostgreSQL database dump -- SET autocommit TO 'on'; \connect - postgres SET autocommit TO 'on'; --------------------------------------------------------------------------- Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I just checked and pg_dump -d _doesn't_ place the INSERT's in a > > transsaction. Seems it should, > > I think this is a bad idea. If one were after speed, one would have > used the COPY format in the first place. If one uses INSERTs, there > may be a reason for it --- like, say, wanting each row insertion to > succeed or fail independently. Put a begin/end around it, and you > lose that. > > > and perhaps add a: > > SET autocommit TO 'on' > > as well. > > This is probably a good idea, since pg_dump scripts effectively assume > that anyway. > > > Of course, that SET would fail when restoring to prior > > releases, > > Irrelevant; current pg_dump scripts already issue a SET that pre-7.3 > servers won't recognize (search_path). A failed SET is harmless anyway, > or should be. (What we really need is for someone to fix pg_restore to > not abort on SQL errors...) > > regards, tom lane > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 Index: src/bin/pg_dump/pg_backup_archiver.c =================================================================== RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_backup_archiver.c,v retrieving revision 1.67 diff -c -c -r1.67 pg_backup_archiver.c *** src/bin/pg_dump/pg_backup_archiver.c 1 Feb 2003 22:06:59 -0000 1.67 --- src/bin/pg_dump/pg_backup_archiver.c 14 Feb 2003 19:39:08 -0000 *************** *** 206,212 **** sav = SetOutput(AH, ropt->filename, ropt->compression); ahprintf(AH, "--\n-- PostgreSQL database dump\n--\n\n"); ! /* * Drop the items at the start, in reverse order */ --- 206,213 ---- sav = SetOutput(AH, ropt->filename, ropt->compression); ahprintf(AH, "--\n-- PostgreSQL database dump\n--\n\n"); ! ahprintf(AH, "SET autocommit TO 'on';\n\n"); ! /* * Drop the items at the start, in reverse order */ *************** *** 2109,2115 **** dbname ? fmtId(dbname) : "-"); appendPQExpBuffer(qry, " %s\n\n", fmtId(user)); ! ahprintf(AH, qry->data); destroyPQExpBuffer(qry); --- 2110,2117 ---- dbname ? fmtId(dbname) : "-"); appendPQExpBuffer(qry, " %s\n\n", fmtId(user)); ! appendPQExpBuffer(qry, "SET autocommit TO 'on';\n\n"); ! ahprintf(AH, qry->data); destroyPQExpBuffer(qry); Index: src/bin/pg_dump/pg_backup_db.c =================================================================== RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_backup_db.c,v retrieving revision 1.45 diff -c -c -r1.45 pg_backup_db.c *** src/bin/pg_dump/pg_backup_db.c 13 Feb 2003 04:54:15 -0000 1.45 --- src/bin/pg_dump/pg_backup_db.c 14 Feb 2003 19:39:09 -0000 *************** *** 213,218 **** --- 213,233 ---- if (password) free(password); + /* check for version mismatch */ + _check_database_version(AH, true); + + /* Turn autocommit on */ + if (AH->public.remoteVersion >= 70300) + { + PGresult *res; + + res = PQexec(AH->connection, "SET autocommit TO 'on'"); + if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) + die_horribly(AH, NULL, "SET autocommit TO 'on' failed: %s", + PQerrorMessage(AH->connection)); + PQclear(res); + } + PQsetNoticeProcessor(newConn, notice_processor, NULL); return newConn; Index: src/bin/pg_dump/pg_dumpall.c =================================================================== RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_dumpall.c,v retrieving revision 1.13 diff -c -c -r1.13 pg_dumpall.c *** src/bin/pg_dump/pg_dumpall.c 16 Jan 2003 15:27:59 -0000 1.13 --- src/bin/pg_dump/pg_dumpall.c 14 Feb 2003 19:39:09 -0000 *************** *** 190,195 **** --- 190,196 ---- printf("-- PostgreSQL database cluster dump\n"); printf("--\n\n"); printf("\\connect \"template1\"\n\n"); + printf("SET autocommit TO 'on';\n\n"); dumpUsers(conn); dumpGroups(conn); *************** *** 552,557 **** --- 553,559 ---- fprintf(stderr, _("%s: dumping database \"%s\"...\n"), progname, dbname); printf("\\connect %s\n", fmtId(dbname)); + printf("SET autocommit TO 'on';\n\n"); ret = runPgDump(dbname); if (ret != 0) { *************** *** 676,681 **** --- 678,691 ---- } } PQclear(res); + + if (server_version >= 70300) + { + PGresult *res; + + res = executeQuery(conn, "SET autocommit TO 'on';SELECT 1;"); + PQclear(res); + } return conn; }
В списке pgsql-hackers по дате отправления: