Toast,bytea, Text -blob all confusing

On Thu, Aug 23, 2001 at 01:09:14PM -0400, jason.ory@ndchealth.com wrote:

I'm trying my best to convert from MySQL to PgSQL but I cant get a good
answer about
certian questions. It was an easy task in mysql but all this talk about
, text , toast and bytea is just confusing me.
I cant get a clear picture of any of this,from the book from Bruce, the
e-mail archives. Ive looked all i can,
with every keyword i can think of from years past. Here is my situation.

WHAT I WAS DOING IN MYSQL
Via the web my clients are uploading basic text/data files, sometimes >
than 30MB. In the past ,via CGI I have been parsing the file
into one STL string, using mysql_escape_string to escape it and then using
an INSERT to place the
,\'+"stlstring+"\' , into a BLOB column.
I dont want to use a temp. file anywhere. The data will always be passed via
the database and buffers for certian reasons.

THIS IS WHAT I CANT SEEM TO FIGURE OUT IN POSTGRESQL
1. I cant get a clear answer on what kind of data type to use for my large
text string? TEXT, ???, ??? or something about TOAST
I have seen in the e-mail archive but cant find any documentaion?

TOAST is just a name for the mechanism/feature that is used in Postgres

= 7.1 to overcome the (32 KB if I recall correctly) limit on the row

size in previous versions. It's completely transparent to the
programmer, i.e. if you use TEXT, for instance, you can have a row up to
1 GB in size (which is probably not practical) theoretically. The
advantage over using BLOBs is that you can search this field (with 30 MB
fieldis, if you have a few of them, this is probably not practical
either so you'd probably want to consider some full text indexing
mechanism). I'd use TEXT for this reason.

2. I've written my own escape method ,("cant find one for Pgsql") , BUT i
don't know what
to escape and not to escape. So it keeps failing. I cand find any docs. on
what to escape either?

Hm. I don't understand why the database (using MySQL or Postgres) would
make any difference there.

Hope it helps,

Frank

On Thu, 23 Aug 2001 jason.ory@ndchealth.com wrote:

THIS IS WHAT I CANT SEEM TO FIGURE OUT IN POSTGRESQL
1. I cant get a clear answer on what kind of data type to use for my large
text string? TEXT, ???, ??? or something about TOAST
I have seen in the e-mail archive but cant find any documentaion?

I would suggest bytea or blob. Blobs are well-documented in normal
documentation and in documentation of your favorite interface, so I'll
just talk about bytea.

2. I've written my own escape method ,("cant find one for Pgsql") , BUT i
don't know what
to escape and not to escape. So it keeps failing. I cand find any docs. on
what to escape either?

For bytea, follow this rule: to escape a null character, use this:
'\\0'. To escape a backslash, use this: '\\\\'.

Same idea to unescape data.

At 03:05 PM 27-08-2001 -0400, Alex Pilosov wrote:

On Thu, 23 Aug 2001 jason.ory@ndchealth.com wrote:

THIS IS WHAT I CANT SEEM TO FIGURE OUT IN POSTGRESQL
1. I cant get a clear answer on what kind of data type to use for my large
text string? TEXT, ???, ??? or something about TOAST
I have seen in the e-mail archive but cant find any documentaion?

I would suggest bytea or blob. Blobs are well-documented in normal
documentation and in documentation of your favorite interface, so I'll
just talk about bytea.

2. I've written my own escape method ,("cant find one for Pgsql") , BUT i
don't know what
to escape and not to escape. So it keeps failing. I cand find any docs. on
what to escape either?

For bytea, follow this rule: to escape a null character, use this:
'\\0'. To escape a backslash, use this: '\\\\'.

Same idea to unescape data.

Are there other characters that need to be escaped? I suspect there are
more characters that need to be escaped - ctrl chars? single quotes?. Why
four backslashes for one? Is there a definitive documentation anywhere for
what bytea is _supposed_ (not what it might actually be) to be and how it
is to be handled?

Also why wouldn't escaping stuff like this work with TEXT then? If a null
is going to be backslash backslash zero, and come out the same way, it sure
looks like TEXT to me :). OK so there's this thing about storage. So maybe
I could save a byte by just converting nulls to backslash zero and real
backslashes to backslash backslash. Tada.

OK it's probably not the same, but having to put four backslashes when two
should be enough to quote one makes me rather puzzled and uneasy.

Cheerio,
Link.

On Tue, 28 Aug 2001, Lincoln Yeoh wrote:

For bytea, follow this rule: to escape a null character, use this:
'\\0'. To escape a backslash, use this: '\\\\'.

Same idea to unescape data.

Are there other characters that need to be escaped? I suspect there are
more characters that need to be escaped - ctrl chars? single quotes?. Why
four backslashes for one? Is there a definitive documentation anywhere for
what bytea is _supposed_ (not what it might actually be) to be and how it
is to be handled?

Yes, sorry for being unclear on this one. Here's a more detailed
explanation: Bytea is just a stream of data. On input, it must follow C
escaping conventions, on output, it will be escaped using C escaping
conventions.

However, there's a trap: before things get to bytea input handler, they
are first processed by postgresql general parser. Hence, the string \\0
given from psql will become \0 when it gets to bytea input handler. String
\\\\ will become \\. All non-printable characters must be escaped like
this: \\(octal of character), for ex, chr(255) must be presented as \\377.
(If you want, you can also use this as an alternative and more generic way
to escape a backslash, \\134). Single quote must be escaped either as \\47
or as \'. Note the single backslash. Why only one? Because bytea parser
doesn't care about single quotes and you only need to escape it for the
postgresql parser...

So, just keep in mind the double-parsing of input and you'll be safe.

Also why wouldn't escaping stuff like this work with TEXT then? If a null
is going to be backslash backslash zero, and come out the same way, it sure
looks like TEXT to me :). OK so there's this thing about storage. So maybe

Because text is null-terminated, can't have a null inside.

I could save a byte by just converting nulls to backslash zero and real
backslashes to backslash backslash. Tada.

If you do that, you'll break ordering/comparison. Bytea in memory is
stored EXACTLY the way input string was, without any escaping, hence, all
comparisons will be correct ( '\\0'::bytea is less than '\\1'::bytea).

With your representation, comparisons will fail, because in memory, data
is escaped using some escaping convention that you made up.

OK it's probably not the same, but having to put four backslashes when two
should be enough to quote one makes me rather puzzled and uneasy.

Double parsing, hence double escaping.

--
Alex Pilosov | http://www.acedsl.com/home.html
CTO - Acecape, Inc. | AceDSL:The best ADSL in the world
325 W 38 St. Suite 1005 | (Stealth Marketing Works! :)
New York, NY 10018 |

For bytea, follow this rule: to escape a null character, use this:
'\\0'. To escape a backslash, use this: '\\\\'.

Same idea to unescape data.

Are there other characters that need to be escaped? I suspect there are
more characters that need to be escaped - ctrl chars? single quotes?.

Why

four backslashes for one? Is there a definitive documentation anywhere

for

what bytea is _supposed_ (not what it might actually be) to be and how

it

is to be handled?

Yes, sorry for being unclear on this one. Here's a more detailed
explanation: Bytea is just a stream of data. On input, it must follow C
escaping conventions, on output, it will be escaped using C escaping
conventions.

However, there's a trap: before things get to bytea input handler, they
are first processed by postgresql general parser. Hence, the string \\0
given from psql will become \0 when it gets to bytea input handler. String
\\\\ will become \\. All non-printable characters must be escaped like
this: \\(octal of character), for ex, chr(255) must be presented as \\377.
(If you want, you can also use this as an alternative and more generic way
to escape a backslash, \\134). Single quote must be escaped either as \\47
or as \'. Note the single backslash. Why only one? Because bytea parser
doesn't care about single quotes and you only need to escape it for the
postgresql parser...

So, just keep in mind the double-parsing of input and you'll be safe.

Also why wouldn't escaping stuff like this work with TEXT then? If a

null

is going to be backslash backslash zero, and come out the same way, it

sure

looks like TEXT to me :). OK so there's this thing about storage. So

maybe

Because text is null-terminated, can't have a null inside.

I could save a byte by just converting nulls to backslash zero and real
backslashes to backslash backslash. Tada.

If you do that, you'll break ordering/comparison. Bytea in memory is
stored EXACTLY the way input string was, without any escaping, hence, all
comparisons will be correct ( '\\0'::bytea is less than '\\1'::bytea).

With your representation, comparisons will fail, because in memory, data
is escaped using some escaping convention that you made up.

OK it's probably not the same, but having to put four backslashes when

two

should be enough to quote one makes me rather puzzled and uneasy.

Double parsing, hence double escaping.

Great explanation Alex --thanks! I'll add a bit:

I've done about 400,000 inserts and subsequent queries to verify that, from
PHP at least, only four charaters need to be escaped. The inserts were 20
byte strings gotten by concatenating some known text with a counter in a
loop, and then producing a SHA-1 hash. This produces very uniformly
distributed binary data. Then I ran the same loop, except I queried for the
inserted strings. I'm quite confident from this experiment that binary can
reliably be inserted via standard SQL when these four characters are
escaped. Here's the run down:

\\000 First slash is consumed by the general parser, leaving \000 for the
byteain function. If you only use one '\', the general parser converts the
character into a true '\0' byte, and the byteain function sees this byte as
the string terminator and stops. This causes the input string to be
truncated (which seems to confuse many people).

\\012 In my early tests 0x0a (LF) was getting converted to 0x20 (space).
I think this was happening during PHP's parsing, but I'm still not sure.
I'll dig into this some more later.

\\047 As Alex mentioned, the byteain function doesn't treat this as a
special character, but of course the general parser does as this is a single
quote. It also works fine to escape it as \', I just prefer to use all
octals.

\\134 Both the general parser and the byteain function see this (a single
\) as the special escape character. Therefore the general parser turns \\\\
into \\, and the byteain function turns \\ into \ for actual storage. Again,
I prefer to use the octal representation instead.

I hope this helps reduce the concerns and confusion over bytea. If anyone
can help explain why my linefeeds were getting converted to spaces, all the
mysteries would be explained ;-)

-- Joe

For bytea, follow this rule: to escape a null character, use

this:

'\\0'. To escape a backslash, use this: '\\\\'.

Can anybody explain in technical terms why this is implemented
so inconveniently ?

Since bytea is probably not very common among users yet
we could imho still change it to not do double escapes.

Imho we need to decide where to do the escaping,
eighter in the parser or in the input functions.

I think actually the backend parser has no business changing
constants, he is imho only allowed to parse it, so he knows
where a constant begins, and where it ends.

Andreas

Zeugswetter Andreas SB SD wrote:

For bytea, follow this rule: to escape a null character, use

this:

'\\0'. To escape a backslash, use this: '\\\\'.

Can anybody explain in technical terms why this is implemented
so inconveniently ?

I think that this has to to with making textin and textout behave
symmetrically, and the requirement that textout must produce a
valid C-string for ASCII transfer format.

Since bytea is probably not very common among users yet
we could imho still change it to not do double escapes.

But how ?

Imho we need to decide where to do the escaping,
eighter in the parser or in the input functions.

It would be probably hard to make the parser to _not_ unescape some
types, as it does not yet know it

I think actually the backend parser has no business changing
constants, he is imho only allowed to parse it, so he knows
where a constant begins, and where it ends.

If it is any consolation then you have to write the inset of
a single \ from shell command so:

psql -c "insert into t values('\\\\\\\\')"

;)

------------------
Hannu

"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes:

I think actually the backend parser has no business changing
constants, he is imho only allowed to parse it, so he knows
where a constant begins, and where it ends.

How do you propose to handle embedded quote marks in literals,
if there is no parser-level escape convention?

Don't suggest a type-specific escape convention; at the time the
parser runs, it's impossible to know what type the literal will
turn out to be.

regards, tom lane

Thanks you your description, I have added a bytea section to the docs.

Patch attached.

For bytea, follow this rule: to escape a null character, use this:
'\\0'. To escape a backslash, use this: '\\\\'.

Same idea to unescape data.

Are there other characters that need to be escaped? I suspect there are
more characters that need to be escaped - ctrl chars? single quotes?.

Why

four backslashes for one? Is there a definitive documentation anywhere

for

what bytea is _supposed_ (not what it might actually be) to be and how

it

is to be handled?

Yes, sorry for being unclear on this one. Here's a more detailed
explanation: Bytea is just a stream of data. On input, it must follow C
escaping conventions, on output, it will be escaped using C escaping
conventions.

However, there's a trap: before things get to bytea input handler, they
are first processed by postgresql general parser. Hence, the string \\0
given from psql will become \0 when it gets to bytea input handler. String
\\\\ will become \\. All non-printable characters must be escaped like
this: \\(octal of character), for ex, chr(255) must be presented as \\377.
(If you want, you can also use this as an alternative and more generic way
to escape a backslash, \\134). Single quote must be escaped either as \\47
or as \'. Note the single backslash. Why only one? Because bytea parser
doesn't care about single quotes and you only need to escape it for the
postgresql parser...

So, just keep in mind the double-parsing of input and you'll be safe.

Also why wouldn't escaping stuff like this work with TEXT then? If a

null

is going to be backslash backslash zero, and come out the same way, it

sure

looks like TEXT to me :). OK so there's this thing about storage. So

maybe

Because text is null-terminated, can't have a null inside.

I could save a byte by just converting nulls to backslash zero and real
backslashes to backslash backslash. Tada.

If you do that, you'll break ordering/comparison. Bytea in memory is
stored EXACTLY the way input string was, without any escaping, hence, all
comparisons will be correct ( '\\0'::bytea is less than '\\1'::bytea).

With your representation, comparisons will fail, because in memory, data
is escaped using some escaping convention that you made up.

OK it's probably not the same, but having to put four backslashes when

two

should be enough to quote one makes me rather puzzled and uneasy.

Double parsing, hence double escaping.

Great explanation Alex --thanks! I'll add a bit:

I've done about 400,000 inserts and subsequent queries to verify that, from
PHP at least, only four charaters need to be escaped. The inserts were 20
byte strings gotten by concatenating some known text with a counter in a
loop, and then producing a SHA-1 hash. This produces very uniformly
distributed binary data. Then I ran the same loop, except I queried for the
inserted strings. I'm quite confident from this experiment that binary can
reliably be inserted via standard SQL when these four characters are
escaped. Here's the run down:

\\000 First slash is consumed by the general parser, leaving \000 for the
byteain function. If you only use one '\', the general parser converts the
character into a true '\0' byte, and the byteain function sees this byte as
the string terminator and stops. This causes the input string to be
truncated (which seems to confuse many people).

\\012 In my early tests 0x0a (LF) was getting converted to 0x20 (space).
I think this was happening during PHP's parsing, but I'm still not sure.
I'll dig into this some more later.

\\047 As Alex mentioned, the byteain function doesn't treat this as a
special character, but of course the general parser does as this is a single
quote. It also works fine to escape it as \', I just prefer to use all
octals.

\\134 Both the general parser and the byteain function see this (a single
\) as the special escape character. Therefore the general parser turns \\\\
into \\, and the byteain function turns \\ into \ for actual storage. Again,
I prefer to use the octal representation instead.

I hope this helps reduce the concerns and confusion over bytea. If anyone
can help explain why my linefeeds were getting converted to spaces, all the
mysteries would be explained ;-)

-- Joe

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

-- 
  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, Pennsylvania 19026

\\012 In my early tests 0x0a (LF) was getting converted to 0x20

(space).

I think this was happening during PHP's parsing, but I'm still not sure.
I'll dig into this some more later.

<redfaced>
The script I was using in PHP *explicitly* converted all linefeeds to
spaces. Mystery solved.
</redfaced>

I think Bruce's text still works though.

-- Joe

\\012 In my early tests 0x0a (LF) was getting converted to 0x20

(space).

I think this was happening during PHP's parsing, but I'm still not sure.
I'll dig into this some more later.

<redfaced>
The script I was using in PHP *explicitly* converted all linefeeds to
spaces. Mystery solved.
</redfaced>

I think Bruce's text still works though.

I can see other interfaces doing fancy things with newlines and carriage
returns so I added it to the docs.

-- 
  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, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

However, there's a trap: before things get to bytea input handler, they
are first processed by postgresql general parser.

This description fails to make clear that the two levels of parsing only
apply for datums that are written as string literals in SQL commands.
An example where this doesn't apply is COPY input data.

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

However, there's a trap: before things get to bytea input handler, they
are first processed by postgresql general parser.

This description fails to make clear that the two levels of parsing only
apply for datums that are written as string literals in SQL commands.
An example where this doesn't apply is COPY input data.

Are you talking about my SGML changes? I clearly mention quote-handling
and bytea handling, which pretty clearly not apply in COPY.

-- 
  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, Pennsylvania 19026