Send a very long query through npgsql.createcommand

Question

I’m trying to send a (very) long query to a PostGreSQL V15 Database using NPGSQL with a code that look like that :

string Connection_string = "Host=--:5432;Username=postgres;Password=--;Database=-_-;";

DataSource = NpgsqlDataSource.Create(Connection_string);

string query = "INSERT INTO \"dsf_thdy_process\" values ('Commentaire','long_text',' long_text ',' long_text ',[…],' long_text ')"; // very long query with lot of texts to insert in lot of columns

using (var connection = DataSource.OpenConnection())
{
using var command =DataSource.CreateCommand(query);
command.ExecuteNonQuery();
}`

I get an exception » Npgsql.PostgresException: '54000: the row is to long : length 11392, maximal length 8160' «

If I reduce the length of my query, it’s work.

I tried to add ‘Write Buffer Size =16000' in the connection string, as explained in https://www.npgsql.org/doc/performance.html but no effect.

In lost cause, I also tried to add some line return in the query, but still no effect.

Is there a way to increase the length accepted by CreateCommand ? (or another way to send a long query through npgsql)

thanks

Answer 1

After some other research and testing I found the explanation : the max size of a row in PostGreSQL is 8160 (block_size).

If you got bigger values, PostGreSQL will use the TOAST mechanism to cope it, but it’s only work for a limited amount of columns (18 bytes per pointer 8160 bytes max -> 453 columns) and in my case I have 750 columns, therefor the error.

The block_size value can be changed, but it’s not recommanded.

Source : https://wiki.postgresql.org/wiki/TOAST and How to change/set the block size in Postgres? Is there any file to make the configuration?