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

As others mentioned, never concatenate a string to be sent, you are WIDE-OPEN to SQL-Injection. Best parameterize the query. The following is a SAMPLE of what you would do.

var pgCmd = new NpgsqlCommand("");  // not including actual command here.
pgCmd.CommandText = 
@"insert into someTable
( col1,
  col2,
  col3,...
)
values
( :pCol1,
  :pCol2,
  :pCol3,...
)

Postgres uses a ":" colon as a parameter indicator. I also prefix the actual parameter with "p" just to indicate a parameter value, not just inserting a column name. Helps to prevent ambiguity to me when reading.

Now that you have the command, add the parameters...

pgCmd.Parameters.Add( new NpgsqlParameter( "pCol1", someIntegerValue ));
pgCmd.Parameters.Add( new NpgsqlParameter( "pCol2", someStringValue ));
pgCmd.Parameters.Add( new NpgsqlParameter( "pCol3", someDateValue ));

Then you can execute the command against whatever connection you have.

Now, it is actually better to explicitly type-case identify the parameters, but look at the overload function instances to help with that. I see 10 overloads base on what you want to provide. I actually wrapped a function to add string parameters, date, integer, etc so that would create the parameter, proper type, parameter name and appropriate value.

HTH

Answer 2

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?