[HACKERS] patch: fix a regress tests
Hello there is fix a locale dependency of regress tests Last time I forgot to attach a patch Regards Pavel Stehule *** ./src/test/regress/expected/foreign_data.out.orig 2011-10-04 13:56:41.0 +0200 --- ./src/test/regress/expected/foreign_data.out 2011-10-06 14:06:20.0 +0200 *** *** 631,637 -- CREATE FOREIGN TABLE CREATE SCHEMA foreign_schema; ! CREATE SERVER sc FOREIGN DATA WRAPPER dummy; CREATE FOREIGN TABLE ft1 ();-- ERROR ERROR: syntax error at or near ; LINE 1: CREATE FOREIGN TABLE ft1 (); --- 631,637 -- CREATE FOREIGN TABLE CREATE SCHEMA foreign_schema; ! CREATE SERVER s0 FOREIGN DATA WRAPPER dummy; CREATE FOREIGN TABLE ft1 ();-- ERROR ERROR: syntax error at or near ; LINE 1: CREATE FOREIGN TABLE ft1 (); *** *** 641,655 CREATE FOREIGN TABLE ft1 (c1 serial) SERVER sc; -- ERROR NOTICE: CREATE FOREIGN TABLE will create implicit sequence ft1_c1_seq for serial column ft1.c1 ERROR: default values on foreign tables are not supported ! CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS;-- ERROR ERROR: syntax error at or near WITH OIDS ! LINE 1: CREATE FOREIGN TABLE ft1 () SERVER sc WITH OIDS; ^ CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS (param 1 'val1') NOT NULL, c2 text OPTIONS (param2 'val2', param3 'val3'), c3 date ! ) SERVER sc OPTIONS (delimiter ',', quote '', be quoted 'value'); COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; \d+ ft1 --- 641,655 CREATE FOREIGN TABLE ft1 (c1 serial) SERVER sc; -- ERROR NOTICE: CREATE FOREIGN TABLE will create implicit sequence ft1_c1_seq for serial column ft1.c1 ERROR: default values on foreign tables are not supported ! CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS;-- ERROR ERROR: syntax error at or near WITH OIDS ! LINE 1: CREATE FOREIGN TABLE ft1 () SERVER s0 WITH OIDS; ^ CREATE FOREIGN TABLE ft1 ( c1 integer OPTIONS (param 1 'val1') NOT NULL, c2 text OPTIONS (param2 'val2', param3 'val3'), c3 date ! ) SERVER s0 OPTIONS (delimiter ',', quote '', be quoted 'value'); COMMENT ON FOREIGN TABLE ft1 IS 'ft1'; COMMENT ON COLUMN ft1.c1 IS 'ft1.c1'; \d+ ft1 *** *** 659,665 c1 | integer | not null | (param 1 'val1') | plain| ft1.c1 c2 | text| | (param2 'val2', param3 'val3') | extended | c3 | date| || plain| ! Server: sc FDW Options: (delimiter ',', quote '', be quoted 'value') Has OIDs: no --- 659,665 c1 | integer | not null | (param 1 'val1') | plain| ft1.c1 c2 | text| | (param2 'val2', param3 'val3') | extended | c3 | date| || plain| ! Server: s0 FDW Options: (delimiter ',', quote '', be quoted 'value') Has OIDs: no *** *** 667,673 List of foreign tables Schema | Table | Server | FDW Options | Description +---++-+- ! public | ft1 | sc | (delimiter ',', quote '', be quoted 'value') | ft1 (1 row) CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR --- 667,673 List of foreign tables Schema | Table | Server | FDW Options | Description +---++-+- ! public | ft1 | s0 | (delimiter ',', quote '', be quoted 'value') | ft1 (1 row) CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR *** *** 717,723 c8 | text| | (p2 'V2') | extended | c9 | integer | || plain| c10| integer | | (p1 'v1') | plain| ! Server: sc FDW Options: (delimiter ',', quote '', be quoted 'value') Has OIDs: no --- 717,723 c8 | text| | (p2 'V2') | extended | c9 | integer | || plain| c10| integer | | (p1 'v1') | plain| ! Server: s0 FDW Options: (delimiter ',', quote '', be quoted 'value') Has OIDs: no *** *** 760,766 c7 | integer | | (p1 'v1', p2 'v2') c8 | text| | (p2 'V2') c10 | integer | | (p1 'v1') ! Server: sc FDW Options: (quote '~', be quoted 'value', escape '@')
[HACKERS] Extend file_fdw wrapper
Attached patch. pasman 0001-Extend-file_fdw-wrapper.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
Re: [HACKERS] Extend file_fdw wrapper
2011/10/10 pasman pasmański pasma...@gmail.com: Attached patch. ... and what are these new options intended to do? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] What is known about PostgreSQL HP-UX support?
On Oct 10, 2011, at 4:21 AM, Alex Goncharov wrote: [ Thanks all for the very productive discussion in the thread libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable which I originated. Very useful. Now on something different. ] About two years ago, I had to research some PostgreSQL failures on HP-UX on a lame PA-RISC box. Looking at the PostgreSQL source code then, I got an impression that running PostgreSQL on HP-UX was an open question -- HP-UX didn't seem like a seriously targeted platform. Was I wrong in my assessment? Does anybody have a good experience running PostgreSQL on HP-UX? What version of both? PA-RISC? IA64? Thanks, hello, HPUX is not too common these days but it works like a charm. we have a couple of large IA64 servers running on HPUX at a major customer. things work without any problems. it compiled out of the box just like expected and we have not seen any failures or so for almost 2 years now. so, thumbs up ... nothing to be afraid of. many thanks, hans -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de -- 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] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
On sön, 2011-10-09 at 11:51 -0400, Tom Lane wrote: The problem with something like a protocol bump is that the coding required to make it happen (in the backend and libpq, that is) is only a small part of the total distributed cost. Why do we have major and minor protocol version numbers, which are supposed to allow incremental addition of features to the protocol? What other costs do you have in mind? -- 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] WIP: Collecting statistics on CSV file data
(2011/10/07 21:56), David Fetter wrote: (But this is BTW. I'm interested in developing CREATE FOREIGN INDEX. I've examined whether there are discussions about the design and implementation of it in the archive, but could not find information. If you know anything, please tell me.) Look into the virtual index interface from Informix. Thank you for the information. We might want to start a wiki page on this. Yeah, I think it might be better to add information to the SQL/MED wiki page: http://wiki.postgresql.org/wiki/SQL/MED Best regards, Etsuro Fujita -- 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] What is known about PostgreSQL HP-UX support?
On 10.10.2011 05:39, Alex Goncharov wrote: 1. The PostgreSQL server will build on HPUX 11.31 IA64. Yes. 2. The server will run all right (speaking on the test cases you personally covered only) on HPUX 11.31 IA64. Yes, works fine. 3. Both PostgreSQL v. 8.4 and 9.1. Not sure about 8.4 (or 9.0). A bunch of HP-UX related fixes were committed around April-May, to fix issues I bumped into when setting up the HP-UX ia64 buildfarm member (anole). I don't think all of them were back-patched. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] What is known about PostgreSQL HP-UX support?
Thank you all who replied! -- Alex -- alex-goncha...@comcast.net -- /* * They're only trying to make me LOOK paranoid! */ -- 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] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
Florian Pflug wrote: On Oct9, 2011, at 14:20 , Kevin Grittner wrote: Florian Pflug wrote: Coming up with a reasonable algorithm isn't *that* hard. Agreed. Our shop has used a home-grown framework for over a decade where we parse queries using ANTLR ( http://www.antlr.org/ ) and we tracked this trough all expressions. There really weren't that many situations where we had to punt. Sounds cool. What was your use-case for doing that? Portability. That approach is what made the conversion to PostgreSQL from the commercial product we were using quick and painless. -Kevin -- 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] pg_upgrade - add config directory setting
Bruce Momjian wrote: Bruce Momjian wrote: OK, I have modified the postmaster in PG 9.2 to allow output of the data directory, and modified pg_ctl to use that, so starting in PG 9.2 pg_ctl will work cleanly for config-only directories. I will now work on pg_upgrade to also use the new flag to find the data directory from a config-only install. However, this is only available in PG 9.2, and it will only be in PG 9.3 that you can hope to use this feature (if old is PG 9.2 or later). I am afraid the symlink hack will have to be used for several more years, and if you are supporting upgrades from pre-9.2, perhaps forever. I did find that it is possible to use pg_ctl -w start on a config-only install using this trick: su -l postgres \ -c env PGPORT=\5432\ /usr/lib/postgresql-9.1/bin/pg_ctl start -w \ -t 60 -s -D /var/lib/postgresql/9.1/data/ \ -o '-D /etc/postgresql-9.1/ \ --data-directory=/var/lib/postgresql/9.1/data/ \ --silent-mode=true' Unfortunately pg_upgrade doesn't support the -o option which would make this possible for pg_upgrade. One idea would be to add -o/-O options to pg_upgrade 9.2 to allow this to work even with old installs, but frankly, this is so confusing I am not sure we want to encourage people to do things like this. Of course, the symlink hack is even worse, so maybe there is some merit to this. OK, the attached patch adds -o/-O options to pg_upgrade to mimick pg_ctl -o, and documents the 'Gentoo method' for allowing pg_upgrade to handle pre-9.2 upgrades for config-only installs. I think this closes the issue, with no backpatching required for it to work for new PG 9.2. Users will have to continue using the symlink method for new PG 9.1. Applied to head. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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: fix a regress tests
On Mon, Oct 10, 2011 at 2:34 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello there is fix a locale dependency of regress tests Last time I forgot to attach a patch Committed (but next time please mention the details, so I don't have to guess). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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: fix a regress tests
2011/10/10 Robert Haas robertmh...@gmail.com: On Mon, Oct 10, 2011 at 2:34 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Hello there is fix a locale dependency of regress tests Last time I forgot to attach a patch Committed (but next time please mention the details, so I don't have to guess). I am sorry, thank you Regards Pavel -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Inconsistency in postgresql.conf
Bruce Momjian wrote: I notice that we use '(none)' as a default for external_pid_file, while other default no-value settings in the file are ''. The attached patch changes this. Applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [v9.2] DROP statement reworks
On Wed, Oct 5, 2011 at 2:58 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: Hmm. It indeed makes translation hard. I reverted this portion of the part-2 patch, as attached. Please review the newer one, instead of the previous revision. Please fix the compiler warnings. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] WIP: Join push-down for foreign tables
(2011/10/08 1:06), Kohei KaiGai wrote: What is the reason why the foreign join is not pushed down? Maybe, injected Sort plan prevent the planner to consider both side of relations being foreign scan owned by same server? I'm still investigating the reason. Thanks for your testing. I'm not sure, but I think that Sort plan node would not be the reason because it's an element of merge join. Maybe some wrong points would be in my join method consideration. In my assumption, ft1 and ft2 should be joined first (because such join has very low costs) and then that result and lt3 should be joined with one of local join methods, such as merge join and hash join. I'll try your example soon. Regards, -- Shigeru Hanada -- 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] Range Types - typo + NULL string constructor
On 2 October 2011 20:05, Jeff Davis pg...@j-davis.com wrote: On Sun, 2011-10-02 at 11:32 +0200, Florian Pflug wrote: Looking at the patch, I noticed that it's possible to specify the default boundaries ([], [), (] or ()) per individual float type with the DEFAULT_FLAGS clause of CREATE TYPE .. AS RANGE. I wonder if that doesn't do more harm then good - it makes it impossible to deduce the meaning of e.g. numericrange(1.0, 2.0) without looking up the definition of numericrange. I suggest we pick one set of default boundaries, ideally '[)' since that is what all the built-in canonization functions produce, and stick with it. Done. Also, made the range parsing even more like records with more code copied verbatim. And fixed some parsing tests along the way. I don't know if this has already been discussed, but can you explain the following: postgres=# select '[1,8]'::int4range; int4range --- [1,9) (1 row) It seems unintuitive to represent a discrete range using an exclusive upper bound. While I agree that the value itself is correct, it's representation looks odd. Is it necessary? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] [v9.2] DROP statement reworks
2011/10/10 Robert Haas robertmh...@gmail.com: On Wed, Oct 5, 2011 at 2:58 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: Hmm. It indeed makes translation hard. I reverted this portion of the part-2 patch, as attached. Please review the newer one, instead of the previous revision. Please fix the compiler warnings. I checked compiler warnings using COPT=-Werror, but it detects warning on only unrelated files as below. Does it really come from my patches? (Does it depend on ./configure options?) gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -Werror -I../../../src/include -D_GNU_SOURCE -c -o execQual.o execQual.c execQual.c: In function ‘GetAttributeByNum’: execQual.c:1104:67: error: the comparison will always evaluate as ‘true’ for the address of ‘tmptup’ will never be NULL [-Werror=address] execQual.c: In function ‘GetAttributeByName’: execQual.c:1165:67: error: the comparison will always evaluate as ‘true’ for the address of ‘tmptup’ will never be NULL [-Werror=address] execQual.c: In function ‘ExecEvalFieldSelect’: execQual.c:3914:67: error: the comparison will always evaluate as ‘true’ for the address of ‘tmptup’ will never be NULL [-Werror=address] cc1: all warnings being treated as errors gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -Werror -I../../../../src/include -D_GNU_SOURCE -c -o tuplesort.o tuplesort.c tuplesort.c: In function ‘comparetup_heap’: tuplesort.c:2751:66: error: the comparison will always evaluate as ‘true’ for the address of ‘ltup’ will never be NULL [-Werror=address] tuplesort.c:2752:66: error: the comparison will always evaluate as ‘true’ for the address of ‘rtup’ will never be NULL [-Werror=address] tuplesort.c: In function ‘copytup_heap’: tuplesort.c:2783:71: error: the comparison will always evaluate as ‘true’ for the address of ‘htup’ will never be NULL [-Werror=address] tuplesort.c: In function ‘readtup_heap’: tuplesort.c:2835:71: error: the comparison will always evaluate as ‘true’ for the address of ‘htup’ will never be NULL [-Werror=address] cc1: all warnings being treated as errors Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] Extend file_fdw wrapper
At a quick glance, this patch seems to have an issue about priority. Which value is used if an option has been set both on a foreign table and a foreign server? Also I think documents and regression tests would be required for this kind of change. Regards, -- Shigeru Hanada -- 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] libpq, PQdescribePrepared - PQftype, PQfmod, no PQnullable
Peter Eisentraut pete...@gmx.net writes: On sön, 2011-10-09 at 11:51 -0400, Tom Lane wrote: The problem with something like a protocol bump is that the coding required to make it happen (in the backend and libpq, that is) is only a small part of the total distributed cost. Why do we have major and minor protocol version numbers, which are supposed to allow incremental addition of features to the protocol? Well, that's a good question. I seem to recall that the last time it was discussed, questions were raised about whether a minor-number version bump would really work as desired. In particular, if the client connects asking for 3.1 and the server doesn't know anything later than 3.0, you end up having to do another connection cycle, which is rather inefficient and has got unpleasant failure cases too. This could be avoided if there were a way to have the server allow the connection but only at 3.0 level, but (1) there is no way to report that in 3.0 protocol, and (2) requiring clients to support 3.0 as well as 3.1 could be burdensome. Basically, it's uncharted territory, because we've never actually done it before. It wouldn't be a bad idea to put make sure upgrading to a 4.1 protocol version will actually work smoothly into our list of goals for protocol 4.0 ... 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] Extend file_fdw wrapper
On Mon, Oct 10, 2011 at 10:51:03PM +0900, Shigeru Hanada wrote: At a quick glance, this patch seems to have an issue about priority. Which value is used if an option has been set both on a foreign table and a foreign server? I believe that the finer-grained setting should always override the coarser, so in this case, the setting for the table should take precedence over the setting for the server. Also I think documents and regression tests would be required for this kind of change. +1 :) 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 iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics 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
[HACKERS] ALTER EXTENSION .. ADD/DROP weirdness
OK, I'm stumped: rhaas=# create extension pg_stat_statements; CREATE EXTENSION rhaas=# drop view pg_stat_statements; ERROR: cannot drop view pg_stat_statements because extension pg_stat_statements requires it HINT: You can drop extension pg_stat_statements instead. rhaas=# alter extension pg_stat_statements drop view pg_stat_statements; ALTER EXTENSION rhaas=# drop view pg_stat_statements; ERROR: cannot drop view pg_stat_statements because other objects depend on it DETAIL: extension pg_stat_statements depends on view pg_stat_statements HINT: Use DROP ... CASCADE to drop the dependent objects too. At the very last, the error message is totally confusing, because the point is that I just removed that object from the extension, and I'm being told that I can't remove it because it's part of the extension. A little snooping around with \dx+ reveals a possible cause: the view itself has been removed from the extension, but the associated types are still connected to it: rhaas=# \dx+ pg_stat_statements Objects in extension pg_stat_statements Object Description - function pg_stat_statements() function pg_stat_statements_reset() type pg_stat_statements type pg_stat_statements[] (4 rows) OK, no problem, I'll just disconnect those, too: rhaas=# alter extension pg_stat_statements drop type pg_stat_statements; ALTER EXTENSION rhaas=# alter extension pg_stat_statements drop type pg_stat_statements[]; ERROR: syntax error at or near [ LINE 1: ...extension pg_stat_statements drop type pg_stat_statements[]; ^ Hmm. So just how do I do this? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Extend file_fdw wrapper
On 10/10/2011 09:51 AM, Shigeru Hanada wrote: At a quick glance, this patch seems to have an issue about priority. Which value is used if an option has been set both on a foreign table and a foreign server? Also I think documents and regression tests would be required for this kind of change. I'm not even sure I understand why we should want this anyway. The closest analog I can think of to a more conventional server is that the whole file system is the foreign server, and there just don't seem to be any relevant options at that level. All the options being supplied seem much saner left as just foreign table options. 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] [v9.2] DROP statement reworks
On Mon, Oct 10, 2011 at 9:39 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2011/10/10 Robert Haas robertmh...@gmail.com: On Wed, Oct 5, 2011 at 2:58 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: Hmm. It indeed makes translation hard. I reverted this portion of the part-2 patch, as attached. Please review the newer one, instead of the previous revision. Please fix the compiler warnings. I checked compiler warnings using COPT=-Werror, but it detects warning on only unrelated files as below. Does it really come from my patches? (Does it depend on ./configure options?) OK, well, I applied pgsql-v9.2-drop-reworks-2.v4.1.patch and tried to compile, and got this: In file included from ../../../src/include/catalog/dependency.h:17, from dependency.c:19: ../../../src/include/catalog/objectaddress.h:21: warning: type defaults to ‘int’ in declaration of ‘ObjectAddress’ ../../../src/include/catalog/objectaddress.h:21: error: expected ‘;’, ‘,’ or ‘)’ before ‘*’ token The problem here is pretty obvious: you've defined get_object_namespace, which takes an argument of type ObjectAddress, before defining the ObjectAddress datatype, which is the next thing in the same header file. How does that even compile for you? That's easy enough to fix, but then I get this: objectaddress.c: In function ‘get_object_namespace’: objectaddress.c:996: warning: implicit declaration of function ‘get_object_property_attnum_namespace’ objectaddress.c:1000: warning: implicit declaration of function ‘get_object_property_catid_by_oid’ objectaddress.c:1006: warning: implicit declaration of function ‘get_object_property_typetext’ objectaddress.c:1006: warning: format ‘%s’ expects type ‘char *’, but argument 3 has type ‘int’ Maybe the problem here is that I've only applied the first patch, and this stuff is cleaned up in the later patches in the series. But what is the point of separating out the patches if you can't even compile with only some of them applied? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Extend file_fdw wrapper
Hi. Current behaviour is error message when foreign table and foreign server have the same option defined. I don't know how to write regression test, may i read about it somewhere? -- pasman -- 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] [v9.2] Fix Leaky View Problem
On Sun, Oct 9, 2011 at 11:50 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote:
I tried to refactor the patches based on the interface of WITH (...)
and usage of
pg_class.reloptions, although here is no functionality changes; including the
behavior when a view is replaced.
My preference is WITH (...) interface, however, it is not a strong one.
So, I hope either of versions being reviewed.
I spent some more time looking at this, and I guess I'm pretty unsold
on the whole approach. In the part 2 patch, for example, we're doing
this:
+static bool
+mark_qualifiers_depth_walker(Node *node, void *context)
+{
+ int depth = *((int *)(context));
+
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, FuncExpr))
+ {
+ ((FuncExpr *)node)-depth = depth;
+ }
+ else if (IsA(node, OpExpr))
+ {
+ ((OpExpr *)node)-depth = depth;
+ }
+ else if (IsA(node, DistinctExpr))
+ {
+ ((DistinctExpr *)node)-depth = depth;
+ }
+ else if (IsA(node, ScalarArrayOpExpr))
+ {
+ ((ScalarArrayOpExpr *)node)-depth = depth;
+ }
+ else if (IsA(node, CoerceViaIO))
+ {
+ ((CoerceViaIO *)node)-depth = depth;
+ }
+ else if (IsA(node, ArrayCoerceExpr))
+ {
+ ((ArrayCoerceExpr *)node)-depth = depth;
+ }
+ else if (IsA(node, NullIfExpr))
+ {
+ ((NullIfExpr *)node)-depth = depth;
+ }
+ else if (IsA(node, RowCompareExpr))
+ {
+ ((RowCompareExpr *)node)-depth = depth;
+ }
+ return expression_tree_walker(node,
mark_qualifiers_depth_walker, context);
+}
It seems really ugly to me to suppose that we need to add a depth
field to every single one of these node types. If you've missed one,
then we have a security hole. If someone else adds another node type
later that requires this field and doesn't add it, we have a security
hole. And since all of these depth fields are going to make their way
into stored rules, those security holes will require an initdb to fix.
Ouch! And what happens if the security view becomes a non-security
view or visca versa? Now all of those stored depth fields are out of
date. Maybe you can argue that we can just patch that up when we
reload them, but that seems to me to miss the point. If the data in a
stored rule can get out of date, then it shouldn't be stored there in
the first place.
Tom may have a better feeling on this than I do, but my gut feeling
here is that this whole approach is letting the cat out of the bag and
then trying to stuff it back in. I don't think that's going to be
very reliable, and more than that, I don't like our chances of having
confidence in its reliability. I feel like the heart of what we're
doing here ought to be preventing the subquery from getting flattened.
For example:
rhaas=# create table secret (a int, b text);
CREATE TABLE
rhaas=# insert into secret select g, random()::text||random()::text
from generate_series(1,1) g;
INSERT 0 1
rhaas=# create view window_on_secret as select * from secret where a = 1;
CREATE VIEW
rhaas=# create table leak (a int, b text);
CREATE TABLE
rhaas=# create or replace function snarf(a int, b text) returns
boolean as $$begin insert into leak values ($1, $2); return true;
end$$ language plpgsql cost
0.1;
CREATE FUNCTION
rhaas=# explain analyze select * from window_on_secret;
QUERY PLAN
---
Seq Scan on secret (cost=0.00..209.00 rows=1 width=39) (actual
time=0.022..2.758 rows=1 loops=1)
Filter: (a = 1)
Rows Removed by Filter:
Total runtime: 2.847 ms
(4 rows)
rhaas=# select * from leak;
a | b
---+---
(0 rows)
rhaas=# explain analyze select * from window_on_secret where snarf(a, b);
QUERY PLAN
-
Seq Scan on secret (cost=0.00..209.00 rows=1 width=39) (actual
time=0.671..126.521 rows=1 loops=1)
Filter: (snarf(a, b) AND (a = 1))
Rows Removed by Filter:
Total runtime: 126.565 ms
(4 rows)
Woops! I've stolen the whole table. But look what happens when I
change the definition of window_on_secret so that it can't be
flattened:
rhaas=# truncate leak;
TRUNCATE TABLE
rhaas=# create or replace view window_on_secret as select * from
secret where a = 1 limit 10;
CREATE VIEW
rhaas=# explain analyze select * from window_on_secret where snarf(a, b);
QUERY PLAN
--
Subquery Scan on window_on_secret (cost=0.00..209.01 rows=1
width=39) (actual
Re: [HACKERS] patch: move dumpUserConfig call in dumpRoles function of pg_dumpall.c
On Thu, Aug 4, 2011 at 2:04 PM, Robert Haas robertmh...@gmail.com wrote: On Thu, Aug 4, 2011 at 1:53 PM, Phil Sorber p...@omniti.com wrote: Ok, here is the patch that just moves the ALTER/SET pieces to the end. Can we get this included in the next commit fest? Yep, just make yourself an account and add it. Unfortunately, it doesn't look like anyone ever replied to this thread, but Tom posted some thoughts on another thread that seem to me to be a serious problem for this patch: http://archives.postgresql.org/message-id/13764.1315094...@sss.pgh.pa.us I don't see any easy way around that problem, so I'm going to mark this patch Returned with Feedback for now. It strikes me as craziness to try to guess which settings we should restore at the beginning and which at the end, so I think we need a better idea. I don't really understand why it's not OK to just have pg_dump issue RESET ROLE at appropriate points in the process; that seems like it would be sufficient and not particularly ugly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Extend file_fdw wrapper
On Mon, Oct 10, 2011 at 10:23:51AM -0400, Andrew Dunstan wrote: On 10/10/2011 09:51 AM, Shigeru Hanada wrote: At a quick glance, this patch seems to have an issue about priority. Which value is used if an option has been set both on a foreign table and a foreign server? Also I think documents and regression tests would be required for this kind of change. I'm not even sure I understand why we should want this anyway. The closest analog I can think of to a more conventional server is that the whole file system is the foreign server, and there just don't seem to be any relevant options at that level. All the options being supplied seem much saner left as just foreign table options. You raise an excellent point, which is that there probably should be options at that level which override the (settable) generic file_fdw options. 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 iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics 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] Extend file_fdw wrapper
On 10/10/2011 11:59 AM, David Fetter wrote: On Mon, Oct 10, 2011 at 10:23:51AM -0400, Andrew Dunstan wrote: On 10/10/2011 09:51 AM, Shigeru Hanada wrote: At a quick glance, this patch seems to have an issue about priority. Which value is used if an option has been set both on a foreign table and a foreign server? Also I think documents and regression tests would be required for this kind of change. I'm not even sure I understand why we should want this anyway. The closest analog I can think of to a more conventional server is that the whole file system is the foreign server, and there just don't seem to be any relevant options at that level. All the options being supplied seem much saner left as just foreign table options. You raise an excellent point, which is that there probably should be options at that level which override the (settable) generic file_fdw options. That's not my point at all. 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] patch: move dumpUserConfig call in dumpRoles function of pg_dumpall.c
Robert Haas robertmh...@gmail.com writes: I don't really understand why it's not OK to just have pg_dump issue RESET ROLE at appropriate points in the process; that seems like it would be sufficient and not particularly ugly. Well, it was alleged that that would fix this problem: http://archives.postgresql.org/pgsql-hackers/2010-12/msg00916.php but if it does fix it, I think that's a bug in itself: http://archives.postgresql.org/pgsql-hackers/2010-12/msg01031.php But more to the point, I think the specific case of ALTER DATABASE SET ROLE is just one element of a class of problems, namely that settings attached to either databases or roles could create issues for restoring a dump. Issuing RESET ROLE would fix only that one single case. 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] ALTER EXTENSION .. ADD/DROP weirdness
Robert Haas robertmh...@gmail.com writes: rhaas=# alter extension pg_stat_statements drop type pg_stat_statements[]; ERROR: syntax error at or near [ LINE 1: ...extension pg_stat_statements drop type pg_stat_statements[]; ^ Hmm. So just how do I do this? alter extension pg_stat_statements drop type _pg_stat_statements, probably. 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] patch: move dumpUserConfig call in dumpRoles function of pg_dumpall.c
On Mon, Oct 10, 2011 at 12:14 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I don't really understand why it's not OK to just have pg_dump issue RESET ROLE at appropriate points in the process; that seems like it would be sufficient and not particularly ugly. Well, it was alleged that that would fix this problem: http://archives.postgresql.org/pgsql-hackers/2010-12/msg00916.php but if it does fix it, I think that's a bug in itself: http://archives.postgresql.org/pgsql-hackers/2010-12/msg01031.php Hmm. But more to the point, I think the specific case of ALTER DATABASE SET ROLE is just one element of a class of problems, namely that settings attached to either databases or roles could create issues for restoring a dump. Issuing RESET ROLE would fix only that one single case. It's not very clear to me that we're going to find a fix that reaches across every setting, though. I mean, for something like maintenance_work_mem, there's no correctness issue regardless of when the new setting takes effect, but there might very well be a performance issue, and it's not really all that clear when the right time to put the old setting back is. And that ambiguity about what's actually correct is, perhaps, the root of the problem. There are related cases where we have a clear-cut policy. For example, we are clear that you must use the newer pg_dump against the older server for best results. That's not always convenient, but at least it's a line in the sand. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] WIP: Join push-down for foreign tables
2011/10/10 Shigeru Hanada shigeru.han...@gmail.com: (2011/10/08 1:06), Kohei KaiGai wrote: What is the reason why the foreign join is not pushed down? Maybe, injected Sort plan prevent the planner to consider both side of relations being foreign scan owned by same server? I'm still investigating the reason. Thanks for your testing. I'm not sure, but I think that Sort plan node would not be the reason because it's an element of merge join. Maybe some wrong points would be in my join method consideration. In my assumption, ft1 and ft2 should be joined first (because such join has very low costs) and then that result and lt3 should be joined with one of local join methods, such as merge join and hash join. This might be out of left field, but wouldn't it make more sense to get postgresql_fdw committed first, and then add the join push-down functionality afterwards? I mean, otherwise, we're going to be left with a situation where we have join pushdown in core, but the only FDW that can actually make use of it elsewhere. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Range Types - typo + NULL string constructor
On Mon, 2011-10-10 at 14:27 +0100, Thom Brown wrote: I don't know if this has already been discussed, but can you explain the following: postgres=# select '[1,8]'::int4range; int4range --- [1,9) (1 row) It seems unintuitive to represent a discrete range using an exclusive upper bound. While I agree that the value itself is correct, it's representation looks odd. Is it necessary? The canonicalize function (specified at type creation time) allows you to specify the canonical output representation. So, I can change the canonical form for discrete ranges to use '[]' notation if we think that's more expected. But then int4range(1,8) would still mean int4range(1,8,'[)') and therefore '[1,7]'. I used to have a default_flags parameter that could also be specified at type creation time that would control the default third parameter (the parameter that controls inclusivity) of the constructor. However, I removed the default_flags parameter because, as Florian pointed out, it's better to have a consistent output from the constructor. I'm open to suggestions, including potentially bringing back default_flags. Regards, Jeff Davis -- 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] Range Types - typo + NULL string constructor
Jeff Davis pg...@j-davis.com writes: On Mon, 2011-10-10 at 14:27 +0100, Thom Brown wrote: I don't know if this has already been discussed, but can you explain the following: postgres=# select '[1,8]'::int4range; int4range --- [1,9) (1 row) It seems unintuitive to represent a discrete range using an exclusive upper bound. While I agree that the value itself is correct, it's representation looks odd. Is it necessary? The canonicalize function (specified at type creation time) allows you to specify the canonical output representation. So, I can change the canonical form for discrete ranges to use '[]' notation if we think that's more expected. What if I write '[1,INT_MAX]'::int4range? The open-parenthesis form will fail with an integer overflow. I suppose you could canonicalize it to an unbounded range, but that seems unnecessarily surprising. 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] WIP: Join push-down for foreign tables
Robert Haas robertmh...@gmail.com writes: This might be out of left field, but wouldn't it make more sense to get postgresql_fdw committed first, and then add the join push-down functionality afterwards? I mean, otherwise, we're going to be left with a situation where we have join pushdown in core, but the only FDW that can actually make use of it elsewhere. There's likely to be a lot of FDW infrastructure that will not be exercised by anything in core or contrib ... 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] SET variable - Permission issues
On 10/09/2011 09:09 PM, Robert Haas wrote: Having said that, I do think it might be useful to have ways of controlling the values that users can set for GUC values, not so much as a guard against an all-out assault (which is probably futile) but as a way for DBAs to enforce system policy. But even that seems like a lot of work for a fairly marginal benefit I think the issues Josh raised are valid concerns for a number of use cases. Even if you don't want to allow anyone on the Internet into your database (as Josh does, since his application is a game and his attempt is to set policies and privileges such that it is actually safe), there are plenty of companies needing to run Postgres in a multi-tenant environment. Currently customer A can set work_mem = some very large number; and set statement_timeout = 0; and run a big query effectively DOS'ing customers B, C, and D. If these two settings could be restricted by the DBA, there would be a much lower chance of this happening. There are undoubtedly other holes to fill, but it seems like a worthy cause. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 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] Range Types - typo + NULL string constructor
On Oct10, 2011, at 18:53 , Tom Lane wrote: What if I write '[1,INT_MAX]'::int4range? The open-parenthesis form will fail with an integer overflow. I suppose you could canonicalize it to an unbounded range, but that seems unnecessarily surprising. That is a very good point. Canonicalizing to an unbounded range doesn't work, because, as it stands, the ranges '[1, INT_MAX]' and '[1,)' are *not* equal. So the only remaining option is to canonicalize to the closed form always. I still think we should strive for consistency here, so let's also make '[]' the default flags for the range constructors. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] COUNT(*) and index-only scans
I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*). Is this something we can do for PG 9.2? Is anyone working on this? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] SET variable - Permission issues
On Mon, Oct 10, 2011 at 1:06 PM, Joe Conway m...@joeconway.com wrote: On 10/09/2011 09:09 PM, Robert Haas wrote: Having said that, I do think it might be useful to have ways of controlling the values that users can set for GUC values, not so much as a guard against an all-out assault (which is probably futile) but as a way for DBAs to enforce system policy. But even that seems like a lot of work for a fairly marginal benefit I think the issues Josh raised are valid concerns for a number of use cases. Even if you don't want to allow anyone on the Internet into your database (as Josh does, since his application is a game and his attempt is to set policies and privileges such that it is actually safe), there are plenty of companies needing to run Postgres in a multi-tenant environment. Currently customer A can set work_mem = some very large number; and set statement_timeout = 0; and run a big query effectively DOS'ing customers B, C, and D. If these two settings could be restricted by the DBA, there would be a much lower chance of this happening. There are undoubtedly other holes to fill, but it seems like a worthy cause. Even in a controlled environment, say in a company where only legit apps developed in-house are run on the DB, a DBA would want peace of mind that the developers are not setting these GUCs at runtime (which is often even recommended in case of work_mem) to bypass a policy set by the DBA and are capable of bringing the DB down to its knees. Regards, -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] Range Types - typo + NULL string constructor
On Mon, 2011-10-10 at 12:53 -0400, Tom Lane wrote: The canonicalize function (specified at type creation time) allows you to specify the canonical output representation. So, I can change the canonical form for discrete ranges to use '[]' notation if we think that's more expected. What if I write '[1,INT_MAX]'::int4range? The open-parenthesis form will fail with an integer overflow. I suppose you could canonicalize it to an unbounded range, but that seems unnecessarily surprising. So, are you suggesting that I canonicalize to '[]' then? That seems reasonable to me, but there's still some slight awkwardness because int4range(1,10) would be '[1,9]'. Regards, Jeff Davis -- 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] COUNT(*) and index-only scans
On 10 October 2011 18:23, Bruce Momjian br...@momjian.us wrote: I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*). Is this something we can do for PG 9.2? Is anyone working on this? Yes it does, provided that there is an appropriate WHERE clause. But yes, I think we definitely want this if it's relatively easy. In addition to this, it's not always easy to get it to use an index-only scan even if it's going to significantly faster. I'm assuming some supporting planner work needs to be added too. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] COUNT(*) and index-only scans
On Mon, Oct 10, 2011 at 6:23 PM, Bruce Momjian br...@momjian.us wrote: I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*). Is this something we can do for PG 9.2? Is anyone working on this? People usually conflate multiple problems when they talk about count(*). The usual case people are concerned about would require materialized views, not index-only scans. -- greg -- 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] COUNT(*) and index-only scans
Bruce Momjian br...@momjian.us wrote: I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*). Is this something we can do for PG 9.2? Is anyone working on this? Well, it's not that it doesn't optimize COUNT(*) -- it's that it doesn't yet cost the index scan as cheaper than a table scan when you're accessing every row. create table t (id int not null primary key); insert into t select generate_series(1, 100); vacuum freeze analyze; explain analyze select count(*) from t where id between 50 and 500010; That gives you an index-only scan; but without the WHERE clause it uses a seq scan. I think it's mainly a matter of doing enough benchmarks to figure out how best to model the costs of the index scan so that it can be picked for that case. -Kevin -- 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] Range Types - typo + NULL string constructor
On 10 October 2011 18:31, Jeff Davis pg...@j-davis.com wrote: On Mon, 2011-10-10 at 12:53 -0400, Tom Lane wrote: The canonicalize function (specified at type creation time) allows you to specify the canonical output representation. So, I can change the canonical form for discrete ranges to use '[]' notation if we think that's more expected. What if I write '[1,INT_MAX]'::int4range? The open-parenthesis form will fail with an integer overflow. I suppose you could canonicalize it to an unbounded range, but that seems unnecessarily surprising. So, are you suggesting that I canonicalize to '[]' then? That seems reasonable to me, but there's still some slight awkwardness because int4range(1,10) would be '[1,9]'. Why? int4range(1,10,'[]') returns: int4range --- [1,11) (1 row) Which if corrected to display the proposed way would just be '[1,10]'. So the default boundaries should be '[]' as opposed to '[)' as it is now. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Range Types - typo + NULL string constructor
On Mon, 2011-10-10 at 19:22 +0200, Florian Pflug wrote: I still think we should strive for consistency here, so let's also make '[]' the default flags for the range constructors. For continuous ranges I don't think that's a good idea. Closed-open is a very widely-accepted convention and there are good reasons for it -- for instance, it's good for specifying contiguous-but-non-overlapping ranges. So, I think we either need to standardize on '[)' or allow different default_flags for different types. Or, always specify the inclusivity in the constructor (hopefully in a convenient way). Regards, Jeff Davis -- 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] Range Types - typo + NULL string constructor
On Mon, 2011-10-10 at 18:39 +0100, Thom Brown wrote: So the default boundaries should be '[]' as opposed to '[)' as it is now. Would that vary between range types? In other words, do I bring back default_flags? If not, I think a lot of people will object. The most common use-case for range types are for continuous ranges like timestamps. And (as I pointed out in reply to Florian) there are good reasons to use the '[)' convention for those cases. Regards, Jeff Davis -- 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] COUNT(*) and index-only scans
On Mon, Oct 10, 2011 at 1:36 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Bruce Momjian br...@momjian.us wrote: I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*). Is this something we can do for PG 9.2? Is anyone working on this? Well, it's not that it doesn't optimize COUNT(*) -- it's that it doesn't yet cost the index scan as cheaper than a table scan when you're accessing every row. create table t (id int not null primary key); insert into t select generate_series(1, 100); vacuum freeze analyze; explain analyze select count(*) from t where id between 50 and 500010; That gives you an index-only scan; but without the WHERE clause it uses a seq scan. I think it's mainly a matter of doing enough benchmarks to figure out how best to model the costs of the index scan so that it can be picked for that case. Right now, our costing model for index-only scans is pretty dumb. It assumes that using an index-only scan will avoid 10% of the heap fetches. That could easily be low, and on an insert-only table or one where only the recently-updated rows are routinely accessed, it could also be high. To use an index-only scan for a full-table COUNT(*), we're going to have to be significantly smarter, because odds are good that skipping 10% of the heap fetches won't be sufficient inducement to the planner to go that route; we are going to need a real number. This isn't just an exercise in costing, though: right now, we don't even generate a plan to use an index for a full-table scan, because we assume that it can never be cheaper. This is actually not quite true even in previous releases (suppose the table is severely bloated but the index is not) and it's going to be less true now that we have index-only scans. So that's going to need some adjustment, too. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] unite recovery.conf and postgresql.conf
Simon, Tatsuo/Josh/Robert also discussed how recovery.conf can be used to provide parameters solely for recovery. That is difficult to do without causing all downstream tools to make major changes in the ways they supply parameters. Actually, this case is easily solved by an include recovery.conf parameter. So it's a non-issue. Keeping our APIs relatively stable is important to downstream tools. I have no objection to a brave new world, as long as you don't chuck out the one that works right now. Breaking APIs needs a good reason and I've not seen one discussed anywhere. No problem with immediately deprecating the old API and declare is planned to be removed in release 10.0. So after debugging some of our failover scripts, here's the real-world problems I'm trying to solve. These design flaws are issues which cause automated failover or failback to abort, leading to unexpected downtime, so they are not just issues of neatness: 1. Recovery.conf being both a configuration file AND a trigger to initiate recovery mode, preventing us from separating configuration management from failover. 2. The inability to read recovery.conf parameters via SQL on a hot standby, forcing us to parse the file to find out its settings, or guess. (1) is a quite serious issue; it effectively makes recovery.conf impossible to integrate with puppet and other configuration management frameworks. I also don't see a way to fix it without breaking backwards compatibility. BTW, I'm not criticizing the original design for this. We simply didn't know better until lots of people were using these tools in production. But it's time to fix them, and the longer we wait, the more painful it will be. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Range Types - typo + NULL string constructor
On 10 October 2011 18:45, Jeff Davis pg...@j-davis.com wrote: On Mon, 2011-10-10 at 18:39 +0100, Thom Brown wrote: So the default boundaries should be '[]' as opposed to '[)' as it is now. Would that vary between range types? In other words, do I bring back default_flags? If not, I think a lot of people will object. The most common use-case for range types are for continuous ranges like timestamps. And (as I pointed out in reply to Florian) there are good reasons to use the '[)' convention for those cases. I'm proposing it for discrete ranges. For continuous ranges, I guess it makes sense to have up to, but not including. The same boundary inclusivity/exclusivity thing seems unintuitive for discrete ranges. This has the downside of inconsistency, but I don't think that's really a solid argument against it since their use will be different anyway. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Range Types - typo + NULL string constructor
On 10 October 2011 18:53, Thom Brown t...@linux.com wrote: On 10 October 2011 18:45, Jeff Davis pg...@j-davis.com wrote: On Mon, 2011-10-10 at 18:39 +0100, Thom Brown wrote: So the default boundaries should be '[]' as opposed to '[)' as it is now. Would that vary between range types? In other words, do I bring back default_flags? If not, I think a lot of people will object. The most common use-case for range types are for continuous ranges like timestamps. And (as I pointed out in reply to Florian) there are good reasons to use the '[)' convention for those cases. I'm proposing it for discrete ranges. For continuous ranges, I guess it makes sense to have up to, but not including. The same boundary inclusivity/exclusivity thing seems unintuitive for discrete ranges. This has the downside of inconsistency, but I don't think that's really a solid argument against it since their use will be different anyway. Okay, a real example of why discrete should be '[]' and continuous should be '[)'. If you book a meeting from 09:00 to 11:00 (tsrange), at 11:00 precisely it either becomes free or is available to someone else, so it can be booked 11:00 to 12:00 without conflict. If you have raffle tickets numbered 1 to 100 (int4range), and you ask for tickets 9 to 11, no-one else can use 11 as it aligns with the last one you bought. So for me, it's intuitive for them to behave differently. So yes, default behaviour would vary between types, but I didn't previously read anything on default_flags, so I don't know where that comes into it. Shouldn't it be the case that if a type has a canonical function, it's entirely inclusive, otherwise it's upper boundary is exclusive? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] ALTER EXTENSION .. ADD/DROP weirdness
On Mon, Oct 10, 2011 at 12:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: rhaas=# alter extension pg_stat_statements drop type pg_stat_statements[]; ERROR: syntax error at or near [ LINE 1: ...extension pg_stat_statements drop type pg_stat_statements[]; ^ Hmm. So just how do I do this? alter extension pg_stat_statements drop type _pg_stat_statements, probably. *tests* Yeah, that works. But it seems undesirable for people writing upgrade scripts to need to count on the way we generate internal type names for array types. But there's a bigger problem: it seems to me that we have an inconsistency between what happens when you create an extension from scratch and when you upgrade it from unpackaged. Both pg_buffercache and pg_stat_statements just do this in the upgrade from unpackaged case: ALTER EXTENSION ext-name ADD view view-name; They do *not* add the type and the array type. But when the 1.0 script is run, the type and array type end up belonging to the extension. This seems bad. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] [v9.2] DROP statement reworks
OK, well, I applied pgsql-v9.2-drop-reworks-2.v4.1.patch and tried to compile, and got this: In file included from ../../../src/include/catalog/dependency.h:17, from dependency.c:19: ../../../src/include/catalog/objectaddress.h:21: warning: type defaults to ‘int’ in declaration of ‘ObjectAddress’ ../../../src/include/catalog/objectaddress.h:21: error: expected ‘;’, ‘,’ or ‘)’ before ‘*’ token The problem here is pretty obvious: you've defined get_object_namespace, which takes an argument of type ObjectAddress, before defining the ObjectAddress datatype, which is the next thing in the same header file. How does that even compile for you? Sorry, I didn't write out dependency of the patches. Please apply the patches in order of part-1, part-2 then part-3. I checked correctness of the part-2 on the tree with the part-1 already. Both of the part-1 and part-2 patches try to modify objectaddress.h, and the part-2 tries to add get_object_namespace() definition around the code added by the part-1, so the patch commands get confused and moved the hunk in front of the definition of ObjectAddress. [kaigai@iwashi pgsql]$ cat ~/patch/pgsql-v9.2-drop-reworks-2.v4.1.patch | patch -p1 : patching file src/backend/catalog/objectaddress.c Hunk #1 succeeded at 976 (offset -429 lines). : patching file src/include/catalog/objectaddress.h Hunk #1 succeeded at 17 with fuzz 2 (offset -18 lines). : patching file src/test/regress/expected/drop_if_exists.out I'm sorry again. I tought it was obvious from the filenames. * Part-1 pgsql-v9.2-drop-reworks-1.v4.patch * Part-2 (depends on Part-1) pgsql-v9.2-drop-reworks-2.v4.1.patch * Part-3 (depends on Part-1 and -2) pgsql-v9.2-drop-reworks-3.v4.patch Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] SET variable - Permission issues
Gurjeet Singh singh.gurj...@gmail.com writes: On Mon, Oct 10, 2011 at 1:06 PM, Joe Conway m...@joeconway.com wrote: Currently customer A can set work_mem = some very large number; and set statement_timeout = 0; and run a big query effectively DOS'ing customers B, C, and D. If these two settings could be restricted by the DBA, there would be a much lower chance of this happening. There are undoubtedly other holes to fill, but it seems like a worthy cause. Even in a controlled environment, say in a company where only legit apps developed in-house are run on the DB, a DBA would want peace of mind that the developers are not setting these GUCs at runtime (which is often even recommended in case of work_mem) to bypass a policy set by the DBA and are capable of bringing the DB down to its knees. Any developer who can't think of six ways to DOS the server without changing those settings should be fired on the spot for incompetence. I could get interested in this if it weren't that the problem is so open-ended as to be basically insoluble. The only solution to the OP's problem that's not got more holes than a wheel of Swiss cheese is to not let untrustworthy people have direct SQL access to the server. It *does not improve security* to close a couple of obvious holes and leave a bunch of other avenues to the same end open. All it does is give you a false sense of security. 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] COUNT(*) and index-only scans
Kevin Grittner kevin.gritt...@wicourts.gov writes: Bruce Momjian br...@momjian.us wrote: I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*). Is this something we can do for PG 9.2? Is anyone working on this? Well, it's not that it doesn't optimize COUNT(*) -- it's that it doesn't yet cost the index scan as cheaper than a table scan when you're accessing every row. I think what Robert is complaining about is that we won't currently consider an index that matches neither any WHERE clauses nor ORDER BY, ie, count(*) over the whole table won't get considered for an index-only scan, regardless of cost estimates. 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] ALTER EXTENSION .. ADD/DROP weirdness
Robert Haas robertmh...@gmail.com writes: But there's a bigger problem: it seems to me that we have an inconsistency between what happens when you create an extension from scratch and when you upgrade it from unpackaged. Both pg_buffercache and pg_stat_statements just do this in the upgrade from unpackaged case: ALTER EXTENSION ext-name ADD view view-name; They do *not* add the type and the array type. But when the 1.0 script is run, the type and array type end up belonging to the extension. This seems bad. Hmm, yeah, we need to make those consistent. The underlying issue here is whether objects dependent on an extension member should have direct dependencies on the extension too, and if not, how do we prevent that? The recordDependencyOnCurrentExtension calls don't have enough information to know what to do, I think. 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] SET variable - Permission issues
On Mon, Oct 10, 2011 at 2:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Any developer who can't think of six ways to DOS the server without changing those settings should be fired on the spot for incompetence. No kidding. But the point is that if the developer down the hall maliciously destroys your database server, you can go through channels and get him reprimanded or fired. But if the developer down the hall is a new hire who doesn't know beans about PostgreSQL and tries setting work_mem to 10GB, well, oops, it was an accident. And then another developer gets hired three weeks later and does the same thing. And then three months later somebody does it again. After a while people no longer remember that in each case it was a developer to blame. What they remember is that the DBA let the server go down three times. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Overhead cost of Serializable Snapshot Isolation
I'm looking into upgrading a fairly busy system to 9.1. They use serializable mode for a few certain things, and suffer through some serialization errors as a result. While looking over the new serializable/SSI documentation, one thing that stood out is: http://www.postgresql.org/docs/current/interactive/transaction-iso.html The monitoring of read/write dependencies has a cost, as does the restart of transactions which are terminated with a serialization failure, but balanced against the cost and blocking involved in use of explicit locks and SELECT FOR UPDATE or SELECT FOR SHARE, Serializable transactions are the best performance choice for some environments. I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of read/write dependencies'? This is my only concern at the moment. Are we talking insignificant overhead? Minor? Is it measurable? Hard to say without knowing the number of txns, number of locks, etc.? -- Greg Sabino Mullane g...@endpoint.com End Point Corporation PGP Key: 0x14964AC8 pgpXfFQOk4fgH.pgp Description: PGP signature
Re: [HACKERS] ALTER EXTENSION .. ADD/DROP weirdness
On Mon, Oct 10, 2011 at 2:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: But there's a bigger problem: it seems to me that we have an inconsistency between what happens when you create an extension from scratch and when you upgrade it from unpackaged. Both pg_buffercache and pg_stat_statements just do this in the upgrade from unpackaged case: ALTER EXTENSION ext-name ADD view view-name; They do *not* add the type and the array type. But when the 1.0 script is run, the type and array type end up belonging to the extension. This seems bad. Hmm, yeah, we need to make those consistent. The underlying issue here is whether objects dependent on an extension member should have direct dependencies on the extension too, and if not, how do we prevent that? The recordDependencyOnCurrentExtension calls don't have enough information to know what to do, I think. Well, I'm not an expert on this code, but from a user perspective, I think it would be nicer if only the view ended up being a member of the extension, and the generated types did not. Otherwise, writing an extension upgrade script requires detailed knowledge of what other objects are going to be generated internally. In fact, it doesn't seem implausible that the set of internally generated objects from a given DDL command could change between releases, which would really be rather ugly here. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] COUNT(*) and index-only scans
Robert Haas robertmh...@gmail.com wrote: Right now, our costing model for index-only scans is pretty dumb. It assumes that using an index-only scan will avoid 10% of the heap fetches. That could easily be low, and on an insert-only table or one where only the recently-updated rows are routinely accessed, it could also be high. As a reality check, I just ran this query on a table in a statewide copy of our data: select count(*), sum(case when xmin = '2'::xid then 0 else 1 end) as read_heap from CaseHist; and got: count | read_heap ---+--- 205765311 | 3934924 So on our real-world database, it would skip something on the order of 98% of the heap reads, right? This isn't just an exercise in costing, though: right now, we don't even generate a plan to use an index for a full-table scan, because we assume that it can never be cheaper. This is actually not quite true even in previous releases (suppose the table is severely bloated but the index is not) and it's going to be less true now that we have index-only scans. So that's going to need some adjustment, too. OK. Thanks for clarifying. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
table/index options | was: [HACKERS] COUNT(*) and index-only scans
2011/10/10 Robert Haas robertmh...@gmail.com: On Mon, Oct 10, 2011 at 1:36 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: That gives you an index-only scan; but without the WHERE clause it uses a seq scan. I think it's mainly a matter of doing enough benchmarks to figure out how best to model the costs of the index scan so that it can be picked for that case. Right now, our costing model for index-only scans is pretty dumb. It assumes that using an index-only scan will avoid 10% of the heap fetches. That could easily be low, and on an insert-only table or one where only the recently-updated rows are routinely accessed, it could also be high. To use an index-only scan for a full-table COUNT(*), we're going to have to be significantly smarter, because odds are good that skipping 10% of the heap fetches won't be sufficient inducement to the planner to go that route; we are going to need a real number. I have to raise that I think we are going to face the exact same issue with the visibility_fraction that we face with the hack to set random_page_cost very low to help optimizer (when index/table is mostly in cache). 4 options have been viewed so far: 1. pg_class (initial proposal to store the cache estimates) 2. pg_class_nt (revived by Alvaro IIRC) 3. reloption 4. GUC (by Tom for visibility_fraction) I am in favor of 1 or 2, 4 is a backup option, and 3 an open door to planner hint (others also let DBA use its knowledge if he wants, but 3 make it mandatory for the DBA to decide, and no automatic way can be used to update it, except if someone make ALTER TABLE lock free) (It does not prevent a cost_indexonly() to be written meawhile...) What do you think/prefer/suggest ? -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- 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] COUNT(*) and index-only scans
Tom Lane t...@sss.pgh.pa.us wrote: I think what Robert is complaining about is that we won't currently consider an index that matches neither any WHERE clauses nor ORDER BY, ie, count(*) over the whole table won't get considered for an index-only scan, regardless of cost estimates. I guess the trick would be to get it to consider such plans only under some conditions, to avoid explosive growth in planning time for some types of queries. Some statistics bucket for the number of non-frozen tuples in the relation, maybe? -Kevin -- 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] Bug in walsender when calling out to do_pg_stop_backup (and others?)
On Thu, Oct 6, 2011 at 23:46, Florian Pflug f...@phlo.org wrote: On Oct6, 2011, at 21:48 , Magnus Hagander wrote: The question is, should we do more? To me, it'd make sense to terminate a backend once it's connection is gone. We could, for example, make pq_flush() set a global flag, and make CHECK_FOR_INTERRUPTS handle a broken connection that same way as a SIGINT or SIGTERM. The problem here is that we're hanging at a place where we don't touch the socket. So we won't notice the socket is gone. We'd have to do a select() or something like that at regular intervals to make sure it's there, no? We do emit NOTICEs saying pg_stop_backup still waiting ... repeatedly, so we should notice a dead connection sooner or later. When I tried, I even got a message in the log complaining about the broken pipe. Ah, good point, that should be doable. Forgot about that message... As it stands, the interval between two NOTICEs grows exponentially - we send the first after waiting 5 second, the next after waiting 60 seconds, and then after waiting 120, 240, 480, ... seconds. This means that that the backend would in the worst case linger the same amount of time *after* pg_basebackup was cancelled that pg_basebackup waited for *before* it was cancelled. It'd be nice to generally terminate a backend if the client vanishes, but so far I haven't had any bright ideas. Using FASYNC and F_SETOWN unfortunately sends a signal *everytime* the fd becomes readable or writeable, not only on EOF. Doing select() in CHECK_FOR_INTERRUPTS seems far too expensive. We could make the postmaster keep the fd's of around even after forking a backend, and make it watch for broken connections using select(). But with a large max_backends settings, we'd risk running out of fds in the postmaster... Ugh. Yeah. But at least catching it and terminating it when we *do* notice it's down would certainly make sense... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Should we get rid of custom_variable_classes altogether?
On Mon, Oct 3, 2011 at 00:05, Tom Lane t...@sss.pgh.pa.us wrote: So at this point I'd vote for just dropping it and always allowing custom (that is, qualified) GUC names to be set, whether the prefix corresponds to any loaded module or not. Comments, other proposals? While working on E.164 telephone numbers datatype contrib module (https://github.com/commandprompt/e164/commits/guc) I've stumbled across this problem: how do I add regression tests involving the module-defined GUC option? Trying to hack postgresql.conf to include e164 in the custom_variable_classes then send it a HUP doesn't seem to be an option. But it seems that you cannot (re)set it otherwise. See: $ psql -d contrib_regression psql (9.1.0) Type help for help. contrib_regression=# SET e164.area_codes_format=''; ERROR: unrecognized configuration parameter e164.area_codes_format contrib_regression=# SET custom_variable_classes='e164'; ERROR: parameter custom_variable_classes cannot be changed now I wonder how/if other contrib modules ever do regression tests on their GUC options? At this rate, removing the custom_variable_classes option altogether is pretty much going to solve my problem. -- Regards, Alex -- 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] Overhead cost of Serializable Snapshot Isolation
On 10.10.2011 21:25, Greg Sabino Mullane wrote: I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of read/write dependencies'? This is my only concern at the moment. Are we talking insignificant overhead? Minor? Is it measurable? Hard to say without knowing the number of txns, number of locks, etc.? I'm sure it does depend heavily on all of those things, but IIRC Kevin ran some tests earlier in the spring and saw a 5% slowdown. That feels like reasonable initial guess to me. If you can run some tests and measure the overhead in your application, it would be nice to hear about it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Overhead cost of Serializable Snapshot Isolation
On Mon, Oct 10, 2011 at 02:25:59PM -0400, Greg Sabino Mullane wrote: I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of read/write dependencies'? This is my only concern at the moment. Are we talking insignificant overhead? Minor? Is it measurable? Hard to say without knowing the number of txns, number of locks, etc.? I'd expect that in most cases the main cost is not going to be overhead from the lock manager but rather the cost of having transactions aborted due to conflicts. (But the rollback rate is extremely workload-dependent.) We've seen CPU overhead from the lock manager to be a few percent on a CPU-bound workload (in-memory pgbench). Also, if you're using a system with many cores and a similar workload, SerializableXactHashLock might become a scalability bottleneck. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- 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] Overhead cost of Serializable Snapshot Isolation
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 10.10.2011 21:25, Greg Sabino Mullane wrote: I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of read/write dependencies'? This is my only concern at the moment. Are we talking insignificant overhead? Minor? Is it measurable? Hard to say without knowing the number of txns, number of locks, etc.? I'm sure it does depend heavily on all of those things, but IIRC Kevin ran some tests earlier in the spring and saw a 5% slowdown. That feels like reasonable initial guess to me. If you can run some tests and measure the overhead in your application, it would be nice to hear about it. Right: the only real answer is it depends. At various times I ran different benchmarks where the overhead ranged from lost in the noise to about 5% for one variety of worst case. Dan ran DBT-2, following the instructions on how to measure performance quite rigorously, and came up with a 2% hit versus repeatable read for that workload. I rarely found a benchmark where the hit exceeded 2%, but I have a report of a workload where they hit was 20% -- for constantly overlapping long-running transactions contending for the same table. I do have some concern about whether the performance improvements from reduced LW locking contention elsewhere in the code may (in whack-a-mole fashion) cause the percentages to go higher in SSI. The biggest performance issues in some of the SSI benchmarks were on LW lock contention, so those may become more noticeable as other contention is reduced. I've been trying to follow along on the threads regarding Robert's work in that area, with hopes of applying some of the same techniques to SSI, but it's not clear whether I'll have time to work on that for the 9.2 release. (It's actually looking improbably at this point.) If you give it a try, please optimize using the performance considerations for SSI in the manual. They can make a big difference. -Kevin -- 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] COUNT(*) and index-only scans
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: I think what Robert is complaining about is that we won't currently consider an index that matches neither any WHERE clauses nor ORDER BY, ie, count(*) over the whole table won't get considered for an index-only scan, regardless of cost estimates. I guess the trick would be to get it to consider such plans only under some conditions, to avoid explosive growth in planning time for some types of queries. Some statistics bucket for the number of non-frozen tuples in the relation, maybe? My intention was to allow it to consider any covering index. You're thinking about the cost estimate, which is really entirely different. 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] [v9.2] Fix Leaky View Problem
2011/10/10 Robert Haas robertmh...@gmail.com:
On Sun, Oct 9, 2011 at 11:50 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote:
I tried to refactor the patches based on the interface of WITH (...)
and usage of
pg_class.reloptions, although here is no functionality changes; including the
behavior when a view is replaced.
My preference is WITH (...) interface, however, it is not a strong one.
So, I hope either of versions being reviewed.
I spent some more time looking at this, and I guess I'm pretty unsold
on the whole approach. In the part 2 patch, for example, we're doing
this:
+static bool
+mark_qualifiers_depth_walker(Node *node, void *context)
+{
+ int depth = *((int *)(context));
+
... snip ...
+ else if (IsA(node, RowCompareExpr))
+ {
+ ((RowCompareExpr *)node)-depth = depth;
+ }
+ return expression_tree_walker(node,
mark_qualifiers_depth_walker, context);
+}
It seems really ugly to me to suppose that we need to add a depth
field to every single one of these node types. If you've missed one,
then we have a security hole. If someone else adds another node type
later that requires this field and doesn't add it, we have a security
hole. And since all of these depth fields are going to make their way
into stored rules, those security holes will require an initdb to fix.
Indeed, I have to admit this disadvantage from the perspective of code
maintenance, because it had also been a tough work for me to track
the depth field in this patch.
If we make security views work like this, then we don't need to have
one mechanism to sort quals by depth and another to prevent them from
being pushed down through joins. It all just works. Now, there is
one problem: if snarf() were a non-leaky function rather than a
maliciously crafted one, it still wouldn't get pushed down:
Rather than my original design, I'm learning to the idea to keep
sub-queries come from security views; without flatten, because
of its straightforwardness.
If we make security views work like this, then we don't need to have
one mechanism to sort quals by depth and another to prevent them from
being pushed down through joins. It all just works. Now, there is
one problem: if snarf() were a non-leaky function rather than a
maliciously crafted one, it still wouldn't get pushed down:
I agreed. We have been on the standpoint that tries to prevent
leakable functions to reference a portion of join-tree being already
flatten, however, it has been a tough work.
It seems to me it is much simple approach that enables to push
down only non-leaky functions into inside of sub-queries.
An idea is to add a hack on distribute_qual_to_rels() to relocate
a qualifier into inside of the sub-query, when it references only
a particular sub-query being come from a security view, and
when the sub-query satisfies is_simple_subquery(), for example.
Anyway, I'll try to tackle this long standing problem with this
approach in the next commit-fest.
Thanks,
--
KaiGai Kohei kai...@kaigai.gr.jp
--
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] SET variable - Permission issues
On Mon, Oct 10, 2011 at 2:55 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Oct 10, 2011 at 2:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Any developer who can't think of six ways to DOS the server without changing those settings should be fired on the spot for incompetence. No kidding. But the point is that if the developer down the hall maliciously destroys your database server, you can go through channels and get him reprimanded or fired. But if the developer down the hall is a new hire who doesn't know beans about PostgreSQL and tries setting work_mem to 10GB, well, oops, it was an accident. And then another developer gets hired three weeks later and does the same thing. And then three months later somebody does it again. After a while people no longer remember that in each case it was a developer to blame. What they remember is that the DBA let the server go down three times. IOW, honest mistakes happen. Would it be possible to make the SUSET/USERSET property of a GUC modifiable? So a DBA can do ALTER USER novice SET CONTEXT OF work_mem TO 'superuser'; Or, maybe ALTER USER novice SET MAX_VAL OF work_mem TO '1 MB'; and extend it to say ALTER USER novice SET MIN_VAL OF statement_timeout TO '1'; -- So that the user cannot turn off the timeout ALTER DATABASE super_reliable SET ENUM_VALS OF synchronous_commit TO 'on'; -- So that the user cannot change the synchronicity of transactions against this database. Regards, -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] unite recovery.conf and postgresql.conf
On 10/10/11 10:52 AM, Josh Berkus wrote: So after debugging some of our failover scripts, here's the real-world problems I'm trying to solve. These design flaws are issues which cause automated failover or failback to abort, leading to unexpected downtime, so they are not just issues of neatness: That's automated failover or *manual* failback. I never, ever recommend automated failback. Just FYI. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Overhead cost of Serializable Snapshot Isolation
On Mon, Oct 10, 2011 at 02:59:04PM -0500, Kevin Grittner wrote: I do have some concern about whether the performance improvements from reduced LW locking contention elsewhere in the code may (in whack-a-mole fashion) cause the percentages to go higher in SSI. The biggest performance issues in some of the SSI benchmarks were on LW lock contention, so those may become more noticeable as other contention is reduced. I've been trying to follow along on the threads regarding Robert's work in that area, with hopes of applying some of the same techniques to SSI, but it's not clear whether I'll have time to work on that for the 9.2 release. (It's actually looking improbably at this point.) I spent some time thinking about this a while back, but didn't have time to get very far. The problem isn't contention in the predicate lock manager (which is partitioned) but the single lock protecting the active SerializableXact state. It would probably help things a great deal if we could make that lock more fine-grained. However, it's tricky to do this without deadlocking because the serialization failure checks need to examine a node's neighbors in the dependency graph. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- 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] Overhead cost of Serializable Snapshot Isolation
Dan Ports d...@csail.mit.edu wrote: I spent some time thinking about this a while back, but didn't have time to get very far. The problem isn't contention in the predicate lock manager (which is partitioned) but the single lock protecting the active SerializableXact state. It would probably help things a great deal if we could make that lock more fine-grained. However, it's tricky to do this without deadlocking because the serialization failure checks need to examine a node's neighbors in the dependency graph. Did you ever see much contention on SerializablePredicateLockListLock, or was it just SerializableXactHashLock? I think the former might be able to use the non-blocking techniques, but I fear the main issue is with the latter, which seems like a harder problem. -Kevin -- 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] SET variable - Permission issues
On 10/10/2011 01:52 PM, Gurjeet Singh wrote: On Mon, Oct 10, 2011 at 2:38 PM, Tom Lane wrote: Any developer who can't think of six ways to DOS the server without changing those settings should be fired on the spot for incompetence. Perhaps, but I think our long term goal at least should be to make it possible to prevent that -- not necessarily the default configuration, but it should be at least *possible* for a sufficiently careful DBA to harden their postgres instance. I have multiple clients that either do run or would like to run postgres multi-tenant, and at the moment that is somewhere between risky and unacceptable. Would it be possible to make the SUSET/USERSET property of a GUC modifiable? So a DBA can do ALTER USER novice SET CONTEXT OF work_mem TO 'superuser'; Or, maybe ALTER USER novice SET MAX_VAL OF work_mem TO '1 MB'; and extend it to say ALTER USER novice SET MIN_VAL OF statement_timeout TO '1'; -- So that the user cannot turn off the timeout ALTER DATABASE super_reliable SET ENUM_VALS OF synchronous_commit TO 'on'; -- So that the user cannot change the synchronicity of transactions against this database. I like this better than GRANT/REVOKE on SET. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 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] SET variable - Permission issues
Joe Conway m...@joeconway.com wrote: On 10/10/2011 01:52 PM, Gurjeet Singh wrote: ALTER USER novice SET MIN_VAL OF statement_timeout TO '1'; -- So that the user cannot turn off the timeout ALTER DATABASE super_reliable SET ENUM_VALS OF synchronous_commit TO 'on'; -- So that the user cannot change the synchronicity of transactions against this database. I like this better than GRANT/REVOKE on SET. +1 I would really like a way to prevent normal users from switching from the default transaction isolation level I set. This seems like a good way to do that. Putting sane bounds on some other settings, more to protect against the accidental bad settings than malicious mischief, would be a good thing, too. -Kevin -- 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] unite recovery.conf and postgresql.conf
On Mon, Oct 10, 2011 at 6:52 PM, Josh Berkus j...@agliodbs.com wrote: Tatsuo/Josh/Robert also discussed how recovery.conf can be used to provide parameters solely for recovery. That is difficult to do without causing all downstream tools to make major changes in the ways they supply parameters. Actually, this case is easily solved by an include recovery.conf parameter. So it's a non-issue. That is what I've suggested and yes, doing that is straightforward. If you mean do that in a program if we had a problem with adding parameters, we also have a problem adding an include. We should avoid breaking programs which we have no reason to break. Stability is good, change without purpose is not. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Overhead cost of Serializable Snapshot Isolation
On Mon, Oct 10, 2011 at 04:10:18PM -0500, Kevin Grittner wrote: Did you ever see much contention on SerializablePredicateLockListLock, or was it just SerializableXactHashLock? I think the former might be able to use the non-blocking techniques, but I fear the main issue is with the latter, which seems like a harder problem. No, not that I recall -- if SerializablePredicateLockListLock was on the list of contended locks, it was pretty far down. SerializableXactHashLock was the main bottleneck, and SerializableXactFinishedListLock was a lesser but still significant one. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- 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] Overhead cost of Serializable Snapshot Isolation
On Mon, Oct 10, 2011 at 8:30 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 10.10.2011 21:25, Greg Sabino Mullane wrote: I agree it is better versus SELECT FOR, but what about repeatable read versus the new serializable? How much overhead is there in the 'monitoring of read/write dependencies'? This is my only concern at the moment. Are we talking insignificant overhead? Minor? Is it measurable? Hard to say without knowing the number of txns, number of locks, etc.? I'm sure it does depend heavily on all of those things, but IIRC Kevin ran some tests earlier in the spring and saw a 5% slowdown. That feels like reasonable initial guess to me. If you can run some tests and measure the overhead in your application, it would be nice to hear about it. How do we turn it on/off to allow the overhead to be measured? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Overhead cost of Serializable Snapshot Isolation
Simon Riggs si...@2ndquadrant.com wrote: How do we turn it on/off to allow the overhead to be measured? User REPEATABLE READ transactions or SERIALIZABLE transactions. The easiest way, if you're doing it for all transactions (which I recommend) is to set default_transaction_isolation. -Kevin -- 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] [v9.2] Fix Leaky View Problem
On Sun, Oct 09, 2011 at 05:50:52PM +0200, Kohei KaiGai wrote: [patch v4] Each revision of this patch yielded a 1.2 MiB email. Please gzip attachments this large. The two revisions you sent in September constituted 18% of the pgsql-hackers bits for the month, and the next-largest message was only 315 KiB. Your mailer also picks base64 for textual attachments, needlessly inflating them by 37%. At the same time, the patch is large because it rewrites every line in pg_proc.h. Especially since it leaves proleakproof = 'f' for _all_ rows, consider instead using an approach like this: http://archives.postgresql.org/message-id/20110611211304.gb21...@tornado.leadboat.com These patches were not context diffs. Thanks, nm -- 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] [v9.2] Fix Leaky View Problem
On 10 October 2011 21:28, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2011/10/10 Robert Haas robertmh...@gmail.com: It seems really ugly to me to suppose that we need to add a depth field to every single one of these node types. If you've missed one, then we have a security hole. If someone else adds another node type later that requires this field and doesn't add it, we have a security hole. And since all of these depth fields are going to make their way into stored rules, those security holes will require an initdb to fix. Indeed, I have to admit this disadvantage from the perspective of code maintenance, because it had also been a tough work for me to track the depth field in this patch. Would you consider putting the depth field directly into a generic superclass node, such as the Expr node? Perhaps that approach would be neater. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Dumping roles improvements?
It occurs to me that we could really use two things to make it easier to move copies of database stuff around: pg_dump -r, which would include a CREATE ROLE for all roles needed to restore the database (probably without passwords), and pg_dumpall -r --no-passwords which would dump the roles but without CREATE PASSWORD statements. This would be useful for cloning databases for use in Dev, Test and Staging, where you don't what to copy the md5s of passwords for possible cracking. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] [v9.2] Fix Leaky View Problem
On Mon, Oct 10, 2011 at 4:28 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: I agreed. We have been on the standpoint that tries to prevent leakable functions to reference a portion of join-tree being already flatten, however, it has been a tough work. It seems to me it is much simple approach that enables to push down only non-leaky functions into inside of sub-queries. An idea is to add a hack on distribute_qual_to_rels() to relocate a qualifier into inside of the sub-query, when it references only a particular sub-query being come from a security view, and when the sub-query satisfies is_simple_subquery(), for example. If you can make this work, I think it could be a pretty sweet plannner optimization even apart from the implications for security views. Consider a query of this form: A LEFT JOIN B LEFT JOIN C where B is a view defined as: B1 JOIN B2 JOIN B3 LEFT JOIN B4 LEFT JOIN B5 Now let's suppose that from_collapse_limit/join_collapse_limit are set low enough that we decline to fold these subproblems together. If there happens to be a qual B.x = 1, where B.x is really B1.x, then the generated plan sucks, because it will basically lose the ability to filter B1 early, very possibly on, say, a unique index. Or at least a highly selective index. If we could allow the B.x qual to trickle down inside of the subquery, we'd get a much better plan. Of course, it's still not as good as flattening, because it won't allow us to consider as many possible join orders - but the whole point of having from_collapse_limit/join_collapse_limit in the first place is that we can't consider all the join orders without having planning time and memory usage balloon wildly out of control. And in many real-world cases, I think that this would probably mitigate the effects of exceeding from_collapse_limit/join_collapse_limit quite a bit. In order to make it work, though, you'd need to arrange things so that we distribute quals to rels in the parent query, then let some of them filter down into the subquery, then distribute quals to rels in the subquery (possibly adjusting RTE indexes?), then finish planning the subquery, then finish planning the parent query. Not sure how possible/straightforward that is. It's probably a good idea to deal with this part first, because if you can't make it work then the whole approach is in trouble. I'm almost imagining that we could break this into three independent patches, like this: 1. Let quals percolate down into subqueries. 2. Add the notion of a security view, which prevents flattening and disables the optimization of patch #1 3. Add the notion of a leakproof function, which can benefit from the optimization of #1 even when the view involved is a security view as introduced in #2 Unlike the way you have it now, I think those patches could be independently committable. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Range Types - typo + NULL string constructor
On Oct10, 2011, at 20:06 , Thom Brown wrote: Okay, a real example of why discrete should be '[]' and continuous should be '[)'. If you book a meeting from 09:00 to 11:00 (tsrange), at 11:00 precisely it either becomes free or is available to someone else, so it can be booked 11:00 to 12:00 without conflict. If you have raffle tickets numbered 1 to 100 (int4range), and you ask for tickets 9 to 11, no-one else can use 11 as it aligns with the last one you bought. So for me, it's intuitive for them to behave differently. So yes, default behaviour would vary between types, but I didn't previously read anything on default_flags, so I don't know where that comes into it. Shouldn't it be the case that if a type has a canonical function, it's entirely inclusive, otherwise it's upper boundary is exclusive? I'm not convinced by this. The question here is not whether which types of ranges we *support*, only which type we consider to be more *canonical*, and whether the bounds provided to a range constructor are inclusive or exclusive by *default*. Maybe ranges over discrete types are slightly more likely to be closed, and ranges over continuous types slightly more likely to be open. Still, I very much doubt that the skew in the distribution is large enough to warrant the confusion and possibility of subtle bugs we introduce by making the semantics of a range type's constructor depend on the definition of the range and/or base type. Especially since we're talking about only *6* extra characters to communicate the intended inclusivity/exclusivity of the bounds to the range constructor. Also, the distinction between types for which ranges should obviously be closed, and those for which they should obviously be half-open is nowhere as clear-cut as it seems at first sight. First, there's the type date, which in my book is discrete. So we'd make date ranges closed by default, not half-open. And there's timestamp, which is continuous so we'd make its default half-open. That doesn't seem exactly intuitive to me. Second, there's int4 and float8, one discrete, one continuous. So would we make int4range(1, 2) include 2, but float8range(1.0, 2.0) *not* include 2.0? best regards, Florian Pflug -- 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] Range Types - typo + NULL string constructor
On Oct10, 2011, at 19:41 , Jeff Davis wrote: On Mon, 2011-10-10 at 19:22 +0200, Florian Pflug wrote: I still think we should strive for consistency here, so let's also make '[]' the default flags for the range constructors. For continuous ranges I don't think that's a good idea. Closed-open is a very widely-accepted convention and there are good reasons for it -- for instance, it's good for specifying contiguous-but-non-overlapping ranges. It really depends on what you're using ranges for. Yeah, if you're convering something with ranges (like mapping things to a certain period of time, or an area of space), then half-open ranges are probably very common. If, OTOH, you're storing measurement with error margins, then open ranges, i.e. '()', are probably what you want. I still firmly believe that consistency trumps convenience here. Specifying the range boundaries' exclusivity/inclusivity explicitly is quite cheap... So, I think we either need to standardize on '[)' or allow different default_flags for different types. Or, always specify the inclusivity in the constructor (hopefully in a convenient way). In the light of Tom's argument, my pick would be '[]'. It's seem strange to normalize ranges over discrete types to closed ranges, yet make the construction function expect open boundaries by default. best regards, Florian Pflug -- 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] Bug in walsender when calling out to do_pg_stop_backup (and others?)
On Oct10, 2011, at 21:25 , Magnus Hagander wrote: On Thu, Oct 6, 2011 at 23:46, Florian Pflug f...@phlo.org wrote: It'd be nice to generally terminate a backend if the client vanishes, but so far I haven't had any bright ideas. Using FASYNC and F_SETOWN unfortunately sends a signal *everytime* the fd becomes readable or writeable, not only on EOF. Doing select() in CHECK_FOR_INTERRUPTS seems far too expensive. We could make the postmaster keep the fd's of around even after forking a backend, and make it watch for broken connections using select(). But with a large max_backends settings, we'd risk running out of fds in the postmaster... Ugh. Yeah. But at least catching it and terminating it when we *do* notice it's down would certainly make sense... I'll try to put together a patch that sets a flag if we discover a broken connection in pq_flush, and tests that flag in CHECK_FOR_INTERRUPTS. Unless you wanna, of course. best regards, Florian Pflug -- 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] COUNT(*) and index-only scans
On Mon, Oct 10, 2011 at 10:36 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Bruce Momjian br...@momjian.us wrote: I talked to Robert Haas and he said that index-only scans do not optimize COUNT(*). Is this something we can do for PG 9.2? Is anyone working on this? Well, it's not that it doesn't optimize COUNT(*) -- it's that it doesn't yet cost the index scan as cheaper than a table scan when you're accessing every row. create table t (id int not null primary key); insert into t select generate_series(1, 100); vacuum freeze analyze; explain analyze select count(*) from t where id between 50 and 500010; That gives you an index-only scan; but without the WHERE clause it uses a seq scan. If you convert the where clause to where id is not null it uses the index only scan again, but only if you nudge it too with enable_seqscan=off. I'm not sure why it needs the nudge in one case but not the other. 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] Dumping roles improvements?
Hello 2011/10/11 Josh Berkus j...@agliodbs.com: It occurs to me that we could really use two things to make it easier to move copies of database stuff around: pg_dump -r, which would include a CREATE ROLE for all roles needed to restore the database (probably without passwords), and pg_dumpall -r --no-passwords which would dump the roles but without CREATE PASSWORD statements. This would be useful for cloning databases for use in Dev, Test and Staging, where you don't what to copy the md5s of passwords for possible cracking. maybe some switch for complete dump of one database with related roles. Pavel -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] index-only scans
I wrote: I have mostly-working code for approach #3, but I haven't tried to make EXPLAIN work yet. While looking at that I realized that there's a pretty good argument for adding the above-mentioned explicit TargetEntry list representing the index columns to index-only plan nodes. Namely, that if we don't do it, EXPLAIN will have to go to the catalogs to find out what's in that index, and this will fall down for hypothetical indexes injected into the planner by index advisors. We could imagine adding some more hooks to let the advisor inject bogus catalog data at EXPLAIN time, but on the whole it seems easier and less fragile to just have the planner include a data structure it has to build anyway into the finished plan. The need for this additional node list field also sways me in a direction that I'd previously been on the fence about, namely that I think index-only scans need to be their own independent plan node type instead of sharing a node type with regular indexscans. It's just too weird that a simple boolean indexonly property would mean completely different contents/interpretation of the tlist and quals. Attached is a draft patch for this. It needs some more review before committing, but it does pass regression tests now. One point worth commenting on is that I chose to rename the OUTER and INNER symbols for special varnos to OUTER_VAR and INNER_VAR, along with adding a new special varno INDEX_VAR. It's bothered me for some time that those macro names were way too generic/susceptible to collision; and since I had to look at all the uses anyway to see if the INDEX case needed to be added, this seemed like a good time to rename them. regards, tom lane binInhGxDA2ea.bin Description: index-only-scan-revisions.patch.gz -- 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] COUNT(*) and index-only scans
On Mon, Oct 10, 2011 at 9:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: My intention was to allow it to consider any covering index. You're thinking about the cost estimate, which is really entirely different. Is there any reason to consider more than one? I would have expected the narrowest one to be the best choice. There's something to be said for using the same index consistently but we already have that problem and make no attempt to do that. And partial indexes might be better but then we would already be considering them if their constraints are satisfied. -- greg -- 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] COUNT(*) and index-only scans
Jeff Janes wrote: Kevin Grittner wrote: create table t (id int not null primary key); insert into t select generate_series(1, 100); vacuum freeze analyze; explain analyze select count(*) from t where id between 50 and 500010; That gives you an index-only scan; but without the WHERE clause it uses a seq scan. If you convert the where clause to where id is not null it uses the index only scan again, but only if you nudge it too with enable_seqscan=off. Clever way to get a full-table test. It turns out that for the above, with your trick to use the index only scan, it comes out 12% faster to do a seqscan, even when the table and index are fully cached (based on the average time of ten runs each way). There's very little overlap, so the difference looks real. But that's on a very narrow record, having just the one column used in the index. I added one wide column like this: alter table t add column x text; update t set x = (repeat(random()::text, (random() * 100)::int)); cluster t USING t_pkey; vacuum freeze analyze; With that change the index-only scan time remained unchanged, while the seqscan time grew to about 2.6 times the index only scan time. That was mildly surprising for me, considering it was all still cached. -Kevin -- 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] unite recovery.conf and postgresql.conf
On Tue, Oct 11, 2011 at 6:37 AM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Oct 10, 2011 at 6:52 PM, Josh Berkus j...@agliodbs.com wrote: Tatsuo/Josh/Robert also discussed how recovery.conf can be used to provide parameters solely for recovery. That is difficult to do without causing all downstream tools to make major changes in the ways they supply parameters. Actually, this case is easily solved by an include recovery.conf parameter. So it's a non-issue. That is what I've suggested and yes, doing that is straightforward. Even if we do that, you still need to modify the tool so that it can handle the recovery trigger file. recovery.conf is used as just a configuration file (not recovery trigger file at all). It's not renamed to recovery.done at the end of recovery. If the tool depends on the renaming from recovery.conf to recovery.done, it also would need to be modified. If the tool needs to be changed anyway, why do you hesitate in changing it so that it adds include recovery.conf into postgresql.conf automatically? Or you think that, to keep the backward compatibility completely, recovery.conf should be used as not only a configuration file but also a recovery trigger one and it should be renamed to recovery.done at the end of recovery? 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] Range Types - typo + NULL string constructor
On Tue, 2011-10-11 at 03:14 +0200, Florian Pflug wrote: Maybe ranges over discrete types are slightly more likely to be closed, and ranges over continuous types slightly more likely to be open. Still, I very much doubt that the skew in the distribution is large enough to warrant the confusion and possibility of subtle bugs we introduce by making the semantics of a range type's constructor depend on the definition of the range and/or base type. I think you persuaded me on the consistency aspect. I'm wondering whether to do away with the default argument entirely, and just force the user to always specify it during construction. It seems like a shame that such pain is caused over the syntax, because in a perfect world it wouldn't be a bother to specify it at all. I even considered using prefix/postfix operators to try to make it nicer, but it seems like every idea I had was just short of practical. Maybe a few extra characters at the end aren't so bad. I'd like to hear from some potential users though to see if anyone recoils at the common case. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
