Handling of errors in reading and reading files using the bytea field of PostgreSQL

The bytea field type in PostgreSQL can store data in binary format. The advantage of this is that the files originally stored in the website directory can be stored in the database, the disadvantage is that if there are too many files or too many files, the data volume in the database will be greatly increased. backup and recovery will waste a lot of time, and data may also fail. I personally think that this storage method is very convenient when the file volume is small.

To put it bluntly, the following describes how to use the bytea field to store and read files. First, the file is stored in the bytea field, and the pg_escape_bytea method in PHP is mainly used. The Code is as follows:

<? = pg_connect("host='localhost' dbname='dbname' user='user' password='password' port='port'"('Could not connect:' . = ("utf-8", "gbk", ); = ( = pg_escape_bytea(); = "}')", ?>

Restoring a file from the bytea field uses the pg_unescape_bytea method in PHP. The implementation code is as follows:

<? = pg_connect("host='localhost' dbname='dbname' user='user' password='password' port='port'"('Could not connect: ' . = "" . ;     = pg_query(, ( = pg_fetch_array(, , = ['contents' = pg_unescape_bytea();         (, ); ?>

After the file is exported, if the binary data is incorrectly transcoded, the file cannot be opened. For example, an error occurs when the wrong PDF file is opened, for example:

This kind of error is especially prone to occur during database migration (I migrated from PostgreSQL 8.4 to 9.1). The solution is to modify the configuration file of PostgreSQL.
Postgresql. conf, set the output type of bytea_output to the escape type (escape) output, that is, bytea_output = 'escape '(if there is # above, delete and enable the configuration ), then reload the PostgreSQL configuration to make the modification take effect, so that the binary data can be decoded and output to the file.

 

Blog statement:

All the articles in this blog, except the words "reprinted" in the title, are original articles or summarized after reading the materials. Please note this statement when referencing non-reprinted articles. -- Blog garden-pallee