Re: Is possible to use Prepare/Execute inside a function?

On 15 Dec 2010, at 18:10, felipe(at)informidia(dot)com(dot)br wrote:

> Hello, I'm having problems with a function after the postgres server has been upgraded to 8.3.10 (I used 8.3.7). I did some testing and found some strange situations.

There shouldn't be any differences when upgrading between minor versions, they are bug-fix releases. If you found any, then that's either a bug in Postgres or a bug in your code.

To find out which, could you elaborate on how you performed said upgrade? Did you dump/restore your database or did you upgrade the binaries in place?
Were both versions compiled with the same settings (eg. integer datetimes)?

If you used dump/restore, are you sure your restore was successful and that no part of your data or code resulted in an error and therefore wasn't restored?

Also, what platform are you on? Is this Windows, some kind of Linux distribution, or what? What version?

> ------------------------------------------------------------------------------------------------------------
> -- not work with the direct call function (worked before the upgrade)
> select f_rodar_reportagem(44359, 193097);

First of all, what do you mean by "not work"? If you get an error, please post it.

What's the implementation of this function? It's possible that you were relying on a bug that got fixed between said releases.
A direct function call should just work, so something strange is going on here. Without the function body we can't tell though.

It's probably best to create a simple test case where you call a function directly (using select) and extend that towards your problem function's implementation until you manage to trigger the issue. If that doesn't work, you can always post the body of your function (unless you have reasons not to, of course).

> -- not work with the call by the trigger (a trigger calls a function and it calls f_rodar_reportagem using select and worked before the upgrade)
> update repesportesmt set
> estado = '1'
> where idrepesportesmt = 44359;

I suspect this has the same result as your earlier query? If not, please post the error message.

> -- WORK using Prepare/Execute
> prepare rodar(int, int) as select f_rodar_reportagem($1, $2);
> execute rodar(44359, 193097);

That is really strange and it's what made me think you may have been relying on a bug. I think the main difference between a direct statement and a prepared statement is that the prepared statement stores the query plan at preparation time, while the direct statement could execute different plans depending on the data. Apparently some of those plans cause your problem.
Posting them here may help as well.

If you look at the query plans for both, what do you see? Can you find any data with which the direct statement does work?

> I do not understand why not working after upgrade, but worked with prepare/execute, so I tried to use inside a function but did not work.
>
> -- got error in this line on params
> prepare rodar(int, int) as select f_rodar_reportagem($1, $2);

Again, post the error message please.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4d0912ac802659365313553!