Re: [GENERAL] Major Version Upgradation from 9.4 to 9.6 in Replication Environment

[Jov]

Not sure windows，on linux，you can use pg_upgrade hardlink mode and use
rsync to achieve this. See 9.6 pg_upgrade doc for detail.

Jov

2017年7月26日 5:50 AM，"Amee Sankhesara - Quipment India" <
amee.sankhes...@quipment.nl>写道：

> [image: Quipment Logo]
>
> Hello,
>
>
>
> We have a windows setup where we have 2 node primary-slave setup which
> comprises Async Replication + Log Shipping on slave.
>
>
>
> Now we want to upgrade it from 9.4 to 9.6 version.
>
>
>
> Do we need to upgrade slave to 9.6, upgrade master to 9.6 and setup
> replication again from scratch?
>
>
>
> What are the best possible ways available to upgrade slave and master
> without setting up replication again?
>
>
>
> Our Setup - WINDOWS SERVER 2012 , PostgreSQL 9.4.7 on primary and slave
>
> Objective - To Upgrade Master and Slave from 9.4 to 9.6
>
>
>
> Thanks,
>
> *Amee Sankhesara*
> *Database Developer*
>
> R&D .Net Department
>
> T / +91 (0) 94295 45266
> amee.sankhesara@ <%20amee.sankhes...@quipment.in> / www.quipment.in
>
>
>
> *QTwee Group BV*
> KvK Arnhem 60858001
> BTW NL854090721B01
> mail@  / www.quipment.nl
>
> *Hoofdkantoor Nijmegen*
> Kerkenbos 1236 C
> 6546 BE Nijmegen
>
> Tel.
>
> +31 (0)24 372 47 00
>
> Fax.
>
> +31 (0)24 372 47 07
>
> *Quipment India*
> 903-904 Landmark,
> Beside Titanium City Centre,
> Anandnagar Road, 100ft Ring Road,
> Ahmedabad
> Gujarat - 380015 - India
> Tel. +91 (0) 79 4009 7847
>
> *Disclaimer*
>
> This e-mail message (including any attachment) is intended only for the
> personal use of the recipient(s) named above. This message is confidential
> and may be legally privileged. If you are not an intended recipient, you
> may not review, copy or distribute this message. If you have received this
> communication in error, please notify us immediately by e-mail and delete
> the original message.
>
>
>

[GENERAL] Major Version Upgradation from 9.4 to 9.6 in Replication Environment

[Amee Sankhesara - Quipment India]

[Quipment Logo]

Hello,

We have a windows setup where we have 2 node primary-slave setup which 
comprises Async Replication + Log Shipping on slave.

Now we want to upgrade it from 9.4 to 9.6 version.

Do we need to upgrade slave to 9.6, upgrade master to 9.6 and setup replication 
again from scratch?

What are the best possible ways available to upgrade slave and master without 
setting up replication again?

Our Setup - WINDOWS SERVER 2012 , PostgreSQL 9.4.7 on primary and slave
Objective - To Upgrade Master and Slave from 9.4 to 9.6

Thanks,
Amee Sankhesara
Database Developer

R&D .Net Department

T / +91 (0) 94295 45266
amee.sankhesara@ / 
www.quipment.in



QTwee Group BV
KvK Arnhem 60858001
BTW NL854090721B01
mail@ / www.quipment.nl

Hoofdkantoor Nijmegen
Kerkenbos 1236 C
6546 BE Nijmegen
Tel.

+31 (0)24 372 47 00

Fax.

+31 (0)24 372 47 07


Quipment India
903-904 Landmark,
Beside Titanium City Centre,
Anandnagar Road, 100ft Ring Road,
Ahmedabad
Gujarat - 380015 - India
Tel. +91 (0) 79 4009 7847




Disclaimer

This e-mail message (including any attachment) is intended only for the 
personal use of the recipient(s) named above. This message is confidential and 
may be legally privileged. If you are not an intended recipient, you may not 
review, copy or distribute this message. If you have received this 
communication in error, please notify us immediately by e-mail and delete the 
original message.

Re: [GENERAL] How to get transaction started always in WRITE mode.

[rob stone]

On Tue, 2017-07-25 at 11:32 +0200, Adam Šlachta wrote:
> Hello,
>  
> In short: Is there any way how to setup PostgreSql 9.6 to always
> start a transaction in WRITE mode?
>  
> Our related configuration:
> "default_transaction_isolation" --> "read committed"
> "default_transaction_read_only" --> "off"
>  
>  
> Longer description (for those who are interested, since it is not
> only PostgreSQL related):
>  
> We are facing problems with "cannot execute  in
> a read-only transaction" exception
> (org.postgresql.util.PSQLException).
> It is very likely the problem is caused by our code, however at the
> moment the fastest solution before we solve the things properly would
> be to setup WRITE mode for all started transactions on a database-
> setup-level.
>  
> SW we use:
> -> Java 8
> -> Hibernate 5.1.2
> -> spring-data-jpa 1.10.4.RELEASE
> -> spring-beans, spring-core, other spring stuff of version
> 4.2.8.RELEASE
>  
> Related configuration (I don't want to spam here with long list of
> configuration files so I pick-up what I consider important):
> Hibernate -> first & second level cache switched OFF
> SessionFactory ->
> org.springframework.orm.hibernate5.LocalSessionFactoryBean
> transactionManager ->
> org.springframework.orm.jpa.JpaTransactionManager
> Spring @Transactional(read-only) hint -> where we could we set it to
> "false"
> Our typical @Repository extends
> org.springframework.data.jpa.repository.JpaRepository, which uses
> implementation from
> org.springframework.data.jpa.repository.support.SimpleJpaRepository.
>  
> Thank you very much for any hints.
> Adam Slachta
> 
> 

Hello Adam,

There is a Hibernate parameter that overrides the database's default
isolation level:-

hibernate.connection.isolation

Are you certain that second level caching is off?
You can make a class in a cache immutable by:-



Don't know if that helps but your problem seems to be a configuration
issue in Hibernate.

Cheers,
Rob


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to get transaction started always in WRITE mode.

[Jerry Sievers]

Adam Šlachta  writes:

> Hello Scott and other potential readers/writers,
>
>  
>
>> Login to the database with psql as the same user that your java app
> connects with try: 
>
>> 
>
>> show default_transaction_read_only;
>
>>  This can be set per-user, it's possible you're getting tripped up
> there.
>
>  
>
> show default_transaction_read_only;
>
> OFF -> this was run under the same user as our application while the
> application was running;
>
>  
>
>> Also, what happens if you run: 
>
>>  
>
>> select pg_is_in_recovery();
>
>> This can happen if you connect to a postgres slave instead of a
> master.  Make sure you're always connecting to a master node for
> executing writes.  
>
>  
>
> select pg_is_in_recovery() returns “F”, however we have not
> configured master/slave (I guess this is mostly used for replication
> right?).
>
>  
>
>> Is it possible that your code / connect layer is setting
> default_transaction_read_only to TRUE when the app connects?
>
>  
>
> Well, this might be hard to tell, since we would have to be sure all
> those layers (hibernate/spring) does not “somehow” set read-only to
> true. We have tried to set it up correctly, but most probably that’s
> where the problem root cause is.
>
> Do you know how to find it out eg. in PostgreSQL logs how the
> read-only flag is setup for current transaction? We have tried to
> enable full logging (postgresql.conf) however reading it is quite
> tough and we did not get any closer to the solution.

Also make sure there's no DB or role specific override.  psql \drds may
reveal something.

Full statement logging via log_statement=all or
log_min_duration_statement=0 should certainly have shown you something
if it's being set explicitly by client and/or pooler.

>
>  
>
> Thank you for your help so far,
>
> Adam
>
>  
>
>  
>
>  
>
>  
>
>  
>
>
>
>
> ---
> This e-mail message including any attachments is for the sole use of
> the intended recipient(s) and may contain privileged or confidential
> information. Any unauthorized review, use, disclosure or distribution
> is prohibited. If you are not the intended recipient, please
> immediately contact the sender by reply e-mail and delete the
> original message and destroy all copies thereof.
>
> Tato zpráva včetně veškerých příloh je důvěrná a mohou ji využít
> pouze osoby, jimž je adresována. Nejste-li adresátem zprávy, obsah i
> s přílohami a kopiemi bezodkladně odstraňte ze svého systému a dále
> ji nijak nevyužívejte. Upozorňujeme Vás, že využívání zpráv, které
> Vám nejsou určeny, je zakázáno, včetně jejich přímého či nepřímého
> zveřejňování, kopírování, tištění, rozšiřování anebo jakéhokoli
> právního jednání učiněného při spoléhání se na jejich obsah. Pokud
> jste zprávu obdrželi omylem, postupujte stejně a neprodleně
> informujte odesílatele.
>
> Der Inhalt dieser E-Mail ist vertraulich und ausschließlich für den
> bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene
> Adressat dieser E-Mail oder dessen Vertreter sein sollten, so
> beachten Sie bitte, dass jede Form der Kenntnisnahme,
> Veröffentlichung, Vervielfältigung oder Weitergabe des Inhalts dieser
> E-Mail unzulässig ist. Wir bitten Sie, sich in diesem Fall mit dem
> Absender der E-Mail in Verbindung zu setzen.
>
>
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] getting error while parsing log file using pgbadger

[PAWAN SHARMA]

Hi All,

I am facing below error while parsing log file.

[postgres@abc pgaudit]$ pgbadger -f stderr postgres-2017-07-25_121445.csv
Can't locate Text/CSV_XS.pm in @INC (@INC contains: /usr/local/lib64/perl5
/usr/local/share/perl5 /usr/lib64/perl5/vendor_perl
/usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at
/bin/pgbadger line 2620.

Please suggest..!!

-Pawan

Re: [GENERAL] How to get transaction started always in WRITE mode.

[Adam Šlachta]

Hello Scott and other potential readers/writers,
 
> Login to the database with psql as the same user that your java app connects 
> with try: 
> 
> show default_transaction_read_only;
>  This can be set per-user, it's possible you're getting tripped up there.
 
show default_transaction_read_only;
OFF -> this was run under the same user as our application while the 
application was running;
 
> Also, what happens if you run: 
>  
> select pg_is_in_recovery();
> This can happen if you connect to a postgres slave instead of a master.  Make 
> sure you're always connecting to a master node for executing writes.  
 
select pg_is_in_recovery() returns “F”, however we have not configured 
master/slave (I guess this is mostly used for replication right?).
 
> Is it possible that your code / connect layer is setting 
> default_transaction_read_only to TRUE when the app connects?
 
Well, this might be hard to tell, since we would have to be sure all those 
layers (hibernate/spring) does not “somehow” set read-only to true. We have 
tried to set it up correctly, but most probably that’s where the problem root 
cause is.
Do you know how to find it out eg. in PostgreSQL logs how the read-only flag is 
setup for current transaction? We have tried to enable full logging 
(postgresql.conf) however reading it is quite tough and we did not get any 
closer to the solution.
 
Thank you for your help so far,
Adam


---
This e-mail message including any attachments is for the sole use of the 
intended recipient(s) and may contain privileged or confidential information. 
Any unauthorized review, use, disclosure or distribution is prohibited. If you 
are not the intended recipient, please immediately contact the sender by reply 
e-mail and delete the original message and destroy all copies thereof. 

Tato zpráva včetně veškerých příloh je důvěrná a mohou ji využít pouze osoby, 
jimž je adresována. Nejste-li adresátem zprávy, obsah i s přílohami a kopiemi 
bezodkladně odstraňte ze svého systému a dále ji nijak nevyužívejte. 
Upozorňujeme Vás, že využívání zpráv, které Vám nejsou určeny, je zakázáno, 
včetně jejich přímého či nepřímého zveřejňování, kopírování, tištění, 
rozšiřování anebo jakéhokoli právního jednání učiněného při spoléhání se na 
jejich obsah. Pokud jste zprávu obdrželi omylem, postupujte stejně a neprodleně 
informujte odesílatele. 

Der Inhalt dieser E-Mail ist vertraulich und ausschließlich für den 
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat 
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte, dass 
jede Form der Kenntnisnahme, Veröffentlichung, Vervielfältigung oder Weitergabe 
des Inhalts dieser E-Mail unzulässig ist. Wir bitten Sie, sich in diesem Fall 
mit dem Absender der E-Mail in Verbindung zu setzen.

Re: [GENERAL] Partitioning (constraint exclusion involving joins)

[Krithika Venkatesh]

We understand the constraints exclusion will work only on constant values.
But in our case we will never pass a constant value to the partitioning key
when we query the partition tables. Will the partition be beneficial in
this case. If yes, can you please explain.

Thanks

On 25-Jul-2017 6:46 PM, "Justin Pryzby"  wrote:

On Tue, Jul 25, 2017 at 06:21:43PM +0530, Krithika Venkatesh wrote:
> I have a table that is partitioned on a numeric column (ID).
>
> Partitioning works when I query the table with no joins.
>
> SELECT * FROM TABLE A a WHERE ID IN (SELECT ID FROM TABLE B b WHERE
> CREATED_TS = CURRENT_TIMESTAMP)
>
> Partitioning doesn't work when I do join.
>
> SELECT A.* FROM TABLE A a INNER JOIN TABLE B b ON  a.ID = b.ID.

I think you mean "constraint exclusion doesn't work when yo do a join",

which is because it only works on simple values compiled before the planner
gets to see them:

main=# explain SELECT COUNT(1) FROM eric_enodeb_metrics WHERE
start_time>now(); -- -'999 minutes'::interval;
   QUERY PLAN

-
 Aggregate  (cost=62.44..62.45 rows=1 width=8)
   ->  Append  (cost=0.00..62.40 rows=14 width=0)
 ->  Seq Scan on eric_enodeb_metrics  (cost=0.00..0.00 rows=1
width=0)
   Filter: (start_time > now())
 ->  Index Only Scan using eric_enodeb_201607_idx on
eric_enodeb_201607  (cost=0.42..4.44 rows=1 width=0)
   Index Cond: (start_time > now())
 ->  Index Only Scan using eric_enodeb_201608_idx on
eric_enodeb_201608  (cost=0.42..4.44 rows=1 width=0)
   Index Cond: (start_time > now())
 ->  Index Only Scan using eric_enodeb_201609_idx on
eric_enodeb_201609  (cost=0.42..4.44 rows=1 width=0)
   Index Cond: (start_time > now())

https://www.postgresql.org/docs/current/static/ddl-partitioning.html
|The following caveats apply to constraint exclusion:
| Constraint exclusion only works when the query's WHERE clause contains
|constants (or externally supplied parameters). For example, a comparison
|against a non-immutable function such as CURRENT_TIMESTAMP cannot be
optimized,
|since the planner cannot know which partition the function value might fall
|into at run time.
[..]


.. and see an early mail on its implementation, here:
https://www.postgresql.org/message-id/1121251997.3970.237.camel@localhost.
localdomain

Justin

Re: [GENERAL] Partitioning (constraint exclusion involving joins)

[Justin Pryzby]

On Tue, Jul 25, 2017 at 06:21:43PM +0530, Krithika Venkatesh wrote:
> I have a table that is partitioned on a numeric column (ID).
> 
> Partitioning works when I query the table with no joins.
> 
> SELECT * FROM TABLE A a WHERE ID IN (SELECT ID FROM TABLE B b WHERE
> CREATED_TS = CURRENT_TIMESTAMP)
> 
> Partitioning doesn't work when I do join.
> 
> SELECT A.* FROM TABLE A a INNER JOIN TABLE B b ON  a.ID = b.ID.

I think you mean "constraint exclusion doesn't work when yo do a join",

which is because it only works on simple values compiled before the planner
gets to see them:

main=# explain SELECT COUNT(1) FROM eric_enodeb_metrics WHERE start_time>now(); 
-- -'999 minutes'::interval;
   QUERY PLAN   
 
-
 Aggregate  (cost=62.44..62.45 rows=1 width=8)
   ->  Append  (cost=0.00..62.40 rows=14 width=0)
 ->  Seq Scan on eric_enodeb_metrics  (cost=0.00..0.00 rows=1 width=0)
   Filter: (start_time > now())
 ->  Index Only Scan using eric_enodeb_201607_idx on eric_enodeb_201607 
 (cost=0.42..4.44 rows=1 width=0)
   Index Cond: (start_time > now())
 ->  Index Only Scan using eric_enodeb_201608_idx on eric_enodeb_201608 
 (cost=0.42..4.44 rows=1 width=0)
   Index Cond: (start_time > now())
 ->  Index Only Scan using eric_enodeb_201609_idx on eric_enodeb_201609 
 (cost=0.42..4.44 rows=1 width=0)
   Index Cond: (start_time > now())

https://www.postgresql.org/docs/current/static/ddl-partitioning.html
|The following caveats apply to constraint exclusion:
| Constraint exclusion only works when the query's WHERE clause contains
|constants (or externally supplied parameters). For example, a comparison
|against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized,
|since the planner cannot know which partition the function value might fall
|into at run time.
[..]


.. and see an early mail on its implementation, here:
https://www.postgresql.org/message-id/1121251997.3970.237.camel@localhost.localdomain

Justin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Partitioning

[Krithika Venkatesh]

Hi,

I have a table that is partitioned on a numeric column (ID).

Partitioning works when I query the table with no joins.

SELECT * FROM TABLE A a WHERE ID IN (SELECT ID FROM TABLE B b WHERE
CREATED_TS = CURRENT_TIMESTAMP)

Partitioning doesn't work when I do join.

SELECT A.* FROM TABLE A a INNER JOIN TABLE B b ON  a.ID = b.ID.

Is there any other option that would work.

Thanks in Advance..

Re: [GENERAL] How to get transaction started always in WRITE mode.

[Scott Mead]

On Tue, Jul 25, 2017 at 5:32 AM, Adam Šlachta 
wrote:

> Hello,
>
>
>
> In short: Is there any way how to setup PostgreSql 9.6 to always start a
> transaction in WRITE mode?
>
>
>
> Our related configuration:
>
> "default_transaction_isolation" --> "read committed"
>
> "default_transaction_read_only" --> "off"
>

Login to the database with psql as the same user that your java app
connects with try:

show default_transaction_read_only;

This can be set per-user, it's possible you're getting tripped up there.
Also, what happens if you run:

select pg_is_in_recovery();


This can happen if you connect to a postgres slave instead of a master.
Make sure you're always connecting to a master node for executing writes.


>
>
>
> Longer description (for those who are interested, since it is not only
> PostgreSQL related):
>
>
>
> We are facing problems with "cannot execute  in a
> read-only transaction" exception (org.postgresql.util.PSQLException).
>
> It is very likely the problem is caused by our code, however at the moment
> the fastest solution before we solve the things properly would be to setup
> WRITE mode for all started transactions on a database-setup-level.
>
>
>
> SW we use:
>
> -> Java 8
>
> -> Hibernate 5.1.2
>
> -> spring-data-jpa 1.10.4.RELEASE
>
> -> spring-beans, spring-core, other spring stuff of version 4.2.8.RELEASE
>
>
>
> Related configuration (I don't want to spam here with long list of
> configuration files so I pick-up what I consider important):
>
> Hibernate -> first & second level cache switched OFF
>
> SessionFactory -> org.springframework.orm.hibernate5.
> LocalSessionFactoryBean
>
> transactionManager -> org.springframework.orm.jpa.JpaTransactionManager
>
> Spring @Transactional(read-only) hint -> where we could we set it to
> "false"
>
> Our typical @Repository extends 
> org.springframework.data.jpa.repository.JpaRepository,
> which uses implementation from org.springframework.data.jpa.
> repository.support.SimpleJpaRepository.
>

Is it possible that your code / connect layer is setting
default_transaction_read_only to TRUE when the app connects?





>
>
> Thank you very much for any hints.
>
> Adam Slachta
>
>
>
> ---
> This e-mail message including any attachments is for the sole use of the
> intended recipient(s) and may contain privileged or confidential
> information. Any unauthorized review, use, disclosure or distribution is
> prohibited. If you are not the intended recipient, please immediately
> contact the sender by reply e-mail and delete the original message and
> destroy all copies thereof.
>
> Tato zpráva včetně veškerých příloh je důvěrná a mohou ji využít pouze
> osoby, jimž je adresována. Nejste-li adresátem zprávy, obsah i s přílohami
> a kopiemi bezodkladně odstraňte ze svého systému a dále ji nijak
> nevyužívejte. Upozorňujeme Vás, že využívání zpráv, které Vám nejsou
> určeny, je zakázáno, včetně jejich přímého či nepřímého zveřejňování,
> kopírování, tištění, rozšiřování anebo jakéhokoli právního jednání
> učiněného při spoléhání se na jejich obsah. Pokud jste zprávu obdrželi
> omylem, postupujte stejně a neprodleně informujte odesílatele.
>
> Der Inhalt dieser E-Mail ist vertraulich und ausschließlich für den
> bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
> dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
> dass jede Form der Kenntnisnahme, Veröffentlichung, Vervielfältigung oder
> Weitergabe des Inhalts dieser E-Mail unzulässig ist. Wir bitten Sie, sich
> in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
>
>


-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

Re: [GENERAL] Row based permissions: at DB or at Application level?

[vinny]

On 2017-07-25 11:40, Thomas Güttler wrote:

I would like to reduce the "ifing and elsing" in my python code (less
conditions, less bugs, more SQL, more performance)

Regards,
  Thomas Güttler



A quick brainstorm:

You could, probably...
but you'd have to create a separate database user for every Django user,
get Django to connect to the database as that user
and setup policies for each of those users, for every use-case.

When I look at an example policy from the manual:

CREATE POLICY fp_u ON information FOR UPDATE
  USING (group_id <= (SELECT group_id FROM users WHERE user_name = 
current_user));


I'm not sure if this is any less bug-sensitive than an IF in Python...
And don't forget you have to interpret any error-response from the 
database into

something that Django can make understandable to the end-user.

I'm not saying row-level security is bad, far from it, but I doubt that 
using it
to replace Django's own security is going to magically make life much 
easier.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Row based permissions: at DB or at Application level?

[Daniel Westermann]

>How could an application which gets written from scratch use PostgreSQL to 
>implement 
>row based permissions? 

Are you looking for this? 
https://www.postgresql.org/docs/9.6/static/ddl-rowsecurity.html 

Regards 
Daniel 

[GENERAL] Row based permissions: at DB or at Application level?

[Thomas Güttler]

I am thinking about rewriting an existing application which uses PostgreSQL via Django (ORM and Web-Framework written in 
Python).


Up to now the permission checks are done at the application level.

Up to now queries like: "Show all items which the current user is allowed to 
modify" result in complicated SQL and
this leads to slow queries.

Up to now there is one db-user and the application does the filtering of rows 
to prevent application users to see
items which they are not allowed to see.

I guess most web applications work like this.

I would like to reduce the "ifing and elsing" in my python code (less 
conditions, less bugs, more SQL, more performance)

How could an application which gets written from scratch use PostgreSQL to 
implement
row based permissions?

Regards,
  Thomas Güttler

--
Thomas Guettler http://www.thomas-guettler.de/


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How to get transaction started always in WRITE mode.

[Adam Šlachta]

Hello,
 
In short: Is there any way how to setup PostgreSql 9.6 to always start a 
transaction in WRITE mode?
 
Our related configuration:
"default_transaction_isolation" --> "read committed"
"default_transaction_read_only" --> "off"
 
 
Longer description (for those who are interested, since it is not only 
PostgreSQL related):
 
We are facing problems with "cannot execute  in a 
read-only transaction" exception (org.postgresql.util.PSQLException).
It is very likely the problem is caused by our code, however at the moment the 
fastest solution before we solve the things properly would be to setup WRITE 
mode for all started transactions on a database-setup-level.
 
SW we use:
-> Java 8
-> Hibernate 5.1.2
-> spring-data-jpa 1.10.4.RELEASE
-> spring-beans, spring-core, other spring stuff of version 4.2.8.RELEASE
 
Related configuration (I don't want to spam here with long list of 
configuration files so I pick-up what I consider important):
Hibernate -> first & second level cache switched OFF
SessionFactory -> org.springframework.orm.hibernate5.LocalSessionFactoryBean
transactionManager -> org.springframework.orm.jpa.JpaTransactionManager
Spring @Transactional(read-only) hint -> where we could we set it to "false"
Our typical @Repository extends 
org.springframework.data.jpa.repository.JpaRepository, which uses 
implementation from 
org.springframework.data.jpa.repository.support.SimpleJpaRepository.
 
Thank you very much for any hints.
Adam Slachta



---
This e-mail message including any attachments is for the sole use of the 
intended recipient(s) and may contain privileged or confidential information. 
Any unauthorized review, use, disclosure or distribution is prohibited. If you 
are not the intended recipient, please immediately contact the sender by reply 
e-mail and delete the original message and destroy all copies thereof. 

Tato zpráva včetně veškerých příloh je důvěrná a mohou ji využít pouze osoby, 
jimž je adresována. Nejste-li adresátem zprávy, obsah i s přílohami a kopiemi 
bezodkladně odstraňte ze svého systému a dále ji nijak nevyužívejte. 
Upozorňujeme Vás, že využívání zpráv, které Vám nejsou určeny, je zakázáno, 
včetně jejich přímého či nepřímého zveřejňování, kopírování, tištění, 
rozšiřování anebo jakéhokoli právního jednání učiněného při spoléhání se na 
jejich obsah. Pokud jste zprávu obdrželi omylem, postupujte stejně a neprodleně 
informujte odesílatele. 

Der Inhalt dieser E-Mail ist vertraulich und ausschließlich für den 
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat 
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte, dass 
jede Form der Kenntnisnahme, Veröffentlichung, Vervielfältigung oder Weitergabe 
des Inhalts dieser E-Mail unzulässig ist. Wir bitten Sie, sich in diesem Fall 
mit dem Absender der E-Mail in Verbindung zu setzen.

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

[Dmitry Lazurkin]

On 25.07.2017 05:50, Jeff Janes wrote:
It isn't either-or.  It is the processing of millions of rows over the 
large in-list which is taking the time. Processing an in-list as a 
hash table would be great, but no one has gotten around to it 
implementing it yet.  Maybe Dmitry will be the one to do that.


Thanks. Yes, I want. But... Is this task too complex for novice?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general