Re: [GENERAL] Question slow query

På torsdag 12. januar 2017 kl. 03:15:59, skrev Patrick B < patrickbake...@gmail.com >: Hi guys,   I've got a slow query, running at 25 seconds.   -> Bitmap Heap Scan on ja_notes r_1103088 (cost=234300.55..1254978.62 rows=553275 width=101) (actual time=1423.411..10

Re: [GENERAL] Means to emulate global temporary table

> On Jan 11, 2017, at 7:02 PM, David G. Johnston > wrote: > > ​"throughout" mustn't mean "by other sessions" or this becomes unwieldy. > > Here's a mock-up: > > CREATE TABLE template_table (); > CREATE VIEW view_over_my_template_instance AS SELECT * FROM > my_instance_of_template_table; --fa

Re: [GENERAL] Means to emulate global temporary table

On Wed, Jan 11, 2017 at 7:51 PM, John R Pierce wrote: > On 1/11/2017 6:39 PM, Ian Lewis wrote: > >> Does this mean that a local temporary table created in one function in a >> database is visible globally throughout the database for the duration of >> the session? >> > > postgres temporary tables

Re: [GENERAL] Means to emulate global temporary table

On 1/11/2017 6:39 PM, Ian Lewis wrote: Does this mean that a local temporary table created in one function in a database is visible globally throughout the database for the duration of the session? postgres temporary tables are only visible to the session that creates them. all kind of w

Re: [GENERAL] Means to emulate global temporary table

On Wed, Jan 11, 2017 at 7:39 PM, Ian Lewis wrote: > On Wed, Jan 11, 2017 at 4:54 PM, David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> > ​The way I read this is that the OP wants to be able to write >>> functions that target temporary tables. These functions all assume that >>> sa

Re: [GENERAL] Means to emulate global temporary table

On Wed, Jan 11, 2017 at 4:38 PM, Adrian Klaver wrote: So what makes them temporary as they seem to persist between sessions? They are temporary in the sense that the content of the table is per-session, just as a local temporary table would be. That is, each session has its own independent data

Re: [GENERAL] Means to emulate global temporary table

> > On Wed, Jan 11, 2017 at 4:54 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> > ​The way I read this is that the OP wants to be able to write functions >> that target temporary tables. These functions all assume that said tables >> already exist so the functions themselves do

[GENERAL] Question slow query

Hi guys, I've got a slow query, running at 25 seconds. -> Bitmap Heap Scan on ja_notes r_1103088 (cost=234300.55..1254978.62 rows=553275 width=101) (actual time=1423.411..10572.549 rows=475646 loops=1) Recheck Cond: (n_type = ANY ('{note,

[GENERAL] Re: Are new connection/security features in order, given connection pooling?

I can relate to the original plea from my own exploration of this topic. Before I get into that, I will mention as an aside that to date we have found RLS to be really slow for web client authorization, given that we have to use session parameters to store web client context and there doesn't seem

Re: [GENERAL] Means to emulate global temporary table

On 01/11/2017 05:30 PM, Ian Lewis wrote: Ccing list On Wed, Jan 11, 2017 at 4:38 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: So what makes them temporary as they seem to persist between sessions? They are temporary in the sense that the content of the table is per-session,

Re: [GENERAL] temporarily disable autovacuum on a database or server ?

On Wed, Jan 11, 2017 at 6:19 PM, Melvin Davidson wrote: > >> > *Yes, you're right about ALTER SYSTER. Unfortunately, the op provided > neither PostgreSQL version or O/S, so we can't even be sure that is * > > *an option. That is why I stated "I cannot confirm".* > > ​Thought it was just a non-de

Re: [GENERAL] temporarily disable autovacuum on a database or server ?

On Wed, Jan 11, 2017 at 8:09 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Jan 11, 2017 at 5:50 PM, Melvin Davidson > wrote: > >> On Wed, Jan 11, 2017 at 7:26 PM, Jonathan Vanasco >> wrote: >> >>> >>> >> *I can't confirm this, but have you tried :* >> >> *SELECT set_config

Re: [GENERAL] temporarily disable autovacuum on a database or server ?

On Wed, Jan 11, 2017 at 5:50 PM, Melvin Davidson wrote: > On Wed, Jan 11, 2017 at 7:26 PM, Jonathan Vanasco > wrote: > >> >> > *I can't confirm this, but have you tried :* > > *SELECT set_config('autovacuum', 'off'', false);* > > *SELECT pg_reload_conf(); * > > *note: you must be a superuser for

Re: [GENERAL] ERROR: canceling statement due to statement timeout

2017-01-12 13:41 GMT+13:00 Adrian Klaver : > On 01/11/2017 04:31 PM, Patrick B wrote: > >> 2017-01-12 13:23 GMT+13:00 Adrian Klaver > >: >> >> On 01/11/2017 04:08 PM, Patrick B wrote: >> >> Hi guys, >> >> I'm using PostgreSQL 9.2 in two differe

Re: [GENERAL] ERROR: canceling statement due to statement timeout

On 01/11/2017 04:31 PM, Patrick B wrote: 2017-01-12 13:23 GMT+13:00 Adrian Klaver mailto:adrian.kla...@aklaver.com>>: It is not the same DB if it is on two different servers not connected by replication. More to the point statement_timeout is a client connection setting, so is th

Re: [GENERAL] Means to emulate global temporary table

On Wed, Jan 11, 2017 at 5:38 PM, Adrian Klaver wrote: > Because the tables are known, many different functions can access the >> same tables during a session to manipulate the result set. And, because >> the tables are global the client can see the results easily based on the >> then-current tabl

Re: [GENERAL] temporarily disable autovacuum on a database or server ?

On Wed, Jan 11, 2017 at 7:26 PM, Jonathan Vanasco wrote: > I've run into a performance issue, and I think autovacuum may be involved. > > does anyone know if its possible to temporarily stop autovacuum without a > server restart ? > > It seems that it either requires a server restart, or specific

Re: [GENERAL] ERROR: canceling statement due to statement timeout

On 01/11/2017 04:31 PM, Patrick B wrote: 2017-01-12 13:23 GMT+13:00 Adrian Klaver mailto:adrian.kla...@aklaver.com>>: On 01/11/2017 04:08 PM, Patrick B wrote: Hi guys, I'm using PostgreSQL 9.2 in two different servers. server1 (Master Postgres DB server, running Po

Re: [GENERAL] Means to emulate global temporary table

On 01/11/2017 02:07 PM, Ian Lewis wrote: I am working on porting from an SQL Anywhere server that has support for general temporary tables. It appears that PostgreSQL does not have such support. We use global temporary tables as a means to provide session-local content to clients based on calcul

Re: [GENERAL] ERROR: canceling statement due to statement timeout

2017-01-12 13:23 GMT+13:00 Adrian Klaver : > On 01/11/2017 04:08 PM, Patrick B wrote: > >> Hi guys, >> >> I'm using PostgreSQL 9.2 in two different servers. >> >> server1 (Master Postgres DB server, running Postgres 9.2 / 128GB ram) - >> RAID 10 Magnetic disks >> server2 (Master Postgres DB server

[GENERAL] temporarily disable autovacuum on a database or server ?

I've run into a performance issue, and I think autovacuum may be involved. does anyone know if its possible to temporarily stop autovacuum without a server restart ? It seems that it either requires a server restart, or specific tables to be configured. Several times a day/week, I run a handfu

Re: [GENERAL] ERROR: canceling statement due to statement timeout

On 01/11/2017 04:08 PM, Patrick B wrote: Hi guys, I'm using PostgreSQL 9.2 in two different servers. server1 (Master Postgres DB server, running Postgres 9.2 / 128GB ram) - RAID 10 Magnetic disks server2 (Master Postgres DB server, running Postgres 9.2 / 128GB ram) - EBS (AWS) io2 10k IOPS Whe

[GENERAL] ERROR: canceling statement due to statement timeout

Hi guys, I'm using PostgreSQL 9.2 in two different servers. server1 (Master Postgres DB server, running Postgres 9.2 / 128GB ram) - RAID 10 Magnetic disks server2 (Master Postgres DB server, running Postgres 9.2 / 128GB ram) - EBS (AWS) io2 10k IOPS When I run a query, I get this error: ERROR:

Re: [GENERAL] Slow index scan - Pgsql 9.2

2017-01-11 4:05 GMT+13:00 Tomas Vondra : > On 01/10/2017 04:05 AM, Patrick B wrote: > >> ​3,581​ individual pokes into the heap to confirm tuple visibility >> and apply the deleted filter - that could indeed take a while. >> David J. >> >> >> I see.. The deleted column is: >> >> delete

Re: [GENERAL] Database of articles, LaTeX code and pictures

On 1/11/2017 2:47 PM, philolilou wrote: Hello everyone, i wish to build a place where to store articles (with picture) and that can be accessed easily later by researching. Articles i wish to store, are actually articles of magazine, or some internet interesting articles. For the articles

Re: [GENERAL] Means to emulate global temporary table

On 1/11/2017 2:07 PM, Ian Lewis wrote: I am working on porting from an SQL Anywhere server that has support for general temporary tables. It appears that PostgreSQL does not have such support. postgres temporary tables are either local to a transaction, or to a connection/session, and are aut

[GENERAL] Means to emulate global temporary table

I am working on porting from an SQL Anywhere server that has support for general temporary tables. It appears that PostgreSQL does not have such support. We use global temporary tables as a means to provide session-local content to clients based on calculations run on the server. That is, the cli

[GENERAL] Database of articles, LaTeX code and pictures

Hello everyone, i wish to build a place where to store articles (with picture) and that can be accessed easily later by researching. Articles i wish to store, are actually articles of magazine, or some internet interesting articles. For the articles of magazines, i thought scan all interest

Re: [GENERAL] Are new connection/security features in order, given connection pooling?

On 01/11/2017 03:10 PM, Stephen Frost wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: > > ... If you end up having to dedicate each connection to a particular unprivileged userID, then you can just open the connection as that user to start with; a magic one-way privilege switch doesn't really h

Re: [GENERAL] Are new connection/security features in order, given connection pooling?

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Guyren Howe writes: > > I’m not following. What I would like is just a lightweight way to switch > > the connections to use a different role, or some moral equivalent, that > > would prevent an SQL injection from wrecking havoc. I’m not proposing > > any

Re: [GENERAL] Are new connection/security features in order, given connection pooling?

Guyren Howe writes: > I’m not following. What I would like is just a lightweight way to switch the > connections to use a different role, or some moral equivalent, that would > prevent an SQL injection from wrecking havoc. I’m not proposing anything that > will change anything else about how th

Re: [GENERAL] requested timeline doesn't contain minimum recovery point

> > > > I mean, could random bytes appear as a valid record (very unlikely, but > > possible)? > > Yes, that could be possible if some memory or disk is broken. That's > why, while it is important to take backups, it is more important to > make sure that they are able to restore correctly before de

[GENERAL] How to identify Primary key column during build stage of Custom index?

Hello all, Basically I am new to postgresql, and I am trying to build a custom Index to postgres using IndexAmRoutine. Further details on what I am working is below. amapi.h - API for Postgres index access methods. IndexBuildResult *Indexbuild(Relation heap, Relation index, IndexInfo

Re: [GENERAL] Are new connection/security features in order, given connection pooling?

On 1/11/2017 12:49 AM, Guyren Howe wrote: would prevent an SQL injection from wrecking havoc. sql injection is quite easy to avoid. always make parameterized queries, never use client supplied data to assemble a query with string concatenation. voila, no injection possible. -- john r pie

Re: [GENERAL] Are new connection/security features in order, given connection pooling?

On Jan 11, 2017, at 0:07 , John R Pierce wrote: > > On 1/10/2017 11:32 PM, Guyren Howe wrote: >> Further to my recent inquiries about leveraging Postgres’ security features >> from client apps, it appears to me that some likely reasonably simple >> changes to those features would enable client

Re: [GENERAL] Are new connection/security features in order, given connection pooling?

On 1/10/2017 11:32 PM, Guyren Howe wrote: Further to my recent inquiries about leveraging Postgres’ security features from client apps, it appears to me that some likely reasonably simple changes to those features would enable client apps to better leverage what are otherwise great features.