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

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. *IF* we give our end users roles in Postgres

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

On Tue, Jan 10, 2017 at 10:35 PM, Tom DalPozzo wrote: > I redid the tests following your suggestion to issue a checkpoint manually. > IT WORKS! > Just a question: when the standby server starts, I see the log error > messages (ex.: "invalid record length...") when WAL end is reached. I know > tha

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

> On Jan 9, 2017, at 1:54 PM, Kevin Grittner wrote: > > On Mon, Jan 9, 2017 at 11:49 AM, Israel Brewster > wrote: > >> [load of new data] > >> Limit (cost=354643835.82..354643835.83 rows=1 width=9) (actual >> time=225998.319..225998.320 rows=1 loops=1) > >> [...] I ran the query again [...

Re: [GENERAL] Table Design for Many Updates

No, they aren’t connecting directly to the database. They go through a web api. The data is queued on the device and uploaded in the background. So a little latency isn’t a problem. I’ve looked at Amazon’s SQS but I don’t like the idea of being tied to a specific hosting vendor. Thanks,

Re: [GENERAL] Table Design for Many Updates

Thanks for the insights. I don’t think we have any where clauses that would filter on a column from the related table. Craig From: David G. Johnston [mailto:david.g.johns...@gmail.com] Sent: Tuesday, January 10, 2017 1:42 PM To: Craig Boucher Cc: pgsql-general@postgresql.org Subject: Re

Re: [GENERAL] Table Design for Many Updates

On 1/10/2017 1:42 PM, David G. Johnston wrote: On Tue, Jan 10, 2017 at 2:33 PM, Craig Boucher >wrote: I have a multi-tenant database that I'm migrating from SQL Server to PostgreSQL 9.6.1. I read the recent articles about the potential write amplification is

Re: [GENERAL] Table Design for Many Updates

On Tue, Jan 10, 2017 at 2:33 PM, Craig Boucher wrote: > I have a multi-tenant database that I'm migrating from SQL Server to > PostgreSQL 9.6.1. I read the recent articles about the potential write > amplification issue in Postgres. I have one particular table that has 14 > columns, a primary k

[GENERAL] Table Design for Many Updates

I have a multi-tenant database that I'm migrating from SQL Server to PostgreSQL 9.6.1. I read the recent articles about the potential write amplification issue in Postgres. I have one particular table that has 14 columns, a primary key, five foreign keys, and eight indexes. We have a little over

Re: [GENERAL] Materialized view vs. view

Le 10/33/2017 à 21:33, David G. Johnston écrivait : >On Tue, Jan 10, 2017 at 1:01 PM, Melvin Davidson ><[1]melvin6...@gmail.com> wrote: > >Can we all agree that the "Materialized View" should be faster >​ > Yes. The OP told about a 500K rows view. Every select queries on that view

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 2:01 PM, Melvin Davidson wrote: > Can we all agree that the "Materialized View" should be faster I think we have. > and stop this pointless bickering about naming convention, > which I have already stated, is just an opinion and too late to change at this point? Novel o

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 1:01 PM, Melvin Davidson wrote: > > Can we all agree that the "Materialized View" should be faster > ​ > > If you add in the condition that the same answer has to be returned whether you query the view or the mat-view then no, I wouldn't agree; and the original question is

Re: [GENERAL] Materialized view vs. view

> > > "A rose by any other name would still smell as sweet”. Actually there’s no “still” in that line, if you’re quoting Shakespeare. And the full “That which we call a rose …” is truly appropriate here. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 2:53 PM, Kevin Grittner wrote: > On Tue, Jan 10, 2017 at 1:36 PM, Melvin Davidson > wrote: > > > IMHO, I disagree. I feel a better name would be "materialized > > table". > > The dictionary defines "materialize" as meaning "become actual > fact" or "appear in bodily form"

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 1:36 PM, Melvin Davidson wrote: > IMHO, I disagree. I feel a better name would be "materialized > table". The dictionary defines "materialize" as meaning "become actual fact" or "appear in bodily form". In the database environment, it generally means that the data is act

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 12:36 PM, Melvin Davidson wrote: > >> > >I disagree with the notion that defining a relation in terms of a > >query (like a view) and materializing the results (like a table) > >makes "materialized view" a misleading name. > > > *IMHO, I disagree. I feel a better name woul

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 2:31 PM, Kevin Grittner wrote: > On Tue, Jan 10, 2017 at 12:44 PM, Melvin Davidson > wrote: > > > fyi, a view is nothing more than just that, a view. > > A materialized view, afaic, is a misleading name, it is actually > > a valid table and you can create indexes on them,

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 12:44 PM, Melvin Davidson wrote: > fyi, a view is nothing more than just that, a view. > A materialized view, afaic, is a misleading name, it is actually > a valid table and you can create indexes on them, I disagree with the notion that defining a relation in terms of a

Re: [GENERAL] i got a process holding the lock

On Tue, Jan 10, 2017 at 1:39 PM, Melvin Davidson wrote: > *Why not just kill the blocking process first? Then everything else will > proceed.* > That's what he said to do. You can do that with a `SELECT pg_cancel_backend($pid)` query.

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 1:36 PM, Adrian Klaver wrote: > On 01/10/2017 10:27 AM, Job wrote: > >> Hi guys, >> >> iam making some tests with a massive number of "select" queries (only >> for reading datas) on a view and a materialized view. >> We use Postgresql 9.6.1 on a 64bit server. >> >> Only fo

Re: [GENERAL] Materialized view vs. view

On Tue, Jan 10, 2017 at 11:27 AM, Job wrote: > Hi guys, > > i am making some tests with a massive number of "select" queries (only > for reading datas) on a view and a materialized view. > We use Postgresql 9.6.1 on a 64bit server. > > Only for "select" queries, which one is faster and less expen

Re: [GENERAL] i got a process holding the lock

On Tue, Jan 10, 2017 at 1:28 PM, Edmundo Robles wrote: > ok, then is better kill one by one! > > On Tue, Jan 10, 2017 at 12:16 PM, Melvin Davidson > wrote: > >> >> >> On Tue, Jan 10, 2017 at 1:07 PM, Edmundo Robles >> wrote: >> >>> I have a lot of parse waiting can i kill that process safely

Re: [GENERAL] Materialized view vs. view

On 01/10/2017 10:27 AM, Job wrote: Hi guys, iam making some tests with a massive number of "select" queries (only for reading datas) on a view and a materialized view. We use Postgresql 9.6.1 on a 64bit server. Only for "select" queries, which one is faster and less expensive as resources cost?

[GENERAL] Materialized view vs. view

Hi guys, i am making some tests with a massive number of "select" queries (only for reading datas) on a view and a materialized view. We use Postgresql 9.6.1 on a 64bit server. Only for "select" queries, which one is faster and less expensive as resources cost? The view or the materialized view

Re: [GENERAL] some amazing stuff

Edmundo Robles wrote: > Please, administrator mark this like spam. Yes, I removed the offending address on sight. We do not remove emails from the archive, though. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Servic

Re: [GENERAL] i got a process holding the lock

2017-01-10 12:58 GMT-05:00 Edmundo Robles : > whe i do a ps -fea | grep postgres this is the output > ostgres 11436 2467 0 11:41 ?00:00:01 postgres: argos_admin vacia > 127.0.0.1(54880) idle > > postgres 19648 2467 0 11:46 ?00:00:00 postgres: argos_admin > vacia 127.0.0.1(6085

Re: [GENERAL] i got a process holding the lock

whe i do a ps -fea | grep postgres this is the output ostgres 11436 2467 0 11:41 ?00:00:01 postgres: argos_admin vacia 127.0.0.1(54880) idle postgres 19648 2467 0 11:46 ?00:00:00 postgres: argos_admin vacia 127.0.0.1(60858) idle postgres 19649 2467 0 11:46 ?00:00:00

[GENERAL] i got a process holding the lock

how can i detect and cancel the process?? --

Re: [GENERAL] Not clear how to switch role without permitting switch back

> On Jan 10, 2017, at 2:05 AM, Guyren Howe wrote: > > For my Love Your Database Project: > > https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.8g1ezwx6r > > > I’m trying to see how a typical web de

Re: [GENERAL] Not clear how to switch role without permitting switch back

On Mon, 9 Jan 2017 23:05:47 -0800, Guyren Howe wrote: >For my Love Your Database Project: > >https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.8g1ezwx6r > > >I’m trying to see how a typical web develop

Re: [GENERAL] Slow index scan - Pgsql 9.2

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: deleted boolean Should I create an index for that? How could I improve

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

> > Could you give more details? What does pg_rewind tell you at each >> phase? Is that on Postgres 9.5 or 9.6? I use pg_rewind quite >> extensively on 9.5 but I have no problems of this time with multiple >> timeline jumps when juggling between two nodes. Another thing that is >> coming to my mind

Re: [GENERAL] Not clear how to switch role without permitting switch back

> On Jan 10, 2017, at 2:05 AM, Guyren Howe > wrote: > > For my Love Your Database Project: > > https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.8g1ezwx6r > > > I’m trying

Re: [GENERAL] Not clear how to switch role without permitting switch back

On 1/9/2017 11:05 PM, Guyren Howe wrote: I’m trying to see how a typical web developer might use Postgres’ roles and row-level security to implement their authorization. too much impedance mismatch with the stateless nature of http -- john r pierce, recycling bits in santa cruz -- Sent v