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

[Guyren Howe]

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?

Re: [GENERAL] Slow index scan - Pgsql 9.2

[David G. Johnston]

On Mon, Jan 9, 2017 at 8:05 PM, 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?
>

​Correct - your first execution swallows disk I/O​.


>
>
> 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?
>
>
​A lot would depend on the selectivity of "deleted"... you are tossing less
than 1/3rd of the rows here so probably not that helpful - though a partial
index over deleted = false would benefit this specific query.

If this query dominates your non-PK usage of this table you could consider
clustering the table on client_id - that would reduce the I/O hit at the
expense of increased maintenance.

Really, though, the query you are showing is poorly written - so I am
assuming that it is standing in for something else.  If that's so you have
provided zero context for meaningful opinions to be rendered.

Queries against the supposedly large customer table, on the
high-cardinality client_id field, which result in many matching rows is
going to be a physical problem if the data doesn't remain in cache.
CLUSTER can help a bit in that situation.  Otherwise your solutions are
more likely to by physical and not logical (model/SQL).

David J.

Re: [GENERAL] Slow index scan - Pgsql 9.2

[Patrick B]

>
> ​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?

Patrick

Re: [GENERAL] Slow index scan - Pgsql 9.2

[David G. Johnston]

On Mon, Jan 9, 2017 at 6:06 PM, Patrick B  wrote:

> *Explain Analyze:*
>
> CTE Scan on "query_p" "qp"  (cost=0.01..1060.57 rows=1 width=8) (actual
> time=4065.244..4065.246 rows=1 loops=1)
>
>   CTE query_p
>
> ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003
> rows=1 loops=1)
>
>   SubPlan 2
>
> ->  Aggregate  (cost=1060.53..1060.54 rows=1 width=0) (actual
> time=4065.229..4065.229 rows=1 loops=1)
>
>   ->  Index Scan using "clientid_customers" on "customers" "c"
>  (cost=0.00..1059.01 rows=607 width=0) (actual time=9.105..4063.728
> rows=2513 loops=1)
>
> Index Cond: ("clientid" = "qp"."client_id")
>
> Filter: (NOT "deleted")
>
> Rows Removed by Filter: 1068
>
> Total runtime: 4075.753 ms
>
>
>
> Why a search for "client_id" is so slow??
>
>
​3,581​ individual pokes into the heap to confirm tuple visibility and
apply the deleted filter - that could indeed take a while.

David J.

Re: [GENERAL] Slow index scan - Pgsql 9.2

[David Rowley]

On 10 January 2017 at 14:06, Patrick B  wrote:
>   ->  Index Scan using "clientid_customers" on "customers" "c"  
> (cost=0.00..1059.01 rows=607 width=0) (actual time=9.105..4063.728 rows=2513 
> loops=1)
> Index Cond: ("clientid" = "qp"."client_id")
> Filter: (NOT "deleted")
> Rows Removed by Filter: 1068
> Total runtime: 4075.753 ms
>
> Why a search for "client_id" is so slow??

EXPLAIN (ANALYZE, BUFFERS) might reveal something.

Perhaps each of the 2513 found rows, plus the 1068 filtered out rows
were spread over the table. Perhaps each on their own heap page, and
all those pages had to be read from disk. The BUFFERS option might
help show if this is the case.

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

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] Slow index scan - Pgsql 9.2

[Jan de Visser]

>
> Hi guys,
>
> I've got the following Query:
>
> WITH
>
>query_p AS (
>
>SELECT CAST(6667176 AS
> BIGINT) AS client_id),
>
>
>
>
>  clients AS (
>
>SELECT
>
>client.id
> ,client.job_share_mode
>
>FROM
>
>customers AS
> client
>
>WHERE
>
>
>  (client.clientid = (SELECT qp.client_id FROM query_p AS qp))
>
>AND
>
>NOT
> client.is_demo
>
>AND
>
>NOT
> client.deleted
>
>)
>
> Select qp.client_id, (SELECT COUNT(0) FROM customers AS c WHERE
> (c.clientid = qp.client_id) AND NOT c.deleted) AS client_count
>
> FROM query_p AS qp
>
>
> *Explain Analyze:*
>
> CTE Scan on "query_p" "qp"  (cost=0.01..1060.57 rows=1 width=8) (actual
> time=4065.244..4065.246 rows=1 loops=1)
>
>   CTE query_p
>
> ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003
> rows=1 loops=1)
>
>   SubPlan 2
>
> ->  Aggregate  (cost=1060.53..1060.54 rows=1 width=0) (actual
> time=4065.229..4065.229 rows=1 loops=1)
>
>   ->  Index Scan using "clientid_customers" on "customers" "c"
>  (cost=0.00..1059.01 rows=607 width=0) (actual time=9.105..4063.728
> rows=2513 loops=1)
>
> Index Cond: ("clientid" = "qp"."client_id")
>
> Filter: (NOT "deleted")
>
> Rows Removed by Filter: 1068
>
> Total runtime: 4075.753 ms
>
>
>
> Why a search for "client_id" is so slow??
>

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.


>
>
> *Table customers:*
>
>   Table "public.customers"
>
>  Column |Type |
> Modifiers
>
> +-+-
> 
>
>  id | bigint  | not null default
> "nextval"('"customers_seq"'::"regclass")
>
>  clientid   | bigint  | not null default 0
>
>  name_first | character varying(80)   | default
> ''::character varying
>
>  name_last  | character varying(80)   | default
> ''::character varying
>
>  company| character varying(255)  | default
> ''::character varying
>
>
> *Index clientid_customers:*
>
> CREATE INDEX
>
> clientid_customers
>
> ON
>
> customers
>
> (
>
> "clientid"
>
> );
>
>
>
> Thanks!
>
> Patrick
>

[GENERAL] Slow index scan - Pgsql 9.2

[Patrick B]

Hi guys,

I've got the following Query:

WITH

   query_p AS (

   SELECT CAST(6667176 AS
BIGINT) AS client_id),




 clients AS (

   SELECT

   client.id
,client.job_share_mode

   FROM

   customers AS
client

   WHERE

   (client.clientid
= (SELECT qp.client_id FROM query_p AS qp))

   AND

   NOT
client.is_demo

   AND

   NOT
client.deleted

   )

Select qp.client_id, (SELECT COUNT(0) FROM customers AS c WHERE (c.clientid
= qp.client_id) AND NOT c.deleted) AS client_count

FROM query_p AS qp


*Explain Analyze:*

CTE Scan on "query_p" "qp"  (cost=0.01..1060.57 rows=1 width=8) (actual
time=4065.244..4065.246 rows=1 loops=1)

  CTE query_p

->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003
rows=1 loops=1)

  SubPlan 2

->  Aggregate  (cost=1060.53..1060.54 rows=1 width=0) (actual
time=4065.229..4065.229 rows=1 loops=1)

  ->  Index Scan using "clientid_customers" on "customers" "c"
 (cost=0.00..1059.01 rows=607 width=0) (actual time=9.105..4063.728
rows=2513 loops=1)

Index Cond: ("clientid" = "qp"."client_id")

Filter: (NOT "deleted")

Rows Removed by Filter: 1068

Total runtime: 4075.753 ms



Why a search for "client_id" is so slow??


*Table customers:*

  Table "public.customers"

 Column |Type |
Modifiers

+-+-

 id | bigint  | not null default
"nextval"('"customers_seq"'::"regclass")

 clientid   | bigint  | not null default 0

 name_first | character varying(80)   | default
''::character varying

 name_last  | character varying(80)   | default
''::character varying

 company| character varying(255)  | default
''::character varying


*Index clientid_customers:*

CREATE INDEX

clientid_customers

ON

customers

(

"clientid"

);



Thanks!

Patrick

Re: [GENERAL] Matching indexe for timestamp

[Vitaly Burovoy]

On 1/9/17, Job  wrote:
> Hello,
>
> on this table:
>
>   Table "public.gruorari_tmp"
>   Column   |  Type  |
> Modifiers
> ---++--
>  id| numeric(1000,1)| not null default
> function_get_next_sequence('gruorari_tmp_id_seq'::text)
>  idgrucate | numeric(1000,1)|
>  dalle | time without time zone |
>  alle  | time without time zone |
>  gg_sett   | integer|
>  azione| character varying  |
> Indexes:
> "keygruorari_tmp" PRIMARY KEY, btree (id)
> "gruorari_tmp_alle_idx" btree (alle)
> "gruorari_tmp_dalle_alle_idx" btree (dalle, alle)
> "gruorari_tmp_dalle_idx" btree (dalle)
> "gruorari_tmp_gg_sett_idx" btree (gg_sett)
> "gruorari_tmp_idgrucate_idx" btree (idgrucate)
>
> i have a specific condition (i report example value):
> "and ( gruorari_tmp.id is null or ( 2 = gg_sett and '16:00:00'::time between
> gruorari_tmp.dalle and gruorari_tmp.alle ) )"
>
> But in the query planner, at that point, Postgresql 9.6.1 seems not to use
> any index (single on dalle / alle field and combindex index on dalle+alle)

Of course. There is no reason to use any index because the condition
"gruorari_tmp.id is null" is not covered by any of them. To find such
rows you have to scan all table (because there can be with any
"alle".."dalle" values), that's why Postgres uses SeqScan.

> but it use seqscan:
>
> Seq Scan on gruorari_tmp  (cost=0.00..5.90 rows=290 width=68) (actual
> time=0.014..0.062 rows=290 loops=1)
>  ->  Hash  (cost=164.06..164.06 rows=1
> width=29) (actual time=0.770..0.770 rows=1 loops=1)
> And it seems to be the main cost for the whole query.

> Which kind of index should i use for that condition/fields?

for _big_ tables Postgres can use "bitmap OR" node if there are two
indexes which can be used.
So create index for "gruorari_tmp.id is null" and gin/gist "(gg_sett,
timerange(gruorari_tmp.dalle, gruorari_tmp.alle))" (see below).

If you use a condition like " between colA and colB", some sort
of a "timerange" is the best case. Unfortunately there is no such
type, but it is easy to create it by an example[1].
Then you can use btree_gin or btree_gist (depending on a base index
type) extension to use an ordinary type column(s) with range type
column(s).

P.S.: Postgres can not to use indexes even if they are right because
according to a statistics SeqScan will take similar access time.

[1]https://www.postgresql.org/docs/9.6/static/rangetypes.html#RANGETYPES-DEFINING

-- 
Best regards,
Vitaly Burovoy


-- 
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] Matching indexe for timestamp

[David G. Johnston]

On Mon, Jan 9, 2017 at 2:58 PM, Job  wrote:

>
> But in the query planner, at that point, Postgresql 9.6.1 seems not to use
> any index (single on dalle / alle field and combindex index on dalle+alle)
> but it use seqscan:
>
> Seq Scan on gruorari_tmp  (cost=0.00..5.90 rows=290 width=68) (actual
> time=0.014..0.062 rows=290 loops=1)
>  ->  Hash  (cost=164.06..164.06 rows=1
> width=29) (actual time=0.770..0.770 rows=1 loops=1)
>
>
​Given a query with expected "rows=290" I am not surprised that it would
simply scan the entire relation.  Especially since you have four columns in
your where clause and so any one index would be insufficient.

You sound as if you believe that any query that doesn't use an index is
flawed.  That is not the case.

David J.

Re: R: [GENERAL] Matching indexe for timestamp

[Adrian Klaver]

On 01/09/2017 03:38 PM, Job wrote:

Please also reply to list.  I do not have time at the moment to go 
through this, someone else on the list might.



Hi Adrian,

You are right; here is the query and the planner.
I think indexes are not used at all!

/F

EXPLAIN ANALYZE select
  webrecord.dominio
from webrecord
  left join grucategorie on grucategorie.codcategoria=webrecord.categoria 
and grucategorie.codgruppo='f50147_01'
  left join grulist on grulist.nome=webrecord.dominio and 
grulist.codgruppo='f50147_01' and grulist.stato in (1)
  left join firewall_geo_reject on 
firewall_geo_reject.country=webrecord.country and 
firewall_geo_reject.codgruppo='f50147_01'
  left join gruorari_tmp on gruorari_tmp.idgrucate=grucategorie.id
where dominio='PATTERN'
  and ( grulist.stato=1 OR grucategorie.codcategoria is not null OR 
firewall_geo_reject.country is not null )
  and ( gruorari_tmp.id is null or ( 1 = gg_sett and '17:23:00'::time 
between gruorari_tmp.dalle and gruorari_tmp.alle ) )
  and NOT EXISTS (select 1 from grulist where stato=2 and 
codgruppo='f50147_01' and nome='PATTERN')
limit 1;

 QUERY PLAN

 Limit  (cost=130.51..172.16 rows=1 width=14) (actual time=436.537..436.538 
rows=1 loops=1)
   InitPlan 1 (returns $0)
 ->  Index Only Scan using aaa_idx on grulist grulist_1  (cost=0.29..80.31 
rows=1 width=0) (actual time=0.062..0.062 rows=0 loops=1)
   Index Cond: ((stato = '2'::numeric) AND (codgruppo = 
'f50147_01'::text) AND (nome = 'PATTERN'::text))
   Heap Fetches: 0
   ->  Result  (cost=50.21..303115.67 rows=7277 width=14) (actual 
time=436.534..436.534 rows=1 loops=1)
 One-Time Filter: (NOT $0)
 ->  Nested Loop Left Join  (cost=50.21..303115.67 rows=7277 width=14) 
(actual time=436.463..436.463 rows=1 loops=1)
   Join Filter: ((grulist.nome)::text = (webrecord.dominio)::text)
   Filter: ((grulist.stato = '1'::numeric) OR 
(grucategorie.codcategoria IS NOT NULL) OR (firewall_geo_reject.country IS NOT 
NULL))
   ->  Nested Loop Left Join  (cost=49.92..302908.01 rows=7277 
width=25) (actual time=436.347..436.347 rows=1 loops=1)
 Join Filter: ((firewall_geo_reject.country)::text = 
(webrecord.country)::text)
 Rows Removed by Join Filter: 13
 ->  Nested Loop Left Join  (cost=49.92..300318.08 
rows=7277 width=46) (actual time=431.407..431.407 rows=1 loops=1)
   Join Filter: ((grucategorie.codcategoria)::text = 
(webrecord.categoria)::text)
   Rows Removed by Join Filter: 18
   Filter: ((gruorari_tmp.id IS NULL) OR ((1 = 
gruorari_tmp.gg_sett) AND ('17:23:00'::time without time zone >= 
gruorari_tmp.dalle) AND ('17:23:00'::time without time zone <= gruorari_tmp.alle)))
   ->  Seq Scan on webrecord  (cost=0.00..249584.12 
rows=159614 width=70) (actual time=430.696..430.696 rows=1 loops=1)
 Filter: ((dominio)::text = 'PATTERN'::text)
 Rows Removed by Filter: 596858
   ->  Materialize  (cost=49.92..455.58 rows=14 
width=35) (actual time=0.663..0.689 rows=19 loops=1)
 ->  Hash Left Join  (cost=49.92..455.51 
rows=14 width=35) (actual time=0.639..0.656 rows=19 loops=1)
   Hash Cond: (grucategorie.id = 
gruorari_tmp.idgrucate)
   ->  Bitmap Heap Scan on grucategorie  
(cost=40.40..445.70 rows=14 width=17) (actual time=0.142..0.143 rows=19 loops=1)
 Recheck Cond: ((codgruppo)::text = 
'f50147_01'::text)
 Heap Blocks: exact=5
 ->  Bitmap Index Scan on 
grucategorie_codgruppo_idx  (cost=0.00..40.39 rows=14 width=0) (actual 
time=0.084..0.084 rows=83 loops=1)
   Index Cond: 
((codgruppo)::text = 'f50147_01'::text)
   ->  Hash  (cost=5.90..5.90 rows=290 
width=36) (actual time=0.381..0.381 rows=290 loops=1)
 Buckets: 1024  Batches: 1  Memory 
Usage: 29kB
 ->  Seq Scan on gruorari_tmp  
(cost=0.00..5.90 rows=290 width=36) (actual time=0.023..0.176 rows=290 loops=1)
 ->  Materialize  (cost=0.00..297.73 rows=21 width=3) 
(actual time=0.151..4.928 rows=13 loops=1)
   ->  Seq Scan on firewall_geo_reject  

Re: [GENERAL] Matching indexe for timestamp

[Adrian Klaver]

On 01/09/2017 01:58 PM, Job wrote:

Hello,

on this table:

  Table "public.gruorari_tmp"
  Column   |  Type  |
Modifiers
---++--
 id| numeric(1000,1)| not null default
function_get_next_sequence('gruorari_tmp_id_seq'::text)
 idgrucate | numeric(1000,1)|
 dalle | time without time zone |
 alle  | time without time zone |
 gg_sett   | integer|
 azione| character varying  |
Indexes:
"keygruorari_tmp" PRIMARY KEY, btree (id)
"gruorari_tmp_alle_idx" btree (alle)
"gruorari_tmp_dalle_alle_idx" btree (dalle, alle)
"gruorari_tmp_dalle_idx" btree (dalle)
"gruorari_tmp_gg_sett_idx" btree (gg_sett)
"gruorari_tmp_idgrucate_idx" btree (idgrucate)





i have a specific condition (i report example value):
"and ( gruorari_tmp.id is null or ( 2 = gg_sett and '16:00:00'::time
between gruorari_tmp.dalle and gruorari_tmp.alle ) )"

But in the query planner, at that point, Postgresql 9.6.1 seems not to
use any index (single on dalle / alle field and combindex index on
dalle+alle) but it use seqscan:

Seq Scan on gruorari_tmp  (cost=0.00..5.90 rows=290 width=68) (actual
time=0.014..0.062 rows=290 loops=1)
 ->  Hash  (cost=164.06..164.06 rows=1
width=29) (actual time=0.770..0.770 rows=1 loops=1)
And it seems to be the main cost for the whole query.
Which kind of index should i use for that condition/fields?


As before, giving just snippets of the information is going to prolong 
or prevent arriving at an answer. So:


1) What is the complete query?

2) What is the complete EXPLAIN ANALYZE?




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

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

[Kevin Grittner]

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.

--
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] checkpoint clarifications needed

[Tom DalPozzo]

>
> Hi, I had already read that doc but I can't  answer clearly to my
>> questions 2,4 and 5.
>>
>
> The answer would seem to depend on what you consider 'a consistency state
> position'. Is it possible to be more explicit about what you mean?
>
>>
>> Hi, I meant a position such that, if you replay up to it, then the DB is
in a consistent state (transactions done entirely or not a t all...).
But, as Alvaro wrote, any position is ok
Thank you very much
Pupillo

Re: [GENERAL] checkpoint clarifications needed

[Tom DalPozzo]

>
> > Hi,
> > so let's suppose that the WAL is:
> > LSN 10: start transaction 123
> > LSN 11: update tuple 100
> >checkpoint position here (not a record but just for understanding)
> > LSN 12: update tuple 100
> > LSN 13: update tuple 100
> > LSN 14: checkpoint record ( postion=11)
> > LSN 15: update tuple 100
> > and that the system crashes now, before ending to write all the
> > transaction's recs to the WAL  (other updates and commit record missing).
> >
> > At the replay, starting from LSN 12, the entire page we had at LSN 11 is
> > written to the disk, though carrying inconsistent data.
> > Then we can even replay up to the end of WAL but always getting
> > inconsistent data.
> > BUT, you say, as the tuple is not commited in the WAL, only the old
> version
> > of the tuple will be visible? Right?
>
> Yes -- all the updated tuples are invisible because the commit record
> for transaction 123 does not appear in wal.  A future VACUUM will remove
> all those tuples.  Note that precisely for this reason, the original
> version of the tuple had not been removed yet.
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Ok! Now many things are clear to me
Thank you very much
Pupillo

Re: [GENERAL] checkpoint clarifications needed

[Alvaro Herrera]

Tom DalPozzo wrote:

> Hi,
> so let's suppose that the WAL is:
> LSN 10: start transaction 123
> LSN 11: update tuple 100
>checkpoint position here (not a record but just for understanding)
> LSN 12: update tuple 100
> LSN 13: update tuple 100
> LSN 14: checkpoint record ( postion=11)
> LSN 15: update tuple 100
> and that the system crashes now, before ending to write all the
> transaction's recs to the WAL  (other updates and commit record missing).
> 
> At the replay, starting from LSN 12, the entire page we had at LSN 11 is
> written to the disk, though carrying inconsistent data.
> Then we can even replay up to the end of WAL but always getting
> inconsistent data.
> BUT, you say, as the tuple is not commited in the WAL, only the old version
> of the tuple will be visible? Right?

Yes -- all the updated tuples are invisible because the commit record
for transaction 123 does not appear in wal.  A future VACUUM will remove
all those tuples.  Note that precisely for this reason, the original
version of the tuple had not been removed yet.

-- 
Á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] checkpoint clarifications needed

[Tom DalPozzo]

>
> Whether any individual tuple in the data files is visible or not depends
> not only on the data itself, but also on the commit status of the
> transactions that created it (and deleted it, if any).  Replaying WAL
> also updates the commit status of transactions, so if you're in the
> middle of replaying WAL, you may be adding tuples to the data files, but
> those tuples will not become visible until their commit records are also
> updated.
>
> You can stop replaying WAL at any point, and data will always be in a
> consistent state.  Some data tuples might be "from the future" and those
> will not be visible, which is what makes it all consistent.
>
> Hi,
so let's suppose that the WAL is:
LSN 10: start transaction 123
LSN 11: update tuple 100
   checkpoint position here (not a record but just for understanding)
LSN 12: update tuple 100
LSN 13: update tuple 100
LSN 14: checkpoint record ( postion=11)
LSN 15: update tuple 100
and that the system crashes now, before ending to write all the
transaction's recs to the WAL  (other updates and commit record missing).

At the replay, starting from LSN 12, the entire page we had at LSN 11 is
written to the disk, though carrying inconsistent data.
Then we can even replay up to the end of WAL but always getting
inconsistent data.
BUT, you say, as the tuple is not commited in the WAL, only the old version
of the tuple will be visible? Right?

Regards
Pupillo

[GENERAL] Matching indexe for timestamp

[Job]

Hello,

on this table:

  Table "public.gruorari_tmp"
  Column   |  Type  |Modifiers
---++--
 id| numeric(1000,1)| not null default 
function_get_next_sequence('gruorari_tmp_id_seq'::text)
 idgrucate | numeric(1000,1)|
 dalle | time without time zone |
 alle  | time without time zone |
 gg_sett   | integer|
 azione| character varying  |
Indexes:
"keygruorari_tmp" PRIMARY KEY, btree (id)
"gruorari_tmp_alle_idx" btree (alle)
"gruorari_tmp_dalle_alle_idx" btree (dalle, alle)
"gruorari_tmp_dalle_idx" btree (dalle)
"gruorari_tmp_gg_sett_idx" btree (gg_sett)
"gruorari_tmp_idgrucate_idx" btree (idgrucate)

i have a specific condition (i report example value):
"and ( gruorari_tmp.id is null or ( 2 = gg_sett and '16:00:00'::time between 
gruorari_tmp.dalle and gruorari_tmp.alle ) )"

But in the query planner, at that point, Postgresql 9.6.1 seems not to use any 
index (single on dalle / alle field and combindex index on dalle+alle) but it 
use seqscan:

Seq Scan on gruorari_tmp  (cost=0.00..5.90 rows=290 width=68) (actual 
time=0.014..0.062 rows=290 loops=1)
 ->  Hash  (cost=164.06..164.06 rows=1 
width=29) (actual time=0.770..0.770 rows=1 loops=1)
And it seems to be the main cost for the whole query.
Which kind of index should i use for that condition/fields?

Thank you!

/F

Re: [GENERAL] checkpoint clarifications needed

[Alvaro Herrera]

Tom DalPozzo wrote:

> 2) I see that a checkpoint position can be right in the middle of a group
> of records related to a transaction (in the example, transaction id 10684).
> So a checkpoint position is NOT a consistency state point, right?

> 4) If I'm right at 2) then, between the checkpoint position (1/F00A7448 )
> and the checkpoint record position (1/FCBD7510) there must be a point where
> the DB is in a consistency state. If not, in case of crash just after
> writing the checkpoint record to the WAL and its position to pg_control,
> the system would replay from the checkpoint position (known by  last
> checkpoint record) without finding a consistency state. Right?
> 
> 5) How can we define, in terms of log records, a consistency state position?

Whether any individual tuple in the data files is visible or not depends
not only on the data itself, but also on the commit status of the
transactions that created it (and deleted it, if any).  Replaying WAL
also updates the commit status of transactions, so if you're in the
middle of replaying WAL, you may be adding tuples to the data files, but
those tuples will not become visible until their commit records are also
updated.

You can stop replaying WAL at any point, and data will always be in a
consistent state.  Some data tuples might be "from the future" and those
will not be visible, which is what makes it all consistent.

-- 
Á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] checkpoint clarifications needed

[Adrian Klaver]

On 01/09/2017 01:10 PM, Tom DalPozzo wrote:

Reread your original post and realized you where also asking
about transaction consistency and WALs. The thumbnail version is
that Postgres writes transactions to the WALs before they are
written to the data files on disk. A checkpoint represents a
point in the sequence when is is known that the changes recorded
in the WAL have been also recorded in the disk data files. So
Postgres then knows that in a recovery scenario it needs to only
redo/replay the WAL changes that are past the last checkpoint.
So the transactions are there it is just a matter of if they
need to be replayed or not. This is subject to caveats:


https://www.postgresql.org/docs/9.5/static/wal-reliability.html



Hi, I had already read that doc but I can't  answer clearly to my
questions 2,4 and 5.


The answer would seem to depend on what you consider 'a consistency 
state position'. Is it possible to be more explicit about what you mean?



Regards
Pupillo





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

Re: [GENERAL] checkpoint clarifications needed

[Tom DalPozzo]

>
> Reread your original post and realized you where also asking about
>> transaction consistency and WALs. The thumbnail version is that Postgres
>> writes transactions to the WALs before they are written to the data files
>> on disk. A checkpoint represents a point in the sequence when is is known
>> that the changes recorded in the WAL have been also recorded in the disk
>> data files. So Postgres then knows that in a recovery scenario it needs to
>> only redo/replay the WAL changes that are past the last checkpoint. So the
>> transactions are there it is just a matter of if they need to be replayed
>> or not. This is subject to caveats:
>>
>
> https://www.postgresql.org/docs/9.5/static/wal-reliability.html
>
>
>> Hi, I had already read that doc but I can't  answer clearly to my
questions 2,4 and 5.
Regards
Pupillo

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

[Rémi Cura]

Hey,
I like your curiosity !

At the billion range, you __have__ to use pgpointcloud,
pyramid raster solution (actually the more common way to perform this task)
or another database (hello monetdb).
Cheers,
Rémi-C

2017-01-09 20:11 GMT+01:00 Jonathan Vanasco :

>
> On Jan 9, 2017, at 12:49 PM, Israel Brewster wrote:
>
> >  Planning time: 4.554 ms
> >  Execution time: 225998.839 ms
> > (20 rows)
> >
> > So a little less than four minutes. Not bad (given the size of the
> database), or so I thought.
> >
> > This morning (so a couple of days later) I ran the query again without
> the explain analyze to check the results, and noticed that it didn't take
> anywhere near four minutes to execute. So I ran the explain analyze again,
> and got this:
>
> ...
>
> >  Planning time: 0.941 ms
> >  Execution time: 9636.285 ms
> > (20 rows)
> >
> > So from four minutes on the first run to around 9 1/2 seconds on the
> second. Presumably this difference is due to caching? I would have expected
> any caches to have expired by the time I made the second run, but the data
> *is* static, so I guess not. Otherwise, I don't know how to explain the
> improvement on the second run - the query plans appear identical (at least
> to me). *IS* there something else (for example, auto vacuum running over
> the weekend) that could explain the performance difference?
>
>
> This may sound crazy, but I suggest running each of these scenarios 3+
> times:
>
> # cold explain
> stop postgres
> start postgres
> explain analyze SELECT
>
> # cold select
> stop postgres
> start postgres
> enable \t for query timing
> SELECT
>
> # cold explain to select
> stop postgres
> start postgres
> explain analyze SELECT
> enable \t for query timing
> SELECT
>
> # cold select to explain
> stop postgres
> start postgres
> enable \t for query timing
> SELECT
> explain analyze SELECT
>
> # cold select to select
> stop postgres
> start postgres
> enable \t for query timing
> SELECT
> SELECT
>
> I've found the timing for "Explain Analyze" to be incredibly different
> from an actual SELECT on complex/large dataset queries... and the
> differences don't seem to correlate to possible speedups from index/table
> caching.
>
>
>
>
> --
> 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?

[Jonathan Vanasco]

On Jan 9, 2017, at 12:49 PM, Israel Brewster wrote:

>  Planning time: 4.554 ms
>  Execution time: 225998.839 ms
> (20 rows)
> 
> So a little less than four minutes. Not bad (given the size of the database), 
> or so I thought.
> 
> This morning (so a couple of days later) I ran the query again without the 
> explain analyze to check the results, and noticed that it didn't take 
> anywhere near four minutes to execute. So I ran the explain analyze again, 
> and got this:

...

>  Planning time: 0.941 ms
>  Execution time: 9636.285 ms
> (20 rows)
> 
> So from four minutes on the first run to around 9 1/2 seconds on the second. 
> Presumably this difference is due to caching? I would have expected any 
> caches to have expired by the time I made the second run, but the data *is* 
> static, so I guess not. Otherwise, I don't know how to explain the 
> improvement on the second run - the query plans appear identical (at least to 
> me). *IS* there something else (for example, auto vacuum running over the 
> weekend) that could explain the performance difference?


This may sound crazy, but I suggest running each of these scenarios 3+ times:

# cold explain
stop postgres
start postgres
explain analyze SELECT

# cold select
stop postgres
start postgres
enable \t for query timing
SELECT

# cold explain to select
stop postgres
start postgres
explain analyze SELECT
enable \t for query timing
SELECT

# cold select to explain
stop postgres
start postgres
enable \t for query timing
SELECT
explain analyze SELECT

# cold select to select
stop postgres
start postgres
enable \t for query timing
SELECT
SELECT

I've found the timing for "Explain Analyze" to be incredibly different from an 
actual SELECT on complex/large dataset queries... and the differences don't 
seem to correlate to possible speedups from index/table caching.




-- 
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?

[Paul Ramsey]

At BILLIONS, you're getting to a point where the point index is probably
(a) very large and (b) very deep, so you might want to do something
different with your data storage, like loading the data in spatially
compact patches of several 10s of points. Then the index will float more
nicely in memory, and be faster to traverse. Something like pgpointcloud
may start to look like it has some advantages.

WRT your time differences, make sure to try the same query but with
*different routes*. I find that often a slow query gets fast if I run it
twice identically, but if I run it twice with different parameterizations I
see slower execution. Basically the second time you're seeing some caching
of the immediately important blocks, but not necessarily every block you
might need for every case.

P.


On Mon, Jan 9, 2017 at 9:49 AM, Israel Brewster 
wrote:

> So just for interests sake, to kick things up a notch (and out of sheer
> morbid curiosity), I loaded a higher-resolution dataset (Elevation data for
> the state of Alaska, 2 arc second resolution, as opposed to 100 meter
> resolution before). Same structure/indexes and everything, just higher
> resolution. So the new database has 1,642,700,002 rows, and is somewhere
> around 300GB in size (including index). Due to the larger data size, I
> moved the database to a different table space which resides on a mirrored
> 2TB spinning platter disk (i.e. slower both because of the RAID and lack of
> SSD). Friday evening I ran the following query:
>
> EXPLAIN ANALYZE WITH segments AS (
> SELECT ST_MakeLine( lag((pt).geom , 1, NULL) OVER (ORDER BY (pt).path)
>   ,(pt).geom)::GEOGRAPHY AS short_line
> FROM ST_DumpPoints(
>   ST_Segmentize(
> ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056
> 61.179167,-156.77 71.285833)'),
> 5000
> )::geometry
> ) as pt
> )
> SELECT elevation
> FROM data ,segments
> WHERE segments.short_line IS NOT NULL
>   AND  ST_DWithin(location, segments.short_line, 100) = TRUE
> ORDER BY elevation DESC
> limit 1;
>
> Which is the same query that took around 300 ms on the smaller dataset.
> The result was this (https://explain.depesz.com/s/mKFN):
>
>
>QUERY PLAN
>
> 
> 
> 
>  Limit  (cost=354643835.82..354643835.83 rows=1 width=9) (actual
> time=225998.319..225998.320 rows=1 loops=1)
>CTE segments
>  ->  WindowAgg  (cost=60.08..82.58 rows=1000 width=64) (actual
> time=0.488..4.032 rows=234 loops=1)
>->  Sort  (cost=60.08..62.58 rows=1000 width=64) (actual
> time=0.460..0.875 rows=234 loops=1)
>  Sort Key: pt.path
>  Sort Method: quicksort  Memory: 57kB
>  ->  Function Scan on st_dumppoints pt  (cost=0.25..10.25
> rows=1000 width=64) (actual time=0.354..0.387 rows=234 loops=1)
>->  Sort  (cost=354643753.25..354645115.32 rows=544829 width=9)
> (actual time=225998.319..225998.319 rows=1 loops=1)
>  Sort Key: data.elevation DESC
>  Sort Method: top-N heapsort  Memory: 25kB
>  ->  Nested Loop  (cost=0.68..354641029.10 rows=544829 width=9)
> (actual time=349.784..225883.557 rows=159654 loops=1)
>->  CTE Scan on segments  (cost=0.00..20.00 rows=995
> width=32) (actual time=0.500..4.823 rows=233 loops=1)
>  Filter: (short_line IS NOT NULL)
>  Rows Removed by Filter: 1
>->  Index Scan using location_gist_idx on
> data  (cost=0.68..356423.07 rows=5 width=41) (actual time=71.416..969.196
> rows=685 loops=233)
>  Index Cond: (location && _st_expand(segments.short_line,
> '100'::double precision))
>  Filter: ((segments.short_line && _st_expand(location,
> '100'::double precision)) AND _st_dwithin(location, segments.short_line,
> '100'::double precision, true))
>  Rows Removed by Filter: 8011
>  Planning time: 4.554 ms
>  Execution time: 225998.839 ms
> (20 rows)
>
> So a little less than four minutes. Not bad (given the size of the
> database), or so I thought.
>
> This morning (so a couple of days later) I ran the query again without the
> explain analyze to check the results, and noticed that it didn't take
> anywhere near four minutes to execute. So I ran the explain analyze again,
> and got this:
>
>
>QUERY PLAN
>
> 
> 
> 
>  Limit  (cost=354643835.82..354643835.83 rows=1 width=9) (actual
> time=9636.165..9636.166 rows=1 loops=1)
>CTE segments
>  ->  WindowAgg  (cost=60.08..82.58 rows=1000 width=64) (actual
> time=0.345..1.137 rows=234 loops=1)
>

Re: [GENERAL] Why autvacuum is not started?

[Jeff Janes]

On Mon, Jan 9, 2017 at 8:45 AM, Edmundo Robles  wrote:

> I have running Postgresql  9.4 and... if i have  a  table  with  following
>  configuration:
> autovacuum_vacuum_scale_factor=0.0,
> autovacuum_analyze_scale_factor=0.0,
> autovacuum_vacuum_threshold=1000,
> autovacuum_analyze_threshold=1000,
> autovacuum_enabled=true
>
> Why  autovacuum is not started if  the table has more than 1000 inserts???
>

Inserts do not generate obsolete tuples, and so are not counted against
the "vacuum threshold" as described here:
https://www.postgresql.org/docs/9.4/static/routine-vacuuming.html.

But inserts can change the data distributions, so do count against "analyze
threshold".

Due to index-only-scans and freeze maps, there are reasons to revisit this
topic, so that insert only tables do get vacuumed and not just analyzed.
But that re-think has yet to be finished, and certainly won't be
back-ported to 9.4.

Cheers,

Jeff

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

[Israel Brewster]

So just for interests sake, to kick things up a notch (and out of sheer morbid curiosity), I loaded a higher-resolution dataset (Elevation data for the state of Alaska, 2 arc second resolution, as opposed to 100 meter resolution before). Same structure/indexes and everything, just higher resolution. So the new database has 1,642,700,002 rows, and is somewhere around 300GB in size (including index). Due to the larger data size, I moved the database to a different table space which resides on a mirrored 2TB spinning platter disk (i.e. slower both because of the RAID and lack of SSD). Friday evening I ran the following query:EXPLAIN ANALYZE WITH segments AS (    SELECT ST_MakeLine( lag((pt).geom , 1, NULL) OVER (ORDER BY (pt).path)                          ,(pt).geom)::GEOGRAPHY AS short_line    FROM ST_DumpPoints(          ST_Segmentize(            ST_GeographyFromText('SRID=4326;LINESTRING(-150.008056 61.179167,-156.77 71.285833)'),            5000        )::geometry    ) as pt)SELECT elevationFROM data ,segmentsWHERE segments.short_line IS NOT NULL  AND  ST_DWithin(location, segments.short_line, 100) = TRUEORDER BY elevation DESClimit 1;Which is the same query that took around 300 ms on the smaller dataset. The result was this (https://explain.depesz.com/s/mKFN):                                                                                     QUERY PLAN                                                                                      Limit  (cost=354643835.82..354643835.83 rows=1 width=9) (actual time=225998.319..225998.320 rows=1 loops=1)   CTE segments     ->  WindowAgg  (cost=60.08..82.58 rows=1000 width=64) (actual time=0.488..4.032 rows=234 loops=1)           ->  Sort  (cost=60.08..62.58 rows=1000 width=64) (actual time=0.460..0.875 rows=234 loops=1)                 Sort Key: pt.path                 Sort Method: quicksort  Memory: 57kB                 ->  Function Scan on st_dumppoints pt  (cost=0.25..10.25 rows=1000 width=64) (actual time=0.354..0.387 rows=234 loops=1)   ->  Sort  (cost=354643753.25..354645115.32 rows=544829 width=9) (actual time=225998.319..225998.319 rows=1 loops=1)         Sort Key: data.elevation DESC         Sort Method: top-N heapsort  Memory: 25kB         ->  Nested Loop  (cost=0.68..354641029.10 rows=544829 width=9) (actual time=349.784..225883.557 rows=159654 loops=1)               ->  CTE Scan on segments  (cost=0.00..20.00 rows=995 width=32) (actual time=0.500..4.823 rows=233 loops=1)                     Filter: (short_line IS NOT NULL)                     Rows Removed by Filter: 1               ->  Index Scan using location_gist_idx on data  (cost=0.68..356423.07 rows=5 width=41) (actual time=71.416..969.196 rows=685 loops=233)                     Index Cond: (location && _st_expand(segments.short_line, '100'::double precision))                     Filter: ((segments.short_line && _st_expand(location, '100'::double precision)) AND _st_dwithin(location, segments.short_line, '100'::double precision, true))                     Rows Removed by Filter: 8011 Planning time: 4.554 ms Execution time: 225998.839 ms(20 rows)So a little less than four minutes. Not bad (given the size of the database), or so I thought.This morning (so a couple of days later) I ran the query again without the explain analyze to check the results, and noticed that it didn't take anywhere near four minutes to execute. So I ran the explain analyze again, and got this:                                                                                     QUERY PLAN                                                                                      Limit  (cost=354643835.82..354643835.83 rows=1 width=9) (actual time=9636.165..9636.166 rows=1 loops=1)   CTE segments     ->  WindowAgg  (cost=60.08..82.58 rows=1000 width=64) (actual time=0.345..1.137 rows=234 loops=1)           ->  Sort  (cost=60.08..62.58 rows=1000 width=64) (actual time=0.335..0.428 rows=234 loops=1)                 Sort Key: pt.path                 Sort Method: quicksort  Memory: 57kB                 ->  Function Scan on st_dumppoints pt  (cost=0.25..10.25 rows=1000 width=64) (actual time=0.198..0.230 rows=234 loops=1)   ->  Sort  (cost=354643753.25..354645115.32 rows=544829 width=9) (actual time=9636.165..9636.165 rows=1 loops=1)         Sort Key: data.elevation DESC         Sort Method: top-N heapsort  Memory: 25kB         ->  Nested Loop  (cost=0.68..354641029.10 rows=544829 width=9) (actual time=1.190..9602.606 rows=159654 loops=1)               ->  CTE Scan on segments  (cost=0.00..20.00 rows=995 width=32) (actual time=0.361..1.318 rows=233 loops=1)                     Filter: 

Re: [GENERAL] Why autvacuum is not started?

[Adrian Klaver]

On 01/09/2017 08:45 AM, Edmundo Robles wrote:

I have running Postgresql  9.4 and... if i have  a  table  with
 following  configuration:
autovacuum_vacuum_scale_factor=0.0,
autovacuum_analyze_scale_factor=0.0,
autovacuum_vacuum_threshold=1000,
autovacuum_analyze_threshold=1000,
autovacuum_enabled=true

Why  autovacuum is not started if  the table has more than 1000 inserts???


https://www.postgresql.org/docs/9.4/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-THRESHOLD

"autovacuum_vacuum_threshold (integer)

Specifies the minimum number of updated or deleted tuples needed to 
trigger a VACUUM in any one table. The default is 50 tuples. This 
parameter can only be set in the postgresql.conf file or on the server 
command line. This setting can be overridden for individual tables by 
changing storage parameters.

"

INSERTs don't count.

They do for analyze though:

"autovacuum_analyze_threshold (integer)

Specifies the minimum number of inserted, updated or deleted tuples 
needed to trigger an ANALYZE in any one table. The default is 50 tuples. 
This parameter can only be set in the postgresql.conf file or on the 
server command line. This setting can be overridden for individual 
tables by changing storage parameters.

"


first  i have  setted  autovacuum_vacuum_threshold to 10,000  inserts
(it is normal each 2 hours ), then to  5,000, 3,000 and  finally  to
1,000 but  autovacuum is not triggered  but autoanalyze.

"select relname,last_vacuum, last_autovacuum, last_analyze,
last_autoanalyze from pg_stat_user_tables where relname like 'sta%';"

last_vacuum=>2017-01-05 10:40:34.228633-06
last_autovacuum => null
last_analyze=>  2017-01-04 15:02:47.438715-06
last_autoanalyze=> 2017-01-09 10:35:51.391114-06

--




--
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] Why autvacuum is not started?

[Edmundo Robles]

I have running Postgresql  9.4 and... if i have  a  table  with  following
 configuration:
autovacuum_vacuum_scale_factor=0.0,
autovacuum_analyze_scale_factor=0.0,
autovacuum_vacuum_threshold=1000,
autovacuum_analyze_threshold=1000,
autovacuum_enabled=true

Why  autovacuum is not started if  the table has more than 1000 inserts???

first  i have  setted  autovacuum_vacuum_threshold to 10,000  inserts (it
is normal each 2 hours ), then to  5,000, 3,000 and  finally  to 1,000 but
 autovacuum is not triggered  but autoanalyze.

"select relname,last_vacuum, last_autovacuum, last_analyze,
last_autoanalyze from pg_stat_user_tables where relname like 'sta%';"

last_vacuum=>2017-01-05 10:40:34.228633-06
last_autovacuum => null
last_analyze=>  2017-01-04 15:02:47.438715-06
last_autoanalyze=> 2017-01-09 10:35:51.391114-06

--

Re: R: R: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

[Adrian Klaver]

On 01/09/2017 01:33 AM, Job wrote:

Hi guys,

Really thank you.
Thanks to your help i solved the problem.


For the record which problem(s)?:

1) Your original function issue.

2) The stand alone query you showed later.

3) Both.



As said by Adrian:


Caveats, it is morning here and coffee is still brewing, but I am not following. The 
left join limits grulist.stato to NULL, 1, 2. Your first condition catches the 1 
value. Should not the second 'and' be (stato = 2 or stato is >>
null). In fact I do not see why it needs to exist at all, which seems to be 
borne out by your experimentation.


O doubled conditions, without reason!

I think making good queries is an art, at the moment i am not an artist! :)

Again,thank you very much!

/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

Re: [GENERAL] checkpoint clarifications needed

[Adrian Klaver]

On 01/09/2017 06:47 AM, Tom DalPozzo wrote:

https://www.postgresql.org/docs/9.5/static/wal-internals.html



"After a checkpoint has been made and the log flushed, the
checkpoint's position is saved in the file pg_control. Therefore, at
the start of recovery, the server first reads pg_control and then
the checkpoint record; then it performs the REDO operation by
scanning forward from the log position indicated in the checkpoint
record. Because the entire content of data pages is saved in the log
on the first page modification after a checkpoint (assuming
full_page_writes is not disabled), all pages changed since the
checkpoint will be restored to a consistent state."


Hi, yes I know that, it's what I meant in my point 3). As it says first
 "the checkpoint's position (NOT the record)is saved in the file
pg_control" then I had some doubt.


Reread your original post and realized you where also asking about 
transaction consistency and WALs. The thumbnail version is that Postgres 
writes transactions to the WALs before they are written to the data 
files on disk. A checkpoint represents a point in the sequence when is 
is known that the changes recorded in the WAL have been also recorded in 
the disk data files. So Postgres then knows that in a recovery scenario 
it needs to only redo/replay the WAL changes that are past the last 
checkpoint. So the transactions are there it is just a matter of if they 
need to be replayed or not. This is subject to caveats:


https://www.postgresql.org/docs/9.5/static/wal-reliability.html


Regards
Pupillo





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

Re: [GENERAL] checkpoint clarifications needed

[Adrian Klaver]

On 01/09/2017 06:47 AM, Tom DalPozzo wrote:

https://www.postgresql.org/docs/9.5/static/wal-internals.html



"After a checkpoint has been made and the log flushed, the
checkpoint's position is saved in the file pg_control. Therefore, at
the start of recovery, the server first reads pg_control and then
the checkpoint record; then it performs the REDO operation by
scanning forward from the log position indicated in the checkpoint
record. Because the entire content of data pages is saved in the log
on the first page modification after a checkpoint (assuming
full_page_writes is not disabled), all pages changed since the
checkpoint will be restored to a consistent state."


Hi, yes I know that, it's what I meant in my point 3). As it says first
 "the checkpoint's position (NOT the record)is saved in the file
pg_control" then I had some doubt.


Yes it is just one piece of information stored in the file.

To see what else is stored there do:

pg_controldata -D your_cluster_data_directory




Regards
Pupillo





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

Re: [GENERAL] checkpoint clarifications needed

[Tom DalPozzo]

>
> https://www.postgresql.org/docs/9.5/static/wal-internals.html
>>
>
> "After a checkpoint has been made and the log flushed, the checkpoint's
> position is saved in the file pg_control. Therefore, at the start of
> recovery, the server first reads pg_control and then the checkpoint record;
> then it performs the REDO operation by scanning forward from the log
> position indicated in the checkpoint record. Because the entire content of
> data pages is saved in the log on the first page modification after a
> checkpoint (assuming full_page_writes is not disabled), all pages changed
> since the checkpoint will be restored to a consistent state."
>
>
>> Hi, yes I know that, it's what I meant in my point 3). As it says first
 "the checkpoint's position (NOT the record)is saved in the file
pg_control" then I had some doubt.
Regards
Pupillo

Re: [GENERAL] checkpoint clarifications needed

[Adrian Klaver]

On 01/09/2017 06:14 AM, Tom DalPozzo wrote:

Hi, I need some clarifications about checkpoints.
Below here a log from my standby server when started and then some parts
of the interested WAL in the master's cluster  obtained by pg_xlogdump.
Just to have an example to talk on.

1) I see: "LOG:  redo starts at 1/F00A7448" . I was expecting a
checkpoint record around 1/F00A7448 but the related checkpoint record is
at lsn: 1/FCBD7510 instead.
I expected that because I read in the doc :"Checkpoints are points in
the sequence of transactions at which it is guaranteed that the heap and
index data files have been updated with ALL information written BEFORE
that checkpoint".
And I interpreted that as "All information written before that
checkpoint RECORD" but now I guess that one thing is a checkpoint point
and one thing is a checkpoint record. Right?

2) I see that a checkpoint position can be right in the middle of a
group of records related to a transaction (in the example, transaction
id 10684). So a checkpoint position is NOT a consistency state point, right?

3) According to doc at 29.5,  in pg_control the position of the last
checkpoint record (not the checkpoint position itself) is saved  right?

4) If I'm right at 2) then, between the checkpoint position (1/F00A7448
) and the checkpoint record position (1/FCBD7510) there must be a point
where the DB is in a consistency state. If not, in case of crash just
after writing the checkpoint record to the WAL and its position to
pg_control, the system would replay from the checkpoint position (known
by  last checkpoint record) without finding a consistency state. Right?

5) How can we define, in terms of log records, a consistency state position?


https://www.postgresql.org/docs/9.5/static/wal-internals.html

"After a checkpoint has been made and the log flushed, the checkpoint's 
position is saved in the file pg_control. Therefore, at the start of 
recovery, the server first reads pg_control and then the checkpoint 
record; then it performs the REDO operation by scanning forward from the 
log position indicated in the checkpoint record. Because the entire 
content of data pages is saved in the log on the first page modification 
after a checkpoint (assuming full_page_writes is not disabled), all 
pages changed since the checkpoint will be restored to a consistent state."




Best regards
Pupillo

STANDBY SERVER LOG
LOG:  redo starts at 1/F00A7448

LOG:  consistent recovery state reached at 2/426DF28
LOG:  invalid record length at 2/426DF28: wanted 24, got 0
LOG:  database system is ready to accept read only connections
LOG:  started streaming WAL from primary at 2/400 on timeline 1


FROM PG_XLOGDUMP OF MASTER

rmgr: Heaplen (rec/tot): 14/  1186, tx:  10684, lsn:
1/F009EEE0, prev 1/F009EEA8, desc: UPDATE off 1 xmax 10684 ; new off 3
xmax 0, blkref #0: rel 1663/16384/16422 blk 20054, blkref #1: rel
1663/16384/16422 blk 19774
rmgr: Btree   len (rec/tot):  2/64, tx:  10684, lsn:
1/F009F388, prev 1/F009EEE0, desc: INSERT_LEAF off 2, blkref #0: rel
1663/16384/16428 blk 711
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F009F3C8, prev 1/F009F388, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9663 FPW
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F00A13E8, prev 1/F009F3C8, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9664 FPW
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F00A3408, prev 1/F00A13E8, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9665 FPW
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F00A5428, prev 1/F00A3408, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9666 FPW
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F00A7448, prev 1/F00A5428, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9667 FPW
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F00A9468, prev 1/F00A7448, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9668 FPW
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F00AB488, prev 1/F00A9468, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9669 FPW
rmgr: Heaplen (rec/tot):  8/  8063, tx:  10682, lsn:
1/F00AD4A8, prev 1/F00AB488, desc: LOCK off 5: xid 10682: flags 0
LOCK_ONLY EXCL_LOCK , blkref #0: rel 1663/16384/16422 blk 19787 FPW
rmgr: Heaplen (rec/tot):  8/  8063, tx:  10684, lsn:
1/F00AF440, prev 1/F00AD4A8, desc: LOCK off 2: xid 10684: flags 0
LOCK_ONLY EXCL_LOCK , blkref #0: rel 1663/16384/16422 blk 19774 FPW
rmgr: Heaplen (rec/tot): 14/  4657, tx:  10682, lsn:
1/F00B13D8, prev 1/F00AF440, desc: UPDATE off 5 xmax 10682 ; new off 4
xmax 0, blkref #0: rel 1663/16384/16422 blk 20075 FPW, blkref #1: rel
1663/16384/16422 blk 19787
rmgr: Heaplen (rec/tot): 14/  4657, tx:  10684, lsn:
1/F00B2628, prev 

Re: [GENERAL] Querying dead rows

[Albe Laurenz]

Rakesh Kumar wrote:
> Is there a way to query dead rows (that is, rows which are dead and still not 
> cleaned up by Vacuum)
> using SQL.  I am asking this just as an academical question.

Sort of.

You can use heap_page_item_attrs() from the pageinspect contrib module to get 
at the
data, but you will only see the binary representation.

If your question is motivated by security concerns, you cannot do this unless 
you
are a superuser.

Yours,
Laurenz Albe

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

[GENERAL] checkpoint clarifications needed

[Tom DalPozzo]

Hi, I need some clarifications about checkpoints.
Below here a log from my standby server when started and then some parts of
the interested WAL in the master's cluster  obtained by pg_xlogdump.
Just to have an example to talk on.

1) I see: "LOG:  redo starts at 1/F00A7448" . I was expecting a checkpoint
record around 1/F00A7448 but the related checkpoint record is at lsn:
1/FCBD7510 instead.
I expected that because I read in the doc :"Checkpoints are points in the
sequence of transactions at which it is guaranteed that the heap and index
data files have been updated with ALL information written BEFORE that
checkpoint".
And I interpreted that as "All information written before that checkpoint
RECORD" but now I guess that one thing is a checkpoint point and one thing
is a checkpoint record. Right?

2) I see that a checkpoint position can be right in the middle of a group
of records related to a transaction (in the example, transaction id 10684).
So a checkpoint position is NOT a consistency state point, right?

3) According to doc at 29.5,  in pg_control the position of the last
checkpoint record (not the checkpoint position itself) is saved  right?

4) If I'm right at 2) then, between the checkpoint position (1/F00A7448 )
and the checkpoint record position (1/FCBD7510) there must be a point where
the DB is in a consistency state. If not, in case of crash just after
writing the checkpoint record to the WAL and its position to pg_control,
the system would replay from the checkpoint position (known by  last
checkpoint record) without finding a consistency state. Right?

5) How can we define, in terms of log records, a consistency state position?

Best regards
Pupillo

STANDBY SERVER LOG
LOG:  redo starts at 1/F00A7448

LOG:  consistent recovery state reached at 2/426DF28
LOG:  invalid record length at 2/426DF28: wanted 24, got 0
LOG:  database system is ready to accept read only connections
LOG:  started streaming WAL from primary at 2/400 on timeline 1


FROM PG_XLOGDUMP OF MASTER

rmgr: Heaplen (rec/tot): 14/  1186, tx:  10684, lsn:
1/F009EEE0, prev 1/F009EEA8, desc: UPDATE off 1 xmax 10684 ; new off 3 xmax
0, blkref #0: rel 1663/16384/16422 blk 20054, blkref #1: rel
1663/16384/16422 blk 19774
rmgr: Btree   len (rec/tot):  2/64, tx:  10684, lsn:
1/F009F388, prev 1/F009EEE0, desc: INSERT_LEAF off 2, blkref #0: rel
1663/16384/16428 blk 711
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F009F3C8, prev 1/F009F388, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9663 FPW
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F00A13E8, prev 1/F009F3C8, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9664 FPW
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F00A3408, prev 1/F00A13E8, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9665 FPW
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F00A5428, prev 1/F00A3408, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9666 FPW
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F00A7448, prev 1/F00A5428, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9667 FPW
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F00A9468, prev 1/F00A7448, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9668 FPW
rmgr: XLOGlen (rec/tot):  0/  8193, tx:  0, lsn:
1/F00AB488, prev 1/F00A9468, desc: FPI_FOR_HINT , blkref #0: rel
1663/16384/16484 blk 9669 FPW
rmgr: Heaplen (rec/tot):  8/  8063, tx:  10682, lsn:
1/F00AD4A8, prev 1/F00AB488, desc: LOCK off 5: xid 10682: flags 0 LOCK_ONLY
EXCL_LOCK , blkref #0: rel 1663/16384/16422 blk 19787 FPW
rmgr: Heaplen (rec/tot):  8/  8063, tx:  10684, lsn:
1/F00AF440, prev 1/F00AD4A8, desc: LOCK off 2: xid 10684: flags 0 LOCK_ONLY
EXCL_LOCK , blkref #0: rel 1663/16384/16422 blk 19774 FPW
rmgr: Heaplen (rec/tot): 14/  4657, tx:  10682, lsn:
1/F00B13D8, prev 1/F00AF440, desc: UPDATE off 5 xmax 10682 ; new off 4 xmax
0, blkref #0: rel 1663/16384/16422 blk 20075 FPW, blkref #1: rel
1663/16384/16422 blk 19787
rmgr: Heaplen (rec/tot): 14/  4657, tx:  10684, lsn:
1/F00B2628, prev 1/F00B13D8, desc: UPDATE off 2 xmax 10684 ; new off 4 xmax
0, blkref #0: rel 1663/16384/16422 blk 20054 FPW, blkref #1: rel
1663/16384/16422 blk 19774
rmgr: Btr
...
rmgr: Heap2   len (rec/tot):  8/68, tx:  0, lsn:
1/FCBD7448, prev 1/FCBD7400, desc: CLEAN remxid 10903, blkref #0: rel
1663/16384/16422 blk 2001
rmgr: Heap2   len (rec/tot):  8/66, tx:  0, lsn:
1/FCBD7490, prev 1/FCBD7448, desc: CLEAN remxid 10903, blkref #0: rel
1663/16384/16422 blk 2003
rmgr: Standby len (rec/tot): 24/50, tx:  0, lsn:
1/FCBD74D8, prev 1/FCBD7490, desc: RUNNING_XACTS nextXid 10907
latestCompletedXid 10906 oldestRunningXid 10907
rmgr: XLOG 

R: R: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

[Job]

Hi guys,

Really thank you.
Thanks to your help i solved the problem.

As said by Adrian:

>>Caveats, it is morning here and coffee is still brewing, but I am not 
>>following. The left join limits grulist.stato to NULL, 1, 2. Your first 
>>condition catches the 1 value. Should not the second 'and' be (stato = 2 or 
>>stato is >>
>>null). In fact I do not see why it needs to exist at all, which seems to be 
>>borne out by your experimentation. 

O doubled conditions, without reason!

I think making good queries is an art, at the moment i am not an artist! :)

Again,thank you very much!

/F



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