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 RecursivescanNext) 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 RecursionNext) 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 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, nk, 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 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, nk, 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 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_id, v.*
FROM
region r
CROSS JOIN
(
VALUES
('Minas Gerais','Estado'),
('Espírito Santo','Estado'),
('Rio de Janeiro','Estado'),
('São Paulo','Estado')
) AS v
WHERE (r.region_name, r.region_type) = ('Sudeste','Região');
INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
region r
CROSS JOIN
(
VALUES
('Paraná','Estado'),
('Santa Catarina','Estado'),
('Rio Grande do Sul','Estado')
) AS v
WHERE (r.region_name, r.region_type) = ('Sul','Região');
INSERT INTO region (parent_region_id, region_name, region_type)
SELECT r.region_id, v.*
FROM
region r
CROSS JOIN
(
VALUES
('Goiânia','Cidade'),
('Aparecida de Goiânia','Cidade'),
('Anápolis','Cidade'),
('Luziânia','Cidade'),
('Águas Lindas de Goiás','Cidade'),
('Rio Verde','Cidade
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 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 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 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
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
[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] 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] 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] 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: [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: [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 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 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] 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] 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 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 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] 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 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] [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] [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 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
variablelist
varlistentry
! termliteral\a/literal/term
listitem
para
! 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 command\pset/command for a
! more general solution.
/para
/listitem
/varlistentry
--- 673,687
variablelist
varlistentry
!termliteral\a /literal [ replaceable
! class=parameterON/replaceable |
! replaceable class=parameterOFF/replaceable ] /term
listitem
para
! Without parameter, toggle format between aligned and
! unaligned. With parameter, set it. This command is kept for
! backwards compatibility. See command\pset/command for a more
! general solution.
/para
/listitem
/varlistentry
***
*** 1292,1305
varlistentry
! termliteral\H/literal/term
listitem
para
! Turns on acronymHTML/acronym query output format. If the
! acronymHTML/acronym format is already on, it is switched
! back to the default aligned text format. This command is for
! compatibility and convenience, but see command\pset/command
! about setting other output options.
/para
/listitem
/varlistentry
--- 1294,1308
varlistentry
!termliteral\H /literal [ replaceable
! class=parameterON/replaceable |
! replaceable class=parameterOFF/replaceable ] /term
listitem
para
! Without parameter, toggles between acronymHTML/acronym and
! aligned query output format. With paramter, sets it.
! This command is for compatibility and convenience, but see
! command\pset/command about setting other output options.
/para
/listitem
/varlistentry
***
*** 1867,1876
varlistentry
!termliteral\timing/literal/term
listitem
para
! Toggles a display of how long each SQL statement takes, in
milliseconds.
/para
/listitem
/varlistentry
--- 1870,1882
varlistentry
!termliteral\timing /literal [replaceable
! class=parameterON/replaceable | replaceable
! class=parameterOFF/replaceable] /term
listitem
para
! Without parameter, toggles a display of how long each SQL
! statement takes, in milliseconds. With parameter, sets same.
/para
/listitem
/varlistentry
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
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 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
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
[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 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] 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] [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 listitem para 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: ! ! programlisting ! 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 ); /programlisting 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: programlisting ! CREATE RULE measurement_insert_y2004m02 AS ! ON INSERT TO measurement WHERE ! ( logdate gt;= DATE '2004-02-01' AND logdate lt; 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 gt;= DATE '2005-12-01' AND logdate lt; 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 gt;= DATE '2006-01-01' AND logdate lt; DATE '2006-02-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! /programlisting ! ! Note that the literalWHERE/literal clause in each rule ! exactly matches the literalCHECK/literal ! constraint for its partition. /para /listitem /orderedlist --- 2510,2589 listitem para 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: ! ! programlisting ! 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; ! $$; ! /programlisting ! ! 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. ! ! programlisting ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); /programlisting 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: programlisting ! CREATE OR REPLACE FUNCTION measurement_insert() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! IF ( logdate gt;= DATE '2004-02-01' AND logdate lt; DATE '2004-03-01' ) THEN ! ! INSERT INTO measurement_y2004m02 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp
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 listitem para 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: ! ! programlisting ! 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 ); /programlisting 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: programlisting ! CREATE RULE measurement_insert_y2004m02 AS ! ON INSERT TO measurement WHERE ! ( logdate gt;= DATE '2004-02-01' AND logdate lt; 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 gt;= DATE '2005-12-01' AND logdate lt; 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 gt;= DATE '2006-01-01' AND logdate lt; DATE '2006-02-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! /programlisting ! ! Note that the literalWHERE/literal clause in each rule ! exactly matches the literalCHECK/literal ! constraint for its partition. /para /listitem /orderedlist --- 2510,2589 listitem para 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: ! ! programlisting ! 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; ! $$; ! /programlisting ! ! 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. ! ! programlisting ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); /programlisting 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
[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 listitem para 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: ! ! programlisting ! 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 ); /programlisting 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: programlisting ! CREATE RULE measurement_insert_y2004m02 AS ! ON INSERT TO measurement WHERE ! ( logdate gt;= DATE '2004-02-01' AND logdate lt; 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 gt;= DATE '2005-12-01' AND logdate lt; 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 gt;= DATE '2006-01-01' AND logdate lt; DATE '2006-02-01' ) ! DO INSTEAD ! INSERT INTO measurement_y2006m01 VALUES ( NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ); ! /programlisting ! ! Note that the literalWHERE/literal clause in each rule ! exactly matches the literalCHECK/literal ! constraint for its partition. /para /listitem /orderedlist --- 2510,2589 listitem para 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: ! ! programlisting ! 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; ! $$; ! /programlisting ! ! 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. ! ! programlisting ! CREATE TRIGGER insert_measurement_current_partition ! BEFORE INSERT ! ON measurement ! EXECUTE PROCEDURE measurement_current_partition(); /programlisting 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: programlisting ! CREATE OR REPLACE FUNCTION measurement_insert() ! RETURNS TRIGGER ! LANGUAGE plpgsql ! AS $$ ! BEGIN ! IF ( logdate gt;= DATE '2004-02-01' AND logdate lt; DATE '2004-03-01' ) THEN ! ! INSERT INTO measurement_y2004m02 ! VALUES ( ! NEW.city_id, ! NEW.logdate, ! NEW.peaktemp, ! NEW.unitsales ! ); ! ELSIF ( logdate gt;= DATE '2005-12-01' AND logdate lt; DATE '2006-01-01' ) THEN ! ... ! ELSIF ( logdate gt;= DATE '2008
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: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. /para -para - Partitioning can also be arranged using a literalUNION ALL/literal - view: - - programlisting - 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; - /programlisting - - However, the need to - recreate the view adds an extra step to adding and dropping - individual partitions of the data set. -/para /sect2 sect2 id=ddl-partitioning-managing-partitions --- 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
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 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 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 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] 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: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] 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] [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
!entity alterTrigger system alter_trigger.sgml
!entity alterType system alter_type.sgml
!entity alterUser system alter_user.sgml
+ !entity alterView system alter_view.sgml
!entity analyzesystem analyze.sgml
!entity begin system begin.sgml
!entity checkpoint system checkpoint.sgml
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 ] replaceable class=parameterstart/replaceable ] [
CACHE replaceable class=parametercache/replaceable ] [ [ NO ] CYCLE ]
[ OWNED BY { replaceable
class=parametertable/replaceable.replaceable
class=parametercolumn/replaceable | NONE } ]
ALTER SEQUENCE replaceable class=parametername/replaceable SET SCHEMA
replaceable class=parameternew_schema/replaceable
+ ALTER SEQUENCE replaceable class=parametername/replaceable RENAME TO
replaceable class=parameternew_name/replaceable
/synopsis
/refsynopsisdiv
***
*** 190,195
--- 191,207
/para
/listitem
/varlistentry
+
+varlistentry
+ termliteralRENAME TO/literal replaceable
class=parameternew_name/replaceable/term
+ listitem
+ para
+ The literalRENAME TO/literal option causes the sequence to be
+ renamed.
+ /para
+ /listitem
+/varlistentry
+
/variablelist
/para
/refsect1
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
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 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] 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
[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
[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 filenamepostgresql.conf/ file or on the server command line. /para +note + para + Even when this variable is set to off, the autovacuum daemon + will run periodically in order to prevent transaction_id + wraparound. See xref linkend=vacuum-for-wraparound for + more information. + /para +/note /listitem /varlistentry 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
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] 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 firstnameThomas/firstname surnameLockhart/surname /author +author + firstnameDavid/firstname + surnameFetter/surname +/author /authorgroup date1999-05-20/date /appendixinfo *** *** 150,155 --- 154,166 comes with productnameCVS/productname, or see the online documentation at ulink url=http://www.nongnu.org/cvs/;/ulink. /para + para +For those things which productnameCVS/productname does not do +by itself, such as letting you create patches without write access, +you can use productnamecvsutils/productname, which is packaged +for many operating systems, or available in source form at ulink +url=http://www.red-bean.com/cvsutils/;/ulink. + /para /sect1 sect1 id=cvs-tree ---(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 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
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] [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] [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] 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] 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] [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
synopsis
GRANT replaceable class=PARAMETERprivileges/replaceable
! ON replaceable class=PARAMETERtable/replaceable [ ( replaceable
class=PARAMETERcolumn/replaceable [, ...] ) ] [, ...]
TO { PUBLIC | replaceable class=PARAMETERusername/replaceable [,
...] } [ WITH GRANT OPTION ]
/synopsis
/para
--- 525,532
synopsis
GRANT replaceable class=PARAMETERprivileges/replaceable
! ON replaceable class=PARAMETERtable/replaceable [ (
! replaceable class=PARAMETERparameter/replaceable [, ...] ) ] [, ...]
TO { PUBLIC | replaceable class=PARAMETERusername/replaceable [,
...] } [ WITH GRANT OPTION ]
/synopsis
/para
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
synopsis
REVOKE [ GRANT OPTION FOR ] replaceable
class=PARAMETERprivileges/replaceable
! ON replaceable class=parameterobject/replaceable [ ( replaceable
class=parametercolumn/replaceable [, ...] ) ]
FROM { PUBLIC | replaceable class=parameterusername/replaceable [,
...] }
{ RESTRICT | CASCADE }
/synopsis
--- 235,242
synopsis
REVOKE [ GRANT OPTION FOR ] replaceable
class=PARAMETERprivileges/replaceable
! ON replaceable class=parameterobject/replaceable [ (
! replaceable class=parameterparameter/replaceable [, ...] ) ]
FROM { PUBLIC | replaceable class=parameterusername/replaceable [,
...] }
{ RESTRICT | CASCADE }
/synopsis
---(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 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 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 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. */
+ 0, /*
Array diminsions of typbasetype */
+ false); /* Type
NOT NULL */
+ pfree(relarrayname);/* Seems like the right thing to do
here. */
+ }
/*
* now create an entry
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 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 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] 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] [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: programlisting -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
Re: [PATCHES] Enums patch v2
On Tue, Dec 19, 2006 at 08:09:47AM +, Heikki Linnakangas wrote: Tom Dunstan wrote: Here is an updated version of the enums patch. It has been brought up to date and applies against current CVS HEAD. The original email is at [1], and describes the implementation. I'm sorry I missed the original discussions, but I have to ask: Why do we want enums in core? The only potential advantage I can see over using a look-up table and FK references is performance. A natural ordering is another. I'd love to be able to make a type color that has Red Orange Yellow Green Blue Indigo Violet and then be able to do an ORDER BY color; And I'd rather spend time improving the performance of FK checks than add extra machinery to do the same thing in a different way. Not the same thing. Ignoring my general dislike of enums, I have a few issues with the patch as it is: 1. What's the point of having comparison operators for enums? For most use cases, there's no natural ordering of enum values. A natural ordering is precisely the use case for enums. Otherwise, you just use a FK to a one-column table and have done. 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] [HACKERS] pg_dump -t broken for mixed case table names in beta3?
On Tue, Nov 28, 2006 at 02:43:17PM -0500, Tom Lane wrote: Kevin Grittner [EMAIL PROTECTED] writes: Unless I'm missing something, pg_dump is not allowing selective dump of a table where the table name is mixed case. You do it like this: $ pg_dump -s -t 'DbTranLogRecord' dtr A bit ugly but the conflict between shell and SQL quoting rules sometimes forces us into compromises :-( Perhaps it's worth having an example for this in the pg_dump man page? Please find enclosed a patch which adds this :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Index: doc/src/sgml/ref/pg_dump.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/pg_dump.sgml,v retrieving revision 1.91 diff -c -r1.91 pg_dump.sgml *** doc/src/sgml/ref/pg_dump.sgml 23 Oct 2006 18:10:32 - 1.91 --- doc/src/sgml/ref/pg_dump.sgml 28 Nov 2006 22:50:15 - *** *** 805,810 --- 805,818 /para para +To dump a single table named literalcamelCaseTable/: + + screen + prompt$/prompt userinputpg_dump -t 'camelCaseTable' mydb gt; db.sql/userinput + /screen + /para + + para To dump all tables whose names start with literalemp/ in the literaldetroit/ schema, except for the table named literalemployee_log/literal: ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] Numeric overflow problem + patch
Folks, Dennis Björklund and I discovered a little problem with how CVS TIP reports overflows on cast. Please find enclosed a patch which fixes it. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Index: src/backend/utils/adt/numeric.c === RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/numeric.c,v retrieving revision 1.94 diff -c -r1.94 numeric.c *** src/backend/utils/adt/numeric.c 14 Jul 2006 05:28:28 - 1.94 --- src/backend/utils/adt/numeric.c 28 Sep 2006 18:47:31 - *** *** 3217,3227 ereport(ERROR, (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), errmsg(numeric field overflow), !errdetail(A field with precision %d, scale %d must have an absolute value less than %s%d., precision, scale, /* Display 10^0 as 1 */ maxdigits ? 10^ : , ! maxdigits ? maxdigits : 1))); break; } ddigits -= DEC_DIGITS; --- 3217,3230 ereport(ERROR, (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE), errmsg(numeric field overflow), !errdetail(A field with precision %d, scale %d must have an absolute value less than %s%d - 5 * %s%d., precision, scale, /* Display 10^0 as 1 */ maxdigits ? 10^ : , ! maxdigits ? maxdigits : 1, ! 10^-, ! scale + 1 ! ))); break; } ddigits -= DEC_DIGITS; Index: src/test/regress/expected/numeric.out === RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/numeric.out,v retrieving revision 1.18 diff -c -r1.18 numeric.out *** src/test/regress/expected/numeric.out 25 Jan 2006 18:20:22 - 1.18 --- src/test/regress/expected/numeric.out 28 Sep 2006 18:47:31 - *** *** 688,699 INSERT INTO fract_only VALUES (2, '0.1'); INSERT INTO fract_only VALUES (3, '1.0'); -- should fail ERROR: numeric field overflow ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1. INSERT INTO fract_only VALUES (4, '-0.'); INSERT INTO fract_only VALUES (5, '0.4'); INSERT INTO fract_only VALUES (6, '0.5'); -- should fail ERROR: numeric field overflow ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1. INSERT INTO fract_only VALUES (7, '0.1'); INSERT INTO fract_only VALUES (8, '0.00017'); SELECT * FROM fract_only; --- 688,699 INSERT INTO fract_only VALUES (2, '0.1'); INSERT INTO fract_only VALUES (3, '1.0'); -- should fail ERROR: numeric field overflow ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5. INSERT INTO fract_only VALUES (4, '-0.'); INSERT INTO fract_only VALUES (5, '0.4'); INSERT INTO fract_only VALUES (6, '0.5'); -- should fail ERROR: numeric field overflow ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5. INSERT INTO fract_only VALUES (7, '0.1'); INSERT INTO fract_only VALUES (8, '0.00017'); SELECT * FROM fract_only; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] Numeric overflow problem + patch
On Thu, Sep 28, 2006 at 05:11:43PM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1. [ becomes ] ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5. This strikes me as overly pedantic. The message needs to be clear, and the proposed change will just confuse people. It might, but the error that's currently in there is wrong. With the patch applied, you get: postgres=# SELECT .5::NUMERIC(4,4); ERROR: numeric field overflow DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5. postgres=# SELECT .4999::NUMERIC(4,4); numeric - 0. (1 row) I'd thought of changing it to the corresponding numeric piece, but this doesn't work so well for NUMERIC(16,8) and the like. Cheers, D -- 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
Re: [PATCHES] [HACKERS] Numeric overflow problem + patch
On Thu, Sep 28, 2006 at 11:16:56PM +0200, Martijn van Oosterhout wrote: On Thu, Sep 28, 2006 at 05:11:43PM -0400, Tom Lane wrote: David Fetter [EMAIL PROTECTED] writes: ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1. [ becomes ] ! DETAIL: A field with precision 4, scale 4 must have an absolute value less than 1 - 5 * 10^-5. This strikes me as overly pedantic. The message needs to be clear, and the proposed change will just confuse people. I don't know if the code can detect the difference, but a message like: A field with precision 4, scale 4 must *round to* an absolute value less than 1 What does .999 round to? How about .5? Since that more accurately describes the actual problem. I'd say it doesn't, as worded. Maybe some other wording would be clearer. 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] XML syntax patch
On Mon, Sep 04, 2006 at 09:21:12PM -0400, Bruce Momjian wrote: I have received an update XML syntax patch from Nikolay (summer of code) based on David Fetter's patch from 2005. Comments? It would be nice to have for 8.2. It was Pavel Stehule's patch. I just added a little bit of documentation. 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: [PATCHES] Concurrent connections in psql patch
On Sun, Sep 03, 2006 at 05:09:44PM -0400, Gregory Stark wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is this something people are interested in? I am thinking no based on the lack of requests and the size of the patch. Lack of requests? I was actually surprised by how enthusiastically people reacted to it. I think it could form the basis of some concurrency testing, something we'll need more and more as time goes on. :) Gregory, Would you be up for getting this updated in the 8.3 cycle? Cheers, D However I don't think the patch as is is ready to be committed. Aside from missing documentation and regression tests it was only intended to be a proof-of-concept and to be useful for specific tests I was doing. I did try to do a decent job, I got \timing and server-tracked variables like encoding. But I need to go back through the code and make sure there are no other details like that. It would be nice to get feedback from other developers from looking at the patch to confirm that there aren't more fundamental problems with the approach and how it uses libpq before I go through the effort of cleaning up the details. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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 -- 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
[PATCHES] Predicate Locking
Folks, This patch clarifies the 'predicate locking' section in the docs. Thanks to Harrison Fisk of MySQL AB for helping. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Index: doc/src/sgml/mvcc.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v retrieving revision 2.57 diff -c -r2.57 mvcc.sgml *** doc/src/sgml/mvcc.sgml 25 Aug 2006 04:06:45 - 2.57 --- doc/src/sgml/mvcc.sgml 1 Sep 2006 17:28:28 - *** *** 471,477 result in problems. (Certainly the example above is rather contrived and unlikely to represent real software.) Accordingly, productnamePostgreSQL/productname does not implement predicate ! locking, and so far as we are aware no other production DBMS does either. /para para --- 471,480 result in problems. (Certainly the example above is rather contrived and unlikely to represent real software.) Accordingly, productnamePostgreSQL/productname does not implement predicate ! locking. No system based on next-key locking implements it ! either because next-key locking only helps when all your ! predicates are point or range searches against an available ! B-tree index. DB2, however, does implement predicate locking. /para para ---(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] New variable server_version_num
On Sat, Jul 29, 2006 at 09:14:16PM -0400, Greg Sabino Mullane wrote: Today on IRC David Fetter and some others were discussing version numbers and we realized that although libpq now provides the version of Postgres as a number, this is still a wheel that is being reinvented by apps many times over, as it is not available any other way. Hence, a small patch to provide a new variable server_version_num, which is almost the same as server_version but uses the handy PG_VERSION_NUM which allows apps to do things like if ($version = 80200) without having to parse apart the value of server_version themselves. Here's an SQL function which does the same thing. I've had it tested back through 7.4x, and it should work back to the 7.3 series, although I haven't tested it there. Thanks to Andrew of Supernews for the short version :) SELECT sum( substring( split_part( current_setting( 'server_version' ), '.' , i ) FROM '^[[:digit:]]+' )::integer * 10^(6-i*2) ) AS server_version_integer FROM ( SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 ) AS s(i); 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] New variable server_version_num
On Sat, Jul 29, 2006 at 09:14:16PM -0400, Greg Sabino Mullane wrote: Today on IRC David Fetter and some others were discussing version numbers and we realized that although libpq now provides the version of Postgres as a number, this is still a wheel that is being reinvented by apps many times over, as it is not available any other way. Hence, a small patch to provide a new variable server_version_num, which is almost the same as server_version but uses the handy PG_VERSION_NUM which allows apps to do things like if ($version = 80200) without having to parse apart the value of server_version themselves. What's the status on applying this patch? Cheers, D -- 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
Re: [PATCHES] [EMAIL PROTECTED]: plperl enhancing return possibilities]
On Wed, Aug 09, 2006 at 09:53:08PM -0400, 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. Bruce, I believe this one's been superseded by a later patch directly from Pavel. Cheers, D --- David Fetter wrote: Folks, Pavel Stehule sent this to me, thinking I could review it. I can't just yet. It refers to http://archives.postgresql.org/pgsql-hackers/2006-07/msg01421.php Thanks in advance :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! -- Start of included mail From: Pavel Stehule [EMAIL PROTECTED] Subject: plperl enhancing return possibilities To: [EMAIL PROTECTED], [EMAIL PROTECTED] Date: Sun, 30 Jul 2006 23:02:15 +0200 Hello, I sending this patch for review. I'll try separate this patch to a) better array support, b) consistency in OUT parameters. I invite any comments Regards Pavel Stehule [ Attachment, skipping... ] -- End of included mail. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] [DOCS] Values list-of-targetlists patch for comments (was Re:
On Wed, Aug 09, 2006 at 03:05:02PM +0200, Peter Eisentraut wrote: Am Freitag, 4. August 2006 04:50 schrieb Tom Lane: I'd like to see us refactor the docs as necessary to reflect that idea. Peter is right that this needs some discussion in syntax.sgml as well as in the reference pages --- but I'm still not very clear on how the presentation should go. I'm beginning to think that VALUES might be a separate command after all. What's below definitely bolsters that idea :) postgres=# VALUES(1); column1 - 1 (1 row) However, there are some oddities: postgres=# SELECT * FROM (VALUES (1,2)) AS foo(bar,baz); bar | baz -+- 1 | 2 (1 row) postgres=# (VALUES (1,2)) AS foo(bar,baz); ERROR: syntax error at or near AS LINE 1: (VALUES (1,2)) AS foo(bar,baz); Does the SQL standard have anything to say about assigning identifiers both to the entire VALUES() statement and to its columns when the VALUES() statement is by itself? Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] log_statement output for protocol
On Sat, Aug 05, 2006 at 07:39:48PM +1200, Oliver Jowett wrote: Bruce Momjian wrote: OK, updated patch, with output of text bind parameters. New output is: LOG: prepare sel1: SELECT $1 + $2; LOG: bind sel1: SELECT $1 + $2; LOG: bind sel1: parameter 1: 8 LOG: bind sel1: parameter 2: 5 LOG: execute sel1: SELECT $1 + $2; I put each bind parameter on a separate line. Is that OK? My only comment here is that this is very verbose. The JDBC driver now always uses the extended query protocol, even when not doing server-side prepare, so you're going to get multiple lines of output all the time when using JDBC apps. Does this mean that the JDBC driver needs work on this? A 50-parameter query could be .. interesting .. I realize that you need this level of output to reflect what is happening at the protocol level, but seeing all the protocol detail is not really what you expect when you turn on basic statement logging, is it? Possibly not basic statement logging, but there are cases where I would have saved a *lot* of time if it had been available. 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] New variable server_version_num
On Tue, Aug 01, 2006 at 04:25:00PM -0400, Christopher Browne wrote: ?column? | setting --+- 5432 | 7.4.13 (1 row) ?column? | setting --+- 5533 | 7.4.10 (1 row) ?column? | setting --+- 5532 | 8.0.5 (1 row) ?column? | setting --+-- 5882 | 8.2devel (1 row) If I wanted to, it oughtn't be difficult to string smash those settings into something very nearly useful... It may or may not be difficult, depending on your definition of 'difficult,' but it's very easy and reproducible to get something that can be cast to integer and compared that way. The existence of version-number-comparison libraries like version.pm points to the idea that it is, in fact, difficult to compare versions in general. Cheers, D -- 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
