How to automatically propagate new/changed database functions from one database to another

On Fri, 2009-06-05 at 13:12 -0400, Atul Chojar wrote:

We want to implement a mechanism by which if any database function is
created or modified in one database, then the same should
automatically get done in another (1 or more) database(s).

It seems triggers on system catalogs like pg_proc are not allowed. Is
there any way in postgres to do this, by some other way?

This is the wrong away to go about it. You should put this into your
development process not within the database itself. There are a number
of utilities that would allow you to do such a thing.

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

Could you give some examples of such utilities?

Thanks!
atul

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joshua D. Drake
Sent: Friday, June 05, 2009 1:37 PM
To: Atul Chojar
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to automatically propagate new/changed database
functions from one database to another

On Fri, 2009-06-05 at 13:12 -0400, Atul Chojar wrote:

We want to implement a mechanism by which if any database function is
created or modified in one database, then the same should
automatically get done in another (1 or more) database(s).

It seems triggers on system catalogs like pg_proc are not allowed. Is
there any way in postgres to do this, by some other way?

This is the wrong away to go about it. You should put this into your
development process not within the database itself. There are a number
of utilities that would allow you to do such a thing.

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

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

We recently upgraded from postgres version 8.2.7 to 8.3.7. The below
pl/pgsql test function behaves differently in the 2 versions.

The code of the function is :-

CREATE OR REPLACE FUNCTION "public"."testloop" () RETURNS varchar AS

$body$

BEGIN

FOR idx IN 1..10 LOOP

raise notice 'idx=%', idx;

idx:=idx+1;

END LOOP;

return '';

END;

$body$

LANGUAGE 'plpgsql'

The sql :-

select testloop();

in 8.2.7 returns:-

NOTICE: idx=1

NOTICE: idx=3

NOTICE: idx=5

NOTICE: idx=7

NOTICE: idx=9

But in 8.3.7 returns:-

NOTICE: idx=1

NOTICE: idx=2

NOTICE: idx=3

NOTICE: idx=4

NOTICE: idx=5

NOTICE: idx=6

NOTICE: idx=7

NOTICE: idx=8

NOTICE: idx=9

NOTICE: idx=10

So in 8.3.7, the incrementing of the for-loop variable "idx" is being
ignored; that is not the case in 8.2.7. Is this a new feature of 8.3.7 or a
bug?

Since a lot of our functions depend on manual altering of for-loop
variables, any prompt help/advise/suggestions would be greatly appreciated!

Thanks!

atul

<http://www.airfacts.com/&gt; AirFacts, Inc.
8120 Woodmont Ave., Suite 700
Bethesda, MD 20814
Tel: 301-760-7315

We recently upgraded from postgres version 8.2.7 to 8.3.7. The below
pl/pgsql test function behaves differently in the 2 versions.

The code of the function is :-

CREATE OR REPLACE FUNCTION "public"."testloop" () RETURNS varchar AS

$body$

BEGIN

FOR idx IN 1..10 LOOP

raise notice 'idx=%', idx;

idx:=idx+1;

END LOOP;

return '';

END;

$body$

LANGUAGE 'plpgsql'

The sql :-

select testloop();

in 8.2.7 returns:-

NOTICE: idx=1

NOTICE: idx=3

NOTICE: idx=5

NOTICE: idx=7

NOTICE: idx=9

But in 8.3.7 returns:-

NOTICE: idx=1

NOTICE: idx=2

NOTICE: idx=3

NOTICE: idx=4

NOTICE: idx=5

NOTICE: idx=6

NOTICE: idx=7

NOTICE: idx=8

NOTICE: idx=9

NOTICE: idx=10

So in 8.3.7, the incrementing of the for-loop variable "idx" is being
ignored; that is not the case in 8.2.7. Is this a new feature of 8.3.7 or a
bug?

Since a lot of our functions depend on manual altering of for-loop
variables, any prompt help/advise/suggestions would be greatly appreciated!

Thanks!

atul

<http://www.airfacts.com/&gt; AirFacts, Inc.
8120 Woodmont Ave., Suite 700
Bethesda, MD 20814
Tel: 301-760-7315

"Atul Chojar" <achojar@airfacts.com> writes:

So in 8.3.7, the incrementing of the for-loop variable "idx" is being
ignored; that is not the case in 8.2.7. Is this a new feature of 8.3.7 or a
bug?

It's the new implementation. Depending on unspecified implementation
details is a good way to have broken code.

regards, tom lane

Hello

2009/6/10 Tom Lane <tgl@sss.pgh.pa.us>:

"Atul Chojar" <achojar@airfacts.com> writes:

So in 8.3.7, the incrementing of the for-loop variable "idx" is being
ignored; that is not the case in 8.2.7. Is this a new feature of 8.3.7 or a
bug?

It's the new implementation.  Depending on unspecified implementation
details is a good way to have broken code.

                       regards, tom lane

we should to mark control varables as read-only?

regards
Pavel Stehule

On Wed, Jun 10, 2009 at 04:51:44PM -0400, Tom Lane wrote:

It's the new implementation. Depending on unspecified implementation
details is a good way to have broken code.

i'm not sure if it's good change. there might be perfectly good reasons
to increment idx from within loop.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

2009/6/11 hubert depesz lubaczewski <depesz@depesz.com>:

On Wed, Jun 10, 2009 at 04:51:44PM -0400, Tom Lane wrote:

It's the new implementation.  Depending on unspecified implementation
details is a good way to have broken code.

i'm not sure if it's good change. there might be perfectly good reasons
to increment idx from within loop.

generally - modification of cycle's control variable isn't good
technique, because it's should be broken by some optimizations. When
you would to modify this some variables, then use "while-loop"
instead.

regards
Pavel Stehule

On Thu, Jun 11, 2009 at 12:45:56PM +0200, Pavel Stehule wrote:

generally - modification of cycle's control variable isn't good
technique, because it's should be broken by some optimizations. When

i would argue then that these optimizations are broken, then.

you would to modify this some variables, then use "while-loop"
instead.

while technically possible, i find for loops much more straight forward,
and clearer to understand.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

2009/6/11 hubert depesz lubaczewski <depesz@depesz.com>:

On Thu, Jun 11, 2009 at 12:45:56PM +0200, Pavel Stehule wrote:

generally - modification of cycle's control variable isn't good
technique, because it's should be broken by some optimizations. When

i would argue then that these optimizations are broken, then.

you would to modify this some variables, then use "while-loop"
instead.

while technically possible, i find for loops much more straight forward,
and clearer to understand.

sure, forloop is clean, but not when you do some alchemy with control
variables. When you need increase step, then use BY clause. But I
thing, so it's easy protect users by marking control variables as read
only variable.

regards
Pavel Stehule