Re: [GENERAL] Re: Using different GCC, CFLAGS, CCFLAGS and CPPFLAGS to compile Postgres and PostGIS?

[Tom Lane]

postgres user  writes:
> Also can you explain if I built Postgres from source on one platform lets
> say RHEL_6 and deployed its artifacts like its binaries, libs and share on
> a CentOS

In general I would not expect that to work.  RHEL to CentOS is a special
case because they're really the same platform --- if it didn't work, you'd
have grounds to file a bug against the CentOS maintainers.  But, say, RHEL
to Debian likely wouldn't work, and neither set of maintainers would
consider an ABI-compatibility complaint to be a valid bug.

> and tried building extensions against Postgres on CentOS are there
> any dangers of doing that?

Doesn't matter if the core system itself doesn't work, which it wouldn't
in cross-platform cases.

regards, tom lane


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

[GENERAL] Re: Using different GCC, CFLAGS, CCFLAGS and CPPFLAGS to compile Postgres and PostGIS?

[postgres user]

Also can you explain if I built Postgres from source on one platform lets
say RHEL_6 and deployed its artifacts like its binaries, libs and share on
a CentOS and tried building extensions against Postgres on CentOS are there
any dangers of doing that?

On Wed, Feb 1, 2017 at 8:34 PM, postgres user 
wrote:

> Hi,
>
> I am wondering about this question for a while with no definite answer to
> it, can someone explain me in detail to clear me out on the following
> question :
>
> What can go wrong or is it acceptable if I build Postgres from source with
> let's say GCC 4.x.y and some specific CFLAGS, CPPFLAGS and CCFLAGS and when
> I try to install an extension for Postgres such as PostGIS using a
> different version of GCC lets say GCC 4.x.z with varying CFLAGS, CCFLAGS
> and CPPFLAGS and if I follow the same process for other non-contrib
> extensions? I want to know the theory behind this and it would serve me a
> great help to understand the systems aspect of the process as well.
>
> Thanks
>

Re: [GENERAL] Using different GCC, CFLAGS, CCFLAGS and CPPFLAGS to compile Postgres and PostGIS?

[Tom Lane]

postgres user  writes:
> What can go wrong or is it acceptable if I build Postgres from source with
> let's say GCC 4.x.y and some specific CFLAGS, CPPFLAGS and CCFLAGS and when
> I try to install an extension for Postgres such as PostGIS using a
> different version of GCC lets say GCC 4.x.z with varying CFLAGS, CCFLAGS
> and CPPFLAGS and if I follow the same process for other non-contrib
> extensions? I want to know the theory behind this and it would serve me a
> great help to understand the systems aspect of the process as well.

You could certainly break things that way if you tried hard enough.
"Hard enough" would involve, say, selecting compiler flags that alter
ABI details like function calling conventions or struct packing rules.
Then the code in the extension would expect to call or be called
differently than the code in the core server expects to do it, or
would believe that structs declared in core server header files are
laid out differently than the code in the core server thinks, etc.

Using a different compiler would matter if it wasn't ABI-compatible with
the compiler used for the core code.  Usually, vendor-supplied compilers
for different C variants are all configured to be ABI-compatible on a
given platform; but again you could break it if you tried hard enough,
like say using a home-built compiler that you'd configured randomly
differently from the platform's default compiler.

It would be more useful to ask about specific changes you want to make
in the compiler and flag choices (and explaining why you want to make
those specific changes wouldn't be a bad thing either).

regards, tom lane


-- 
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] Using different GCC, CFLAGS, CCFLAGS and CPPFLAGS to compile Postgres and PostGIS?

[John R Pierce]

On 2/1/2017 8:34 PM, postgres user wrote:


I am wondering about this question for a while with no definite answer 
to it, can someone explain me in detail to clear me out on the 
following question :


What can go wrong or is it acceptable if I build Postgres from source 
with let's say GCC 4.x.y and some specific CFLAGS, CPPFLAGS and 
CCFLAGS and when I try to install an extension for Postgres such as 
PostGIS using a different version of GCC lets say GCC 4.x.z with 
varying CFLAGS, CCFLAGS and CPPFLAGS and if I follow the same process 
for other non-contrib extensions? I want to know the theory behind 
this and it would serve me a great help to understand the systems 
aspect of the process as well.




the PGXS build process that most postgres extensions are built with 
should use the same flags the postgres server was built with.   minor 
compiler version differences shouldn't matter, but major ones certainly 
would.   if the project doesn't use PGXS its harder to keep it all 
straight, while remaining portable.



otherwise, your question is far too vague to be directly answerable.   
given a nearly infinite number of combinations of ways of doing things 
wrong, predicting whats going to happen in each case is futile.








--
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

[GENERAL] Using different GCC, CFLAGS, CCFLAGS and CPPFLAGS to compile Postgres and PostGIS?

[postgres user]

Hi,

I am wondering about this question for a while with no definite answer to
it, can someone explain me in detail to clear me out on the following
question :

What can go wrong or is it acceptable if I build Postgres from source with
let's say GCC 4.x.y and some specific CFLAGS, CPPFLAGS and CCFLAGS and when
I try to install an extension for Postgres such as PostGIS using a
different version of GCC lets say GCC 4.x.z with varying CFLAGS, CCFLAGS
and CPPFLAGS and if I follow the same process for other non-contrib
extensions? I want to know the theory behind this and it would serve me a
great help to understand the systems aspect of the process as well.

Thanks

Re: [GENERAL] Testing an extension exhaustively?

[Steve Atkins]

> On Feb 1, 2017, at 4:03 PM, John R Pierce  wrote:
> 
> On 2/1/2017 3:39 PM, postgres user wrote:
>> If I have the Postgresql server installed on my machine i.e I have all the 
>> bins, libs and share directories of the Postgresql and I have the libs and 
>> sql's installed for one of the contrib extensions lets say "chkpass", how 
>> does one go about testing this extension exhaustively on the server? I ask 
>> this because I would want to do this manually first and then go about 
>> automating the testing of this extension. So rather than just execute CREATE 
>> EXTENSION and DROP EXTENSION I want some solid evidence that the extension 
>> is working fine under all circumstances and is not crashing the server at 
>> any moment? Looking for some new strategies and ideas to come my way through 
>> this.
> 
> you would write test cases for all the functionality provided by this 
> extension, same as you'd test any other sort of API.

And you might find http://pgtap.org convenient for doing that.

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] Testing an extension exhaustively?

[John R Pierce]

On 2/1/2017 3:39 PM, postgres user wrote:
If I have the Postgresql server installed on my machine i.e I have all 
the bins, libs and share directories of the Postgresql and I have the 
libs and sql's installed for one of the contrib extensions lets say 
"chkpass", how does one go about testing this extension exhaustively 
on the server? I ask this because I would want to do this manually 
first and then go about automating the testing of this extension. So 
rather than just execute CREATE EXTENSION and DROP EXTENSION I want 
some solid evidence that the extension is working fine under all 
circumstances and is not crashing the server at any moment? Looking 
for some new strategies and ideas to come my way through this.


you would write test cases for all the functionality provided by this 
extension, same as you'd test any other sort of API.



--
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

[GENERAL] Testing an extension exhaustively?

[postgres user]

Hi,

If I have the Postgresql server installed on my machine i.e I have all the
bins, libs and share directories of the Postgresql and I have the libs and
sql's installed for one of the contrib extensions lets say "chkpass", how
does one go about testing this extension exhaustively on the server? I ask
this because I would want to do this manually first and then go about
automating the testing of this extension. So rather than just execute
CREATE EXTENSION and DROP EXTENSION I want some solid evidence that the
extension is working fine under all circumstances and is not crashing the
server at any moment? Looking for some new strategies and ideas to come my
way through this.

Thanks.

Re: [GENERAL] Postgresql out-of-memory kill

[Israel Brewster]

On Feb 1, 2017, at 1:45 PM, Tom Lane  wrote:
> 
> Israel Brewster  writes:
>> So just a bit ago I ran into a bit of excitement when the kernel decided
>> to kill one of my postmaster processes due to an out-of-memory issue,
> 
> Fun :-(
> 
>> So a single postmaster process was using over 72GB of ram.
> 
> No, the kernel was blaming it for 72GB, which is an entirely different
> statement.  The Linux OOM killer makes some assumptions that are
> ludicrously wrong for Postgres: not only does it blame a parent process
> for the total memory consumption of all its children, but if the children
> share a large shared memory segment, *it counts the shared memory segment
> over again for each child*.  At least this was true last I looked;
> perhaps very recent kernels are a bit less insane about shared memory.
> In any case, the core problem is blaming the parent process for the
> sins of a child.
> 
> Now the PG postmaster itself consumes very little memory, and this is
> quite unlikely to suddenly go wrong because it doesn't do very much.
> A child backend process might go crazy, but what you want to happen then
> is for the OOM killer to kill the child process not the postmaster.
> That will still result in a database crash/restart scenario, but as long
> as the postmaster is alive everything should recover automatically.
> 
> Your problem, then, is that the OOM killer is egregiously and with malice
> aforethought killing the wrong process.
> 
> The usual fix for this is to configure things so that the postmaster is
> excluded from OOM kill but its children aren't.  See
> https://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT
> (but be sure to consult the page for your PG version, as we've changed
> the support mechanism for that in the past.)
> 
> If you're using a vendor-supplied packaging of PG and it doesn't have some
> easy way to turn on this behavior, complain to the vendor ...
> 
>   regards, tom lane
> 

Thanks for the explanation. This is a CentOS 6 box, kernel  
2.6.32-642.11.1.el6.x86_64, running the PostgreSQL supplied Postgres 9.6.1, so 
hopefully the information on that page applies. I'll mess around with modifying 
the init.d script to exclude the postmaster process. Thanks again!

---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---



> 
> -- 
> 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] Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

[Nikolai Zhubr]

01.02.2017 1:02, I wrote:
[...]

Could you use process monitor or such to see what the process is doing
while using a lot of CPU?


I'm not sure how to do this, especially considering that the process in
question is running as a service?

Now, some more input:

* 9.5.2 server running on linux x86_64 - unaffected! (What a relief! We
are moving to Centos soon anyway!)

* 9.4.4 server running on win7 32-bit - affected, same thing as on XP.


I've managed to create a "fix" (see diff below).
It looks like the wait logic is somehow broken on windows currently, 
though I can not find the problem myself yet.
It would be great if someone more familiar with the (windows-specific) 
code came up with ideas.

I have a build environment ready so I could do more tests then.

--- be-secure.c.orig2017-02-01 22:37:37.228032608 +0300
+++ be-secure.c 2017-02-01 22:51:17.655751292 +0300
@@ -159,6 +159,7 @@
 * socket to become ready again.
 */
}
+Sleep(15); /* n.zhubr */
goto retry;
}

@@ -238,6 +239,7 @@
 * socket to become ready again.
 */
}
+Sleep(15); /* n.zhubr */
goto retry;
}


Thank you.

Nikolai




Thank you.

Nikolai



Regards,

Andres









--
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] Postgresql out-of-memory kill

[Tom Lane]

Israel Brewster  writes:
> So just a bit ago I ran into a bit of excitement when the kernel decided
> to kill one of my postmaster processes due to an out-of-memory issue,

Fun :-(

> So a single postmaster process was using over 72GB of ram.

No, the kernel was blaming it for 72GB, which is an entirely different
statement.  The Linux OOM killer makes some assumptions that are
ludicrously wrong for Postgres: not only does it blame a parent process
for the total memory consumption of all its children, but if the children
share a large shared memory segment, *it counts the shared memory segment
over again for each child*.  At least this was true last I looked;
perhaps very recent kernels are a bit less insane about shared memory.
In any case, the core problem is blaming the parent process for the
sins of a child.

Now the PG postmaster itself consumes very little memory, and this is
quite unlikely to suddenly go wrong because it doesn't do very much.
A child backend process might go crazy, but what you want to happen then
is for the OOM killer to kill the child process not the postmaster.
That will still result in a database crash/restart scenario, but as long
as the postmaster is alive everything should recover automatically.

Your problem, then, is that the OOM killer is egregiously and with malice
aforethought killing the wrong process.

The usual fix for this is to configure things so that the postmaster is
excluded from OOM kill but its children aren't.  See
https://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT
(but be sure to consult the page for your PG version, as we've changed
the support mechanism for that in the past.)

If you're using a vendor-supplied packaging of PG and it doesn't have some
easy way to turn on this behavior, complain to the vendor ...

regards, tom lane


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

[GENERAL] Postgresql out-of-memory kill

[Israel Brewster]

So just a bit ago I ran into a bit of excitement when the kernel decided to kill one of my postmaster processes due to an out-of-memory issue, which would have been fine, except that the problem was then compounded by Pacemaker attempting to restart postgresql, but only managing to get as far as stopping the primary and failing to promote the secondary, leaving me with nothing. Not fun for a mission-critical database, but luckily I was notified of the issue nearly immediately, and was able to get everything back up and running quickly (after a few moments of panic).In any case the root problem here was the out-of-memory issue. The logs show this:Feb  1 11:58:34 fai-dbs1 kernel: Out of memory: Kill process 26316 (postmaster) score 837 or sacrifice childFeb  1 11:58:34 fai-dbs1 kernel: Killed process 26316, UID 26, (postmaster) total-vm:72328760kB, anon-rss:55470760kB, file-rss:4753180kBSo a single postmaster process was using over 72GB of ram. Obviously I have something in my config tuned too high. The question is "what"? The machine has 64 GB of RAM, and I want postgresql to be able to use as much of that as it wants (since the machine is dedicated to postgresql), but obviously it needs to be more limited than what I have.From my config:max_connections = 300shared_buffers = 14GB  # Roughly 1/4 of 64GB, as per https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serverwork_mem = 75MBNot sure what else in there would play a role. I was seeing some of this in the postgresql logs shortly before the issue arose:2017-02-01 11:58:02.074 AKST > LOG:  checkpoints are occurring too frequently (12 seconds apart)2017-02-01 11:58:02.074 AKST > HINT:  Consider increasing the configuration parameter "max_wal_size".I was thinking perhaps the work_mem was the issue, but if my understanding and calculations are correct, that would mean I had more than 800 simultaneous sorts/queries going on at the time, which seems quite improbable, given the relatively light load on this database. At the moment, for example, a query on pg_stat_activity reveals only 11 entries.What am I missing here?
---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD

Re: [GENERAL] Data Modeling Tools - Version specific to Postgres

[Thomas Kellerer]

Greg Slawek schrieb am 01.02.2017 um 19:35:


Can anyone recommend a data modeling tool (preferably Mac OSX
compatible)?

I would like to be sure it can export version specific SQL code (ie
9.1 vs 9.4)

I have used Toad Data Modeler years ago on Windows, which was pretty
good at sticking to the differences in each postgres version. I've
seen Navicat advertised a few places which I was considering
purchasing, and also checked out the list at the postgres wiki -
https://wiki.postgresql.org/wiki/GUI_Database_Design_Tools

I am curious if anyone has any experience using one



Have a look at DbSchema: http://www.dbschema.com/

It's Java based and should run on Mac OS as well.




--
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] Another windowed function with different values.

[Edmundo Robles]

this issue was raised by timezone settings ...

On Wed, Feb 1, 2017 at 10:15 AM, Edmundo Robles 
wrote:

> i just changed the  jdbc, but   still get the wrong values...
>
> On Wed, Feb 1, 2017 at 9:47 AM, Edmundo Robles 
> wrote:
>
>> this issues could be  raised by a incorrect jdbc?   the jdbc used is
>> postrgresql-9.2-1002.jdbc4.jar and i have a postgresql 9.4
>>
>> On Wed, Feb 1, 2017 at 9:35 AM, Edmundo Robles 
>> wrote:
>>
>>>
>>> I have postgresql 9.4  and  wrote a function get_ignition_time() to get
>>>   the first time when a  car was ignition on and  the  last time when
>>>  ignition is off,   those
>>> time stamps are  used in another function  get_fuel_consumption() to get
>>>  the  fuel consumption.
>>>
>>>
>>>
>>> The issue is  when:
>>> 1.   I run get_ignition_time()   directly in: psql, pgadmin  got the
>>> right values.
>>> 2.   I run  get_ignition_time()  inside iReport preview and  got the
>>> right values.
>>>
>>> 3. But, when i run  the report from web server got wrong values...first
>>> i thought on timezone issues, because i have had issues with timestamp
>>>  constraints at resotring database, but the timestamp  mismatch  the CST
>>> timezone, if you compare the time stamp  from  1 step  the  difference is
>>> not  6hr.
>>>
>>>
>>>
>>> the  get_igniton_time is called in a cursor  inside get_fuel_consumption
>>> but   when i fetch  it  the ini_time and end_time are wrong like   in 3rd
>>> step
>>>
>>>
>>>
>>> I was  rewrote the function many times,
>>> i have used  window value first_value and last_value,
>>> i have wrote  one  query  when igntion is on anohter whem igntion is
>>> off  then joined,
>>> etc,
>>>i have wrote  on different ways to get the same value like  step 1
>>> and 2 but  3 is always wrong...
>>>i have set the order, like mentionend on 'windowedagg ... mail'
>>>but no success 
>>>
>>> what  is  the safe way to use  windowed  function???
>>>
>>>
>>>
>>>
>>>  this  is the  last function i wrote:
>>>
>>> CREATE OR REPLACE FUNCTION get_ignition_time(in punits character
>>> varying, pfrom character varying,  pto character varying)
>>>   RETURNS TABLE(id_unit integer
>>>   , ini_time timestamp with time zone
>>>   , end_time timestamp with time zone) as
>>> $BODY$
>>> DECLARE
>>>vunits integer[]= string_to_array(punits, ',');
>>> BEGIN
>>> RETURN QUERY with   foo as (
>>> select   st.id_trun, st.time_stamp
>>> , min(st.time_stamp)  filter (where ignition=true)  over w
>>> , max(st.time_stamp)  filter (where ignition=false)  over w
>>> from big_big_table st
>>> where  st.id_trun =  ANY(ARRAY[vunits])
>>> and st.time_stamp>=pfrom::timestamptz and st.time_stamp <
>>> pto::timestamptz
>>> window  w as (partition by  st.id_trun )
>>> order by st.id_trun,st.time_stamp
>>> )
>>> select distinct  f.id_trun,f.min,f.max from foo f where min is not null
>>>  and max is not null;
>>>
>>> END;
>>> $BODY$
>>>   LANGUAGE plpgsql VOLATILE
>>>   COST 100
>>>   ROWS 1000;
>>>
>>>
>>>
>>>
>>>
>>> CREATE OR REPLACE FUNCTION get_fuel_cosumption_dt(IN truns character
>>> varying, IN dfrom character varying, IN dto character varying)
>>>   RETURNS TABLE(
>>> id_trun integer,
>>> first_day smallint,
>>> last_day smallint,
>>> consumtpion_over_day bigint,
>>> recharge_over_day bigint
>>> ) AS
>>> $BODY$
>>> DECLARE
>>>
>>>rec record;
>>>trip cursor for  select * from hydra.get_ignition_time(truns,
>>> dfrom,dto);
>>> BEGIN
>>>
>>>   create temp table if not exists t_fuel_consumption_dt(
>>>id_trun integer,
>>> first_day smallint,
>>> last_day smallint,
>>> cosumption_over_day bigint,
>>> recharge_over_day bigint
>>> ) on commit drop;
>>>
>>>open  trip;
>>> loop fetch trip into rec;
>>> exit when not found;
>>>
>>>   raise log 'XXX::>> select r.*  from
>>> hydra.rep_calculo_gas(''%'', ''%'', ''%'') r;' ,
>>> rec.id_trun::varchar,rec.ini_time::varchar,rec.end_time::varchar ;
>>>
>>>   insert into t_fuel_consumption_dt
>>>   select
>>>   r.*
>>>from 
>>> hydra.get_consumption(rec.id_trun::varchar,rec.ini_time::varchar,rec.end_time::varchar)
>>> r;
>>> end loop;
>>>close trip;
>>>
>>>return query select * from t_fuel_consumption_dt;
>>>
>>> END;
>>> $BODY$
>>>   LANGUAGE plpgsql VOLATILE
>>>   COST 100
>>>   ROWS 1000;
>>>
>>>
>>
>>
>> --
>>
>>
>
>
> --
>
>


--

[GENERAL] Data Modeling Tools - Version specific to Postgres

[Greg Slawek]

Can anyone recommend a data modeling tool (preferably Mac OSX compatible)?

I would like to be sure it can export version specific SQL code (ie 9.1 vs
9.4)

I have used Toad Data Modeler years ago on Windows, which was pretty good
at sticking to the differences in each postgres version. I've seen Navicat
advertised a few places which I was considering purchasing, and also
checked out the list at the postgres wiki -
https://wiki.postgresql.org/wiki/GUI_Database_Design_Tools

I am curious if anyone has any experience using one

Thanks,
Greg

[GENERAL] Another windowed function with different values.

[Edmundo Robles]

I have postgresql 9.4  and  wrote a function get_ignition_time() to get
the first time when a  car was ignition on and  the  last time when
 ignition is off,   those
time stamps are  used in another function  get_fuel_consumption() to get
 the  fuel consumption.



The issue is  when:
1.   I run get_ignition_time()   directly in: psql, pgadmin  got the right
values.
2.   I run  get_ignition_time()  inside iReport preview and  got the right
values.

3. But, when i run  the report from web server got wrong values...first i
thought on timezone issues, because i have had issues with timestamp
 constraints at resotring database, but the timestamp  mismatch  the CST
timezone, if you compare the time stamp  from  1 step  the  difference is
not  6hr.



the  get_igniton_time is called in a cursor  inside get_fuel_consumption
but   when i fetch  it  the ini_time and end_time are wrong like   in 3rd
step



I was  rewrote the function many times,
i have used  window value first_value and last_value,
i have wrote  one  query  when igntion is on anohter whem igntion is
off  then joined,
etc,
   i have wrote  on different ways to get the same value like  step 1 and 2
but  3 is always wrong...
   i have set the order, like mentionend on 'windowedagg ... mail'
   but no success 

what  is  the safe way to use  windowed  function???




 this  is the  last function i wrote:

CREATE OR REPLACE FUNCTION get_ignition_time(in punits character varying,
pfrom character varying,  pto character varying)
  RETURNS TABLE(id_unit integer
  , ini_time timestamp with time zone
  , end_time timestamp with time zone) as
$BODY$
DECLARE
   vunits integer[]= string_to_array(punits, ',');
BEGIN
RETURN QUERY with   foo as (
select   st.id_trun, st.time_stamp
, min(st.time_stamp)  filter (where ignition=true)  over w
, max(st.time_stamp)  filter (where ignition=false)  over w
from big_big_table st
where  st.id_trun =  ANY(ARRAY[vunits])
and st.time_stamp>=pfrom::timestamptz and st.time_stamp < pto::timestamptz
window  w as (partition by  st.id_trun )
order by st.id_trun,st.time_stamp
)
select distinct  f.id_trun,f.min,f.max from foo f where min is not null
 and max is not null;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;





CREATE OR REPLACE FUNCTION get_fuel_cosumption_dt(IN truns character
varying, IN dfrom character varying, IN dto character varying)
  RETURNS TABLE(
id_trun integer,
first_day smallint,
last_day smallint,
consumtpion_over_day bigint,
recharge_over_day bigint
) AS
$BODY$
DECLARE

   rec record;
   trip cursor for  select * from hydra.get_ignition_time(truns,dfrom,dto);
BEGIN

  create temp table if not exists t_fuel_consumption_dt(
   id_trun integer,
first_day smallint,
last_day smallint,
cosumption_over_day bigint,
recharge_over_day bigint
) on commit drop;

   open  trip;
loop fetch trip into rec;
exit when not found;

  raise log 'XXX::>> select r.*  from hydra.rep_calculo_gas(''%'',
''%'', ''%'') r;' ,
rec.id_trun::varchar,rec.ini_time::varchar,rec.end_time::varchar ;

  insert into t_fuel_consumption_dt
  select
  r.*
   from
hydra.get_consumption(rec.id_trun::varchar,rec.ini_time::varchar,rec.end_time::varchar)
r;
end loop;
   close trip;

   return query select * from t_fuel_consumption_dt;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;

Re: [GENERAL] Another windowed function with different values.

[Edmundo Robles]

this issues could be  raised by a incorrect jdbc?   the jdbc used is
postrgresql-9.2-1002.jdbc4.jar and i have a postgresql 9.4

On Wed, Feb 1, 2017 at 9:35 AM, Edmundo Robles  wrote:

>
> I have postgresql 9.4  and  wrote a function get_ignition_time() to get
> the first time when a  car was ignition on and  the  last time when
>  ignition is off,   those
> time stamps are  used in another function  get_fuel_consumption() to get
>  the  fuel consumption.
>
>
>
> The issue is  when:
> 1.   I run get_ignition_time()   directly in: psql, pgadmin  got the right
> values.
> 2.   I run  get_ignition_time()  inside iReport preview and  got the right
> values.
>
> 3. But, when i run  the report from web server got wrong values...first i
> thought on timezone issues, because i have had issues with timestamp
>  constraints at resotring database, but the timestamp  mismatch  the CST
> timezone, if you compare the time stamp  from  1 step  the  difference is
> not  6hr.
>
>
>
> the  get_igniton_time is called in a cursor  inside get_fuel_consumption
> but   when i fetch  it  the ini_time and end_time are wrong like   in 3rd
> step
>
>
>
> I was  rewrote the function many times,
> i have used  window value first_value and last_value,
> i have wrote  one  query  when igntion is on anohter whem igntion is
> off  then joined,
> etc,
>i have wrote  on different ways to get the same value like  step 1 and
> 2 but  3 is always wrong...
>i have set the order, like mentionend on 'windowedagg ... mail'
>but no success 
>
> what  is  the safe way to use  windowed  function???
>
>
>
>
>  this  is the  last function i wrote:
>
> CREATE OR REPLACE FUNCTION get_ignition_time(in punits character varying,
> pfrom character varying,  pto character varying)
>   RETURNS TABLE(id_unit integer
>   , ini_time timestamp with time zone
>   , end_time timestamp with time zone) as
> $BODY$
> DECLARE
>vunits integer[]= string_to_array(punits, ',');
> BEGIN
> RETURN QUERY with   foo as (
> select   st.id_trun, st.time_stamp
> , min(st.time_stamp)  filter (where ignition=true)  over w
> , max(st.time_stamp)  filter (where ignition=false)  over w
> from big_big_table st
> where  st.id_trun =  ANY(ARRAY[vunits])
> and st.time_stamp>=pfrom::timestamptz and st.time_stamp < pto::timestamptz
> window  w as (partition by  st.id_trun )
> order by st.id_trun,st.time_stamp
> )
> select distinct  f.id_trun,f.min,f.max from foo f where min is not null
>  and max is not null;
>
> END;
> $BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 100
>   ROWS 1000;
>
>
>
>
>
> CREATE OR REPLACE FUNCTION get_fuel_cosumption_dt(IN truns character
> varying, IN dfrom character varying, IN dto character varying)
>   RETURNS TABLE(
> id_trun integer,
> first_day smallint,
> last_day smallint,
> consumtpion_over_day bigint,
> recharge_over_day bigint
> ) AS
> $BODY$
> DECLARE
>
>rec record;
>trip cursor for  select * from hydra.get_ignition_time(truns,
> dfrom,dto);
> BEGIN
>
>   create temp table if not exists t_fuel_consumption_dt(
>id_trun integer,
> first_day smallint,
> last_day smallint,
> cosumption_over_day bigint,
> recharge_over_day bigint
> ) on commit drop;
>
>open  trip;
> loop fetch trip into rec;
> exit when not found;
>
>   raise log 'XXX::>> select r.*  from hydra.rep_calculo_gas(''%'',
> ''%'', ''%'') r;' , rec.id_trun::varchar,rec.ini_
> time::varchar,rec.end_time::varchar ;
>
>   insert into t_fuel_consumption_dt
>   select
>   r.*
>from 
> hydra.get_consumption(rec.id_trun::varchar,rec.ini_time::varchar,rec.end_time::varchar)
> r;
> end loop;
>close trip;
>
>return query select * from t_fuel_consumption_dt;
>
> END;
> $BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 100
>   ROWS 1000;
>
>


--

Re: [GENERAL] Another windowed function with different values.

[Edmundo Robles]

i just changed the  jdbc, but   still get the wrong values...

On Wed, Feb 1, 2017 at 9:47 AM, Edmundo Robles  wrote:

> this issues could be  raised by a incorrect jdbc?   the jdbc used is
> postrgresql-9.2-1002.jdbc4.jar and i have a postgresql 9.4
>
> On Wed, Feb 1, 2017 at 9:35 AM, Edmundo Robles 
> wrote:
>
>>
>> I have postgresql 9.4  and  wrote a function get_ignition_time() to get
>> the first time when a  car was ignition on and  the  last time when
>>  ignition is off,   those
>> time stamps are  used in another function  get_fuel_consumption() to get
>>  the  fuel consumption.
>>
>>
>>
>> The issue is  when:
>> 1.   I run get_ignition_time()   directly in: psql, pgadmin  got the
>> right values.
>> 2.   I run  get_ignition_time()  inside iReport preview and  got the
>> right values.
>>
>> 3. But, when i run  the report from web server got wrong values...first i
>> thought on timezone issues, because i have had issues with timestamp
>>  constraints at resotring database, but the timestamp  mismatch  the CST
>> timezone, if you compare the time stamp  from  1 step  the  difference is
>> not  6hr.
>>
>>
>>
>> the  get_igniton_time is called in a cursor  inside get_fuel_consumption
>> but   when i fetch  it  the ini_time and end_time are wrong like   in 3rd
>> step
>>
>>
>>
>> I was  rewrote the function many times,
>> i have used  window value first_value and last_value,
>> i have wrote  one  query  when igntion is on anohter whem igntion is
>> off  then joined,
>> etc,
>>i have wrote  on different ways to get the same value like  step 1 and
>> 2 but  3 is always wrong...
>>i have set the order, like mentionend on 'windowedagg ... mail'
>>but no success 
>>
>> what  is  the safe way to use  windowed  function???
>>
>>
>>
>>
>>  this  is the  last function i wrote:
>>
>> CREATE OR REPLACE FUNCTION get_ignition_time(in punits character varying,
>> pfrom character varying,  pto character varying)
>>   RETURNS TABLE(id_unit integer
>>   , ini_time timestamp with time zone
>>   , end_time timestamp with time zone) as
>> $BODY$
>> DECLARE
>>vunits integer[]= string_to_array(punits, ',');
>> BEGIN
>> RETURN QUERY with   foo as (
>> select   st.id_trun, st.time_stamp
>> , min(st.time_stamp)  filter (where ignition=true)  over w
>> , max(st.time_stamp)  filter (where ignition=false)  over w
>> from big_big_table st
>> where  st.id_trun =  ANY(ARRAY[vunits])
>> and st.time_stamp>=pfrom::timestamptz and st.time_stamp <
>> pto::timestamptz
>> window  w as (partition by  st.id_trun )
>> order by st.id_trun,st.time_stamp
>> )
>> select distinct  f.id_trun,f.min,f.max from foo f where min is not null
>>  and max is not null;
>>
>> END;
>> $BODY$
>>   LANGUAGE plpgsql VOLATILE
>>   COST 100
>>   ROWS 1000;
>>
>>
>>
>>
>>
>> CREATE OR REPLACE FUNCTION get_fuel_cosumption_dt(IN truns character
>> varying, IN dfrom character varying, IN dto character varying)
>>   RETURNS TABLE(
>> id_trun integer,
>> first_day smallint,
>> last_day smallint,
>> consumtpion_over_day bigint,
>> recharge_over_day bigint
>> ) AS
>> $BODY$
>> DECLARE
>>
>>rec record;
>>trip cursor for  select * from hydra.get_ignition_time(truns,
>> dfrom,dto);
>> BEGIN
>>
>>   create temp table if not exists t_fuel_consumption_dt(
>>id_trun integer,
>> first_day smallint,
>> last_day smallint,
>> cosumption_over_day bigint,
>> recharge_over_day bigint
>> ) on commit drop;
>>
>>open  trip;
>> loop fetch trip into rec;
>> exit when not found;
>>
>>   raise log 'XXX::>> select r.*  from
>> hydra.rep_calculo_gas(''%'', ''%'', ''%'') r;' ,
>> rec.id_trun::varchar,rec.ini_time::varchar,rec.end_time::varchar ;
>>
>>   insert into t_fuel_consumption_dt
>>   select
>>   r.*
>>from 
>> hydra.get_consumption(rec.id_trun::varchar,rec.ini_time::varchar,rec.end_time::varchar)
>> r;
>> end loop;
>>close trip;
>>
>>return query select * from t_fuel_consumption_dt;
>>
>> END;
>> $BODY$
>>   LANGUAGE plpgsql VOLATILE
>>   COST 100
>>   ROWS 1000;
>>
>>
>
>
> --
>
>


--

Re: [GENERAL] Can we not give tyrannical pedants control of #postgresql?

[Stephen Frost]

Greetings,

* Merlin Moncure (mmonc...@gmail.com) wrote:
> On Thu, Jan 19, 2017 at 5:23 PM, Julian Paul  wrote:
> > I hope that particular stereotypes aren't proven here, but it appears
> > #postgresql encourages a particular tier and makes aware of it's rigid
> > hierarchy. I owe alot to #postgresql but not to these particular users, I've
> > perhaps been idle for too long and the channel has change for the worse,
> > well that's not my fault. I leave it with the community to sort out.
> 
> I haven't been on irc much lately, but I've noticed this trend as well.

I'm on it pretty regularly, though I wasn't when the event which started
this thread happened, so I can't really speak to it and that's why I
hadn't responded.

In general, I feel like the channel is quite welcoming to newcomers, but
there is often a bit of a learning curve and when others point things
out that can sometimes be off-putting (not unlike our mailing lists..).

In any case, I'm happy to try and help out if people feel that there's
abusing of OPs or inappropriate behavior.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Can we not give tyrannical pedants control of #postgresql?

[Merlin Moncure]

On Thu, Jan 19, 2017 at 5:23 PM, Julian Paul  wrote:
> I hope that particular stereotypes aren't proven here, but it appears
> #postgresql encourages a particular tier and makes aware of it's rigid
> hierarchy. I owe alot to #postgresql but not to these particular users, I've
> perhaps been idle for too long and the channel has change for the worse,
> well that's not my fault. I leave it with the community to sort out.

I haven't been on irc much lately, but I've noticed this trend as well.

merlin


-- 
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] insert - on conflict question

[Beena Emerson]

On Wed, Feb 1, 2017 at 4:15 PM, Johann Spies  wrote:

> How do I formulate the on conflict  do update-section of this query? When
> I try set title=q.title, q is unknown.  When I try and change 'title' in
> the select-part to something else and try title=ti I get the message that
> ti cannot be used in this part of the query.
>
> INSERT INTO wos_2017_1.article (ut,
> title,
> author_count)WITH p AS (
> SELECT
> ARRAY [ ARRAY [ 't', 'some_namespace' ] ] AS ns),
> q AS (
> SELECT
> ut,
> unnest (xpath ('//t:title[@type= "item"]/text()',
> xml,
> p.ns))::text title,
> unnest (xpath ('//t:summary/t:names/@count',
> xml,
> p.ns))::TEXT::INTEGER AS author_count
> FROM
> p,
> source.cover_2016)SELECT
> ut,
> regexp_replace (regexp_replace (regexp_replace (title, '<', '<', 'g'), 
> '&', '&', 'g'), '>', '>', 'g')
> title,
> author_countFROM
> q
>
> ON CONFLICT (ut)
> DO UPDATESET
> title = title,
> author_count = author_count;
>
>
>
In the  ON CONFLICT... SET we need to use EXCLUDED keyword.

ON CONFLICT (ut)
DO UPDATE
SET
title = EXCLUDED.title,
author_count = EXCLUDED.author_count;

-- 
Thank you,

Beena Emerson

Have a Great Day!

[GENERAL] insert - on conflict question

[Johann Spies]

How do I formulate the on conflict  do update-section of this query? When I
try set title=q.title, q is unknown.  When I try and change 'title' in the
select-part to something else and try title=ti I get the message that ti
cannot be used in this part of the query.

INSERT INTO wos_2017_1.article (ut,
title,
author_count)WITH p AS (
SELECT
ARRAY [ ARRAY [ 't', 'some_namespace' ] ] AS ns),
q AS (
SELECT
ut,
unnest (xpath ('//t:title[@type= "item"]/text()',
xml,
p.ns))::text title,
unnest (xpath ('//t:summary/t:names/@count',
xml,
p.ns))::TEXT::INTEGER AS author_count
FROM
p,
source.cover_2016)SELECT
ut,
regexp_replace (regexp_replace (regexp_replace (title, '<', '<',
'g'), '&', '&', 'g'), '>', '>', 'g')
title,
author_countFROM
q

ON CONFLICT (ut)
DO UPDATESET
title = title,
author_count = author_count;



-- 
Because experiencing your loyal love is better than life itself,
my lips will praise you.  (Psalm 63:3)