[GENERAL] foreign key to "some rows" of a second table

[Chris Withers]

  
  
Hi All,

So, I have a table that looks like this:


CREATE TABLE config (
    region    varchar(10),
    name    varchar(10),
    value    varchar(40)
);

Another looks like this:

CREATE TABLE tag (
    host    varchar(10),
    type    varchar(10),
    value    varchar(10)
);

What's the best way to set up a constraint on the 'config' table
such that the 'region' column can only contain values that exist in
the 'tag' table's value column where the 'type' is 'region'?

cheers,

Chris
  

Re: [GENERAL] foreign key to "some rows" of a second table

[Achilleas Mantzios]

On 22/02/2016 13:03, Chris Withers wrote:

Hi All,

So, I have a table that looks like this:

CREATE TABLE config (
regionvarchar(10),
namevarchar(10),
valuevarchar(40)
);

Another looks like this:

CREATE TABLE tag (
hostvarchar(10),
typevarchar(10),
valuevarchar(10)
);

What's the best way to set up a constraint on the 'config' table such that the 
'region' column can only contain values that exist in the 'tag' table's value 
column where the 'type' is 'region'?


Hi,
that's the reason CONSTRAINT TRIGGERS were introduced in PostgreSQL, I guess.
Just write an AFTER INSERT OR UPDATE TRIGGER ON config,
which checks for integrity.



cheers,

Chris



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: [GENERAL] Why is my database so big?

[FarjadFarid(ChkNet)]

Tom, thanks for your unbiased detailed response. 

Interesting post. 

-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tom Lane
Sent: 22 February 2016 05:06
To: Andrew Smith
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Why is my database so big?

Andrew Smith  writes:
> I am setting up a proof of concept database to store some historical data.
> Whilst I've used PostgreSQL a bit in the past this is the first time 
> I've looked into disk usage due to the amount of data that could 
> potentially be stored. I've done a quick test and I'm a little 
> confused as to why it is occupying so much space on disk. Here is my table
definition:

> CREATE TABLE "TestSize"
> (
>   "Id" integer NOT NULL,
>   "Time" timestamp without time zone NOT NULL,
>   "Value" real NOT NULL,
>   "Status" smallint NOT NULL,
>   PRIMARY KEY ("Id", "Time")
> );

> CREATE INDEX test_index ON "TestSize" ("Id");

> With a completely empty table the database is 7 MB. After I insert 1 
> million records into the table the database is 121 MB. My 
> understanding is that each of the fields is sized as follows:

> integer - 4 bytes
> timestamp without time zone - 8 bytes
> real - 4 bytes
> smallint - 2 bytes

> So for 1 million records, it needs at least 18 million bytes, or ~17 
> MB to store the data. Now I'm sure there is extra space required for 
> managing the primary key fields, the index and other misc overhead 
> involved in getting this data into the internal storage format used by 
> PostgreSQL. But even if I triple the number of bytes stored for each 
> record, I only end up with 51 MB or so. Am I missing something obvious?

It doesn't sound that far out of line.  Postgres is not great with narrow
tables like this one :-(.  The factors you're not accounting for include:

* Alignment padding.  On a 64-bit machine those fields would occupy 24
bytes, not 18, because row widths are always going to be multiples of 8.

* Row header overhead, which is 28 bytes per row (24-byte row header plus
4-byte row pointer).

* That primary key index is going to need 16 data bytes per entry (alignment
again), plus an 8-byte index tuple header, plus a 4-byte row pointer.

* The other index similarly requires 8+8+4 bytes per row.

* Indexes tend not to be packed completely full.  If you load a PG btree in
exactly sequential order, the leaf pages should get packed to about the
index fillfactor (90% by default).  If you load in random order, ancient
wisdom is that the steady-state load factor for a btree is about 66%.

* There's some other inefficiencies from upper-level btree pages, page
header overhead, inability to split rows across pages, etc; though these
tend to not amount to much unless you have wide rows or wide index entries.

Given the above considerations, the *minimum* size of this table plus
indexes is 100 bytes/row.  Your observed result of 114 bytes/row suggests
you're getting an index load factor of around 80%, if I counted on my
fingers correctly.  That's not awful, but I'd guess that at least one of the
indexes is getting loaded nonsequentially.

You could REINDEX the indexes (possibly after playing with their fillfactor
settings) to improve their density.  But you're not going to be able to move
the needle by more than about 10% overall, so personally I wouldn't bother.

The long and the short of it is that Postgres is more oriented to OLTP-style
applications where access to and update of individual rows is the key
performance metric.  If you're dealing with historical data and mainly want
aggregated query results, it's possible you'd get better performance and
more-compact storage from a column-store database.

There's ongoing investigation into extending Postgres to support
column-style storage for better support of applications like that; but any
such feature is probably several years away, and it will not come without
performance compromises of its own.

regards, tom lane


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



-- 
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] Why is my database so big?

[Stephen Frost]

All,

* FarjadFarid(ChkNet) (farjad.fa...@checknetworks.com) wrote:
> Tom, thanks for your unbiased detailed response. 
> 
> Interesting post. 

Please don't top-post.  My comments are in-line, below.

> -Original Message-
> From: pgsql-general-ow...@postgresql.org
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tom Lane
> Sent: 22 February 2016 05:06
> To: Andrew Smith
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Why is my database so big?
> 
> Andrew Smith  writes:
> > I am setting up a proof of concept database to store some historical data.
> > Whilst I've used PostgreSQL a bit in the past this is the first time 
> > I've looked into disk usage due to the amount of data that could 
> > potentially be stored. I've done a quick test and I'm a little 
> > confused as to why it is occupying so much space on disk. Here is my table
> definition:
> 
> > CREATE TABLE "TestSize"
> > (
> >   "Id" integer NOT NULL,
> >   "Time" timestamp without time zone NOT NULL,
> >   "Value" real NOT NULL,
> >   "Status" smallint NOT NULL,
> >   PRIMARY KEY ("Id", "Time")
> > );
> 
> > CREATE INDEX test_index ON "TestSize" ("Id");

Note that you don't really need an index on "Id" because including a
primary key will automatically include an index on those fields, and an
index over ("Id", "Time") can be used to satisfy queries which have a
conditional on just the "Id" column.  Removing that extra index will
likely help with space issues.

> > With a completely empty table the database is 7 MB. After I insert 1 
> > million records into the table the database is 121 MB. My 
> > understanding is that each of the fields is sized as follows:
> 
> > integer - 4 bytes
> > timestamp without time zone - 8 bytes
> > real - 4 bytes
> > smallint - 2 bytes

I'd recommend against using timestamp w/o time zone.  For starters, as
noted, it's not actually saving you any space over timestamp w/ time
zone, and second, it makes working with that field painful and prone to
error.

> The long and the short of it is that Postgres is more oriented to OLTP-style
> applications where access to and update of individual rows is the key
> performance metric.  If you're dealing with historical data and mainly want
> aggregated query results, it's possible you'd get better performance and
> more-compact storage from a column-store database.
> 
> There's ongoing investigation into extending Postgres to support
> column-style storage for better support of applications like that; but any
> such feature is probably several years away, and it will not come without
> performance compromises of its own.

One approach to dealing with the PG per-row overhead is to use arrays
instead of rows, when you don't need that per-row visibility
information.  I've found that to be very successful for single-column
tables, but the technique may work reasonably well for other structures
also.

Thanks!

Stephen


signature.asc
Description: Digital signature

[GENERAL] Why does query planner choose slower BitmapAnd ?

[Seamus Abshere]

hi,

I don't understand why the query planner is choosing a BitmapAnd when an
Index Scan followed by a filter is obviously better.

(Note that "new_york_houses" is a view of table "houses" with one
condition on city - and there is an index idx_houses_city. That is the
Index Scan that I think it should use exclusively.)

Here's a fast query that uses the Index Scan followed by a filter:

> => explain analyze SELECT COUNT(DISTINCT id) FROM new_york_houses WHERE 
> roof_area >= 0 AND roof_area < 278.7091;
>   
>  QUERY PLAN
> ---
>  Aggregate  (cost=167298.10..167298.11 rows=1 width=16) (actual 
> time=141.137..141.137 rows=1 loops=1)
>->  Index Scan using idx_houses_city on households  (cost=0.57..167178.87 
> rows=47694 width=16) (actual time=0.045..105.953 rows=53971 loops=1)
>  Index Cond: (city = 'New York'::text)
>  Filter: ((roof_area >= 0) AND ((roof_area)::numeric < 278.7091))
>  Rows Removed by Filter: 101719
>  Planning time: 0.688 ms
>  Execution time: 141.250 ms
> (7 rows)

When I add another condition, "phoneable", however, it chooses an
obviously wrong plan:

> => explain analyze SELECT COUNT(DISTINCT id) FROM new_york_houses WHERE 
> roof_area >= 0 AND roof_area < 278.7091 AND phoneable = true;
>   
>QUERY PLAN
> ---
>  Aggregate  (cost=128163.05..128163.06 rows=1 width=16) (actual 
> time=4564.677..4564.677 rows=1 loops=1)
>->  Bitmap Heap Scan on households  (cost=105894.80..128147.78 rows=6106 
> width=16) (actual time=4456.690..4561.416 rows=5183 loops=1)
>  Recheck Cond: (city = 'New York'::text)
>  Filter: (phoneable AND (roof_area >= 0) AND ((roof_area)::numeric < 
> 278.7091))
>  Rows Removed by Filter: 40103
>  Heap Blocks: exact=14563
>  ->  BitmapAnd  (cost=105894.80..105894.80 rows=21002 width=0) 
> (actual time=4453.510..4453.510 rows=0 loops=1)
>->  Bitmap Index Scan on idx_houses_city  (cost=0.00..1666.90 
> rows=164044 width=0) (actual time=16.505..16.505 rows=155690 loops=1)
>  Index Cond: (city = 'New York'::text)
>->  Bitmap Index Scan on idx_houses_phoneable  
> (cost=0.00..104224.60 rows=10271471 width=0) (actual time=4384.461..4384.461 
> rows=10647041 loops=1)
>  Index Cond: (phoneable = true)
>  Planning time: 0.709 ms
>  Execution time: 4565.067 ms
> (13 rows)

On Postgres 9.4.4 with 244gb memory and SSDs

maintenance_work_mem 100
work_mem 50
random_page_cost 1
seq_page_cost 2

The "houses" table has been analyzed recently and has statistics set to
the max.

Thanks,
Seamus


-- 
Seamus Abshere, SCEA
https://github.com/seamusabshere
https://www.linkedin.com/in/seamusabshere


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

[GENERAL] bpchar, text and indexes

[Victor Yegorov]

Greetings.

I'd like to understand why Postgres behaves the way it does.
I was not able to find relevant mail thread myself, if one exists — please,
point at it.

Test setup:

PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7
20120313 (Red Hat 4.4.7-16), 64-bit

create table t(t_id int4, sn_c char(20));
insert into t select id,
chr((random()*26)::int4+65)||chr((random()*26)::int4+65)||((random()*9)::int4+1)
from generate_series(1, 1) id;
create index i_t_sn_c on t(sn_c);
vacuum analyze t;

Now, if I do a typical query, all is good:

postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c =
'AB1234';
  QUERY PLAN
---
 Index Only Scan using i_t_sn_c on t (actual time=0.015..0.015 rows=0
loops=1)
   Index Cond: (sn_c = 'AB1234'::bpchar)
   Heap Fetches: 0


If I explicitly cast constant to `text`, then Postgres will add
`(sn_c)::text` cast, which disables index:

postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c =
'AB1234'::text;
   QUERY PLAN
-
 Seq Scan on t (actual time=5.729..5.729 rows=0 loops=1)
   Filter: ((sn_c)::text = 'AB1234'::text)
   Rows Removed by Filter: 1


Although, if I will use LIKE instead of equality, then index is used:

postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c ~~
'AB1234'::text;
  QUERY PLAN
---
 Index Only Scan using i_t_sn_c on t (actual time=0.012..0.012 rows=0
loops=1)
   Index Cond: (sn_c = 'AB1234'::bpchar)
   Filter: (sn_c ~~ 'AB1234'::text)
   Heap Fetches: 0


And what I also see is — `varchar` has no such effect:

postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c =
'AB1234'::varchar;
  QUERY PLAN
---
 Index Only Scan using i_t_sn_c on t (actual time=0.041..0.041 rows=0
loops=1)
   Index Cond: (sn_c = 'AB1234'::bpchar)
   Heap Fetches: 0



My questions are:

1. according to `pg_cast`, `text` => `bpchar` is binary coercible. Why
Postgres is casting `sn_c` to `text` here, disabling index usage?

2. as I can see in `pg_cast`, setup for `varchar` is pretty much the same:
`varchar` => `bpchar` is also binary coercible. So why for `varchar`
behaviour is different?


Thanks in advance.


-- 
Victor Y. Yegorov

Re: [GENERAL] bpchar, text and indexes

[Thomas Kellerer]

Victor Yegorov schrieb am 22.02.2016 um 16:45:
> Test setup:
> 
> PostgreSQL 9.4.6 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 
> 20120313 (Red Hat 4.4.7-16), 64-bit
> 
> create table t(t_id int4, sn_c char(20));
> insert into t select id, 
> chr((random()*26)::int4+65)||chr((random()*26)::int4+65)||((random()*9)::int4+1)
>  from generate_series(1, 1) id;
> create index i_t_sn_c on t(sn_c);
> vacuum analyze t;
> 
> Now, if I do a typical query, all is good:
> 
> postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c = 
> 'AB1234';
>   QUERY PLAN
> ---
>  Index Only Scan using i_t_sn_c on t (actual time=0.015..0.015 rows=0 loops=1)
>Index Cond: (sn_c = 'AB1234'::bpchar)
>Heap Fetches: 0
> 
> 
> If I explicitly cast constant to `text`, then Postgres will add 
> `(sn_c)::text` cast, which disables index:
> 
> postgres=# EXPLAIN (analyze, costs off) SELECT sn_c FROM t WHERE sn_c = 
> 'AB1234'::text;
>QUERY PLAN
> -
>  Seq Scan on t (actual time=5.729..5.729 rows=0 loops=1)
>Filter: ((sn_c)::text = 'AB1234'::text)
>Rows Removed by Filter: 1
> 

I assume that this has to do with the fact that char(n) is blank padded to 20 
character. 

To be able to correctly compare that to a text value, sn_c has to be casted to 
text and then the index (which contains blank padded values) can not be used 
any more. 

Another very good example why the dreaded char() should not be used ;)

If you use varchar(20) instead of char(20) both queries yield the same 
execution plan (at least on my local 9.5)

Thomas




-- 
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] Why does query planner choose slower BitmapAnd ?

[Tom Lane]

Seamus Abshere  writes:
> I don't understand why the query planner is choosing a BitmapAnd when an
> Index Scan followed by a filter is obviously better.

> On Postgres 9.4.4 with 244gb memory and SSDs

> maintenance_work_mem 100
> work_mem 50
> random_page_cost 1
> seq_page_cost 2

[ squint... ]  There's no physically explainable situation where
random_page_cost should be less than seq_page_cost.  You may be
hitting a "garbage in, garbage out" situation with those numbers.

Given the large amount of RAM and the SSD underlying storage,
I'd set random_page_cost = seq_page_cost = 1.  You might also
find it advantageous to increase the CPU cost parameters a touch.
I've heard it reported that setting cpu_tuple_cost to something like
0.03 to 0.05 provides a better fit to modern hardware than the
default setting does.  In this particular case, though, it seems
like what you need to do is bump up cpu_index_tuple_cost a little
so as to make the indexscan on idx_houses_phoneable look more expensive.

(BTW, is that index really on just a boolean column?  It seems
unlikely that "phoneable" would be a sufficiently selective
condition to justify having an index on it.  I'd seriously consider
dropping that index as another solution approach.)

regards, tom lane


-- 
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] bpchar, text and indexes

[Victor Yegorov]

2016-02-22 18:00 GMT+02:00 Thomas Kellerer :

> I assume that this has to do with the fact that char(n) is blank padded to
> 20 character.
>

Yes, this was my thought too, but I do not understand what is going on in
details.


To be able to correctly compare that to a text value, sn_c has to be casted
> to text and then the index (which contains blank padded values) can not be
> used any more.
>

Well, for `varchar` type Postgres is able to do `varchar` -> `bpchar` cast
for my constant. I do not understand why for `text` it cannot and casts
column instead.


-- 
Victor Y. Yegorov

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

[Stephen Frost]

Tom, all,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Seamus Abshere  writes:
> > I don't understand why the query planner is choosing a BitmapAnd when an
> > Index Scan followed by a filter is obviously better.
> 
> > On Postgres 9.4.4 with 244gb memory and SSDs
> 
> > maintenance_work_mem 100
> > work_mem 50
> > random_page_cost 1
> > seq_page_cost 2
> 
> [ squint... ]  There's no physically explainable situation where
> random_page_cost should be less than seq_page_cost.  You may be
> hitting a "garbage in, garbage out" situation with those numbers.

Certainly agree with Tom on the above point.

> (BTW, is that index really on just a boolean column?  It seems
> unlikely that "phoneable" would be a sufficiently selective
> condition to justify having an index on it.  I'd seriously consider
> dropping that index as another solution approach.)

Also agreed here, but I've seen field evidence (with reasonable
configurations) that definitely shows that we're a bit too happy to go
with a BitmapAnd scan across two indexes where one returns an order of
magnitude (or less) pages to consider than the other and most of the
time we spend on the overall query is in going through the index to find
a bunch of pages we're just going to throw away when we do the AND.

In one specific case which I can recall offhand (having seen it quite
recently), there was a btree index and a gist index (PostGIS geometry)
where the btree index pulled back perhaps 100k rows but the gist index
returned nearly everything (the bounding box included in the query
covering almost the entire table).  Dropping the gist index greatly
improved *that* query, but, of course, destroyed the performance of more
selective queries bounding box queries which didn't include a constraint
on the column with the btree index (forcing a sequential scan of the
table).

I've not looked into the specific costing here to see why the BitmapAnd
ended up being chosen over just doing an index scan with the btree and
then filtering, but I do believe it to be a problem area that would be
good to try and improve.  The first question is probably- are we
properly accounting for the cost of scanning the index vs the cost of
scanning one index and then applying the filter?

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

[Tom Lane]

Stephen Frost  writes:
> I've not looked into the specific costing here to see why the BitmapAnd
> ended up being chosen over just doing an index scan with the btree and
> then filtering, but I do believe it to be a problem area that would be
> good to try and improve.  The first question is probably- are we
> properly accounting for the cost of scanning the index vs the cost of
> scanning one index and then applying the filter?

We are costing it out in what seems a sane way to me.  In the given
example the "bad" plan is estimated at just slightly cheaper than what
(I assume) the "good" plan is.  I'm inclined to think this represents a
failure to choose good cost parameters for the installation.

Given how remarkably quick the single-index scan is, I also wonder if
that index is fully cached while we had to read some of the other index
from kernel or SSD.  Relative cache states of different indexes is a
problem the planner doesn't currently try to deal with; it's possible
that that could bias it towards trying to AND a large-but-not-fully-cached
index with a smaller-and-fully-cached-one, when not bothering with the
larger index would in fact be better.  You might be able to counter that
to some extent by reducing effective_cache_size, though possibly that
cure is worse than the disease.

regards, tom lane


-- 
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] Why does query planner choose slower BitmapAnd ?

[Seamus Abshere]

On Mon, Feb 22, 2016, at 01:48 PM, Tom Lane wrote:
> Given how remarkably quick the single-index scan is, I also wonder if that 
> index is fully cached while we had to read some of the other index from 
> kernel or SSD.

This makes sense, except that the speed of the query is the same if I
run it many times in a row. Shouldn't the partially-cached index get
loaded fully by the second query?

On Mon, Feb 22, 2016, at 01:20 PM, Stephen Frost wrote:
> The first question is probably- are we properly accounting for the cost of 
> scanning the index vs the cost of scanning one index and then applying the 
> filter?

I can affect the query planner's cost estimates with random_page_cost
(only), but I still can't get it to avoid the BitmapAnd - probably
because I am affecting other cost estimates in the same proportion.

No change with original settings OR cpu_tuple_cost=10 OR
seq_page_cost=10 OR (cpu_tuple_cost=0.05, seq_page_cost=1,
random_page_cost=1)

> ->  BitmapAnd  (cost=105894.80..105894.80 rows=21002 width=0) (actual 
> time=4859.397..4859.397 rows=0 loops=1)
>   ->  Bitmap Index Scan on idx_houses_city  (cost=0.00..1666.90 rows=164044 
> width=0) (actual time=16.098..16.098 rows=155690 loops=1)
> Index Cond: (city = 'New York'::text)
>   ->  Bitmap Index Scan on idx_houses_phoneable  (cost=0.00..104224.60 
> rows=10271471 width=0) (actual time=4771.520..4771.520 rows=10647041 loops=1)
> Index Cond: (phoneable = true)

However with random_page_cost=10 (hint: cost estimates go up by 4x or
so)

> ->  BitmapAnd  (cost=354510.80..354510.80 rows=21002 width=0) (actual 
> time=4603.575..4603.575 rows=0 loops=1)
>   ->  Bitmap Index Scan on idx_houses_city  (cost=0.00..5590.90 rows=164044 
> width=0) (actual time=16.529..16.529 rows=155690 loops=1)
> Index Cond: (city = 'New York'::text)
>   ->  Bitmap Index Scan on idx_houses_phoneable  (cost=0.00..348916.60 
> rows=10271471 width=0) (actual time=4530.424..4530.424 rows=10647041 loops=1)
> Index Cond: (phoneable = true)

I think this is why we originally set random_page_cost so "low"... it
was our way of "forcing" more index usage (we have a big, wide table).

Is there any other way to differentiate the 2 index scans? FWIW, 10% of
houses are phoneable, 0.2% are in the city. (Maybe I'm just supposed to
drop the index like Tom said.)

Best, thanks,
Seamus

-- 
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere


-- 
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] bpchar, text and indexes

[Tom Lane]

Victor Yegorov  writes:
> Well, for `varchar` type Postgres is able to do `varchar` -> `bpchar` cast
> for my constant. I do not understand why for `text` it cannot and casts
> column instead.

In cross-type comparisons like these, the parser basically has a choice
between whether to apply texteq or bpchareq.  It's going to have to cast
one side or the other either way.  It prefers to cast to text, not away
from text, because text is the preferred type in the string category.
So "bpchar = text" is resolved as texteq.

In the "bpchar = varchar" case, that doesn't happen because neither side
is text to start with, so bpchareq is chosen on the grounds of requiring
fewer casts.

There's no varchareq operator (because varchar just relies on text's
operators).  If there were, you'd probably get an "ambiguous operator,
please cast" error from "bpchar = varchar", because neither of those are
preferred types so there would be no way to prefer one interpretation
over the other.  Similarly, if text weren't treated as a preferred type
then "bpchar = text" would just fail, it would still not be resolved
the way you want.

See
http://www.postgresql.org/docs/9.5/static/typeconv-oper.html
for a more detailed explanation.

regards, tom lane


-- 
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] Why does query planner choose slower BitmapAnd ?

[Stephen Frost]

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost  writes:
> > I've not looked into the specific costing here to see why the BitmapAnd
> > ended up being chosen over just doing an index scan with the btree and
> > then filtering, but I do believe it to be a problem area that would be
> > good to try and improve.  The first question is probably- are we
> > properly accounting for the cost of scanning the index vs the cost of
> > scanning one index and then applying the filter?
> 
> We are costing it out in what seems a sane way to me.  In the given
> example the "bad" plan is estimated at just slightly cheaper than what
> (I assume) the "good" plan is.  I'm inclined to think this represents a
> failure to choose good cost parameters for the installation.

I'll try and get an opportunity to review the numbers for the case which
I ran across previously.

> Given how remarkably quick the single-index scan is, I also wonder if
> that index is fully cached while we had to read some of the other index
> from kernel or SSD.  Relative cache states of different indexes is a
> problem the planner doesn't currently try to deal with; it's possible
> that that could bias it towards trying to AND a large-but-not-fully-cached
> index with a smaller-and-fully-cached-one, when not bothering with the
> larger index would in fact be better.  You might be able to counter that
> to some extent by reducing effective_cache_size, though possibly that
> cure is worse than the disease.

Unfortunately, this doesn't actually hold water for the case which I ran
into as this was across multiple repeated invocations, where both indexes
were fully cached.  It was simply much more expensive to scan the entire
GIST index (which wasn't small) than to fetch and filter the records
returned from the btree index.

I could see possibly adjusting the costing of the bounding box operator
to be lower, to lower the overall cost of the plan to use the btree
index and then filter as compared to the BitmapAnd scan, but the actual
run-times weren't even close, as I recall (something like 45-60s vs.
less than a second), which makes me wonder about there being a missing
costing somewhere.  The number of records returned from the indexes was
similar to the case presented here- 100k records from one, 10m+ from the
other.

I'm just wondering how we manage to not realize that scanning through
gigabytes of index pages is going to be more expensive than running an
operator comparison across 100k records.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Why does query planner choose slower BitmapAnd ?

[Tom Lane]

Stephen Frost  writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> Given how remarkably quick the single-index scan is, I also wonder if
>> that index is fully cached while we had to read some of the other index
>> from kernel or SSD.

> Unfortunately, this doesn't actually hold water for the case which I ran
> into as this was across multiple repeated invocations, where both indexes
> were fully cached.  It was simply much more expensive to scan the entire
> GIST index (which wasn't small) than to fetch and filter the records
> returned from the btree index.

Well, I think the main problem in the case you are describing is a bad
estimate of how much of the GIST index needs to be examined, which is
something that needs to be fixed in gistcostestimate or operator-specific
selectivity estimates, not in choose_bitmap_and.  In Seamus' example it
seems that none of the rowcount estimates are unduly far off, so I don't
think he had an estimation failure of the same kind.

> I'm just wondering how we manage to not realize that scanning through
> gigabytes of index pages is going to be more expensive than running an
> operator comparison across 100k records.

IOW, almost certainly we *don't* realize that the query will involve
scanning through gigabytes of index pages.  But btree indexes are much
simpler and easier to make that estimate for than GIST indexes are.

regards, tom lane


-- 
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] Why does query planner choose slower BitmapAnd ?

[Seamus Abshere]

On Mon, Feb 22, 2016, at 02:14 PM, Tom Lane wrote:
> IOW, almost certainly we *don't* realize that the query will involve scanning 
> through gigabytes of index pages.  But btree indexes are much simpler and 
> easier to make that estimate for...

Isn't this the crux of my issue, at least?


-- 
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere


-- 
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] Why does query planner choose slower BitmapAnd ?

[Tom Lane]

Seamus Abshere  writes:
> On Mon, Feb 22, 2016, at 02:14 PM, Tom Lane wrote:
>> IOW, almost certainly we *don't* realize that the query will involve 
>> scanning through gigabytes of index pages.  But btree indexes are much 
>> simpler and easier to make that estimate for...

> Isn't this the crux of my issue, at least?

Not unless you are using a GIST index for booleans, which I sure hope you
ain't.

regards, tom lane


-- 
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] Why does query planner choose slower BitmapAnd ?

[Tom Lane]

Seamus Abshere  writes:
> Is there any other way to differentiate the 2 index scans? FWIW, 10% of
> houses are phoneable, 0.2% are in the city. (Maybe I'm just supposed to
> drop the index like Tom said.)

Hm.  10% is above the threshold where I'd usually think that an indexscan
could beat a seqscan, so dropping the "phoneable" index is definitely
something you should consider, especially if updates on this table are
frequent (because you're paying to update the index too).

However, I'd still counsel fooling with the cpu cost parameters first.
Alternatively, you could leave those at defaults and set random_page_cost
and seq_page_cost to 0.1 to 0.5 or so, which would net out to the same
effect (charging more for CPU relative to I/O) though with different
absolute cost numbers.

regards, tom lane


-- 
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] Why does query planner choose slower BitmapAnd ?

[Jeff Janes]

On Mon, Feb 22, 2016 at 8:20 AM, Stephen Frost  wrote:
>
> Also agreed here, but I've seen field evidence (with reasonable
> configurations) that definitely shows that we're a bit too happy to go
> with a BitmapAnd scan across two indexes where one returns an order of
> magnitude (or less) pages to consider than the other and most of the
> time we spend on the overall query is in going through the index to find
> a bunch of pages we're just going to throw away when we do the AND.
>
> In one specific case which I can recall offhand (having seen it quite
> recently), there was a btree index and a gist index (PostGIS geometry)
> where the btree index pulled back perhaps 100k rows but the gist index
> returned nearly everything (the bounding box included in the query
> covering almost the entire table).  Dropping the gist index greatly
> improved *that* query, but, of course, destroyed the performance of more
> selective queries bounding box queries which didn't include a constraint
> on the column with the btree index (forcing a sequential scan of the
> table).
>
> I've not looked into the specific costing here to see why the BitmapAnd
> ended up being chosen over just doing an index scan with the btree and
> then filtering, but I do believe it to be a problem area that would be
> good to try and improve.  The first question is probably- are we
> properly accounting for the cost of scanning the index vs the cost of
> scanning one index and then applying the filter?

I looked into this before as well, and I think it is vastly
underestimating the cost of adding a bit into the bitmap, near this
comment:

/*
 * Charge a small amount per retrieved tuple to reflect the costs of
 * manipulating the bitmap.  This is mostly to make sure that a bitmap
 * scan doesn't look to be the same cost as an indexscan to retrieve a
 * single tuple.
 */

It charges 0.1 CPU_operator_cost, while reality seemed to be more like
6 CPU_operator_cost.

The assumption seems to be that this estimate doesn't need to be
accurate, because the cost estimate when the bitmap is *used* will
make up for it.  But when ANDing together bitmaps of very unequal
size, that doesn't happen.

I've been wanting to revisit this in the 9.6 code base, but can't find
my code and notes, so this all from memory.

Cheers,

Jeff


-- 
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] Why does query planner choose slower BitmapAnd ?

[Seamus Abshere]

On Mon, Feb 22, 2016, at 02:30 PM, Jeff Janes wrote:
> It charges 0.1 CPU_operator_cost, while reality seemed to be more like 6 
> CPU_operator_cost.

fdy=> select name, setting, boot_val from pg_settings where name ~
'cpu';
 name | setting | boot_val
--+-+--
 cpu_index_tuple_cost | 0.005   | 0.005
 cpu_operator_cost| 0.0025  | 0.0025
 cpu_tuple_cost   | 0.01| 0.01
(3 rows)

Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It
"fixed" my problem by preventing the BitmapAnd.

Is this dangerous?


-- 
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] Why does query planner choose slower BitmapAnd ?

[Tom Lane]

Jeff Janes  writes:
> I looked into this before as well, and I think it is vastly
> underestimating the cost of adding a bit into the bitmap, near this
> comment:

> /*
>  * Charge a small amount per retrieved tuple to reflect the costs of
>  * manipulating the bitmap.  This is mostly to make sure that a bitmap
>  * scan doesn't look to be the same cost as an indexscan to retrieve a
>  * single tuple.
>  */

> It charges 0.1 CPU_operator_cost, while reality seemed to be more like
> 6 CPU_operator_cost.

That sounds *awfully* high.  I don't have any problem with the idea that
that number is off, but I'd want to see some evidence before bumping it
by a factor of 60.

The general assumption here is that most of the per-tuple costs ought to
be reflected in cpu_tuple_cost and cpu_index_tuple_cost.  This addition is
meant to reflect the extra cost of going through a bitmap rather than
just fetching the tuple directly.  That extra cost is certainly not zero,
but it seems to me that it ought to be fairly small relative to the other
processing costs of index and heap fetch.  With the default cpu_xxx_costs,
what you suggest here would mean charging twice as much CPU per-tuple for
a bitmap scan as for a plain index scan, and that doesn't sound right.
(Or if it is right, maybe we have a performance bug in tidbitmap.c.)

[ thinks for a bit... ]  Another thought to look into is that I don't
think the planner worries about the bitmap becoming "lossy", which would
result in many more heap tuple checks than it's predicting.  It might be
that we need to model that effect.  I don't think it's at play in Seamus'
example, given the large work_mem he's using, but maybe it explains your
results?

regards, tom lane


-- 
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] Why does query planner choose slower BitmapAnd ?

[Tom Lane]

Seamus Abshere  writes:
> Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It
> "fixed" my problem by preventing the BitmapAnd.

> Is this dangerous?

Use a gentle tap, man, don't swing the hammer with quite so much abandon.
I'd have tried doubling the setting to start with.  Raising it 20X might
cause other queries to change behavior undesirably.

regards, tom lane


-- 
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] Why does query planner choose slower BitmapAnd ?

[Seamus Abshere]

On Mon, Feb 22, 2016, at 02:49 PM, Tom Lane wrote:
> Seamus Abshere  writes:
> > Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It 
> > "fixed" my problem by preventing the BitmapAnd.
> > Is this dangerous?
> 
> Use a gentle tap, man, don't swing the hammer with quite so much abandon.
> I'd have tried doubling the setting to start with.  Raising it 20X might
> cause other queries to change behavior undesirably.

Doubling it was enough :)


-- 
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] Why does query planner choose slower BitmapAnd ?

[Seamus Abshere]

On Mon, Feb 22, 2016, at 02:53 PM, Seamus Abshere wrote:
> On Mon, Feb 22, 2016, at 02:49 PM, Tom Lane wrote:
> > Seamus Abshere  writes:
> > > Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It 
> > > "fixed" my problem by preventing the BitmapAnd.
> > > Is this dangerous?
> > 
> > Use a gentle tap, man, don't swing the hammer with quite so much abandon.
> > I'd have tried doubling the setting to start with.  Raising it 20X might
> > cause other queries to change behavior undesirably.
> 
> Doubling it was enough :)

 name | setting | boot_val
--+-+--
 cpu_index_tuple_cost | 0.09| 0.005   <- 18x boot val, 9x
 cpu_tuple_cost
 cpu_operator_cost| 0.0025  | 0.0025
 cpu_tuple_cost   | 0.01| 0.01
 
In the end I'm back to the big hammer.

I found that larger cities (e.g., more results from the city index)
required a larger cpu_index_tuple_cost to prevent the BitmapAnd.

Now cpu_index_tuple_cost is set to 0.09, which is 18x its boot_val and
9x cpu_tuple_cost... which seems strange.

Logically, should I be changing cpu_operator_cost instead?


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

[GENERAL] decoding BLOB's

[CS DBA]

Hi All;

we've found a post about dumping blobs:
/
/

/I wrote: [fanlijing wants to write bytea to file] > A simple > COPY 
(SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT binary) > 
should do the trick. Corrections: a) "binary" must be surrounded by 
single quotes. b) *that won't dump just the binary data - you would have 
to remove the first 25 bytes and the last 2 bytes...* So maybe using the 
functions I mentioned would be the best way after all. You could also 
write your own user defined function in C. /




Can someone point me in the right direction per how I would remove the 
first 25 bytes and the last 2 bytes from a bytea column?


Thanks in advance

Re: [GENERAL] decoding BLOB's

[Joshua D. Drake]

On 02/22/2016 11:08 AM, CS DBA wrote:

Hi All;

we've found a post about dumping blobs:
/
/

/I wrote: [fanlijing wants to write bytea to file] > A simple > COPY
(SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT binary) >
should do the trick. Corrections: a) "binary" must be surrounded by
single quotes. b) *that won't dump just the binary data - you would have
to remove the first 25 bytes and the last 2 bytes...* So maybe using the
functions I mentioned would be the best way after all. You could also
write your own user defined function in C. /



Can someone point me in the right direction per how I would remove the
first 25 bytes and the last 2 bytes from a bytea column?


http://www.postgresql.org/docs/9.3/static/functions-binarystring.html

Substring might do it for you.

JD



Thanks in advance





--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
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] Why does query planner choose slower BitmapAnd ?

[Stephen Frost]

* Seamus Abshere (sea...@abshere.net) wrote:
> Is there any other way to differentiate the 2 index scans? FWIW, 10% of
> houses are phoneable, 0.2% are in the city. (Maybe I'm just supposed to
> drop the index like Tom said.)

Have to admit that I continue to be interested in this as it might
relate to the somewhat similar (or, at least, seems to be, to me) case
that I ran into before.

What might be interesting would be to see a run with:

explain (analyze, verbose, buffers) select ...

for both of the original queries.  It'd also be nice to know what the
size is of each of the indexes involved.

Last, but perhaps not least, have you considered using a partial index
and only indexing where phoneable is actually true?  That would remove
the need to update the index for the case where phoneable is false and
would make the index smaller.  I don't know that it'd really change
what's going on here, though if it did, that would be interesting too.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] decoding BLOB's

[John R Pierce]

On 2/22/2016 11:23 AM, Joshua D. Drake wrote:

Can someone point me in the right direction per how I would remove the
first 25 bytes and the last 2 bytes from a bytea column?


http://www.postgresql.org/docs/9.3/static/functions-binarystring.html

Substring might do it for you. 


won't doing it in SQL still result in a BYTEA result which will be 
wrapped when dumped via COPY ?


instead, I think this needs to be done externally to sql, like by piping 
the resulting file through something like ...


   tail -c +25 infile.dat | head -c -2 > outfile.dat

or doing something in perl or whatever.

--
john r pierce, recycling bits in santa cruz

[GENERAL] Read-only tables to avoid row visibility check

[Seamus Abshere]

hi,

https://wiki.postgresql.org/wiki/ReadOnlyTables mentions the possibility
of `ALTER TABLE table SET READ ONLY`.

Would this mean that row visibility checks could be skipped and thus
index-only scans much more common?

Thanks,
Seamus

-- 
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere


-- 
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] decoding BLOB's

[Chris Mair]

Can someone point me in the right direction per how I would remove the
first 25 bytes and the last 2 bytes from a bytea column?


http://www.postgresql.org/docs/9.3/static/functions-binarystring.html

Substring might do it for you.


won't doing it in SQL still result in a BYTEA result which will be wrapped when 
dumped via COPY ?

instead, I think this needs to be done externally to sql, like by piping the 
resulting file through something like ...

tail -c +25 infile.dat | head -c -2 > outfile.dat

or doing something in perl or whatever.


Hi,

I think it's easier if you go via base64 and then use the shell command 
"base64" to
decode it back to binary.

I start from this sample blob with 5 bytes (values 0, 1, 2, 4, 5), just a sample
(I actually failed to count to 4 correctly ;):

chris=# select * from t;
 id | blob
+--
  1 | \x0001020405
(1 row)

In the shell now I can do:

chris$ psql -A -t -c "select encode(blob, 'base64') from t where id = 1"
AAECBAU=

To save this in binary I add base64 -d (Linux) or base64 -D (OS X, FreeBSD?):

chris$ psql -A -t -c "select encode(blob, 'base64') from t where id = 1" | base64 
-D > blob.dat

blob.dat is now the 5 bytes, nothing else:

chris$ od -tx1 blob.dat
00000  01  02  04  05
005

Bye,
Chris.





--
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] Why does query planner choose slower BitmapAnd ?

[Alban Hertroys]

> On 22 Feb 2016, at 16:58, Tom Lane  wrote:
> 
> (BTW, is that index really on just a boolean column?  It seems
> unlikely that "phoneable" would be a sufficiently selective
> condition to justify having an index on it.  I'd seriously consider
> dropping that index as another solution approach.)

On that train of thought, I would think that a person or company would only be 
phoneable if they have a phone number registered somewhere. That somewhere 
probably being in another table that's too far away from the current table to 
check it straight away - so this is an optimisation, right?

Where I see that going is as follows: A "contact" either has a phone number - 
in which case you'd probably rather get that phone number - or they don't, in 
which case a null value is often sufficient[1].
While a phone number certainly takes up more storage than a boolean, it 
wouldn't require an index (because it's available right there) nor the extra 
joins to look up the actual phone number. And if you'd still want to put an 
index on it, the null values won't be indexed, which takes a bit off the burden 
of the larger field size.

You _could_ also take a shortcut and use a variation of your current approach 
by storing null instead of false for phoneable, but then your index would 
contain nothing but true values which rather defeats the point of having an 
index.

Query-wise, I suspect that the number of "contacts" that have a phone number 
far outweighs the number that doesn't, in which case it's more efficient to 
query for those that don't have one (fewer index hits) and eliminate those from 
the results than the other way around. In my experience, both the NOT EXISTS 
and the LEFT JOIN + WHERE phoneable IS NULL tend to perform better.

A final variation on the above would be to have a conditional index on your PK 
for those "contacts" that are NOT phoneable. That's probably the shortest and 
quickest list to query. I'd still prefer that field to contain something a bit 
more meaningful though...

Well, enough of my rambling!

Ad 1. It is possible that you cater for the possibility that you don't know 
whether a "contact" has a phone number or not, in which case null would 
probably be the wrong choice for "no phone number" because then you wouldn't be 
able to distinguish between "no phone number" and "I don't know".

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



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

[GENERAL] ERROR: cannot convert relation containing dropped columns to view

[Nicklas Avén]

Hello

I get this error message :
ERROR:  cannot convert relation containing dropped columns to view

I have googled, but find only very old posts that doesn't seem to be the
same situation.

What I have done is that I have a quite big table that I added a column
to for deletion time.

Then I droped that column and added it again with the right type.

After that I cannot create a rule that is returning data.

The reason I have to return data is irrelevant here, but PostgREST
expects that.

To reproduce:
create table foo
(
id serial,
deleted int
);

alter table foo drop column deleted;
alter table foo add column deleted timestamp;

CREATE or replace RULE del_post AS ON DELETE TO foo
DO INSTEAD
update foo set deleted = now()
WHERE id = OLD.id
returning *;

returns:
ERROR:  cannot convert relation containing dropped columns to view


If I don't drop any column (adding the right type at once) it works as
expected.

two questions:
1) is this a bug
2) is there a way to "cean" the table from the deleted columns without
recreating it?

Best Regards

Nicklas Avén




-- 
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] Read-only tables to avoid row visibility check

[Melvin Davidson]

First of all, it would be really nice if you mentioned the version of
PostgreSQL and O/S when posing questions.

That being said, that wiki is a _discussion_, and as such, a suggestion on
how it "might" be implemented.

However, at this time, there is no such option as SET READ ONLY in any
version of PostgreSQL.

The best you can do is revoke INSERT, UPDATE & DELETE from public and all
specific granted users.

On Mon, Feb 22, 2016 at 3:35 PM, Seamus Abshere  wrote:

> hi,
>
> https://wiki.postgresql.org/wiki/ReadOnlyTables mentions the possibility
> of `ALTER TABLE table SET READ ONLY`.
>
> Would this mean that row visibility checks could be skipped and thus
> index-only scans much more common?
>
> Thanks,
> Seamus
>
> --
> Seamus Abshere, SCEA
> +598 99 54 99 54
> https://github.com/seamusabshere
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



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

Re: [GENERAL] Read-only tables to avoid row visibility check

[Seamus Abshere]

On Mon, Feb 22, 2016, at 06:30 PM, Melvin Davidson wrote:
> However, at this time, there is no such option as SET READ ONLY in any 
> version of PostgreSQL.

I know.

I am wondering if hypothetical read-only tables would make index-only
scans more possible by avoiding the need for row visibility checks.


-- 
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere


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

[GENERAL] synch streaming replication question

[John Wiencek]

Hi All

I have a three node cluster using streaming replication configured as
follows:

One synch node and one asynch node.


If my synch node is down the master node is hung until that node is brought
back on line.  

Two questions:

1.  Is this the expected action on my master?  It makes sense if it is since
the master is waiting for an ack from the synch node.

2. Is there anyway to configure my cluster so I have a synch node but not
have my master node ³HANG² if the synch node is down?



Regards

John  Wiencek

Re: [GENERAL] Read-only tables to avoid row visibility check

[David G. Johnston]

On Mon, Feb 22, 2016 at 2:38 PM, Seamus Abshere  wrote:

> On Mon, Feb 22, 2016, at 06:30 PM, Melvin Davidson wrote:
> > However, at this time, there is no such option as SET READ ONLY in any
> version of PostgreSQL.
>
> I know.
>
> I am wondering if hypothetical read-only tables would make index-only
> scans more possible by avoiding the need for row visibility checks.


​If the system is working properly then a READ ONLY table in fact should be
able to use Index Only Scans without the hack of a DBA telling it that said
table is READ ONLY.​  The presence or absence of such an attribute should
not impact that particular optimization.  Corner cases such as, "it was
READ ONLY so I made it writable, wrote to it, then changed it back to READ
ONLY", have not been considered here.

​Since the answer to your actual question would be "it depends on the
implementation" it would probably be more constructive to actually
communicate the thoughts that provoked the question.

David J.​

[GENERAL] Get the date of creation of objects in the database

[Edson F. Lidorio]

Hello,

How to get the date of creation of objects in batabase?
For example: The date of creation of tables and trigger.

Thank you;

Edson


--
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] Get the date of creation of objects in the database

[Adrian Klaver]

On 02/22/2016 02:00 PM, Edson F. Lidorio wrote:

Hello,

How to get the date of creation of objects in batabase?
For example: The date of creation of tables and trigger.


AFAIK that is not recorded by default. If you are using Postgres 9.3+ 
you could roll your own audit system, going forward, using event triggers:


http://www.postgresql.org/docs/9.5/interactive/event-triggers.html

Take a look at matrix below for what is included and not included:

http://www.postgresql.org/docs/9.5/interactive/event-trigger-matrix.html

PL/pgSQL example:

http://www.postgresql.org/docs/9.5/interactive/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER



Thank you;

Edson





--
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] Get the date of creation of objects in the database

[David G. Johnston]

On Mon, Feb 22, 2016 at 3:00 PM, Edson F. Lidorio 
wrote:

> Hello,
>
> How to get the date of creation of objects in batabase?
> For example: The date of creation of tables and trigger.
>

​The only source of data for that question is the local filesystem.  If
that is acceptable you can find examples online provided to others who have
asked this question.

If you want something internal to the database the answer is no.  Those
same searches will turn up the various reasons why such a feature has not
gotten any strong traction from the developers.

This was my search query:  "postgresql object creation time"

David J.

Re: [GENERAL] ERROR: cannot convert relation containing dropped columns to view

[Tom Lane]

Nicklas =?ISO-8859-1?Q?Av=E9n?=  writes:
> create table foo
> (
> id serial,
> deleted int
> );

> alter table foo drop column deleted;
> alter table foo add column deleted timestamp;

> CREATE or replace RULE del_post AS ON DELETE TO foo
> DO INSTEAD
> update foo set deleted = now()
> WHERE id = OLD.id
> returning *;

> returns:
> ERROR:  cannot convert relation containing dropped columns to view

Hmm.

> 1) is this a bug

Well, it's an unimplemented feature anyway.  The reason the error message
is like that seems to be that it was correct (that is, that was the only
possible case) when it was introduced, which was in the 2002 patch that
implemented DROP COLUMN to begin with:
 
+/*
+ * Disallow dropped columns in the relation.  This won't happen
+ * in the cases we actually care about (namely creating a view
+ * via CREATE TABLE then CREATE RULE).  Trying to cope with it
+ * is much more trouble than it's worth, because we'd have to
+ * modify the rule to insert dummy NULLs at the right positions.
+ */
+if (attr->attisdropped)
+elog(ERROR, "cannot convert relation containing dropped 
columns to view");

When we made rules with RETURNING go through this logic, in 2006, we
don't seem to have revisited the message text, much less thought about
whether we needed to take "more trouble" about dealing with dropped
columns in a real table.

I'm not sure how hard it would be to support the case.  Given that yours
is the first complaint in ten years, and that rules in general are pretty
out of favor, it's probably not going to be very high on the to-do list.
My own inclination would just be to provide a more on-point error message
for this case.

> 2) is there a way to "cean" the table from the deleted columns without
> recreating it?

Nope, sorry.

What I'd suggest is that you consider implementing this behavior without
using rules.  Instead, what you want is something like

create view visible_foo as
  select  from foo where deleted is null;

plus INSTEAD OF triggers that redirect inserts/updates/deletes from
visible_foo to foo.  This way is likely to perform better than a rule
and have less-surprising semantics in corner cases.

regards, tom lane


-- 
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] Get the date of creation of objects in the database

[Zlatko Asenov]

You may find useful a SIEM to record activity like this. 

-Original Message-
From: "David G. Johnston" 
Sent: ‎2/‎23/‎2016 0:14
To: "Edson F. Lidorio" 
Cc: "pgsql-general" 
Subject: Re: [GENERAL] Get the date of creation of objects in the database

On Mon, Feb 22, 2016 at 3:00 PM, Edson F. Lidorio  wrote:

Hello,

How to get the date of creation of objects in batabase?
For example: The date of creation of tables and trigger.



​The only source of data for that question is the local filesystem.  If that is 
acceptable you can find examples online provided to others who have asked this 
question.


If you want something internal to the database the answer is no.  Those same 
searches will turn up the various reasons why such a feature has not gotten any 
strong traction from the developers.


This was my search query:  "postgresql object creation time"


David J.

Re: [GENERAL] Read-only tables to avoid row visibility check

[Seamus Abshere]

On Mon, Feb 22, 2016, at 06:48 PM, David G. Johnston wrote:
> it would probably be more constructive to actually communicate the thoughts 
> that provoked the question.

My company has a largish table - 250+ columns, 1 row for every household
in the US. It's read-only. We've gotten advice to convert to a column
store (cstore_fdw, etc.) but we would love to just stay with
tried-and-true postgres tables. Plus, many of our queries are against
dozens of columns at once.

Being able to tell postgres that our table is "Read Only" has imaginary
mystical properties for me, first and foremost being able to count
against indexes without ever hitting the disk.

> ​If the system is working properly then a READ ONLY table in fact should be 
> able to use Index Only Scans without the hack of a DBA telling it that said 
> table is READ ONLY.​

So this should happen already?


-- 
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] ERROR: cannot convert relation containing dropped columns to view

[Nicklas Aven]

 Tom Lane skrev 

> Nicklas =?ISO-8859-1?Q?Av=E9n?=  writes:
> > create table foo
> > (
> > id serial,
> > deleted int
> > );
> 
> > alter table foo drop column deleted;
> > alter table foo add column deleted timestamp;
> 
> > CREATE or replace RULE del_post AS ON DELETE TO foo
> > DO INSTEAD
> > update foo set deleted = now()
> > WHERE id = OLD.id
> > returning *;
> 
> > returns:
> > ERROR:  cannot convert relation containing dropped columns to view
> 
> Hmm.
> 
> > 1) is this a bug
> 
> Well, it's an unimplemented feature anyway.  The reason the error message
> is like that seems to be that it was correct (that is, that was the only
> possible case) when it was introduced, which was in the 2002 patch that
> implemented DROP COLUMN to begin with:
>  
> +/*
> + * Disallow dropped columns in the relation.  This won't happen
> + * in the cases we actually care about (namely creating a view
> + * via CREATE TABLE then CREATE RULE).  Trying to cope with it
> + * is much more trouble than it's worth, because we'd have to
> + * modify the rule to insert dummy NULLs at the right positions.
> + */
> +if (attr->attisdropped)
> +elog(ERROR, "cannot convert relation containing dropped 
> columns to view");
> 
> When we made rules with RETURNING go through this logic, in 2006, we
> don't seem to have revisited the message text, much less thought about
> whether we needed to take "more trouble" about dealing with dropped
> columns in a real table.
> 
> I'm not sure how hard it would be to support the case.  Given that yours
> is the first complaint in ten years, and that rules in general are pretty
> out of favor, it's probably not going to be very high on the to-do list.
> My own inclination would just be to provide a more on-point error message
> for this case.
> 
> > 2) is there a way to "cean" the table from the deleted columns without
> > recreating it?
> 
> Nope, sorry.
> 
> What I'd suggest is that you consider implementing this behavior without
> using rules.  Instead, what you want is something like
> 
> create view visible_foo as
>   select  from foo where deleted is null;
> 
> plus INSTEAD OF triggers that redirect inserts/updates/deletes from
> visible_foo to foo.  This way is likely to perform better than a rule
> and have less-surprising semantics in corner cases.
> 
>   regards, tom lane


Ok, thank you.
I think you are right about putting this logic on the view instead. I had my 
reasons for going the rule path, but as you say there is reasons for not do 
that too.

Thanks a lot for very fast reponse!

Best Regards
Nicklas Avén

Re: [GENERAL] Read-only tables to avoid row visibility check

[Merlin Moncure]

On Mon, Feb 22, 2016 at 2:35 PM, Seamus Abshere  wrote:
> hi,
>
> https://wiki.postgresql.org/wiki/ReadOnlyTables mentions the possibility
> of `ALTER TABLE table SET READ ONLY`.
>
> Would this mean that row visibility checks could be skipped and thus
> index-only scans much more common?

Personally I don't see how that buys you very much.  Right now you can
VACUUM the table which will update the visibility map, allowing index
only scans to be chosen.  Visibility checks are also already optimized
away by the database over time via hint bits.

I think the optimization you are looking for is to have the database
exploit the fact that when the table is created and/or loaded in a
single transaction, it marks everything visible and valid by default
and then wipes it all away should the insert fail.

merlin


-- 
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] Get the date of creation of objects in the database

[Melvin Davidson]

On Mon, Feb 22, 2016 at 5:18 PM, Zlatko Asenov 
wrote:

> You may find useful a SIEM to record activity like this.
> --
> From: David G. Johnston 
> Sent: ‎2/‎23/‎2016 0:14
> To: Edson F. Lidorio 
> Cc: pgsql-general 
> Subject: Re: [GENERAL] Get the date of creation of objects in the database
>
> On Mon, Feb 22, 2016 at 3:00 PM, Edson F. Lidorio 
> wrote:
>
>> Hello,
>>
>> How to get the date of creation of objects in batabase?
>> For example: The date of creation of tables and trigger.
>>
>
> ​The only source of data for that question is the local filesystem.  If
> that is acceptable you can find examples online provided to others who have
> asked this question.
>
> If you want something internal to the database the answer is no.  Those
> same searches will turn up the various reasons why such a feature has not
> gotten any strong traction from the developers.
>
> This was my search query:  "postgresql object creation time"
>
> David J.
>
>
Long ago, I requested that capability be added to PostgreSQL, as both
Oracle and MS SQL Server store the creation date in their catalogs.

Perhaps if you care to add your support to mine, you can help influence the
developers and it will be added in a future version.

See the link below.

Add relcreated to pg_class catalog

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

Re: [GENERAL] Read-only tables to avoid row visibility check

[Tom Lane]

Seamus Abshere  writes:
> Being able to tell postgres that our table is "Read Only" has imaginary
> mystical properties for me, first and foremost being able to count
> against indexes without ever hitting the disk.

>> ​If the system is working properly then a READ ONLY table in fact should 
>> be able to use Index Only Scans without the hack of a DBA telling it that 
>> said table is READ ONLY.​

> So this should happen already?

Yeah.  Index-only scans will work if all (or at least most) of the table
hasn't been modified since the last VACUUM.  If we had a READ ONLY
property, I do not think it would affect that logic at all; it would just
prevent future mods going forward.  Which, as noted, you could already do
by revoking suitable privileges.

regards, tom lane


-- 
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] Get the date of creation of objects in the database

[Edson F. Lidorio]

On 22-02-2016 19:42, Melvin Davidson wrote:


On Mon, Feb 22, 2016 at 5:18 PM, Zlatko Asenov 
mailto:zlatko.ase...@gmail.com>> wrote:


You may find useful a SIEM to record activity like this.

From: David G. Johnston 
Sent: ‎2/‎23/‎2016 0:14
To: Edson F. Lidorio 
Cc: pgsql-general 
Subject: Re: [GENERAL] Get the date of creation of objects in the
database

On Mon, Feb 22, 2016 at 3:00 PM, Edson F. Lidorio
mailto:ed...@openmailbox.org>>wrote:

Hello,

How to get the date of creation of objects in batabase?
For example: The date of creation of tables and trigger.


​ The only source of data for that question is the local
filesystem.  If that is acceptable you can find examples online
provided to others who have asked this question.

If you want something internal to the database the answer is no. 
Those same searches will turn up the various reasons why such a

feature has not gotten any strong traction from the developers.

This was my search query:  "postgresql object creation time"

David J.


Long ago, I requested that capability be added to PostgreSQL, as both 
Oracle and MS SQL Server store the creation date in their catalogs.


Perhaps if you care to add your support to mine, you can help 
influence the developers and it will be added in a future version.


See the link below.

Add relcreated to pg_class catalog 


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

+ 1

Listen11similar discussionat [1]

[1] 
http://pgsql-hackers.postgresql.narkive.com/TQSHWw1l/proposal-store-timestamptz-of-database-creation-on-pg-database

Re: [GENERAL] Get the date of creation of objects in the database

[Adrian Klaver]

On 02/22/2016 03:24 PM, Edson F. Lidorio wrote:






+ 1

Listen11similar discussionat [1]

[1]
http://pgsql-hackers.postgresql.narkive.com/TQSHWw1l/proposal-store-timestamptz-of-database-creation-on-pg-database


Where the above leads to is implementing a version control system inside 
the database. Not necessarily a bad idea, but something I personally see 
as better handled by dedicated version control software. Myself, I use 
sqitch(http://sqitch.org/) in combination with Mercurial to keep track 
of object creation and changes. Being independent of the database it 
handles dealing with multiple instances(think dev, testing, production) 
easier.


--
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] synch streaming replication question

[Michael Paquier]

On Tue, Feb 23, 2016 at 6:43 AM, John Wiencek  wrote:

> I have a three node cluster using streaming replication configured as
> follows:
> One synch node and one asynch node.
> If my synch node is down the master node is hung until that node is brought
> back on line.
>
> Two questions:
> 1.  Is this the expected action on my master?  It makes sense if it is since
> the master is waiting for an ack from the synch node.

Yes, when synchronous_commit = on, the default values that you should
have, the master will wait from standby the confirmation that the WAL
record for the commit has been flushed;
http://www.postgresql.org/docs/devel/static/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-MASTER

> 2. Is there anyway to configure my cluster so I have a synch node but not
> have my master node “HANG” if the synch node is down?

You can do that at transaction level for example by disabling
synchronous_commit.
Regards,
-- 
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] synch streaming replication question

[Thomas Munro]

On Tue, Feb 23, 2016 at 3:09 PM, Michael Paquier
 wrote:
> On Tue, Feb 23, 2016 at 6:43 AM, John Wiencek  wrote:
>
>> I have a three node cluster using streaming replication configured as
>> follows:
>> One synch node and one asynch node.
>> If my synch node is down the master node is hung until that node is brought
>> back on line.
>>
>> Two questions:
>> 1.  Is this the expected action on my master?  It makes sense if it is since
>> the master is waiting for an ack from the synch node.
>
> Yes, when synchronous_commit = on, the default values that you should
> have, the master will wait from standby the confirmation that the WAL
> record for the commit has been flushed;
> http://www.postgresql.org/docs/devel/static/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-MASTER
>
>> 2. Is there anyway to configure my cluster so I have a synch node but not
>> have my master node “HANG” if the synch node is down?
>
> You can do that at transaction level for example by disabling
> synchronous_commit.

Or you could configure both of your standbys as synchronous standbys.
Only one of them will actually be a synchronous standby at a time, and
the other one will take over that role if the first one is down, so
your system won't hang but you'll still have the sync standby
guarantee.

-- 
Thomas Munro
http://www.enterprisedb.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] repmgr faiover question

[John Wiencek]

Hello

I have a three node postgresql 9.4 cluster configured with one standby in
sync mode and the other in asynch mode.

If the master datbase is shutdown the pg promote happens as expected.
However on the remaining standby node the RECOVERY.CONF file is
misconfigured when issuing the command:

Repmgr ­f ./etc/repmgr/repmgr.conf ‹verbose standby follow

The recovery.conf file get created like this:

primary_conninfo = 'port=5432 host=10.999.99.999  user=repmgr
application_name=postgres-1d-01.node.us-east-1..x.com¹

When it should look like thisŠ

primary_conninfo  = 'host=postgres-1c-01.node.us-east-1..xxx.com
port=5432 user=replication password=¹


Where is it picking up the user and application_name?


This same this is happening when I use rampager to reclone my former master
as a standby database.

Regards

John

Re: [GENERAL] synch streaming replication question

[John Wiencek]

How do I configure both standbys as ³synch² nodes?

Do I list both nodenames or ip addresesses in the postgresql.conf
synchronous_standyby_names entry?

My current entery looks like this:

synchronous_standby_names = 'keypg2,*'




John 


On 2/22/16, 8:25 PM, "Thomas Munro"  wrote:

>On Tue, Feb 23, 2016 at 3:09 PM, Michael Paquier
> wrote:
>> On Tue, Feb 23, 2016 at 6:43 AM, John Wiencek 
>>wrote:
>>
>>> I have a three node cluster using streaming replication configured as
>>> follows:
>>> One synch node and one asynch node.
>>> If my synch node is down the master node is hung until that node is
>>>brought
>>> back on line.
>>>
>>> Two questions:
>>> 1.  Is this the expected action on my master?  It makes sense if it is
>>>since
>>> the master is waiting for an ack from the synch node.
>>
>> Yes, when synchronous_commit = on, the default values that you should
>> have, the master will wait from standby the confirmation that the WAL
>> record for the commit has been flushed;
>> 
>>http://www.postgresql.org/docs/devel/static/runtime-config-replication.ht
>>ml#RUNTIME-CONFIG-REPLICATION-MASTER
>>
>>> 2. Is there anyway to configure my cluster so I have a synch node but
>>>not
>>> have my master node ³HANG² if the synch node is down?
>>
>> You can do that at transaction level for example by disabling
>> synchronous_commit.
>
>Or you could configure both of your standbys as synchronous standbys.
>Only one of them will actually be a synchronous standby at a time, and
>the other one will take over that role if the first one is down, so
>your system won't hang but you'll still have the sync standby
>guarantee.
>
>-- 
>Thomas Munro
>http://www.enterprisedb.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] synch streaming replication question

[Michael Paquier]

On Tue, Feb 23, 2016 at 12:02 PM, John Wiencek  wrote:
> How do I configure both standbys as ³synch² nodes?
>
> Do I list both nodenames or ip addresesses in the postgresql.conf
> synchronous_standyby_names entry?
>
> My current entery looks like this:
>
> synchronous_standby_names = 'keypg2,*'

This is fine. Thanks to '*', all the other standby nodes connected to
this primary server will be thought as potential synchronous
candidates. And one of them will be. With this configuration keypg2 is
chosen first though if it is connected.
-- 
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] Live steraming replication setup issue!

[Venkata Balaji N]

On Tue, Feb 23, 2016 at 10:02 AM, Ashish Chauhan  wrote:

> Thanks Venkata, I am able to setup replication now. Just wondering when I
> check replication_delay and lag, I am getting negative number, any idea why?
>
>
>
> receive|replay| replication_delay | lag
>
> --+--+---+-
>
> 796/BA9D8000 | 796/BA9D7FF0 | -00:00:01.612415  |  -2
>

The WAL records in receive and replay means the same WAL record, please see
below :

postgres=# select pg_xlogfile_name('796/BA9D8000');
 pg_xlogfile_name
--
 0001079600BA
(1 row)

postgres=# select pg_xlogfile_name('796/BA9D7FF0');
 pg_xlogfile_name
--
 0001079600BA
(1 row)

That means the replication is continuously streaming and may behind few WAL
records. Do you see the lag all the time ? Did you test if the replication
is working fine ?

You can check that via pg_controldata as well. What does sync_state in
pg_stat_replication say ?

Regards,
Venkata B N

Fujitsu Australia

Re: [GENERAL] Get the date of creation of objects in the database

[Stephen Frost]

David,

* David G. Johnston (david.g.johns...@gmail.com) wrote:
> The only source of data for that question is the local filesystem.  If
> that is acceptable you can find examples online provided to others who have
> asked this question.

What on the local filesystem would help here..?  All you know from that
is when the relfilenode was created, but that's not the same as when the
table was created in the face of various commands that we have which
change the relfilenode..

> If you want something internal to the database the answer is no.  Those
> same searches will turn up the various reasons why such a feature has not
> gotten any strong traction from the developers.

> This was my search query:  "postgresql object creation time"

At least on a first blush look through the threads linked from such a
search, I'm unimpressed by the arguments against and note that there are
quite a few arguments for.

The summary of comments seems to be:

1) We don't know what we want

  Doesn't seem terribly hard to define.

  Object creation time (aka: CREATE TABLE time)
  Object modification time (aka: ALTER TABLE time)

  We could provide a function for 'last data modification time' which
  simply uses the filesystem modification time.

2) It'll be expensive to keep track of

  We often claim that we aren't too worried about DDL cost, and this
  would be a very small additional cost on top of that.  If it's
  measurable then perhaps we could avoid doing it for temp tables, but
  that strikes me as really the only case that it might be worthwhile
  and I'm not convinced it is.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Get the date of creation of objects in the database

[Tom Lane]

Stephen Frost  writes:
> At least on a first blush look through the threads linked from such a
> search, I'm unimpressed by the arguments against and note that there are
> quite a few arguments for.

I think you missed the worries around what dump/reload semantics would be.

>   We could provide a function for 'last data modification time' which
>   simply uses the filesystem modification time.

As far as tables go, the filesystem mod time would not be anything of
great use to users.  Consider that (1) there might be committed but
unwritten data sitting in shared buffers, so the filesystem mod time could
be too old by as much as the max checkpoint interval; while (2) writes for
hint bit setting or xid freezing could happen long after the last data
write, so the filesystem mod time could be almost arbitrarily later than
what the user thinks is the last mod time.  Not to mention whether
physical rewrites such as CLUSTER ought to count as data mods.

But I thought this request was about DDL timestamps, not data timestamps.
The filesystem will help you not at all there, because at best it would
know about the last mod time on the relevant system catalog, not any
individual object.

Anyway, my main objection to this idea is that it would be a sinkhole for
arguments over what the detailed semantics would be.  Should dump/reload
result in a new DDL timestamp?  (If not, the only way to prevent it would
be to invent a new "ALTER object SET TIMESTAMP" family of DDL, which would
not merely be a lot of work but would mean that the timestamps would have
exactly 0 value for any sort of forensic purposes.)  Should, eg, COMMENT
ON cause a DDL timestamp update on the referenced object?  How about
REINDEX or VACUUM or ANALYZE?  How about something like creating a foreign
key reference to a table?  I think that you could make credible arguments
either way on each of these issues, depending on what you assume the true
use-case is for having the timestamps; which means that trying to support
them is a mug's game.  We won't satisfy anybody, least of all the users
who don't care and don't need the additional overhead.

Lastly, even if we had a DDL timestamp, it wouldn't tell you anything
about what that last change was.  So I think logging/auditing DDL
operations is a far better path to pursue.

regards, tom lane


-- 
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] Get the date of creation of objects in the database

[Stephen Frost]

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Stephen Frost  writes:
> > At least on a first blush look through the threads linked from such a
> > search, I'm unimpressed by the arguments against and note that there are
> > quite a few arguments for.
> 
> I think you missed the worries around what dump/reload semantics would be.

No, I just wasn't impressed by those concerns.  That's about like asking
"what should rsync do with filesystem timestamps?"  It's really not a
hugely difficult issue to address.

> >   We could provide a function for 'last data modification time' which
> >   simply uses the filesystem modification time.
> 
> As far as tables go, the filesystem mod time would not be anything of
> great use to users.  Consider that (1) there might be committed but
> unwritten data sitting in shared buffers, so the filesystem mod time could
> be too old by as much as the max checkpoint interval; while (2) writes for
> hint bit setting or xid freezing could happen long after the last data
> write, so the filesystem mod time could be almost arbitrarily later than
> what the user thinks is the last mod time.  Not to mention whether
> physical rewrites such as CLUSTER ought to count as data mods.

It's not an auditing system, no, it'd be a "this is the last time we
wrote to this relation."  We'd have to caveat that accordingly, to point
out that we might have data in shared buffers, etc, but this is a
definitional concern for the function, not an argument that such a
capability wouldn't be useful to some people.

> But I thought this request was about DDL timestamps, not data timestamps.

This specific one, the question about data modification was brought up
on at least the thread which I was reading.

> The filesystem will help you not at all there, because at best it would
> know about the last mod time on the relevant system catalog, not any
> individual object.

Agreed, which is why I only suggested a function that looks at the
filesystem for the "last data modification time" case.  The other cases,
which addressed the question brought up on this thread but you didn't
quote into your response, would depend on new fields in the relevant
system catalogs.

> Anyway, my main objection to this idea is that it would be a sinkhole for
> arguments over what the detailed semantics would be.  Should dump/reload
> result in a new DDL timestamp?  (If not, the only way to prevent it would
> be to invent a new "ALTER object SET TIMESTAMP" family of DDL, which would
> not merely be a lot of work but would mean that the timestamps would have
> exactly 0 value for any sort of forensic purposes.)

Yes, we would need such timestamps, no, I don't agree that having such
makes it useless (are the timestamps on unix filesystems similairly
"useless"?  I'd say no.)

> Should, eg, COMMENT
> ON cause a DDL timestamp update on the referenced object?  How about
> REINDEX or VACUUM or ANALYZE?  How about something like creating a foreign
> key reference to a table?  I think that you could make credible arguments
> either way on each of these issues, depending on what you assume the true
> use-case is for having the timestamps; which means that trying to support
> them is a mug's game.  We won't satisfy anybody, least of all the users
> who don't care and don't need the additional overhead.

We're back to the argument about the additional overhead and I simply
don't buy into that.  Yes, we'd have to come up with reasonable answers
to the above questions, but I don't see that as beyond our capabilities
or impossible to have reasonable answers which most users will
appreciate (COMMENT => yes, REINDEX => no, VACUUM => no, ANALYZE => no,
ALTER TABLE => yes, CREATE POLICY => yes, FOREIGN KEY REFERENCE => no;
note that this list is, essentially, "would a schema-only pg_dump for
*this* table be different?").

> Lastly, even if we had a DDL timestamp, it wouldn't tell you anything
> about what that last change was.  So I think logging/auditing DDL
> operations is a far better path to pursue.

I certainly don't mean to imply that this is more valuable or important
than a proper in-core auditing solution.  I don't believe our current
logging implementation is a credible answer to this request.  Given that
we don't have an in-core auditing solution, it's hard to say if it would
be easy to handle such a request.

Ultimately, I don't see what's been done by the other RDBM systems as
being particularly novel or difficult for us to provide and, in this
case, I don't believe they're just providing a feature which no one uses
or understands but rather are responding to entirely reasonable user
requests and have a reasonable solution that's continued to be useful
over time.

Even so, I'm not so enamored with this request that I'd spend resources
on it, but I wouldn't throw away a patch which implemented it out of
hand either.

Thanks!

Stephen


signature.asc
Description: Digital signature