Re: Memory efficient insertion/retrieval of bytea

[Tomasz Ostrowski <tometzky@xxxxxxxxxxxxx>]

On Wed, 20 Sep 2006, Tomasz Ostrowski wrote:

> I've made some experiments with attached program and
> came up that for inserting 1kB of data this program needs about
> 5120kB of virtual memory, 1MB - 10MB, 10MB - 55MB.

Forgot to attach it.

Pozdrawiam
Tometzky
-- 
Best of prhn - najzabawniejsze teksty polskiego UseNet-u
http://prhn.dnsalias.org/
  Chaos zawsze pokonuje porządek, gdyż jest lepiej zorganizowany.
                                              [ Terry Pratchett ]

#include <libpq-fe.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <unistd.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <assert.h>

int main(int argc, char* argv[]) {
	int data_fd;
	char* query_buf;
	unsigned char* data_cache;
	size_t query_buf_len, query_buf_off;
	ssize_t readbytes;

	assert(argc == 2);

	PGconn* pgconn = PQconnectdb("");
	if ( PQstatus(pgconn) != CONNECTION_OK ) { fprintf(stderr, "Cannot connect: %s\n", PQerrorMessage(pgconn)); exit(-1); };
	
	if ( ( data_fd = open(argv[1],O_RDONLY) ) == -1 ) { perror("Cannot open data file"); exit(-1); };

	/* allocate query buffer */
	#define	QUERYSTART "create temporary table test (data bytea); insert into test values ('"
	#define QUERYEND "')"
	{
		struct stat statbuf;
		if ( fstat(data_fd, &statbuf) == -1 ) { perror("Cannot stat data file"); exit(-1); };
		/* query_buf_len = maximum needed size of query buffer */
		query_buf_len = statbuf.st_size*5+sizeof(QUERYSTART)+sizeof(QUERYEND);
	}
	if ( ! (query_buf = malloc(query_buf_len)) ) { perror(NULL); exit(-1); };

	/* fill query buffer with QUERYSTART without null termination */
	memcpy(query_buf, QUERYSTART, sizeof(QUERYSTART)-1);
	query_buf_off = sizeof(QUERYSTART)-1; /* Offset in the query buffer */

	/* allocate cache for file data */
	#define CACHESIZE (0x7fff) /* minimum POSIX limit for read cache size */
	if ( ! ( data_cache = malloc(CACHESIZE) ) ) { perror(NULL); exit(-1); };

	/* read file */
	while ( ( readbytes = read(data_fd, data_cache, CACHESIZE) ) > 0 ) {
		size_t esc_data_cache_len;
		unsigned char* esc_data_cache;

		/* escape data */
		if ( ! ( esc_data_cache = PQescapeByteaConn(pgconn, data_cache, readbytes, &esc_data_cache_len) ) ) { fprintf(stderr, "Cannot escape data: %s\n", PQerrorMessage(pgconn)); exit(-1); };

		/* copy escaped data without null termination to query buffer */
		assert( query_buf_off + esc_data_cache_len < query_buf_len );
		memcpy(query_buf+query_buf_off, esc_data_cache, esc_data_cache_len-1);
		query_buf_off += esc_data_cache_len-1;

		/* free escaped data */
		PQfreemem(esc_data_cache);
	}
	if ( readbytes == -1 ) { perror("Cannot read from data file"); exit(-1); }
	
	/* deallocate cache for file data */
	free(data_cache);

	/* fill query buffer with QUERYEND with null termination */
	assert(query_buf_off+sizeof(QUERYEND) <= query_buf_len);
	memcpy(query_buf+query_buf_off, QUERYEND, sizeof(QUERYEND));
	
	/* fprintf(stderr, "%s\n", query_buf); */
	
	PQexec(pgconn, query_buf);
	
	/* deallocate query buffer */
	free(query_buf);

	PQfinish(pgconn);
	
	return 0;
}