[GENERAL] Problem with Numerics multiplication in C-function
Hi, I have a strange problem with Numeric multiplication in C-function.
There are 2 functions getRate and getVAT which return Numeric. In 3rd
function calculateService I try to multiply the results of getRate and
getVAT. After execution I have two types of error, some time without
messages and with message "Invalid memory alloc ... ".
If I initialize the Numeric variables inside calculateService and multiply,
function numeric_mul works fine.
PostgreSQL 8.3.7
Datum getRate(PG_FUNCTION_ARGS) {
int32 accountId = PG_GETARG_INT32(0);
int16 serviceId = PG_GETARG_INT16(1);
DateADT date = PG_GETARG_DATEADT(2);
bool isNull;
char command[QUERY_MAX_SIZE];
char message[MESSAGE_MAX_SIZE];
Numeric rate = DatumGetNumeric(DirectFunctionCall3(numeric_in,
CStringGetDatum(RATE_ERROR_CSTRING_VALUE), 0, -1));
//Build SQL query
snprintf(command, sizeof (command), "...");
SPI_connect();
SPI_execute(command, true, 0);
rate = DatumGetNumeric(SPI_getbinval(SPI_tuptable->vals[0],
SPI_tuptable->tupdesc,
1,
&isNull));
#ifdef PR_DEBUG
snprintf(message, sizeof (message), " getRate: Returns rate =
%s.",
DatumGetCString(DirectFunctionCall1(numeric_out,
NumericGetDatum(rate;
elog(INFO, message);
#endif
SPI_finish();
PG_RETURN_NUMERIC(rate);
}
Datum calculateService(PG_FUNCTION_ARGS) {
// Like the getRate
}
Datum calculateService(PG_FUNCTION_ARGS) {
int32 accountId = PG_GETARG_INT32(0);
int16 serviceId = PG_GETARG_INT16(1);
DateADT date = PG_GETARG_DATEADT(2);
int32 transactionRegisterId = PG_GETARG_INT32(3);
Numeric rate;
Numeric vat;
Numeric amount;
rate = DatumGetNumeric(DirectFunctionCall3(getRate,
Int32GetDatum(accountId),
Int16GetDatum(serviceId),
DateADTGetDatum(date)));
vat = DatumGetNumeric(DirectFunctionCall1(getVAT,
DateADTGetDatum(date)));
#ifdef PR_DEBUG
snprintf(message, sizeof (message), " calculateService: rate =
%s, vat",
DatumGetCString(DirectFunctionCall1(numeric_out,
NumericGetDatum(rate;
elog(INFO, message);
#endif
amount = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
NumericGetDatum(rate),
NumericGetDatum(vat)));
// ERROR
...
}
--
Best regards,
Ilya Urikh.
[GENERAL] Problem with Numerics multiplication in C-function
Hi, I have a strange problem with Numeric multiplication in C-function.
There are 2 functions getRate and getVAT which return Numeric. In 3rd
function calculateService I try to multiply the results of getRate and
getVAT. After execution I have two types of error, some time without
messages and with message "Invalid memory alloc ... ".
If I initialize the Numeric variables inside calculateService and
multiply, function numeric_mul works fine.
PostgreSQL 8.3.7
Datum getRate(PG_FUNCTION_ARGS) {
int32 accountId = PG_GETARG_INT32(0);
int16 serviceId = PG_GETARG_INT16(1);
DateADT date = PG_GETARG_DATEADT(2);
bool isNull;
char command[QUERY_MAX_SIZE];
char message[MESSAGE_MAX_SIZE];
Numeric rate = DatumGetNumeric(DirectFunctionCall3(numeric_in,
CStringGetDatum(RATE_ERROR_CSTRING_VALUE), 0, -1));
//Build SQL query
snprintf(command, sizeof (command), "...");
SPI_connect();
SPI_execute(command, true, 0);
rate = DatumGetNumeric(SPI_getbinval(SPI_tuptable->vals[0],
SPI_tuptable->tupdesc,
1,
&isNull));
#ifdef PR_DEBUG
snprintf(message, sizeof (message), " getRate: Returns rate =
%s.",
DatumGetCString(DirectFunctionCall1(numeric_out,
NumericGetDatum(rate;
elog(INFO, message);
#endif
SPI_finish();
PG_RETURN_NUMERIC(rate);
}
Datum calculateService(PG_FUNCTION_ARGS) {
// Like the getRate
}
Datum calculateService(PG_FUNCTION_ARGS) {
int32 accountId = PG_GETARG_INT32(0);
int16 serviceId = PG_GETARG_INT16(1);
DateADT date = PG_GETARG_DATEADT(2);
int32 transactionRegisterId = PG_GETARG_INT32(3);
Numeric rate;
Numeric vat;
Numeric amount;
rate = DatumGetNumeric(DirectFunctionCall3(getRate,
Int32GetDatum(accountId),
Int16GetDatum(serviceId),
DateADTGetDatum(date)));
vat = DatumGetNumeric(DirectFunctionCall1(getVAT,
DateADTGetDatum(date)));
#ifdef PR_DEBUG
snprintf(message, sizeof (message), " calculateService: rate
= %s, vat",
DatumGetCString(DirectFunctionCall1(numeric_out,
NumericGetDatum(rate;
elog(INFO, message);
#endif
amount = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
NumericGetDatum(rate),
NumericGetDatum(vat)));
// ERROR
...
}
Best regards,
Ilya Urikh.
Re: [GENERAL] Problem with Numerics multiplication in C-function
Thank's a lot! That's really help. I failed to bear memory context switching in mind. I resolved this problem with following strings in *getRate*: result = SPI_palloc(sizeof(rate)); memcpy(result, rate, sizeof(rate)); P.S. I think it's a good idea to add example to documentation. Anyway when I had started to work with PostgreSQL I was very surprised at so clear and structured documentation. :) On Tue, Aug 4, 2009 at 1:44 AM, Martijn van Oosterhout wrote: > On Mon, Aug 03, 2009 at 10:20:36AM -0400, Tom Lane wrote: > > Martijn van Oosterhout writes: > > > What you need to do is copy the Datum you wish to return into the upper > > > memory context. I'm not seeing a helpful SPI utility function or > > > obvious example here you help you, but I'm sure it's something with > > > MemoryContextSwitchTo() and datumCopy(). > > > > SPI_palloc is what to use to allocate the result in the right place. > > Ofcourse, and then a memcpy(). I was distracted by the fact there is an > SPI_copy/returntuple(), but no SPI_returndatum(). It might actually be > helpful to include in the documentation an example of returning from a > function a result from SPI, since it's not entirely obvious. > > Have a nice day, > -- > Martijn van Oosterhout http://svana.org/kleptog/ > > Please line up in a tree and maintain the heap invariant while > > boarding. Thank you for flying nlogn airlines. > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iD8DBQFKdvezIB7bNG8LQkwRAiwGAJ0dyrP+JROL9F/OhKvzLlR5O2h4tACdEIAE > QvCDFfLfnzCpkti8PXcVp38= > =ZXiG > -END PGP SIGNATURE- > > -- Best regards, Ilya Urikh.
[GENERAL] Problem with using snprintf in C-function
Hi,
I have a problem with snprintf function which insert the variables to string
with error.
This code I wrote for PostgreSQL 8.3.7 on Fedora 10 and it worked fine. But
now I use CentOS 5.3 and PostgreSQL 8.3.8.
Function:
Datum calculateAccount(PG_FUNCTION_ARGS) {
int64 accountId = PG_GETARG_INT64(0);
DateADT startDate = PG_GETARG_DATEADT(1);
DateADT endDate = PG_GETARG_DATEADT(2);
charcommand[QUERY_MAX_SIZE];
charstartDateStr[MAXDATELEN + 3];
charendDateStr[MAXDATELEN + 3];
snprintf(startDateStr, sizeof (startDateStr), "'%s'",
DatumGetCString(DirectFunctionCall1(date_out, startDate)));
snprintf(endDateStr, sizeof (endDateStr), "'%s'",
DatumGetCString(DirectFunctionCall1(date_out, endDate)));
elog(INFO, startDateStr);
elog(INFO, endDateStr);
snprintf(command, sizeof (command),
"SELECT serviceId, periodStartDate, periodEndDate\
FROM accountServiceBaseView\
WHERE accountId = %ld AND\
periodStartDate <= %s AND\
periodEndDate >= %s;",
accountId, startDateStr, endDateStr);
elog(INFO, command);
PG_RETURN_BOOL(true);
}
Result:
select calculateaccount(123, '01-01-2009', '01-02-2009');
INFO: '2009-01-01'
INFO: '2009-02-01'
INFO: SELECT serviceId, periodStartDate, periodEndDate FROM
accountServiceBaseView WHERE accountId = 123 AND periodStartDate <= (null)
AND periodEndDate >= '2009-01-01';
If I change the order of parameters to "periodStartDate <= %s AND
periodEndDate >= %s AND accountId = %ld", all works fine. Unfortunately this
method can not be applied to other functions of my module.
--
Best regards,
Ilya Urikh.
Re: [GENERAL] Problem with using snprintf in C-function
Pavel,
Could you provide some example? I don't really understand which placeholder
I can use.
On Sun, Oct 18, 2009 at 1:20 AM, Pavel Stehule wrote:
> 2009/10/17 Ilya Urikh :
> > Hi,
> >
> > I have a problem with snprintf function which insert the variables to
> string
> > with error.
> > This code I wrote for PostgreSQL 8.3.7 on Fedora 10 and it worked fine.
> But
> > now I use CentOS 5.3 and PostgreSQL 8.3.8.
> >
> > Function:
> > Datum calculateAccount(PG_FUNCTION_ARGS) {
> > int64 accountId = PG_GETARG_INT64(0);
> > DateADT startDate = PG_GETARG_DATEADT(1);
> > DateADT endDate = PG_GETARG_DATEADT(2);
> >
> > charcommand[QUERY_MAX_SIZE];
> > charstartDateStr[MAXDATELEN + 3];
> > charendDateStr[MAXDATELEN + 3];
> >
> > snprintf(startDateStr, sizeof (startDateStr), "'%s'",
> > DatumGetCString(DirectFunctionCall1(date_out, startDate)));
> > snprintf(endDateStr, sizeof (endDateStr), "'%s'",
> > DatumGetCString(DirectFunctionCall1(date_out, endDate)));
> > elog(INFO, startDateStr);
> > elog(INFO, endDateStr);
> > snprintf(command, sizeof (command),
> > "SELECT serviceId, periodStartDate, periodEndDate\
> > FROM accountServiceBaseView\
> > WHERE accountId = %ld AND\
> > periodStartDate <= %s AND\
> > periodEndDate >= %s;",
> > accountId, startDateStr, endDateStr);
> > elog(INFO, command);
> >
>
> Hello
>
> my reply is little bit offtopic. Why you convert date values to
> string? You can use placeholders and execute query with parameters in
> native format. It's more simply and safe.
>
> Regards
> Pavel Stehule
>
>
>
>
> > PG_RETURN_BOOL(true);
> > }
> >
> > Result:
> > select calculateaccount(123, '01-01-2009', '01-02-2009');
> > INFO: '2009-01-01'
> > INFO: '2009-02-01'
> > INFO: SELECT serviceId, periodStartDate, periodEndDate FROM
> > accountServiceBaseView WHERE accountId = 123 AND periodStartDate <=
> (null)
> > AND periodEndDate >= '2009-01-01';
> >
> >
> > If I change the order of parameters to "periodStartDate <= %s AND
> > periodEndDate >= %s AND accountId = %ld", all works fine. Unfortunately
> this
> > method can not be applied to other functions of my module.
> >
> >
> > --
> > Best regards,
> > Ilya Urikh.
> >
>
--
Best regards,
Ilya Urikh.
