Using latest pganalyze/libpg_query vs not latest pg_query.proto fails Protobuf via NuGet

[svdgriendt]

After typing all of the below, I think I spotted the issue
In the main.yaml you're building against the latest version of https://github.com/pganalyze/libpg_query.git, but that's not the version of which the proto file is. Maybe it's better to pin these to specific commit hashes, to prevent these mismatches? My assumption is that updating the protobuf file and running the C# codegen again will fix this issue.

It's that I don't know how exactly you generated the the C# code out of it, it seems automatic in some way as I can't find the PgQuery.cs file anywhere...

Also a small hint: the NATIVE_SETUP.md is now outdated that the Examples/pg_query.dll is no longer there 😉.

---

I am not entirely sure where you fish this pg_query.dll from. I was surprised that my check-out with a submodule was working, but with the NuGet version it was not. I noticed this, because after peeking a bit beyond the examples, I noticed you already implemented a protobuf version, which covers a lot of what I was talking about earlier for "strongly typed classes/structs" in #1

I noticed that in 824e098 there were two pg_query.dll files, but they're actually different ones! When I include the one from Examples, I get the same behavior as with the current NuGet version. It's also exactly the same in size. When I use the one from Npgquery.Tests (which I did before with the submodule), it works as expected.

Some code to reproduce this with (using the NuGet package)

public static void DoIt()
{
  var query = """
              CREATE TABLE foo
              (
                id INT PRIMARY KEY
              );
              """;

  var tmp = Parser.QuickParse(query);
  var parsed = Parser.QuickParseProtobuf(query);
  if (parsed.IsError)
    return;

  var statement = parsed.ParseTree.Stmts[0].Stmt;
  switch (statement.NodeCase)
  {
    case Node.NodeOneofCase.CreateStmt:
      var createStatement = statement.CreateStmt!;
      var catalogName = createStatement.Relation.Catalogname;
      var schemaName = createStatement.Relation.Schemaname;
      var tableName = createStatement.Relation.Relname;
      Console.WriteLine($"Table = {catalogName}.{schemaName}.{tableName}");
      break;
  }
}

What you'll see when you use the NuGet or the Examples version, which is 3,732,480 bytes, that the protobuf version comes back with a DropTableSpaceStmt. When you use the Npgquery.Tests version, which is 3,558,400 bytes, that the protobuf version comes back with a CreateStmt, which is expected here.

Also, when I add this with the latest commit available currently (f57f798), I just passes. But again, this uses a different version of pg_query.dll (for win-x64 at least) than that's packaged in NuGet.

    [Fact]
    public void ParseProtobuf_CreateTable_ReturnsCreateStmt()
    {
        // Arrange
        var query = "CREATE TABLE foo (id INT PRIMARY KEY);";

        // Act
        var result = _parser.ParseProtobuf(query);

        // Assert
        Assert.True(result.IsSuccess);
        Assert.NotNull(result.ParseTree);
        Assert.Null(result.Error);
        Assert.Equal(query, result.Query);
        Assert.NotNull(result.ParseTree.Stmts);
        Assert.Single(result.ParseTree.Stmts);
        Assert.Equal(Node.NodeOneofCase.CreateStmt, result.ParseTree.Stmts[0].Stmt.NodeCase);
    }

[JaredMSFT]

Hey @svdgriendt! the dll was intentionally removed from the Examples project to ensure that the embedded resource from the nuget package is used when running the Examples project.

I was previously manually building the dll (and the associated protobuf definitions), but I've moved that process into an automation step using GitHub Actions

I'll investigate how to ensure the protobuf definition is also synchronized.

Thanks again!

[JaredMSFT]

@svdgriendt one more update, i've added some resources to ensure that the protobuf definitions are aligned to the libpg_query repo sources, and included some new unit tests, and published an updated package version. thanks so much for the feedback, please review as your time allows and then we can close this issue when ready.

[svdgriendt]

I just pulled the 1.1.0 version from NuGet and now it works directly what I built before to query the Protobof result. I've checked the commits you've done to fix it, which is great with the PowerShell scripts. I'm not sure if I'm on the latest version (7f257e8), but I also checked .github/workflow/.main.yml there. I see there was already a libpg_query_ref, which I so overlooked last night...

I can see libpg_query_ref is used in "native Linux", "native MacOS" and "native Windows". That looks like all of them, so that's good. I'm not familiar enough with GitHub so tell how you inject these paramaters, but I have trust here 😄.

Just for the sake of a double check, I've also ran the ./buildscripts/Build-LibPgQueryWindows.ps1 with 7f257e8, but for me it doesn't work as I don't run Visual Studio. I can see it really does pull the correct tag, so there can't be a mismatch there either. But given that this release makes it all work again, and all looks to use a tag, it can't go wrong really, right?

I also tried to run the unit tests, and many failed. The reason being that there's no automatic pg_query.dll available during check-out or build. You're now forced to build it yourself (which failed for me, but no worries, I grabbed it from the NuGet package). Once I placed pg_query.dll at the right places, it was all good and all unit tests passed. But also, when pg_query.dll was not there, some tests passed, which look quite silly to me?

• ProtobufDefinitionTests
• QueryUtilsTests
  • CleanQuery_QueryWithWhitespace_RetournsCleanedQuery
  • HaveSameStructure_DifferentQueries_ReturnsFalse
  • ParsePlpgsql_InvalidCodes_ReturnError
  • ParsePlpgsql_InvalidSyntax_ReturnsError
  • ParsePlpgsql_MissingEnd_ReturnsError
  • ParsePlpgsql_NullInput_ThrowsException
  • QueryUtils_IsValidPlpgsql_InvalidCode_ReturnsFalse

Perhaps that's something for later and not in this issue? Feels like hijacking and scope creep to me 😉.