[HACKERS] Review of SQLDA support for ECPG
I took a look at 2-pg85-sqlda-10-ctxdiff.patch. Starting from CVS HEAD of
roughly 2009-10-03 05:00 UTC, prerequisite patches 1a-1h applied cleanly.
2-pg85-sqlda hit a trivial whitespace reject in ecpg.trailer along with a more
substantive reject at ecpg.addons:407 (FetchStmtMOVEfetch_args). Fixing it up
by hand leads to my first question - why did the transition from `opt_ecpg_into'
to `opt_ecpg_fetch_into' affect FETCH FORWARD and not FETCH BACKWARD?
The main test suite acquired no regressions, but I get failures in two tests of
the ecpg test suite (make -C src/interfaces/ecpg/test check). I attach
regression.{out,diff} and postmaster.log from the test run. The abort in
sqlda.pgc looks like the interesting failure, but I exhausted time with which to
dig into it further. preproc/cursor.pgc creates (and ideally drops) the same
table `t1' as compat_informix/{sqlda,cursor}.pgc, so a crash in either of the
others makes it fail too. Could they all use temp tables, use different table
names, or `DROP TABLE IF EXISTS t1' first?
Do those logs suggest the cause of the sqlda.pgc failure? If not, I will look
into it further. Otherwise, I'm happy to review a future iteration of the
patch.
As a side note, with patch 1* but not patch 2, test_informix entered an infinite
loop with this error:
ERROR: syntax error at or near c at character 15
STATEMENT: fetch forward c
Thank you,
nm
test compat_informix/dec_test ... ok
test compat_informix/charfuncs ... ok
test compat_informix/rfmtdate ... ok
test compat_informix/rfmtlong ... ok
test compat_informix/rnull ... ok
test compat_informix/cursor ... ok
test compat_informix/sqlda ... FAILED (test process was terminated by signal 6:
Aborted)
test compat_informix/test_informix ... ok
test compat_informix/test_informix2 ... ok
test connect/test2... ok
test connect/test3... ok
test connect/test4... ok
test connect/test5... ok
test pgtypeslib/dt_test ... ok
test pgtypeslib/dt_test2 ... ok
test pgtypeslib/num_test ... ok
test pgtypeslib/num_test2 ... ok
test preproc/array_of_struct ... ok
test preproc/autoprep ... ok
test preproc/comment ... ok
test preproc/cursor ... FAILED (test process exited with exit code 1)
test preproc/define ... ok
test preproc/init ... ok
test preproc/strings ... ok
test preproc/type ... ok
test preproc/variable ... ok
test preproc/whenever ... ok
test sql/array... ok
test sql/binary ... ok
test sql/code100 ... ok
test sql/copystdout ... ok
test sql/define ... ok
test sql/desc ... ok
test sql/dynalloc ... ok
test sql/dynalloc2... ok
test sql/dyntest ... ok
test sql/execute ... ok
test sql/fetch... ok
test sql/func ... ok
test sql/indicators ... ok
test sql/oldexec ... ok
test sql/quote... ok
test sql/show ... ok
test sql/insupd ... ok
test sql/parser ... ok
test thread/thread... ok
test thread/thread_implicit ... ok
test thread/prep ... ok
test thread/alloc ... ok
test thread/descriptor... ok
***
/home/nm/src/pg/bd-sqlda/src/interfaces/ecpg/test/expected/compat_informix-sqlda.stdout
2009-10-03 04:23:59.0 -0400
---
/home/nm/src/pg/bd-sqlda/src/interfaces/ecpg/test/results/compat_informix-sqlda.stdout
2009-10-04 01:52:52.0 -0400
***
*** 1,60
- FETCH RECORD 1
- name sqlda descriptor: 'id' value 1
- name sqlda descriptor: 't' value 'a'
- name sqlda descriptor: 'd1' value DECIMAL '1.0'
- name sqlda descriptor: 'd2' value 1.00
- name sqlda descriptor: 'c' value 'a '
- FETCH RECORD 2
- name sqlda descriptor: 'id' value 2
- name sqlda descriptor: 't' value NULL'
- name sqlda descriptor: 'd1' value NULL'
- name sqlda descriptor: 'd2' value NULL'
- name sqlda descriptor: 'c' value NULL'
- FETCH RECORD 3
- name sqlda descriptor: 'id' value 3
- name sqlda descriptor: 't' value 'c'
- name sqlda descriptor: 'd1' value DECIMAL '-3'
- name sqlda descriptor: 'd2' value nan
- name sqlda descriptor: 'c' value 'c '
- FETCH RECORD 4
- name sqlda descriptor: 'id' value 4
- name sqlda descriptor: 't' value 'd'
- name sqlda descriptor: 'd1' value DECIMAL '4.0'
- name sqlda descriptor: 'd2' value 4.00
- name sqlda descriptor: 'c' value 'd '
- FETCH RECORD 1
- name sqlda descriptor: 'id' value 1
- name sqlda descriptor: 't' value 'a'
- name sqlda descriptor: 'd1' value DECIMAL '1.0'
- name sqlda descriptor: 'd2' value 1.00
- name sqlda descriptor: 'c' value 'a '
- FETCH RECORD 2
- name sqlda descriptor: 'id' value 2
- name sqlda descriptor: 't' value NULL'
- name sqlda descriptor: 'd1' value NULL'
- name sqlda descriptor: 'd2' value NULL'
- name sqlda descriptor: 'c' value NULL'
- FETCH RECORD 3
- name sqlda descriptor: 'id' value 3
- name sqlda descriptor: 't' value 'c'
- name sqlda descriptor:
Re: [HACKERS] Getting the red out (of the buildfarm)
On Sat, 2009-10-03 at 13:40 -0400, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: OK, the reason I couldn't reproduce this for the life of me is that I had PGCLIENTENCODING=UTF8 in the environment of the server(!). Once I unset that, I could reproduce the problem. This could be made a bit more well-defined if we ran pg_regress with --multibyte=something, although that is then liable to fail in encodings that don't have an equivalent of \u0080. Some with your suggestion above: It will only work for some encodings. I'm back to wondering why we need a regression test for this at all. Wouldn't it be just as useful to be testing a character code that is well-defined everywhere? Or just drop this test altogether? It's already got way too many expected files for my taste. Note that I didn't write this test; it has been there for ages. It used to prove that you couldn't process non-ASCII Unicode characters in PL/Python at all (for some value of at all ...), and after I implemented Unicode support they now show that you can. So they served a real purpose, and changing them to use an ASCII character code (which is presumably the only thing that is well-defined everywhere) wouldn't have done the same thing. (In that case I probably would have had to write the test case myself.) I understand the annoyance, but I think we do need to have an organized way to do testing of non-ASCII data and in particular UTF8 data, because there are an increasing number of special code paths for those. Perhaps we could have a naming convention for test files like testname.utf8.sql, so they only get run in the appropriate environment. Any scheme like that has the disadvantage, however, that the proper rejection of non-ASCII data in ASCII environments isn't tested. (That's what all these alternative result files for the plpython_unicode test are for, btw.) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] taking a stab at agg(foo ORDER BY bar)
The spec defines array_agg(foo ORDER BY ...) which we don't implement yet; obviously, we have no reason not to define this for any aggregate, rather than just array_agg. This doesn't seem to present any problems as far as the syntax goes, and the actual execution is just a small matter of coding, but I'm not seeing the best way to handle it in parse-analysis. All the existing infrastructure for ORDER BY seems to be dependent on targetlists, which obviously we don't have in the context of an aggregate call. Ideas? -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using results from INSERT ... RETURNING
On Fri, Oct 2, 2009 at 7:37 AM, Marko Tiikkaja
marko.tiikk...@cs.helsinki.fi wrote:
Robert Haas wrote:
Now the point here is that I eventually want to be able to write
something like this:
with foo as (insert into project (name) values ('Writeable CTEs')
returning id) select * from foo;
...but how does this get me any closer? It seems to me that the plan
for THAT statement has to be a CTE scan over top of BOTH of the
inserts, but here I have two insert nodes that comprise two separate
plans. The DML node, as presently implemented, supports a list of
plans, but they all have to be of the same type, so it's really only
useful for handling append, and as previously discussed, it's not
clear that the proposed handling is any better than what we already
have.
I don't think you should be able to do this. I'm not too familiar with
rules, but in your example the rule doesn't modify the output of the
INSERT .. RETURNING so I think it shouldn't do that here either. How I
see it is that in your example the INSERT INTO shadow would be added to
the top level instead of the CTE and the plan would look something like
this:
CTE Scan on foo (cost=0.01..0.03 rows=1 width=4)
CTE foo
- Insert (cost=0.00..0.01 rows=1 width=0)
- Result (cost=0.00..0.01 rows=1 width=0)
Insert (cost=0.00..0.01 rows=1 width=0)
- Result (cost=0.00..0.01 rows=1 width=0)
so you would get the RETURNING output from the CTE and the INSERT to the
shadow table would be executed separately.
Yeah, I think you're right.
I'm not saying that we don't
want to provide the means to do this, but writeable CTEs alone aren't
meant to handle this.
Well, I think a patch to implement writeable CTEs is probably going to
have to handle this case - I don't think we can just ignore rewrite
rules when processing a CTE. But it does seem to be beyond the scope
of the current patch.
I'm going to go ahead and mark this Ready for Committer. Thanks for
your patience.
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using results from INSERT ... RETURNING
Marko - I noticed something a little odd about the new append-plan handling. rhaas=# explain update parent set c = 1; QUERY PLAN --- Update (cost=0.00..60.80 rows=4080 width=12) - Seq Scan on parent (cost=0.00..31.40 rows=2140 width=10) - Seq Scan on child parent (cost=0.00..29.40 rows=1940 width=14) (3 rows) That may be OK, actually, but it does look a little weird. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using results from INSERT ... RETURNING
On Sun, Oct 4, 2009 at 8:14 AM, Robert Haas robertmh...@gmail.com wrote: Marko - I noticed something a little odd about the new append-plan handling. rhaas=# explain update parent set c = 1; QUERY PLAN --- Update (cost=0.00..60.80 rows=4080 width=12) - Seq Scan on parent (cost=0.00..31.40 rows=2140 width=10) - Seq Scan on child parent (cost=0.00..29.40 rows=1940 width=14) (3 rows) That may be OK, actually, but it does look a little weird. Argh. Nevermind. It was like that before. Sigh. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using results from INSERT ... RETURNING
Robert Haas wrote: I'm not saying that we don't want to provide the means to do this, but writeable CTEs alone aren't meant to handle this. Well, I think a patch to implement writeable CTEs is probably going to have to handle this case - I don't think we can just ignore rewrite rules when processing a CTE. But it does seem to be beyond the scope of the current patch. My use of this was a bit ambiguous here; what I meant was that writeable CTEs are going to work just like a top-level INSERT .. RETURNING would have, i.e. return only rows inserted to project. I'm going to go ahead and mark this Ready for Committer. Thanks for your patience. Thanks for reviewing! Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE LIKE INCLUDING COMMENTS and STORAGES
Recently, I encountered a situation where the docs on (or impl?) INCLUDING INDEXES and INCLUDING CONSTRAINTS are not clearly defined for primary keys. Should it be noted in the docs that in this case, we are referring to the technical implementation of a primary key, i.e. a unique index and a not null constraint, thus both conditions are required? testdb= CREATE TABLE foo (id int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for table foo CREATE TABLE testdb= \d foo; Table public.foo Column | Type | Modifiers +-+--- id | integer | not null Indexes: foo_pkey PRIMARY KEY, btree (id) testdb= CREATE TABLE foo2 (LIKE FOO INCLUDING CONSTRAINTS EXCLUDING INDEXES); CREATE TABLE testdb= \d foo2 Table public.foo2 Column | Type | Modifiers +-+--- id | integer | not null testdb= CREATE TABLE foo3 (LIKE FOO EXCLUDING CONSTRAINTS INCLUDING INDEXES); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo3_pkey for table foo3 CREATE TABLE testdb= \d foo3; Table public.foo3 Column | Type | Modifiers +-+--- id | integer | not null Indexes: foo3_pkey PRIMARY KEY, btree (id) testdb= Regards, Khee Chin. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Getting the red out (of the buildfarm)
Peter Eisentraut pete...@gmx.net writes: I understand the annoyance, but I think we do need to have an organized way to do testing of non-ASCII data and in particular UTF8 data, because there are an increasing number of special code paths for those. Well, if you want to keep the test, we should put in the variant with \200, because it is now clear that that is in fact the right answer in a nontrivial number of environments (arguably *more* cases than in which \u0080 is correct). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] taking a stab at agg(foo ORDER BY bar)
Andrew Gierth and...@tao11.riddles.org.uk writes: This doesn't seem to present any problems as far as the syntax goes, and the actual execution is just a small matter of coding, but I'm not seeing the best way to handle it in parse-analysis. All the existing infrastructure for ORDER BY seems to be dependent on targetlists, which obviously we don't have in the context of an aggregate call. Well, if you don't want to refactor that code, it wouldn't be difficult to make a one-entry tlist containing the aggregate argument, and then throw it away again after you'd extracted what you need. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using results from INSERT ... RETURNING
Robert Haas robertmh...@gmail.com writes: Well, I think a patch to implement writeable CTEs is probably going to have to handle this case - I don't think we can just ignore rewrite rules when processing a CTE. But it does seem to be beyond the scope of the current patch. I hadn't been paying too much attention to this thread, but ... why is anyone worrying about that? Rewrite rules are not the concern of either the planner or the executor. A do also rule will result in two entirely separate Query trees, which will each be planned separately and executed separately. Any given executor run only has to think about one type of DML command --- otherwise the executor would be broken already, since it takes only one command-type argument. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using results from INSERT ... RETURNING
Tom Lane wrote: Any given executor run only has to think about one type of DML command --- otherwise the executor would be broken already, since it takes only one command-type argument. If I understood you correctly, this would imply that you wouldn't be able to do for example: INSERT INTO foo WITH t AS ( DELETE FROM bar RETURNING * ) SELECT * FROM t; which is probably the most useful thing you could do with this feature. Am I misinterpreting what you said? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using results from INSERT ... RETURNING
On Oct 4, 2009, at 11:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Well, I think a patch to implement writeable CTEs is probably going to have to handle this case - I don't think we can just ignore rewrite rules when processing a CTE. But it does seem to be beyond the scope of the current patch. I hadn't been paying too much attention to this thread, but ... why is anyone worrying about that? Rewrite rules are not the concern of either the planner or the executor. A do also rule will result in two entirely separate Query trees, which will each be planned separately and executed separately. Any given executor run only has to think about one type of DML command --- otherwise the executor would be broken already, since it takes only one command-type argument. If an INSERT/UPDATE/DELETE appears within a CTE, it will still need to be rewritten. But you're right that this is irrelevant to the present patch; I just didn't see that at once. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using results from INSERT ... RETURNING
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: If I understood you correctly, this would imply that you wouldn't be able to do for example: INSERT INTO foo WITH t AS ( DELETE FROM bar RETURNING * ) SELECT * FROM t; Um ... forget what I said --- not enough caffeine yet, apparently. Yeah, rewrite rules are going to be a *serious* stumbling block to this whole concept. Maybe we should just punt the project until we have a clear idea of how to do that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using results from INSERT ... RETURNING
On Sun, Oct 04, 2009 at 01:16:50PM -0400, Tom Lane wrote: Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: If I understood you correctly, this would imply that you wouldn't be able to do for example: INSERT INTO foo WITH t AS ( DELETE FROM bar RETURNING * ) SELECT * FROM t; Um ... forget what I said --- not enough caffeine yet, apparently. Yeah, rewrite rules are going to be a *serious* stumbling block to this whole concept. Maybe we should just punt the project until we have a clear idea of how to do that. Maybe rewrite rules just don't fit with this feature, and should cause an error. We have other things that don't work together, and the world hasn't ended yet. This leads me to another Modest Proposal, which I'll detail in another post. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using results from INSERT ... RETURNING
On Oct 4, 2009, at 1:24 PM, David Fetter da...@fetter.org wrote: On Sun, Oct 04, 2009 at 01:16:50PM -0400, Tom Lane wrote: Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: If I understood you correctly, this would imply that you wouldn't be able to do for example: INSERT INTO foo WITH t AS ( DELETE FROM bar RETURNING * ) SELECT * FROM t; Um ... forget what I said --- not enough caffeine yet, apparently. Yeah, rewrite rules are going to be a *serious* stumbling block to this whole concept. Maybe we should just punt the project until we have a clear idea of how to do that. This was discussed a bit upthread and doesn't seem obviously unmanageable to me. Maybe rewrite rules just don't fit with this feature, and should cause an error. We have other things that don't work together, and the world hasn't ended yet. That doesn't seem very satisfactory. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY enhancements
On Fri, 2009-09-25 at 10:01 -0400, Emmanuel Cecchet wrote: Robert, Here is the new version of the patch that applies to CVS HEAD as of this morning. I just started looking at this now. It seems to fail make check, diffs attached. I haven't looked into the cause of the failure yet. Regards, Jeff Davis *** /home/jdavis/wd/git/postgresql/src/test/regress/expected/copy_errorlogging.out 2009-10-04 10:24:15.0 -0700 --- /home/jdavis/wd/git/postgresql/src/test/regress/results/copy_errorlogging.out 2009-10-04 10:24:32.0 -0700 *** *** 46,58 -- both \n and \r\n present COPY foo FROM '/home/jdavis/wd/git/postgresql/src/test/regress/data/foo_malformed_terminator.data'; - ERROR: literal carriage return found in data - HINT: Use \r to represent carriage return. - CONTEXT: COPY foo, line 2: SELECT count(*) from foo; count --- ! 0 (1 row) -- create error logging table --- 46,55 -- both \n and \r\n present COPY foo FROM '/home/jdavis/wd/git/postgresql/src/test/regress/data/foo_malformed_terminator.data'; SELECT count(*) from foo; count --- ! 5 (1 row) -- create error logging table *** *** 75,81 SELECT count(*) from foo; count --- ! 4 (1 row) DELETE FROM foo; --- 72,78 SELECT count(*) from foo; count --- ! 9 (1 row) DELETE FROM foo; *** *** 101,113 DELETE FROM foo; -- error logging skipping tuples: both \n and \r\n present COPY foo FROM '/home/jdavis/wd/git/postgresql/src/test/regress/data/foo_malformed_terminator.data' (ERROR_LOGGING, ERROR_LOGGING_SKIP_BAD_ROWS); - ERROR: literal carriage return found in data - HINT: Use \r to represent carriage return. - CONTEXT: COPY foo, line 2: SELECT count(*) from foo; count --- ! 0 (1 row) DELETE FROM foo; --- 98,107 DELETE FROM foo; -- error logging skipping tuples: both \n and \r\n present COPY foo FROM '/home/jdavis/wd/git/postgresql/src/test/regress/data/foo_malformed_terminator.data' (ERROR_LOGGING, ERROR_LOGGING_SKIP_BAD_ROWS); SELECT count(*) from foo; count --- ! 5 (1 row) DELETE FROM foo; == -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unicode UTF-8 table formatting for psql text output
On Fri, Oct 02, 2009 at 05:34:16PM -0700, Brad T. Sliger wrote: On Friday 02 October 2009 04:21:35 Roger Leigh wrote: I have attached a patch which implements the feature as a pset variable. This also slightly simplifies some of the patch since the table style is passed to functions directly in printTableContent rather than separately. The psql option '-P tablestyle=ascii' is passed to psql in pg_regress_main.c which means the testsuite doesn't fail any more. The option is documented in the psql docs, and is also tab-completed. Users can just put '\pset tablestyle ascii' in their .psqlrc if they want the old format in a UTF-8 locale. I looked at psql-utf8-table-5.patch. Many thanks for taking the time to do this. I've attached a followup patch which addresses your point below: Lint(1) says there is an extra trailing ',' in src/bin/psql/print.h. in 'typedef enum printTextRule'. The addition to src/bin/psql/command.c could use a comment, like adjacent code. Fixed. 'ASCII' and 'UTF8' may need acronym/acronym tags in doc/src/sgml/ref/psql-ref.sgml, like adjacent code. I'm not sure someone who hasn't seen this patch in action would immediately know what it does from the documentation. `gmake html` works without the patch, but fails with the patch: Also fixed. I also added some additional explanation of the option which hopefully makes its purpose more obvious. The acronym tag isn't used for the itemised option list names, but is used in the descriptive text; I can also add it there if appropriate. It's likely that tablestyle could well be named better. format is already used, but if there's a more intuitive name that fits better, I'm happy to change it. openjade:ref/psql-ref.sgml:1692:15:E: document type does not allow element TERM here; assuming missing VARLISTENTRY start-tag Also fixed. After the patch, `\pset format wrapped` produces '\pset: unknown option: format'. I saw this in interactive psql and from .psqlrc. I think this can be fixed by changing the addition to src/bin/psql/command.c from an 'if' clause to an 'else if' clause. Oops, yes. Sorry about that hiccup. I've also fixed this. Regards, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ `. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/ `-GPG Public Key: 0x25BFB848 Please GPG sign your mail. diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 85e9375..6f1bdc7 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1690,6 +1690,54 @@ lo_import 152801 /varlistentry varlistentry + termliteraltablestyle/literal/term + listitem + para + Sets the style of text table output to one + of literalascii/literal, or literalutf8/literal. + Unique abbreviations are allowed. (That would mean one + letter is enough.) literalutf8/literal will be selected + by default if supported by your locale, + otherwise literalascii/literal will be used. + /para + + para + Query result tables are displayed as text for some output + formats (literalunaligned/literal, + literalaligned/literal, and literalwrapped/literal + formats). The tables are drawn using characters of the + user's locale character set. By + default, acronymASCII/acronym characters will be used, + which will display correctly in all locales. However, if + the user is using a locale with a acronymUTF-8/acronym + character set, the default will be to + use acronymUTF-8/acronym box drawing characters in place + of ASCII punctuation to display more readable tables. + /para + + para + This option is useful for overriding the default table + style, for example to force the use of + only acronymASCII/acronym characters when extended + character sets such as acronymUTF-8/acronym are + inappropriate. This might be the case if preserving output + compatibility with older psql versions is important (prior + to 8.5.0). + /para + + para + quoteUTF8/quote use Unicode acronymUTF-8/acronym box + drawing characters. + /para + + para + quoteASCII/quote use plain acronymASCII/acronym characters. + /para + + /listitem + /varlistentry + + varlistentry termliteralcolumns/literal/term listitem para diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index da57eb4..31631cf 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -1772,6 +1772,25 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet) printf(_(Output format is %s.\n),
[HACKERS] Rules: A Modest Proposal
Folks, At the moment, user-accessible RULEs have, as far as I know, just two sane uses: * Writing to VIEWs * Routing writes to partitions And the second is pretty thin, given the performance issues for numbers of partitions over 2. What say we see about addressing those problems separately, and removing user-accessible RULEs entirely? There are already patches to deal with the first, at least for the kinds of VIEWs where this can be deduced automatically, and people are starting to take on the second. The one remaining (as in nobody's really addressed it with code) issue would be triggers on VIEWs. As other systems have done it, it's clearly not essentially impossible. What would be needed? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
2009/10/4 David Fetter da...@fetter.org: Folks, At the moment, user-accessible RULEs have, as far as I know, just two sane uses: * Writing to VIEWs * Routing writes to partitions somebody use it as instead triggers. And I am sure, so there are people, who use it for writable views. regards Pavel Stehule And the second is pretty thin, given the performance issues for numbers of partitions over 2. What say we see about addressing those problems separately, and removing user-accessible RULEs entirely? There are already patches to deal with the first, at least for the kinds of VIEWs where this can be deduced automatically, and people are starting to take on the second. The one remaining (as in nobody's really addressed it with code) issue would be triggers on VIEWs. As other systems have done it, it's clearly not essentially impossible. What would be needed? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] DefaultACLs
On 10/3/09 8:09 AM, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: let's let the default, global default ACL contain the hard-wired privileges, instead of making them hardwired. Wow, that would be great. It would meant that DBAs could change the global default permissions. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Privileges and inheritance
So let's get rid of that. Selecting (or in general, operating) on a table with children only checks the privileges on that table, not the children. Is there any use case where the current behavior is useful at all? In theory, someone out there may be using privs to restrict access to child tables. In practice, this would be unmanageable enough that I doubt anyone is doing it intentionally. Except ... I can imagine a multi-tenant setup where certain ROLEs only have permissions on some child relations, but not others. So we'd want to still enable a permissions check on a child when the child is called directly rather than through the parent. And we'd want to hammer this to death looking for ways it can be a security exploit. Like, could you make a table into the parent of an existing table you didn't have permissions on? We could use a GUC variable to ease the transition, perhaps like sql_inheritance = no | yes_without_privileges | yes no | without_privileges | yes Mind you, this is a boolean now, isn't it? --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
There are already patches to deal with the first, at least for the kinds of VIEWs where this can be deduced automatically, and people are starting to take on the second. How would we deal with VIEWs which weren't simple enough for automated updating, then? I don't think that removing a major feature, one which some users have written applications around, is even feasible. What would be the benefit of this radical proposal? --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
On Sun, Oct 04, 2009 at 11:42:45AM -0700, Josh Berkus wrote: There are already patches to deal with the first, at least for the kinds of VIEWs where this can be deduced automatically, and people are starting to take on the second. How would we deal with VIEWs which weren't simple enough for automated updating, then? I don't think that removing a major feature, one which some users have written applications around, is even feasible. What would be the benefit of this radical proposal? --Josh Berkus When you speak of writing to a view, what do you mean exactly? Are we saying refresh a view or update the parent tables of a view? -- --Dan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
Dan Colish wrote: When you speak of writing to a view, what do you mean exactly? Are we saying refresh a view or update the parent tables of a view? He means INSERT, UPDATE and DELETE operations on the view. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
On Sun, Oct 04, 2009 at 08:48:15PM +0200, Pavel Stehule wrote: 2009/10/4 David Fetter da...@fetter.org: Folks, At the moment, user-accessible RULEs have, as far as I know, just two sane uses: * Writing to VIEWs * Routing writes to partitions somebody use it as instead triggers. Some people also shoot themselves in the foot. They're mostly a foot-gun. And I am sure, so there are people, who use it for writable views. That *is* the first case I mentioned. Your point is? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
On Sun, Oct 04, 2009 at 03:15:10PM -0400, Andrew Dunstan wrote: Dan Colish wrote: When you speak of writing to a view, what do you mean exactly? Are we saying refresh a view or update the parent tables of a view? He means INSERT, UPDATE and DELETE operations on the view. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers How would you resolve where to perform these operations in the parent tables? I have not discovered a good way to determine which tables a user would desire to alter if the view contains a subset of data from the parent and these subsets do not include the primary keys. Even with primary keys as members of a view, there is a good potential for side effects. For example, consider the following tables: usernames, student_grades, course_listings If you have a view joining all three tables and delete one row in the view, you have the potential for deleting too much data from a parent table; ie, you choose to delete a username and its associated grades but also end up deleteing a course. You could also delete a course and end up deleting all the usernames and the grades associated. -- --Dan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Using results from INSERT ... RETURNING
Tom Lane wrote: Yeah, rewrite rules are going to be a *serious* stumbling block to this whole concept. Maybe we should just punt the project until we have a clear idea of how to do that. I've implemented rewrite rules for writeable CTEs, and at least now I don't see any problems except one. I can't seem to think of what would be the correct behaviour in this case: = CREATE rule foo_rule AS ON INSERT TO foo DO ALSO SELECT * FROM bar; CREATE RULE = WITH t AS (INSERT INTO foo VALUES(0) RETURNING *) SELECT * FROM t; If you rewrite the query as it is rewritten in the top-level case, you get a plan such as this: --- CTE Scan ON t (cost=0.01..0.03 rows=1 width=4) CTE t - INSERT (cost=0.00..0.01 rows=1 width=0) - Result (cost=0.00..0.01 rows=1 width=0) Seq Scan ON bar (cost=0.00..34.00 rows=2400 width=4) and now you have *two* SELECT statements. Currently the portal code gives the output of the Seq Scan ON bar here which is IMO very surprising. Does ignoring the rule here sound sane or should we error out? Or does someone have a better idea? DO ALSO INSERT/UPDATE/DELETE works as expected here. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
On Sun, Oct 04, 2009 at 11:42:45AM -0700, Josh Berkus wrote: There are already patches to deal with the first, at least for the kinds of VIEWs where this can be deduced automatically, and people are starting to take on the second. How would we deal with VIEWs which weren't simple enough for automated updating, then? View triggers, as proposed. I don't think that removing a major feature, one which some users have written applications around, is even feasible. *I've* written an application around them, and frankly, they are a giant foot-gun in every case that's not already handle-able other ways. What would be the benefit of this radical proposal? The radical proposal was the RULE system. It's been tested now, and it's pretty much failed. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Privileges and inheritance
On Sun, 2009-10-04 at 11:56 -0700, Josh Berkus wrote: Except ... I can imagine a multi-tenant setup where certain ROLEs only have permissions on some child relations, but not others. So we'd want to still enable a permissions check on a child when the child is called directly rather than through the parent. Well, when you access the child, it doesn't care whether it has a parent. So this is equivalent to checking permissions before accessing a table, period. I think we'll keep that. ;-) And we'd want to hammer this to death looking for ways it can be a security exploit. Like, could you make a table into the parent of an existing table you didn't have permissions on? I don't think so, but you're free to hammer. ;-) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
On Sun, Oct 4, 2009 at 3:34 PM, David Fetter da...@fetter.org wrote: What would be the benefit of this radical proposal? The radical proposal was the RULE system. It's been tested now, and it's pretty much failed. You still haven't explained what actual benefit we'd get out of doing this. I agree that rules, except for SELECT rules, don't seem to be very useful. Perhaps others have found them so, but I have found triggers to be a better fit for everything that I ever want to do. Every time I think, hmm, maybe I could use a rule for that, I reread the chapter and change my mind. However, there is a very real possibility that there are people out there who have applications that are based on the way rules work today. If we were to remove support for rules, they would not be able to upgrade past 8.4. That seems to me to be the sort of thing that we wouldn't want to do unless we had a good reason - and the closest you've come to saying what you think that reason might be is they're mostly a foot-gun, which I don't find very compelling. I think we want to be moving in the direction of making upgrading easier, not more difficult, and that means maintaining backward compatibility even for features that are of marginal utility, unless they're getting in the way of something else. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
2009/10/4 David Fetter da...@fetter.org: On Sun, Oct 04, 2009 at 08:48:15PM +0200, Pavel Stehule wrote: 2009/10/4 David Fetter da...@fetter.org: Folks, At the moment, user-accessible RULEs have, as far as I know, just two sane uses: * Writing to VIEWs * Routing writes to partitions somebody use it as instead triggers. Some people also shoot themselves in the foot. They're mostly a foot-gun. it same as inheritance. BEFORE triggers should be a problem to (in some cases) And I am sure, so there are people, who use it for writable views. That *is* the first case I mentioned. Your point is? sorry updateable views, is correct name. I know, so rules are dangerous gun, but I know so there are people, who use it. And actually we don't have a substitutions. I thing so if pg drop a rules. then it needs true updateable views and instead triggers. And maybe some as audit tools. When you would to to drop some functionality, then you have to propose a substitution. Pavel Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Lock Wait Statistics (next commitfest)
On Mon, Sep 28, 2009 at 12:14 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sat, Aug 8, 2009 at 7:47 PM, Mark Kirkwood mar...@paradise.net.nz wrote: Patch with max(wait time). Still TODO - amalgamate individual transaction lock waits - redo (rather ugly) temporary pg_stat_lock_waits in a form more like pg_locks This version has the individual transaction lock waits amalgamated. Still TODO: redo pg_stat_lock_waits ... it applies with some hunks, compiles fine and seems to work... i'm still not sure this is what we need, some more comments could be helpful. what kind of questions are we capable of answer with this and and what kind of questions are we still missing? for example, now we know number of locks that had to wait, total time waiting and max time waiting for a single lock... but still we can have an inaccurate understanding if we have lots of locks waiting little time and a few waiting a huge amount of time... Aren't the huge ones already loggable from the deadlock detector? With the max, we can at least put an upper limit on how long the longest ones could have been. However, is there a way to reset the max? I tried deleting data/pg_stat_tmp, but that didn't work. With cumulative values, you can you take snapshots and then take the difference of them, that won't work with max. If the max can't be reset except with an initdb, I think that makes it barely usable. something i have been asked when system starts to slow down is can we know if there were a lock contention on that period? for now the only way to answer that is logging locks I was surprised to find that running with track_locks on did not cause a detectable difference in performance, so you could just routinely do regularly scheduled snapshots and go back and mine them over the time that a problem was occurring. I just checked with pgbench over various levels of concurrency and fsync settings. If potential slowness wouldn't show up there, I don't know how else to look for it. Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unicode UTF-8 table formatting for psql text output
I have a comment on this bit: @@ -125,6 +128,17 @@ main(int argc, char *argv[]) /* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */ pset.popt.topt.format = PRINT_ALIGNED; + + /* Default table style to plain ASCII */ + pset.popt.topt.table_style = asciiformat; +#if (defined(HAVE_LANGINFO_H) defined(CODESET)) + /* If a UTF-8 locale is available, switch to UTF-8 box drawing characters */ + if (pg_strcasecmp(nl_langinfo(CODESET), UTF-8) == 0 || + pg_strcasecmp(nl_langinfo(CODESET), utf8) == 0 || + pg_strcasecmp(nl_langinfo(CODESET), CP65001) == 0) + pset.popt.topt.table_style = utf8format; +#endif + pset.popt.topt.border = 1; pset.popt.topt.pager = 1; pset.popt.topt.start_table = true; Elsewhere in the psql code, notably in mbprint.c, we make the decision on whether to apply certain Unicode-aware processing based on whether the client encoding is UTF8. The same should be done here. There is a patch somewhere in the pipeline that would automatically set the psql client encoding to whatever the locale says, but until that is done, the client encoding should be the sole setting that rules what kind of character set processing is done on the client side. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
David, The radical proposal was the RULE system. It's been tested now, and it's pretty much failed. I don't think you've demonstrated that. I know *you* don't like RULEs, but others do. I could propose that UUIDs are a bankrupt concept (which I believe) and therefore we should drop the UUID contrib module, but I don't think I'd get very far. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
On Sun, Oct 04, 2009 at 01:25:31PM -0700, Josh Berkus wrote: David, The radical proposal was the RULE system. It's been tested now, and it's pretty much failed. I don't think you've demonstrated that. I know *you* don't like RULEs, but others do. It's less about like or dislike and more about facing up to the reality that we've got a major legacy foot-gun left over from the experimentation of the Berkeley days. You'll recall we removed time travel for much less good reasons, namely performance, as opposed to actually breaking stuff. What people actually use RULEs for successfully, I've named. I'm proposing we cover those cases, deprecate (not depreciate ;) RULEs in the cycle or two following that coverage, and remove them after that. I could propose that UUIDs are a bankrupt concept (which I believe) and therefore we should drop the UUID contrib module, but I don't think I'd get very far. UUIDs are much harder to shoot yourself with. :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY enhancements
Hi! On Fri, Sep 25, 2009 at 7:01 AM, Emmanuel Cecchet m...@asterdata.com wrote: Here is the new version of the patch that applies to CVS HEAD as of this morning. Cool features! This is my first pass at the error logging portion of this patch. I'm going to take a break and try to go through the partitioning logic as well later this afternoon. caveat: I'm not familiar with most of the code paths that are being touched by this patch. Overall: * I noticed '\see' included in the comments in your code. These should be removed. * The regression is failling, as Jeff indicated, and I didn't figure out why yet either. Hopefully will have a look closer this afternoon. Comments: * copy.c: Better formatting, maybe rewording needed for comment starting on line 1990. ** Maybe say: Check that errorData-sqlerrcode only logged tuples that are malformed. This ensures that we let other errors pass through. * copy.c: line: 2668 - need to fix that comment :) (/* Regular code */) -- this needs to be more descriptive of what is actually happening. We fell through the partitioning logic, right, and back to the default behavior? * copy.c: line 2881, maybe say instead: Mark that we have not read a line yet. This is necessary so that we can perform error logging on complete lines only. Formatting: * copy.c: whitespace is maybe a little off at line: 2004-2009 * NEW FILES: src/backend/utils/misc/errorlogging.c errorlogging.h need headers Code: * copy.c: line 1990 - cur_lineno_str[11] related: why is this conversion necessary? (sorry if that is a dumb question) * copy.c: line 2660 - what if we are error logging for copy? Does this get logged properly? * errorlogging.c: Noticed the comment at 503 - 'note that we always enable wal logging'.. Thinking this through - the reasoning is that you won't be rolling back the error logging no matter what, right? * src/include/commands/copy.c and copy.h: struct CopyStateData was moved from copy.c to copy.h; this made sense to me, just noting. That move made it a little tricky to find the changes that were made. There were 10 items added. ** super nit pick: 'readlineOk' uses camel-case instead of underscores like the rest of the new variables * errorlogging.c: could move currentCommand pg_stat call in Init routine into MalformedTupleIs, or maybe move the setting of that parameter into the Init routine for consistency's sake. Documentation: * doc/src/sgml/ref/copy.sgml: line 313: 'failif' needs a space ** Also: The error table log examples have relations shown in a different order than the actual CREATE TABLE declaration in the code. * src/test/regress/sql/copyselect.sql: Format of options changed.. added parenthesis. Is this change documented elsewhere? (sorry if I just missed this in the rest of the thread/patch) -- http://chesnok.com/daily - me http://endpoint.com - work -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Buffer usage in EXPLAIN and pg_stat_statements (review)
On Wed, Sep 30, 2009 at 10:40 PM, Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp wrote: Euler Taveira de Oliveira eu...@timbira.com wrote: But there are some confusions in postgres; ReadBufferCount and BufferHitCount are used for get and hit, but heap_blks_read and heap_blks_hit are used for read and hit in pg_statio_all_tables. I see. :( I fixed the confusions of get, hit and read in your patch. long num_hit = ReadBufferCount + ReadLocalBufferCount; long num_read = num_hit - BufferHitCount - LocalBufferHitCount; should be long num_get = ReadBufferCount + ReadLocalBufferCount; long num_hit = BufferHitCount + LocalBufferHitCount; long num_read = num_get - num_hit; ReadBufferCount means number of buffer access :( Patch attached. I took a look at this today and I have a couple of comments. The basic functionality looks useful, but I think the terminology is too terse. Specific commens: 1. In the EXPLAIN output, I think that the buffers information should be output on its own line, rather than appended to the line that already contains costs and execution times. The current output doesn't include the word buffers or blocks anywhere, which seems to me to be a critical flaw. I would suggest something like Blocks Read: %ld Hit: %ld Temp Read: %ld\n. See the way we handle output of sort type and space usage, for example. 2. Similarly, in pg_stat_statements, the Counters structure could easily use the same names for the structure members that we already use in e.g. pg_stat_database - blks_hit, blks_read, and, say, blks_temp_read. In fact I tend to think we should stick with blocks rather than buffers overall, for consistency with what the system does elsewhere. 3. With respect to the doc changes in explain.sgml, we consistently use disk rather than disc in the documentation; but it may not be necessary to use that word at all, and I think the paragraph can be tightened up a bit: Include information on the number of blocks read, the number of those that are hits (already in shared buffers and do not need to be read in), and the number of those that are reads on temporary, backend-local buffers. This parameter requires that the literalANALYZE/literal parameter also be used. This parameter defaults to literalFALSE/literal. 4. Instrumentation stack is broken doesn't seem terribly helpful in understanding what has gone wrong. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
On Sun, Oct 04, 2009 at 04:07:40PM -0400, Robert Haas wrote: On Sun, Oct 4, 2009 at 3:34 PM, David Fetter da...@fetter.org wrote: What would be the benefit of this radical proposal? The radical proposal was the RULE system. It's been tested now, and it's pretty much failed. You still haven't explained what actual benefit we'd get out of doing this. Removing land mines is a benefit. I agree that rules, except for SELECT rules, don't seem to be very useful. Perhaps others have found them so, but I have found triggers to be a better fit for everything that I ever want to do. Every time I think, hmm, maybe I could use a rule for that, I reread the chapter and change my mind. It's people who either don't read the chapter or don't change their mind who get in all that trouble. Actually using RULEs is just cruisin' for a bruisin'. However, there is a very real possibility that there are people out there who have applications that are based on the way rules work today. If we were to remove support for rules, they would not be able to upgrade past 8.4. That seems to me to be the sort of thing that we wouldn't want to do unless we had a good reason - and the closest you've come to saying what you think that reason might be is they're mostly a foot-gun, which I don't find very compelling. In another post, I proposed a deprecation and removal strategy. If someone has a use case I haven't named, they've yet to chime in. Of course, it's a little early yet, but I've seen a *lot* of PostgreSQL deployments, and none of them had RULEs for anything but the cases I mentioned. I think we want to be moving in the direction of making upgrading easier, not more difficult, and that means maintaining backward compatibility even for features that are of marginal utility, unless they're getting in the way of something else. Well, there's a utilitarian argument for not having land mines in our code. To call what RULEs can do to your assumptions about how things work (data integrity, etc.) in PostgreSQL, astonishing would be an understatement. As for the upgrades, you've made an interesting point. I suspect that for the cases mentioned, there could be a mechanical way to do what needs doing. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
* David Fetter (da...@fetter.org) wrote: On Sun, Oct 04, 2009 at 04:07:40PM -0400, Robert Haas wrote: The radical proposal was the RULE system. It's been tested now, and it's pretty much failed. You still haven't explained what actual benefit we'd get out of doing this. Removing land mines is a benefit. Removing useful functionality without replacing it is definitely worse. Do we have a patch which implements the necessary mechanics to replace RULEs, even for the specific situations you list? Until then, I don't think there's much to discuss. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Rules: A Modest Proposal
On Sun, October 4, 2009 1:48 pm, Pavel Stehule wrote: 2009/10/4 David Fetter da...@fetter.org: Folks, At the moment, user-accessible RULEs have, as far as I know, just two sane uses: * Writing to VIEWs * Routing writes to partitions somebody use it as instead triggers. And I am sure, so there are people, who use it for writable views. We have such a rule (instead of a trigger) in our SaaS app. I'm lobbying to remove it, and make it a real trigger, but that hasn't happened yet. so there are folks out there. regards Pavel Stehule And the second is pretty thin, given the performance issues for numbers of partitions over 2. What say we see about addressing those problems separately, and removing user-accessible RULEs entirely? There are already patches to deal with the first, at least for the kinds of VIEWs where this can be deduced automatically, and people are starting to take on the second. The one remaining (as in nobody's really addressed it with code) issue would be triggers on VIEWs.  As other systems have done it, it's clearly not essentially impossible.  What would be needed? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter Skype: davidfetter    XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: l...@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Triggers on columns
Peter Eisentraut pete...@gmx.net wrote: OK, but what you can do is point both variants to the same C function and check with PG_NARGS() with how many arguments you were called. That would save some of the indirections. The regressiontest 'opr_sanity' failed if do so. Should we remove this check only for pg_get_triggerdef? If we cannot do that, the first version of patch is still the best solution. -- Considering only built-in procs (prolang = 12), look for multiple uses -- of the same internal function (ie, matching prosrc fields). It's OK to -- have several entries with different pronames for the same internal function, -- but conflicts in the number of arguments and other critical items should -- be complained of. (We don't check data types here; see next query.) -- Note: ignore aggregate functions here, since they all point to the same -- dummy built-in function. oid | proname | oid | proname ! --+---+--+--- ! 1662 | pg_get_triggerdef | 2730 | pg_get_triggerdef ! (1 row) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
On Oct 4, 2009, at 1:57 PM, David Fetter wrote: It's less about like or dislike and more about facing up to the reality that we've got a major legacy foot-gun left over from the experimentation of the Berkeley days. I think you're going to need to be a bit more concrete than that. In what way is it a foot-gun? What examples can you provide? What, exactly, are the issues? Perhaps, given concrete examples of issues with RULEs, we could look at addressing those problems rather than throwing out the baby (let alone put the baby in concrete -- sorry, the metaphors are getting away from me). Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
On Sun, Oct 4, 2009 at 6:42 PM, David Fetter da...@fetter.org wrote: I agree that rules, except for SELECT rules, don't seem to be very useful. Perhaps others have found them so, but I have found triggers to be a better fit for everything that I ever want to do. Every time I think, hmm, maybe I could use a rule for that, I reread the chapter and change my mind. It's people who either don't read the chapter or don't change their mind who get in all that trouble. Actually using RULEs is just cruisin' for a bruisin'. Well, it's not our custom to tailor our feature set to people who aren't willing or able to read the instructions. If we're going to start removing all the features that will bite you in the posterior in such cases, can we start with NOT IN and the application of IS NULL/IS NOT NULL to records? Because I'd bet good money those bite VASTLY more people than anything involving rules. As for the upgrades, you've made an interesting point. I suspect that for the cases mentioned, there could be a mechanical way to do what needs doing. Only if the new system is pretty darn similar to how the existing system works. But at this point this is all hand-waving, as we have no design for anything that could replace what we have now even for the use cases you think are important (which I'm also unconvinced cover what everyone else thinks are important, but that's a separate issue). ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Making hash indexes worthwhile
I see that the docs were recently changed from discouraging hash indexes both because they were no known uses in which they were meaningfully better than btree, and because they aren't recoverable; to now just discouraging them because they are not recoverable. Does that mean there are now uses in which hash indexes are provably better than btree if one is willing to overlook the lack of recoverability? If so, what are those situations? I've played around a bit with hash indexes, and it seems to me that making them generally worthwhile will take (at least) reducing or entirely doing away with the heavy-wait locks. I have a few ideas, which are not necessarily independent of each other. ===metablock=== If each bucket knows how many bits of the hash-value are significant for the entries in that bucket, then there will be a way to detect races from the metablock to the main bucket block. Rather than chaining locks from the metablock to the bucket, the process can compute the bucket and remember the number of significant bits in that bucket according to the metablock, then release the metablock lock before attempting to acquire the the buck lock. When the bucket lock is obtained, if the number of bits is greater than the number remembered from the metablock, then it can drop the bucket lock and try again. Once you don't need to chain locks from the metablock to the bucket, the lock on the metablock can probably be lowered to just a buffer content lock rather than a heavy lock. Even better, maybe the metablock can be cached in the relcache. Since the changes to the metablock seem to be entirely unidirectional (other than things like index dropping and rebuilding, which I think maybe the normal relcache flushing mechanisms will take care of), it should always be possible to detect when you have a too-stale copy and have to go get the current one. ===get rid of heavy locks=== While there are several potential places for deadlock, it seems like the main one is that the hash index scan code returns control to the executor while holding locks on the hash buckets/pages, so you can get hybrid deadlocks where one side of the blockage is in hash-code space and the other in executor-space. The obvious way to fix this would be to read the entire bucket worth of qualifying tids into local memory and release the bucket lock before returning control to the executor, kind of like btree does with pages. the main problem here is that a logical bucket can have an unbounded number of overflow pages. So if a bucket has too many tids with the sought-for full hash value (more than will fit in work_mem), we would have to prepared to spill to disk. Is that an acceptable trade-off? Obviously there are a lot of subtleties to work out with insertions and bucket-splits, but if having read-only scans occasionally need to spill to disk is a no-go then there is no point in trying to work the other things out. Thanks for any feedback, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
David E. Wheeler wrote: On Oct 4, 2009, at 1:57 PM, David Fetter wrote: It's less about like or dislike and more about facing up to the reality that we've got a major legacy foot-gun left over from the experimentation of the Berkeley days. I think you're going to need to be a bit more concrete than that. In what way is it a foot-gun? What examples can you provide? What, exactly, are the issues? While I don't agree with David Fetter's premise, I think rehashing how we handle VIEWs would be a good step towards updatable views. Right now, the implementation of that is stalled precisely because of the rule system. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
On Sun, Oct 04, 2009 at 08:54:56PM -0400, Alvaro Herrera wrote: David E. Wheeler wrote: On Oct 4, 2009, at 1:57 PM, David Fetter wrote: It's less about like or dislike and more about facing up to the reality that we've got a major legacy foot-gun left over from the experimentation of the Berkeley days. I think you're going to need to be a bit more concrete than that. In what way is it a foot-gun? What examples can you provide? What, exactly, are the issues? While I don't agree with David Fetter's premise, I think rehashing how we handle VIEWs would be a good step towards updatable views. Right now, the implementation of that is stalled precisely because of the rule system. I am not sure where that view implemenation is, but I doubt its stalled because of the rule system. You can definitely create updatable views using rules. However, I'm not sure updatable views are a good thing in most scenarios. I see way too much damage as a likely outcome. Rules are one of the great generative features of postgres and I see no reason to cut them. Features should not be limited just because they can be used incorrectly, since they can also be used in other correct/interesting ways we have yet to think up. -- --Dan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE LIKE INCLUDING COMMENTS and STORAGES
Khee Chin kheec...@gmail.com wrote: Recently, I encountered a situation where the docs on (or impl?) INCLUDING INDEXES and INCLUDING CONSTRAINTS are not clearly defined for primary keys. Should it be noted in the docs that in this case, we are referring to the technical implementation of a primary key, i.e. a unique index and a not null constraint, thus both conditions are required? It might be a confusable feature, but it should be discussed separated from this patch. IMO, almost all user will use INCLUDING ALL if the syntax is added by the patch. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
On Sun, Oct 4, 2009 at 8:54 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: David E. Wheeler wrote: On Oct 4, 2009, at 1:57 PM, David Fetter wrote: It's less about like or dislike and more about facing up to the reality that we've got a major legacy foot-gun left over from the experimentation of the Berkeley days. I think you're going to need to be a bit more concrete than that. In what way is it a foot-gun? What examples can you provide? What, exactly, are the issues? While I don't agree with David Fetter's premise, I think rehashing how we handle VIEWs would be a good step towards updatable views. Right now, the implementation of that is stalled precisely because of the rule system. This is the last I remember hearing of it, which seems to suggest that only a week's worth of work (maybe a bit more for those of us who are not Tom Lane) is needed: http://archives.postgresql.org/pgsql-hackers/2009-01/msg01746.php But maybe you have some other thoughts? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Privileges and inheritance
Peter Eisentraut pete...@gmx.net writes: On Sun, 2009-10-04 at 11:56 -0700, Josh Berkus wrote: And we'd want to hammer this to death looking for ways it can be a security exploit. Like, could you make a table into the parent of an existing table you didn't have permissions on? I don't think so, but you're free to hammer. ;-) I believe you have to be owner of both tables to do an ALTER INHERIT. So you would have the right to make the child more accessible than it had been. Whether you realized you were doing that might be a bit debatable ... but I don't seriously think this is a problem. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
Robert Haas escribió: While I don't agree with David Fetter's premise, I think rehashing how we handle VIEWs would be a good step towards updatable views. Right now, the implementation of that is stalled precisely because of the rule system. This is the last I remember hearing of it, which seems to suggest that only a week's worth of work (maybe a bit more for those of us who are not Tom Lane) is needed: Right, that's exactly what I meant. Note that a week's worth of Tom work in that area is probably measured in months for anybody else (a bit more in your words), and this fits my definition of rehashing view handling. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Triggers on columns
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes:
Peter Eisentraut pete...@gmx.net wrote:
OK, but what you can do is point both variants to the same C function
and check with PG_NARGS() with how many arguments you were called. That
would save some of the indirections.
The regressiontest 'opr_sanity' failed if do so. Should we remove this
check only for pg_get_triggerdef? If we cannot do that, the first version
of patch is still the best solution.
I have always been of the opinion that V1 functions should be written
in the style
foo(PG_FUNCTION_ARGS)
{
type1 arg1 = PG_GETARG_whatever(0);
type2 arg2 = PG_GETARG_whatever(1);
type3 arg3 = PG_GETARG_whatever(2);
as much as possible. The V1 protocol is already a big hit to
readability compared to plain-vanilla C functions, and one of the main
reasons is that you can't instantly see what arguments a function is
expecting. Sticking to the above style ameliorates that. Cute tricks
like conditionally grabbing arguments depending on PG_NARGS do far more
damage to readability than they can ever repay in any other metric.
In short: while I haven't looked at the patch, I think Peter may be
steering you in the wrong direction.
In cases where you do have related functions, I suggest having
SQL-callable V1 functions that absorb their arguments in this
style, and then have them call a common subroutine that's a plain
C function.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY enhancements
The problem comes from the foo_malformed_terminator.data file. It is supposed to have a malformed terminator that was not catch by patch. The second line should look like: 2 two^M If it does not, you can edit it with emacs, go at the end of the second line and press Ctrl+q followed by Ctrl+m and that will do the trick. I am attaching a copy of the file, hoping that the attachment will arrive with the malformed terminator in your inbox! manu Jeff Davis wrote: On Fri, 2009-09-25 at 10:01 -0400, Emmanuel Cecchet wrote: Robert, Here is the new version of the patch that applies to CVS HEAD as of this morning. I just started looking at this now. It seems to fail make check, diffs attached. I haven't looked into the cause of the failure yet. Regards, Jeff Davis -- Emmanuel Cecchet Aster Data Systems Web: http://www.asterdata.com 1 one 2 two 3 three 4 four 5 five -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Making hash indexes worthwhile
Jeff Janes jeff.ja...@gmail.com writes: I see that the docs were recently changed from discouraging hash indexes both because they were no known uses in which they were meaningfully better than btree, and because they aren't recoverable; to now just discouraging them because they are not recoverable. Does that mean there are now uses in which hash indexes are provably better than btree if one is willing to overlook the lack of recoverability? If so, what are those situations? One reason is that you can index values that are too big for btrees; since hash indexes now store only the hash codes, they don't have a hard length limit on the underlying value. I'm not sure how useful that really is in practice, but it's at least an argument for considering them in certain cases. I've played around a bit with hash indexes, and it seems to me that making them generally worthwhile will take (at least) reducing or entirely doing away with the heavy-wait locks. Concurrency is really the least of the issues for hash indexes. So far it's not clear that they're fast enough even in sequential use ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.5 TODO: any info on Create dump tool for write-ahead logs... in PITR section (1.4)?
Hi, On Sat, Oct 3, 2009 at 7:30 AM, shakahsha...@gmail.com shakahsha...@gmail.com wrote: Can anyone elaborate (or point me to some additional info) on the 8.5 TODO item in the Point-In-Time Recover (PITR) section (1.4): Create dump tool for write-ahead logs for use in determining transaction id for point-in-time recovery This is useful for checking PITR recovery. I poked around a bit and found some code that walks the WAL logs (in src/backend/access/transam/xlog.c, I think) and I could probably figure out how to display a WAL log file contents, but I'm hoping someone can provide some context as to what issue this TODO item is trying to address (i.e., what output would be useful). I think that xlogdump (http://xlogviewer.projects.postgresql.org/) is the first step to address that TODO item. Though I'm not sure if the xlogdump project is still active. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE LIKE INCLUDING COMMENTS and STORAGES
Andrew Dunstan and...@dunslane.net wrote: I'm wondering why we are not copying comments on cloned indexes. I realize that might involve a bit more code, but I think I'd feel happier if we cloned all the comments we reasonably could from the outset. Is it really that hard to do? I found it is not so difficult as I expected; patch attached. Now it copies comments on indexes and columns of the indexes on INCLUDING COMMENTS. Regression test and documentation are also adjusted. Please review around chooseIndexName() and uses of it. The codes becomes a bit complex and might be ugly because we will have some duplicated codes; pg_expression_%d hacks and uses of ChooseRelationName() are spread into index.c, indexcmds.c and parse_utilcmd.c. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center create-including_20091005.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] dblink memory leak
Joe Conway m...@joeconway.com wrote: The point is *memory leak* in dblink when a query is canceled or become time-out. I think it is a bug, and my patch could fix it. Please see if this works for you. It does not work because errors can occur in caller of dblink functions; Error callback should be still registered after SRF_RETURN_NEXT, so we cannot place callback context on stack of the function. More works needed. RegisterExprContextCallback() might be good for this purpose, but we need to modify callbacks are fired even if error. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rules: A Modest Proposal
On Sun, Oct 4, 2009 at 10:01 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Robert Haas escribió: While I don't agree with David Fetter's premise, I think rehashing how we handle VIEWs would be a good step towards updatable views. Right now, the implementation of that is stalled precisely because of the rule system. This is the last I remember hearing of it, which seems to suggest that only a week's worth of work (maybe a bit more for those of us who are not Tom Lane) is needed: Right, that's exactly what I meant. Note that a week's worth of Tom work in that area is probably measured in months for anybody else (a bit more in your words), :-) and this fits my definition of rehashing view handling. The trick is to get rid of the self-join, I suppose, but it's unclear to me whether some change to the existing view handling would make that easier. Do you have an idea? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dblink memory leak
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: Joe Conway m...@joeconway.com wrote: Please see if this works for you. It does not work because errors can occur in caller of dblink functions; Error callback should be still registered after SRF_RETURN_NEXT, so we cannot place callback context on stack of the function. More works needed. Yeah, I meant to comment on that: it's an abuse of the error context mechanism and will never be safe. (An example is that if someone innocently did an elog(LOG) or something like that, the callback would get triggered.) The global PGresult value seems awfully dangerous too. I think what you want to do instead is use PG_TRY blocks to ensure that transient results are cleaned up. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dblink memory leak
Tom Lane t...@sss.pgh.pa.us wrote:
I think what you want to do instead is use PG_TRY blocks to ensure that
transient results are cleaned up.
I think PG_TRY blocks are not enough, too. PG_TRY requires a statement
block, but we need to return from dblink functions per tuple.
Error and interruption can occur at the caller:
ExecMakeTableFunctionResult()
{
for (;;)
{
*here* CHECK_FOR_INTERRUPTS();
result = FunctionCallInvoke(fcinfo); = { PG_TRY ... END }
if (rsinfo.isDone == ExprEndResult)
break;
tuplestore_puttuple(tupstore, tmptup);
}
}
Also, we should think SRF-functions might not be called repeatedly
until max_calls whether the transaction is committed or rollbacked
because we might have some optimization in FunctionScan in the future.
For example:
SELECT * FROM dblink() LIMIT 3
might call dblink() only 3 times if we optimize executor logic
(it should not occur for now, though).
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Privileges and inheritance
Peter Eisentraut wrote: I would like to propose a change in the way privilege checking is done with inheritance hierarchies. Currently, selecting from a table that has children requires explicit privileges on all the children. This is inconsistent with all other commands, which treat children as implicitly part of the parent table. (Arguably, it exposes an implementation detail, since you could just as well implement inheritance by keeping all the children's data for the inherited columns in the parent's heap.) As inheritance has now found new popularity as a partitioning mechanism, this exacerbates the annoyance because you have to copy the privilege sets to possibly dozens or hundreds of subtables in cumbersome ways for really no good reason. I think it is a matter of perspectives. (So, we will not have a perfectly correct answer.) If we consider that inherited columns are a part of parent table, it is odd to apply checks on both of parent and child tables when we select data from a table with its children, as you mentioned. On the other hand, it also needs to check permission both of child table and its parents when we select data from a table with its parents, because the selected columns are inherited from other tables. The current implementation handles the parent/children as an individual relations. It does not consider the inherited columns are a part of parent tables. For example, ALTER TABLE ADD COLUMN statement checks ownership on the target table and all the children to add a new column. It is equivalent to the iteration of ALTER TABLE on the children. If you use inheritance for data modeling (the original purpose), you face another problem. Either you grant table privileges on all the child tables, thus giving users access to more information than they were supposed to have, or you grant column privileges on only those columns that were inherited, being careful to keep that set updated whenever table definitions are altered. (Before 8.4 you couldn't even do that.) It's messy. I think we should check permission on the parent tables/columns when a user tries to select inherited columns on the child table. It stands on the perspective that the inherited columns are owned by the parent (source) table. The matter is a case when we cannot identify where is the source of the inherited column if the child table has multiple parents. An idea is to check permissions on all the parents in this case. We could use a GUC variable to ease the transition, perhaps like sql_inheritance = no | yes_without_privileges | yes I think the GUC should toggle which table owns the inherited columns. If DBA considers the inherited columns are a part of the parent table, individual checks can be bypassed. Otherwise, we can keep the compatible bahavior. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dblink memory leak
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: I think PG_TRY blocks are not enough, too. PG_TRY requires a statement block, but we need to return from dblink functions per tuple. That bit will have to be undone. There is no reason for dblink not to return a tuplestore. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Buffer usage in EXPLAIN and pg_stat_statements (review)
Robert Haas robertmh...@gmail.com wrote: 1. I would suggest something like Blocks Read: %ld Hit: %ld Temp Read: %ld\n. See the way we handle output of sort type and space usage, for example. I have some questions: * Did you use single space and double spaces in your example intentionally? * Should we use lower cases here? * Can I use temp instead of Temp Read to shorten the name? 2. Similarly, in pg_stat_statements, the Counters structure could easily use the same names for the structure members that we already use in e.g. pg_stat_database - blks_hit, blks_read, and, say, blks_temp_read. In fact I tend to think we should stick with blocks rather than buffers overall, for consistency with what the system does elsewhere. I agree to rename them into blks_*, but EXPLAIN (blocks) might be misleading; EXPLAIN (buffer) can be interpreted as buffer usage, but normally we don't call it block usage. My suggestion is: * EXPLAIN (buffers) prints (blocks read: %ld hit: %ld temp: %ld) * auto_explain.log_buffers are not changed * pg_stat_statements uses blks_hit and blks_read 4. Instrumentation stack is broken doesn't seem terribly helpful in understanding what has gone wrong. This message is only for hackers and should not occur. Assert() might be ok instead. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Buffer usage in EXPLAIN and pg_stat_statements (review)
On Sun, Oct 4, 2009 at 11:22 PM, Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp wrote: Robert Haas robertmh...@gmail.com wrote: 1. I would suggest something like Blocks Read: %ld Hit: %ld Temp Read: %ld\n. See the way we handle output of sort type and space usage, for example. I have some questions: * Did you use single space and double spaces in your example intentionally? No, that was unintentional. * Should we use lower cases here? No. We don't anywhere else in explain.c. * Can I use temp instead of Temp Read to shorten the name? I can't tell what that means without reading the source code. I think clarity should take precedence over brevity. 2. Similarly, in pg_stat_statements, the Counters structure could easily use the same names for the structure members that we already use in e.g. pg_stat_database - blks_hit, blks_read, and, say, blks_temp_read. In fact I tend to think we should stick with blocks rather than buffers overall, for consistency with what the system does elsewhere. I agree to rename them into blks_*, but EXPLAIN (blocks) might be misleading; EXPLAIN (buffer) can be interpreted as buffer usage, but normally we don't call it block usage. My suggestion is: * EXPLAIN (buffers) prints (blocks read: %ld hit: %ld temp: %ld) * auto_explain.log_buffers are not changed * pg_stat_statements uses blks_hit and blks_read I agree. 4. Instrumentation stack is broken doesn't seem terribly helpful in understanding what has gone wrong. This message is only for hackers and should not occur. Assert() might be ok instead. Hmm, I think I like the idea of an Assert(). Logging a cryptic message at DEBUG2 doesn't seem sufficient for a can't-happen condition that probably indicates a serious bug in the code. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Buffer usage in EXPLAIN and pg_stat_statements (review)
Here is an update version of buffer usage patch. * All buffers_* and bufs_* are renamed to blks_*. * 'disc' = 'disk' in documentation * Replace debug-log to Assert(). * Fix a bug in ResetLocalBufferUsage(). log_xxx_stats had not worked. Robert Haas robertmh...@gmail.com wrote: ?* Can I use temp instead of Temp Read to shorten the name? I can't tell what that means without reading the source code. I think clarity should take precedence over brevity. I used temp_blks_read because we have idx_blks_read in pg_statio_xxx. =# \d pg_stat_statements View public.pg_stat_statements Column | Type | Modifiers +--+--- userid | oid | dbid | oid | query | text | calls | bigint | total_time | double precision | rows | bigint | blks_hit | bigint | blks_read | bigint | temp_blks_read | bigint | =# SET work_mem = '1MB'; =# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pgbench_accounts ORDER BY bid; QUERY PLAN --- Sort (cost=21913.32..22163.33 rows=15 width=97) (actual time=81.345..99.054 rows=10 loops=1) Sort Key: bid Sort Method: external sort Disk: 10472kB Blocks Hit: 0 Read: 0 Temp Read: 1309 - Seq Scan on pgbench_accounts (cost=0.00..2667.05 rows=15 width=97) (actual time=0.018..23.129 rows=10 loops=1) Blocks Hit: 74 Read: 1694 Temp Read: 0 Total runtime: 105.238 ms (7 rows) =# SET work_mem = '18MB'; =# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pgbench_accounts ORDER BY bid; QUERY PLAN --- Sort (cost=10972.32..11222.33 rows=15 width=97) (actual time=35.437..43.069 rows=10 loops=1) Sort Key: bid Sort Method: quicksort Memory: 17916kB Blocks Hit: 0 Read: 0 Temp Read: 0 - Seq Scan on pgbench_accounts (cost=0.00..2667.05 rows=15 width=97) (actual time=0.028..15.030 rows=10 loops=1) Blocks Hit: 32 Read: 1635 Temp Read: 0 Total runtime: 52.026 ms (7 rows) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center buffer_usage_20091005.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
