Skip to content

Postgres temp tables being are being created with column names larger than maximum #82

@ghost

Description

When running an insert on a table called yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy with 2 fields called xxxxx_xxxxxxxxxxxxx_xxxxxxx_answer_id and xxxxx_xxxxxxxxxxxxx_xxxxxxx_id

Npgsql.Bulk will run the query:

CREATE TEMP TABLE _temp__a85e0e06_a254_4805_b172_28627fbcfd4d_54 ON COMMIT DROP AS
SELECT
    "yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy"."xxxxx_xxxxxxxxxxxxx_xxxxxxx_answer_id" AS 
yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy_xxxxx_xxxxxxxxxxxxx_xxxxxxx_answer_id,

    "yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy"."xxxxx_xxxxxxxxxxxxx_xxxxxxx_id" AS 
yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy_xxxxx_xxxxxxxxxxxxx_xxxxxxx_id
FROM
    "yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy"
LIMIT
    0

However, postgres caps column names at 63 characters and discards all the characters after that limit, resulting in the error:

ERROR: column "yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy_xxxxx_xxxxxxxxxxxxx_xxxxxxx_" specified more than once

Call stack:

2023-08-03 09:49:11 Npgsql.PostgresException (0x80004005): 42701: column "yyyyy_yyyyyyyyyyyyy_yyyyyyy_yyyyyy_xxxxx_xxxxxxxxxxxxx_xxxxxxx_" specified more than once
2023-08-03 09:49:11    at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|233_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
2023-08-03 09:49:11    at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
2023-08-03 09:49:11    at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
2023-08-03 09:49:11    at Npgsql.NpgsqlDataReader.NextResult()
2023-08-03 09:49:11    at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
2023-08-03 09:49:11    at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
2023-08-03 09:49:11    at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
2023-08-03 09:49:11    at Npgsql.NpgsqlCommand.ExecuteNonQuery()
2023-08-03 09:49:11    at Npgsql.Bulk.NpgsqlBulkUploader.ExecuteNonQuery(NpgsqlConnection connection, String command)
2023-08-03 09:49:11    at Npgsql.Bulk.NpgsqlBulkUploader.Insert[T](IEnumerable`1 entities, InsertConflictAction onConflict)

Could some UUID be used instead of simply concatenating the table and column names?

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions