[GENERAL] MVIEW refresh consistently faster then insert ... select

Hello, I have a table that is an aggregation of another table. This aggregation reduces an input of ~14 million rows to ~4 million rows. So far I have used a truncate/insert approach for this: truncate table stock; insert into stock (product_id, warehouse_id, reserved_provisional, re

Re: [GENERAL] Thoughts on "Love Your Database"

Good morning > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Guyren Howe > Sent: Mittwoch, 4. Mai 2016 06:11 > To: pgsql-general > Subject: [GENERAL] Thoughts on "Love Your Database" > > I've long been frustrated

Re: [GENERAL] Thoughts on "Love Your Database"

> ow...@postgresql.org] On Behalf Of Guyren Howe > I've long been frustrated with how most web developers I meet have no idea > how to use an SQL database properly. I think I'm going to write a book called > Love Your Database, aimed at web developers, that explains how to make their > apps better

Re: [GENERAL] Function PostgreSQL 9.2

On Tuesday, May 3, 2016, drum.lu...@gmail.com wrote: > >- This is what I did... > > -- Creating the tableCREATE TABLE public.company_seqs(company_id BIGINT NOT > NULL, > last_seq BIGINT NOT NULL DEFAULT 1000,CONSTRAINT company_seqs_pk PRIMARY KEY > (company_id)); > > -- Creating the functio

[GENERAL] Thoughts on "Love Your Database"

I've long been frustrated with how most web developers I meet have no idea how to use an SQL database properly. I think I'm going to write a book called Love Your Database, aimed at web developers, that explains how to make their apps better by leveraging the power of SQL in general, and Postgre

Re: [GENERAL] Function PostgreSQL 9.2

- This is what I did... -- Creating the tableCREATE TABLE public.company_seqs(company_id BIGINT NOT NULL, last_seq BIGINT NOT NULL DEFAULT 1000,CONSTRAINT company_seqs_pk PRIMARY KEY (company_id)); -- Creating the function CREATE OR REPLACE FUNCTION users_code_seq() RETURNS "trigger" AS' BE

Re: [GENERAL] Function PostgreSQL 9.2

From: David G. Johnston Sent: Tuesday, May 03, 2016 2:46 PM To: drum.lu...@gmail.com …The only other reasonable option is change your model and requirements to something less complex. Seriously, get yourself the books I have recommended and study them BEFORE you continue attempting to

Re: [GENERAL] ruby pg connection fails on centos - okay on debian and dev machine

On 05/03/2016 05:07 PM, john.tiger wrote: our model.rb runs fine on a dev machine and a debian server but is failing on a new centos server - checked the postgres db name and user name and password - all seem fine The error message is? could it be: host => "localhost" or maybe CORS ? CO

Re: [GENERAL] ruby pg connection fails on centos - okay on debian and dev machine

On 05/03/2016 05:07 PM, john.tiger wrote: our model.rb runs fine on a dev machine and a debian server but is failing on a new centos server - checked the postgres db name and user name and password - all seem fine could it be: host => "localhost" or maybe CORS ? What do the PostgreSQL log

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 5:06 PM, Melvin Davidson wrote: > > > On Tue, May 3, 2016 at 7:53 PM, drum.lu...@gmail.com > wrote: > >> >>> >>> I agree that having thousands of sequences can be hard to manage, >>> especially in a function, but you did not state that fact before, >>> only that you wanted

[GENERAL] ruby pg connection fails on centos - okay on debian and dev machine

our model.rb runs fine on a dev machine and a debian server but is failing on a new centos server - checked the postgres db name and user name and password - all seem fine could it be: host => "localhost" or maybe CORS ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 7:53 PM, drum.lu...@gmail.com wrote: > >> >> I agree that having thousands of sequences can be hard to manage, >> especially in a function, but you did not state that fact before, >> only that you wanted separate sequences for each company. That >> being said, here is an al

Re: [GENERAL] Function PostgreSQL 9.2

> > > > I agree that having thousands of sequences can be hard to manage, > especially in a function, but you did not state that fact before, > only that you wanted separate sequences for each company. That > being said, here is an alternate solution. > Yep.. that was my mistake. > > 1. CREATE T

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 5:53 PM, Adrian Klaver wrote: > On 05/03/2016 02:27 PM, drum.lu...@gmail.com wrote: > >> >> >> On 4 May 2016 at 01:18, Melvin Davidson > > wrote: >> >> >> >> On Tue, May 3, 2016 at 1:21 AM, David G. Johnston >> mailto:david.g.johns...@gm

Re: [GENERAL] Function PostgreSQL 9.2

On 05/03/2016 02:27 PM, drum.lu...@gmail.com wrote: On 4 May 2016 at 01:18, Melvin Davidson mailto:melvin6...@gmail.com>> wrote: On Tue, May 3, 2016 at 1:21 AM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: Well.. I don't need to add a constraint if I alr

Re: [GENERAL] pg_upgrade with an extension name change

On Fri, Apr 8, 2016 at 12:15:27PM -0700, Christophe Pettus wrote: > I'm attempting to upgrade a database from 9.2 to 9.5 using pg_upgrade. > The 9.2 database has the "orafunc" extension installed, which appears > to have changed names to "orafce". pg_upgrade complains that it can't > find "orafunc"

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 2:27 PM, drum.lu...@gmail.com wrote: > On 4 May 2016 at 01:18, Melvin Davidson wrote: >> >> On Tue, May 3, 2016 at 1:21 AM, David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> Well.. I don't need to add a constraint if I already have a default value,

Re: [GENERAL] Function PostgreSQL 9.2

On 05/03/2016 02:27 PM, drum.lu...@gmail.com wrote: On 4 May 2016 at 01:18, Melvin Davidson mailto:melvin6...@gmail.com>> wrote: On Tue, May 3, 2016 at 1:21 AM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: Well.. I don't need to add a constraint if I alr

Re: [GENERAL] Function PostgreSQL 9.2

On 4 May 2016 at 01:18, Melvin Davidson wrote: > > > On Tue, May 3, 2016 at 1:21 AM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> >>> Well.. I don't need to add a constraint if I already have a default >>> value, that's right... >>> >> >> Wrong >> >> David J. >> > > What you need

Re: [GENERAL] Get sum of sums

On 5/3/2016 12:48 PM, Steve Clark wrote: I have the following table that has netflow data. I can get the top ten receivers by the query below - but I would also like to get at the same time a grand total of the RX Bytes. I am not exactly sure how to do it. This is with version 8.4. select ip_d

Re: [GENERAL] Get sum of sums

On Tue, May 3, 2016 at 12:57 PM, Victor Yegorov wrote: > 2016-05-03 22:48 GMT+03:00 Steve Clark : > >> select ip_dst as "Receiver" ,sum(bytes) as "RX Bytes" from acct_v9 where >> stamp_inserted >= '2016-04-26' and stamp_inserted <= '2016-04-30' and >> tag=246 group by ip_dst order by "RX Bytes"

Re: [GENERAL] Get sum of sums

2016-05-03 22:48 GMT+03:00 Steve Clark : > select ip_dst as "Receiver" ,sum(bytes) as "RX Bytes" from acct_v9 where > stamp_inserted >= '2016-04-26' and stamp_inserted <= '2016-04-30' and > tag=246 group by ip_dst order by "RX Bytes" desc limit 10; SELECT ip_dst AS "Receiver", sum(bytes) AS

[GENERAL] Get sum of sums

Hi List, I have the following table that has netflow data. I can get the top ten receivers by the query below - but I would also like to get at the same time a grand total of the RX Bytes. I am not exactly sure how to do it. This is with version 8.4. select ip_dst as "Receiver" ,sum(bytes) as

Re: [GENERAL] Insert only table and size of GIN index JSONB field.

On Tue, May 3, 2016 at 3:09 AM, Maxim Boguk wrote: > Hi, > > I started with empty table with index over > custom_fields | jsonb > field > defined as: > "idx_learners_custom_fields" gin (custom_fields) > Globally gin_pending_list_limit set to 2MB. > Database version is 9.5.2. > > Now question:

Re: [GENERAL] Allow disabling folding of unquoted identifiers to lowercase

That's an interesting idea! The client users would use is probably pgAdmin. I don't know whether pgAdmin parses the query, though. If it does then it should be relatively easy to add this. If not, I'd imagine it's not going to happen. On 2 May 2016 at 13:59, John McKown wrote: > On Mon, May 2, 2

Re: [GENERAL] arrays, inline to pointer

In fact, disabling toast compression will probably improve the performance (the indirection will still take place). A float array is not usually very compressible anyway. On May 3, 2016 10:37 AM, "John R Pierce" wrote: > On 5/3/2016 1:21 AM, Marcus Engene wrote: > > For each array I've added, and

Re: [GENERAL] Field size become unlimited in union...

"David G. Johnston" writes: > This seems to fail to answer the OPs question. Specifically, do these > rules automatically, or at least if #1 is not true, cause typemod > information to be lost? IOW, is it because of the unknown that both end up > up-casted to typemod-less text? Any mismatch of

Re: [GENERAL] Does the initial postgres user have a password?

> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Albe Laurenz > > What is going on here? Is there a password for user ‘postgres’ or > > isn’t there? What is it? Why don’t I need it sometimes, and I do other > times? > > You have a password set

Re: [GENERAL] Field size become unlimited in union...

On Tue, May 3, 2016 at 6:50 AM, Adrian Klaver wrote: > On 05/03/2016 04:28 AM, Durumdara wrote: > >> Hello! >> >> As I experienced, PGSQL changes the result field sizes to unlimited >> varchar, when I passed different field size in same column of the union. >> >> SP.Key - varchar(100) >> SP.Value

Re: [GENERAL] postgresql & Fulltext & ranking & my own functions

Thanks Oleg, this is a good start for me 2016-05-03 15:47 GMT+02:00 Oleg Bartunov : > > > On Tue, May 3, 2016 at 3:21 PM, Nicolas Paris wrote: > >> Hello, >> >> Documentation says : ( >> http://www.postgresql.org/docs/9.5/static/textsearch-controls.html#TEXTSEARCH-RANKING >> ) >> "The built-in r

Re: [GENERAL] Vacuum full of parent without partitions possible?

I am thinking that you are not using all child tables all time ,so it may not be the best solution but if you don't want to lock your active processes, alternatively you can try to disconnect parent-child (which is not actively in use ) relation using no inherintence then rename old child table .

Re: [GENERAL] Field size become unlimited in union...

On 05/03/2016 04:28 AM, Durumdara wrote: Hello! As I experienced, PGSQL changes the result field sizes to unlimited varchar, when I passed different field size in same column of the union. SP.Key - varchar(100) SP.Value - varchar(200) Example: select 'a', value from sp union all select

Re: [GENERAL] Insert only table and size of GIN index JSONB field.

On Tue, May 3, 2016 at 1:35 PM, Maxim Boguk wrote: > > > On Tue, May 3, 2016 at 8:09 PM, Maxim Boguk wrote: > >> Hi, >> >> I started with empty table with index over >> custom_fields | jsonb >> field >> defined as: >> "idx_learners_custom_fields" gin (custom_fields) >> Globally gin_pending_

Re: [GENERAL] postgresql & Fulltext & ranking & my own functions

On Tue, May 3, 2016 at 3:21 PM, Nicolas Paris wrote: > Hello, > > Documentation says : ( > http://www.postgresql.org/docs/9.5/static/textsearch-controls.html#TEXTSEARCH-RANKING > ) > "The built-in ranking functions are only examples. You can write your own > ranking functions and/or combine their

Re: [GENERAL] Vacuum full of parent without partitions possible?

Jan Keirse writes: > I have a table that used to contain all data. > because it grew too big I added a partition trigger a long time ago and > since than all new data was added to small partitions. By now all data in > the original parent table has become obsolete and was deleted, however the > di

Re: [GENERAL] Function PostgreSQL 9.2

On Tue, May 3, 2016 at 1:21 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > >> Well.. I don't need to add a constraint if I already have a default >> value, that's right... >> > > Wrong > > David J. > What you need is a TRIGGER function & TRIGGER that will select and assign the next

Re: [GENERAL] Does the initial postgres user have a password?

Other tools may try to connect with different users or with same users but using ip (not local host) and pg_hba.conf file may not allow paswordless login except localhost.Reseting postgres password with alter user, and configuring pg_hba.conf file can solve your problem. Regards, Ugur 2016-05-0

[GENERAL] postgresql & Fulltext & ranking & my own functions

Hello, Documentation says : ( http://www.postgresql.org/docs/9.5/static/textsearch-controls.html#TEXTSEARCH-RANKING ) "The built-in ranking functions are only examples. You can write your own ranking functions and/or combine their results with additional factors to fit your specific needs." The b

Re: [GENERAL] Does the initial postgres user have a password?

dandl wrote: > I have a new 9.5 installation, Windows x64, developer only. No users have > been added, and no passwords > set. > > I can access the system: > · using pgAdmin3, without specifying a user or password > · using psql, specifying user ‘postgres’ but no password > > I c

Re: [GENERAL] (VERY) Slow Query - PostgreSQL 9.2

From: drum.lu...@gmail.com Sent: Tuesday, May 03, 2016 2:55 AM I'm trying to get the query below a better performance.. but just don't know what else I can do... Please, have a look and let me know if you can help somehow.. also.. if you need some extra data j

[GENERAL] Vacuum full of parent without partitions possible?

Hello, I have a table that used to contain all data. because it grew too big I added a partition trigger a long time ago and since than all new data was added to small partitions. By now all data in the original parent table has become obsolete and was deleted, however the disk space cannot be rec

Re: [GENERAL] (VERY) Slow Query - PostgreSQL 9.2

> On 03 May 2016, at 11:55, drum.lu...@gmail.com wrote: > > Hi all, > > I'm trying to get the query below a better performance.. but just don't know > what else I can do... > > Please, have a look and let me know if you can help somehow.. also.. if you > need some extra data jet ask me please

[GENERAL] Field size become unlimited in union...

Hello! As I experienced, PGSQL changes the result field sizes to unlimited varchar, when I passed different field size in same column of the union. SP.Key - varchar(100) SP.Value - varchar(200) Example: select 'a', value from sp union all select key, value from sp The first field is not

[GENERAL] Does the initial postgres user have a password?

I have a new 9.5 installation, Windows x64, developer only. No users have been added, and no passwords set. I can access the system: * using pgAdmin3, without specifying a user or password * using psql, specifying user ‘postgres’ but no password I cannot access the system

Re: [GENERAL] Insert only table and size of GIN index JSONB field.

On Tue, May 3, 2016 at 8:09 PM, Maxim Boguk wrote: > Hi, > > I started with empty table with index over > custom_fields | jsonb > field > defined as: > "idx_learners_custom_fields" gin (custom_fields) > Globally gin_pending_list_limit set to 2MB. > Database version is 9.5.2. > > Now question

Re: [GENERAL] (VERY) Slow Query - PostgreSQL 9.2

On Tue, 3 May 2016 21:55:21 +1200 "drum.lu...@gmail.com" wrote: > Hi all, > > I'm trying to get the query below a better performance.. but just don't > know what else I can do... > > Please, have a look and let me know if you can help somehow.. also.. if you > need some extra data jet ask me pl

[GENERAL] Insert only table and size of GIN index JSONB field.

Hi, I started with empty table with index over custom_fields | jsonb field defined as: "idx_learners_custom_fields" gin (custom_fields) Globally gin_pending_list_limit set to 2MB. Database version is 9.5.2. Now question: If table populated with 1M records in single transaction then the final

[GENERAL] (VERY) Slow Query - PostgreSQL 9.2

Hi all, I'm trying to get the query below a better performance.. but just don't know what else I can do... Please, have a look and let me know if you can help somehow.. also.. if you need some extra data jet ask me please. * Note that the gorfs.inode_segments table is 1.7TB size I have the fol

Re: [GENERAL] arrays, inline to pointer

On 5/3/2016 1:21 AM, Marcus Engene wrote: For each array I've added, and populated, any dealings with the table has become way way slower. I can only assume this is because the array data is inline in the datablock on disk that stores the row. any field on a table thats more than a few dozen

[GENERAL] arrays, inline to pointer

Hi, I have some whopper tables for machine learning. One table has a handful of 200-500 double precision arrays (representing feature vectors). It's a 9.5 on a SSD (over USB3). Each table has 5-15M rows in them. For each array I've added, and populated, any dealings with the table has become