Re: [PATCHES] [HACKERS] WITH RECUSIVE patches 0723
> Now, I get a different problem, this time with the following code > intended to materialize paths on the fly and summarize down to a > certain depth in a tree: > > CREATE TABLE tree( > id INTEGER PRIMARY KEY, > parent_id INTEGER REFERENCES tree(id) > ); > > INSERT INTO tree > VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3), >(9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11); > > WITH RECURSIVE t(id, path) AS ( > VALUES(1,ARRAY[NULL::integer]) > UNION ALL > SELECT tree.id, t.path || tree.id > FROM tree JOIN t ON (tree.parent_id = t.id) > ) > SELECT > t1.id, count(t2.*) > FROM > t t1 > JOIN > t t2 > ON ( > t1.path[1:2] = t2.path[1:2] > AND > array_upper(t1.path,1) = 2 > AND > array_upper(t2.path,1) > 2 > ) > GROUP BY t1.id; > ERROR: unrecognized node type: 203 Thanks for the report. We will look into this. > Please apply the attached patch to help out with tab > completion in psql. Ok, it will appear in the next patches. -- 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: [PATCHES] [HACKERS] WITH RECUSIVE patches 0723
On Thu, Jul 24, 2008 at 01:55:37PM +0900, Tatsuo Ishii wrote:
> > Program received signal SIGSEGV, Segmentation fault.
>
> Thanks for the report. Here is the new patches from Yoshiyuki.
Thanks for the patch :)
Now, I get a different problem, this time with the following code
intended to materialize paths on the fly and summarize down to a
certain depth in a tree:
CREATE TABLE tree(
id INTEGER PRIMARY KEY,
parent_id INTEGER REFERENCES tree(id)
);
INSERT INTO tree
VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
(9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
WITH RECURSIVE t(id, path) AS (
VALUES(1,ARRAY[NULL::integer])
UNION ALL
SELECT tree.id, t.path || tree.id
FROM tree JOIN t ON (tree.parent_id = t.id)
)
SELECT
t1.id, count(t2.*)
FROM
t t1
JOIN
t t2
ON (
t1.path[1:2] = t2.path[1:2]
AND
array_upper(t1.path,1) = 2
AND
array_upper(t2.path,1) > 2
)
GROUP BY t1.id;
ERROR: unrecognized node type: 203
Please apply the attached patch to help out with tab
completion in psql.
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
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
***
*** 613,621 psql_completion(char *text, int start, int end)
"COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE",
"DELETE FROM", "DISCARD", "DROP", "END", "EXECUTE", "EXPLAIN",
"FETCH",
"GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY",
"PREPARE",
! "REASSIGN", "REINDEX", "RELEASE", "RESET", "REVOKE", "ROLLBACK",
"SAVEPOINT", "SELECT", "SET", "SHOW", "START", "TRUNCATE",
"UNLISTEN",
! "UPDATE", "VACUUM", "VALUES", NULL
};
static const char *const backslash_commands[] = {
--- 613,621
"COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE",
"DELETE FROM", "DISCARD", "DROP", "END", "EXECUTE", "EXPLAIN",
"FETCH",
"GRANT", "INSERT", "LISTEN", "LOAD", "LOCK", "MOVE", "NOTIFY",
"PREPARE",
! "REASSIGN", "RECURSIVE", "REINDEX", "RELEASE", "RESET",
"REVOKE", "ROLLBACK",
"SAVEPOINT", "SELECT", "SET", "SHOW", "START", "TRUNCATE",
"UNLISTEN",
! "UPDATE", "VACUUM", "VALUES", "WITH", NULL
};
static const char *const backslash_commands[] = {
***
*** 2044,2049 psql_completion(char *text, int start, int end)
--- 2044,2058
pg_strcasecmp(prev2_wd, "ANALYZE") == 0))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
+ /* WITH [RECURSIVE] */
+ else if (pg_strcasecmp(prev_wd, "WITH") == 0)
+ {
+ static const char *const list_WITH[] =
+ {"RECURSIVE", NULL};
+
+ COMPLETE_WITH_LIST(list_WITH);
+ }
+
/* ANALYZE */
/* If the previous word is ANALYZE, produce list of tables */
else if (pg_strcasecmp(prev_wd, "ANALYZE") == 0)
--
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. Given that everyone who has tested this has found a different way to crash it, and that the frequency of crash reports shows no signs of slowing down, I have to think that committing it is premature. I tried to look through the patch just now and failed to make any sense of it, because of the complete absence of documentation. Two unexplained examples added to the SELECT reference page don't do it for me. I want to see an explanation of exactly what behaviors are intended to be provided (and, in view of the long TODO list that was posted awhile back, what isn't provided). And there needs to be more than zero internal documentation. A README file, or perhaps a very long file header comment, needs to be provided to explain what's supposed to happen, when, and where when processing a recursive query. (For comparison look at the README.HOT file that was created to explain the HOT patch --- something at about that level of detail would help this patch a lot. Or consider adding a section to chapter 43 in the SGML docs.) We really can't accept a patch that is so poorly documented as to be unreviewable. Unreviewable also means it'll be unmaintainable going forward. 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
Re: [PATCHES] pg_dump additional options for performance
On Thu, 2008-07-24 at 03:54 -0400, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > [80k patch] > > Surely there is a whole lot of unintended noise in this patch? > I certainly don't believe that you meant to change keywords.c > for instance. Removed, thanks. Unrelated to this patch, it seems I have some issues with my repository, judging by this and another unrelated issue reported by Martin Zaun. -- 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 24 Jul 2008 07:30:19 - *** *** 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 24 Jul 2008 07:30:19 - *** *** 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 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. + + + The limitations of pg_restore are detailed below. Index: src/bin/pg_dump/pg_backup.h === RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/bin/pg_dump/pg_backup.h,v retrieving revision 1.47 diff -c -r1.47 pg_backup.h *** src/bin/pg_dump/pg_backup.h 13 Apr 2008 03:49:
Re: [PATCHES] pg_dump additional options for performance
Simon Riggs <[EMAIL PROTECTED]> writes: > [80k patch] Surely there is a whole lot of unintended noise in this patch? I certainly don't believe that you meant to change keywords.c for instance. 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
