Thread: Vs NULL

Vs NULL

From
sridhar bamandlapally
Date:
Hi All

We are testing our Oracle compatible business applications on PostgreSQL database,

the issue we are facing is <empty string> Vs NULL

In Oracle '' (<empty string>) and NULL are treated as NULL

but, in PostgreSQL '' <empty string> not treated as NULL

I need some implicit way in PostgreSQL where ''<empty string> can be treated as NULL

Please,

Thanks
Sridhar BN

Re: [PERFORM] Vs NULL

From
Pavel Stehule
Date:
Hi

2015-02-09 12:22 GMT+01:00 sridhar bamandlapally <sridhar.bn1@gmail.com>:
Hi All

We are testing our Oracle compatible business applications on PostgreSQL database,

the issue we are facing is <empty string> Vs NULL

In Oracle '' (<empty string>) and NULL are treated as NULL

but, in PostgreSQL '' <empty string> not treated as NULL

I need some implicit way in PostgreSQL where ''<empty string> can be treated as NULL

It is not possible in PostgreSQL.  PostgreSQL respects ANSI SQL standard - Oracle not.

Regards

Pavel

p.s. theoretically you can overwrite a type operators to support Oracle behave, but you should not be sure about unexpected negative side effects.

 

Please,

Thanks
Sridhar BN


Re: [PERFORM] Vs NULL

From
Nikolas Everett
Date:

Its been a while since I really worked with Postgres, but could you write a trigger to convert empty string to null on save?  You'd have to carefully apply it everywhere but it'd get you the searching for null finds empty. If that is all you do the you've got it.

Essentially, there isn't a switch for it but you can do it with some mechanisms.

Nik

On Feb 9, 2015 6:54 AM, "Pavel Stehule" <pavel.stehule@gmail.com> wrote:
Hi

2015-02-09 12:22 GMT+01:00 sridhar bamandlapally <sridhar.bn1@gmail.com>:
Hi All

We are testing our Oracle compatible business applications on PostgreSQL database,

the issue we are facing is <empty string> Vs NULL

In Oracle '' (<empty string>) and NULL are treated as NULL

but, in PostgreSQL '' <empty string> not treated as NULL

I need some implicit way in PostgreSQL where ''<empty string> can be treated as NULL

It is not possible in PostgreSQL.  PostgreSQL respects ANSI SQL standard - Oracle not.

Regards

Pavel

p.s. theoretically you can overwrite a type operators to support Oracle behave, but you should not be sure about unexpected negative side effects.

 

Please,

Thanks
Sridhar BN


Re: [PERFORM] Vs NULL

From
Marc Mamin
Date:

>>Hi
>>
>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally <sridhar.bn1@gmail.com>:
>>
>>    Hi All
>>
>>    We are testing our Oracle compatible business applications on PostgreSQL database,
>>
>>    the issue we are facing is <empty string> Vs NULL
>>
>>    In Oracle '' (<empty string>) and NULL are treated as NULL
>>
>>    but, in PostgreSQL '' <empty string> not treated as NULL
>>
>>    I need some implicit way in PostgreSQL where ''<empty string> can be treated as NULL

>It is not possible in PostgreSQL.  PostgreSQL respects ANSI SQL standard - Oracle not.
>
>Regards
>
>Pavel
>
>p.s. theoretically you can overwrite a type operators to support Oracle behave, but you should not be sure about unexpected negative side effects.


A clean way would be to disallow empty strings on the PG side.
This is somewhat combersome depending on how dynamic your model is
and add some last on your db though.


ALTER TABLE tablename ADD CONSTRAINT tablename_not_empty_ck
  CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL ...) IS NULL)

-- and to ensure compatibility with your app or migration:

CREATE OR REPLACE FUNCTION tablename_setnull_trf()
  RETURNS trigger AS
$BODY$
BEGIN
-- for all *string* columns
   NEW.colname1 = NULLIF (colname1,'');
   NEW.colname2 = NULLIF (colname2,'');
   NEW.colname3 = NULLIF (colname3,'');
RETURN NEW;
END;
$BODY$

CREATE TRIGGER tablename_setnull_tr
  BEFORE INSERT OR UPDATE
  ON tablename
  FOR EACH ROW
  EXECUTE PROCEDURE tablename_setnull_trf();
 
You can query the pg catalog to generate all required statements.
A possible issue is the order in which triggers are fired, when more than one exist for a given table:
"If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name"
( http://www.postgresql.org/docs/9.3/static/trigger-definition.html )

regards,

Marc Mamin

Re: Vs NULL

From
Geoff Winkless
Date:
On 9 February 2015 at 11:22, sridhar bamandlapally <sridhar.bn1@gmail.com> wrote:
the issue we are facing is <empty string> Vs NULL

In Oracle '' (<empty string>) and NULL are treated as NULL

but, in PostgreSQL '' <empty string> not treated as NULL

I need some implicit way in PostgreSQL where ''<empty string> can be treated as NULL


The Right Thing to do is to fix your application, and don't use broken DBMSes: NULL should not denote anything except "this value is not set". If you count an empty string as null, how do you represent the empty string?

Oracle's own documentation suggests that developers should not rely on this behaviour since it may change in the future.

So Do The Right Thing now, and you won't get bitten later.

Geoff​
 

Re: Vs NULL

From
Geoff Winkless
Date:
On 9 February 2015 at 12:48, Geoff Winkless <pgsqladmin@geoff.dj> wrote:
Oracle's own documentation suggests that developers should not rely on this behaviour since it may change in the future.

Just in case you're looking for it:



Note:Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.


Geoff​
 

Re: [PERFORM] Vs NULL

From
Marc Mamin
Date:

>>>Hi
>>>
>>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally <sridhar.bn1@gmail.com>:
>>>
>>>    Hi All
>>>
>>>    We are testing our Oracle compatible business applications on PostgreSQL database,
>>>
>>>    the issue we are facing is <empty string> Vs NULL
>>>
>>>    In Oracle '' (<empty string>) and NULL are treated as NULL
>>>
>>>    but, in PostgreSQL '' <empty string> not treated as NULL
>>>
>>>    I need some implicit way in PostgreSQL where ''<empty string> can be treated as NULL
>
>>It is not possible in PostgreSQL.  PostgreSQL respects ANSI SQL standard - Oracle not.
>>
>>Regards
>>
>>Pavel
>>
>>p.s. theoretically you can overwrite a type operators to support Oracle behave, but you should not be sure about unexpected negative side effects.
>
>
>A clean way would be to disallow empty strings on the PG side.
>This is somewhat combersome depending on how dynamic your model is
>and add some last on your db though.

hmm, you could also consider disallowing NULLs, i.e. force empty strings.
this may result in a better compatibility although unwise from postgres point of view (see null storage in PG)
and neither way allow a compatibility out of the box:

                    Postgres     ORACLE
'' IS NULL       false           true
NULL || 'foo'   NULL          'foo'
                
as mention in another post, you need to check/fix your application.              

>
>ALTER TABLE tablename ADD CONSTRAINT tablename_not_empty_ck
>  CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL ...) IS NULL)

oops, this shold be
   CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL ...))

>
>-- and to ensure compatibility with your app or migration:
>
>CREATE OR REPLACE FUNCTION tablename_setnull_trf()
>  RETURNS trigger AS
>$BODY$
>BEGIN
>-- for all *string* columns
>   NEW.colname1 = NULLIF (colname1,'');
>   NEW.colname2 = NULLIF (colname2,'');
>   NEW.colname3 = NULLIF (colname3,'');
>RETURN NEW;
>END;
>$BODY$
>
>CREATE TRIGGER tablename_setnull_tr
>  BEFORE INSERT OR UPDATE
>  ON tablename
>  FOR EACH ROW
>  EXECUTE PROCEDURE tablename_setnull_trf();
>  
>You can query the pg catalog to generate all required statements.
>A possible issue is the order in which triggers are fired, when more than one exist for a given table:
>"If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name"
>( http://www.postgresql.org/docs/9.3/static/trigger-definition.html )
>
>regards,
>
>Marc Mamin

Re: [PERFORM] Vs NULL

From
sridhar bamandlapally
Date:
In application code is 

while inserting/updating: INSERT/UPDATE into ... ( '' )  - which is empty string in PG, and in Oracle its NULL

while selecting: SELECT ... WHERE column IS NULL / NOT NULL

the issue is, while DML its empty string and while SELECT its comparing with NULL





On Mon, Feb 9, 2015 at 6:32 PM, Marc Mamin <M.Mamin@intershop.de> wrote:

>>>Hi
>>>
>>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally <sridhar.bn1@gmail.com>:
>>>
>>>    Hi All
>>>
>>>    We are testing our Oracle compatible business applications on PostgreSQL database,
>>>
>>>    the issue we are facing is <empty string> Vs NULL
>>>
>>>    In Oracle '' (<empty string>) and NULL are treated as NULL
>>>
>>>    but, in PostgreSQL '' <empty string> not treated as NULL
>>>
>>>    I need some implicit way in PostgreSQL where ''<empty string> can be treated as NULL
>
>>It is not possible in PostgreSQL.  PostgreSQL respects ANSI SQL standard - Oracle not.
>>
>>Regards
>>
>>Pavel
>>
>>p.s. theoretically you can overwrite a type operators to support Oracle behave, but you should not be sure about unexpected negative side effects.
>
>
>A clean way would be to disallow empty strings on the PG side.
>This is somewhat combersome depending on how dynamic your model is
>and add some last on your db though.

hmm, you could also consider disallowing NULLs, i.e. force empty strings.
this may result in a better compatibility although unwise from postgres point of view (see null storage in PG)
and neither way allow a compatibility out of the box:

                    Postgres     ORACLE
'' IS NULL       false           true
NULL || 'foo'   NULL          'foo'
                
as mention in another post, you need to check/fix your application.              

>
>ALTER TABLE tablename ADD CONSTRAINT tablename_not_empty_ck
>  CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL ...) IS NULL)

oops, this shold be
   CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL ...))

>
>-- and to ensure compatibility with your app or migration:
>
>CREATE OR REPLACE FUNCTION tablename_setnull_trf()
>  RETURNS trigger AS
>$BODY$
>BEGIN
>-- for all *string* columns
>   NEW.colname1 = NULLIF (colname1,'');
>   NEW.colname2 = NULLIF (colname2,'');
>   NEW.colname3 = NULLIF (colname3,'');
>RETURN NEW;
>END;
>$BODY$
>
>CREATE TRIGGER tablename_setnull_tr
>  BEFORE INSERT OR UPDATE
>  ON tablename
>  FOR EACH ROW
>  EXECUTE PROCEDURE tablename_setnull_trf();
>  
>You can query the pg catalog to generate all required statements.
>A possible issue is the order in which triggers are fired, when more than one exist for a given table:
>"If more than one trigger is defined for the same event on the same relation, the triggers will be fired in alphabetical order by trigger name"
>( http://www.postgresql.org/docs/9.3/static/trigger-definition.html )
>
>regards,
>
>Marc Mamin

Re: [PERFORM] Vs NULL

From
Scott Ribe
Date:
On Feb 9, 2015, at 8:53 PM, sridhar bamandlapally <sridhar.bn1@gmail.com> wrote:
>
> the issue is, while DML its empty string and while SELECT its comparing with NULL

The issue is, empty string is NOT the same as null, and expecting select for null to match empty strings is a bug,
whichyou need to fix. 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice






Re: [PERFORM] Vs NULL

From
David G Johnston
Date:
sridhar bamandlapally wrote
> In application code is
>
> while inserting/updating: INSERT/UPDATE into ... ( '' )  - which is empty
> string in PG, and in Oracle its NULL
>
> while selecting: SELECT ... WHERE column IS NULL / NOT NULL
>
> the issue is, while DML its empty string and while SELECT its comparing
> with NULL

If this is the extent of your problem then you can add table triggers to
change the empty-string input so that the result of the insert/update is
NULL.  Then all of your selects can use IS NULL for their comparisons just
like they do now.

That is as "implicit" as you are going to get without actually fixing the
underlying problem.

David J.






--
View this message in context: http://postgresql.nabble.com/empty-string-Vs-NULL-tp5837188p5837308.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: [PERFORM] Vs NULL

From
Florent Guillaume
Date:
Hi,

Please take this to another list, this has little to do with
PostgreSQL admin or performance.

Florent



On Tue, Feb 10, 2015 at 4:53 AM, sridhar bamandlapally
<sridhar.bn1@gmail.com> wrote:
> In application code is
>
> while inserting/updating: INSERT/UPDATE into ... ( '' )  - which is empty
> string in PG, and in Oracle its NULL
>
> while selecting: SELECT ... WHERE column IS NULL / NOT NULL
>
> the issue is, while DML its empty string and while SELECT its comparing with
> NULL
>
>
>
>
>
> On Mon, Feb 9, 2015 at 6:32 PM, Marc Mamin <M.Mamin@intershop.de> wrote:
>>
>>
>> >>>Hi
>> >>>
>> >>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally
>> >>> <sridhar.bn1@gmail.com>:
>> >>>
>> >>>    Hi All
>> >>>
>> >>>    We are testing our Oracle compatible business applications on
>> >>> PostgreSQL database,
>> >>>
>> >>>    the issue we are facing is <empty string> Vs NULL
>> >>>
>> >>>    In Oracle '' (<empty string>) and NULL are treated as NULL
>> >>>
>> >>>    but, in PostgreSQL '' <empty string> not treated as NULL
>> >>>
>> >>>    I need some implicit way in PostgreSQL where ''<empty string> can
>> >>> be treated as NULL
>> >
>> >>It is not possible in PostgreSQL.  PostgreSQL respects ANSI SQL standard
>> >> - Oracle not.
>> >>
>> >>Regards
>> >>
>> >>Pavel
>> >>
>> >>p.s. theoretically you can overwrite a type operators to support Oracle
>> >> behave, but you should not be sure about unexpected negative side effects.
>> >
>> >
>> >A clean way would be to disallow empty strings on the PG side.
>> >This is somewhat combersome depending on how dynamic your model is
>> >and add some last on your db though.
>>
>> hmm, you could also consider disallowing NULLs, i.e. force empty strings.
>> this may result in a better compatibility although unwise from postgres
>> point of view (see null storage in PG)
>> and neither way allow a compatibility out of the box:
>>
>>                     Postgres     ORACLE
>> '' IS NULL       false           true
>> NULL || 'foo'   NULL          'foo'
>>
>> as mention in another post, you need to check/fix your application.
>>
>> >
>> >ALTER TABLE tablename ADD CONSTRAINT tablename_not_empty_ck
>> >  CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL
>> > ...) IS NULL)
>>
>> oops, this shold be
>>    CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL
>> ...))
>>
>> >
>> >-- and to ensure compatibility with your app or migration:
>> >
>> >CREATE OR REPLACE FUNCTION tablename_setnull_trf()
>> >  RETURNS trigger AS
>> >$BODY$
>> >BEGIN
>> >-- for all *string* columns
>> >   NEW.colname1 = NULLIF (colname1,'');
>> >   NEW.colname2 = NULLIF (colname2,'');
>> >   NEW.colname3 = NULLIF (colname3,'');
>> >RETURN NEW;
>> >END;
>> >$BODY$
>> >
>> >CREATE TRIGGER tablename_setnull_tr
>> >  BEFORE INSERT OR UPDATE
>> >  ON tablename
>> >  FOR EACH ROW
>> >  EXECUTE PROCEDURE tablename_setnull_trf();
>> >
>> >You can query the pg catalog to generate all required statements.
>> >A possible issue is the order in which triggers are fired, when more than
>> > one exist for a given table:
>> >"If more than one trigger is defined for the same event on the same
>> > relation, the triggers will be fired in alphabetical order by trigger name"
>> >( http://www.postgresql.org/docs/9.3/static/trigger-definition.html )
>> >
>> >regards,
>> >
>> >Marc Mamin
>
>



--
Florent Guillaume, Director of R&D, Nuxeo
Open Source Content Management Platform for Business Apps
http://www.nuxeo.com   http://community.nuxeo.com


Re: [PERFORM] Vs NULL

From
sridhar bamandlapally
Date:
The first contact of database migration/issues is DBA (admin), 

accept performance is not required

Thanks
Sridhar BN



On Tue, Feb 10, 2015 at 3:37 PM, Florent Guillaume <fg@nuxeo.com> wrote:
Hi,

Please take this to another list, this has little to do with
PostgreSQL admin or performance.

Florent



On Tue, Feb 10, 2015 at 4:53 AM, sridhar bamandlapally
<sridhar.bn1@gmail.com> wrote:
> In application code is
>
> while inserting/updating: INSERT/UPDATE into ... ( '' )  - which is empty
> string in PG, and in Oracle its NULL
>
> while selecting: SELECT ... WHERE column IS NULL / NOT NULL
>
> the issue is, while DML its empty string and while SELECT its comparing with
> NULL
>
>
>
>
>
> On Mon, Feb 9, 2015 at 6:32 PM, Marc Mamin <M.Mamin@intershop.de> wrote:
>>
>>
>> >>>Hi
>> >>>
>> >>>2015-02-09 12:22 GMT+01:00 sridhar bamandlapally
>> >>> <sridhar.bn1@gmail.com>:
>> >>>
>> >>>    Hi All
>> >>>
>> >>>    We are testing our Oracle compatible business applications on
>> >>> PostgreSQL database,
>> >>>
>> >>>    the issue we are facing is <empty string> Vs NULL
>> >>>
>> >>>    In Oracle '' (<empty string>) and NULL are treated as NULL
>> >>>
>> >>>    but, in PostgreSQL '' <empty string> not treated as NULL
>> >>>
>> >>>    I need some implicit way in PostgreSQL where ''<empty string> can
>> >>> be treated as NULL
>> >
>> >>It is not possible in PostgreSQL.  PostgreSQL respects ANSI SQL standard
>> >> - Oracle not.
>> >>
>> >>Regards
>> >>
>> >>Pavel
>> >>
>> >>p.s. theoretically you can overwrite a type operators to support Oracle
>> >> behave, but you should not be sure about unexpected negative side effects.
>> >
>> >
>> >A clean way would be to disallow empty strings on the PG side.
>> >This is somewhat combersome depending on how dynamic your model is
>> >and add some last on your db though.
>>
>> hmm, you could also consider disallowing NULLs, i.e. force empty strings.
>> this may result in a better compatibility although unwise from postgres
>> point of view (see null storage in PG)
>> and neither way allow a compatibility out of the box:
>>
>>                     Postgres     ORACLE
>> '' IS NULL       false           true
>> NULL || 'foo'   NULL          'foo'
>>
>> as mention in another post, you need to check/fix your application.
>>
>> >
>> >ALTER TABLE tablename ADD CONSTRAINT tablename_not_empty_ck
>> >  CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL
>> > ...) IS NULL)
>>
>> oops, this shold be
>>    CHECK (false= (colname1 IS NULL OR colname2 IS NULL OR colname3 IS NULL
>> ...))
>>
>> >
>> >-- and to ensure compatibility with your app or migration:
>> >
>> >CREATE OR REPLACE FUNCTION tablename_setnull_trf()
>> >  RETURNS trigger AS
>> >$BODY$
>> >BEGIN
>> >-- for all *string* columns
>> >   NEW.colname1 = NULLIF (colname1,'');
>> >   NEW.colname2 = NULLIF (colname2,'');
>> >   NEW.colname3 = NULLIF (colname3,'');
>> >RETURN NEW;
>> >END;
>> >$BODY$
>> >
>> >CREATE TRIGGER tablename_setnull_tr
>> >  BEFORE INSERT OR UPDATE
>> >  ON tablename
>> >  FOR EACH ROW
>> >  EXECUTE PROCEDURE tablename_setnull_trf();
>> >
>> >You can query the pg catalog to generate all required statements.
>> >A possible issue is the order in which triggers are fired, when more than
>> > one exist for a given table:
>> >"If more than one trigger is defined for the same event on the same
>> > relation, the triggers will be fired in alphabetical order by trigger name"
>> >( http://www.postgresql.org/docs/9.3/static/trigger-definition.html )
>> >
>> >regards,
>> >
>> >Marc Mamin
>
>



--
Florent Guillaume, Director of R&D, Nuxeo
Open Source Content Management Platform for Business Apps
http://www.nuxeo.com   http://community.nuxeo.com

getting client_addr not as a superuser

From
Михаил
Date:
Hi all,
I have a PG 9.3 and a streaming replication and I need standby ip address in the monitoring. To get that i can run 

select client_addr from pg_stat_replication

but i have to connect as a superuser what's not desirable.

As i see in that view, it uses two functions: pg_stat_get_activity and pg_stat_get_wal_senders and one table pg_authid. As i don't need role information, i've cutted the table from the query and got the following query:

SELECT s.pid, s.client_addr
   FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port)
               ,pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state)
 WHERE s.pid = w.pid;
When i run it as a superuser, everything is ok, when i run it as an ordinary user, the client_addr is NULL. As the function pg_stat_get_wal_senders() returns the result, the problem is in receiving the address from pg_stat_get_activity.

Using/granting pg_stat_get_backend_client_addr() is not solving the problem.

Is there any way to get client_addr value running not as a superuser?


Regards, Mikhail

Re: getting client_addr not as a superuser

From
Jov
Date:

you can create a function with security differ option as superuser.

2015年2月10日 8:22 PM于 "Михаил" <bemewe@mail.ru>写道:
Hi all,
I have a PG 9.3 and a streaming replication and I need standby ip address in the monitoring. To get that i can run 

select client_addr from pg_stat_replication

but i have to connect as a superuser what's not desirable.

As i see in that view, it uses two functions: pg_stat_get_activity and pg_stat_get_wal_senders and one table pg_authid. As i don't need role information, i've cutted the table from the query and got the following query:

SELECT s.pid, s.client_addr
   FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port)
               ,pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state)
 WHERE s.pid = w.pid;
When i run it as a superuser, everything is ok, when i run it as an ordinary user, the client_addr is NULL. As the function pg_stat_get_wal_senders() returns the result, the problem is in receiving the address from pg_stat_get_activity.

Using/granting pg_stat_get_backend_client_addr() is not solving the problem.

Is there any way to get client_addr value running not as a superuser?


Regards, Mikhail

Re: getting client_addr not as a superuser

From
Luis Antonio Dias de Sá Junior
Date:
Hi,

Have you try to put replication permissions? 

Ex. CREATE ROLE username LOGIN
  PASSWORD 'bla'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE REPLICATION;

2015-02-10 10:53 GMT-02:00 Jov <amutu@amutu.com>:

you can create a function with security differ option as superuser.

2015年2月10日 8:22 PM于 "Михаил" <bemewe@mail.ru>写道:

Hi all,
I have a PG 9.3 and a streaming replication and I need standby ip address in the monitoring. To get that i can run 

select client_addr from pg_stat_replication

but i have to connect as a superuser what's not desirable.

As i see in that view, it uses two functions: pg_stat_get_activity and pg_stat_get_wal_senders and one table pg_authid. As i don't need role information, i've cutted the table from the query and got the following query:

SELECT s.pid, s.client_addr
   FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port)
               ,pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state)
 WHERE s.pid = w.pid;
When i run it as a superuser, everything is ok, when i run it as an ordinary user, the client_addr is NULL. As the function pg_stat_get_wal_senders() returns the result, the problem is in receiving the address from pg_stat_get_activity.

Using/granting pg_stat_get_backend_client_addr() is not solving the problem.

Is there any way to get client_addr value running not as a superuser?


Regards, Mikhail




--
Luis Antonio Dias de Sá Junior

Re: [PERFORM] Vs NULL

From
Scott Ribe
Date:
On Feb 10, 2015, at 3:40 AM, sridhar bamandlapally <sridhar.bn1@gmail.com> wrote:
>
> The first contact of database migration/issues is DBA (admin),

This is a SQL usage issue, not a db admin issue, and so is most appropriate for the pgsql-general list. (Anyway, your
questionhas been answered 4 times now.) 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice






Re[2]: [ADMIN] getting client_addr not as a superuser

From
Михаил
Date:
Hi,
replication permissions doesn't help:

=> \du zabbix
List of roles
Role name │ Attributes │ Member of
───────────┼─────────────┼───────────
zabbix │ Replication │ {}

[local] zabbix@postgres
=> select client_addr from pg_stat_replication;
client_addr
─────────────
NULL
(1 row)
Seems like for that moment function with security definer is the only solution, though it smells like workaround.


Вторник, 10 февраля 2015, 12:11 -02:00 от Luis Antonio Dias de Sá Junior <luisjunior.sa@gmail.com>:

Hi,

Have you try to put replication permissions? 

Ex. CREATE ROLE username LOGIN
  PASSWORD 'bla'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE REPLICATION;

2015-02-10 10:53 GMT-02:00 Jov <amutu@amutu.com>:

you can create a function with security differ option as superuser.

2015年2月10日 8:22 PM于 "Михаил" <bemewe@mail.ru>写道:

Hi all,
I have a PG 9.3 and a streaming replication and I need standby ip address in the monitoring. To get that i can run 

select client_addr from pg_stat_replication

but i have to connect as a superuser what's not desirable.

As i see in that view, it uses two functions: pg_stat_get_activity and pg_stat_get_wal_senders and one table pg_authid. As i don't need role information, i've cutted the table from the query and got the following query:

SELECT s.pid, s.client_addr
   FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port)
               ,pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state)
 WHERE s.pid = w.pid;
When i run it as a superuser, everything is ok, when i run it as an ordinary user, the client_addr is NULL. As the function pg_stat_get_wal_senders() returns the result, the problem is in receiving the address from pg_stat_get_activity.

Using/granting pg_stat_get_backend_client_addr() is not solving the problem.

Is there any way to get client_addr value running not as a superuser?


Regards, Mikhail




--
Luis Antonio Dias de Sá Junior


С уважением,

bemewe@mail.ru