Restoring 9.1 db from crashed disk/VM

[robert]

Friends

I hope you had a good start into 2021.


I would like to get some advice on how to restore a 9.1 DB, of which I have the

/var/lib/postgresql

with some 20GB salvaged.

Now I find now easily usable 9.1 PostgreSQL installer anymore.

How should I proceed to get that data in a newer PG loaded?

Could I just install a 9.5 and  copy the salvaged folder into

/var/lib/postgresql/9/main


Or could I add the folder to my PG 12 installation like:

/var/lib/postgresql/12/main
                            /9/main

and tell PG 12 somehow to read the data for the PG9 folder?

Thanks for your insights

Robert

the installation of pgadmin4 makes me weep in frustration

[robert]

Hi There


first, thanks a lot for the great an beautiful software we get from 
PostgreSQL and all people around it.


But I wonder how it comes, that installing pgadmin4 is so incredibly hard?

And no documentation.

I would like to install pgadmin4 to my ubuntu 18.1 laptop.

Where do I find doku on how to install pgadmin4. Preferably I would like 
to install it using pip?



thanks

robert

--
Robert Rottermann CEO

031 333 10 20
rob...@redo2oo.ch <mailto:rob...@redo2oo.ch>
Sickingerstrasse 3, 3014 Bern <http://maps.apple.com/?q=Sickingerstrasse 
3,3014 Bern>


https://Redo2oo.ch <https://redo2oo.ch>

*Ihr Partner wenn es um ERP Lösungen geht.*

Re: pgadmin4: not possible to create server

[robert]

thanks

unfortunately id did not help
On 06.02.2018 18:29, Murtuza Zabuawala wrote:

Add following in your pg_hba.conf,

host all all             0.0.0.0/ <http://127.0.0.1/32>0md5

restart your Postgres database server and try to connect again from 
pgAdmin4.

from pgadmin3 I can connect

any more ideas?
robert
On Tue, Feb 6, 2018 at 10:54 PM, robert rottermann <mailto:rob...@redcor.ch>>wrote:


Hi there,

I have installed pgadmin4 locally using its docker image.

this i did running the following command:

docker run -p 80:80 -e "PGADMIN_DEFAULT_EMAIL=admin" -e
"PGADMIN_DEFAULT_PASSWORD=admin" -d dpage/pgadmin4

I then could access it and log into it.

However all my attempts end in:

Unable to connect to server:

could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?


can anybody give me a hint, what to do?

thanks

robert


in /etc/postgresql/10/main/postgresql.conf

i have:


#--
# CONNECTIONS AND AUTHENTICATION

#--

# - Connection Settings -

listen_addresses = '*'  # what IP address(es) to listen on;
  # comma-separated list of addresses;


and this is my /etc/postgresql/10/main/pg_hba.conf

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all postgres peer

# TYPE  DATABASE    USER    ADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
host    all all 127.0.0.1/32 <http://127.0.0.1/32> trust
# IPv6 local connections:
host    all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication all peer
host    replication all 127.0.0.1/32 <http://127.0.0.1/32> md5
host    replication all ::1/128 md5

Re: pgadmin4: not possible to create server

[robert]

On 06.02.2018 18:28, Adrian Klaver wrote:

On 02/06/2018 09:24 AM, robert rottermann wrote:

Hi there,

I have installed pgadmin4 locally using its docker image.

this i did running the following command:

docker run -p 80:80 -e "PGADMIN_DEFAULT_EMAIL=admin" -e 
"PGADMIN_DEFAULT_PASSWORD=admin" -d dpage/pgadmin4


I then could access it and log into it.

However all my attempts end in:

Unable to connect to server:

could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?


So where is the Postgres server located?

on my local desktop
robert

Re: pgadmin4: not possible to create server

[robert]

On 06.02.2018 23:12, Adrian Klaver wrote:

On 02/06/2018 02:08 PM, robert wrote:



On 06.02.2018 18:28, Adrian Klaver wrote:

On 02/06/2018 09:24 AM, robert rottermann wrote:

Hi there,

I have installed pgadmin4 locally using its docker image.

this i did running the following command:

docker run -p 80:80 -e "PGADMIN_DEFAULT_EMAIL=admin" -e 
"PGADMIN_DEFAULT_PASSWORD=admin" -d dpage/pgadmin4


I then could access it and log into it.

However all my attempts end in:

Unable to connect to server:

could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?


So where is the Postgres server located?

on my local desktop


So you pgAdmin4 running in a container on your local machine and 
Postgres running in the native OS(the OS is?) on your local machine. 
Is the Docker container set up to 'see' the local machine?

Re: pgadmin4: not possible to create server

[robert]

On 06.02.2018 23:12, Adrian Klaver wrote:

On 02/06/2018 02:08 PM, robert wrote:



On 06.02.2018 18:28, Adrian Klaver wrote:

On 02/06/2018 09:24 AM, robert rottermann wrote:

Hi there,

I have installed pgadmin4 locally using its docker image.

this i did running the following command:

docker run -p 80:80 -e "PGADMIN_DEFAULT_EMAIL=admin" -e 
"PGADMIN_DEFAULT_PASSWORD=admin" -d dpage/pgadmin4


I then could access it and log into it.

However all my attempts end in:

Unable to connect to server:

could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?


So where is the Postgres server located?

on my local desktop


So you pgAdmin4 running in a container on your local machine and 
Postgres running in the native OS(the OS is?) on your local machine. 
Is the Docker container set up to 'see' the local machine?


what do you mean by see?

solution: Re: pgadmin4: not possible to create server

[robert]

Adrian


So you pgAdmin4 running in a container on your local machine and 
Postgres running in the native OS(the OS is?) on your local machine. 
Is the Docker container set up to 'see' the local machine?


what do you mean by see?


For fuller explanation see:

https://stackoverflow.com/questions/24319662/from-inside-of-a-docker-container-how-do-i-connect-to-the-localhost-of-the-mach 




your hint pointed in the right direction:
it is not localhost, on which the server is running, because localhost 
is relative to the container, so it is in effect the container itself.

Postgres is running on the gatway of the containers network.
This I found out by issuing:
docker inspect reverent_volhard (where reverent_volhard is the 
containers name in which pgadmin4 runs)

this providest:
...
  "Networks": {
    "bridge": {
    "IPAMConfig": null,
    "Links": null,
    "Aliases": null,
    "NetworkID": 
"df33249ff65c37ea1ca1f142b4a74283c4429c44c3ed1eb5ea80c695c87ab86d",
    "EndpointID": 
"905c0c78145ef1847854f221200d8e4a7788f99b901030460111258ab9156b29",

    "Gateway": "172.17.0.1",
    "IPAddress": "172.17.0.2",
    "IPPrefixLen": 16,
    "IPv6Gateway": "",
    "GlobalIPv6Address": "",
    "GlobalIPv6PrefixLen": 0,
    "MacAddress": "02:42:ac:11:00:02",
    "DriverOpts": null
    }

so postgres runs on 172.17.0.1

and bingo, it works

thanks again
robert

Re: pgadmin4: not possible to create server

[robert]

On 06.02.2018 23:33, Adrian Klaver wrote:

On 02/06/2018 02:19 PM, robert wrote:




So you pgAdmin4 running in a container on your local machine and 
Postgres running in the native OS(the OS is?) on your local machine. 
Is the Docker container set up to 'see' the local machine?


what do you mean by see?



Meant to ask earlier, why you are using the Docker image instead of 
one of the binaries listed here:


https://www.pgadmin.org/download/

i tried to install pgadmin4 some 10 months ago and found it confusing 
with the server and desktop mode

Re: pgadmin4: not possible to create server

[robert]

thanks

unfortunately id did not help
On 06.02.2018 18:29, Murtuza Zabuawala wrote:

Add following in your pg_hba.conf,

host all all             0.0.0.0/ <http://127.0.0.1/32>0md5

restart your Postgres database server and try to connect again from 
pgAdmin4.

from pgadmin3 I can connect

any more ideas?
robert
On Tue, Feb 6, 2018 at 10:54 PM, robert rottermann <mailto:rob...@redcor.ch>>wrote:


Hi there,

I have installed pgadmin4 locally using its docker image.

this i did running the following command:

docker run -p 80:80 -e "PGADMIN_DEFAULT_EMAIL=admin" -e
"PGADMIN_DEFAULT_PASSWORD=admin" -d dpage/pgadmin4

I then could access it and log into it.

However all my attempts end in:

Unable to connect to server:

could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?


can anybody give me a hint, what to do?

thanks

robert


in /etc/postgresql/10/main/postgresql.conf

i have:


#--
# CONNECTIONS AND AUTHENTICATION

#--

# - Connection Settings -

listen_addresses = '*'  # what IP address(es) to listen on;
  # comma-separated list of addresses;


and this is my /etc/postgresql/10/main/pg_hba.conf

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all postgres peer

# TYPE  DATABASE    USER    ADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
host    all all 127.0.0.1/32 <http://127.0.0.1/32> trust
# IPv6 local connections:
host    all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication all peer
host    replication all 127.0.0.1/32 <http://127.0.0.1/32> md5
host    replication all ::1/128 md5







--
Robert Rottermann CEO

031 333 10 20
rob...@redo2oo.ch <mailto:rob...@redo2oo.ch>
Sickingerstrasse 3, 3014 Bern 
<http://maps.apple.com/?q=Sickingerstrasse%203,3014%20Bern>


https://Redo2oo.ch <https://redo2oo.ch>

*Ihr Partner wenn es um ERP Lösungen geht.*

Re: pgadmin4: not possible to create server

[robert]

On 06.02.2018 18:28, Adrian Klaver wrote:

On 02/06/2018 09:24 AM, robert rottermann wrote:

Hi there,

I have installed pgadmin4 locally using its docker image.

this i did running the following command:

docker run -p 80:80 -e "PGADMIN_DEFAULT_EMAIL=admin" -e 
"PGADMIN_DEFAULT_PASSWORD=admin" -d dpage/pgadmin4


I then could access it and log into it.

However all my attempts end in:

Unable to connect to server:

could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?


So where is the Postgres server located?

on my local desktop
robert

Query hitting empty tables taking 48 minutes

[Robert Creager]

I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java OpenJDK 
1.8.0_131, jdbc 9.3-1104-jdbc41 which is exhibiting very bizarre behavior.  A 
query is executing against a couple of tables that have 1 and 0 records in 
them.  ds3.job_entry has 0 rows, ds3.blob has 1 row.  If I execute the query 
manually via command line, it executes fine.  There are no other active 
queries, no locks.  The system is idle, in our Dev Test group, so I can leave 
it this way for a bit of time.  The general software setup is in production and 
I’ve seen nothing like this before.  Even a system with 300M ds3.blob entries 
executes this query fine.

Jun  7 17:24:21 blackpearl postgres[10670]: [7737-1] 
db=tapesystem,user=Administrator,app=[unknown],client=127.0.0.1 LOG:  duration: 
2903612.206 ms  execute fetch from S_2037436/C_2037437: SELECT * FROM ds3.blob 
WHERE EXISTS (SELECT * FROM ds3.job_entry WHERE blob_id = ds3.blob.id AND 
(job_id = $1))

tapesystem=# explain analyze SELECT * FROM ds3.blob WHERE EXISTS (SELECT * FROM 
ds3.job_entry WHERE blob_id = ds3.blob.id AND (job_id = 
'b51357cd-e07a-4c87-a50b-999c347a5c71'));   
  QUERY PLAN
---

 Nested Loop  (cost=0.84..6.89 rows=1 width=77) (actual time=0.044..0.044 
rows=0 loops=1)
   ->  Index Scan using job_entry_job_id_idx on job_entry  (cost=0.42..2.44 
rows=1 width=16) (actual time=0.042..0.042 rows=0 loops=1)
 Index Cond: (job_id = 'b51357cd-e07a-4c87-a50b-999c347a5c71'::uuid)
   ->  Index Scan using blob_pkey on blob  (cost=0.42..4.44 rows=1 width=77) 
(never executed)
 Index Cond: (id = job_entry.blob_id)  
 Planning time: 0.388 ms
 Execution time: 0.118 ms   




pid client_port runtime query_start datname state   query   usename
10670   11211   0 years 0 mons 0 days 0 hours 43 mins 28.852273 secs
2018-06-07 17:24:22.026384  tapesystem  active  SELECT * FROM ds3.blob 
WHERE EXISTS (SELECT * FROM ds3.job_entry WHERE blob_id = ds3.blob.id AND 
(job_id = $1)) Administrator


From the system with 300M ds3.blob entries, which works fine (along with every 
other system in house):

QUERY PLAN
Nested Loop  (cost=0.57..738052.90 rows=164379 width=75) (actual 
time=1.001..1947.029 rows=5 loops=1)
  ->  Seq Scan on job_entry  (cost=0.00..10039.50 rows=164379 width=16) (actual 
time=0.871..56.442 rows=5 loops=1)
Filter: (job_id = 'ef27d2fa-2727-424e-8f44-da9e33a5ca05'::uuid)
Rows Removed by Filter: 60001
  ->  Index Scan using blob_pkey on blob  (cost=0.57..4.42 rows=1 width=75) 
(actual time=0.037..0.037 rows=1 loops=5)
Index Cond: (id = job_entry.blob_id)
Planning time: 6.330 ms
Execution time: 1951.564 ms


Please keep my CC of my work e-mail present.

Best,
Robert

Re: Query hitting empty tables taking 48 minutes

[Robert Creager]

> On Jun 7, 2018, at 12:40 PM, Adrian Klaver  wrote:
> 
> On 06/07/2018 11:17 AM, Robert Creager wrote:
>> I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java OpenJDK 
>> 1.8.0_131, jdbc 9.3-1104-jdbc41 which is exhibiting very bizarre behavior.  
>> A query is executing against a couple of tables that have 1 and 0 records in 
>> them.  ds3.job_entry has 0 rows, ds3.blob has 1 row.   If I execute the 
>> query manually via command line, it executes fine.   There are no other 
>> active queries, no locks.  The system is idle, in our Dev Test group, so I 
>> can leave it this way for a bit of time.  The general software setup is in 
>> production and I’ve seen nothing like this before.  Even a system with 300M 
>> ds3.blob entries executes this query fine.
> 
> So I am assuming the problem query is being run through Java/jdbc, correct?

Yes.

> 
> There is also the below in the log:
> 
> " ... execute fetch from S_2037436/C_2037437 …"

So, that means nothing to me.  Something to you?

> 
> My guess is that we will need to see the Java code that sets up and runs the 
> query. Is that possible?

OMG, you had to ask ;-)  Not really, for two reasons.  It’s an actual shipping 
product, and I’d have to send you so much code to get from the source of the 
query down through the execute…. Now, the execute, on it’s own, is below.  
m_statement is a java.sql.PreparedStatement.  Keep in mind this code is 
literally executing millions of times a day on a busy system.  Except for this 
special snowflake system...

private Executor executeQuery()
{
final MonitoredWork monitoredWork = new MonitoredWork( 
StackTraceLogging.NONE, m_readableSql.getLogMessagePreExecution() );
try
{
m_closeTransactionUponClose = ( null == m_transactionNumber );
m_statement.setFetchSize( 1 );
final Duration duration = new Duration();
m_resultSet = m_statement.executeQuery();
m_readableSql.log( duration, null );
return this;
}
catch ( final SQLException ex )
{
throw new DaoException( 
"Failed to execute: " + 
m_readableSql.getLogMessagePreExecution(),
ex );
}
finally
{
monitoredWork.completed();
}
}

> 
>> Jun  7 17:24:21 blackpearl postgres[10670]: [7737-1] 
>> db=tapesystem,user=Administrator,app=[unknown],client=127.0.0.1 LOG:  
>> duration: *2903612.206* ms  execute fetch from S_2037436/C_2037437: SELECT * 
>> FROM ds3.blob WHERE EXISTS (SELECT * FROM ds3.job_entry WHERE blob_id = 
>> ds3.blob.id <http://ds3.blob.id> AND (job_id = $1))
>> tapesystem=# explain analyze SELECT * FROM ds3.blob WHERE EXISTS (SELECT * 
>> FROM ds3.job_entry WHERE blob_id = ds3.blob.id <http://ds3.blob.id> AND 
>> (job_id = 'b51357cd-e07a-4c87-a50b-999c347a5c71'));
>>   QUERY PLAN
>> ---
>>Nested Loop  (cost=0.84..6.89 rows=1 width=77) (actual time=0.044..0.044 
>> rows=0 loops=1)
>>->  Index Scan using job_entry_job_id_idx on job_entry  (cost=0.42..2.44 
>> rows=1 width=16) (actual time=0.042..0.042 rows=0 loops=1)
>>  Index Cond: (job_id = 'b51357cd-e07a-4c87-a50b-999c347a5c71'::uuid)
>>->  Index Scan using blob_pkey on blob  (cost=0.42..4.44 rows=1 width=77) 
>> (never executed)
>>  Index Cond: (id = job_entry.blob_id)
>>  Planning time: 0.388 ms
>>  Execution time: 0.118 ms
>> pid  client_port runtime query_start datname state   query   usename
>> 1067011211   0 years 0 mons 0 days 0 hours 43 mins 28.852273 secs 
>> 2018-06-07 17:24:22.026384 tapesystem  active  SELECT * FROM ds3.blob 
>> WHERE EXISTS (SELECT * FROM ds3.job_entry WHERE blob_id = ds3.blob.id 
>> <http://ds3.blob.id> AND (job_id = $1))Administrator
>> From the system with 300M ds3.blob entries, which works fine (along with 
>> every other system in house):
>> QUERY PLAN
>> Nested Loop  (cost=0.57..738052.90 rows=164379 width=75) (actual 
>> time=1.001..1947.029 rows=5 loops=1)
>>   ->  Seq Scan on job_entry  (cost=0.00..10039.50 rows=164379 width=16) 
>> (actual time=0.871..56.442 rows=5 loops=1)
>> Filter: (job_id = 'ef27d2fa-2727-424e-8f44-da9e33a5ca05'::uuid)
>> Rows Removed by Filter: 60001
>>   ->  Index Scan using blob_pkey on blob  (cost=0.57..4.42 rows=1 width=75) 
>> (actual time=0.037..0.037 rows=1 loops=5)
>> Index Cond: (id = job_entry.blob_id)
>> Planning time: 6.330 ms
>> Execution time: 1951.564 ms
>> Please keep my CC of my work e-mail present.
>> Best,
>> Robert
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: Query hitting empty tables taking 48 minutes

[Robert Creager]

> On Jun 7, 2018, at 1:14 PM, Adrian Klaver  wrote:
> 
> On 06/07/2018 11:55 AM, Robert Creager wrote:
>>> On Jun 7, 2018, at 12:40 PM, Adrian Klaver >> <mailto:adrian.kla...@aklaver.com>> wrote:
>>> 
>>> On 06/07/2018 11:17 AM, Robert Creager wrote:
>>>> I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java OpenJDK 
>>>> 1.8.0_131, jdbc 9.3-1104-jdbc41 which is exhibiting very bizarre behavior. 
>>>>  A query is executing against a couple of tables that have 1 and 0 records 
>>>> in them.  ds3.job_entry has 0 rows, ds3.blob has 1 row.   If I execute the 
>>>> query manually via command line, it executes fine.   There are no other 
>>>> active queries, no locks.  The system is idle, in our Dev Test group, so I 
>>>> can leave it this way for a bit of time.  The general software setup is in 
>>>> production and I’ve seen nothing like this before.  Even a system with 
>>>> 300M ds3.blob entries executes this query fine.
>>> 
>>> So I am assuming the problem query is being run through Java/jdbc, correct?
>> Yes.
>>> 
>>> There is also the below in the log:
>>> 
>>> " ... execute fetch from S_2037436/C_2037437 …"
>> So, that means nothing to me.  Something to you?
> 
> Just that the query being run from the code is going through a different path 
> then the version you ran at the command line. Just trying to get to apples to 
> apples if possible.
> 
>>> 
>>> My guess is that we will need to see the Java code that sets up and runs 
>>> the query. Is that possible?
>> OMG, you had to ask ;-)  Not really, for two reasons.  It’s an actual 
>> shipping product, and I’d have to send you so much code to get from the 
>> source of the query down through the execute…. Now, the execute, on it’s 
>> own, is below.  m_statement is a java.sql.PreparedStatement.  Keep in mind 
>> this code is literally executing millions of times a day on a busy system.  
>> Except for this special snowflake system...
> 
> I am not a Java programmer(hence the 'we' in my previous post), so someone 
> who is will need to comment on the below. Though I have to believe the:
> 
> m_statement.setFetchSize( 1 );
> 
> has got to do with:
> 
> " ... execute fetch from S_2037436/C_2037437 …"
> 
> In your OP you said the tables involved have 1 and 0 rows in them.

Yes.

> 
> Is that from count(*) on each table?

Yes.

> Or is it for for job_id = 'b51357cd-e07a-4c87-a50b-999c347a5c71’?

There is no job_id entry, that table is empty.

Best,
Robert

> 
>> private Executor executeQuery()
>> {
>> final MonitoredWork monitoredWork = new MonitoredWork(
>> StackTraceLogging.NONE, 
>> m_readableSql.getLogMessagePreExecution() );
>> try
>> {
>> m_closeTransactionUponClose = ( null == m_transactionNumber );
>> m_statement.setFetchSize( 1 );
>> final Duration duration = new Duration();
>> m_resultSet = m_statement.executeQuery();
>> m_readableSql.log( duration, null );
>> return this;
>> }
>> catch ( final SQLException ex )
>> {
>> throw new DaoException(
>> "Failed to execute: " + 
>> m_readableSql.getLogMessagePreExecution(),
>> ex );
>> }
>> finally
>> {
>> monitoredWork.completed();
>> }
>> }
>>> 
> 
>>> -- 
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: Query hitting empty tables taking 48 minutes

[Robert Creager]

> On Jun 7, 2018, at 3:34 PM, Tom Lane  wrote:
> 
> Robert Creager  writes:
>> Jun  7 17:24:21 blackpearl postgres[10670]: [7737-1] 
>> db=tapesystem,user=Administrator,app=[unknown],client=127.0.0.1 LOG:  
>> duration: 2903612.206 ms  execute fetch from S_2037436/C_2037437: SELECT * 
>> FROM ds3.blob WHERE EXISTS (SELECT * FROM ds3.job_entry WHERE blob_id = 
>> ds3.blob.id AND (job_id = $1))
> 
>> tapesystem=# explain analyze SELECT * FROM ds3.blob WHERE EXISTS (SELECT * 
>> FROM ds3.job_entry WHERE blob_id = ds3.blob.id AND (job_id = 
>> 'b51357cd-e07a-4c87-a50b-999c347a5c71'));   
>>  QUERY PLAN
>> ---
>> 
>> Nested Loop  (cost=0.84..6.89 rows=1 width=77) (actual time=0.044..0.044 
>> rows=0 loops=1)
>>   ->  Index Scan using job_entry_job_id_idx on job_entry  (cost=0.42..2.44 
>> rows=1 width=16) (actual time=0.042..0.042 rows=0 loops=1)
>> Index Cond: (job_id = 'b51357cd-e07a-4c87-a50b-999c347a5c71'::uuid)
>>   ->  Index Scan using blob_pkey on blob  (cost=0.42..4.44 rows=1 width=77) 
>> (never executed)
>> Index Cond: (id = job_entry.blob_id) 
>>  
>> Planning time: 0.388 ms
>> Execution time: 0.118 ms 
>>  
>>  
> 
> That's fairly bizarre, but important to notice here is that you don't have
> an apples-to-apples comparison.  The query in the log is evidently
> parameterized, whereas your EXPLAIN isn't; it's got a hard-wired constant
> to compare to job_id.  I'd suggest trying this in psql:
> 
> PREPARE foo(uuid) AS SELECT * FROM ds3.blob WHERE EXISTS (SELECT * FROM 
> ds3.job_entry WHERE blob_id = ds3.blob.id AND (job_id = $1));
> 
> EXPLAIN ANALYZE EXECUTE foo('b51357cd-e07a-4c87-a50b-999c347a5c71');
> 
> Assuming you get a sane plan the first time, repeat the EXPLAIN ANALYZE
> half a dozen times, and note whether the plan changes after six
> executions.  (The point here is to see if the plancache will shift to
> a "generic" plan, and if so whether that's worse than a "custom" plan
> for the specific parameter value.)

The plan didn’t change after bunches (> 6) executions.

tapesystem=# EXPLAIN ANALYZE EXECUTE 
foo('b51357cd-e07a-4c87-a50b-999c347a5c71');
  QUERY PLAN
 
---
 Nested Loop  (cost=0.84..6.89 rows=1 width=77) (actual time=0.032..0.032 
rows=0 loops=1)  
   ->  Index Scan using job_entry_job_id_idx on job_entry  (cost=0.42..2.44 
rows=1 width=16) (actual time=0.031..0.031 rows=0 loops=1)  
   
 Index Cond: (job_id = 'b51357cd-e07a-4c87-a50b-999c347a5c71'::uuid)
  
   ->  Index Scan using blob_pkey on blob  (cost=0.42..4.44 rows=1 width=77) 
(never executed)
  
 Index Cond: (id = job_entry.blob_id)   
 
 Execution time: 0.096 ms   
 
(6 rows) 

Executing with the job_id shown in the stats of the empty table below (didn’t 
change after bunches of executions).  The job_entry table has very ephemeral 
data in general.

tapesystem=# EXPLAIN ANALYZE EXECUTE 
foo('cc54ca5d-0dca-4b35-acd9-e0fe69c6b247');
 QUERY PLAN 


 Hash Join  (cost=9582.63..21191.13 rows=300019 width=77) (actual 
time=22.679..22.679 rows=0 loops=1)
   Hash Cond: (job_entry.blob_id = blob.id)
   ->  Seq Scan on job_entry  (cost=0.00..7483.24 rows=300019 width=16) (actual 
time=22.677..22.677 rows=0 loops=1)
 Filter: (job_id = 'cc54ca5d-0dca-4b35-acd9-e0fe69c6b247'::uuid)
   ->  Hash  (cost=5832.28..5832.28 rows=300028 width=77) (never executed)
 ->  Se

Re: Query hitting empty tables taking 48 minutes

[Robert Creager]

Re-sending from a subscribed address (sigh).

On Jun 7, 2018, at 4:18 PM, Robert  wrote:


> On Jun 7, 2018, at 2:15 PM, Laurenz Albe  <mailto:laurenz.a...@cybertec.at>> wrote:
> 
> Robert Creager wrote:
>> I have a system running FreeBSD 11.1-STABLE, PostgreSQL 9.6.8,Java OpenJDK 
>> 1.8.0_131,
>> jdbc 9.3-1104-jdbc41 which is exhibiting very bizarre behavior.
>> A query is executing against a couple of tables that have 1 and 0 records in 
>> them.
>> ds3.job_entry has 0 rows, ds3.blob has 1 row.
>> If I execute the query manually via command line, it executes fine.  There 
>> are no other active queries, no locks.
>> The system is idle, in our Dev Test group, so I can leave it this way for a 
>> bit of time.
>> The general software setup is in production and I’ve seen nothing like this 
>> before.
>> Even a system with 300M ds3.blob entries executes this query fine.
>> 
>> Jun  7 17:24:21 blackpearl postgres[10670]: [7737-1] 
>> db=tapesystem,user=Administrator,app=[unknown],client=127.0.0.1 LOG:  
>> duration: 2903612.206 ms  execute fetch from S_2037436/C_2037437: SELECT * 
>> FROM ds3.blob WHERE EXISTS (SELECT * FROM ds3.job_entry WHERE blob_id = 
>> ds3.blob.id <http://ds3.blob.id/> AND (job_id = $1))
>> 
>> tapesystem=# explain analyze SELECT * FROM ds3.blob WHERE EXISTS (SELECT * 
>> FROM ds3.job_entry WHERE blob_id = ds3.blob.id <http://ds3.blob.id/> AND 
>> (job_id = 'b51357cd-e07a-4c87-a50b-999c347a5c71'));   
>>  QUERY PLAN
>> ---
>> 
>> Nested Loop  (cost=0.84..6.89 rows=1 width=77) (actual time=0.044..0.044 
>> rows=0 loops=1)
>>   ->  Index Scan using job_entry_job_id_idx on job_entry  (cost=0.42..2.44 
>> rows=1 width=16) (actual time=0.042..0.042 rows=0 loops=1)
>> Index Cond: (job_id = 'b51357cd-e07a-4c87-a50b-999c347a5c71'::uuid)
>>   ->  Index Scan using blob_pkey on blob  (cost=0.42..4.44 rows=1 width=77) 
>> (never executed)
>> Index Cond: (id = job_entry.blob_id) 
>>  
>> Planning time: 0.388 ms
>> Execution time: 0.118 ms 
>>  
>>   
>> 
>> 
>> pid  client_port runtime query_start datname state   query   usename
>> 1067011211   0 years 0 mons 0 days 0 hours 43 mins 28.852273 secs
>> 2018-06-07 17:24:22.026384  tapesystem  active  SELECT * FROM 
>> ds3.blob WHERE EXISTS (SELECT * FROM ds3.job_entry WHERE blob_id = 
>> ds3.blob.id <http://ds3.blob.id/> AND (job_id = $1))   Administrator
>> 
>> 
>> From the system with 300M ds3.blob entries, which works fine (along with 
>> every other system in house):
>> 
>> QUERY PLAN
>> Nested Loop  (cost=0.57..738052.90 rows=164379 width=75) (actual 
>> time=1.001..1947.029 rows=5 loops=1)
>>  ->  Seq Scan on job_entry  (cost=0.00..10039.50 rows=164379 width=16) 
>> (actual time=0.871..56.442 rows=5 loops=1)
>>Filter: (job_id = 'ef27d2fa-2727-424e-8f44-da9e33a5ca05'::uuid)
>>Rows Removed by Filter: 60001
>>  ->  Index Scan using blob_pkey on blob  (cost=0.57..4.42 rows=1 width=75) 
>> (actual time=0.037..0.037 rows=1 loops=5)
>>Index Cond: (id = job_entry.blob_id)
>> Planning time: 6.330 ms
>> Execution time: 1951.564 ms
> 
> The JDBC query is using a cursor since you are using setFetchSize(1).
> 
> I can see two reasons why the plan might be different:
> 
> 1. It is a prepared statement that has been executed often enough
>   for the generic plan to kick in, and the generic plan is bad.

Tom brought up that, see my response to him.  It is a bad plan, but on a table 
with no entries.

> 2. PostgreSQL chooses a fast startup plan because a cursor is used,
>   and that plan performs much worse.

The parameterized plan Tom had me look at starts with sequence scans, which 
would be bad on the job_entry table (topping out at ~9M entries worst case), 
horrendous on the blob table (open ended size, testing with 300M entries on one 
of our systems).

> To see if 1. causes the problem, you could set
>  m_statement.setPrepareThreshold(0);
> and see if that makes a difference.

If I can keep this machine and spin some code, I could 

Re: Query hitting empty tables taking 48 minutes

[Robert Creager]

> On Jun 7, 2018, at 4:58 PM, David G. Johnston  
> wrote:
> 
> On Thu, Jun 7, 2018 at 3:02 PM, Robert Creager  <mailto:rob...@logicalchaos.org>> wrote:
> ​[...]​ 
>  job_id  | f |  1 | cc54ca5d-0dca-4b35-acd9-e0fe69c6b247
> 
> IIUC, the system believes​ your job_entry table has 300k records ALL of them 
> having the UUID value ending in "*b247" - so it is unsurprising that it 
> chooses to sequentially scan job_entry when its given that ID to search for.  
> And if its given a different ID is realizes it can accurately confirm the 
> absence of the supplied value in the table by using the index.

Yes, I agree. And this explain execution time is fine.  It’s the ’same’ query 
running in the app for 43 minutes at a shot on the same tables that’s giving me 
heartburn.

> I would suspect that vacuuming these tables would solve your problem.  
> Whether there is an issue beyond a lack of vacuuming, or related to 
> auto-vacuum, I am unsure.  Though at this point it may take a vacuum full to 
> recover back to a sane state.  Though ANALYZE by itself should clear up the 
> statistical discrepancy.

Auto-vacuum is on, and I was thinking the same re VACUUM fixing it, but, it 
seems there may be a deeper problem here, hence calling in the people who know 
things :-)

autovacuum_vacuum_threshold = 5000  # min number of row updates before 
vacuum
autovacuum_analyze_threshold = 5000 # min number of row updates before
autovacuum_vacuum_scale_factor = 0.1# fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.05  # fraction of table size before analyze

(Auto)Vacuum was run (no analyze) ~4.5 hours ago (if this query is correct), 
but maybe another should of been triggered?

SELECT
  *,
  n_dead_tup > av_threshold AS "av_needed",
  CASE WHEN reltuples > 0
THEN round(100.0 * n_dead_tup / (reltuples))
  ELSE 0 END AS pct_dead
FROM (SELECT
N.nspname,
C.relname,
pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins,
pg_stat_get_tuples_updated(C.oid) AS n_tup_upd,
pg_stat_get_tuples_deleted(C.oid) AS n_tup_del,
pg_stat_get_live_tuples(C.oid) AS n_live_tup,
pg_stat_get_dead_tuples(C.oid) AS n_dead_tup,
C.reltuples AS reltuples,
round(current_setting('autovacuum_vacuum_threshold') :: INTEGER + 
current_setting('autovacuum_vacuum_scale_factor') :: NUMERIC * C.reltuples) AS 
av_threshold,
date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), 
pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum,
date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), 
pg_stat_get_last_analyze_time(C.oid))) AS last_analyze
  FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE C.relkind IN ('r', 't') AND N.nspname NOT IN ('pg_catalog', 
'information_schema') AND N.nspname !~ '^pg_toast') AS av
WHERE reltuples > 0
ORDER BY av_needed DESC, n_dead_tup DESC;

nspname relname n_tup_ins   n_tup_upd   n_tup_del   n_live_tup  
n_dead_tup  reltuples   av_thresholdlast_vacuum last_analyze
av_needed   pct_dead
ds3 job_entry   303658  815 303658  0   300022  300022  35002   
2018-06-07 23:09:00.00  NULLtrue100

> But, I may be missing something, my experience and skill here is somewhat 
> limited.

Ditto, at this level.

Best,
Robert

Re: Query hitting empty tables taking 48 minutes

[Robert Creager]

On Jun 7, 2018, at 4:58 PM, David G. Johnston 
mailto:david.g.johns...@gmail.com>> wrote:

I would suspect that vacuuming these tables would solve your problem.  Whether 
there is an issue beyond a lack of vacuuming, or related to auto-vacuum, I am 
unsure.  Though at this point it may take a vacuum full to recover back to a 
sane state.  Though ANALYZE by itself should clear up the statistical 
discrepancy.

A nightly VACUUM FULL which ran based on heuristics resolved the problem.  This 
would seem to point to a db problem more than an app problem?  I’m unsure how 
the app could have an affect of this magnitude on the database, although I’d 
love to be told otherwise.

Best,
Robert

Re: Query hitting empty tables taking 48 minutes

[Robert Creager]

> On Jun 8, 2018, at 10:23 AM, David G. Johnston  
> wrote:
> 
> ​Not sure what the right answer is but its seems your database (those tables 
> at least) are mis-configured for the workload being ​executed against them.  
> Significantly increasing the aggressiveness of the auto-vacuum process and/or 
> inserting manual vacuum analyze commands into your application at appropriate 
> times are probably necessary.
> 

I’m fine with changing up table parameters, which is the option that would make 
sense for us (thanks for pointing that out).  I have the auto vacuum threshold 
high because of other huge tables, and was not aware of the per table settings. 
 I’ll use this excuse one time, I inherited this setup, now I own it :-)

I’m concerned about a query that’s going against two tables that have had 300k 
entries in them (ie now empty and 2 entries) taking so long.  Even if those 
tables where full, the query should of taken no time at all.  The machine has 
64GB memory, 12 physical cores (+12 hyper threads) and the storage is on a ZFS 
pool with 5 mirrored vdevs of 7.2k SAS drives.  The entire db size is 2.63GB, 
easily fitting into memory.  This is a production appliance, and is build to 
handle the load.  Obviously needs some intelligent tuning though.


nspname relname n_tup_ins   n_tup_upd   n_tup_del   n_live_tup  
n_dead_tup  reltuples   av_thresholdlast_vacuum last_analyze
av_needed   pct_dead
ds3 blob303498  2559303496  2   0   2   5000
2018-06-08 04:35:00.00  NULLfalse   0
ds3 job_entry   303659  815 303659  0   0   0   5000
2018-06-08 04:35:00.00  NULLfalse   0

Best,
Robert

Re: Query hitting empty tables taking 48 minutes

[Robert Creager]

> On Jun 7, 2018, at 4:18 PM, Robert  wrote:
> 
>> You can capture the execution plan of the bad statement by using 
>> auto_explain,
>> that would certainly shed more light on the problem.
> 

A different query started showing up as the problem, the auto_explain with 
analyze shows an oddity, the total query duration is 11k seconds, while the 
query itself is 3 seconds.  I captured a ZFS snapshot as soon as the problem 
was noticed.

Jun 16 23:15:30 blackpearl postgres[9860]: [79-1] 
db=tapesystem,user=Administrator,app=[unknown],client=127.0.0.1 LOG:  duration: 
10856644.336 ms  plan:   
Jun 16 23:15:30 blackpearl postgres[9860]: [79-2]   Query Text: DELETE FROM 
ds3.blob WHERE EXISTS (SELECT * FROM ds3.s3_object WHERE id = 
ds3.blob.object_id AND (bucket_id = $1)) 
Jun 16 23:15:30 blackpearl postgres[9860]: [79-3]   Delete on blob  
(cost=1308.79..1312.82 rows=1 width=12) (actual time=3465.919..3465.919 rows=0 
loops=1)
Jun 16 23:15:30 blackpearl postgres[9860]: [79-4] ->  Nested Loop  
(cost=1308.79..1312.82 rows=1 width=12) (actual time=50.293..2435.271 
rows=30 loops=1)  
Jun 16 23:15:30 blackpearl postgres[9860]: [79-5]   ->  Bitmap Heap 
Scan on s3_object  (cost=634.39..636.41 rows=1 width=22) (actual 
time=50.269..153.885 rows=30 loops=1) 
Jun 16 23:15:30 blackpearl postgres[9860]: [79-6] Recheck 
Cond: (bucket_id = 'bc6e6b10-80ad-4329-9fb9-1a66d8c1505e'::uuid)
 
Jun 16 23:15:30 blackpearl postgres[9860]: [79-7] Heap 
Blocks: exact=3704
Jun 16 23:15:30 blackpearl postgres[9860]: [79-8] ->  
Bitmap Index Scan on ds3_s3_object__bucket_id  (cost=0.00..634.39 rows=1 
width=0) (actual time=49.552..49.552 rows=30 loops=1)
Jun 16 23:15:30 blackpearl postgres[9860]: [79-9]   
Index Cond: (bucket_id = 'bc6e6b10-80ad-4329-9fb9-1a66d8c1505e'::uuid)  
   
Jun 16 23:15:30 blackpearl postgres[9860]: [79-10]  ->  Bitmap Heap 
Scan on blob  (cost=674.39..676.41 rows=1 width=22) (actual time=0.005..0.006 
rows=1 loops=30)
Jun 16 23:15:30 blackpearl postgres[9860]: [79-11]Recheck 
Cond: (object_id = s3_object.id)
Jun 16 23:15:30 blackpearl postgres[9860]: [79-12]Heap 
Blocks: exact=30
Jun 16 23:15:30 blackpearl postgres[9860]: [79-13]->  
Bitmap Index Scan on ds3_blob__object_id  (cost=0.00..674.39 rows=1 width=0) 
(actual time=0.004..0.004 rows=1 loops=30)
Jun 16 23:15:30 blackpearl postgres[9860]: [79-14]  
Index Cond: (object_id = s3_object.id)


Doing a ZFS rollback and executing the query shows what is happening, although 
not to the extent above.  If I read this correctly, it’s the constraint checks 
that are causing the query to take so long.  I don’t see any server 
configuration that might allow those checks to be parallelized.  Is that 
possible?

tapesystem=# PREPARE foo(uuid) AS DELETE FROM ds3.blob WHERE EXISTS (SELECT * 
FROM ds3.s3_object WHERE id = ds3.blob.object_id AND (bucket_id = $1)); EXPLAIN 
ANALYZE EXECUTE foo('bc6e6b10-80ad-4329-9fb9-1a66d8c1505e');
PREPARE
   QUERY PLAN   

   

   
 Delete on blob  (cost=9555.07..21134.01 rows=23 width=12) (actual 
time=1516.140..1516.140 rows=0 loops=1)
   ->  Hash Join  (cost=9555.07..21134.01 rows=23 width=12) (actual 
time=237.816..621.306 rows=30 loops=1)
 Hash Cond: (s3_object.id = blob.object_id)
 ->  Seq Scan on s3_object  (cost=0.00..7454.04 rows=23 width=22) 
(actual time=0.027..148.503 rows=30 loops=1)
 
   Filter: (bucket_id = 
'bc6e6b10-80ad-4329-9fb9-1a66d8c1505e'::uuid)
   Rows Removed by Filter: 3
 ->  Hash  (cost=5805.03..5805.03 rows=33 width=22) (actual 
time=235.219..235.219 rows=33 loops=1)
   Buckets: 524288  Batches: 1  Memory Usage: 19917kB
   ->  Seq Scan on blob  (cost=0.00..5805.03 rows=33 width=22) 
(actual time=0.038..114.107 rows=33 loops=1)

 Trigger for constraint blob_tape_blob_id_fkey: time=5389.627 calls=30
 Trigger for constraint m

Re: Query hitting empty tables taking 48 minutes

[Robert Creager]

> On Jun 18, 2018, at 4:04 PM, Laurenz Albe  wrote:
> 
> Robert Creager wrote:
>> A different query started showing up as the problem, the auto_explain with 
>> analyze shows an oddity,
>> the total query duration is 11k seconds, while the query itself is 3 
>> seconds.  I captured a ZFS
>> snapshot as soon as the problem was noticed.
>> 
>>   db=tapesystem,user=Administrator,app=[unknown],client=127.0.0.1 LOG:  
>> duration: 10856644.336 ms  plan: 
>>   
>> Query Text: DELETE FROM ds3.blob WHERE EXISTS (SELECT * FROM 
>> ds3.s3_object WHERE id = ds3.blob.object_id AND (bucket_id = $1))
>>  
>> Delete on blob  (cost=1308.79..1312.82 rows=1 width=12) (actual 
>> time=3465.919..3465.919 rows=0 loops=1)  
>>   
>> [...]
>> 
>> Doing a ZFS rollback and executing the query shows what is happening, 
>> although not to the extent above.
>> If I read this correctly, it’s the constraint checks that are causing the 
>> query to take so long.
>> I don’t see any server configuration that might allow those checks to be 
>> parallelized.  Is that possible?
>> 
>> tapesystem=# PREPARE foo(uuid) AS DELETE FROM ds3.blob WHERE EXISTS (SELECT 
>> * FROM ds3.s3_object WHERE id = ds3.blob.object_id AND (bucket_id = $1)); 
>> EXPLAIN ANALYZE EXECUTE foo('bc6e6b10-80ad-4329-9fb9-1a66d8c1505e');
>> PREPARE
> 
> Are we missing an "EXECUTE foo('bc6e6b10-80ad-4329-9fb9-1a66d8c1505e')" here?

Nope, just hidden on the same line, this is the plan for that EXECUTE

> 
>>   QUERY PLAN 
>>  
>> 
>> 
>>
>> Delete on blob  (cost=9555.07..21134.01 rows=23 width=12) (actual 
>> time=1516.140..1516.140 rows=0 loops=1)
>> [...]
>> Trigger for constraint blob_tape_blob_id_fkey: time=5389.627 calls=30
>> [...]
>> Execution time: 85175.581 ms
>> 
>> I executed a vacuum analyze, then a vacuum full analyze, neither changed the 
>> timing.
>> Other than removing constraints, is there any way to address this?
> 
> I cannot explain the discrepancy between the runtimes of 85 seconds versus 
> 10857 seconds.

It would be nice if the auto_explain analyze did include the other information 
like the psql analyze does.

> But other than that, it sure looks like the foreign keys are missing an index 
> on
> the source columns, leading to a sequential scan for each deletion and table.

You’d think, but they are present.  As an example, the first constraint 
blob_tape_blob_id_fkey is indexed.

create table blob
(
[…]
  iduuid   not null
constraint blob_pkey
primary key,
[…]
);

create table blob_tape
(
  blob_id uuidnot null
constraint blob_tape_blob_id_fkey
references ds3.blob
on update cascade on delete cascade,
  id  uuidnot null
constraint blob_tape_pkey
primary key,
[…]
);

create index tape_blob_tape__blob_id
  on blob_tape (blob_id);

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

Re: Query hitting empty tables taking 48 minutes

[Robert Creager]

> On Jun 18, 2018, at 4:33 PM, Robert Creager  wrote:
> 
>> I cannot explain the discrepancy between the runtimes of 85 seconds versus 
>> 10857 seconds.
> 
> It would be nice if the auto_explain analyze did include the other 
> information like the psql analyze does.

Like this.  I’ll try again.

auto_explain.log_triggers (boolean)
auto_explain.log_triggers causes trigger execution statistics to be included 
when an execution plan is logged. This parameter has no effect unless 
auto_explain.log_analyze is enabled. This parameter is off by default. Only 
superusers can change this setting.

Re: Not able to update some rows in a table

[Robert Zenz]

> I have a table with 21 columns.
> Primary key is done with 20 of these colums.

Oh, okay. What data types do these encompass?

> I don't know why, but I'm not able to update some of these records.
> I don't understand, it seems that I'm not able to fetch.

You mean you can't query them with all 20/21 columns present in the where 
clause?

Re: Ways to deal with large amount of columns;

[Robert Zenz]

As David said, you'd be better off having a table that looks like this (in terms
of columns):

 * MONTH
 * AGENT
 * CASHFLOW

So your query to get the sum of a single agent would be looking like:

select
sum(CHASFLOW)
where
AGENT = 'Agent'
and MONTH between values;

It might be a little more work to create a GUI for that (or map this model to
the existing GUI) but it is much simpler to maintain and work with.


On 30.08.2018 11:13, a wrote:
> Hi all:
> 
> 
> I need to make a table contains projected monthly cashflow for multiple 
> agents (10,000 around).
> 
> 
> Therefore, the column number would be 1000+. 
> 
> 
> I would need to perform simple aggregate function such as count, sum or 
> average on each cashflow projected.
> 
> 
> So if there is anyway of doing this? Will there be anything like define a 
> macro in C that I can manipulate multiple columns by simple word that 
> representing them.
> 
> 
> Thanks so much!
> 
> 
> Shore
> 

Re: Code of Conduct plan

[Robert Eckhardt]

On Fri, Sep 14, 2018 at 9:41 AM, Adrian Klaver
 wrote:
> On 9/14/18 1:31 AM, Chris Travers wrote:
>>
>>
>>
>> On Wed, Sep 12, 2018 at 10:53 PM Tom Lane > > wrote:
>>
>> I wrote:
>>  > Stephen Frost mailto:sfr...@snowman.net>>
>> writes:
>>  >> We seem to be a bit past that timeline...  Do we have any update
>> on when
>>  >> this will be moving forward?
>>  >> Or did I miss something?
>>
>>  > Nope, you didn't.  Folks have been on holiday which made it hard
>> to keep
>>  > forward progress going, particularly with respect to selecting
>> the initial
>>  > committee members.  Now that Magnus is back on shore, I hope we can
>>  > wrap it up quickly --- say by the end of August.
>>
>> I apologize for the glacial slowness with which this has all been
>> moving.
>> The core team has now agreed to some revisions to the draft CoC based
>> on
>> the comments in this thread; see
>>
>> https://wiki.postgresql.org/wiki/Code_of_Conduct
>>
>> (That's the updated text, but you can use the diff tool on the page
>> history tab to see the changes from the previous draft.)
>>
>>
>> I really have to object to this addition:
>> "This Code is meant to cover all interaction between community members,
>> whether or not it takes place within postgresql.org 
>> infrastructure, so long as there is not another Code of Conduct that takes
>> precedence (such as a conference's Code of Conduct)."
>
>
> I second that objection. It is not in PGDG's remit to cure the world, for
> whatever form of cure you ascribe to. This is especially true as 'community
> member' has no strict definition.

I understand the concern, however, if you look at how attacks happen
it is frequently through other sites. Specifically under/poorly
moderated sites. For specific examples, people who have issues with
people on Quora will frequently go after them on Facebook and Twitter.

these aren't a solution looking for a problem. If we just want to look
at the clusterfuck that is happening in the reddis community right now
we can see conversations spilling onto twitter and into ad hominem
vitriol.

My $0.02
-- Rob Eckhardt

>
>
>>
>> That covers things like public twitter messages over live political
>> controversies which might not be personally directed.   At least if one is
>> going to go that route, one ought to *also* include a safe harbor for
>> non-personally-directed discussions of philosophy, social issues, and
>> politics.  Otherwise, I think this is asking for trouble.  See, for example,
>> what happened with Opalgate and how this could be seen to encourage use of
>> this to silence political controversies unrelated to PostgreSQL.
>>
>>
>> I think we are about ready to announce the initial membership of the
>> CoC committee, as well, but that should be a separate post.
>>
>>  regards, tom lane
>>
>>
>>
>> --
>> Best Wishes,
>> Chris Travers
>>
>> Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor
>> lock-in.
>> http://www.efficito.com/learn_more
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Code of Conduct plan

[Robert Haas]

On Fri, Sep 14, 2018 at 11:10 AM, Dave Page  wrote:
> That wording has been in the published draft for 18 months, and noone
> objected to it that I'm aware of. There will always be people who don't like
> some of the wording, much as there are often people who disagree with the
> way a patch to the code is written. Sooner or later though, the general
> consensus prevails and we have to move on, otherwise nothing will ever get
> completed.

It's not clear to me that there IS a general consensus here.  It looks
to me like the unelected core team got together and decided to impose
a vaguely-worded code of conduct on a vaguely-defined group of people
covering not only their work on PostgreSQL but also their entire life.
It is not difficult to imagine that someone's private life might
include "behavior that may bring the PostgreSQL project into
disrepute."

However, I also don't think it matters very much.  The Code of Conduct
Committee is going to consist of small number of people -- at least
four, perhaps a few more.  But there are hundreds of people involved
on the PostgreSQL mailing lists, maybe thousands.  If the Code of
Conduct Committee, or the core team, believes that it can impose on a
very large group of people, all of whom are volunteers, some set of
rules with which they don't agree, it's probably going to find out
pretty quickly that it is mistaken.  If people from that large group
get banned for behavior which is perceived by other members of that
large group to be legitimate, then there will be a ferocious backlash.
Nobody wants to see people who are willing to contribute driven away
from the project, and anyone we drive away without a really good
reason will find some other project that welcomes their participation.
So the only thing that the Code of Conduct Committee is likely to be
able to do in practice is admonish people to be nicer (which is
probably a good thing) and punish really egregious conduct, especially
when committed by people who aren't involved enough that their absence
will be keenly felt.

In practice, therefore, democracy is going to win out.  That's both
good and bad.  It's good because nobody wants a CoC witch-hunt, and
it's bad because there's probably some behavior which legitimately
deserves censure and will escape it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Code of Conduct plan

[Robert Haas]

On Fri, Sep 14, 2018 at 4:17 PM, Tom Lane  wrote:
> There's been quite a lot of input, from quite a lot of people, dating
> back at least as far as a well-attended session at PGCon 2016.  I find
> it quite upsetting to hear accusations that core is imposing this out
> of nowhere.  From my perspective, we're responding to a real need
> voiced by other people, not so much by us.

Yeah, but there's a difference between input and agreement.  I don't
think there's been a mailing list thread anywhere at any time where a
clear majority of the people on that thread supported the idea of a
code of conduct.  I don't think that question has even been put.  I
don't think there's ever been a developer meeting where by a show of
hands the idea of a CoC, much less the specific text, got a clear
majority.  I don't think that any attempt has been made to do that,
either.  Core is (thankfully) not usually given to imposing new rules
on the community; we normally operate by consensus.  Why this specific
instance is an exception, as it certainly seems to be, is unclear to
me.

To be clear, I'm not saying that no harassment occurs in our
community.  I suspect women get harassed at our conferences.  I know
of only one specific incident that made me uncomfortable, and that was
quite a few years ago and the woman in question laughed it off when I
asked her if there was a problem, but I have heard rumors of other
things on occasion, and I just wouldn't be too surprised if we're not
all as nice in private as we pretend to be in public.  And on the
other hand, I think that mailing list discussions step over the line
to harassment from time to time even though that's in full public
view.  Regrettably, you and I have both been guilty of that from time
to time, as have many others.  I know that I, personally, have been
trying to be a lot more careful about the way I phrase criticism in
recent years; I hope that has been noticeable, but I only see it from
my own perspective, so I don't know.  Nonwithstanding, I would like to
see us, as a group, do better.  We should tolerate less bad behavior
in ourselves and in others, and however good or bad we are today as
people, we should try to be better people.

Whether or not the code of conduct plan that the core committee has
decided to implement is likely to move us in that direction remains
unclear to me.  I can't say I'm very impressed by the way the process
has been carried out up to this point; hopefully it will work out for
the best all the same.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: How to handle things that change over time?

[Robert Heinen]

I"m a big fan of a table / table_history combo. Meaning, if you have a
person and that person can have different states that change frequently,
you can do something like this:

create table people(
person_id
person_name
)

create table people_state_history(

person_id references people,
effective tsrange not null
default tsrange(clock_timestamp()::timestamp without time zone,
'infinity'),

primary key (person_id, effective),

person_state

)

Use a constraint so you can't have overlapping effective ranges:
alter table person_state_history add
constraint no_overlapping_person_state_history
exclude using gist (
cast(person_id as text ) with =,
effective with &&);


Then, to see a person's state at the current time, you can do something
like this:

select person_id, person_state from people_state_history where effective <@
now()::timestamp without time zone
(and add your joins in as well).

We've also created a trigger, so any new insert on the state_history table
will automatically close the last row.

create or replace function set_event_history_in_use ()
returns trigger
as
$$

begin

update person_state_history
set effective = tsrange(lower(effective),
clock_timestamp()::timestamp without time zone)
where clock_timestamp()::timestamp without time zone <@ effective
and person_id = NEW.person_id;
NEW.effective := tsrange(clock_timestamp()::timestamp without time
zone, 'infinity');
return NEW;

end;
$$
language plpgsql;

Then, depending on business requirements, you can create this dual table to
track history across time. If salaries are something people would like to
see over time, then you could do a person_salary_history table, etc.

Hope this helps!

Best,
Rob Heinen

On Fri, Sep 13, 2019 at 11:57 AM stan  wrote:

>
> I am working on a system that will support internal bossiness work for a
> company. Periodicly things will change in their "world". For example they
> periodically recalculate the "mark up" of various components, of their
> bushiness, such as labor and or purchased materials. Presently I am keeping
> these constants in a table, and I have the constant, and an effective
> start,
> and end date for these factors. In this scenario, the number of rows will
> just grow over time, and by using the proper conditions in our select
> statement, i can apply the correct factors as they change over time.
>
> In another scenario, a column of the employee table is the pay rate.
> Obviously this will change over time, also. It does not appear to me that
> this lends itself to this same treatment, as most of the data associated
> with a given employee, is fairly static, and if I add an entire row, just
> because the pay rate changed, this looks overly complex.
>
> This cannot be the first time this issue has been addressed. What have
> others done in this scenario?
>
>
>

Re: GPS coordinates problem

[Robert Heinen]

You can use the postgis extension:

create extension postgis;

Then you can create a geography coulmn

location geography( point, 4326)

and insert a lat /long as a point like this:
ST_GeographyFromText('SRID=4326;POINT(%(longitude)s %(latitude)s)'),





On Tue, Oct 8, 2019 at 1:30 PM Andreas Kretschmer 
wrote:

>
>
> Am 08.10.19 um 12:50 schrieb Timmy Siu:
> > Now, I need Global Position System coordinates as a data type. How do
> > I define it in Postgresql 11 or 12?
>
> consider PostGIS.
>
>
> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>
>

Postgresql 12.x on Windows (vs Linux)

[Robert Ford]

I am aware that this might be a broad question, but I am not expecting *very
*specific answers either:

When it come to running a modern PostgreSQL server, which serves say 1 TB
of data, are there substantial differences in performance between Windows
Server 2019 and Linux today?  I know there are some issues with
shared_buffers and windows, but does it manifest in real performance issues?

I have searched the web for this question, but is surprised to find very
few concrete benchmarks on this.  Anyone with real world experience on this
topic?

best regards
RF

Basic question about structuring SQL

[Robert Inder]

I'm an experienced programmer but really new to SQL,
and I'm wanting a pointer to "the SQL way" to structure/organise chunks of
code.

A while back, I wrote a chunk of SQL to Do Something Useful.
I put it in a file (do-something-useful.sql).
And, to protect against getting into a weird state, I wrapped the code in
my file with
   BEGIN;
   UPDATE
   DELETE...
   COMMIT;
With the idea that I can do
   psql my_database
   \i do-something-useful.sql
And be sure that either my task will be have been completed, or nothing
with have changed.

NOW, I want to do the same for a BIGGER task.
BUT I realise that if I create bigger-task.sql like this...
   BEGIN;
  <>
   \i do-something-useful.sql
  <>
   COMMIT;
...the COMMIT inside "do-something-useful.sql" closes the transaction
started in "bigger-task.sql"
So I can get some things committed even if other things (in tidy-up) fail.

So how SHOULD I tackle this?
PostgreSQL does not do nested transactions (right?)

So how should I structure my chunks of SQL so that I can have "safe"
(all-or-nothing) blocks,
AND use them from within one another?

Robert

RE: PG Admin 4

[Robert West]

Yes...all that is in there is the same results for the entire batch..no 
intermediate results for each piece of DDL.

I'm submitting a large series of DDL changes in one batchI'm still looking 
how to see the results of the execution of each DDL statement.

RW

-Original Message-
From: Adrian Klaver  
Sent: Friday, July 10, 2020 1:47 PM
To: Robert West ; pgsql-gene...@postgresql.org
Subject: Re: PG Admin 4

ATTENTION: This email was sent from an external source. Please exercise caution 
when opening attachments or clicking links.


On 7/10/20 8:53 AM, rwest wrote:
> I'm relatively new to PostgreSql and am trying to navigate my way 
> around the tools like PG Admin 4 to do Database Admin work.
>
> I'm trying to run an entire set of DDL with lots of tables, indexes, etc.
> through PG Admin 4 for a database.  The only thing I saw after I ran 
> the script was a message about the final index being created and how 
> long the entire script ran in milliseconds.  When running a large 
> batch of objects, I would expect to see messages about the result of 
> each individual object being created (like what happens in SSMS for 
> SQL Server, or any of the tools for Oracle).  Is there some setting or 
> switch somewhere in PG Admin 4 that I can turn on to see messages for 
> each object that it processes while it is executing my script?  It 
> doesn't give me a warm a fuzzy feeling to not see anything while the script 
> it running.

Have you looked in the Query History tab?:

https://www.pgadmin.org/docs/pgadmin4/4.23/query_tool.html#query-history-panel

>
> Thanks - RW
>
>
>
> --
> Sent from: 
> https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>


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

Writing WAL files

[Robert Inder]

I am moving a database from PSQL 9 (!) on CentOS 6 to PSQL 12 on CentOS 7

I have a pair of servers -- one live, one standby.
The live server defines an archive_command as "rsync" to shift WAL
files to the standby server,
The standby server uses "pg_standby" to monitor and process incoming WAL
files.
I believe this is all very vanilla, and indeed changes made in the live
database are duly shipped to the standby.

BUT...

One of the things I like about the old PGSQL 9 setup is that it generates
and ships a WAL file every few minutes, even if nothing has happened in the
database.
I find it re-assuring to be able to see WAL files arriving and being
processed without problem even when the live system was idle.

But I cannot get PGSQL 12 to do this.  It only writes (and thus ships) WAL
files when something happens in the database.
If the database is idle, it simply does not write any WAL files.

I thought I would get WAL files written from an idle database if, in
postgresql.conf, I set "archive_timeout" to 120.

And I've tried setting "checkpoint_timeout" to 90s,

But to no avail.  No WAL files are written unless the database changes.

So what am I missing?  How CAN I get postgresql 12 to write "unnecessary"
WAL files every couple of minutes?

Robert.

-- 
Robert Inder,0131 229 1052 / 07808 492
213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
   Interactions speak louder than
words

Re: Writing WAL files

[Robert Inder]

On Sun, 4 Oct 2020 at 18:01, Adrian Klaver 
wrote:

> On 10/4/20 9:54 AM, Robert Inder wrote:
> > I am moving a database from PSQL 9 (!) on CentOS 6 to PSQL 12 on CentOS 7
>
> It would help to know what the x in 9.x is? Before version 10 of
> Postgres, the second number denoted a major version.
>

9.4.
Moving to 12.4.



>
> >
> > I have a pair of servers -- one live, one standby.
> > The live server defines an archive_command as "rsync" to shift WAL
> > files to the standby server,
> > The standby server uses "pg_standby" to monitor and process incoming WAL
> > files.
> > I believe this is all very vanilla, and indeed changes made in the live
> > database are duly shipped to the standby.
> >
> > BUT...
> >
> > One of the things I like about the old PGSQL 9 setup is that it
> > generates and ships a WAL file every few minutes, even if nothing has
> > happened in the database.
> > I find it re-assuring to be able to see WAL files arriving and being
> > processed without problem even when the live system was idle.
> >
> > But I cannot get PGSQL 12 to do this.  It only writes (and thus ships)
> > WAL files when something happens in the database.
> > If the database is idle, it simply does not write any WAL files.
> >
> > I thought I would get WAL files written from an idle database if, in
> > postgresql.conf, I set "archive_timeout" to 120.
> >
> > And I've tried setting "checkpoint_timeout" to 90s,
> >
> > But to no avail.  No WAL files are written unless the database changes.
> >
> > So what am I missing?  How CAN I get postgresql 12 to write
> > "unnecessary" WAL files every couple of minutes?
> >
> > Robert.
> >
> > --
> > Robert Inder,0131 229 1052 / 07808
> > 492 213
> > Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
> > Registered in Scotland, Company no. SC 150689
> > Interactions speak louder
> > than words
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>

-- 
Robert Inder,0131 229 1052 / 07808 492
213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
   Interactions speak louder than
words

Re: Writing WAL files

[Robert Inder]

On Sun, 4 Oct 2020 at 20:52, Alvaro Herrera  wrote:

>
> This is on purpose; archiving WAL files that contain nothing is pure
> wastage of good electrons.

Seriously?  Oh, holy 


> I suggest that in PG12 you can monitor the
> "lag" of a standby server more directly by looking at columns write_lag,
> flush_lag, replay_lag in the pg_stat_replication view.


And are those things updated when there are no changes to the master
database?
If so, can anyone make the case that continually checking and updating them
(how often?) wastes fewer electrons than shipping an empty file every few
minutes?

Or are they only measured when something is updated?

If I upgrade/install/reconfigure/restart something, I want to know that I
haven't broken the sync.

Will looking at the replay_lag (where?  master?  standby?) tell me that the
sync is still good?
Or will they capture the last sync. operation, and so only tell me what I
need to know if I do some kind of database operation?

And if I have to do some kind of database operation, I may as well stick
wiht the current arrangement,
since that operation would force a WAL file transfer anyway...

(You'll need to
> change your configuration so that it uses streaming replication instead
> of pg_standby and rsync, but that's far more convenient so it's a good
> change anyway.)
>

Maybe, but it's forcing me to spend time understanding stuff that I really
don't want to know about.

Robert.

-- 
Robert Inder,0131 229 1052 / 07808 492
213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
   Interactions speak louder than
words

Re: Writing WAL files

[Robert Inder]

On Mon, 5 Oct 2020 at 18:29, Michael Lewis  wrote:

>
> If you setup a scripted process to update a single row with a timestamptz
> on the source/primary every minute, then you have a very simple consistent
> change and also a way to check on the replica what is current time vs
> last_scripted_update_time if you will and know the approx lag. It would
> seem like a simple albeit hacky solution to you wanting a file every X
> minutes regardless of server activity.
>

I'd thought of going half way to that, and just have a cron job for
   psql -c 'CHECKPOINT'
which doesn't give me the quantitative indication I'd get from updating a
timestamp, but doesn't commit me to know about any particular database,
either.

The thing that I find most disconcerting is that the documentation for
Version 12 says checkpoint_timeout is
"Maximum time between automatic WAL checkpoints"

But the change Adrian Klaverd highlighted suggests that this is
deliberately no longer the case,
and I am left wondering what it does, in fact do/mean now.


By the by, top-posting (reply above all quoted text) is discouraged on
> these groups.
>

Indeed.  Once upon a time, my signature use to be along the lines of


So what?  It's easier for me, so I'll do it!

> > What's wrong with top posting?
> It makes it hard to see comments in context.

Robert.

-- 
Robert Inder,0131 229 1052 / 07808 492
213
Interactive Information Ltd,   3, Lauriston Gardens, Edinburgh EH3 9HH
Registered in Scotland, Company no. SC 150689
   Interactions speak louder than
words

Re: Christopher Browne

[Robert Treat]

On Wed, Nov 4, 2020 at 6:29 PM Steve Singer  wrote:
>
>
> It is with much sadness that I am letting the community know that Chris
> Browne passed away recently.
>
> Chris had been a long time community member and was active on various
> Postgresql mailing lists.  He was a member of the funds committee, the PgCon
> program committee and served on the board of the PostgreSQL Community
> Association of Canada. Chris was a maintainer of the Slony
> replication system and worked on various PG related tools.
>
> I worked with Chris for over 10 years and anytime someone had a problem he
> would jump at the chance to help and propose solutions. He
> always had time to listen to your problem and offer ideas or explain how
> something worked.
>
> I will miss Chris
>

Chris is one of the folks whom I got to meet online very early in my
time with the project, who was there in Toronto at the first
developer's conference [1], and was one of the folks I always looked
forward to seeing in Ottawa, even if it was just to quickly catch-up.
I suspect his contributions to the project are understated, but a lot
of time, troubleshooting, and tutoring came from Chris to many others
of us around Slony (back when Slony was the main game in town) and
around plpgsql. Of course, he knew his way around an OS just as well
as he knew Postgres.

I think my fondest memory of Chris was one, many years back, when I
just happened to be in Toronto for non-postgres related business, and
we coordinated to meet up for a quick drink and to catch-up. He was
kind enough to offer me an invitation to a private "computer users"
dinner that he often frequented at a hole-in-the-wall Hungarian place.
I'm always game for a bit of adventure and I'm so glad I was because
ended up being a truly special night, learning much more about Chris
away from the database stuff, with a bunch of great food (still don't
know half of what it was), and the realization that this small group
of friends included several luminaries in the computer science field,
(as one example Henry Spencer, who wrote the "regex" software library
for regular expressions), who I can't imagine ever having the
opportunity to meet otherwise.

Most of us are not nearly as open and as kind as he was, and he will
indeed be missed.

[1] https://ic.pics.livejournal.com/obartunov/24248903/36575/36575_original.jpg,
he is the one in the back, holding up the sign.


Robert Treat
https://xzilla.net

ubuntu 18: PostgreSQL does not start. how can I totally remove and reinstall it

[robert rottermann]

Hi there

I made somehow a mess with my PostgreSQL installation an an ubuntu 18.4 with 
PostgreSQL 10.0


service postgresql status
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor 
preset: enabled)

   Active: active (exited) since Sat 2021-01-23 21:21:13 CET; 2min 26s ago
  Process: 1853 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 1853 (code=exited, status=0/SUCCESS)

I would like to remove everything, and reinstall.

How can I do that?

It seems, that

apt remove --purge postgresql
apt remove --purge postgresql-client

I not enough, as I have the same situation afterwards.

Or is there a way that I can rebuild PostgreSQL datastructure?

thanks

Robert

solved (was plain stupidity) Re: ubuntu 18: PostgreSQL does not start. how can I totally remove and reinstall it

[robert rottermann]

thanks a lot.
why dos such stupidity not hurt. ?

have a nice weekend
robert

On 24.01.21 08:04, Julien Rouhaud wrote:

On Sun, Jan 24, 2021 at 2:58 PM rob...@redo2oo.ch  wrote:

root@elfero-test:~/scripts# pg_lsclusters
Ver Cluster Port Status OwnerData directory  Log file
10  main5433 online postgres /var/lib/postgresql/10/main 
/var/log/postgresql/postgresql-10-main.log
[...]
psycopg2.OperationalError: could not connect to server: Connection refused
 Is the server running on host "localhost" (127.0.0.1) and accepting
 TCP/IP connections on port 5432?

It looks like your instance is configured to listen on port 5433, not 5432.

Re: LwLocks contention

[Robert Treat]

On Mon, Apr 25, 2022 at 10:33 AM Michael Lewis  wrote:
>
> On Thu, Apr 21, 2022 at 6:17 AM Chris Bisnett  wrote:
>>
>> We're working to update our application so that we can
>> take advantage of the pruning. Are you also using native partitioning?
>
>
> No partitioned tables at all, but we do have 1800 tables and some very 
> complex functions, some trigger insanity, huge number of indexes, etc etc.
>
> There are lots of things to fix, but I just do not yet have a good sense of 
> the most important thing to address right now to reduce the odds of this type 
> of traffic jam occurring again. I very much appreciate you sharing your 
> experience. If I could reliably reproduce the issue or knew what data points 
> to start collecting going forward, that would at least give me something to 
> go on, but it feels like I am just waiting for it to happen again and hope 
> that some bit of information makes itself known that time.
>
> Perhaps I should have posted this to the performance list instead of general.

In my experience lwlock contention (especially around buffer_mapping)
is more about concurrent write activity than any particular number of
tables/partitions. The first recommendation I would have is to install
pg_buffercache and see if you can capture some snapshots of what the
buffer cache looks like, especially looking for pinning_backends. I'd
also spend some time capturing pg_stat_activity output to see what
relations are in play for the queries that are waiting on said lwlocks
(especially trying to map write queries to tables/indexes).

Robert Treat
https://xzilla.net

pg_stat_activity.query empty

[Robert Lichtenberger]

When executing "SELECT * FROM pg_stat_activity" I get lots of rows that 
have an empty "query" column.


According to [1] this column should contain the "Text of this backend's 
most recent query", either a query that is currently running or the last 
query that was executed.


So how comes that a lot of the rows in pg_stat_activity have an empty 
"query"?



https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

UUID vs serial and currval('sequence_id')

[Robert Stanford]

Hi,

When doing an insert with a serial primary key we can refer to
currval('sequence_name') in subsequent inserts and we also return it for
later processing.

Example:
CREATE TABLE contact (
contactid serial not null primary key, -- creates sequence
'contact_contactid_seq'
firstname text not null,
lastname text
);
CREATE TABLE contactinterests(
contactid int not null references contact(contactid),
interest text
);

-- insert statement as single transaction
INSERT INTO contact(
firstname, lastname)
  VALUES('John', 'Smith');
INSERT INTO contactinterests(
contactid, interest)
  VALUES (currval('contact_contactid_seq'),'Fishing');

--insert statement as single transaction returning contactid
INSERT INTO contact(
firstname, lastname)
  VALUES('John', 'Smith');
INSERT INTO contactinterests(
contactid, interest)
  VALUES (currval('contact_contactid_seq'),'Fishing')
returning currval('contact_contactid_seq');

Which is very nice as it gives us back the contactid.

Is it possible to get similar functionality using gen_random_uuid() or
uuid-ossp?

Thanks
Robert

Re: UUID vs serial and currval('sequence_id')

[Robert Stanford]

On Tue, 3 May 2022 at 08:39, David G. Johnston 
wrote:

> You basically have to use "INSERT ... RETURNING" or variables.  Which/how
> depends on the language you are writing in.  Pure SQL without client
> involvement requires that you use chained CTEs of INSERT...RETURNING (or I
> suppose you could leverage set_config(), haven't tried that way myself).
> In pl/pgsql you can also use variables, and the same goes for psql - though
> that requires client involvement and so isn't generally that great a choice.
>
>
Thanks, so  I can do:

alter table contact add column contactuuid uuid
alter table contactinterests add column contactuuid uuid
alter table contactinterests drop column contactid

with thisuuid as (
SELECT gen_random_uuid() as thisuuid
),
 contactuuid as(
INSERT INTO contact(
 contactuuid,firstname, lastname)
VALUES(
(select thisuuid  from thisuuid ),'John', 'Smith') returning
(select thisuuid  from thisuuid )
)
INSERT INTO contactinterests(
contactuuid, interest)
  VALUES (
(select thisuuid  from contactuuid ),'Fishing')
  returning (select thisuuid  from contactuuid );


 Robert

Window function?

[Robert Stanford]

Hi,

I have time series data from multiple inputs with start and
end timestamps.

Can anyone suggest an elegant way to coalesce consecutive rows so only the
first start time and last end time for each group of events (by input) is
returned.

Sample from and to below where the rows for Input number 4 could be
massaged.
(Timestamps selected with timestamp(0) for convenience)

>From this:
Input   Start End
5   2022-06-04 09:09:00   2022-06-04 09:09:29
4   2022-06-04 09:08:50   2022-06-04 09:09:00
4   2022-06-04 09:08:10   2022-06-04 09:08:50
4   2022-06-04 09:07:47   2022-06-04 09:08:10
17  2022-06-04 09:06:47   2022-06-04 09:07:47
4   2022-06-04 09:06:37   2022-06-04 09:06:47
4   2022-06-04 09:06:29   2022-06-04 09:06:37
4   2022-06-04 09:06:17   2022-06-04 09:06:29
4   2022-06-04 09:05:53   2022-06-04 09:06:17
16  2022-06-04 09:04:33   2022-06-04 09:05:53

To this:
Input   Start End
5   2022-06-04 09:09:00   2022-06-04 09:09:29
4   2022-06-04 09:07:47   2022-06-04 09:09:00
17  2022-06-04 09:06:47   2022-06-04 09:07:47
4   2022-06-04 09:05:53   2022-06-04 09:06:47
16  2022-06-04 09:04:33   2022-06-04 09:05:53

Thanks in advance to anyone who can help!
Robert

Re: Window function?

[Robert Stanford]

On Sat, 4 Jun 2022 at 22:56, Thiemo Kellner 
wrote:

> Hi Robert
>
> Interesting problem. I need to think about it.
>
> You need to figure out when Input changes. You can achieve this by using
> lead or lag (depending of the sort direction over start)
> https://www.postgresql.org/docs/current/functions-window.html .
>
After a bit of poking around this seems to get things going in the right
direction.

select row_number() over (order by start)  - row_number() over (partition
by input order by start)

Re: Asking for existence of a GUI frame work similar to Oracle APEX for PostgreSQL

[Robert Treat]

On Fri, Oct 21, 2022 at 1:49 PM Dionisis Kontominas
 wrote:
> It depends on whether you keep the GUI in Oracle APEX. If so then yes, as 
> APEX and tomcat and ORDS are free and need only the basic license for the 
> Oracle DB. Otherwise if a free/low cost low code tool/framework is found then 
> they can be abolished.
>

It's been several years since I have looked at APEX, but my
recollection was that it relied heavily on Oracle packages and
functions, and the few attempts I saw towards porting any of that to
Postgres were abandoned pretty quickly. This generally resulted in
most migrations becoming full-on rewrites into a new platform. Once
you've given that up, its most a matter of finding a new platform that
you want to work with... most of the companies I worked with moved to
things like django or rails, but I suspect something like budibase
and/or similar alternatives might be more what you are angling
towards. Hope that helps.


Robert Treat
https://xzilla.net

default icu locale for new databases (PG15)

[Robert Sjöblom]

Greetings,

When initializing a new database server with a default collation, there 
are a number of different locales available. What's the difference between


1. se-x-icu
2. se-SE-x-icu
3. sv-SE-x-icu

? And, perhaps more importantly, how do I future-proof this so that I'm 
not making a decision today that will make my life worse when upgrading 
from postgres 15 to 16+? For a database with Swedish collation, which 
option is "the best"? Or perhaps "the most foolproof"?


Best regards,
Robert Sjöblom

--
Innehållet i detta e-postmeddelande är konfidentiellt och avsett endast för 
adressaten.Varje spridning, kopiering eller utnyttjande av innehållet är 
förbjuden utan tillåtelse av avsändaren. Om detta meddelande av misstag 
gått till fel adressat vänligen radera det ursprungliga meddelandet och 
underrätta avsändaren via e-post

Logical Replication Sync Validation

[Robert Sjöblom]

Greetings,

We have the following setup:
A pg10 cluster with logical replication to a pg15 cluster, set up with 
the following query:

CREATE PUBLICATION "dbname_pub" FOR ALL TABLES;

We do an initial sync of DDL with pg_dump. The purpose of the 
replication is to perform an online upgrade with minimal downtime.


At the moment where we would switch to pg15 being the primary/lead we 
will stop writes to pg10; at that point we will validate that we are 
fully in sync, tear down pg10 and send writes to pg15. Our question is 
how we can validate our sync status. Given that there have been no DDL 
changes on pg10 (new tables, for instance), would the following check be 
sufficient?


Compare byte diff between WAL LSNs:
SELECT abs(pg_wal_lsn_diff(write_lsn, flush_lsn)) AS lag FROM 
pg_stat_replication;


If the byte diff is 0, I would assume that we're fully in sync. Is this 
understanding correct?


Another idea we've had would be to use CTID to fetch the last row 
(update/insert) in each table on both sides and compare row content, is 
this feasible? Is it safe to rely on CTIDs across logical replication?


best regards,
Robert Sjöblom

--
Innehållet i detta e-postmeddelande är konfidentiellt och avsett endast för 
adressaten.Varje spridning, kopiering eller utnyttjande av innehållet är 
förbjuden utan tillåtelse av avsändaren. Om detta meddelande av misstag 
gått till fel adressat vänligen radera det ursprungliga meddelandet och 
underrätta avsändaren via e-post

Re: CVE-2019-9193 about COPY FROM/TO PROGRAM

[Robert Treat]

On Fri, Apr 5, 2019 at 8:35 AM Jeff Janes  wrote:
> On Tue, Apr 2, 2019 at 11:31 AM Andres Freund  wrote:
>> On 2019-04-02 07:35:02 -0500, Brad Nicholson wrote:
>>
>> > A blog post would be nice, but it seems to me have something about this
>> > clearly in the manual would be best, assuming it's not there already.  I
>> > took a quick look, and couldn't find anything.
>>
>> https://www.postgresql.org/docs/devel/sql-copy.html
>>
>> "Note that the command is invoked by the shell, so if you need to pass
>> any arguments to shell command that come from an untrusted source, you
>> must be careful to strip or escape any special characters that might
>> have a special meaning for the shell. For security reasons, it is best
>> to use a fixed command string, or at least avoid passing any user input
>> in it."
>>
>> "Similarly, the command specified with PROGRAM is executed directly by
>> the server, not by the client application, must be executable by the
>> PostgreSQL user. COPY naming a file or command is only allowed to
>> database superusers or users who are granted one of the default roles
>> pg_read_server_files, pg_write_server_files, or
>> pg_execute_server_program, since it allows reading or writing any file
>> or running a program that the server has privileges to access."
>>
>> Those seem reasonable to me?
>
>
> Yes, but I think that the use of the phrase "default roles" here is 
> unfortunate.  I know it means that the role exists by default, but it is easy 
> to read that to mean they are granted by default.  They should probably be 
> called something like 'built-in roles' or 'system roles'.
>
> And even with the understanding that we are referring to existence, not grant 
> status, "default roles" is still not really correct. If it exists by default, 
> that means I can make it not exist by taking action.  But these roles cannot 
> be dropped.
>
> We don't have 'default functions' or 'default types' in the user-facing 
> documentation.  We shouldn't call these 'default roles'.
>

As someone who likes to break systems in interesting ways, I do find
it interesting that you can actually remove all superuser roles and/or
the superuser bit from all roles (not that I would recommend that to
anyone) but that these roles cannot be removed without some serious
heavy lifting.

Given that, I think I would tend to agree, describing them more
consistently as "system roles" is probably warranted.

Robert Treat
https://xzilla.net
https://credativ.com

Re: pl/pgsql outside the DB, (i.e. on the terminal) possible ?

[Robert Treat]

On Thu, Mar 7, 2024 at 11:26 AM Pavel Stehule  wrote:
> čt 7. 3. 2024 v 16:59 odesílatel Christophe Pettus  napsal:
>> > On Mar 7, 2024, at 06:56, Achilleas Mantzios - cloud 
>> >  wrote:
>> > So, I ask, have there been any efforts to bring PL/PGSQL to the terminal?
>>
>> Strictly speaking, of course, you can use PL/pgSQL from the terminal 
>> already: just use psql, connect to the database, and create and run 
>> functions and procedures as much as you like.
>>
>> If the question is, "Have there been any efforts to implement a PL/pgSQL 
>> interpreter without PostgreSQL?", that's a different and much more complex 
>> problem.  PL/pgSQL uses the PostgreSQL query execution machinery to run 
>> pretty much anything that is not a control structure, and the language is 
>> very focused on interacting with the database.  I doubt it would be worth 
>> anyone's time to try to build some kind of minimal framework that implements 
>> the SPI to allow PL/pgSQL to operate without PostgreSQL.
>
>
> yes
>
> plpgsql cannot exist without Postgres. PL/pgSQL is strongly reduced 
> interpreted Ada language. The gcc compiler supports Ada language.
>
> I found https://bush.sourceforge.net/bushref.html - it is interpret with Ada 
> syntax, but it is better to learn Python - it is easy - with a pretty big 
> library.
>
> free pascal https://www.freepascal.org/ is good compiler and you can write 
> terminal applications too - with Turbo Vision
>

Of course there's a certain amount of personal preference with all
this stuff. I started with basic and really liked it, and then had to
learn pascal and hated it so much that I decided to eschew programming
for years. If you are just trying to learn for fun, I see no reason
why SQL, paired with data in a database, wouldn't be worth spending
time on. Once you're comfortable with that, I like ruby on the command
line and it interacts nicely with databases, and also works well
within the rails console. That said, my son liked lua when he was a
kid, so yeah, there's lots of options, even if plpgsql on the command
line isn't strictly one of them.

Robert Treat
https://xzilla.net

Re: Seeing high query planning time on Azure Postgres Single Server version 11.

[Robert Treat]

It'd be worth checking that your default_statistics_target isn't set
to anything wild, but beyond that, it'd be interesting to look at the
output of vacuum verbose on some of the system catalogs as istm you
might have catalog bloat.

I should also mention that you're running a non-longer-supported
version of Postgres (v11) and not even the latest release of said EOL
version. And if I am not mistaken, "Azure Postgres single server
version" is also deprecated, so you should really focus on getting
upgraded to something more modern.

Robert Treat
https://xzilla.net

On Sat, Mar 9, 2024 at 8:12 AM hassan rafi  wrote:
>
> Postgres version: PostgreSQL 11.18, compiled by Visual C++ build 1800, 64-bit
> relname  
> |relpages|reltuples|relallvisible|relkind|relnatts|relhassubclass|reloptions|pg_table_size|
> -++-+-+---++--+--+-+
> store_seller_products|16007942|843460096|   797033|r  |  16|false 
> |NULL  | 131980795904|
>
>
> relname 
> |relpages|reltuples|relallvisible|relkind|relnatts|relhassubclass|reloptions|pg_table_size|
> ++-+-+---++--+--+-+
> products_inventory_delta| 2847202|259351648|  1606201|r  |   
> 4|false |NULL  |  23330758656|
>
> Peak load (write): 3000 TPS (mostly updates).
> Peak load (read): 800 TPS.
>
>
> On Sat, Mar 9, 2024 at 5:58 PM Ron Johnson  wrote:
>>
>> On Sat, Mar 9, 2024 at 7:18 AM hassan rafi  wrote:
>>>
>>> Hi team,
>>>
>>> We are seeing unusually high query planning times on our Postgres server. I 
>>> am attaching a few query plans.
>>
>>
>> Postgresql version number?
>> Rows in the tables?
>> System load?

Re: Question about PostgreSQL upgrade from version 12 to version 15

[Robert Treat]

On Thu, Mar 21, 2024 at 7:48 AM Alvaro Herrera  wrote:
> On 2024-Mar-21, Joseph Kennedy wrote:
> > I'm planning to upgrade my PostgreSQL database from version 12 to
> > version 15 using pg_upgrade. After completing the upgrade process, I'm
> > curious to know whether it's necessary to reindex the database.
> >
> > Could anyone please clarify whether reindexing is required after
> > completing the upgrade process from PostgreSQL version 12 to version
> > 15 using pg_upgrade?
>
> A reindex(*) is necessary for indexes on textual columns(**), and only
> if you're also changing the underlying OS version(***) such that the
> collation behavior changes.  If you're keeping the database on the same
> OS version, there's no need to reindex anything.
>
> (*) More than reindex actually: you may need to refresh materialized
> views and consider carefully any partition bounds you may have, if you
> have any partition keys that include textual columns.  Even worse: if
> you have FDWs on a Postgres server that queries a table from another
> Postgres server with different collation libraries, it could bit you
> there too.
>
> (**) textual column in this case means anything that is affected by
> collation changes; typically that's things like varchar, text, citext,
> etc, for which a collation other than "C" is explicit or implied.  You
> don't need to do anything for indexes on numbers, dates, geometries,
> etc, nor for textual columns where the index is defined with the C
> collation.
>
> (***) the underlying C library changes collation rules rather frequently
> (especially glibc), because the developers of said libraries don't
> consider that this has any important, permanent impact (but it does
> impact indexes for Postgres).  Most such changes are innocuous, but from
> time to time they make changes that wreak havoc.  If you're using ICU
> collations with your Postgres 12 databases, you may also be affected if
> you upgrade from one ICU version to another.
>
>
> Joe Conway gave a very good presentation on this topic recently:
> https://www.postgresql.eu/events/fosdem2024/schedule/session/5127-collation-challenges-sorting-it-out/
>

As a bonus, if you do decide to reindex, you'll also benefit from the
index deduplication work that was introduced in v13, which should help
reduce disk space and make queries a little faster.


Robert Treat
https://xzilla.net

Re: PostgreSQL as advanced job queuing system

[Robert Treat]

On Fri, Mar 22, 2024 at 8:05 AM Dominique Devienne  wrote:
>
> On Fri, Mar 22, 2024 at 12:58 PM ushi  wrote:
>>
>> i am playing with the idea to implement a job queuing system using 
>> PostgreSQL.
>
>
> FYI, two bookmarks I have on this subject, that I plan to revisit eventually:
> * https://news.ycombinator.com/item?id=20020501
> * 
> https://www.enterprisedb.com/blog/listening-postgres-how-listen-and-notify-syntax-promote-high-availability-application-layer
>
> If others have links to good articles on this subject, or good input to give 
> in this thread, I'd be interested. Thanks, --DD

This is a well worn topic within the postgres community, with a number
of different implementations, but a couple of links that are probably
worth looking at would be:
- https://wiki.postgresql.org/wiki/PGQ_Tutorial, probably the first
queue system that gained wide adoption
- https://brandur.org/river, a new queue system based on postgres/go,
which also has a link to an article about why the authors had ditched
postgres based queueing in favor of redis some years before which is
worth a read to understand some of the issues that Postgres has as the
basis for a queue system.

And yeah, I suspect this may become a hot topic again now that Redis
is moving away from open source:
https://redis.com/blog/redis-adopts-dual-source-available-licensing/

Robert Treat
https://xzilla.net

Re: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE

[Robert Haas]

On Mon, Jul 8, 2024 at 6:08 PM Tom Lane  wrote:
> >> Hmm, if that check doesn't require INHERIT TRUE I'd say it's
> >> a bug.
>
> > The code doesn't support that claim.
>
> That doesn't make it not a bug.  Robert, what do you think?  If this
> is correct behavior, why is it correct?

Correct is debatable, but it's definitely intentional. I didn't think
that referencing a group in pg_hba.conf constituted either (a) the
group inheriting the privileges of the role -- which would make it
governed by INHERIT -- or (b) the group being able to SET ROLE to the
role -- which would make it controlled by SET. I guess you're arguing
for INHERIT which is probably the more logical of the two, but I'm not
really sold on it. I think the pg_hba.conf matching is just asking
whether X is in set S, not whether S has the privileges of X.

For contemporaneous evidence of my thinking on this subject see
https://www.postgresql.org/message-id/ca+tgmobheyynw9vrhvolvd8odspbjuu9cbk6tms6owd70hf...@mail.gmail.com
particularly the paragraph that starts with "That's it".

--
Robert Haas
EDB: http://www.enterprisedb.com

Re: Planet Postgres and the curse of AI

[Robert Treat]

On Tue, Aug 20, 2024 at 8:33 AM Greg Sabino Mullane  wrote:
>
> On Tue, Jul 23, 2024 at 12:45 PM Avinash Vallarapu 
>  wrote:
>>
>> However, I do agree with Lawrence that it is impossible to prove whether it 
>> is written by AI or a human.
>> AI can make mistakes and it might mistakenly point out that a blog is 
>> written by AI (which I know is difficult to implement).
>
>
> Right - I am not interested in "proving" things, but I think a policy to 
> discourage overuse of AI is warranted.
>
>> People may also use AI generated Images in their blogs, and they may be 
>> meaningful for their article.
>> Is it only the content or also the images ?  It might get too complicated 
>> while implementing some rules.
>
>
> Only the content, the images are perfectly fine. Even expected, these days.
>
>>
>> Ultimately, Humans do make mistakes and we shouldn't discourage people 
>> assuming it is AI that made that mistake.
>
>
> Humans make mistakes. AI confidently hallucinates.
>

I think this is a key point, and one that we could focus on for
purposes of discouragement. Ie.  "Blogs that are found to repeatedly
post incorrect information and/or AI style hallucinations may be
restricted from contributing to the planet postgres feed. This will be
determined on a case by case basis."  While it is likely impossible to
come up with a set of rules that will satisfy some of the more
legalistic folks among us, this would be a simple warning that would
at least encourage folks to make sure they aren't posting bad
information and leave a door open for enforcement if needed. And yes,
this assumes that the folks running planet will enforce if needed,
though I don't think it requires heavy policing at this point.

Robert Treat
https://xzilla.net

Re: Backward compat issue with v16 around ROLEs

[Robert Haas]

n.
Prior to v16, this principle applied to grants of everything except
role; now, it also applies to role grants. Whether that's correct is
an arguable point, but it seems very strange to me to argue that role
grants should work differently from every other type of grant in the
system, and it does have some nice properties. But that means that the
anti-circularity provisions that we apply in other cases also need to
be applied to roles. Otherwise, in your example, if the ddevienne role
were removed, dd_admin and dd_owner would retain the ability to
administer each other even though that grant would now have no source.
That administrative authority would have come from ddevienne
originally but, by making a set of circular grants, dd_admin and
dd_owner could arrange to retain that privilege even after ddevienne
was gone. We now forbid that just as we do for other object types.

However, it seems like we might be able to fix this by just making the
code smarter. Maybe there's a problem that I'm not seeing, but if the
boss grants a privilege to alice and alice grants it to bob and bob
grants it back to alice and then the boss revokes the privilege, why
can't we figure out that alice no longer has a source for that
privilege *aside from the one involved in the cycle* and undo the
reciprocal grants that bob and alice made to each other? Right now I
believe we just ask "is the number of sources that alices has for this
privilege still greater than zero" which only works if there are no
cycles but maybe we can do better. We'd probably need to think
carefully about concurrency issues, though, and whether pg_dump is
smart enough to handle this case. Also, there are separate code paths
for role grants and non-role grants, and since I went to a lot of
trouble to make them work the same way, I'd really prefer it if we
didn't go back to having them work differently...

-- 
Robert Haas
EDB: http://www.enterprisedb.com

how to set permission, so I can run pg_dumd in a cron job

[robert rottermann]

Hi Friends

I would like to have a cronjob creating a dump of a db.

I am on a ubuntu 18 lts, potgres v10.

I have a user robert with superuser db permission.

a database "mydb" of which I would like to to a nightly dump.


I tried to set thing in hba_conf like this:

# Database administrative login by Unix domain socket
local   all postgres peer

# TYPE  DATABASE    USER    ADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
host    all all 127.0.0.1/32 trust

and I create a /root/.pgpass file with this content


hostname:port:database:username:password
localhost:5432:mydb:robert:



but still I get:

root@elfero:~# pg_dump  -U robert -d mydb > dumps/mydb.sql
pg_dump: [archiver (db)] connection to database "mydb" failed: FATAL:  Peer 
authentication failed for user "robert"



can you please give me a hand

thanks

robert

Re: how to set permission, so I can run pg_dumd in a cron job

[robert rottermann]

Adrin, thanks

On 13.03.21 17:23, Adrian Klaver wrote:

On 3/13/21 8:16 AM, robert rottermann wrote:

Hi Friends

I would like to have a cronjob creating a dump of a db.

I am on a ubuntu 18 lts, potgres v10.

I have a user robert with superuser db permission.

a database "mydb" of which I would like to to a nightly dump.


I tried to set thing in hba_conf like this:

# Database administrative login by Unix domain socket
local   all postgres peer

# TYPE  DATABASE    USER    ADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
host    all all 127.0.0.1/32 trust

and I create a /root/.pgpass file with this content


hostname:port:database:username:password
localhost:5432:mydb:robert:



but still I get:

root@elfero:~# pg_dump  -U robert -d mydb > dumps/mydb.sql
pg_dump: [archiver (db)] connection to database "mydb" failed: FATAL:  Peer 
authentication failed for user "robert"



can you please give me a hand


1) Did you have the server reload the conf files after making the changes?

yes I did


2) Is there an entry for something like:

local   all all  peer

before the lines you show above?


no

I added the whol pg_conf at the end.

There is one thing particulare with the installation.

I have postgresql 9.5 and 10.0 on the box.

now when I check

pg_dump -V
pg_dump (PostgreSQL) 9.5.24

root@elfero:~# ll /usr/bin/pg_dump
lrwxrwxrwx 1 root root 37 Nov 13  2019 /usr/bin/pg_dump -> 
../share/postgresql-common/pg_wrapper*


when I switch to user postgres, and the us psql, I get the following:

root@elfero:~# su postgres
postgres@elfero:/root$ psql -d  elfero
could not change directory to "/root": Permission denied
psql (10.16 (Ubuntu 10.16-0ubuntu0.18.04.1), server 9.5.24)
Type "help" for help.

looks, as if I have a mess with this two instances.


How can I best clean that up.

I need only PostgreSQL 10

thanks again

robert



# PostgreSQL Client Authentication Configuration File
# ===
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file. A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access. Records take one of these forms:
#
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain
# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
# plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", "replication", a
# database name, or a comma-separated list thereof. The "all"
# keyword does not match "replication". Access to replication
# must be enabled in a separate record (see example below).
#
# USER can be "all", a user name, a group name prefixed with "+", or a
# comma-separated list thereof. In both the DATABASE and USER fields
# you can also write a file name prefixed with "@" to include names
# from a separate file.
#
# ADDRESS specifies the set of hosts the record matches. It can be a
# host name, or it is made up of an IP address and a CIDR mask that is
# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
# specifies the number of significant bits in the mask. A host name
# that starts with a dot (.) matches a suffix of the actual host name.
# Alternatively, you can write an IP address and netmask in separate
# columns to specify the set of hosts. Instead of a CIDR-address, you
# can write "samehost" to match any of the server's own IP addresses,
# or "samenet" to match any address in any subnet that the server is
# directly connected to.
#
# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",
# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".
# Note that "password" sends passwords in clear text; "md5" or
# "scram-sha-256" are preferred since they send encrypted passwords.
#
# OPTIONS are a set of options for the authentication in the format
# NAME=VALUE. The available options depend on the different
# authentication methods -- refer to the "Client Authentication"
# section in the documentation for a list of which opt

solved: Re: how to set permission, so I can run pg_dumd in a cron job

[robert rottermann]

Thanks again Adrian


On 13.03.21 18:37, Adrian Klaver wrote:

On 3/13/21 9:25 AM, robert rottermann wrote:


Adrin, thanks




can you please give me a hand


1) Did you have the server reload the conf files after making the changes?

yes I did


2) Is there an entry for something like:

local   all all  peer

before the lines you show above?


no

I added the whol pg_conf at the end.

There is one thing particulare with the installation.

I have postgresql 9.5 and 10.0 on the box.

now when I check

pg_dump -V
pg_dump (PostgreSQL) 9.5.24

root@elfero:~# ll /usr/bin/pg_dump
lrwxrwxrwx 1 root root 37 Nov 13  2019 /usr/bin/pg_dump -> 
../share/postgresql-common/pg_wrapper*


when I switch to user postgres, and the us psql, I get the following:

root@elfero:~# su postgres
postgres@elfero:/root$ psql -d  elfero
could not change directory to "/root": Permission denied
psql (10.16 (Ubuntu 10.16-0ubuntu0.18.04.1), server 9.5.24)
Type "help" for help.

looks, as if I have a mess with this two instances.


How can I best clean that up.

I need only PostgreSQL 10


From command line what does pg_lsclusters show?

root@elfero:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory Log 
file
9.5 main    5432 online postgres /mnt/HC_Volume_8755337/postgresql/9.5/main 
/var/log/postgresql/postgresql-9.5-main.log
10  main    5433 down   postgres /var/lib/postgresql/10/main 
/var/log/postgresql/postgresql-10-main.log





Which pg_hba.conf file did you change?  The one in:

/etc/postgresql/9.5/main

or

/etc/postgresql/10/main


To specify a version of pg_dump to use do something like:

pg_dump --cluster 10/main -d some_db -U some_user -p some_port

The important part is the some_port. You will need to use the one specified 
for the instance(cluster) as returned by the pg_lscluster command.


I did in deed change the wrong file

now, I need remove one of the two postgresql versions, but for this I will open 
a new question



thanks and have a nice weekend

Robert





thanks again

robert





# Database administrative login by Unix domain socket
#local all postgres peer
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
q# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5

how to best remove version 10 (and keep version 9.5)

[robert rottermann]

Hi folks

I am running an Ubuntu 18.04 lts remote box.

For some (complicated) reasons I did install PostgreSQL 9.5 alongside the 
already installed V10 instance.


I did set the data_directory to point some disk  “outside” the standard 
/etc/postgresql/9.5/main structure.


Now, what I would like to do is to get rid of one of the two installed 
PostgreSQL versions.


Also there are aliasings like:     /usr/bin/pg_dump -> 
../share/postgresql-common/pg_wrapper*


I would like to avoid them if possible.

Things are running now with version 9.5 so I tend to prefer to keep it.

The whole setup is kind of temporary and will be migrated to the “real thing" 
during this year. So it is not a problem that the PostgreSQL version is dated.



I would be grateful for your help

robert

Re: impact of version upgrade on fts

[Robert Treat]

On Sun, Apr 25, 2021 at 11:27 AM Adrian Klaver
 wrote:
>
> On 4/25/21 5:28 AM, Malik Rumi wrote:
> > Greetings.
> >
> > I am about to do a long overdue upgrade to the latest version, which I
> > believe is 13.2. However, I have full text search in my current install
> > (9.4) and I am wondering if there are any special provisions I need to
> > take to make sure that is not lost in the transition?  If this would be
> > true for any other packages, please advise and or point me to the place
> > in the docs where I can read up on this. I looked and did not see
> > anything, which just proves I didn't know what to look for. Thanks.
>
> FTS used to be a separate extension(tsearch2) before version Postgres
> version 8.3. Since then it has been integrated into the core code, so it
> would not be lost. That being said it would be advisable to read the
> release notes for 9.5 --> 13 to see what changed in the core code.
>

You should also check the release notes / process of any "non-core"
extensions you might be using, for example PostGIS has had a number of
changes and you'll need to upgrade the extension itself to work in the
new version of Postgres. Specifics around that will also depend on how
you instead to run your upgrade process.


Robert Treat
https://xzilla.net

Setting a default for nextval sequence

[Robert Lakes]

I'm attempting to set the default value for a serial column.  I created a
generic function that I am passing a table name as the only parameter.  I
had it working correctly, however, it does not seem to like the sequence
name being the same name for each audit table that is created through the
function.
So I changed the code to include the table name as part of the naming
convention.  Now, I need help on how to alter the serial column the new
value

EXECUTE 'CREATE SEQUENCE '|| t_name ||'tab_id_seq'|| ' OWNED BY '|| t_name
|| '_cdc'||'.table_id';

EXECUTE 'ALTER TABLE ' || quote_ident(t_name || '_cdc') || ' ALTER
COLUMN table_id SET DEFAULT nextval(''tab_id_seq'');';

Setting a serial column with serial object that has a name that is built dynamically

[Robert Lakes]

How do I set a serial column with the next value when the serial object has
a name that is built dynamically?

EXECUTE 'CREATE SEQUENCE '|| *t_name *|| *'id_seq'* || ' OWNED BY '||
t_name || '_cdc'||'.table_id';

EXECUTE 'ALTER TABLE ' || quote_ident(t_name || '_cdc') || ' ALTER
COLUMN table_id SET DEFAULT nextval('*'tab_id_seq'*');';

Re: ERROR: too many dynamic shared memory segments

[Robert Haas]

On Tue, Nov 28, 2017 at 2:32 AM, Dilip Kumar  wrote:
>  I think BitmapHeapScan check whether dsa is valid or not if DSA is not
> valid then it should assume it's non-parallel plan.
>
> Attached patch should fix the issue.

So, create the pstate and then pretend we didn't?  Why not just avoid
creating it in the first place, like this?

I haven't checked whether this fixes the bug, but if it does, we can
avoid introducing an extra branch in BitmapHeapNext.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


no-pstate.patch
Description: Binary data

Re: ERROR: too many dynamic shared memory segments

[Robert Haas]

On Tue, Nov 28, 2017 at 9:45 AM, Dilip Kumar  wrote:
>> I haven't checked whether this fixes the bug, but if it does, we can
>> avoid introducing an extra branch in BitmapHeapNext.
>
> With my test it's fixing the problem.

I tested it some more and found that, for me, it PARTIALLY fixes the
problem.  I tested like this:

--- a/src/backend/access/transam/parallel.c
+++ b/src/backend/access/transam/parallel.c
@@ -279,7 +279,7 @@ InitializeParallelDSM(ParallelContext *pcxt)
  * parallelism than to fail outright.
  */
 segsize = shm_toc_estimate(&pcxt->estimator);
-if (pcxt->nworkers > 0)
+if (pcxt->nworkers > 0 && false)
 pcxt->seg = dsm_create(segsize, DSM_CREATE_NULL_IF_MAXSEGMENTS);
 if (pcxt->seg != NULL)
 pcxt->toc = shm_toc_create(PARALLEL_MAGIC,

That turned out to produce more than one problem.  I find that the
select_parallel test then fails like this:

ERROR:  could not find key 18446744073709486082 in shm TOC at 0x10be98040

The fix for that problem seems to be:

--- a/src/backend/access/transam/parallel.c
+++ b/src/backend/access/transam/parallel.c
@@ -430,7 +430,8 @@ ReinitializeParallelDSM(ParallelContext *pcxt)

 /* Recreate error queues. */
 error_queue_space =
-shm_toc_lookup(pcxt->toc, PARALLEL_KEY_ERROR_QUEUE, false);
+shm_toc_lookup(pcxt->toc, PARALLEL_KEY_ERROR_QUEUE, true);
+Assert(pcxt->nworkers == 0 || error_queue_space != NULL);
 for (i = 0; i < pcxt->nworkers; ++i)
 {
 char   *start;

With that fix in place, I then hit a crash in parallel bitmap heap
scan.  After applying no-pstate.patch, which I just committed and
back-patched to v10, then things look OK.  I'm going to apply the fix
for the error_queue_space problem also.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Where to troubleshoot phpPgAdmin login issues?

[Robert Gordon]

I am getting "Login failed" errors when attempting to log into phpPgAdmin as 
user 'postgres'.   In what file path would I find the relevant log files, to 
try and identify why the login is being rejected?

OS: CentOS 7
PostgreSQL: 9.6.6
pg_hba.conf settings for access:
# TYPE  DATABASEUSERADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all peer
# IPv4 local connections:
#hostall all 127.0.0.1/32ident
#host   all all 127.0.0.1/32md5
hostall all 192.168.101.1/32   md5
# IPv6 local connections:
#hostall all ::1/128 ident
hostall all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication postgrespeer
#hostreplication postgres127.0.0.1/32ident
#hostreplication postgres::1/128 ident

Query optimization with repeated calculations

[Robert McGehee]

Hello,
I have a general query optimization question involving repeated
calculations.

I frequently want to make views that repeat a calculation over and over
again in lots of columns. In the example below, let’s say it’s (a+b), but
we can imagine the calculation being much more complicated.

For example:
CREATE VIEW AS
SELECT (a+b)*c as c1, (a+b)*d as d1, (a+b)*e as e1
FROM table;

My question is, is PostgreSQL "smart" enough to cache this (a+b)
calculation in the above example, or does it recalculate it in each column?
If it recalculates it in each column, would I generally get better
performance by using a subquery to convince PostgreSQL to cache the
result?  For example:

CREATE VIEW AS
SELECT x*c as c1, x*d as d1, x*e as e1
FROM (SELECT (a+b) as x, * FROM table) x;

Or perhaps I could use a CTE? In some instances, I might need 2 or more
subqueries to “cache” all the calculations (if one column depends on
another column that depends on another column), and I feel that sometimes
Postgres makes better optimization decisions when there are not subqueries
involved, but I am not sure.

Obviously I could benchmark everything every single time this comes up, but
there is overhead in that and some kind of rule of thumb or general
guideline would be helpful as it is a frequent question for me.

Thank you!
Robert

Information on savepoint requirement within transctions

[Robert Zenz]

I'm currently doing a small writeup of a bug fix in our framework which involves
savepoints in PostgreSQL (JDBC). However, I have a hard time locating the
documentation regarding this. I mean, from what I can extract from various
sources, PostgreSQL requires to use savepoints if one wants to continue a
transaction after a failed statement, but I can't find where in the
documentation that is stated and documented.

Can somebody point me to the correct location where this is documented and maybe
even explained why that is the case?

Re: Information on savepoint requirement within transctions

[Robert Zenz]

Well, no. What I'm looking for is information on how the transactions behave in
an error case, and why there is the requirement to have a savepoint in place to
be able to continue a transaction after a failed statement.

As far as I'm aware neither PostgreSQL nor OS version do matter for this, I'm
interested in the general behavior of the database. But as I said, I do find a
lot of documentation on transactions in general, but not about their behavior in
an error case.

Your first link is "kinda" what I'm looking for, because it closes with

 > Moreover, ROLLBACK TO is the only way to regain control of a transaction
block that was put in aborted state by the system due to an error, short of
rolling it back completely and starting again.

and I'm looking on more information on *that*.


On 26.01.2018 15:59, Melvin Davidson wrote:
> On Fri, Jan 26, 2018 at 9:47 AM, Robert Zenz 
> wrote:
> 
>> I'm currently doing a small writeup of a bug fix in our framework which
>> involves
>> savepoints in PostgreSQL (JDBC). However, I have a hard time locating the
>> documentation regarding this. I mean, from what I can extract from various
>> sources, PostgreSQL requires to use savepoints if one wants to continue a
>> transaction after a failed statement, but I can't find where in the
>> documentation that is stated and documented.
>>
>> Can somebody point me to the correct location where this is documented and
>> maybe
>> even explained why that is the case?
> 
> 
> You have not specified which version of PostgreSQL you are using (or your
> O/S), but is this the documention you are looking for?
> 
> https://www.postgresql.org/docs/9.6/static/tutorial-transactions.html
> 
> https://www.postgresql.org/docs/9.6/static/sql-savepoint.html
> 
> https://www.postgresql.org/docs/9.6/static/sql-rollback-to.html
> 
> https://www.postgresql.org/docs/9.6/static/sql-release-savepoint.html
> 

Re: Information on savepoint requirement within transctions

[Robert Zenz]

What I'm looking for is more information/documentation on that topic that I can
use as source and link back to (from a blog post).

That last paragraph in your first link is exactly what I meant. Let's start to
clarify things, put into pseudo code:

start transaction
insert into A
insert into B but fail
insert into C
commit

As far as I'm aware, in most other databases this would work like that, even
though one statement has failed, one can continue using this transaction and
actually commit the rows in A and C. In PostgreSQL the use of savepoints is
required:

start transaction
insert into A
create savepoint
insert into B but fail
rollback to savepoint
insert into C
commit

Otherwise the transaction is, after the failed statement, in a state in which it
can not be used anymore. Is that correct?


On 26.01.2018 16:42, Melvin Davidson wrote:
> On Fri, Jan 26, 2018 at 10:32 AM, Robert Zenz 
> wrote:
> 
>> Well, no. What I'm looking for is information on how the transactions
>> behave in
>> an error case, and why there is the requirement to have a savepoint in
>> place to
>> be able to continue a transaction after a failed statement.
>>
>> As far as I'm aware neither PostgreSQL nor OS version do matter for this,
>> I'm
>> interested in the general behavior of the database. But as I said, I do
>> find a
>> lot of documentation on transactions in general, but not about their
>> behavior in
>> an error case.
>>
>> Your first link is "kinda" what I'm looking for, because it closes with
>>
>>  > Moreover, ROLLBACK TO is the only way to regain control of a transaction
>> block that was put in aborted state by the system due to an error, short of
>> rolling it back completely and starting again.
>>
>> and I'm looking on more information on *that*.
>>
>>
>> On 26.01.2018 15:59, Melvin Davidson wrote:
>>> On Fri, Jan 26, 2018 at 9:47 AM, Robert Zenz >>
>>> wrote:
>>>
>>>> I'm currently doing a small writeup of a bug fix in our framework which
>>>> involves
>>>> savepoints in PostgreSQL (JDBC). However, I have a hard time locating
>> the
>>>> documentation regarding this. I mean, from what I can extract from
>> various
>>>> sources, PostgreSQL requires to use savepoints if one wants to continue
>> a
>>>> transaction after a failed statement, but I can't find where in the
>>>> documentation that is stated and documented.
>>>>
>>>> Can somebody point me to the correct location where this is documented
>> and
>>>> maybe
>>>> even explained why that is the case?
>>>
>>>
>>> You have not specified which version of PostgreSQL you are using (or your
>>> O/S), but is this the documention you are looking for?
>>>
>>> https://www.postgresql.org/docs/9.6/static/tutorial-transactions.html
>>>
>>> https://www.postgresql.org/docs/9.6/static/sql-savepoint.html
>>>
>>> https://www.postgresql.org/docs/9.6/static/sql-rollback-to.html
>>>
>>> https://www.postgresql.org/docs/9.6/static/sql-release-savepoint.html
>>>
>>
> Simply put, a SAVEPOINT does not allow you to "continue" a transaction
> after an error.
> What is does is allow you to commit everything up to the SAVEPOINT.
> Everything after
> the SAVEPOINT is not committed. There is no such thing as ROLLBACK THEN
> CONTINUE,
> which is what I think you are looking for.
> 
> Once again, please remember to specify your PostgreSQL version and O/S when
> addressing this forum.
> It helps to clarify solutions for historical purposes.
> 
> 

Re: Information on savepoint requirement within transctions

[Robert Zenz]

On 26.01.2018 17:11, David G. Johnston wrote:
> ​The convention for these lists is to inline or bottom-post.  Top-posting
> is discouraged.

Okay, I'll keep it in mind.

> Here's my take, the docs support this but maybe take some interpretation...
> 
> A SAVEPOINT ​begins what is effectively a sub-transaction without ending
> the main transaction.  If that sub-transaction fails you can throw it away
> (ROLLBACK TO) and pretend that it didn't happen: you are dropped back to
> the point where the savepoint was issued and the main transaction
> re-engaged.
> 
> Its kinda like a try/catch block:
> 
> BEGIN:
> 
> do_stuff
> 
> SAVEPOINT try { lets_fail; this_works; } catch { ROLLBACK TO }
> 
> do_more_stuff
> 
> ​COMMIT;​
> 
> ​As ​long as both do_stuff and do_more_stuff succeed when you commit the
> things that they did will persist.
> 
> The stuff in lets_fail AND this_works, however, will be discarded because
> of the lets_fail failing and this_works belonging to the same
> sub-transaction.
> 
> ​If do_more_stuff depends on lets_fail or this_works succeeding then
> do_more_stuff will ​fail and will cause do_stuff to rollback as well.

Thanks for the detailed explanation, that helps a lot. However, I'm still
looking for "official" documentation on their *requirement* in combination with
failing statements. Documentation, bug report, mailing list discussions,
something like that. In particular I'm interested in the questions:

 * Why are they required in combination with failing statements (when every
other database does an "automatic savepoint and rollback" for a failed 
statement)?
 * When was that behavior chosen? Was it always like that? Was it adopted later?
 * What is the design decision behind it?

There is a lot of information on what savepoints are and how they work (and also
thanks to you I'm now fairly certain I have good grasp on them), but I fail to
locate documentation on these questions.

Fwd: Re: Information on savepoint requirement within transctions

[Robert Zenz]

On 29.01.2018 12:37, Adam Tauno Williams wrote:
> It this statement true?  I very much feel *not*.  This depends on how
> you have set AUTO_COMMIT - and it certainly is not true for
> transactions of multiple statements.

Maybe I should clarify at that point that AUTO_COMMIT is OFF, and yes, that is
how it does behave for me.

> Informix does not do "automatic savepoint and rollback" - you will
> rollback the entire transaction.

Okay, not *all* of them, but *most* as it seems.

Re: Information on savepoint requirement within transctions

[Robert Zenz]

On 29.01.2018 14:36, David G. Johnston wrote:
> ​Those questions would not be answered in user-facing documentation.  You
> can explore the git history and search the far-back mailing list archives if
> you wish to satisfy your curiosity. For me this is how it works - the only 
> question for me is whether I should argue that the behavior should be 
> changed. I do vaguely recall this topic coming up in the recent (couple of 
> years) past...but changing transaction behavior like this is problematic no 
> matter how desirable the new state might be to have (and that's debatable).

From my point of view, no, it shouldn't be changed. It has always been this way
and I find nothing wrong with the approach, it is only something that you need
to be aware of, that's all.

> It may be worth updating the docs here...

I'd vote for that. I would have expected to see this mentioned in the
documentation a little bit more prominent than just a single sentence at the end
of the transaction tutorial. A short section about how the transaction behaves
in an error cases (and what to do) would be nice.

> ...but you have received your official answer - I'm nearly positive I'm right
> and even if I was mistaken most likely I would have been corrected by now. I
> am writing this on a mailing list...
> 
> David J.
> 

Thank you for your time and explanations.

Re: Information on savepoint requirement within transctions

[Robert Zenz]

On 29.01.2018 15:11, Alban Hertroys wrote:
> IMHO, the burden of explaining that is with those RDBMSes that don't
> behave properly:
> 
> If you start a transaction and something goes wrong in the process,
> the logical behaviour is to fail - the user will want to rollback to a
> sane state, doing any more work is rather pointless because of that.
> Allowing a commit at the end is dubious at best.

One could argue that automatically "undoing all broken things" (read: reverting
back to the state before the failing statement was executed) would be a feature
worth having. As far as I recall, that has also been brought up on the mailing
list. Though, I don't care particularly about it. I was just interested in the
documentation.

> That does not exclude PG from documenting this behaviour, but I'd have
> a look at the docs for those other vendors whether they perhaps
> documented their irregular transactional behaviour ;)

Heh, good luck. :)

> You didn't mention which RDBMSes behave like what you expected
> (probably from experience), but I seem to recall Oracle does odd stuff
> like that, as well as issuing a commit to all open transactions when
> any DDL happens or treating NULLs and empty literals as the same
> thing. Just to say that the "big names" aren't without flaws - they're
> kind of hard to fix when users probably depend on their behaviour
> though.

To reiterate my example (to get rid of any misconceptions):

begin transaction
insert row #1
insert row #2 (this fails)
insert row #3
commit

I've tested MySQL/MariaDB, Oracle, H2 and SQLite, all allow to continue a
transaction after a failed statement without user interaction (rows #1 and #3
are in the database after committing). PostgresSQL requires the manual rollback
to savepoint after a failed statement (obviously stores #1 and #3 in the
database if each insert is "wrapped" with a savepoint). MSSQL on the other hand
loses the complete state up to the failed statement and allows the user to
continue to use the transaction like nothing happened (only #3 is inserted when
committing). So, I think we can all agree who's the actually broken one here. ;)

Re: Information on savepoint requirement within transctions

[Robert Zenz]

On 29.01.2018 16:33, Tom Lane wrote:
> That behavior does exist, and so does documentation for it; you're just
> looking in the wrong place.
> 
> Years ago (7.3 era, around 2002) we experimented with a server-side
> GUC variable "AUTOCOMMIT", which switched from the implicit-commit-
> if-you-don't-say-BEGIN behavior to implicitly-issue-BEGIN-so-you-
> have-to-say-COMMIT-explicitly.  That was an unmitigated disaster:
> flipping the setting broke just about all client applications.  After
> spending many months trying to fix just the apps we ship with Postgres,
> and getting pushback from users whose code broke with the alternate
> setting, we gave up and removed the feature.  Instead we set project
> policy that if you want to modify transactional behavior you have to
> do it on the client side, where it doesn't risk breaking other apps.
> Institutional memory around here is very long, so any time proposals to
> change the server or wire-protocol behavior in this area come up, they
> get batted down.
> 
> What we do have though is client-side support for appropriate behaviors.
> In psql, see the AUTOCOMMIT and ON_ERROR_ROLLBACK control variables.
> Other interfaces such as JDBC have their own ideas about how this ought
> to work.

Very interesting. However, I'm talking explicitly about the behavior that occurs
when AUTOCOMMIT is switched off and a statement fails.

Most curiously, you already did such a feature (of what I was talking about)
request in 2007:
https://www.postgresql.org/message-id/flat/11539.1177352713%40sss.pgh.pa.us#11539.1177352...@sss.pgh.pa.us

Re: Information on savepoint requirement within transctions

[Robert Zenz]

On 30.01.2018 03:07, David G. Johnston wrote:
 > ​So, my first pass at this.

Nice, thank you.

 > + These are of particular use for client software to use when executing
 > + user-supplied SQL statements and want to provide try/catch behavior
 > + where failures are ignored.

Personally, I'd reword this to something like this:

 > These are of particular use for client software which is executing
 > user-supplied SQL statements and wants to provide try/catch behavior
 > with the ability to continue to use the transaction after a failure.

Or maybe something like this:

 > These are of particular use for client software which requires
 > fine-grained support over failure behavior within a transaction.
 > They allow to provide a try/catch behavior with the ability
 > to continue to use a transaction after a failure.

Also I'd like to see something like this in the docs at roughly the same 
position:

 > If a failure occurs during a transaction, the transaction enters
 > an aborted state. An aborted or failed transaction cannot be used
 > anymore to issue more commands, ROLLBACK or ROLLBACK TO must be used
 > to regain control of the aborted transaction. A commit issued while
 > the transaction is aborted is automatically converted into a
 > .

I'm not sure about the terminology here, though, because the Transaction
Tutorial (https://www.postgresql.org/docs/9.6/static/tutorial-transactions.html)
speaks of "aborted" transactions, while you use the term "failed" here.

Re: Information on savepoint requirement within transctions

[Robert Zenz]

On 31.01.2018 19:58, David G. Johnston wrote:
> ​Now that I've skimmed the tutorial again I think pointing the reader of
> the SQL Commands there to learn how it works in practice is better than
> trying to explain it in BEGIN and/or SAVEPOINT.

That seems like a good idea, yeah.

> I decided to add a title to the part of SAVEPOINTS and introduce the term
> "Sub-Transaction" there though I'm not married to it - re-wording it using
> only "savepoint" is something that should be tried still.

Technically, it *is* a sub-transaction, Savepoints are just the means to do it.
I think that a sub-transaction is the concept, Savepoint is the implementation.

> A title and a paragraph or two on aborted transaction behavior probably
> should be added as well.

I'd like that. I might be able to type something up, though I'm currently a
little bit short on time, so don't wait for me please.

Just to make sure, you have two typos in there, "61: tranasctions" and "106:
implment". Also I'd like to take the opportunity to agree with Laurenz here,
"pseudo" seems to be misplaced, they *are* sub-transactions.

pgadmin4: not possible to create server

[robert rottermann]

Hi there,

I have installed pgadmin4 locally using its docker image.

this i did running the following command:

docker run -p 80:80 -e "PGADMIN_DEFAULT_EMAIL=admin" -e 
"PGADMIN_DEFAULT_PASSWORD=admin" -d dpage/pgadmin4


I then could access it and log into it.

However all my attempts end in:

Unable to connect to server:

could not connect to server: Connection refused
Is the server running on host "localhost" (::1) and accepting
TCP/IP connections on port 5432?
could not connect to server: Connection refused
Is the server running on host "localhost" (127.0.0.1) and accepting
TCP/IP connections on port 5432?


can anybody give me a hint, what to do?

thanks

robert


in /etc/postgresql/10/main/postgresql.conf

i have:

#--
# CONNECTIONS AND AUTHENTICATION
#--

# - Connection Settings -

listen_addresses = '*'  # what IP address(es) to listen on;
    # comma-separated list of 
addresses;



and this is my /etc/postgresql/10/main/pg_hba.conf

# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all postgres peer

# TYPE  DATABASE    USER    ADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all all trust
# IPv4 local connections:
host    all all 127.0.0.1/32 trust
# IPv6 local connections:
host    all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication all peer
host    replication all 127.0.0.1/32 md5
host    replication all ::1/128 md5

Prepared statements (PREPARE and JDBC) are a lot slower than "normal" ones.

[Robert Zenz]

We are seeing a quite heavy slow down when using prepared statements in 10.1.

I haven't done some thorough testing, to be honest, but what we are having is a
select from a view (complexity of it should not matter in my opinion), something
like this:

prepare TEST (text, int) select * from OUR_VIEW where COLUMNA = $1 and
COLUMNB = $2;

-- Actual test code follows.

-- Takes ~2 seconds.
select * from OUR_VIEW where COLUMNA = 'N' and COLUMNB = 35;

-- Takes ~10 seconds.
execute TEST ('N', 35);

Both return the same amount of rows, order of execution does not matter, these
times are reproducible. If the same select statement is executed through JDBC it
takes roughly 6 seconds (execution time only, no data fetched at that point).
I'm a little bit at a loss here. Is such a slow down "expected", did we simply
miss that prepared statements are slower? Or is there something else going on
that we are simply not aware of?

Re: Why no pg_has_role(..., 'ADMIN')?

[Robert Haas]

On Fri, Sep 20, 2024 at 12:37 PM Laurenz Albe  wrote:
> > But knowing whether DROP ROLE will work,
> > w/o invalidating the current transaction,
> > seems like something quite useful to know now, no?
> >
> > I can query pg_auth_members for admin_option,
> > but only easily for direct membership. Taking into
> > account indirect membership, which I assume applies,
> > is exactly why pg_has_role() exists, no?
>
> That would be a useful addition, yes.

I think this already exists. The full list of modes supported by
pg_has_role() is listed in convert_role_priv_string(). You can do
something like pg_has_role('alice', 'USAGE WITH ADMIN OPTION'). This
is not new: it worked in older releases too, but AFAIK it's never been
mentioned in the documentation.

However, the precise rule for DROP ROLE in v16+ is not just that you
need to have ADMIN OPTION on the role. The rule is:

1. You must have ADMIN OPTION on the target role.
2. You must also have CREATEROLE.
3. If the target role is SUPERUSER, you must be SUPERUSER.

If I'm not wrong, pg_has_role(..., 'USAGE WITH ADMIN OPTION') will
test #1 for you, but not #2 or #3.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Re: Why no pg_has_role(..., 'ADMIN')?

[Robert Haas]

On Fri, Sep 20, 2024 at 2:34 PM Tom Lane  wrote:
> I'm now inclined to add wording within the pg_has_role entry, along
> the lines of
>
> WITH ADMIN OPTION or WITH GRANT OPTION can be added to any of
> these privilege types to test whether ADMIN privilege is held
> (all six spellings test the same thing).

I don't have an opinion about the details, but +1 for documenting it
somehow. I also think it's weird that we have six spellings that test
the same thing, none of which are $SUBJECT. pg_has_role seems a little
half-baked to me...

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Re: How to perform a long running dry run transaction without blocking

[Robert Leach]

>> Anyway, thanks so much for your help.  This discussion has been very useful, 
>> and I think I will proceed at first, exactly how you suggested, by queuing 
>> every validation job (using celery).  Then I will explore whether or not I 
>> can apply the "on timeout" strategy in a small patch.
>> Incidentally, during our Wednesday meeting this week, we actually opened our 
>> public instance to the world for the first time, in preparation for the 
>> upcoming publication.  This discussion is about the data submission 
>> interface, but that interface is actually disabled on the public-facing 
>> instance.  The other part of the codebase that I was primarily responsible 
>> for was the advanced search.  Everything else was primarily by other team 
>> members.  If you would like to check it out, let me know what you think: 
>> http://tracebase.princeton.edu 
> 
> I would have to hit the books again to understand all of what is going on 
> here.

It's a mass spec tracing database.  Animals are infused with radio labeled 
compounds and mass spec is used to see what the animal's biochemistry turns 
those compounds into.  (My undergrad was biochem, so I've been resurrecting my 
biochem knowledge, as needed for this project.  I've been mostly doing RNA and 
DNA sequence analysis since undergrad, and most of that was prokaryotic.

> One quibble with the Download tab, there is no indication of the size of the 
> datasets. I generally like to know what I am getting into before I start a 
> download. Also, is there explicit throttling going on? I am seeing 
> 10.2kb/sec, whereas from here 
> https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page I downloaded a 
> 47.65M file at 41.9MB/s

Thank you!  Not knowing the download size is exactly a complaint I had.  That 
download actually uses my advanced search interface (in browse mode).  There is 
the same issue with the download buttons on the advanced search.  With the 
streaming, we're not dealing with temp files, which is nice, at least for the 
advanced search, but we can't know the download size that way.  So I had wanted 
a progress bar to at least show progress (current record per total).  I could 
even estimate the size (an option I explored for a few days).  Eventually, I 
proposed a celery solution for that and I was overruled.

As for the download in the nav bar, we have an issue to change that to a 
listing of actual files broken down by study (3 files per study).  There's not 
much actual utility from a user perspective for downloading everything anyway.  
We've just been focussed on other things.  In fact, we have a request from a 
user for that specific feature, done in a way that's compatible with curl/scp.  
We just have to figure out how to not have to CAS authenticate each command, 
something I don't have experience with.

Re: How to perform a long running dry run transaction without blocking

[Robert Leach]

>> I'd run across a stackexchange answer 
>> <https://stackoverflow.com/a/75037925/2057516> that suggested setting a 
>> statement timeout (specific to postgres) in a django view.  If I did that 
>> for the validation view, it wouldn't necessarily 100% correspond to a 
>> blocking issue, but since the overall goal is the prevent a gateway timeout, 
>> I'm fine with that.
> 
> When you do a SET statement_timeout is session/connection specific.
> 
> For what that means in Django see:
> 
> https://docs.djangoproject.com/en/5.1/ref/databases/
> 
> Personally I think this will introduce more complexity then it's worth.
> 
> Further down the page at above link it shows how you can use a psycopg(2) 
> pool, but that is another layer added to an already complex setup.

TBF, adding a celery strategy to the codebase to queue jobs is already 
comparatively very complex to start out.  It's a pretty heavy handed solution 
to what seems to be a relatively rare occurrence (though rather recurrent when 
it does happen).  By that metric, I would think that queuing jobs at all is too 
complex to be worth it.  So I'm not sure that catching a timeout exception to 
queue a job instead of queuing the job at the outset is that much more complex. 
 I can just create a view that inherits from the current view (to which the 
statement timeout is applied) which is *not* subject to the statement timeout 
when I want to queue a longer running validation due to the block.  I think I 
should be able to redirect the request to that derived class view.  Not sure 
exactly how ATM, but it shouldn't be more than 1 line of code/statement, I 
would think.

Regarding statement timeouts in general, I found a pretty great article 
<https://hakibenita.com/9-django-tips-for-working-with-databases#statement-timeout>
 that suggests setting site-wide statement timeouts as a general rule, which I 
think makes a lot of sense.  In fact, some of the pages on our site (that I 
didn't write) are not going to scale well as the DB grows.  I already feel like 
the samples page is way too slow.  They should probably all gracefully handle 
timeouts so that users don't encounter raw 500 errors.  So whether or not I 
decide to "queue on statement timeout", I think using statement timeouts is 
worthwhile.

I take your point though.  How many more lines of code do you think would make 
the strategy worthwhile?  I'd be willing to bet I could keep it under 20 
compared to straight up queuing every validation.  In fact, I'm leaning toward 
implementing your suggestion of queuing everything as a "phase 1" of this 
endeavor, and then implement my proposed "queue on timeout" idea as phase 2 
(perhaps as a configurable option - at least until I'm satisfied it works well).

>> I don't know if that stack exchange suggestion does what it claims to do or 
>> not.  I haven't tried it.  But they key claim of interest here is that the 
>> timeout is specific to the view.  I.e. any other views or background 
>> processes wouldn't be subject to the timeout.  I'm suspicious as to whether 
>> this is actually true.  My novice understanding of Django's cursors is that 
>> it maintains a pool of connections, and I'm not 
> 
> Actually cursors are attributes of connections.

Yeah, thus my suspicion that it may not apply to only the view in the example.  
I suspected their "proof" that it didn't affect other views was due to randomly 
getting a connection to which that statement timeout had not yet been applied.  
*However*, I just read this article 
<https://medium.com/nerd-for-tech/djangos-database-connection-management-ad39a298d2e3>
 which describes Django's connection management and I realized that Django's 
default behavior (which is what we use) is to create a new connection for every 
request, so I think that, with the caveat that it wouldn't work with persistent 
connections, the stack exchange claim is actually correct.

I would just have to figure out how best to apply the timeout to everything 
except those queued validations.  And I think maybe a context manager might be 
the simplest way to do it.

Anyway, thanks so much for your help.  This discussion has been very useful, 
and I think I will proceed at first, exactly how you suggested, by queuing 
every validation job (using celery).  Then I will explore whether or not I can 
apply the "on timeout" strategy in a small patch.

Incidentally, during our Wednesday meeting this week, we actually opened our 
public instance to the world for the first time, in preparation for the 
upcoming publication.  This discussion is about the data submission interface, 
but that interface is actually disabled on the public-facing instance.  The 
other p

Re: How to perform a long running dry run transaction without blocking

[Robert Leach]

> Have you considered a validation app? Have it read the inputs and look in db 
> for conflicts, rather than attempt the insert. Zero transactions necessary


I did consider that about a year or two ago when I first conceived the data 
validation interface.  Doing that now would solve the problem of concurrent 
validations blocking one another, but selects would still get blocked if their 
result is pending the outcome of a load running in the background.  If I 
mitigate that issue by running those loads over night on a schedule, I still 
lose out on the benefits of having the loading code do the validation for me...

I would have to explicitly find and report on problems that the load exceptions 
currently do for me, without any extra code.

So what I'm saying is that the data being validated is inter-dependent.  There 
are about a dozen different loading scripts (one for each sheet of the uploaded 
excel file) whose runs are orchestrated by a master load script that ensures 
they are run in the right order so that the interdependent data can be checked. 
 For example, these are some relative orders of what needs to be loaded so that 
data can be validated:

Study > Animals
Tracers > Infusates > Animals
Treatments > Animals
Tissues > Samples

The Animal load script would fail if the new data in (the tables) Study, 
Tracers, Infusates, and Treatments aren't inserted, because it links to those 
newly created records.  And there's no way to detect problems in those new 
relationships in the unchanged database if they aren't inserted.  That's what 
doing this all in a transaction, and actually doing the inserts (for which I 
use Django `get_or_create` method calls) provides.

In other words, I would have to save and explicitly check the inter-related 
sheet data in data structures independent of the database in order to find the 
equivalent of (for example) `ObjectDoesNotExist` errors that originate from the 
database.  Right now, I get those errors caught "for free".  All I have to do 
is tell the user what sheet/row/column is related to that error.  And it saves 
me the overhead of having to maintain synchronicity between separate validation 
code and loading code when the loading code changes.

Robert William Leach
Research Software Engineer
133 Carl C. Icahn Lab
Lewis-Sigler Institute for Integrative Genomics
Princeton University
Princeton, NJ 08544

Re: How to perform a long running dry run transaction without blocking

[Robert Leach]

>>> The load to the development server does no validation?
>>> 
>>> If so what is the purpose?
>>> 
>>> The background processes are other validation runs?
>> It's the same code that executes in both cases (with or without the 
>> `--validate` flag).  All that that flag does is it (effectively) raises the 
>> dry run exception before it leaves the transaction block, so it always 
>> validates (whether the flag is supplied or not).
> 
> More for my sake then anything else, why do the load to the development 
> server at all if the production load is the only one that counts?

The software is still in a new major version beta.  We're adding features and 
fixing bugs.  It's not unusual to encounter a new bug, fix it on dev to get the 
load to work, then deploy a point release on prod.  And that means repeated 
load attempts that interfere with the validation interface.  Besides, beyond 
this, we're planning on a separate staging database that dev effectively now 
is.  Sometimes, a curator only finds a technical data issue after the initial 
load while browsing the newly loaded data on the dev site.

>> So the load doesn't fail until the end of the run, which is inefficient from 
>> a maintenance perspective.  I've been thinking of adding a `--failfast` 
>> option for use on the back end.  Haven't done it yet.  I started a load 
>> yesterday in fact that ran 2 hours before it buffered an exception related 
>> to a newly introduced bug.  I fixed the bug and ran the load again.  It 
>> finished sometime between COB yesterday and this morning (successfully!).
> 
> Alright I am trying to reconcile this with from below, 'The largest studies 
> take just under a minute'.

The context of the 'The largest studies take just under a minute' statement is 
that it's not loading the hefty/time-consuming raw data.  It's only validating 
the metadata.  That's fast (5-60s).  And that data is a portion of the 
transaction in the back-end load.  There are errors that validation can miss 
that are due to not touching the raw data, and in fact, those errors are 
addressed by curators editing the excel sheets.  That's why it's all in the 
load transaction instead of loaded separately, but those problems are somewhat 
rare (and we currently have a new feature in the design phase that should 
almost completely eliminate those issues).

>>> Seems you are looking for some sort of queuing system.
>>> 
>>> What are the time constraints for getting the validation turned around.
>> I have considered a queuing system, though when I previously floated a proof 
>> of concept using celery, I was informed it was too much.  Though, at the 
>> time, all I was trying to do was a progress bar for a query stats feature.  
>> So proposing celery in this instance may get more traction with the rest of 
>> the team.
>> Most of the small validation processes finish in under a dozen seconds.   
>> The largest studies take just under a minute.  I have plans to optimize the 
>> loading scripts that hopefully could get the largest studies down to a dozen 
>> seconds.  If I could do that, and do the back end loads in off-peak hours, 
>> then I'd be willing to suffer the rare timeouts from concurrent validations. 
>>  The raw data loads will still likely take a much longer time.
> 
> This is where I get confused, probably because I am not exactly sure what 
> constitutes validation. My sense is that involves a load of data into live 
> tables and seeing what fails PK, FK or other constraints.
> 
> If that is the case I am not seeing how the 'for real' data load would be 
> longer?

The validation skips the time-consuming raw data load.  That raw data is 
collectively hundreds of gigs in size and could not be uploaded on the 
validation page anyway.  The feature I alluded to above that would make errors 
associated with the raw data almost completely eliminated is one where the 
researcher can drop the raw data folder into the form and it just walks the 
directory to get all the raw data file names and relative paths.  It's those 
data relationships whose validations are currently skipped.

> At any rate I can't see how loading into a live database multiple sets of 
> data while operations are going on in the database can be made conflict free. 
> To me  it seems the best that be done is:
> 
> 1) Reduce chance for conflict by spreading the actions out.
> 
> 2) Have retry logic that deals with conflicts.

I'm unfamiliar with retry functionality, but those options sound logical to me 
as a good path forward, particularly using celery to spread out validations and 
doing the back end loads at night (or using some sort of fast dump/load).  The 
thing that bothers me about the celery solution is that most of the time, 2 
users validating different data will not block, so I would be making users wait 
for no reason.  Ideally, I could anticipate the block and only at that point, 
separate those validations.

This brings up a question though about a possibility I sus

Re: How to perform a long running dry run transaction without blocking

[Robert Leach]

> Please reply to list also.
> Ccing list

👍🏻

>> They enter the data in an excel spreadsheet containing about a dozen 
>> inter-related worksheets, named, for example: Study, Animals, Samples, 
>> Tissues, Treatments, Sequences
> 
> Long term is there a thought to have them enter directly into database where 
> validation can happen in real time

No, not really.  Sample metadata tends to be an afterthought to researchers.  
They have it in their notebooks and getting them to enter it at all is like 
pulling teeth.  The validation interface actually has a bunch of other features 
I haven't mentioned that streamline the process for them.  Before it gets to 
actually validating the data, it tries to lighten the manual burden on the 
researchers (and help with consistent nomenclature) by pulling sample names out 
of the raw files, massaging them, and filling those in along with a mass of 
common data that is used to populate drop-downs in the excel columns to avoid 
researcher typos and value variants.

Having everything work with excel actually made the site more attractive to the 
researchers, because they're comfortable with it and use it already, so it 
lowered the bar for using our software.

Besides, we don't trust the users enough to enter data unsupervised.  There are 
a lot of aspects of the data that cannot be automatically validated and involve 
experimental parameters that are adjacent to the purpose of our site.  We have 
curators that need to look at everything to ensure consistency, and looking at 
all the data in context is necessary before any of it is entered.

That said, back in the aughts, I wrote a perl cgi site for a toxin and 
virulence factor database that used a web interface for data entry and achieved 
the curation goal by saving a form of all inter-related data.  The submit 
button sent that form to a list of curators who could approve the insert/update 
and make it actually happen.  I think I had actually suggested that form of 
data entry when this current project first started, but I was overruled.  
However, in this project, the equivalent procedure would be per-sample, and 
you'd lose out on the overall context.  It's an interesting challenge, but I 
think we're pretty committed now on this file load path.

>>> Where are the background processes loading data to?
>> We first run our loads on a development server with a separate copy of the 
>> database, but then we re-run those same loads on the production server, 
>> where users perform their validations.
> 
> The load to the development server does no validation?
> 
> If so what is the purpose?
> 
> The background processes are other validation runs?

It's the same code that executes in both cases (with or without the 
`--validate` flag).  All that that flag does is it (effectively) raises the dry 
run exception before it leaves the transaction block, so it always validates 
(whether the flag is supplied or not).

So the load doesn't fail until the end of the run, which is inefficient from a 
maintenance perspective.  I've been thinking of adding a `--failfast` option 
for use on the back end.  Haven't done it yet.  I started a load yesterday in 
fact that ran 2 hours before it buffered an exception related to a newly 
introduced bug.  I fixed the bug and ran the load again.  It finished sometime 
between COB yesterday and this morning (successfully!).

>> One of the thoughts I'd had to work around the problem was to somehow dump 
>> the data from the development load and load it onto production in some sort 
>> of scheduled downtime or something.  However, even if we do that, I'm 
>> concerned that multiple users concurrently validating different submissions 
>> would encounter this blocking issue, and since those validations can take 
>> (on the upper end) just under a minute, it's enough for at least 1 user to 
>> encounter a timeout.  I have not yet proven that can happen, but based on my 
>> reading of the postgres documentation, it seems logical.
> 
> Seems you are looking for some sort of queuing system.
> 
> What are the time constraints for getting the validation turned around.

I have considered a queuing system, though when I previously floated a proof of 
concept using celery, I was informed it was too much.  Though, at the time, all 
I was trying to do was a progress bar for a query stats feature.  So proposing 
celery in this instance may get more traction with the rest of the team.

Most of the small validation processes finish in under a dozen seconds.  The 
largest studies take just under a minute.  I have plans to optimize the loading 
scripts that hopefully could get the largest studies down to a dozen seconds.  
If I could do that, and do the back end loads in off-peak hours, then I'd be 
willi

How to perform a long running dry run transaction without blocking

[Robert Leach]

I've been trying to solve this problem in Django and I've finally decided after 
over a year going down this path that there's no way to solve it in Django (in 
a database agnostic fashion). So I'm thinking that I need to explore a 
Postgres-specific solution. FYI, I'm not a database expert. I just know enough 
to get myself into trouble like this problem.

Let me try and distill all the back story down to an oversimplified explanation:

I created a scientific data submission validation interface that helps 
researchers compile their data submissions to our database. To do this, I 
decided to add a `--validate` option to the load scripts that essentially 
raises a dry run exception at the end of a load run so that the data is all 
rolled back before leaving the atomic transaction block.

This validation interface skips the raw data load step, which is the heftiest, 
most long running, part and generally finishes in well under a minute.

The interface works fantastically well. It rolls back problematic data in 
smaller transactions and buffers those errors for a final report that the user 
can work to fix in their data files and retry validation until all their 
problems are solved and the data is ready to load.

The problem is that if we have a long running load going on in the background 
(which can take hours) and during that load, a user tries to validate a new 
submission that contains some overlapping common data (e.g. a new tissue type 
or protocol that is currently being loaded) or perhaps they are trying to 
validate data added to an existing submission that is being partially loaded, 
that validation process gets blocked and the validation interface encounters a 
gateway timeout.

I had tried changing the isolation level to both repeatable read and 
serializable, but the hang can still occur (though serializable seems to avoid 
hangs in some cases that repeatable read does not).

My initial interpretation of the isolation level documentation was that 
transactions would not be aware of what is happening in other transactions and 
that if there was a problem, you would only ever get a serialization error at 
the end when the data is committed, but after encountering the hang yesterday, 
I realized that the "snapshot" that the second transaction gets includes locks 
already established by the first transaction.

I had hoped that since my validation interface would never commit, it would 
never have a serialization error or hang.

What options are available that might allow this all to work as I intended? Is 
there some way to tell the validation process transaction to assume that any 
pending changes from another transaction will succeed?

I have lots of ideas on how to mitigate these occurrences, but I don't want to 
bias any potentially helpful responses. Am I screwed from my early decision to 
use the loading code to validate data submissions?

Re: Yet more ROLE changes in v18 beta1???

[Robert Haas]

Hi Dominique,

Thanks for testing. This time, whatever is going wrong here is
probably not my fault, because I don't think I changed anything in
this area for v18. Actually, I'm unaware of anyone else having made
significant changes either, but that could very easily be a case of me
not paying enough attention. I think we might need to know more about
what exactly happened in order to track it down.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

Re: PgBouncer-Postgres : un supported startup parameter statement_timeout

[Robert Sjöblom]

There's an option to ignore parameters in pgbouncer's config. Here's an SO
answer that gives an example: https://stackoverflow.com/a/36911794

On Wed, 16 Jul 2025, 09:19 KK CHN,  wrote:

> Hi,
>
> I am facing an issue with PgBouncer-Postgres setup,
>
> *PgBouncer (PgBouncer 1.23.1 running on VM instance FreeBSD 14.2) and
> PostgreSQL DB cluster on a virtual machine (PG version 16.0,  RHEL 9.4) *.
>
>
>
> My application backend is nodeJS which throws the following Error in the
> nodejs  console log: when connecting through pgbouncer to the backend
> database server...
>
> ERROR thrown:
>
> my-node>* Failed to connect to PostgreSQL database transaction_db :
> unsupported startup parameter: statement_timeout *{"code":"08P01",
> "length":69, "name": "error", xx xx
> my-node> error:[object Object ] {"timestamp":"15-07-2025 10:14:26"} x x xx
>
>
> Note:   The nodejs apps  earlier directly establishing connections to the
>  PostgreSQL DB ( no such errors)  now redirected via   Pgbouncer throws
> this error.
>
>
>
> *The config parameters for  pgbouncer as follows(correct me if any
> mistakes made)*
> [root@pgbouncer ~]# grep ^[^';;;'] /usr/local/etc/pgbouncer.ini
> [databases]
> transaction_db = host=dbmain.mydomain.com port=5444 dbname=transaction_db
>
> [users]
> [pgbouncer]
> Logfile = /var/log/pgbouncer/pgbouncer.log
> pidfile = /var/run/pgbouncer/pgbouncer.pid
> listen_addr = *
> listen_port = 5444
> auth_type = md5
> auth_file = /usr/local/etc/pgbouncer.users
> admin_users = adminuser
> stats_users = adminuser,
> pool_mode = transaction
> server_reset_query = DISCARD ALL
> server_reset_query_always = 1
> ignore_startup_parameters = extra_float_digits  // I have added this also
> or can I add these options too in the following line  ?
> ;; ignore_startup_parameters = extra_float_digits, options,
> statement_timeout, idle_in_transaction_session_timeout  // doubt options
> supported in latest versions ?
>
> max_client_conn = 5000   // can I give this much high value ?
> default_pool_size = 20
> min_pool_size = 10
> reserve_pool_size = 10
> reserve_pool_timeout = 5
> max_db_connections = 100
> max_user_connections = 30
> server_lifetime = 3600
> server_idle_timeout = 600 // is this a low value or need to be  increased ?
> [root@pgbouncer ~]#
>
>
>
> The config params of  N*odeJS application which uses nodejs connection
> pooling in* code as follows
>
> the Node JS application using the following  nodejs pooling configurations
> for the application level
>
> *cat app_10072025/config/pg-pool-config.js*
> ...
>
>
>
>
>
>
>
>
> *const poolOptions = {max: 10,min: 2,idleTimeoutMillis:
> 60,   //Idle for 5MinconnectionTimeoutMillis: 1,  //Reconnect
> 10secstatement_timeout: 6,//Query executiion 1 minacquire:
> 2,maxUses: 1000 //reconnect after 1000 queries};*
>
> const pools = {
>
> transaction_db: new Pool({
> connectionString: `postgresql://${DB_USER}:${DB_PASSWORD}@
> ${DB_HOST}:${DB_PORT}/${DB_NAME_TRANSACTION_DB}`,
> ...poolOptions,
> }),
> };
>
> ..
>
> Any  hints   and suggestions in the config params are most welcome.
>
> Thank you,
> Krishane
>

-- 
Innehållet i detta e-postmeddelande är konfidentiellt och avsett endast för 
adressaten.Varje spridning, kopiering eller utnyttjande av innehållet är 
förbjuden utan tillåtelse av avsändaren. Om detta meddelande av misstag 
gått till fel adressat vänligen radera det ursprungliga meddelandet och 
underrätta avsändaren via e-post

Re: ZFS filesystem - supported ?

[Robert L Mathews]

On 10/25/21 1:40 PM, Mladen Gogala wrote:
This is probably not the place 
to discuss the inner workings of snapshots, but it is worth knowing that 
snapshots drastically increase the IO rate on the file system - for 
every snapshot. That's where the slowness comes from.


I have recent anecdotal experience of this. I experiment with using 
Btrfs for a 32 TB backup system that has five 8 TB spinning disks. 
There's an average of 8 MBps of writes scattered around the disks, which 
isn't super high, obviously.


The results were vaguely acceptable until I created a snapshot of it, at 
which point it became completely unusable. Even having one snapshot 
present caused hundreds of btrfs-related kernel threads to thrash in the 
"D" state almost constantly, and it never stopped doing that even when 
left for many hours.


I then experimented with adding a bcache layer on top of Btrfs to see if 
it would help. I added a 2 TB SSD using bcache, partitioned as 1900 GB 
read cache and 100 GB write cache. It made very little difference and 
was still unusable as soon as a snapshot was taken.


I did play with the various btrfs and bcache tuning knobs quite a bit 
and couldn't improve it.


Since that test was a failure, I then decided to try the same setup with 
OpenZFS on a lark, with the same set of disks in a "raidz" array, with 
the 2 TB SSD as an l2arc read cache (no write cache). It easily handles 
the same load, even with 72 hourly snapshots present, with the default 
settings. I'm actually quite impressed with it.


I'm sure that the RAID, snapshots and copy-on-write reduce the maximum 
performance considerably, compared to ext4. But on the other hand, it 
did provide the performance I expected to be possible given the setup. 
Btrfs *definitely* didn't; I was surprised at how badly it performed.


--
Robert L Mathews, Tiger Technologies, http://www.tigertech.net/