pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feed(libpq question) Holy cow, what's all this fluff?!
9+ messages / 6 participants
[nested] [flat]
* (libpq question) Holy cow, what's all this fluff?!
@ 1999-02-13 23:35 Matthew Hagerty <matthew@venux.net>
1999-02-13 23:44 ` Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?! David Ford <david@killerlabs.com>
1999-02-13 23:44 ` Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?! Taral <taral@cyberjunkie.com>
0 siblings, 2 replies; 9+ messages in thread
From: Matthew Hagerty @ 1999-02-13 23:35 UTC (permalink / raw)
To: pgsql-interfaces
Greetings,
When using the PQgetvalue() function of libpq, the results, at least char
fields, return the value in full, i.e. a char(20) field with the text
"Matthew" for example returns "Matthew " (all 20 characters,
padded with spaces).
I'm sure there is a good reason for this? But, none the less, I would like
to turn it off if I can, otherwise I'm off to writing a small function to
strip the spaces off. This is kind of a pain though because I don't need
to modify the values, just display them, or fill a browser's form fields
with values from the database (which is my biggest need to strip the
fluff). The docs say that I should not access the structure holding query
results, so I will have to malloc each value, copy it, strip the fluff,
send it out on its way, and release the memory. More housekeeping, yuck!
Not to mention more processing time. Please tell me there is a way to get
field values with libpq that are exact to the content length and not the
field length.
Thanks,
Matthew
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?!
1999-02-13 23:35 (libpq question) Holy cow, what's all this fluff?! Matthew Hagerty <matthew@venux.net>
@ 1999-02-13 23:44 ` David Ford <david@killerlabs.com>
1 sibling, 0 replies; 9+ messages in thread
From: David Ford @ 1999-02-13 23:44 UTC (permalink / raw)
To: Matthew Hagerty <matthew@venux.net>; +Cc: pgsql-interfaces
On Sat, 13 Feb 1999, Matthew Hagerty wrote:
> When using the PQgetvalue() function of libpq, the results, at least char
> fields, return the value in full, i.e. a char(20) field with the text
> "Matthew" for example returns "Matthew " (all 20 characters,
> padded with spaces).
varchar() perhaps?
-d
--
This is Linux Country. On a quiet night, you can hear Windows NT reboot!
Do you remember how to -think- ? Do you remember how to experiment? Linux
__ is an operating system that brings back the fun and adventure in computing.
\/ for linux-kernel: please read linux/Documentation/* before posting problems
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?!
1999-02-13 23:35 (libpq question) Holy cow, what's all this fluff?! Matthew Hagerty <matthew@venux.net>
@ 1999-02-13 23:44 ` Taral <taral@cyberjunkie.com>
1999-02-14 04:30 ` Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?! Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1999-02-14 05:17 ` Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?! Matthew Hagerty <matthew@venux.net>
1 sibling, 2 replies; 9+ messages in thread
From: Taral @ 1999-02-13 23:44 UTC (permalink / raw)
To: Matthew Hagerty <matthew@venux.net>; pgsql-interfaces
On Sat, 13 Feb 1999, Matthew Hagerty wrote:
>Greetings,
>
>When using the PQgetvalue() function of libpq, the results, at least char
>fields, return the value in full, i.e. a char(20) field with the text
>"Matthew" for example returns "Matthew " (all 20 characters,
>padded with spaces).
You want a varchar (or is it bpchar?), not a char. char(x) will always return x
characters, with space padding if necessary.
Taral
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?!
1999-02-13 23:35 (libpq question) Holy cow, what's all this fluff?! Matthew Hagerty <matthew@venux.net>
1999-02-13 23:44 ` Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?! Taral <taral@cyberjunkie.com>
@ 1999-02-14 04:30 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1 sibling, 0 replies; 9+ messages in thread
From: Thomas G. Lockhart @ 1999-02-14 04:30 UTC (permalink / raw)
To: Taral <taral@cyberjunkie.com>; +Cc: Matthew Hagerty <matthew@venux.net>; pgsql-interfaces
> You want a varchar (or is it bpchar?), not a char. char(x) will always
> return x characters, with space padding if necessary.
"bpchar" == "blank padded char", which is what char() is mapped to
internally. As Matthew has already learned, he probably really wants to
be using varchar(), or fix it up with trim().
- Tom
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?!
1999-02-13 23:35 (libpq question) Holy cow, what's all this fluff?! Matthew Hagerty <matthew@venux.net>
1999-02-13 23:44 ` Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?! Taral <taral@cyberjunkie.com>
@ 1999-02-14 05:17 ` Matthew Hagerty <matthew@venux.net>
1999-02-14 18:45 ` Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?! Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 9+ messages in thread
From: Matthew Hagerty @ 1999-02-14 05:17 UTC (permalink / raw)
To: pgsql-interfaces
At 04:30 AM 2/14/99 +0000, Thomas G. Lockhart wrote:
>> You want a varchar (or is it bpchar?), not a char. char(x) will always
>> return x characters, with space padding if necessary.
>
>"bpchar" == "blank padded char", which is what char() is mapped to
>internally. As Matthew has already learned, he probably really wants to
>be using varchar(), or fix it up with trim().
>
> - Tom
Yeah, but the docs say you get a performance hit for using varchar, text,
and the like. Which is worse, the database performance hit or the extra
call to trim for every char field?
Matthew
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?!
1999-02-13 23:35 (libpq question) Holy cow, what's all this fluff?! Matthew Hagerty <matthew@venux.net>
1999-02-13 23:44 ` Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?! Taral <taral@cyberjunkie.com>
1999-02-14 05:17 ` Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?! Matthew Hagerty <matthew@venux.net>
@ 1999-02-14 18:45 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 1999-02-14 18:45 UTC (permalink / raw)
To: Matthew Hagerty <matthew@venux.net>; +Cc: pgsql-interfaces
Matthew Hagerty <matthew@venux.net> writes:
> At 04:30 AM 2/14/99 +0000, Thomas G. Lockhart wrote:
>>> You want a varchar (or is it bpchar?), not a char. char(x) will always
>>> return x characters, with space padding if necessary.
> Yeah, but the docs say you get a performance hit for using varchar, text,
> and the like. Which is worse, the database performance hit or the extra
> call to trim for every char field?
Whatever docs you are looking at are obsolete. char(n), varchar(n), and
text have essentially interchangeable performance and representation.
They all have a length word and some characters.
(I've griped about that myself --- particularly that plain "char" is
effectively an 8-byte field, not a 1-byte field like you'd expect.
Nobody else seems worried about it though.)
I'd recommend using "text", personally, unless you have a specific
reason for imposing an upper length limit on the string.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: [INTERFACES] (libpq question) Holy cow, what's all this fluff ?!
@ 1999-02-14 00:09 Michael Davis <michael.davis@prevuenet.com>
0 siblings, 0 replies; 9+ messages in thread
From: Michael Davis @ 1999-02-14 00:09 UTC (permalink / raw)
To: pgsql-interfaces
You could trim(field).
-----Original Message-----
From: Matthew Hagerty [SMTP:matthew@venux.net]
Sent: Saturday, February 13, 1999 4:35 PM
To: pgsql-interfaces@postgreSQL.org
Subject: [INTERFACES] (libpq question) Holy cow, what's all
this fluff?!
Greetings,
When using the PQgetvalue() function of libpq, the results, at least
char
fields, return the value in full, i.e. a char(20) field with the
text
"Matthew" for example returns "Matthew " (all 20
characters,
padded with spaces).
I'm sure there is a good reason for this? But, none the less, I
would like
to turn it off if I can, otherwise I'm off to writing a small
function to
strip the spaces off. This is kind of a pain though because I don't
need
to modify the values, just display them, or fill a browser's form
fields
with values from the database (which is my biggest need to strip the
fluff). The docs say that I should not access the structure holding
query
results, so I will have to malloc each value, copy it, strip the
fluff,
send it out on its way, and release the memory. More housekeeping,
yuck!
Not to mention more processing time. Please tell me there is a way
to get
field values with libpq that are exact to the content length and not
the
field length.
Thanks,
Matthew
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?!
@ 1999-02-14 19:09 Matthew Hagerty <matthew@venux.net>
1999-02-14 20:38 ` Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?! Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Matthew Hagerty @ 1999-02-14 19:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
At 01:45 PM 2/14/99 -0500, Tom Lane wrote:
>Matthew Hagerty <matthew@venux.net> writes:
>> At 04:30 AM 2/14/99 +0000, Thomas G. Lockhart wrote:
>>>> You want a varchar (or is it bpchar?), not a char. char(x) will always
>>>> return x characters, with space padding if necessary.
>
>> Yeah, but the docs say you get a performance hit for using varchar, text,
>> and the like. Which is worse, the database performance hit or the extra
>> call to trim for every char field?
>
>Whatever docs you are looking at are obsolete. char(n), varchar(n), and
>text have essentially interchangeable performance and representation.
>They all have a length word and some characters.
>
>(I've griped about that myself --- particularly that plain "char" is
>effectively an 8-byte field, not a 1-byte field like you'd expect.
>Nobody else seems worried about it though.)
>
>I'd recommend using "text", personally, unless you have a specific
>reason for imposing an upper length limit on the string.
>
> regards, tom lane
The docs that mention the performance hit were really the FAQ, item 3.10.
Check it out. Also, item 3.8 mentions psql's \do command, however when I
type \do nothing happens and I have to ctrl-c to get the prompt back. Any
suggestions? All I need to do is a case-insensitive query.
Thanks,
Matthew
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?!
1999-02-14 19:09 Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?! Matthew Hagerty <matthew@venux.net>
@ 1999-02-14 20:38 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 9+ messages in thread
From: Tom Lane @ 1999-02-14 20:38 UTC (permalink / raw)
To: Matthew Hagerty <matthew@venux.net>; +Cc: pgsql-interfaces
Matthew Hagerty <matthew@venux.net> writes:
> At 01:45 PM 2/14/99 -0500, Tom Lane wrote:
>> Whatever docs you are looking at are obsolete. char(n), varchar(n), and
>> text have essentially interchangeable performance and representation.
> The docs that mention the performance hit were really the FAQ, item 3.10.
> Check it out.
The FAQ is mostly right if you read it very carefully, but I think it's
misleading.
: CHAR char 1 character
: CHAR(#) bpchar blank padded to the specified fixed length
: VARCHAR(#) varchar size specifies maximum length, no padding
: TEXT text length limited only by maximum tuple length
: BYTEA bytea variable-length array of bytes
:
: The last four types above are "varlena" types (i.e. the first four
: bytes are the length, followed by the data). char(#) allocates the
: maximum number of bytes no matter how much data is stored in the
: field. text, varchar(#), and bytea all have variable length on the
: disk, and because of this, there is a small performance penalty for
: using them. Specifically, the penalty is for access to all columns
: after the first column of this type.
The FAQ is wrong to claim that char is not a varlena type --- it is in
fact the same as char(1). (There is a true single-byte type named char
defined internally, but an appearance of "char" in a CREATE TABLE query
gets translated to char(1) instead. The inconsistency of internal and
external type names has doubtless contributed to confusion in this area.)
As the FAQ correctly states, char(n) is a varlena type; that means that
you pay a four-byte overhead for the length word, plus an alignment
penalty of zero to three bytes to ensure the length word is
long-aligned. (This is why I remarked that char(1) is effectively an
8-byte type: if you put several of them in a row, they take up 8 bytes
apiece.) This space overhead, plus the time overhead from dealing with
non-fixed-length columns, is the performance penalty paid for any
varlena type.
One particular component of the time cost for varlena types is that
the within-tuple offset of later columns is not fixed, meaning that
fastgetattr() can't cache their offsets, but has to scan the tuple data
to find them. Although char(n) is represented as a varlena type, there
is a special hack in fastgetattr() to make it treat char(n) as
fixed-size for the specific purpose of deciding whether later columns'
offsets are cacheable.
The FAQ is correct to describe this effect as "small", but perhaps it
should say "very small" --- for one thing the offset cache is irrelevant
if you have any NULL fields in the current tuple. I wouldn't worry
about it myself, and I certainly wouldn't choose an inappropriate data
type for my application in order to avoid it. The FAQ is not doing you
any service to suggest that you should prefer char(n) when you don't
really want a fixed-length string. The space penalty of the unwanted
pad data is likely to cost you far more than you can hope to save by
making fastgetattr() a little quicker.
> Also, item 3.8 mentions psql's \do command, however when I
> type \do nothing happens and I have to ctrl-c to get the prompt back.
\do works for me, but it's pretty slow (about 20 seconds on my hardware)
... how long did you give it?
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~1999-02-14 20:38 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-02-13 23:35 (libpq question) Holy cow, what's all this fluff?! Matthew Hagerty <matthew@venux.net>
1999-02-13 23:44 ` David Ford <david@killerlabs.com>
1999-02-13 23:44 ` Taral <taral@cyberjunkie.com>
1999-02-14 04:30 ` Thomas G. Lockhart <lockhart@alumni.caltech.edu>
1999-02-14 05:17 ` Matthew Hagerty <matthew@venux.net>
1999-02-14 18:45 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-02-14 00:09 RE: [INTERFACES] (libpq question) Holy cow, what's all this fluff ?! Michael Davis <michael.davis@prevuenet.com>
1999-02-14 19:09 Re: [INTERFACES] (libpq question) Holy cow, what's all this fluff?! Matthew Hagerty <matthew@venux.net>
1999-02-14 20:38 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox