ARTICLE AD BOX
I'm working on a database to store information from .warc files, which are being parsed by a program I wrote in BunJS. The problem is that inserting data into the database takes a long time to insert per item on 1tb+ .warc batches, so I wrote a function to batch upsert multiple responses and its information into the appropriate tables (create a new entry, uri->uris, payload->payload).
Here is the relevant SQL:
CREATE TYPE response_input AS ( file_id BIGINT, warc_id TEXT, custom_id TEXT, uri TEXT, status INT, headers JSONB, payload_offset BIGINT, -- nullable payload_size BIGINT, -- nullable payload_content_type TEXT -- nullable ); -- Bulk upsert function for responses (optimized/cleaned) CREATE OR REPLACE FUNCTION upsert_responses_bulk(rows response_input[]) RETURNS TABLE(response_id BIGINT) AS $$ BEGIN ...Following that, here is the typescript I have to hold the infomation, then parse (too long cropped out), then insert
const responses:{ file_id: number, warc_id: string, custom_id: string, uri: string, status: number, headers: Record<string, any>, payload_offset: number, payload_size: number, payload_content_type: string, }[] = []; //Parsing done here, cut out brevity //... const tuples = responses.map(r => [ r.file_id, r.warc_id, r.custom_id, r.uri, r.status, JSON.stringify(r.headers), r.payload_offset, r.payload_size, r.payload_content_type, ]); const result = await sql` SELECT * FROM upsert_responses_bulk( ${sql.array(tuples)}::response_input[] )`Unfortunately, that didn't work and I got this error message
bunjs-1 | PostgresError: cannot cast type json[] to response_input[] bunjs-1 | errno: "42846", bunjs-1 | severity: "ERROR", bunjs-1 | position: "57", bunjs-1 | file: "parse_expr.c", bunjs-1 | routine: "transformTypeCast", bunjs-1 | code: "ERR_POSTGRES_SERVER_ERROR"The BunJS SQL documentation doesn't really go into depths about types and how to properly use custom SQL types with JSON inside of them, so I'm at a bit of a loss here. Can anyone help, thanks in advance
