Cursors for PGJDBC queries

[Rashmi V Bharadwaj]

Hi,I am trying to set the fetch size for my ResultSet to avoid Out of Memory exception. I have created the Statement with ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY and ResultSet.HOLD_CURSORS_OVER_COMMIT and I've also disabled auto commit as mentioned in the link Getting results based on a cursor. I am still getting Out of memory error. My SQL query is a simple SELECT statement to retrieve all the rows from a table. According to https://postgrespro.com/list/thread-id/2370772, the holdability must be CLOSE_CURSORS_AT_COMMIT. Could you please confirm this is a requirement?Thanks,Rashmi

Re: Cursors for PGJDBC queries

[Luca Ferrari]

On Thu, Aug 1, 2019 at 9:10 AM Rashmi V Bharadwaj  wrote:
> I am trying to set the fetch size for my ResultSet to avoid Out of Memory 
> exception. I have created the Statement with ResultSet.TYPE_FORWARD_ONLY, 
> ResultSet.CONCUR_READ_ONLY and ResultSet.HOLD_CURSORS_OVER_COMMIT and I've 
> also disabled auto commit as mentioned in the link Getting results based on a 
> cursor. I am still getting Out of memory error. My SQL query is a simple 
> SELECT statement to retrieve all the rows from a table. According to 
> https://postgrespro.com/list/thread-id/2370772, the holdability must be 
> CLOSE_CURSORS_AT_COMMIT. Could you please confirm this is a requirement?

Hard to say without more information. Could it be something you need
to set on the jvm like
However, you should post on the JDBC mailing list
.

Re: Cursors for PGJDBC queries

[Luca Ferrari]

On Thu, Aug 1, 2019 at 9:30 AM Luca Ferrari  wrote:
> Hard to say without more information. Could it be something you need
> to set on the jvm like

sorry, I was intended to write
-Xms256m
-Xmx1024m
to adjust the jvm memory limits.
Again I believe that the jdbc mailing list is the right place to ask
for such a problem.

Luca

Re: Cursors for PGJDBC queries

[Thomas Kellerer]

Rashmi V Bharadwaj schrieb am 01.08.2019 um 09:10:
> I am trying to set the fetch size for my ResultSet to avoid Out of
> Memory exception. I have created the Statement with
> ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY and
> ResultSet.HOLD_CURSORS_OVER_COMMIT and I've also disabled auto commit
> as mentioned in the link Getting results based on a cursor
> .
> I am still getting Out of memory error. My SQL query is a simple
> SELECT statement to retrieve all the rows from a table. According to
> https://postgrespro.com/list/thread-id/2370772, the holdability must
> be CLOSE_CURSORS_AT_COMMIT. Could you please confirm this is a
> requirement?

To rule out the obvious: you did call Statement.setFetchSize() before calling 
executeQuery()? 


Using

  connection.setAutoCommit(false);
  Statement stmt = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
ResultSet.CONCUR_READ_ONLY);
  stmt.setFetchSize(100);
  ResultSet rs = stmt.executeQuery("");
  while (rs.next()) {
...
  }

works perfectly for me, even with really large results.

Re: adding more space to the existing server

[Adrian Klaver]

On 7/31/19 3:21 PM, Julie Nishimura wrote:

Hello postgres folks,

We're tossing around the idea of upgrading a replicated postgres cluster 
(37 dbs) by breaking the replication, adding different size (larger) 
data disks to the hot-spare, then turning replication back on, letting 
it fully populate, then breaking replication, making the standby the 
primary, upgrade the disks on the other system, bring it back up, 
replicate backwards until fully replicated then failing-back to the 
original primary. Is this feasible?


To make a determination more information would be helpful:

1) What is the method of replication?

2) What is the expected downtime for the disk upgrades?

3) Dependent on 1). Is there sufficient storage to hold the data until 
it can be replicated back?


4) How is the data on the existing disks going to get to the new disks?



Our current size is 22 tb, and it is 97% full
(PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit)



Thank you for your suggestions



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

PGAdmin4.11.1 on Ubuntu 18.04

[Tony Shelver]

I am getting an 'able oid' when querying any tables using the Query tool.
When using the view / edit data option, everything works fine.

I came across this
<<<

The problem is due to python3-psycopg2. The latest pgadmin4 version
requires psycopg2-2.8. But if you're on Debian/Ubuntu stable, apt installed
v2.7. So you need to update it with pip :

sudo pip3 install -U psycopg2
>>>

The problem is that the upgrade to psycopg 2.8 doesn't work, as it's
installed as part of the dtsutils package along with pgadmin4.

Any ideas?

Re: PGAdmin4.11.1 on Ubuntu 18.04

[Adrian Klaver]

On 8/1/19 7:39 AM, Tony Shelver wrote:

I am getting an 'able oid' when querying any tables using the Query tool.


Can you show the actual entire  error message?

More below.


When using the view / edit data option, everything works fine.

I came across this
<<<

The problem is due to python3-psycopg2. The latest pgadmin4 version 
requires psycopg2-2.8. But if you're on Debian/Ubuntu stable, apt 
installed v2.7. So you need to update it with pip :


|sudo pip3 install -U psycopg2



|

|The problem is that the upgrade to psycopg 2.8 doesn't work, as it's 
installed as part of the dtsutils package along with pgadmin4.


I'm not following. If is installed as part of pgAdmin4 why do you need 
to upgrade?




|

|Any ideas?
|




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

Re: PGAdmin4.11.1 on Ubuntu 18.04

[Adrian Klaver]

On 8/1/19 7:39 AM, Tony Shelver wrote:

I am getting an 'able oid' when querying any tables using the Query tool.
When using the view / edit data option, everything works fine.


Should have asked in previous post:

What Postgres version(s)?



I came across this
<<<

The problem is due to python3-psycopg2. The latest pgadmin4 version 
requires psycopg2-2.8. But if you're on Debian/Ubuntu stable, apt 
installed v2.7. So you need to update it with pip :


|sudo pip3 install -U psycopg2



|

|The problem is that the upgrade to psycopg 2.8 doesn't work, as it's 
installed as part of the dtsutils package along with pgadmin4.


|

|Any ideas?
|




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

Re: PGAdmin4.11.1 on Ubuntu 18.04

[Tony Shelver]

Thanks for the replies: the version is PG11.4.

As for needing to upgrade:  I just want to fix the problem.  This was a
working environment until I updated pgAdmin4.

The fix suggested on Stackoverflow was to move to psycopg2 2.8, but it
doesn't seem to be possible.

If there is another fix that anyone is aware of, I would love to know.

I tried to uninstall pgAdmin4, then install psycopg 2.8 via pip, then
reinstall pgadmoin4 again using the Synatpic / Ubunto package manager, but
it just installs pscopg2.7 over the top.

On Thu, 1 Aug 2019 at 16:50, Adrian Klaver 
wrote:

> On 8/1/19 7:39 AM, Tony Shelver wrote:
> > I am getting an 'able oid' when querying any tables using the Query tool.
> > When using the view / edit data option, everything works fine.
>
> Should have asked in previous post:
>
> What Postgres version(s)?
>
> >
> > I came across this
> > <<<
> >
> > The problem is due to python3-psycopg2. The latest pgadmin4 version
> > requires psycopg2-2.8. But if you're on Debian/Ubuntu stable, apt
> > installed v2.7. So you need to update it with pip :
> >
> > |sudo pip3 install -U psycopg2
> 
> > |
> >
> > |The problem is that the upgrade to psycopg 2.8 doesn't work, as it's
> > installed as part of the dtsutils package along with pgadmin4.
> >
> > |
> >
> > |Any ideas?
> > |
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: PGAdmin4.11.1 on Ubuntu 18.04

[Tony Shelver]

The article at stackoverflow is here


On Thu, 1 Aug 2019 at 17:28, Tony Shelver  wrote:

> Thanks for the replies: the version is PG11.4.
>
> As for needing to upgrade:  I just want to fix the problem.  This was a
> working environment until I updated pgAdmin4.
>
> The fix suggested on Stackoverflow was to move to psycopg2 2.8, but it
> doesn't seem to be possible.
>
> If there is another fix that anyone is aware of, I would love to know.
>
> I tried to uninstall pgAdmin4, then install psycopg 2.8 via pip, then
> reinstall pgadmoin4 again using the Synatpic / Ubunto package manager, but
> it just installs pscopg2.7 over the top.
>
> On Thu, 1 Aug 2019 at 16:50, Adrian Klaver 
> wrote:
>
>> On 8/1/19 7:39 AM, Tony Shelver wrote:
>> > I am getting an 'able oid' when querying any tables using the Query
>> tool.
>> > When using the view / edit data option, everything works fine.
>>
>> Should have asked in previous post:
>>
>> What Postgres version(s)?
>>
>> >
>> > I came across this
>> > <<<
>> >
>> > The problem is due to python3-psycopg2. The latest pgadmin4 version
>> > requires psycopg2-2.8. But if you're on Debian/Ubuntu stable, apt
>> > installed v2.7. So you need to update it with pip :
>> >
>> > |sudo pip3 install -U psycopg2
>> 
>> > |
>> >
>> > |The problem is that the upgrade to psycopg 2.8 doesn't work, as it's
>> > installed as part of the dtsutils package along with pgadmin4.
>> >
>> > |
>> >
>> > |Any ideas?
>> > |
>> >
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>

Re: PGAdmin4.11.1 on Ubuntu 18.04

[Ron]

I think the real question is "how do I make pgadmin4 use the locally 
installed psycopg instead of the system version?"


On 8/1/19 10:32 AM, Tony Shelver wrote:
The article at stackoverflow is here 



On Thu, 1 Aug 2019 at 17:28, Tony Shelver > wrote:


Thanks for the replies: the version is PG11.4.

As for needing to upgrade:  I just want to fix the problem.  This was
a working environment until I updated pgAdmin4.

The fix suggested on Stackoverflow was to move to psycopg2 2.8, but it
doesn't seem to be possible.

If there is another fix that anyone is aware of, I would love to know.

I tried to uninstall pgAdmin4, then install psycopg 2.8 via pip, then
reinstall pgadmoin4 again using the Synatpic / Ubunto package manager,
but it just installs pscopg2.7 over the top.

On Thu, 1 Aug 2019 at 16:50, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote:

On 8/1/19 7:39 AM, Tony Shelver wrote:
> I am getting an 'able oid' when querying any tables using the
Query tool.
> When using the view / edit data option, everything works fine.

Should have asked in previous post:

What Postgres version(s)?

>
> I came across this
> <<<
>
> The problem is due to python3-psycopg2. The latest pgadmin4 version
> requires psycopg2-2.8. But if you're on Debian/Ubuntu stable, apt
> installed v2.7. So you need to update it with pip :
>
> |sudo pip3 install -U psycopg2

> |
>
> |The problem is that the upgrade to psycopg 2.8 doesn't work, as
it's
> installed as part of the dtsutils package along with pgadmin4.
>
> |
>
> |Any ideas?
> |
>


-- 
Adrian Klaver

adrian.kla...@aklaver.com 



--
Angular momentum makes the world go 'round.

Re: PGAdmin4.11.1 on Ubuntu 18.04

[Adrian Klaver]

On 8/1/19 8:34 AM, Ron wrote:
I think the real question is "how do I make pgadmin4 use the locally 
installed psycopg instead of the system version?"




Yeah the requirements file shows psycopg2-2.8:
https://github.com/postgres/pgadmin4/blob/master/requirements.txt

The issue(I'm assuming) is:

http://initd.org/psycopg/articles/2019/04/04/psycopg-28-released/
"Added table_oid and table_column attributes on cursor.description items 
(ticket #661)."


Have you looked at:

~/.config/pgadmin/pgadmin4.conf

to see what it is using as PythonPath?


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

compressing network packets

[farjad . farid]

Under windows environment, is there a way of compressing the network packet 
reaching postgresql server? 

Many thanks. 

Re: PGAdmin4.11.1 on Ubuntu 18.04

[Adrian Klaver]

On 8/1/19 8:28 AM, Tony Shelver wrote:

Thanks for the replies: the version is PG11.4.


I tried to uninstall pgAdmin4, then install psycopg 2.8 via pip, then 
reinstall pgadmoin4 again using the Synatpic / Ubunto package manager, 
but it just installs pscopg2.7 over the top.




What repo are you getting the packages from?

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

Re: compressing network packets

[Francisco Olarte]

On Thu, Aug 1, 2019 at 5:43 PM farjad.farid
 wrote:
> Under windows environment, is there a way of compressing the network packet 
> reaching postgresql server?

You'll need to explain a bit more. You have windows, I assume for
either client, server or both, and you have a pg server, on an
unespecified environment.

Also packets "reaching" the server does not sound good, you probably
want to compress packets "sent" to the server and DECOMPRESS them upon
reaching the server, or something like that.

Try to be a bit clearer, something like "Is there a way to compress
network traffic between a pg windows client and a windows server on
another machine" and some windows savvy reader may be able to help you
( I can think of a couple solutions for that on my basic desktop OS
with the basic tools, I'm sure something like windows must have dozens
).

Francisco Olarte.

Re: PGAdmin4.11.1 on Ubuntu 18.04

[Adrian Klaver]

On 8/1/19 8:28 AM, Tony Shelver wrote:

Thanks for the replies: the version is PG11.4.



I tried to uninstall pgAdmin4, then install psycopg 2.8 via pip, then 
reinstall pgadmoin4 again using the Synatpic / Ubunto package manager, 
but it just installs pscopg2.7 over the top.




If you are installing from the PGDG repo's then it seems someone did not 
get the memo from the requirements.txt:


"# IMPORTANT:
#
#If runtime or build time dependencies are changed in this file, the # 
#committer*must* ensure the DEB and RPM package maintainers are informed 
#as soon as possible."


psycopg2>=2.8

apt-cache show pgadmin4-common
Package: pgadmin4-common
Source: pgadmin4
Version: 4.11-1.pgdg16.04+1

Depends: ...,  python3-psycopg2 (>= 2.7.4), ...

There is an issue filed:

https://redmine.postgresql.org/issues/4550

You will need a community account to see/update.
I will be updating issue.



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

Re: PGAdmin4.11.1 on Ubuntu 18.04

[Ray O'Donnell]

On 01/08/2019 16:32, Tony Shelver wrote:
The article at stackoverflow is here 



Thanks for the link! The solution given in one of the comments there 
worked for me (Debian 9):


- Install pip3 if needed: sudo apt install python3-pip

- pip3 install psycopg2-binary (doesn't need pg_config)

- Edit ~/.config/pgadmin/pgadmin4.conf and set 
PythonPath="/home/rod/.local/python3.5;/home/rod/.local/python3.5/site-packages"


I then needed to kill the pgAdmin4 server so that it picked up the new 
setting.


Ray.

--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: PGAdmin4.11.1 on Ubuntu 18.04

[Daniele Varrazzo]

On Thu, Aug 1, 2019 at 4:43 PM Adrian Klaver  wrote:

> Yeah the requirements file shows psycopg2-2.8:
> https://github.com/postgres/pgadmin4/blob/master/requirements.txt
>
> The issue(I'm assuming) is:
>
> http://initd.org/psycopg/articles/2019/04/04/psycopg-28-released/
> "Added table_oid and table_column attributes on cursor.description items
> (ticket #661)."

Couldn't be pgadmin forgiving if these attributes are not available?

-- Daniele

Re: PGAdmin4.11.1 on Ubuntu 18.04

[Adrian Klaver]

On 8/1/19 9:05 AM, Daniele Varrazzo wrote:

On Thu, Aug 1, 2019 at 4:43 PM Adrian Klaver  wrote:


Yeah the requirements file shows psycopg2-2.8:
https://github.com/postgres/pgadmin4/blob/master/requirements.txt

The issue(I'm assuming) is:

http://initd.org/psycopg/articles/2019/04/04/psycopg-28-released/
"Added table_oid and table_column attributes on cursor.description items
(ticket #661)."


Couldn't be pgadmin forgiving if these attributes are not available?


Looks like it is wrapped up in this:

https://www.pgadmin.org/docs/pgadmin4/4.11/release_notes_4_11.html
Issue #1760 - Add support for editing of resultsets in the Query Tool, 
if the data can be identified as updatable.


https://redmine.postgresql.org/issues/1760

def check_updatable_results_pkeys(self):

"""

This function is used to check whether the last successful query
produced updatable results and sets the necessary flags and
attributes accordingly. Should be called after polling for the 
results is successful(results are ready)


"""


...

  self.is_updatable_resultset, self.primary_keys, pk_names,table_oid = \
is_query_resultset_updatable(conn, sql_path)




-- Daniele




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

Linux Mint - cannot find auto_explain extension

[Keith Roberts]

Hi All,

I'm running Linux Mint 18 Sarah and have installed PostgreSQL 9.5.18 but 
cannot load the auto_explain extension.


postgres=# SELECT * from pg_extension;
-[ RECORD 1 ]--+
extname    | plpgsql
extowner   | 10
extnamespace   | 11
extrelocatable | f
extversion | 1.0
extconfig  |
extcondition   |

postgres=# SELECT * from pg_available_extensions;
-[ RECORD 1 ]-+-
name  | plpgsql
default_version   | 1.0
installed_version | 1.0
comment   | PL/pgSQL procedural language

postgres=#
postgres=# CREATE EXTENSION auto_explain;
ERROR:  could not open extension control file 
"/usr/share/postgresql/9.5/extension/auto_explain.control": No such file 
or directory

postgres=#

Does anyone have any idea what package I need to install to get the 
auto_explain extension please?


TIA

Keith Roberts

Re: Linux Mint - cannot find auto_explain extension

[Julien Rouhaud]

Hi,

On Thu, Aug 1, 2019 at 7:53 PM Keith Roberts  wrote:
>
> postgres=# CREATE EXTENSION auto_explain;
> ERROR:  could not open extension control file
> "/usr/share/postgresql/9.5/extension/auto_explain.control": No such file
> or directory
> postgres=#
>
> Does anyone have any idea what package I need to install to get the
> auto_explain extension please?

As explained in the documentation
(https://www.postgresql.org/docs/current/auto-explain.html)
auto_explain is not an extension but a module.  The rest of the
documentation will show you how to configure and use it, provided that
you have contrib packages installed.

Altering multiple column types

[Bharanee Rathna]

Hi,

I'm encountering an issue altering multiple column types in a single ALTER
TABLE

psql (12beta2, server 11.4)
Type "help" for help.
test=# create table users(id serial primary key, name varchar(255),
age int, email varchar(255));
CREATE TABLEtest=# create index users_name_idx on users(name);
CREATE INDEXtest=# create index users_email_idx on users(email);
CREATE INDEXtest=# alter table users alter column name type text,
alter column email type text;
psql: ERROR:  relation "users_name_idx" already exists
test=# alter table users alter column name type text;
ALTER TABLE
test=# alter table users alter column email type text;
ALTER TABLE


Is there a limitation I'm missing in the docs ?

https://www.postgresql.org/docs/11/sql-altertable.html

Thanks

Re: Altering multiple column types

[Luca Ferrari]

On Fri, Aug 2, 2019 at 7:42 AM Bharanee Rathna  wrote:
>
> Hi,
>
> I'm encountering an issue altering multiple column types in a single ALTER 
> TABLE
>
> psql (12beta2, server 11.4)

it is working on my 11.4 with psql 11.4. Could it be a problem of psql
version 12? Can you try again with a psql "stable"?

testdb=> create table users(id serial primary key, name varchar(255),
age int, email varchar(255));
CREATE TABLE
testdb=> alter table users alter column name type text, alter column
email type text;
ALTER TABLE
testdb=> drop table users;
DROP TABLE
testdb=> create table users(id serial primary key, name varchar(255),
age int, email varchar(255));
CREATE TABLE
testdb=> create index users_email_idx on users(email);
CREATE INDEX
testdb=> alter table users alter column name type text, alter column
email type text;
ALTER TABLE
testdb=> \d users
Table "public.users"
 Column |  Type   | Collation | Nullable |  Default
+-+---+--+---
 id | integer |   | not null | nextval('users_id_seq'::regclass)
 name   | text|   |  |
 age| integer |   |  |
 email  | text|   |  |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_email_idx" btree (email)

testdb=> select version();
version
---
 PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
8.3.0-6ubuntu1~18.10.1) 8.3.0, 64-bit
(1 row)