Re: Cannot drop column

[Rich Shepard]

On Wed, 18 Dec 2024, Rich Shepard wrote:


Since your column name has an Upper case character, you will have to use
double quotes to drop it. Try alter table statustypes drop column
"Suspect";



Thanks for the lesson.


But,

bustrac=# alter table statustypes drop column "Suspect"; 
ERROR:  column "Suspect" of relation "statustypes" does not exist

bustrac=#

Double quoting didn't resolve the issue.

Rich

Re: Cannot drop column

[David G. Johnston]

On Wednesday, December 18, 2024, Rich Shepard 
wrote:

> On Wed, 18 Dec 2024, Rich Shepard wrote:
>
> Since your column name has an Upper case character, you will have to use
>>> double quotes to drop it. Try alter table statustypes drop column
>>> "Suspect";
>>>
>>
> Thanks for the lesson.
>>
>
> But,
>
> bustrac=# alter table statustypes drop column "Suspect"; ERROR:  column
> "Suspect" of relation "statustypes" does not exist
> bustrac=#
>
> Double quoting didn't resolve the issue.
>

Red Ron’s email…

David J.

Re: Cannot drop column

[Rich Shepard]

On Wed, 18 Dec 2024, Viral Shah wrote:


Since your column name has an Upper case character, you will have to use
double quotes to drop it. Try alter table statustypes drop column "Suspect";


Viral,

Huh! I've not before encountered this in the 30+ years I've used postgres.

Thanks for the lesson.

Regards,

Rich

Re: Cannot drop column

[Rich Shepard]

On Wed, 18 Dec 2024, David G. Johnston wrote:


Failed to double-quote your column name.


David,

Thanks. That's a new one on me.

Regards,

Rich

Cannot drop column

[Rich Shepard]

I'm not seeing why postgres won't drop a table's column:

bustrac=# select * from statustypes order by stat_name;
 stat_name 


 Client
 Lead
 No further contact
 Opportunity
 Proposal submitted
 Prospect
 Qualified
 Referral
 Suspect
(9 rows)

bustrac=# alter table statustypes drop column Suspect;
ERROR:  column "suspect" of relation "statustypes" does not exist
bustrac=#

What have I done incorrectly?

TIA,

Rich

Re: Cannot drop column

[David G. Johnston]

On Wed, Dec 18, 2024 at 11:47 AM Rich Shepard 
wrote:

> I'm not seeing why postgres won't drop a table's column:
>
> bustrac=# select * from statustypes order by stat_name;
>   stat_name
> 
>   Client
>   Lead
>   No further contact
>   Opportunity
>   Proposal submitted
>   Prospect
>   Qualified
>   Referral
>   Suspect
> (9 rows)
>
> bustrac=# alter table statustypes drop column Suspect;
> ERROR:  column "suspect" of relation "statustypes" does not exist
> bustrac=#
>
> What have I done incorrectly?
>

Failed to double-quote your column name.

David J.

Re: Cannot drop column

[Ron Johnson]

On Wed, Dec 18, 2024 at 1:47 PM Rich Shepard 
wrote:

> I'm not seeing why postgres won't drop a table's column:
>
> bustrac=# select * from statustypes order by stat_name;
>   stat_name
> 
>   Client
>   Lead
>   No further contact
>   Opportunity
>   Proposal submitted
>   Prospect
>   Qualified
>   Referral
>   Suspect
> (9 rows)
>
> bustrac=# alter table statustypes drop column Suspect;
> ERROR:  column "suspect" of relation "statustypes" does not exist
> bustrac=#
>
> What have I done incorrectly?
>

stat_name is the column.  "Suspect" is a row value of column stat_name.

DELETE FROM statustypes WHERE stat_name = 'Suspect';

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: Cannot drop column

[Viral Shah]

Since your column name has an Upper case character, you will have to use
double quotes to drop it. Try alter table statustypes drop column "Suspect";

Thanks,
Viral



On Wed, Dec 18, 2024 at 1:47 PM Rich Shepard 
wrote:

> I'm not seeing why postgres won't drop a table's column:
>
> bustrac=# select * from statustypes order by stat_name;
>   stat_name
> 
>   Client
>   Lead
>   No further contact
>   Opportunity
>   Proposal submitted
>   Prospect
>   Qualified
>   Referral
>   Suspect
> (9 rows)
>
> bustrac=# alter table statustypes drop column Suspect;
> ERROR:  column "suspect" of relation "statustypes" does not exist
> bustrac=#
>
> What have I done incorrectly?
>
> TIA,
>
> Rich
>
>
>

Re: Cannot drop column

[Adrian Klaver]

On 12/18/24 11:04 AM, Rich Shepard wrote:

On Wed, 18 Dec 2024, Viral Shah wrote:


Since your column name has an Upper case character, you will have to use
double quotes to drop it. Try alter table statustypes drop column 
"Suspect";


Viral,

Huh! I've not before encountered this in the 30+ years I've used postgres.


It has been that way for a long time:

https://www.postgresql.org/docs/7.0/syntax525.htm

Your issue though is as Ron pointed out:

alter table statustypes drop column Suspect

That is trying to drop a column named "Suspect" when in fact the column 
name is stat_name. 'Suspect' is a value in the column.




Thanks for the lesson.

Regards,

Rich




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to do an update with XML column ?

[Francisco Olarte]

Hi:

On Wed, 18 Dec 2024 at 11:00, celati Laurent  wrote:

> ERROR:  could not identify an equality operator for type xml
> LINE 39: group by orga_name
>   ^

Have you tried the obvious group by cast(orga_name as text) ? Never
used XML but most types in postgres convert to text injectively ( but
not bijectively ) ( I mean, same values, same text, but you can have
different text same values ( like the obvious text->float conversion
))


Francisco Olarte.

Re: Query about pg_wal directory filled up

[Laurenz Albe]

On Tue, 2024-12-17 at 19:11 +0530, Rama Krishnan wrote:
> One of their main concerns is whether the database will allow `INSERT` queries
> when the `pg_wal` directory becomes full.  
> 
> As I understand, once the `pg_wal` directory is completely filled, the 
> database
> will allow only read queries and will not permit any write operations.  

That is not the case.  If pg_wal fills up (more precisely: if creating a new
file in pg_wal fails), PostgreSQL will crash and go down.

Yours,
Laurenz Albe

Re: Documentation enancement regarding NULL and operators

[Laurenz Albe]

On Sun, 2024-12-15 at 17:35 +, Luca Dametto wrote:
> Most programming languages return "true" when two null values are compared,
> and false when, being the two values nullable, one of them is null and the
> other one isn't. 
>
> [not SQL, however]
> 
> Whilst I'd love to discuss the reasons of this, I understand that it would
> be a waste of time for everyone, as we cannot change the status-quo even
> if it made sense, as it would break many thousands of projects. 
> 
> For that reason, I'd just like to improve the documentation to add at least
> a note about "hey, this won't work as you might expect, because it works
> in a different way than 99% of programming languages out there.".

There are already efforts to improve the documentation concerning NULL:
https://commitfest.postgresql.org/51/5086/
If that doesn't cover your case, perhaps you can join the discussion
and suggest improvements.

Yours,
Laurenz Albe

Re: Documentation enancement regarding NULL and operators

[Adrian Klaver]

On 12/15/24 09:35, Luca Dametto wrote:

Hi All,
I'm coming from hours of debugging a SQL trigger that wasn't working 
properly. After a beautiful headache and infinite hours of documentation 
reading I've found out that something doesn't work as I would expect.


Most programming languages return "true" when two null values are 
compared, and false when, being the two values nullable, one of them is 
null and the other one isn't.
Any developer coming from Python, Javascript, PHP (and many more) would 
expected 'example'= null to return false, whilst SQL thanks to 3VL 
returns you a gentle ¯\_(ツ)_/¯ . Not a true, not a false, just nothing 
- in a boolean statement.


Python3:
 >>> "example" == None
False
 >>> None == None
True

NodeJS:
 > "example" == null
false
 > null == null
true

PHP 8:
 > var_dump("example" == null);
bool(false)
 > var_dump(null == null);
bool(true)

Whilst I'd love to discuss the reasons of this, I understand that it 
would be a waste of time for everyone, as we cannot change the 
status-quo even if it made sense, as it would break many thousands of 
projects.


For that reason, I'd just like to improve the documentation to add at 
least a note about "hey, this won't work as you might expect, because it 
works in a different way than 99% of programming languages out there.".
I've tried to understand how to submit my proposal for the documentation 
improvements, but it's way harder than what my brain can handle with the 
current headache caused by this stuff, I've attached a git patch to this 
email in case it's useful.


Content: "
PostgreSQL follows SQL's 3VL, due to that some comparisons regarding 
NULL values may not work as you might expect.
As an example, two nullable columns that contain NULL, when compared 
using the OPERATOR =, will return nothing instead of TRUE like your 
programming language may do. In this case, only 'IS NOT DISTINCT FROM' 
would return the result you expect.

"


See:

https://www.postgresql.org/docs/current/functions-comparison.html

" Ordinary comparison operators yield null (signifying “unknown”), not 
true or false, when either input is null. For example, 7 = NULL yields 
null, as does 7 <> NULL. When this behavior is not suitable, use the IS 
[ NOT ] DISTINCT FROM predicates:"





Kind regards,
Luca


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Intermittent errors when fetching cursor rows on PostgreSQL 16

[Ron Johnson]

On Wed, Dec 18, 2024 at 5:01 AM Enrico Schenone 
wrote:

> Good day.
> My name is Enrico Schenone, from Genoa, Italy.
> I'm a software achitect working at Cleis Tech - Genoa - Italy  -
> http://gruppocleis.it
> Me and my team are using PostgreSQL v12 to v16 on Debian 10-12 and Ubuntu
> Server 22.04 LTS with no-cluster configuration.
> Our applications are developed with 4Js Genero platform (classified as
> LCAP) - https://4js.com
>
> I whish to report an issue where I can't say if it happens at server or
> client side (or both as well).
> The problem occurs occasionally and only when fetching rows from a
> server-side cursor. The related query may be complex with joins or very
> easy (just one static table with 86 rows without WHERE conditions).
> I have set the "debug5" verbosity level of PostgreSQL log and I have
> extracted from millions of log line those who are belonging to separate
> failing sessions/connections.
> At the same time I have extracted the related application log.
> For each failure reported into the client-side application log, I have a
> distinct PostgreSQL detailed log.
>
> Then I have merged client-side end server-side logs along the timeline and
> I have observed what client and server does.
> For example (S: means PostgreSQL Server log, while C: means Client log):
>

Can you replicate the error in Prod using psql and cursors?

See
https://www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-USING
Section 41.7.3.5.

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

PG16 ADMIN OPTION

[Pawan Sharma]

Hello Everyone,

I recently noticed the difference between PG15 and PG16 regarding *CREATEROLE
*and ADMIN OPTION.

Granting the Admin Option to another role is not allowed in PG16, whereas
it was permitted in PG15. Please help me with how we can allow them without
superuser intervention.

PostgreSQL 15:
psql (15.10 (Homebrew), server 15.10 (Homebrew))
Type "help" for help.

postgres=# create role pgtest login createdb createrole;
CREATE ROLE
postgres=# \c postgres pgtest
psql (16.6 (Homebrew), server 15.10 (Homebrew))
You are now connected to database "postgres" as user "pgtest".
postgres=>
postgres=> create role test login;
CREATE ROLE
postgres=>
postgres=> grant pgtest to test  with ADMIN OPTION;
GRANT ROLE
postgres=> \drg
  List of role grants
 Role name | Member of |   Options   | Grantor
---+---+-+-
 test  | pgtest | ADMIN, INHERIT, SET | pgtest
(1 row)



PostgreSQL 16
psql (16.6 (Homebrew))
Type "help" for help.

postgres=# create role pgtest login createdb createrole;
CREATE ROLE
postgres=# \c postgres pgtest
You are now connected to database "postgres" as user "pgtest".
postgres=> create role test login;
CREATE ROLE
postgres=>
postgres=> grant pgtest to test  with ADMIN OPTION;
ERROR:  permission denied to grant role "admin"
DETAIL:  Only roles with the ADMIN option on role "pgtest" may grant this
role.
postgres=>
postgres=> \drg
  List of role grants
 Role name | Member of | Options |   Grantor
---+---+-+-
 pgtest | test  | ADMIN   | pawansharma
(1 row)

postgres=>

Thank you so much.

Regards,
Pawan

How to do an update with XML column ?

[celati Laurent]

Dear all,
I'm taking the liberty to ask an question. Do you know if there are some
ways in order to update and/or do group by with an XML column ?
For instance,

select id, unnest(xpath(
'//cit:CI_Organisation/cit:name/gco:CharacterString/text()',
   CAST(data AS XML),
ARRAY[
ARRAY['cit', 'http://standards.iso.org/iso/19115/-3/cit/2.0'],
ARRAY['gco', 'http://standards.iso.org/iso/19115/-3/gco/1.0'],
ARRAY['mdb','http://standards.iso.org/iso/19115/-3/mdb/2.0'],
ARRAY['cat','http://standards.iso.org/iso/19115/-3/cat/1.0'],
])) as orga_name, changedate, createdate, displayorder,
doctype, extra, popularity, rating, root, schemaid, title, istemplate,
isharvested, harvesturi,
   harvestuuid, groupowner, metadata.owner, metadata.source, uuid
from public.metadata
group by orga_name


I obtained :

ERROR:  could not identify an equality operator for type xml
LINE 39: group by orga_name
  ^

SQL state: 42883
Character: 2556


Thanks so much

Query about pg_wal directory filled up

[Rama Krishnan]

Hi Team,

One of our clients recently migrated from Oracle to PostgreSQL and is
conducting multiple scenarios to understand PostgreSQL behavior.

One of their main concerns is whether the database will allow `INSERT`
queries when the `pg_wal` directory becomes full.

As I understand, once the `pg_wal` directory is completely filled, the
database will allow only read queries and will not permit any write
operations.

Kindly advise


Thanks
RK

Intermittent errors when fetching cursor rows on PostgreSQL 16

[Enrico Schenone]

Good day.
My name is Enrico Schenone, from Genoa, Italy.
I'm a software achitect working at Cleis Tech - Genoa - Italy  - 
http://gruppocleis.it
Me and my team are using PostgreSQL v12 to v16 on Debian 10-12 and 
Ubuntu Server 22.04 LTS with no-cluster configuration.
Our applications are developed with 4Js Genero platform (classified as 
LCAP) - https://4js.com


I whish to report an issue where I can't say if it happens at server or 
client side (or both as well).
The problem occurs occasionally and only when fetching rows from a 
server-side cursor. The related query may be complex with joins or very 
easy (just one static table with 86 rows without WHERE conditions).
I have set the "debug5" verbosity level of PostgreSQL log and I have 
extracted from millions of log line those who are belonging to separate 
failing sessions/connections.

At the same time I have extracted the related application log.
For each failure reported into the client-side application log, I have a 
distinct PostgreSQL detailed log.


Then I have merged client-side end server-side logs along the timeline 
and I have observed what client and server does.

For example (S: means PostgreSQL Server log, while C: means Client log):

|S||:|
|||2024-12-16 17:27:14.*406* CET [2214722] cleistech@hh24odds_prod - 
192.168.16.17906*76054e0.21cb42* STATEMENT: *fetch forward 50 from cu6*


||C:|
|ERROR  ; 2024-12-16 17:27:14.*407*; PID: 104257;  User: genero; Ricerca 
quote evento 1433958 fallita. General SQL error, check 
SQLCA.SQLERRD[2]. - *SQLSTATE: XX001*|

|
S:|
|2024-12-16 17:27:14.*407* CET [2214722] cleistech@hh24odds_prod - 
192.168.16.17908006*676054e0.21cb42* LOG: ||*08006*||*: could not 
receive data from client*: Connection reset by peer |
|2024-12-16 17:27:14.*407* CET [2214722] cleistech@hh24odds_prod - 
192.168.16.17908006*676054e0.21cb42* LOCATION:  pq_recvbuf, pqcomm.c:953 |
|2024-12-16 17:27:14.*407* CET [2214722] cleistech@hh24odds_prod - 
192.168.16.17908003*676054e0.21cb42* DEBUG: *08003*: unexpected EOF on 
client connection


||C:|
|ERROR  ; 2024-12-16 17:27:14.*408*; PID: 104257;  User: genero; 
ver_quote: ERRORE in foreach ricerca bettype con spread. *SQLSTATE: 
XX000* - SQLCODE:   -6372 -  -1 - *no connection to the 
server* - abbandono validazione. |


Before failing on the reported cursor, the program succesfully creates 
and uses other cursors.
When the issue is detected at client-side, the program terminates 
without any abort and it is re-instantiated in seconds or minutes by a 
scheduler.
After a variable time (normally some minutes) and several failures it 
goes to normal end without errors.


What I reported in the body of this mail is only a subset of postgreSQL 
and application logs. I can send several log files each reporting a 
distinct and complete connection ID history.
I tried to reproduce the issue on a "in-vitro" environment, with 
single-to-multiple instances of the same program (up to 99 parallel 
instances) and I have executed more than half million of test processes 
without errors.

This problem commonly happens only in production environments.

Production environments can be:

 * Distinct application server and DB server on distinct subnets (no
   dropped packet detected on firewall, no memory/disk/network failure
   detected by "nmon" tool)
 * Distinct application server and DB server on same subnet (no firewall)
 * Same server for PostgreSQL and applications


The VM running the PostgreSQL that I have used for my test is an Ubuntu 
Server 22.04 LTS with 16 CPUs and 64 GB of RAM.

For client applications I use Ubuntu Server 22.04 LTS.
The postgresql.conf file is attached to this e-mail.

I'm able to detect that there is an error but I really becomes mad 
trying to find it. It seems a phantom that I know to exist but I can't 
bring up.
I kindly ask you to help me understand what and where is the problem, 
and how to solve it.


Hoping you can help me or address to someone who can do it.
Thanks in advance.
Enrico
--

*Enrico Schenone*
Software Architect

*Cleis Tech s.r.l.* - www.gruppocleis.it
Sede di Genova, Via Paolo Emilio Bensa, 2 - 16124 Genova, ITALY
Tel: +39-0104071400   Fax: +39-0104073276
Mobile: +39-320 7709352
E-mail: eschen...@cleistech.it




# -
# PostgreSQL configuration file
# -
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a S

Documentation enancement regarding NULL and operators

[Luca Dametto]

Hi All,
I'm coming from hours of debugging a SQL trigger that wasn't working 
properly. After a beautiful headache and infinite hours of documentation 
reading I've found out that something doesn't work as I would expect.


Most programming languages return "true" when two null values are 
compared, and false when, being the two values nullable, one of them is 
null and the other one isn't.
Any developer coming from Python, Javascript, PHP (and many more) would 
expected 'example'= null to return false, whilst SQL thanks to 3VL 
returns you a gentle ¯\_(ツ)_/¯ . Not a true, not a false, just nothing - 
in a boolean statement.


Python3:
>>> "example" == None
False
>>> None == None
True

NodeJS:
> "example" == null
false
> null == null
true

PHP 8:
> var_dump("example" == null);
bool(false)
> var_dump(null == null);
bool(true)

Whilst I'd love to discuss the reasons of this, I understand that it 
would be a waste of time for everyone, as we cannot change the 
status-quo even if it made sense, as it would break many thousands of 
projects.


For that reason, I'd just like to improve the documentation to add at 
least a note about "hey, this won't work as you might expect, because it 
works in a different way than 99% of programming languages out there.".
I've tried to understand how to submit my proposal for the documentation 
improvements, but it's way harder than what my brain can handle with the 
current headache caused by this stuff, I've attached a git patch to this 
email in case it's useful.


Content: "
PostgreSQL follows SQL's 3VL, due to that some comparisons regarding 
NULL values may not work as you might expect.
As an example, two nullable columns that contain NULL, when compared 
using the OPERATOR =, will return nothing instead of TRUE like your 
programming language may do. In this case, only 'IS NOT DISTINCT FROM' 
would return the result you expect.

"

Kind regards,
Luca

0001-Improved-documentation-for-Syntax-s.-Operators-Prece.patch
Description: Binary data

Wrapping a where clause to preserve rows with nulls

[Adrian Garcia Badaracco]

I have a query where I have to run a where clause generated by another
system (i.e., I can't modify that where clause. The where clause may return
`null`,  but I actually want to keep rows that return `null` (and rows that
return `true` but not rows that return `false`).

I thought it would be as simple as wrapping in `(...) is not false` but
that seems to prevent index usage.

For example, let's say that given the table:

CREATE TABLE test_index (value INTEGER);
CREATE INDEX idx_value ON test_index(value);

And the predicate `value = 5000`, if I run the original query that excludes
rows where the predicate is null it uses the index:

SELECT *
FROM test_index
WHERE value = 5000;

But as soon as I tack on an `IS NOT FALSE` the index is not used:

SELECT *
FROM test_index
WHERE (value = 5000) IS NOT FALSE;

This was surprising to me. I was hoping this might be able to use the index.

Is there any way to include the rows where the predicate evaluates to null
while still using an index?

Re: Wrapping a where clause to preserve rows with nulls

[Tom Lane]

"David G. Johnston"  writes:
> On Wednesday, December 18, 2024, Adrian Garcia Badaracco <
> adr...@adriangb.com> wrote:
>> Is there any way to include the rows where the predicate evaluates to null
>> while still using an index?

> ... A btree index, which handles =, can’t be told to behave
> differently and so cannot fulfill your desire to produce rows where the
> stored value is null; it can only produce those equal to 5000.

Not in a single scan, no.  But multiple scans are possible:

regression=# create table t (id int unique);
CREATE TABLE
regression=# explain select * from t where id = 5000 or id is null;
  QUERY PLAN  
--
 Bitmap Heap Scan on t  (cost=8.42..18.98 rows=14 width=4)
   Recheck Cond: ((id IS NULL) OR (id = 5000))
   ->  BitmapOr  (cost=8.42..8.42 rows=14 width=0)
 ->  Bitmap Index Scan on t_id_key  (cost=0.00..4.25 rows=13 width=0)
   Index Cond: (id IS NULL)
 ->  Bitmap Index Scan on t_id_key  (cost=0.00..4.16 rows=1 width=0)
   Index Cond: (id = 5000)
(7 rows)

The OP was quite unclear about what semantics he wants for
multiple-variable WHERE clauses, but maybe something like this
would work:

WHERE (original-clause) OR x IS NULL OR y IS NULL OR ...

where each variable mentioned in original-clause is allowed
to also be NULL.  Or perhaps what is wanted is

WHERE (original-clause) OR (x IS NULL AND y IS NULL AND ...)

??

regards, tom lane

Re: Wrapping a where clause to preserve rows with nulls

[Adrian Garcia Badaracco]

Thank you for the great idea Tom. While yes I can't modify the original
WHERE clause I do think I'll be able to introspect it or get the system
generating it to tell me which columns it references and then add an OR x
is NULL OR y is NULL ...

For context, just in case it's interesting, I store Parquet statistics in a
Postgres table and run the output of this thing on them:
https://github.com/apache/datafusion/blob/f92442ea8e8944c78f8e40d6648d049ff8e335ec/datafusion/physical-optimizer/src/pruning.rs#L146-L456
Hence why I can't really control the WHERE clause (at least not without
re-implementing a bunch of finicky error prone code).

On Wed, Dec 18, 2024 at 10:38 PM Tom Lane  wrote:

> "David G. Johnston"  writes:
> > On Wednesday, December 18, 2024, Adrian Garcia Badaracco <
> > adr...@adriangb.com> wrote:
> >> Is there any way to include the rows where the predicate evaluates to
> null
> >> while still using an index?
>
> > ... A btree index, which handles =, can’t be told to behave
> > differently and so cannot fulfill your desire to produce rows where the
> > stored value is null; it can only produce those equal to 5000.
>
> Not in a single scan, no.  But multiple scans are possible:
>
> regression=# create table t (id int unique);
> CREATE TABLE
> regression=# explain select * from t where id = 5000 or id is null;
>   QUERY PLAN
>
>
> --
>  Bitmap Heap Scan on t  (cost=8.42..18.98 rows=14 width=4)
>Recheck Cond: ((id IS NULL) OR (id = 5000))
>->  BitmapOr  (cost=8.42..8.42 rows=14 width=0)
>  ->  Bitmap Index Scan on t_id_key  (cost=0.00..4.25 rows=13
> width=0)
>Index Cond: (id IS NULL)
>  ->  Bitmap Index Scan on t_id_key  (cost=0.00..4.16 rows=1
> width=0)
>Index Cond: (id = 5000)
> (7 rows)
>
> The OP was quite unclear about what semantics he wants for
> multiple-variable WHERE clauses, but maybe something like this
> would work:
>
> WHERE (original-clause) OR x IS NULL OR y IS NULL OR ...
>
> where each variable mentioned in original-clause is allowed
> to also be NULL.  Or perhaps what is wanted is
>
> WHERE (original-clause) OR (x IS NULL AND y IS NULL AND ...)
>
> ??
>
> regards, tom lane
>

Re: Wrapping a where clause to preserve rows with nulls

[Adrian Garcia Badaracco]

Well, there is a wrinkle: if the predicate returns `false` but one of the
columns is null then the whole thing ends up `true` when I'd want it to be
`false`. Say col_a = [1] and col_b = [null]:

WHERE (col_a < 1 AND col_b > 1) OR col_a IS NULL OR col_b IS NULL -> WHERE
(false AND null) OR false OR true -> WHERE false OR false OR true -> true.

That's still a pretty good solution for now.

On Wed, Dec 18, 2024 at 10:41 PM Adrian Garcia Badaracco <
adr...@adriangb.com> wrote:

> Thank you for the great idea Tom. While yes I can't modify the original
> WHERE clause I do think I'll be able to introspect it or get the system
> generating it to tell me which columns it references and then add an OR x
> is NULL OR y is NULL ...
>
> For context, just in case it's interesting, I store Parquet statistics in
> a Postgres table and run the output of this thing on them:
> https://github.com/apache/datafusion/blob/f92442ea8e8944c78f8e40d6648d049ff8e335ec/datafusion/physical-optimizer/src/pruning.rs#L146-L456
> Hence why I can't really control the WHERE clause (at least not without
> re-implementing a bunch of finicky error prone code).
>
> On Wed, Dec 18, 2024 at 10:38 PM Tom Lane  wrote:
>
>> "David G. Johnston"  writes:
>> > On Wednesday, December 18, 2024, Adrian Garcia Badaracco <
>> > adr...@adriangb.com> wrote:
>> >> Is there any way to include the rows where the predicate evaluates to
>> null
>> >> while still using an index?
>>
>> > ... A btree index, which handles =, can’t be told to behave
>> > differently and so cannot fulfill your desire to produce rows where the
>> > stored value is null; it can only produce those equal to 5000.
>>
>> Not in a single scan, no.  But multiple scans are possible:
>>
>> regression=# create table t (id int unique);
>> CREATE TABLE
>> regression=# explain select * from t where id = 5000 or id is null;
>>   QUERY PLAN
>>
>>
>> --
>>  Bitmap Heap Scan on t  (cost=8.42..18.98 rows=14 width=4)
>>Recheck Cond: ((id IS NULL) OR (id = 5000))
>>->  BitmapOr  (cost=8.42..8.42 rows=14 width=0)
>>  ->  Bitmap Index Scan on t_id_key  (cost=0.00..4.25 rows=13
>> width=0)
>>Index Cond: (id IS NULL)
>>  ->  Bitmap Index Scan on t_id_key  (cost=0.00..4.16 rows=1
>> width=0)
>>Index Cond: (id = 5000)
>> (7 rows)
>>
>> The OP was quite unclear about what semantics he wants for
>> multiple-variable WHERE clauses, but maybe something like this
>> would work:
>>
>> WHERE (original-clause) OR x IS NULL OR y IS NULL OR ...
>>
>> where each variable mentioned in original-clause is allowed
>> to also be NULL.  Or perhaps what is wanted is
>>
>> WHERE (original-clause) OR (x IS NULL AND y IS NULL AND ...)
>>
>> ??
>>
>> regards, tom lane
>>
>

Re: Intermittent errors when fetching cursor rows on PostgreSQL 16

[Adrian Klaver]

On 12/17/24 08:30, Enrico Schenone wrote:

Good day.
My name is Enrico Schenone, from Genoa, Italy.
I'm a software achitect working at Cleis Tech - Genoa - Italy  - 
http://gruppocleis.it
Me and my team are using PostgreSQL v12 to v16 on Debian 10-12 and 
Ubuntu Server 22.04 LTS with no-cluster configuration.
Our applications are developed with 4Js Genero platform (classified as 
LCAP) - https://4js.com


I whish to report an issue where I can't say if it happens at server or 
client side (or both as well).


This:

"unexpected EOF on client connection "

makes me believe this is on client side.

To be clear the client is running on Ubuntu Server 22.04, correct?

Have you looked at the OS system log for relevant entries at the time 
the error occurs?


If so what are they?

This only happens in production environment, is there anything in it 
that is materially different from where you ran the test below?




Hoping you can help me or address to someone who can do it.
Thanks in advance.
Enrico
--

*Enrico Schenone*
Software Architect



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Query about pg_wal directory filled up

[Saul Perdomo]

On Wed, Dec 18, 2024, 5:01 a.m. Rama Krishnan  wrote:

>
> Hi Team,
>
> One of our clients recently migrated from Oracle to PostgreSQL and is
> conducting multiple scenarios to understand PostgreSQL behavior.
>
> One of their main concerns is whether the database will allow `INSERT`
> queries when the `pg_wal` directory becomes full.
>
>
The way out of this "conundrum" is to make sure to implement a solid backup
process.

I'm a huge fan of PGBackRest:

https://pgbackrest.org/



> Kindly advise
>
>
> Thanks
> RK
>

Re: How to deal with dangling files after aborted `pg_restore`?

[Saul Perdomo]

Genuine question: Why are you looking to recover from this half-cooked
state instead of restarting the restore process from the beginning?

On Tue, Dec 17, 2024, 1:10 a.m. Ivan Kurnosov  wrote:

> The scenario:
>
> 1. There is a postgresql 17 server running
> 2. Restore dump with `--single-transaction` flag
> 3. For whatever reason the server goes away (eg: we kill the process)
> 4. Now `base` directory is filled with abandoned table files which
> postgresql know nothing about
>
> Playground:
>
> Terminal 1:
> Start server: `docker run --rm -it -e POSTGRES_HOST_AUTH_METHOD=trust -v
> $PWD/postgres:/var/lib/postgresql/data postgres:17.2`
>
> Terminal 2:
> 1. Start container with recent pg_restore: `docker run --rm -it -v
> $PWD:/app -w /app postgres:17.2 bash`
> 2. Start dump recovery: `pg_restore -h 172.17.0.2 -U postgres -d postgres
> --single-transaction -v -Fc --no-owner dump.sql`
>
> Terminal 3:
> 1. Find what container is the server: `docker ps`
> 2. Kill it: `docker kill d7ecf6e66c1d`
>
> Terminal 1:
> Start the server again, with the same command
>
> Terminal 3:
> Check there are abandoned large files:
> ```
> # ls -la /home/ivan/postgres/base/5
> 
> -rw--- 1 systemd-coredump systemd-coredump 342884352 Dec 17 18:58 16399
> -rw--- 1 systemd-coredump systemd-coredump  11149312 Dec 17 18:58 16404
> -rw--- 1 systemd-coredump systemd-coredump188416 Dec 17 18:58
> 16403_fsm
> -rw--- 1 systemd-coredump systemd-coredump 686145536 Dec 17 18:58 16403
> ```
>
> Terminal 2:
> 1. Confirm those OIDs are not accounted:
> ```
> psql -h 172.17.0.2 -U postgres -c 'select * from pg_class'|grep 16404
> psql -h 172.17.0.2 -U postgres -c 'select * from pg_class'|grep 16403
> psql -h 172.17.0.2 -U postgres -c 'select * from pg_class'|grep 16399
> ```
>
> Hence a question: am I doing something wrong? Is it expected behaviour? If
> so - how would one recover from this scenario now WITHOUT dropping entire
> database?
>
> --
> With best regards, Ivan Kurnosov
>

Re: Wrapping a where clause to preserve rows with nulls

[Adrian Garcia Badaracco]

I'll note that the clause is arbitrary in the sense that I don't generate
it and cannot edit it but it's basically a bunch of boolean comparisons
chained i.e. `col_a >= 1 and col_b <=5 and col_c ...` so I can in general
add an index on say col_a and it does get used.

On Wed, Dec 18, 2024 at 9:47 PM Adrian Garcia Badaracco 
wrote:

> I have a query where I have to run a where clause generated by another
> system (i.e., I can't modify that where clause. The where clause may return
> `null`,  but I actually want to keep rows that return `null` (and rows that
> return `true` but not rows that return `false`).
>
> I thought it would be as simple as wrapping in `(...) is not false` but
> that seems to prevent index usage.
>
> For example, let's say that given the table:
>
> CREATE TABLE test_index (value INTEGER);
> CREATE INDEX idx_value ON test_index(value);
>
> And the predicate `value = 5000`, if I run the original query that
> excludes rows where the predicate is null it uses the index:
>
> SELECT *
> FROM test_index
> WHERE value = 5000;
>
> But as soon as I tack on an `IS NOT FALSE` the index is not used:
>
> SELECT *
> FROM test_index
> WHERE (value = 5000) IS NOT FALSE;
>
> This was surprising to me. I was hoping this might be able to use the
> index.
>
> Is there any way to include the rows where the predicate evaluates to null
> while still using an index?
>
>

Re: Wrapping a where clause to preserve rows with nulls

[David G. Johnston]

On Wednesday, December 18, 2024, Adrian Garcia Badaracco <
adr...@adriangb.com> wrote:

>
> Is there any way to include the rows where the predicate evaluates to null
> while still using an index?
>

That seems quite unlikely.  Your definition of equality is incompatible
with the system’s standard definition while requiring that the standard
equals operator be used in the query.  Null values and non-null values are
not considered equal, or, put another way, always considered distinct from
each other.  A btree index, which handles =, can’t be told to behave
differently and so cannot fulfill your desire to produce rows where the
stored value is null; it can only produce those equal to 5000.

David J.

Re: How to deal with dangling files after aborted `pg_restore`?

[Ron Johnson]

It could be that he only restored *some* tables in an existing database.

On Wed, Dec 18, 2024 at 7:02 PM Saul Perdomo  wrote:

> Genuine question: Why are you looking to recover from this half-cooked
> state instead of restarting the restore process from the beginning?
>
> On Tue, Dec 17, 2024, 1:10 a.m. Ivan Kurnosov  wrote:
>
>> The scenario:
>>
>> 1. There is a postgresql 17 server running
>> 2. Restore dump with `--single-transaction` flag
>> 3. For whatever reason the server goes away (eg: we kill the process)
>> 4. Now `base` directory is filled with abandoned table files which
>> postgresql know nothing about
>>
>> Playground:
>>
>> Terminal 1:
>> Start server: `docker run --rm -it -e POSTGRES_HOST_AUTH_METHOD=trust -v
>> $PWD/postgres:/var/lib/postgresql/data postgres:17.2`
>>
>> Terminal 2:
>> 1. Start container with recent pg_restore: `docker run --rm -it -v
>> $PWD:/app -w /app postgres:17.2 bash`
>> 2. Start dump recovery: `pg_restore -h 172.17.0.2 -U postgres -d postgres
>> --single-transaction -v -Fc --no-owner dump.sql`
>>
>> Terminal 3:
>> 1. Find what container is the server: `docker ps`
>> 2. Kill it: `docker kill d7ecf6e66c1d`
>>
>> Terminal 1:
>> Start the server again, with the same command
>>
>> Terminal 3:
>> Check there are abandoned large files:
>> ```
>> # ls -la /home/ivan/postgres/base/5
>> 
>> -rw--- 1 systemd-coredump systemd-coredump 342884352 Dec 17 18:58
>> 16399
>> -rw--- 1 systemd-coredump systemd-coredump  11149312 Dec 17 18:58
>> 16404
>> -rw--- 1 systemd-coredump systemd-coredump188416 Dec 17 18:58
>> 16403_fsm
>> -rw--- 1 systemd-coredump systemd-coredump 686145536 Dec 17 18:58
>> 16403
>> ```
>>
>> Terminal 2:
>> 1. Confirm those OIDs are not accounted:
>> ```
>> psql -h 172.17.0.2 -U postgres -c 'select * from pg_class'|grep 16404
>> psql -h 172.17.0.2 -U postgres -c 'select * from pg_class'|grep 16403
>> psql -h 172.17.0.2 -U postgres -c 'select * from pg_class'|grep 16399
>> ```
>>
>> Hence a question: am I doing something wrong? Is it expected behaviour?
>> If so - how would one recover from this scenario now WITHOUT dropping
>> entire database?
>>
>> --
>> With best regards, Ivan Kurnosov
>>
>

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!