Woah that is some wild postgresql | Hacker News Reader

Woah that is some wild postgresql

twitter.com

341 pointsprawn44 comments

Whenever the PostgreSQL Mandlebrot comes up, I always show off my Oracle version. It was written independently (i.e., I didn't port the PostgreSQL version; I didn't know about it at the time) and I documented the process:

http://xoph.co/20130917/mandelbrot-sql/

Of course, it comes with a hefty dose of proprietary functionality. I think the newer versions of Oracle support recursive CTEs -- but fortunately, I don't have to deal with Oracle any more, so it's dead to me ;)

> but fortunately, I don't have to deal with Oracle any more, so it's dead to me

I recently did a project where I got to use Oracle 12c. Bit of a hump getting back into it (last time I used Oracle was 8 in the early 2000s), but, man, it is amazing what you can do with it when you leverage all of its power.

I really hope they adjust their pricing model in the future to be friendly to non-billion-dollar corporations -- I'd love to use Oracle as the foundation for a startup, because the technology is decades ahead of where open source is, but, man, $48k/core... that's rough.

The SQLite documentation has a wonderfully titled section "Outlandish Recursive Query Examples" [1], which includes computation of a Mandelbrot Set.

[1] https://www.sqlite.org/lang_with.html

And the code from the OP picture is on the PostgreSQL Wiki since 2009:

https://wiki.postgresql.org/wiki/Mandelbrot_set

"This SQL query (requires PostgreSQL 8.4) produces an ASCII-art image of the Mandelbrot set. It is written entirely in SQL:2008-conformant SQL."

I like the one solving a sudoku puzzle, too. Impressive!

I thought about collecting some of these in the past.

Fun thing to do, put this in a `.ps` file and send it to a printer. Should think for a bit and then print a mandelbrot:

  %!PS-Adobe-2.0 51 .5 551 { /x exch def 111 .5 721 { /y exch def y 521 sub 201
  div x 301 sub 201 div 1 index 1 index 31 -1 1 { /n exch def 1 index dup mul 1
  index dup mul 1 index 1 index add /d exch def sub 4 index add 3 1 roll 2 mul
  mul 2 index add d 4 gt {exit} if } for pop pop pop pop n 31 div setgray x y
  moveto .5 0 rlineto 0 .5 rlineto -.5 0 rlineto fill } for } for showpage

I had a TSQL one as well, not mine though: https://www.growse.com/2009/05/01/odd-ways-of-making-a-mande...

Ohh I attempted to make one in PDF (it doesn't have any control flow so loops must be manually unrolled), but it turns out that most PDF readers doesn't actually follow the spec when displaying a PDF on a monitor (no you can't just throw the precision away internally just because the end result is going to be displayed with limited precision) - this includes Adobe Reader btw. I have been wanting to write a blog post about it for some time, but have never gotten around to get it done.

I'm sure I saw a way of doing it in Latex - I'm guessing that when you call pdftex (or whatever) then that's the point it calculates the output image, rather than letting the PDF reader do it?

PGF (LaTeX) indeed has a "Mandelbrot set" shading parameter[1]. Note: this is calculated by the PDF renderer. PDF has function shading and I think one can abuse it enough to render the Mandelbrot set. It certainly looks different in different viewers.

[1] http://mirror.switch.ch/ftp/mirror/tex/graphics/pgf/base/doc... Page 696

I did it in PDF by manually writing a custom tint transformation function. I used a 16x16 image with grayscale values from 0 to 255 and split the values in nibbles in the function code (which is a limited subset of postscript without things such as control flow). This worked fine, though a 16x16 Mandelbrot isn't very interesting.

So I tried with a 256x256 image instead with 16bpp, but it turns out that most viewers doesn't give a fuck and rounds the values down to 8 bits of precision before they are handed to the tint function.

Nice experiment. Some time ago I bumped into a similar problem when I experimented with SVGs lighting functions. SVG supports Phong shading based on a bump map. AFAIK you have to pass the bump map as the alpha channel of an image. Firefox only uses 8 bits for the alpha channel so the sphere I designed had steps on it.

Also Firefox used the sRGB non-linear colorspace for Phong shading producing incorrect results, but it was a different annoyance.

I just found my experiments. Code for the 16x16 Mandelbrot here: https://gist.github.com/poizan42/71f38038e8d91f91f8b8

Experimentation with creating 256x256 Mandelbrot here: https://gist.github.com/poizan42/3054232ad7624602a7fa

My sphere experiment:

https://jsfiddle.net/qb3pjojv/

Apparently Chromium has the same problem as Firefox, however the image doesn't look like the same in the two browsers. The Firefox version is much darker mostly because of the usage of the incorrect colorspace for shading.

One of these was floating around my university in the 90s. Looked great, but the computing service eventually started posting messages asking people not to run it, because it would cause a LaserJet II to hang for about an hour while it did the calculations.

Recursive common table expressions are indeed very powerful! You can even solve the eight queens problem in them:

http://www.andreas-dewes.de/en/2015/queens-of-the-data-age-a...

As others have pointed out there is a really nice section on CTEs in the SQLite documenation as well:

https://www.sqlite.org/lang_with.html

With these features that have been added to SQL over the past decades, you could probably at this point implement Datalog in it, or a Datalog-to-SQL translator. Might be an interesting experiment.

Copy-and-paste version here:

https://gist.github.com/rupey/3be62b4f511b4d4de8fc

I always loved Snowflakes[0] in postgresql. That said though, almost anything could be visualized in postgresql because of the awesome geometric[1] functions that are natively supported. Someday when I'm bored, I might try doing something like the Mona Lisa in postgres.

[0] http://explainextended.com/2013/12/31/happy-new-year-5/

[1] http://www.postgresql.org/docs/9.0/static/functions-geometry...

Any sufficiently Turing-complete system will be used to draw the Mandelbrot set.

After that, we move on to Zawinskie's law.

I always thought that the next step after Mandelbrot is usually running Doom.

I think it's mandelbrot -> read email -> lisp -> web server -> Doom -> operating system

Operating system would come before Doom, and web server probably the second thing after Mandelbrot set these days.

What does sufficiently Turing-complete mean? I thought a system is, or isn't?

I'm just weaseling out because I don't want to claim that every system that can draw Mandelbrots is Turing complete (not considering pathetic cases like "draw_mandelbrot" keywords/functions/parameters).

I'll leave the proof to interested readers.

Genuinely Turing-complete machines have infinite memory ("tape") and an arbitrarily large number of steps within which to complete an algorithm. I suppose "sufficiently Turing-complete" to mean that you can express an algorithm to the machine such that the algorithm can complete within the resources (RAM and time) that you have to give it.

I wonder if there's some sort of fractal rendering procedure like zooming/rotating/viewing, that could be turing complete with a given fractal. Would be a kind of cool hack.

Here's the slides of the source for this, including loads of other cool things that can be done with PG:

https://wiki.postgresql.org/images/0/0b/PGforSmarties.pdf

Another cool thing you can do is solve the 8-Queens problem. There is a great write up at : http://www.andreas-dewes.de/en/2015/queens-of-the-data-age-a...

Check out the pie chart...

https://wiki.postgresql.org/wiki/Category:Fun_Snippets

The Mandelbrot is awesome, but the pie chart is some next level stuff. You can even make it colored.

Can someone ELI5 this? And what is the image on the right? Thanks!

The Mandelbrot set (the image on the right) is a famous example of a fractal, a mathematical shape that gets more detailed the more you zoom in.

The code on the left is SQL, normally used to query databases, but in this case is being used to create the image on the right.

This is impressive because SQL isn't generally thought of as a language for arbitrary computation.

I still haven't taken the time to grok Recursive CTEs. I think it's mostly my internal set theorist putting up the fight :)

very nice, indeed.

it would be very impressive, if postgresql could generate it's own CRUD interface (select option: angular / react)

so much tedious frame-work could be just dropped.

You might be interested in this: https://github.com/begriffs/postgrest

"PostgREST serves a fully RESTful API from any existing PostgreSQL database. It provides a cleaner, more standards-compliant, faster API than you are likely to write from scratch."

Sidenote: Anyone had any experience using postgrest? Any good?

It's pretty nice to work with for internal CRUD applications and prototyping, once support for stored functions [0] is completed it'll be tremendously more useful.

0: https://github.com/begriffs/postgrest/issues/114

I don't think this is the database's job. However, lots of frameworks do code generation. With Yii you can supply a database and it'll generate the necessary controllers, models and views.

This makes me want to cry.. When do you ever not have ANY logic in CRUD?

Configuration data? Manual input of test data?

Well, I did a terminal-like, request/response user interface for PostgreSQL when I got carried away with a homework assignment. It had a menu system several layers deep.

I assume one could do the same by having it generate html and javascript.

Note that could != should.

http://rny.io/nginx/postgresql/2013/07/26/simple-api-with-ng...

Probably don't use it in production.

Why not? Currently the extension API could be used to create a server that directly serves web pages and is as secure as every other application.

Good luck with that.

Very possible with oracle apex and oracle rest data services, even with the free version of Oracle

that's nasty!!

Reply on news.ycombinator.com