We currently have two doc repositories:

• npgsql/doc which is where the conceptual doc markdown files live, and all the configuration for building the docs site
• npgsql/livedocs which is where generated HTML/CSS/JS gets pushed from npgsql/doc

The latter could simply be a branch within the former, simplifying the whole setup.

Follow-up from npgsql/npgsql#2939 because I already forgot about it.

See npgsql/efcore.pg#2161

The issue

I'm running into this issue and have been unable to track down why it happens. The "Max Pool Size" connection string keyword should be supported but somehow isn't.

Exception has occurred: CLR/System.ArgumentException
Exception thrown: 'System.ArgumentException' in System.Data.Common.dll: 'Keyword not supported: max pool size'
   at Npgsql.NpgsqlConnectionStringBuilder.GetProperty(String keyword)
   at Npgsql.NpgsqlConnectionStringBuilder.set_Item(String keyword, Object value)
   at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
   at Npgsql.NpgsqlConnection.GetPoolAndSettings()

Further technical details

Npgsql version: 4.1.1
PostgreSQL version: 11
Operating system: Linux

As per npgsql/npgsql#3597, once 6.0 is released.

Checking out the docs on spatial data for npgsql only a sample of the operations supported by this library are written - far more are supported by this library

I think the docs should be updated to cover these operations, or the docs should point to the MS docs (as the top of the page suggests).

At URL https://www.npgsql.org/efcore/mapping/full-text-search.html?tabs=pg12%2Cv5

e.g. npgsql/npgsql#4001

Hello guys!

How to I can get access to information about updated/deleted/inserted/default inserts/no action rows for MERGE operation using npgsql?

Quoting from the official documentation:

The number of rows updated, inserted or deleted is not available as part of the command tag. An optional NOTIFY message can be generated to present this information, if desired.

NOTIFY: 34 rows processed: 11 updated, 5 deleted, 15 inserted, 3 default inserts, 0 no action

As far as I understand, I can force pgsql to display this information, including in JSON form, and then deserialize it.
How to do it?

Hi,

We have been using the logical replication feature with Npgsql 5.x quite successfully. The release notes for Npgsql 6.x say:

The logical replication API has been redone

As part of the redesign around logical replication support, some major changes to the API had to be done. Applications will have to be changed to use the new API.

It would be helpful if there was some documentation for this API, even a migration guide. I searched the manual and the best I could find is here which is for version 5 and doesn't compile with Npgsql 6.x.

A migration guide would be helpful since PgOutputReplicationOptions now expects a protocol version where previously none was required. I will read the Postgres manual to find out which version of replication is supported by my v13.6 instance but it would be nice if this change (and others) was covered.

I hope to use the logical replication API in Npgsql v6 as successfully as v5 ... I just need a little help.

Sorry if this information exists elsewhere and I have not found it.

Thank you for Npgsql.

As in dotnet/EntityFramework.Docs#2744

So, example:

            var cmd = new NpgsqlCommand($"SELECT * FROM \"{SchemaName}\".\"{TargetTableName}\" LIMIT 0", pgsql);
            var reader = cmd.ExecuteReader();
            var columnSchema = reader.GetColumnSchema();
            
            for (int i = 0; i < columnSchema.Count; i++)
            {
                Console.WriteLine(columnSchema[i].IsKey); // ALWAYS IS NULL
            }
            Console.WriteLine("++++++++++++++++++++");

No info:

            var columnSchema2 = pgsql.GetSchema("Columns", new string[] { null, null, TargetTableName });
            for (int i = 0; i < columnSchema2.Rows.Count; i++)
            {
                for (int j = 0; j < columnSchema2.Columns.Count; j++)
                {
                    Console.WriteLine("{0}: {1}", columnSchema2.Columns[j], columnSchema2.Rows[i][j]);
                }
                Console.WriteLine("********************");
            }
            Console.WriteLine("++++++++++++++++++++");

table_catalog: TestCatalog
table_schema: public
table_name: TestTable
column_name: Id
ordinal_position: 1
column_default:
is_nullable: NO
data_type: integer
character_maximum_length:
character_octet_length:
numeric_precision: 32
numeric_precision_radix: 2
numeric_scale: 0
datetime_precision:
character_set_catalog:
character_set_schema:
character_set_name:
collation_catalog:

Also no info:

            for (int i = 0; i < columnSchema2.PrimaryKey.Length; i++)
            {
                Console.WriteLine(columnSchema2.PrimaryKey[i].ColumnName);
                Console.WriteLine("********************");
            }
            Console.WriteLine("++++++++++++++++++++");

Is there a workaround?

Because NpgsqlEventId was introduced to Npgsql ADO.

At the moment our conceptual (non-API) docs lives in each specific project's repo, under a doc directory. The doc repo (this repo) uses git submodules to include the three projects and access those directories. The logic here was that documentation changes should be part of the same commit which implements the feature.

In practice, this doesn't seem to have worked well:

• Documentation is frequently written later and in a separate commit: devs don't necessarily have time when developing the feature to also write full docs.
• Doc commits are done in the work branch (dev), and then have to be cherry-picked to master, pushed to github, and only then can the git submodule be updated in the doc repo. This is a very heavy and cumbersome flow.
• Our project's git history becomes cluttered with doc commits.

Instead, I propose we just move all conceptual docs to this repo and be done with it. Updates can be done directly in the repo, without touching the project repos, and the flow will be much lighter.

Note that we'll still use git submodules to access the code in the main repos, in order to generate API docs.

Any objections?

/cc @YohDeadfall @austindrenski

Especially since the big improvements which came in with npgsql/efcore.pg#1992

• Soft (server-side) vs. hard (client-side) cancellation
• Cancellation timeout (in the connection string params page as well)
• Timeouts are per-operation, the cancellation token isn't stored internally.
  • This means you must call Read/NextResult with the token to actually trigger the cancellation (see npgsql/npgsql#4542)
  • But just one Read/NextResult might not work, since if it has the data buffered, it ignores the token entirely.
  • So the guidance should always be to consume the reader until the end.
• And are not aggregated (npgsql/npgsql#1041)
• Nothing (yet) supported in multiplexing
• Describe statement_timeout and where it's problematic, and also how to configure it on the connection string (npgsql/efcore.pg#2486 (comment))

Steps to reproduce

• Checkout https://github.com/SeppPenner/SerilogSinkForPostgreSQL.

• Setup a local PostgreSQL database.

• Check the SerilogSinksPostgreSQL.IntegrationTests project

• Check the class DbWriteTests with the method AutoCreateTableIsTrueShouldCreateTable()

• Adjust the private const string ConnectionString = "User ID=postgres;Password=postgres;Host=localhost;Port=5432;Database=Serilog"; property and run the method AutoCreateTableIsTrueShouldCreateTable()

The issue

DateTimeOffset cannot be mapped to Timestamp (NpgsqlType). I do not know if this is a bug because https://www.npgsql.org/doc/types/basic.html shows:

for the write mapping.

Exception:

{System.InvalidCastException: Can't write CLR type System.DateTimeOffset with handler type TimestampHandler
   at Npgsql.TypeHandling.NpgsqlSimpleTypeHandler`1.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter) in C:\projects\npgsql\src\Npgsql\TypeHandling\NpgsqlSimpleTypeHandler.cs:line 236
   at Npgsql.NpgsqlParameter.ValidateAndGetLength() in C:\projects\npgsql\src\Npgsql\NpgsqlParameter.cs:line 553
   at Npgsql.NpgsqlCommand.ValidateParameters() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 793
   at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__100.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1140}

My query:

INSERT INTO "Logs" ("Message", "MessageTemplate", "Level", "RaiseDate", "Exception", "Properties", "PropertyTest", "IntPropertyTest", "MachineName") VALUES (@Message, @MessageTemplate, @Level, @RaiseDate, @Exception, @Properties, @PropertyTest, @IntPropertyTest, @MachineName)

The parameter mapping:

Parameters:

DbType, Value, .Net type, NpgsqlType

String, "Test0: TestObjectType1 { IntProp: 42, StringProp: "Test" } test2: TestObjectType2 { DateProp: 05/12/2019 18:21:41, NestedProp: TestObjectType1 { IntProp: 42, StringProp: "Test" } } testStr: stringValue", string, Text
String, "Test{testNo}: {@testObject} test2: {@testObj2} testStr: {@testStr:l}", string, Text
String, "Information", Enum, Varchar
DateTime, 12.05.2019 18:21:41 +02:00, DateTimeOffset, Timestamp
String, {}, DbNull, Text
Object, "{"Timestamp":"2019-05-12T18:21:41.2106317+02:00","Level":"Information","MessageTemplate":"Test{testNo}: {@testObject} test2: {@testObj2} testStr: {@testStr:l}","Properties":{"testNo":0,"testObject":{"_typeTag":"TestObjectType1","IntProp":42,"StringProp":"Test"},"testObj2":{"_typeTag":"TestObjectType2","DateProp":"2019-05-12T18:21:41.1639858+02:00","NestedProp":{"_typeTag":"TestObjectType1","IntProp":42,"StringProp":"Test"}},"testStr":"stringValue","MachineName":"MyPC"},"Renderings":{"testStr":[{"Format":"l","Rendering":"stringValue"}]}}\r\n", string, Jsonb
String, "{"testNo":0, "testObject":{"IntProp":42,"StringProp":"Test","_typeTag":"TestObjectType1"}, "testObj2":{"DateProp":"2019-05-12T18:21:41.1639858+02:00","NestedProp":{"IntProp":42,"StringProp":"Test","_typeTag":"TestObjectType1"},"_typeTag":"TestObjectType2"}, "testStr":"stringValue", "MachineName":"MyPC"}", string, Text
Int32, 0, int, Integer
String, "MyPC", string, Text

Further technical details

Npgsql version: 4.0.6 (Latest)
PostgreSQL version: PostgreSQL 11.2 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit
Operating system: Windows 10

Other details about my project setup

I'm using Serilog with the Serilog.Sinks.PeriodicBatching sink.

If you look on the page related to connection string parameters you will see that of the Host parameter it says "If the value begins with a slash, it is used as the directory for the Unix-domain socket." This implies, at least to me, that npgsql can connect over Unix socket (and indeed I've spent much of the last hour trying to do just that) however if you go the security page we find that in fact "Npgsql does not support logging in via Unix-domain sockets (auth method local)."

The connection string page needs to be much clearer about this than it currently is. I would also like to see a link to the security page. I would also suggest adding to the description of the username and password parameters not that makes it clear that the pg_hba.conf needs to be configured to allow that user to connect to that database using password or md5. It should also be made clear that the it is not necessary to change your password parameter if the method is set to md5 (unless it is, in which case you should let us know that fact and how to change the password accordingly).

We may just need to disable it for API docs.

Link to https://docs.microsoft.com/en-us/dotnet/core/diagnostics/event-counters

This should document the removal of the old Windows Performance Counters support in 5.0 as well.

After reading this page: https://www.npgsql.org/doc/connection-string-parameters.html?q=Unix-domain%20sockets

It would be nice to have the following:

• A full example of a unix socket connection string
• Any details around whether the npgsql driver generates or appends /.s.PGSQL.5432 or if it needs to be specified explicitly
• An explanation as to why the port is still required when configuring socket connections

Hi,

I need to use the IncludeErrorDetail parameter in my connection string and even if I used the latest Npgsql version (5.0.7 at this time) I kept receiving Keyword not supported: includeerrordetail error.

Since there is a typo in this title having a trailing t at the end, I even gave that a shot.

Turns out the correct variant is IncludeErrorDetails (that is, with an s ).

Please correct the name in the official documentation and everywhere else it is referenced.

Thanks

We need a page listing relevant external plugins and database compatibility:

• PGroonga (https://github.com/JoyMoe/PGroonga.EntityFrameworkCore)
• EFCore.NamingConventions
• Cockroach (InterleaveInParent...)

Now that we've switched to Microsoft.Extensions.Logging. Keep the old docs for legacy use of pre-7.0 Npgsql.

[Test]
public async Task GetDataTypeName_enum()
{
	var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
	{
		MaxPoolSize = 1,
		Multiplexing = true
	};
	await using var conn = await OpenConnectionAsync(csb);
	await using var _ = await GetTempTypeName(conn, out var typeName);
	await conn.ExecuteNonQueryAsync($"CREATE TYPE {typeName} AS ENUM ('one')");
	conn.ReloadTypes();
	await using var cmd = new NpgsqlCommand($"SELECT 'one'::{typeName}", conn);
	await using var reader = await cmd.ExecuteReaderAsync(Behavior);
	await reader.ReadAsync();
	Assert.That(reader.GetDataTypeName(0), Is.EqualTo($"public.{typeName}"));
}

@roji @NinoFloris

Steps to reproduce

I've been reading about array, and I was wondering if there was a list of the item types supported for array and ranges:

• https://www.npgsql.org/doc/types/basic.html
• https://www.postgresql.org/docs/current/arrays.html

I can't find any... I mean the documentation can make you imagine that you can actually define an array or arrays or arrays of IP Address... which I doubt can work. It's very likely only 1D arrays, right?

But reading a further it gets a little fuzzy:

• https://www.postgresql.org/docs/13/extend-type-system.html
• https://www.postgresql.org/docs/13/datatype.html
• https://stackoverflow.com/questions/34403844/postgresql-multidimensional-arrays
• https://stackoverflow.com/questions/29186515/is-there-a-way-to-use-arrays-in-entity-framework-postgresql

Some talk about multi dimensional arrays... others just 1D arrays of a given base type... it's all confusing, so I would like to what is supported

The issue

No clear documentation about what types are supported as array items.

Further technical details

Npgsql version: 5.0.1.1
PostgreSQL version: PostgreSQL 10.5 (Ubuntu 10.5-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit
Operating system: (Client) Microsoft Windows 10 Enterprise

This documentation still doesn't describe semantics to add indices using Entity Framework Core library for Jsonb fields. Kindly point me to valid documentation for the same.
Thank You.

There's now both master and main, where main is marked as the default. Should the "improve this doc" now reference this branch as well? Note I'm not familiar with docfx so there might be more to this than the line I referenced.

See npgsql/npgsql#2269

Hey, I am trying to replicate a behavior found on Mapping and Translation/JSON page and it seems that JsonDocument does not have the GetProperty method available. Also, the translation doesn't seem to work. Is there any innacuracy introduced here or maybe the document is simply outdated and it should be done differently?

When looking around the Npgsql website I sometimes want to go straight to the Github repo to look at code, or find related issues I'm running across. There seems to be no visible way to do this.

Thoughts on adding links to the Github repos?

Could maybe just put the link to the org (https://github.com/npgsql) on the homepage under contributors? Or add a link on each page (npgsql/efcore/ef6) to their respective repos?

nevermind.

Resources:

• npgsql/npgsql#1721
• https://stackoverflow.com/questions/64261358/npgsql-connection-with-ssl-certificates-in-net-core-web-api

Version 3.2.5.

The class NpgsqlLogger and some interfaces define the methods such as IsEnabled and Log.

It seems that connectorId means: no connector. Maybe it is better to make it int?.

But in general, it would be easier to understand for developers writing a logger to add some documentation to the methods and their arguments.

Now it requires a download of npgsql itself and looking at the code to what it means.

Also, it might be handy when the Log method accept an object of a LogEvent class or alike. That class might define something like a unique message code to ease identification of what a message means.

Also, it might be handy to check whether bool Enabled is actually called ever.

From time to time users port an existing codebase from MSSQL to PostgreSQL, and run into issues - it would be useful to have a doc page that lists common pitfalls and issues. The following is a probably partial list that needs to be completed::

• Lack of MARS support (#462)

var myQueryable1 = myContext.MyDbset1.OrderByDescending(m => m.Timestamp);
var myQueryable2 = myContext.MyDbset2.Where(m => m.IsActive);

foreach (var q1 in myQueryable1) {
  foreach (var q2 in myQueryable2) { //Exception occurs here
    ...
  }
}

• No DateTimeOffset database type (timestamptz is not a good representation)
• Stored functions and procedures differences
  • Cannot return multiple resultsets from functions, need to manually use cursors (#1785)
  • No out params separate from the resultset
  • If you use the new PG 11 stored procedures, you have to call them manually (no CommandType.StoredProcedure support)

I've been reviewing https://github.com/npgsql/doc/blob/main/conceptual/Npgsql/security.md/#L1 and I'm not sure of one key point: are the environment variables/connection string parameters meant to contain only file paths? or can the contents of the certs themselves be used as the value? I'm on a sub-5.0 version.

Related, is the Client Certificate Key not exposed as an environment variable? and if not, can it be used with the root and client certs being specified through the environment variables?

Both of the code sample comments denote "to":
// Import two columns to table data
// Export two columns to table data

The latter I would assume should be
// Export two columns from table data

Basic Usage

Current:

The command contains the SQL to be executed, as wel as any parameters (see the parameters section below). Commands can be executed in the following three ways:

Suggested:

The command contains the SQL to be executed, as well as any parameters (see the parameters section below). Commands can be executed in the following three ways:

Our release note page for EFCore.PG 3.x contains contribution information, but they seem to refer to Npgsql instead of EFCore.PG.

Note that to me it makes sense to unify 3.0 and 3.1 into the same 3.x page (including all their patch version contribution info) - 3.0 is out of support and there's no reason for anyone to use it anymore (plus 3.1 doesn't really improve that much over it).

@Brar do you think you can take a look at this?

The page Connection String Parameters of npgsql does not include the Include Error Detail switch.

Definition:
https://github.com/npgsql/npgsql/blob/e84f96abf67ca4198787126a00a8b14b1a228be0/src/Npgsql/NpgsqlConnectionStringBuilder.cs#L712-L731

Should be added to:
https://github.com/npgsql/doc/blob/main/conceptual/Npgsql/connection-string-parameters.md

I'm trying to do the following but cannot find any help :

• sorted by relevance
• different weight per property (title, description)
• ignore accents

I have tried many ways and only managed to make this work (which only returns matches) :

Entries = Db.Posts.Where(p => p.SearchVector.Matches(Query));

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Post>()
        .HasGeneratedTsVectorColumn(
            p => p.SearchVector, "english",
            p => new { p.Name, p.Description })
        .HasIndex(p => p.SearchVector).HasMethod("GIN");

I think it would be worth adding in the docs.

On https://www.npgsql.org/doc/installation.html#gac-installation, there is the text:

"... you can download the Npgsql Windows installer from our Github releases page"

However, there is no Npgsql Windows installer, only source releases.

1. Is there a binary Windows installer available somewhere, then the link should be updated
2. If no binary exists, but the Windows installer is still supported, perhaps this should be a separate project, and link to that?
3. If the Npgsql Windows installer is no longer supported, I think Microsoft would like to know that, since they reference is on: https://support.microsoft.com/en-us/office/import-data-from-external-data-sources-power-query-be4330b3-5356-486c-a168-b68e9e616f5a

See npgsql/efcore.pg#2303

I want to import multiple CSV files within a single transaction into my Postgres database, i.e., if any of the CSV file imports fails, all imports should be rolled back.

Bulk copy for CSV is explained here. However, I am not sure if this happens all within the same transaction, especially when using different writers. So let's assume I do this:

NpgsqlTransaction transaction = conn.BeginTransaction();
using (var fooWriter = conn.BeginTextImport("COPY foo (field_text, field_int4) FROM STDIN")) {
    fooWriter.Write("HELLO FOO\t1\n");
    fooWriter.Write("GOODBYE\t2\n");
}
using (var barWriter = conn.BeginTextImport("COPY bar (field_text, field_int4) FROM STDIN")) {
    barWriter.Write("HELLO BAR\t1\n");
    barWriter.Write("GOODBYE\t2\n");
}
transaction.Commit();

in order to import data to a table called foo and a table called bar.

When either the import to bar fails, or transaction.Rollback() is called, is the import to foo rolled back, too? I think it would help to clarify this in the docs.

Furthermore, for the Binary Copy the explanation about writer.Complete():

When you've finished, you must call Complete() to save the data; not doing so will cause the COPY operation to be rolled back when the writer is disposed (this behavior is important in case an exception is thrown).

seems to imply that either a commit or a rollback happens already when the Writer is disposed. How does this relate to my question above and what is the relationship between Writer lifecycle / scope and the NpgsqlTransaction instance in the context of binary bulk copies?

https://www.npgsql.org/doc/api/Npgsql.NpgsqlConnection.html#methods shows only two methods now: Open() and OpenAsync(). NpgsqlConnection of course has a lot more methods than that!

https://www.npgsql.org/doc/prepare.html shows an example of two UPDATE statements. It explains that the individual statements are prepared. However, it doesn't clearly state if using multiple statements with a single prepare in one command is actually the best practice.

What if I want to do 200 Updates or 200 Inserts? Do I put all 200 Updates in a single parameterized query and call Prepare() once followed by ExecuteQuery()? Is this the optimal way to do it?

Would it perform differently if I created 1 single Update statement in one query, prepared it, executed.... and then put the remaining 199 in a second query without preparing it? I feel like it would help if the documentation explained better what's happening under the hood.

Another example on the same page demonstrates Preparing a query, Executing it, then changing the parameters and Executing it again. Is this meant to be in a loop?? The code is lacking explanation.

Maybe some guidance on how to use prepare based on what KIND of operation(s) you're doing would be helpful.

We should have a Pooling page under "advanced topics", which details how the pool works and all the various tweaks we support.

• Database restart doesn't clear the pool - you need resiliency (e.g. npgsql/npgsql#5073)

Hi, I like to use Npgsql.EntityFrameworkCore.PostgreSQL.Trigrams, but I am unable to use that because I am finding this kind of errors during configuration. It would be a great help for all of us if a demo code with configuration be added in the documentation like Microsoft documentation.

Micro correction for documentation...

Current ICU documentation link is https://unicode-org.github.io/icu/userguide/

Documentation at https://www.npgsql.org/efcore/misc/collations-and-case-sensitivity.html?tabs=data-annotations points to old address.

• Respect [JsonPropertyName] (since 5.0, npgsql/efcore.pg#2037)
• Workaround for setting JsonSerialization options: npgsql/efcore.pg#1107 (comment)

I have this issue on chrone/firefox/edge