Re: Permission for not Django app to do Write and Read

[Peter J. Holzer]

On 2019-08-24 13:22:38 +0200, Luca Ferrari wrote:
> On Sat, Aug 24, 2019 at 11:53 AM Peter Wainaina  wrote:
> >
> > Thanks much for the response. This is what I mean am a database
  ^
> > administrator for a production company and the product owner doesn't
^
> > want me to be able to either read or write information that will
> > come from Django application.
> 
> I personally hate this kind of setup, because it does not make any
> sense to me that a developer must setup an application that must
> interact with a database that the developer himself cannot interact
> with.

Peter is the DBA here, not the developer. I don't think you can prevent
the DBA from seeing the data in the database - except by encrypting it
(as you suggested below). I guess it would be possible to write a Django
backend which implements an encryption layer, but getting decent
performance out of it might be ... challenging.

> However, keep it simple: define a django user, assign each object to
> such user, revoke any permission from public.
> And then let's the product owner setup a password.

You can still access the tables as postgres.

Of course you could set up another database instance where you don't
have DBA privileges, but then the product owner has to trust the system
administrator instead of the DBA. At some point you have to trust
somebody or do everything yourself.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

A question aboout postgresql-server-dev versions

[stan]

I am using Postgresql version 11 on Ubuntu 18.04. I am considering using an
extension called libphonenumber. It needs Postgresql-server-dev to build. I
found postgresql-server-dev-all in the list of available packages, BUT when
I ask apt-get to lad it, a dependency is postgresql-server-dev-10. 

Seems wrong, is this OK?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: A question aboout postgresql-server-dev versions

[Jeff Janes]

On Sun, Aug 25, 2019 at 8:34 AM stan  wrote:

> I am using Postgresql version 11 on Ubuntu 18.04. I am considering using an
> extension called libphonenumber. It needs Postgresql-server-dev to build. I
> found postgresql-server-dev-all in the list of available packages, BUT when
> I ask apt-get to lad it, a dependency is postgresql-server-dev-10.
>
> Seems wrong, is this OK?
>

If it is offering 10 but not 11, then that is probably because 11 is
already installed and doesn't need to be installed again.

What do happens if you do "sudo apt install  postgresql-server-dev-11" ?

Cheers,

Jeff

What is the difference between creating a type, and a domain ?

[stan]

Subject basically says it all.

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Having difficulties partitionning with jsonb

[Jeff Janes]

On Thu, Aug 22, 2019 at 5:41 PM ouellet marc-andre <
ouellet_marcan...@hotmail.com> wrote:

> CREATE TABLE test ( id integer, data jsonb ) Partition by range (( data
> #>> '{info,time}' ));
>
> CREATE TABLE test_part1 PARTITION OF test
> FOR VALUES FROM ('3') TO ('4');
>
> INSERT INTO test VALUES (1,'{"info":[{"time":39814.0,"value":2}, {"time
> ":39815.0,"value":3}]}');
>
>
The partitioning expression yields NULL on the given input.

select '{"info":[{"time":39814.0,"value":2},
{"time":39815.0,"value":3}]}'::jsonb #>> '{info,time}';
 ?column?
--
 (null)
(1 row)

Maybe you meant '{info,0,time}', or maybe you meant '{info,1,time}'.  Or
maybe you meant something else. You will have to explain yourself.

Cheers,

Jeff

>

Re: What is the difference between creating a type, and a domain ?

[Tom Lane]

stan  writes:
> Subject basically says it all.

A domain is a kind of type ... but it's not the only kind of type.

https://www.postgresql.org/docs/current/domains.html

regards, tom lane

BDR: moving a node

[E]

Hello,

As a preamble, I'm running PostgreSQL 9.4 BDR on a Debian 9.

I'm moving a node to a new host. This is the node from which I started 
BDR and had a second replicate join it.


I browsed the list archive, Googled all I could, found some threads and 
Github issues, but came up with a grant total of no concrete answers.


When I say "moving", I mean I'm copying the entire filesystem with 
PostgreSQL shut down, so I assume my data integrity is taken care of. 
All I imagine I need to do is replace the DSN on the second node, which 
in my carelessness, I set up using an IP address and not an FQDN.
I looked up Node Management Functions in the doc,   but cannot come up 
with the final set of instructions to perform an update of the DSN.


What is the process to update the DSN? I assume I'll have to relay the 
changes in my pg_hba.conf, but do not understand, and don't want to 
tinker, with BDR before obtaining some educated advice.


I apologize if my question comes across as dumb. I understand I have to 
update bdr.bdr_connections, but how, and what do I restart? What about 
the server that was moved, any updates needed there?


thanks for your help!

Re: Postgres SQLSTATE[08006] [7] timeout expired

[Condor]

On 25-08-2019 02:54, Adrian Klaver wrote:

On 8/24/19 3:42 PM, Howard Wells wrote:
I have three servers behind a load balancer and a fourth server solely 
for Postgres 10 database that is not behind the load balancer.  All 
four are behind the same firewall, with port 5432 open.


I have a simple browser-based html form to submit email addresses, 
usernames and passwords; the form submits through a jQuery program 
that calls a php program.


I can navigate the browser to the IP address of the server that hosts 
the Postgres database and submit records to the database with no 
problems.  When I navigate to the site's URL (which goes through the 
load balancer), I always get "SQLSTATE[08006] [7] timeout expired" 
after a short delay -- in other words, no connection.


The php program is very simple:

$dsn = vsprintf('pgsql:host=%s;port=%s;dbname=%s;user=%s;password=%s', 
[

     'host' => 'xxx.xx.xx.xx',
     'port' => '5432',
     'dbname' => '[DBNAME]',
     'user' => '[USERNAME]',
     'password' => '[PASSWORD]',
]);

echo 'HTTP Referer ' . $_SERVER['HTTP_REFERER'];
echo PHP_EOL;

echo 'IP Address ' . $_SERVER["REMOTE_ADDR"];
echo PHP_EOL;

try{
// create a PostgreSQL database connection

   echo "I'm here now";
   echo PHP_EOL;

//ini_set("default_socket_timeout", 300);
$pdo = new PDO($dsn);
$pdo->setAttribute(PDO::ATTR_TIMEOUT, 300);

// display a message if connected to the PostgreSQL successfully
if($pdo){
echo "Connected to the database successfully!";
echo PHP_EOL;
}
}catch (PDOException $e){
// report error message
echo $e->getMessage();
exit();
}

$pdo = null;

?>

The echo messages are simply debugging progress messages for the 
browser's dev console.


The pg_hba.conf has these lines enabled:

pg_hba.conf:
host    all [username]   0.0.0.0/0   trust
host    all all 0.0.0.0/0   md5
host    all all  ::/0   md5
host    all all  all    md5

The $_SERVER["REMOTE_ADDR"]; line shown in my PHP program above always 
comes back with the address of the load balancer.


I'm new to Postgres, so any help with this connection problem will be 
very appreciated.  Thanks a lot.


In addition to what Rob suggested, what do you see or not in the
Postgres logs when you try the connections(both direct to db and to
load balancer).



You can use strace or tcpdump to see what's happened. First check to see 
did the balncer is up and port is opened. Next if the balancer is bind 
on port 5432 and pgsql server is started on same ip without port to be 
changed this can't be happened unless the pgsql server is started on 
socket without inet connection. If the balancer is up and they are not 
in the same server then it's seems the problem is between balancer and 
sql server. The balancer is not connected to pg server with some reason 
/firewall, permissions, users and so on, you need to check them - 
balancer logs, firewall rules /.


Regards,
HS

pgcharts on Ubuntu 18.04 ?

[stan]

If anyone has installed the package for this on 18.04, and gotten it working,
I'd like to discuss what you had to do.

When I use apt-get to install it, I get a a couple of errors:

Warning: The home directory /var/log/pgcharts you specified can't be accessed: 
No
such file or directory

Not creating home directory `/var/log/pgcharts'



-es a /vr/log directory, and since apt-get is running as root, it should be
able to create that directory.

In any case, when I try to send my browser to the specified port on
localhost after doing an install there is nothing running on that port.

I would also be interested in any comments about how well this package
works. I am not opposed to building it from source, and working through the
install process, if that is needed, if it is useful.


"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: pgcharts on Ubuntu 18.04 ?

[Roger De Four]

Apparently can't be done on Ubuntu - cf.
https://github.com/dimitri/pgcharts


On Sun, 25 Aug 2019 at 12:47, stan  wrote:

> If anyone has installed the package for this on 18.04, and gotten it
> working,
> I'd like to discuss what you had to do.
>
> When I use apt-get to install it, I get a a couple of errors:
>
> Warning: The home directory /var/log/pgcharts you specified can't be
> accessed: No
> such file or directory
>
> Not creating home directory `/var/log/pgcharts'
>
>
>
> -es a /vr/log directory, and since apt-get is running as root, it should be
> able to create that directory.
>
> In any case, when I try to send my browser to the specified port on
> localhost after doing an install there is nothing running on that port.
>
> I would also be interested in any comments about how well this package
> works. I am not opposed to building it from source, and working through the
> install process, if that is needed, if it is useful.
>
>
> "They that would give up essential liberty for temporary safety deserve
> neither liberty nor safety."
> -- Benjamin Franklin
>
>
>

Re: Postgres SQLSTATE[08006] [7] timeout expired

[Howard Wells]

I have enabled logging.  I chose the highest level of recording so I would have 
the best chance of finding out what's wrong.  My error log is written to a .csv 
file.  I initiated a browser-based transaction to insert records into a 
database.  That transaction produced 1,299 records, with what appear to be 
repeating blocks.  I don't see anything in here that specifically tells me why 
the client is not able to connect.

The last 26 lines look like representative of the transaction blocks:

StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGR, 
xid/subid/cid: 0/1/0
pg_statistic: vac: 0 (threshold 129), anl: 0 (threshold 89)
pg_type: vac: 0 (threshold 125), anl: 0 (threshold 88)
pg_authid: vac: 0 (threshold 51), anl: 0 (threshold 51)
pg_attribute: vac: 0 (threshold 566), anl: 0 (threshold 308)
pg_proc: vac: 0 (threshold 629), anl: 0 (threshold 339)
pg_class: vac: 0 (threshold 118), anl: 0 (threshold 84)
pg_index: vac: 0 (threshold 77), anl: 0 (threshold 63)
pg_opclass: vac: 0 (threshold 77), anl: 0 (threshold 63)
pg_am: vac: 0 (threshold 51), anl: 0 (threshold 51)
pg_amop: vac: 0 (threshold 192), anl: 0 (threshold 121)
pg_amproc: vac: 0 (threshold 132), anl: 0 (threshold 91)
pg_cast: vac: 0 (threshold 94), anl: 0 (threshold 72)
pg_namespace: vac: 0 (threshold 51), anl: 0 (threshold 51)
pg_database: vac: 0 (threshold 50), anl: 0 (threshold 50)
pg_tablespace: vac: 0 (threshold 50), anl: 0 (threshold 50)
CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGR, 
xid/subid/cid: 0/1/0
shmem_exit(0): 1 before_shmem_exit callbacks to make
shmem_exit(0): 7 on_shmem_exit callbacks to make
proc_exit(0): 2 callbacks to make
exit(0)
shmem_exit(-1): 0 before_shmem_exit callbacks to make
shmem_exit(-1): 0 on_shmem_exit callbacks to make
proc_exit(-1): 0 callbacks to make
reaping dead processes
server process (PID 15930) exited with exit code 0

My problem now is I don't know what to look for to find out why the client is 
not able to connect within the timeout period.  The dev console (Chrome) always 
ends with a display of the echo messages up to the lines:

$pdo = new PDO($dsn);
$pdo->setAttribute(PDO::ATTR_TIMEOUT, 300);

And the final line is the Chrome dev console is SQLSTATE[08006] [7] timeout 
expired.

The load balancer is not the issue; if I point the browser to the IP address of 
one of the servers behind the load balancer (bypassing the load balancer), the 
same thing happens.  Only when I go to the IP address of the server with the 
Postgres database will the transaction complete, and it happens quickly.

Thanks for any tips on what to look for in the csv log file to explain why the 
browser-based PHP connection does not complete

Howard

‐‐‐ Original Message ‐‐‐
On Sunday, August 25, 2019 9:36 AM, Condor  wrote:

> On 25-08-2019 02:54, Adrian Klaver wrote:
>
> > On 8/24/19 3:42 PM, Howard Wells wrote:
> >
> > > I have three servers behind a load balancer and a fourth server solely
> > > for Postgres 10 database that is not behind the load balancer.  All
> > > four are behind the same firewall, with port 5432 open.
> > > I have a simple browser-based html form to submit email addresses,
> > > usernames and passwords; the form submits through a jQuery program
> > > that calls a php program.
> > > I can navigate the browser to the IP address of the server that hosts
> > > the Postgres database and submit records to the database with no
> > > problems.  When I navigate to the site's URL (which goes through the
> > > load balancer), I always get "SQLSTATE[08006] [7] timeout expired"
> > > after a short delay -- in other words, no connection.
> > > The php program is very simple:
> > >  > > echo 'Hello ' . htmlspecialchars($_POST["firstname"]) . '!' ;
> > > echo PHP_EOL;
> > > $dsn = vsprintf('pgsql:host=%s;port=%s;dbname=%s;user=%s;password=%s',
> > > [
> > >     'host' => 'xxx.xx.xx.xx',
> > >     'port' => '5432',
> > >     'dbname' => '[DBNAME]',
> > >     'user' => '[USERNAME]',
> > >     'password' => '[PASSWORD]',
> > > ]);
> > > echo 'HTTP Referer ' . $_SERVER['HTTP_REFERER'];
> > > echo PHP_EOL;
> > > echo 'IP Address ' . $_SERVER["REMOTE_ADDR"];
> > > echo PHP_EOL;
> > > try{
> > > // create a PostgreSQL database connection
> > > echo "I'm here now";
> > >   echo PHP_EOL;
> > > //ini_set("default_socket_timeout", 300);
> > > $pdo = new PDO($dsn);
> > > $pdo->setAttribute(PDO::ATTR_TIMEOUT, 300);
> > > // display a message if connected to the PostgreSQL successfully
> > > if($pdo){
> > > echo "Connected to the database successfully!";
> > > echo PHP_EOL;
> > > }
> > > }catch (PDOException $e){
> > > // report error message
> > > echo $e->getMessage();
> > > exit();
> > > }
> > > $pdo = null;
> > > ?>
> > > The echo messages are simply debugging progress messages for the
> > > browser's dev console.
> > > The pg_hba.conf has these lines enabled:
> > > pg_hba.conf:
> > > host    all [username]   0.0.0.0/0   trust
> > > host  

Re: Postgres SQLSTATE[08006] [7] timeout expired

[Adrian Klaver]

On 8/25/19 11:46 AM, Howard Wells wrote:

I have enabled logging.  I chose the highest level of recording so I would have 
the best chance of finding out what's wrong.  My error log is written to a .csv 
file.  I initiated a browser-based transaction to insert records into a 
database.  That transaction produced 1,299 records, with what appear to be 
repeating blocks.  I don't see anything in here that specifically tells me why 
the client is not able to connect.


Huh? The client could not connect, but it produced 1299 records. You 
will need to be more specific or clear about what you consider a client 
and a connection?


To get to the bottom of this we will need:

1) The load balancer in use? I know you say below that the balancer is 
not at issue, but that remains to be seen in my mind.


2) How the balancer is set up.

3) What is the client?

4) What is the server?

5) How does the server connect to the database?




The last 26 lines look like representative of the transaction blocks:

StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGR, 
xid/subid/cid: 0/1/0
pg_statistic: vac: 0 (threshold 129), anl: 0 (threshold 89)
pg_type: vac: 0 (threshold 125), anl: 0 (threshold 88)
pg_authid: vac: 0 (threshold 51), anl: 0 (threshold 51)
pg_attribute: vac: 0 (threshold 566), anl: 0 (threshold 308)
pg_proc: vac: 0 (threshold 629), anl: 0 (threshold 339)
pg_class: vac: 0 (threshold 118), anl: 0 (threshold 84)
pg_index: vac: 0 (threshold 77), anl: 0 (threshold 63)
pg_opclass: vac: 0 (threshold 77), anl: 0 (threshold 63)
pg_am: vac: 0 (threshold 51), anl: 0 (threshold 51)
pg_amop: vac: 0 (threshold 192), anl: 0 (threshold 121)
pg_amproc: vac: 0 (threshold 132), anl: 0 (threshold 91)
pg_cast: vac: 0 (threshold 94), anl: 0 (threshold 72)
pg_namespace: vac: 0 (threshold 51), anl: 0 (threshold 51)
pg_database: vac: 0 (threshold 50), anl: 0 (threshold 50)
pg_tablespace: vac: 0 (threshold 50), anl: 0 (threshold 50)
CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGR, 
xid/subid/cid: 0/1/0
shmem_exit(0): 1 before_shmem_exit callbacks to make
shmem_exit(0): 7 on_shmem_exit callbacks to make
proc_exit(0): 2 callbacks to make
exit(0)
shmem_exit(-1): 0 before_shmem_exit callbacks to make
shmem_exit(-1): 0 on_shmem_exit callbacks to make
proc_exit(-1): 0 callbacks to make
reaping dead processes
server process (PID 15930) exited with exit code 0

My problem now is I don't know what to look for to find out why the client is 
not able to connect within the timeout period.  The dev console (Chrome) always 
ends with a display of the echo messages up to the lines:

$pdo = new PDO($dsn);
$pdo->setAttribute(PDO::ATTR_TIMEOUT, 300);

And the final line is the Chrome dev console is SQLSTATE[08006] [7] timeout 
expired.

The load balancer is not the issue; if I point the browser to the IP address of 
one of the servers behind the load balancer (bypassing the load balancer), the 
same thing happens.  Only when I go to the IP address of the server with the 
Postgres database will the transaction complete, and it happens quickly.

Thanks for any tips on what to look for in the csv log file to explain why the 
browser-based PHP connection does not complete

Howard

‐‐‐ Original Message ‐‐‐
On Sunday, August 25, 2019 9:36 AM, Condor  wrote:


On 25-08-2019 02:54, Adrian Klaver wrote:


On 8/24/19 3:42 PM, Howard Wells wrote:


I have three servers behind a load balancer and a fourth server solely
for Postgres 10 database that is not behind the load balancer.  All
four are behind the same firewall, with port 5432 open.
I have a simple browser-based html form to submit email addresses,
usernames and passwords; the form submits through a jQuery program
that calls a php program.
I can navigate the browser to the IP address of the server that hosts
the Postgres database and submit records to the database with no
problems.  When I navigate to the site's URL (which goes through the
load balancer), I always get "SQLSTATE[08006] [7] timeout expired"
after a short delay -- in other words, no connection.
The php program is very simple:
 'xxx.xx.xx.xx',
     'port' => '5432',
     'dbname' => '[DBNAME]',
     'user' => '[USERNAME]',
     'password' => '[PASSWORD]',
]);
echo 'HTTP Referer ' . $_SERVER['HTTP_REFERER'];
echo PHP_EOL;
echo 'IP Address ' . $_SERVER["REMOTE_ADDR"];
echo PHP_EOL;
try{
// create a PostgreSQL database connection
echo "I'm here now";
   echo PHP_EOL;
//ini_set("default_socket_timeout", 300);
$pdo = new PDO($dsn);
$pdo->setAttribute(PDO::ATTR_TIMEOUT, 300);
// display a message if connected to the PostgreSQL successfully
if($pdo){
echo "Connected to the database successfully!";
echo PHP_EOL;
}
}catch (PDOException $e){
// report error message
echo $e->getMessage();
exit();
}
$pdo = null;
?>
The echo messages are simply debugging progress messages for the
browser's dev console.
The pg_hba.conf has these lines enabled:
pg_hba.conf:
host    all [username]   

For SELECT statement (just a reading one, no 'FOR UPDATE'), is COMMIT or ROLLBACK preferred?

[David Wall]

Using the latest PostgreSQL, does it matter if my code does a ROLLBACK 
or a COMMIT on an non-modifying SELECT statement?  My impression is 
they'd be the same as nothing is changed and therefore there's nothing 
to commit or rollback, but wondered if there was any difference in how 
they are processed by Postgres?


Thanks,
David

Re: Postgres SQLSTATE[08006] [7] timeout expired

[Rob Sargent]

> 
>  console.
> The pg_hba.conf has these lines enabled:
> pg_hba.conf:
> hostall [username]   0.0.0.0/0   trust
> hostall all 0.0.0.0/0   md5
> hostall all  ::/0   md5
> hostall all  all

Who is encrypting the password?
> 

Re: Postgres SQLSTATE[08006] [7] timeout expired

[Howard Wells]

Thanks for the replies.  I am getting the information requested by Adrian 
Klaver.  Rob Sargent, I am going to temporary enable full trust because the 
password authentication could be the issue.  Then I'll write back.

Howard

‐‐‐ Original Message ‐‐‐
On Sunday, August 25, 2019 12:20 PM, Rob Sargent  wrote:

> >
>
> > > > > > console.
> > > > > > The pg_hba.conf has these lines enabled:
> > > > > > pg_hba.conf:
> > > > > > host all [username] 0.0.0.0/0 trust
> > > > > > host all all 0.0.0.0/0 md5
> > > > > > host all all ::/0 md5
> > > > > > host all all all
>
> Who is encrypting the password?
>
> >

Re: Postgres SQLSTATE[08006] [7] timeout expired

[Howard Wells]

I enabled "host  all all 0.0.0.0/0 trust" in pg_hba.conf and restarted 
Postgres, but I still get the same behavior.  It looks like password 
authentication is not the issue.  I enabled MD5, but I didn't know I would have 
to do anything else; from this test, that doesn't seem to be the problem.  I'm 
getting the load balancer info.  Thanks again.


Howard

‐‐‐ Original Message ‐‐‐
On Sunday, August 25, 2019 12:48 PM, Howard Wells  wrote:

> Thanks for the replies. I am getting the information requested by Adrian 
> Klaver. Rob Sargent, I am going to temporary enable full trust because the 
> password authentication could be the issue. Then I'll write back.
>
> Howard
>
> ‐‐‐ Original Message ‐‐‐
> On Sunday, August 25, 2019 12:20 PM, Rob Sargent robjsarg...@gmail.com wrote:
>
> > > > > > >
> >
> > > > > > > console.
> > > > > > > The pg_hba.conf has these lines enabled:
> > > > > > > pg_hba.conf:
> > > > > > > host all [username] 0.0.0.0/0 trust
> > > > > > > host all all 0.0.0.0/0 md5
> > > > > > > host all all ::/0 md5
> > > > > > > host all all all
> >
> > Who is encrypting the password?
> >
> > >

Re: pgcharts on Ubuntu 18.04 ?

[Johann 'Myrkraverk' Oskarsson]

On Mon, Aug 26, 2019 at 1:40 AM Roger De Four
 wrote:
>
> Apparently can't be done on Ubuntu - cf.  https://github.com/dimitri/pgcharts

There is nothig there that indicates Ubuntu is a problem platform.

Have you tried a git clone and used the provided Makefile yourself?

--
Johann

 I'm not from the internet, I just work there.

Re: For SELECT statement (just a reading one, no 'FOR UPDATE'), is COMMIT or ROLLBACK preferred?

[Rob Sargent]

> On Aug 25, 2019, at 1:09 PM, David Wall  wrote:
> 
> Using the latest PostgreSQL, does it matter if my code does a ROLLBACK or a 
> COMMIT on an non-modifying SELECT statement?  My impression is they'd be the 
> same as nothing is changed and therefore there's nothing to commit or 
> rollback, but wondered if there was any difference in how they are processed 
> by Postgres?
> 
> Thanks,
> David
> 
> 
> 
In interactive psql, both issue a warning that there is no current transaction. 
 What is your auto-commit setting and how is your code sent to the server?

Re: For SELECT statement (just a reading one, no 'FOR UPDATE'), is COMMIT or ROLLBACK preferred?

[David Wall]

On 8/25/19 12:40 PM, Rob Sargent wrote

On Aug 25, 2019, at 1:09 PM, David Wall  wrote:

Using the latest PostgreSQL, does it matter if my code does a ROLLBACK or a 
COMMIT on an non-modifying SELECT statement?  My impression is they'd be the 
same as nothing is changed and therefore there's nothing to commit or rollback, 
but wondered if there was any difference in how they are processed by Postgres?

Thanks,
David




In interactive psql, both issue a warning that there is no current transaction. 
 What is your auto-commit setting and how is your code sent to the server?



We are accessing it via JDBC, and so we SQL via PreparedStatements 
against a Connection, and the connection is not auto-commit.  By 
default, the connection has a BEGIN TRANSACTION in place, so after all 
requests we do, we need to commit/rollback.  The main issue is that if 
we do a SELECT and get a ResultSet that has no rows, if we do a commit 
or a rollback, it seems reasonable that these are identical as no 
changes were made.  My inclination is to do a Connection.commit() on the 
connection because it wasn't in error or anything even if no rows were 
found, but wondered if a Connection.rollback() has any difference 
(positive/negative) in such a scenario.  We have SELECT sql statements 
that sometimes do a rollback after such queries because even though no 
rows was found is fine for SQL, it may be an issue in the application 
that expects there to be at least one row.  So we're trying to determine 
if there's actually any difference between commit/rollback after SELECT 
statements (with rows returned or not), a bit like if there's any 
difference for an UPDATE statement that returns zero rows were updated.

Re: For SELECT statement (just a reading one, no 'FOR UPDATE'), is COMMIT or ROLLBACK preferred?

[Tom Lane]

David Wall  writes:
> ... So we're trying to determine 
> if there's actually any difference between commit/rollback after SELECT 
> statements (with rows returned or not), a bit like if there's any 
> difference for an UPDATE statement that returns zero rows were updated.

They're different code paths, but I'd expect any performance difference
to be at the noise level; if nothing happened in the transaction then
no WAL traffic will be emitted in either case.

A more useful thing to think about, IMO, is this: if your app thinks
that the statement had no side-effects but actually it did (maybe it
called a volatile function that did something), would you want those
effects to be persisted or not?

regards, tom lane

Re: Postgres SQLSTATE[08006] [7] timeout expired

[Howard Wells]

I solved this problem.  All four servers are behind a firewall, but port 5432 
was not open on the firewall.  When I opened 5432, the problem disappeared.

Thanks to Rob Sargent, Adrian Klaver and Condor for your help.

Howard

‐‐‐ Original Message ‐‐‐
On Sunday, August 25, 2019 12:20 PM, Rob Sargent  wrote:

> >
>
> > > > > > console.
> > > > > > The pg_hba.conf has these lines enabled:
> > > > > > pg_hba.conf:
> > > > > > host all [username] 0.0.0.0/0 trust
> > > > > > host all all 0.0.0.0/0 md5
> > > > > > host all all ::/0 md5
> > > > > > host all all all
>
> Who is encrypting the password?
>
> >

Re: Postgres SQLSTATE[08006] [7] timeout expired

[Adrian Klaver]

On 8/25/19 2:59 PM, Howard Wells wrote:

I solved this problem.  All four servers are behind a firewall, but port 5432 
was not open on the firewall.  When I opened 5432, the problem disappeared.


Glad you solved. Confused as to how:

1) From first post:
"All four are behind the same firewall, with port 5432 open."

2) A subsequent post:

"Only when I go to the IP address of the server with the Postgres 
database will the transaction complete, and it happens quickly."





Thanks to Rob Sargent, Adrian Klaver and Condor for your help.

Howard

‐‐‐ Original Message ‐‐‐
On Sunday, August 25, 2019 12:20 PM, Rob Sargent  wrote:






console.
The pg_hba.conf has these lines enabled:
pg_hba.conf:
host all [username] 0.0.0.0/0 trust
host all all 0.0.0.0/0 md5
host all all ::/0 md5
host all all all


Who is encrypting the password?










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

Re: BDR: moving a node

[Andreas Kretschmer]

Am 25.08.19 um 18:11 schrieb E:
What is the process to update the DSN? I assume I'll have to relay the 
changes in my pg_hba.conf, but do not understand, and don't want to 
tinker, with BDR before obtaining some educated advice.


I apologize if my question comes across as dumb. I understand I have 
to update bdr.bdr_connections, but how, and what do I restart? What 
about the server that was moved, any updates needed there?


the safest way would be to part that node from the cluster and rejoin it 
afterwards.


You have (at least) to check all replication slots, please check also 
pg_replication_identifier (please use pg_replication_identifier_drop() 
to drop the node ) , bdr.bdr_nodes and bdr.bdr_connections. Please keep 
in mind, BDR version 1 will be out of support soon, and PG 9.4 as well. 
Current development of BDR is BDR 3.6.x., it works with PG 10+ .




Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: psql \copy hanging

[Arnaud L.]

Le 22/08/2019 à 16:23, Arnaud L. a écrit :

Le 22/08/2019 à 16:21, Adrian Klaver a écrit :

The only thing I can think of at the moment is to put the offending line
in a separate script and see what happens.


Then I think I'll first try to switch the line orders. I'll execute this
action last and see if it changes anything. If it doesn't, I'll break
the script into smaller parts.


It ran fine over the week-end so I'll consider this as temporary glitch. 
It's always dissatisfying not to know what has happened, but I'm not 
going to try too hard to reproduce this.


Thanks for helping and sorry for the noise !

Cheers
--
Arnaud