Обсуждение: proc state as "Idle_in_transaction" in pg_stat_activity
Hi All,
Good Evening.
Could someone please suggest the process to resolve the issue.
Issue : proc state as "Idle_in_transaction"
Env: Postgres 9.5.15
Job Schedulers : Tomcat ( For running procs).
We are using tomcat as scheduler for running few jobs.we are observing one of the proc state as "Idle_in_transaction" and due to this remaining other dependent procs get stucked and causing waiting for ever.
If we ran same proc from psql and pgadmin we could able to run successfully and able to get response and no "Idle_in_transaction" state.
proc: select msg,rescode from schema.proc_name('arg1','arg2');
We are not sure from where we have to start troubleshooting.
Thanks & Regards,
Postgadm.
Good Evening.
Could someone please suggest the process to resolve the issue.
Issue : proc state as "Idle_in_transaction"
Env: Postgres 9.5.15
Job Schedulers : Tomcat ( For running procs).
We are using tomcat as scheduler for running few jobs.we are observing one of the proc state as "Idle_in_transaction" and due to this remaining other dependent procs get stucked and causing waiting for ever.
If we ran same proc from psql and pgadmin we could able to run successfully and able to get response and no "Idle_in_transaction" state.
proc: select msg,rescode from schema.proc_name('arg1','arg2');
We are not sure from where we have to start troubleshooting.
Thanks & Regards,
Postgadm.
On Sun, Apr 12, 2020 at 5:37 AM postgann2020 s <postgann2020@gmail.com> wrote:
If we ran same proc from psql and pgadmin we could able to run successfully and able to get response and no "Idle_in_transaction" state.[...]We are not sure from where we have to start troubleshooting.
Probably something isn't issuing "COMMIT;" or "con.commit();" when it should be.
David J.
On Sun, Apr 12, 2020 at 8:37 AM postgann2020 s <postgann2020@gmail.com> wrote:
Hi All,
Good Evening.
Could someone please suggest the process to resolve the issue.
Issue : proc state as "Idle_in_transaction"
Env: Postgres 9.5.15
Job Schedulers : Tomcat ( For running procs).
We are using tomcat as scheduler for running few jobs.we are observing one of the proc state as "Idle_in_transaction" and due to this remaining other dependent procs get stucked and causing waiting for ever.
If we ran same proc from psql and pgadmin we could able to run successfully and able to get response and no "Idle_in_transaction" state.
proc: select msg,rescode from schema.proc_name('arg1','arg2');
If you run just this in psql, you will be in autocommit mode. The statement will run in its own transaction which commit as soon as the statement finishes.
The problem is not with the line you show, it is in what happens before (a transaction is opened) and after (it is not committed) that line.
Cheers,
Jeff
Thanks To all of you.
On Mon, Apr 13, 2020, 10:54 PM Jeff Janes <jeff.janes@gmail.com> wrote:
On Sun, Apr 12, 2020 at 8:37 AM postgann2020 s <postgann2020@gmail.com> wrote:Hi All,
Good Evening.
Could someone please suggest the process to resolve the issue.
Issue : proc state as "Idle_in_transaction"
Env: Postgres 9.5.15
Job Schedulers : Tomcat ( For running procs).
We are using tomcat as scheduler for running few jobs.we are observing one of the proc state as "Idle_in_transaction" and due to this remaining other dependent procs get stucked and causing waiting for ever.
If we ran same proc from psql and pgadmin we could able to run successfully and able to get response and no "Idle_in_transaction" state.
proc: select msg,rescode from schema.proc_name('arg1','arg2');If you run just this in psql, you will be in autocommit mode. The statement will run in its own transaction which commit as soon as the statement finishes.The problem is not with the line you show, it is in what happens before (a transaction is opened) and after (it is not committed) that line.Cheers,Jeff
hi Pgsql-Admin,
I am new to Postgres and naive in understanding
I tried to create a table in a schema and then given privilege to some other user, still he is not able to access. Below are the things I tried:
postgres=# alter schema s1 owner to user1;
ALTER SCHEMA
ALTER SCHEMA
postgres=# \c postgres user1
You are now connected to database "postgres" as user "user1".
You are now connected to database "postgres" as user "user1".
postgres=# create table s1.new (a integer);
CREATE TABLE
CREATE TABLE
postgres=# grant select on s1.new to user2
postgres-# ;
GRANT
postgres-# ;
GRANT
postgres=# \c postgres user2
You are now connected to database "postgres" as user "user2".
postgres=> select * from s1.new;
ERROR: permission denied for schema s1
LINE 1: select * from s1.new;
^
You are now connected to database "postgres" as user "user2".
postgres=> select * from s1.new;
ERROR: permission denied for schema s1
LINE 1: select * from s1.new;
^
Regards
Ankush
On Tue, Apr 14, 2020 at 12:36 PM postadmin2020 S <postgadm2020@gmail.com> wrote:
Thanks To all of you.On Mon, Apr 13, 2020, 10:54 PM Jeff Janes <jeff.janes@gmail.com> wrote:On Sun, Apr 12, 2020 at 8:37 AM postgann2020 s <postgann2020@gmail.com> wrote:Hi All,
Good Evening.
Could someone please suggest the process to resolve the issue.
Issue : proc state as "Idle_in_transaction"
Env: Postgres 9.5.15
Job Schedulers : Tomcat ( For running procs).
We are using tomcat as scheduler for running few jobs.we are observing one of the proc state as "Idle_in_transaction" and due to this remaining other dependent procs get stucked and causing waiting for ever.
If we ran same proc from psql and pgadmin we could able to run successfully and able to get response and no "Idle_in_transaction" state.
proc: select msg,rescode from schema.proc_name('arg1','arg2');If you run just this in psql, you will be in autocommit mode. The statement will run in its own transaction which commit as soon as the statement finishes.The problem is not with the line you show, it is in what happens before (a transaction is opened) and after (it is not committed) that line.Cheers,Jeff
Best Regards,
Ankush Chawla
Ankush Chawla
A couple of things not noted. First do NOT use the postgres database for any work. That is a system db, not intended for use by any roles. Nor should you use the template0 or template1 databases unless you understand what those are going to do. Now, grant the user the ability to connect to the database, then grant permission to the schema if this table is not in the public schema.
Easiest way:
Easiest way:
GRANT ALL ON DATABASE <db name> TO user2;
GRANT ALL ON SCHEMA s1 TO user2:
But again, I wouldn’t use the postgres database for any work.
—
Jay
Sent from my iPad
On Apr 15, 2020, at 2:05 PM, Ankush Chawla <ankushchawla03@gmail.com> wrote:
hi Pgsql-Admin,I am new to Postgres and naive in understandingI tried to create a table in a schema and then given privilege to some other user, still he is not able to access. Below are the things I tried:postgres=# alter schema s1 owner to user1;
ALTER SCHEMApostgres=# \c postgres user1
You are now connected to database "postgres" as user "user1".postgres=# create table s1.new (a integer);
CREATE TABLEpostgres=# grant select on s1.new to user2
postgres-# ;
GRANTpostgres=# \c postgres user2
You are now connected to database "postgres" as user "user2".
postgres=> select * from s1.new;
ERROR: permission denied for schema s1
LINE 1: select * from s1.new;
^RegardsAnkushOn Tue, Apr 14, 2020 at 12:36 PM postadmin2020 S <postgadm2020@gmail.com> wrote:Thanks To all of you.On Mon, Apr 13, 2020, 10:54 PM Jeff Janes <jeff.janes@gmail.com> wrote:On Sun, Apr 12, 2020 at 8:37 AM postgann2020 s <postgann2020@gmail.com> wrote:Hi All,
Good Evening.
Could someone please suggest the process to resolve the issue.
Issue : proc state as "Idle_in_transaction"
Env: Postgres 9.5.15
Job Schedulers : Tomcat ( For running procs).
We are using tomcat as scheduler for running few jobs.we are observing one of the proc state as "Idle_in_transaction" and due to this remaining other dependent procs get stucked and causing waiting for ever.
If we ran same proc from psql and pgadmin we could able to run successfully and able to get response and no "Idle_in_transaction" state.
proc: select msg,rescode from schema.proc_name('arg1','arg2');If you run just this in psql, you will be in autocommit mode. The statement will run in its own transaction which commit as soon as the statement finishes.The problem is not with the line you show, it is in what happens before (a transaction is opened) and after (it is not committed) that line.Cheers,Jeff--Best Regards,
Ankush Chawla
Hi ankush,
Execute below mentioned commands with postgres user and try access to tables with required user it will work.
1.GRANT USAGE ON SCHEMA schema_name TO username;
2.GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;
Regards,
Mohammed Afsar
Database Engineer
On Wed, Apr 15, 2020, 11:35 PM Ankush Chawla <ankushchawla03@gmail.com> wrote:
hi Pgsql-Admin,I am new to Postgres and naive in understandingI tried to create a table in a schema and then given privilege to some other user, still he is not able to access. Below are the things I tried:postgres=# alter schema s1 owner to user1;
ALTER SCHEMApostgres=# \c postgres user1
You are now connected to database "postgres" as user "user1".postgres=# create table s1.new (a integer);
CREATE TABLEpostgres=# grant select on s1.new to user2
postgres-# ;
GRANTpostgres=# \c postgres user2
You are now connected to database "postgres" as user "user2".
postgres=> select * from s1.new;
ERROR: permission denied for schema s1
LINE 1: select * from s1.new;
^RegardsAnkushOn Tue, Apr 14, 2020 at 12:36 PM postadmin2020 S <postgadm2020@gmail.com> wrote:Thanks To all of you.On Mon, Apr 13, 2020, 10:54 PM Jeff Janes <jeff.janes@gmail.com> wrote:On Sun, Apr 12, 2020 at 8:37 AM postgann2020 s <postgann2020@gmail.com> wrote:Hi All,
Good Evening.
Could someone please suggest the process to resolve the issue.
Issue : proc state as "Idle_in_transaction"
Env: Postgres 9.5.15
Job Schedulers : Tomcat ( For running procs).
We are using tomcat as scheduler for running few jobs.we are observing one of the proc state as "Idle_in_transaction" and due to this remaining other dependent procs get stucked and causing waiting for ever.
If we ran same proc from psql and pgadmin we could able to run successfully and able to get response and no "Idle_in_transaction" state.
proc: select msg,rescode from schema.proc_name('arg1','arg2');If you run just this in psql, you will be in autocommit mode. The statement will run in its own transaction which commit as soon as the statement finishes.The problem is not with the line you show, it is in what happens before (a transaction is opened) and after (it is not committed) that line.Cheers,Jeff--Best Regards,
Ankush Chawla
You should write new emails and not reply to unrelated ones with individuals already copied and without removing the bogus quoted email.
On Wed, Apr 15, 2020 at 11:05 AM Ankush Chawla <ankushchawla03@gmail.com> wrote:
ERROR: permission denied for schema s1
LINE 1: select * from s1.new;
This error seems mostly self-explanatory - the user "user2" doesn't have permission to use schema "s1". See the documentation for how to grant this permission - its similar to how you granted select on the table.
David J.
On Wed, Apr 15, 2020 at 11:19 AM John Scalia <jayknowsunix@gmail.com> wrote:
A couple of things not noted. First do NOT use the postgres database for any work.
No, its not, is a user database that is also installed by default. It is in fact a desirable database to use for development and testing. Maybe avoid using it for production workloads but that arguable.
Nor should you use the template0 or template1 databases unless you understand what those are going to do.
This, yes.
David J.