versioned pl/pgsql functions

On Tue, 2010-09-21 at 14:56 -0500, Rhys A.D. Stewart wrote:

hey all,

I think versioned pl/[pgsql|python|perl|bash|java] functions would be
a great addition to 9.1. Imagine that instead of CREATE OR REPLACE
FUNCTION you could do CREATE AND VERSION FUNCTION and then all
modifications to the function could be versioned so that you could
revert/rollback to a specific version of the function a la
git/svn...and then you could do diffs and what not so that you
wouldn't have to rely on text files which get lost because they were
on a tiny jump drive.....

Hmmm, it seems this could be done relatively simply with the ability to
put triggers on system catalogs. The tablelog contrib function would
allow us to do this.

Just my $1.70

Regards,

Rhys

Peace & Love|Live Long & Prosper

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

2010/9/21 Joshua D. Drake <jd@commandprompt.com>:

On Tue, 2010-09-21 at 14:56 -0500, Rhys A.D. Stewart wrote:

hey all,

I think versioned pl/[pgsql|python|perl|bash|java] functions would be
a great addition to 9.1. Imagine that instead of CREATE OR REPLACE
FUNCTION you could do CREATE AND VERSION FUNCTION and then all
modifications to the function could be versioned so that you could
revert/rollback to a specific version of the function a la
git/svn...and then you could do diffs and what not so that you
wouldn't have to rely on text files which get lost because they were
on a tiny jump drive.....

Hmmm, it seems this could be done relatively simply with the ability to
put triggers on system catalogs. The tablelog contrib function would
allow us to do this.

After ten years with stored procedures I am thinking so this is not
too well technique. Much better is writing stored procedures to a file
and using usual tools for file's versioning. We did some tools for
storing a versions inside database, but still we prefer a standard
developer tools - vi or emacs like editor and plain text. The main
problem of procedures maintained inside database is loss of outer
formatting and outer comments. And you cannot to group procedures to
modules. On the other hand - some history can be useful for all
database objects - not only for stored procedures.

Hey all,

After ten years with stored procedures I am thinking so this is not

too well technique. Much better is writing stored procedures to a file
and using usual tools for file's versioning. We did some tools for
storing a versions inside database, but still we prefer a standard
developer tools - vi or emacs like editor and plain text. The main
problem of procedures maintained inside database is loss of outer
formatting and outer comments. And you cannot to group procedures to
modules. On the other hand - some history can be useful for all
database objects - not only for stored procedures.

Agree with Pavel.

--
Regards,
Dmitriy

On Tuesday 21 September 2010, Dmitriy Igrishin elucidated thus:

Hey all,

After ten years with stored procedures I am thinking so this is not

too well technique. Much better is writing stored procedures to a
file and using usual tools for file's versioning. We did some tools
for storing a versions inside database, but still we prefer a
standard developer tools - vi or emacs like editor and plain text.
The main problem of procedures maintained inside database is loss
of outer formatting and outer comments. And you cannot to group
procedures to modules. On the other hand - some history can be
useful for all database objects - not only for stored procedures.

Agree with Pavel.

At the danger of sounding like a "me too:"

Agree with Pavel.

Keep version control in version control. Make creation/replacement of
stored procedures part of your deployment system.

j

--
Joshua Kugler
Part-Time System Admin/Programmer
http://www.eeinternet.com - Fairbanks, AK
PGP Key: http://pgp.mit.edu/ �ID 0x73B13B6A

On Tue, Sep 21, 2010 at 5:12 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2010/9/21 Joshua D. Drake <jd@commandprompt.com>:

On Tue, 2010-09-21 at 14:56 -0500, Rhys A.D. Stewart wrote:

hey all,

I think versioned pl/[pgsql|python|perl|bash|java] functions would be
a great addition to 9.1. Imagine that instead of CREATE OR REPLACE
FUNCTION you could do CREATE AND VERSION FUNCTION and then all
modifications to the function could be versioned so that you could
revert/rollback to a specific version of the function a la
git/svn...and then you could do diffs and what not so that you
wouldn't have to rely on text files which get lost because they were
on a tiny jump drive.....

Hmmm, it seems this could be done relatively simply with the ability to
put triggers on system catalogs. The tablelog contrib function would
allow us to do this.

After ten years with stored procedures I am thinking so this is not
too well technique. Much better is writing stored procedures to a file
and using usual tools for file's versioning. We did some tools for
storing a versions inside database, but still we prefer a standard
developer tools - vi or emacs like editor and plain text. The main
problem of procedures maintained inside database is loss of outer
formatting and outer comments. And you cannot to group procedures to
modules. On the other hand - some history can be useful for all
database objects - not only for stored procedures.

+ 1

also you are now managing your sql with the same tools you use for the
rest of the code. this is the way to go. the situation with views
argues even more for external management -- the original sql creating
the view is destroyed putting it in the database and it's not always
clear what the original intent was pulling it back out...

merlin

merlin

hi,

a few weeks ago i found a project aimed at providing version control for pq:
http://www.pgcon.org/2009/schedule/events/158.en.html
http://www.post-facto.org/
it worth to try it out, and use it ;)

regards,
kirk

On 09/21/10 21:56, Rhys A.D. Stewart wrote:

hey all,

I think versioned pl/[pgsql|python|perl|bash|java] functions would be
a great addition to 9.1. Imagine that instead of CREATE OR REPLACE
FUNCTION you could do CREATE AND VERSION FUNCTION and then all
modifications to the function could be versioned so that you could
revert/rollback to a specific version of the function a la
git/svn...and then you could do diffs and what not so that you
wouldn't have to rely on text files which get lost because they were
on a tiny jump drive.....

Just my $1.70

Regards,

Rhys

Peace & Love|Live Long & Prosper

--
Nagy Zoltan (kirk) <kirk@bteam.hu>

On Tue, Sep 21, 2010 at 7:28 PM, Joshua J. Kugler <joshua@eeinternet.com> wrote:

Keep version control in version control. Make creation/replacement of
stored procedures part of your deployment system.

+1