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

[Guyren Howe]

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 and we were to connect *as them*, 
then we have a brilliant range of authorization features available, what with 
the lovely role inheritance and the row security features.

*BUT* because of practical considerations having to do with connection pooling, 
no-one opens a different connection for each request. And there appears to be 
no other simple way to use the row security.

This seems a bit tragic. I would love to have some way of, say, opening a 
connection without a default role, and having to supply a role name and token 
with each actual request.

Or perhaps there is some other solution along those lines.

In any event, if there is some simple way of solving the same problem with the 
current architecture, I’d love to hear it.

Alexander pointed me at 
http://blog.2ndquadrant.com/application-users-vs-row-level-security/ 
 but that 
is a level of complexity that scares me off, particularly for a security 
feature. At the very least, is there a simple and well-tested library I could 
load up, rather than rolling my own security feature?

If there *isn’t* a simple way to use PG’s authorization features for the likes 
of a web app, is a feature request in order?

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

[Michael Paquier]

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
> that it's normal.
> But I'm wondering if the system, in order to detect the end of the WAL,
> controls only the validity of the records in the WAL.

You may want to look at xlogreader.c and track report_invalid_record()
to see what are the error checks being done. No full checks are done
depending on the record types, but there are some checks for the
backup blocks, the size record, etc.

> 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 deploying
them.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Israel Brewster]

> 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 [...]
> 
>> Limit  (cost=354643835.82..354643835.83 rows=1 width=9) (actual
>> time=9636.165..9636.166 rows=1 loops=1)
> 
>> So from four minutes on the first run to around 9 1/2 seconds on the second.
>> Presumably this difference is due to caching?
> 
> It is likely to be, at least in part.  Did you run VACUUM on the
> data before the first run?  If not, hint bits may be another part
> of it.  The first access to each page after the bulk load would
> require some extra work for visibility checking and would cause a
> page rewrite for the hint bits.

That could be - I had planned to run a VACUUM ANALYZE after creating the 
indexes, but forgot. By the time I got around to running the second query, 
autovacuum should have kicked in and done it for me.

> 
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Table Design for Many Updates

[Craig Boucher]

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,

Craig

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: Tuesday, January 10, 2017 1:48 PM
To: pgsql-general@postgresql.org
Subject: 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 
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 a thousand 
devices (this number will increase over time) on the Internet that will insert 
a row into this table and then proceed to update two columns in that row about 
once a minute for the next two hours.  The two columns are NOT NULL and are not 
FK or indexed columns.  I've thought about moving them to a one-to-one related 
table.  Any thoughts on if this is a wise move or if I'm making a mountain out 
of a mole hill?  It looks like this scenario would be covered by the 
Heap-Only-Tuple update but with over a hundred updates to the same row and over 
a thousand different rows being updated at a time, will I reap the benefits?

 


I do hope those 1000-and-growing devices aren't directly connecting to sql, but 
are instead talking to some sort of app service which queues up requests to a 
sane number of database worker processes where said worker process count can be 
tuned for to balance throughput and response ?





​ With a reasonable fill-factor on the table you probably would be OK - but I'm 
partial to separating out the static and dynamic data into separate tables if 
the rest of the model and intended applications support it.  The main concern 
is how many queries do you have with a WHERE clause that includes fields from 
both sets?  Cross-table statistical estimates are problematic​ but if you don't 
have to be concerned about them it would be conceptually cleaner to setup a 
one-to-one here.

 

if these two updated fields are not indexed, its unlikely they're being used in 
WHERE clauses

the real question with HOT is if the table can be vacuumed frequently enough so 
the tuples can continue to be updated in the same block.

 

-- 
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Table Design for Many Updates

[Craig Boucher]

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: [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 
key, five foreign keys, and eight indexes.  We have a little over a thousand 
devices (this number will increase over time) on the Internet that will insert 
a row into this table and then proceed to update two columns in that row about 
once a minute for the next two hours.  The two columns are NOT NULL and are not 
FK or indexed columns.  I've thought about moving them to a one-to-one related 
table.  Any thoughts on if this is a wise move or if I'm making a mountain out 
of a mole hill?  It looks like this scenario would be covered by the 
Heap-Only-Tuple update but with over a hundred updates to the same row and over 
a thousand different rows being updated at a time, will I reap the benefits?

 

 

​With a reasonable fill-factor on the table you probably would be OK - but I'm 
partial to separating out the static and dynamic data into separate tables if 
the rest of the model and intended applications support it.  The main concern 
is how many queries do you have with a WHERE clause that includes fields from 
both sets?  Cross-table statistical estimates are problematic​ but if you don't 
have to be concerned about them it would be conceptually cleaner to setup a 
one-to-one here.

 

David J.

Re: [GENERAL] Table Design for Many Updates

[John R Pierce]

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 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 a thousand devices
(this number will increase over time) on the Internet that will
insert a row into this table and then proceed to update two
columns in that row about once a minute for the next two hours. 
The two columns are NOT NULL and are not FK or indexed columns. 
I've thought about moving them to a one-to-one related table.  Any

thoughts on if this is a wise move or if I'm making a mountain out
of a mole hill?  It looks like this scenario would be covered by
the Heap-Only-Tuple update but with over a hundred updates to the
same row and over a thousand different rows being updated at a
time, will I reap the benefits?




I do hope those 1000-and-growing devices aren't directly connecting to 
sql, but are instead talking to some sort of app service which queues up 
requests to a sane number of database worker processes where said worker 
process count can be tuned for to balance throughput and response ?



​ With a reasonable fill-factor on the table you probably would be OK 
- but I'm partial to separating out the static and dynamic data into 
separate tables if the rest of the model and intended applications 
support it.  The main concern is how many queries do you have with a 
WHERE clause that includes fields from both sets?  Cross-table 
statistical estimates are problematic​ but if you don't have to be 
concerned about them it would be conceptually cleaner to setup a 
one-to-one here.


if these two updated fields are not indexed, its unlikely they're being 
used in WHERE clauses


the real question with HOT is if the table can be vacuumed frequently 
enough so the tuples can continue to be updated in the same block.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Table Design for Many Updates

[David G. Johnston]

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 key, five foreign keys, and eight indexes.  We have a
> little over a thousand devices (this number will increase over time) on the
> Internet that will insert a row into this table and then proceed to update
> two columns in that row about once a minute for the next two hours.  The
> two columns are NOT NULL and are not FK or indexed columns.  I've thought
> about moving them to a one-to-one related table.  Any thoughts on if this
> is a wise move or if I'm making a mountain out of a mole hill?  It looks
> like this scenario would be covered by the Heap-Only-Tuple update but with
> over a hundred updates to the same row and over a thousand different rows
> being updated at a time, will I reap the benefits?
>
>
>
​With a reasonable fill-factor on the table you probably would be OK - but
I'm partial to separating out the static and dynamic data into separate
tables if the rest of the model and intended applications support it.  The
main concern is how many queries do you have with a WHERE clause that
includes fields from both sets?  Cross-table statistical estimates are
problematic​ but if you don't have to be concerned about them it would be
conceptually cleaner to setup a one-to-one here.

David J.

[GENERAL] Table Design for Many Updates

[Craig Boucher]

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 a thousand devices (this number will increase over time) on the
Internet that will insert a row into this table and then proceed to update
two columns in that row about once a minute for the next two hours.  The two
columns are NOT NULL and are not FK or indexed columns.  I've thought about
moving them to a one-to-one related table.  Any thoughts on if this is a
wise move or if I'm making a mountain out of a mole hill?  It looks like
this scenario would be covered by the Heap-Only-Tuple update but with over a
hundred updates to the same row and over a thousand different rows being
updated at a time, will I reap the benefits?

 

Thanks,

Craig

Re: [GENERAL] Materialized view vs. view

[Nicolas Paris]

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 will 
have to fetch those 500K rows before - in any case this is quite slow.
However, 500K rows do not represent a huge physical space to
materialize. But do not forget to index/refresh the materialized views,
depending on the select set of queries to run.

Answer would have been different with 50K rows I guess.

> 
>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, IMO, unanswerable (or at least, if
>answered, requires making assumptions that tend to go unstated).
> 
> is just an opinion and too late to change at this point?
> 
>Your opinion, or the name? :)
>​David J.​
> 
> References
> 
>1. mailto:melvin6...@gmail.com


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Materialized view vs. view

[Kevin Grittner]

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 opinions about what words mean can lead to confusion.  Left
alone, what you said might have confused some readers about what
"materialized" means.  "Materialized view" has been a term of art,
part of database jargon, for over 30 years.

https://en.wikipedia.org/wiki/Jargon :

"A main driving force in the creation of technical jargon is
precision and efficiency of communication when a discussion must
easily range from general themes to specific, finely differentiated
details without circumlocution."

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [GENERAL] Materialized view vs. view

[David G. Johnston]

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, IMO, unanswerable (or at least, if answered, requires
making assumptions that tend to go unstated).

 is just an opinion and too late to change at this point?
>
>
Your opinion, or the name? :)

​David J.​

Re: [GENERAL] Materialized view vs. view

[Rob Sargent]

> 
> 
> "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 to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Materialized view vs. view

[Melvin Davidson]

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".  In the database environment, it
> generally means that the data is actually stored, rather than being
> something which can be generated.  For example, in query execution
> the relation produced by an execution node may feed into a
> Materialize node if the generated relation is expected to be
> scanned multiple times by a higher-level node and scanning a stored
> copy of the relation each time is expected to be faster than
> regenerating the relation each time.  "Materialized table" would be
> redundant; a table is always materialized.  A view is data
> generated by running a query. In the simple case, the resulting
> relation is not stored, but is regenerated on each reference.  The
> "materialized view" feature lets you materialize it, like a table.
>
> If you don't think materializing data means storing a copy of it
> for re-use, I'm not sure what you think it means.
>
> This is not to beat up on you, but to try to keep terminology
> clear, to facilitate efficient communication.  There are some terms
> we have been unable to avoid using with different meanings in
> different contexts (e.g., "serialization"); that's unfortunate, but
> hard to avoid.  I want to keep it to the minimum necessary by
> avoiding creep of other terms to multiple definitions.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
"A rose by any other name would still smell as sweet".
I have expressed my opinion. We are getting off the point of the topic
which is "What is faster, a View or a Materialized View".
Can we all agree that the "Materialized View" should be faster 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?

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Materialized view vs. view

[Kevin Grittner]

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 actually stored, rather than being
something which can be generated.  For example, in query execution
the relation produced by an execution node may feed into a
Materialize node if the generated relation is expected to be
scanned multiple times by a higher-level node and scanning a stored
copy of the relation each time is expected to be faster than
regenerating the relation each time.  "Materialized table" would be
redundant; a table is always materialized.  A view is data
generated by running a query. In the simple case, the resulting
relation is not stored, but is regenerated on each reference.  The
"materialized view" feature lets you materialize it, like a table.

If you don't think materializing data means storing a copy of it
for re-use, I'm not sure what you think it means.

This is not to beat up on you, but to try to keep terminology
clear, to facilitate efficient communication.  There are some terms
we have been unable to avoid using with different meanings in
different contexts (e.g., "serialization"); that's unfortunate, but
hard to avoid.  I want to keep it to the minimum necessary by
avoiding creep of other terms to multiple definitions.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: [GENERAL] Materialized view vs. view

[David G. Johnston]

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 would be "materialized table". *
> *However, it is too late to change that now. Just my personal opinion.*
>

​Sounds redundant - and implies that a TABLE without the materialized
prefix isn't, which is not true.

The only other name I came up with was worse...CREATE VIEWTABLE​ AS

David J.

Re: [GENERAL] Materialized view vs. view

[Melvin Davidson]

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,
>
> 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.  I don't think I can
> say it better than others already have, so I recommend reading the
> first three paragraphs of the "Introduction" section of this paper:
>
> http://homepages.inf.ed.ac.uk/wenfei/qsx/reading/gupta95maintenance.pdf
> Ashish Gupta and Inderpal Singh Mumick.
> Maintenance of Materialized Views: Problems, Techniques, and Applications.
>
> > so theoretically you should be able to reduce response time on
> > them.
>
> As the above-referenced text suggests, a materialized view is
> essentially a cache of the results of the specified query.  While,
> in rare cases, this may be captured to provide the query results as
> of some particular moment in time, the overwhelming reason for
> creating a materialized view is to improve performance over a
> non-materialized view.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
>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 would be "materialized table". *
*However, it is too late to change that now. Just my personal opinion.*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Materialized view vs. view

[Kevin Grittner]

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
query (like a view) and materializing the results (like a table)
makes "materialized view" a misleading name.  I don't think I can
say it better than others already have, so I recommend reading the
first three paragraphs of the "Introduction" section of this paper:

http://homepages.inf.ed.ac.uk/wenfei/qsx/reading/gupta95maintenance.pdf
Ashish Gupta and Inderpal Singh Mumick.
Maintenance of Materialized Views: Problems, Techniques, and Applications.

> so theoretically you should be able to reduce response time on
> them.

As the above-referenced text suggests, a materialized view is
essentially a cache of the results of the specified query.  While,
in rare cases, this may be captured to provide the query results as
of some particular moment in time, the overwhelming reason for
creating a materialized view is to improve performance over a
non-materialized view.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

[Vick Khera]

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

[Melvin Davidson]

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 for "select" queries, which one is faster and less expensive as
>> resources cost?
>> The view or the materialized view?
>>
>
> If are running tests as you state above, you should know or am I missing
> something?
>
>
>
>> The view has about 500K lines.
>>
>> Thank you!
>> /F
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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,
so theoretically you should be able to reduce response time on them.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Materialized view vs. view

[David G. Johnston]

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 expensive as
> resources cost?
> The view or the materialized view?
>
> The view has about 500K lines.
>
>
There is no simple answer to this - it all depends upon your actual usage.
With proper indexes and a disregard for the "REFRESH MATERIALIZED VIEW ..."
command cost the materialized view should always perform better.  But most
people don't get to disregard the refresh dynamic imposed by materialized
views.

David J.

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

[Melvin Davidson]

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?  if
>>> restart postgresql will have problems at recovery?
>>>
>>> On Tue, Jan 10, 2017 at 12:02 PM, Melvin Davidson 
>>> wrote:
>>>


 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(60858) idle
>
> postgres 19649  2467  0 11:46 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(60859) idle
>
> postgres 19653  2467  0 11:46 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(60863) idle
>
> postgres 19656  2467  0 11:46 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(60866) idle
>
> postgres 19657  2467  0 11:46 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(60867) idle
>
> postgres 20253  2467  0 11:46 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(32961) idle
>
> postgres 21280  2467  0 11:47 ?00:00:00 postgres: argos_admin
> vacia ::1(33804) idle
>
> postgres 21692  2467  0 11:48 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(33840) idle
>
> postgres 21817  2467  0 11:48 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(33948) idle
>
> postgres 21819  2467  0 11:48 ?00:00:01 postgres: argos_admin
> vacia 127.0.0.1(33949) idle
>
> postgres 21823  2467  0 11:48 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(33953) idle
>
> postgres 21825  2467  0 11:48 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(33955) idle
>
> postgres 21844  2467  0 11:48 ?00:00:01 postgres: argos_admin
> vacia 127.0.0.1(33974) idle
>
> postgres 22576  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia ::1(34604) idle
>
> postgres 22713  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia ::1(34673) idle
>
> postgres 22811  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia ::1(34718) SELECT waiting
>
> postgres 22825  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34512) idle
>
> postgres 22828  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34515) idle
>
> postgres 22903  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34578) idle
>
> postgres 22925  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34600) idle
>
> postgres 22926  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34601) idle
>
> postgres 22928  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34604) idle
>
> postgres 22929  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34605) idle
>
> postgres 22939  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34614) idle
>
> postgres 23510  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34953) idle
>
> postgres 23511  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34954) idle
>
> postgres 23512  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34955) idle
>
> postgres 23516  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34957) idle
>
> postgres 23520  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34959) idle
>
> postgres 23549  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34980) SELECT waiting
>
> postgres 23584  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(35002) idle
>
> postgres 23612  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(35006) idle
>
> postgres 23623  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia ::1(35227) idle
>
> postgres 23638  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia ::1(35231) idle
>
> postgres 23644  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(35017) idle
>
> postgres 23658  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(35025) idle
>
> postgres 23663  2467  0 11:50 ?00:00:00 

Re: [GENERAL] Materialized view vs. view

[Adrian Klaver]

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?
The view or the materialized view?


If are running tests as you state above, you should know or am I missing 
something?




The view has about 500K lines.

Thank you!
/F



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Materialized view vs. view

[Job]

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?

The view has about 500K lines.

Thank you!
/F

Re: [GENERAL] some amazing stuff

[Alvaro Herrera]

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 & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Melvin Davidson]

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(60858) idle
>
> postgres 19649  2467  0 11:46 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(60859) idle
>
> postgres 19653  2467  0 11:46 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(60863) idle
>
> postgres 19656  2467  0 11:46 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(60866) idle
>
> postgres 19657  2467  0 11:46 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(60867) idle
>
> postgres 20253  2467  0 11:46 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(32961) idle
>
> postgres 21280  2467  0 11:47 ?00:00:00 postgres: argos_admin
> vacia ::1(33804) idle
>
> postgres 21692  2467  0 11:48 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(33840) idle
>
> postgres 21817  2467  0 11:48 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(33948) idle
>
> postgres 21819  2467  0 11:48 ?00:00:01 postgres: argos_admin
> vacia 127.0.0.1(33949) idle
>
> postgres 21823  2467  0 11:48 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(33953) idle
>
> postgres 21825  2467  0 11:48 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(33955) idle
>
> postgres 21844  2467  0 11:48 ?00:00:01 postgres: argos_admin
> vacia 127.0.0.1(33974) idle
>
> postgres 22576  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia ::1(34604) idle
>
> postgres 22713  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia ::1(34673) idle
>
> postgres 22811  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia ::1(34718) SELECT waiting
>
> postgres 22825  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34512) idle
>
> postgres 22828  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34515) idle
>
> postgres 22903  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34578) idle
>
> postgres 22925  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34600) idle
>
> postgres 22926  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34601) idle
>
> postgres 22928  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34604) idle
>
> postgres 22929  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34605) idle
>
> postgres 22939  2467  0 11:49 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34614) idle
>
> postgres 23510  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34953) idle
>
> postgres 23511  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34954) idle
>
> postgres 23512  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34955) idle
>
> postgres 23516  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34957) idle
>
> postgres 23520  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34959) idle
>
> postgres 23549  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(34980) SELECT waiting
>
> postgres 23584  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(35002) idle
>
> postgres 23612  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(35006) idle
>
> postgres 23623  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia ::1(35227) idle
>
> postgres 23638  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia ::1(35231) idle
>
> postgres 23644  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(35017) idle
>
> postgres 23658  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(35025) idle
>
> postgres 23663  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia ::1(35244) SELECT waiting
>
> postgres 23674  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia ::1(35249) idle
>
> postgres 23685  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia ::1(35254) idle
>
> postgres 23726  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(35056) idle
>
> postgres 23731  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia ::1(35275) idle
>
> postgres 23734  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(35060) SELECT waiting
>
> postgres 23736  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia ::1(35280) idle
>
> postgres 23747  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(35067) idle
>
> postgres 23756  2467  0 11:50 ?00:00:00 postgres: argos_admin
> vacia 127.0.0.1(35071) SELECT waiting
>
> postgres 23773  2467  0 11:51 ?00:00:00 postgres: argos_admin
> vacia ::1(35296) SELECT waiting
>

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

[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(60858) idle

postgres 19649  2467  0 11:46 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(60859) idle

postgres 19653  2467  0 11:46 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(60863) idle

postgres 19656  2467  0 11:46 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(60866) idle

postgres 19657  2467  0 11:46 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(60867) idle

postgres 20253  2467  0 11:46 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(32961) idle

postgres 21280  2467  0 11:47 ?00:00:00 postgres: argos_admin vacia
::1(33804) idle

postgres 21692  2467  0 11:48 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(33840) idle

postgres 21817  2467  0 11:48 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(33948) idle

postgres 21819  2467  0 11:48 ?00:00:01 postgres: argos_admin vacia
127.0.0.1(33949) idle

postgres 21823  2467  0 11:48 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(33953) idle

postgres 21825  2467  0 11:48 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(33955) idle

postgres 21844  2467  0 11:48 ?00:00:01 postgres: argos_admin vacia
127.0.0.1(33974) idle

postgres 22576  2467  0 11:49 ?00:00:00 postgres: argos_admin vacia
::1(34604) idle

postgres 22713  2467  0 11:49 ?00:00:00 postgres: argos_admin vacia
::1(34673) idle

postgres 22811  2467  0 11:49 ?00:00:00 postgres: argos_admin vacia
::1(34718) SELECT waiting

postgres 22825  2467  0 11:49 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34512) idle

postgres 22828  2467  0 11:49 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34515) idle

postgres 22903  2467  0 11:49 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34578) idle

postgres 22925  2467  0 11:49 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34600) idle

postgres 22926  2467  0 11:49 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34601) idle

postgres 22928  2467  0 11:49 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34604) idle

postgres 22929  2467  0 11:49 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34605) idle

postgres 22939  2467  0 11:49 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34614) idle

postgres 23510  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34953) idle

postgres 23511  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34954) idle

postgres 23512  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34955) idle

postgres 23516  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34957) idle

postgres 23520  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34959) idle

postgres 23549  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(34980) SELECT waiting

postgres 23584  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(35002) idle

postgres 23612  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(35006) idle

postgres 23623  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
::1(35227) idle

postgres 23638  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
::1(35231) idle

postgres 23644  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(35017) idle

postgres 23658  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(35025) idle

postgres 23663  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
::1(35244) SELECT waiting

postgres 23674  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
::1(35249) idle

postgres 23685  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
::1(35254) idle

postgres 23726  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(35056) idle

postgres 23731  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
::1(35275) idle

postgres 23734  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(35060) SELECT waiting

postgres 23736  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
::1(35280) idle

postgres 23747  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(35067) idle

postgres 23756  2467  0 11:50 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(35071) SELECT waiting

postgres 23773  2467  0 11:51 ?00:00:00 postgres: argos_admin vacia
::1(35296) SELECT waiting

postgres 23788  2467  0 11:51 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(35086) idle

postgres 23794  2467  0 11:51 ?00:00:00 postgres: argos_admin vacia
127.0.0.1(35090) SELECT waiting

postgres 24298  2467  0 11:51 ?00:00:00 postgres: argos_admin vacia
::1(35567) SELECT 

[GENERAL] i got a process holding the lock

[Edmundo Robles]

how can i  detect and cancel the process??


--

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

[Alexander M. Sauer-Budge]

> 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 developer might use Postgres’ roles and 
> row-level security to implement their authorization.
> 
> What I’m struggling with is that connection pooling seems to make 
> straightforward use of the roles to enforce access impossible.
> 
> If I’m using a connection pool, then I’m not re-connecting to Postgres with 
> the user for the current transaction. But then my only option is to use SET 
> ROLE. But that is not much security at all, because the current user can just 
> do SET ROLE back to the (presumably privileged) default, or to any other 
> user’s role.
> 
> What am I missing here?


Tomas at 2nd Quadrant wrote a nice article about doing that:

http://blog.2ndquadrant.com/application-users-vs-row-level-security/ 


You can also look at how projects like PostgREST (http://postgrest.com/ 
) and PostGaphQL 
(https://github.com/calebmer/postgraphql 
) tackle the problem (although I don’t 
recall at the moment if they are as careful about avoiding the possibility of 
an unprotected SET ROLE as Tomas is in the above article).

Best,
Alex

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

[George Neuner]

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 developer might use Postgres’
>roles and row-level security to implement their authorization.
>
>What I’m struggling with is that connection pooling seems to make
>straightforward use of the roles to enforce access impossible.
>
>If I’m using a connection pool, then I’m not re-connecting to 
>Postgres with the user for the current transaction. But then my
>only option is to use SET ROLE. But that is not much security at
>all, because the current user can just do SET ROLE back to the 
>(presumably privileged) default, or to any other user’s role.
>
>What am I missing here?

That middleware can control what a user is permitted to do.  

YMMV, but to me "web application" means there is a server-side program
sitting in front of the database and controlling access to it.  

I grudgingly will permit *compiled* clients direct connection to an
Internet facing database, but I am dead set against allowing direct
connection from any browser hosted code because - regardless of any
"shrouding" that might be done - browser code is completely insecure,
accessible to anyone who can right-click on the page.

George



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Slow index scan - Pgsql 9.2

[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:

deleted boolean

Should I create an index for that? How could I improve this query?


Does it execute as slowly when you run it for a 2nd time?


No, it doesn't. I think it's because of cache?


I would think because of the NOT "deleted" clause. Which is
interesting, because that's a column which you conveniently didn't
include in the definition below.


My mistake.


Would an Index be sufficient to solve the problem?



Not a separate index - the query probably would not benefit from two 
separate indexes. But you can amend the existing index, to allow 
index-only scans, i.e. creating an index like this:


  CREATE INDEX ON (clientid, is_demo, deleted, id, job, job_share_mode)

This will make the index larger, but it should allow index-only scans.

The other thing you could try is partial index, i.e.

  CREATE INDEX ON (clientid) WHERE NOT is_demo AND NOT deleted;

You can also combine those approaches, but you'll have to include all 
columns into the index, even those in the index predicate:


  CREATE INDEX ON (clientid, is_demo, deleted, id, job, job_share_mode)
  WHERE NOT is_demo AND NOT deleted;

I'd bet all of those will outperform the current plan.

regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom DalPozzo]

>
> 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: you are using pg_rewing with a source node that is
>> running. You should issue a checkpoint manually after promoting the
>> node to be sure that its control file gets the new timeline number.
>> --
>> Michael
>>
> Hi,
>
> sometimes pg_rewind says that nothing needs to be done, sometimes it says
> it's rewinding and done at the end.
> I'm using 9.6. I moved there from 9.5 as I'm also using replication slots
> and in 9.6 there is a second parameter added. But I seem to remember that
> it did the same in 9.5 too but I'm not really sure.
> I checked that the server, at promotion said the message about the new
> timeline.
> I will make some more tests.
> Regards
> Pupillo
>

Hi!
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
that it's normal.
But I'm wondering if the system, in order to detect the end of the WAL,
controls only the validity of the records in the WAL.
I mean, could random bytes appear as a valid record (very unlikely, but
possible)?
Thanks
Pupillo

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

[Alexander M. Sauer-Budge]

> 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 developer might use Postgres’ roles and 
> row-level security to implement their authorization.
> 
> What I’m struggling with is that connection pooling seems to make 
> straightforward use of the roles to enforce access impossible.
> 
> If I’m using a connection pool, then I’m not re-connecting to Postgres with 
> the user for the current transaction. But then my only option is to use SET 
> ROLE. But that is not much security at all, because the current user can just 
> do SET ROLE back to the (presumably privileged) default, or to any other 
> user’s role.
> 
> What am I missing here?


Tomas at 2nd Quadrant wrote a nice article about doing that:

http://blog.2ndquadrant.com/application-users-vs-row-level-security/ 


You can also look at how projects like PostgREST (http://postgrest.com/ 
) and PostGaphQL 
(https://github.com/calebmer/postgraphql 
) tackle the problem (although I don’t 
recall at the moment if they are as careful about avoiding the possibility of 
an unprotected SET ROLE as Tomas is in the above article).

Best,
Alex

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

[John R Pierce]

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 via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general