Re: [HACKERS] dpkg-buildpackage fails on 9.2.6 on ubuntu 12.04.3 LTS

2013-12-17 Thread imagene...@gmail.com
Resolved.

Regression test outputs for name and enum can be replaced with generated out.

On Tue, Dec 17, 2013 at 12:17 AM, imagene...@gmail.com
imagene...@gmail.com wrote:
 This is resolved by running dpkg-buildpackage with postgres and
 dpkg-buildpackage -rsudo with the postgresql source folder in a
 directory owned by postgres as dpkg creates a temporary folder in said
 parent directory.

 However, I am now running into the following issue:

 The reason I am compiling is because I must change #define NAMEDATALEN
 64 to a larger value. Setting it to 256 has presumably yielded the
 following errors in the make check:

 It fails on the name and enum tests.

 I am rerunning the compilation to confirm this is a result of this
 change. Please specify how to remove the regression check from the
 dpkg build or how to resolve this (is there a max value that will not
 fail?) if this is caused by this change or by something else on the
 specified platform.


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


Re: [HACKERS] Why no INSTEAD OF triggers on tables?

2013-12-17 Thread Marko Tiikkaja

On 12/17/13, 4:53 AM, Robert Haas wrote:

Well, I'm specifically thinking of master partition tables.  In that
case, we really want an INSTEAD OF trigger.


/me scratches head.

So, put a BEFORE trigger, and make it return NULL.  Same effect,
different notation.


But it's not the same effect at all, that's the point:

=# create view foov as select 1 as a;
CREATE VIEW

=# create function insteadof() returns trigger as $$
$# begin
$# -- INSERT here
$# return new;
$# end
$# $$ language plpgsql;
CREATE FUNCTION

=# create function before() returns trigger as $$
$# begin
$# -- INSERT here
$# return null;
$# end
$# $$ language plpgsql;
CREATE FUNCTION

=# create trigger t1 instead of insert on foov for each row execute 
procedure insteadof();

CREATE TRIGGER

=# create trigger t2 before insert on bart for each row execute 
procedure before();

CREATE TRIGGER

=# insert into foov values (1) returning *;
 a
---
 1
(1 row)

INSERT 0 1

local:marko=#* insert into bart values (1) returning *;
 a
---
(0 rows)

INSERT 0 0



Regards,
Marko Tiikkaja


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


Re: [HACKERS] Changeset Extraction Interfaces

2013-12-17 Thread Andres Freund
On 2013-12-16 23:01:16 -0500, Robert Haas wrote:
 On Sat, Dec 14, 2013 at 12:37 PM, Andres Freund and...@2ndquadrant.com 
 wrote:
  On 2013-12-14 11:50:00 -0500, Robert Haas wrote:
  Well, it still seems to me that the right way to think about this is
  that the change stream begins at a certain point, and then once you
  cross a certain threshold (all transactions in progress at that time
  have ended) any subsequent snapshot is a possible point from which to
  roll forward.
 
  Unfortunately it's not possible to build exportable snapshots at any
  time - it requires keeping far more state around since we need to care
  about all transactions, not just transactions touching the
  catalog. Currently you can only export the snapshot in the one point we
  become consistent, after that we stop maintaining that state.
 
 I don't get it.  Once all the old transactions are gone, I don't see
 why you need any state at all to build an exportable snapshot.  Just
 take a snapshot.

The state we're currently decoding, somewhere in already fsynced WAL,
won't correspond to the state in the procarray. There might be
situations where it will, but we can't guarantee that we ever reach that
point without taking locks that will be problematic.

 The part that you're expressing willingness to do sounds entirely
 satisfactory to me.  As I mentioned on the other thread, I'm perhaps
 even willing to punt that feature entirely provided that we have a
 clear design for how to add it later, but I think it'd be nicer to get
 it done now.

We'll see how the next version looks like. Not sure on that myself yet
;)

 And just for the record, I think the idea that I am holding this patch
 hostage is absurd.  I have devoted a large amount of time and energy
 to moving this forward and plan to devote more.  Because of that work,
 big chunks of what is needed here are already committed. If my secret
 plan is to make it as difficult as possible for you to get this
 committed, I'm playing a deep game.

I am not saying at all that you're planning to stop the patch from
getting in. You've delivered pretty clear proof that that's not the
case.
But that doesn't prevent us from arguing over details and disagreeing
whether they are dealbreakers or not, does it ;)

I think you know that I am hugely grateful for the work you've put into
the topic.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


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


Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

2013-12-17 Thread David Rowley
On Mon, Dec 16, 2013 at 9:36 PM, Hannu Krosing ha...@2ndquadrant.comwrote:

  On 12/16/2013 08:39 AM, David Rowley wrote:


  Any other ideas or +1's for any of the existing ones?

 +1, inverse good :)


In the attached patch I've renamed negative to inverse. I've also disabled
the inverse functions when an expression in an aggregate contains a
volatile function.

Regards

David Rowley


inverse_aggregate_functions_v1.0.patch.gz
Description: GNU Zip compressed data

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


Re: [HACKERS] Extension Templates S03E11

2013-12-17 Thread Dimitri Fontaine
Tom Lane t...@sss.pgh.pa.us writes:
 Right.  I think a lot of the tension comes from people being unconvinced
 that the existing extension feature is an ideal model for this sort of
 use-case.  Extensions were mainly designed around the notion of a .so

The effort here is all about extending the Extension Use Case, yes.

 OTOH, for a set of pure-SQL objects, it's not necessary that there be a
 canonical text file somewhere, and we have in principle complete knowledge
 of the objects' semantics as well as the ability to dump-and-restore into
 newer PG versions.  So it's not at all clear that we should just adopt the
 existing model with the smallest possible changes --- which AFAICS is
 basically what this proposal is.  Maybe that's the way to go, but we
 should consider alternatives, and in particular I think there is much
 more reason to allow inside-the-database mutation of the SQL objects.

My thinking is that if we invent a new mechanism for extensions that are
not managed like contribs, we will find out that only contribs are going
to be using extensions.

Given the options of either growing extensions into being able to cope
with more than a single model or building an entirely new system having
most of the same feature set than Extensions, I'm pushing for the option
where we build on top of what we have already.

 I think the name Extension Templates is horrible because it misleads
 all of us on this list into thinking the proposed feature is completely
 something other than what it is.  I don't have a better name offhand,
 but that's got to change before it becomes a feature.

 Given your previous para, I wonder if library or package would work
 better.  I agree that template isn't le mot juste.

We can't use “package” because it means something very different in
direct competition. I have other propositions, but they are only
relevant if we choose not to improve Extensions… right?

Regards,
-- 
Dimitri Fontaine06 63 07 10 78
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


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


Re: [HACKERS] Optimize kernel readahead using buffer access strategy

2013-12-17 Thread KONDO Mitsumasa

Hi,

I fixed the patch to improve followings.

  - Can compile in MacOS.
  - Change GUC name enable_kernel_readahead to readahead_strategy.
  - Change POSIX_FADV_SEQUNENTIAL to POISX_FADV_NORMAL when we select sequential
access strategy, this reason is later...

I tested simple two access paterns which are followings in pgbench tables scale 
size is 1000.


  A) SELECT count(bid) FROM pgbench_accounts; (Index only scan)
  B) SELECT count(bid) FROM pgbench_accounts; (Seq scan)

 In each test, I restart postgres and drop file cache before each test.

Unpatched PG is faster than patched in A and B query. It was about 1.3 times 
faster. Result of A query as expected, because patched PG cannot execute 
readahead at all. So cache cold situation is bad for patched PG. However, it 
might good for cache hot situation, because it doesn't read disk IO at all and 
can calculate file cache usage and know which cache is important.


However, result of B query as unexpected, because my patch select 
POSIX_FADV_SEQUNENTIAL collectry, but it slow. I cannot understand that, 
nevertheless I read kernel source code... Next, I change POSIX_FADV_SEQUNENTIAL 
to POISX_FADV_NORMAL in my patch. B query was faster as unpatched PG.


In heavily random access benchmark tests which are pgbench and DBT-2, my patched 
PG is about 1.1 - 1.3 times faster than unpatched PG. But postgres buffer hint 
strategy algorithm have not optimized for readahead strategy yet, and I don't fix 
it. It is still only for ring buffer algorithm in shared_buffer.



Attached printf-debug patch will show you inside postgres buffer strategy. When 
you see S it selects sequential access strategy, on the other hands, when you 
see R it selects random access strategy. It might interesting for you. It's 
very visual.


Example output is here.

[mitsu-ko@localhost postgresql]$ bin/vacuumdb
SSS~~SS
[mitsu-ko@localhost postgresql]$ bin/psql -c EXPLAIN ANALYZE SELECT count(aid) FROM 
pgbench_accounts
   
QUERY PLAN
-
 Aggregate  (cost=2854.29..2854.30 rows=1 width=4) (actual time=33.438..33.438 
rows=1 loops=1)
   -  Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  
(cost=0.29..2604.29 rows=10 width=4) (actual time=0.072..20.912 rows=10 
loops=1)
 Heap Fetches: 0
 Total runtime: 33.552 ms
(4 rows)

RRR~~RR
[mitsu-ko@localhost postgresql]$ bin/psql -c EXPLAIN ANALYZE SELECT count(bid) FROM 
pgbench_accounts
SSS~~SS
--
 Aggregate  (cost=2890.00..2890.01 rows=1 width=4) (actual time=40.315..40.315 
rows=1 loops=1)
   -  Seq Scan on pgbench_accounts  (cost=0.00..2640.00 rows=10 width=4) 
(actual time=0.112..23.001 rows=10 loops=1)
 Total runtime: 40.472 ms
(3 rows)


Thats's all now.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center
*** a/configure
--- b/configure
***
*** 19937,19943  LIBS=`echo $LIBS | sed -e 's/-ledit//g' -e 's/-lreadline//g'`
  
  
  
! for ac_func in cbrt dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll pstat readlink setproctitle setsid shm_open sigprocmask symlink sync_file_range towlower utime utimes wcstombs wcstombs_l
  do
  as_ac_var=`$as_echo ac_cv_func_$ac_func | $as_tr_sh`
  { $as_echo $as_me:$LINENO: checking for $ac_func 5
--- 19937,19943 
  
  
  
! for ac_func in cbrt dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fadvise pstat readlink setproctitle setsid shm_open sigprocmask symlink sync_file_range towlower utime utimes wcstombs wcstombs_l
  do
  as_ac_var=`$as_echo ac_cv_func_$ac_func | $as_tr_sh`
  { $as_echo $as_me:$LINENO: checking for $ac_func 5
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***
*** 1252,1257  include 'filename'
--- 1252,1281 
/listitem
   /varlistentry
  
+  varlistentry id=guc-readahead-strategy xreflabel=readahead_strategy
+   termvarnamereadahead_strategy/varname (typeinteger/type)/term
+   indexterm
+primaryvarnamereadahead_strategy/configuration parameter/primary
+   /indexterm
+   listitem
+para
+ This feature is to select which readahead strategy is used. When we
+ set off(default), readahead strategy is optimized by OS. On the other
+ hands, when we set on, readahead strategy is optimized by Postgres.
+ In typicaly situations, OS readahead strategy will be good working,
+ however Postgres often knows better readahead strategy before 
+ 

Re: [HACKERS] [bug fix] pg_ctl always uses the same event source

2013-12-17 Thread MauMau

From: Amit Kapila amit.kapil...@gmail.com

Few minor things:
1.
evtHandle = RegisterEventSource(NULL,
*event_source? event_source: DEFAULT_EVENT_SOURCE);

In this code, you are trying to access the value (*event_source) and
incase it is not initialised,
it will not contain the value and could cause problem, why not
directly check 'event_source'?


event_source here is a global static char array, so it's automatically 
initialized with zeros and safe to access.




2. minor coding style issue
pg_ctl.c
evtHandle = RegisterEventSource(NULL,
*event_source? event_source: DEFAULT_EVENT_SOURCE);

elog.c
! evtHandle = RegisterEventSource(NULL,
! event_source ? event_source : DEFAULT_EVENT_SOURCE);

In both above usages, it is better that arguments in second line should 
start

inline with previous lines first argument. You can refer other places,
for ex. refer call to ReportEvent in pg_ctl.c just below
RegisterEventSource call.


Thanks.  I passed the source files through pgindent and attached the revised 
patch.  Although the arguments in the second line are not in line with the 
first line's arguments, that's what pgindent found good.


Regards
MauMau


pg_ctl_eventsrc_v3.patch
Description: Binary data

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


[HACKERS] [bug fix] connection service file doesn't take effect with ECPG apps

2013-12-17 Thread MauMau

Hello,

I've found a small bug of ECPG and attached a patch.  I tested the fix with 
9.4.  I'd like the fix to be back-ported.



[Problem]
The ECPG app runs the statement:

EXEC SQL CONNECT TO 'tcp:postgresql://?service=my_service';

I want this app to connect to any database based on the connection service 
file.
For example, I wrote the following connection service file pg_service.conf, 
placed it in the current directory, set PGSERVICEFILE environment variable 
to point to it:


[my_service]
dbname = mydb
host = myhost
port = 

myhost is a different host than the one where the app runs.

Unfortunately, the app could not connect to the intended database.  It tried 
to connect to the (non-existent) database server on the local machine and 
failed.



[Cause]
ECPGconnect() parses the URI and produces an empty host name.  It passes an 
empty string as the value for host connection parameter to 
PQconnectdbParams().


Given an empty host name, PQconnectdbParams() ignores the host parameter in 
pg_service.conf.  When host is , PQconnectdbParams() try to connect via 
local UNIX domain socket.



[Fix]
It doesn't make sense for ECPGconnect() to pass an empty host name to 
PQconnectdbParams(), so prevent it from passing host parameter for the 
service setting to take effect.  port is the same.



Regards
MauMau


ecpg_service.patch
Description: Binary data

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


Re: [HACKERS] Optimize kernel readahead using buffer access strategy

2013-12-17 Thread Simon Riggs
On 17 December 2013 11:50, KONDO Mitsumasa
kondo.mitsum...@lab.ntt.co.jp wrote:

 Unpatched PG is faster than patched in A and B query. It was about 1.3 times
 faster. Result of A query as expected, because patched PG cannot execute
 readahead at all. So cache cold situation is bad for patched PG. However, it
 might good for cache hot situation, because it doesn't read disk IO at all
 and can calculate file cache usage and know which cache is important.

 However, result of B query as unexpected, because my patch select
 POSIX_FADV_SEQUNENTIAL collectry, but it slow. I cannot understand that,
 nevertheless I read kernel source code... Next, I change
 POSIX_FADV_SEQUNENTIAL to POISX_FADV_NORMAL in my patch. B query was faster
 as unpatched PG.

 In heavily random access benchmark tests which are pgbench and DBT-2, my
 patched PG is about 1.1 - 1.3 times faster than unpatched PG. But postgres
 buffer hint strategy algorithm have not optimized for readahead strategy
 yet, and I don't fix it. It is still only for ring buffer algorithm in
 shared_buffer.

These are interesting results. Good research.

They also show that the benefit of this is very specific to the exact
task being performed. I can't see any future for a setting that
applies to everything or nothing. We must be more selective.

We also need much better benchmark results, clearly laid out, so they
can be reproduced and discussed.

Please keep working on this.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


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


Re: [HACKERS] logical changeset generation v6.8

2013-12-17 Thread Andres Freund
On 2013-12-16 00:53:10 -0500, Robert Haas wrote:
  Yes, I think we could mostly reuse it, we'd probably want to add a field
  or two more (application_name, sync_prio?). I have been wondering
  whether some of the code in replication/logical/logical.c shouldn't be
  in replication/slot.c or similar. So far I've opted for leaving it in
  its current place since it would have to change a bit for a more general
  role.
 
 I strongly favor moving the slot-related code to someplace with slot
 in the name, and replication/slot.c seems about right.  Even if we
 don't extend them to cover non-logical replication in this release,
 we'll probably do it eventually, and it'd be better if that didn't
 require moving large amounts of code between files.

Any opinion on the storage location of the slot files? It's currently
pg_llog/$slotname/state[.tmp]. It's a directory so we have a location
during logical decoding to spill data to...

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


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


Re: [HACKERS] logical changeset generation v6.8

2013-12-17 Thread Robert Haas
On Tue, Dec 17, 2013 at 7:48 AM, Andres Freund and...@2ndquadrant.com wrote:
 On 2013-12-16 00:53:10 -0500, Robert Haas wrote:
  Yes, I think we could mostly reuse it, we'd probably want to add a field
  or two more (application_name, sync_prio?). I have been wondering
  whether some of the code in replication/logical/logical.c shouldn't be
  in replication/slot.c or similar. So far I've opted for leaving it in
  its current place since it would have to change a bit for a more general
  role.

 I strongly favor moving the slot-related code to someplace with slot
 in the name, and replication/slot.c seems about right.  Even if we
 don't extend them to cover non-logical replication in this release,
 we'll probably do it eventually, and it'd be better if that didn't
 require moving large amounts of code between files.

 Any opinion on the storage location of the slot files? It's currently
 pg_llog/$slotname/state[.tmp]. It's a directory so we have a location
 during logical decoding to spill data to...

pg_replslot?  pg_replication_slot?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] Why no INSTEAD OF triggers on tables?

2013-12-17 Thread Robert Haas
On Tue, Dec 17, 2013 at 3:27 AM, Marko Tiikkaja ma...@joh.to wrote:
 On 12/17/13, 4:53 AM, Robert Haas wrote:

 Well, I'm specifically thinking of master partition tables.  In that
 case, we really want an INSTEAD OF trigger.

 /me scratches head.

 So, put a BEFORE trigger, and make it return NULL.  Same effect,
 different notation.


 But it's not the same effect at all, that's the point:

 =# create view foov as select 1 as a;
 CREATE VIEW

 =# create function insteadof() returns trigger as $$
 $# begin
 $# -- INSERT here
 $# return new;
 $# end
 $# $$ language plpgsql;
 CREATE FUNCTION

 =# create function before() returns trigger as $$
 $# begin
 $# -- INSERT here
 $# return null;
 $# end
 $# $$ language plpgsql;
 CREATE FUNCTION

 =# create trigger t1 instead of insert on foov for each row execute
 procedure insteadof();
 CREATE TRIGGER

 =# create trigger t2 before insert on bart for each row execute procedure
 before();
 CREATE TRIGGER

 =# insert into foov values (1) returning *;
  a
 ---
  1
 (1 row)

 INSERT 0 1

 local:marko=#* insert into bart values (1) returning *;
  a
 ---
 (0 rows)

 INSERT 0 0

Ah, interesting point.  I didn't realize it worked like that.  That
does seem like a mighty useful thing to be able to do.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] pg_rewarm status

2013-12-17 Thread Robert Haas
On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila amit.kapil...@gmail.com wrote:
 I have used pg_prewarm during some of work related to Buffer Management and
 other performance related work. It is quite useful utility.
 +1 for reviving this patch for 9.4

Any other votes?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] Useless Replica Identity: NOTHING noise from psql \d

2013-12-17 Thread Robert Haas
On Mon, Dec 16, 2013 at 7:25 AM, Andres Freund and...@2ndquadrant.com wrote:
 On 2013-12-14 17:43:36 +0100, Andres Freund wrote:
 On 2013-12-14 11:27:53 -0500, Tom Lane wrote:
  In HEAD:
 
  regression=# \d pg_depend
 Table pg_catalog.pg_depend
 Column|  Type   | Modifiers
  -+-+---
   classid | oid | not null
   objid   | oid | not null
   objsubid| integer | not null
   refclassid  | oid | not null
   refobjid| oid | not null
   refobjsubid | integer | not null
   deptype | char  | not null
  Indexes:
  pg_depend_depender_index btree (classid, objid, objsubid)
  pg_depend_reference_index btree (refclassid, refobjid, refobjsubid)
  Replica Identity: NOTHING
 
  Where did that last line come from, and who thinks it's so important
  that it should appear by default?  It seems absolutely content-free
  even if I were using whatever feature it refers to, since it is
  (I presume) the default state.

 Hm. Yes, that's slightly inellegant. It's shown because it's not
 actually the normal default normal tables. Just for system tables. Maybe
 we should just set it to default (in pg_class) for system tables as
 well, and just change it in the relcache.

 Hm. I don't like that choice much after thinking for a bit. Seems to
 make querying the catalog unneccessarily complex.
 How about making it conditional on the table's namespace instead? That
 will do the wrong thing if somebody moves a table to pg_catalog and
 configures a replica identity, but I think we can live with that, given
 how many other things work strangely around that.

 Patch attached.

I vote for showing it only with +, but regardless of whether the value
matches the expected default.  I'd keep the relkind test, though,
because I think I noticed that it currently shows up for indexes,
which is dumb.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] Changeset Extraction Interfaces

2013-12-17 Thread Robert Haas
On Tue, Dec 17, 2013 at 4:31 AM, Andres Freund and...@2ndquadrant.com wrote:
 On 2013-12-16 23:01:16 -0500, Robert Haas wrote:
 On Sat, Dec 14, 2013 at 12:37 PM, Andres Freund and...@2ndquadrant.com 
 wrote:
  On 2013-12-14 11:50:00 -0500, Robert Haas wrote:
  Well, it still seems to me that the right way to think about this is
  that the change stream begins at a certain point, and then once you
  cross a certain threshold (all transactions in progress at that time
  have ended) any subsequent snapshot is a possible point from which to
  roll forward.
 
  Unfortunately it's not possible to build exportable snapshots at any
  time - it requires keeping far more state around since we need to care
  about all transactions, not just transactions touching the
  catalog. Currently you can only export the snapshot in the one point we
  become consistent, after that we stop maintaining that state.

 I don't get it.  Once all the old transactions are gone, I don't see
 why you need any state at all to build an exportable snapshot.  Just
 take a snapshot.

 The state we're currently decoding, somewhere in already fsynced WAL,
 won't correspond to the state in the procarray. There might be
 situations where it will, but we can't guarantee that we ever reach that
 point without taking locks that will be problematic.

You don't need to guarantee that.  Just take a current snapshot and
then throw away (or don't decode in the first place) any transactions
that would be visible to that snapshot.  This is simpler and more
flexible, and possibly more performant, too, because with your design
you'll have to hold back xmin to the historical snapshot you build
while copying the table rather than to a current snapshot.

I really think we should consider whether we can't get by with ripping
out the build-an-exportable-snapshot code altogether.  I don't see
that it's really buying us much.  We need a way for the client to know
when decoding has reached the point where it is guaranteed complete -
i.e. all transactions in progress at the time decoding was initiated
have ended.  We also need a way for a backend performing decoding to
take a current MVCC snapshot, export it, and send the identifier to
the client.  And we need a way for the client to know whether any
given one of those snapshots includes a particular XID we may have
decoded.  But I think all of that might still be simpler than what you
have now, and it's definitely more flexible.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] SSL: better default ciphersuite

2013-12-17 Thread Robert Haas
On Sun, Dec 15, 2013 at 5:10 PM, James Cloos cl...@jhcloos.com wrote:
 For reference, see:

   https://wiki.mozilla.org/Security/Server_Side_TLS

 for the currently suggested suite for TLS servers.
...
 But for pgsql, I'd leave off the !PSK; pre-shared keys may prove useful
 for some.  And RC4, perhaps, also should be !ed.

 And if anyone wants Kerberos tls-authentication, one could add
 KRB5-DES-CBC3-SHA, but that is ssl3-only.

 Once salsa20-poly1305 lands in openssl, that should be added to the
 start of the list.

I'm starting to think we should just leave this well enough alone.  We
can't seem to find two people with the same idea of what would be
better than what we have now.  And of course the point of making it a
setting in the first place is that each person can set it to whatever
they deem best.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] Proposal: variant of regclass

2013-12-17 Thread Robert Haas
On Mon, Dec 16, 2013 at 8:22 PM, Tatsuo Ishii is...@postgresql.org wrote:
 static Datum regclass_gut(char *class_name_or_oid, bool raiseError);
 static List *stringToQualifiedNameList_gut(const char *string, bool 
 raiseError);

 Please spell that as guts not gut.

 Thanks. I see.

 regclass_gut is called from regclassin and toregclass and do the most
 job before regclassin did. raiseError flag controls whether an error
 is raised or not when an invalid argument (for example non existent
 relation) is given. For this purpose, regclass_gut wraps the call to
 oidin using a PG_TRY block.

 I do not think that use of PG_TRY is either necessary or acceptable
 --- for example, it will happily trap and discard query-cancel errors,
 as well as other errors that are not necessarily safe to ignore.
 If you don't want to risk an error on an invalid numeric string,
 how about parsing the integer yourself with sscanf?

 Fair enough. I will remove the part.

 More generally, though, I don't see a great need to try to promise
 that this function *never* throws any errors, and so I'm also suspicious
 of the hacking you've done on stringToQualifiedNameList.  I'm even
 less happy about the idea that this patch might start reaching into
 things like makeRangeVarFromNameList.  I think it's sufficient if it
 doesn't throw an error on name-not-found; you don't have to try to
 prevent weird syntax problems from throwing errors.

 For the pgpool-II use case, I'm happy to follow you because pgpool-II
 always does a grammatical check (using raw parser) on a query first
 and such syntax problem will be pointed out, thus never reaches to
 the state where calling toregclass.

 One concern is, other users expect toregclass to detect such syntax
 problems. Anybody?

It seems fine to me if the new function ignores the specific error of
relation does not exist while continuing to throw other errors.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] stuck spinlock

2013-12-17 Thread bricklen
On Mon, Dec 16, 2013 at 6:46 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 Andres Freund and...@2ndquadrant.com writes:
  Hard to say, the issues fixed in the release are quite important as
  well. I'd tend to say they are more important. I think we just need to
  release 9.3.3 pretty soon.

 Yeah.


Has there been any talk about when a 9.3.3 (and/or 9.2.7?) patch might be
released?


Re: [HACKERS] Extension Templates S03E11

2013-12-17 Thread Stephen Frost
* Dimitri Fontaine (dimi...@2ndquadrant.fr) wrote:
  OTOH, for a set of pure-SQL objects, it's not necessary that there be a
  canonical text file somewhere, and we have in principle complete knowledge
  of the objects' semantics as well as the ability to dump-and-restore into
  newer PG versions.  So it's not at all clear that we should just adopt the
  existing model with the smallest possible changes --- which AFAICS is
  basically what this proposal is.  Maybe that's the way to go, but we
  should consider alternatives, and in particular I think there is much
  more reason to allow inside-the-database mutation of the SQL objects.
 
 My thinking is that if we invent a new mechanism for extensions that are
 not managed like contribs, we will find out that only contribs are going
 to be using extensions.

That's only accurate if the new mechanism supports .so's, which seems
unlikely to be the case.  What I think we'd end up with is a split
between extensions, which would be things containing .so's, and
libraries or what-have-you, which would be more-or-less everything
else.  That kind of a break-down strikes me as perfectly reasonable.
There would also be flexability in that an author might choose to use an
extension even in cases where it's not strictly necessary to do so, for
whatever reason they want.

 Given the options of either growing extensions into being able to cope
 with more than a single model or building an entirely new system having
 most of the same feature set than Extensions, I'm pushing for the option
 where we build on top of what we have already.

I'm not sure that we need to throw away everything that exists to add on
this new capability; perhaps we can build a generic versioned
object-container system on which extensions and
packages/libraries/classes/whatever can also be built on (or perhaps
that's what 'extensions' end up morphing into).

 We can't use “package” because it means something very different in
 direct competition. I have other propositions, but they are only
 relevant if we choose not to improve Extensions… right?

I'd like to see extensions improved.  I don't feel like the proposed
'extension templates' is the way to do that because I don't think it
really solves anything and it adds a layer that strikes me as wholly
unnecessary.  I could see pulling in the control file contents as a
catalog, adding in dependency information which could be checked
against, perhaps hard vs. soft dependencies, and other things that make
sense to track for extensions-currently-installed into a given database.

However, as I understand it from the various discussions on this topic
outside of this list, the immediate concern is the need for a multi-OS
extension distribution network with support for binaries, .so's and
.dll's and whatever else, to make installing extensions easier for
developers on various platforms.  I'm all for someone building that and
dealing with the issues associated with that, but building a client for
it in core, either in a way where a backend would reach out and
download the files or accepting binary .so's through the frontend
protocol, isn't the first step in that and I very much doubt it would
ever make sense.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] patch: make_timestamp function

2013-12-17 Thread Pavel Stehule
Hello

updated patch

time zone type functions are overloaded now

postgres=# select '1973-07-15 08:15:55.33+02'::timestamptz;
timestamptz
---
 1973-07-15 07:15:55.33+01
(1 row)

postgres=# SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, 2);
 make_timestamptz
---
 1973-07-15 07:15:55.33+01
(1 row)

postgres=# SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33);
 make_timestamptz
---
 1973-07-15 08:15:55.33+01
(1 row)

Regards

Pavel




2013/12/15 Pavel Stehule pavel.steh...@gmail.com

 Hello


 2013/12/13 Jim Nasby j...@nasby.net

 On 12/13/13 1:49 PM, Fabrízio de Royes Mello wrote:


 On Fri, Dec 13, 2013 at 5:35 PM, Tom Lane t...@sss.pgh.pa.us mailto:
 t...@sss.pgh.pa.us wrote:

  
   =?ISO-8859-1?Q?Fabr=EDzio_de_Royes_Mello?= 
 fabriziome...@gmail.commailto:
 fabriziome...@gmail.com writes:
I think the goal of the make_date/time/timestamp function series
 is build
a date/time/timestamp from scratch, so the use of
 'make_timestamptz' is to
build a specific timestamp with timezone and don't convert it.
  
   Yeah; we don't really want to incur an extra timezone rotation just
 to get
   to a timestamptz.  However, it's not clear to me if make_timestamptz()
   needs to have an explicit zone parameter or not.  It could just assume
   that you meant the active timezone.
  

 +1. And if you want a different timezone you can just set the 'timezone'
 GUC.


 Why wouldn't we have a version that optionally accepts the timezone? That
 mirrors what you can currently do with a cast from text, and having to set
 the GUC if you need a different TZ would be a real PITA.


 It is not bad idea.

 What will be format for timezone in this case? Is a doble enough?

 last version of this patch attached (without overloading in this moment)




 --
 Jim C. Nasby, Data Architect   j...@nasby.net
 512.569.9461 (cell) http://jim.nasby.net



commit 7f03cda7a5a5e173b51b2ddc87c1e437a5dc7b34
Author: Pavel Stehule pavel.steh...@gooddata.com
Date:   Thu Dec 12 18:08:47 2013 +0100

initial implementation make_timestamp

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a411e3a..9adec6b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6735,6 +6735,78 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
row
 entry
  indexterm
+  primarymake_timetz/primary
+ /indexterm
+ literal
+  function
+   make_timetz(parameterhour/parameter typeint/type,
+   parametermin/parameter typeint/type,
+   parametersec/parameter typedouble precision/type,
+   optional parametertimezone/parameter typeint/type /optional)
+  /function
+ /literal
+/entry
+entrytypetime with time zone/type/entry
+entry
+ Create time with time zone from hour, minute and seconds fields
+/entry
+entryliteralmake_timetz(8, 15, 23.5)/literal/entry
+entryliteral08:15:23.5+01/literal/entry
+   /row
+
+   row
+entry
+ indexterm
+  primarymake_timestamp/primary
+ /indexterm
+ literal
+  function
+   make_timestamp(parameteryear/parameter typeint/type,
+   parametermonth/parameter typeint/type,
+   parameterday/parameter typeint/type,
+   parameterhour/parameter typeint/type,
+   parametermin/parameter typeint/type,
+   parametersec/parameter typedouble precision/type)
+  /function
+ /literal
+/entry
+entrytypetimestamp/type/entry
+entry
+ Create timestamp from year, month, day, hour, minute and seconds fields
+/entry
+entryliteralmake_timestamp(1-23, 7, 15, 8, 15, 23.5)/literal/entry
+entryliteral2013-07-15 08:15:23.5/literal/entry
+   /row
+
+   row
+entry
+ indexterm
+  primarymake_timestamptz/primary
+ /indexterm
+ literal
+  function
+   make_timestamptz(parameteryear/parameter typeint/type,
+   parametermonth/parameter typeint/type,
+   parameterday/parameter typeint/type,
+   parameterhour/parameter typeint/type,
+   parametermin/parameter typeint/type,
+   parametersec/parameter typedouble precision/type,
+   optional parametertimezone/parameter typeint/type /optional)
+  /function
+ /literal
+/entry
+entrytypetimestamp with time zone/type/entry
+entry
+ Create timestamp with time zone from year, month, day, hour, minute
+ and seconds fields
+/entry
+entryliteralmake_timestamp(1-23, 7, 15, 8, 15, 23.5)/literal/entry
+entryliteral2013-07-15 08:15:23.5+01/literal/entry
+   /row
+
+   row
+entry
+ indexterm
   primarynow/primary
  

Re: [HACKERS] commit fest 2013-11 final report

2013-12-17 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes:
 Committers should now try to get the Ready for Committer queue cleared.

I'm still hacking on the WITHIN GROUP patch, and will look at the planner
indexes thing after that's done.

 That leaves 8 patches that haven't been reviewed.  If you take a look,
 you can imagine why: They're the most complicated and obscure topics,
 combined with reviewers who signed up but didn't get to do a review,
 plus perhaps authors who were too busy to follow up aggressively.  I'll
 leave those open in case someone still wants to take a look.  Authors
 should move those patches forward to the next commit fest if they want.

Perhaps we should just move all the Needs Review and RFC patches forward
to the next fest, so we don't forget about them?

regards, tom lane


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


Re: [HACKERS] patch: make_timestamp function

2013-12-17 Thread Jim Nasby

On 12/15/13, 12:59 PM, Pavel Stehule wrote:

Why wouldn't we have a version that optionally accepts the timezone? That 
mirrors what you can currently do with a cast from text, and having to set the 
GUC if you need a different TZ would be a real PITA.


It is not bad idea.

What will be format for timezone in this case? Is a doble enough?


Sorry for not seeing this earlier, but no, I think double is barking up the 
wrong tree. It should accept the same timezone identifiers that the rest of the 
system does, like blah AT TIME ZONE foo and SET timezone = foo;

Specifically, it needs to support things like 'GMT' and 'CST6CDT'.

I can see an argument for another version that accepts numeric so if you want 
to do -11.5 you don't have to wrap it in quotes...
--
Jim C. Nasby, Data Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net


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


Re: [HACKERS] pg_rewarm status

2013-12-17 Thread Jim Nasby

On 12/17/13, 8:34 AM, Robert Haas wrote:

On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila amit.kapil...@gmail.com wrote:

I have used pg_prewarm during some of work related to Buffer Management and
other performance related work. It is quite useful utility.
+1 for reviving this patch for 9.4


Any other votes?


We've had to manually code something that runs EXPLAIN ANALYZE SELECT * from a 
bunch of tables to warm our caches after a restart, but there's numerous flaws 
to that approach obviously.

Unfortunately, what we really need to warm isn't the PG buffers, it's the FS 
cache, which I suspect this won't help. But I still see where just pg_buffers 
would be useful for a lot of folks, so +1.
--
Jim C. Nasby, Data Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net


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


Re: [HACKERS] 9.3 reference constraint regression

2013-12-17 Thread Alvaro Herrera
Andres Freund wrote:
 On 2013-12-16 17:43:37 -0300, Alvaro Herrera wrote:
  Alvaro Herrera wrote:
  
   This POC patch changes the two places in HeapTupleSatisfiesUpdate that
   need to be touched for this to work.  This is probably too simplistic,
   in that I make the involved cases return HeapTupleBeingUpdated without
   checking that there actually are remote lockers, which is the case of
   concern.  I'm not yet sure if this is the final form of the fix, or
   instead we should expand the Multi (in the cases where there is a multi)
   and verify whether any lockers are transactions other than the current
   one.  As is, nothing seems to break, but I think that's probably just
   chance and should not be relied upon.
  
  After playing with this, I think the reason this seems to work without
  fail is that all callers of HeapTupleSatisfiesUpdate are already
  prepared to deal with the case where HeapTupleBeingUpdated is returned
  but there is no actual transaction that would block the operation.
  So I think the proposed patch is okay, barring a few more comments.
 
 Are you sure? the various wait/nowait cases don't seem to handle that
 correctly.

Well, it would help if those cases weren't dead code.  Neither
heap_update nor heap_delete are ever called in the no wait case at
all.  Only heap_lock_tuple is, and I can't see any misbehavior there
either, even with HeapTupleBeingUpdated returned when there's a
non-local locker, or when there's a MultiXact as xmax, regardless of its
status.

Don't get me wrong --- it's not like this case is all that difficult to
handle.  All that's required is something like this in
HeapTupleSatisfiesUpdate:

if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetXmin(tuple)))
{
...
if (HEAP_XMAX_IS_LOCKED_ONLY(tuple-t_infomask))/* not deleter 
*/
{
if (tuple-t_infomask  HEAP_XMAX_IS_MULTI)
{
intnmembers;
boolremote;
inti;
MultiXactMember *members;

nmembers =
GetMultiXactIdMembers(HeapTupleHeaderGetRawXmax(tuple),
  members, false);
remote = false;
for (i = 0; i  nmembers; i++)
{
if 
(!TransactionIdIsCurrentTransactionId(members[i].xid))
{
remote = true;
break;
}
}
if (nmembers  0)
pfree(members);

if (remote)
return HeapTupleBeingUpdated;
else
return HeapTupleMayBeUpdated;
}
else if 
(!TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetRawXmax(tuple)))
return HeapTupleBeingUpdated;
return HeapTupleMayBeUpdated;
}
}

The simpler code just does away with the GetMultiXactIdMembers() and
returns HeapTupleBeingUpdated always.  In absence of a test case that
misbehaves with that, it's hard to see that it is a good idea to go all
this effort there.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services


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


Re: [HACKERS] patch: make_timestamp function

2013-12-17 Thread Pavel Stehule
2013/12/17 Jim Nasby j...@nasby.net

 On 12/15/13, 12:59 PM, Pavel Stehule wrote:

 Why wouldn't we have a version that optionally accepts the timezone?
 That mirrors what you can currently do with a cast from text, and having to
 set the GUC if you need a different TZ would be a real PITA.


 It is not bad idea.

 What will be format for timezone in this case? Is a doble enough?


 Sorry for not seeing this earlier, but no, I think double is barking up
 the wrong tree. It should accept the same timezone identifiers that the
 rest of the system does, like blah AT TIME ZONE foo and SET timezone = foo;


I checked a code from datetime parser, and there we are not consistent

postgres=# select '1973-07-15 08:15:55.33+02'::timestamptz;
timestamptz
---
 1973-07-15 07:15:55.33+01
(1 row)

postgres=# select '1973-07-15 08:15:55.33+02.2'::timestamptz;
ERROR:  invalid input syntax for type timestamp with time zone: 1973-07-15
08:15:55.33+02.2
LINE 1: select '1973-07-15 08:15:55.33+02.2'::timestamptz;

It allows only integer

but AT TIME ZONE allows double (but decimal parts is ignored quietly)

postgres=# select make_time(10,20,30) at time zone '+10.2';
  timezone
-
 23:20:30-10

so I propose (and I implemented) a variant with int as time zone

and we can (if we would) implement next one with text as time zone

Regards

Pavel



 Specifically, it needs to support things like 'GMT' and 'CST6CDT'.

 I can see an argument for another version that accepts numeric so if you
 want to do -11.5 you don't have to wrap it in quotes...

 --
 Jim C. Nasby, Data Architect   j...@nasby.net
 512.569.9461 (cell) http://jim.nasby.net



Re: [HACKERS] patch: make_timestamp function

2013-12-17 Thread Alvaro Herrera
Pavel Stehule escribió:

 but AT TIME ZONE allows double (but decimal parts is ignored quietly)
 
 postgres=# select make_time(10,20,30) at time zone '+10.2';
   timezone
 -
  23:20:30-10
 
 so I propose (and I implemented) a variant with int as time zone
 
 and we can (if we would) implement next one with text as time zone

Yeah, I think a constructor should allow a text timezone.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services


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


Re: [HACKERS] pg_rewarm status

2013-12-17 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote:
 Amit Kapila amit.kapil...@gmail.com wrote:
 I have used pg_prewarm during some of work related to Buffer
 Management and other performance related work. It is quite
 useful utility.
 +1 for reviving this patch for 9.4

 Any other votes?

Where I would have used a prewarm utility is following an off-hours
VACUUM FREEZE run.  Where this maintenance made sense the only
downside I saw was a brief period in the mornings where the cache
was not populated with the hot data, and performance was somewhat
degraded until the cache settled in again.

So, +1.

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


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


Re: [HACKERS] SSL: better default ciphersuite

2013-12-17 Thread Bruce Momjian
On Tue, Dec 17, 2013 at 09:51:30AM -0500, Robert Haas wrote:
 On Sun, Dec 15, 2013 at 5:10 PM, James Cloos cl...@jhcloos.com wrote:
  For reference, see:
 
https://wiki.mozilla.org/Security/Server_Side_TLS
 
  for the currently suggested suite for TLS servers.
 ...
  But for pgsql, I'd leave off the !PSK; pre-shared keys may prove useful
  for some.  And RC4, perhaps, also should be !ed.
 
  And if anyone wants Kerberos tls-authentication, one could add
  KRB5-DES-CBC3-SHA, but that is ssl3-only.
 
  Once salsa20-poly1305 lands in openssl, that should be added to the
  start of the list.
 
 I'm starting to think we should just leave this well enough alone.  We
 can't seem to find two people with the same idea of what would be
 better than what we have now.  And of course the point of making it a
 setting in the first place is that each person can set it to whatever
 they deem best.

Yes, I am seeing that too.  Can we agree on one that is _better_ than
what we have now, even if we can't agree on a _best_ one?

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


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


Re: [HACKERS] pg_rewarm status

2013-12-17 Thread Robert Haas
On Tue, Dec 17, 2013 at 11:02 AM, Jim Nasby j...@nasby.net wrote:
 On 12/17/13, 8:34 AM, Robert Haas wrote:

 On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila amit.kapil...@gmail.com
 wrote:

 I have used pg_prewarm during some of work related to Buffer Management
 and
 other performance related work. It is quite useful utility.
 +1 for reviving this patch for 9.4


 Any other votes?


 We've had to manually code something that runs EXPLAIN ANALYZE SELECT * from
 a bunch of tables to warm our caches after a restart, but there's numerous
 flaws to that approach obviously.

 Unfortunately, what we really need to warm isn't the PG buffers, it's the FS
 cache, which I suspect this won't help. But I still see where just
 pg_buffers would be useful for a lot of folks, so +1.

It'll do either one.  For the FS cache, on Linux, you can also use pgfincore.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] patch: make_timestamp function

2013-12-17 Thread Tom Lane
Alvaro Herrera alvhe...@2ndquadrant.com writes:
 Yeah, I think a constructor should allow a text timezone.

Yes.  I think a numeric timezone parameter is about 99% useless,
and if you do happen to need that behavior you can just cast the
numeric to text no?

regards, tom lane


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


Re: [HACKERS] patch: make_timestamp function

2013-12-17 Thread Pavel Stehule
2013/12/17 Tom Lane t...@sss.pgh.pa.us

 Alvaro Herrera alvhe...@2ndquadrant.com writes:
  Yeah, I think a constructor should allow a text timezone.

 Yes.  I think a numeric timezone parameter is about 99% useless,
 and if you do happen to need that behavior you can just cast the
 numeric to text no?


yes, it is possible. Although fully numeric API is much more consistent.

Pavel



 regards, tom lane



Re: [HACKERS] ANALYZE sampling is too good

2013-12-17 Thread Heikki Linnakangas

On 12/17/2013 12:06 AM, Jeff Janes wrote:

On Mon, Dec 9, 2013 at 3:14 PM, Heikki Linnakangas
hlinnakan...@vmware.comwrote:


  I took a stab at using posix_fadvise() in ANALYZE. It turned out to be
very easy, patch attached. Your mileage may vary, but I'm seeing a nice
gain from this on my laptop. Taking a 3 page sample of a table with
717717 pages (ie. slightly larger than RAM), ANALYZE takes about 6 seconds
without the patch, and less than a second with the patch, with
effective_io_concurrency=10. If anyone with a good test data set loaded
would like to test this and post some numbers, that would be great.


Performance is often chaotic near transition points, so I try to avoid data
sets that are slightly bigger or slightly smaller than RAM (or some other
limit).

Do you know how many io channels your SSD has (or whatever the term of art
is for SSD drives)?


No idea. It's an Intel 335.


On a RAID with 12 spindles, analyzing pgbench_accounts at scale 1000 (13GB)
with 4 GB of RAM goes from ~106 seconds to ~19 seconds.

However, I'm not sure what problem we want to solve here.


The case that Greg Stark mentioned in the email starting this thread is 
doing a database-wide ANALYZE after an upgrade. In that use case, you 
certainly want to get it done as quickly as possible, using all the 
available resources.



I certainly would not wish to give a background maintenance process
permission to confiscate my entire RAID throughput for its own
operation.


Then don't set effective_io_concurrency. If you're worried about that, 
you probably wouldn't want any other process to monopolize the RAID 
array either.



Perhaps this could only be active for explicit analyze, and only if
vacuum_cost_delay=0?


That would be a bit weird, because ANALYZE in general doesn't obey 
vacuum_cost_delay. Maybe it should, though...



Perhaps there should be something like alter background role autovac set
  Otherwise we are going to end up with an autovacuum_* shadow
parameter for many of our parameters, see autovacuum_work_mem discussions.


Yeah, so it seems.

- Heikki


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


Re: [HACKERS] patch: make_timestamp function

2013-12-17 Thread Pavel Stehule
2013/12/17 Alvaro Herrera alvhe...@2ndquadrant.com

 Pavel Stehule escribió:

  but AT TIME ZONE allows double (but decimal parts is ignored quietly)
 
  postgres=# select make_time(10,20,30) at time zone '+10.2';
timezone
  -
   23:20:30-10
 
  so I propose (and I implemented) a variant with int as time zone
 
  and we can (if we would) implement next one with text as time zone

 Yeah, I think a constructor should allow a text timezone.


is there some simple way, how to parse text time zone?

Regards

Pavel



 --
 Álvaro Herrerahttp://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services



Re: [HACKERS] row security roadmap proposal

2013-12-17 Thread Robert Haas
On Mon, Dec 16, 2013 at 3:12 PM, Gregory Smith gregsmithpg...@gmail.com wrote:
 On 12/16/13 9:36 AM, Craig Ringer wrote:

 - Finish and commit updatable security barrier views. I've still got a
 lot of straightening out to do there.

 I don't follow why you've put this part first.  It has a lot of new
 development and the risks that go along with that, but the POC projects I've
 been testing are more interested in the view side issues.

I don't really see a way that any of this can work without that.  To
be clear, that work is required even just for read-side security.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] Patch: show xid and xmin in pg_stat_activity and pg_stat_replication

2013-12-17 Thread Robert Haas
On Tue, Dec 17, 2013 at 9:58 AM, Christian Kruse
christ...@2ndquadrant.com wrote:
 Hi,

 attached you will find a patch for showing the current transaction id
 (xid) and the xmin of a backend in pg_stat_activty and the xmin in
 pg_stat_replication.

 This may be helpful when looking for the cause of bloat.

 I added two new struct members in PgBackendStatus which get filled in
 pgstat_read_current_status() and slightly modified the catalog schema
 and the pg_stat_get_activity() procedure.

 I'm not sure if it is a good idea to gather the data in
 pgstat_read_current_status(), but I chose to do it this way
 nonetheless because otherwise I would have to create collector
 functions like pgstat_report_xid_assignment() /
 pgstat_report_xmin_changed() (accordingly to
 pgstat_report_xact_timestamp()) which may result in a performance hit.

Please add your patch here so we don't lose track of it:

https://commitfest.postgresql.org/action/commitfest_view/open

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] patch: make_timestamp function

2013-12-17 Thread Pavel Stehule
Hello


2013/12/17 Pavel Stehule pavel.steh...@gmail.com




 2013/12/17 Tom Lane t...@sss.pgh.pa.us

 Alvaro Herrera alvhe...@2ndquadrant.com writes:
  Yeah, I think a constructor should allow a text timezone.

 Yes.  I think a numeric timezone parameter is about 99% useless,
 and if you do happen to need that behavior you can just cast the
 numeric to text no?


 yes, it is possible. Although fully numeric API is much more consistent.


I was wrong - there are timezones with minutes like Iran = '1:30';

so int in hours is bad type - so only text is probably best

Pavel


 Pavel



 regards, tom lane





Re: [HACKERS] Extension Templates S03E11

2013-12-17 Thread Dimitri Fontaine
Stephen Frost sfr...@snowman.net writes:
 My thinking is that if we invent a new mechanism for extensions that are
 not managed like contribs, we will find out that only contribs are going
 to be using extensions.

 That's only accurate if the new mechanism supports .so's, which seems
 unlikely to be the case.

Really?

Look at dynamic_library_path, then at a classic CREATE FUNCTION command
that maps into a C provided symbol:

  CREATE OR REPLACE FUNCTION prefix_range_in(cstring)
  RETURNS prefix_range AS '$libdir/prefix' LANGUAGE C IMMUTABLE STRICT;

A packaging or distribution software will have no problem removing the
'$libdir/' part of the magic AS string here. Once removed, prefix.so
will be loaded from anywhere on the file system paths listed into the
dynamic_library_path GUC.

So now, you don't need anymore to have file system write privileges into
a central place owned by root, it can be anywhere else, and the backend
hooks, when properly setup, will be able to benefit from that.

The missing bits are where to find the extension control files and
scripts.

The only reason why the current proposal mention *nothing* about how to
deal with modules (.so etc) is because each and every time a mention is
made about that problem, the answer from Tom is “rejected, full stop”.

 What I think we'd end up with is a split
 between extensions, which would be things containing .so's, and
 libraries or what-have-you, which would be more-or-less everything
 else.  That kind of a break-down strikes me as perfectly reasonable.

Only if it's the best we can do.

 There would also be flexability in that an author might choose to use an
 extension even in cases where it's not strictly necessary to do so, for
 whatever reason they want.

Note that of course you can still install proper OS packages when we
ship with support for Extension Templates.

 I'd like to see extensions improved.  I don't feel like the proposed
 'extension templates' is the way to do that because I don't think it
 really solves anything and it adds a layer that strikes me as wholly
 unnecessary.

You still didn't propose any other way to have at it, where it's already
my fourth detailed proposal. I did spend time on designing what I think
you're trying to say hand-wavely in that exchange, and I don't quite
like the result, as I see now way for it not to entirely deprecate
extensions.

Maybe the proper answer is that we should actually confine extensions to
being the way to install contribs and nothing else, and deprecate them
for cases where you don't have an OS level package.  It seems really
strange to build a facility with such a generic name as “extension” only
to resist changing any of it, then stop using it at first opportunity.

Also, I'm not sure about the consequences in terms of user trust if we
build something new to solve a use case that looks so much the same.

 However, as I understand it from the various discussions on this topic
 outside of this list, the immediate concern is the need for a multi-OS
 extension distribution network with support for binaries, .so's and
 .dll's and whatever else, to make installing extensions easier for
 developers on various platforms.  I'm all for someone building that and
 dealing with the issues associated with that, but building a client for
 it in core, either in a way where a backend would reach out and
 download the files or accepting binary .so's through the frontend
 protocol, isn't the first step in that and I very much doubt it would
 ever make sense.

That's exactly the reason why the first piece of that proposal has
absolutely nothing to do with building said client, and is all about how
NOT to have to build it in core *ever*.

If you don't like what I'm building because it's not solving the problem
you want to solve… well don't use what I'm building, right?

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support


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


Re: [HACKERS] planner missing a trick for foreign tables w/OR conditions

2013-12-17 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Mon, Dec 16, 2013 at 6:59 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 The hard part is not extracting the partial qual.  The hard part is
 trying to make sure that adding this entirely-redundant scan qual doesn't
 catastrophically degrade join size estimates.

 OK, I had a feeling that's where the problem was likely to be.  Do you
 have any thoughts about a more principled way of solving this problem?
 I mean, off-hand, it's not clear to me that the comments about this
 being a MAJOR HACK aren't overstated.

Well, the business about injecting the correction by adjusting a cached
selectivity is certainly a hack, but it's not one that I think is urgent
to get rid of; I don't foresee anything that's likely to break it soon.

 I might be missing something, but I suspect it works fine if every
 path for the relation is generating the same rows.

I had been thinking it would fall down if there are several OR conditions
affecting different collections of rels, but after going through the math
again, I'm now thinking I was wrong and it does in fact work out.  As you
say, we do depend on all paths generating the same rows, but since the
extracted single-rel quals are inserted as plain baserestrictinfo quals,
that'll be true.

A bigger potential objection is that we're opening ourselves to larger
problems with estimation failures due to correlated qual conditions, but
again I'm finding that the math doesn't bear that out.  It's reasonable
to assume that our estimate for the extracted qual will be better than
our estimate for the OR as a whole, so our adjusted size estimates for
the filtered base relations are probably OK.  And the adjustment to the
OR clause selectivity means that the size estimate for the join comes
out exactly the same.  We'll actually be better off than with what is
likely to happen now, which is that people manually extract the simplified
condition and insert it into the query explicitly.  PG doesn't realize
that that's redundant and so will underestimate the join size.

So at this point I'm pretty much talked into it.  We could eliminate the
dependence on indexes entirely, and replace this code with a step that
simply tries to pull single-base-relation quals out of ORs wherever it can
find one.  You could argue that the produced quals would sometimes not be
worth testing for, but we could apply a heuristic that says to forget it
unless the estimated selectivity of the extracted qual is less than,
I dunno, 0.5 maybe.  (I wonder if it'd be worth inserting a check that
there's not already a manually-generated equivalent clause, too ...)

A very nice thing about this is we could do this step ahead of relation
size estimate setting and thus remove the redundant work that currently
happens in set_plain_rel_size when the optimization fires.  Which is
another aspect of the current code that's a hack, so getting rid of it
would be a net reduction in hackiness.

regards, tom lane


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


Re: [HACKERS] patch: make_timestamp function

2013-12-17 Thread Bruce Momjian
On Tue, Dec 17, 2013 at 06:07:38PM +0100, Pavel Stehule wrote:
 Hello
 
 
 2013/12/17 Pavel Stehule pavel.steh...@gmail.com
 
 
 
 
 2013/12/17 Tom Lane t...@sss.pgh.pa.us
 
 Alvaro Herrera alvhe...@2ndquadrant.com writes:
  Yeah, I think a constructor should allow a text timezone.
 
 Yes.  I think a numeric timezone parameter is about 99% useless,
 and if you do happen to need that behavior you can just cast the
 numeric to text no?
 
 
 yes, it is possible. Although fully numeric API is much more consistent.
 
 
 
 I was wrong - there are timezones with minutes like Iran = '1:30';
 
 so int in hours is bad type - so only text is probably best

I think India is the big non-integer timezone offset country:

http://www.timeanddate.com/worldclock/city.html?n=176
UTC/GMT +5:30 hours

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


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


Re: [HACKERS] pg_rewarm status

2013-12-17 Thread Jeff Janes
On Tue, Dec 17, 2013 at 8:02 AM, Jim Nasby j...@nasby.net wrote:

 On 12/17/13, 8:34 AM, Robert Haas wrote:

 On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila amit.kapil...@gmail.com
 wrote:

 I have used pg_prewarm during some of work related to Buffer Management
 and
 other performance related work. It is quite useful utility.
 +1 for reviving this patch for 9.4


 Any other votes?


 We've had to manually code something that runs EXPLAIN ANALYZE SELECT *
 from a bunch of tables to warm our caches after a restart, but there's
 numerous flaws to that approach obviously.

 Unfortunately, what we really need to warm isn't the PG buffers, it's the
 FS cache, which I suspect this won't help. But I still see where just
 pg_buffers would be useful for a lot of folks, so +1.


Since it doesn't use directIO, you can't warm the PG buffers without also
warming FS cache as a side effect.  That is why I like 'buffer' as the
default--if the data fits in shared_buffers, it warm those, otherwise it at
least warms the FS.  If you want to only warm the FS cache, you can use
either the 'prefetch' or 'read' modes instead.

 Cheers,

Jeff


Re: [HACKERS] Extension Templates S03E11

2013-12-17 Thread Stephen Frost
* Dimitri Fontaine (dimi...@2ndquadrant.fr) wrote:
 Stephen Frost sfr...@snowman.net writes:
  My thinking is that if we invent a new mechanism for extensions that are
  not managed like contribs, we will find out that only contribs are going
  to be using extensions.
 
  That's only accurate if the new mechanism supports .so's, which seems
  unlikely to be the case.
 
 Really?

Yes.  The 'new mechanism' to which I was referring was when the entire
XXX (extension, library, package, whatever) is in the PG catalog and not
managed through files on the filesystem, as contrib-like extensions are.

I'm quite aware that what you're asking for is technically possible-
that's not what this discussion is about.

 The only reason why the current proposal mention *nothing* about how to
 deal with modules (.so etc) is because each and every time a mention is
 made about that problem, the answer from Tom is “rejected, full stop”.

Perhaps I'm not making myself clear here, but *I agree with Tom* on this
point.

  There would also be flexability in that an author might choose to use an
  extension even in cases where it's not strictly necessary to do so, for
  whatever reason they want.
 
 Note that of course you can still install proper OS packages when we
 ship with support for Extension Templates.

With the various naming conflicts and other risks associated with doing
that, which I don't believe were very clearly addressed.  An
off-the-cuff answer to that issue is not sufficient, imv.

 You still didn't propose any other way to have at it, where it's already
 my fourth detailed proposal. 

I didn't outline a proposal which provides what you want, no.  That was
intentional.

 I did spend time on designing what I think
 you're trying to say hand-wavely in that exchange, and I don't quite
 like the result, as I see now way for it not to entirely deprecate
 extensions.

I don't think we need to, nor should we, deprecate extensions entirely
when that's the approach which *should be* used for .so requiring
extensions.  Obviously, that's my opinion, and you don't agree with it,
and it seems neither of us is willing to shift from that position.

 Maybe the proper answer is that we should actually confine extensions to
 being the way to install contribs and nothing else, and deprecate them
 for cases where you don't have an OS level package.  It seems really
 strange to build a facility with such a generic name as “extension” only
 to resist changing any of it, then stop using it at first opportunity.

I'm open to changing how extensions work, to adding dependency
information and making other improvements.  Being interested in
improving the extension system doesn't mean I'm required to support
shipping .so's in this manner or installing text script blobs into
catalog tables.

  However, as I understand it from the various discussions on this topic
  outside of this list, the immediate concern is the need for a multi-OS
  extension distribution network with support for binaries, .so's and
  .dll's and whatever else, to make installing extensions easier for
  developers on various platforms.  I'm all for someone building that and
  dealing with the issues associated with that, but building a client for
  it in core, either in a way where a backend would reach out and
  download the files or accepting binary .so's through the frontend
  protocol, isn't the first step in that and I very much doubt it would
  ever make sense.
 
 That's exactly the reason why the first piece of that proposal has
 absolutely nothing to do with building said client, and is all about how
 NOT to have to build it in core *ever*.

You can already build what you're after without extension templates
entirely, if you're allowing files to be stashed out on the filesystem
anywhere.  Your argument that you need root doesn't hold any water with
me on this issue- there's quite a few mechanisms out there already which
allow you to trivially become root.  You can write pl/perlu which sudo's
and apt-get installs your favorite extension, if you like.  That doesn't
mean we should build a system into core which tries to do that for you.

And, yes, I know that you pushed for and got the GUC in to allow you to
have other places to pull .so's from.  Having that flexibility doesn't
mean we have to support populating that directory from PG.  You probably
would have been better off pushing for a GUC that allowed a '.d' like
directory system for extensions to be defined in.  That *still* doesn't
require extension templates, storing SQL scripts as text blobs in
catalog tables, and you can even avoid the whole 'root' concern if you
want.

 If you don't like what I'm building because it's not solving the problem
 you want to solve… well don't use what I'm building, right?

I'm pretty sure that I've pointed out a number of issues that go well
beyond not liking it.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Why no INSTEAD OF triggers on tables?

2013-12-17 Thread Josh Berkus
On 12/16/2013 07:53 PM, Robert Haas wrote:
 So, put a BEFORE trigger, and make it return NULL.  Same effect,
 different notation.

NOT the same:

Master partition table with BEFORE trigger:

josh=# insert into a ( id, val ) values ( 23, 'test' ), ( 24, 'test'),
(25,'test');

INSERT 0 0
^^^

View with INSTEAD OF trigger:

josh=# insert into a_v ( id, val ) values ( 23, 'test' ), ( 24, 'test'),
(25,'test');

INSERT 0 3
^^^

The difference here is that the INSTEAD OF trigger returns a
rows-affected count, and the BEFORE trigger does not (it returns 0).
Some drivers and ORMs, most notably Hibernate, check this rows-returned
count, and error if they don't match the rows sent.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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


Re: [HACKERS] patch: make_timestamp function

2013-12-17 Thread Michael Weber
There are also timezones off by 15 minutes (although only a few, mainly
Nepal).

The only integer representation I've ever seen is in 15 minutes units.

http://www.timeanddate.com/time/time-zones-interesting.html


On Tue, Dec 17, 2013 at 12:33 PM, Bruce Momjian br...@momjian.us wrote:

 On Tue, Dec 17, 2013 at 06:07:38PM +0100, Pavel Stehule wrote:
  Hello
 
 
  2013/12/17 Pavel Stehule pavel.steh...@gmail.com
 
 
 
 
  2013/12/17 Tom Lane t...@sss.pgh.pa.us
 
  Alvaro Herrera alvhe...@2ndquadrant.com writes:
   Yeah, I think a constructor should allow a text timezone.
 
  Yes.  I think a numeric timezone parameter is about 99% useless,
  and if you do happen to need that behavior you can just cast the
  numeric to text no?
 
 
  yes, it is possible. Although fully numeric API is much more
 consistent.
 
 
 
  I was wrong - there are timezones with minutes like Iran = '1:30';
 
  so int in hours is bad type - so only text is probably best

 I think India is the big non-integer timezone offset country:

 http://www.timeanddate.com/worldclock/city.html?n=176
 UTC/GMT +5:30 hours

 --
   Bruce Momjian  br...@momjian.ushttp://momjian.us
   EnterpriseDB http://enterprisedb.com

   + Everyone has their own god. +


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



Re: [HACKERS] Minor comment improvement

2013-12-17 Thread Bruce Momjian
On Fri, Dec 13, 2013 at 06:53:02PM +0900, Etsuro Fujita wrote:
 This is a small patch a bit improving a comment in
 src/backend/commands/copy.c.

Thanks, applied.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


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


Re: [HACKERS] Extension Templates S03E11

2013-12-17 Thread Tom Lane
Dimitri Fontaine dimi...@2ndquadrant.fr writes:
 Stephen Frost sfr...@snowman.net writes:
 That's only accurate if the new mechanism supports .so's, which seems
 unlikely to be the case.

 Really?

Yes, really.

 So now, you don't need anymore to have file system write privileges into
 a central place owned by root, it can be anywhere else,

Modern OSes have security checks that can prevent loading libraries from
random places.  This is widely seen as not merely a good thing, but
security-critical for network-exposed daemons.  Of which we are one.

I keep telling you this, and it keeps not sinking in.  One more time: any
feature that does what you want will be dead on arrival so far as vendors
like Red Hat are concerned.  I don't care how creatively you argue for it,
they will refuse to ship it (or at least refuse to disable the SELinux
policy that prevents it).  Period.  Please stop wasting my time with
suggestions otherwise, because it won't happen.

So what we have left to discuss is whether we want to develop, and base a
community extension-distribution infrastructure on, a mechanism that some
popular vendors will actively block.  I'm inclined to think it's a bad
idea, but I just work here.

 If you don't like what I'm building because it's not solving the problem
 you want to solve… well don't use what I'm building, right?

What worries me is that time and effort will go into this instead of
something that would be universally acceptable/useful.  I grant that
there are some installations whose security policies are weak enough
that they could use what you want to build.  But I'm not sure how
many there are, and I'm worried about market fragmentation if we need
to have more than one distribution mechanism.

Of course, we're already talking about two distribution infrastructures
(one for packages including .so's, and one for those without).  I see no
way around that unless we settle for the status quo.  But what you're
suggesting will end up with three distribution infrastructures, with
duplicative methods for packages including .so's depending on whether
they're destined for security-conscious or less-security-conscious
platforms.  I don't want to end up with that.

regards, tom lane


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


Re: [HACKERS] planner missing a trick for foreign tables w/OR conditions

2013-12-17 Thread Robert Haas
On Tue, Dec 17, 2013 at 12:28 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 I had been thinking it would fall down if there are several OR conditions
 affecting different collections of rels, but after going through the math
 again, I'm now thinking I was wrong and it does in fact work out.  As you
 say, we do depend on all paths generating the same rows, but since the
 extracted single-rel quals are inserted as plain baserestrictinfo quals,
 that'll be true.

OK.

 A bigger potential objection is that we're opening ourselves to larger
 problems with estimation failures due to correlated qual conditions, but
 again I'm finding that the math doesn't bear that out.  It's reasonable
 to assume that our estimate for the extracted qual will be better than
 our estimate for the OR as a whole, so our adjusted size estimates for
 the filtered base relations are probably OK.  And the adjustment to the
 OR clause selectivity means that the size estimate for the join comes
 out exactly the same.  We'll actually be better off than with what is
 likely to happen now, which is that people manually extract the simplified
 condition and insert it into the query explicitly.  PG doesn't realize
 that that's redundant and so will underestimate the join size.

I had not thought of that, but it seems like a valid point.

 So at this point I'm pretty much talked into it.  We could eliminate the
 dependence on indexes entirely, and replace this code with a step that
 simply tries to pull single-base-relation quals out of ORs wherever it can
 find one.  You could argue that the produced quals would sometimes not be
 worth testing for, but we could apply a heuristic that says to forget it
 unless the estimated selectivity of the extracted qual is less than,
 I dunno, 0.5 maybe.

This is an area where I think things are very different from local and
remote tables.  For a local table, the cost of transmitting a row from
one plan node to another is basically zero.  For a remote table, it's
potentially far higher, although unfortunately it's hard to know
exactly.  But if the qual is cheap to evaluate, and we're getting back
a lot of rows, I suspect even eliminating 5-10% of them could work out
to a win.  With a local table, 50% sounds like a reasonable number.

Another point to ponder is that there could be places where this
actually changes the plan significantly for the better.  Pre-filtering
one side of a join might, for example, reduce the amount of data on
one side to the point where a hash join is chosen over some other
strategy.  I don't know that this will actually help all that many
people but the best case is pretty dramatic for those it does help:
the partial qual might be almost as selective as the join condition
itself.

  (I wonder if it'd be worth inserting a check that
 there's not already a manually-generated equivalent clause, too ...)

Sounds a little too clever IMHO.

 A very nice thing about this is we could do this step ahead of relation
 size estimate setting and thus remove the redundant work that currently
 happens in set_plain_rel_size when the optimization fires.  Which is
 another aspect of the current code that's a hack, so getting rid of it
 would be a net reduction in hackiness.

I'm not sure that would save anything measurable performance-wise, but
the hackiness reduction would be nice to have.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] Extension Templates S03E11

2013-12-17 Thread Josh Berkus
On 12/16/2013 11:44 AM, Tom Lane wrote: Right.  I think a lot of the
tension comes from people being unconvinced
 that the existing extension feature is an ideal model for this sort of
 use-case.  Extensions were mainly designed around the notion of a .so
 with some SQL datatype/function/etc declarations that have to match up
 with the C code.  So it made sense for them to be relatively static things
 that live in the filesystem.  Notably, if you're migrating to a new PG
 major version, you're at the very least gonna have to recompile the C code
 and quite likely will need to change it some.  So adapting the SQL code
 if needed goes along with that, and would never be automatic in any case.

I see what you mean.  On the other hand:

a) introducing a new concept would require a new reserved word

b) it would also require figuring out how it interacts with extensions

c) extensions already have versioning, which this feature needs

d) extensions already have dependancies, which this feature needs

While it splits Extensions into two slightly different concepts, I find
that on the whole less confusing than the alternative.

On 12/16/2013 05:17 PM, Jim Nasby wrote:
 Somewhat related to this, I really wish Postgres had the idea of a
 class, that was allowed to contain any type of object and could be
 instantiated when needed. For example, if we had an address class,
 we could instantiate it once for tracking our customer addresses, and a
 second time for tracking the addresses customers supply for their
 employers. Such a mechanism would probably be ideal for what we need,
 but of course you'd still have the question of how to load a class
 definition that someone else has published.

Well, the idea originally (POSTGRES) was for the Type, Domain, and
Inheritance system to do just what you propose.  Nobody ever worked out
all the practicalities and gotchas to make it really work in production,
though.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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


Re: [HACKERS] Extension Templates S03E11

2013-12-17 Thread Simon Riggs
On 13 December 2013 18:42, Stephen Frost sfr...@snowman.net wrote:
 * Jeff Davis (pg...@j-davis.com) wrote:
 For what it's worth, I think the idea of extension templates has good
 conceptual integrity. Extensions are external blobs. To make them work
 more smoothly in several ways, we move them into the catalog. They have
 pretty much the same upsides and downsides of our existing extensions,
 aside from issues directly related to filesystem vs. catalog.

 I've never particularly liked the idea that extensions are external
 blobs, to be honest.

I've been reading this, trying to catch back up with hackers. This
thread is amazing because this feature ought to be a shoe-in.

Jeff expresses his points politely, but not strongly enough. I agree with him.

I keep seeing people repeat I don't like blobs as if that were an
objection. There is no danger or damage from doing this. I can't see
any higher beauty that we're striving for by holding out. Why not
allow the user to choose XML, JSON, YAML, or whatever they choose.

Some things need to wait for the right design, like RLS, for a variety
of reasons. I don't see any comparison here and I can't see any reason
for a claim of veto on grounds of higher wisdom to apply to this case.

Blocking this stops nothing, it just forces people to do an extra
non-standard backflip to achieve their goals. Is that what we want?
Why?

 Stephen had some legitimate concerns. I don't entirely agree, but they
 are legitimate concerns, and we don't want to just override them.

 At the same time, I'm skeptical of the alternatives Stephen offered
 (though I don't think he intended them as a full proposal).

 It was more thoughts on how I'd expect these things to work.  I've also
 been talking to David about what he'd like to see done with PGXN and his
 thinking was a way to automate creating RPMs and DEBs based on PGXN spec
 files, but he points out that the challenge there is dealing with
 external dependencies.

 So right now I'm discouraged about the whole idea of installing
 extensions using SQL. I don't see a lot of great options. On top of
 that, the inability to handle native code limits the number of
 extensions that could make use of such a facility, which dampens my
 enthusiasm.

 Yeah, having looked at PGXN, it turns out that some 80+% of the
 extensions there have .c code included, something well beyond what I was
 expecting, but most of those cases also look to have external
 dependencies (eg: FDWs), which really makes me doubt this notion that
 they could be distributed independently and outside of the OS packaging
 system (or that it would be a particularly good idea to even try...).

That is clear evidence that the packaging is getting in the way of
extensions that don't include binary programs.

My only personal interest in this is to stimulate the writing of
further extensions, which is fairly clearly hampered by the overhead
required for packaging.

Who needs old fashioned package management? Some bigger extensions
need it. Smaller ones don't. Who are we to force people to distribute
their wares in only certain ways?

I can't see any reason to block this, nor any better design than the
flexible, neutral and simple one proposed. If there's some secret
reason to block this, please let me know off list cos I currently
don't get it at all.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


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


Re: [HACKERS] row security roadmap proposal

2013-12-17 Thread Simon Riggs
On 16 December 2013 14:36, Craig Ringer cr...@2ndquadrant.com wrote:

 - Decide on and implement a structure for row-security functionality its
 self. I'm persuaded by Robert's comments here, that whatever we expose
 must be significantly more usable than a DIY on top of views (with the
 fix for updatable security barrier views to make that possible). I
 outlined the skeleton of a possible design in my earlier post, with the
 heirachical and non-heirachical access labels. Should be implemented
 using the same API we expose for extensions (like SEPostgresql RLS).

That part isn't clear why we must do better than that.

Having declarative security is a huge step forward, in just the same
way that updateable views were. They save the need for writing scripts
to implement things, rather than just having a useful default.

If there is a vision for that, lets see the vision. And then decide
whether its worth waiting for.

Personally, I see no reason not to commit the syntax we have now. So
people can see what we'll be supporting, whenever that is.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


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


Re: [HACKERS] Extension Templates S03E11

2013-12-17 Thread Stephen Frost
* Simon Riggs (si...@2ndquadrant.com) wrote:
 I keep seeing people repeat I don't like blobs as if that were an
 objection. There is no danger or damage from doing this. I can't see
 any higher beauty that we're striving for by holding out. Why not
 allow the user to choose XML, JSON, YAML, or whatever they choose.

I have no idea where you're going with this, but I *do* object to
sticking an SQL script which defines a bunch of objects into a catalog
table *right next to where they are properly defined*.  There's just no
sense in it that I can see, except that it happens to mimic what we do
today- to no particular purpose.

 Blocking this stops nothing, it just forces people to do an extra
 non-standard backflip to achieve their goals. Is that what we want?
 Why?

It's hardly non-standard when it's required for 80+% of the extensions
that exist today anyway.

 That is clear evidence that the packaging is getting in the way of
 extensions that don't include binary programs.

I'm totally on-board with coming up with a solution for extensions which
do not include .so's.  Avoiding mention of the .so issue doesn't somehow
change this solution into one which actually solves the issue around
non-binary extensions.

 My only personal interest in this is to stimulate the writing of
 further extensions, which is fairly clearly hampered by the overhead
 required for packaging.

I'm not convinced of that but I agree that we can do better and would
like to see a solution which actually makes progress in that regard.  I
don't feel that this does that- indeed, it hardly changes the actual
packaging effort required of extension authors at all.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] preserving forensic information when we freeze

2013-12-17 Thread Robert Haas
On Wed, Dec 11, 2013 at 5:25 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
 Andres Freund escribió:
 What's your plan to commit this? I'd prefer to wait till Alvaro's
 freezing changes get in, so his patch will look the same in HEAD and
 9.3. But I think he plans to commit soon.

 Yes, I do.  I'm waiting on feedback on the patch I posted this
 afternoon, so if there's nothing more soon I will push it.

That's done now, so I've rebased this patch and hacked on it a bit
more.  The latest version is attached.  Review would be appreciate in
case I've goofed up anything critical, especially around adjusting
things over top of Alvaro's freezing changes.  But I think this is
more or less ready to go.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/contrib/pageinspect/heapfuncs.c b/contrib/pageinspect/heapfuncs.c
index 6d8f6f1..a78cff3 100644
--- a/contrib/pageinspect/heapfuncs.c
+++ b/contrib/pageinspect/heapfuncs.c
@@ -162,7 +162,7 @@ heap_page_items(PG_FUNCTION_ARGS)
 
 			tuphdr = (HeapTupleHeader) PageGetItem(page, id);
 
-			values[4] = UInt32GetDatum(HeapTupleHeaderGetXmin(tuphdr));
+			values[4] = UInt32GetDatum(HeapTupleHeaderGetRawXmin(tuphdr));
 			values[5] = UInt32GetDatum(HeapTupleHeaderGetRawXmax(tuphdr));
 			values[6] = UInt32GetDatum(HeapTupleHeaderGetRawCommandId(tuphdr)); /* shared with xvac */
 			values[7] = PointerGetDatum(tuphdr-t_ctid);
diff --git a/src/backend/access/common/heaptuple.c b/src/backend/access/common/heaptuple.c
index e39b977..347d616 100644
--- a/src/backend/access/common/heaptuple.c
+++ b/src/backend/access/common/heaptuple.c
@@ -539,7 +539,7 @@ heap_getsysattr(HeapTuple tup, int attnum, TupleDesc tupleDesc, bool *isnull)
 			result = ObjectIdGetDatum(HeapTupleGetOid(tup));
 			break;
 		case MinTransactionIdAttributeNumber:
-			result = TransactionIdGetDatum(HeapTupleHeaderGetXmin(tup-t_data));
+			result = TransactionIdGetDatum(HeapTupleHeaderGetRawXmin(tup-t_data));
 			break;
 		case MaxTransactionIdAttributeNumber:
 			result = TransactionIdGetDatum(HeapTupleHeaderGetRawXmax(tup-t_data));
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index db683b1..a9fcd98 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -1738,7 +1738,7 @@ heap_hot_search_buffer(ItemPointer tid, Relation relation, Buffer buffer,
 		 */
 		if (TransactionIdIsValid(prev_xmax) 
 			!TransactionIdEquals(prev_xmax,
- HeapTupleHeaderGetXmin(heapTuple-t_data)))
+ HeapTupleHeaderGetRawXmin(heapTuple-t_data)))
 			break;
 
 		/*
@@ -1908,7 +1908,7 @@ heap_get_latest_tid(Relation relation,
 		 * tuple.  Check for XMIN match.
 		 */
 		if (TransactionIdIsValid(priorXmax) 
-		  !TransactionIdEquals(priorXmax, HeapTupleHeaderGetXmin(tp.t_data)))
+		  !TransactionIdEquals(priorXmax, HeapTupleHeaderGetRawXmin(tp.t_data)))
 		{
 			UnlockReleaseBuffer(buffer);
 			break;
@@ -2257,13 +2257,10 @@ heap_prepare_insert(Relation relation, HeapTuple tup, TransactionId xid,
 	tup-t_data-t_infomask = ~(HEAP_XACT_MASK);
 	tup-t_data-t_infomask2 = ~(HEAP2_XACT_MASK);
 	tup-t_data-t_infomask |= HEAP_XMAX_INVALID;
+	HeapTupleHeaderSetXmin(tup-t_data, xid);
 	if (options  HEAP_INSERT_FROZEN)
-	{
-		tup-t_data-t_infomask |= HEAP_XMIN_COMMITTED;
-		HeapTupleHeaderSetXmin(tup-t_data, FrozenTransactionId);
-	}
-	else
-		HeapTupleHeaderSetXmin(tup-t_data, xid);
+		HeapTupleHeaderSetXminFrozen(tup-t_data);
+
 	HeapTupleHeaderSetCmin(tup-t_data, cid);
 	HeapTupleHeaderSetXmax(tup-t_data, 0);		/* for cleanliness */
 	tup-t_tableOid = RelationGetRelid(relation);
@@ -5094,7 +5091,7 @@ l4:
 		 * the end of the chain, we're done, so return success.
 		 */
 		if (TransactionIdIsValid(priorXmax) 
-			!TransactionIdEquals(HeapTupleHeaderGetXmin(mytup.t_data),
+			!TransactionIdEquals(HeapTupleHeaderGetRawXmin(mytup.t_data),
  priorXmax))
 		{
 			UnlockReleaseBuffer(buf);
@@ -5725,12 +5722,6 @@ heap_prepare_freeze_tuple(HeapTupleHeader tuple, TransactionId cutoff_xid,
 		TransactionIdPrecedes(xid, cutoff_xid))
 	{
 		frz-frzflags |= XLH_FREEZE_XMIN;
-
-		/*
-		 * Might as well fix the hint bits too; usually XMIN_COMMITTED will
-		 * already be set here, but there's a small chance not.
-		 */
-		frz-t_infomask |= HEAP_XMIN_COMMITTED;
 		changed = true;
 	}
 
@@ -5837,13 +5828,6 @@ heap_prepare_freeze_tuple(HeapTupleHeader tuple, TransactionId cutoff_xid,
 frz-frzflags |= XLH_INVALID_XVAC;
 			else
 frz-frzflags |= XLH_FREEZE_XVAC;
-
-			/*
-			 * Might as well fix the hint bits too; usually XMIN_COMMITTED
-			 * will already be set here, but there's a small chance not.
-			 */
-			Assert(!(tuple-t_infomask  HEAP_XMIN_INVALID));
-			frz-t_infomask |= HEAP_XMIN_COMMITTED;
 			changed = true;
 		}
 	}
@@ -5874,19 +5858,27 @@ heap_prepare_freeze_tuple(HeapTupleHeader tuple, TransactionId cutoff_xid,
 void
 heap_execute_freeze_tuple(HeapTupleHeader tuple, xl_heap_freeze_tuple *frz)
 {

Re: [HACKERS] Extension Templates S03E11

2013-12-17 Thread Heikki Linnakangas

On 12/17/2013 08:32 PM, Stephen Frost wrote:

* Simon Riggs (si...@2ndquadrant.com) wrote:

My only personal interest in this is to stimulate the writing of
further extensions, which is fairly clearly hampered by the overhead
required for packaging.


I'm not convinced of that but I agree that we can do better and would
like to see a solution which actually makes progress in that regard.  I
don't feel that this does that- indeed, it hardly changes the actual
packaging effort required of extension authors at all.


I'll repeat my requirement: the same extension must be installable the 
old way and the new way. I've lost track which of the ideas being 
discussed satisfy that requirement, but I object to any that doesn't.


Considering that, I don't see how any if this is going to reduce the 
overhead required for packaging. An extension author will write the 
extension exactly the same way he does today. Perhaps you meant the 
overhead of installing an extension, ie. the work that the DBA does, not 
the work that the extension author does?


- Heikki


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


[HACKERS] processing time zone

2013-12-17 Thread Pavel Stehule
I had to write obscure code for processing time zone and using it for
timestamptz

Datum
make_timestamptz_at_timezone(PG_FUNCTION_ARGS)
{
Timestamp timestamp;
text   *zone;
int tz;
chartzname[TZ_STRLEN_MAX + 1];
char   *lowzone;
int type,
val;
struct pg_tm tt,
   *tm = tt;
fsec_t  fsec;
TimestampTz result;
int session_tz;

timestamp = make_timestamp_internal(PG_GETARG_INT32(0), /* year */
 PG_GETARG_INT32(1),/* month */
 PG_GETARG_INT32(2),/* mday */
 PG_GETARG_INT32(3),/* hour */
 PG_GETARG_INT32(4),/* min */
 PG_GETARG_FLOAT8(5));  /* sec */

if (timestamp2tm(timestamp, NULL, tm, fsec, NULL, NULL) != 0)
ereport(ERROR,

(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
 errmsg(timestamp out of range)));

zone = PG_GETARG_TEXT_PP(6);

text_to_cstring_buffer(zone, tzname, sizeof(tzname));

if (DecodeTimezone(tzname, tz) != 0)
{
lowzone = downcase_truncate_identifier(tzname,

strlen(tzname),

false);
type = DecodeSpecial(0, lowzone, val);


if (type == TZ || type == DTZ)
tz = val * MINS_PER_HOUR;
else
{
pg_tz  *tzp;

tzp = pg_tzset(tzname);

if (tzp)
tz = DetermineTimeZoneOffset(tm, tzp);
else
{
ereport(ERROR,

(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
 errmsg(time zone \%s\
not recognized, tzname)));
tz =
0; /* keep compiler quiet */
}
}
}

elog(NOTICE, entry 0: %d, tz);

session_tz = DetermineTimeZoneOffset(tm, session_timezone);

PG_RETURN_TIMESTAMPTZ((TimestampTz) dt2local(timestamp, -tz));
}

It works

postgres=# select make_timestamptz(2014,12,17,21,06,37.7,'Europe/Moscow') ;
 make_timestamptz
--
 2014-12-17 18:06:37.7+01
(1 row)

postgres=# select '2014-12-17 21:06:37.7 Europe/Moscow'::timestamptz;
   timestamptz
--
 2014-12-17 18:06:37.7+01
(1 row)

Is some better way, how to parse time zone?

Regards

Pavel Stehule


Re: [HACKERS] row security roadmap proposal

2013-12-17 Thread Simon Riggs
On 17 December 2013 17:03, Robert Haas robertmh...@gmail.com wrote:
 On Mon, Dec 16, 2013 at 3:12 PM, Gregory Smith gregsmithpg...@gmail.com 
 wrote:
 On 12/16/13 9:36 AM, Craig Ringer wrote:

 - Finish and commit updatable security barrier views. I've still got a
 lot of straightening out to do there.

 I don't follow why you've put this part first.  It has a lot of new
 development and the risks that go along with that, but the POC projects I've
 been testing are more interested in the view side issues.

 I don't really see a way that any of this can work without that.  To
 be clear, that work is required even just for read-side security.

Not sure I'd say required, but its certainly desirable to have
updateable security barrier views in themselves. And it comes across
to me as a cleaner and potentially more performant way of doing the
security checks for RLS. So I think its the right thing to do to wait
for this, even if we can't do that for 9.4

Realistically, we have a significant amount of work before we're ready
to pass a high security audit based around these features.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


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


Re: [HACKERS] SSL: better default ciphersuite

2013-12-17 Thread Adrian Klaver

On 12/17/2013 08:26 AM, Bruce Momjian wrote:

On Tue, Dec 17, 2013 at 09:51:30AM -0500, Robert Haas wrote:

On Sun, Dec 15, 2013 at 5:10 PM, James Cloos cl...@jhcloos.com wrote:

For reference, see:

   https://wiki.mozilla.org/Security/Server_Side_TLS

for the currently suggested suite for TLS servers.

...

But for pgsql, I'd leave off the !PSK; pre-shared keys may prove useful
for some.  And RC4, perhaps, also should be !ed.

And if anyone wants Kerberos tls-authentication, one could add
KRB5-DES-CBC3-SHA, but that is ssl3-only.

Once salsa20-poly1305 lands in openssl, that should be added to the
start of the list.


I'm starting to think we should just leave this well enough alone.  We
can't seem to find two people with the same idea of what would be
better than what we have now.  And of course the point of making it a
setting in the first place is that each person can set it to whatever
they deem best.


Yes, I am seeing that too.  Can we agree on one that is _better_ than
what we have now, even if we can't agree on a _best_ one?



Agreed. I would note that what is being proposed is a default that helps 
those of us (myself included) that do not know ciphers in and out, start 
with reasonable expectation of protection. This is a GUC so it can be 
modified to suite personal taste.


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


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


Re: [HACKERS] pg_rewarm status

2013-12-17 Thread Robert Haas
On Tue, Dec 17, 2013 at 3:31 PM, MauMau maumau...@gmail.com wrote:
 Any other votes?

 +1
 Some customers requested:

 1. fill the database cache with frequently accessed data before starting or
 resuming service for their users (for the first time or after maintenance
 work), so that they can provide steady and predictable performance.

 2. pin some (reference or master) data in the database cache not to be
 evicted from the cache (like Oracle's KEEP buffer?), for the same reason as
 1.

 I'd love such useful feature like pg_rewarm to be included in core.  I hope
 such nice features won't be rejected just because there are already similar
 external tools.

For the record, the name of the tool is pg_PREwarm, not pg_rewarm.
The subject line of this thread is a typo.

Sounds like it might be worth dusting the patch off again...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] SSL: better default ciphersuite

2013-12-17 Thread Marko Kreen
On Tue, Dec 17, 2013 at 11:26:13AM -0500, Bruce Momjian wrote:
 On Tue, Dec 17, 2013 at 09:51:30AM -0500, Robert Haas wrote:
  I'm starting to think we should just leave this well enough alone.  We
  can't seem to find two people with the same idea of what would be
  better than what we have now.  And of course the point of making it a
  setting in the first place is that each person can set it to whatever
  they deem best.
 
 Yes, I am seeing that too.  Can we agree on one that is _better_ than
 what we have now, even if we can't agree on a _best_ one?

To recap - old settings are:

  DEFAULT:!LOW:!EXP:!MD5:@STRENGTH
  prefer-client-order

new proposal is:

  HIGH:MEDIUM:+3DES:!aNULL
  prefer-server-order

This is better than old state in following aspects:

- First, it does not remove any ciphers compared to current
  list.  So anything that could connect previously can connect
  still.

- Clearer to people not intimately familiar with OpenSSL and TLS.
  In particular, the 'MEDIUM' communicates that some less secure
  ciphers are enabled (RC4).

- Fixes the 3DES ordering.  OpenSSL default list is ordered roughly
  by (key-bits, ECDHE, DHE, plain RSA).  3DES has 168-bit key so
  it appears before 128-bit ciphers, although it offers around 112-bit
  actual security.  This problem exists already with existing Postgres
  versions: if you set suite to AES128:3DES, then libpq-based clients
  will use 3DES.

When combined with prefer-server-order, it has following benefits:

- Clarity: admin can look at configured cipher order and make reasonable
  guesses what will be used.

- Actually activates the 3DES fix.  Although non-libpq/OpenSSL based
  clients did used their own order, OpenSSL-based client did have
  same order problem in client-side.

- Old clients that did prefer RC4 will use it as last resort only,
  when only alternative is 3DES.

- Old clients that did prefer non-DHE ciphers will use DHE ciphers
  if available.


One goal the new settings *do not* try to achieve is to pick the absolutely
fastest cipher from the secure ones.  Old settings did not it either,
when speaking of libpq clients.  Java did try from client-side, but
as a result old deployed versions use now insecure settings.  I think
it's best when the default settings prefer security over speed,
everyone who is has special requirements speed-wise - AES is slow -
can tune list themselves.


So, does anyone have reasons not to use proposed new default?

-- 
marko



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


Re: [HACKERS] SSL: better default ciphersuite

2013-12-17 Thread Gavin Flower

On 18/12/13 05:26, Bruce Momjian wrote:

On Tue, Dec 17, 2013 at 09:51:30AM -0500, Robert Haas wrote:

On Sun, Dec 15, 2013 at 5:10 PM, James Cloos cl...@jhcloos.com wrote:

For reference, see:

   https://wiki.mozilla.org/Security/Server_Side_TLS

for the currently suggested suite for TLS servers.

...

But for pgsql, I'd leave off the !PSK; pre-shared keys may prove useful
for some.  And RC4, perhaps, also should be !ed.

And if anyone wants Kerberos tls-authentication, one could add
KRB5-DES-CBC3-SHA, but that is ssl3-only.

Once salsa20-poly1305 lands in openssl, that should be added to the
start of the list.

I'm starting to think we should just leave this well enough alone.  We
can't seem to find two people with the same idea of what would be
better than what we have now.  And of course the point of making it a
setting in the first place is that each person can set it to whatever
they deem best.

Yes, I am seeing that too.  Can we agree on one that is _better_ than
what we have now, even if we can't agree on a _best_ one?

Because various security agencies probably have people trying to confuse 
the issue, and acting to discourage strong encryption...


Possibly choose the one computationally most difficult to crack - but 
even then, we don't know what algorithms they are using, which are bound 
to be very sophisticated.


I've a horrible feeling, that I'm not paranoid enough!


Cheers,
Gavin


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


Re: [HACKERS] pg_rewarm status

2013-12-17 Thread MauMau

From: Robert Haas robertmh...@gmail.com
On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila amit.kapil...@gmail.com 
wrote:
I have used pg_prewarm during some of work related to Buffer Management 
and

other performance related work. It is quite useful utility.
+1 for reviving this patch for 9.4


Any other votes?


+1
Some customers requested:

1. fill the database cache with frequently accessed data before starting or 
resuming service for their users (for the first time or after maintenance 
work), so that they can provide steady and predictable performance.


2. pin some (reference or master) data in the database cache not to be 
evicted from the cache (like Oracle's KEEP buffer?), for the same reason as 
1.


I'd love such useful feature like pg_rewarm to be included in core.  I hope 
such nice features won't be rejected just because there are already similar 
external tools.



Regards
MauMau



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


Re: [HACKERS] pg_rewarm status

2013-12-17 Thread Josh Berkus
On 12/17/2013 06:34 AM, Robert Haas wrote:
 On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila amit.kapil...@gmail.com wrote:
 I have used pg_prewarm during some of work related to Buffer Management and
 other performance related work. It is quite useful utility.
 +1 for reviving this patch for 9.4
 
 Any other votes?

I still support this patch (as I did originally), and don't think that
the overlap with pgFincore is of any consequence.  pgFincore does more
than pgrewarm ever will, but it's also platform-specific, so it still
makes sense for both to exist.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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


[HACKERS] GiST support for inet datatypes

2013-12-17 Thread Emre Hasegeli
Hi,

Attached patch adds GiST support to the inet datatypes with two new
operators. Overlaps operator can be used with exclusion constraints.
Is adjacent to operator is just the negator of it. Index uses only
the network bits of the addresses. Except for the new operators and
is contained within, contains; basic comparison operators are also
supported.

Query planner never chooses to use the index for the operators which
the index is particularly useful because selectivity estimation functions
are missing. I am planning to work on them.

I also wanted to add strictly left of and strictly right of operators
but I did not want to introduce new symbols. I think we need style
guidelines for them. Range types use @ and @ for is contained within
and contains operators;  and  for strictly left of and strictly right of
operators. It would be nice if we could change the symbols for contains
and is contained within operators of the inet datatypes. Then we could
use the old ones for strictly left of and strictly right of operators.

I did not touch opr_sanity regression tests as I did not decide
how to solve these problems. I did not add documentation except
the new operators. It would be nice to mention the index and exclusion
constraints for inet datatypes somewhere. I did not know which page
would be more suitable.


inet-gist-v1.patch
Description: Binary data

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


Re: [HACKERS] SSL: better default ciphersuite

2013-12-17 Thread Marko Kreen
On Sun, Dec 15, 2013 at 05:10:38PM -0500, James Cloos wrote:
  MK == Marko Kreen mark...@gmail.com writes:
  PE == Peter Eisentraut pete...@gmx.net writes:

 PE Any other opinions on this out there?
 
 For reference, see:
 
   https://wiki.mozilla.org/Security/Server_Side_TLS
 
 for the currently suggested suite for TLS servers.
 
 That is:
 
 ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:
 ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:
 DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:kEDH+AESGCM:
 ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA:
 ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384:
 ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-RSA-AES128-SHA256:
 DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256:
 DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:AES128-GCM-SHA256:
 AES256-GCM-SHA384:ECDHE-RSA-RC4-SHA:ECDHE-ECDSA-RC4-SHA:
 AES128:AES256:RC4-SHA:HIGH:
 !aNULL:!eNULL:!EXPORT:!DES:!3DES:!MD5:!PSK

This is example of ciphersuite list for people who have special
requirements and care about tracking yearly changes in SSL landscape.
And can deploy config changes relatively fast.

This discussion is about Postgres default suite which cannot and should
not be periodically changed, for people who leave Postgres settings
to defaults and expect setup work well.

We would like to leave as much as possible to OpenSSL, but not more.

Looking at the history of OpenSSL, their default order has been
good, except the 3DES vs. AES128 priority.

Looking into future, I guess following events are likely:

- RC4 gets practially broken and/or removed from TLS
  (draft-popov-tls-prohibiting-rc4-01).

- New ciphersuites: Salsa/Chacha (256-bit key).

- New modes: CCM (RFC6655, draft-mcgrew-tls-aes-ccm-ecc-07),
  other ciphers with GCM, new AEAD constructs.

- CBC mode fixes: pad-mac-encrypt, pad-encrypt-mac.  Those may
  be implemented with TLS extensions, so no new ciphersuites.

RC4 situation - the 'MEDIUM' in my proposal communicates
that not all ciphers are best, and prefer-server-order
makes sure it is selected as last resort.  So that is solved.

New ciphersuites - if we want to select fastest from secure
suites we need to change configuration periodically
(RC4-AES128-CBC-AES128-GCM-SALSA) and I don't think Postgres
should bother we that.  So I think it's better to leave ordering
new ciphers to OpenSSL, and people who have special requirements
can worry about best configuration for specific stack they are running.

-- 
marko



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


Re: [HACKERS] planner missing a trick for foreign tables w/OR conditions

2013-12-17 Thread Simon Riggs
On 17 December 2013 17:28, Tom Lane t...@sss.pgh.pa.us wrote:

 So at this point I'm pretty much talked into it.  We could eliminate the
 dependence on indexes entirely, and replace this code with a step that
 simply tries to pull single-base-relation quals out of ORs wherever it can
 find one.  You could argue that the produced quals would sometimes not be
 worth testing for, but we could apply a heuristic that says to forget it
 unless the estimated selectivity of the extracted qual is less than,
 I dunno, 0.5 maybe.  (I wonder if it'd be worth inserting a check that
 there's not already a manually-generated equivalent clause, too ...)

Sounds sensible.

What surprises me is we don't have an API that allows an FDW to decide
what it can accept or not. It seems strange to have a unilateral
decision by our planner about what another planner is capable of.
Should we extend the API to allow the question to be asked?

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


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


Re: [HACKERS] planner missing a trick for foreign tables w/OR conditions

2013-12-17 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Tue, Dec 17, 2013 at 12:28 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 (I wonder if it'd be worth inserting a check that
 there's not already a manually-generated equivalent clause, too ...)

 Sounds a little too clever IMHO.

The argument for doing it is that we might otherwise find ourselves
degrading the plans for previously-manually-optimized queries.  On the
other hand, the existing index-driven code has probably forestalled the
need for many people to do that; at least, I don't recall seeing much
discussion of doing that sort of thing by hand.

I'm happy to leave the issue out of the first version of the patch,
anyway.

regards, tom lane


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


Re: [HACKERS] planner missing a trick for foreign tables w/OR conditions

2013-12-17 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes:
 What surprises me is we don't have an API that allows an FDW to decide
 what it can accept or not. It seems strange to have a unilateral
 decision by our planner about what another planner is capable of.

Uh, what?

There's certainly missing features in our FDW APIs --- no ability to push
over joins or aggregates for instance --- but none of that has anything to
do with assumptions about what the other end is capable of.  We're just
not done inventing those APIs.

regards, tom lane


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


Re: [HACKERS] [PATCH] SQL assertions prototype

2013-12-17 Thread Josh Berkus
On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:
 A fundamental problem with this is that it needs to handle isolation
 reliable, so that the assertion cannot be violated when two concurrent
 backends do things. Consider the example from the manual, which checks
 that a table has at least one row. Now, if the table has two rows to
 begin with, and in one backend you delete one row, and concurrently in
 another backend you delete the other row, and then commit both
 transactions, the assertion is violated.
 
 In other words, the assertions need to be checked in serializable mode.
 Now that we have a real serializable mode, I think that's actually
 feasible.

Going back over this patch, I haven't seen any further discussion of the
point Heikki raises above, which seems like a bit of a showstopper.

Heikki, did you have specific ideas on how to solve this?  Right now my
mind boggles.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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


Re: [HACKERS] 9.3 reference constraint regression

2013-12-17 Thread Alvaro Herrera
Alvaro Herrera wrote:

 Well, it would help if those cases weren't dead code.  Neither
 heap_update nor heap_delete are ever called in the no wait case at
 all.  Only heap_lock_tuple is, and I can't see any misbehavior there
 either, even with HeapTupleBeingUpdated returned when there's a
 non-local locker, or when there's a MultiXact as xmax, regardless of its
 status.

I spent some more time trying to generate a test case that would show a
problem with the changed return values here, and was unable to.

I intend to apply this patch soon.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training  Services
*** a/src/backend/utils/time/tqual.c
--- b/src/backend/utils/time/tqual.c
***
*** 686,693  HeapTupleSatisfiesUpdate(HeapTupleHeader tuple, CommandId curcid,
  			if (tuple-t_infomask  HEAP_XMAX_INVALID)	/* xid invalid */
  return HeapTupleMayBeUpdated;
  
! 			if (HEAP_XMAX_IS_LOCKED_ONLY(tuple-t_infomask))	/* not deleter */
  return HeapTupleMayBeUpdated;
  
  			if (tuple-t_infomask  HEAP_XMAX_IS_MULTI)
  			{
--- 686,713 
  			if (tuple-t_infomask  HEAP_XMAX_INVALID)	/* xid invalid */
  return HeapTupleMayBeUpdated;
  
! 			if (HEAP_XMAX_IS_LOCKED_ONLY(tuple-t_infomask))
! 			{
! /*
!  * Careful here: even if this tuple was created by our
!  * transaction, it might be locked by other transactions, in
!  * case the original version was key-share locked when we
!  * updated it.	We cannot simply return MayBeUpdated, because
!  * that would lead to those locks being ignored in the future.
!  * Therefore we return HeapTupleBeingUpdated here, which
!  * causes the caller to recheck those locks.
!  *
!  * Note we might return BeingUpdated spuriously in some cases,
!  * particularly when there's a multixact which has no members
!  * outside of this transaction.  This doesn't cause any issues
!  * currently, but might need tweaking.
!  */
! if (tuple-t_infomask  HEAP_XMAX_IS_MULTI)
! 	return HeapTupleBeingUpdated;
! else if (!TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetRawXmax(tuple)))
! 	return HeapTupleBeingUpdated;
  return HeapTupleMayBeUpdated;
+ 			}
  
  			if (tuple-t_infomask  HEAP_XMAX_IS_MULTI)
  			{
***
*** 700,706  HeapTupleSatisfiesUpdate(HeapTupleHeader tuple, CommandId curcid,
  
  /* updating subtransaction must have aborted */
  if (!TransactionIdIsCurrentTransactionId(xmax))
! 	return HeapTupleMayBeUpdated;
  else
  {
  	if (HeapTupleHeaderGetCmax(tuple) = curcid)
--- 720,733 
  
  /* updating subtransaction must have aborted */
  if (!TransactionIdIsCurrentTransactionId(xmax))
! {
! 	/*
! 	 * This would normally be HeapTupleMayBeUpdated, but
! 	 * we do this instead to cause caller to recheck
! 	 * other lockers; see note above in the LOCKED_ONLY case.
! 	 */
! 	return HeapTupleBeingUpdated;
! }
  else
  {
  	if (HeapTupleHeaderGetCmax(tuple) = curcid)

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


Re: [HACKERS] [bug fix] multibyte messages are displayed incorrectly on the client

2013-12-17 Thread Bruce Momjian
On Fri, Dec 13, 2013 at 10:41:17PM +0900, MauMau wrote:
 [Cause]
 While the session is being established, the server cannot use the
 client encoding for message conversion yet, because it cannot access
 system catalogs to retrieve conversion functions.  So, the server
 sends messages to the client without conversion.  In the above
 example, the server sends Japanese UTF-8 messages to psql, which
 expects those messages in SJIS.
 
 
 [Fix]
 Disable message localization during session startup.  In other
 words, messages are output in English until the database session is
 established.

I think the question is whether the server encoding or English are
likely to be better for the average client.  My bet is that the server
encoding is more likely correct.

However, you are right that English/ASCII at least will always be
viewable, while there are many server/client combinations that will
produce unreadable characters.

I would be interested to hear other people's experience with this.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +


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


Re: [HACKERS] [PATCH] SQL assertions prototype

2013-12-17 Thread Kevin Grittner
Josh Berkus j...@agliodbs.com wrote:
 On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:
 A fundamental problem with this is that it needs to handle isolation
 reliable, so that the assertion cannot be violated when two concurrent
 backends do things. Consider the example from the manual, which checks
 that a table has at least one row. Now, if the table has two rows to
 begin with, and in one backend you delete one row, and concurrently in
 another backend you delete the other row, and then commit both
 transactions, the assertion is violated.

 In other words, the assertions need to be checked in serializable mode.
 Now that we have a real serializable mode, I think that's actually
 feasible.

 Going back over this patch, I haven't seen any further discussion of the
 point Heikki raises above, which seems like a bit of a showstopper.

 Heikki, did you have specific ideas on how to solve this?  Right now my
 mind boggles.

It works fine as long as you set default_transaction_isolation =
'serializable' and never override that.  :-)  Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.

Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.

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


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


Re: [HACKERS] [PATCH] SQL assertions prototype

2013-12-17 Thread Andrew Dunstan


On 12/17/2013 04:42 PM, Kevin Grittner wrote:

Josh Berkus j...@agliodbs.com wrote:

On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:

A fundamental problem with this is that it needs to handle isolation
reliable, so that the assertion cannot be violated when two concurrent
backends do things. Consider the example from the manual, which checks
that a table has at least one row. Now, if the table has two rows to
begin with, and in one backend you delete one row, and concurrently in
another backend you delete the other row, and then commit both
transactions, the assertion is violated.

In other words, the assertions need to be checked in serializable mode.
Now that we have a real serializable mode, I think that's actually
feasible.

Going back over this patch, I haven't seen any further discussion of the
point Heikki raises above, which seems like a bit of a showstopper.

Heikki, did you have specific ideas on how to solve this?  Right now my
mind boggles.

It works fine as long as you set default_transaction_isolation =
'serializable' and never override that.  :-)  Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.

Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.





Maybe the presence of an assertion should be enough to force 
serializable, i.e. turn it on and not allow it to be turned off.


cheers

andrew









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


Re: [HACKERS] [PATCH] SQL assertions prototype

2013-12-17 Thread Gavin Flower

On 18/12/13 10:48, Andrew Dunstan wrote:


On 12/17/2013 04:42 PM, Kevin Grittner wrote:

Josh Berkus j...@agliodbs.com wrote:

On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:

A fundamental problem with this is that it needs to handle isolation
reliable, so that the assertion cannot be violated when two concurrent
backends do things. Consider the example from the manual, which checks
that a table has at least one row. Now, if the table has two rows to
begin with, and in one backend you delete one row, and concurrently in
another backend you delete the other row, and then commit both
transactions, the assertion is violated.

In other words, the assertions need to be checked in serializable 
mode.

Now that we have a real serializable mode, I think that's actually
feasible.
Going back over this patch, I haven't seen any further discussion of 
the

point Heikki raises above, which seems like a bit of a showstopper.

Heikki, did you have specific ideas on how to solve this? Right now my
mind boggles.

It works fine as long as you set default_transaction_isolation =
'serializable' and never override that.  :-)  Of course, it sure
would be nice to have a way to prohibit overrides, but that's
another issue.

Otherwise it is hard to see how to make it work in a general way
without a mutually exclusive lock mode on the table for the
duration of any transaction which modifies the table.





Maybe the presence of an assertion should be enough to force 
serializable, i.e. turn it on and not allow it to be turned off.


cheers

andrew



Perhaps then it should be called an 'assurance', rather than an 'assertion?'

(Not being entirely facetious!)


Cheers,
Gavin


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


Re: [HACKERS] Extension Templates S03E11

2013-12-17 Thread Simon Riggs
On 17 December 2013 17:54, Tom Lane t...@sss.pgh.pa.us wrote:

 So now, you don't need anymore to have file system write privileges into
 a central place owned by root, it can be anywhere else,

 Modern OSes have security checks that can prevent loading libraries from
 random places.  This is widely seen as not merely a good thing, but
 security-critical for network-exposed daemons.  Of which we are one.

 I keep telling you this, and it keeps not sinking in.  One more time: any
 feature that does what you want will be dead on arrival so far as vendors
 like Red Hat are concerned.  I don't care how creatively you argue for it,
 they will refuse to ship it (or at least refuse to disable the SELinux
 policy that prevents it).  Period.  Please stop wasting my time with
 suggestions otherwise, because it won't happen.

 So what we have left to discuss is whether we want to develop, and base a
 community extension-distribution infrastructure on, a mechanism that some
 popular vendors will actively block.  I'm inclined to think it's a bad
 idea, but I just work here.

Yes, there is a strong argument against enabling Postgres
out-of-the-box to allow loading of .so files from random places and
bypassing distro security procedures.

But that argument doesn't apply to all types of extension. For example, data.

In any case, right now, its easy to load an FDW and then do an INSERT
SELECT from a foreign server into a text column. There are no
restrictions on URLs to access foreign servers. Then write a *trusted*
PL/pgSQL procedure to execute the contents of the text column to do
whatever. All you need is the Postgres foreign data wrapper loaded, an
insecure URL and a route to it.

I don't see a material difference between that route and the new one
proposed. The only difference is really that the new route would be
blessed as being the officially recommended way to import objects
without needing a file, and to allow them to be backed up and
restored.

So perhaps all we need is a module that once loaded allows other
things to be loaded. (Red Hat etc can then ban that as they see fit.)

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


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


Re: [HACKERS] Extension Templates S03E11

2013-12-17 Thread Simon Riggs
On 17 December 2013 18:32, Stephen Frost sfr...@snowman.net wrote:
 * Simon Riggs (si...@2ndquadrant.com) wrote:
 I keep seeing people repeat I don't like blobs as if that were an
 objection. There is no danger or damage from doing this. I can't see
 any higher beauty that we're striving for by holding out. Why not
 allow the user to choose XML, JSON, YAML, or whatever they choose.

 I have no idea where you're going with this, but I *do* object to
 sticking an SQL script which defines a bunch of objects into a catalog
 table *right next to where they are properly defined*.  There's just no
 sense in it that I can see, except that it happens to mimic what we do
 today- to no particular purpose.

The purpose is clear: so it is part of the database backup. It's a
fairly boring purpose, not fancy at all. But it is a purpose, indeed
*the* purpose.

I don't see any technical objection here.

We aim to have the simplest implementation that meets the stated need
and reasonable extrapolations of that. Text in a catalog table is the
simplest implementation. That is not a reason to reject it, especially
when we aren't suggesting a viable alternative.

I have zero attachment to this design, my interest is in the feature.
How do we achieve the feature if not this way?

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


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


Re: [HACKERS] Extension Templates S03E11

2013-12-17 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes:
 On 17 December 2013 18:32, Stephen Frost sfr...@snowman.net wrote:
 I have no idea where you're going with this, but I *do* object to
 sticking an SQL script which defines a bunch of objects into a catalog
 table *right next to where they are properly defined*.  There's just no
 sense in it that I can see, except that it happens to mimic what we do
 today- to no particular purpose.

 The purpose is clear: so it is part of the database backup. It's a
 fairly boring purpose, not fancy at all. But it is a purpose, indeed
 *the* purpose.

The point Stephen is making is that it's just as easy, and far more
reliable, to dump the package-or-whatever-you-call-it by dumping the
definitions of the contained objects, as to dump it by dumping the text
blob it was originally created from.  So I don't see a lot of merit
to claiming that we need to keep the text blob for this purpose.

We did it differently for extensions in part because you can't dump a .so
as a SQL command, so dump-the-contained-objects wasn't going to be a
complete backup strategy in any case.  But for a package containing only
SQL objects, that's not a problem.

 We aim to have the simplest implementation that meets the stated need
 and reasonable extrapolations of that. Text in a catalog table is the
 simplest implementation. That is not a reason to reject it, especially
 when we aren't suggesting a viable alternative.

The first part of this assertion is debatable, and the claim that no
viable alternative has been suggested is outright wrong.

regards, tom lane


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


Re: [HACKERS] commit fest 2013-11 final report

2013-12-17 Thread Peter Eisentraut
On 12/17/13, 10:19 AM, Tom Lane wrote:
 Perhaps we should just move all the Needs Review and RFC patches forward
 to the next fest, so we don't forget about them?

This was done the last few times, but it has caused some controversy.
One problem was that a number of patches arrived in this commit fest
without either the author or the reviewers knowing about it, which
caused the already somewhat stale patch to become completely abandoned.

I think what I'll do is send an email to each of the affected patch
threads describing the situation.  But I'd like someone involved in the
patch, either author or reviewer, to make the final call about moving
the patch forward.


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


Re: [HACKERS] Extension Templates S03E11

2013-12-17 Thread Simon Riggs
On 17 December 2013 23:42, Tom Lane t...@sss.pgh.pa.us wrote:

 We aim to have the simplest implementation that meets the stated need
 and reasonable extrapolations of that. Text in a catalog table is the
 simplest implementation. That is not a reason to reject it, especially
 when we aren't suggesting a viable alternative.

 The first part of this assertion is debatable, and the claim that no
 viable alternative has been suggested is outright wrong.

I just hadn't read about that myself. All I'd read was why this
feature should be blocked.

Sounds like we have a way forward for this feature then, just not with
the current patch.

Can someone attempt to summarise the way forward, with any caveats and
necessary restrictions? It would save further column inches of debate.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


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


Re: [HACKERS] [PATCH] SQL assertions prototype

2013-12-17 Thread Josh Berkus
On 12/17/2013 01:42 PM, Kevin Grittner wrote:
 Josh Berkus j...@agliodbs.com wrote:
 Going back over this patch, I haven't seen any further discussion of the
 point Heikki raises above, which seems like a bit of a showstopper.

 Heikki, did you have specific ideas on how to solve this?  Right now my
 mind boggles.
 
 It works fine as long as you set default_transaction_isolation =
 'serializable' and never override that.  :-)  Of course, it sure
 would be nice to have a way to prohibit overrides, but that's
 another issue.
 
 Otherwise it is hard to see how to make it work in a general way
 without a mutually exclusive lock mode on the table for the
 duration of any transaction which modifies the table.

Serializable or not, *what* do we lock for assertions?  It's not rows.
Tables?  Which tables?  What if the assertion is an interpreted language
function?  Does the SSI reference counter really take care of all of this?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


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


[HACKERS] [PATCH] Doc fix for VACUUM FREEZE

2013-12-17 Thread Maciek Sakrejda
VACUUM FREEZE sets both vacuum_freeze_min_age and vacuum_freeze_table_age
to 0, but only the former is documented. This patch notes that the other
setting is also affected.


Re: [HACKERS] [PATCH] Doc fix for VACUUM FREEZE

2013-12-17 Thread Maciek Sakrejda
(now with patch--sorry about that)

diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index eb71581..5f03343 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -100,8 +100,9 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ replaceable class=PARAMETER
  para
   Selects aggressive quotefreezing/quote of tuples.
   Specifying literalFREEZE/literal is equivalent to performing
-  commandVACUUM/command with the
-  xref linkend=guc-vacuum-freeze-min-age parameter
+  commandVACUUM/command with both
+  xref linkend=guc-vacuum-freeze-table-age and
+  xref linkend=guc-vacuum-freeze-min-age
   set to zero.
  /para
 /listitem

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


Re: [HACKERS] pg_rewarm status

2013-12-17 Thread KONDO Mitsumasa

(2013/12/18 5:33), Robert Haas wrote:

Sounds like it might be worth dusting the patch off again...

I'd like to request you to add all_index option and usage_count option.
When all_index option is selected, all index become rewarm nevertheless user 
doesn't input relation name. And usage_count option adds usage_copunt in 
shared_buffers. Useful buffers will remain long and not to be thrown easly.

I think these are easy to implements and useful. So please if you like.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center


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


Re: [HACKERS] Optimize kernel readahead using buffer access strategy

2013-12-17 Thread KONDO Mitsumasa

(2013/12/17 21:29), Simon Riggs wrote:

These are interesting results. Good research.

Thanks!


They also show that the benefit of this is very specific to the exact
task being performed. I can't see any future for a setting that
applies to everything or nothing. We must be more selective.

This patch is still needed some human judgement whether readahead is on or off.
But it might have been already useful for clever users. However, I'd like to 
implement adding more the minimum optimization.



We also need much better benchmark results, clearly laid out, so they
can be reproduced and discussed.

I think this feature is big benefit for OLTP, and it might useful for BI now.
BI queries are mostly compicated, so we will need to test more in some
situations. Printf debug is very useful for debugging my patch, and it will 
accelerate the optimization.



Please keep working on this.

OK. I do it patiently.

Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center


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


Re: [HACKERS] Logging WAL when updating hintbit

2013-12-17 Thread Amit Kapila
On Fri, Dec 13, 2013 at 7:57 PM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
 On 12/13/2013 07:55 AM, Sawada Masahiko wrote:

 On Fri, Dec 13, 2013 at 1:51 PM, Dilip kumar dilip.ku...@huawei.com
 wrote:

 On 04 December 2013, Sawada Masahiko Wrote

 I have modified the patch base on your comment, and I attached the v7
 patch.


 Thanks, committed with some minor changes:

Should this patch in CF app be moved to Committed Patches or is there
something left for this patch?

 I'm not totally satisfied with the name of the GUC, wal_log_hintbits.

 Me either; at the very least, it's short an underscore: wal_log_hint_bits
 would be more readable.  But how about just wal_log_hints?

+1 for wal_log_hints, it sounds better.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


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


Re: [HACKERS] [bug fix] pg_ctl always uses the same event source

2013-12-17 Thread Amit Kapila
On Tue, Dec 17, 2013 at 5:33 PM, MauMau maumau...@gmail.com wrote:
 From: Amit Kapila amit.kapil...@gmail.com

 Few minor things:

 event_source here is a global static char array, so it's automatically
 initialized with zeros and safe to access.

   Right, I had missed that point.


 2. minor coding style issue


 Thanks.  I passed the source files through pgindent and attached the revised
 patch.  Although the arguments in the second line are not in line with the
 first line's arguments, that's what pgindent found good.

   Okay, no problem.

Few other points:
-
1.
#ifdef WIN32
/* Get event source from postgresql.conf for eventlog output */
get_config_value(event_source, event_source, sizeof(event_source));
#endif

event logging is done for both win32 and cygwin env.
under hash define (Win32 || cygwin),
so event source name should also be retrieved for both
environments. Refer below in code:

#if defined(WIN32) || defined(__CYGWIN__)
static void
write_eventlog(int level, const char *line)

2.
Docs needs to be updated for default value:
http://www.postgresql.org/docs/devel/static/event-log-registration.html
http://www.postgresql.org/docs/devel/static/runtime-config-logging.html#GUC-EVENT-SOURCE

In this patch, we are planing to change default value of event_source
from PostgreSQL to PostgreSQL 9.4 (PostgreSQL PG_MAJORVERSION)
as part of fixing the issue reported in this thread.

If anyone has objection to that, please let us know now to avoid re-work
at later stage.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


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


Re: [HACKERS] Logging WAL when updating hintbit

2013-12-17 Thread Michael Paquier
On Wed, Dec 18, 2013 at 11:22 AM, Amit Kapila amit.kapil...@gmail.com wrote:
 On Fri, Dec 13, 2013 at 7:57 PM, Heikki Linnakangas
 hlinnakan...@vmware.com wrote:
 Thanks, committed with some minor changes:

 Should this patch in CF app be moved to Committed Patches or is there
 something left for this patch?
Nothing has been forgotten for this patch. It can be marked as committed.
-- 
Michael


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


Re: [HACKERS] Problem with displaying wide tables in psql

2013-12-17 Thread Sameer Thakur
On Wed, Dec 11, 2013 at 11:13 PM, Sergey Muraviov
sergey.k.murav...@gmail.com wrote:
 Hi.

 I've improved the patch.
 It works in expanded mode when either format option is set to wrapped (\pset
 format wrapped), or we have no pager, or pager doesn't chop long lines (so
 you can still use the trick).
 Target output width is taken from either columns option (\pset columns 70),
 or environment variable $COLUMNS, or terminal size.
 And it's also compatible with any border style (\pset border 0|1|2).

 Here are some examples:

 postgres=# \x 1
 postgres=# \pset format wrapped
 postgres=# \pset border 0
 postgres=# select * from wide_table;
 * Record 1
 value afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf  sadf sa df
 sadfsadfa
   sd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f sadf sad fadsf
 * Record 2
 value afadsafasd fasdf asdfasd

 postgres=# \pset border 1
 postgres=# \pset columns 70
 postgres=# select * from wide_table;
 -[ RECORD 1 ]-
 value | afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf  sadf sa
   | df sadfsadfasd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f
   |  sadf sad fadsf
 -[ RECORD 2 ]-
 value | afadsafasd fasdf asdfasd

 postgres=# \pset border 2
 postgres=# \pset columns 60
 postgres=# select * from wide_table;
 +-[ RECORD 1 ]-+
 | value | afadsafasd fasdf asdfasd fsad fas df sadf sad f  |
 |   | sadf  sadf sa df sadfsadfasd fsad fsa df sadf as |
 |   | d fa sfd sadfsadf asdf sad f sadf sad fadsf  |
 +-[ RECORD 2 ]-+
 | value | afadsafasd fasdf asdfasd |
 +---+--+

 Regards,
 Sergey


The patch  applies and compile cleanly. I tried the following
\pset format wrapped
\pset columns 70.
Not in expanded mode
select * from wide_table works fine.
select * from pg_stats has problems in viewing. Is it that pg_stats
can be viewed easily only in expanded mode i.e. if columns displayed
are wrapped then there is no way to view results in non expanded mode?
regards
Sameer


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


Re: [HACKERS] row security roadmap proposal

2013-12-17 Thread Craig Ringer
On 12/18/2013 01:03 AM, Robert Haas wrote:
 On Mon, Dec 16, 2013 at 3:12 PM, Gregory Smith gregsmithpg...@gmail.com 
 wrote:
  On 12/16/13 9:36 AM, Craig Ringer wrote:
 
  - Finish and commit updatable security barrier views. I've still got a
  lot of straightening out to do there.
 
  I don't follow why you've put this part first.  It has a lot of new
  development and the risks that go along with that, but the POC projects 
  I've
  been testing are more interested in the view side issues.
 I don't really see a way that any of this can work without that.  To
 be clear, that work is required even just for read-side security.

It's possible to build limited read-side-only security on top of the
existing s.b. views as they stand, with no update support.

You can grant write-only access to the base relations, and require
people to use a different relation name / schema when they want to
access a relation for write vs for read. You can't use RETURNING, and
you can still learn from result rowcounts etc. It's clumsy but usable-ish.

So it works - as long as you're using absolutely 100% read-only access
for users you need to constrain, or you don't mind explicitly referring
to the base table for write operations and not being able to use RETURNING.

I've been looking at write support primarily because I was under the
impression from prior discussion I read that the feature wasn't
considered committable as a read-only feature. If a consensus can be
built that read-only RLS would be acceptable after all, then I'll
happily defer that in favour of the other work items.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services


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