Re: [HACKERS] [COMMITTERS] pgsql: Allow external recovery_config_directory

2013-03-28 Thread Heikki Linnakangas

On 27.03.2013 23:36, Simon Riggs wrote:

Why do you think recovery_config_directory are different to
config_file with respect to pg_basebackup?


pg_basebackup doesn't try to *write* anything to config_file. It does 
write to $PGDATA/recovery.conf, with the expectation that it takes 
effect when you start the server.


When you take a backup, I think it's quite reasonable that if you have 
set config_file to a different location, that's not backed up. Same with 
recovery.conf. But when pg_basebackup *creates* recovery.conf, it's at 
least surprising if it doesn't take effect.


- 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: [PATCH] Exorcise zero-dimensional arrays (Was: Re: [HACKERS] Should array_length() Return NULL)

2013-03-28 Thread Dean Rasheed
On 28 March 2013 03:01, Tom Lane t...@sss.pgh.pa.us wrote:
 [snip]
 ranges *are not arrays*.

OK, fair enough. I guess it's the mathematician in me seeing patterns
in things that behave similarly, but which are admittedly different.

Is the patch also going to allow empty arrays in higher dimensions
where not just the last dimension is empty? It seems as though, if
it's allowing 1-by-0 arrays like '{{}}' and '[4:4][8:7]={{}}', it
should also allow 0-by-0 arrays like '[4:3][8:7]={}', and 0-by-3
arrays like '[4:3][11:13]={}'.

That last example seems like the more useful kind of thing to allow,
since you might one day be able to append a non-empty 1-D array onto
it. As it stands, the patch only allows empty 2-D arrays that are
empty in the final dimension, to which the only thing you could append
would be more empty 1-D arrays.

Regards,
Dean


-- 
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] [COMMITTERS] pgsql: Allow external recovery_config_directory

2013-03-28 Thread Simon Riggs
On 28 March 2013 08:31, Heikki Linnakangas hlinnakan...@vmware.com wrote:
 On 27.03.2013 23:36, Simon Riggs wrote:

 Why do you think recovery_config_directory are different to
 config_file with respect to pg_basebackup?


 pg_basebackup doesn't try to *write* anything to config_file. It does write
 to $PGDATA/recovery.conf, with the expectation that it takes effect when you
 start the server.

 When you take a backup, I think it's quite reasonable that if you have set
 config_file to a different location, that's not backed up. Same with
 recovery.conf. But when pg_basebackup *creates* recovery.conf, it's at least
 surprising if it doesn't take effect.

No, it *would* take effect. The parameter is set in a config file that
is not part of the backup, so if you start the server from the backup
then it doesn't know that the recovery_config_directory had been set
and so it would read the recovery.conf written by pg_basebackup. If
you backup the parameter files as well, then it would fail, but that
is easily handled by saying the recovery.conf can exist either in
datadir or recovery_config_directory. I don't see that as a major
problem, or change. But for other reasons, I have revoked the patch.

You have highlighted that pg_basebackup does *not* take a fully
working backup in all cases, especially the -R case where it is
clearly broken. It is that pre-existing issue that leads to your
complaint, not the patch itself.

Please admit that by adding a line to the docs to explain the
non-working case of -R.

-- 
 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] [COMMITTERS] pgsql: Allow external recovery_config_directory

2013-03-28 Thread Robert Haas
On Thu, Mar 28, 2013 at 6:23 AM, Simon Riggs si...@2ndquadrant.com wrote:
 No, it *would* take effect. The parameter is set in a config file that
 is not part of the backup, so if you start the server from the backup
 then it doesn't know that the recovery_config_directory had been set
 and so it would read the recovery.conf written by pg_basebackup.

Are you saying pg_basebackup doesn't back up postgresql.conf?  I thought it did.

-- 
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] in-catalog Extension Scripts and Control parameters (templates?)

2013-03-28 Thread Robert Haas
On Wed, Mar 27, 2013 at 10:32 AM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
 Surely creating an extension template must be a superuser-only
 operation, in which case this is an issue because Mallory could also
 have just blown up the world directly if he's already a superuser
 anyway.

 Yeah .. (except this is NOT an issue)

 If the current patch isn't enforcing that, it's 100% broken.

 Even if it's not enforcing that, it's not 100% broken, it only contains
 one more bug we need to fix.

Sure.  I didn't mean that such a mistake would make the patch
unsalvageable, only that it would be disastrous from a security point
of view.  But as you say, pretty easy to fix.

-- 
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] Default connection parameters for postgres_fdw and dblink

2013-03-28 Thread Daniel Farina
On Wed, Mar 27, 2013 at 8:22 AM, Robert Haas robertmh...@gmail.com wrote:
 On Wed, Mar 27, 2013 at 10:47 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Use a service file maybe?  But you can't have it both ways: either we
 like the behavior of libpq absorbing defaults from the postmaster
 environment, or we don't.  You were just arguing this was a bug, and
 now you're calling it a feature.

 Maybe we could compromise and call it a beature.  Or a fug.  In all
 seriousness, it's in that grey area where most people would probably
 consider this a surprising and unwanted behavior, but there might be a
 few out there who had a problem and discovered that they could use
 this trick to solve it.

 In terms of a different solution, what about a GUC that can contain a
 connection string which is used to set connection defaults, but which
 can still be overriden?  So it would mimic the semantics of setting an
 environment variable, but it would be better, because you could do all
 of the usual GUC things with it instead of having to hack on the
 postmaster startup environment.

In my meta-experience, nobody really wants defaults in that
configuration anyway, and if they do, most of them would be
appreciative of being notified positively of such a problem of relying
on connection defaults (maybe with a warning at first at most?).   In
this case, such an abrupt change in the contrib is probably fine.

From the vantage point I have: full steam ahead, with the sans GUC
solution...it's one less detailed GUC in the world, and its desired
outcome can be achieved otherwise.  I'd personally rather open myself
up for dealing with the consequences of this narrow change in my own
corner of the world.

This anecdote carries a standard disclaimer: my meta-experience
doesn't include all use cases for everyone everywhere.

--
fdr


-- 
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] FDW for PostgreSQL

2013-03-28 Thread Stephen Frost
Tom, all,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
 Another thing I was wondering about, but did not change, is that if we're
 having the remote transaction inherit the local transaction's isolation
 level, shouldn't it inherit the READ ONLY property as well?

Apologies for bringing this up pretty late, but wrt writable FDW
transaction levels, I was *really* hoping that we'd be able to implement
autonomous transactions on top of writeable FDWs.  It looks like there's
no way to do this using the postgres_fdw due to it COMMIT'ing only when
the client transaction commits.  Would it be possible to have a simply
function which could be called to say commit the transaction on the
foreign side for this server/table/connection/whatever?  A nice
addition on top of that would be able to define 'auto-commit' for a
given table or server.

I'll try and find time to work on this, but I'd love feedback on if this
is possible and where the landmines are.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] [COMMITTERS] pgsql: Allow external recovery_config_directory

2013-03-28 Thread Simon Riggs
On 28 March 2013 11:36, Robert Haas robertmh...@gmail.com wrote:
 On Thu, Mar 28, 2013 at 6:23 AM, Simon Riggs si...@2ndquadrant.com wrote:
 No, it *would* take effect. The parameter is set in a config file that
 is not part of the backup, so if you start the server from the backup
 then it doesn't know that the recovery_config_directory had been set
 and so it would read the recovery.conf written by pg_basebackup.

 Are you saying pg_basebackup doesn't back up postgresql.conf?  I thought it 
 did.

postgresql.conf will be backed up if it is present in the data
directory. If it is not present, it is not backed up.

Therefore anybody using pg_basebackup and the config_file parameter
does *not* have an executable backup when used with the -R option, as
Heikki was suggesting was a requirement for this patch. So if we
regard that as a bug with the patch, then there is a bug with -R
with/without the patch.

pg_basebackup's behaviour with respect to .conf files is undocumented
so its a feature that it skips .conf files in the config_file case.

-- 
 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] Support for REINDEX CONCURRENTLY

2013-03-28 Thread Fujii Masao
On Thu, Mar 28, 2013 at 10:34 AM, Andres Freund and...@anarazel.de wrote:
 On 2013-03-28 10:18:45 +0900, Michael Paquier wrote:
 On Thu, Mar 28, 2013 at 3:12 AM, Fujii Masao masao.fu...@gmail.com wrote:
 Since we call relation_open() with lockmode, ISTM that we should also call
  relation_close() with the same lockmode instead of NoLock. No?
 
 Agreed on that.

 That doesn't really hold true generally, its often sensible to hold the
 lock till the end of the transaction, which is what not specifying a
 lock at close implies.

You're right. Even if we release the lock there, the lock is taken again soon
and hold till the end of the transaction. There is no need to release the lock
there.

Regards,

-- 
Fujii Masao


-- 
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] [COMMITTERS] pgsql: Allow external recovery_config_directory

2013-03-28 Thread Heikki Linnakangas

On 28.03.2013 14:45, Simon Riggs wrote:

On 28 March 2013 11:36, Robert Haasrobertmh...@gmail.com  wrote:

On Thu, Mar 28, 2013 at 6:23 AM, Simon Riggssi...@2ndquadrant.com  wrote:

No, it *would* take effect. The parameter is set in a config file that
is not part of the backup, so if you start the server from the backup
then it doesn't know that the recovery_config_directory had been set
and so it would read the recovery.conf written by pg_basebackup.


Are you saying pg_basebackup doesn't back up postgresql.conf?  I thought it did.


postgresql.conf will be backed up if it is present in the data
directory. If it is not present, it is not backed up.


Right.


Therefore anybody using pg_basebackup and the config_file parameter
does *not* have an executable backup when used with the -R option, as
Heikki was suggesting was a requirement for this patch.


That's not related to the -R option, the situation with config_file is 
the same with or without it. But yes, if you use config_file option to 
point outside the data directory, the config file won't be backed up. 
That feels intuitive to me, I wouldn't expect it to be. Same with 
include or include_dir directives in the config file, as well as 
hba_file and ident_file - I wouldn't expect any of the files that those 
point to to be included in the backup.


The filesystem-level backup procedure documented in the user manual, not 
using pg_basebackup, behaves the same.



pg_basebackup's behaviour with respect to .conf files is undocumented
so its a feature that it skips .conf files in the config_file case.


Ok. It's always beem self-evident to me, but I agree it should be 
documented. How about the attached?


- Heikki
diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index e444b1c..987802a 100644
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -920,7 +920,10 @@ SELECT pg_stop_backup();
 If you are using tablespaces that do not reside underneath this directory,
 be careful to include them as well (and be sure that your backup dump
 archives symbolic links as links, otherwise the restore will corrupt
-your tablespaces).
+your tablespaces). If you have relocated any configuration files
+outside the database cluster directory (see
+xref linkend=config-includes and
+xref linkend=runtime-config-file-locations), include them as well.
/para
 
para
diff --git a/doc/src/sgml/ref/pg_basebackup.sgml b/doc/src/sgml/ref/pg_basebackup.sgml
index 9fe440a..bdf74a0 100644
--- a/doc/src/sgml/ref/pg_basebackup.sgml
+++ b/doc/src/sgml/ref/pg_basebackup.sgml
@@ -512,7 +512,11 @@ PostgreSQL documentation
The backup will include all files in the data directory and tablespaces,
including the configuration files and any additional files placed in the
directory by third parties. Only regular files and directories are allowed
-   in the data directory, no symbolic links or special device files.
+   in the data directory, no symbolic links or special device files. If you
+   have relocated any configuration files outside the data directory (see
+   xref linkend=config-includes and
+   xref linkend=runtime-config-file-locations), they will not be included
+   in the backup.
   /para
 
   para

-- 
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] FDW for PostgreSQL

2013-03-28 Thread Tom Lane
Stephen Frost sfr...@snowman.net writes:
 Apologies for bringing this up pretty late, but wrt writable FDW
 transaction levels, I was *really* hoping that we'd be able to implement
 autonomous transactions on top of writeable FDWs.  It looks like there's
 no way to do this using the postgres_fdw due to it COMMIT'ing only when
 the client transaction commits.  Would it be possible to have a simply
 function which could be called to say commit the transaction on the
 foreign side for this server/table/connection/whatever?  A nice
 addition on top of that would be able to define 'auto-commit' for a
 given table or server.

TBH I think this is a fairly bad idea.  You can get that behavior via
dblink if you need it, but there's no way to do it in an FDW without
ending up with astonishing (and not in a good way) semantics.  A commit
would force committal of everything that'd been done through that
connection, regardless of transaction/subtransaction structure up to
that point; and it would also destroy open cursors.  The only way to
make this sane at all would be to provide user control of which
operations go to which connections; which is inherent in dblink's API
but is simply not a concept in the FDW universe.  And I don't want to
try to plaster it on, either.

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] [sepgsql 1/3] add name qualified creation label

2013-03-28 Thread Robert Haas
On Wed, Mar 27, 2013 at 8:41 AM, Robert Haas robertmh...@gmail.com wrote:
 Based on KaiGai's analysis, it seems to me that there is no serious
 problem here in terms of versioning, and as this patch represents a
 small but useful step forward in our support for SELinux integration,
 I'd like to go ahead and push it.

 Are there serious objections to that course of action?

Sounds like not, but when I ran the sepgsql regression tests with this
applied, they failed in the following way:

*** /home/rhaas/pgsql/contrib/sepgsql/expected/label.out
2013-03-28 10:49:26.513998274 -0400
--- /home/rhaas/pgsql/contrib/sepgsql/results/label.out 2013-03-28
10:50:50.818996744 -0400
***
*** 95,106 
   column  | t3.tableoid | unconfined_u:object_r:user_sepgsql_table_t:s0
   column  | t4.n| unconfined_u:object_r:sepgsql_table_t:s0
   column  | t4.m| unconfined_u:object_r:sepgsql_table_t:s0
!  column  | t4.ctid | unconfined_u:object_r:sepgsql_sysobj_t:s0
!  column  | t4.xmin | unconfined_u:object_r:sepgsql_sysobj_t:s0
!  column  | t4.cmin | unconfined_u:object_r:sepgsql_sysobj_t:s0
!  column  | t4.xmax | unconfined_u:object_r:sepgsql_sysobj_t:s0
!  column  | t4.cmax | unconfined_u:object_r:sepgsql_sysobj_t:s0
!  column  | t4.tableoid | unconfined_u:object_r:sepgsql_sysobj_t:s0
  (16 rows)

  --
--- 95,106 
   column  | t3.tableoid | unconfined_u:object_r:user_sepgsql_table_t:s0
   column  | t4.n| unconfined_u:object_r:sepgsql_table_t:s0
   column  | t4.m| unconfined_u:object_r:sepgsql_table_t:s0
!  column  | t4.ctid | unconfined_u:object_r:sepgsql_table_t:s0
!  column  | t4.xmin | unconfined_u:object_r:sepgsql_table_t:s0
!  column  | t4.cmin | unconfined_u:object_r:sepgsql_table_t:s0
!  column  | t4.xmax | unconfined_u:object_r:sepgsql_table_t:s0
!  column  | t4.cmax | unconfined_u:object_r:sepgsql_table_t:s0
!  column  | t4.tableoid | unconfined_u:object_r:sepgsql_table_t:s0
  (16 rows)

  --

Some trivial rebasing appears needed as well.

-- 
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] Catching resource leaks during WAL replay

2013-03-28 Thread Heikki Linnakangas

On 28.03.2013 01:01, Tom Lane wrote:

Simon Riggssi...@2ndquadrant.com  writes:

On 27 March 2013 20:40, Heikki Linnakangashlinnakan...@vmware.com  wrote:

While looking at bug #7969, it occurred to me that it would be nice if we
could catch resource leaks in WAL redo routines better. It would be useful
during development, to catch bugs earlier, and it could've turned that
replay-stopping error into a warning.



I'm inclined to think that the overhead isn't worth the trouble. This
is the only bug of its type we had in recent years.


I agree that checking for resource leaks after each WAL record seems
too expensive compared to what we'd get for it.  But perhaps it's worth
making a check every so often, like at restartpoints?


That sounds very seldom. How about making it an assertion to check after 
every record? I guess I'll have to do some testing to see how expensive 
it really is.


- 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] [COMMITTERS] pgsql: Allow external recovery_config_directory

2013-03-28 Thread Simon Riggs
On 28 March 2013 13:47, Heikki Linnakangas hlinnakan...@vmware.com wrote:

 Therefore anybody using pg_basebackup and the config_file parameter
 does *not* have an executable backup when used with the -R option, as
 Heikki was suggesting was a requirement for this patch.


 That's not related to the -R option, the situation with config_file is the
 same with or without it.

Yes, it is related to -R. You said that with -R we are expecting to
have a fully executable backup, which won't happen because the config
files are missing.

That is the situation now and the fact the patch had the same issue is
not relevant. It's a pre-existing issue.

-- 
 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] Catching resource leaks during WAL replay

2013-03-28 Thread Tom Lane
Heikki Linnakangas hlinnakan...@vmware.com writes:
 On 28.03.2013 01:01, Tom Lane wrote:
 Simon Riggssi...@2ndquadrant.com  writes:
 I'm inclined to think that the overhead isn't worth the trouble. This
 is the only bug of its type we had in recent years.

 I agree that checking for resource leaks after each WAL record seems
 too expensive compared to what we'd get for it.  But perhaps it's worth
 making a check every so often, like at restartpoints?

 That sounds very seldom. How about making it an assertion to check after 
 every record? I guess I'll have to do some testing to see how expensive 
 it really is.

Well, the actually productive part of this patch is to reduce such a
failure from ERROR to WARNING, which seems like it probably only
requires *one* resource cleanup after we exit the apply loop.  Doing it
per restartpoint is probably reasonable to limit the resource owner's
memory consumption (if there were a leak) over a long replay sequence.
I am really not seeing much advantage to doing it per record.

I suppose you are thinking of being helpful during development, but if
anything I would argue that the current behavior of a hard failure is
best for development.  It guarantees that the developer will notice the
failure, if it occurs at all in his test scenario; whereas a WARNING
that goes only to the postmaster log will be very very easily missed.

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] FDW for PostgreSQL

2013-03-28 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote:
 TBH I think this is a fairly bad idea.  You can get that behavior via
 dblink if you need it, 

While I appreciate that dblink can do it, I simply don't see it as a
good solution to this.

 but there's no way to do it in an FDW without
 ending up with astonishing (and not in a good way) semantics.  A commit
 would force committal of everything that'd been done through that
 connection, regardless of transaction/subtransaction structure up to
 that point; and it would also destroy open cursors.  The only way to
 make this sane at all would be to provide user control of which
 operations go to which connections; which is inherent in dblink's API
 but is simply not a concept in the FDW universe.  And I don't want to
 try to plaster it on, either.

This concern would make a lot more sense to me if we were sharing a
given FDW connection between multiple client backends/sessions; I admit
that I've not looked through the code but the documentation seems to
imply that we create one-or-more FDW connection per backend session and
there's no sharing going on.

A single backend will be operating in a linear fashion through the
commands sent to it.  As such, I'm not sure that it's quite as bad as it
may seem.

Perhaps a reasonable compromise would be to have a SERVER option which
is along the lines of 'autocommit', where a user could request that any
query to this server is automatically committed independent of the
client transaction.  I'd be happier if we could allow the user to
control it, but this would at least allow for 'log tables', which are
defined using this server definition, where long-running pl/pgsql code
could log progress where other connections could see it.

Thanks,

Stephen


signature.asc
Description: Digital signature


[HACKERS] Changing recovery.conf parameters into GUCs

2013-03-28 Thread Simon Riggs
What we want to do is make recovery parameters into GUCs, allowing
them to be reset by SIGHUP and also to allow all users to see the
parameters in use, from any session.

The existing mechanism for recovery is that
1. we put parameters in a file called recovery.conf
2. we use the existence of a recovery.conf file to trigger archive
recovery/replication

I also wish to see backwards compatibility maintained, so am proposing
the following:

a)  recovery parameters are made into GUCs (for which we have a patch
from Fujii)
b)  all processes automatically read recovery.conf as the last step in
reading configuration files, if it exists, even if data_directory
parameter is in use (attached patch)
c)  we trigger archive recovery by the presence of either
recovery.conf or recovery.trigger in the data directory. At the end,
we rename to recovery.done just as we do now. This means that any
parameters put into recovery.conf will not be re-read when we SIGHUP
after end of recovery. Note that recovery.trigger will NOT be read for
parameters and is assumed to be zero-length.
(minor patch required)

This allows these use cases

1. Existing users create $PGDATA/recovery.conf and everything works as
before. No servers crash, because the HA instructions in the wiki
still work. Users can now see the parameters in pg_settings and we can
use SIGHUP without restarting server. Same stuff, new benefits.

2. New users wish to move their existing recovery.conf file to the
config directory. Same file contents, same file name (if desired),
same behaviour, just more convenient location for config management
tools. Recovery is triggered by recovery.trigger in $PGDATA. Trigger
and configuration are now separate, if desired.

3. Split mode. We can put things like trigger_file into the
postgresql.conf directory and also put other parameters (for example
PITR settings) into recovery.conf. Where multiple tools are in use, we
support both APIs.

Specific details...

* primary_conninfo, trigger_file and standby_mode are added to
postgresql.conf.sample
* all ex-recovery.conf parameters are SIGHUP, so no errors if
recovery.conf has changed to .done

If desired, this behaviour could be enabled by a parameter called
recovery_conf_enabled = on (default). When set to off, this would
throw an error if recovery.conf exists. (I don't see a need for this)

The patch to implement this is very small (attached). This works
standalone, but obviously barfs at the actual parameter parsing stage.
Just in case it wasn't clear, this patch is intended to go with the
parts of Fujji's patch that relate to GUC changes.

If we agree, I will merge and re-post before commit.

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


read_recovery.conf_from_data_directory.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] FDW for PostgreSQL

2013-03-28 Thread Tom Lane
Stephen Frost sfr...@snowman.net writes:
 * Tom Lane (t...@sss.pgh.pa.us) wrote:
 ... The only way to
 make this sane at all would be to provide user control of which
 operations go to which connections; which is inherent in dblink's API
 but is simply not a concept in the FDW universe.  And I don't want to
 try to plaster it on, either.

 This concern would make a lot more sense to me if we were sharing a
 given FDW connection between multiple client backends/sessions; I admit
 that I've not looked through the code but the documentation seems to
 imply that we create one-or-more FDW connection per backend session and
 there's no sharing going on.

Well, ATM postgres_fdw shares connections across tables and queries;
but my point is that that's all supposed to be transparent and invisible
to the user.  I don't want to have API features that make connections
explicit, because I don't think that can be shoehorned into the FDW
model without considerable strain and weird corner cases.

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] sql_drop Event Triggerg

2013-03-28 Thread Alvaro Herrera
Pushed, with some further minor changes.  One not-so-minor change I
introduced was that pg_event_trigger_dropped_objects() now only works
within a sql_drop event function.  The reason I decided to do this was
that if we don't have that protection, then it is possible to have a
ddl_command_end trigger calling pg_event_trigger_dropped_objects(); and
if there is an sql_drop trigger, then it'd return the list of dropped
objects, but if there's no sql_drop trigger, it'd raise an error.  That
seemed surprising enough action-at-a-distance that some protection is
warranted.

Thanks for all the review.

-- 
Á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] FDW for PostgreSQL

2013-03-28 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote:
 I don't want to have API features that make connections
 explicit, because I don't think that can be shoehorned into the FDW
 model without considerable strain and weird corner cases.

It seems we're talking past each other here.  I'm not particularly
interested in exposing what connections have been made to other servers
via some API (though I could see some debugging use there).  What I was
hoping for is a way for a given user to say I want this specific
change, to this table, to be persisted immediately.  I'd love to have
that ability *without* FDWs too.  It just happens that FDWs provide a
simple way to get there from here and without a lot of muddying of the
waters, imv.

FDWs are no stranger to remote connections which don't have transactions
either, file_fdw will happily return whatever the current contents of
the file are with no concern for PG transactions.  I would expect a
writable file_fdw to act the same and immediately write out any data
written to it.

Hope that clarifies things a bit.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] [sepgsql 1/3] add name qualified creation label

2013-03-28 Thread Kohei KaiGai
Thanks for your checking.

I doubt of whether security policy module for this regression test is not
installed on your test environment.
Could you try ./test_sepgsql after:
  $ make -f /usr/share/selinux/devel/Makefile clean
  $ make -f /usr/share/selinux/devel/Makefile
  $ sudo semodule -i sepgsql-regtest
  $ sudo semodule -l | grep sepgsql-regtest
  sepgsql-regtest 1.05

I expect the installed sepgsql-regtest should be 1.05.

This patch contains updates towards the security policy that adds
special rule to assign special default security label on system
columns, so regression test will fail if previous policy was loaded.

It might ought to be checked within ./test_sepgsql script, however,
it takes superuser privilege to run semodule -l even though it lists
all the modules without any modification...

Thanks,

2013/3/28 Robert Haas robertmh...@gmail.com:
 On Wed, Mar 27, 2013 at 8:41 AM, Robert Haas robertmh...@gmail.com wrote:
 Based on KaiGai's analysis, it seems to me that there is no serious
 problem here in terms of versioning, and as this patch represents a
 small but useful step forward in our support for SELinux integration,
 I'd like to go ahead and push it.

 Are there serious objections to that course of action?

 Sounds like not, but when I ran the sepgsql regression tests with this
 applied, they failed in the following way:

 *** /home/rhaas/pgsql/contrib/sepgsql/expected/label.out
 2013-03-28 10:49:26.513998274 -0400
 --- /home/rhaas/pgsql/contrib/sepgsql/results/label.out 2013-03-28
 10:50:50.818996744 -0400
 ***
 *** 95,106 
column  | t3.tableoid | unconfined_u:object_r:user_sepgsql_table_t:s0
column  | t4.n| unconfined_u:object_r:sepgsql_table_t:s0
column  | t4.m| unconfined_u:object_r:sepgsql_table_t:s0
 !  column  | t4.ctid | unconfined_u:object_r:sepgsql_sysobj_t:s0
 !  column  | t4.xmin | unconfined_u:object_r:sepgsql_sysobj_t:s0
 !  column  | t4.cmin | unconfined_u:object_r:sepgsql_sysobj_t:s0
 !  column  | t4.xmax | unconfined_u:object_r:sepgsql_sysobj_t:s0
 !  column  | t4.cmax | unconfined_u:object_r:sepgsql_sysobj_t:s0
 !  column  | t4.tableoid | unconfined_u:object_r:sepgsql_sysobj_t:s0
   (16 rows)

   --
 --- 95,106 
column  | t3.tableoid | unconfined_u:object_r:user_sepgsql_table_t:s0
column  | t4.n| unconfined_u:object_r:sepgsql_table_t:s0
column  | t4.m| unconfined_u:object_r:sepgsql_table_t:s0
 !  column  | t4.ctid | unconfined_u:object_r:sepgsql_table_t:s0
 !  column  | t4.xmin | unconfined_u:object_r:sepgsql_table_t:s0
 !  column  | t4.cmin | unconfined_u:object_r:sepgsql_table_t:s0
 !  column  | t4.xmax | unconfined_u:object_r:sepgsql_table_t:s0
 !  column  | t4.cmax | unconfined_u:object_r:sepgsql_table_t:s0
 !  column  | t4.tableoid | unconfined_u:object_r:sepgsql_table_t:s0
   (16 rows)

   --

 Some trivial rebasing appears needed as well.

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



-- 
KaiGai Kohei kai...@kaigai.gr.jp


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


[HACKERS] Extra security measures for next week's releases

2013-03-28 Thread Tom Lane
The core committee has decided that one of the security issues due to be
fixed next week is sufficiently bad that we need to take extra measures
to prevent it from becoming public before packages containing the fix
are available.  (This is a scenario we've discussed before, but never
had to actually implement.)

What we intend to do is shut off updates from the master git repo to
the anonymous-git mirror, and to github, from Monday afternoon until
Thursday morning.  Commit-log emails to pgsql-committers will also be
held for this period.  This will prevent the commits that fix and
document the bug from becoming visible to anyone except Postgres
committers.  Updates will resume as soon as the release announcement
is made.

Although committers will still be able to work normally, we realize
that this is likely to be a handicap for non-committers; and it will
also mean that buildfarm runs will not test any new commits until the
mirrors are allowed to update.  We do not intend to start doing this
as a routine thing, and apologize in advance for any disruption.
It seems necessary in this instance, however.

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] pg_upgrade segfaults when given an invalid PGSERVICE value

2013-03-28 Thread Steve Singer

On 13-03-26 12:40 AM, Tom Lane wrote:

Bruce Momjian br...@momjian.us writes:

On Mon, Mar 25, 2013 at 07:07:42PM -0400, Tom Lane wrote:

Well, plan B would be to invent a replacement function that does have
the ability to return an error message, but that seems like a lot of
work for a problem that's so marginal that it wasn't noticed till now.
(It's not so much creating the function that worries me, it's fixing
clients to use it.)

Plan C would be to redefine bogus value of PGSERVICE as not an error,
period.



Given all of these poor options, is defining a PQconndefaults() as
perhaps out of memory or a service file problem really not better?


Uh ... no.  In the first place, what evidence have you got that those
are (and will continue to be) the only two possible causes?  In the
second place, this still requires changing every client of
PQconndefaults(), even if it's only to the extent of fixing their
error message texts.  If we're going to do that, I'd rather ask them
to change to a more future-proof solution.



So to summarise:

Plan A: The first patch I attached for pg_upgrade + documentation 
changes, and changing the other places that call PQconndefaults() to 
accept failures on either out of memory, or an invalid PGSERVICE


Plan B: Create a new function PQconndefaults2(char * errorBuffer) or 
something similar that returned error information to the caller.


Plan C: PQconndefaults() just ignores an invalid service but connection 
attempts fail because other callers of conninfo_add_defaults still pay 
attention to connection failures.  This is the second patch I sent.


Plan D:  Service lookup failures are always ignored by 
conninfo_add_defaults. If you attempt to connect with a bad PGSERVICE 
set it will behave as if no PGSERVICE value was set.   I don't think 
anyone explicitly proposed this yet.


Plan 'D' is the only option that I'm opposed to, it will effect a lot 
more applications then ones that call PQconndefaults() and I feel it 
will confuse users.


I'm not convinced plan B is worth the effort of having to maintain two 
versions of PQconndefaults() for a while to fix a corner case.







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] [sepgsql 1/3] add name qualified creation label

2013-03-28 Thread Robert Haas
On Thu, Mar 28, 2013 at 12:33 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote:
 Thanks for your checking.

 I doubt of whether security policy module for this regression test is not
 installed on your test environment.

Ah, you are right.  Sorry for the noise.

Committed.

-- 
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] [COMMITTERS] pgsql: sepgsql: Support for new post-ALTER access hook.

2013-03-28 Thread Robert Haas
On Wed, Mar 27, 2013 at 11:27 AM, Thom Brown t...@linux.com wrote:
 On 27 March 2013 15:19, Robert Haas robertmh...@gmail.com wrote:
 On Wed, Mar 27, 2013 at 10:51 AM, Thom Brown t...@linux.com wrote:
 create here is referring to the sepgsql permission, not the SQL
 command, so it's correct as-is.

 My bad.

 Here's an attempt at reworking the whole section to be more
 understandable.  I took the liberty of rearranging the text into
 bulleted lists, which I hope is more clear.

 Comments welcome.

 This looks a lot better, apart from:

 s/permssions/permissions/

 +1 from me.

OK, committed.

-- 
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] Call for Google Summer of Code mentors, admins

2013-03-28 Thread Tomas Vondra
Hi,

I've been asked by a local student to be his mentor in GSoC. He is
interested in working on parallelizing hash join/aggregation.

So I'd like to volunteer to be a mentor for this project (if it gets
accepted).

I've asked him to post his project proposal on pgsql-students.

kind regards
Tomas

On 14.2.2013 19:02, Josh Berkus wrote:
 Folks,
 
 Once again, Google is holding Summer of Code.  We need to assess whether
 we want to participate this year.
 
 Questions:
 
 - Who wants to mentor for GSOC?
 
 - Who can admin for GSOC?  Thom?
 
 - Please suggest project ideas for GSOC
 
 - Students seeing this -- please speak up if you have projects you plan
 to submit.
 



-- 
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] Ignore invalid indexes in pg_dump.

2013-03-28 Thread Bruce Momjian
On Tue, Mar 26, 2013 at 09:43:54PM +, Tom Lane wrote:
 Ignore invalid indexes in pg_dump.
 
 Dumping invalid indexes can cause problems at restore time, for example
 if the reason the index creation failed was because it tried to enforce
 a uniqueness condition not satisfied by the table's data.  Also, if the
 index creation is in fact still in progress, it seems reasonable to
 consider it to be an uncommitted DDL change, which pg_dump wouldn't be
 expected to dump anyway.
 
 Back-patch to all active versions, and teach them to ignore invalid
 indexes in servers back to 8.2, where the concept was introduced.

This commit affects pg_upgrade.  You might remember we had to patch
pg_upgrade to prevent it from migrating clusters with invalid indexes in
December, 2012:

http://momjian.us/main/blogs/pgblog/2012.html#December_14_2012

This was released on February 7, 2013 in 9.2.3 and other back branches:

http://www.postgresql.org/docs/9.2/static/release-9-2-3.html

This git commit means that pg_upgrade can again migrate systems with
invalid indexes as pg_upgrade can just skip migrating them because
pg_dump will dump the SQL commands to create them --- previously
pg_upgrade threw an error.

Should I just patch pg_upgrade to remove the indisvalid, skip
indisvalid indexes, and backpatch it?  Users should be using the
version of pg_upgrade to match new pg_dump.  Is there any case where
they don't match?  Do I still need to check for indisready?

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

  + It's impossible for everything to be true. +


-- 
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_upgrade segfaults when given an invalid PGSERVICE value

2013-03-28 Thread Bruce Momjian
On Thu, Mar 28, 2013 at 03:06:30PM -0400, Steve Singer wrote:
 So to summarise:
 
 Plan A: The first patch I attached for pg_upgrade + documentation
 changes, and changing the other places that call PQconndefaults() to
 accept failures on either out of memory, or an invalid PGSERVICE
 
 Plan B: Create a new function PQconndefaults2(char * errorBuffer) or
 something similar that returned error information to the caller.
 
 Plan C: PQconndefaults() just ignores an invalid service but
 connection attempts fail because other callers of
 conninfo_add_defaults still pay attention to connection failures.
 This is the second patch I sent.
 
 Plan D:  Service lookup failures are always ignored by
 conninfo_add_defaults. If you attempt to connect with a bad
 PGSERVICE set it will behave as if no PGSERVICE value was set.   I
 don't think anyone explicitly proposed this yet.
 
 Plan 'D' is the only option that I'm opposed to, it will effect a
 lot more applications then ones that call PQconndefaults() and I
 feel it will confuse users.
 
 I'm not convinced plan B is worth the effort of having to maintain
 two versions of PQconndefaults() for a while to fix a corner case.

Yep, that's pretty much it.  I agree B is overkill, and D seems
dangerous.  I think Tom likes C --- my only question is how many people
will be confused that C returns inaccurate information, because though
it returns connection options, it doesn't process the service file,
while forced processing of the service file for real connections throws
an error.

We might be fine with C, but it must be documented in the C code and
SGML docs.

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

  + It's impossible for everything to be true. +


-- 
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] Ignore invalid indexes in pg_dump.

2013-03-28 Thread Tom Lane
Bruce Momjian br...@momjian.us writes:
 Should I just patch pg_upgrade to remove the indisvalid, skip
 indisvalid indexes, and backpatch it?  Users should be using the
 version of pg_upgrade to match new pg_dump.  Is there any case where
 they don't match?  Do I still need to check for indisready?

Yeah, if you can just ignore !indisvalid indexes that should work fine.
I see no need to look at indisready if you're doing 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] Ignore invalid indexes in pg_dump.

2013-03-28 Thread anara...@anarazel.de


Tom Lane t...@sss.pgh.pa.us schrieb:

Bruce Momjian br...@momjian.us writes:
 Should I just patch pg_upgrade to remove the indisvalid, skip
 indisvalid indexes, and backpatch it?  Users should be using the
 version of pg_upgrade to match new pg_dump.  Is there any case where
 they don't match?  Do I still need to check for indisready?

Yeah, if you can just ignore !indisvalid indexes that should work fine.
I see no need to look at indisready if you're doing that.

You need to look at inisready in 9.2 since thats used for about to be dropped 
indexes. No?

Andres

--- 
Please excuse brevity and formatting - I am writing this on my mobile phone.


-- 
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] Ignore invalid indexes in pg_dump.

2013-03-28 Thread Bruce Momjian
On Thu, Mar 28, 2013 at 10:31:51PM +0100, anara...@anarazel.de wrote:
 
 
 Tom Lane t...@sss.pgh.pa.us schrieb:
 
 Bruce Momjian br...@momjian.us writes:
  Should I just patch pg_upgrade to remove the indisvalid, skip
  indisvalid indexes, and backpatch it?  Users should be using the
  version of pg_upgrade to match new pg_dump.  Is there any case where
  they don't match?  Do I still need to check for indisready?
 
 Yeah, if you can just ignore !indisvalid indexes that should work fine.
 I see no need to look at indisready if you're doing that.
 
 You need to look at inisready in 9.2 since thats used for about to be dropped 
 indexes. No?

Well, if it is dropped, pg_dump will not dump it.  At this point though,
pg_upgrade is either running in check mode, or it is the only user.  I
think we are OK.

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

  + It's impossible for everything to be true. +


-- 
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] Ignore invalid indexes in pg_dump.

2013-03-28 Thread Tom Lane
anara...@anarazel.de and...@anarazel.de writes:
 Tom Lane t...@sss.pgh.pa.us schrieb:
 Yeah, if you can just ignore !indisvalid indexes that should work fine.
 I see no need to look at indisready if you're doing that.

 You need to look at inisready in 9.2 since thats used for about to be dropped 
 indexes. No?

No, he doesn't need to look at indisready/indislive; if either of those
flags are off then indisvalid should certainly be off too.  (If it
isn't, queries against the table are already in trouble.)

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] Ignore invalid indexes in pg_dump.

2013-03-28 Thread anara...@anarazel.de


Tom Lane t...@sss.pgh.pa.us schrieb:

anara...@anarazel.de and...@anarazel.de writes:
 Tom Lane t...@sss.pgh.pa.us schrieb:
 Yeah, if you can just ignore !indisvalid indexes that should work
fine.
 I see no need to look at indisready if you're doing that.

 You need to look at inisready in 9.2 since thats used for about to be
dropped indexes. No?

No, he doesn't need to look at indisready/indislive; if either of those
flags are off then indisvalid should certainly be off too.  (If it
isn't, queries against the table are already in trouble.)

9.2 represents inisdead as live  !ready, doesn't it? So just looking at 
indislive will include about to be dropped or partially dropped indexes?

Andres

--- 
Please excuse brevity and formatting - I am writing this on my mobile phone.


-- 
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] Ignore invalid indexes in pg_dump.

2013-03-28 Thread Bruce Momjian
On Thu, Mar 28, 2013 at 10:47:55PM +0100, anara...@anarazel.de wrote:


 Tom Lane t...@sss.pgh.pa.us schrieb:

 anara...@anarazel.de and...@anarazel.de writes:
  Tom Lane t...@sss.pgh.pa.us schrieb:
  Yeah, if you can just ignore !indisvalid indexes that should work
 fine.
  I see no need to look at indisready if you're doing that.
 
  You need to look at inisready in 9.2 since thats used for about to
  be
 dropped indexes. No?
 
 No, he doesn't need to look at indisready/indislive; if either of
 those flags are off then indisvalid should certainly be off too.  (If
 it isn't, queries against the table are already in trouble.)

 9.2 represents inisdead as live  !ready, doesn't it? So just looking
 at indislive will include about to be dropped or partially dropped
 indexes?

Where do you see 'inisdead' defined?

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

  + It's impossible for everything to be true. +


-- 
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] Ignore invalid indexes in pg_dump.

2013-03-28 Thread Andres Freund
On 2013-03-28 17:54:08 -0400, Bruce Momjian wrote:
 On Thu, Mar 28, 2013 at 10:47:55PM +0100, anara...@anarazel.de wrote:
 
 
  Tom Lane t...@sss.pgh.pa.us schrieb:
 
  anara...@anarazel.de and...@anarazel.de writes:
   Tom Lane t...@sss.pgh.pa.us schrieb:
   Yeah, if you can just ignore !indisvalid indexes that should work
  fine.
   I see no need to look at indisready if you're doing that.
  
   You need to look at inisready in 9.2 since thats used for about to
   be
  dropped indexes. No?
  
  No, he doesn't need to look at indisready/indislive; if either of
  those flags are off then indisvalid should certainly be off too.  (If
  it isn't, queries against the table are already in trouble.)
 
  9.2 represents inisdead as live  !ready, doesn't it? So just looking
  at indislive will include about to be dropped or partially dropped
  indexes?
 
 Where do you see 'inisdead' defined?

Sorry, its named the reverse, indislive. And its only there in 9.3+...

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] Ignore invalid indexes in pg_dump.

2013-03-28 Thread Tom Lane
anara...@anarazel.de and...@anarazel.de writes:
 9.2 represents inisdead as live  !ready, doesn't it? So just looking at 
 indislive will include about to be dropped or partially dropped indexes?

Ooooh ... you're right, in 9.2 (only) we need to check both indisvalid
and indisready (cf IndexIsValid() macro in the different branches).
So that's actually a bug in the committed pg_dump patch, too.  I'll fix
that shortly.

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] Ignore invalid indexes in pg_dump.

2013-03-28 Thread Andres Freund
On 2013-03-28 17:35:08 -0400, Bruce Momjian wrote:
 On Thu, Mar 28, 2013 at 10:31:51PM +0100, anara...@anarazel.de wrote:
  
  
  Tom Lane t...@sss.pgh.pa.us schrieb:
  
  Bruce Momjian br...@momjian.us writes:
   Should I just patch pg_upgrade to remove the indisvalid, skip
   indisvalid indexes, and backpatch it?  Users should be using the
   version of pg_upgrade to match new pg_dump.  Is there any case where
   they don't match?  Do I still need to check for indisready?
  
  Yeah, if you can just ignore !indisvalid indexes that should work fine.
  I see no need to look at indisready if you're doing that.
  
  You need to look at inisready in 9.2 since thats used for about to be 
  dropped indexes. No?
 
 Well, if it is dropped, pg_dump will not dump it.  At this point though,
 pg_upgrade is either running in check mode, or it is the only user.  I
 think we are OK.

Its about DROP INDEX CONCURRENTLY which can leave indexes in a partial state
visible to the outside. Either just transiently while a DROP CONCURRENLTY is
going on or even permanently if the server crashed during such a drop or
similar. c.f. index.c:index_drop

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


[HACKERS] Hash Join cost estimates

2013-03-28 Thread Stephen Frost
All,

  Marty's issue w/ NestLoop reminded me that I'd put together a test
  case which illustrates a HashJoin doing the wrong thing.

  The test case is here:

  http://snowman.net/~sfrost/test_case.sql

  Simply load that up and then check out this plan:

  explain select * from small_table join big_table using (id_short);

  We end up deciding to build a hash table of 4M records and then seq
  scan the table that only has 41K.  Much of the reason for this is
  because the analytics point out, correctly, that the column we're
  using from the small table to join isn't unique and therefore the
  buckets will be deeper- but it's not *nearly* as bad as we estimate.

  The bucket estimate for the small table comes back as 26, while the
  reality is that we only look through ~5.5 entries per bucket with
  the longest run being 21.  With the big table being hashed, the bucket
  estimate is 4 (though this seem to vary way more than I would expect,
  sometimes 4, sometimes 8, sometimes 2..) while the average number
  scanned through is actually ~3.5 and the longest scan ends up being
  20.

  Without the bucket question, we end up with pretty reasonable results
  (directly out of initial_cost_hashjoin):

  41K hashed, seqscan 4M:
  startup_cost = 1229.46
  run_cost = 72307.39

  4M hashed, 41K seqscan:
  startup_cost = 115030.10
  run_cost = 817.70

  When we get through dealing with the bucketing question in
  final_cost_hashjoin (costsize.c:2673), we have some pretty gross
  results for the 'good' plan's run_cost:

  run_cost = 72307.39 + 138848.81 = 211156.20

  While the 'bad' plan's run_cost is only bumped a tiny bit:

  run_cost = 817.7 + 411.76 = 1229.46

  Resulting in total costs that look all kinds of wacky:

  41K hashed, seqscan 4M: 115030.10 + 1229.46 = 116259.56
  4M hashed, seqscan 41K: 1229.46 + 211156.20 = 212385.66

  Or the 'good' plan being costed at *nearly double*.  Now, my laptop
  might not be the 'best' system CPU wise, but there's a pretty massive
  time difference between these plans:

  41K hashed, seqscan 4M: 2252.007 ms http://explain.depesz.com/s/FEq
  4M hashed, seqscan 41K: 2708.471 ms http://explain.depesz.com/s/FOU

  That's half a second and a good 15+% difference.

  Now, back to the bucket estimation- the ndistinct for the small table
  is -0.475058 (or 19561 tuples), which is about right.  There are 23
  distinct values, 18,795 duplicated, and another 841 with dup counts
  ranging from 3 to 10.  This leads to an avgfreq of 0.51,
  unfortunately, we're going for only 8192 buckets, so this gets moved
  up to 0.00012 and then the adjustment for MCV (which is 0.00027) bumps
  this all the way up to 0.00064, leading to our bucket depth estimate
  of 26 (bucket size estimate multiplied by the inner rows) and the
  resulting cost dominating the overall costing.

  If we consider the bucketing wrong and look at what the costs would
  have been with the actual number of average scans (and therefore
  excluding the 0.5 'adjustment'), we get:

  seqscan 41K cost: 360.28 (cpu_op_cost * 41K * 3.5)
  seqscan 4M cost: 58743.73 (cpu_op_cost * 4M * 5.5)

  which isn't exactly going in the 'right' direction for us.  Now, I'm
  sure that there's a cost to having to consider more buckets, but it
  seems to be far less, in comparison to the hash table creation cost,
  than what our model would suggest.
  
  In the end, I think the problem here is that we are charging far too
  much for these bucket costs (particularly when we're getting them so
  far wrong) and not nearly enough for the cost of building the hash
  table in the first place.

  Thoughts?  Ideas about how we can 'fix' this?  Have others run into
  similar issues?

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [PATCH] Exorcise zero-dimensional arrays (Was: Re: [HACKERS] Should array_length() Return NULL)

2013-03-28 Thread Brendan Jurd
On 28 March 2013 20:34, Dean Rasheed dean.a.rash...@gmail.com wrote:
 Is the patch also going to allow empty arrays in higher dimensions
 where not just the last dimension is empty?

It doesn't allow that at present.

 It seems as though, if
 it's allowing 1-by-0 arrays like '{{}}' and '[4:4][8:7]={{}}', it
 should also allow 0-by-0 arrays like '[4:3][8:7]={}', and 0-by-3
 arrays like '[4:3][11:13]={}'.

I think the array literal parser would reject this on the grounds that
the brace structure doesn't match the dimension specifiers.  You could
modify that check to respect zero length in dimensions other than the
innermost one, but it's hard to say whether it would be worth the
effort.

It might be instructive to hear from somebody who does (or intends to)
use multidim arrays for some practical purpose, but I don't even know
whether such a person exists within earshot of this list.

Cheers,
BJ


-- 
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] Enabling Checksums

2013-03-28 Thread Robert Haas
On Wed, Mar 27, 2013 at 10:15 AM, Andres Freund and...@2ndquadrant.com wrote:
 On 2013-03-27 10:06:19 -0400, Robert Haas wrote:
 On Mon, Mar 18, 2013 at 4:31 PM, Greg Smith g...@2ndquadrant.com wrote:
  to get them going again.  If the install had checksums, I could have 
  figured
  out which blocks were damaged and manually fixed them, basically go on a
  hunt for torn pages and the last known good copy via full-page write.

 Wow.  How would you extract such a block image from WAL?

 That would be a great tool to have, but I didn't know there was any
 practical way of doing it today.

 Given pg_xlogdump that should be doable with 5min of hacking in 9.3. Just add
 some hunk to write out the page to the if (config-bkp_details) hunk in
 pg_xlogdump.c:XLogDumpDisplayRecord. I have done that for some debugging 
 already.

 If somebody comes up with a sensible  simple UI for this I am willing to
 propose a patch adding it to pg_xlogdump. One would have to specify the
 rel/file/node, the offset, and the target file.

Hmm.  Cool.  But, wouldn't the hard part be to figure out where to
start reading the WAL in search of the *latest* FPI?

-- 
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] Changing recovery.conf parameters into GUCs

2013-03-28 Thread Michael Paquier
Hi,

The main argument on which this proposal is based on is to keep
backward-compatibility.
This has been discussed before many times and the position of each people
is well-known,
so I am not going back to that...

So, based on *only* what I see in this thread...

On Fri, Mar 29, 2013 at 12:48 AM, Simon Riggs si...@2ndquadrant.com wrote:

 What we want to do is make recovery parameters into GUCs, allowing
 them to be reset by SIGHUP and also to allow all users to see the
 parameters in use, from any session.

 The existing mechanism for recovery is that
 1. we put parameters in a file called recovery.conf
 2. we use the existence of a recovery.conf file to trigger archive
 recovery/replication

 I also wish to see backwards compatibility maintained, so am proposing
 the following:

 a)  recovery parameters are made into GUCs (for which we have a patch
 from Fujii)

b)  all processes automatically read recovery.conf as the last step in
 reading configuration files, if it exists, even if data_directory
 parameter is in use (attached patch)
 c)  we trigger archive recovery by the presence of either
 recovery.conf or recovery.trigger in the data directory. At the end,
 we rename to recovery.done just as we do now. This means that any
 parameters put into recovery.conf will not be re-read when we SIGHUP
 after end of recovery. Note that recovery.trigger will NOT be read for
 parameters and is assumed to be zero-length.
 (minor patch required)

 This allows these use cases

 1. Existing users create $PGDATA/recovery.conf and everything works as
 before. No servers crash, because the HA instructions in the wiki
 still work. Users can now see the parameters in pg_settings and we can
 use SIGHUP without restarting server. Same stuff, new benefits.

Forcing hardcoding of include_if_exists recovery.conf at the bottom of
postgresql.conf
is not a good thing for the user as it makes postgresql.conf processing
more opaque and
complicates parameter reloading. IMO, simplicity and transparency of
operations are
important when processing parameters.

2. New users wish to move their existing recovery.conf file to the
 config directory. Same file contents, same file name (if desired),
 same behaviour, just more convenient location for config management
 tools. Recovery is triggered by recovery.trigger in $PGDATA. Trigger
 and configuration are now separate, if desired.

So, people could be able to also define a recovery.trigger file? What about
the case where both recovery.conf and recovery.trigger are found in the
base folder?
Priority needs to be given to one way of processing or the other. Is it
really our goal
to confuse the user with internal priority mechanisms at least for GUC
handling?

3. Split mode. We can put things like trigger_file into the
 postgresql.conf directory and also put other parameters (for example
 PITR settings) into recovery.conf. Where multiple tools are in use, we
 support both APIs.

 Specific details...

 * primary_conninfo, trigger_file and standby_mode are added to
 postgresql.conf.sample

Not adding all the recovery_target_* parameters would confuse the user.
With this proposal it is actually possible to define a recovery target
inside
postgresql.conf even if the parameter is not plainly written in
postgresql.conf.sample.


 * all ex-recovery.conf parameters are SIGHUP, so no errors if
 recovery.conf has changed to .done

Drop of recovery.trigger at the same time? And what about the case
where both files are specified, that the server can only remove one of the
trigger files, and is then restarted with only 1 trigger file present?

If desired, this behaviour could be enabled by a parameter called
 recovery_conf_enabled = on (default). When set to off, this would
 throw an error if recovery.conf exists. (I don't see a need for this)

Me neither, the less GUCs the better.


 The patch to implement this is very small (attached). This works
 standalone, but obviously barfs at the actual parameter parsing stage.
 Just in case it wasn't clear, this patch is intended to go with the
 parts of Fujji's patch that relate to GUC changes.

 If we agree, I will merge and re-post before commit.

If an agreement is reached based on this proposal, I highly recommend that
you use one of the latest updated version I sent. Fujii's version had some
bugs, one of them being that as standbyModeRequested can be set to true if
specified in postgresql.conf, a portion of the code using in xlog.c can be
triggered even if ArchiveRecoveryRequested is not set to true. I also added
fixes related to documentation.

Comments from others are welcome.
-- 
Michael


[HACKERS] Draft release notes up for review

2013-03-28 Thread Tom Lane
Since there has been some, um, grumbling about the quality of the
release notes of late, I've prepared draft notes for next week's
releases, covering commits through today.  These are now committed
into the master branch for review, and should show up at
http://www.postgresql.org/docs/devel/static/
after guaibasaurus' next buildfarm run, about three hours from now.

Please comment if you find anything that could be improved.

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] Draft release notes up for review

2013-03-28 Thread Alvaro Herrera
Tom Lane wrote:
 Since there has been some, um, grumbling about the quality of the
 release notes of late, I've prepared draft notes for next week's
 releases, covering commits through today.  These are now committed
 into the master branch for review, and should show up at
 http://www.postgresql.org/docs/devel/static/
 after guaibasaurus' next buildfarm run, about three hours from now.

I prodded it a bit -- it's up now.

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


[HACKERS] ISAM to SQL

2013-03-28 Thread John Mudd
I see a few old messages referring to ISAM to SQL emulation.

For example:
http://www.postgresql.org/message-id/200402171616.i1hgg9u11...@candle.pha.pa.us

Does anyone know of any actual source code to one of these projects? Any
active projects?

John


Re: [HACKERS] ISAM to SQL

2013-03-28 Thread Merlin Moncure
On Thu, Mar 28, 2013 at 9:47 PM, John Mudd johnbm...@gmail.com wrote:

 I see a few old messages referring to ISAM to SQL emulation.

 For example:

 http://www.postgresql.org/message-id/200402171616.i1hgg9u11...@candle.pha.pa.us

 Does anyone know of any actual source code to one of these projects? Any
 active projects?

I wrote an unfortunately closed source ISAM driver for postgresql for
COBOL (AcuCobol).  It was not a too terribly difficult project --
Postgres has a couple of features that made it pretty easy, especially
advisory locks and row-wise comparison.  Type conversion can be
difficult or complex depending on how far you want to take things and
how clean the API  is that you will be hooking on the ISAM side.  I
almost put together a C-ISAM driver a couple years back for a project
but it fell through.

merlin


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