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
postgres=# \c postgres user1 You are now connected to database "postgres" as user "user1".
postgres=# create table s1.new (a integer); CREATE TABLE
postgres=# grant select on s1.new to user2 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; ^
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.