Thread: Trouble returning a text field from an SRF
I have written several postgres extensions in the past. However,currently I am having difficulty with an extension returning a tuple. Basically I want to return a tuple containing an integer and a very large variable length string. I created a type on Postgres that contains two fields (int4 and text). Originally, I was using buildTupleFromCStrings(). My function works fine and everything is cool, until my string becomes large. At that point NULL is returned in my tuple (I believe that this is because Postgres is using char type conversion on the string). So, my question is how can I return the large string as part of my tuple? Is there a Datum function that will allow me to convert a text* into a datum? Any help or a push in the right direction would be appreciated.
Thanks,
Rob.
Rob Tester escribió: > I have written several postgres extensions in the past. However,currently I > am having difficulty with an extension returning a tuple. Basically I want > to return a tuple containing an integer and a very large variable length > string. I created a type on Postgres that contains two fields (int4 and > text). Originally, I was using buildTupleFromCStrings(). My function works > fine and everything is cool, until my string becomes large. At that > point NULL is returned in my tuple (I believe that this is > because Postgres is using char type conversion on the string). So, my > question is how can I return the large string as part of my tuple? Is there > a Datum function that will allow me to convert a text* into a datum? Any > help or a push in the right direction would be appreciated. You can use things like DirectFunctionCall1(textin, CStringGetDatum(the-c-string)) which returns a Datum containing a Text with the C-string in it. I would normally try to use heap_form_tuple instead of buildTupleFromCStrings if possible, but I wouldn't think that the latter would fail on too large texts. It's hard to say what's failing without actually seeing the code anyway. Maybe the problem is failure to handle TOAST at some level. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
I created my tuple that way and set the Datum using
DirectFunctionCall1(...), but there still seems to be a limitation at 65535
chars for the data. When my string is 65535 (or shorter) I get the text
back. When it is longer I get nothing for the string, but the rest of the
tuple is intact.
My tuple descriptor:
CREATE TYPE testText AS (alevel integer, someText text);
Code in my pgLib extension:
//Code that handles the initial SRF stuff omitted
.
.
.
.
textOut=palloc(MIN_SIZE*count);
if (textOut){strcpy(textOut,"TEST=>");for(pointCnt=0;pointCnt<count;pointCnt++){ if (pointCnt!=0){
strcat(wktStr,","); } sprintf(buffer,"%4.8lf %4.8lf",0.0,0.0); strcat(textOut,buffer);}strcat(wktStr,"==>END");
values[0]= Int32GetDatum(strlen(textOut));values[1]= DirectFunctionCall1(textin,PointerGetDatum(textOut));
tuple=heap_formtuple(tupDesc, values, nulls);
result = HeapTupleGetDatum(tuple);
SRF_RETURN_NEXT(funcctx, result);
}
.
.
.
.
This function works great as long as strlen(textOut)<=65535 after that, I
get nothing for the someText value in my return type.
Any thoughts on how to get the rest of my strings when they are over 65535
bytes in length?
-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@commandprompt.com]
Sent: Sunday, May 13, 2007 11:12 AM
To: Rob Tester
Cc: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] Trouble returning a text field from an SRF
Rob Tester escribió:
> I have written several postgres extensions in the past. However,currently
I
> am having difficulty with an extension returning a tuple. Basically I want
> to return a tuple containing an integer and a very large variable length
> string. I created a type on Postgres that contains two fields (int4 and
> text). Originally, I was using buildTupleFromCStrings(). My function works
> fine and everything is cool, until my string becomes large. At that
> point NULL is returned in my tuple (I believe that this is
> because Postgres is using char type conversion on the string). So, my
> question is how can I return the large string as part of my tuple? Is
there
> a Datum function that will allow me to convert a text* into a datum? Any
> help or a push in the right direction would be appreciated.
You can use things like
DirectFunctionCall1(textin, CStringGetDatum(the-c-string))
which returns a Datum containing a Text with the C-string in it.
I would normally try to use heap_form_tuple instead of
buildTupleFromCStrings if possible, but I wouldn't think that the latter
would fail on too large texts. It's hard to say what's failing without
actually seeing the code anyway. Maybe the problem is failure to handle
TOAST at some level.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
"Rob Tester" <robtester@gmail.com> writes:
> I created my tuple that way and set the Datum using
> DirectFunctionCall1(...), but there still seems to be a limitation at 65535
> chars for the data. When my string is 65535 (or shorter) I get the text
> back. When it is longer I get nothing for the string, but the rest of the
> tuple is intact.
Maybe the problem is on the client side? There's no visible restriction
in the code you quoted (though I am wondering a bit about what "wktStr"
is, and whether you've computed the required size of textOut correctly).
regards, tom lane
Hmm..
wktStr was the original variable name from the code, I changed it to be more
'readable' for the question I was presenting (probably not a good idea in
hindsight). The length calculation has been changed as well and what I
presented would be a few bytes short.
I have tried this using pgadmin3 (v1.6.1) and npgsql with the same results,
no data returned in the text field when the data goes over 65535 chars.
Maybe both clients are incorrectly setting a limit on a text field?
Rob.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, May 14, 2007 2:01 PM
To: Rob Tester
Cc: 'Alvaro Herrera'; pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] Trouble returning a text field from an SRF
"Rob Tester" <robtester@gmail.com> writes:
> I created my tuple that way and set the Datum using
> DirectFunctionCall1(...), but there still seems to be a limitation at
65535
> chars for the data. When my string is 65535 (or shorter) I get the text
> back. When it is longer I get nothing for the string, but the rest of the
> tuple is intact.
Maybe the problem is on the client side? There's no visible restriction
in the code you quoted (though I am wondering a bit about what "wktStr"
is, and whether you've computed the required size of textOut correctly).
regards, tom lane
"Rob Tester" <robtester@gmail.com> writes:
> textOut=palloc(MIN_SIZE*count);
> if (textOut){
> strcpy(textOut,"TEST=>");
> for(pointCnt=0;pointCnt<count;pointCnt++){
> if (pointCnt!=0){
> strcat(wktStr,",");
> }
> sprintf(buffer,"%4.8lf %4.8lf",0.0,0.0);
> strcat(textOut,buffer);
> }
> strcat(wktStr,"==>END");
Fwiw, this isn't the cause of your 64k limit but if you're processing
thousands of data points this way you might consider rewriting it without the
strcats. As is it's a O(n^2) algorithm. Each time through the loop it'll have
to scan the entire string it already has built up (twice even). Instead just
keep a pointer to the end of the string and add your stuff there. You can use
strcpy and pass it the pointer to the end, or just call sprintf directly on
that pointer.
> tuple=heap_formtuple(tupDesc, values, nulls);
I'm curious about where you got the tupDesc and what it contains.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Thanks for the tip, I will correct that if I ever get the long strings to be
returned.
I use BlessTupleDesc() in the first SRF call as the following:
BlessTupleDesc(RelationNameGetTupleDesc("testText"));
As per a previous email testText is a Type that I created on the server
using:
CREATE TYPE testText AS (alevel integer, someText text);
-----Original Message-----
From: Gregory Stark [mailto:stark@enterprisedb.com]
Sent: Monday, May 14, 2007 4:30 PM
To: Rob Tester
Cc: 'Alvaro Herrera'; pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] Trouble returning a text field from an SRF
"Rob Tester" <robtester@gmail.com> writes:
> textOut=palloc(MIN_SIZE*count);
> if (textOut){
> strcpy(textOut,"TEST=>");
> for(pointCnt=0;pointCnt<count;pointCnt++){
> if (pointCnt!=0){
> strcat(wktStr,",");
> }
> sprintf(buffer,"%4.8lf %4.8lf",0.0,0.0);
> strcat(textOut,buffer);
> }
> strcat(wktStr,"==>END");
Fwiw, this isn't the cause of your 64k limit but if you're processing
thousands of data points this way you might consider rewriting it without
the
strcats. As is it's a O(n^2) algorithm. Each time through the loop it'll
have
to scan the entire string it already has built up (twice even). Instead just
keep a pointer to the end of the string and add your stuff there. You can
use
strcpy and pass it the pointer to the end, or just call sprintf directly on
that pointer.
> tuple=heap_formtuple(tupDesc, values, nulls);
I'm curious about where you got the tupDesc and what it contains.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com
"Rob Tester" <robtester@gmail.com> writes:
> I have tried this using pgadmin3 (v1.6.1) and npgsql with the same results,
> no data returned in the text field when the data goes over 65535 chars.
> Maybe both clients are incorrectly setting a limit on a text field?
What happens when you try it in plain old psql?
regards, tom lane