Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

I think the table is dropped by end of function > On Jan 28, 2018, at 2:45 PM, David G. Johnston > wrote: > >> On Sunday, January 28, 2018, Rob Sargent wrote: >> The commit in question is the function btw. > > That doesn't make sense -

Consider Spaces in pg_trgm for Better Similarity

Is there a way to consider white space in tri-grams?  That would allow for better matches of phrases. For example, currently "one two three" and "three two one" would generate the same tri-grams ({  o,  t, on, th, tw,ee ,hre,ne ,one,ree,thr,two,wo }), and the distance of "one two four" will

Re: FW: Setting up streaming replication problems

Thanks for your patience. On 01/29/18 00:11, Martin Goodson wrote: "A password needs to be provided too, if the primary demands password authentication. It can be provided in the primary_conninfo string, or in a separate ~/.pgpass file on the standby server (use replication as the database

Re: [GENERAL] pgpass file type restrictions

On Thu, Oct 19, 2017 at 08:22:18AM -0400, Andrew Dunstan wrote: > > > On 10/19/2017 02:12 AM, Tom Lane wrote: > > Desidero writes: > >> I’m running into problems with the restriction on pgpass file types. When > >> attempting to use something like an anonymous pipe for a

How to Optimize pg_trgm Performance

I want to use pg_trgm for auto-suggest functionality.  I created a Materialized View with the information that I need, with the relevant columns being (keywords text, rank int).  keywords is the column from which I build the tri-grams, and rank is some popularity factor so that popular results

Re: FW: Setting up streaming replication problems

Me again. Hope you wont feel to bothered by me. I just summarise so far and am still in dire need of guidance. Debian 9 with PostreSQL 10. I have two nodes in the cluster I use as master and as standby. I tried to setup replication with Rigg's book and the official documentation and a couple

Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

On Sunday, January 28, 2018, Rob Sargent wrote: > > The commit in question is the function btw. > That doesn't make sense - functions can't commit. David J.

pg_trgm Extension Installed but Operators and Functions do not exist

I am trying to run a query which was working a few weeks ago, and it utilizes the <<-> operator but I am getting an error: operator does not exist text <<-> text. If I try instead the function word_similarity() I get a similar (no pun intended) error: function word_similarity(text, text) does

Re: PostgreSQL Restore Database Without Backup and Log

Adrian Klaver writes: > On 01/26/2018 05:58 AM, fabio.silva wrote: >> I had a Ransomware atack and I lost a lot of file from my server. >> I just have the OID folder and the files inside it. >> Is it possible restore database using only this data files? > I am going

Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

“On commit drop” is generally good practice for several reasons: if your function is called in a loop, or the create statement is in a loop or you want to call your function more than once in a given session (with out explicitly dropping your temp table). The commit in question is the function

Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

On 01/28/2018 08:46 AM, Ryan Murphy wrote: I believe the main, and maybe only, concern is the bloating of the system catalog tables since you are constantly adding and removing records.  Yes, they will be vacuumed but vacuuming and bloat on catalog tables slows every single query down to

Re: Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

On Sunday, January 28, 2018, Ryan Murphy wrote: > Hello, > > I heard at a PostgreSQL talk that you should not liberally create temp > tables in the course of frequently-used functions etc, because (roughly) > you're using up some of the same resources that you for your

Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

Hello, I heard at a PostgreSQL talk that you should not liberally create temp tables in the course of frequently-used functions etc, because (roughly) you're using up some of the same resources that you for your regular tables. Is this true? Is there an important reason not to have e.g. a

Re: Best way to select a random row from a derived table

On 28-01-2018 08:39, Ryan Murphy wrote: Hello hackers and postgressers, I am aware of 2 ways to select a random row from a table: 1) select * from table_name order by random() limit 1; -- terribly inefficient 2) select * from table_name tablesample system_rows(1) limit 1;

Re: Best way to select a random row from a derived table

I am aware of 2 ways to select a random row from a table: 1) select * from table_name order by random() limit 1; -- terribly inefficient 2) select * from table_name tablesample system_rows(1) limit 1; -- only works on tables, not views or subqueries Is there an option