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 :
>> ... 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  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 :
> Thomas
>
> 2016-07-04 6:44 GMT+02:00 Thomas Munro :
>> ... 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 :
>> On Wed, Jun 29, 2016 at 11:51 AM, Stefan Keller  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&development=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 :
> ... 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 :
> On Wed, Jun 29, 2016 at 11:51 AM, Stefan Keller  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&development=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 :
> Hi, Stefan!
>
> On Sun, Oct 11, 2015 at 10:00 PM, Stefan Keller  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 :
> 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 :
> 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 :
> 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 :

> 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 :
> Stefan Keller  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 :
> 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 :
> 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 :
> 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]

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 :
> Le vendredi 9 mars 2012 16:50:05, Robert Haas a écrit :
>> On Fri, Mar 9, 2012 at 10:33 AM, Dimitri Fontaine
>>
>>  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

2012/3/11 Robert Haas :
> On Sat, Mar 10, 2012 at 4:35 PM, Stefan Keller  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_prewarm&a=1&submit=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]

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 :
> On Fri, Mar 9, 2012 at 10:53 AM, Jeff Janes  wrote:
>> On Fri, Mar 9, 2012 at 5:21 AM, Robert Haas  wrote:
>>> On Fri, Mar 9, 2012 at 5:24 AM, Fujii Masao  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 :
>
>
> On 02/02/2012 12:20 PM, Pavel Stehule wrote:
>>
>> 2012/2/2 Andrew Dunstan:
>>>
>>>
>>> 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:
> ,
> 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 :
> Hello,
>
> On Thu, Dec 22, 2011 at 12:51 PM, Benedikt Grundmann
>  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 :
> Jesper Krogh  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 :
> Hi Robert
>
> 2011/11/28 Robert Haas  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 :
>> On Sat, Nov 26, 2011 at 7:20 AM, Florian Schwendener  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  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 :
> On Sat, Nov 26, 2011 at 7:20 AM, Florian Schwendener  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 :
> Robert,
>
> 2011/9/6 Alexander Korotkov :
>> 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 :
> 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 :
> 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 

> On Wed, May 20, 2009 at 6:34 AM, Stefan Keller  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 

> Robert Haas  writes:
> > On Mon, May 18, 2009 at 4:03 PM, Stefan Keller 
> 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.