Обсуждение: BUG #5626: Parallel pg_restore fails with "tuple concurrently updated"
The following bug has been logged online: Bug reference: 5626 Logged by: Albert Ullrich Email address: aullrich@blackducksoftware.com PostgreSQL version: 8.4.4 Operating system: Centos 5.5 64bit Description: Parallel pg_restore fails with "tuple concurrently updated" Details: pg_restore -e -v -j 4 -Fc -L /tmp/fp_basic.toc -d fp_basic /tmp/fp_basic.dump pg_restore: connecting to database for restore pg_restore: processing item 5 SCHEMA file_tables pg_restore: creating SCHEMA file_tables pg_restore: processing item 7 SCHEMA scratch pg_restore: creating SCHEMA scratch pg_restore: processing item 27 FUNCTION get_file_tab_id(text) pg_restore: creating FUNCTION get_file_tab_id(text) pg_restore: processing item 1578 TABLE file_table pg_restore: creating TABLE file_table pg_restore: processing item 1607 SEQUENCE scratch_table_id pg_restore: creating SEQUENCE scratch_table_id pg_restore: processing item 1953 SEQUENCE SET scratch_table_id pg_restore: executing SEQUENCE SET scratch_table_id pg_restore: processing item 1608 TABLE scratch_tablespace pg_restore: creating TABLE scratch_tablespace pg_restore: entering main parallel loop pg_restore: launching item 1915 TABLE DATA file_table pg_restore: launching item 1941 TABLE DATA scratch_tablespace pg_restore: skipping item 1942 ENCODING ENCODING pg_restore: skipping item 1943 STDSTRINGS STDSTRINGS pg_restore: skipping item 1944 DATABASE fp_basic pg_restore: skipping item 8 SCHEMA public pg_restore: skipping item 1946 ACL public pg_restore: launching item 35 FUNCTION removefilefromdb(integer) pg_restore: launching item 1579 TABLE files pg_restore: restoring data for table "file_table" pg_restore: restoring data for table "scratch_tablespace" pg_restore: creating FUNCTION removefilefromdb(integer) pg_restore: creating TABLE files pg_restore: finished item 1579 TABLE files pg_restore: launching item 1580 TABLE files_0 pg_restore: creating TABLE files_0 pg_restore: finished item 35 FUNCTION removefilefromdb(integer) pg_restore: launching item 1581 TABLE files_1 pg_restore: creating TABLE files_1 pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 1581; 1259 153051 TABLE files_1 blackduck pg_restore: [archiver (db)] could not execute query: ERROR: tuple concurrently updated Command was: CREATE TABLE files_1 ( ) INHERITS (public.files); pg_restore: *** aborted because of error pg_restore: finished item 1581 TABLE files_1 pg_restore: [archiver] worker process failed: exit code 1 pg_restore: *** aborted because of error
"Albert Ullrich" <aullrich@blackducksoftware.com> writes: > Description: Parallel pg_restore fails with "tuple concurrently > updated" > pg_restore -e -v -j 4 -Fc -L /tmp/fp_basic.toc -d fp_basic > /tmp/fp_basic.dump Apparently you've used the -L option to reorder the dump objects in a way that won't work with parallel restore. On the whole I don't recommend trying to use -L with parallel restore at all, but if you must do it, it's your responsibility to choose a safe order. Basically, you had better keep all the PRE_DATA objects ahead of the DATA objects, and those ahead of POST_DATA objects. Did you have a specific reason for not wanting to let parallel restore choose the restore order for itself? regards, tom lane
We run essentially the following commands to create the table of contents i= n order to prevent pg_restore from failing: pg_restore -l database.dump | \ eval fgrep -v -e "' SCHEMA - public '" \ -e "' COMMENT - SCHEMA public '" \ -e "' PROCEDURAL LANGUAGE - plpgsql'" database.toc Where would the reordering happen? Thanks, A. Ullrich On 8/19/10 3:59 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: "Albert Ullrich" <aullrich@blackducksoftware.com> writes: > Description: Parallel pg_restore fails with "tuple concurrently > updated" > pg_restore -e -v -j 4 -Fc -L /tmp/fp_basic.toc -d fp_basic > /tmp/fp_basic.dump Apparently you've used the -L option to reorder the dump objects in a way that won't work with parallel restore. On the whole I don't recommend trying to use -L with parallel restore at all, but if you must do it, it's your responsibility to choose a safe order. Basically, you had better keep all the PRE_DATA objects ahead of the DATA objects, and those ahead of POST_DATA objects. Did you have a specific reason for not wanting to let parallel restore choose the restore order for itself? regards, tom lane
Albert Ullrich <aullrich@blackducksoftware.com> writes: > We run essentially the following commands to create the table of contents in order to prevent pg_restore from failing: > pg_restore -l database.dump | \ > eval fgrep -v -e "' SCHEMA - public '" \ > -e "' COMMENT - SCHEMA public '" \ > -e "' PROCEDURAL LANGUAGE - plpgsql'" database.toc Mph ... removing the public schema from the restore list is problematic, because you've got a lot of stuff *in* the public schema, and of course all that stuff depends on the public schema entry. Normally this doesn't bother pg_restore because it just blindly restores in the order you tell it, without paying much attention to the dependency entries. However, in parallel restore mode it does believe the dependencies, and the fact that you've got lots of entries that depend on something not to be restored screws it up. We should probably try to make pg_restore smarter about this case, but for the moment my advice remains: don't use -L with parallel restore. It appears to me that you're trying to avoid running pg_restore as superuser, which no doubt seems a bit safer, but it's not a terribly well-tested path. regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote: > Mph ... removing the public schema from the restore list is problematic, > because you've got a lot of stuff *in* the public schema, and of course > all that stuff depends on the public schema entry. Normally this > doesn't bother pg_restore because it just blindly restores in the order > you tell it, without paying much attention to the dependency entries. The problem here, to some extent, is that 'public' is where everyone dumps their favorite contrib functions (classic example here being PostGIS). I just ran into this during an 8.3->8.4 upgrade yesterday. I installed the new PostGIS on 8.4 and didn't need/want the old PostGIS to be copied over from the 8.3 instance. In that case I wasn't trying parallel restore, but there are certainly cases where I'll want to.. > We should probably try to make pg_restore smarter about this case, Yes, definitely. I don't have an immediate solution though, unfortunately. Would be kind of neat if pg_restore could connect to the NEW database and determine if certain things exist which are needed dependencies... That's a whole lot of rather complex work though. Thanks, Stephen
Stephen Frost <sfrost@snowman.net> writes: > * Tom Lane (tgl@sss.pgh.pa.us) wrote: >> We should probably try to make pg_restore smarter about this case, > Yes, definitely. I don't have an immediate solution though, I just posted some further analysis to pgsql-hackers. Please follow up there. regards, tom lane
I wrote: > We should probably try to make pg_restore smarter about this case, I've applied a patch for this: http://archives.postgresql.org/pgsql-committers/2010-08/msg00271.php regards, tom lane
I followed your advise, Tom and reworked the way we do dumps and restores t= o remove the requirement for having TOCs. So far the restores executed flawlessly and I am grateful for having this f= eature available to us! Huge time savings! Thanks! Albert On 8/20/10 1:24 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: Albert Ullrich <aullrich@blackducksoftware.com> writes: > We run essentially the following commands to create the table of contents= in order to prevent pg_restore from failing: > pg_restore -l database.dump | \ > eval fgrep -v -e "' SCHEMA - public '" \ > -e "' COMMENT - SCHEMA public '" \ > -e "' PROCEDURAL LANGUAGE - plpgsql'" database.toc Mph ... removing the public schema from the restore list is problematic, because you've got a lot of stuff *in* the public schema, and of course all that stuff depends on the public schema entry. Normally this doesn't bother pg_restore because it just blindly restores in the order you tell it, without paying much attention to the dependency entries. However, in parallel restore mode it does believe the dependencies, and the fact that you've got lots of entries that depend on something not to be restored screws it up. We should probably try to make pg_restore smarter about this case, but for the moment my advice remains: don't use -L with parallel restore. It appears to me that you're trying to avoid running pg_restore as superuser, which no doubt seems a bit safer, but it's not a terribly well-tested path. regards, tom lane A. Ullrich Director, Quality Assurance and Infrastructure Black Duck Software, Inc. aullrich@blackducksoftware.com T +1.781.810.2092 C +1.781.405.0780 F +1.781.891.5145 http://www.blackducksoftware.com