Re: [HACKERS] Forthcoming SQL standards about JSON and Multi-Dimensional Arrays (FYI)

[Stefan Keller]

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)

[Stefan Keller]

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)

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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?

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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.

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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

[Stefan Keller]

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()

[Stefan Keller]

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()

[Stefan Keller]

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()

[Stefan Keller]

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.