Postgres OnLine Journal (Entries tagged as postgresql 9.2)

PostgreSQL 9.2 windows binaries for file_textarray_fdw

nospam@example.com (Leo Hsu and Regina Obe) — Sat, 19 Jan 2013 14:06:00 -0500

We discussed a while back the Text array foreign data wrapper that allows you to register and query a delimited file as if it were a regular table with one array column. It's probably the FDW we use most often and reminded of that recently when I had to query a 500,000 record resident list tab delimited file to prep for geocoding.

When we upgraded to 9.2 and we could no longer compile, I wrote to Andrew Dunstan about this and he kindly created a 9.2 version. Unfortunately there are still quite a few FDWs broken as a result of the 9.2 changes and I was hoping to try to apply similar patches to them that I saw Andrew do, but haven't had the patience or time yet. Anyway we've compiled these for 9.2 under our mingw64-w64 and mingw64-w32 chains using Andrew's 9.2 GitHub stable branch. https://github.com/adunstan/file_text_array_fdw/tree/REL9_2_STABLE and we've tested them using the PostgreSQL EDB windows VC++ compiled versions. We hope you find them as useful as we have.

I'm hoping to add more FDWs to these 9.2 bags once we have those working again. If you want to compile yourself or compile others, we have instructions in the packaged README.txt.

Using PLV8 to build JSON selectors

nospam@example.com (Leo Hsu and Regina Obe) — Sun, 23 Sep 2012 21:40:00 -0400

Unfortunately we missed Postgres Open this year, but we did catch some of the slides. One of them was Embracing the web with JSON and PLV8 by Will Leinweber of Heroku. He had a great slide deck with a lot of interesting points. One surprising for us was that even in tasks that both PL/PgSQL and PL/V8 can do, PL/V8 is sometimes faster as demonstrated in his slides: #51 thru #54

Another interesting point he covered which is the topic of this article is the ease with which you can build PLV8 functions from javascript libs on the web. In particular JSON:Select library. In one article we demonstrated a JQuery app with PLV8 and one of JQuery's foundations is the CSS like selector syntax it provides for JSON and HTML document elements which allows you to drill down a document using CSS3 style referencing, much like what xpath does for xml. One of the glaring features missing in PostgreSQL 9.2 basic JSON support is a function to navigate a JSON document comparable to the PostgreSQL built-in xpath function for xml. So how do we get this json selector goodness available to us in the database? Like all good monkeys, we copy/emulate it.

Continue reading "Using PLV8 to build JSON selectors"

Creating GeoJSON Feature Collections with JSON and PostGIS functions

nospam@example.com (Leo Hsu and Regina Obe) — Fri, 24 Aug 2012 02:55:00 -0400

If you do a lot of web-based GIS applications, a common desire is to allow a user to draw out an area on the map and then do searches against that area and return back a FeatureCollection where each feature is composed of a geometry and attributes about that feature. In the past the format was GML or KML, but the world seems to be moving to prefer JSON/GeoJSON. Normally you'd throw a mapping server that talks Web Feature Service , do more or less with a webscripting glue, or use a Webservice such as CartoDb that lets you pass along raw SQL.

In this article we'll demonstrate how to build GeoJSON feature collections that can be consumed by web mapping apps. using the built in JSON functions in PostgreSQL 9.2 and some PostGIS hugging. Even if you don't use PostGIS, we hope you'll come away with some techniques for working with PostgreSQL extended types and also how to morph relational data into JSON buckets.

Continue reading "Creating GeoJSON Feature Collections with JSON and PostGIS functions"

Schemas vs. Schemaless structures and The PostgreSQL Type Farm

nospam@example.com (Leo Hsu and Regina Obe) — Sun, 12 Aug 2012 05:18:00 -0400

There has been a lot of talk lately about schemaless models touted by NoSQL groups and how PostgreSQL fits into this New world order. Is PostgreSQL Object-Relational? Is it Multi-Model. We tend to think of PostgreSQL as type liberal and it's liberalness gets more liberal with each new release. PostgreSQL is fundamentally relational, but has little bias about what data types define each column of related tables. One of PostgreSQL great strengths is the ease with which different types can coexist in the same table and the flexible index plumbing and plan optimizer it provides that allows each type, regardless of how wild, to take full advantage of various index strategies and custom index bindings. Our 3 favorite custom non-built-in types we use in our workflow are PostGIS (of course), LTree (Hierarchical Type), and HStore (Key-Value type). In some cases, we may use all 3 in the same database and sometimes the same table - where we use PostGIS for spatial location, LTree for logical location, and Hstore just to keep track of random facts about an object that are easier to access than having a separate related table and are too random to warrant devoting a separate column for each. Sometimes we are guilty of using xml as well when we haven't figured out what schema model best fits a piece of data and hstore is too flat of a type to work. The advent of JSON in PostgreSQL 9.2 does provide for a nested schema-less model similar to what the XML type offers, but more JavaScript friendly. I personally see JSON as more of a useful transport type than one I'd build my business around or a type you'd use when you haven't figured out what if any structure is most suitable for your data. When you have no clue what structure a piece of data should be stored, you should let the data tell you what structure it wants to be stored in and only then will you discover by storing it in a somewhat liberal fashion how best to retrofit in a more structural self-descriptive manner. Schemas are great because they are self-describing, but they are not great when your data does not want to sit in a self-described bucket. You may find in the end that some data is just wild and refuses to stay between the lines and then by all means stuff it in xml or json or create a whole new type suit it feels comfortable in.

PLV8JS and PLCoffee Part 2B: PHP JQuery App

nospam@example.com (Leo Hsu and Regina Obe) — Fri, 10 Aug 2012 13:32:00 -0400

In our last article, PL/V8JS and PL/Coffee JSON search requests we demonstrated how to create a PostgreSQL PL/Javascript stored function that takes as input, a json wrapped search request. We generated the search request using PostgreSQL. As mentioned, in practice, the json search request would be generated by a client side javascript API such as JQuery. This time we'll put our stored function to use in a real web app built using PHP and JQuery. The PHP part is fairly minimalistic just involving a call to the database and return a single row back. Normally we use a database abstraction layer such as ADODB or PearDB, but this is so simple that we are just going to use the raw PHP PostgreSQL connection library directly. This example requires PHP 5.1+ since it uses the pg_query_param function introduced in PHP 5.1. Most of the work is happening in the JQuery client side tier and the database part we already saw. That said the PHP part is fairly trivial to swap out with something like ASP.NET and most other web server side languages.

Continue reading "PLV8JS and PLCoffee Part 2B: PHP JQuery App"

PLV8JS and PLCoffee Part 1: Upserting

nospam@example.com (Leo Hsu and Regina Obe) — Sat, 21 Jul 2012 13:13:00 -0400

Today's modern web application workflow in its simplest form looks something like this:

Get dataset as JSON object usually using yet another JSON query object to pass the request using a javascript framework like JQuery/ExtJS/OpenLayers/Leaflet etc.
Make changes to JSON dataset object and send back to the web server.
On webserver unravel the JSON object and save to respective database tables. This part is really yucky as it often involves the web application server side language doing the unraveling and then yet another step of setting up stored procedures or other update logic to consume it.

We hate the way people build tiers for the same reason Cartman hates lines at the amusement park. Sure tiers are great for certain things like building connected microcosms, but most of the time they are overkill and if applied too early make your application needlessly complicated. In the end all we care about is data: serving data, analyzing data, getting good data and everything else is just peacock feathers.

The introduction of JSON type support in PostgreSQL 9.2 and languages PL/V8 (PL/Javascript) and its Pythoness-like twin PL/Coffee provides several options for bringing your data and application closer together since they have native support for JSON. In this first part we'll demonstrate one: An upsert stored procedure that takes a single JSON object instead of separate args and updates existing data and adds missing records. In later articles we'll show you the front end app and also add a sprinkle of PostGIS in there to demonstrate working with custom types.

Continue reading "PLV8JS and PLCoffee Part 1: Upserting"

Building PLV8JS and PLCoffee for Windows using MingW64 w64-w32

nospam@example.com (Leo Hsu and Regina Obe) — Mon, 16 Jul 2012 18:55:00 -0400

As mentioned in our previous article Building on MingW deploying on VC we often build on MingW and deploy on Windows servers running EDB distributed VC PostgreSQL builds for extensions we want that don't come packaged. One of the new ones we are really excited about is the PL/V8 and PL/Coffee ones. Could we do it and would it actually work on a VC build. YES WE CAN and yes it does. I HAZ Coffee and a V8: .

Here are some instructions we hope others will find useful. Even if you aren't on Windows, you might still find them useful since MingW behaves much like other Unix environments.

UPDATE We rebuilt these against 9.2.1 and PLV8 1.3. Refer to PLV8 1.3 windows binaries for PostgreSQL 9.2 for further details.

If you are on windows, and just want to start using PLV8 and PLCoffee. We have binary builds for both PostgreSQL 9.2 Windows 32-bit (pg92plv8jsbin_w32.zip) and PostgreSQL 9.2 Windows 64-bit (pg92plv8jsbin_w64.zip) which you should be able to just extract into your PostgreSQL 9.2 beta windows install. We quickly tested with EDB VC++ builds and they seem to work fine on standard VC++ PostgreSQL 9.2beta2 installs. We haven't bothered building for lower PostgreSQL, but if there is some interest, we'd be happy to try.

Continue reading "Building PLV8JS and PLCoffee for Windows using MingW64 w64-w32"

PostgreSQL: Up and Running book officially out

nospam@example.com (Leo Hsu and Regina Obe) — Tue, 10 Jul 2012 14:53:00 -0400

Our new book PostgreSQL: Up and Running is officially out. It's available in hard-copy and e-Book version directly from O'Reilly, Safari Books Online and available from Amazon in Kindle store. It should be available in hard-copy within the next week or so from other distributors.

Sadly we won't be attending OSCON this year, but there are several PostgreSQL talks going on. If you are speaking at a talk or other PostgreSQL related get together, and would like to give out some free coupons of our book or get a free e-book copy for yourself to see if it's worth effort mentioning, please send us an e-mail: lr at pcorp.us .

Our main focus in writing the book is demonstrating features that make PostgreSQL uniquely poised for newer kinds of workflows with particular focus on PostgreSQL 9.1 and 9.2. Part of the reason for this focus is our roots and that we wanted to write a short book to get a feel for the audience. We started to use PostgreSQL in 2001 because of PostGIS, but were still predominantly SQL Server programmers. At the time SQL Server did not have a spatial component that integrated seamlessly with SQL. As die-hard SQLers, PostGIS really turned us on. As years went by, we began to use PostgreSQL not just for our spatial apps, but predominantly non-spatial ones as well that had heavy reporting needs and that we had a choice of platform. So we came for PostGIS but stayed because of all the other neat features PostgreSQL had that we found lacking in SQL Server. Three off the bat are arrays, regular expressions, and choice of procedural languages. Most other books on the market just treat PostgreSQL like it's any other relational database. In a sense that's good because it demonstrates that using PostgreSQL does not require a steep learning curve if you've used another relational database. We didn't spend as much time on these common features as we'd like to in the book because it's a short book and we figure most users familiar with relational databases are quite knowledgeable of common features from other experience. It's true that a lot of people coming to PostgreSQL are looking for cost savings, ACID compliance, cross-platform support and decent speed , but as PostgreSQL increases in speed, ease of features, and unique features, we think we'll be seeing more people migrating just because its simply better than any other databases for the new kinds of workflows we are seeing today -- e.g. BigData analysis, integration with other datasources, leveraging of domain specific languages in a more seamless way with data.

So what's that creature on the cover?

It's an elephant shrew (sengi) and is neither an elephant nor a shrew, but closest in ancestry to the elephant, sea cow, and aardvark. It is only found in Africa (mostly East Africa around Kenya) and in zoos. It gets its name from its unusually long nose which it uses for sniffing out insect prey and keeping tabs on its mate. It has some other unusual habits: it's a trail blazer building trails it uses to scout insect prey and also builds escape routes on the trail it memorizes to escape from predators. It's monogamous, but prefers to keep separate quarters from its mate. Males will chase off other males and females will chase off other females. It's fast and can usually out-run its predators.

PostgreSQL 9.2: Preserving column names of subqueries

nospam@example.com (Leo Hsu and Regina Obe) — Thu, 07 Jun 2012 01:17:00 -0400

There is another new feature in 9.2 that doesn't get much press, and probably because it's hard to explain. It is a pretty useful feature if you are working with the new json type or the existing hstore type. In prior versions if you used a subquery and converted the rows to hstore or json the column names were not preserved. Andrew mentioned a back-port path for this issue in Upgradeable JSON. We described a workaround for this issue in Mail merging using hstore. The workaround for including PostGIS geometry in json record output as described in Native JSON type support wouldn't work as nicely without this enhancement. Here is an example to demonstrate.

Continue reading "PostgreSQL 9.2: Preserving column names of subqueries"

PostgreSQL 9.2 Native JSON type support

nospam@example.com (Leo Hsu and Regina Obe) — Mon, 21 May 2012 17:39:00 -0400

One new welcome feature in PostgreSQL 9.2 is the native json support and companion row_as_json and array_as_json functions. PostGIS also has a json function for outputting geographies and geometries in GeoJSON format which is almost a standard in web mapping.

Here is an example of how you'd use the new feature - create our test table

CREATE TABLE test(gid serial PRIMARY KEY, title text, geog geography(Point, 4326));

INSERT INTO test(title, geog) 
 VALUES('a'
 , ST_GeogFromText('POINT(-71.057811 42.358274)'));

INSERT INTO test(title, geog) 
 VALUES('b'
 , ST_GeogFromText('POINT(42.358274 -71.057811 )'));

Now with a command like this we can output all data as a single json object.

SELECT array_to_json(array_agg(t))
FROM test As t;

But there is a tincy little problem. Our geog outputs don't look anything like GeoJSON format. Our output looks like this:

 [{"gid":1,"title":"a","geog":"0101000020E61000005796E82CB3C3
51C0E98024ECDB2D4540"}
,{"gid":2,"title":"b","geog":"0
101000020E6100000E98024ECDB2D45405796E82CB3C351C0"}]

To follow the GeoJSON standard, our geography object should output like this:

"geog":{"type":"Point","coordinates":[-71.057811000000001,42.358274000000002]}

Continue reading "PostgreSQL 9.2 Native JSON type support"

PostgreSQL 9.2 pg_dump enhancements

nospam@example.com (Leo Hsu and Regina Obe) — Mon, 21 May 2012 04:41:00 -0400

One of the things I'm excited about in PostgreSQL 9.2 are the new pg_dump section - pre-data, data, and post-data options and the exclude-table-data option. Andrew Dunstan blogged about this briefly in pg_dump exclude table data. What is also nice is that pgAdmin III 1.16 supports the section option via the graphical interface . I was a bit disappointed not to find the exclude-table-data option in pgAdmin III interface though. The other nice thing about this feature is that you can use the PostgreSQL 9.2 dump even against a 9.1 or lower db and achieve the same benefit.

The 9.2 pg_restore has similar functionality for restoring specific sections of a backup too.

So what is all this section stuff for. Well it comes in particularly handy for upgrade scripts. I'll first explain what the sections mean and a concrete example of why you want this.

pre-data - this would be the table structures, functions etc without the constraints such as check and primary key and indexes.
data -- it's uhm the data
post-data - This is all constraints, primary keys, indexes etc.

Continue reading "PostgreSQL 9.2 pg_dump enhancements"

Improving speed of GIST indexes in PostgreSQL 9.2

nospam@example.com (Leo Hsu and Regina Obe) — Wed, 12 Oct 2011 18:24:00 -0400

This is about improvements to GIST indexes that I hope to see in PostgreSQL 9.2. One is a patch for possible inclusion in PostgreSQL 9.2 called SP-GiST, Space-Partitioned GiST created by Teodor Sigaev and Oleg Bartunov whose basic technique is described in SP-GiST: An Extensible Database Index for Supporting Space Partitioning Trees. For those who don't know Teodor and Oleg, they are the great fellows that brought us many other GiST and GIN goodnesses that many specialty PostgreSQL extensions enjoy -- e.g. PostGIS, trigrams, ltree, pgsphere, hstore, full-text search to name a few.

Another is a recent one just committed by Alexander Korotkov which I just recently found out about on New node splitting algorithm for GIST and admit I don't know enough about to judge. I have to admit to being very clueless when it comes to the innards of index implementations so don't ask me any technical details. It's one of those short-comings among the trillion others I have that I have learned to accept will probably never change.

What the SP-GIST patch will provide in terms of performance and speed was outlined in PGCon 2011: SP-GiST - a new indexing infrastructure for PostgreSQL Space-Partitioning trees in PostgreSQL.

What it provides specifically for PostGIS is summarized in Paul's call for action noted below. As a passionate user of PostGIS ,ltree, tsearch, and hstore, I'm pretty excited about these patches and other GIST and general index enhancements and there potential use in GIST dependent extensions. I'm hoping to see these spring to life in PostgreSQL 9.2 and think it will help to further push the envelope of where PostgreSQL can go as a defacto platform for cutting-edge technology and scientific research. I think one of PostgreSQL's greatest strength is its extensible index API.

Paul's PostGIS newsgroup note about seeking funding for faster GIST indexes , work done so far on SP-GIST and call for further action is rebroadcast in it's entirety here.

Thanks to the sponsorship of Michigan Technological University, we now
have 50% of the work complete. There is a working patch at the
commitfest https://commitfest.postgresql.org/action/patch_view?id=631
which provides quad-tree and kd-tree indexes.

However, there is a problem: unless the patch is reviewed and goes
through more QA/QC, it'll never get into PostgreSQL proper. In case
you think I am kidding: we had a patch for KNN searching ready for the
9.0 release, but it wasn't reviewed in time, so we had to wait all the
way through the 9.1 cycle to get it.

I am looking for sponsors in the $5K to $10K range to complete this
work. If you use PostgreSQL in your business, this is a chance to add
a basic capability that may help you in all kinds of ways you don't
expect. We're talking about faster geospatial indexes here, but this
facility will also radically speed any partitioned space. (For
example, the suffix-tree, which can search through URLs incredibly
fast. Another example, you can use a suffix tree to very efficiently
index geohash strings. Interesting.)

If you think there's a possibility, please contact me and I will send
you a prospectus you can take to your manager. Let's make this happen
folks!

Paul

Continue reading "Improving speed of GIST indexes in PostgreSQL 9.2"