Re: Effects of dropping a large table

[Laurenz Albe]

On Tue, 2023-07-18 at 13:58 -0400, Devin Ivy wrote:
> I'm hoping to ensure I understand the implications of dropping a large table 
> and
> the space being reclaimed by the database and/or OS.  We're using pg v14.
> 
> This table is quite large with a primary key and one additional index—all
> together these are on the order of 1TB.  The primary key and index take-up
> quite a bit more space than the table itself.  Our hope is to discontinue
> use of this table and then eventually drop it.  However, the database is
> under constant load and we'd like to avoid (or at least anticipate) downtime
> or degraded performance.  The database also replicates to a standby instance.
> 
> So in short, what can we expect if we drop this table?  Will the strategy
> that pg takes to give that space back to the rest of the database and/or
> OS have significant effects on availability or performance?  Finally, are
> there any other considerations that we should take into account?  I appreciate
> your time and input, thanks!

If you drop a table, the underlying files are deleted, and the disk space
becomes available.  Usually that is a cheap operation, but that of course
depends on the file system you are using.  On a copy-on-write file system,
the space won't be freed right away.

The only difficulty that can arise is if you have long running transactions
that involve the table.  To prevent a hanging DROP TABLE from blocking other
transactions for a long time, you can

  SET lock_timeout = '1s';
  DROP TABLE ...;

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

?????? fsync data directory after DB crash

[Pandora]

Yes, I saw the usage of syncfs in PG14, but it is recommended to use it on 
Linux 5.8 or higher. If my OS version is lower than 5.8, can I still enable it?


 


Pandora
yeyu...@qq.com



 




--  --
??: "Thomas Munro"https://www.postgresql.org/message-id/flat/CA%2BhUKGKgj%2BSN6z91nVmOmTv2KYrG7VnAGdTkWdSjbOPghdtooQ%40mail.gmail.com#576caccf21cb6c3e883601fceb28d36b

Only recovery_init_sync_method=syncfs actually went in from that
thread.  It works better for some setups (systems where opening
squillions of files just do perform a no-op fsync() is painfully
expensive).

Need help setting up Windows authentication SSPI user mapping

[Haidong Huang]

Hi,

I would like to get helo with setting Windows authentication in a testing 
scenario.

I use Windows 11. Windows connects to AAD and thus I use a windows account. The 
domain name is na, user name is hai. I also setup a local user account 
hailocal. My machine name is haisurface

I have PostgreSQL 15 installed locally, and I want to connect to the local 
PostgreSQL using Windows authentication that is mapped to the postgres user 
name.

pg_hba.conf:
hostall hailocal   127.0.0.1/32sspi  
include_realm=0


pg_ident.conf:
win_map hailocal@haisurface postgres


The Greenplum OLE DB Provider is unable to connect if I choose to use Windows 
authentication. The error message is the generic error that is no meaning. Do I 
have the wrong configuration? if yes, what configuration can I have?

[cid:00560d56-0563-4af8-8de1-ab6eb5946faf]

Thanks in advance

Re: Fwd: Regarding Installation of PostgreSQL

[Adrian Klaver]

On 7/18/23 20:14, Sahil Sojitra wrote:


-- Forwarded message -
From: *Sahil Sojitra* >

Date: Tue, 18 Jul, 2023, 8:43 am
Subject: Regarding Installation of PostgreSQL
To: mailto:secur...@postgresql.org>>


Hello Sir,
                I got stuck into an error repeatedly while installing 
PostgreSQL v15.3 and I don't know what to do, while opening pgAdmin 4 
just getting *a blank page* written *Loading pgAdmin 4 v7.4 * plz 
provide me the steps to resolve this issue. i am attaching the 
screenshot of the error below




This is a pgAdmin issue the appropriate list for this is:

https://www.postgresql.org/list/pgadmin-support/

where there two threads that start here

https://www.postgresql.org/message-id/CANHF%2Bv4R-Q2DiF4%2B3Z6d3faV-f27h1MYD1qHK_9EnU%3DH2G%2BHrw%40mail.gmail.com

and here

https://www.postgresql.org/message-id/CAL%3DJ9swWnXd%2BOs%2BnzkB6Ew50hYuqshOQn4ybu9e2SGZk_WrEcw%40mail.gmail.com

that deal with the problem.







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

Re: suggestion about time based partitioning and hibernate

[Alvaro Herrera]

On 2023-Jul-18, Luca Ferrari wrote:

> Dear all,
> I'm looking for ideas here, and it could be someone already stepped
> into declarative partitioning of an existing database where Hibernate
> (a Java ORM) handles the tables.
> The situation is as follows:
> 
> create table foo( id primary key, a_date date, ... );
> 
> Now, the trivial way to partition this would be on a range based on
> a_date, so that the primary key of the tables shifts from id to (id,
> a_date).

Hmm.  If you can make partitioning give you some benefit without having
to change the model, then by all means explore it.  But if you're forced
to change the model (in this case, by adding a column to your primary
key), that is going to cause you lots of pain, and some queries might
become slower rather than faster.  Therefore I suggest to avoid doing
that.  Either look at some other partitioning scheme that doesn't
involve adding columns to the primary key, or disregard partitioning for
this table entirely.

-- 
Álvaro Herrera PostgreSQL Developer  —  https://www.EnterpriseDB.com/

Re: Effects of dropping a large table

[David Rowley]

On Wed, 19 Jul 2023 at 07:41, Rob Sargent  wrote:
> You might consider deleting portions of the table in separate (consecutive) 
> batches (maybe 5% per delete).  And then truncate table is not logged so that 
> might be an alternative.

Can you explain why this would be a useful thing to do?

It sounds to me like it would just create a load of needless WAL from
the deletes and the vacuum that cleans up the dead rows each of which
is more likely to cause lag problems on the replica servers, which the
OP is trying to avoid.

David

Re: Effects of dropping a large table

[Rob Sargent]

On 7/19/23 17:15, David Rowley wrote:

On Wed, 19 Jul 2023 at 07:41, Rob Sargent  wrote:

You might consider deleting portions of the table in separate (consecutive) 
batches (maybe 5% per delete).  And then truncate table is not logged so that 
might be an alternative.

Can you explain why this would be a useful thing to do?

It sounds to me like it would just create a load of needless WAL from
the deletes and the vacuum that cleans up the dead rows each of which
is more likely to cause lag problems on the replica servers, which the
OP is trying to avoid.

David
No, you're right.  I was remembering problems with _deleting_ 
essentially all of a large table (with limited resources).  The drop 
might not have the same problem.  But aren't they logged/transactional 
and then in the WALs anyway.

Nu-B here

[Amn Ojee Uw]

After this command 'sudo -u postgres psql'
I get this message :
*could not change directory to "/home/my_account": Permission denied**
**psql (12.15 (Debian 12.15-1.pgdg120+1))**
**Type "help" for help.*

I tried using help, but to no avail.
What am I doing wrong?

Thanks in advance!

My 1st JDBC and PostgreSQL

[Amn Ojee Uw]

Following the example in :
https://www.tutorialspoint.com/postgresql/postgresql_java.htm
I wrote the bellow method :

public void connectToDatabase() throws ClassNotFoundException, 
SQLException {

    try {
    this.perr(this.getDatabaseClass()); // 
org.postgresql.Driver = Class
    this.perr(this.getDatabaseUrl());   // 
jdbc:postgresql://localhost:5432/sara.pgdb = url

    this.perr(this.getUserID());    // chispa = user
    this.perr(this.getUserPassword());  // 8UUKZW = password

    Class.forName(this.getDatabaseClass().toString());
    conn = 
DriverManager.getConnection(this.getDatabaseUrl().toString(),

    this.getUserID().toString(),
    this.getUserPassword().toString() );
    } catch (final SQLException | ClassNotFoundException e) {
    throw e;
    }
    }

However, it throws an exception that reads :
Exception in thread "main" org.postgresql.util.PSQLException: FATAL: 
database "sara.pgdb" does not exist


In reference to the above web page, it reads :
The following Java code shows how to connect to an existing database. If 
the database does not exist, then it will be created and finally a 
database object will be returned.


True, the database file does not exist, but isn't JDBC supposed to 
create it?



Any help would be much appreciated.

Re: Nu-B here

[Tom Lane]

Amn Ojee Uw  writes:
> After this command 'sudo -u postgres psql'
> I get this message :
> *could not change directory to "/home/my_account": Permission denied**
> **psql (12.15 (Debian 12.15-1.pgdg120+1))**
> **Type "help" for help.*

This is normal if you launch psql via "sudo -u postgres" in a directory
that the postgres user doesn't have permissions to read/chdir to.
psql will still start, but its working directory will not be what
you'd expect, so commands that involve local filesystem access might
misbehave.

We have a fix for this error report in the upcoming v16, but psql commands
that involve local filesystem access will still not act very nicely if
psql can't read your directory.

regards, tom lane

Re: Nu-B here

[Maciek Sakrejda]

On Wed, Jul 19, 2023, 17:36 Amn Ojee Uw  wrote:

> After this command 'sudo -u postgres psql'
> I get this message :
> *could not change directory to "/home/my_account": Permission denied*
> *psql (12.15 (Debian 12.15-1.pgdg120+1))*
> *Type "help" for help.*
>
> I tried using help, but to no avail.
> What am I doing wrong?
>
It looks like you're hitting a non-fatal error at some point during psql
startup when you're running as the "postgres" user. It seems to be a
permissions issue. Do you still get the error if you run

sudo -u postgres psql --no-psqlrc

?

You may also want to create a Postgres user (possibly a superuser) matching
your operating system user if you have not already done so to avoid having
to deal with sudo for connecting.

Thanks
Maciek

Re: My 1st JDBC and PostgreSQL

[Tom Lane]

Amn Ojee Uw  writes:
> https://www.tutorialspoint.com/postgresql/postgresql_java.htm
> In reference to the above web page, it reads :
> The following Java code shows how to connect to an existing database. If 
> the database does not exist, then it will be created and finally a 
> database object will be returned.
> True, the database file does not exist, but isn't JDBC supposed to 
> create it?

I think that web page is lying to you.  JDBC cannot promise to
create a database for you, because (a) if the given database
doesn't exist, it would have to guess at some other database
to connect to, with no certainty of success; and (b) even if
it manages to connect to the server, there's no certainty
that you'd have permissions to create a new database.
So I don't believe any JDBC driver would even try.

regards, tom lane

Re: My 1st JDBC and PostgreSQL

[Chuck Davis]

Postgresql is a sophisticated database server.  You can do what you're
attempting with something like Derby at the connection.  But with
Postgresql "you get what you pay for".
1) Insall Postgresql
2) start the database
3) use the interface app psql to create a database;
4) load the JDBC driver in your client and connect.
5) manipulate the database via the JDBC driver.

All these steps are explained quite nicely in the Postgresql documentation
you can find at the web site.  The JDBC site is separate but a Google
search will find it for you.

On Wed, Jul 19, 2023 at 5:37 PM Amn Ojee Uw  wrote:

> Following the example in :
> https://www.tutorialspoint.com/postgresql/postgresql_java.htm
> I wrote the bellow method :
>
> public void connectToDatabase() throws ClassNotFoundException,
> SQLException {
>  try {
>  this.perr(this.getDatabaseClass()); //
> org.postgresql.Driver = Class
>  this.perr(this.getDatabaseUrl());   //
> jdbc:postgresql://localhost:5432/sara.pgdb = url
>  this.perr(this.getUserID());// chispa = user
>  this.perr(this.getUserPassword());  // 8UUKZW = password
>
>  Class.forName(this.getDatabaseClass().toString());
>  conn =
> DriverManager.getConnection(this.getDatabaseUrl().toString(),
>  this.getUserID().toString(),
>  this.getUserPassword().toString() );
>  } catch (final SQLException | ClassNotFoundException e) {
>  throw e;
>  }
>  }
>
> However, it throws an exception that reads :
> Exception in thread "main" org.postgresql.util.PSQLException: FATAL:
> database "sara.pgdb" does not exist
>
> In reference to the above web page, it reads :
> The following Java code shows how to connect to an existing database. If
> the database does not exist, then it will be created and finally a
> database object will be returned.
>
> True, the database file does not exist, but isn't JDBC supposed to
> create it?
>
>
> Any help would be much appreciated.
>
>
>
>

Re: My 1st JDBC and PostgreSQL

[Jeffrey Walton]

On Wed, Jul 19, 2023 at 8:37 PM Amn Ojee Uw  wrote:
>
> Following the example in :
> https://www.tutorialspoint.com/postgresql/postgresql_java.htm
> I wrote the bellow method :
>
> public void connectToDatabase() throws ClassNotFoundException,
> SQLException {
>  try {
>  this.perr(this.getDatabaseClass()); //
> org.postgresql.Driver = Class
>  this.perr(this.getDatabaseUrl());   //
> jdbc:postgresql://localhost:5432/sara.pgdb = url
>  this.perr(this.getUserID());// chispa = user
>  this.perr(this.getUserPassword());  // 8UUKZW = password
>
>  Class.forName(this.getDatabaseClass().toString());
>  conn =
> DriverManager.getConnection(this.getDatabaseUrl().toString(),
>  this.getUserID().toString(),
>  this.getUserPassword().toString() );
>  } catch (final SQLException | ClassNotFoundException e) {
>  throw e;
>  }
>  }
>
> However, it throws an exception that reads :
> Exception in thread "main" org.postgresql.util.PSQLException: FATAL:
> database "sara.pgdb" does not exist
>
> In reference to the above web page, it reads :
> The following Java code shows how to connect to an existing database. If
> the database does not exist, then it will be created and finally a
> database object will be returned.
>
> True, the database file does not exist, but isn't JDBC supposed to
> create it?

If you are not married to tutorialspoint.com gear, then you can get
the Chinook database for testing. Chinook is the free/open source
alternative to Microsoft's Northwind database. See
https://github.com/lerocha/chinook-database .

Jeff

Postgres SQL

[Anthony Apollis]

Hi

What list can i post sql related errors etc?

Re: Nu-B here

[Alban Hertroys]

> On 20 Jul 2023, at 02:36, Amn Ojee Uw  wrote:
> 
> After this command 'sudo -u postgres psql'
> I get this message : 
> could not change directory to "/home/my_account": Permission denied

What’s the reason that you’re using the OS user postgres?

If you’re simply trying to connect to the database named postgres as database 
user postgres, you can instead use the command 'psql -U postgres postgres’.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.