Thread: BUG #18111: Query hangs when trying to INSERT empty string into NOT NULL table

BUG #18111: Query hangs when trying to INSERT empty string into NOT NULL table

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      18111
Logged by:          Maciej Klimkowicz
Email address:      maciej.klimkowicz@oxla.com
PostgreSQL version: 15.1
Operating system:   Ubuntu
Description:

Steps to reproduce:

1.CREATE TABLE texttable (col1 TEXT NOT NULL);
2.INSERT INTO texttable (col1) VALUES ('');

Query hangs indefinitely.
For NULL column it works as expected.


Re: BUG #18111: Query hangs when trying to INSERT empty string into NOT NULL table

From
David Rowley
Date:
On Fri, 15 Sept 2023 at 21:48, PG Bug reporting form
<noreply@postgresql.org> wrote:
> 1.CREATE TABLE texttable (col1 TEXT NOT NULL);
> 2.INSERT INTO texttable (col1) VALUES ('');
>
> Query hangs indefinitely.
> For NULL column it works as expected.

Does it still hang on a freshly created database?

Do you see any ungranted locks in pg_locks for the process ID of the
waiting backend?

select * from pg_locks where pid=NNN and not granted;

You can obtain the PID from the backend before you execute the query
that's going to hang with:

select pg_backend_pid();

Do you see any rows pg_event_trigger?:

select * from pg_event_trigger;

David



Re: BUG #18111: Query hangs when trying to INSERT empty string into NOT NULL table

From
Julien Rouhaud
Date:
Hi,

On Fri, Sep 15, 2023 at 09:00:31AM +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> PostgreSQL version: 15.1
>
> Steps to reproduce:
>
> 1.CREATE TABLE texttable (col1 TEXT NOT NULL);
> 2.INSERT INTO texttable (col1) VALUES ('');
>
> Query hangs indefinitely.
> For NULL column it works as expected.

It seems very unlikely to be a bug in postgres.  I can't reproduce it on either
latest minor version for pg 15 or on HEAD.  Have you checked in
pg_stat_activity and/or pg_locks why the query is waiting on?  Maybe you have
some other extension or are using a fork that's responsible for this behavior?

In any case, the version 15.1 is missing 3 minor versions, so about 9 months
worth of bug fixes.  You should start by updating to 15.4 first and see if you
still have the problem.