Unhandled exception in execution #xxx: 42702: column reference "DatabasePath_Text" is ambiguous

[cole.brand_2889]

Npgsql.PostgresException (0x80004005): 42702: column reference "DatabasePath_Text" is ambiguous
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at 
<snip>
Inedo.ProGet.Feeds.Docker.BlobScanner.DockerBlobScanner.ScanBlobAsync(DockerBlobs blobData, Stream blobStream, ILogSink log) in C:\Users\builds\AppData\Local\Temp\InedoAgent\BuildMaster\192.168.44.60\Temp\_E653823\Src\src\ProGet\Feeds\Docker\BlobScanner\DockerBlobScanner.cs:line 112
<snip>
  Exception data:
    Severity: ERROR
    SqlState: 42702
    MessageText: column reference "DatabasePath_Text" is ambiguous
    InternalPosition: 750
    InternalQuery: WITH BlobPackages_Table AS (
        SELECT * FROM jsonb_to_recordset("@BlobPackages_Table") AS ("DatabasePath_Text" VARCHAR(200), "PackageVersion_Id" INT)
    ),
    packagesToRemove AS (
        SELECT *
          FROM "DockerBlobPackages" DBP
          LEFT JOIN BlobPackages_Table BPT 
                 ON BPT."DatabasePath_Text" = DBP."DatabasePath_Text" 
                AND BPT."PackageVersion_Id" = DBP."PackageVersion_Id"
         WHERE DBP."DockerBlob_Id" = "@DockerBlob_Id" 
           AND BPT."PackageVersion_Id" IS NULL
    ),
    deletes AS  (
        DELETE FROM "DockerBlobPackages" DBP
              USING packagesToRemove PTR
              WHERE DBP."DockerBlob_Id" = "@DockerBlob_Id"
                AND DBP."DatabasePath_Text" = PTR."DatabasePath_Text" 
                AND DBP."PackageVersion_Id" = PTR."PackageVersion_Id"
    ),
    newBlobPackages AS (
        SELECT BPT.*
          FROM BlobPackages_Table BPT
               LEFT JOIN "DockerBlobPackages" DBP 
                      ON DBP."DockerBlob_Id" = "@DockerBlob_Id" 
                     AND BPT."DatabasePath_Text" = DBP."DatabasePath_Text" 
                     AND BPT."PackageVersion_Id" = DBP."PackageVersion_Id"
         WHERE DBP."PackageVersion_Id" IS NULL
    )
    INSERT INTO "DockerBlobPackages"
         SELECT "@DockerBlob_Id",
                "DatabasePath_Text",
                "PackageVersion_Id"
           FROM newBlobPackages BPT
    Where: PL/pgSQL function "DockerBlobs_RecordScanData"(integer,xml,jsonb) line 18 at SQL statement
    File: parse_relation.c
    Line: 831
    Routine: scanRTEForColumn

Is it possible we have something misconfigured on our Aurora Postgres instance? Or is this a bug in the application?

Version 2025.25 (Build 11)

[atripp]

Hi @cole-brand_2889 ,

In general, that error message means some kind of code problem. Like, using "DatabasePathText" on a multi-table join without specifying which table/alias it belongs to.

However given the queries (see below code), I don't see the problem. I haven't seen the error on PostgreSQL... so that must mean it's Aurora Postgres specific?

According to ChatGPT, "Aurora PostgreSQL is stricter about recordset functions and want a relation alias before the column definition list.", but who knows if that's true. About the only way to test this theory is to modify the function code in your database. I've pasted it below, and you should be able to just run that to "edit" the code.

It'll get updated during any normal upgrade/downgrade, so no real worry.

The first change suggested was to add a BPT here:

    WITH BlobPackages_Table AS (
        SELECT * FROM jsonb_to_recordset("@BlobPackages_Table") AS BPT("DatabasePath_Text" VARCHAR(200), "PackageVersion_Id" INT)
    ),

I don't see how that could work, but who knows. Another suggested change was this:

    WITH BlobPackages_Table AS (
        SELECT * FROM jsonb_to_recordset(COALESCE("@BlobPackages_Table", '[]'::jsonb)) AS BPT("DatabasePath_Text" VARCHAR(200), "PackageVersion_Id" INT)
    ),

Although, I also don't believe that word work, since the @BlobPackages_Table would not be null. But again who knows.

Anyway... that's where I would start. It might be something else altogether, but I can't see it and I guess my ChatGPT prompt didn't spot it either.

CREATE OR REPLACE PROCEDURE "DockerBlobs_RecordScanData"
(
    "@DockerBlob_Id" INT,
    "@BlobInfo_Configuration" XML,
    "@BlobPackages_Table" JSONB
)
LANGUAGE plpgsql
AS $$
BEGIN

    IF "@BlobInfo_Configuration" IS NULL THEN
        DELETE FROM "DockerBlobInfos" WHERE "DockerBlob_Id" = "@DockerBlob_Id";
    ELSE
        INSERT INTO "DockerBlobInfos" ("DockerBlob_Id", "BlobInfo_Configuration")
             VALUES ("@DockerBlob_Id", "@BlobInfo_Configuration")
        ON CONFLICT DO
         UPDATE SET "BlobInfo_Configuration" = "@BlobInfo_Configuration"
              WHERE "DockerBlob_Id" = "@DockerBlob_Id";
    END IF;

    UPDATE "DockerBlobs"
       SET "LastScan_Date" = CURRENT_TIMESTAMP
     WHERE "DockerBlob_Id" = "@DockerBlob_Id";

    WITH BlobPackages_Table AS (
        SELECT * FROM jsonb_to_recordset("@BlobPackages_Table") AS ("DatabasePath_Text" VARCHAR(200), "PackageVersion_Id" INT)
    ),
    packagesToRemove AS (
        SELECT *
          FROM "DockerBlobPackages" DBP
          LEFT JOIN BlobPackages_Table BPT 
                 ON BPT."DatabasePath_Text" = DBP."DatabasePath_Text" 
                AND BPT."PackageVersion_Id" = DBP."PackageVersion_Id"
         WHERE DBP."DockerBlob_Id" = "@DockerBlob_Id" 
           AND BPT."PackageVersion_Id" IS NULL
    ),
    deletes AS  (
        DELETE FROM "DockerBlobPackages" DBP
              USING packagesToRemove PTR
              WHERE DBP."DockerBlob_Id" = "@DockerBlob_Id"
                AND DBP."DatabasePath_Text" = PTR."DatabasePath_Text" 
                AND DBP."PackageVersion_Id" = PTR."PackageVersion_Id"
    ),
    newBlobPackages AS (
        SELECT BPT.*
          FROM BlobPackages_Table BPT
               LEFT JOIN "DockerBlobPackages" DBP 
                      ON DBP."DockerBlob_Id" = "@DockerBlob_Id" 
                     AND BPT."DatabasePath_Text" = DBP."DatabasePath_Text" 
                     AND BPT."PackageVersion_Id" = DBP."PackageVersion_Id"
         WHERE DBP."PackageVersion_Id" IS NULL
    )
    INSERT INTO "DockerBlobPackages"
         SELECT "@DockerBlob_Id",
                "DatabasePath_Text",
                "PackageVersion_Id"
           FROM newBlobPackages BPT;

END $$;

// also I do realize the json input is not a great way to handle this, but it's how we needed to port a few things from SQL Server to maintain parity in behavior

Let us know if you find anything! Thanks, Alana