Low cache hit ratio
Hello, I have recently deployed a new Slave (streaming replication) and have been monitoring its cache hit ratio. At the moment, read-only queries are being sent to this slave but only 10% of the traffic. The cache hit ratio is now at 82%. This database is around 1.4TB and is running on EC2 instances in AWS. PostgreSQL version is 9.2.24. The instance is a r4.2xlarge, so it's got 8 vCPUs and 61GB of RAM. The postgresql.conf file can be seen below (will show only relevant parameters for the cache/performance): > hot_standby = on > random_page_cost = 1.1 > max_connections = 500 > shared_buffers = 15GB > statement_timeout = 0 > work_mem = 31457kB > maintenance_work_mem = 2GB > wal_level = hot_standby > fsync = on > synchronous_commit = on > wal_buffers = 16MB > checkpoint_segments = 32 > checkpoint_completion_target = 0.9 > max_wal_senders = 20 > wal_keep_segments = 1024 > effective_cache_size = 45GB > logging_collector = on > autovacuum = on > log_autovacuum_min_duration = 1000 > autovacuum_max_workers = 5 > autovacuum_naptime = 40s > autovacuum_vacuum_threshold = 200 > autovacuum_analyze_threshold = 150 > autovacuum_vacuum_scale_factor = 0.02 > autovacuum_analyze_scale_factor = 0.005 > deadlock_timeout = 2s > max_files_per_process = 4096 > effective_io_concurrency = 200 > hot_standby_feedback = on > max_standby_streaming_delay = 120s > default_statistics_target = 100 I have tried reducing the shared_buffers parameter from 15GB to 8GB, but the cache hit ratio went down to 60%. Do you guys have any suggestions, on what I could try to get this cache more hits? Thanks in advance! --- Regards, Lucas > This message is encrypted. Both the Public Key and the GPG encrypted message > are included in this email so that you can verify its origin. publickey - root@sud0.nz - 0xC5E964A1.asc Description: application/pgp-keys signature.asc Description: OpenPGP digital signature
Updating PK and all FKs to it in CTE
Hi all,
I am on Postgres 13 and have a problem with updates in a CTE. While certainly
not generally recommended, I need to update the primary key in a table that is
referenced by a few other tables. The table definition is attached to the end
of this email [2]. I'd like to avoid dropping and recreating the constraints or
even columns, because these tables can become quite large. While I could define
the FK constraints as ON UPDATE CASCADE, I wonder why an alternative solution
using a CTE doesn't work:
According to [1], I should be able to update all FKs and the PK within a
single CTE. My CTE looks like this (a few tables left out for readability, they
follow the same pattern):
WITH update_catmaid_deep_link AS (
UPDATE catmaid_deep_link
SET active_skeleton_id = 12
WHERE active_skeleton_id = 16150756
), update_class_instance_class_instance_a AS (
UPDATE class_instance_class_instance
SET class_instance_a = 12
WHERE class_instance_a = 16150756
),
[…]
), update_catmaid_skeleton_summary AS (
UPDATE catmaid_skeleton_summary
SET skeleton_id = 12
WHERE skeleton_id = 16150756
AND project_id = 1
)
UPDATE class_instance
SET id = 12
WHERE id = 16150756
AND project_id = 1;
However, when I try this, I still get an error about a conflict with a FK
constraint in a table updated in the CTE:
ERROR: 23503: update or delete on table "class_instance" violates foreign key constraint
"catmaid_skeleton_summary_skeleton_id_fkey" on table "catmaid_skeleton_summary"
DETAIL: Key (id)=(16150756) is still referenced from table
"catmaid_skeleton_summary".
It seems like the CTE change wasn't reflected when checking the constraint (?).
As can be seen in the table definition [2], the primary key comes with an
INCLUDE statement. Is this potentially a problem? Is such an update maybe not
always possible without DDL?
Thanks for any insight!
Cheers,
Tom
[1] https://stackoverflow.com/a/34383663/1665417
[2] Table definition:
# \d class_instance
Table "public.class_instance"
Column | Type | Collation | Nullable | Default
---+--+---+--+-
id| bigint | | not null |
nextval('concept_id_seq'::regclass)
user_id | integer | | not null |
project_id| integer | | not null |
creation_time | timestamp with time zone | | not null | now()
edition_time | timestamp with time zone | | not null | now()
txid | bigint | | not null |
txid_current()
class_id | bigint | | not null |
name | character varying(255) | | not null |
Indexes:
"class_instance_id_pkey" PRIMARY KEY, btree (id) INCLUDE (class_id,
project_id)
"class_instance_class_id" btree (class_id)
"class_instance_name_trgm_idx" gin (name gin_trgm_ops)
"class_instance_project_id" btree (project_id)
"class_instance_upper_name_idx" btree (upper(name::text))
"class_instance_user_id" btree (user_id)
Foreign-key constraints:
"class_instance_class_id_fkey" FOREIGN KEY (class_id) REFERENCES class(id)
DEFERRABLE INITIALLY DEFERRED
"class_instance_project_id_fkey" FOREIGN KEY (project_id) REFERENCES
project(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
"class_instance_user_id_fkey" FOREIGN KEY (user_id) REFERENCES
auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "catmaid_deep_link" CONSTRAINT
"catmaid_deep_link_active_skeleton_id_fkey" FOREIGN KEY (active_skeleton_id) REFERENCES
class_instance(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
TABLE "catmaid_sampler" CONSTRAINT "catmaid_sampler_skeleton_id_fkey"
FOREIGN KEY (skeleton_id) REFERENCES class_instance(id) ON DELETE CASCADE DEFERRABLE INITIALLY
DEFERRED
TABLE "catmaid_skeleton_summary" CONSTRAINT
"catmaid_skeleton_summary_skeleton_id_fkey" FOREIGN KEY (skeleton_id) REFERENCES
class_instance(id) DEFERRABLE INITIALLY DEFERRED
TABLE "class_instance_class_instance" CONSTRAINT
"class_instance_class_instance_class_instance_a_fkey" FOREIGN KEY (class_instance_a)
REFERENCES class_instance(id) DEFERRABLE INITIALLY DEFERRED
TABLE "class_instance_class_instance" CONSTRAINT
"class_instance_class_instance_class_instance_b_fkey" FOREIGN KEY (class_instance_b)
REFERENCES class_instance(id) DEFERRABLE INITIALLY DEFERRED
TABLE "connector_class_instance" CONSTRAINT
"connector_class_instance_class_instance_id_fkey" FOREIGN KEY (class_instance_id)
REFERENCES class_instance(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
TABLE "point_class_instance" CONSTRAIN
Re: Low cache hit ratio
> On Jul 29, 2021, at 3:09 AM, Lucas wrote: > > Hello, > > I have recently deployed a new Slave (streaming replication) and have been > monitoring its cache hit ratio. > > At the moment, read-only queries are being sent to this slave but only 10% of > the traffic. > The cache hit ratio is now at 82%. This database is around 1.4TB and is > running on EC2 instances in AWS. PostgreSQL version is 9.2.24. > > The instance is a r4.2xlarge, so it's got 8 vCPUs and 61GB of RAM. Hi Lucas, Have you tried the pg_buffercache extension? It gives you some visibility into what’s in the cache so you can understand what’s staying resident and not leaving room for other things. I wrote a view atop pg_buffercache that I use for this purpose. It’s pasted below; I hope you find it helpful. My only caveat is that I run this under Postgres 11. I *think* I’ve used it under Postgres 9.6 but I’m not sure. It definitely hasn’t been tested on 9.2. Hope this helps, Philip /* A view of pg_buffercache which shows what's in the Postgres cache. Access to pg_buffercache requires membership in the group pg_monitor. It's OK to query this ad hoc, but don't query it aggressively (e.g. in a polling loop). The Postgres doc says -- > When the pg_buffercache view is accessed, internal buffer manager locks are taken for > long enough to copy all the buffer state data that the view will display. This ensures > that the view produces a consistent set of results, while not blocking normal buffer > activity longer than necessary. Nonetheless there could be some impact on database > performance if this view is read often. https://www.postgresql.org/docs/11/pgbuffercache.html */ CREATE OR REPLACE VIEW vw_postgres_cache AS SELECT c.relname, sum(usagecount) AS usage_count, /* count(*) is the number of buffers in use. Each buffer is 8Kb. (8Kb is the page size a.k.a. block size configured at compile time, confirm in psql with the command `show block_size`). I cast the count to float to introduce a float into calculations that are otherwise all int which would produce a result rounded to the nearest int. */ -- cache_% shows the portion of the cache that this entity occupies ((count(*)::float/ pg_settings.setting::int) * 100)::numeric(3, 1) AS "cache_%", -- entity_% shows the portion of this entity that's in cache -- pg_table_size() is in bytes, multiply buffer count by (8 * 1024) so units match (((count(*)::float * 8192) / pg_table_size(c.relname::text)) * 100)::numeric(4,1) AS "entity_%", (count(*)::float * 8 / (1024 * 1024))::numeric(20, 1) AS Gb, (count(*)::float * 8 / 1024 )::numeric(20, 1) AS Mb FROM pg_buffercache b CROSS JOIN pg_settings INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database())) WHERE pg_settings.name = 'shared_buffers' -- If this is run on a system where shared_buffers is expressed in something other than 8kB -- blocks, it will calculate incorrect kB/Mb/Gb values. Asserting the value of the unit here -- ensures no results at all will be returned in that case. AND pg_settings.unit = '8kB' GROUP BY c.relname, pg_settings.setting::int HAVING -- Only include entries that occupy at least 0.1% of the cache ((count(*)::float / pg_settings.setting::int) * 100) >= 0.1 ORDER BY 6 DESC ;
Re: Updating PK and all FKs to it in CTE
Tom Kazimiers writes: > I am on Postgres 13 and have a problem with updates in a CTE. While certainly > not generally recommended, I need to update the primary key in a table that is > referenced by a few other tables. The table definition is attached to the end > of this email [2]. I'd like to avoid dropping and recreating the constraints > or > even columns, because these tables can become quite large. While I could > define > the FK constraints as ON UPDATE CASCADE, I wonder why an alternative solution > using a CTE doesn't work: I tried to reproduce your problem and failed; the attached script acts as I'd expect. So there must be some moving part you've not mentioned. Can yo create a similar self-contained example that fails? regards, tom lane drop table if exists pt, c1, c2; create table pt (id int primary key); create table c1 (id int primary key, ref int references pt deferrable initially deferred); create table c2 (id int primary key, ref int references pt deferrable initially deferred); insert into pt values(11); insert into c1 values(1, 11); insert into c2 values(2, 11); update pt set id = 12 where id = 11; -- fails with u1 as ( update c1 set ref = 12 where ref = 11 ) update pt set id = 12 where id = 11; -- fails with u1 as ( update c1 set ref = 12 where ref = 11 ), u2 as ( update c2 set ref = 12 where ref = 11 ) update pt set id = 12 where id = 11; -- ok
Issue with PSQL JDBC Driver Null Pointer
Hello,
I am getting an NPE exception in the JDBC driver 42.2.23.
When the alias is null, NPE results. Is it possible to handle null value ?
I have filled a bug against RCA
https://github.com/vladmihalcea/hibernate-types/issues/335. But, I thought
that PSQL Driver can handle it more gracefully.
Caused by: java.lang.NullPointerException: null
at
org.postgresql.jdbc.TypeInfoCache.getTypeForAlias(TypeInfoCache.java:675)
at
org.postgresql.jdbc.TypeInfoCache.getPGArrayType(TypeInfoCache.java:519)
at
org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1375)
at
org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1399)
at
org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
at
org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
at
org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
at
com.vladmihalcea.hibernate.type.array.internal.ArraySqlTypeDescriptor$1.doBind(ArraySqlTypeDescriptor.java:36)
at
org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:73)
at
org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:276)
at
org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:271)
at
org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:39)
at
org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2995)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3292)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3828)
at
org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:107)
at
org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
at
org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
at
org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
at
org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:348)
at
org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
at
org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102)
at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1362)
at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:453)
at
org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3212)
at
org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2380)
at
org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:447)
at
org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
at
org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)
at
org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
at
org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
... 123 common frames omitted
public String getTypeForAlias(String alias) {
String type = typeAliases.get(alias);
if (type != null) {
return type;
}
if (alias.indexOf('"') == -1) { *// THIS LINE THROWS NPE SINCE
alias is null ** *
type = typeAliases.get(alias.toLowerCase());
if (type != null) {
return type;
}
}
return alias;
}
--
Thanks,
Bhavesh
Re: Updating PK and all FKs to it in CTE
On Thu, Jul 29, 2021 at 10:51:09AM -0400, Tom Lane wrote: I tried to reproduce your problem and failed; the attached script acts as I'd expect. So there must be some moving part you've not mentioned. Can yo create a similar self-contained example that fails? Thanks very much for trying this. I failed to create a self-contained example and just now realized what the problem is: a trigger function accidentally created an entry with the old ID (changing the primary key was not anticipated until now), throwing off the constraint check. I noticed when running this explicitly in a transaction and then inspecting the table that triggered the error and also got the same error with ON UPDATE CASCADE. My apologies, it just took a while for me to realize where this entry came from and that it was not the CTE or ON UPDATE CASCADE causing this. It's all resolved now and I am glad the CTE behaves as expected. Kind regards, Tom
Re: Issue with PSQL JDBC Driver Null Pointer
On Thu, 29 Jul 2021 at 11:04, Bhavesh Mistry
wrote:
> Hello,
>
> I am getting an NPE exception in the JDBC driver 42.2.23.
>
> When the alias is null, NPE results. Is it possible to handle null value
> ? I have filled a bug against RCA
> https://github.com/vladmihalcea/hibernate-types/issues/335. But, I
> thought that PSQL Driver can handle it more gracefully.
>
> Caused by: java.lang.NullPointerException: null
> at
> org.postgresql.jdbc.TypeInfoCache.getTypeForAlias(TypeInfoCache.java:675)
> at
> org.postgresql.jdbc.TypeInfoCache.getPGArrayType(TypeInfoCache.java:519)
> at
> org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1375)
> at
> org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1399)
> at
> org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
> at
> org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
> at
> org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
> at
> com.vladmihalcea.hibernate.type.array.internal.ArraySqlTypeDescriptor$1.doBind(ArraySqlTypeDescriptor.java:36)
> at
> org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:73)
> at
> org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:276)
> at
> org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:271)
> at
> org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:39)
> at
> org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2995)
> at
> org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3292)
> at
> org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3828)
> at
> org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:107)
> at
> org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
> at
> org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
> at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
> at
> org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
> at
> org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:348)
> at
> org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
> at
> org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102)
> at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1362)
> at
> org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:453)
> at
> org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3212)
> at
> org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2380)
> at
> org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:447)
> at
> org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
> at
> org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)
> at
> org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
> at
> org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
> ... 123 common frames omitted
>
> public String getTypeForAlias(String alias) {
> String type = typeAliases.get(alias);
> if (type != null) {
> return type;
> }
> if (alias.indexOf('"') == -1) { *// THIS LINE THROWS NPE SINCE alias is
> null ** *
> type = typeAliases.get(alias.toLowerCase());
> if (type != null) {
> return type;
> }
> }
> return alias;
> }
>
>
> It would appear that improv: type alias handling in TypeInfoCache by
bokken · Pull Request #1986 · pgjdbc/pgjdbc (github.com)
should be backpatched into 42.2.24 which should solve this problem
Thanks,
Dave
>
> --
> Thanks,
>
> Bhavesh
>
>
Re: PostgreSQL reference coffee mug
El día miércoles, julio 28, 2021 a las 07:30:24p. m. +0200, Matthias Apitz escribió: > I printed the PDF on paper, cut it to the size of 7.5cm x 16cm and > wrapped it around the mugs I have here. 16cm is not correct. My mugs have > more or less 7-8cm as diameter which gives around: > > $ bc -l > >>> 7.5 * pi(2) > 23.550 > > I will go tomorrow to the copy shop and ask/measure it there... > Which means also, we have more space for more information. Resulted, that the size can be 20.12cm X 7.5cm. Attached is an updated version as PDF. Hints/bugs are welcome. matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Tear it down! Defund the Humboldt Forum! https://www.jungewelt.de/artikel/406715.humboldt-forum-feudaler-themenpark.html postgreSQL-coffee-mug.pdf Description: Adobe PDF document
Re: PostgreSQL reference coffee mug
On 29/07/21, Matthias Apitz (g...@unixarea.de) wrote: > El día miércoles, julio 28, 2021 a las 07:30:24p. m. +0200, Matthias Apitz > escribió: > Resulted, that the size can be 20.12cm X 7.5cm. Attached is an updated > version as PDF. Hints/bugs are welcome. I personally find that, after \l (list databases), connecting to a different one with \c is pretty useful. \? : to list help subjects \? options \? variables \h \watch [SEC] : is helpful, for example for keeping an eye on connections via pg_stat_activity \e | \ef | \ev : is incredibly helpful if you are working on a query, function or view which you immediately want to load into the database via the query buffer (setting the EDITOR env will help). Best done in production, of course. \set ON_ERROR_STOP on : to abort on first error \timing on : very helpful to see how fast or slowly your function or query runs Perhaps some example pg_stat_activity calls might be worth list, eg: SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE query != '' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc; # https://gist.github.com/rgreenjr/3637525 Rory
Re: Issue with PSQL JDBC Driver Null Pointer
On Thu, 29 Jul 2021 at 15:44, Bhavesh Mistry
wrote:
> Hi Dave,
>
> It still does not address the NPE issue. If an alias is NULL. What
> should be the behavior?
>
>
> public String getTypeForAlias(String alias) {
> String type = TYPE_ALIASES.get(alias);
> if (type != null) {
> return type;
> }
> type = TYPE_ALIASES.get(alias.toLowerCase()) *// NPE STILL HERE*;
> if (type == null) {
> type = alias;
> }
> //populate for future use
> TYPE_ALIASES.put(alias, type);
> return type;
> }
>
Very good question. I guess we should return null in this case.
I'll fix that
Dave
Re: Issue with PSQL JDBC Driver Null Pointer
Hi Dave,
It still does not address the NPE issue. If an alias is NULL. What should
be the behavior?
public String getTypeForAlias(String alias) {
String type = TYPE_ALIASES.get(alias);
if (type != null) {
return type;
}
type = TYPE_ALIASES.get(alias.toLowerCase()) *// NPE STILL HERE*;
if (type == null) {
type = alias;
}
//populate for future use
TYPE_ALIASES.put(alias, type);
return type;
}
Thanks,
Bhavesh
On Thu, Jul 29, 2021 at 8:44 AM Dave Cramer
wrote:
>
>
> On Thu, 29 Jul 2021 at 11:04, Bhavesh Mistry
> wrote:
>
>> Hello,
>>
>> I am getting an NPE exception in the JDBC driver 42.2.23.
>>
>> When the alias is null, NPE results. Is it possible to handle null
>> value ? I have filled a bug against RCA
>> https://github.com/vladmihalcea/hibernate-types/issues/335. But, I
>> thought that PSQL Driver can handle it more gracefully.
>>
>> Caused by: java.lang.NullPointerException: null
>> at
>> org.postgresql.jdbc.TypeInfoCache.getTypeForAlias(TypeInfoCache.java:675)
>> at
>> org.postgresql.jdbc.TypeInfoCache.getPGArrayType(TypeInfoCache.java:519)
>> at
>> org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1375)
>> at
>> org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1399)
>> at
>> org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
>> at
>> org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
>> at
>> org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
>> at
>> com.vladmihalcea.hibernate.type.array.internal.ArraySqlTypeDescriptor$1.doBind(ArraySqlTypeDescriptor.java:36)
>> at
>> org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:73)
>> at
>> org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:276)
>> at
>> org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:271)
>> at
>> org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:39)
>> at
>> org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2995)
>> at
>> org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3292)
>> at
>> org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3828)
>> at
>> org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:107)
>> at
>> org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
>> at
>> org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
>> at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
>> at
>> org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
>> at
>> org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:348)
>> at
>> org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
>> at
>> org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102)
>> at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1362)
>> at
>> org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:453)
>> at
>> org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3212)
>> at
>> org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2380)
>> at
>> org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:447)
>> at
>> org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
>> at
>> org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.access$300(JdbcResourceLocalTransactionCoordinatorImpl.java:40)
>> at
>> org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:281)
>> at
>> org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
>> ... 123 common frames omitted
>>
>> public String getTypeForAlias(String alias) {
>> String type = typeAliases.get(alias);
>> if (type != null) {
>> return type;
>> }
>> if (alias.indexOf('"') == -1) { *// THIS LINE THROWS NPE SINCE alias
>> is null ** *
>> type = typeAliases.get(alias.toLowerCase());
>> if (type != null) {
>> return type;
>> }
>>
Re: Issue with PSQL JDBC Driver Null Pointer
I would do
public String getTypeForAlias(String alias) {
if (alias==null) return null; // this shall solve NPE on
alias.toLowerCase()
String type = TYPE_ALIASES.get(alias);
if (type != null) {
return type;
}
type = TYPE_ALIASES.get(alias.toLowerCase()) *// NPE STILL HERE*;
if (type == null) {
type = alias;
}
//populate for future use
TYPE_ALIASES.put(alias, type);
return type;
}
Em 29/07/2021 16:44, Bhavesh Mistry escreveu:
Hi Dave,
It still does not address the NPE issue. If an alias is NULL. What
should be the behavior?
public String getTypeForAlias(String alias) {
String type = TYPE_ALIASES.get(alias);
if (type != null) {
return type;
}
type = TYPE_ALIASES.get(alias.toLowerCase()) *// NPE STILL HERE*;
if (type == null) {
type = alias;
}
//populate for future use
TYPE_ALIASES.put(alias, type);
return type;
}
Thanks,
Bhavesh
On Thu, Jul 29, 2021 at 8:44 AM Dave Cramer
wrote:
On Thu, 29 Jul 2021 at 11:04, Bhavesh Mistry
mailto:bhavesh.mistr...@gmail.com>>
wrote:
Hello,
I am getting an NPE exception in the JDBC driver 42.2.23.
When the alias is null, NPE results. Is it possible to handle
null value ? I have filled a bug against RCA
https://github.com/vladmihalcea/hibernate-types/issues/335
.
But, I thought that PSQL Driver can handle it more gracefully.
|Caused by: java.lang.NullPointerException: null at
org.postgresql.jdbc.TypeInfoCache.getTypeForAlias(TypeInfoCache.java:675)
at
org.postgresql.jdbc.TypeInfoCache.getPGArrayType(TypeInfoCache.java:519)
at
org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1375)
at
org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1399)
at
org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
at
org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
at
org.apache.commons.dbcp2.DelegatingConnection.createArrayOf(DelegatingConnection.java:827)
at
com.vladmihalcea.hibernate.type.array.internal.ArraySqlTypeDescriptor$1.doBind(ArraySqlTypeDescriptor.java:36)
at
org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:73)
at
org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:276)
at
org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:271)
at
org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:39)
at
org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2995)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3292)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3828)
at
org.hibernate.action.internal.EntityInsertAction.execute(EntityInsertAction.java:107)
at
org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
at
org.hibernate.engine.spi.ActionQueue.lambda$executeActions$1(ActionQueue.java:478)
at
java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
at
org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:475)
at
org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:348)
at
org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40)
at
org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:102)
at
org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1362)
at
org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:453)
at
org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:3212)
at
org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:2380)
at
org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:447)
at
org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:183)
at
org.hibernate.resource.transaction.backend.jdb
