Re: Oracle_fdw

[Laurenz Albe]

On Tue, 2025-04-22 at 07:28 -0400, Yongye Serkfem wrote:
> We are using Postgresql-15, ahd having issues importing foreign schema with 
> all
> its objects from an Oracle databse. Here is the error I am getting,
> "error connecting to Oracle: OCIEnvCreate failed to create environment 
> handle: Detail: SQL state: HV00N
> I have installed oracle instantclient 19_24
> I would appreciate any help getting this resolved. 

This error usually indicated that you configured the Oracle Client wrongly.
Usually, that is a question of setting environment variables wrong, but
unfortunately it is a very generic error that Oracle throws when "something
is wrong".

Your best bet is to research all the oracle_fdw issues that reported this
error.  Hopefully, one of these cases matches yours:
https://github.com/laurenz/oracle_fdw/issues?q=is%3Aissue%20OCIEnvCreate

Yours,
Laurenz Albe

Oracle_fdw

[Yongye Serkfem]

Hello Team!
We are using Postgresql-15, ahd having issues importing foreign schema with
all its objects from an Oracle databse. Here is the error I am getting,
"error connecting to Oracle: OCIEnvCreate failed to create environment
handle: Detail: SQL state: HV00N
I have installed oracle instantclient 19_24
I would appreciate any help getting this resolved.

Regards
Yong Serk

SQL transactions executing from VMSS

[chandan Kumar]

Hi Team,
Greetings!

Kindly help in below situation, where in I see slowness in response time
while I do testing running SQL transaction in batches from VMSS to postgres
database in flexible server(PaaS) 14.17 version. VMSS to IaaS(postgres db
on ubuntu) is faster than VMSs to PaaS. 14 version is same on both the
system. response size is same but response time is almost 2.5x more on
PaaS. I see dynatrace is showing more time is on waiting time. Query plan
is same on both the system. If i run single sql transaction from pg admin
it is executing in same time.
 what can cause this slowness, any idea please.
-- 
*With warm regards*
* Chandan*

Re: Cannot turn track_counts on

[Anton Shepelev]

Adrian Klaver:

> Anton Shepelev:
>
> > Have you any further ideas how to turn track_counts on?
>
> It is matter of finding out what is setting?:
>
> source  | override

Indeed, I having begun with this crucial question.

> There is something different about your setup, as here on
> Ubuntu(which uses the Debian packaging) I see:
> [...]

Yes.  It is on on your side, and pgsql shows NULL values as
NULL.  Can the latter be due to a differnce in Postgres
versions, for mine is 11.21 (as I have reported before)?

We have several other servers with identical versions of the
OS and PostgreSQL:

  user@DC:/opt/sva$ cat /etc/issue
  Astra Linux 1.7.5
  user@xx:/opt/sva$ apt show postgresql
  Package: postgresql
  Version: 11+225astra3
  Priority: optional
  Section: database
  Source: postgresql-common (225astra3)
  Maintainer: Debian PostgreSQL Maintainers 

  user@xx:/opt/sva$ psql -V
  psql (PostgreSQL) 11.21 (Debian 1:11.21-astra.se6+ci1)

but `track_counts' is stuck off only on one.

A complete reinstall with purging of all configuration data
comes to mind, but it is a last-resort measure, as the
system is a production one, and actively used.

-- 
()  ascii ribbon campaign -- against html e-mail
/\  www.asciiribbon.org   -- against proprietary attachments

Feature Proposal: Column-Level DELETE Operation in SQL

[Abhishek Hatgine]

*Dear SQL Development Team,*

I hope this message finds you well.

I'd like to propose a new feature for consideration in future versions of
SQL — the ability to perform a column-level DELETE operation, allowing
removal of specific column values without affecting the entire row.

*Proposal Summary*

Currently, SQL provides two core commands:

   -

   DELETE – to remove entire rows.
   -

   UPDATE – to change or nullify column values.

However, there’s no specific, expressive way to *delete the value of a
column* directly. The typical workaround is to use:

UPDATE Customers SET Address = NULL WHERE CustomerID = 103;

While this works fine, it doesn't semantically express that the developer
intends to *remove* the value — not just update it.

*Proposed Syntax Examples*

Here are some ideas for possible new syntax:


DELETE Address FROM Customers WHERE CustomerID = 103;-- or
REMOVE COLUMN Address FROM Customers WHERE CustomerID = 103;

And even:

DELETE Address, PostalCode FROM Customers WHERE Country = 'India';

These would act as a shortcut or expressive alias for setting one or more
column values to NULL.

*Why This Matters*

   -

   *Improved readability* and code clarity.
   -

   More intuitive for developers coming from languages or NoSQL systems
   where fields can be "deleted" from an object/document.
   -

   Emphasizes intent: *deleting* a value is conceptually different from
   *updating* it to NULL.
   -

   Opens doors for potential enhancements in tooling and IDE support.

I understand this would require careful consideration within the SQL
standards, but I believe it could make SQL more expressive and
beginner-friendly while preserving its power.

Thank you for your time and for all the work you do to maintain and improve
SQL systems.

Warm regards,
*Abhishek Hatgine*
SQL Learner
Your Email – hatgineabhishe...@gmail.com
Location – Pune

Re: Feature Proposal: Column-Level DELETE Operation in SQL

[Amul Sul]

On Tue, Apr 22, 2025 at 5:56 PM Karsten Hilbert  wrote:
>
> Am Mon, Apr 21, 2025 at 10:23:30PM +0530 schrieb Abhishek Hatgine:
>
> >*deleting* a value is conceptually different from *updating* it to NULL.
>
> In what way ?
>
> In other words, you'll have to much better define what you
> mean be "deleting a value".
>
> DROP COLUMN already exists, for that matter.
>
> Updating a column to its DEFAULT value may also be closer to
> what you envision a "column-delete" to do.
>

Agreed, this is a much better approach to achieve the same.

I am a bit skeptical about whether the proposed syntax will be
acceptable if it's not SQL standard, unless it's absolutely necessary
to support functionality that can't be achieved with the existing
syntax which is not the case here.

Regards,
Amul

Re: Feature Proposal: Column-Level DELETE Operation in SQL

[thombrown1979]

On Tue, 22 Apr 2025, 13:09 Abhishek Hatgine,
 wrote:
Dear SQL Development Team,
>
> I hope this message finds you well.
>
> I'd like to propose a new feature for consideration in future versions of SQL 
> — the ability to perform a column-level DELETE operation, allowing removal of 
> specific column values without affecting the entire row.
>
> Proposal Summary
>
> Currently, SQL provides two core commands:
>
> DELETE – to remove entire rows.
>
> UPDATE – to change or nullify column values.
>
> However, there’s no specific, expressive way to delete the value of a column 
> directly. The typical workaround is to use:
>
> UPDATE Customers SET Address = NULL WHERE CustomerID = 103;
>
> While this works fine, it doesn't semantically express that the developer 
> intends to remove the value — not just update it.
>
> Proposed Syntax Examples
>
> Here are some ideas for possible new syntax:
>
>
> DELETE Address FROM Customers WHERE CustomerID = 103;
> -- or
> REMOVE COLUMN Address FROM Customers WHERE CustomerID = 103;
>
> And even:
>
> DELETE Address, PostalCode FROM Customers WHERE Country = 'India';
>
> These would act as a shortcut or expressive alias for setting one or more 
> column values to NULL.
>
> Why This Matters
>
> Improved readability and code clarity.
>
> More intuitive for developers coming from languages or NoSQL systems where 
> fields can be "deleted" from an object/document.
>
> Emphasizes intent: deleting a value is conceptually different from updating 
> it to NULL.
>
> Opens doors for potential enhancements in tooling and IDE support.
>
> I understand this would require careful consideration within the SQL 
> standards, but I believe it could make SQL more expressive and 
> beginner-friendly while preserving its power.
>
> Thank you for your time and for all the work you do to maintain and improve 
> SQL systems

Hi,

I don't agree that this is intuitive. When you DELETE a row, the row
is gone. The equivalent for columns would be to DROP a column, which
is not the functionality you are asking for. Setting something to NULL
is a perfectly fine operation if you need to unset the value of that
column.

I think what you are proposing would just be syntactic sugar, but is
likely to cause confusion about what exactly it does, and no less
verbose than an explicit UPDATE.

Bear in mind, you also have to contend with default values, NOT NULL
constraints, and foreign key constraints, which further complicate
matters.

Regards

Thom

Re: Feature Proposal: Column-Level DELETE Operation in SQL

[David G. Johnston]

On Monday, April 21, 2025, Abhishek Hatgine 
wrote:.
>
>
>-
>
>More intuitive for developers coming from languages or NoSQL systems
>where fields can be "deleted" from an object/document.
>
> Why should this matter to us?  We don’t have this paradigm, you can’t
remove columns from existence on a per-row basis.

David J.

Another documentation issue

[Igor Korot]

Hi, ALL,

On the page 
https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-SEQ-PAGE-COST

it is only given the default value of this parameter.

No min/max values are provided..

The same can be sad about
https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-RANDOM-PAGE-COST

However, this page
https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-EFFECTIVE-IO-CONCURRENCY
describes both default and mn/max, however t s says:

[quote]
The default is 1 on supported systems, otherwise 0
[/quote]]

No explanation of what is "supported system" is given...

And the same can be said about
https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-IO-CONCURRENCY.

Thank you

Re: Pgbackrest fails due after an ISP change

[KK CHN]

Hi List,

Thanks for the clarification.

 If I want to start a fresh backup to the same repo server ( the one which
fails the pgbackrest backup due to the file system full outage) what all
are  the steps I have to perform on the Repo Server and the DB server ?

AFAIK,These all are the steps  required , correct me if I'm wrong.

1.  Stop the   stanza on both repo server as well as on DB server  (
$sudo -u  myuser  pgbackrest --stanza=My_RepoX  --force stop)
2.  Then delete the stanza from either Repo Server or DB server (  by the
delete stanza option ) Is the deletion of the stanza needed to be
performed only at the Repo server is enough ?
3.  After deletion of the stanza,   delete the folders in the repo server
 archive, backup respectively by ( rm -f   archive, rm -rf backup
 folders  for My_RepoX ?)
4. Then execute the same  stanza create command  from the reposerver  ?  (
$ sudo -u myuser pgbackrest  --stanza=My_RepoX  --log-level-console=info
stanza-create)?
5.  Then Check the stanza creation and config all perfect by  (  sudo -u
myuser pgbackrest --stanza=My_RepoX  --log-level-console=info check )
6.   If the step 5 success,   schedule the full backup from the cron


I hope  this will help me to take backup afresh in case an existing
pgbackrest backup  fails ( whatever the stupid reason what happened as in
my case ) .

( In my case pgbackrest fails due to the scenario I faced, I mean due to
the file system full  and didn't notice it for 24 hours, even after made
space of the partition /root which got full,  later on the pgbackrest
backups from cron scheduler unable to complete the backup)


   If anything is wrong or not sufficient as in the 6 steps AFAIK (As this
a live Repo Server for other DB Clusters too, more than 10 nos, nothing
should go wrong) ,   please correct/guide me

Thank you,
Krishane



On Mon, Apr 21, 2025 at 9:49 PM Greg Sabino Mullane 
wrote:

>
>
> On Mon, Apr 21, 2025 at 9:03 AM KK CHN  wrote:
>
>>
>> ERROR: [082]: WAL segment 000102200038 was not archived
>> before the 6ms timeout
>>
> ...
>
>> How can I make the full backup command not to check the  WAL was archived
>> or not  to the repo server for atleast once ?
>>
>
> You cannot. WAL is an integral part of the backups. You should run the
> pgbackrest "check" command and look at your Postgres logs to figure out why
> WAL files are not being archived. Once that is solved, try the backup again.
>
> https://pgbackrest.org/command.html#command-check
>
>
> Cheers,
> Greg
>
> --
> Crunchy Data - https://www.crunchydata.com
> Enterprise Postgres Software Products & Tech Support
>
>

Re: Feature Proposal: Column-Level DELETE Operation in SQL

[Karsten Hilbert]

Am Mon, Apr 21, 2025 at 10:23:30PM +0530 schrieb Abhishek Hatgine:

>*deleting* a value is conceptually different from *updating* it to NULL.

In what way ?

In other words, you'll have to much better define what you
mean be "deleting a value".

DROP COLUMN already exists, for that matter.

Updating a column to its DEFAULT value may also be closer to
what you envision a "column-delete" to do.

Karsten
-- 
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: Feature Proposal: Column-Level DELETE Operation in SQL

[Laurenz Albe]

On Mon, 2025-04-21 at 22:23 +0530, Abhishek Hatgine wrote:
> I'd like to propose a new feature for consideration in future versions of SQL 
> — the ability
> to perform a column-level DELETE operation, allowing removal of specific 
> column values
> without affecting the entire row.

In PostgreSQL, it affects the entire row anyway...

> Proposal Summary
> Currently, SQL provides two core commands:
>  * 
>    DELETE – to remove entire rows.
>  * 
>    UPDATE – to change or nullify column values.
>
> However, there’s no specific, expressive way to delete the value of a column 
> directly.
> The typical workaround is to use:
>
> UPDATE Customers SET Address = NULL WHERE CustomerID = 103;
>
> While this works fine, it doesn't semantically express that the developer 
> intends to
> remove the value — not just update it.
>
> Proposed Syntax Examples
> Here are some ideas for possible new syntax:
> 
> DELETE Address FROM Customers WHERE CustomerID = 103;
> -- or
> REMOVE COLUMN Address FROM Customers WHERE CustomerID = 103;
> And even:
> DELETE Address, PostalCode FROM Customers WHERE Country = 'India';
>
> These would act as a shortcut or expressive alias for setting one or more 
> column values
> to NULL.
>
> Why This Matters
>  * Improved readability and code clarity.
>  * More intuitive for developers coming from languages or NoSQL systems where 
> fields can be
>"deleted" from an object/document.
>  * Emphasizes intent: deleting a value is conceptually different from 
> updating it to NULL.
>  * Opens doors for potential enhancements in tooling and IDE support.
> I understand this would require careful consideration within the SQL 
> standards, but I
> believe it could make SQL more expressive and beginner-friendly while 
> preserving its power.

My immediate gut reaction is "no, thank you".
For anybody who knows SQL, the code would become *less* readable.

But I'd say that PostgreSQL is the wrong place to propose this change.  We are 
unlikely
to implement SQL syntax that deviates from the standard like that.
You should try to convince the SQL standard committee to accept that new 
syntax, then
we'd feel more motivated to implement it.

Apart from my strong gut reaction, I have some techical problems with your 
proposed
syntax:

- If you REMOVE or DELETE a column in a row, will it always become NULL or 
should it
  become the DEFAULT value?  With an UPDATE, that is clear: either you say
  "SET col = NULL" or "SET col = DEFAULT".  So the UPDATE syntax is actually 
clearer.

- Also, the proposed syntax could easily be confused with "ALTER tab DROP col", 
which
  actually removes the column from a table.  SQL users would be confused by 
your syntax,
  because they would expect that if you REVOVE or DELETE a column, it would no 
longer
  be there.

Yours,
Laurenz Albe

RE: Feature Proposal: Column-Level DELETE Operation in SQL

[Deas, Scott]

I would also suggest that this would complicate grants.  It makes sense that we 
have users who can UPDATE rows, but not remove rows completely, but if this new 
syntax utilizes the DELETE keyword, how would that be implemented from a 
permissions perspective?  We use CI/CD automation for DDL and some DML, and we 
have checks in place that restrict “data removal”.  Utilizing DELETE to UPDATE 
would complicate these guardrails and any others that folks have implemented.

If this is truly functionality you need, maybe wrap the “UPDATE” in a function 
call.

Thanks,
Scott

From: David G. Johnston 
Sent: Tuesday, April 22, 2025 9:52 AM
To: Abhishek Hatgine 
Cc: pgsql-gene...@postgresql.org
Subject: Re: Feature Proposal: Column-Level DELETE Operation in SQL

***This email is from an external source. Only open links and attachments from 
a Trusted Sender.***
On Monday, April 21, 2025, Abhishek Hatgine 
mailto:hatgineabhishe...@gmail.com>> wrote:.

  *   More intuitive for developers coming from languages or NoSQL systems 
where fields can be "deleted" from an object/document.
Why should this matter to us?  We don’t have this paradigm, you can’t remove 
columns from existence on a per-row basis.

David J.


Notice of Confidentiality: **This E-mail and any of its attachments may contain 
Lincoln National Corporation proprietary information, which is privileged, 
confidential, or subject to copyright belonging to the Lincoln National 
Corporation family of companies. This E-mail is intended solely for the use of 
the individual or entity to which it is addressed. If you are not the intended 
recipient of this E-mail, you are hereby notified that any dissemination, 
distribution, copying, or action taken in relation to the contents of and 
attachments to this E-mail is strictly prohibited and may be unlawful. If you 
have received this E-mail in error, please notify the sender immediately and 
permanently delete the original and any copy of this E-mail and any printout. 
This email and its attachments may collect your personal information to improve 
Lincoln’s products or to provide you with services related to its products. For 
more information, please see our privacy 
policy. Thank You.**

Re: Cannot turn track_counts on

[Adrian Klaver]

On 4/22/25 01:31, Anton Shepelev wrote:

Adrian Klaver:





There is something different about your setup, as here on
Ubuntu(which uses the Debian packaging) I see:
[...]


Yes.  It is on on your side, and pgsql shows NULL values as
NULL.  Can the latter be due to a differnce in Postgres
versions, for mine is 11.21 (as I have reported before)?


No that is because I have my ~/.psqlrc set up with:

\pset null 'NULL'

From here:

https://www.postgresql.org/docs/current/app-psql.html

See the:

Meta-Commands
\pset

section

as well as

Files

psqlrc and ~/.psqlrc



   user@xx:/opt/sva$ psql -V
   psql (PostgreSQL) 11.21 (Debian 1:11.21-astra.se6+ci1)

but `track_counts' is stuck off only on one.

A complete reinstall with purging of all configuration data
comes to mind, but it is a last-resort measure, as the
system is a production one, and actively used.



Given that the Debian Postgres packaging allows you to install multiple 
clusters of a given version I would create a new 11.21 cluster as a test 
and see what track_counts is set to. That would help determine whether 
the setting is coming from the initial install. You could then add the 
other components to the cluster one by one and see if and where the 
track_counts setting changes.



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

Re: Oracle_fdw

[Yongye Serkfem]

Thanks, I'll look into it.

On Tue, Apr 22, 2025, 7:54 AM Laurenz Albe  wrote:

> On Tue, 2025-04-22 at 07:28 -0400, Yongye Serkfem wrote:
> > We are using Postgresql-15, ahd having issues importing foreign schema
> with all
> > its objects from an Oracle databse. Here is the error I am getting,
> > "error connecting to Oracle: OCIEnvCreate failed to create environment
> handle: Detail: SQL state: HV00N
> > I have installed oracle instantclient 19_24
> > I would appreciate any help getting this resolved.
>
> This error usually indicated that you configured the Oracle Client wrongly.
> Usually, that is a question of setting environment variables wrong, but
> unfortunately it is a very generic error that Oracle throws when "something
> is wrong".
>
> Your best bet is to research all the oracle_fdw issues that reported this
> error.  Hopefully, one of these cases matches yours:
> https://github.com/laurenz/oracle_fdw/issues?q=is%3Aissue%20OCIEnvCreate
>
> Yours,
> Laurenz Albe
>

Bash automation for PostgreSQL

[Travis Bean]

Hello,

I developed a Bash script to automate the installation and
configuration of open-source software (i.e., launchpad.net/linuxha). I
want to make sure the syntax of this script is perfect so I can use it
as a teaching tool to educate people about Linux.

I need to know if there is anything misconfigured with my PostgreSQL syntax.

If you find a bug in LinuxHA, please submit a bug report to
bugs.launchpad.net/linuxha/+filebug.

Kind regards,

Travis Bean