Re: [GENERAL] referential integrity between elements of an array and another table?

Henry M writes: > No hesitation. I am just trying to understand the options. This blog seemed > to indicate the functionality existed but it looks like it was never > completed ( > https://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/ > ).

Re: [GENERAL] full text search on hstore or json with materialized view?

On Thu, 20 Apr 2017 00:55:48 -0400, George Neuner wrote: Doh! > SELECT count(distinct s.id) >FROM samples_lg_txt AS s >JOIN keys AS k ON k.id = s.key >WHERE (k.name = 'key1' AND s.tsv @@ to_query('value1') > OR (k.name = 'key2' AND s.tsv @@

Re: [GENERAL] full text search on hstore or json with materialized view?

On Wed, 19 Apr 2017 16:28:13 -0700, Rj Ewing wrote: >okay, messing around a bit more with the secondary k,v table it seems like >this could be a good solution.. > >I created a keys table to hold the 63 key values, then I dropped and >recreated the secondary table, using a FK

[GENERAL] cluster on brin indexes?

I see this, but no follow up: https://www.postgresql.org/message-id/CAEepm%3D2LUCLZ2J4cwPv5DisHqD9BE_AXnqHGqf0Tj-cvtiqVcQ%40mail.gmail.com So, is it possible or not? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] full text search on hstore or json with materialized view?

Please don't top-post, thanks. On Tue, Apr 18, 2017 at 3:18 PM, Rj Ewing wrote: > On Tue, Apr 18, 2017 at 3:00 PM, Bruce Momjian wrote: > >> >> Full text search of JSON and JSONB data is coming in Postgres 10, which >> is to to be released in September of

Re: [GENERAL] Large data and slow queries

Okay, so after changing longitude/latitude to float4, and re-organizing the table a bit, I got the query down from about 8 minutes to 40 seconds. The details are in the gist comments: https://gist.github.com/ioquatix/bddda36d9e4ffaceb7a62d7b62259121 Now, just need to get performance another 2

Re: [GENERAL] full text search on hstore or json with materialized view?

Please don't top post. https://en.wikipedia.org/wiki/Posting_style#Placement_of_replies https://en.wikipedia.org/wiki/Posting_style#Choosing_the_proper_posting_style >> On Tue, 18 Apr 2017 14:38:15 -0700, Rj Ewing >> wrote: >> >> > : >> >An idea that has come up is to use

Re: [GENERAL] referential integrity between elements of an array and another table?

For me, foreign keys for arrays would be incredibly helpful. For simple checkbox-type options, it's _much_ simpler to just put them in an array, and I work with a UI that can handle the arrays well. I do hope this makes it into Postgresql someday! In the meantime, I've mostly sacrificed the

Re: [GENERAL] referential integrity between elements of an array and another table?

No hesitation. I am just trying to understand the options. This blog seemed to indicate the functionality existed but it looks like it was never completed ( https://blog.2ndquadrant.com/postgresql-9-3-development-array-element-foreign-keys/ ). I see in this document

Re: [GENERAL] full text search on hstore or json with materialized view?

okay, messing around a bit more with the secondary k,v table it seems like this could be a good solution.. I created a keys table to hold the 63 key values, then I dropped and recreated the secondary table, using a FK referencing the keys table. I'm not really sure why, but a basic full text

Re: [GENERAL] Unable to upload backups

Hi, Seems to me your role "ronb" doesn't have the rights to create schema on your database. Please refer to the GRANT command in the documentation. https://www.postgresql.org/docs/9.0/static/sql-grant.html Sent from [ProtonMail](https://protonmail.ch), encrypted email based in Switzerland.

Re: [GENERAL] UDP buffer drops / statistics collector

> On 19 Apr 2017, at 20:36, Tim Kane wrote: > > Well, this is frustrating.. > The buffer drops are still occurring - so I thought it worth trying use a > ramdisk and set stats_temp_directory accordingly. > > I've reloaded the instance, and can see that the stats directory

Re: [GENERAL] referential integrity between elements of an array and another table?

On 04/19/2017 12:13 PM, Henry M wrote: I was just reading this question on reddit (the text duplicated below). I was wondering if there is an approach for handling array foreign key references. I am interested in the answer since I started using array fields as well. Thank you. The only thing

Re: [GENERAL] referential integrity between elements of an array and another table?

On 04/19/2017 01:13 PM, Henry M wrote: I was just reading this question on reddit (the text duplicated below). I was wondering if there is an approach for handling array foreign key references. I am interested in the answer since I started using array fields as well. Thank you. -

Re: [GENERAL] referential integrity between elements of an array and another table?

On 04/19/2017 01:13 PM, Henry M wrote: I was just reading this question on reddit (the text duplicated below). I was wondering if there is an approach for handling array foreign key references. I am interested in the answer since I started using array fields as well. Thank you. -

[GENERAL] referential integrity between elements of an array and another table?

I was just reading this question on reddit (the text duplicated below). I was wondering if there is an approach for handling array foreign key references. I am interested in the answer since I started using array fields as well. Thank you. - below is the message from the reddit poster:

Re: [GENERAL] full text search on hstore or json with materialized view?

I did some testing using a secondary table with the key, value column. However I don't think this will provide the performance that we need. Queries we taking 60+ seconds just for a count. With 1 million rows in the primary table, this resulted in 44 million rows in the secondary k,v table for

Re: [GENERAL] streaming replication and archive_status

2017-04-18 22:46 GMT-03:00 Jeff Janes : > On Tue, Apr 18, 2017 at 5:20 AM, Luciano Mittmann > wrote: > >> >> >> Hi Jeff, >> >> **Does each file in pg_xlog/archive_status/ have a corresponding file >> one directory up? >> >> no corresponding file on

Re: [GENERAL] UDP buffer drops / statistics collector

Well, this is frustrating.. The buffer drops are still occurring - so I thought it worth trying use a ramdisk and set *stats_temp_directory* accordingly. I've reloaded the instance, and can see that the stats directory is now being populated in the new location. *Except* - there is one last file

Re: [GENERAL] Recover corrupted data

On 4/19/2017 9:24 AM, Alexandre wrote: 2) We dont use RAID. so just a direct attached single disk drive? is it perchance a 'desktop' type disk? those often have dodgy write buffering and lie about writes (saying they are complete when they are just in a volatile ram buffer). sometimes

Re: [GENERAL] Recover corrupted data

On Wed, 19 Apr 2017 13:25:41 -0300, Alexandre wrote: > : >But there is no solution for [file corruption]? The only solutions are to guard against it: make frequent backups and make use of safety mechanisms in Postgresql and in the OS. Postgresql logs (WAL) intended changes

Re: [GENERAL] Why so long?

On 20 April 2017 at 03:24, Steve Clark wrote: > pmacct=# explain select min(id) from netflow; >QUERY PLAN >

Re: [GENERAL] Recover corrupted data

It appears to be just one table I'm trying to backup without that table. But there is no solution for this kind of error? On Wed, Apr 19, 2017 at 1:11 PM, Moreno Andreo wrote: > Il 19/04/2017 17:49, Vick Khera ha scritto: > > 1) restore from backup > 2) fix whatever

Re: [GENERAL] Recover corrupted data

1) We have a backup but its from the last month, I will try to backup the data without the table that raises the exception. 2) We dont use RAID. Thank you On Wed, Apr 19, 2017 at 12:49 PM, Vick Khera wrote: > 1) restore from backup > 2) fix whatever configuration you made to

Re: [GENERAL] Why so long?

On 04/19/2017 11:57 AM, Jeff Janes wrote: > On Wed, Apr 19, 2017 at 8:24 AM, Steve Clark > wrote: > > Hello, > > I am confused. I have a table that has an incrementing primary key id. > > When I select max(id) from table

Re: [GENERAL] Recover corrupted data

Il 19/04/2017 17:49, Vick Khera ha scritto: 1) restore from backup 2) fix whatever configuration you made to let windows (or your hardware) destroy your data on crash. is there some RAID cache that is not backed up by a battery?

Re: [GENERAL] Why so long?

On Wed, Apr 19, 2017 at 8:24 AM, Steve Clark wrote: > Hello, > > I am confused. I have a table that has an incrementing primary key id. > > When I select max(id) from table is returns almost instantly but > when I select min(id) from table it takes longer than I want

Re: [GENERAL] Recover corrupted data

1) restore from backup 2) fix whatever configuration you made to let windows (or your hardware) destroy your data on crash. is there some RAID cache that is not backed up by a battery? On Wed, Apr 19, 2017 at 10:18 AM, Alexandre wrote: > Hello, > > The computer had a

Re: [GENERAL] Why so long?

Should add this is version 9.4.10 of postgresql On 04/19/2017 11:24 AM, Steve Clark wrote: > Hello, > > I am confused. I have a table that has an incrementing primary key id. > > When I select max(id) from table is returns almost instantly but > when I select min(id) from table it takes longer

[GENERAL] Why so long?

Hello, I am confused. I have a table that has an incrementing primary key id. When I select max(id) from table is returns almost instantly but when I select min(id) from table it takes longer than I want to wait. Shouldn't postgresql be able to quickly find the minimum id value in the index?

Re: [GENERAL] Large data and slow queries

> On 19 Apr 2017, at 12:58, Samuel Williams > wrote: > > It's interesting you talk about using multiple indexes. In > MySQL/MariaDB and derivatives, I've never seen the query planner > consider using multiple indexes. So, it's possible that Postgres may > use

Re: [GENERAL] Large data and slow queries

Ah right, yeah, it's insert only. So, it's never been vacuumed. On 20 April 2017 at 01:25, Stephen Frost wrote: > Greetings, > > * Samuel Williams (space.ship.travel...@gmail.com) wrote: >> Thanks for all the suggestions Stephen. >> >> > That explain analyze shows a whole ton

Re: [GENERAL] tuple statistics update

Tom DalPozzo writes: > 2017-04-18 22:06 GMT+02:00 Tom Lane : >> If your session is just sitting, that's not surprising. I think stats >> updates are only transmitted to the collector at transaction end (and >> even then, only if it's been at least N msec

Re: [GENERAL] Unable to upload backups

On 04/19/2017 07:16 AM, Ron Ben wrote: Here :) Thanks. See my previous response. Basically we need more information before this can be solved. I think I may have found the problem. The role defined as: CREATE ROLE "ronb" LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

[GENERAL] Recover corrupted data

Hello, The computer had a unexpected shutdown, it is a Windows machine. Now some data appears to be corrupted, I am receiving exceptions like this: ERROR: could not read block 0 in file "base/16393/16485": read only 0 of 8192 bytes There is some way to correct this?

Re: [GENERAL] Unable to upload backups

Here :) I think I may have found the problem.   The role defined as:   CREATE ROLE "ronb" LOGIN  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;GRANT users TO "ronb"; GRANT users2 TO "ronb";   users is a group role:   CREATE ROLE users   SUPERUSER INHERIT CREATEDB CREATEROLE

Re: [GENERAL] Unable to upload backups

On 04/19/2017 06:49 AM, Ron Ben wrote: Is it possible to get your email program to left justify text on sending? I can figure out the right justified text, it just takes me longer. I think I may have found the problem. The role defined as: CREATE ROLE "ronb" LOGIN NOSUPERUSER INHERIT

Re: [GENERAL] tuple statistics update

On 04/19/2017 12:28 AM, Tom DalPozzo wrote: 2017-04-18 21:42 GMT+02:00 Adrian Klaver >: On 04/17/2017 09:18 AM, Tom DalPozzo wrote: Hi, I'm using libpq to insert tuples in my table and keep looking at statistics

Re: [GENERAL] Unable to upload backups

I think I may have found the problem.   The role defined as:   CREATE ROLE "ronb" LOGIN  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;GRANT users TO "ronb"; GRANT users2 TO "ronb";   users is a group role:   CREATE ROLE users   SUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;    

Re: [GENERAL] Unable to upload backups

On 04/19/2017 03:56 AM, Ron Ben wrote: Hi, I'm using PostgreSQL 9.3.2 I'm running the command: psql -h testserver -U ronb -f backup.sql -q -d foldertest 2>error.txt output.txt What was the command that created backup.sql? This should generate my database in foldertest However this

Re: [GENERAL] Large data and slow queries

Greetings, * Samuel Williams (space.ship.travel...@gmail.com) wrote: > Thanks for all the suggestions Stephen. > > > That explain analyze shows a whole ton of heap fetches. When was the > last time a VACUUM was run on this table, to build the visibility map? > Without the visibility map being

Re: [GENERAL] Large data and slow queries

Thanks for all the suggestions Stephen. > That explain analyze shows a whole ton of heap fetches. When was the last time a VACUUM was run on this table, to build the visibility map? Without the visibility map being current, an Index-Only Scan, as is happening here, can really suck. I'm using

Re: [GENERAL] Large data and slow queries

Greetings, * Samuel Williams (space.ship.travel...@gmail.com) wrote: > We want the following kinds of query to be fast: "kinds of query" isn't helpful, you should be reviewing exactly the queries you care about because statistics and your exact data set and what the exact query you're running is

Re: [GENERAL] potential extension of psql's \df+ ?

2017-04-19 12:14 GMT+02:00 Karsten Hilbert : > On Wed, Apr 19, 2017 at 12:00:04PM +0200, Pavel Stehule wrote: > > > > Hence I wonder whether an approach along these lines: > > > > > > select > > > row_number() over () > > > ||

Re: [GENERAL] Large data and slow queries

Hello Vinny, Samuel, Perhaps I'm missing something, but I'd be interested in the reasoning behind this. For column 'what', it seems you have no index on all values, only indices with specific values for 'what'. How does this speed up the search? Will PostgreSQL use those indices, instead of

Re: [GENERAL] Large data and slow queries

On 2017-04-19 13:25, Martijn Tonies (Upscene Productions) wrote: Samuel, others, Perhaps I'm missing something, but I'd be interested in the reasoning behind this. For column 'what', it seems you have no index on all values, only indices with specific values for 'what'. How does this speed up

Re: [GENERAL] Large data and slow queries

Martijn that is a good question. It's because we are only concerned with a subset of events for this index and this particular query. The query planner can recognise this and use the index correctly. By doing this, we reduce the size of the index significantly. In the best case, where we only

Re: [GENERAL] Large data and slow queries

Samuel, others, Perhaps I'm missing something, but I'd be interested in the reasoning behind this. For column 'what', it seems you have no index on all values, only indices with specific values for 'what'. How does this speed up the search? Will PostgreSQL use those indices, instead of

Re: [GENERAL] Large data and slow queries

On 2017-04-19 09:48, John R Pierce wrote: On 4/19/2017 12:31 AM, vinny wrote: Given the number of records, my first thought was either partitioning or partial-indexes. The fewer rows are in the index, the quicker it will be to check, and it's not a lot of work to create separate indexes for

Re: [GENERAL] Large data and slow queries

Thanks Alban, I appreciate your ideas and thoughts. I'm a little reluctant to go down the partitioning route as I think we'll probably end up sharding out horizontally using citus data in the near future and naive postgres partitioning may hamper that effort. It's interesting you talk about

[GENERAL] Unable to upload backups

Hi, I'm using PostgreSQL 9.3.2 I'm running the command:   psql -h testserver -U ronb -f backup.sql  -q -d foldertest  2>error.txt >output.txt This should generate my database in foldertest However this doesn't work. It's unable to create schemas in the error.txt i see "permission denied for

Re: [GENERAL] Large data and slow queries

> On 19 Apr 2017, at 6:01, Samuel Williams > wrote: > > Hi. > > We have 400,000,000 records in a table (soon to be 800,000,000), here > is the schema (\d+) > > https://gist.github.com/ioquatix/bddda36d9e4ffaceb7a62d7b62259121 > > We want the following kinds

Re: [GENERAL] potential extension of psql's \df+ ?

On Wed, Apr 19, 2017 at 12:00:04PM +0200, Pavel Stehule wrote: > > Hence I wonder whether an approach along these lines: > > > > select > > row_number() over () > > || src_line ... > > ) as func_src; > > > > would be a worthwhile change to the query

Re: [GENERAL] Large data and slow queries

> Did that 50% performance gain come from just the datatype, or that fact that > the index became smaller? How would one measure this? On 19 April 2017 at 19:48, John R Pierce wrote: > On 4/19/2017 12:31 AM, vinny wrote: >> >> Given the number of records, my first thought

Re: [GENERAL] potential extension of psql's \df+ ?

Hi 2017-04-19 11:55 GMT+02:00 Karsten Hilbert : > Hi all ! > > Every so often, when working with functions, errors get > reported with context information similar to this: > > Context: PL/pgSQL function "test_function" line 5 at SQL statement > > Often, the

[GENERAL] potential extension of psql's \df+ ?

Hi all ! Every so often, when working with functions, errors get reported with context information similar to this: Context: PL/pgSQL function "test_function" line 5 at SQL statement Often, the function source is kept under version control (or in a file annotated, commented, etc in

Re: [GENERAL] # of connections and architecture design

Il 18/04/2017 18:51, Jeff Janes ha scritto: On Tue, Apr 18, 2017 at 2:42 AM, Moreno Andreo wrote: Hi all,     As many of you has read last Friday (and many has

Re: [GENERAL] Large data and slow queries

On 4/19/2017 12:31 AM, vinny wrote: Given the number of records, my first thought was either partitioning or partial-indexes. The fewer rows are in the index, the quicker it will be to check, and it's not a lot of work to create separate indexes for lat/long ranges or dates. that only works

Re: [GENERAL] tuple statistics update

2017-04-18 22:06 GMT+02:00 Tom Lane : > Tom DalPozzo writes: > > Hi, I'm using libpq to insert tuples in my table and keep looking at > > statistics through psql instead. > > I noticed that sometimes n_tuple_ins is not updated even after 1 min that > >

Re: [GENERAL] Large data and slow queries

On 2017-04-19 07:04, Samuel Williams wrote: Thanks John. Yes, you are absolutely right, you want the index to be bottom heavy so you can cull as much as possible at the top. I'm familiar with that, once implementing a brute-force sudoku solver, it has the same principle. I've been working on

Re: [GENERAL] tuple statistics update

2017-04-18 21:42 GMT+02:00 Adrian Klaver : > On 04/17/2017 09:18 AM, Tom DalPozzo wrote: > >> Hi, I'm using libpq to insert tuples in my table and keep looking at >> statistics through psql instead. >> I noticed that sometimes n_tuple_ins is not updated even after 1 min