[GENERAL] function returning a merge of the same query executed X time

[Marc-André Goderre]

Hi all,
I'm having difficulties to  create a function that should execute X time the 
same query and return  their results as a single table.
I tried this way but it don't work :
Thanks to help.

create function cm_get_json_loop_info(
IN profile integer,
IN distance double precision DEFAULT 10,
IN x_start double precision DEFAULT 0,
IN y_start double precision DEFAULT 0,
IN nbr integer default 1)

returns setof json as 
$BODY$
declare jsona json;
BEGIN
for json a i in 1.. nbr loop

select row_to_json(q) 
from (select row_number() over() as id, sum(cost) as 
total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data 
from (select * from cm_get_loop_route_4(2, 10, -73.597070, 
45.544083))r)q
return next jsona
end loop;
return jsona;

Marc



-- 
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] function returning a merge of the same query executed X time

[Jim Nasby]

On 4/22/15 8:14 AM, Marc-André Goderre wrote:

 select row_to_json(q)
from (select row_number() over() as id, sum(cost) as 
total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data
from (select * from cm_get_loop_route_4(2, 10, -73.597070, 
45.544083))r)q


Untested...

CREATE FUNCTION ...(
  ...
  , iterations int
)
LANGUAGE sql AS
$body$
select row_to_json(q)
	from (select row_number() over() as id, sum(cost) as 
total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data

from (select * from cm_get_loop_route_4(2, 10, -73.597070, 
45.544083))r)q
, generate_series(1, iterations) i
$body$
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
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] function returning a merge of the same query executed X time

[Geoff Winkless]

Can you not just CROSS JOIN it to generate_series(1, 8)?

On 22 April 2015 at 14:14, Marc-André Goderre  wrote:

> Hi all,
> I'm having difficulties to  create a function that should execute X time
> the same query and return  their results as a single table.
> I tried this way but it don't work :
> Thanks to help.
>
> create function cm_get_json_loop_info(
> IN profile integer,
> IN distance double precision DEFAULT 10,
> IN x_start double precision DEFAULT 0,
> IN y_start double precision DEFAULT 0,
> IN nbr integer default 1)
>
> returns setof json as
> $BODY$
> declare jsona json;
> BEGIN
> for json a i in 1.. nbr loop
>
> select row_to_json(q)
> from (select row_number() over() as id, sum(cost) as
> total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data
> from (select * from cm_get_loop_route_4(2, 10, -73.597070,
> 45.544083))r)q
> return next jsona
> end loop;
> return jsona;
>
> Marc
>
>
>
> --
> 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] function returning a merge of the same query executed X time

[Adrian Klaver]

On 04/22/2015 06:14 AM, Marc-André Goderre wrote:

Hi all,
I'm having difficulties to  create a function that should execute X time the 
same query and return  their results as a single table.
I tried this way but it don't work :
Thanks to help.


See comments inline.


create function cm_get_json_loop_info(
 IN profile integer,
 IN distance double precision DEFAULT 10,
 IN x_start double precision DEFAULT 0,
 IN y_start double precision DEFAULT 0,
 IN nbr integer default 1)

 returns setof json as
 $BODY$
 declare jsona json;
 BEGIN
 for json a i in 1.. nbr loop

   ^^^
   Not your declared variable. Besides which if the variable 
where correct you would just be assigning it the numbers 1 through nbr.


See:
http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS

40.6.3.5. FOR (Integer Variant)



 select row_to_json(q)
from (select row_number() over() as id, sum(cost) as 
total_cost,sum(length) as total_length,json_agg(row_to_json(r)) as data
from (select * from cm_get_loop_route_4(2, 10, -73.597070, 
45.544083))r)q
return next jsona


The output of the query is not assigned to jsona.

http://www.postgresql.org/docs/9.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT

40.5.3. Executing a Query with a Single-row Result


 end loop;
 return jsona;


This should be just RETURN.

http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

40.6.1.2. RETURN NEXT and RETURN QUERY


Marc






--
Adrian Klaver
adrian.kla...@aklaver.com


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

[GENERAL] LDAP Authentication

[Joseph Kregloh]

I am currently setting up various environments to allow our developers to
login to Postgresql using their LDAP password. Also limiting the servers
they can access.

I have successfully setup LDAP and setup simple authentication using simple
bind. This was my test case. Now I need to move to the next lever which
would be search and bind. This will allow me to grant access to particular
servers for some people. I am not sure where or how the ldapsearchattibute
comes into play.

For example, this would be the search filter I will
use: 
(&(objectClass=posixAccount)(uid=$username)(|(host=$hostname)(host=$fqdn)(host=\\*)))

So it makes sure that the object class is possixAccount and the user id is
$username, next match is, host = $hostname or host = $fqdn or host = *.
These are attributes on the user's account.

How will Postgres match the username? How does it pass it to the query,
everything else I can hardcode into the search filter.

Would an ldapurl be better here? But again how will I pass the uid to the
ldapurl?

Thanks,
-Joseph Kregloh

[GENERAL] ERROR: could not open relation with OID

[Steve Crawford]

This morning we got the following error from a daily script that 
produces a simple largest-table report:

ERROR: could not open relation with OID 597597503

I reran the script and it completed without error.

Our server is running 9.1.15 from PgDg Ubuntu repos and the query run by 
the script is:


SELECT
relname AS TABLE,
lpad ( pg_size_pretty ( pg_relation_size ( oid ) ), 9 ) AS SIZE,
( 100 * pg_relation_size ( oid ) /
( SELECT
sum ( pg_relation_size ( oid ) )
FROM
pg_class
WHERE
relkind = 'r' ) ) ::numeric ( 4, 1 ) AS percent
FROM
pg_class
WHERE
relkind = 'r'
ORDER BY
relpages DESC
LIMIT 60;

My research hasn't led to a definitive conclusion on how to trace this 
issue and how much I should be concerned about this error.


If it is of importance, it appears that a temporary table and temporary 
index were being created within the same second that the query was run.


Any advice?

Cheers,
Steve


--
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] ERROR: could not open relation with OID

[Andomar]

This morning we got the following error from a daily script that
produces a simple largest-table report:
ERROR: could not open relation with OID 597597503

From a bit of Googling, it seems that Postgres was unable to open the 
physical file that contains the relation.


Is it possible that there was an error accessing the disk?  How is the 
filesystem that contains your PGDATA directory mounted?


Kind regards,
Andomar



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

[GENERAL] Streaming-SQL Database PipelineDB (Based on PostgreSQL 9.4) - Available in Beta

[Jeff Ferguson]

Hello (PostgreSQL) World,

We recently shipped our streaming-SQL analytics database, PipelineDB
, which is based on PostgreSQL 9.4, in beta and
are looking for companies to participate as early access partners.

You can read more about the product in our technical documentation
, but essentially our database runs continuous
SQL queries on streaming data and stores only the result of these queries
in the database, enabling realtime analytics and operations/monitoring
capabilities with a very efficient system.

Feel free to email me at j...@pipelinedb.com if your company might be
interested to participate in the beta.

Thanks,
Jeff

-- 
Jeff Ferguson
President and Co-Founder


ᐧ

Re: [GENERAL] ERROR: could not open relation with OID

[Adrian Klaver]

On 04/22/2015 11:40 AM, Steve Crawford wrote:

This morning we got the following error from a daily script that
produces a simple largest-table report:
ERROR: could not open relation with OID 597597503

I reran the script and it completed without error.

Our server is running 9.1.15 from PgDg Ubuntu repos and the query run by
the script is:

SELECT
 relname AS TABLE,
 lpad ( pg_size_pretty ( pg_relation_size ( oid ) ), 9 ) AS SIZE,
 ( 100 * pg_relation_size ( oid ) /
 ( SELECT
 sum ( pg_relation_size ( oid ) )
 FROM
 pg_class
 WHERE
 relkind = 'r' ) ) ::numeric ( 4, 1 ) AS percent
FROM
 pg_class
WHERE
 relkind = 'r'
ORDER BY
 relpages DESC
LIMIT 60;

My research hasn't led to a definitive conclusion on how to trace this
issue and how much I should be concerned about this error.

If it is of importance, it appears that a temporary table and temporary
index were being created within the same second that the query was run.

Any advice?



WHERE
relkind = 'r'
AND
relpersistence != 't'



Cheers,
Steve





--
Adrian Klaver
adrian.kla...@aklaver.com


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

[GENERAL] Connecting to 2 different DB on same machine

[puneet252002]

Hi Team,I am running in a confusion on how to connect two different database
at different path. 1. Default PostgreSQL DB2. ManageEngine Servicedesk Plus
DBI have installed PostgreSQL for windows successfully and connected to
default database of PostgreSQL. Now I to connect ManageEngine Servicedesk
Plus Database located at path "C:\ManageEngine\ServiceDesk". database name
is ServiceDesk.How to connect above 2 different DB on PGADMIN III?
RegardsPuneet



--
View this message in context: 
http://postgresql.nabble.com/Connecting-to-2-different-DB-on-same-machine-tp5846406.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] LDAP Authentication

[John R Pierce]

On 4/22/2015 11:37 AM, Joseph Kregloh wrote:
I have successfully setup LDAP and setup simple authentication using 
simple bind. This was my test case. Now I need to move to the next 
lever which would be search and bind. This will allow me to grant 
access to particular servers for some people. I am not sure where or 
how the ldapsearchattibute comes into play.





you would do this by CREATE USER on the various servers for those 
people, along with GRANT.  LDAP only provides authentication, it doesn't 
manage authorization.


--
john r pierce, recycling bits in santa cruz



--
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] Connecting to 2 different DB on same machine

[John R Pierce]

On 4/22/2015 10:49 AM, puneet252002 wrote:
Hi Team, I am running in a confusion on how to connect two different 
database at different path. 1. Default PostgreSQL DB 2. ManageEngine 
Servicedesk Plus DB I have installed PostgreSQL for windows 
successfully and connected to default database of PostgreSQL. Now I to 
connect ManageEngine Servicedesk Plus Database located at path 
"C:\ManageEngine\ServiceDesk". database name is ServiceDesk. How to 
connect above 2 different DB on PGADMIN III? Regards Puneet 


if thats two different database *servers* running on the same system, 
they would need to use two different port numbers.the default port 
is 5432, so this alternate database server would have to be running on 
some other port, like 5433.


if thats just two different databases on the same server instance, you'd 
specify the database name when you connect to the default port.




--
john r pierce, recycling bits in santa cruz



--
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] ERROR: could not open relation with OID

[Steve Crawford]

On 04/22/2015 01:25 PM, Adrian Klaver wrote:



If it is of importance, it appears that a temporary table and temporary
index were being created within the same second that the query was run.

Any advice?



WHERE
relkind = 'r'
AND
relpersistence != 't'

So to confirm. Fix the query and don't worry?

Cheers,
Steve



--
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] LDAP Authentication

[Joseph Kregloh]

On Wed, Apr 22, 2015 at 5:30 PM, John R Pierce  wrote:

> On 4/22/2015 11:37 AM, Joseph Kregloh wrote:
>
>> I have successfully setup LDAP and setup simple authentication using
>> simple bind. This was my test case. Now I need to move to the next lever
>> which would be search and bind. This will allow me to grant access to
>> particular servers for some people. I am not sure where or how the
>> ldapsearchattibute comes into play.
>>
>>
>
> you would do this by CREATE USER on the various servers for those people,
> along with GRANT.  LDAP only provides authentication, it doesn't manage
> authorization.
>
>
I see. That would still require a manual process to create the user on each
server. I was planing on using some already existing scripts to create the
user automatically on all servers and then LDAP would authorize depending
on attributes in their LDAP profile.


> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> 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] LDAP Authentication

[John R Pierce]

On 4/22/2015 2:57 PM, Joseph Kregloh wrote:




I see. That would still require a manual process to create the user on 
each server. I was planing on using some already existing scripts to 
create the user automatically on all servers and then LDAP would 
authorize depending on attributes in their LDAP profile.


but thats not how it works, so all the 'planing' in the world won't 
change a thing.


access rights per database are managed with GRANT, users must be CREATE 
USER on each server regardless of how they are authenticated.


--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] ERROR: could not open relation with OID

[Adrian Klaver]

On 04/22/2015 02:37 PM, Steve Crawford wrote:

On 04/22/2015 01:25 PM, Adrian Klaver wrote:



If it is of importance, it appears that a temporary table and temporary
index were being created within the same second that the query was run.

Any advice?



WHERE
relkind = 'r'
AND
relpersistence != 't'

So to confirm. Fix the query and don't worry?


Well it is software, so always worry:) To reduce the worry level I would 
confirm that there is really no current relation with an OID of 
597597503. Still given that the query ran successfully on retry and that 
it failed in the presence of a temporary table creation I would say the 
evidence is that a temporary table disappeared out from under the query.




Cheers,
Steve





--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Connecting to 2 different DB on same machine

[Adrian Klaver]

On 04/22/2015 10:49 AM, puneet252002 wrote:

Hi Team, I am running in a confusion on how to connect two different
database at different path. 1. Default PostgreSQL DB 2. ManageEngine
Servicedesk Plus DB I have installed PostgreSQL for windows successfully
and connected to default database of PostgreSQL. Now I to connect
ManageEngine Servicedesk Plus Database located at path
"C:\ManageEngine\ServiceDesk". database name is ServiceDesk. How to
connect above 2 different DB on PGADMIN III? Regards Puneet


Did you follow the instructions from below to set up the database?:

https://www.manageengine.com/products/service-desk/help/adminguide/introduction/setting_up_database.html




--
Adrian Klaver
adrian.kla...@aklaver.com


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