Re: [HACKERS] Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
Hi Peter, hi all, I wrote 2016-07-06 13:19 GMT+02:00: > ... > 2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.mu...@enterprisedb.com>: >> ... But ISO/IEC CD 9075-15 >> (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to >> Working Group". Is that how they say "returned with feedback"? >> ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage >> 30.60. Does anyone know what that one is about? ... @Peter B. Can you clarify the stage? Regarding the differences of array functions of the ISO proposal, I have no clue why the working group did not follow the implementation of PostgreSQL. @Peter B. You know PostgreSQL well. Can you explain? :Stefan P.S. 2016-07-02 17:11 GMT+02:00 Tom Lane <t...@sss.pgh.pa.us> wrote: > Peter E. had observer status at one point, don't know if he still does. @Peter E.: Do you still have observer status at the ISO committee? 2016-07-06 13:19 GMT+02:00 Stefan Keller <sfkel...@gmail.com>: > Thomas > > 2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.mu...@enterprisedb.com>: >> ... But ISO/IEC CD 9075-15 >> (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to >> Working Group". Is that how they say "returned with feedback"? >> ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage >> 30.60. Does anyone know what that one is about? Maybe something like > > Peter surely would know: https://www.jacobs-university.de/directory/pbaumann > > :Stefan > > 2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.mu...@enterprisedb.com>: >> On Wed, Jun 29, 2016 at 11:51 AM, Stefan Keller <sfkel...@gmail.com> wrote: >>> Hi, >>> >>> FYI: I'd just like to point you to following two forthcoming standard >>> parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on >>> "Multi-Dimensional Arrays" (SQL/MDA). >>> >>> They define there some things different as already in PG. See also >>> Peter Baumann's slides [1] and e.g. [2] >>> >>> :Stefan >>> >>> [1] >>> https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql >>> [2] >>> http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf >> >> Thanks for these pointers. On the "standards under development" >> page[1], I see that ISO/IEC PDTR 19075-6 (SQL/JSON) is at stage 30.60 >> "Close of voting/ comment period". But ISO/IEC CD 9075-15 >> (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to >> Working Group". Is that how they say "returned with feedback"? >> ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage >> 30.60. Does anyone know what that one is about? Maybe something like >> MATCH_RECOGNIZE in Oracle? >> >> [1] >> http://www.iso.org/iso/home/store/catalogue_tc/catalogue_tc_browse.htm?commid=45342=on >> >> -- >> Thomas Munro >> 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] Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
Thomas 2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.mu...@enterprisedb.com>: > ... But ISO/IEC CD 9075-15 > (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to > Working Group". Is that how they say "returned with feedback"? > ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage > 30.60. Does anyone know what that one is about? Maybe something like Peter surely would know: https://www.jacobs-university.de/directory/pbaumann :Stefan 2016-07-04 6:44 GMT+02:00 Thomas Munro <thomas.mu...@enterprisedb.com>: > On Wed, Jun 29, 2016 at 11:51 AM, Stefan Keller <sfkel...@gmail.com> wrote: >> Hi, >> >> FYI: I'd just like to point you to following two forthcoming standard >> parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on >> "Multi-Dimensional Arrays" (SQL/MDA). >> >> They define there some things different as already in PG. See also >> Peter Baumann's slides [1] and e.g. [2] >> >> :Stefan >> >> [1] >> https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql >> [2] >> http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf > > Thanks for these pointers. On the "standards under development" > page[1], I see that ISO/IEC PDTR 19075-6 (SQL/JSON) is at stage 30.60 > "Close of voting/ comment period". But ISO/IEC CD 9075-15 > (Multi-Dimensional Arrays) is in stage 30.92 "CD referred back to > Working Group". Is that how they say "returned with feedback"? > ISO/IEC PDTR 19075-5 (Row Pattern Recognition) has also reached stage > 30.60. Does anyone know what that one is about? Maybe something like > MATCH_RECOGNIZE in Oracle? > > [1] > http://www.iso.org/iso/home/store/catalogue_tc/catalogue_tc_browse.htm?commid=45342=on > > -- > Thomas Munro > 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
[HACKERS] Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)
Hi, FYI: I'd just like to point you to following two forthcoming standard parts from "ISO/IEC JTS 1/SC 32" comittee: one on JSON, and one on "Multi-Dimensional Arrays" (SQL/MDA). They define there some things different as already in PG. See also Peter Baumann's slides [1] and e.g. [2] :Stefan [1] https://www.unibw.de/inf4/professors/geoinformatics/agile-2016-workshop-gis-with-nosql [2] http://jtc1sc32.org/doc/N2501-2550/32N2528-WG3-Tutorial-Opening-Plenary.pdf -- 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] point_ops for GiST
Hi Alexander Thanks for your succinct reply. Actually I considered contributing myself for the first time to PostgreSQL and/or PostGIS. So, concluding from your explanations there's no big use case behind build-in geometric types except serving as reference implementation? I'm still torn over this splitting resources to implement types like geometry twice. :Stefan 2015-10-12 11:24 GMT+02:00 Alexander Korotkov <a.korot...@postgrespro.ru>: > Hi, Stefan! > > On Sun, Oct 11, 2015 at 10:00 PM, Stefan Keller <sfkel...@gmail.com> wrote: >> >> Pls. don't misunderstand my questions: They are directed to get an >> even more useful spatial data handling of PostgreSQL. I'm working with >> PostGIS since years and are interested in any work regarding spatial >> types... >> >> Can anyone report use cases or applications of these built-in geometric >> types? >> >> Would'nt it be even more useful to concentrate to PostGIS >> geometry/geography types and extend BRIN to these types? > > > Note, that PostGIS is a different project which is maintained by separate > team. PostGIS have its own priorities, development plan etc. > PostgreSQL have to be self-consistent. In particular, it should have > reference implementation of operator classes which extensions can use as the > pattern. This is why it's important to maintain built-in geometric types. > > In short: once we implement it for built-in geometric types, you can ask > PostGIS team to do the same for their geometry/geography. > > -- > Alexander Korotkov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres 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] point_ops for GiST
Hi Emre Pls. don't misunderstand my questions: They are directed to get an even more useful spatial data handling of PostgreSQL. I'm working with PostGIS since years and are interested in any work regarding spatial types... Can anyone report use cases or applications of these built-in geometric types? Would'nt it be even more useful to concentrate to PostGIS geometry/geography types and extend BRIN to these types? :Stefan 2015-06-13 23:04 GMT+02:00 Emre Hasegeli: >> Emre Hasegeli just pointed out to me that this patch introduced >> box_contain_pt() and in doing so used straight C comparison (<= etc) >> instead of FPlt() and friends. I would think that that's a bug and >> needs to be changed -- but certainly not backpatched, because gist >> indexes would/might become corrupt. > > The problem with this is BRIN inclusion opclass uses some operators to > implement others. It was using box @> point operator to implement > point ~= point operator by indexing points in boxes. The former > doesn't use the macros, but later does. The opclass could return > wrong result when the point right near the index boundaries. > > Currently, there are not BRIN opclasses for geometric types except box > because of this reason. I would like to work on supporting them for > the next release. I think the best way is to change the operators > which are not using the macros to be consistent with the others. Here > is the list: > > * polygon << polygon > * polygon &< polygon > * polygon &> polygon > * polygon >> polygon > * polygon <<| polygon > * polygon &<| polygon > * polygon |&> polygon > * polygon |>> polygon > * box @> point > * point <@ box > * lseg <@ box > * circle @> point > * point <@ circle > > I can send a patch, if it is acceptable. > > > -- > 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] BRIN range operator class
Hi, 2015-05-05 2:51 GMT+02:00 Andreas Karlsson andr...@proxel.se: From my point of view as a reviewer this patch set is very close to being committable. I'd like to thank already now to all committers and reviewers and hope BRIN makes it into PG 9.5. As a database instructor, conference organisator and geospatial specialist I'm looking forward for this clever new index. I'm keen to see if a PostGIS specialist jumps in and adds PostGIS geometry support. Yours, S. 2015-05-05 2:51 GMT+02:00 Andreas Karlsson andr...@proxel.se: From my point of view as a reviewer this patch set is very close to being committable. = brin-inclusion-v06-01-sql-level-support-functions.patch This patch looks good. = brin-inclusion-v06-02-strategy-numbers.patch This patch looks good, but shouldn't it be merged with 07? = brin-inclusion-v06-03-remove-assert-checking.patch As you wrote earlier this is needed because the new range indexes would violate the asserts. I think it is fine to remove the assertion. = brin-inclusion-v06-04-fix-brin-deform-tuple.patch This patch looks good and can be committed separately. = brin-inclusion-v06-05-box-vs-point-operators.patch This patch looks good and can be committed separately. = brin-inclusion-v06-06-inclusion-opclasses.patch - operator classes store the union of the values in the indexed column is not technically true. It stores something which covers all of the values. - Missing space in except box and point*/. - Otherwise looks good. = brin-inclusion-v06-07-remove-minmax-amprocs.patch Shouldn't this be merged with 02? Otherwise it looks good. -- Andreas Karlsson -- 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
[HACKERS] New Minmax index for geometry data type?
Hi Alvaro 2014-08-15 20:16 GMT+02:00 you answered: (...) Yeah, it's just a matter of adding an opclass for it -- pretty simple (...) Right now there are opclasses for the following types: (...) Of course, the real interesting part is adding a completely different opclass, such as one that stores bounding boxes. That was exactly what I was going to ask you regarding support of minmax (block range) index for GEOMETRY types (as defined in PostGIS): 1. What would be the advantage of such a minmax index over GiST besides size? 2. Are the plans to implement this? 3. If no, how large would you estimate the efforts to implement this in days for an experienced programmer like you? Yours, Stefan 2014-08-15 20:16 GMT+02:00 Alvaro Herrera alvhe...@2ndquadrant.com: Fujii Masao wrote: I've not read the patch yet. But while testing the feature, I found that * Brin index cannot be created on CHAR(n) column. Maybe other data types have the same problem. Yeah, it's just a matter of adding an opclass for it -- pretty simple stuff really, because you don't need to write any code, just add a bunch of catalog entries and an OPCINFO line in mmsortable.c. Right now there are opclasses for the following types: int4 numeric text date timestamp with time zone timestamp time with time zone time char We can eventually extend to cover all types that have btree opclasses, but we can do that in a separate commit. I'm also considering removing the opclass for time with time zone, as it's a pretty useless type. I mostly added the ones that are there as a way to test that it behaved reasonably in the various cases (pass by val vs. not, variable width vs. fixed, different alignment requirements) Of course, the real interesting part is adding a completely different opclass, such as one that stores bounding boxes. * FILLFACTOR cannot be set in brin index. I hadn't added this one because I didn't think there was much point previously, but I think it might now be useful to allow same-page updates. -- Álvaro Herrerahttp://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 -- 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] PostgreSQL Columnar Store for Analytic Workloads
Hi Hadi Do you think that cstore_fd*w* is also welll suited for storing and retrieving linked data (RDF)? -S. 2014-04-03 18:43 GMT+02:00 Hadi Moshayedi h...@citusdata.com: Dear Hackers, We at Citus Data have been developing a columnar store extension for PostgreSQL. Today we are excited to open source it under the Apache v2.0 license. This columnar store extension uses the Optimized Row Columnar (ORC) format for its data layout, which improves upon the RCFile format developed at Facebook, and brings the following benefits: * Compression: Reduces in-memory and on-disk data size by 2-4x. Can be extended to support different codecs. We used the functions in pg_lzcompress.h for compression and decompression. * Column projections: Only reads column data relevant to the query. Improves performance for I/O bound queries. * Skip indexes: Stores min/max statistics for row groups, and uses them to skip over unrelated rows. We used the PostgreSQL FDW APIs to make this work. The extension doesn't implement the writable FDW API, but it uses the process utility hook to enable COPY command for the columnar tables. This extension uses PostgreSQL's internal data type representation to store data in the table, so this columnar store should support all data types that PostgreSQL supports. We tried the extension on TPC-H benchmark with 4GB scale factor on a m1.xlarge Amazon EC2 instance, and the query performance improved by 2x-3x compared to regular PostgreSQL table. Note that we flushed the page cache before each test to see the impact on disk I/O. When data is cached in memory, the performance of cstore_fdw tables were close to the performance of regular PostgreSQL tables. For more information, please visit: * our blog post: http://citusdata.com/blog/76-postgresql-columnar-store-for-analytics * our github page: https://github.com/citusdata/cstore_fdw Feedback from you is really appreciated. Thanks, -- Hadi
Re: [HACKERS] View Index and UNION
Hi Tom You are right: UNION ALL is correct in terms of contents (tables contents are disjunct) and of performance (no separate sort required theoretically). In my specific case even with UNION ALL the planner still chose a Seq Scan. Note that there is a KNN index with ORDER BY ... - ... involved. I have to dig into my tests in order to give you the EXPLAIN ANALYZE. Yours, Stefan 2013/5/26 Tom Lane t...@sss.pgh.pa.us: Stefan Keller sfkel...@gmail.com writes: Given following schema: 1. TABLE a and TABLE b, each with INDEX on attribute geom. 2. A VIEW with union: CREATE VIEW myview AS SELECT * FROM a UNION SELECT * FROM b; 3. And a simple query with KNN index and a coordinate mypos : SELECT * FROM myview ORDER BY ST_Geomfromtext(mypos) - myview.geom I think this would work out-of-the-box in 9.1 or later, if you made the view use UNION ALL instead of UNION. 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] View Index and UNION
Yes, it actually does, but the planner chooses a seq scan to prepare for that. -S. 2013/5/26 William King william.k...@quentustech.com: Could this scenario not be handled by a step that orders the two tables independently, then for the view interleaves the presorted results? Merging two sorted sets into a single sorted set is usually a trivial task, and it could still take advantage of the existing indexes. William King Senior Engineer Quentus Technologies, INC 1037 NE 65th St Suite 273 Seattle, WA 98115 Main: (877) 211-9337 Office: (206) 388-4772 Cell: (253) 686-5518 william.k...@quentustech.com On 05/25/2013 05:35 PM, Stefan Keller wrote: Hi I've encountered a fundamental problem which - to me - can only be solved with an (future/possible) real index on views in PostgreSQL (like the exist already in MS SQL Server and Ora): Given following schema: 1. TABLE a and TABLE b, each with INDEX on attribute geom. 2. A VIEW with union: CREATE VIEW myview AS SELECT * FROM a UNION SELECT * FROM b; 3. And a simple query with KNN index and a coordinate mypos : SELECT * FROM myview ORDER BY ST_Geomfromtext(mypos) - myview.geom Now, the problem is, that for the order by it is not enough that each on the two tables calculate the ordering separately: We want a total ordering over all involved tables! In fact, the planner realizes that and chooses a seq scan over all tuples of table a and b - which is slow and suboptimal! To me, that's a use case where we would wish to have a distinct index on views. Any opinions on this? Yours, Stefan -- 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
[HACKERS] View Index and UNION
Hi I've encountered a fundamental problem which - to me - can only be solved with an (future/possible) real index on views in PostgreSQL (like the exist already in MS SQL Server and Ora): Given following schema: 1. TABLE a and TABLE b, each with INDEX on attribute geom. 2. A VIEW with union: CREATE VIEW myview AS SELECT * FROM a UNION SELECT * FROM b; 3. And a simple query with KNN index and a coordinate mypos : SELECT * FROM myview ORDER BY ST_Geomfromtext(mypos) - myview.geom Now, the problem is, that for the order by it is not enough that each on the two tables calculate the ordering separately: We want a total ordering over all involved tables! In fact, the planner realizes that and chooses a seq scan over all tuples of table a and b - which is slow and suboptimal! To me, that's a use case where we would wish to have a distinct index on views. Any opinions on this? Yours, Stefan -- 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] 9.3 Pre-proposal: Range Merge Join
Hi Jeff 2013/1/18 Jeff Davis pg...@j-davis.com: On Thu, 2013-01-17 at 21:03 +0100, Stefan Keller wrote: Hi Jeff I'm perhaps really late in this discussion but I just was made aware of that via the tweet from Josh Berkus about PostgreSQL 9.3: Current Feature Status What is the reason to digg into spatial-joins when there is PostGIS being a bullet-proof and fast implementation? Hi Stefan, You are certainly not too late. PostGIS uses the existing postgres infrastructure to do spatial joins. That mean it either does a cartesian product and filters the results, or it uses a nested loop with an inner index scan. That isn't too bad, but it could be better. I am trying to introduce a new way to do spatial joins which will perform better in more circumstances. For instance, we can't use an inner index if the input tables are actually subqueries, because we can't index a subquery. Regards, Jeff Davis Sounds good. Did you already had contact e.g. with Paul (cc'ed just in case)? And will this clever index also be available within all these hundreds of PostGIS functions? Regards, Stefan -- 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] 9.3 Pre-proposal: Range Merge Join
Hi Jeff 2012/4/19 Jeff Davis pg...@j-davis.com: On Wed, 2012-04-18 at 01:21 -0400, Tom Lane wrote: (...) This is just handwaving of course. I think some digging in the spatial-join literature would likely find ideas better than any of these. I will look in some more detail. The merge-like approach did seem to be represented in the paper referenced by Alexander (the external plane sweep), but it also refers to several methods based on partitioning. I'm beginning to think that more than one of these ideas has merit. Regards, Jeff Davis I'm perhaps really late in this discussion but I just was made aware of that via the tweet from Josh Berkus about PostgreSQL 9.3: Current Feature Status What is the reason to digg into spatial-joins when there is PostGIS being a bullet-proof and fast implementation? Yours, Stefan -- 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_prewarm
Hi Robert 2012/3/11 Robert Haas robertmh...@gmail.com: On Sat, Mar 10, 2012 at 4:35 PM, Stefan Keller sfkel...@gmail.com wrote: The main conclusion was: * Do a tar cf /dev/zero $PG_DATA/base either shortly before or shortly after the database is created * Do a seq scan SELECT * FROM osm_point. Is your tool a replacement of those above? It can be used that way, although it is more general. (The patch does include documentation...) Thanks for the hint. That function is cool and it seems to be the solution of the concluding question in my talk about read-only databases at pgconf.de 2011! I'm new to the contrib best practices of Postgres so I did not expect that a file 'pg_prewarm_v1.patch' contains a brand new stand-alone extension. Does pg_prewarm have already a website entry somewhere? I did not find anything (like here http://www.postgresql.org/search/?q=pg_prewarma=1submit=Search ) except at Commitfest open patches (https://commitfest.postgresql.org/ ). -Stefan -- 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_prewarm
Cédric and Robert Thanks, Cédric, for the reminder. Would be nice to sort out the features of the two Postgres extentions pgfincore (https://github.com/klando/pgfincore ) and pg_prewarm: what do they have in common, what is complementary? I would be happy to test both. But when reading the current documentation I'm missing installation requirements (PG version, replication? memory/hardware requirements), specifics of Linux (and Windows if supported), and some config. hints (e.g. relationships/dependencies of OS cache and PG cache an postgresql.conf). -Stefan 2012/3/11 Cédric Villemain ced...@2ndquadrant.com: Le vendredi 9 mars 2012 16:50:05, Robert Haas a écrit : On Fri, Mar 9, 2012 at 10:33 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: So that's complementary with pgfincore, ok. I still wish we could maintain the RAM content HOT on the standby in the same way we are able to maintain its data set on disk, though. That's an interesting idea. It seems tricky, though. it is the purpose of the latest pgfincore version. I use a varbit as output of introspection on master, then you are able to store in a table, stream to slaves, then replay localy. -- 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 -- 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_prewarm
Hi Robert, Just recently I asked on postgres-performance PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory? Somehow open was, what's the best practice of configuration and relationship between disk/OS cache vs. Portgres cache The main conclusion was: * Do a tar cf /dev/zero $PG_DATA/base either shortly before or shortly after the database is created * Do a seq scan SELECT * FROM osm_point. Is your tool a replacement of those above? -Stefan 2012/3/9 Robert Haas robertmh...@gmail.com: On Fri, Mar 9, 2012 at 10:53 AM, Jeff Janes jeff.ja...@gmail.com wrote: On Fri, Mar 9, 2012 at 5:21 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao masao.fu...@gmail.com wrote: When a relation is loaded into cache, are corresponding indexes also loaded at the same time? No, although if you wanted to do that you could easily do so, using a query like this: select pg_prewarm(indexrelid, 'main', 'read', NULL, NULL) from pg_index where indrelid = 'your_table_name'::regclass; Could that be included in an example? Maybe admins are expected to know how to construct such queries of the cuff, but I always need to look it up each time which is rather tedious. Not a bad idea. I thought of including an Examples section, but it didn't seem quite worth it for the simple case of prewarming a heap. Might be worth it to also include this. In the patch: s/no special projection/no special protection/ OK, will fix. Thanks for putting this together. I will confess that it was 0% altruistic. Not having it was ruining my day. :-) -- 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 -- 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] JSON output functions.
Hi Andrew Nice work! Just for completeness: Did you also think of including geometry types in JSON output functions in later releases? There's a nice extension of JSON called GeoJSON for a starting point. Yours, Stefan 2012/2/3 Andrew Dunstan and...@dunslane.net: On 02/02/2012 12:20 PM, Pavel Stehule wrote: 2012/2/2 Andrew Dunstanand...@dunslane.net: On 02/02/2012 04:35 AM, Abhijit Menon-Sen wrote: At 2012-02-01 18:48:28 -0500, andrew.duns...@pgexperts.com wrote: For now I'm inclined not to proceed with that, and leave it as an optimization to be considered later if necessary. Thoughts? I agree, there doesn't seem to be a pressing need to do it now. OK, here's my final version of the patch for constructor functions. If there's no further comment I'll go with this. These function are super, Thank you Do you plan to fix a issue with row attribute names in 9.2? Yeah. Tom did some initial work which he published here: http://archives.postgresql.org/message-id/28413.1321500388%40sss.pgh.pa.us, noting: It's not really ideal with respect to the ValuesScan case, because what you get seems to always be the hard-wired columnN names for VALUES columns, even if you try to override that with an alias ... Curiously, it works just fine if the VALUES can be folded and later he said: Upon further review, this patch would need some more work even for the RowExpr case, because there are several places that build RowExprs without bothering to build a valid colnames list. It's clearly soluble if anyone cares to put in the work, but I'm not personally excited enough to pursue it .. I'm going to look at that issue first, since the unfolded VALUES clause seems like something of an obscure corner case. Feel free to chime in if you can. 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 -- 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] Real-life range datasets
Hi I'm proposing OpenStreetMap which is of variable size up to 250 GB XML Data for whole world. It's downloadable from CloudMade.com or Geofabrik.de and can be imported into PostgreSQL using osm2pgsql. It's a key/value schema literally of the real world. I'm using hstore option of osm2pgsql and hstore index is based on GIST. I'm running a database instance called PostGIS Terminal which is a daily extract of Switzerland: http://labs.geometa.info/postgisterminal/?xapi=node%5Bname%3DHochschule%20Rapperswil%5D This is a typical query which extracts 'real' highways (being of geometry linestring, aka line/way) with a speed limit = 100 km/h: SELECT ST_AsText(way) geom FROM osm_line WHERE tags @ 'highway=motorway' AND coalesce(substring((tags-'maxspeed') FROM E'[0-9]+')::int,0) = 100 Yours, Stefan 2011/12/23 Alexander Korotkov aekorot...@gmail.com: Hello, On Thu, Dec 22, 2011 at 12:51 PM, Benedikt Grundmann bgrundm...@janestreet.com wrote: I should be able to give you a table with the same characteristics as the instruments table but bogus data by replacing all entries in the table with random strings of the same length or something like that. I can probably take a little bit of time during this or the next week to generate such fake real world data ;-) Is there an ftp site to upload the gzipped pg_dump file to? Thank you very much for your response! I'm going to send you accessories for upload soon. - With best regards, Alexander Korotkov. -- 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] SP-GiST versus index-only scans
Tom, There seems to exist some opportunities now with GIST which relate to geometry/geography types (but not only...): 1. Index-only scans on geometry columns with SP-GIST (being able to do a SELECT id FROM my_table WHERE mygeom...;). 2. Index clustering incuding NULL values (i.e. being able to do a CLUSTER mygeom_index ON mytable; ). This discussion suggests that at least 1. is close to be implemented. The problem of 2. has to do with handling NULL values; it's mentioned in the PostGIS manual [1]. I'm aware of kd-tree index development [2]. Don't know if clustering and index-only scans would be resolved there. But I can't find neither in the Todo List [3] ? What do you think? Yours, Stefan [2] http://postgis.refractions.net/docs/ch06.html#id2635907 [3] http://old.nabble.com/IMPORTANT%3A-%28Still%29-Seeking-Funding-for-Faster-PostGIS-Indexes-td32633545.html [3] http://wiki.postgresql.org/wiki/Todo#Indexes 2011/12/14 Tom Lane t...@sss.pgh.pa.us: Jesper Krogh jes...@krogh.cc writes: On 2011-12-14 19:48, Tom Lane wrote: I think this is somewhat wishful thinking unfortunately. The difficulty is that if the index isn't capable of reconstructing the original value, then it's probably giving only an approximate (lossy) answer, which means we'll have to visit the heap to recheck each result, which pretty much defeats the purpose of an index-only scan. I can see that it is hard to generalize, but in the tsvector case the we are indeed not capable of reconstructing the row since the positions are not stored in the index, the actual lookup is not a lossy and I'm fairly sure (based on experience) that pg dont revisit heap-tuples for checking (only for visibillity). Well, the way the tsvector code handles this stuff is that it reports the result as lossy only if the query actually poses a constraint on position (some do, some don't). That case was actually what made us move the determination of lossiness from plan time to execution time, since in the case of a non-constant tsquery, there's no way for the planner to know about it (and even with the constant case, you'd need a helper function that doesn't exist today). But this behavior is problematic for index-only scans because the planner can't tell whether a query will be lossy or not, and it makes a heck of a lot bigger difference than it used to. 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 -- 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] odbc_fdw
After having read the most recent thread here about FDW [1] I still in doubt what the status is of FDW in PostgreSQL 9.1. Looking at the official docs of file_fdw as Additional Supplied Module [2] there's no clue about SQL/MED and what the SQL syntax is. I'd like to contribute at least to the documentation given there's any implementation activity around FDWs. Yours, Stefan P.S. Sorry of not moving over to psql-general but I suspect that there's support needed from developpers to make this nice stuff run. [1] http://postgresql.1045698.n5.nabble.com/pgsql-fdw-FDW-for-PostgreSQL-server-td4935560.html[2] http://www.postgresql.org/docs/9.1/interactive/file-fdw.html 2011/11/28 Stefan Keller sfkel...@gmail.com: Hi Robert 2011/11/28 Robert Haas robertmh...@gmail.com wrote: You might want to try this question on pgsql-general or pgsql-novice rather than here; this is a list for discussing the development of PostgreSQL itself. Thanks for the hint. It was actually my advice to post this question here. A quick search retrieves mostly (unanswered) postings which report problems compiling the ODBC_FDW extension (e.g. [1]). My posting to psql-general is still orphaned [2]?. Then I talked to some colleagues of the steering committee of PGConf.DE recently and they confirmed that Foreign Data Wrappers (FDW) probably are not stable. So, I hope we finally find some FDW users and developpers over there at pgsql-general :- Yours, Stefan [1] http://postgresql.1045698.n5.nabble.com/Problem-with-odbc-fdw-td4908875.html [2] http://postgresql.1045698.n5.nabble.com/Integration-of-PostgresSQL-and-MongoDB-Any-Foreign-Data-Wrappers-SQL-MED-td4900771.html 2011/11/28 Robert Haas robertmh...@gmail.com: On Sat, Nov 26, 2011 at 7:20 AM, Florian Schwendener fschw...@hsr.ch wrote: Hi there! I built the current PostgreSQL 9.1.1 sources under Ubuntu 11.04 (in a VMware under Win7). I followed the steps in this guide: www.thegeekstuff.com/2009/04/linux-postgresql-install-and-configure-from-source It seems to work (I can run the server and connect to it with PgAdmin). Now I'd like to integrate the ODBC_FDW extension in my installation. Sadly, the make file throws errors (no target named ... specified). Is there any way to do this in a simple way? I'm a linux newbie, by the way... Thank you for your help! You might want to try this question on pgsql-general or pgsql-novice rather than here; this is a list for discussing the development of PostgreSQL itself. I think you'll find that no one can help you much based on the information you've provided here; you'll need to say exactly what you did and exactly what error message you got. -- 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 -- 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] odbc_fdw
Hi Robert 2011/11/28 Robert Haas robertmh...@gmail.com wrote: You might want to try this question on pgsql-general or pgsql-novice rather than here; this is a list for discussing the development of PostgreSQL itself. Thanks for the hint. It was actually my advice to post this question here. A quick search retrieves mostly (unanswered) postings which report problems compiling the ODBC_FDW extension (e.g. [1]). My posting to psql-geneal is still orphaned [2]?. Then I talked to some colleagues of the steering committee of PGConf.DE recently and they confirmed that Foreign Data Wrappers (FDW) probably are not stable. So, I hope we finally find some FDW users and developpers over there at pgsql-general :- Yours, Stefan [1] http://postgresql.1045698.n5.nabble.com/Problem-with-odbc-fdw-td4908875.html [2] http://postgresql.1045698.n5.nabble.com/Integration-of-PostgresSQL-and-MongoDB-Any-Foreign-Data-Wrappers-SQL-MED-td4900771.html 2011/11/28 Robert Haas robertmh...@gmail.com: On Sat, Nov 26, 2011 at 7:20 AM, Florian Schwendener fschw...@hsr.ch wrote: Hi there! I built the current PostgreSQL 9.1.1 sources under Ubuntu 11.04 (in a VMware under Win7). I followed the steps in this guide: www.thegeekstuff.com/2009/04/linux-postgresql-install-and-configure-from-source It seems to work (I can run the server and connect to it with PgAdmin). Now I'd like to integrate the ODBC_FDW extension in my installation. Sadly, the make file throws errors (no target named ... specified). Is there any way to do this in a simple way? I'm a linux newbie, by the way... Thank you for your help! You might want to try this question on pgsql-general or pgsql-novice rather than here; this is a list for discussing the development of PostgreSQL itself. I think you'll find that no one can help you much based on the information you've provided here; you'll need to say exactly what you did and exactly what error message you got. -- 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 -- 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: Fast GiST index build
I'm on the way to open a ticket for hash indexes (adding WAL support) anyway: May I open a ticket for adding GiST support to unlogged tables ? Stefan 2011/9/14 Stefan Keller sfkel...@gmail.com: Robert, 2011/9/6 Alexander Korotkov aekorot...@gmail.com: GiST use serial numbers of operations for concurrency. In current implementation xlog record ids are used in capacity of that numbers. In unlogged table no xlog records are produced. So, we haven't serial numbers of operations. AFAIK, it's enough to provide some other source of serial number in order to make GiST work with unlogged tables. GiST is IMHO quite broadly used. I use it for example for indexing geometry and hstore types and there's no other choice there. Do you know whether unlogged option in create table will support GiST in the next release? Stefan -- 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: Fast GiST index build
Robert, 2011/9/6 Alexander Korotkov aekorot...@gmail.com: GiST use serial numbers of operations for concurrency. In current implementation xlog record ids are used in capacity of that numbers. In unlogged table no xlog records are produced. So, we haven't serial numbers of operations. AFAIK, it's enough to provide some other source of serial number in order to make GiST work with unlogged tables. GiST is IMHO quite broadly used. I use it for example for indexing geometry and hstore types and there's no other choice there. Do you know whether unlogged option in create table will support GiST in the next release? Stefan -- 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: Fast GiST index build
Hi, Unlogged tables seems to me to follow a similar goal. Obviously GiST indexes are not supported there. Do you know the technical reason? Do you see some synergy in your work on fast GiST index building and unlogged tables? Yours, Stefan 2011/9/6 Alexander Korotkov aekorot...@gmail.com: Small bugfix: in gistBufferingFindCorrectParent check that downlinkoffnum doesn't exceed maximal offset number. -- With best regards, Alexander Korotkov. -- 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
[HACKERS] hstore - Implementation and performance issues around its operators
Hi, We did a benchmark comparing a Key-Value-Pairs stored as EAV db schema versus hstore. The results are promising in favor of hstore but there are some question which remain. 1. Obviously the '@' has to be used in order to let use the GiST index. Why is the '-' operator not supported by GiST ('-' is actually mentioned in all examples of the doc.)? 2. Currently the hstore elements are stored in order as they are coming from the insert statement / constructor. Why are the elements not ordered i.e. why is the hstore not cached in all hstore functions (like hstore_fetchval etc.)? 3. In the source code 'hstore_io.c' one finds the following enigmatic note: ... very large hstore values can't be output. this could be fixed, but many other data types probably have the same issue. What is the max. length of a hstore (i.e. the max. length of the sum of all elements in text representation)? 4. Last, I don't fully understand the following note in the hstore doc. (http://www.postgresql.org/docs/current/interactive/hstore.html ): Notice that the old names are reversed from the convention formerly followed by the core geometric data types! Why names? Why not rather 'operators' or 'functions'? What does this reversed from the convention mean concretely? Yours, Stefan P.S. I already tried to ask these questions to postgres-performance and to the hstore authors without success... -- 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] Proposal: functions get_text() or get_url()
Ok. But again: There is a library mentioned and documented in the famous PostgreSQL book from Douglas Douglas called pgcurl ( http://gborg.postgresql.org/project/pgcurl/ ). Where's this gone? Yours, S. 2009/5/20 Robert Haas robertmh...@gmail.com On Wed, May 20, 2009 at 6:34 AM, Stefan Keller sfkel...@gmail.com wrote: Questions: Don't see, why this would be a security issue: How could such a function do any harm? large files? No, large files aren't the problem. The problem is that the PostgreSQL server process may have rights to access things that the user doesn't. For a simple case, imagine that PostgreSQL is behind a firewall and the user is in front of the firewall, but there's a port open to permit access to PostgreSQL. Now imagine that there is a web server behind the firewall. The firewall blocks the user from accessing the web server directly, but the user can ask PostgreSQL to download the URLs for him. In that way, the user can bypass the firewall. (Consider for example Andrew Chernow's company, which has clients connecting to their database server from all over the Internet...) ...Robert
Re: [HACKERS] Proposal: functions get_text() or get_url()
Tom, Apparently you've not found pg_read_file() ? Thanks a lot. Did'nt find this. This helped! Still, get_url() would be handy too... :- Questions: Don't see, why this would be a security issue: How could such a function do any harm? large files? Finally: Got some tricky followup questions regarding index usage in tsearch2 and regex. Should I place these here (or else where?)? Regards, S. 2009/5/19 Tom Lane t...@sss.pgh.pa.us Robert Haas robertmh...@gmail.com writes: On Mon, May 18, 2009 at 4:03 PM, Stefan Keller sfkel...@gmail.com wrote: I'd expect functions like get_text() or get_url() in order to do the following: INSERT INTO collection(id, path, content) VALUES(1, '/tmp/mytext, get_text('/tmp/mytext)); Apparently you've not found pg_read_file() ? AFAIK there was a get_url in libcurl but I neither find it any more. But anyway: This should be part of the core... :- Putting this into core would have security implications. The file or URL would be downloaded by the PostgreSQL server process, not the client process - therefore I think it would have to be super-user only, which would make it much less useful. Yes. I very strongly doubt that we'd accept a url-fetching function at all. Aside from the security issues, it would necessarily pull in a boatload of dependencies that we'd prefer not to have. Of course, you can write such a thing trivially in plperlu or several other untrusted PLs, and include any security restrictions you see fit while you're at it. I'm not seeing how a built-in function that would have to impose one-size-fits-all security requirements would be an improvement. regards, tom lane
[HACKERS] Proposal: functions get_text() or get_url()
How to insert a text file into a field in PostgreSQL? I'd like to insert a row with fields from a local or remote text file. I'd expect functions like get_text() or get_url() in order to do the following: INSERT INTO collection(id, path, content) VALUES(1, '/tmp/mytext, get_text('/tmp/mytext)); AFAIK there was a get_url in libcurl but I neither find it any more. But anyway: This should be part of the core... :- -S.