Text after newline character is truncated when using pgtype.Text to insert into a varchar column

[slowcooked99]

I have been running into an odd issue where when using pgtype.Text to insert into a varchar column, the text being inserted will be truncated if it contains newline characters like \n. Note that the varchar column in my table does not have a character limit.

In my usecase, I am building a pgtype.Text like this and trying to insert a markdown string:

pgtype.Text{String: "my text **hello** \n goodbye", Valid: true}

However, in the production environment the text in the column gets truncated to only the text before the newline, ie my text **hello**. I have tried to reproduce this issue in a development environment but it does not have the same issue (the newline and text following it gets inserted fine in development).

Could there be some sort of environment discrepancy that I have overlooked which causes the text after a newline to be ignored on insert?

The query I am using looks something like this:

const upsert = `-- name: Upsert :exec
INSERT INTO my_db."MyTable" ("id", "markdownText")
VALUES ($1, $2)
ON CONFLICT ("id") DO UPDATE SET "markdownText" = $2
`

type UpsertParams struct {
	ID  string
	MarkdownText pgtype.Text
}

func (q *Queries) Upsert(ctx context.Context, arg UpsertParams) error {
	_, err := q.db.Exec(ctx, upsert,
		arg.ID,
		arg.MarkdownText
	)
	return err
}

[jackc]

Can you test that you are using the same QueryExecMode in development and production? Obviously, it should work in both but that's the only thing that comes to mind on the pgx side.

Another possibility is if some proxy is corrupting the query on production. If you're using a proxy on production see if the same problem occurs when connecting directly to the database.

[slowcooked99]

Can you test that you are using the same QueryExecMode in development and production? Obviously, it should work in both but that's the only thing that comes to mind on the pgx side.

Thanks for the response @jackc. I found that we are using the default cache statement exec mode in development but using exec mode in production.

My hunch is that could be impacting how input parameters are interpreted in the Upsert query. I see there's a code comment in conn.go that says it uses the extended protocol with text formatted parameters and results. I still haven't confirmed but I'll try changing the prod query exec mode to cache statement and see if that fixes the issue.

Another possibility is if some proxy is corrupting the query on production. If you're using a proxy on production see if the same problem occurs when connecting directly to the database.

We're not using a proxy as far as I can tell.

[slowcooked99]

I ended up changing the mode to cache_statement in Prod (which matches the development environment) but the truncation issue is still there. Looking into other environmental differences.

[slowcooked99]

Closing this discussion - I found there was a separate asynchronous worker in my system which was updating the text in the db incorrectly. There's nothing actually wrong with how pgx is executing the query.

Thanks anyways for the suggestions!