C#, PostgreSQL syntax error at end of input

Question

I can't seem to be able to create migration with simplest sql to create PostgreSQL procedure.

Error is:

Exception data: Severity: ERROR SqlState: 42601 MessageText: syntax error at end of input Position: 186 File: scan.l Line: 1184 Routine: scanner_yyerror

Sql code works fine when executed in pgAdmin.

protected override void Up(MigrationBuilder migrationBuilder)
{
    var sp = @"CREATE PROCEDURE reset_primary_holder()
                LANGUAGE SQL
                BEGIN ATOMIC

                SELECT * FROM id.users;

                END;";
    migrationBuilder.Sql(sp);
}

What am I doing wrong?

A procedure isn't meant to return a result you need to use a function. (Btw: do you really have a schema named `id`? Sounds like a strange naming convention) — , Jan 23 '23 at 08:56
I guess EF parses the SQL string somehow and stops at the first `;` and thus sends a partial (and invalid) SQL command to the server — , Jan 23 '23 at 08:59

score 2 · Answer 1 · answered Jan 23 '23 at 09:27

2

Npgsql (the ADO.NET driver, not the EF provider) by default parses SQL to find semicolons and rewrite statements, and the new PostgreSQL syntax breaks that parser; see https://github.com/npgsql/npgsql/issues/4445.

The general recommended fix is to add the following to the start of your program:

AppContext.SetSwitch("Npgsql.EnableSqlRewriting", false);

This disables statement parsing/rewriting altogether; it means you need to use positional parameter placeholders ($1, $2 instead of @p1, @p2) - see the docs.

However, EF is unfortunately not yet compatible with this mode, so you'll have to make do without this syntax...

answered Jan 23 '23 at 09:27

Shay Rojansky

15,357
2
40
69

Thanks @Shay. If I understand correctly problem is with new syntax that PG introduced. Is there a way to rewrite this code without BEGIN ATOMIC or to set the switch back on after migrations are run at startup? I know that this is not the question might not be for for you, but I have to try :) – Milan Jan 23 '23 at 12:56
I'm not sure if the same thing can be done in PG with another syntax - that's more a question for PG people. Unfortunately, Npgsql caches the value of EnableSqlRewriting (for perf), so you can't change it after startup. – Shay Rojansky Jan 24 '23 at 11:08
But note that EF generally discourages running migrations at startup ([see docs](https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/applying?tabs=dotnet-core-cli)). If you run migrations separately, it's easy to work around this by setting EnableSqlRewriting to false only for migrations (via a design-time context), or by executing SQL migrations scripts directly (at which point Npgsql isn't involved). – Shay Rojansky Jan 24 '23 at 11:09
Thanks @Shay, I really appreciate your help here and in general your contributions to community. – Milan Jan 25 '23 at 12:39

score 0 · Accepted Answer · answered Jan 24 '23 at 08:43

In the end only viable solution was to to use different syntax:

protected override void Up(MigrationBuilder migrationBuilder)
{
    var sp = @"CREATE PROCEDURE my_procedure()
            AS $$
            UPDATE id.users
            SET ...;
            Another statement;
            $$ LANGUAGE sql;";            
    migrationBuilder.Sql(sp);
}

This syntax is not mentioned in documentation on stored procedures and since this is my first PG procedure / function it was not immediately obvious to me that there is alternative.

Thanks @Shay for pointing me in the right direction.

C#, PostgreSQL syntax error at end of input

2 Answers2