Re: [PATCHES] [HACKERS] WITH RECUSIVE patches 0723
> On Wed, Jul 23, 2008 at 10:59:20AM -0400, Tom Lane wrote: > > Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > > Reviewers, please let me know if you find problems with the > > > patches. If none, I would like to commit this weekend. > > > > Has this patch actually been reviewed yet? The only reports I've > > seen are from testing; nothing from anyone actually reading the > > code. I know I've not looked at it yet. > > I've read the code, for what that's worth, which isn't much. I just > tried out this patch on a fresh checkout of CVS TIP and found: > > EXPLAIN WITH RECURSIVE t(i) AS (VALUES(1::int) UNION ALL SELECT i+1 FROM t > WHERE i < 5) SELECT * FROM t AS t1 JOIN t AS t2 USING(i); > QUERY PLAN > > - > Hash Join (cost=0.08..0.16 rows=2 width=4) >Hash Cond: (t1.i = t2.i) >-> Recursion on t1 (cost=0.00..0.06 rows=2 width=4) > -> Append (cost=0.00..0.04 rows=2 width=4) >-> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) >-> Recursive Scan on t (cost=0.00..0.00 rows=1 width=4) > Filter: (i < 5) >-> Hash (cost=0.06..0.06 rows=2 width=4) > -> Recursion on t2 (cost=0.00..0.06 rows=2 width=4) >-> Append (cost=0.00..0.04 rows=2 width=4) > -> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 > width=4) > -> Recursive Scan on t (cost=0.00..0.00 rows=1 width=4) >Filter: (i < 5) > (13 rows) > > When I try to execute the query without the EXPLAIN, having attached a > debugger > to the back-end, I get. > > (gdb) continue > Continuing. > > Program received signal SIGSEGV, Segmentation fault. Thanks for the report. Here is the new patches from Yoshiyuki. It appeared that addRangeTableEntryForRecursive() needs to do deep copy for the subquery and ref in the RangeTblEntry to avoid double free bug (remember that your example is a self join case). Also I added your query to the regression test case with minor modifications. -- Tatsuo Ishii SRA OSS, Inc. Japan recursive_query.patch.gz Description: Binary data -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] pg_dump additional options for performance
Simon,
* Simon Riggs ([EMAIL PROTECTED]) wrote:
> ...and with command line help also.
The documentation and whatnot looks good to me now. There are a couple
of other issues I found while looking through and testing the patch
though-
Index: src/bin/pg_dump/pg_dump.c
===
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.497
diff -c -r1.497 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 20 Jul 2008 18:43:30 - 1.497
--- src/bin/pg_dump/pg_dump.c 23 Jul 2008 17:04:24 -
***
*** 225,232
RestoreOptions *ropt;
static int disable_triggers = 0;
! static int outputNoTablespaces = 0;
static int use_setsessauth = 0;
static struct option long_options[] = {
{"data-only", no_argument, NULL, 'a'},
--- 229,238
RestoreOptions *ropt;
static int disable_triggers = 0;
! static int outputNoTablespaces = 0;
static int use_setsessauth = 0;
+ static int use_schemaBeforeData;
+ static int use_schemaAfterData;
static struct option long_options[] = {
{"data-only", no_argument, NULL, 'a'},
***
This hunk appears to have a bit of gratuitous whitespace change, not a
big deal tho.
***
*** 464,474
[...]
+ if (dataOnly)
+ dumpObjFlags = REQ_DATA;
+
+ if (use_schemaBeforeData == 1)
+ dumpObjFlags = REQ_SCHEMA_BEFORE_DATA;
+
+ if (use_schemaAfterData == 1)
+ dumpObjFlags = REQ_SCHEMA_AFTER_DATA;
+
+ if (schemaOnly)
+ dumpObjFlags = (REQ_SCHEMA_BEFORE_DATA | REQ_SCHEMA_AFTER_DATA);
***
It wouldn't kill to be consistant between testing for '== 1' and just
checking for non-zero. Again, not really a big deal, and I wouldn't
mention these if there weren't other issues.
***
*** 646,652
* Dumping blobs is now default unless we saw an inclusion switch or -s
* ... but even if we did see one of these, -b turns it back on.
*/
! if (include_everything && !schemaOnly)
outputBlobs = true;
/*
--- 689,695
* Dumping blobs is now default unless we saw an inclusion switch or -s
* ... but even if we did see one of these, -b turns it back on.
*/
! if (include_everything && WANT_PRE_SCHEMA(dumpObjFlags))
outputBlobs = true;
/*
***
Shouldn't this change be to "WANT_DATA(dumpObjFlags)"? That's what most
of the '!schemaOnly' get translated to. Otherwise I think you would be
getting blobs when you've asked for just schema-before-data, which
doesn't seem like it'd make much sense.
***
*** 712,718
dumpStdStrings(g_fout);
/* The database item is always next, unless we don't want it at all */
! if (include_everything && !dataOnly)
dumpDatabase(g_fout);
/* Now the rearrangeable objects. */
--- 755,761
dumpStdStrings(g_fout);
/* The database item is always next, unless we don't want it at all */
! if (include_everything && WANT_DATA(dumpObjFlags))
dumpDatabase(g_fout);
/* Now the rearrangeable objects. */
***
Shouldn't this be 'WANT_PRE_SCHEMA(dumpObjFlags)'?
***
*** 3414,3420
continue;
/* Ignore indexes of tables not to be dumped */
! if (!tbinfo->dobj.dump)
continue;
if (g_verbose)
--- 3459,3465
continue;
/* Ignore indexes of tables not to be dumped */
! if (!tbinfo->dobj.dump || !WANT_POST_SCHEMA(dumpObjFlags))
continue;
if (g_verbose)
***
I didn't test this, but it strikes me as an unnecessary addition? If
anything, wouldn't this check make more sense being done right after
dropping into getIndexes()? No sense going through the loop just for
fun.. Technically, it's a behavioral change for --data-only since it
used to gather index information anyway, but it's a good optimization if
done in the right place.
Also around here, there doesn't appear to be any checking in
dumpEnumType(), which strikes me as odd. Wouldn't that deserve a
if (!WANT_PRE_SCHEMA(dumpObjFlags))
return;
check? If not even some kind of equivilant ->dobj.dump check..
***
*** 9803,9809
tbinfo->dobj.catId, 0,
tbinfo->dobj.dumpId);
}
! if (!schemaOnly)
{
resetPQExpBuffer(query);
appendPQExpBuffer(query, "SELECT pg_catalog.setval(");
--- 9848,9854
tbinfo->dobj.catId, 0,
tbinfo->dobj.du
Re: [PATCHES] [HACKERS] WITH RECUSIVE patches 0723
> On Wed, Jul 23, 2008 at 10:59:20AM -0400, Tom Lane wrote: > > Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > > Reviewers, please let me know if you find problems with the > > > patches. If none, I would like to commit this weekend. > > > > Has this patch actually been reviewed yet? The only reports I've > > seen are from testing; nothing from anyone actually reading the > > code. I know I've not looked at it yet. > > I've read the code, for what that's worth, which isn't much. I just > tried out this patch on a fresh checkout of CVS TIP and found: > > EXPLAIN WITH RECURSIVE t(i) AS (VALUES(1::int) UNION ALL SELECT i+1 FROM t > WHERE i < 5) SELECT * FROM t AS t1 JOIN t AS t2 USING(i); > QUERY PLAN > > - > Hash Join (cost=0.08..0.16 rows=2 width=4) >Hash Cond: (t1.i = t2.i) >-> Recursion on t1 (cost=0.00..0.06 rows=2 width=4) > -> Append (cost=0.00..0.04 rows=2 width=4) >-> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) >-> Recursive Scan on t (cost=0.00..0.00 rows=1 width=4) > Filter: (i < 5) >-> Hash (cost=0.06..0.06 rows=2 width=4) > -> Recursion on t2 (cost=0.00..0.06 rows=2 width=4) >-> Append (cost=0.00..0.04 rows=2 width=4) > -> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 > width=4) > -> Recursive Scan on t (cost=0.00..0.00 rows=1 width=4) >Filter: (i < 5) > (13 rows) > > When I try to execute the query without the EXPLAIN, having attached a > debugger > to the back-end, I get. Thanks for the report. We will look into this. -- Tatsuo Ishii SRA OSS, Inc. Japan > (gdb) continue > Continuing. > > Program received signal SIGSEGV, Segmentation fault. > 0x08192dcd in ExecQual (qual=0xa183824, econtext=0xa183230, resultForNull=0 > '\0') at execQual.c:4513 > 4513expr_value = ExecEvalExpr(clause, econtext, &isNull, NULL); > (gdb) i s > #0 0x08192dcd in ExecQual (qual=0xa183824, econtext=0xa183230, > resultForNull=0 '\0') at execQual.c:4513 > #1 0x08199b23 in ExecScan (node=0xa1831a4, accessMtd=0x81a6bb0 > ) at execScan.c:131 > #2 0x081a6ba9 in ExecRecursiveScan (node=0xa1831a4) at nodeRecursivescan.c:48 > #3 0x08192320 in ExecProcNode (node=0xa1831a4) at execProcnode.c:380 > #4 0x081a6923 in RecursionNext (node=0xa17fe18) at nodeRecursion.c:68 > #5 0x08199a83 in ExecScan (node=0xa17fe18, accessMtd=0x81a6840 > ) at execScan.c:68 > #6 0x081a6839 in ExecRecursion (node=0xa17fe18) at nodeRecursion.c:116 > #7 0x081923e0 in ExecProcNode (node=0xa17fe18) at execProcnode.c:339 > #8 0x081a1c13 in MultiExecHash (node=0xa17fcc4) at nodeHash.c:94 > #9 0x081a28b9 in ExecHashJoin (node=0xa17b654) at nodeHashjoin.c:159 > #10 0x081922d8 in ExecProcNode (node=0xa17b654) at execProcnode.c:395 > #11 0x081901db in standard_ExecutorRun (queryDesc=0xa15c334, > direction=ForwardScanDirection, count=0) at execMain.c:1271 > #12 0x08240dc4 in PortalRunSelect (portal=0xa15631c, forward=1 '\001', > count=0, dest=0xa1733d8) at pquery.c:937 > #13 0x082420e6 in PortalRun (portal=0xa15631c, count=2147483647, isTopLevel=1 > '\001', dest=0xa1733d8, altdest=0xa1733d8, > completionTag=0xbfcacaea "") at pquery.c:793 > #14 0x0823d0a7 in exec_simple_query ( > query_string=0xa12fc9c "WITH RECURSIVE t(i) AS (VALUES(1::int) UNION ALL > SELECT i+1 FROM t WHERE i < 5) SELECT * FROM t AS t1 JOIN t AS t2 USING(i);") > at postgres.c:977 > #15 0x0823e84c in PostgresMain (argc=4, argv=0xa0d0dac, username=0xa0d0d7c > "shackle") at postgres.c:3559 > #16 0x0820957f in ServerLoop () at postmaster.c:3238 > #17 0x0820a4e0 in PostmasterMain (argc=3, argv=0xa0ced50) at postmaster.c:1023 > #18 0x081b69d6 in main (argc=3, argv=0xa0ced50) at main.c:188 > > What other information could help track down this problem? > > Cheers, > David. > -- > David Fetter <[EMAIL PROTECTED]> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: [EMAIL PROTECTED] > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [HACKERS] WITH RECUSIVE patches 0723
On Wed, Jul 23, 2008 at 10:59:20AM -0400, Tom Lane wrote: > Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > Reviewers, please let me know if you find problems with the > > patches. If none, I would like to commit this weekend. > > Has this patch actually been reviewed yet? The only reports I've > seen are from testing; nothing from anyone actually reading the > code. I know I've not looked at it yet. I've read the code, for what that's worth, which isn't much. I just tried out this patch on a fresh checkout of CVS TIP and found: EXPLAIN WITH RECURSIVE t(i) AS (VALUES(1::int) UNION ALL SELECT i+1 FROM t WHERE i < 5) SELECT * FROM t AS t1 JOIN t AS t2 USING(i); QUERY PLAN - Hash Join (cost=0.08..0.16 rows=2 width=4) Hash Cond: (t1.i = t2.i) -> Recursion on t1 (cost=0.00..0.06 rows=2 width=4) -> Append (cost=0.00..0.04 rows=2 width=4) -> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) -> Recursive Scan on t (cost=0.00..0.00 rows=1 width=4) Filter: (i < 5) -> Hash (cost=0.06..0.06 rows=2 width=4) -> Recursion on t2 (cost=0.00..0.06 rows=2 width=4) -> Append (cost=0.00..0.04 rows=2 width=4) -> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) -> Recursive Scan on t (cost=0.00..0.00 rows=1 width=4) Filter: (i < 5) (13 rows) When I try to execute the query without the EXPLAIN, having attached a debugger to the back-end, I get. (gdb) continue Continuing. Program received signal SIGSEGV, Segmentation fault. 0x08192dcd in ExecQual (qual=0xa183824, econtext=0xa183230, resultForNull=0 '\0') at execQual.c:4513 4513expr_value = ExecEvalExpr(clause, econtext, &isNull, NULL); (gdb) i s #0 0x08192dcd in ExecQual (qual=0xa183824, econtext=0xa183230, resultForNull=0 '\0') at execQual.c:4513 #1 0x08199b23 in ExecScan (node=0xa1831a4, accessMtd=0x81a6bb0 ) at execScan.c:131 #2 0x081a6ba9 in ExecRecursiveScan (node=0xa1831a4) at nodeRecursivescan.c:48 #3 0x08192320 in ExecProcNode (node=0xa1831a4) at execProcnode.c:380 #4 0x081a6923 in RecursionNext (node=0xa17fe18) at nodeRecursion.c:68 #5 0x08199a83 in ExecScan (node=0xa17fe18, accessMtd=0x81a6840 ) at execScan.c:68 #6 0x081a6839 in ExecRecursion (node=0xa17fe18) at nodeRecursion.c:116 #7 0x081923e0 in ExecProcNode (node=0xa17fe18) at execProcnode.c:339 #8 0x081a1c13 in MultiExecHash (node=0xa17fcc4) at nodeHash.c:94 #9 0x081a28b9 in ExecHashJoin (node=0xa17b654) at nodeHashjoin.c:159 #10 0x081922d8 in ExecProcNode (node=0xa17b654) at execProcnode.c:395 #11 0x081901db in standard_ExecutorRun (queryDesc=0xa15c334, direction=ForwardScanDirection, count=0) at execMain.c:1271 #12 0x08240dc4 in PortalRunSelect (portal=0xa15631c, forward=1 '\001', count=0, dest=0xa1733d8) at pquery.c:937 #13 0x082420e6 in PortalRun (portal=0xa15631c, count=2147483647, isTopLevel=1 '\001', dest=0xa1733d8, altdest=0xa1733d8, completionTag=0xbfcacaea "") at pquery.c:793 #14 0x0823d0a7 in exec_simple_query ( query_string=0xa12fc9c "WITH RECURSIVE t(i) AS (VALUES(1::int) UNION ALL SELECT i+1 FROM t WHERE i < 5) SELECT * FROM t AS t1 JOIN t AS t2 USING(i);") at postgres.c:977 #15 0x0823e84c in PostgresMain (argc=4, argv=0xa0d0dac, username=0xa0d0d7c "shackle") at postgres.c:3559 #16 0x0820957f in ServerLoop () at postmaster.c:3238 #17 0x0820a4e0 in PostmasterMain (argc=3, argv=0xa0ced50) at postmaster.c:1023 #18 0x081b69d6 in main (argc=3, argv=0xa0ced50) at main.c:188 What other information could help track down this problem? Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [HACKERS] WITH RECUSIVE patches 0723
> Tatsuo Ishii <[EMAIL PROTECTED]> writes: > > Reviewers, please let me know if you find problems with the > > patches. If none, I would like to commit this weekend. > > Has this patch actually been reviewed yet? The only reports I've > seen are from testing; nothing from anyone actually reading the > code. I know I've not looked at it yet. The reviewer registered at the Wiki is David Fetter and I believe he is reading the patches. Michael Makes has contributed the ecpg part. So apparently he is knowing the ecpg part at least. I know the patch is huge. Reviewers, please let me know if you have any question about the code. I would like to do anything for helping the review. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [HACKERS][PATCHES] odd output in restore mode
On Wed, 2008-07-23 at 21:38 +0300, Heikki Linnakangas wrote: > Simon Riggs wrote: > > On Tue, 2008-07-22 at 17:19 -0700, Martin Zaun wrote: > >> 8. Unresolved question of implementing now/later a "cp" replacement > > > > The patch implements what's been agreed. > > > > I'm not rewriting "cp", for reasons already discussed. > > > > Not a comment to you Martin, but it's fairly clear that I'm not > > maintaining this correctly for Windows. I've never claimed to have > > tested this on Windows, and only included Windows related items as > > requested by others. I need to make it clear that I'm not going to > > maintain it at all, for Windows. If others wish to report Windows issues > > then they can suggest appropriate fixes and test them also. > > Hmm. I just realized that replacing the "cp" command within pg_standby > won't help at all. The problem is with the command that copies the files > *to* the archivelocation that pg_standby polls, not with the copy > pg_standby does from archivelocation to pg_xlog. And we don't have much > control over that. > > We really need a more reliable way of detecting that a file has been > fully copied. One simple improvement would be to check the xlp_magic > field of the last page, though it still wouldn't be bullet-proof. > > Do the commands that preallocate the space keep the file exclusively > locked during the copy? If they do, shouldn't we get an error in trying > to run the restore copy command, and retry after the 1s sleep in > RestoreWALFileForRecovery? Though if the archive location is a samba > mount or something, I guess we can't rely on Windows-style exclusive > locking. With respect, I need to refer you back to the my last paragraph above. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [HACKERS][PATCHES] odd output in restore mode
Kevin Grittner wrote: "Heikki Linnakangas" <[EMAIL PROTECTED]> wrote: We really need a more reliable way of detecting that a file has been fully copied. In our scripts we handle this by copying to a temp directory on the same mount point as the archive directory and doing a mv to the archive location when the copy is successfully completed. I think that this even works on Windows. Could that just be documented as a strong recommendation for the archive script? Needs testing at least. If it does in fact work then we can just adjust the docs and be done - or maybe provide a .bat file or perl script that would work as na archive_command on Windows. cheers andrew -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [HACKERS][PATCHES] odd output in restore mode
>>> "Heikki Linnakangas" <[EMAIL PROTECTED]> wrote: > We really need a more reliable way of detecting that a file has been > fully copied. In our scripts we handle this by copying to a temp directory on the same mount point as the archive directory and doing a mv to the archive location when the copy is successfully completed. I think that this even works on Windows. Could that just be documented as a strong recommendation for the archive script? -Kevin -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [HACKERS][PATCHES] odd output in restore mode
Simon Riggs wrote: On Tue, 2008-07-22 at 17:19 -0700, Martin Zaun wrote: 8. Unresolved question of implementing now/later a "cp" replacement The patch implements what's been agreed. I'm not rewriting "cp", for reasons already discussed. Not a comment to you Martin, but it's fairly clear that I'm not maintaining this correctly for Windows. I've never claimed to have tested this on Windows, and only included Windows related items as requested by others. I need to make it clear that I'm not going to maintain it at all, for Windows. If others wish to report Windows issues then they can suggest appropriate fixes and test them also. Hmm. I just realized that replacing the "cp" command within pg_standby won't help at all. The problem is with the command that copies the files *to* the archivelocation that pg_standby polls, not with the copy pg_standby does from archivelocation to pg_xlog. And we don't have much control over that. We really need a more reliable way of detecting that a file has been fully copied. One simple improvement would be to check the xlp_magic field of the last page, though it still wouldn't be bullet-proof. Do the commands that preallocate the space keep the file exclusively locked during the copy? If they do, shouldn't we get an error in trying to run the restore copy command, and retry after the 1s sleep in RestoreWALFileForRecovery? Though if the archive location is a samba mount or something, I guess we can't rely on Windows-style exclusive locking. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] pg_dump additional options for performance
On Wed, 2008-07-23 at 17:40 +0100, Simon Riggs wrote: > On Mon, 2008-07-21 at 07:56 -0400, Stephen Frost wrote: > > Simon, > > > > * Simon Riggs ([EMAIL PROTECTED]) wrote: > > > > I hadn't realized that Simon was using "pre-schema" and "post-schema" > > > > to name the first and third parts. I'd agree that this is confusing > > > > nomenclature: it looks like it's trying to say that the data is the > > > > schema, and the schema is not! How about "pre-data and "post-data"? > > > > > > OK by me. Any other takers? > > > > Having the command-line options be "--schema-pre-data" and > > "--schema-post-data" is fine with me. Leaving them the way they are is > > also fine by me. It's the documentation (back to pg_dump.sgml, > > ~774/~797) that starts talking about Pre-Schema and Post-Schema. > > OK, Mr.Reviewer, sir: > > * patch redone using --schema-before-data and --schema-after-data > > * docs rewritten using short clear descriptions using only the words > "before" and "after", like in the option names > > * all variable names changed > > * retested > > So prefixes "pre" and "post" no longer appear anywhere. No latin derived > phrases, just good ol' Anglo-Saxon words. ...and with command line help also. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support Index: doc/src/sgml/ref/pg_dump.sgml === RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/ref/pg_dump.sgml,v retrieving revision 1.103 diff -c -r1.103 pg_dump.sgml *** doc/src/sgml/ref/pg_dump.sgml 20 Jul 2008 18:43:30 - 1.103 --- doc/src/sgml/ref/pg_dump.sgml 23 Jul 2008 16:55:05 - *** *** 133,139 Include large objects in the dump. This is the default behavior except when --schema, --table, or ! --schema-only is specified, so the -b switch is only useful to add large objects to selective dumps. --- 133,140 Include large objects in the dump. This is the default behavior except when --schema, --table, or ! --schema-only or --schema-before-data or ! --schema-after-data is specified, so the -b switch is only useful to add large objects to selective dumps. *** *** 426,431 --- 427,452 + --schema-before-data + + + Dump object definitions (schema) that occur before table data, + using the order produced by a full dump. + + + + + + --schema-after-data + + + Dump object definitions (schema) that occur after table data, + using the order produced by a full dump. + + + + + -S username --superuser=username *** *** 790,795 --- 811,844 +The output of pg_dump can be divided into three parts: + + + + Before Data - objects output before data, which includes + CREATE TABLE statements and others. + This part can be requested using --schema-before-data. + + + + + Table Data - data can be requested using --data-only. + + + + + After Data - objects output after data, which includes + CREATE INDEX statements and others. + This part can be requested using --schema-after-data. + + + +This allows us to work more easily with large data dump files when +there is some need to edit commands or resequence their execution for +performance. + + + Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be loaded into Index: doc/src/sgml/ref/pg_restore.sgml === RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/ref/pg_restore.sgml,v retrieving revision 1.75 diff -c -r1.75 pg_restore.sgml *** doc/src/sgml/ref/pg_restore.sgml 13 Apr 2008 03:49:21 - 1.75 --- doc/src/sgml/ref/pg_restore.sgml 23 Jul 2008 16:55:05 - *** *** 321,326 --- 321,346 + --schema-before-data + + + Restore object definitions (schema) that occur before table data, + using the order produced by a full restore. + + + + + + --schema-after-data + + + Restore object definitions (schema) that occur after table data, + using the order produced by a full restore. + + + + + -S username --superuser=username *** *** 572,577 --- 592,626 +The actions of pg_restore can be +divided into three parts: + + + + Before Data - objects output before data, which includes + CREATE TABLE statements and others. + This part can be requested using --schema-before-data. +
Re: [PATCHES] pg_dump additional options for performance
On Mon, 2008-07-21 at 07:56 -0400, Stephen Frost wrote: > Simon, > > * Simon Riggs ([EMAIL PROTECTED]) wrote: > > > I hadn't realized that Simon was using "pre-schema" and "post-schema" > > > to name the first and third parts. I'd agree that this is confusing > > > nomenclature: it looks like it's trying to say that the data is the > > > schema, and the schema is not! How about "pre-data and "post-data"? > > > > OK by me. Any other takers? > > Having the command-line options be "--schema-pre-data" and > "--schema-post-data" is fine with me. Leaving them the way they are is > also fine by me. It's the documentation (back to pg_dump.sgml, > ~774/~797) that starts talking about Pre-Schema and Post-Schema. OK, Mr.Reviewer, sir: * patch redone using --schema-before-data and --schema-after-data * docs rewritten using short clear descriptions using only the words "before" and "after", like in the option names * all variable names changed * retested So prefixes "pre" and "post" no longer appear anywhere. No latin derived phrases, just good ol' Anglo-Saxon words. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support Index: doc/src/sgml/ref/pg_dump.sgml === RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/ref/pg_dump.sgml,v retrieving revision 1.103 diff -c -r1.103 pg_dump.sgml *** doc/src/sgml/ref/pg_dump.sgml 20 Jul 2008 18:43:30 - 1.103 --- doc/src/sgml/ref/pg_dump.sgml 23 Jul 2008 15:26:29 - *** *** 133,139 Include large objects in the dump. This is the default behavior except when --schema, --table, or ! --schema-only is specified, so the -b switch is only useful to add large objects to selective dumps. --- 133,140 Include large objects in the dump. This is the default behavior except when --schema, --table, or ! --schema-only or --schema-before-data or ! --schema-after-data is specified, so the -b switch is only useful to add large objects to selective dumps. *** *** 426,431 --- 427,452 + --schema-before-data + + + Dump object definitions (schema) that occur before table data, + using the order produced by a full dump. + + + + + + --schema-after-data + + + Dump object definitions (schema) that occur after table data, + using the order produced by a full dump. + + + + + -S username --superuser=username *** *** 790,795 --- 811,844 +The output of pg_dump can be divided into three parts: + + + + Before Data - objects output before data, which includes + CREATE TABLE statements and others. + This part can be requested using --schema-before-data. + + + + + Table Data - data can be requested using --data-only. + + + + + After Data - objects output after data, which includes + CREATE INDEX statements and others. + This part can be requested using --schema-after-data. + + + +This allows us to work more easily with large data dump files when +there is some need to edit commands or resequence their execution for +performance. + + + Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be loaded into Index: doc/src/sgml/ref/pg_restore.sgml === RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/ref/pg_restore.sgml,v retrieving revision 1.75 diff -c -r1.75 pg_restore.sgml *** doc/src/sgml/ref/pg_restore.sgml 13 Apr 2008 03:49:21 - 1.75 --- doc/src/sgml/ref/pg_restore.sgml 23 Jul 2008 16:00:29 - *** *** 321,326 --- 321,346 + --schema-before-data + + + Restore object definitions (schema) that occur before table data, + using the order produced by a full restore. + + + + + + --schema-after-data + + + Restore object definitions (schema) that occur after table data, + using the order produced by a full restore. + + + + + -S username --superuser=username *** *** 572,577 --- 592,626 +The actions of pg_restore can be +divided into three parts: + + + + Before Data - objects output before data, which includes + CREATE TABLE statements and others. + This part can be requested using --schema-before-data. + + + + + Table Data - data can be requested using --data-only. + + + + + After Data - objects output af
Re: [PATCHES] [HACKERS] WITH RECUSIVE patches 0723
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > Reviewers, please let me know if you find problems with the > patches. If none, I would like to commit this weekend. Has this patch actually been reviewed yet? The only reports I've seen are from testing; nothing from anyone actually reading the code. I know I've not looked at it yet. regards, tom lane -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
[PATCHES] WITH RECUSIVE patches 0723
> Hi, > > Here is the lastest WITH RECURSIVE patches against 2007/07/17 CVS (CVS > HEAD won't compile for me). > > This version includes regression tests and is almost ready for commit > IMO. I pulled fresh CVS HEAD and it seems the problem is gone. Here is the lastest WITH RECURSIVE patches against CVS HEAD. Reviewers, please let me know if you find problems with the patches. If none, I would like to commit this weekend. -- Tatsuo Ishii SRA OSS, Inc. Japan recursive_query.patch.gz Description: Binary data -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [HACKERS][PATCHES] odd output in restore mode
On Tue, 2008-07-22 at 17:19 -0700, Martin Zaun wrote: > 1. Issues with applying the patch to CVS HEAD: For me, the patch applies cleanly to CVS HEAD. I do notice that there are two files "standby.sgml" and "pgstandby.sgml". I can't see where "standby.sgml" comes from, but I haven't created it; perhaps it is a relic of the SGML build process. I've recreated my source tree since I wrote the patch also. Weird. I'll redo the patch so it points at pgstandby.sgml, which is the one thats listed as being in the main source tree. > 2. Missing description for new command-line options in pgstandby.sgml > > - no description of the proposed new command-line options -h and -p? These are done. The patch issues have missed those hunks. > 3. No coding style issues seen > > Just one comment: the logic that selects the actual restore command to > be used has moved from CustomizableInitialize() to main() -- a matter > of personal taste, perhaps. But in my view the: > + the #ifdef WIN32/HAVE_WORKING_LINK logic has become easier to read Thanks > 4. Issue: missing break in switch, silent override of '-l' argument? > > This behaviour has been in there before Well spotted. I don't claim to test this for Windows. > 5. Minor wording issue in usage message on new '-p' option > > I was wondering if the "always" in the usage text > fprintf(stderr, " -p always uses GNU compatible 'cp' command > on all platforms\n"); > is too strong, since multiple restore command options overwrite each > other, e.g. "-p -c" applies Windows's "copy" instead of Gnu's "cp". I was assuming you don't turn the switch off again immediately afterwards. > 6. Minor code comment suggestion > > Unrelated to this patch, I wonder if the code comments on all four > time-related vars better read "seconds" instead of "amount of time": > int sleeptime = 5; /* amount of time to sleep between file > checks */ > int holdtime = 0; /* amount of time to wait once file appears > full */ > int waittime = -1; /* how long we have been waiting, -1 no wait > * yet */ > int maxwaittime = 0;/* how long are we prepared to wait for? */ As you say, unrelated to the patch. > 7. Question: benefits of separate holdtime option from sleeptime? > > Simon Riggs wrote: > > * provide "holdtime" delay, default 0 (on all platforms) > > Going back on the hackers+patches emails and parsing the code > comments, I'm sorry if I missed that, but I'm not sure I've understood > the exact tuning benefits that introducing the new holdtime option > provides over using the existing sleeptime, as it's been the case > (just on Win32 only). This is central to the patch, since the complaint was about the delay introduced by doing that previously. > 8. Unresolved question of implementing now/later a "cp" replacement The patch implements what's been agreed. I'm not rewriting "cp", for reasons already discussed. Not a comment to you Martin, but it's fairly clear that I'm not maintaining this correctly for Windows. I've never claimed to have tested this on Windows, and only included Windows related items as requested by others. I need to make it clear that I'm not going to maintain it at all, for Windows. If others wish to report Windows issues then they can suggest appropriate fixes and test them also. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [HACKERS][PATCHES] odd output in restore mode
On Tue, 2008-07-22 at 17:19 -0700, Martin Zaun wrote: > 1. Issues with applying the patch to CVS HEAD: Sounds awful. Thanks for the review, will fix. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
