wrong `pog.Date` format causes crash

[binajmen]

Description

When using pog to insert data into PostgreSQL nullable columns, passing an empty string ("") causes a runtime error. This is a common issue with PostgreSQL drivers since PostgreSQL treats empty strings differently from NULL values. Currently, pog crashes with an Erlang error instead of handling this case gracefully.

Current Behavior

When attempting to insert an empty string into a nullable PostgreSQL column:

The empty string is passed directly to PostgreSQL
pog crashes with an Erlang error:

runtime error: Erlang error
No Erlang function clause matched the arguments it was called with.
stacktrace:
  pog_ffi.convert_error
  pog_ffi.query
  pog.execute

Expected Behavior

There are two potential solutions to consider:

• Handle empty strings gracefully: Convert empty strings to NULL values automatically when inserting into nullable columns
• Better error handling: Provide a clear error message when an empty string is provided for a nullable column, explaining that NULL should be used instead

Steps to Reproduce

Create a PostgreSQL table with a nullable column:

create table if not exists transactions (
    id serial primary key,
    account varchar(50) not null,
    counterparty_account varchar(50)  -- nullable
);

Create an insert query:

insert into transactions (
    account,
    counterparty_account
) values (
    $1,  -- account (varchar(50))
    $2   -- counterparty_account (varchar(50), nullable)
)
returning id

Attempt to insert a record with an empty string for the nullable field:

sql.insert_transaction(
  pog,
  tx.account,
  ""  // empty string for counterparty_account
)

For a quick reproduction, you can clone this repo:

https://github.com/binajmen/budget

Additional Context

• This issue was initially discovered when working with squirrel-generated code, but the root cause appears to be in pog's handling of empty strings.
• Related Discord discussion: https://discord.com/channels/768594524158427167/1334687945566847066

Technical Details

pog version: 3.2.0
PostgreSQL version: 17
Gleam version: 1.6.2

[binajmen]

Ok this was due to a wrong format for the date...

[lpil]

We certainly don't want to crash! Could you share a reproduction for the crash please 🙏

[binajmen]

Sure, with the following code snippet:

import gleam/option.{Some}
import pog

pub fn main() {
  let db =
    pog.default_config()
    |> pog.host("localhost")
    |> pog.database("postgres")
    |> pog.user("postgres")
    |> pog.password(Some("postgres"))
    |> pog.pool_size(15)
    |> pog.connect

  let create_table_query =
    "
    CREATE TABLE IF NOT EXISTS test (
      id SERIAL PRIMARY KEY,
      some_date DATE
    )
  "

  let assert Ok(response) =
    pog.query(create_table_query)
    |> pog.execute(db)

  let insert_query =
    "
    INSERT INTO test (some_date) 
    VALUES ($1)
  "

  let assert Ok(response) =
    pog.query(insert_query)
    |> pog.parameter(pog.date(pog.Date(31, 01, 2025)))
    |> pog.execute(db)

  pog.disconnect(db)
}

You should get the following error:

runtime error: Erlang error

No Erlang function clause matched the arguments it was called with.

stacktrace:
  pog_ffi.convert_error /Users/binajmen/Developer/pog_crash/build/dev/erlang/pog/_gleam_artefacts/pog_ffi.erl:122
  pog_ffi.query /Users/binajmen/Developer/pog_crash/build/dev/erlang/pog/_gleam_artefacts/pog_ffi.erl:119
  pog.execute /Users/binajmen/Developer/pog_crash/build/packages/pog/src/pog.gleam:478
  pog_crash.main /Users/binajmen/Developer/pog_crash/src/pog_crash.gleam:43

I was parsing a date in the format dd/mm/yyyy and forgot to switch the year and day, hence the pog.Date(31, 01, 2025).

[ghivert]

This still happen when providing wrong format. This is due to pgo returning {error, badarg} when an argument is false (instead of the correct badarg_encoding). Are you interested in a PR to add a catch-all clause in convert_error? Adding an error like UnexpectedError for example? To help people understand the issue is not coming from their code?

(I discovered the issue when providing a wrong UUID to PG, and ended up with a function clause error.)

[lpil]

This is due to pgo returning {error, badarg} when an argument is false (instead of the correct badarg_encoding)

Sounds like a bug in PGO?

Are you interested in a PR to add a catch-all clause in convert_error? Adding an error like UnexpectedError for example?

We don't want to be converting unexpected errors into expected ones, it would mean that critical errors from bugs in the underlying code don't cause a crash, as they should.

[ghivert]

Sounds like a bug in PGO?

Yes. I think it is. But taking time to reproduce the error and fix it in PGO is a bit out of my possibilities as of now. 😔

We don't want to be converting unexpected errors into expected ones, it would mean that critical errors from bugs in the underlying code don't cause a crash, as they should.

I understand, that was the reason of my question. 🙂 It should probably be better to fix directly PGO, but currently an error in PGO leads to a hard crash without explanation. 😔 I was just thinking to provide a better experience for pog users.

[lpil]

It might provide a better experience in this specific case, but it would provide a worse experience in others, since we don't know what the buggy code is doing. We'll need to get this fixed upstream.