Re: [PATCHES] [HACKERS] WITH RECURSIVE patches 0818
On Sat, Aug 23, 2008 at 03:35:52PM +0900, Tatsuo Ishii wrote: > > > Here is new patches fixing the bug you pointed out (patches was > > > created by Yoshiyuki). Also I added your SQL to the regression > > > test, and now the patches is against CVS HEAD. For your > > > convenience I also include patches against the previous version. > > > > Thanks :) > > > > Any progress on the READMEs for this? > > I have posted kind of README (implementation.txt) along with patches > on Aug 18. Have you read it? Oops. I've now put it up with some minor edits and formatting at <http://wiki.postgresql.org/wiki/CTEReadme>. It is linked from the Commitfest page. > > Also, now that we are into August, would Asaba-san and whomever else > > like to try out the git repository? To do so, I just need a login > > name and a public key. > > I will send you later. Thanks :) 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 RECURSIVE patches 0818
On Sat, Aug 23, 2008 at 11:33:13AM +0900, Tatsuo Ishii wrote: > > I think I may have found another bug: > > > > WITH RECURSIVE t(i,j) AS ( > > VALUES (1,2) > > UNION ALL > > SELECT t2.i, t.j > > FROM ( > > SELECT 2 AS i > > UNION ALL /* Wrongly getting detected, I think */ > > SELECT 3 AS i > > ) AS t2 > > JOIN > > t > > ON (t2.i = t.i) > > ) > > SELECT * FROM t; > > ERROR: attribute number 2 exceeds number of columns 1 > > > > Is there some way to ensure that in the case of WITH RECURSIVE, > > the query to the right of UNION ALL follows only the SQL:2008 > > rules about not having outer JOINs, etc. in it, but otherwise make > > it opaque to the error-checking code? > > > > I know I didn't explain that well, but the above SQL should work > > and the error appears to stem from the parser's looking at the > > innermost UNION ALL instead of the outermost. > > Here is new patches fixing the bug you pointed out (patches was > created by Yoshiyuki). Also I added your SQL to the regression test, > and now the patches is against CVS HEAD. For your convenience I also > include patches against the previous version. Thanks :) Any progress on the READMEs for this? Also, now that we are into August, would Asaba-san and whomever else like to try out the git repository? To do so, I just need a login name and a public key. 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 RECURSIVE patches 0818
On Mon, Aug 18, 2008 at 04:38:52PM +0900, Tatsuo Ishii wrote: > Hi, > > Here is the latest WITH RECURSIVE patches against CVS HEAD. Besides > syncing to CVS HEAD, followings are main differences from previous > one: Thanks for the new patch :) I think I may have found another bug: WITH RECURSIVE t(i,j) AS ( VALUES (1,2) UNION ALL SELECT t2.i, t.j FROM ( SELECT 2 AS i UNION ALL /* Wrongly getting detected, I think */ SELECT 3 AS i ) AS t2 JOIN t ON (t2.i = t.i) ) SELECT * FROM t; ERROR: attribute number 2 exceeds number of columns 1 Is there some way to ensure that in the case of WITH RECURSIVE, the query to the right of UNION ALL follows only the SQL:2008 rules about not having outer JOINs, etc. in it, but otherwise make it opaque to the error-checking code? I know I didn't explain that well, but the above SQL should work and the error appears to stem from the parser's looking at the innermost UNION ALL instead of the outermost. 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 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
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: [HACKERS] [PATCHES] WITH RECUSIVE patches 0717
On Mon, Jul 21, 2008 at 08:19:35AM +0900, Tatsuo Ishii wrote:
> > > Thus I think we should avoid this kind of ORDER BY. Probably we should
> > > avoid LIMIT/OFFSET and FOR UPDATE as well.
> >
> > What of index-optimized SELECT max(...) ?
>
> Aggregate functions in a recursive term is prohibited by the
> standard. For example,
>
> WITH RECURSIVE x(n) AS (SELECT 1 UNION ALL SELECT max(n) FROM x)
> SELECT * FROM x;
>
> produces an error.
On the other side of UNION ALL, it's OK, right? For example,
WITH RECURSIVE x(n) AS (
SELECT max(i) FROM t
UNION ALL
SELECT n+1 FROM x WHERE n < 20
)
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] WITH RECUSIVE patches 0717
On Fri, Jul 18, 2008 at 07:56:09AM -0700, David Fetter wrote: > On Fri, Jul 18, 2008 at 10:41:20AM +0900, Tatsuo Ishii wrote: > > > > Here is the lastest WITH RECURSIVE patches against CVS HEAD created by > > > > Yoshiyuki Asaba and minor corrections by Tatsuo Ishii. > > > > > > I tried this patch vs. CVS HEAD used my usual configure option with > > > only --with-prefix set, then tried to make, and got: > > > > > > make[3]: *** No rule to make target `parse_cte.o', needed by > > > `objfiles.txt'. Stop. > > > make[3]: Leaving directory `/home/shackle/pgsql/src/backend/parser' > > > make[2]: *** [parser-recursive] Error 2 > > > make[2]: Leaving directory `/home/shackle/pgsql/src/backend' > > > make[1]: *** [all] Error 2 > > > make[1]: Leaving directory `/home/shackle/pgsql/src' > > > make: *** [all] Error 2 > > > > > > Is there something missing? > > > > Oops. I forgot to include patches against newly added files. Please > > try included patches. > > This now compiles. > > I have a test case that hangs and smashes. > > WITH t(i) AS ( > SELECT * FROM generate_series(1,5) > ) > SELECT > t1.i, > 2*t2.i > FROM > t AS t1 > JOIN > t AS t2 USING(i); > > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > > An equivalent query without RECURSIVE breaks in a different, in some > sense even more severe, way, as in it just hands out a wrong result > set: D'oh! That's what I get for sending this before waking up. It works just fine. > While this case is trivial, others are not. For example, if someone > wishes to do a k-deep summary on a parts explosion n levels deep, > n>k, one way to do this would be to JOIN the k-deep part of the path > enumeration to the parts greater than k deep. > > What would need to be fixed in order to make the above things work? 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] WITH RECUSIVE patches 0717
On Fri, Jul 18, 2008 at 10:41:20AM +0900, Tatsuo Ishii wrote: > > > Here is the lastest WITH RECURSIVE patches against CVS HEAD created by > > > Yoshiyuki Asaba and minor corrections by Tatsuo Ishii. > > > > I tried this patch vs. CVS HEAD used my usual configure option with > > only --with-prefix set, then tried to make, and got: > > > > make[3]: *** No rule to make target `parse_cte.o', needed by > > `objfiles.txt'. Stop. > > make[3]: Leaving directory `/home/shackle/pgsql/src/backend/parser' > > make[2]: *** [parser-recursive] Error 2 > > make[2]: Leaving directory `/home/shackle/pgsql/src/backend' > > make[1]: *** [all] Error 2 > > make[1]: Leaving directory `/home/shackle/pgsql/src' > > make: *** [all] Error 2 > > > > Is there something missing? > > Oops. I forgot to include patches against newly added files. Please > try included patches. This now compiles. I have a test case that hangs and smashes. WITH t(i) AS ( SELECT * FROM generate_series(1,5) ) SELECT t1.i, 2*t2.i FROM t AS t1 JOIN t AS t2 USING(i); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. An equivalent query without RECURSIVE breaks in a different, in some sense even more severe, way, as in it just hands out a wrong result set: WITH RECURSIVE t(i) AS ( VALUES(1::int) UNION ALL SELECT i+1 FROM t WHERE i < 5 ) SELECT t1.i, 2*t2.i FROM t AS t1 JOIN t AS t2 USING(i); i | ?column? ---+-- 1 |2 2 |4 3 |6 4 |8 5 | 10 (5 rows) While this case is trivial, others are not. For example, if someone wishes to do a k-deep summary on a parts explosion n levels deep, n>k, one way to do this would be to JOIN the k-deep part of the path enumeration to the parts greater than k deep. What would need to be fixed in order to make the above things work? 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] WITH RECUSIVE patches 0717
On Thu, Jul 17, 2008 at 06:40:25PM +0900, Tatsuo Ishii wrote: > Hi, > > Here is the lastest WITH RECURSIVE patches against CVS HEAD created by > Yoshiyuki Asaba and minor corrections by Tatsuo Ishii. I tried this patch vs. CVS HEAD used my usual configure option with only --with-prefix set, then tried to make, and got: make[3]: *** No rule to make target `parse_cte.o', needed by `objfiles.txt'. Stop. make[3]: Leaving directory `/home/shackle/pgsql/src/backend/parser' make[2]: *** [parser-recursive] Error 2 make[2]: Leaving directory `/home/shackle/pgsql/src/backend' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/shackle/pgsql/src' make: *** [all] Error 2 Is there something missing? > (David Fetter's psql help patches are not included. It seems his git > repository has gone). I apologize for that. I rearranged it last night because the name was not scalable, but delayed sending this out until today. It can now be found at <http://git.postgresql.org/?p=~davidfetter/with_recursive/.git;a=summary> To pull from the new location, in your .git/config, change URL from something like the following: url = git://[EMAIL PROTECTED]/git/~davidfetter/postgresql/.git to url = git://[EMAIL PROTECTED]/git/~davidfetter/with_recursive/.git > This version implements: > > - detect certain queries those are not valid acroding to the standard Great :) > I also include erroneous query examples created by Yoshiyuki (probably > will become part of regression tests). > > Remaining problmes are: > > 1) sort query names acording to the dependency This can be done at query time already using arrays per Asaba-san's suggestion. I'll add some examples to the documentation. > 2) planner always estimate 0 cost for recursion plans > 3) add regression tests > > For 1), I have proposed we limit query names to 1, in another word do > not allow mutually recursive queries. For 2) there's no good idea to > solve it, so I suggest leave it as it is now. > > For 3) I will generate regression tests as soon as possible. > > So the patches seem to be almost ready to commit IMO. Wonderful! 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 RECURSIVE updated to CVS TIP
On Wed, Jul 16, 2008 at 01:57:04PM +0900, Tatsuo Ishii wrote: > > > No idea. What do you think if we allow only one query name at the > > > moment. > > > > I'm not sure I understand what that has to do with sorting. > > > > Please find attached a place where I've found some problems sorting by > > tree by array as Asaba-san suggested. > > Humm. your problem seems to do nothing with the problem I refer to. Sorry about that. Is my problem reproducible? Is there maybe some way to include regression tests around it? > What I have in my mind is something like: > > WITH RECURSIVE foo(a, b) AS > (SELECT ... UNION SELECT...), > > bar(c, d) AS > (SELECT ... FROM foo WHERE ...UNION...) > ) > SELECT * FROM foo; > > In this there are two query names (foo, bar) and we need to detect the > dependency that bar relies on foo before processing the query. I think mutually recursive queries may have been dropped from SQL:2008. > However, as I said earlier, this kind of use case would be rare in > the real world, and I'd like to limit ourselves to having only one > query name at the moment. > > Also I suggest to concentrate on reviewing the WITH RECURSIVE > implementation itself now, rather than discussing how to use git > repository or how to write an interesting WITH RECURSIVE > applications. > > Don't get me wrong. I believe git is a great tool. But we have > limited time and need to think about the priority. Fair enough :) 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 RECURSIVE updated to CVS TIP
On Wed, Jul 16, 2008 at 09:37:25AM +0900, Tatsuo Ishii wrote:
> > On Tue, Jul 08, 2008 at 06:01:05PM +0900, Tatsuo Ishii wrote:
> > > Here is the patches he made against CVS HEAD (as of today).
> > >
> > > According to him followings are fixed with the patches:
> > >
> > > - fix crush with DISTINCT
> > > - fix creating VIEW
> > > - fix the case when recursion plan has another recursion plan under it
> > > - fix WITH RECURSIVE ...(..) SELECT ...WHERE.. returns wrong result
> > > - fix inifinit recursion with OUTER JOIN
> >
> > Great!
> >
> > I've patched psql for some partial support of WITH [RECURSIVE].
> >
> > http://git.postgresql.org/?p=~davidfetter/postgresql/.git;a=commit;h=da63f9a82b9e902b5542f788b2e6e6bc95221793
>
> Thanks. I will incorporate them with propsed patches.
Part of the point of this exercise is to make git the way to do this.
Can you please point me to a git repository where your latest changes
are so I can publish them?
> > > Not yet fixed:
> > >
> > > - detect certain queries those are not valid acroding to the standard
> > > - sort query names acording to the dependency
> >
> > Is there something in the standard on how to do this? How to sort
> > the nodes other ways?
>
> No idea. What do you think if we allow only one query name at the
> moment.
I'm not sure I understand what that has to do with sorting.
Please find attached a place where I've found some problems sorting by
tree by array as Asaba-san suggested.
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
CREATE TABLE region (
region_id SERIAL PRIMARY KEY,
region_name TEXT NOT NULL,
region_type TEXT NOT NULL,
parent_region_id INTEGER REFERENCES region(region_id),
CHECK (
CASE WHEN region_type = 'Country' THEN 1 ELSE 0 END +
CASE WHEN parent_region_id IS NULL THEN 0 ELSE 1 END = 1
)
);
CREATE UNIQUE INDEX country_top_idx
ON region(region_name) WHERE region_type = 'Country';
CREATE UNIQUE INDEX must_have_parents_idx ON
region(region_name, region_type, parent_region_id)
WHERE parent_region_id IS NOT NULL;
INSERT INTO region (region_name, region_type)
VALUES
('Brasil', 'Country'),
('United States', 'Country');
INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
region r
CROSS JOIN
(
VALUES
('Centro-Oeste','Região'),
('Nordeste','Região'),
('Norte','Região'),
('Sudeste','Região'),
('Sul','Região')
) AS v
WHERE (r.region_name, r.region_type) = ('Brasil','Country');
INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
region r
CROSS JOIN
(
VALUES
('Goiás','Estado'),
('Mato Grosso','Estado'),
('Mato Grosso do Sul','Estado'),
('Distrito Federal','Distrito Federal')
) AS v
WHERE (r.region_name, r.region_type) = ('Centro-Oeste','Região');
INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
region r
CROSS JOIN
(
VALUES
('Maranhão','Estado'),
('Piauí','Estado'),
('Ceará','Estado'),
('Rio Grande do Norte','Estado'),
('Paraíba','Estado'),
('Pernambuco','Estado'),
('Alagoas','Estado'),
('Sergipe','Estado'),
('Bahia','Estado')
) AS v
WHERE (r.region_name, r.region_type) = ('Nordeste','Região');
INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
region r
CROSS JOIN
(
VALUES
('Acre','Estado'),
('Amazonas','Estado'),
('Roraima','Estado'),
('Rondônia','Estado'),
('Pará','Estado'),
('Amapá','Estado'),
('Tocantins','Estado')
) AS v
WHERE (r.region_name, r.region_type) = ('Norte','Região');
INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_
Re: [PATCHES] [HACKERS] WITH RECURSIVE updated to CVS TIP
On Tue, Jul 08, 2008 at 06:01:05PM +0900, Tatsuo Ishii wrote: > Here is the patches he made against CVS HEAD (as of today). > > According to him followings are fixed with the patches: > > - fix crush with DISTINCT > - fix creating VIEW > - fix the case when recursion plan has another recursion plan under it > - fix WITH RECURSIVE ...(..) SELECT ...WHERE.. returns wrong result > - fix inifinit recursion with OUTER JOIN Great! I've patched psql for some partial support of WITH [RECURSIVE]. http://git.postgresql.org/?p=~davidfetter/postgresql/.git;a=commit;h=da63f9a82b9e902b5542f788b2e6e6bc95221793 > Not yet fixed: > > - detect certain queries those are not valid acroding to the standard > - sort query names acording to the dependency Is there something in the standard on how to do this? How to sort the nodes other ways? 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 RECURSIVE updated to CVS TIP
On Wed, Jul 09, 2008 at 04:43:27PM -0400, Aidan Van Dyk wrote: > * David Fetter <[EMAIL PROTECTED]> [080709 14:45]: > > On Tue, Jul 08, 2008 at 09:28:34PM -0400, Alvaro Herrera wrote: > > > > In fact, I fail to see the point of you providing the repo if > > > the upstream guys are apparently not using it ... > > > > It's *very* early days to be dismissing git entirely. We don't > > have auths fixed up yet, and I contend that that's because the > > people who have sudo on the git machine are unwilling to create > > git-shell accounts for people who need them. > > I don't think that was intended to dismiss git entirely, but only > question what the point of this particular git repo/branch is for: > http://git.postgresql.org/?p=~davidfetter/postgresql/.git;a=shortlog;h=with_recursive > > Is it just to provide an alternative way to fetch the patch? I > would have thought that anybody who can compile PostgreSQL from > source can apply a patch (if the patch available and applies > cleanly). > > The with_recursive branch doesn't seem to provide any of the nice > goodies that git could provide (i.e. patch history, merge > corresponding to various versions so you can easily see what > changed, etc) I'm really new to this git thing, but I now have access to create git-shell accounts, etc. on git.postgresql.org. Any ideas you can offer on how better to handle this would really help me. :) 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 RECURSIVE updated to CVS TIP
On Tue, Jul 08, 2008 at 09:28:34PM -0400, Alvaro Herrera wrote: > David Fetter wrote: > > On Tue, Jul 08, 2008 at 06:01:05PM +0900, Tatsuo Ishii wrote: > > > Here is the patches he made against CVS HEAD (as of today). > > > > The git repository should now match this :) > > > > http://git.postgresql.org/?p=~davidfetter/postgresql/.git;a=summary > > > > Apparently, it's easiest to clone via the following URL: > > > > http://git.postgresql.org/git/~davidfetter/postgresql/.git > > > > Is there some git repository I can pull from to make this a little > > less manual? > > In fact, I fail to see the point of you providing the repo if the > upstream guys are apparently not using it ... It's *very* early days to be dismissing git entirely. We don't have auths fixed up yet, and I contend that that's because the people who have sudo on the git machine are unwilling to create git-shell accounts for people who need them. If I get sudo access, I'll be delighted to do that stuff. There's another issue people seem to keep trying to sneak into this discussion, which is creating a high-value target for attackers, aka single sign-on. We really need to have a separate discussion of single sign-on and not hold up every infrastructure project while waiting for a feature that it is far from clear that we should even have in the first place. 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 RECURSIVE updated to CVS TIP
On Tue, Jul 08, 2008 at 06:01:05PM +0900, Tatsuo Ishii wrote: > Here is the patches he made against CVS HEAD (as of today). The git repository should now match this :) http://git.postgresql.org/?p=~davidfetter/postgresql/.git;a=summary Apparently, it's easiest to clone via the following URL: http://git.postgresql.org/git/~davidfetter/postgresql/.git Is there some git repository I can pull from to make this a little less manual? 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 RECURSIVE updated to CVS TIP
On Mon, Jul 07, 2008 at 04:22:21PM +0900, Yoshiyuki Asaba wrote: > Hi, > > > test=# explain select count(*) > > test-# from ( WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL > > SELECT DISTINCT n+1 FROM t ) > > test(# SELECT * FROM t WHERE n < 50) as t > > test-# WHERE n < 100; > > server closed the connection unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > The connection to the server was lost. Attempting reset: Failed. > > !> \q > > > > this one will kill the planner :( > > removing the (totally stupid) distinct avoids the core dump. > > Thanks. I've fixed on local repository. Asaba-san, do you have a patch against CVS HEAD or against the previous one? 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 RECURSIVE updated to CVS TIP
On Sat, Jul 05, 2008 at 10:43:57AM +0200, Hans-Juergen Schoenig wrote: > hello david, > > i did some quick testing with this wonderful patch. > it seems there are some flaws in there still: > > test=# explain select count(*) > test-# from ( WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT > DISTINCT n+1 FROM t ) > test(# SELECT * FROM t WHERE n < 50) as t > test-# WHERE n < 100; > server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > !> \q > > this one will kill the planner :( > removing the (totally stupid) distinct avoids the core dump. Any idea why this might be happening? > i found one more issue; > > -- broken: wrong result > test=# select count(*) from ( WITH RECURSIVE t(n) AS ( > SELECT 1 UNION ALL SELECT n + 1 FROM t) > SELECT * FROM t WHERE n < 50) as t WHERE n < ( > select count(*) from ( WITH RECURSIVE t(n) AS ( > SELECT 1 UNION ALL SELECT n + 1 FROM t ) > SELECT * FROM t WHERE n < 50) as t WHERE n < 100) ; > count > --- > 1 > (1 row) > > if i am not totally wrong, this should give us a different result. What result should it give, and what do you think is going wrong here? > i am looking forward to see this patch in core :). So am I :) > it is simply wonderful ... > > many thanks, Thanks go to the kind people who actually wrote the thing. I've just been using git to keep the bit-rot off it :) 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
[PATCHES] WITH RECURSIVE updated to CVS TIP
Folks, Please find patch enclosed, including some documentation. Can we see about getting this in this commitfest? 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 recursive_query-7.patch.bz2 Description: BZip2 compressed 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] Explain XML patch v2
On Wed, Jul 02, 2008 at 05:57:29PM +0200, Peter Eisentraut wrote: > It would also be interesting if EXPLAIN could optionally be a > function that returns a datum of type XML, to allow further > processing. It would be better to have a function which allows people to plug in their own serialization. A JSON or YAML one, for example, would be much lighter weight on both ends. 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] pg_dump lock timeout
On Sun, May 11, 2008 at 06:00:35AM -0700, David Gould wrote: > On Sun, May 11, 2008 at 04:30:47AM -0700, daveg wrote: > > > > Attached is a patch to add a commandline option to pg_dump to > > limit how long pg_dump will wait for locks during startup. > > Ooops, really attached this time. Can we see about getting this into the July commitfest? Dave has presented a use case complete with logs where having this could have prevented a failed backup and consequent data loss. 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] SQL: table function support
On Thu, Jun 12, 2008 at 12:33:57PM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > On Mon, Jun 09, 2008 at 05:56:59PM -0700, Neil Conway wrote: > >> I'm not necessarily opposed to this, but I wonder if we really > >> need *more* syntax variants for declaring set-returning > >> functions. The existing patchwork of features is confusing enough > >> as it is... > > > The way we declare set-returning functions ranges from odd to > > byzantine. A clear, easy-to-understand syntax (even if it's just > > sugar over something else) like Pavel's would go a long way toward > > getting developers actually to use them. > > Apparently, whether the syntax is byzantine or not is in the eye of > the beholder. I find the TABLE() syntax to be *less* clear. I went and got reports from the field. Over the years, I've had to explain at great length and with no certain success to developers at a dozen different companies how to use OUT parameters. RETURNS TABLE(...) is *much* more intuitive to those people, who have a tendency to do things like create temp tables rather than figure out the OUT parameter syntax afresh. 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] SQL: table function support
On Mon, Jun 09, 2008 at 05:56:59PM -0700, Neil Conway wrote: > On Tue, 2008-06-03 at 13:03 +0200, Pavel Stehule wrote: > > this patch add support of table functions syntax like ANSI SQL > > 2003. > > I'm not necessarily opposed to this, but I wonder if we really need > *more* syntax variants for declaring set-returning functions. The > existing patchwork of features is confusing enough as it is... The way we declare set-returning functions ranges from odd to byzantine. A clear, easy-to-understand syntax (even if it's just sugar over something else) like Pavel's would go a long way toward getting developers actually to use them. 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] Feature: give pg_dump a WHERE clause expression
On Sun, Jun 01, 2008 at 02:08:47PM -0500, Davy Durham wrote: > On Sun, 2008-06-01 at 10:43 -0400, Tom Lane wrote: > > Davy Durham <[EMAIL PROTECTED]> writes: > > > I have added a new parameter, -w/--where=EXPR This lets you > > > specify an expression that will be used in a WHERE clause when > > > the data is dumped. > > > > This seems pretty poorly thought out. It can hardly work in a > > dump of more than one table, which means that there's not any real > > reason to use pg_dump at all. Just do a COPY (SELECT ...) TO > > somefile. > > Well, my primary reason for writing the patch was to have a standard > SQL file using INSERT statements in order to load the some of a > table's data into a database other than postgresql which does not > support the COPY statement. Have you tried DBI-Link <http://pgfoundry.org/projects/dbi-link/>? 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: Updated patch (Re: [PATCHES] WITH RECURSIVE patch V0.1)
On Sat, May 24, 2008 at 05:08:51AM -0700, David Fetter wrote: > On Sun, May 18, 2008 at 08:51:29PM +0900, Tatsuo Ishii wrote: > > WITH RECURSIVE patch V0.1 > > Please find updated patch with bug fixes from Yoshiyuki Asaba and > Michael Meskes. Any mistakes in it are mine. :) As promised, the mistakes were mine, and I did not include Asaba-san's fix. :P Please find enclosed another patch remedying this problem and documenting some of what WITH [RECURSIVE] does. I'm also thinking there should be updates to psql's \h and tab completion code. 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 recursive_query-3.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Updated patch (Re: [PATCHES] WITH RECURSIVE patch V0.1)
On Sun, May 18, 2008 at 08:51:29PM +0900, Tatsuo Ishii wrote: > WITH RECURSIVE patch V0.1 Please find updated patch with bug fixes from Yoshiyuki Asaba and Michael Meskes. Any mistakes in it are mine. :) 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 recursive_query-2.patch.gz Description: GNU Zip compressed 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] WITH RECURSIVE patch V0.1
On Sat, May 24, 2008 at 05:01:11AM +0900, Yoshiyuki Asaba wrote: > Hi, > > From: David Fetter <[EMAIL PROTECTED]> > Subject: Re: [PATCHES] WITH RECURSIVE patch V0.1 > Date: Fri, 23 May 2008 11:26:30 -0700 > > > Where is the new patch? > > I will create the revised patch on June. This is a patch for this > problem. Thanks very much :) 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] WITH RECURSIVE patch V0.1
On Sat, May 24, 2008 at 03:21:01AM +0900, Yoshiyuki Asaba wrote: > Hi, > > From: David Fetter <[EMAIL PROTECTED]> > Subject: Re: [PATCHES] WITH RECURSIVE patch V0.1 > Date: Sun, 18 May 2008 11:47:37 -0700 > > > I tried a bunch of different queries, and so far, only these two > > haven't worked. Any ideas what I'm doing wrong here? > > > > WITH RECURSIVE t(n) AS ( > > SELECT 1 > > UNION ALL > > SELECT n+1 > > FROM t > > WHERE n < 100 > > ) > > SELECT * FROM t; > > ERROR: cannot extract attribute from empty tuple slot > > Thank you for the report. I've fixed. > > postgres=# WITH RECURSIVE t(n) AS ( > SELECT 1 > UNION ALL > SELECT n+1 > FROM t > WHERE n < 100 > ) > SELECT count(*) FROM t; > count > --- >100 > (1 row) > > Regards, > -- > Yoshiyuki Asaba > [EMAIL PROTECTED] Great! Where is the new patch? 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] WITH RECURSIVE patch V0.1
On Mon, May 19, 2008 at 05:57:17PM +0900, Yoshiyuki Asaba wrote: > Hi, > > > I think it's the other way around. The server should not emit > > infinite number of records. > > How about adding new GUC parameter "max_recursive_call"? Couldn't we just have it pay attention to the existing max_stack_depth? 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] WITH RECURSIVE patch V0.1
On Mon, May 19, 2008 at 12:21:20AM -0400, Gregory Stark wrote: > "Zoltan Boszormenyi" <[EMAIL PROTECTED]> writes: > > Also, it seems there are no infinite recursion detection: > > > > # with recursive x(level, parent, child) as ( > >select 1::integer, * from test_connect_by where parent is null > >union all > >select x.level + 1, base.* from test_connect_by as base, x where > > base.child > > = x.child > > ) select * from x; > > ... it waits and waits and waits ... > > Well, psql might wait and wait but it's actually receiving rows. A > cleverer client should be able to deal with infinite streams of > records. That would be a very good thing for libpq (and its descendants) to have :) > I think DB2 does produce a warning if there is no clause it can > determine will bound the results. But that's not actually reliable. I'd think not, as it's (in some sense) a Halting Problem. > It's quite possible to have clauses which will limit the output but > not in a way the database can determine. Consider for example a > tree-traversal for a binary tree stored in a recursive table > reference. The DBA might know that the data contains no loops but > the database doesn't. I seem to recall Oracle's implementation can do this traversal on write operations, but maybe that's just their marketing. 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] WITH RECURSIVE patch V0.1
On Sun, May 18, 2008 at 08:51:29PM +0900, Tatsuo Ishii wrote: > WITH RECURSIVE patch V0.1 > > Here are patches to implement WITH RECURSIVE clause. There are some > limitiations and TODO items(see the "Current limitations" section > below). Comments are welcome. > > 1. Credit > > These patches were developed by Yoshiyuki Asaba ([EMAIL PROTECTED]) > with some discussions with Tatsuo Ishii ([EMAIL PROTECTED]). This is really great! Kudos to all who made this happen :) I tried a bunch of different queries, and so far, only these two haven't worked. Any ideas what I'm doing wrong here? WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT * FROM t; ERROR: cannot extract attribute from empty tuple slot WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT * FROM t; ERROR: cannot extract attribute from empty tuple slot 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] Patch to change psql default banner v6
On Thu, May 15, 2008 at 03:21:37PM -0400, Bruce Momjian wrote: > Alvaro Herrera wrote: > > > > I'm OK with thisG but please move the printSSLInfo() call just before > > echoing the help line. > > Oh, good catch, moved. I also moved the Win32 code page message up too. > Patch attached. > > I hacked up an example that shows both SSL and Win32 code page messages: I believe there's a bug in this patch, namely that the warnings when there's a server-client mismatch only appear at startup time. This is a pretty clear POLA violation, IMHO. On my laptop, I have two pg instances running: 8.3.0 on port 5432, CVS TIP on 2225. Here's what I get if I invoke psql from the command line: $ psql -p 5432 postgres Welcome to psql 8.4devel (server 8.3.0), the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit WARNING: You are connected to a server with major version 8.3, but your psql client is major version 8.4. Some backslash commands, such as \d, might not work properly. Here's what I get if I use \c, having connected to CVS TIP first: [EMAIL PROTECTED] \c - - - 5432 You are now connected to database "postgres" at port "5432". I think that the warning should be consistently there on connect instead of just at program start. Not coincidentally, moving all the checks into one spot, i.e. making startup.c and command.c call and test the same things to connect to a database, advances my Evil Plan™ to make more interesting things happen when switching versions :) 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] Patch to change psql default banner v6
On Fri, May 16, 2008 at 01:22:55AM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > I believe there's a bug in this patch, namely that the warnings when > > there's a server-client mismatch only appear at startup time. > > Please do not blame this patch for a problem that has been there all > along. > > I don't say that the point doesn't need investigation, but blaming > the patch-at-hand for the issue is just misleading. The patch at hand, as you point out, emphasizes a problem that's been there all along, namely that \c doesn't do the same things that command line connection does. I'm volunteering to make them use the same methods :) 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] Patch to change psql default banner v6
On Thu, May 15, 2008 at 06:57:12PM -0400, Tom Lane wrote: > Andrew Dunstan <[EMAIL PROTECTED]> writes: > > David Fetter wrote: > >> I hate to bike-shed this even further, but I'd like to make those > >> "incompatibility" messages just go away by making 8.4's psql (and > >> all those going forward) support every living version of Postgres > >> at the time of their release, > > > I think you must have been out in the sun too long. > > Hey, he's welcome to try to do it. But it's utterly unrelated to > the patch at hand, and we are not holding up the patch at hand until > something like that happens. Nor am I suggesting holding up this patch for that reason :) 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] Patch to change psql default banner v6
On Thu, May 15, 2008 at 06:55:31PM -0400, Andrew Dunstan wrote: > David Fetter wrote: >> >> I hate to bike-shed this even further, but I'd like to make those >> "incompatibility" messages just go away by making 8.4's psql (and >> all those going forward) support every living version of Postgres >> at the time of their release, so 8.4's psql would be able to talk >> seamlessly to Postgres 7.4 :) > > I think you must have been out in the sun too long. One thing I really treasure about working on the Postgres project is frank feedback. :) > Just look at the pg_dump code if you want something of an idea of > what this would involve. Given that each previous version tied backslash commands to some particular chunk of SQL, what would be the problem with either immediately or lazily setting those to the chunks of SQL already present in previous versions? 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] Patch to change psql default banner v6
On Thu, May 15, 2008 at 12:09:25PM -0400, Alvaro Herrera wrote: > Andrew Dunstan wrote: > > > Welcome to UI development. There is always *far* more argument of minor > > matters of appearance than over anything else, in my experience. > > Which is a good thing (in this case at least), because otherwise we > would end up with a crappy UI just because a single person thinks it's > "good enough". I hate to bike-shed this even further, but I'd like to make those "incompatibility" messages just go away by making 8.4's psql (and all those going forward) support every living version of Postgres at the time of their release, so 8.4's psql would be able to talk seamlessly to Postgres 7.4 :) Cheers, David (well, not really bike-shedding, but trying to propose a feature that reduces the amount of UI clutter) -- 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] [GENERAL] Making sure \timing is on
On Tue, May 13, 2008 at 01:53:33PM -0700, David Fetter wrote:
> On Tue, May 13, 2008 at 11:36:57AM -0400, Tom Lane wrote:
> > David Fetter <[EMAIL PROTECTED]> writes:
> > >>> Surely this is merely proof of concept and not a complete patch.
> > >>
> > >> Next patch attached :)
> >
> > Uh, my point was that the agreement was to do this to *all* of
> > psql's toggling backslash commands, not only \timing.
>
> Done :)
Ugh. This time with the correct patch attached :P
Cheers,
David (not having much luck with attachments)
--
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
Index: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.203
diff -c -c -r1.203 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 8 May 2008 17:04:26 - 1.203
--- doc/src/sgml/ref/psql-ref.sgml 13 May 2008 20:52:29 -
***
*** 673,685
! \a
! If the current table output format is unaligned, it is switched to
aligned.
! If it is not unaligned, it is set to unaligned. This command is
! kept for backwards compatibility. See \pset for a
! more general solution.
--- 673,687
!\a [ ON |
! OFF ]
! Without parameter, toggle format between aligned and
! unaligned. With parameter, set it. This command is kept for
! backwards compatibility. See \pset for a more
! general solution.
***
*** 1292,1305
! \H
! Turns on HTML query output format. If the
! HTML format is already on, it is switched
! back to the default aligned text format. This command is for
! compatibility and convenience, but see \pset
! about setting other output options.
--- 1294,1308
!\H [ ON |
! OFF ]
! Without parameter, toggles between HTML and
! aligned query output format. With paramter, sets it.
! This command is for compatibility and convenience, but see
! \pset about setting other output options.
***
*** 1867,1876
!\timing
! Toggles a display of how long each SQL statement takes, in
milliseconds.
--- 1870,1882
!\timing [ON | OFF]
! Without parameter, toggles a display of how long each SQL
! statement takes, in milliseconds. With parameter, sets same.
Index: src/bin/psql/command.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.188
diff -c -c -r1.188 command.c
*** src/bin/psql/command.c 8 May 2008 17:04:26 - 1.188
--- src/bin/psql/command.c 13 May 2008 20:52:29 -
***
*** 180,189
*/
if (strcmp(cmd, "a") == 0)
{
! if (pset.popt.topt.format != PRINT_ALIGNED)
! success = do_pset("format", "aligned", &pset.popt,
pset.quiet);
else
! success = do_pset("format", "unaligned", &pset.popt,
pset.quiet);
}
/* \C -- override table title (formerly change HTML caption) */
--- 180,199
*/
if (strcmp(cmd, "a") == 0)
{
! char *opt = psql_scan_slash_option(scan_state,
!
OT_NORMAL, NULL, true);
! if (opt)
! success = do_pset("format",
!
ParseVariableBool(opt) ? "aligned" : "unaligned",
! &pset.popt,
pset.quiet);
else
! {
! if (pset.popt.topt.format != PRINT_ALIGNED)
! success = do_pset("format", "aligned",
&pset.popt, pset.quiet);
! else
! success = do_pset("format", "unaligned",
&pset.popt, pset.quiet);
! }
! free(opt);
Re: [PATCHES] [GENERAL] Making sure \timing is on
On Tue, May 13, 2008 at 11:36:57AM -0400, Tom Lane wrote:
> David Fetter <[EMAIL PROTECTED]> writes:
> >>> Surely this is merely proof of concept and not a complete patch.
> >>
> >> Next patch attached :)
>
> Uh, my point was that the agreement was to do this to *all* of
> psql's toggling backslash commands, not only \timing.
Done :)
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
Index: src/bin/psql/describe.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.168
diff -c -c -r1.168 describe.c
*** src/bin/psql/describe.c 2 May 2008 10:16:16 - 1.168
--- src/bin/psql/describe.c 4 May 2008 23:54:53 -
***
*** 307,315
"WHEN t.typlen < 0\n"
" THEN CAST('var' AS
pg_catalog.text)\n"
"ELSE CAST(t.typlen AS
pg_catalog.text)\n"
! " END AS \"%s\",\n",
gettext_noop("Internal name"),
! gettext_noop("Size"));
appendPQExpBuffer(&buf,
" pg_catalog.obj_description(t.oid, 'pg_type')
as \"%s\"\n",
gettext_noop("Description"));
--- 307,325
"WHEN t.typlen < 0\n"
" THEN CAST('var' AS
pg_catalog.text)\n"
"ELSE CAST(t.typlen AS
pg_catalog.text)\n"
! " END AS \"%s\",\n"
! "
pg_catalog.array_to_string(\n"
! " ARRAY(\n"
! " SELECT
e.enumlabel\n"
! " FROM
pg_catalog.pg_enum e\n"
! " WHERE e.enumtypid
= t.oid\n"
! " ORDER BY e.oid\n"
! " ),\n"
! " E'\\n'\n"
! " ) AS \"%s\",\n",
gettext_noop("Internal name"),
! gettext_noop("Size"),
! gettext_noop("Elements"));
appendPQExpBuffer(&buf,
" pg_catalog.obj_description(t.oid, 'pg_type')
as \"%s\"\n",
gettext_noop("Description"));
--
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] [GENERAL] Making sure \timing is on
On Tue, May 13, 2008 at 08:14:51AM -0700, David Fetter wrote:
> On Tue, May 13, 2008 at 10:47:40AM -0400, Alvaro Herrera wrote:
> > Tom Lane escribió:
> > > Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > > > David Fetter escribi?:
> > > >> Thanks for the heads-up :)
> > > >>
> > > >> Second patch attached, this time with some docs.
> > >
> > > > Added to July commitfest.
> > >
> > > Surely this is merely proof of concept and not a complete patch.
> >
> > David, ya heard da man :-)
>
> Next patch attached :)
>
> Cheers,
> David (free() the malloc()s!)
*Sigh*
This time with the patch actually attached :P
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
Index: doc/src/sgml/ref/psql-ref.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/psql-ref.sgml,v
retrieving revision 1.203
diff -c -c -r1.203 psql-ref.sgml
*** doc/src/sgml/ref/psql-ref.sgml 8 May 2008 17:04:26 - 1.203
--- doc/src/sgml/ref/psql-ref.sgml 13 May 2008 14:41:18 -
***
*** 1867,1876
!\timing
! Toggles a display of how long each SQL statement takes, in
milliseconds.
--- 1867,1879
!\timing [ON | OFF]
! Without parameter, toggles a display of how long each SQL
! statement takes, in milliseconds. With parameter, sets same.
Index: src/bin/psql/command.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.188
diff -c -c -r1.188 command.c
*** src/bin/psql/command.c 8 May 2008 17:04:26 - 1.188
--- src/bin/psql/command.c 13 May 2008 14:41:21 -
***
*** 884,890
/* \timing -- toggle timing of queries */
else if (strcmp(cmd, "timing") == 0)
{
! pset.timing = !pset.timing;
if (!pset.quiet)
{
if (pset.timing)
--- 903,914
/* \timing -- toggle timing of queries */
else if (strcmp(cmd, "timing") == 0)
{
! char *opt = psql_scan_slash_option(scan_state,
!
OT_NORMAL, NULL, false);
! if (opt)
! pset.timing = ParseVariableBool(opt);
! else
! pset.timing = !pset.timing;
if (!pset.quiet)
{
if (pset.timing)
***
*** 892,897
--- 916,922
else
puts(_("Timing is off."));
}
+ free(opt);
}
/* \unset */
--
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] [GENERAL] Making sure \timing is on
On Tue, May 13, 2008 at 10:47:40AM -0400, Alvaro Herrera wrote: > Tom Lane escribió: > > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > > David Fetter escribi?: > > >> Thanks for the heads-up :) > > >> > > >> Second patch attached, this time with some docs. > > > > > Added to July commitfest. > > > > Surely this is merely proof of concept and not a complete patch. > > David, ya heard da man :-) Next patch attached :) Cheers, David (free() the malloc()s!) -- 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] Fix \dT enum in psql
On Sun, May 04, 2008 at 07:49:25PM -0400, Tom Lane wrote:
> Andrew Dunstan <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> But it'll only be in \dT+ anyway, no?
>
> > Not in this patch.
>
> Hmmm ... given that a long list of enum members would bloat the
> output quite a lot, I think I'd vote for putting it in \dT+.
Here's one where it's only in \dT+
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
Index: src/bin/psql/describe.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.168
diff -c -c -r1.168 describe.c
*** src/bin/psql/describe.c 2 May 2008 10:16:16 - 1.168
--- src/bin/psql/describe.c 4 May 2008 23:54:53 -
***
*** 307,315
"WHEN t.typlen < 0\n"
" THEN CAST('var' AS
pg_catalog.text)\n"
"ELSE CAST(t.typlen AS
pg_catalog.text)\n"
! " END AS \"%s\",\n",
gettext_noop("Internal name"),
! gettext_noop("Size"));
appendPQExpBuffer(&buf,
" pg_catalog.obj_description(t.oid, 'pg_type')
as \"%s\"\n",
gettext_noop("Description"));
--- 307,325
"WHEN t.typlen < 0\n"
" THEN CAST('var' AS
pg_catalog.text)\n"
"ELSE CAST(t.typlen AS
pg_catalog.text)\n"
! " END AS \"%s\",\n"
! "
pg_catalog.array_to_string(\n"
! " ARRAY(\n"
! " SELECT
e.enumlabel\n"
! " FROM
pg_catalog.pg_enum e\n"
! " WHERE e.enumtypid
= t.oid\n"
! " ORDER BY e.oid\n"
! " ),\n"
! " E'\\n'\n"
! " ) AS \"%s\",\n",
gettext_noop("Internal name"),
! gettext_noop("Size"),
! gettext_noop("Elements"));
appendPQExpBuffer(&buf,
" pg_catalog.obj_description(t.oid, 'pg_type')
as \"%s\"\n",
gettext_noop("Description"));
--
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] Fix \dT enum in psql
On Sun, May 04, 2008 at 06:40:51PM -0400, Andrew Dunstan wrote: > > > David Fetter wrote: >> Folks, >> >> In psql, \dT doesn't show the elements for enums. Please find >> patch vs. CVS TIP attached which fixes this per the following TODO >> item: >> >> http://archives.postgresql.org/pgsql-hackers/2008-01/msg00826.php > > I notice that this patch adds an "Elements" column to the output of > \dT, which will only be used by enum types. That seems rather ... > cluttered. Is the name too long, or did you want it rolled into one of the other columns, or...? 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] Fix \dT enum in psql
On Thu, May 01, 2008 at 10:53:00PM -0400, Andrew Dunstan wrote: > David Fetter wrote: >> Folks, >> >> In psql, \dT doesn't show the elements for enums. Please find >> patch vs. CVS TIP attached which fixes this per the following TODO >> item: >> >> http://archives.postgresql.org/pgsql-hackers/2008-01/msg00826.php > > I don't have a particular problem with this patch - indeed the query > in it looks eerily familiar :-) I can't imagine why ;) > However, I'm wondering if we should wait until a possible rework of > the mechanics of enums as recently discussed? Or we could put it in > and that way it would have to be redone when enums are rejigged. I'm thinking getting it in there soon will keep the bitrot to a minimum. One thing it doesn't include is regression tests. Shall I add a few? 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] Patch to add a feature to pg_standby
On Wed, Apr 30, 2008 at 03:43:54PM +0100, Simon Riggs wrote: > On Wed, 2008-04-30 at 07:31 -0700, David Fetter wrote: > > On Wed, Apr 30, 2008 at 12:49:44PM +0100, Simon Riggs wrote: > > > On Wed, 2008-04-30 at 11:29 +0100, Heikki Linnakangas wrote: > > > > This could be implemented by a "pass-through" restore_command, > > > > that calls pg_standby, and does the custom action when > > > > pg_standby returns successfully. > > > > > > Yes, that's the preferred route for most cases. > > > > > > pg_standby was designed to be customisable, so if it works for > > > Chris, thats OK. > > > > > > After some mulling on this, I'm not sure we need to include this > > > in pg_standby however. If we did we'd end up having before/after > > > commands and retry options etc. > > > > Would those be good things to have as a whole package? > > I think the answer is we already do. It's called bash. Just a stray thought...bash doesn't run very well on common Windows installations. 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] Patch to add a feature to pg_standby
On Wed, Apr 30, 2008 at 12:49:44PM +0100, Simon Riggs wrote: > On Wed, 2008-04-30 at 11:29 +0100, Heikki Linnakangas wrote: > > This could be implemented by a "pass-through" restore_command, > > that calls pg_standby, and does the custom action when pg_standby > > returns successfully. > > Yes, that's the preferred route for most cases. > > pg_standby was designed to be customisable, so if it works for > Chris, thats OK. > > After some mulling on this, I'm not sure we need to include this in > pg_standby however. If we did we'd end up having before/after > commands and retry options etc. Would those be good things to have as a whole package? 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
[PATCHES] Fix \dT enum in psql
Folks,
In psql, \dT doesn't show the elements for enums. Please find patch
vs. CVS TIP attached which fixes this per the following TODO item:
http://archives.postgresql.org/pgsql-hackers/2008-01/msg00826.php
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
Index: src/bin/psql/describe.c
===
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.166
diff -c -c -r1.166 describe.c
*** src/bin/psql/describe.c 30 Mar 2008 18:10:20 - 1.166
--- src/bin/psql/describe.c 8 Apr 2008 20:29:01 -
***
*** 310,317
--- 310,328
" END AS \"%s\",\n",
gettext_noop("Internal name"),
gettext_noop("Size"));
+
appendPQExpBuffer(&buf,
+ " pg_catalog.array_to_string(\n"
+ "ARRAY(\n"
+ "SELECT e.enumlabel\n"
+ " FROM pg_catalog.pg_enum e\n"
+ " WHERE e.enumtypid = t.oid\n"
+ " ORDER BY e.oid\n"
+ " ),\n"
+ " E'\\n'\n"
+ " ) AS \"%s\",\n"
" pg_catalog.obj_description(t.oid, 'pg_type')
as \"%s\"\n",
+ gettext_noop("Elements"),
gettext_noop("Description"));
appendPQExpBuffer(&buf, "FROM pg_catalog.pg_type t\n"
--
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] Database owner installable modules patch
On Sun, Apr 06, 2008 at 11:29:50PM +0100, Gregory Stark wrote: > I wonder if there's much of a use case for any statements aside from > CREATE statements. Yes. Some modules could have COPY or equivalent in them, as they could easily contain data. 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] Consistent \d commands in psql
On Tue, Apr 01, 2008 at 12:58:44AM -0400, Tom Lane wrote: > Gregory Stark <[EMAIL PROTECTED]> writes: > >>> One --perhaps nice, perhaps not-- property of this is that if > >>> you defined a function named "rtrim" and then did "\df rtrim" it > >>> would show you _both_ the system and user function and make it > >>> easier to see the conflict. Whereas the other behaviour I > >>> proposed would hide the system function which might exacerbate > >>> the user's confusion. > > >> Another way we could approach this is ... > > > That doesn't satisfy the original source of the annoyance which is > > that \df spams your terminal with ten screens of system functions > > with your user functions hidden amongst them. > > Sure, but I think the core objection there is that there is no easy > way to see only the user-defined functions. Given your point quoted > first above, I'm unconvinced that should be the default behavior. When we have a bad default--and I'd argue that for anyone not developing PostgreSQL itself, showing system functions is a bad default--we should change it to something sane. 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] script binaries renaming
On Mon, Mar 24, 2008 at 09:19:42PM +0100, Zdenek Kotala wrote: > Tom Lane napsal(a): >> Bruce Momjian <[EMAIL PROTECTED]> writes: >>> Where are we on this? Tom thinks we don't want this. TODO has: >>> * Prefix command-line utilities like createuser with 'pg_' >>> http://archives.postgresql.org/pgsql-hackers/2007-06/msg00025.php >> >>> I think we need to make a decision. >> >> Well, I don't have any particular objection to adding pg_ prefixes >> as alternate names for the existing scripts. However, it's not >> clear what is the point unless we have the intention to remove the >> old names at some time in the foreseeable future. And the >> consensus of the previous thread on -patches seemed to be that >> nobody except Zdenek was very eager to do that. > > Yeah, I have to had two reason for this patch. First is my personal, > because I don't like these names since 1999. And second is that > Solaris architects do not like these names. Especially createdb and > createuser. It could clash with some system utility. +1 for renaming the utilities. Not stomping on the global namespace is one place where MySQL is really out ahead of us. 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] Fix pgstatindex using for large indexes
On Mon, Feb 25, 2008 at 11:50:11AM -0500, Tom Lane wrote: > Andrew Dunstan <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >>> Is there any currently supported platform which does not have > >>> uint64? > >> > >> I don't know, and neither do you. > > > Maybe we should look at some reasonable way of getting the info > > out of a compiled instance. How about if we get pg_config to > > output the value of INT64_IS_BUSTED? > > We know all the buildfarm machines have working int64, because > they'd fail the bigint regression test if not. That's not the point > here. If we don't have buildfarm coverage for machines where INT64_IS_BUSTED, how do we know we support those architectures at all? 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] 2WRS [WIP]
On Mon, Feb 04, 2008 at 07:10:10PM +0100, [EMAIL PROTECTED] wrote: > Hi to all. > > I'm implementing a refinement of the External Sorting (ES) algorithm > [postgresql-8.2.5/src/backend/utils/sort/tuplesort.c] . The patch is > still WIP. Patches for new features need to be on CVS TIP, as the project does not add new features to stable releases. How do you want to be named on this? 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] Auto-explain patch
On Mon, Jan 28, 2008 at 08:49:23PM +, Dean Rasheed wrote:
>
> > On Mon, Jan 28, 2008 at 07:55:53PM +, Dean Rasheed wrote:
> >>
> >>> Dean,
> >>>
> >>> Maybe I missed something obvious here, but how does this patch handle
> >>> the situation where people have turned on INTEGER_DATETIMES?
> >>>
> >>> Cheers,
> >>> David.
> >>> --
> >>> David Fetter 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
> >>
> >> Sorry, I don't understand. I am new to this code, but I can't see
> >> how the INTEGER_DATETIMES flag will affect this code. I am using the
> >> macros and functions from instrument.h and explain.c for timing,
> >> which appear to use gettimeofday() or QueryPerformanceCounter(),
> >> coverting the result to a double to report the total time spent
> >> running the query.
> >
> > It's the double part I don't quite get. Shouldn't that be an int64 in
> > the case of INTEGER_DATETIMES?
> >
> > Cheers,
> > David.
> >
>
> All the times are 64-bit integers (or at least structures with 2
> 32-bit integers in them) until the end, when the elapsed time is
> converted to a double so that the query runtime can be printed out
> in ms ("Query runtime: %.3f ms"). This is the same as EXPLAIN
> ANALYSE, except in that case it is the total runtime ("Total
> runtime: %.3f ms\n") that gets reported, including startup/shutdown
> trigger times.
Thanks for the explanation, and sorry for the noise :)
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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] Auto-explain patch
On Mon, Jan 28, 2008 at 07:55:53PM +, Dean Rasheed wrote: > > > Dean, > > > > Maybe I missed something obvious here, but how does this patch handle > > the situation where people have turned on INTEGER_DATETIMES? > > > > Cheers, > > David. > > -- > > David Fetter 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 > > Sorry, I don't understand. I am new to this code, but I can't see > how the INTEGER_DATETIMES flag will affect this code. I am using the > macros and functions from instrument.h and explain.c for timing, > which appear to use gettimeofday() or QueryPerformanceCounter(), > coverting the result to a double to report the total time spent > running the query. It's the double part I don't quite get. Shouldn't that be an int64 in the case of INTEGER_DATETIMES? Cheers, David. > > As far as I can see, the INTEGER_DATETIMES flag doesn't affect this > code. Am I missing something? > > Dean. > > _____ > Free games, great prizes - get gaming at Gamesbox. > http://www.searchgamesbox.com -- 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Auto-explain patch
On Mon, Jan 28, 2008 at 09:21:50AM +, Dean Rasheed wrote: > > This is the patch allows logging of the explain plan for each query run, as > described here: > > http://archives.postgresql.org/pgsql-performance/2008-01/msg00245.php > > I hope this is useful. > > Dean. Dean, Maybe I missed something obvious here, but how does this patch handle the situation where people have turned on INTEGER_DATETIMES? 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Fri, Nov 30, 2007 at 12:34:05PM +0530, NikhilS wrote: > Hi, > > Another reason to go along with triggers is that "COPY" honors > triggers, but does not honor rules. While trying to do bulk inserts > into a parent of partitioned tables where rules are being employed, > the COPY operation will not be so straightforward. Folks, Does my latest patch attached address this well enough? 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 Index: doc/src/sgml/ddl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.77 diff -c -r1.77 ddl.sgml *** doc/src/sgml/ddl.sgml 28 Nov 2007 15:42:31 - 1.77 --- doc/src/sgml/ddl.sgml 1 Dec 2007 04:55:46 - *** *** 2510,2564 If data will be added only to the latest partition, we can ! set up a very simple rule to insert data. We must ! redefine this each month so that it always points to the ! current partition: ! ! ! CREATE OR REPLACE RULE measurement_current_partition AS ! ON INSERT TO measurement ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex set of rules as shown below: ! CREATE RULE measurement_insert_y2004m02 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ... ! CREATE RULE measurement_insert_y2005m12 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! CREATE RULE measurement_insert_y2006m01 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ! ! Note that the WHERE clause in each rule ! exactly matches the CHECK ! constraint for its partition. --- 2510,2589 If data will be added only to the latest partition, we can ! set up a very simple trigger function to insert data. We must ! redefine this each month so that it always points to the current ! partition: ! ! ! CREATE OR REPLACE FUNCTION measurement_current_partition() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! INSERT INTO measurement_y2006m01 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! RETURN NEW; ! END; ! $$; ! ! ! The first time we create the table, we create a trigger which ! calls the above trigger function. When we replace the trigger ! function, we don't need to replace the trigger. ! ! ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex trigger function as shown ! below: ! CREATE OR REPLACE FUNCTION measurement_insert() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! IF ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) THEN ! ! INSERT INTO measurement_y2004m02 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! ELSIF ( logdate >= DATE
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Thu, Nov 29, 2007 at 11:42:18AM -0500, Bruce Momjian wrote: > Joshua D. Drake wrote: > > Tom Lane wrote: > > > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > >> Rules are extremely slow in comparisons and not anywhere near as > > >> flexible. As I said up post yesterday... they work well in the basic > > >> partitioning configuration but anything else they are extremely > > >> deficient. > > > > > > I think that the above claim is exceedingly narrow-minded. > > > > We are talking about partitioning. It is supposed to be narrow-minded. > > Sure, but look at all the confusion we have had just on this list about > it. We had better state why triggers should be used in place of rules > _for_ _partitioning_ or that confusion will continue. Please find enclosed a patch with use cases for each. 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 Index: doc/src/sgml/ddl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.77 diff -c -r1.77 ddl.sgml *** doc/src/sgml/ddl.sgml 28 Nov 2007 15:42:31 - 1.77 --- doc/src/sgml/ddl.sgml 29 Nov 2007 17:51:46 - *** *** 2510,2564 If data will be added only to the latest partition, we can ! set up a very simple rule to insert data. We must ! redefine this each month so that it always points to the ! current partition: ! ! ! CREATE OR REPLACE RULE measurement_current_partition AS ! ON INSERT TO measurement ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex set of rules as shown below: ! CREATE RULE measurement_insert_y2004m02 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ... ! CREATE RULE measurement_insert_y2005m12 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! CREATE RULE measurement_insert_y2006m01 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ! ! Note that the WHERE clause in each rule ! exactly matches the CHECK ! constraint for its partition. --- 2510,2589 If data will be added only to the latest partition, we can ! set up a very simple trigger function to insert data. We must ! redefine this each month so that it always points to the current ! partition: ! ! ! CREATE OR REPLACE FUNCTION measurement_current_partition() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! INSERT INTO measurement_y2006m01 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! RETURN NEW; ! END; ! $$; ! ! ! The first time we create the table, we create a trigger which ! calls the above trigger function. When we replace the trigger ! function, we don't need to replace the trigger. ! ! ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Thu, Nov 29, 2007 at 12:55:53AM -0500, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > On Wed, Nov 28, 2007 at 09:58:26PM -0500, Jonah H. Harris wrote: > >> On Nov 28, 2007 3:53 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > >>> Entirely removing the example of how to do it with rules doesn't > >>> seem like a good idea. > >> > >> Agreed. > > > Do you have an example of one use case where using RULEs rather > > than TRIGGERs is a good idea? > > The argument I made for keeping the example around is not dependent > on the assumption that using a rule is a good idea. It's dependent > on the established fact that we have recommended that in prior > releases, and therefore people are going to be seeing that construct > in real databases. We've corrected mistakes in the manual before :) 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Wed, Nov 28, 2007 at 09:58:26PM -0500, Jonah H. Harris wrote: > On Nov 28, 2007 3:53 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > > Entirely removing the example of how to do it with rules doesn't > > seem like a good idea. > > Agreed. Do you have an example of one use case where using RULEs rather than TRIGGERs is a good idea? 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Wed, Nov 28, 2007 at 10:06:01PM -0300, Alvaro Herrera wrote: > David Fetter wrote: > > > Greg Sabino Mullane managed to contrive an example where RULEs > > might conceivably be the least-bad way to do this, that being a > > machine where no PLs may be installed. > > Perhaps this just means we should consider installing plpgsql by > default. I'm all for that :) 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Wed, Nov 28, 2007 at 05:17:38PM -0500, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > On Wed, Nov 28, 2007 at 03:53:04PM -0500, Tom Lane wrote: > >> Entirely removing the example of how to do it with rules doesn't > >> seem like a good idea. > > > It does to me. I haven't found a case yet where rules worked even > > as well as triggers. > > I don't have a problem with emphasizing triggers as the preferred > solution, but we should keep the older example, if only because > people are going to see DB schemas that use that approach, and they > won't understand what's going on (or realize they could convert) if > they've not seen an example. Greg Sabino Mullane managed to contrive an example where RULEs might conceivably be the least-bad way to do this, that being a machine where no PLs may be installed. Telling people how to do this is not *quite* as bad as describing how to do EAV, but it's pretty close. 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Wed, Nov 28, 2007 at 03:53:04PM -0500, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > Best practices for partitioning so far have shown that TRIGGERs > > are better than RULEs for most cases. Please find attached a > > patch which reflects this. > > Entirely removing the example of how to do it with rules doesn't > seem like a good idea. It does to me. I haven't found a case yet where rules worked even as well as triggers. 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Wed, Nov 28, 2007 at 12:41:20PM -0800, David Fetter wrote: > On Wed, Nov 28, 2007 at 12:39:04PM -0800, Joshua D. Drake wrote: > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > On Wed, 28 Nov 2007 12:26:15 -0800 > > David Fetter <[EMAIL PROTECTED]> wrote: > > > > > Folks, > > > > > > Best practices for partitioning so far have shown that TRIGGERs are > > > better than RULEs for most cases. Please find attached a patch which > > > reflects this. > > > > > > Thanks to Robert Treat for help putting this together :) > > > > > > Cheers, > > > David. > > > > +1 > > > > Joshua D. Drake > > Per Robert, I've also dropped the UNION partitioning suggestion as > it's pretty useless. Oops. Patch including *both* changes attached this time. 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 Index: doc/src/sgml/ddl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.77 diff -c -r1.77 ddl.sgml *** doc/src/sgml/ddl.sgml 28 Nov 2007 15:42:31 - 1.77 --- doc/src/sgml/ddl.sgml 28 Nov 2007 20:44:57 - *** *** 2510,2564 If data will be added only to the latest partition, we can ! set up a very simple rule to insert data. We must ! redefine this each month so that it always points to the ! current partition: ! ! ! CREATE OR REPLACE RULE measurement_current_partition AS ! ON INSERT TO measurement ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex set of rules as shown below: ! CREATE RULE measurement_insert_y2004m02 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ... ! CREATE RULE measurement_insert_y2005m12 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! CREATE RULE measurement_insert_y2006m01 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ! ! Note that the WHERE clause in each rule ! exactly matches the CHECK ! constraint for its partition. --- 2510,2589 If data will be added only to the latest partition, we can ! set up a very simple trigger function to insert data. We must ! redefine this each month so that it always points to the current ! partition: ! ! ! CREATE OR REPLACE FUNCTION measurement_current_partition() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! INSERT INTO measurement_y2006m01 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! RETURN NEW; ! END; ! $$; ! ! ! The first time we create the table, we create a trigger which ! calls the above trigger function. When we replace the trigger ! function, we don't need to replace the trigger. ! ! ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); We might want to insert data and have the server automatically locate the partition into which the row should be added. We !
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Wed, Nov 28, 2007 at 12:39:04PM -0800, Joshua D. Drake wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On Wed, 28 Nov 2007 12:26:15 -0800 > David Fetter <[EMAIL PROTECTED]> wrote: > > > Folks, > > > > Best practices for partitioning so far have shown that TRIGGERs are > > better than RULEs for most cases. Please find attached a patch which > > reflects this. > > > > Thanks to Robert Treat for help putting this together :) > > > > Cheers, > > David. > > +1 > > Joshua D. Drake Per Robert, I've also dropped the UNION partitioning suggestion as it's pretty useless. 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 Index: doc/src/sgml/ddl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.77 diff -c -r1.77 ddl.sgml *** doc/src/sgml/ddl.sgml 28 Nov 2007 15:42:31 - 1.77 --- doc/src/sgml/ddl.sgml 28 Nov 2007 20:39:48 - *** *** 2571,2594 script that generates the required DDL automatically. - - Partitioning can also be arranged using a UNION ALL - view: - - - CREATE VIEW measurement AS - SELECT * FROM measurement_y2004m02 - UNION ALL SELECT * FROM measurement_y2004m03 - ... - UNION ALL SELECT * FROM measurement_y2005m11 - UNION ALL SELECT * FROM measurement_y2005m12 - UNION ALL SELECT * FROM measurement_y2006m01; - - - However, the need to - recreate the view adds an extra step to adding and dropping - individual partitions of the data set. - --- 2571,2576 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] Partition: use triggers instead of rules
Folks, Best practices for partitioning so far have shown that TRIGGERs are better than RULEs for most cases. Please find attached a patch which reflects this. Thanks to Robert Treat for help putting this together :) 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 Index: doc/src/sgml/ddl.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v retrieving revision 1.77 diff -c -r1.77 ddl.sgml *** doc/src/sgml/ddl.sgml 28 Nov 2007 15:42:31 - 1.77 --- doc/src/sgml/ddl.sgml 28 Nov 2007 20:23:44 - *** *** 2510,2564 If data will be added only to the latest partition, we can ! set up a very simple rule to insert data. We must ! redefine this each month so that it always points to the ! current partition: ! ! ! CREATE OR REPLACE RULE measurement_current_partition AS ! ON INSERT TO measurement ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex set of rules as shown below: ! CREATE RULE measurement_insert_y2004m02 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2004m02 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ... ! CREATE RULE measurement_insert_y2005m12 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2005m12 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! CREATE RULE measurement_insert_y2006m01 AS ! ON INSERT TO measurement WHERE ! ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! ! ! Note that the WHERE clause in each rule ! exactly matches the CHECK ! constraint for its partition. --- 2510,2589 If data will be added only to the latest partition, we can ! set up a very simple trigger function to insert data. We must ! redefine this each month so that it always points to the current ! partition: ! ! ! CREATE OR REPLACE FUNCTION measurement_current_partition() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! INSERT INTO measurement_y2006m01 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! RETURN NEW; ! END; ! $$; ! ! ! The first time we create the table, we create a trigger which ! calls the above trigger function. When we replace the trigger ! function, we don't need to replace the trigger. ! ! ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); We might want to insert data and have the server automatically locate the partition into which the row should be added. We ! could do this with a more complex trigger function as shown ! below: ! CREATE OR REPLACE FUNCTION measurement_insert() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! IF ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' ) THEN ! ! INSERT INTO measurement_y2004m02 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! ELSIF ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' ) THEN ! ... ! ELSIF ( logdate >= DATE '2008-01-01' AND logdate < DATE '2006-02-01' ) THEN !
Re: [PATCHES] Hibernate Dialects for PostgreSQL
On Mon, Nov 12, 2007 at 01:41:49PM +, Simon Riggs wrote: > On Mon, 2007-11-12 at 14:28 +0100, Peter Eisentraut wrote: > > Simon Riggs wrote: > > > I enclose 3 Dialects for PostgreSQL, as discussed on -hackers. > > > > There is no such thing as PostgreSQL 8. Please don't do that; it would > > confuse users about the versioning scheme. > > Would 8x be the right thing then? > > So PostgreSQL8xDialect or PostgreSQL80Dialect or ... I'd say 80 so it's easy to separate off 81, 82, 83, etc. :) 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] V0.2 patch for TODO Item: SQL-language reference parameters by name.
On Sat, Nov 03, 2007 at 12:44:07PM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > I think a prefix of ':' would be good, as it's already a standard, > > kinda. Anybody who names a database object :foo deserves whatever > > happens to them :P > > The important word there is "kinda". We do not need a prefix and > I'll resist introducing one. What I mean by "kinda" is that it's a standard way of handling parameters in Oracle and in DBI. I think it would be a very bad idea to require that people use the function name in parameters, as such names can be quite long. People using names like :foo for database objects could just quote them :) Another possibility would be to introduce another parameter type in addition to IN, OUT and INOUT called PREFIX (required to be of type text) which would enable people to change from the default prefix. 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] V0.2 patch for TODO Item: SQL-language reference parameters by name.
On Sat, Nov 03, 2007 at 12:36:45PM +0100, Gevik Babakhani wrote: > Hello All, > > This patch implements a (generic) callback functionality in the parser. > The mechanism can be used to send callback messages from within the parser > to external functions. > > I would like to know your opinion about the following: > > In previous discussion Tom referred to: > > >One point here is that it would be good to be able to qualify the argument > > names with the function name, for example > > create function myfunc(x int) ... > > select ... from t where t.x = myfunc.x > > The above is possible but I think qualifying the argument names with the > function name > can be cumbersome when one has to provide the function name multiple times. > For example: (where clause) > > create or replace function sp_item_get_by_type_or_category(p_type > integer,p_category integer) > returns setof item_view as > $$ > select . from item_view i > inner join tblcategory c on i.catid = c.catid > inner join tbltype t on i.typeid = t.typeid > where > c.catid = sp_item_get_by_type_or_category.p_category or > t.typeid = sp_item_get_by_type_or_categor.p_type; > $$ > language sql; > > Perhaps we could use the word "this" instead of the entire function name > > For example: > > where > c.catid = this.p_category or > t.typeid = this.p_type; > > > > Any thoughts? I think a prefix of ':' would be good, as it's already a standard, kinda. Anybody who names a database object :foo deserves whatever happens to them :P 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [DOCS] rename of a view
On Sun, Jul 01, 2007 at 09:03:45PM -0700, Neil Conway wrote: > On Sun, 2007-01-07 at 12:55 -0700, David Fetter wrote: > > Here's a new patch + file. This one allows ALTER [SEQUENCE | VIEW] to > > work only on the respective database objects, but permits the old > > ALTER TABLE syntax. > > How about taking a look at the more thorough documentation updates Tom > suggested? So far I've documented it in the index, the references, and in psql's tab completion. What other places would you suggest I look? Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [DOCS] rename of a view
On Sat, Jun 30, 2007 at 01:48:51AM -0400, Tom Lane wrote:
> Neil Conway <[EMAIL PROTECTED]> writes:
> > For the ALTER SEQUENCE case, I think it's also a little weird to
> > have an ALTER SEQUENCE command that modifies some of the
> > properties of a sequence, but not the sequence's name. While that
> > argument doesn't apply to ALTER VIEW at the moment, recent history
> > suggests that it may only be a matter of time before we need to
> > add an ALTER VIEW command anyway...
>
> OK, that's a fairly convincing argument. Fire away.
>
> (I'm still not sure you found all the relevant places in the
> documentation, however.)
Here's a new patch + file. This one allows ALTER [SEQUENCE | VIEW] to
work only on the respective database objects, but permits the old
ALTER TABLE syntax.
Cheers,
David.
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
Index: doc/src/sgml/reference.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/reference.sgml,v
retrieving revision 1.63
diff -c -r1.63 reference.sgml
*** doc/src/sgml/reference.sgml 26 Apr 2007 16:13:08 - 1.63
--- doc/src/sgml/reference.sgml 1 Jul 2007 19:42:33 -
***
*** 53,58
--- 53,59
&alterTrigger;
&alterType;
&alterUser;
+&alterView;
&analyze;
&begin;
&checkpoint;
Index: doc/src/sgml/ref/allfiles.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v
retrieving revision 1.70
diff -c -r1.70 allfiles.sgml
*** doc/src/sgml/ref/allfiles.sgml 26 Apr 2007 16:13:08 - 1.70
--- doc/src/sgml/ref/allfiles.sgml 1 Jul 2007 19:42:33 -
***
*** 25,30
--- 25,31
+
Index: doc/src/sgml/ref/alter_sequence.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/alter_sequence.sgml,v
retrieving revision 1.16
diff -c -r1.16 alter_sequence.sgml
*** doc/src/sgml/ref/alter_sequence.sgml31 Jan 2007 23:26:02 -
1.16
--- doc/src/sgml/ref/alter_sequence.sgml1 Jul 2007 19:42:34 -
***
*** 29,34
--- 29,35
[ RESTART [ WITH ] start ] [
CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table.column | NONE } ]
ALTER SEQUENCE name SET SCHEMA
new_schema
+ ALTER SEQUENCE name RENAME TO
new_name
***
*** 190,195
--- 191,207
+
+
+ RENAME TO new_name
+
+
+ The RENAME TO option causes the sequence to be
+ renamed.
+
+
+
+
Index: src/backend/commands/alter.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/alter.c,v
retrieving revision 1.23
diff -c -r1.23 alter.c
*** src/backend/commands/alter.c26 Mar 2007 16:58:38 - 1.23
--- src/backend/commands/alter.c1 Jul 2007 19:42:34 -
***
*** 83,88
--- 83,90
break;
case OBJECT_TABLE:
+ case OBJECT_SEQUENCE:
+ case OBJECT_VIEW:
case OBJECT_INDEX:
case OBJECT_COLUMN:
case OBJECT_TRIGGER:
***
*** 96,101
--- 98,105
switch (stmt->renameType)
{
case OBJECT_TABLE:
+ case OBJECT_SEQUENCE:
+ case OBJECT_VIEW:
case OBJECT_INDEX:
{
/*
***
*** 113,119
aclcheck_error(aclresult, ACL_KIND_NAMESPACE,
get_namespace_name(namespaceId));
! renamerel(relid,
stmt->newname);
break;
}
case OBJECT_COLUMN:
--- 117,123
aclcheck_error(aclresult, ACL_KIND_NAMESPACE,
get_namespace_name(namespaceId));
!
Re: [PATCHES] [DOCS] OS/X startup scripts
On Wed, May 16, 2007 at 03:53:22PM +0100, Gregory Stark wrote: > "David Fetter" <[EMAIL PROTECTED]> writes: > > > On Wed, May 16, 2007 at 09:12:23AM +0100, Heikki Linnakangas wrote: > >> Jim C. Nasby wrote: > >> >BTW, is there some trick to getting cvs diff to ignore files > >> >that aren't in the repo? > >> > >> Trick? That's what it does by default. > > > > I suspect he's talking about all the lines starting with '?' that > > diff produces. > > > > Lacking sophistication, I've been known to do: > > > > cvs diff [list of files here] |grep -v '^?' > the_file.diff > > Those lines go to stderr. Not when I do "cvs diff." Is there something I should (un)set in my .cvsrc? > If you do "cvs diff > file" it spits out all the cvs file statuses > to the terminal but dumps the diff to the file. > > It doesn't matter, diffs can contain arbitrary junk between the file > diffs. patch only looks at the things it recognizes. IMHO, the diffs also need to be human-readable, and 500 useless lines starting with "?" don't help with that. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [DOCS] OS/X startup scripts
On Wed, May 16, 2007 at 09:12:23AM +0100, Heikki Linnakangas wrote: > Jim C. Nasby wrote: > >BTW, is there some trick to getting cvs diff to ignore files that > >aren't in the repo? > > Trick? That's what it does by default. I suspect he's talking about all the lines starting with '?' that diff produces. Lacking sophistication, I've been known to do: cvs diff [list of files here] |grep -v '^?' > the_file.diff Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [DOCS] OS/X startup scripts
On Mon, May 14, 2007 at 03:31:40PM +1200, Mark Kirkwood wrote: > David Fetter wrote: > >cvs diff works just great until you want to add or remove a file > >without write permissions to the CVS repository, i.e. when you've > >checked out as anonymous. > > > > I usually saved an untouched version of the tree to compare against, > so something like: > > $ cvs diff -Nacr pgsql.orig pgsql > > gives a complete patch including added/deleted files. It is a bit > primitive, but is pretty easy to do! It's great that we have all this knowledge of how to deal with CVS's deficiencies, but at the moment, it amounts to magical lore. Until we get something better to replace it, they should be part of the docs. :) Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [DOCS] Autovacuum and XID wraparound
On Sun, May 13, 2007 at 10:06:40PM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > Per Neil Conway, here's some doc patches re: the autovacuum > > daemon's behavior. Should this be back-patched to 8.2x? > > This fact is already documented in at least three places; do we > really need two more? Yes. > The proposed addition to postgresql.conf seems particularly > over-the-top, since there is no entry in that file that even > pretends to offer a complete description of the associated behavior. I think that a boolean that doesn't do what you expect booleans to do, i.e. turn the thing all the way off, is worth a mention. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [DOCS] OS/X startup scripts
On Sun, May 13, 2007 at 09:51:53PM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > On Sun, May 13, 2007 at 07:04:44PM -0400, Andrew Dunstan wrote: > >> Tom Lane wrote: > >>> Strange, it works fine for everyone else. > >> > >> Especially if you have cvsutils installed (can be found in many > >> places including fedora extras). > > > I didn't know about those, so I'll prepare a patch to the > > developer and documenter docs that mentions this utility :) > > I dunno what cvsutils is, but I do know that plain old "cvs diff" > works fine whether you have commit privs or not. Rather than > preparing a patch to our docs, perhaps you should spend some time > reading the CVS docs. cvs diff works just great until you want to add or remove a file without write permissions to the CVS repository, i.e. when you've checked out as anonymous. Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] On patching without write access to CVS
Folks, Thanks to Andrew Dunstan for pointing me toward cvsutils. As not everybody knows about them, here's a small patch which lets people know at least in theory where they are. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate Index: doc/FAQ_DEV === RCS file: /projects/cvsroot/pgsql/doc/FAQ_DEV,v retrieving revision 1.135 diff -c -r1.135 FAQ_DEV *** doc/FAQ_DEV 5 May 2007 14:33:55 - 1.135 --- doc/FAQ_DEV 14 May 2007 01:56:11 - *** *** 108,114 work. Failure to do so might mean your patch is rejected. If your work is being sponsored by a company, read this article for tips on being more effective. ! A web site is maintained for patches awaiting review, http://momjian.postgresql.org/cgi-bin/pgpatches, and those that are being kept for the next release, --- 108,120 work. Failure to do so might mean your patch is rejected. If your work is being sponsored by a company, read this article for tips on being more effective. ! !To create patches which would otherwise require that you have write !access to the CVS repository, for example ones that add or remove !files, you can use cvsutils. The cvsutils toolchain is packaged !for many operating systems and available in source form at !http://www.red-bean.com/cvsutils/ ! A web site is maintained for patches awaiting review, http://momjian.postgresql.org/cgi-bin/pgpatches, and those that are being kept for the next release, Index: doc/src/sgml/cvs.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/cvs.sgml,v retrieving revision 1.42 diff -c -r1.42 cvs.sgml *** doc/src/sgml/cvs.sgml 27 Mar 2007 01:45:22 - 1.42 --- doc/src/sgml/cvs.sgml 14 May 2007 01:56:11 - *** *** 15,20 --- 15,24 Thomas Lockhart + + David + Fetter + 1999-05-20 *** *** 150,155 --- 154,166 comes with CVS, or see the online documentation at http://www.nongnu.org/cvs/";>. + +For those things which CVS does not do +by itself, such as letting you create patches without write access, +you can use cvsutils, which is packaged +for many operating systems, or available in source form at http://www.red-bean.com/cvsutils/";>. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [DOCS] OS/X startup scripts
On Sun, May 13, 2007 at 07:04:44PM -0400, Andrew Dunstan wrote: > > > Tom Lane wrote: > >David Fetter <[EMAIL PROTECTED]> writes: > > > >>I haven't included the customary diffs. This points me to some of > >>the many deficiencies of CVS, namely that I would need write > >>access in order to have it create a diff, > > > >Strange, it works fine for everyone else. > > Especially if you have cvsutils installed (can be found in many > places including fedora extras). I didn't know about those, so I'll prepare a patch to the developer and documenter docs that mentions this utility :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] Autovacuum and XID wraparound
Folks, Per Neil Conway, here's some doc patches re: the autovacuum daemon's behavior. Should this be back-patched to 8.2x? Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate Index: doc/src/sgml/config.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.122 diff -c -r1.122 config.sgml *** doc/src/sgml/config.sgml20 Apr 2007 02:37:37 - 1.122 --- doc/src/sgml/config.sgml14 May 2007 01:16:02 - *** *** 3172,3177 --- 3172,3185 This parameter can only be set in the postgresql.conf file or on the server command line. + + + Even when this variable is set to off, the autovacuum daemon + will run periodically in order to prevent transaction_id + wraparound. See for + more information. + + Index: src/backend/utils/misc/postgresql.conf.sample === RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v retrieving revision 1.215 diff -c -r1.215 postgresql.conf.sample *** src/backend/utils/misc/postgresql.conf.sample 18 Apr 2007 16:44:18 - 1.215 --- src/backend/utils/misc/postgresql.conf.sample 14 May 2007 01:16:02 - *** *** 372,377 --- 372,379 #--- # AUTOVACUUM PARAMETERS #--- + # Note: even when autovacuum is turned off, the autovacuum daemon will + # run in order to prevent transaction id wraparound. #autovacuum = on # enable autovacuum subprocess? # 'on' requires stats_start_collector ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] OS/X startup scripts
Folks, The attached tarball should be unpacked in contrib/startup-scripts, and supplants PostgreSQL.darwin and StartupParameters.plist.darwin. Thanks to Mark Cotner of Reflectr <http://pgfoundry.org/projects/reflectr/> fame for this update :) I haven't included the customary diffs. This points me to some of the many deficiencies of CVS, namely that I would need write access in order to have it create a diff, and write access is boolean, which means that I can't get write access only to the parts of the tree that make sense for me to have write access to. What say we consider using Git, which includes a CVS interface, starting after 8.3 gets out the door? Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate PostgreSQL_startup.tgz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Concurrent psql patch
On Sun, May 13, 2007 at 02:39:45PM +0100, Gregory Stark wrote: > "Jim Nasby" <[EMAIL PROTECTED]> writes: > > > I don't see how we could make the names shorter without moving > > away from a backslash command (which I'm guessing would be > > painful). > > > > Assuming we're stuck with a backslash command \cs[witch] and \cn > > [owait] seem to be about as good as we could get. > > I don't have \cs or \cn set up as abbreviations. > > I was originally thinking \c1, \c2, ... for \cswitch and \c& for > \cnowait. I'm not sure if going for cryptic short commands is better > or worse here. +1 for \c1, \c2, etc. What's the reasoning behind \c&? Does it "send things into the background" the way & does in the shell? Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] Arrays of Complex Types
On Sun, May 06, 2007 at 01:33:47PM -0400, Andrew Dunstan wrote: > However, there are still some oddities. For example, a change to or > removal of the base type affects the array type, but the array type > can be directly operated on (e.g. alter type _aa set schema foo ). > I'm inclined to say we should prevent direct operations on array > types, and they should live or die by their parent types. > > Thoughts? +1 on binding the array types tightly to the parent types. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] non-recursive WITH clause support
On Mon, Apr 09, 2007 at 09:08:31PM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > On Mon, Apr 09, 2007 at 06:43:08PM -0400, Tom Lane wrote: > >> So this does not really add any new functionality, it's just variant > >> syntax for something you can do about as easily without it, right? > > > Not totally as easily. For example, you can do some kinds of > > aggregation with a few fewer keystrokes. > > Example please? It's not obvious to me what this does that wouldn't be > exactly isomorphic to creating a temporary view. It would be isomorphic, but happens automagically for the scope of the query instead of being three queries. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] non-recursive WITH clause support
On Mon, Apr 09, 2007 at 06:43:08PM -0400, Tom Lane wrote: > Gregory Stark <[EMAIL PROTECTED]> writes: > > Here's an updated patch that fixes the bug I had. This is now functional > > basic > > non-recursive WITH clause support. > > http://community.enterprisedb.com/recursive/with-pg82stable-v2.patch.gz > > It's a pretty short simple patch as is; it just directly inlines any WITH > > clauses as if they had been written as subqueries. > > So this does not really add any new functionality, it's just variant > syntax for something you can do about as easily without it, right? Not totally as easily. For example, you can do some kinds of aggregation with a few fewer keystrokes. > The downside that I see is is that it changes WITH and SET into fully > reserved words, which no doubt will break a few people's applications. Really? > While we're probably going to have to do that eventually, I'd like > to be able to point to some non-negligible benefit resulting from > the change when we do it. > > So my inclination is to not apply this in its current form, but to > wait for the full recursive-WITH feature before taking the > compatibility hit. Is there some way to poll people for uses of WITH and SET in places they shouldn't be? Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] Arrays of Complex Types
On Mon, Apr 09, 2007 at 10:40:49AM -0400, Tom Lane wrote:
> Andrew Dunstan <[EMAIL PROTECTED]> writes:
> > Regarding catalog objects, we might have to try a little harder than
> > just not generating in bootstrap mode - IIRC we generate system views
> > (including pg_stats) in non-bootstrap mode. Maybe we just need to exempt
> > anything in the pg_catalog namespace. What would happen if a user
> > created a view over pg_statistic?
>
> Nothing:
>
> regression=# create view vvv as select * from pg_statistic;
> ERROR: column "stavalues1" has pseudo-type anyarray
>
> which means we do have an issue for the pg_stats view. Now that I look
> instead of guessing, the existing test in CheckAttributeType is not on
> bootstrap mode but standalone mode:
>
> /* Special hack for pg_statistic: allow ANYARRAY during initdb */
> if (atttypid != ANYARRAYOID || IsUnderPostmaster)
> ereport(ERROR,
> (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
> errmsg("column \"%s\" has pseudo-type %s",
> attname, format_type_be(atttypid;
>
> so for consistency we should use the same condition to suppress types
> for system catalogs.
Groovy :)
> > Or maybe we should go to the heart
> > of the problem and simply check for pseudo-types directly.
>
> Actually we may have an issue already:
>
> regression=# create table zzz (f1 pg_statistic);
> CREATE TABLE
>
> I couldn't make it misbehave in a short amount of trying:
>
> regression=# insert into zzz
> values(row(0,0,0,0,0,0,0,0,0,0,0,0,0,array[1,2],null,null,null,array[12,13],null,null,null));
> ERROR: ROW() column has type integer[] instead of type anyarray
>
> but I don't feel comfortable about this at all. Maybe
> CheckAttributeType should be made to recurse into composite columns.
That'd be great :)
Cheers,
D
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PATCHES] [HACKERS] Arrays of Complex Types
On Sun, Apr 08, 2007 at 07:08:38PM -0400, Tom Lane wrote: > Andrew Dunstan <[EMAIL PROTECTED]> writes: > > One of the things that's been bothering me about this proposal is that > > it leaves untouched and indeed greatly expands the scope of the typename > > mangling we do. (i.e. adding an entry to pg_type with _ prepended). > > Yeah, that's been bothering me too. One of the problems with the patch > as-is is that it extends the 62-instead-of-63-char limit to table names > as well as type names. I did this by copying some code which already creates array names, so should that code change to do something different, the 62-instead-of-63-char thing would go away along with it. I agree that the prepended _s are far from optimal. > I've been thinking of proposing that we add a column to pg_type that > points from a type to its array type (if any), ie the reverse link > from typelem. If we had that then the parser could follow that to > determine which type is foo[], instead of relying on the _foo naming > convention. I don't suggest that we stop using the naming convention, > but it would no longer be a hard-and-fast rule, just a convention. That'd be neat :) > In particular we could rejigger things around the edges to reduce > the name conflict problem. For instance the rule for forming array type > names could be "prepend _, truncate to less than 64 bytes if necessary, > then substitute numbers at the end if needed to get something unique". > This is not all that different from what we do now to get unique > serial sequence names, for example. > > This would also open the door to supporting > > CREATE TYPE foo AS ARRAY OF bar I'm sorry to keep harping on this, but I really don't see a use case and do see foot guns both with making the array types optional and with decoupling their names from those of their respective compound types. When they're optional, we get all kinds of "stepping on a step that isn't there" issues, and when they're decoupled, operations like, "ALTER TABLE foo RENAME TO bar" have either surprising or undefined behavior, or both. > without having to have any restrictions about the name of foo. > I'd still much rather do things that way for arrays of composites > than invent a ton of pg_type entries that are mostly going to go > unused. I'm sure there's a better way than my first attempt. > PS: Has anyone looked at what it will take to make the entries in an > array-of-composite be something smaller than full tuples? It's not > going to be anything but a toy unless you can get the per-entry > overhead down to something sane. Perhaps the MinimalTuple > representation would work. Sounds neat, too :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] Fix misleading references to columns in GRANT/REVOKE summaries
Folks,
Per a question Alexey Parshin asked in the IRC channel, I'm attaching
a patch to the GRANT and REVOKE syntax summaries which replaces the
misleading word "column" with "parameter." "Column" is misleading
because it could be read to imply a column-level GRANT/REVOKE, which
we don't have yet.
Cheers,
D
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
Index: doc/src/sgml/ref/grant.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v
retrieving revision 1.64
diff -c -r1.64 grant.sgml
*** doc/src/sgml/ref/grant.sgml 1 Feb 2007 00:28:19 - 1.64
--- doc/src/sgml/ref/grant.sgml 6 Apr 2007 23:39:20 -
***
*** 525,531
GRANT privileges
! ON table [ ( column [, ...] ) ] [, ...]
TO { PUBLIC | username [,
...] } [ WITH GRANT OPTION ]
--- 525,532
GRANT privileges
! ON table [ (
! parameter [, ...] ) ] [, ...]
TO { PUBLIC | username [,
...] } [ WITH GRANT OPTION ]
Index: doc/src/sgml/ref/revoke.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/revoke.sgml,v
retrieving revision 1.42
diff -c -r1.42 revoke.sgml
*** doc/src/sgml/ref/revoke.sgml31 Jan 2007 23:26:04 - 1.42
--- doc/src/sgml/ref/revoke.sgml6 Apr 2007 23:39:20 -
***
*** 235,241
REVOKE [ GRANT OPTION FOR ] privileges
! ON object [ ( column [, ...] ) ]
FROM { PUBLIC | username [,
...] }
{ RESTRICT | CASCADE }
--- 235,242
REVOKE [ GRANT OPTION FOR ] privileges
! ON object [ (
! parameter [, ...] ) ]
FROM { PUBLIC | username [,
...] }
{ RESTRICT | CASCADE }
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [PATCHES] [HACKERS] Arrays of Complex Types
On Tue, Apr 03, 2007 at 02:30:07AM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > On Mon, Apr 02, 2007 at 10:01:44PM -0400, Alvaro Herrera wrote: > >> So, hum, what happened to the idea of creating the array types > >> only on demand? > > > Scotched, as far as I could tell, > > More like "you submitted a patch that entirely ignores multiple > people's opinion on what is needed". > > Bruce may have put this into the patch queue, but do not labor under > the delusion that that means it'll get applied as-is. I assure you I'm not. Two glaring things it's missing are regression tests and documentation. I should have those in this week. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Arrays of Complex Types
On Mon, Apr 02, 2007 at 10:01:44PM -0400, Alvaro Herrera wrote: > Bruce Momjian wrote: > > > > Your patch has been added to the PostgreSQL unapplied patches list > > at: > > > > http://momjian.postgresql.org/cgi-bin/pgpatches > > > > It will be applied as soon as one of the PostgreSQL committers > > reviews and approves it. > > > > So, hum, what happened to the idea of creating the array types only > on demand? Scotched, as far as I could tell, partly due to nobody's having actually done work toward such a thing, and partly because the closest thing I've heard to an objection is pretty nebulous. :) It's a lot simpler to have them always, and it fits in with the larger picture of making arrays fully composable with other operations like DOMAIN, ENUM and TYPE. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] [HACKERS] Arrays of Complex Types
On Fri, Mar 30, 2007 at 05:08:42PM -0400, Tom Lane wrote:
> David Fetter <[EMAIL PROTECTED]> writes:
> > After several rounds of patches, it appears that it might be easier to
> > create a new typtype entry, which I'll tentatively call 'a' because it
> > seems a little fragile and a lot inelegant and hard to maintain to
> > have typtype='c' and typrelid=InvalidOid mean, "this is an array of
> > complex types."
>
> Uh, wouldn't it be typtype = 'c' and typelem != 0 ?
Right. The attached patch passes the current regression tests and at
least to a "smoke test" level does what it's supposed to do. I'd
really like to help refactor the whole array system to use 'a', tho.
Cheers,
D
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
Index: src/backend/catalog/heap.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/heap.c,v
retrieving revision 1.318
diff -c -r1.318 heap.c
*** src/backend/catalog/heap.c 2 Apr 2007 03:49:37 - 1.318
--- src/backend/catalog/heap.c 2 Apr 2007 20:09:16 -
***
*** 45,50
--- 45,51
#include "catalog/pg_statistic.h"
#include "catalog/pg_type.h"
#include "commands/tablecmds.h"
+ #include "commands/typecmds.h"
#include "miscadmin.h"
#include "optimizer/clauses.h"
#include "optimizer/var.h"
***
*** 763,768
--- 764,770
Relationpg_class_desc;
Relationnew_rel_desc;
Oid new_type_oid;
+ char *relarrayname;
pg_class_desc = heap_open(RelationRelationId, RowExclusiveLock);
***
*** 815,820
--- 817,856
relnamespace,
relid,
relkind);
+ /*
+* Add in the corresponding array types if appropriate.
+*/
+ if (relkind == RELKIND_RELATION ||
+ relkind == RELKIND_VIEW ||
+ relkind == RELKIND_COMPOSITE_TYPE)
+ {
+ relarrayname = makeArrayTypeName(relname);
+ TypeCreate(relarrayname,/* Array type name */
+ relnamespace,/* Same
namespace as parent */
+ InvalidOid, /* relation's
type oid, set here to InvalidOid to make dependency work right */
+ 0, /*
relkind, also N/A here */
+ -1, /*
Internal size, unlimited */
+ 'c', /* It's
a complex type */
+ DEFAULT_TYPDELIM,/* Use the default */
+ F_ARRAY_IN, /* Macro for
array input procedure */
+ F_ARRAY_OUT, /* Macro for
array output procedure */
+ F_ARRAY_RECV,/* Macro for
array receive (binary input) procedure */
+ F_ARRAY_SEND,/* Macro for
array send (binary output) procedure */
+ InvalidOid, /* No input
typmod */
+ InvalidOid, /* No output
typmod */
+ InvalidOid, /* Default
ANALYZE procedure */
+ new_type_oid,/* The OID just
created */
+ InvalidOid, /* No base
type--this isn't a DOMAIN */
+ NULL,/* No
default type value */
+ NULL,/*
Don't send binary */
+ false, /*
Never passed by value */
+ 'd', /* Type
alignment. Should this be something else? */
+ 'x', /*
Always TOASTable */
+ -1, /* No
typMod for regular composite types. */
+
Re: [PATCHES] Fwd: Re: [pgsql-patches] pg_get_domaindef
On Fri, Mar 30, 2007 at 01:45:21PM -0400, Bruce Momjian wrote: > Thanks, but after lots of discussion, it turns out the community > doesn't want to add such functions, and I have removed it from the > TODO list. >From what I recall of the discussion, the lack of interest was in actually stepping up and doing it, not in the feature itself. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] POSIX Shared memory, revised, again
On Tue, Feb 27, 2007 at 11:17:44AM -0800, Chris Marcellino wrote: > So I've finished reformulating this patch to use the device/inode > instead of the hash idea I had earlier. > > I have tested this patch (on Darwin) to permit the postmaster to > recover after a crash or killing, and it will not permit the > postmaster to restart while another postmaster or backends are still > in the database (regardless of the lockfile status). The ideas are > the same as before, but are described via flowchart in the attached > PDF. The PDF didn't come through. Could you please re-send? Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] patch adding new regexp functions
On Sat, Feb 17, 2007 at 09:02:24AM +0100, Peter Eisentraut wrote: > Jeremy Drake wrote: > > In case you haven't noticed, I am rather averse to making this return > > text[] because it is much easier in my experience to use the results > > when returned in SETOF rather than text[], > > The primary use case I know for string splitting is parsing > comma/pipe/whatever separated fields into a row structure, and the way > I see it your API proposal makes that exceptionally difficult. > > I don't know what your use case is, though. All of this is missing > actual use cases. > > > While, if you > > really really wanted a text[], you could use the (fully documented) > > ARRAY(select resultstr from regexp_split(...) order by startpos) > > construct. > > I think, however, that we should be providing simple primitives that can > be combined into complex expressions rather than complex primitives > that have to be dissected apart to get simple results. > > > > As for the regexp_matches() function, it seems to me that it > > > returns too much information at once. What is the use case for > > > getting all of prematch, fullmatch, matches, and postmatch in one > > > call? > > > > It was requested by David Fetter: > > http://archives.postgresql.org/pgsql-hackers/2007-02/msg00056.php > > > > It was not horribly difficult to provide, and it seemed reasonable > > to me. I have no need for them personally. > > David Fetter has also repeated failed to offer a use case for this, > so I hesitate to accept this. What is it about having the whole match, pre-match and post-match available that you're objecting to? Are you saying there aren't common uses for any or all of these? Regular expression users use them all over the place, and adding this capability to SQL seems like a reasonable next step :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] patch adding new regexp functions
On Fri, Feb 16, 2007 at 01:03:32PM -0500, Andrew Dunstan wrote: > David Fetter wrote: > >> > >>The question is, what is the use case? If there is one in Perl, can > >>this proposed function API support it? > >> > > > >Perl makes the following variables available in any regex match, > >although it optimizes some cases for when they're not there: > > > >$1, ... $n (captured matches in parentheses) > >$` (pre-match) > >$' (post-match) > >$& (whole match) > > > > Use of any of these is notoriously costly, BTW, especially pre-match > and post-match. See perlre man page. This is why they only appear when called for :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] patch adding new regexp functions
On Fri, Feb 16, 2007 at 05:54:47PM +0100, Peter Eisentraut wrote: > Am Freitag, 16. Februar 2007 17:11 schrieb David Fetter: > > > As for the regexp_matches() function, it seems to me that it > > > returns too much information at once. What is the use case for > > > getting all of prematch, fullmatch, matches, and postmatch in > > > one call? > > > > If not in one call, how would you get it? Perl, for example, > > makes these available to any regex match in the form of variables > > it sets. > > The question is, what is the use case? If there is one in Perl, can > this proposed function API support it? Perl makes the following variables available in any regex match, although it optimizes some cases for when they're not there: $1, ... $n (captured matches in parentheses) $` (pre-match) $' (post-match) $& (whole match) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] patch adding new regexp functions
On Fri, Feb 16, 2007 at 01:19:55PM +0100, Peter Eisentraut wrote: > Am Freitag, 16. Februar 2007 08:02 schrieb Jeremy Drake: > > On Thu, 15 Feb 2007, Peter Eisentraut wrote: > > > I have no strong opinion about how matches are returned. Seeing > > > the definitional difficulties that you point out, it may be fine > > > to return them unordered. But then all "matches" functions > > > should do that. > > > > > > For the "split" functions, however, providing the order is > > > clearly important. > > > > Does this version sufficiently address your concerns? > > I don't think anyone asked for the start position and length in the > result of regexp_split(). The result should be an array of text. > That is what Perl et al. do. > > As for the regexp_matches() function, it seems to me that it returns > too much information at once. What is the use case for getting all > of prematch, fullmatch, matches, and postmatch in one call? If not in one call, how would you get it? Perl, for example, makes these available to any regex match in the form of variables it sets. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PATCHES] patch adding new regexp functions
On Thu, Feb 15, 2007 at 07:35:46PM -0500, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > I've obviously misunderstood the scope of the TODO because it appears > > that an INSERT into pg_type at creation time for compound types that > > looks something like the below would do it. What have I missed? > > There are a couple of issues. One is that we probably don't want > two pg_type entries for every single table. Now that you mention it, I would want that if that's what it takes to get arrays for them. The long-term goal here is to make all of PostgreSQL's types play nicely together. I'm guessing that SETOF will eventually be a way to describe a collection because MULTISET is in SQL:2003. > Will you be satisfied if only CREATE TYPE AS ... makes an array > type? The other is that, at least at the time they were written, > the array support routines couldn't handle composite array values. > Things might or might not be easier today; I don't think we had > record_in and record_out in their current form then. OK. What about pg_depend? Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] patch adding new regexp functions
On Thu, Feb 15, 2007 at 10:57:45AM +0100, Peter Eisentraut wrote: > Jeremy Drake wrote: > > # With a set-returning function, it is possible to add a LIMIT > > clause, to prevent splitting up more of the string than is > > necessary. > > You can also add such functionality to a function in form of a > parameter. That's what things like Perl's split do :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] patch adding new regexp functions
On Thu, Feb 15, 2007 at 10:37:26AM -0500, Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > so that you would have the position for each match, automatically. Is > > this information available in the regex code? > > Certainly, that's where we got the text snippets from to begin with. > However, I'm not sure that this is important enough to justify a > special type --- for one thing, since we don't have arrays of > composites, This is a TODO :) I've obviously misunderstood the scope of the TODO because it appears that an INSERT into pg_type at creation time for compound types that looks something like the below would do it. What have I missed? INSERT INTO pg_type VALUES ( '_foo', /* Generated by makeArrayTypeName */ 16744, /* OID of schema */ 10, /* OID of owner of the base type */ -1, /* typlen indicates varlena */ 'f',/* not passed by value */ 'c',/* typtype is composite */ 't',/* type is already defined */ ',',/* typdelim */ 0, /* should this actually refer to the type? */ 'foo'::regtype, /* typelem */ 'array_in', /* typinput */ 'array_out',/* typoutput */ 'array_recv', /* typreceive */ 'array_send', /* typsend */ 0, /* typanalyze */ 'i',/* typalign. Should this be 'd'? */ 'x',/* typstorage */ 'f',/* not a DOMAIN, but while we're at it, why not arrays of DOMAIN? */ 0, /* base type. should this be different? */ -1, /* no typmod */ 0 /* dims not specified */ ); > that would foreclose responding to Peter's concern that SETOF is the > wrong thing. If you look at the Perl and Tcl APIs for regexes, they > return just the strings, not the numerical positions; and I've not > heard anyone complaining about that. They do return them in the order in which they appear, though, which, as far as I can tell, Jeremy's functions also do. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] writing new regexp functions
On Fri, Feb 02, 2007 at 07:01:33PM -0800, Jeremy Drake wrote:
> Let me know if you see any bugs or issues with this code, and I am
> open to suggestions for further regression tests ;)
> Things that I still want to look into:
> * regexp flags (a la regexp_replace).
One more text field at the end is how the regexp_replace() one does
it.
> * maybe make regexp_matches return setof whatever, if given a 'g' flag
> return all matches in string.
This is doable with current machinery, albeit a little clumsily.
> * maybe a join function that works as an aggregate
>SELECT join(',', col) FROM tbl
> currently can be written as
>SELECT array_to_string(ARRAY(SELECT col FROM tbl), ',')
The array_accum() aggregate in the docs works OK for this purpose.
Cheers,
D
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
Skype: davidfetter
Remember to vote!
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [pgsql-patches] [ADMIN] server process (PID xxx) was
On Sun, Jan 28, 2007 at 05:02:24PM -0500, Bruce Momjian wrote: > Peter Eisentraut wrote: > > Tom Lane wrote: > > > I don't like that because it parenthesizes the most important part of > > > the message, which is a style-guideline violation at least in spirit. > > > How about > > > > > > ... terminated by signal 10: Bus error > > > > I like that. > > OK, but you guys added back that colon you didn't like. :-) Placement is crucial. Compare terminated by signal 10: Bus error <-- proposed version terminated by signal: 10 Bus error <-- older version Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Micro doc patch (plpgsql)
On Tue, Dec 26, 2006 at 01:10:03PM +0100, Joachim Wieland wrote: > On December 26, 5:19 am David Fetter <[EMAIL PROTECTED]> wrote: > > It doesn't need the semicolon. PL/PgSQL's BEGIN is different from > > SQL's. :) > > Exactly. Note that my patch _removes_ the semicolon. If you put it in a > plpgsql function definition as in the example from the docs you get a > syntax error. Oops. Sorry about that :) Cheers, D > > > > > -BEGIN; > > > +BEGIN > > > Joachim -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Micro doc patch (plpgsql)
On Tue, Dec 26, 2006 at 01:36:07AM +0100, Joachim Wieland wrote: > Hi, > > attached is a micro doc patch. BEGIN in plpgsql is without semicolon. It doesn't need the semicolon. PL/PgSQL's BEGIN is different from SQL's. :) Cheers, D > > > Joachim > diff -ur cvs/pgsql/doc/src/sgml/plpgsql.sgml > cvs.build/pgsql/doc/src/sgml/plpgsql.sgml > --- cvs/pgsql/doc/src/sgml/plpgsql.sgml 2006-11-01 05:09:40.0 > +0100 > +++ cvs.build/pgsql/doc/src/sgml/plpgsql.sgml 2006-12-25 11:17:04.0 > +0100 > @@ -1243,7 +1243,7 @@ > to catch the error, for example: > > > -BEGIN; > +BEGIN > SELECT * INTO STRICT myrec FROM emp WHERE empname = myname; > EXCEPTION > WHEN NO_DATA_FOUND THEN > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 6: explain analyze is your friend
