Re: [GENERAL] SP to calc shipments vs receipts

First, regarding the stored procedure recommendation, it depends on what you are trying to do. The decision to go with a stored procedure vs a view depends on how this fits into the rest of your application. Here is what I would do for the SQL though: WITH base_agg AS ( select part_no,

[GENERAL] ?????? [GENERAL] SP to calc shipments vs receipts

hello, is the output calculated by following rule? on_hand SUM(receipt) - SUM(shipment) - SUM(allocated) available SUM(receipt) - SUM(shipment) sql can be: sum(case when trans_type='REC' then trans_qty when trans_type IN ('SHP', 'ALL') then -trans_qty else 0) as on_hand sum(case when trans_type=

Re: [GENERAL] SP to calc shipments vs receipts

On 9/23/2013 10:13 PM, Bret Stern wrote: I have an inventory transaction table with several fields, specifically: part_no trans_type trans_qty part_no | trans_type | trans_qty abc REC 5000(receipt) abc REC 400 (receipt) abc SHP 1000(shi

[GENERAL] SP to calc shipments vs receipts

I have an inventory transaction table with several fields, specifically: part_no trans_type trans_qty part_no | trans_type | trans_qty abc REC 5000(receipt) abc REC 400 (receipt) abc SHP 1000(shipment) abc ALL 1000(all

Re: [GENERAL] streaming replication not working

On 9/23/2013 9:30 PM, Ray Stell wrote: > > On Sep 23, 2013, at 4:47 PM, John DeSoi wrote: >> >> You mean on the primary, right? > > right > > >> Yes, I have one there. But even if I did not, I would expect to see a >> connection error in the log on the standby. No error or any >> indication the str

Re: [GENERAL] streaming replication not working

On Sep 23, 2013, at 4:47 PM, John DeSoi wrote: > > You mean on the primary, right? right > Yes, I have one there. But even if I did not, I would expect to see a > connection error in the log on the standby. No error or any indication the > streaming replication process is running on the stan

Re: [GENERAL] How to create recurrence schedule event?

pgAgent is a job scheduler for PostgreSQL which may be managed using pgAdmin. Prior to pgAdmin v1.9, pgAgent shipped as part of pgAdmin. From pgAdmin v1.9 onwards, pgAgent is shipped as a separate application. On 09/24/2013 02:53 AM, Quang Thoi wrote: Hi, I am trying to port sybase codes to

Re: [GENERAL] Howto import regularly some CSV files with variing names?

On 09/23/2013 05:47 PM, Andreas wrote: Am 24.09.2013 02:25, schrieb Adrian Klaver: On 09/23/2013 05:19 PM, Andreas wrote: I need to import some log-files of an application [...] The import would be easy if the files had a constant name but the app creates csv files with names like "ExportM

Re: [GENERAL] Building with MinGW issue

On 09/23/2013 05:29 PM, Muhammad Bashir Al-Noimi wrote: On 09/24/2013 01:29 AM, Adrian Klaver wrote: Reported here: http://www.postgresql.org/message-id/ovpbg9.x5ovpbg9.pq9n.w333.g...@asuka.myrkraverk.com Not sure what the fix is, short of doing the manual intervention shown in the first mes

Re: [GENERAL] How to create recurrence schedule event?

On Tue, Sep 24, 2013 at 3:55 AM, Raymond O'Donnell wrote: > There isn't a built-in scheduler in PostgreSQL - the usual advice is to > use cron to execute a query via the psql client, or to install and use > pgAgent. Or with a 9.3 server to use a background worker that could do the job for you as a

Re: [GENERAL] Howto import regularly some CSV files with variing names?

Am 24.09.2013 02:25, schrieb Adrian Klaver: On 09/23/2013 05:19 PM, Andreas wrote: I need to import some log-files of an application [...] The import would be easy if the files had a constant name but the app creates csv files with names like "ExportMMDD". So how would I get the filenam

Re: [GENERAL] Building with MinGW issue

On 09/24/2013 01:29 AM, Adrian Klaver wrote: Reported here: http://www.postgresql.org/message-id/ovpbg9.x5ovpbg9.pq9n.w333.g...@asuka.myrkraverk.com Not sure what the fix is, short of doing the manual intervention shown in the first message. I don't think this thread is related here becaus

Re: [GENERAL] Howto import regularly some CSV files with variing names?

On 09/23/2013 05:19 PM, Andreas wrote: Hi, I need to import some log-files of an application running on a different host. This app can't talk to the db but only creates daily a dump in a remote directory that is mountable via samba by a Linux host that runs the db-server. The import would be ea

[GENERAL] Howto import regularly some CSV files with variing names?

Hi, I need to import some log-files of an application running on a different host. This app can't talk to the db but only creates daily a dump in a remote directory that is mountable via samba by a Linux host that runs the db-server. The import would be easy if the files had a constant name

Re: [GENERAL] Building with MinGW issue

On 09/23/2013 03:50 PM, Muhammad Bashir Al-Noimi wrote: On 09/24/2013 12:35 AM, Adrian Klaver wrote: Best guess is that you have a 32/64 bit mismatch problem. This is explained here. http://www.postgresql.org/docs/9.3/interactive/install-windows-full.html#AEN26293 But I build under Windows 7

Re: [GENERAL] Building with MinGW issue

On 09/24/2013 12:35 AM, Adrian Klaver wrote: Best guess is that you have a 32/64 bit mismatch problem. This is explained here. http://www.postgresql.org/docs/9.3/interactive/install-windows-full.html#AEN26293 But I build under

Re: [GENERAL] Building with MinGW issue

On 09/23/2013 02:20 PM, Muhammad Bashir Al-Noimi wrote: On 09/23/2013 04:05 AM, Adrian Klaver wrote: If it where me I would try: --with-includes=/c/OpenSSL-Win32/include --with-libraries=/c/OpenSSL-Win32/lib/MinGW Thanks a lot Adrian; I successfully configured PG with MinGW & OpenSSL but I f

Re: [GENERAL] reading cvs logs with pgadmin queries

On 09/23/2013 12:46 PM, Dave Cramer wrote: OK, I have a little more information. Yes, in isolation I can import these lines, however something happens after 69000 lines. These lines cause an error. Is it the same error? The exact error message is ERROR: extra data after last expected column

Re: [GENERAL] streaming replication not working

On Sep 23, 2013, at 1:00 PM, Ray Stell wrote: >> >> Am I missing something obvious? Do I have to back up the primary again to >> make this change? > > you didn't mention a pg_hba.conf rule. did you add one for the replication > user? You mean on the primary, right? Yes, I have one there.

Re: [GENERAL] Building with MinGW issue

On 09/23/2013 04:05 AM, Adrian Klaver wrote: If it where me I would try: --with-includes=/c/OpenSSL-Win32/include --with-libraries=/c/OpenSSL-Win32/lib/MinGW Thanks a lot Adrian; I successfully configured PG with MinGW & OpenSSL but I faced a new obstacle with make command: In file include

Re: [GENERAL] reading cvs logs with pgadmin queries

I tried copying 500 lines above it and it still works :( Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Mon, Sep 23, 2013 at 4:15 PM, Alvaro Herrera wrote: > Dave Cramer escribió: > > OK, > > > > I have a little more information. > > > > Yes, in isolation I can import the

Re: [GENERAL] streaming replication not working

On Sep 23, 2013, at 1:00 PM, Ray Stell wrote: >> >> Am I missing something obvious? Do I have to back up the primary again to >> make this change? > > you didn't mention a pg_hba.conf rule. did you add one for the replication > user? You mean on the primary, right? Yes, I have one there.

Re: [GENERAL] reading cvs logs with pgadmin queries

Dave Cramer escribió: > OK, > > I have a little more information. > > Yes, in isolation I can import these lines, however something happens after > 69000 lines. These lines cause an error. Stray quotes in previous lines? Perhaps check whether line 65999 was imported correctly. -- Álvaro Herre

Re: [GENERAL] reading cvs logs with pgadmin queries

OK, I have a little more information. Yes, in isolation I can import these lines, however something happens after 69000 lines. These lines cause an error. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Fri, Sep 20, 2013 at 9:59 AM, Adrian Klaver wrote: > On 09/19/2013

Re: [GENERAL] How to create recurrence schedule event?

On 23/09/2013 19:53, Quang Thoi wrote: > Hi, > > > > I am trying to port sybase codes to posgresql. I have looked into the > postgresql documentation > > but couldn't find good information on how to create an equivalent > recurrence event > > which similar to the sybase codes below. There i

[GENERAL] How to create recurrence schedule event?

Hi, I am trying to port sybase codes to posgresql. I have looked into the postgresql documentation but couldn't find good information on how to create an equivalent recurrence event which similar to the sybase codes below. Can some one please help? sybase codes: create event PURGE_ORDER sche

Re: [GENERAL] streaming replication not working

On Sep 23, 2013, at 11:20 AM, John DeSoi wrote: > > Am I missing something obvious? Do I have to back up the primary again to > make this change? you didn't mention a pg_hba.conf rule. did you add one for the replication user? -- Sent via pgsql-general mailing list (pgsql-general@postgres

Re: [GENERAL] Query runs forever after upgrading to 9.3

On Mon, Sep 23, 2013 at 9:50 AM, Andrus wrote: > http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server > wrote that max reasonable value in Windows for shared_buffers is 512MB > Is my setting shared_buffers= 2400MB reasonable in Windows ? > Someone else will hopefully answer that questi

Re: [GENERAL] Query runs forever after upgrading to 9.3

Hi, >For cluster-wide setting you will probably want to drop that significantly -- >start lower, as in somewhere around 10MB >and work up from there as necessary. >For the queries you are finding slow (the reason for these emails) you can set >>work_mem specifically for the session. >Eg. >set

Re: [GENERAL] Query runs forever after upgrading to 9.3

Hi! >Could you also post the results of the following query? >SELECT name, current_setting(name), source >FROM pg_settings >WHERE source NOT IN ('default', 'override'); In real server where problem is: 1 DateStyle ISO, DMY session 2 default_text_sea

Re: [GENERAL] Query runs forever after upgrading to 9.3

On Mon, Sep 23, 2013 at 9:12 AM, Andrus wrote: > > work_mem = 400MB > > Hope that this is OK. > For cluster-wide setting you will probably want to drop that significantly -- start lower, as in somewhere around 10MB and work up from there as necessary. For the queries you are finding slow (the re

Re: [GENERAL] Query runs forever after upgrading to 9.3

Hi, >>21 shared_buffers 2400MB configuration file >What are effective_cache_size and work_mem set to? The defaults? Yes. >They are good candidates to be increased. effective_cache_size could be set to >(for example) 10GB, depending on >how much memory gets consumed

Re: [GENERAL] Query runs forever after upgrading to 9.3

On Mon, Sep 23, 2013 at 8:33 AM, Andrus wrote: > Hi! > >Could you also post the results of the following query? > >SELECT name, current_setting(name), source > >FROM pg_settings > >WHERE source NOT IN ('default', 'override'); > In real server where problem is: > > > 21 shared_buffers

Re: [GENERAL] Tree structure

On Fri, Sep 20, 2013 at 6:29 AM, Kaare Rasmussen wrote: > Hi > > I'm trying to determine the best way to represent a simple tree structure > (like a file/dir tree or a uri path). I guess that's done a zillion times > before; I just don't seem to be able to find the right solution. I have one > spe

[GENERAL] streaming replication not working

I have a 9.2.4 (CentOS 6.5) standby that has been working with no problems using log shipping. I wanted to add streaming replication which I thought would be as simple as adding primary_conninfo to recovery.conf and restarting the standby. But on restart there is no message or error about connec

Re: [GENERAL] Query runs forever after upgrading to 9.3

I fixed the issue by creating indexes for temporary tables before running query: create index on tempalgsemu(toode); create index on temphetkes(toode); SELECT * FROM toode o WHERE exists (SELECT toode FROM tempkaive i where o.toode = i.toode ) OR EXISTS (SELECT toode FROM tempalgsemu i WHERE o

Re: [GENERAL] Query runs forever after upgrading to 9.3

On Mon, Sep 23, 2013 at 8:06 AM, Andrus wrote: > > >Could you please post EXPLAIN for that query? > Could you also post the results of the following query? SELECT name, current_setting(name), source FROM pg_settings WHERE source NOT IN ('default', 'override'); Also, what is the total memory in

Re: [GENERAL] passing multiple records to json_populate_recordset

On Mon, Sep 23, 2013 at 8:33 AM, Merlin Moncure wrote: > On Mon, Sep 23, 2013 at 8:25 AM, Raphael Bauduin wrote: >> >> >> On Mon, Sep 23, 2013 at 11:31 AM, Raphael Bauduin wrote: >>> >>> Hi, >>> >>> I'm experimenting with the json data type and functions in 9.3. >>> I'm storing json objects of t

Re: [GENERAL] Query runs forever after upgrading to 9.3

Hi, thank you. >Could you please post EXPLAIN for that query? As recommend I changed query to use exists : SELECT * FROM toode o WHERE exists (SELECT toode FROM tempkaive i where o.toode = i.toode ) OR EXISTS (SELECT toode FROM tempalgsemu i WHERE o.toode = i.toode) It still hangs in same w

Re: [GENERAL] Query runs forever after upgrading to 9.3

Hi, Thank you. Generally, WHERE IN (SELECT) should be rewritten as WHERE EXISTS (SELECT): SELECT * FROM toode o WHERE EXISTS (SELECT toode FROM tempkaive i WHERE o.toode = i.toode) OR EXISTS (SELECT toode FROM tempalgsemu i WHERE o.toode = i.toode) I re-wrote it. It now hangs in this line

Re: [GENERAL] Tree structure

On Sun, Sep 22, 2013 at 9:48 PM, Kaare Rasmussen wrote: > Hi Alban > > > 4. Using a recursive common table expression (CTE). >> http://www.postgresql.org/**docs/9.2/static/queries-with.**html >> > > Yes, you're right. In fact that's wh

Re: [GENERAL] passing multiple records to json_populate_recordset

On 09/23/2013 06:25 AM, Raphael Bauduin wrote: Some more info, after searching further. This query select json_populate_record(null::product,row_to_json(json_populate_recordset(null::product,event->'products'))) from (select * from events limit 2) as foo ; returns what I want but not in t

Re: [GENERAL] Query runs forever after upgrading to 9.3

Could you please post EXPLAIN for that query? How 'fat' are the temporary tables - just a couple of columns or really wide? On Mon, Sep 23, 2013 at 7:08 PM, Andrus wrote: > Query > > SELECT * FROM toode > WHERE toode in (SELECT toode FROM tempkaive) > OR toode in (SELECT toode FROM tempalg

Re: [GENERAL] Query runs forever after upgrading to 9.3

Re: Andrus 2013-09-23 > SELECT * FROM toode > WHERE toode in (SELECT toode FROM tempkaive) > OR toode in (SELECT toode FROM tempalgsemu) Generally, WHERE IN (SELECT) should be rewritten as WHERE EXISTS (SELECT): SELECT * FROM toode o WHERE EXISTS (SELECT toode FROM tempkaive i WHERE o.toode

[GENERAL] Query runs forever after upgrading to 9.3

Query SELECT * FROM toode WHERE toode in (SELECT toode FROM tempkaive) OR toode in (SELECT toode FROM tempalgsemu) stops working after upgrading to 9.3 RTM in Windows from earlier version. Task Manager shows that postgres.exe process has constantly 13% CPU usage (this is 8 core computer) a

Re: [GENERAL] passing multiple records to json_populate_recordset

On Mon, Sep 23, 2013 at 8:25 AM, Raphael Bauduin wrote: > > > On Mon, Sep 23, 2013 at 11:31 AM, Raphael Bauduin wrote: >> >> Hi, >> >> I'm experimenting with the json data type and functions in 9.3. >> I'm storing json objects of this form in the event column: >> {type: 'event_type, products : [

Re: [GENERAL] passing multiple records to json_populate_recordset

On Mon, Sep 23, 2013 at 11:31 AM, Raphael Bauduin wrote: > Hi, > > I'm experimenting with the json data type and functions in 9.3. > I'm storing json objects of this form in the event column: > {type: 'event_type, products : [ {id:45, 'type': 3, 'gender':'F',..}, ..., > {} ] } > > I can issue

Re: [GENERAL] Tree structure

BE carefull you have a number of limitation with recursive cte (I'm thinking of update and so.) You can work around with plpgsql but it might be painfull. You forgot a solution : if you need powerfull graph features, use postgres as a database and a SPARQL speaking frontend. It may be a bit of ove

[GENERAL] passing multiple records to json_populate_recordset

Hi, I'm experimenting with the json data type and functions in 9.3. I'm storing json objects of this form in the event column: {type: 'event_type, products : [ {id:45, 'type': 3, 'gender':'F',..}, ..., {} ] } I can issue this query, but notice the limit 1: select * from json_populate_records