Thread: BUG: text(varchar) truncates at 31 bytes
#create table t (v varchar);
#insert into t values ('0123456789a0123456789b0123456789c0123456789d');
#select v from t;
v
----------------------------------------------0123456789a0123456789b0123456789c0123456789d
(1 row)
So far, so good.
#select text(v) from t;
text
---------------------------------0123456789a0123456789b012345678
(1 row)
Truncation occurs.
Work around:
# select v::text from t; ?column?
----------------------------------------------0123456789a0123456789b0123456789c0123456789d
(1 row)
I couldnt figure out what happens during a text(varchar) call. I looked
around in pg_proc, but couldnt find the function. There's probably an
automagic type conversion going on or something.
Could someone explain what all the internal varchar-like types are (ie.
varchar,varchar(n),text,char,_char,bpchar) and when they're used? I
find it all really confusing - I'm sure others do too.
Is there anyway to determine what postgresql is doing in its automagic
function calls? I guess I'm asking for an EXPLAIN that describes
function calls. For example,
EXPLAIN select text(v) from t;
--> {Description of conversion from varchar to whatever the text()
function actually works on}
Thanks,
dave
I can confirm this problem exists in current sources. Quite strange.
> #create table t (v varchar);
> #insert into t values ('0123456789a0123456789b0123456789c0123456789d');
>
> #select v from t;
>
> v
> ----------------------------------------------
> 0123456789a0123456789b0123456789c0123456789d
> (1 row)
>
> So far, so good.
>
> #select text(v) from t;
>
> text
> ---------------------------------
> 0123456789a0123456789b012345678
> (1 row)
>
> Truncation occurs.
>
> Work around:
>
> # select v::text from t;
> ?column?
> ----------------------------------------------
> 0123456789a0123456789b0123456789c0123456789d
> (1 row)
>
> I couldnt figure out what happens during a text(varchar) call. I looked
> around in pg_proc, but couldnt find the function. There's probably an
> automagic type conversion going on or something.
>
> Could someone explain what all the internal varchar-like types are (ie.
> varchar,varchar(n),text,char,_char,bpchar) and when they're used? I
> find it all really confusing - I'm sure others do too.
>
> Is there anyway to determine what postgresql is doing in its automagic
> function calls? I guess I'm asking for an EXPLAIN that describes
> function calls. For example,
> EXPLAIN select text(v) from t;
>
> --> {Description of conversion from varchar to whatever the text()
> function actually works on}
>
>
> Thanks,
> dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026
> #select text(v) from t; > > text > --------------------------------- > 0123456789a0123456789b012345678 > (1 row) > > Truncation occurs. Looking at the explain verbose output, it looks like it may be doing a conversion to name because it looks like there isn't a text(varchar), but there's a text(name) and a name(varchar). My guess is there's no text(varchar) because they're considered binary compatible. > Work around: > > # select v::text from t; > ?column? > ---------------------------------------------- > 0123456789a0123456789b0123456789c0123456789d > (1 row) These types are probably marked as binary compatible, so nothing major has to happen in the type conversion. Same thing happens in CAST(v AS text). > Is there anyway to determine what postgresql is doing in its automagic > function calls? I guess I'm asking for an EXPLAIN that describes > function calls. For example, > EXPLAIN select text(v) from t; You can use EXPLAIN VERBOSE if you're willing to wade through the output. :)
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Looking at the explain verbose output, it looks like it may be doing a
> conversion to name because it looks like there isn't a text(varchar),
> but there's a text(name) and a name(varchar). My guess is there's no
> text(varchar) because they're considered binary compatible.
Since the truncation is to 31 characters, it seems clear that a
conversion to "name" happened.
I think the reason for this behavior is that the possibility of a
"freebie" binary-compatible conversion is not considered until all else
fails (see parse_func.c: it's only considered after func_get_detail
fails). Unfortunately func_get_detail is willing to consider all sorts
of implicit conversions, so these secondary possibilities end up being
the chosen alternative.
Perhaps it'd be a better idea for the option of a freebie conversion
to be checked earlier, say immediately after we discover there is no
exact match for the function name and input type. Thomas, what do you
think?
regards, tom lane
...
> Perhaps it'd be a better idea for the option of a freebie conversion
> to be checked earlier, say immediately after we discover there is no
> exact match for the function name and input type. Thomas, what do you
> think?
We *really* need that catalog lookup first. Otherwise, we will never be
able to override the hardcoded compatibility assumptions in that
matching routine. Once we push that routine into a system catalog, we'll
have more flexibility to tune things after the fact.
Without the explicit function call, things would work just fine for the
example at hand, right?
I could put in a dummy passthrough routine. But that seems a bit ugly.
- Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes:
>> Perhaps it'd be a better idea for the option of a freebie conversion
>> to be checked earlier, say immediately after we discover there is no
>> exact match for the function name and input type. Thomas, what do you
>> think?
> We *really* need that catalog lookup first. Otherwise, we will never be
> able to override the hardcoded compatibility assumptions in that
> matching routine.
Sure, I said *after* we fail to find an exact match. But the "freebie"
match is for a function name that matches a type name and is
binary-compatible with the source type. That's not a weak constraint.
ISTM that interpretation should take priority over interpretations that
involve more than one level of transformation.
regards, tom lane
...
> Sure, I said *after* we fail to find an exact match. But the "freebie"
> match is for a function name that matches a type name and is
> binary-compatible with the source type. That's not a weak constraint.
> ISTM that interpretation should take priority over interpretations that
> involve more than one level of transformation.
Ah, OK I think. If there is a counterexample, it is probably no less
obscure than this one.
- Thomas
> Thomas Lockhart <lockhart@fourpalms.org> writes: > >> Perhaps it'd be a better idea for the option of a freebie conversion > >> to be checked earlier, say immediately after we discover there is no > >> exact match for the function name and input type. Thomas, what do you > >> think? > > > We *really* need that catalog lookup first. Otherwise, we will never be > > able to override the hardcoded compatibility assumptions in that > > matching routine. > > Sure, I said *after* we fail to find an exact match. But the "freebie" > match is for a function name that matches a type name and is > binary-compatible with the source type. That's not a weak constraint. > ISTM that interpretation should take priority over interpretations that > involve more than one level of transformation. That sounds very reasonable to me. Andreas
Thomas Lockhart <lockhart@fourpalms.org> writes: >> Sure, I said *after* we fail to find an exact match. But the "freebie" >> match is for a function name that matches a type name and is >> binary-compatible with the source type. That's not a weak constraint. >> ISTM that interpretation should take priority over interpretations that >> involve more than one level of transformation. > Ah, OK I think. If there is a counterexample, it is probably no less > obscure than this one. Done. Essentially, this amounts to interchanging steps 2 and 3 of the function call resolution rules described at http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/typeconv-func.html regards, tom lane
I can confirm this is fixed in current sources. Thanks for the report.
---------------------------------------------------------------------------
> #create table t (v varchar);
> #insert into t values ('0123456789a0123456789b0123456789c0123456789d');
>
> #select v from t;
>
> v
> ----------------------------------------------
> 0123456789a0123456789b0123456789c0123456789d
> (1 row)
>
> So far, so good.
>
> #select text(v) from t;
>
> text
> ---------------------------------
> 0123456789a0123456789b012345678
> (1 row)
>
> Truncation occurs.
>
> Work around:
>
> # select v::text from t;
> ?column?
> ----------------------------------------------
> 0123456789a0123456789b0123456789c0123456789d
> (1 row)
>
> I couldnt figure out what happens during a text(varchar) call. I looked
> around in pg_proc, but couldnt find the function. There's probably an
> automagic type conversion going on or something.
>
> Could someone explain what all the internal varchar-like types are (ie.
> varchar,varchar(n),text,char,_char,bpchar) and when they're used? I
> find it all really confusing - I'm sure others do too.
>
> Is there anyway to determine what postgresql is doing in its automagic
> function calls? I guess I'm asking for an EXPLAIN that describes
> function calls. For example,
> EXPLAIN select text(v) from t;
>
> --> {Description of conversion from varchar to whatever the text()
> function actually works on}
>
>
> Thanks,
> dave
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill,
Pennsylvania19026