Re: [HACKERS] WIP Patch for GROUPING SETS phase 1
Oh, and I build it on top of f92fc4c95ddcc25978354a8248d3df22269201bc On 20-04-2015 10:36, Svenne Krap wrote: The following review has been posted through the commitfest application: make installcheck-world: tested, failed Implements feature: tested, passed Spec compliant: not tested Documentation:tested, passed Hi, I have (finally) found time to review this. The syntax is as per spec as I can see, and the queries I have tested have all produced the correct output. The documentation looks good and is clear. I think it is spec compliant, but I am not used enough to the spec to be sure. Also I have not understood the function of set quantifier (DISTINCT,ALL) part in the group by clause (and hence not tested it). Hence I haven't marked the spec compliant part. The installcheck-world fails, but in src/pl/tcl/results/pltcl_queries.out (a sorting problem when looking at the diff) which should be unrelated to GSP. I don't know enough of the check to know if it has already run the GSP tests.. I have also been running a few tests on some real data. This is run on my laptop with 32 GB of memory and a fast SSD. The first dataset is a join between a data table of 472 MB (4,3 Mrows) and a tiny multi-column lookup table. I am returning a count(*). Here the data is hierarchical so CUBE does not make sense. GROUPING SETS and ROLLUP both works fine and if work_buffers are large enough it slightly beats the handwritten union all equivalent (runtimes as 7,6 seconds to 7,7 seconds). If work_buffers are the default 4MB the union-all-equivalent (UAE) beats the GS-query almost 2:1 due to disk spill (14,3 (GS) vs. 8,2 (UAE) seconds). The other query is on the same datatable as before, but with three columns (two calculated and one natural) for a cube. I am returning a count(*). First column is extract year from date column Second column is divide a value by something and truncate (i.e. make buckets) Third column is a litteral integer column. Here the GS-version is slightly slower than the UAE-version (17,5 vs. 14,2). Nothing obvious about why in the explain (analyze,buffers,costs,timing) . I have the explains, but as the dataset is semi-private and I don't have any easy way to edit out names in it, I will send it on request (non-disclosure from the recipient is of course a must) and not post it on the list. I think the feature is ready to be commited, but am unsure whether I am qualified to gauge that :) /Svenne The new status of this patch is: Ready for Committer -- 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 Patch for GROUPING SETS phase 1
The following review has been posted through the commitfest application: make installcheck-world: tested, failed Implements feature: tested, passed Spec compliant: not tested Documentation:tested, passed Hi, I have (finally) found time to review this. The syntax is as per spec as I can see, and the queries I have tested have all produced the correct output. The documentation looks good and is clear. I think it is spec compliant, but I am not used enough to the spec to be sure. Also I have not understood the function of set quantifier (DISTINCT,ALL) part in the group by clause (and hence not tested it). Hence I haven't marked the spec compliant part. The installcheck-world fails, but in src/pl/tcl/results/pltcl_queries.out (a sorting problem when looking at the diff) which should be unrelated to GSP. I don't know enough of the check to know if it has already run the GSP tests.. I have also been running a few tests on some real data. This is run on my laptop with 32 GB of memory and a fast SSD. The first dataset is a join between a data table of 472 MB (4,3 Mrows) and a tiny multi-column lookup table. I am returning a count(*). Here the data is hierarchical so CUBE does not make sense. GROUPING SETS and ROLLUP both works fine and if work_buffers are large enough it slightly beats the handwritten union all equivalent (runtimes as 7,6 seconds to 7,7 seconds). If work_buffers are the default 4MB the union-all-equivalent (UAE) beats the GS-query almost 2:1 due to disk spill (14,3 (GS) vs. 8,2 (UAE) seconds). The other query is on the same datatable as before, but with three columns (two calculated and one natural) for a cube. I am returning a count(*). First column is extract year from date column Second column is divide a value by something and truncate (i.e. make buckets) Third column is a litteral integer column. Here the GS-version is slightly slower than the UAE-version (17,5 vs. 14,2). Nothing obvious about why in the explain (analyze,buffers,costs,timing) . I have the explains, but as the dataset is semi-private and I don't have any easy way to edit out names in it, I will send it on request (non-disclosure from the recipient is of course a must) and not post it on the list. I think the feature is ready to be commited, but am unsure whether I am qualified to gauge that :) /Svenne The new status of this patch is: Ready for Committer -- 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 Patch for GROUPING SETS phase 1
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 18-03-2015 17:18, Svenne Krap wrote: I still need to check against the standard and I will run it against a non-trivival production load... hopefully I will finish up my review shortly after the weekend... I am still on it, but a little delayed. I hope to get it done this weekend. Svenne -- 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 Patch for GROUPING SETS phase 1
The following review has been posted through the commitfest application: make installcheck-world: tested, failed Implements feature: tested, passed Spec compliant: not tested Documentation:tested, passed This is a midway review, a later will complete it. The patch applies against 8d1f239003d0245dda636dfa6cf0add13bee69d6 and builds correctly. Make installcheck-world fails, but it seems to be somewhere totally unrelated (TCL pl)... The documentation is very well-written and the patch implements the documented syntax. I still need to check against the standard and I will run it against a non-trivival production load... hopefully I will finish up my review shortly after the weekend... -- 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 Patch for GROUPING SETS phase 1
Patch from message (87d24iukc5@news-spur.riddles.org.uk) fails (tried to apply on top of ebc0f5e01d2f ), as b55722692 has broken up the line (in src/backend/optimizer/util/pathnode.c): pathnode-path.rows = estimate_num_groups(root, uniq_exprs, rel-rows); After patching the added parameter (NULL) in by hand, the build fails as src/backend/optimizer/path/indxpath.c:1953 misses the new argument as well - this change is not in the patch. /Svenne The new status of this patch is: Waiting on Author -- 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] Git-master regression failure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 18-06-2013 22:18, Jeff Janes wrote: In Danish, apparently 'AA' 'WA', so two more rows show up. Yes of course We have three extra vowels following Z (namely Æ, Ø and Å) and for keyboard missing those essential keys we have an official alternate way to write them as AE , OE and AA. Which of course means that AA is larger than any other letter ;) Nice find :) Svenne -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.20 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQI5BAEBCAAjBQJRwUjzHBpodHRwOi8vc3Zlbm5lLmRrL3BncC9wb2xpY3kACgkQ /zLSj+olL/LfqxAAqZ6Qc9uC7lZ/gE/ZT5RjMqEudQvHvRxGiUQLcLdif/n2novf yf3Phe7gJOMPgt7V47dT5Wu/l3/PunV6Yo2Q1Ifg+XsrvUXC4CDMLo/RDP97EUwZ B96o1UH2Tx1+GnaJrZkcjq5V8y+4X5QJ6+vP11SEUyuMl7Ist2EegMet55N3ndTe W/mjZr5uTpmEyBr1aMJo6RJ3T1gxPLelc2b4aA5jnQlO7RbXlCgtWUQzQ/Q/dBWb kuE0MEd9CSR4kuo9u6yImVKUMUdX+NGgOeD6D36UtS4pdvnWOI+gX95Mze4AfiY/ ZPt6mCv0afBfQW/uioctHDLScY9v6cT58dkzrLjWyuNd6CfvqBiWfXBygwUclDZK cWpuszwIjVaOnHiDlHK8sg7yXU+FkhV5V8PXz6KBulCPxmR+Z8oI0AKiU2MhRIe7 mQhgDHgHT6nBpqWGlmX3VAjiTL6+/DZT34TnxNqdLDtWKrHUK+2KzZ/kYJ97zwo4 C78UX6F+1/6JxOkMVjm77M1ZQx306pJC8YGS3+7/fRh1EZd3LQXPaOXCU2EtM6UL sBaafDB5yMEZsdNw3DKOLnbg/bTq9MPa2uBZdEJ1w8LsvNrSeOohTXZFn2PL2zoJ L8JnJcAndM3SJG+UO1K1ALJhZlD0oRovYnzolAlKCXavNTn+zBd2uyNYSIQ= =qC25 -END PGP SIGNATURE- -- 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] Git-master regression failure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 19-06-2013 17:41, Kevin Grittner wrote: OK, pushed without the comment. Works like a charm :) Svenne -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.20 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQI5BAEBCAAjBQJRwhpmHBpodHRwOi8vc3Zlbm5lLmRrL3BncC9wb2xpY3kACgkQ /zLSj+olL/LBzRAAje+BsZ6eGbNEXwPWPob0sJwAD+51UNT24DMHCLAg4T38Bheo HaT+xSQm3hlIIjL/JF5BLiRFG4Fz1+GGOGbykKRW1Fm+VWzw6934lajbh/Z7wqzO T+n0XXwhbMnA4O++FvIJ67C7iazsNbAobOmqwjPe+6D6ID8VR9rNB/NEwelueR3L dVmVrSg1Mc8DVHS2h7Aq4bJaeCJHOQstB89+AEa14cwsJ25PiQFcdbUWsNNqbnoE TN3jQlXZLAl/qCzdKQ7FRAsi7Yk1r9uU7g1HNSY7ggh+W5KmRNqX99LS4v5mO6rf XquzXNf6nzl3ngfucB+60C2NloqieYKGVbzDh7LjCThbv284/fdlW+Wu80vYGcto TWIb5Ku4fbKP0Hmcuby1VlcYyZhyiBa76zV7JF5AtMe9yfCpOdmyAUdHDQUq6T6c /BVV/bCVsDAhJN/AJQC2itNnt4G+Bw62YEQCwuw1cNT5BUzjf0mbcer5kRchkNr3 lBdTEp4HBzElBDnCY7oSre+aSKomgNZwyJsps2aLuyz8nTj4WxpDQTDo1m7Kyi7K dFGRZLJRy5FS0xNrVtBW1cfoYy8B2GM9cmcZru5bFo2XAJfJaSSl4EZLdlIk5k66 k+VkwS5XoIl7oR/qT1fSwERM8TfDS2An2rr+jvVAATm2QQsKL42AN7aSROk= =2COi -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Git-master regression failure
Hi All. I just subscribed to RRReviewers (that should be pronounce with a nice rolling r-r-reviewers, right?) As part of my getting up to speed, I tried to build and run test on the current master 073d7cb513f5de44530f4bdbaaa4b5d4cce5f984 Basically I did: 1) Clone into new dir 2) ./configure --enable-debug --enable-cassert --with-pgport=5499 --prefix=$(realpath ../root) 3) make -j4 4) maje -j4 check And expecting in 4) to get all test passed, I was surprised to see that an index-test failed. I run (Gentoo) Linux x86_64, gcc-4.7.3. Any ideas what might have happened? Svenne *** /home/sk/tmp/pgsql-test/source/src/test/regress/expected/create_index.out 2013-06-18 17:38:03.411169360 +0200 --- /home/sk/tmp/pgsql-test/source/src/test/regress/results/create_index.out 2013-06-18 18:36:18.178373329 +0200 *** *** 2673,2679 WHERE f1 'WA' and id 1000 and f1 ~~ 'YX'; count --- ! 97 (1 row) -- --- 2673,2679 WHERE f1 'WA' and id 1000 and f1 ~~ 'YX'; count --- ! 99 (1 row) -- == -- 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] Git-master regression failure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 18-06-2013 18:40, Svenne Krap wrote: Any ideas what might have happened? After doing some more digging... My laptop (which runs PostgreSQL 9.2.4 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (Gentoo 4.7.3 p1.0, pie-0.5.5) 4.7.3, 64-bit) also returns 99, if I - - run the CREATE TABLE tenk1 (from the git-master) - - load data from tenk.data (from git-master) - - run the offending part of the create_index.sql (also from git-master): The offending offending_part is: CREATE TABLE dupindexcols AS SELECT unique1 as id, stringu2::text as f1 FROM tenk1; CREATE INDEX dupindexcols_i ON dupindexcols (f1, id, f1 text_pattern_ops); ANALYZE dupindexcols; EXPLAIN (COSTS OFF) SELECT count(*) FROM dupindexcols WHERE f1 'WA' and id 1000 and f1 ~~ 'YX'; SELECT count(*) FROM dupindexcols WHERE f1 'WA' and id 1000 and f1 ~~ 'YX'; As I have no real idea of what ~~ is for an operator (I have looked it up as scalarltjoinsel), but I cannot find any semantics for it in the docs*... So I have no way of manually checking the expected result. *=The term ~~ is not exactly google-friendly and the docs site's search also returns empty... Anyone has any idea what to look after next? Svenne -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.20 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQI5BAEBCAAjBQJRwKTQHBpodHRwOi8vc3Zlbm5lLmRrL3BncC9wb2xpY3kACgkQ /zLSj+olL/KGHQ//d5WM2gi4yIaMCpaij1+PdCzBwyMwz0E3Ys4mlj1x7x22v8ty 6Gs6HZlofuuUdBkhDvRYYvbJ8asdi51KbjVMbrBkR49txiM00cWLtT74e9mFdTaA f5lOeUmfyqy7O9jlyFvKaC8hVRe7yQrbRK+b2sOBIq2TchVkvjT/AvvDcuPWCKD7 6FgHOtFF4Ae4yqC6foylfBQ59TpOYHBd+ohl38egtDr87tXSXcuIU1bNZeJfqcPM rs02h8L/kmWrx32TmYKLUw6PdluPEJ2WB5Gcsd8Va5p7ai3+rbqzzJGw9ZOFkPv4 9ruTqjTV0l7xhwuztHpSCTiyOoV0dIw13USTO7D/rAseqIUgb0mQE4cXVQW9GVT1 WjCqnaqRwdgYnvke7jtOfX2AeiZjluZfF1e2B2rVMfDan1A5PxjLmxpzH7fYs0bs RDEnOx9bcVUYH6xedVyYHbSmx7GXyzu0xfADsWgsYNclv91cZ5pz9+fwJv8ceNNz ckOcA4DlKeo1fw0idvbEUtVWf/tO8H6r0heNuMgR5M6qOtlbsLsdsJvOIUTzGn7W oxikvMAKbAX0Hl4arQSQE5Og7HcLsEB6YaMp0bpj55Y4prr/fhzJHKL4ioCI6NOH gLknu+IPDyJiNez4QqaqAITDtxo+68euv5uUE+O55o9ssBEWoTX0bz8trM8= =2IBo -END PGP SIGNATURE- -- 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] Git-master regression failure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 18-06-2013 20:17, Kevin Grittner wrote: I was surprised to see that an index-test failed. It works for me. Could you paste or attach some detail? Gladly, if you tell me what would be relevant to attach :) I am brand new to the postgresql source code and hence have no real idea how to catch it.. Svenne -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.20 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQI5BAEBCAAjBQJRwKjRHBpodHRwOi8vc3Zlbm5lLmRrL3BncC9wb2xpY3kACgkQ /zLSj+olL/LIOQ/9E7PsAosZbQemCKeHj3/61kM1m3WMD9YtjZE4OdAJjEM+YCpE HkMheIpsIBo9rV1mH0mNt/rog0GZupGA9xWo+OsRPbQ3bd089ny++eSmGaJ4+nb3 cZiJpGizgQ+8xrMFLtkyt72WFUWashhb6IdokC1WeSrOWOcXGad+Sl48nxkODetR WJHuq0cdzLkivLSXBE3I5a7h28QWAwUHRlOUFNLaVTbVtPiv9Rx8YAnzYCvr3HC1 y+tdrHxONjoM4moLJH8qRQs6nMTCq+3mJgaKjW8FPCf5is5IMZLoACvjf0TOZm9o NnGv/R0TwLVjA/w44qMNJ4kXoX49IDX1IaYBvOraAjjWr+ggC2bmXwPZWqEHAZTG OCLD/t7DONW7uQEXEZSyur1N3CIZ231jl/ufYSefXaV81mtBVF6w7EVLi56UsC1A C626RY0r/87P9Y0avG1oh3ba6hOZhcv2R8GZZKkO7LNGUMSbIm90+FcV3bv/YSKi H5T3nYM0GWJ6kbqt+Mynqy9Q2N/33/rcpAL3ut0wIRkGALuIrb5zowi52y95+zAX ePpMFbM8I8nHuM0ouvcABHXtr6r5m6c1qwfJNbNQuSFz1T3s4/SY2W1IsinLSSKT /bqxP4qJxONDrIv9W4ZpIkxLYyfTHTuD3jP7GzndKcGHtHzMrTqxf1RGBs0= =hhm0 -END PGP SIGNATURE- -- 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] Git-master regression failure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 18-06-2013 20:48, Kevin Grittner wrote: Apologies; I somehow missed the file attached to your initial post. That's the sort of thing I was looking for. Aplogy accepted... :) Having reviewed that, the source code comments indicate it is for character-by-character (not collation order) comparison operators for character types. Perhaps your collation is having an impact regardless of that. Could you show us the values of your settings related to locale? I am not entirely sure what you mean by settings related to locale ... but here is the system locale output and the definition of my main instance (the 9.2 one I wrote about in the second test)...I don't know if/how to extract the same information from the temp instance made by make check. Btw. could you point a newbie to where you found the function (so that I know that for later)? If you need other data, please be more specific about how I get them :) # locale LANG=da_DK.UTF8 LC_CTYPE=da_DK.UTF8 LC_NUMERIC=da_DK.UTF8 LC_TIME=da_DK.UTF8 LC_COLLATE=da_DK.UTF8 LC_MONETARY=da_DK.UTF8 LC_MESSAGES=en_US.UTF8 LC_PAPER=da_DK.UTF8 LC_NAME=da_DK.UTF8 LC_ADDRESS=da_DK.UTF8 LC_TELEPHONE=da_DK.UTF8 LC_MEASUREMENT=da_DK.UTF8 LC_IDENTIFICATION=da_DK.UTF8 LC_ALL= (sk@[local]:5432) [sk] \l List of databases Name | Owner | Encoding | Collate | Ctype| Access privileges - -+--+--+++--- postgres| postgres | UTF8 | da_DK.UTF8 | da_DK.UTF8 | root| root | UTF8 | da_DK.UTF8 | da_DK.UTF8 | sk | sk | UTF8 | da_DK.UTF8 | da_DK.UTF8 | template0 | postgres | UTF8 | da_DK.UTF8 | da_DK.UTF8 | =c/postgres + | | ||| postgres=CTc/postgres template1 | postgres | UTF8 | da_DK.UTF8 | da_DK.UTF8 | =c/postgres + | | ||| postgres=CTc/postgres (5 rows) -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.20 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQI5BAEBCAAjBQJRwK87HBpodHRwOi8vc3Zlbm5lLmRrL3BncC9wb2xpY3kACgkQ /zLSj+olL/JVBQ//UQfke7jc2mcaTV9lvdU8b5B/SnGzsn8d2M2mDPYjcgjmuAmT pacRemmiVf8w6FTKlmz+A7faYVMuFSjJXi25OzvWu8on+zFtstdHWBbMNYI1CuBP itSqs+JVYF8FvkAa+iC9KilV1McRWCOxnlztuN7F29lzpnrcWsGmvhiYL3qVt3TT jyJNTdVIOFGta4f4Kzkq9ZqH6ry42UrXRFa1pFRxfY42zX7LV3BsJuEMQeHnRYjz fkcivuwJJHdn9l6GGZbF4+s227SFOO98GiPah10n5GKzy2saAMntos1p15l5Qy/c TaHE5cp0fttaQMbQEMWB3fY6r3NETxnsDyh6z7EaLV/WHhmzjrUGDp2gCgWbVs5C nvoFL9SkcfzZsuWvy21zmIHOvzAUMm9p57/i5REWqWc2ZH5giLKC71m7skiu/utF 9fmpNEh1U+DG3/VnJjcIyfNq4y3pmkNN063/aLKX21vvBHJ9/oqgL7czYRCBZH6L ufNL0ACZ/zeDTL16K58/gMhf7Si9jAZq7PcobIgSJHKo++7DevgUiSRmbxQ6jV6X ZXseeQJT7Dcf3yVNqNkPJApSiCbR4e0wbgbSPXXkJEjZpo+k3wo3gZI6fEULifdh i/hcLpq+nPrME2l9gcneJ8gNHpBOK4AyfBvfhxOIV35pCpYrBvk3toKPUEc= =bUcn -END PGP SIGNATURE- -- 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] Git-master regression failure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 18-06-2013 21:04, Svenne Krap wrote: (sk@[local]:5432) [sk] \l List of databases Name | Owner | Encoding | Collate | Ctype| Access privileges - Arghh... crappy mailer... I have the information attached here instead... -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.20 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQI5BAEBCAAjBQJRwLACHBpodHRwOi8vc3Zlbm5lLmRrL3BncC9wb2xpY3kACgkQ /zLSj+olL/LJQw//avuPNPvG9sTy+itx9pmdRBHrdWCER4TXyKu15/0cphigMCHo mLNuLfcdwEbuppaqZgrEKP9GicppExmnGHGdgwn60CQiavki9HZpxuH+FX/+DRI6 IEM7Ml6PZw+8g+Uvh26vprgBhFw2B2xxx9uDEN5YEmpjJPqgxh8SXIs7Sy14FmEw uivU+YVLdoqLGUfSO3kng/ypTwqWC7vdycvZM6DtJdJLeZf+444kpX4TlVAncYtG LKRm7ym+9sUY5Tkq/cKNS/hM93fnrGGyD0bg6GtTHUKq0S0Cw79FcvCgWBNu0fOe QU6AtNIslvpxarECN2fkJ02S8pf0lnrDm8l6ZExO3BgY/Fo0wSPo7rhtdFEgPB6d uPy4YL9ezRfY7gkXWEgIX1zNA0h6vVcNi5OqbyC5WHenvI9mQPiwgwvbXTqCQmEF SKC6PoBOaJXaCajyXGVSjk+0IC/QYSMsqoWe1CcySFahfYFxKdHbkImQ02N+PB3x 9ABBuG2QUHTmh09MsCtr/l+McqlLVCPRngV5wYz5Z7guKu1lS6yzaAj6VHxVaw1A wCmVtJcCFNv/WhbarLOJOMsPjrQ5EXIOuM+k61nznooaqnBtChQTT1/ddzx+G3vU 0lX59b7tAM9+z2q+VCj57L6DgspCODMwHucv2MYkInS++ta0QV0U5ebzw/s= =/PiO -END PGP SIGNATURE- Expanded display is used automatically. Null display is NULL. Timing is on. List of databases Name | Owner | Encoding | Collate | Ctype| Access privileges -+--+--+++--- postgres| postgres | UTF8 | da_DK.UTF8 | da_DK.UTF8 | root| root | UTF8 | da_DK.UTF8 | da_DK.UTF8 | sk | sk | UTF8 | da_DK.UTF8 | da_DK.UTF8 | template0 | postgres | UTF8 | da_DK.UTF8 | da_DK.UTF8 | =c/postgres + | | ||| postgres=CTc/postgres template1 | postgres | UTF8 | da_DK.UTF8 | da_DK.UTF8 | =c/postgres + | | ||| postgres=CTc/postgres (5 rows) pgdb-locale.txt.sig Description: PGP signature -- 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] Git-master regression failure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 18-06-2013 21:14, Jeff Janes wrote: But 9.2.4 does pass make check, and only fails if you reproduce those things manually? No, I was lazy and used the (distribution-installed) 9.2 I have tried make check on REL_9_2_4 and that fails to (same sole failure)... If so, I'm guessing that you have some language/locale settings that make check neutralizes in 9.2.4, but that neutralization is broken in HEAD. Nope, just never ran make check on it... As I have no real idea of what ~~ is for an operator (I have looked it up as scalarltjoinsel), but I cannot find any semantics for it in the docs*... So I have no way of manually checking the expected result. Yes, it does seem to be entirely undocumented. Using: git grep '~~', I found the code comment character-by-character (not collation order) comparison operators for character types Anyway, if REL9_2_4 passes make check, but 073d7cb513f5de44530f fails, then you could use git bisect to find the exact commit that broke things. It does not.. I will dig futher and get back... Svenne -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.20 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQI5BAEBCAAjBQJRwLfnHBpodHRwOi8vc3Zlbm5lLmRrL3BncC9wb2xpY3kACgkQ /zLSj+olL/J4oA/7Blq1eQarny6VVu7UUgtN+CWvy3zoiE36AlE/vEvzb4aXWP4c 5Mq8z3vkHrjKCdaioGYLrkEE2PTom+pBV5cK+cT67TAN1J/OJErtRuKhVLk2Bd8I ry/gDgF8dvM0Sx03eN52ViCOjFUmksk+HXOSk8z0aPBdanbOdIQoSC25XJ2Yye8K DP+yY6h9+PxMomMzaz3aK0MechZaJyvbLpStjJEd/nuAiDIymzpzxJfyjpn/jKra 6GVdRfQCMkqiBy5E4YFgxDkGfDJLFtwMJPMw8OAe+Zhgp8YLPSms58bwtdmwcPYD NSfowZa/yaKpLP3k6J/2JVToa+JBPY/vYGlxdU+h15VmsDV8kxCWnRwxC/gYYg9l 7CqWPZskd9ZAonfb781JxoiVmoGbGzFCLE1wlPVazCZsAzQcvVTJD8yl/Ibv8PKv 7LjxuItm3iW8GAp93x7+MGmxKx/YKVu8lfAFTw2/X/G2MfoiJHmAUaklqdATerXl xarQOL0UbiRGQNYSBHjKVxZHQrgAShPS2O2cSw6MijaDsr4US+pcY+S0F0KmtnD/ mobI3vSFFsZxnaIUAL4ExQuyF0PgGm7Ce4jmQxsUx7Ph/ud6AZVLhKStYBcwChPH W+4Laq2fNB6/deH81x+x+j0QSk1oyvSVG+73/bmURWSP9WuhikYJ5kqbOdk= =siUv -END PGP SIGNATURE-
Re: [HACKERS] Git-master regression failure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 On 18-06-2013 21:41, Svenne Krap wrote: I will dig futher and get back... The regression test was added in 9.2, the earliest interesting commit is d6d5f67b5b98b1685f9158e9d00a726afb2ae789, where Tom Lane changes the definition to the current. It still fails (which suggests that it has always and will always fail on my setup) I am happy to run whatever relevant tests you can dream up, but I am fresh out of ideas :) Svenne -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.20 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQI5BAEBCAAjBQJRwLo5HBpodHRwOi8vc3Zlbm5lLmRrL3BncC9wb2xpY3kACgkQ /zLSj+olL/JH5w/+PRU8Ghr56RuOcybp3S86Ig8sw4y56Xu8pb4RMHXNTJcL1zk0 fj0NRr+0sCAR9JXIS1mGHnNZuLpdppLklb0Zi02JeNbXelnY34QSiqgn3c9ikBIZ NTq63MVHj2bOC2YkpJIjCCZI7adjyOYm0Fosm453VV6lSh7PooNLUfqTlGA9lHKo lItndci7ei+JTCFr2G3zv2zshwNW87+nd9oAzf+n0Hz3djswrJWvdby56UtTb5S0 6ayYH14BnF0UU2C1Ko5/JSBgDyzT/kkPyn/YEplcHkO3yYKXgaYdanTyQEgYbL5I kSebe6eh271IY4W7wt+Bb5202HDOU6d3ikCJykE8G9BHw77exUbk27BJWipcUNL0 63IERJAleJ29yOqVZggZ0p3hf7H34lFqLKCTi7+p4mP1ZLlMlaMzbTGMdVJuc21A UyqQBY7nv9m7gIiDxXEQaoFbycn7Lb14xYgUOYIYGmnTblg4A5oUREXkMa08vWZg yq+oWOmusmbB3EET0jvQCQe9ves9J4Pa+5Axry2c4tbPQD2PH25FZGyAeCwJLon9 ZmMdFf3uNp91f+YW/BPDgXxi9A+fn1twI0ldMUYuX6MTopWvMrVHzpqQ3MJxPKLb RdWgKHWXZnu0ygw1F/AqpEgx2Y/SAyyuOId1cmamG8BXJsRZVgezqK+roww= =TzBa -END PGP SIGNATURE- -- 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] LIMIT NULL
Andrew Dunstan wrote: Me neither. I wonder how many other long term users (I have used pgsql for more than a decade - 6.2 was my first version if memory serves) and have never caught that nuance either. Maybe that should be printed as a note on the narrative description pages.. something like: Note this is a simplified introduction to the subject. For authorative description please see topic x in section y (and link to it of course ;)) In effect it does say that - perhaps not quite as explicitly as you might have wanted. It says: The information in this part is presented in a narrative fashion in topical units. Readers looking for a complete description of a particular command should look into Part VI. (the PART VI is a link). Well... I meant on EVERY single page outside section VI (or whatever is considered cannonical) I believe most users today search instead of browse documentation... I certainly do... that I have a decade of postgresql experience just helps me to know that section VI.I is extremly important (but I learned that through trial and error not by reading a small hint). If I google postgresql limit the second link links to http://www.postgresql.org/docs/8.3/interactive/queries-limit.html (for some reason google strongly prefers version 7.3). That page (which is the first you see if you search your way in) gives me the following impressions (i.e. given my information gathering heuristics): - domain postgresql.org (=official) - in the page title it states: PostgreSQL 8.3.5 Documentation (=yeah, right place) - Chapter 7. Queries (=reasonable) - 7.6. LIMIT and OFFSET (= home free ;) Which gives me every indication that I am the right place. Nowhere on this page is the note, that there exists a better place (experience has shown me, that the SELECT page in the SQL reference is far more detailed, but that is unknown for a newbie user). What I would like is that EVERY page, that is considered non-cannonical, for the sake of useability has an end-note, that a better (more detailed and more correct) place exists and an indication of where to go.. which I believe would be http://www.postgresql.org/docs/8.3/interactive/sql-select.html in the choosen example. /Svenne -- 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] LIMIT NULL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rick Vernam wrote: The reference documentation is *always* intended to be more complete and more authoritative than the narrative description. If you don't think so then you need to readjust your expectations. very well, I did not know that. expectations readjusted. thanks. Me neither. I wonder how many other long term users (I have used pgsql for more than a decade - 6.2 was my first version if memory serves) and have never caught that nuance either. Maybe that should be printed as a note on the narrative description pages.. something like: Note this is a simplified introduction to the subject. For authorative description please see topic x in section y (and link to it of course ;)) Svenne -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkmJkpEACgkQPPDUIBpvWQsY2ACdG3y9Ho2vW3z2OKePTy9jCkAq QqEAnAn9J+ZOnAsohnPqbGCtLbfC6e7G =FiGn -END PGP SIGNATURE- -- 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] LIMIT NULL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 David E. Wheeler wrote: I think that all pages that seem to document particular features should cross-reference the reference pages in section VI, but not necessarily vice-versa. I don't think that's asking for a lot. If you're reading the narrative section, um, narratively, then you'll see lots of Look here for more on this topic when you're ready, and that will be much more useful for the search-result-hit readers, too. That is my idea too... every non-cannonical (ie. non section VI) page links to relevant section VI (mostly the SELECT one, if I am not mistaken). No need to link from VI to non-VI or from VI to VI. For myself, I've always used the reference documentation, and kind of never really understood where some of the extra documentation of certain features, like LIMIT/OFFSET, lived. I never realized there was a narrative section. As a technically-minded geek, I go right to the reference, and the other stuff is kind of a weird bonus that comes up when I do a search. I have the same experience, I also go directly for the most technical parts. But even I sometimes search and hit those narrative ones Btw. there seems to be no section VI (i.e. non-narrative) page about datatypes and functions... things there link to section II. So unless you're reading the documentation like a bound book, or had glanced through each of the top-level pages of the TOC to familiarize yourself with the structure, I'm not sure anyone would really understand how the non-reference documentation was organized, or that it wasn't meant to be authoritative. I for sure haven't, and I use the docs almost daily and I never read that passage about novice users before today simply because my intro-filler-text filter had made me skip the text every time I visited section II. /Svenne -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkmJ2MoACgkQPPDUIBpvWQsMJACdGLdMirV/WjrzbF8ZLlHAqxoI 5nkAnRFnoaqDnrjzMtzkr7p7gUepEfzF =4Z0b -END PGP SIGNATURE- -- 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] Feature Request - Table Definition query
It could be useful to have a command that returns the table definition (like pg_dump -st) from within the query interface. This could be particularly useful if one doesn't have access to or prefers not to manipulate results coming from a shell. If I have an API from which to query the database, it seems silly to have to spawn a shell and run a command to query the very same database. Yes. That functionality would be really appreciated by myself and a few pgsql-users I know. (I'm not exactly familiar with how pg_dump manipulates or extracts that data, so this could be a very, very stupid question. If so, please forgive me.) Me neither, but I think one of the problems is, that during upgrade you are supposed to use pg_dump from the new version on the existing database instance. The server could most probably only generate its own format (not the new) which might be a problem. Perhaps it could be implemented by stored function (autoinstalled and available in contrib or perhaps installed by a psql on first run) in a namespace like pg_dump_84 (i.e. tagged with server version). If you want to upgrade you could then install the new set of functions.. Psql could then be a thin wrapper (which calls the needed functions). /Svenne -- 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] [GENERAL] SHA1 on postgres 8.3
Mark Mielke wrote: This presumes that better hashes truly exist. It is basic math to show that all hashes will include collisions. Ignoring the possibility that one hash has theoretical better distribution for real documents, the real benefit of SHA-1 over MD5, is that it has more bits. The ultimate solution here, is to store the original using the full copy hash technique, with 0 chance of collision. This extreme defeats the purpose of a hash to start with. Why does PostgreSQL need something better than md5 as part of core? Bragging rights? Having more than one hash algorithm significantly decreases the risk of (common) collisions. As a non-developer (who does track most messages on the list anyways), I surely find the SHA* functions will add significantly value and they should be easy to install (well-defined functions) with no maintainance afterwards. Hashes are an absolute minimum for keeping passwords stored somehat safely in a database. More two or even three different hashes with different collion-points will strongly increase the security. -- 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] [GENERAL] SHA1 on postgres 8.3
Mark Mielke wrote: More two or even three different hashes with different collion-points will strongly increase the security. No it doesn't unless you are thinking about a security through obscurity argument. It is really the same argument on all your questions If I have a simple table now ID serial Username varchar Password varchar I currently save only md5(id || username || 'password')* into password, if I had access to sha1 (for example) i would add another password column so, having for example ID serial Username varchar Password_md5 varchar Password_sha1 varchar No matter how you see it, I get more bits of hash to check against. I would drop md5 totally and use sha1 and ripemd-160 if possible.. but currently i use only md5 as it is the only available one.. Loading pgcrypto is overkill for something as simple as hash-functions. Svenne * I prepend the id and the username to guard users with weak passwords against known hashvalues (rainbow tables) should the box ever get comprised ... if you are in doubt about the value of this, try google for 40e94aa51dc5c0ccc5aad4e6aefdde2a and guess the secret password... -- 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] [GENERAL] SHA1 on postgres 8.3
Mark Mielke wrote: Svenne Krap wrote: Mark Mielke wrote: Svenne Krap wrote: More two or even three different hashes with different collion-points will strongly increase the security. No it doesn't unless you are thinking about a security through obscurity argument Your logic is invalid - the best quality would be to not use a hash at all, and store in plain text, or ROT-13. Then you will have no collisions. If you truly believe more bits are better, don't use a hash to start with. Ooops, went offlist by a wrong click. Putting it back onliste I am aware that plain text (or any 1:1 mapping) has no chance of collision, but on the other hand if the box is compromised it gives an easy target for stealing passwords (and a lot of users use the same passwords a lot of places). I believe that hashing through one hash function is an acceptable compromise between collisions (i.e. people get in with the wrong password) and password safety (evil hacker cannot read passwords) given you deploy anti rainbow table meassures. I would still prefer two hash functions as they do add a better safeguard towards collisions (the gentoo distribtion actually hashes the files by three different algorithms SHA1, SHA256 and RMD160) - i would be inclined to use three hashes too, if they were instantly available. Svenne -- 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] [GENERAL] SHA1 on postgres 8.3
Heikki Linnakangas wrote: Mark Mielke wrote: One must also remember that if you use two hashes, if *either* one of them is broken in the future so that you can reconstruct the password from the hash, you're screwed. That is quite a good argument actually :) -- 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] [GENERAL] SHA1 on postgres 8.3
Sam Mason wrote: Are you a cryptanalyst and are you sure that this doesn't actually make things worse? I'm sure it gives you a warm fuzzy feeling that it's *got* to be better, but unless someone has done some hard maths I'm not sure how you can be so sure. No sadly I am no cryptoanalyst. Why not just use SHA-512, you get many more quality bits that way. I would, if it was available in core. I would drop md5 totally and use sha1 and ripemd-160 if possible.. but currently i use only md5 as it is the only available one.. Loading pgcrypto is overkill for something as simple as hash-functions. Sounds like a good reason for moving the current md5 function out into pgcrypto as well! :) I am not sure how I am to understand that comment. But again I am just a user... * I prepend the id and the username to guard users with weak passwords against known hashvalues (rainbow tables) should the box ever get comprised ... I take it your threat model doesn't include the attacker logging incoming queries to look for the clear-text password. No it doesn't, I am mostly concerned with the grab and run scenario. I am still convinced having more (and better) hash-functions in core is a gain for some users. And it is fairly un-intrusive as the hash functions are well-defined and never going to change (new ones can be added and old ones deleted, but SHA256 for example will never change). I think I will drop the issue as I cannot present formal proof of my case, sorry to have wasted your time. Svenne
Re: [HACKERS] Is a SERIAL column a black box, or not?
Tom Lane wrote: In short, I think there's a reasonably good case to be made for losing the hidden dependency and re-adopting the viewpoint that saying SERIAL is *exactly* the same as making a sequence and then making a default expression that uses the sequence. Nothing behind the curtain. I speak more as a user than a hacker, but I do still lurk here ;) The way sequences are handled is imho one of the strongest features. The possiblity to query nextval is bordering on divine. I have however stopped using serials for anything else than quick mockup examples. The work of defining the sequence itself and setting acl's is imho trivial compared to consistency. I would actually suggest throwing a warning, that sequences are the proper way of doing it when people use serials - maybe even mark serial-types as obsolete in the docs. I strongly subscribe to the principle of least astonishment, and that means either pure sequences, a mysqlesqe auto_increment or both - but I fail to see, how the macrothing serial will ever work that way. It goes without saying, that I dislike auto_increment. Svenne smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] About pg_hba.conf
Gevik Babakhani wrote: This may be a dumb question but please bear a moment with me. About the TODO item “%Allow pg_hba.conf settings to be controlled via SQL“: If in the future we could configure the settings by SQL commands, assuming the settings are saved in an internal table, what would be the need for a pg_hba.conf file anymore. (except for the backward compatibility of cource) System recovery might also be a reason (if you for some reason make your data in DB unworkable). Svenne smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] control pg_hba.conf via SQL
Tom Lane wrote:
I'm a bit suspicious of proposals that we move either hba or conf into
SQL tables --- one of the main reasons why they are flat files is so
you can still edit them after you've hosed them to the point that the
database won't start or won't let you in. If you don't have a non-kluge
solution to the DBA-mistake-recovery scenario, this is not going to be
an improvement.
What about a line in pg_hba which tells pgsql how to handle it. I.e. an
example is :
--- pg_hba.conf ---
Include_file = {only, first, last, not}
normal lines
--- pg_hba.conf ---
Not means only use in DB data.
First means load file before data from DB (so db can overwrite file
settings)
Last means load file after data from DB (so file can overwrite db settings)
Only means just use file settings (as today)
If the line is missing, assume only (backwards compatibility).
By the way, I really think there should be real grammar for it, not just
a couple of sql helper functions.
Svenne
smime.p7s
Description: S/MIME Cryptographic Signature
