PostgreSQL – How to Store HTTP Response

datatypespostgresql

I want to store a whole HTTP response in PostgreSQL database row.

I need the HTTP status, the headers and the body.

AFAIK there is no native data type for this.

How could I store a HTTP response?

Update

I have been thinking about this again. I guess it is best if I follow this pattern: I won't search anything inside the http response. It is like a blob. Everything I want to search in it while be extracted before and put into a different column. Up to now only the http status code will get used and it will get an own column.

Best Answer

The HTTP/1.1 spec says, through RFC-7230, that:

the status code is a 3-digit number, so an int4 or int2 would do.
for the entire header, see 3.2.4, "Field parsing":

Historically, HTTP has allowed field content with text in the ISO-8859-1 charset [ISO-8859-1], supporting other charsets only through use of [RFC2047] encoding. In practice, most HTTP header field values use only a subset of the US-ASCII charset [USASCII]. Newly defined header fields SHOULD limit their field values to US-ASCII octets. A recipient SHOULD treat other octets in field content (obs-text) as opaque data.

"opaque" pretty much implies that bytea is the only safe choice, if you want to handle the responses of any HTTP server out there.
The message body is defined as message-body = *OCTET, so bytea is also pretty much the only type that fits, unless you prefer the large objects storage and API. bytea is limited to 1Gb so you may want to chunk the value across several smaller rows if you target any size. In practice, very large bytea values tend to be unworkable, personally I wouldn't go over 128Mb per row.

Related Solutions

Sql-server – an appropriate datatype for an RFID, and is there a standard definition for the format

RFID today is made basically of two standards, being HF (13.56MHz) and UHF (800-900MHz depending if Europe/US)

HF tags usually have one Unique Identifier, called UID, that is 64 bits in size. The UID is usually guaranteed to be unique by the producer of the IC and is read only.

HF tags are usually used for ticketing, anti counterfeiting, brand protection applications. HF tags usually have some user memory too, that can vary from a few bits to usually a maximum of 4Kbits, but special application ICs exist with memory up to 80-90Kbit (i.e. RFID passport chips). It is not common to have to store the contents of this data in an external database, but if you do, you need to know the model of the IC.

UHF tags, on the other hand, usually have a 96 or 128 bit EPC (256 bit EPC models are emerging), that is not generally unique and it is reprogrammable. Each application / system integrator usually programs the EPC according to the needs of the application

UHF tags are mostly used for logistics and tracking and many tags do not have user memory. Some models do have it, but it is usually very limited (128 or 256 bits). Of course there are exceptions.

In most applications you will just receive the UID or EPC of the tag, and you will need to store only that. This usually means that a 64 bit number for UID or a uuid field for EPC should be enough.

Postgresql – Group By primary key or DISTINCT increase query time over 1000x with limit

First off, LIMIT / OFFSET without ORDER BY are of limited usefulness, since the order is arbitrary and can change any time (when VACUUM runs or when the table is manipulated in at any way). It is only somewhat reliable with read-only tables. That's fine if you don't care which rows you get back, but it may break paging.

You may be able to solve your conundrum with the good old EXISTS. PostgreSQL can stop searching for more hits as soon as the first is found - as opposed to your query with DISTINCT, where it tries to collect all matches.

It's hard to be more specific without knowing the table structure, cardinalities, index definitions and what's behind your peculiar WHERE expression. But this might just do it:

SELECT s.chem_compound_id AS chem1_0_ 
       -- 10 more columns all short varchar or date fields
FROM   simple_compound s
WHERE  EXISTS (
   SELECT 1
   FROM   compound_composition sc
   JOIN   chemical_structure   c  ON sc.chemical_structure_id = c.structure_id 
   WHERE  c.structure_id  @ ('CCNc1ccccc1', '')::bingo.sub
   AND    sc.chem_compound_id = s.chem_compound_id
   )
LIMIT  5 
OFFSET 5

This also assumes you are only interested in columns from simple_compound in the output.

Best Answer

Related Solutions

Sql-server – an appropriate datatype for an RFID, and is there a standard definition for the format

Postgresql – Group By primary key or DISTINCT increase query time over 1000x with limit

Related Question