Re: [GENERAL] Performance PLV8 vs PLPGSQL

The reason that you cannot use git for that is that it was never intended for live data and stuff that resides on a database.

That said, I agree with you that all table DDLs and stored procedures should be kept under version control. And in fact I do keep them under VC on my database. How do I do this? I have a directory in which I keep all my SQL scripts, and this directory is a git repo. In this directory/project I keep:

1. For every table that is created on the database, I have an SQL script file with the DDL that creates it

2. For every stored proc that I create, I put it in its own SQL script as well

3. For any (relatively) static lookup tables (i.e. state-region associations, etc.), they are put in a SQL script

This workflow gives me several important benefits, including:

1. Everything needed to recreate the database is kept under version control, including the DDL, stored procedures and lookup tables

2. This script folder does not need to reside on the DB server, can be kept anywhere, even on my local laptop; I pull them up in DataGrip and run them on the server (my scripts are on my laptop)

3. All these scripts are VC'd under git

4. They are easily and quickly pushed to my remote repo on Github and Bitbucket for backup

5. When I need to create a new fresh, empty copy of the database somewhere, I just run these scripts and it is done quickly and easily

One more little trick I have is to use Gitkraken for my git GUI. It. is free, and is absolutely the best git GUI available. The devs made it an absolute joy to use, and I never need to use the command line anymore.

Hope this helps!

On Dec 29, 2016, at 10:43 PM, Tim Uckun <timuckun@gmail.com> wrote:

I have datagrip and it's OK but it doesn't really do everything I want.

I don't understand why it doesn't fetch all objects from the database and then put them into the disk in a directory so I can put it all under git and then let me work on them syncing the files back as they change.  For example today I just renamed a function. It didn't refactor properly by identifying stored procs that reference it. If I was using another jetbrains IDE it would have built an index of the project files and did a proper refactor.

This would also allow you to make wholesale disk changes and then sync them up properly to get around postgres dependency issues.

On Fri, Dec 30, 2016 at 1:40 PM, Michael Sheaver <msheaver@me.com> wrote:

If you want an IDE, Jetbrains, the makers of great IDEs like IntelliJ, PyCharm. and AppCode, among others, have recently come out with what is arguably the BEST IDE for DBAs, DataGrip. It runs on most major platforms, and is so good that I have bitten the bullet and paid the yearly subscription for it.

Leave the Postgres core alone focus on what they do best, and that is making the very BEST database environment that can be had at any price, period. Is Postgres perfect? No, not at all. But no other group is so focused on performance, scalability and security as these folks are. And the rate of development, enhancement and continual improvement is, quite honestly, astounding. 

So here is my hat tip to the Postgres team for an awesome job they are doing!

On Dec 29, 2016, at 7:19 PM, Tim Uckun <timuckun@gmail.com> wrote:

I am not saying the postgres core people should work on an IDE, just that an IDE like thing would be nice.

On Fri, Dec 30, 2016 at 12:51 PM, Rob Sargent <robjsargent@gmail.com> wrote:

I would hope Postgres core folk take no more than a nanosecond to reject the idea that they work on an IDE. Focus on reading and writing faster and faster ACID all the while. 

On Dec 29, 2016, at 5:32 PM, Tim Uckun <timuckun@gmail.com> wrote:

Honestly I don't even like JS. Having said that I am not too crazy about PL-PGSQL either. I am willing to put up with either given that they are supported widely in default installs of postgres in AWS, Linux and MacOSX,

As I said before, I think posgres gives a unique and underutilized language platform. You can code in different languages, it has a good variety of built in types, and of course you get persistance and caching built in!  Using DBLINK you might even be able to separate out your code from the bulk of your data in another database. Postgres all the way down!

It's fun to play around with.  There is a lot of missing pieces though. A good IDE like thing would be good, version control would be nice, deeper namespacing (hierarchical schemas?), easier testing etc would go a long way. 

Thanks for all the input guys! 

On Fri, Dec 30, 2016 at 12:14 AM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:

On 12/29/2016 10:35 AM, Pavel Stehule wrote:

2016-12-29 10:03 GMT+01:00 Tim Uckun <timuckun@gmail.com
<mailto:timuckun@gmail.com>>:

    I think it's awesome that postgres allows you to code in different
    languages like this. It really is a unique development environment
    and one that is overlooked as a development platform.  It would be
    nice if more languages were delivered in the default package
    especially lua, V8 and mruby.


It is about dependencies and maintenance. There are not too much people
who has good experience with C embedding Lua, V8 and others. Any people
who can do some work are welcome.

The living outside main package has disadvantages - only enthusiast
knows about it, but some advantages too - you are not fixed on
PostgreSQL development cycle, and development can be faster.

I'll add my 2 cents.

Postgresql and in general SQL are about integrity and coherency.
Checking coherency is much easier with strict data type.
PL/PGSQL gives you that, JS is far far away from that.

Postgresql is a very flexible database and you can stretch it to do "MEAN like"[1] stuff but that's going to increase your "impedance mismatch".

If you think there is some space for JS in your application stack that's nearer to the client rather than to the DB.
Or possibly you need to do "MEAN like" stuff but you don't want to install another "database".

As other said using stored procedures is a two edged sword.
It can decouple DB schema from the application or it can increase the coupling.
Choosing JS for performance in the stored procedure realm is going to encourage coupling and make scalability harder and it is going to become a mess when you'll need to refactor.

[1] https://en.wikipedia.org/wiki/MEAN_(software_bundle)

--
Ivan Sergio Borgonovo
http://www.webthatworks.it http://www.borgonovo.net

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general