date:20130125

Re: [HACKERS] Visual Studio 2012 RC

2013-01-25 Thread Andrew Dunstan



On 01/26/2013 12:38 AM, Craig Ringer wrote:

On 01/25/2013 08:25 PM, Noah Misch wrote:

That should be clearer, that 64-bit support exists but is absent (AFAIK)
from Express editions.

Build farm member "chough" builds 64-bit using VS 2008 Express.

Huh. My 2008 doesn't appear to have 64-bit compilers or cross-compilers
and didn't offer them as an option.

Need to look into that.


That might be a typo.  The machine is currently offline waiting on a new 
CPU fan, but I'll check when it comes back up.


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] Minor inheritance/check bug: Inconsistent behavior

2013-01-25 Thread Amit Kapila

On Friday, January 25, 2013 8:36 PM Bruce Momjian wrote:
> On Fri, Sep 14, 2012 at 02:04:51PM +, Amit kapila wrote:
> > On Thu, 6 Sep 2012 14:50:05 -0400 Robert Hass wrote:
> >
> > On Tue, Aug 28, 2012 at 6:40 AM, Amit Kapila
>  > com> wrote:
> > >>   AFAICT during Update also, it doesn't contain useful. The only
> chance it
> > >> would have contain something useful is when it goes for
> EvalPlanQual and
> > >> then again comes to check for constraints. However these
> attributes get
> > >> filled in heap_update much later.
> > >
> > >> So now should the fix be that it returns an error for system
> column
> > >> reference except for OID case?
> >
> > > +1.
> >
> >
> >
> > 1. I think in this scenario the error for system column except for
> tableOID
> > should be thrown at Create/Alter time.
> >
> > 2. After setting OID in ExecInsert/ExecUpdate may be setting of same
> inside
> > heap functions can be removed.
> >
> >But for now I have kept them as it is.
> >
> >
> >
> > Please find the Patch for bug-fix.
> >
> > If this is okay, I shall send you the test cases for same.
> 
> Did we ever make any progress on this?

I have done the initial analysis and prepared a patch, don't know if
anything more I can do until
someone can give any suggestions to further proceed on this bug. 

With Regards,
Amit Kapila.



-- 
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] Visual Studio 2012 RC

2013-01-25 Thread Craig Ringer

On 01/25/2013 08:25 PM, Noah Misch wrote:
>> That should be clearer, that 64-bit support exists but is absent (AFAIK)
>> from Express editions.
> Build farm member "chough" builds 64-bit using VS 2008 Express.
Huh. My 2008 doesn't appear to have 64-bit compilers or cross-compilers
and didn't offer them as an option.

Need to look into that.

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

Re: [HACKERS] COPY FREEZE has no warning

2013-01-25 Thread Bruce Momjian

On Fri, Jan 25, 2013 at 11:08:56PM -0500, Tom Lane wrote:
> Bruce Momjian  writes:
> > !   ereport(ERROR,
> > !   
> > (ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE,
> > !   errmsg("cannot perform FREEZE 
> > because of previous table activity in the current transaction")));
> 
> [ itch... ]  What is "table activity"?  I always thought of tables as
> being rather passive objects.  And anyway, isn't this backwards?  What
> we're complaining of is *lack* of activity.  I don't see why this isn't
> using the same message as the other code path, namely

Well, here is an example of this message:

BEGIN;
TRUNCATE vistest;
SAVEPOINT s1;
COPY vistest FROM stdin CSV FREEZE;
ERROR:  cannot perform FREEZE because of previous table activity in the 
current transaction
COMMIT;

Clearly it was truncated in the same transaction, but the savepoint
somehow invalidates the freeze.  There is a C comment about it:

 * BEGIN;
 * TRUNCATE t;
 * SAVEPOINT save;
 * TRUNCATE t;
 * ROLLBACK TO save;
 * COPY ...

I changed it to:

ERROR:  cannot perform FREEZE because of transaction activity after 
table creation or truncation

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

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
new file mode 100644
index 6a0fabc..2137c67
*** a/doc/src/sgml/ref/copy.sgml
--- b/doc/src/sgml/ref/copy.sgml
*** COPY { ta
*** 190,207 
would be after running the VACUUM FREEZE command.
This is intended as a performance option for initial data loading.
Rows will be frozen only if the table being loaded has been created
!   in the current subtransaction, there are no cursors open and there
!   are no older snapshots held by this transaction. If those conditions
!   are not met the command will continue without error though will not
!   freeze rows. It is also possible in rare cases that the request
!   cannot be honoured for internal reasons, hence FREEZE
!   is more of a guideline than a hard rule.
   
   
Note that all other sessions will immediately be able to see the data
once it has been successfully loaded. This violates the normal rules
!   of MVCC visibility and by specifying this option the user acknowledges
!   explicitly that this is understood.
   
  
 
--- 190,203 
would be after running the VACUUM FREEZE command.
This is intended as a performance option for initial data loading.
Rows will be frozen only if the table being loaded has been created
!   or truncated in the current subtransaction, there are no cursors
!   open and there are no older snapshots held by this transaction.
   
   
Note that all other sessions will immediately be able to see the data
once it has been successfully loaded. This violates the normal rules
!   of MVCC visibility and users specifying should be aware of the
!   potential problems this might cause.
   
  
 
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
new file mode 100644
index 8778e8b..49cc8dd
*** a/src/backend/commands/copy.c
--- b/src/backend/commands/copy.c
*** CopyFrom(CopyState cstate)
*** 1978,1985 
  	 * ROLLBACK TO save;
  	 * COPY ...
  	 *
- 	 * However this is OK since at worst we will fail to make the optimization.
- 	 *
  	 * Also, if the target file is new-in-transaction, we assume that checking
  	 * FSM for free space is a waste of time, even if we must use WAL because
  	 * of archiving.  This could possibly be wrong, but it's unlikely.
--- 1978,1983 
*** CopyFrom(CopyState cstate)
*** 1991,1996 
--- 1989,1995 
  	 * no additional work to enforce that.
  	 *--
  	 */
+ 	/* createSubid is creation check, newRelfilenodeSubid is truncation check */
  	if (cstate->rel->rd_createSubid != InvalidSubTransactionId ||
  		cstate->rel->rd_newRelfilenodeSubid != InvalidSubTransactionId)
  	{
*** CopyFrom(CopyState cstate)
*** 2006,2023 
  		 * after xact cleanup. Note that the stronger test of exactly
  		 * which subtransaction created it is crucial for correctness
  		 * of this optimisation.
- 		 *
- 		 * As noted above rd_newRelfilenodeSubid is not set in all cases
- 		 * where we can apply the optimization, so in those rare cases
- 		 * where we cannot honour the request we do so silently.
  		 */
! 		if (cstate->freeze &&
! 			ThereAreNoPriorRegisteredSnapshots() &&
! 			ThereAreNoReadyPortals() &&
! 			(cstate->rel->rd_newRelfilenodeSubid == GetCurrentSubTransactionId() ||
! 			 cstate->rel->rd_createSubid == GetCurrentSubTransactionId()))
! 			hi_options |= HEAP_INSERT_FRO

Re: [HACKERS] Visual Studio 2012 RC

2013-01-25 Thread Craig Ringer

On 01/24/2013 01:13 PM, Craig Ringer wrote:
> On 01/24/2013 11:28 AM, Craig Ringer wrote:
>> On 01/24/2013 09:38 AM, Noah Misch wrote:
>>> The most notable difference is that I have no pre-VS2012 Microsoft
>>> compilers installed and no SDKs installed by my explicit action. I
>>> suggest assessing how the Framework64 directories get into your path
>>> and trying without them. nm 
>> Have you verified that 64-bit builds work? I'm testing now, but I've
>> just confirmed that my machine isn't quite right.
Just to confirm, I think that this is ready for commit as posted in
20130101025421.ga17...@tornado.leadboat.com.

I'll amend my docs changes and submit them separately.

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

Re: [HACKERS] COPY FREEZE has no warning

2013-01-25 Thread Tom Lane

Bruce Momjian  writes:
> ! ereport(ERROR,
> ! 
> (ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE,
> ! errmsg("cannot perform FREEZE 
> because of previous table activity in the current transaction")));

[ itch... ]  What is "table activity"?  I always thought of tables as
being rather passive objects.  And anyway, isn't this backwards?  What
we're complaining of is *lack* of activity.  I don't see why this isn't
using the same message as the other code path, namely

> + ereport(ERROR,
> + (ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE,
> +  errmsg("cannot perform FREEZE because the 
> table was not created or truncated in the current transaction")));

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] COPY FREEZE has no warning

2013-01-25 Thread Bruce Momjian

On Fri, Jan 25, 2013 at 05:30:58PM -0500, Tom Lane wrote:
> Bruce Momjian  writes:
> > OK, updated patch attached that throws an error with a more specific
> > message.
> 
> 
> >  *
> >  * As noted above rd_newRelfilenodeSubid is not set in all cases
> >  * where we can apply the optimization, so in those rare cases
> > !* where we cannot honor the request.
> >  */
> 
> This sentence not complete.  I kind of think the entire para visible
> above could be removed, anyway.
> 
> > !   ereport(ERROR, (errmsg("cannot perform FREEZE 
> > operation due to invalid table or transaction state")));
> 
> I don't find this terribly specific.  It would at least be useful to
> have two messages distinguishing whether the cause was invalid table
> state (rd_createSubid and rd_newRelfilenodeSubid not set) or invalid
> transaction state (the snapshot and portal tests).  The former might
> usefully be phrased as "because the table was not created or truncated
> in the current transaction" and the latter as "because other actions are
> in progress within the current transaction".
> 
> I'd also suggest "cannot perform COPY FREEZE because " rather
> than using the unnecessarily vague "operation".
> 
> Also, this is missing an errcode, which means it will report itself as
> an internal error, which it ain't.  It's also randomly unlike the
> standard layout for ereport calls.
> ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE would do for the table case,
> not sure about the other.

OK, that was tricky, but completed with the attached patch. 
Surprisingly, truncation wasn't mention in our docs, though it was used
in the regression tests.  I have fixed that.

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

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
new file mode 100644
index 6a0fabc..2137c67
*** a/doc/src/sgml/ref/copy.sgml
--- b/doc/src/sgml/ref/copy.sgml
*** COPY { ta
*** 190,207 
would be after running the VACUUM FREEZE command.
This is intended as a performance option for initial data loading.
Rows will be frozen only if the table being loaded has been created
!   in the current subtransaction, there are no cursors open and there
!   are no older snapshots held by this transaction. If those conditions
!   are not met the command will continue without error though will not
!   freeze rows. It is also possible in rare cases that the request
!   cannot be honoured for internal reasons, hence FREEZE
!   is more of a guideline than a hard rule.
   
   
Note that all other sessions will immediately be able to see the data
once it has been successfully loaded. This violates the normal rules
!   of MVCC visibility and by specifying this option the user acknowledges
!   explicitly that this is understood.
   
  
 
--- 190,203 
would be after running the VACUUM FREEZE command.
This is intended as a performance option for initial data loading.
Rows will be frozen only if the table being loaded has been created
!   or truncated in the current subtransaction, there are no cursors
!   open and there are no older snapshots held by this transaction.
   
   
Note that all other sessions will immediately be able to see the data
once it has been successfully loaded. This violates the normal rules
!   of MVCC visibility and users specifying should be aware of the
!   potential problems this might cause.
   
  
 
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
new file mode 100644
index 8778e8b..be249f3
*** a/src/backend/commands/copy.c
--- b/src/backend/commands/copy.c
*** CopyFrom(CopyState cstate)
*** 1978,1985 
  	 * ROLLBACK TO save;
  	 * COPY ...
  	 *
- 	 * However this is OK since at worst we will fail to make the optimization.
- 	 *
  	 * Also, if the target file is new-in-transaction, we assume that checking
  	 * FSM for free space is a waste of time, even if we must use WAL because
  	 * of archiving.  This could possibly be wrong, but it's unlikely.
--- 1978,1983 
*** CopyFrom(CopyState cstate)
*** 1991,1996 
--- 1989,1995 
  	 * no additional work to enforce that.
  	 *--
  	 */
+ 	/* createSubid is creation check, newRelfilenodeSubid is truncation check */
  	if (cstate->rel->rd_createSubid != InvalidSubTransactionId ||
  		cstate->rel->rd_newRelfilenodeSubid != InvalidSubTransactionId)
  	{
*** CopyFrom(CopyState cstate)
*** 2006,2023 
  		 * after xact cleanup. Note that the stronger test of exactly
  		 * which subtransaction created it is crucial for correctness
  		 * of this optimisation.
- 		 *
- 		 * As noted above rd_newRelfilenodeSubid is

[HACKERS] checking variadic "any" argument in parser - should be array

2013-01-25 Thread Pavel Stehule

Hello Tom

you did comment

! <><--><--> * Non-null argument had better be an array.
The parser doesn't
! <><--><--> * enforce this for VARIADIC ANY functions
(maybe it should?), so
! <><--><--> * that check uses ereport not just elog.
! <><--><--> */

So I moved this check to parser.

It is little bit stricter - requests typed NULL instead unknown NULL,
but it can mark error better and early

Regards

Pavel


variadic_any_parser_check.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] proposal - assign result of query to psql variable

2013-01-25 Thread Tom Lane

Pavel Stehule  writes:
> [ gset_13.diff ]

I looked at this a bit.  I think you need to reconsider when and how the
\gset state gets cleaned up.  Doing it inside StoreQueryResult is not
very good because that only gets reached upon successful execution.
Consider for example

select 1/0 \gset x

You'll get an ERROR from this, and a reasonable user would suppose that
that was that and the \gset is no longer in effect.  But guess what,
it's still lurking under the surface, waiting to capture his next command.

This is also causing you unnecessary complication in
ExecQueryUsingCursor, which has to work around the fact that
StoreQueryResult destroys state.

I think it would be better to remove that responsibility from
StoreQueryResult and instead put the gset-list cleanup somewhere at the
end of query processing.  Didn't really look into where would be the
best place, but it should be someplace that control passes through no
matter what came back from the server.

BTW, is StoreQueryResult in itself (that is, the switch on
PQresultStatus) actually doing anything useful?  It appears to me that
the error cases are handled elsewhere, such that control never gets to
it unless the PQresultStatus is an expected value.  If that were not the
case, printouts as laconic as "bad response\n" would certainly not be
acceptable --- people would want to see the underlying error message.

Also, I'm not sure I like the PSQL_CMD_NOSEND business, ie, trashing
the query buffer if anything can be found wrong with the \gset itself.
If I've done

big long multiline query here
\gset x y

I'd expect that the error only discards the \gset and not the query.
An error in some other sort of backslash command in that situation
wouldn't discard the query buffer.  For instance try this:

regression=# select 3+2
regression-# \goofus
Invalid command \goofus. Try \? for help.
regression-# ;
 ?column? 
--
5
(1 row)

regression=# 

So AFAICS, PSQL_CMD_NOSEND just represents extra code that's making
things worse not better.

One more gripe is that the parsing logic for \gset is pretty
unintelligible.  You've got a "state" variable with only two values,
whose names seem to boil down to whether a comma is expected or not.
And then you've got a separate "process_comma" flag, which means
... well, I'm not sure, but possibly the very same thing.  For sure it's
not clear whether all four possible combinations of those two variables
are valid and what they'd mean.  This could use another round of
thinking and rewriting.  Or at least better comments.

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

2013-01-25 Thread Jeff Davis

On Fri, 2013-01-25 at 15:29 -0500, Robert Haas wrote:
> I thought Simon had the idea, at some stage, of writing a WAL record
> to cover hint-bit changes only at the time we *write* the buffer and
> only if no FPI had already been emitted that checkpoint cycle.  I'm
> not sure whether that approach was sound, but if so it seems more
> efficient than this approach.

My patch is based on his original idea; although I've made quite a lot
of changes, I believe that I have stuck to his same basic design w.r.t.
WAL.

This patch does not cause a new FPI to be emitted if one has already
been emitted this cycle. It also does not emit a WAL record at all if an
FPI has already been emitted.

If we were to try to defer writing the WAL until the page was being
written, the most it would possibly save is the small XLOG_HINT WAL
record; it would not save any FPIs.

At first glance, it seems sound as long as the WAL FPI makes it to disk
before the data. But to meet that requirement, it seems like we'd need
to write an FPI and then immediately flush WAL before cleaning a page,
and that doesn't seem like a win. Do you (or Simon) see an opportunity
here that I'm missing?

By the way, the approach I took was to add the heap buffer to the WAL
chain of the XLOG_HEAP2_VISIBLE wal record when doing log_heap_visible.
It seemed simpler to understand than trying to add a bunch of options to
MarkBufferDirty.

Regards,
Jeff Davis

-- 
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] Doc patch, normalize search_path in index

2013-01-25 Thread Bruce Momjian

On Fri, Jan 25, 2013 at 05:10:14PM -0500, Peter Eisentraut wrote:
> On 1/25/13 1:59 PM, Bruce Momjian wrote:
> > On Fri, Jan 25, 2013 at 01:46:46PM -0500, Peter Eisentraut wrote:
> >> This matter was already closed:
> >> https://commitfest.postgresql.org/action/patch_view?id=949
> >>
> >> It looks like your patch reverts part of that.
> > 
> > Uh, I am confused because the patch at:
> > 
> > https://commitfest.postgresql.org/action/patch_view?id=950
> > http://www.postgresql.org/message-id/1352874080.4647.0@mofo
> > 
> > shows "configuration parameter" being moved to , though this
> > commit:
> > 
> > 
> > http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=a301eb99c9537186f7dd46ba418e84d755227a94
> 
> The discussion yielded a patch that is different from the commit fest entry.
> 
> We index all GUC settings as
> 
> something configuration parameter
> 
> which the commit a301eb99c9537186f7dd46ba418e84d755227a94 also made the case 
> for search_path.
> 
> Your two commits changed that again.
> 
> > shows it not as secondary.  Would you please suggest a patch or patch
> > it?  Thanks.
> 
> I think both of your commits should be reverted.

I now see the pattern.  Thanks.  Patches reverted.

-- 
  Bruce Momjian  http://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] Hanging backends and possible index corruption

2013-01-25 Thread Tom Lane

Bernd Helmle  writes:
> So i've saved the index file (normal BTree index with a single bigint 
> column), did a REINDEX and the problem was gone. Looking at the index file 
> with pg_filedump and pgbtreecheck from Alvaro gave me the following output:
> ...

Don't know how careful pgbtreecheck is.  The pg_filedump output isn't
very helpful because you filtered away the flags, so we can't tell if
any of these pages are deleted.  (If they are, the duplicate-looking
links might not be errors, since we intentionally don't reset a deleted
page's left/right links when deleting it.)

Could we see the whole special-space dump for each of the pages you're
worried about?

One thought that occurs to me is that POWER is a weak-memory-ordering
architecture, so that it's a tenable idea that this has something to do
with changing page links while not holding sufficient lock on the page.
I don't see btree doing that anywhere, but ...

BTW, how long has this installation been around, and when did you start
seeing funny behavior?  Can you say with reasonable confidence that the
bug was *not* present in any older PG versions?

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] COPY FREEZE has no warning

2013-01-25 Thread Michael Paquier

On Sat, Jan 26, 2013 at 2:42 AM, Robert Haas  wrote:

> On Fri, Jan 25, 2013 at 11:59 AM, Tom Lane  wrote:
> > Bruce Momjian  writes:
> >> On Fri, Jan 25, 2013 at 02:48:37AM +0100, Andres Freund wrote:
> >>> FWIW, and I won't annoy anyone further after this email, now that its
> >>> deterministic, I still think that this should be an ERROR not a
> WARNING.
> >
> >> As the FREEZE is just an optimization, I thought NOTICE, vs WARNING or
> >> ERROR was fine.  If others want this changed, please reply.
> >
> > The previous argument about it was "if you bothered to specify FREEZE,
> > you probably really want/need that behavior".  So I can definitely see
> > Andres' point.  Perhaps WARNING would be a suitable compromise?
>
> I'll vote for ERROR.  I don't see why this sound be a best-effort thing.
>
+ 1. I was surprised to see COPY FREEZE failing silently when testing the
feature. An ERROR would be suited.
-- 
Michael Paquier
http://michael.otacoo.com

Re: [HACKERS] LATERAL, UNNEST and spec compliance

2013-01-25 Thread Tom Lane

David Fetter  writes:
> Please find attached a patch which implements approach 3.  The vast
> majority of it is changes to the regression tests.  The removed
> regression tests in join.{sql,out} are no longer errors, although some
> of them are pretty standard DoS attacks, hence they're all removed.

Here's a less quick-hack-y approach to that.

regards, tom lane

diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index bcee9468240e8a10b8e491a8f1ab8a1e2c5d9ede..caa9f1b3389e5ce57e2e50d13011e41c0ed3d11b 100644
*** a/doc/src/sgml/queries.sgml
--- b/doc/src/sgml/queries.sgml
*** SELECT *
*** 717,730 
  
  
  
!  Subqueries and table functions appearing in FROM can be
   preceded by the key word LATERAL.  This allows them to
   reference columns provided by preceding FROM items.
!  (Without LATERAL, each FROM item is
   evaluated independently and so cannot cross-reference any other
   FROM item.)
   A LATERAL item can appear at top level in the
!  FROM list, or within a JOIN tree; in the latter
   case it can also refer to any items that are on the left-hand side of a
   JOIN that it is on the right-hand side of.
  
--- 717,740 
  
  
  
!  Subqueries appearing in FROM can be
   preceded by the key word LATERAL.  This allows them to
   reference columns provided by preceding FROM items.
!  (Without LATERAL, each subquery is
   evaluated independently and so cannot cross-reference any other
   FROM item.)
+ 
+ 
+ 
+  Table functions appearing in FROM can also be
+  preceded by the key word LATERAL, but for functions the
+  key word is optional; the function's arguments can contain references
+  to columns provided by preceding FROM items in any case.
+ 
+ 
+ 
   A LATERAL item can appear at top level in the
!  FROM list, or within a JOIN tree.  In the latter
   case it can also refer to any items that are on the left-hand side of a
   JOIN that it is on the right-hand side of.
  
*** FROM polygons p1 CROSS JOIN LATERAL vert
*** 770,776 
   polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
  WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
  
!  or in several other equivalent formulations.
  
  
  
--- 780,788 
   polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
  WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
  
!  or in several other equivalent formulations.  (As already mentioned,
!  the LATERAL key word is unnecessary in this example, but
!  we use it for clarity.)
  
  
  
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 26d511fad8c5b8d02bda618006ce2606036db7c7..0f9d52753d832fa458aca563fa2bfcf558120818 100644
*** a/doc/src/sgml/ref/select.sgml
--- b/doc/src/sgml/ref/select.sgml
*** TABLE [ ONLY ] 
-- 
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] Question regarding Sync message and unnamed portal

2013-01-25 Thread Tatsuo Ishii

> On Fri, Jan 25, 2013 at 03:24:27PM -0500, Robert Haas wrote:
>> On Fri, Jan 25, 2013 at 2:33 PM, Bruce Momjian  wrote:
>> > On Fri, Jan 25, 2013 at 02:02:39PM -0500, Robert Haas wrote:
>> >> On Fri, Jan 25, 2013 at 1:28 PM, Bruce Momjian  wrote:
>> >> > On Mon, Oct  1, 2012 at 02:04:00PM +0900, Tatsuo Ishii wrote:
>> >> >> > Tatsuo Ishii  writes:
>> >> >> >> From the manual:
>> >> >> >> "An unnamed portal is destroyed at the end of the transaction"
>> >> >> >
>> >> >> > Actually, all portals are destroyed at end of transaction (unless
>> >> >> > they're from holdable cursors).  Named or not doesn't enter into it.
>> >> >>
>> >> >> We need to fix the document then.
>> >> >
>> >> > I looked into this.  The text reads:
>> >> >
>> >> > If successfully created, a named prepared-statement object 
>> >> > lasts till
>> >> > the end of the current session, unless explicitly destroyed.  
>> >> > An unnamed
>> >> > prepared statement lasts only until the next Parse statement 
>> >> > specifying
>> >> > the unnamed statement as destination is issued.
>> >> >
>> >> > While the first statement does say "named", the next sentence says
>> >> > "unnamed", so I am not sure we can make this any clearer.
>> >>
>> >> I'm not sure what this has to do with the previous topic.  Aren't a
>> >> prepared statement and a portal two different things?
>> >
>> > Oops, thanks.  Here is the right paragraph, same issue:
>> >
>> > If successfully created, a named portal object lasts till the end of 
>> > the
>> > current transaction, unless explicitly destroyed.  An unnamed portal is
>> > destroyed at the end of the transaction, or as soon as the next Bind
>> > statement specifying the unnamed portal as destination is issued.  
>> > (Note
>> 
>> OK.  Well, that seems clear enough.  I'm not sure what it has to do
>> with the original complaint, though, because I don't quite understand
>> the original complaint, which seems to involve not only when portals
>> are destroyed but also what effect Sync messages have.
> 
> Yes, I am confused too.  Unless someone replies, we can consider this
> closed.

Sorry for confusion.

I knew an unamed portal only lasts until current transaction ends.  I
was confused in the case when no explicit transaction is used.

  At completion of each series of extended-query messages, the
  frontend should issue a Sync message.

This is not actually true because Sync is not actually mandatory as
Tom pointed out before. We could use a Flush message instead but it's
another story. And next sentence says:

 This parameterless message causes the backend to close the current
  transaction if it's not inside a BEGIN/COMMIT transaction block
  ("close" meaning to commit if no error, or roll back if error).

I did not understand this at first because if we are not inside a
BEGIN/COMMIT transaction block, how does Sync close it? In my
understanding each extended query message(parse/bind/execute) starts
an internal transaction and does not close it until Sync issued(and
Sync is mandatory according to the manual). So if we are not in an
explicit transaction we cannot reuse unnamed portal because Sync
closes the transaction, which in turn destroys the unnamed portal.
This gave me a miss understanding that unnamed portal is destroyed
even transaction is not explicitly closed.

It would be nice if something like "unnamed portal will be destroyed
by a Sync message if you are in an explicit transaction" is in our
manual.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

-- 
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] SYSV shared memory vs mmap performance

2013-01-25 Thread Robert Haas

On Fri, Jan 25, 2013 at 9:38 AM, Francois Tigeot  wrote:
>> On Fri, Jan 25, 2013 at 8:40 AM, Bruce Momjian 
>> wrote:
>>>
>>> Just a reminder we might have *BSD performance issues with our use
>>> of Posix shared memory in Postgres 9.3.  I am attaching the PDF the
>>> user posted.
>>
>> This is a good point.  The question which I believe I asked before
>> and haven't gotten an answer to is whether there's some way to get
>> the benefit of shm_use_phys with an anonymous mapping.
>
> There is. Postgres 9.3+mmap performance on DragonFly is now much better than
> these old benchmark results show.
>
> After the initial disappointing result, I went on a benchmarking/tuning
> binge with our Dear Leader Matt Dillon. Taking advantage of some previous
> cpu topology work from Mihai Carabas, he heavily improved most performance
> shortcomings we found in the DragonFly kernel.
>
> There were a few mail about this changes on the DragonFly mailing-lists and
> Justin Sherill wrote some interesting articles on his blog.
>
> Some links with more details about improvements and final results:
> http://www.shiningsilence.com/dbsdlog/2012/09/19/10403.html
> http://www.shiningsilence.com/dbsdlog/2012/10/11/10544.html
> http://www.dragonflybsd.org/performance/

Well, that looks pretty cool.  Is there anything we can sensibly do to
recover the lost performance on FreeBSD and NetBSD?

>> It seems to me to be slightly insane to impose draconian shared
>> memory limits out of the box and then complain when people switch to
>> some other type of shared memory to get around them.  I realize that
>> Dragonfly may not be doing that (because I think they may have
>> raised the default shared-memory limits), but I believe some of the
>> more mainstream BSDs are.
>
> The original SYSV limits looked like something straight from the 1980s;
> we're now autotuning them on DragonFly.

Awesome!

> FreeBSD and NetBSD still needed manual tuning last time I had a look.

Bummer.  :-(

-- 
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] COPY FREEZE has no warning

2013-01-25 Thread Tom Lane

Bruce Momjian  writes:
> OK, updated patch attached that throws an error with a more specific
> message.

>*
>* As noted above rd_newRelfilenodeSubid is not set in all cases
>* where we can apply the optimization, so in those rare cases
> !  * where we cannot honor the request.
>*/

This sentence not complete.  I kind of think the entire para visible
above could be removed, anyway.

> ! ereport(ERROR, (errmsg("cannot perform FREEZE 
> operation due to invalid table or transaction state")));

I don't find this terribly specific.  It would at least be useful to
have two messages distinguishing whether the cause was invalid table
state (rd_createSubid and rd_newRelfilenodeSubid not set) or invalid
transaction state (the snapshot and portal tests).  The former might
usefully be phrased as "because the table was not created or truncated
in the current transaction" and the latter as "because other actions are
in progress within the current transaction".

I'd also suggest "cannot perform COPY FREEZE because " rather
than using the unnecessarily vague "operation".

Also, this is missing an errcode, which means it will report itself as
an internal error, which it ain't.  It's also randomly unlike the
standard layout for ereport calls.
ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE would do for the table case,
not sure about the other.

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] Potential autovacuum optimization: new tables

2013-01-25 Thread Bruce Momjian

On Mon, Oct 15, 2012 at 12:06:27AM +0100, Greg Stark wrote:
> On Sat, Oct 13, 2012 at 3:13 AM, Stephen Frost  wrote:
> > Josh's concern is about autovacuum causing lots of stats churn, which is
> > understandable, we don't want it constantly rescanning a table
> 
> I don't think rescanning the table is a big concern. autovacuum will
> only scan as often as it feels like in the first place and these are
> by definition small tables anyways.
> 
> Josh's stated concern was about the churn in the stats table. That
> could cause extra vacuums on the stats table which could be a fairly
> substantial table. Hopefully HOT updates and the visibility bitmap
> would protect against that being too bad though.

Added to TODO:

Improve autovacuum tuning

-- 
  Bruce Momjian  http://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] Doc patch, normalize search_path in index

2013-01-25 Thread Peter Eisentraut

On 1/25/13 1:59 PM, Bruce Momjian wrote:
> On Fri, Jan 25, 2013 at 01:46:46PM -0500, Peter Eisentraut wrote:
>> This matter was already closed:
>> https://commitfest.postgresql.org/action/patch_view?id=949
>>
>> It looks like your patch reverts part of that.
> 
> Uh, I am confused because the patch at:
> 
>   https://commitfest.postgresql.org/action/patch_view?id=950
>   http://www.postgresql.org/message-id/1352874080.4647.0@mofo
> 
> shows "configuration parameter" being moved to , though this
> commit:
> 
>   
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=a301eb99c9537186f7dd46ba418e84d755227a94

The discussion yielded a patch that is different from the commit fest entry.

We index all GUC settings as

something configuration parameter

which the commit a301eb99c9537186f7dd46ba418e84d755227a94 also made the case 
for search_path.

Your two commits changed that again.

> shows it not as secondary.  Would you please suggest a patch or patch
> it?  Thanks.

I think both of your commits should be reverted.



-- 
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] has_language_privilege returns incorrect answer for non-superuser

2013-01-25 Thread Bruce Momjian

On Fri, Jan 25, 2013 at 04:55:48PM -0500, Peter Eisentraut wrote:
> On 1/24/13 6:25 PM, Bruce Momjian wrote:
> > On Thu, Aug 30, 2012 at 07:59:02PM -0700, Joe Conway wrote:
> >> On 08/30/2012 07:23 PM, Bruce Momjian wrote:
> >>> On Thu, Jul 12, 2012 at 06:01:00PM -0700, Joe Conway wrote:
>  I'll take a look at the latter option sometime in the next few weeks and
>  submit for the next commitfest.
> >>>
> >>> Any news on this?
> >>
> >> Not yet -- OBE. I'll try to set aside some time on the long weekend.
> > 
> > Any news on this?
> 
> https://commitfest.postgresql.org/action/patch_view?id=1048

Joe emailed me privately to say he would work on it early next week,
"promise".

-- 
  Bruce Momjian  http://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] Improving the performance of psql tab completion

2013-01-25 Thread Bruce Momjian

On Fri, Oct 12, 2012 at 04:42:46PM -0400, Stephen Frost wrote:
> * Bruce Momjian (br...@momjian.us) wrote:
> > On Fri, Oct 12, 2012 at 03:57:15PM -0400, Stephen Frost wrote:
> > > * Tom Lane (t...@sss.pgh.pa.us) wrote:
> > > > There was also some discussion of fixing the name-check to be indexable,
> > > > which the substring hack isn't.  That would take a bit of work though.
> > > 
> > > Right.  I still want to do it, but it still needs a few more "to-its",
> > > as it were.
> > 
> > TODO item?
> 
> Yes, but it should link to the previous thread which included info about
> what the right approach would be..
> 
> eg: http://archives.postgresql.org/pgsql-hackers/2012-08/msg00654.php
> 
> Or the top of that thread.

Added to TODO:

Improve speed of tab completion by using LIKE

   
http://www.postgresql.org/message-id/20121012060345.ga29...@toroid.org 

-- 
  Bruce Momjian  http://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] has_language_privilege returns incorrect answer for non-superuser

2013-01-25 Thread Peter Eisentraut

On 1/24/13 6:25 PM, Bruce Momjian wrote:
> On Thu, Aug 30, 2012 at 07:59:02PM -0700, Joe Conway wrote:
>> On 08/30/2012 07:23 PM, Bruce Momjian wrote:
>>> On Thu, Jul 12, 2012 at 06:01:00PM -0700, Joe Conway wrote:
 I'll take a look at the latter option sometime in the next few weeks and
 submit for the next commitfest.
>>>
>>> Any news on this?
>>
>> Not yet -- OBE. I'll try to set aside some time on the long weekend.
> 
> Any news on this?

https://commitfest.postgresql.org/action/patch_view?id=1048


-- 
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] explain tup_fetched/returned in monitoring-stats

2013-01-25 Thread Bruce Momjian

On Sat, Oct 20, 2012 at 12:27:16PM +0100, Simon Riggs wrote:
> On 20 October 2012 07:43, Abhijit Menon-Sen  wrote:
> > At 2012-10-15 10:28:17 -0400, robertmh...@gmail.com wrote:
> >>
> >> > Is there any concise description that applies? […]
> >>
> >> I don't think there is.  I think we need to replace those counters
> >> with something better.  The status quo is quite bizarre.
> >
> > Fair enough. Do you have any ideas?
> >
> > I see two possibilities: first, they could become the tuple analogue of
> > blks_read and blks_hit, i.e. tuples fetched from disk, and tuples found
> > in memory. (I don't know if there's a simple way to count that, and I'm
> > not sure it would be very useful; we have blks_{read,hit} after all.)
> >
> > Second, it could do what I thought it did, which is count tuples fetched
> > by sequential and index scans respectively. I'm not sure how useful the
> > values would be, but at least it's information you can't get elsewhere.
> 
> We already have the second one on pg_stat_all_tables.
> 
> A third possibility exists, which is the one Tom described above.
> 
> Collecting information at pg_stat_database level isn't interesting
> anyway (to me) for information that can be collected at table level.

Added to TODO:

Restructure pg_stat_database columns tup_returned and tup_fetched to
return meaningful values

-- 
  Bruce Momjian  http://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] Why will hashed SubPlan not use multiple batches

2013-01-25 Thread Tom Lane

Jeff Janes  writes:
> A hashed SubPlan will not be used if it would need more than one
> batch.  Is there a fundamental reason for that, or just that no one
> got around to adding it?

It can't, really.  Batching a hash join requires freedom to reorder the
rows on both sides of the join.  A SubPlan, by definition, must deliver
the correct answer for the current outer row on-demand.

The only real fix for your problem would be to teach the regular hash
join machinery how to handle NOT IN semantics accurately, so that we
could transform this query into a regular kind of join instead of a
seqscan with a SubPlan wart attached to it.  In the past it hasn't
really seemed worth it, since 99% of the time, once you question
somebody about why they're insisting on NOT IN rather than NOT EXISTS,
you find out that they didn't really want NOT IN semantics after all.

We could also consider adding logic to notice NOT NULL constraints on
the inner select's outputs, which would allow the planner to prove that
the query can be transformed to a regular antijoin.  That only helps
people who've put on such constraints though ...

> I have no control over the real query itself

Sigh.  Another badly-written ORM, I suppose?

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] WAL_DEBUG logs spurious data

2013-01-25 Thread Bruce Momjian

On Thu, Oct 11, 2012 at 07:06:15PM -0400, Tom Lane wrote:
> Markus Wanner  writes:
> > On 10/11/2012 03:11 PM, Tom Lane wrote:
> >> The original design intention was that rm_desc should not attempt to
> >> print any such data, but obviously some folks didn't get the word.
> 
> > FWIW: in case the source code contains comments explaining that
> > intention, I certainly missed them so far.
> 
> Yeah, if we decide to stick with the limitation, some documentation
> would be called for.  I remember having run into this and having removed
> functionality from an rm_desc function rather than question the premise.
> But maybe the extra functionality is worth the cycles.

I assume there is no TODO item or patch here.

-- 
  Bruce Momjian  http://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_retainxlog for inclusion in 9.3?

2013-01-25 Thread Peter Eisentraut

On 1/25/13 12:24 PM, Andres Freund wrote:
> On 2013-01-26 02:21:00 +0900, Fujii Masao wrote:
>> The process which deletes the old WAL files is the checkpointer. The
>> checkpointer can access to the shared memory and know the location
>> of the WAL record which has been already replicated to the standby.
>> ISTM it's not difficult to implement the logic which pg_retainxlog provides
>> into the checkpointer. How about just changing the checkpointer so
>> that it checks whether the WAL file to delete has been already not
>> only archived but also replicated if GUC flag is enabled?

That makes sense.

> The problem with that is that to implement it robustly we would need
> persistent state about the replicas.

Well, pg_retainxlog kind of handwaves around that.  If you use it in the
default mode, it assumes that the pg_receivexlog entries in
pg_stat_replication are that state.  And then it says, if you use other
kinds of clients, you need to keep track of that state yourself.  But
that seems to assume that pg_receivexlog never disconnects (thus losing
its entries from pg_stat_replication).  (pg_receivexlog is designed to
automatically reconnect on connection loss, so this possibility was
obviously thought about.)

So it seems to me this just doesn't work (this way).

-- 
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] No, pg_size_pretty(numeric) was not such a hot idea

2013-01-25 Thread Bruce Momjian

On Wed, Oct 10, 2012 at 11:49:50AM -0700, Josh Berkus wrote:
> 
> >> Assuming that's how 9.2 ships, we might as well wait to see if there
> >> are any real complaints from the field before we decide whether any
> >> changing is needed.
> 
> So, here's a complaint: 9.2 is breaking our code for checking table sizes:
> 
> postgres=# select pg_size_pretty(100);
> ERROR:  function pg_size_pretty(integer) is not unique at character 8
> HINT:  Could not choose a best candidate function. You might need to add
> explicit type casts.
> STATEMENT:  select pg_size_pretty(100);
> ERROR:  function pg_size_pretty(integer) is not unique
> LINE 1: select pg_size_pretty(100);
>^
> HINT:  Could not choose a best candidate function. You might need to add
> explicit type casts.
> 
> Obviously, we can work around it though.  Let's see if anyone else
> complains ...

Where are we on this?  I still see this behavior:

test=> SELECT pg_size_pretty(100);
ERROR:  function pg_size_pretty(integer) is not unique
LINE 1: SELECT pg_size_pretty(100);
   ^
HINT:  Could not choose a best candidate function. You might need to 
add explicit type casts.

\df shows:

test=> \df pg_size_pretty
   List of functions
   Schema   |  Name  | Result data type | Argument data types | 
 Type

++--+-+
 pg_catalog | pg_size_pretty | text | bigint  | 
normal
 pg_catalog | pg_size_pretty | text | numeric | 
normal
(2 rows)

-- 
  Bruce Momjian  http://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] Add the FET timetone abbreviation

2013-01-25 Thread Bruce Momjian


FYI, the FET timezone abbeviation was added in this commit:


http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=7127293a5d9f655ce3ec7e009f18bac8d3d0bc1c

---

On Sat, Oct  6, 2012 at 11:15:49AM +0200, Marc Balmer wrote:
> FET stands for Further-eastern European Time and is the official time in
> e.g. Belarus (Europe/Minsk).
> 
> The attched patch adds FET to the list of Default timezone abbreviations.
> 
> - Marc Balmer
> 

> diff --git a/src/timezone/tznames/Default b/src/timezone/tznames/Default
> index 1369f47..7223ce5 100644
> --- a/src/timezone/tznames/Default
> +++ b/src/timezone/tznames/Default
> @@ -615,6 +615,8 @@ EETDST  10800 D  # East-Egypt Summertime
>   # (Europe/Uzhgorod)
>   # (Europe/Vilnius)
>   # (Europe/Zaporozhye)
> +FET 10800# Further-eastern European Time
> + # (Europe/Minsk)
>  MEST 7200 D  # Middle Europe Summer Time (not in zic)
>  MET  3600# Middle Europe Time (not in zic)
>  METDST   7200 D  # Middle Europe Summer Time (not in zic)
> 

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


-- 
  Bruce Momjian  http://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] replace plugins directory with GUC

2013-01-25 Thread Kohei KaiGai

2013/1/15 Peter Eisentraut :
> On Tue, 2012-10-09 at 20:45 -0400, Peter Eisentraut wrote:
>> About that plugins directory ($libdir/plugins) ... I don't think we
>> ever
>> really got that to work sensibly.  I don't remember the original
>> design
>> discussion, but I have seen a number of explanations offered over the
>> years.  It's not clear who decides what to put in there (plugin
>> author,
>> packager, DBA?), how to put it there (move it, copy it, symlink it? --
>> no support in pgxs), and based on what criteria.
>>
>> It would seem to be much more in the spirit of things to simply list
>> the
>> allowed plugins in a GUC variable, like
>>
>> some_clever_name_here = $libdir/this, $libdir/that
>
> Here is a patch, with some_clever_name = user_loadable_libraries.
>
> There are obviously some conflict/transition issues with using
> user_loadable_libraries vs the plugins directory.  I have tried to
> explain the mechanisms in the documentation, but there are other choices
> possible in some situations.
>
Do we still need to continue hardwired "$libdir/plugins/" ?
If user_loadable_libraries allows to specify directories, not only libraries
themselves, and its default value is "$libdir/plugins/", it seems to me
this enhancement can offer more flexibility without losing backward
compatibility.

On the other hand, I'd like to see your opinion about fine-grained
superuser capabilities for module loading, that we have discussed
in the thread of untrusted language privilege.
It might be a situation where a capability to load module make sense.

Thanks,
-- 
KaiGai Kohei 


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

[HACKERS] Why will hashed SubPlan not use multiple batches

2013-01-25 Thread Jeff Janes

A hashed SubPlan will not be used if it would need more than one
batch.  Is there a fundamental reason for that, or just that no one
got around to adding it?

A small decrease in work_mem leads to a 38000 fold change in estimated
query execution (and that might be accurate, as the actual change in
execution is too large to measure)

I have no control over the real query itself (otherwise changing it
from NOT IN to NOT EXISTS would fix it, because that hash plan will
use multiple batches).

I have temporarily fixed it by increasing work_mem, but it would be
better if the planner did the best with the resources it had.

This example works with default settings on "PostgreSQL 9.2.2 on
x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20120305 (Red
Hat 4.4.6-4), 64-bit".   Same behavior on 9.1.7 and 9.3dev.

Is this a Todo item?

test case below.

create table foo as select (random()*1)::integer as bar from
generate_series(1,10);
create table foo2 as select (random()*1)::integer as bar2 from
generate_series(1,10);
analyze;


set work_mem TO 3300;
explain select foo.bar from foo where bar not in (select bar2 from foo2);
 QUERY PLAN

 Seq Scan on foo  (cost=1693.00..3386.00 rows=5 width=4)
   Filter: (NOT (hashed SubPlan 1))
   SubPlan 1
 ->  Seq Scan on foo2  (cost=0.00..1443.00 rows=10 width=4)
(4 rows)


set work_mem TO 3100;
explain select foo.bar from foo where bar not in (select bar2 from foo2);
QUERY PLAN
--
 Seq Scan on foo  (cost=0.00..129201693.00 rows=5 width=4)
   Filter: (NOT (SubPlan 1))
   SubPlan 1
 ->  Materialize  (cost=0.00..2334.00 rows=10 width=4)
   ->  Seq Scan on foo2  (cost=0.00..1443.00 rows=10 width=4)


Cheers,

Jeff


-- 
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] setting per-database/role parameters checks them against wrong context

2013-01-25 Thread Bruce Momjian

On Fri, Jan 25, 2013 at 03:35:59PM -0500, Tom Lane wrote:
> Bruce Momjian  writes:
> >> diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
> >> index 6b202e0..0677059 100644
> >> --- a/src/backend/utils/misc/guc.c
> >> +++ b/src/backend/utils/misc/guc.c
> >> @@ -5150,7 +5150,7 @@ set_config_option(const char *name, const char 
> >> *value,
> >> elevel = IsUnderPostmaster ? DEBUG3 : LOG;
> >> }
> >> else if (source == PGC_S_DATABASE || source == PGC_S_USER ||
> >> -   source == PGC_S_DATABASE_USER)
> >> +   source == PGC_S_DATABASE_USER || source == 
> >> PG_S_SESSION)
> >> elevel = WARNING;
> >> else
> >> elevel = ERROR;
> 
> > Is there any opinion on whether we need this patch?  It basically allows
> > SET from a session to issue a warning rather than an error.
> 
> Surely this is a completely horrid idea.  It doesn't "allow" SET to
> throw a warning, it changes all interactive-SET cases from ERROR to
> WARNING.  That's a whole lot of collateral damage to fix a very narrow
> case that's not even there anymore.

Agreed.

-- 
  Bruce Momjian  http://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] setting per-database/role parameters checks them against wrong context

2013-01-25 Thread Tom Lane

Bruce Momjian  writes:
>> diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
>> index 6b202e0..0677059 100644
>> --- a/src/backend/utils/misc/guc.c
>> +++ b/src/backend/utils/misc/guc.c
>> @@ -5150,7 +5150,7 @@ set_config_option(const char *name, const char *value,
>> elevel = IsUnderPostmaster ? DEBUG3 : LOG;
>> }
>> else if (source == PGC_S_DATABASE || source == PGC_S_USER ||
>> - source == PGC_S_DATABASE_USER)
>> + source == PGC_S_DATABASE_USER || source == 
>> PG_S_SESSION)
>> elevel = WARNING;
>> else
>> elevel = ERROR;

> Is there any opinion on whether we need this patch?  It basically allows
> SET from a session to issue a warning rather than an error.

Surely this is a completely horrid idea.  It doesn't "allow" SET to
throw a warning, it changes all interactive-SET cases from ERROR to
WARNING.  That's a whole lot of collateral damage to fix a very narrow
case that's not even there anymore.

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] Question regarding Sync message and unnamed portal

2013-01-25 Thread Bruce Momjian

On Fri, Jan 25, 2013 at 03:29:17PM -0500, Tom Lane wrote:
> Bruce Momjian  writes:
> > Oops, thanks.  Here is the right paragraph, same issue:
> 
> > If successfully created, a named portal object lasts till the end of the
> > current transaction, unless explicitly destroyed.  An unnamed portal is
> > destroyed at the end of the transaction, or as soon as the next Bind
> > statement specifying the unnamed portal as destination is issued.  (Note
> 
> What is the issue exactly?  Seems clear and correct to me ... but then,
> I think I wrote this text, a long time ago.

Tatsuo's complain was that on sentence said "named portal", while all
portals are closed on transaction end.  However, "unnamed portal" is
named in the next sentence, so it seems OK.

-- 
  Bruce Momjian  http://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] Enabling Checksums

2013-01-25 Thread Robert Haas

On Thu, Jan 10, 2013 at 1:06 AM, Jeff Davis  wrote:
> On Tue, 2012-12-04 at 01:03 -0800, Jeff Davis wrote:
>> For now, I rebased the patches against master, and did some very minor
>> cleanup.
>
> I think there is a problem here when setting PD_ALL_VISIBLE. I thought I
> had analyzed that before, but upon review, it doesn't look right.
> Setting PD_ALL_VISIBLE needs to be associated with a WAL action somehow,
> and a bumping of the LSN, otherwise there is a torn page hazard.
>
> The solution doesn't seem particularly difficult, but there are a few
> strange aspects and I'm not sure exactly which path I should take.
>
> First of all, the relationship between MarkBufferDirty and
> SetBufferCommitInfoNeedsSave is a little confusing. The comment over
> MarkBufferDirty is confusing because it says that the caller must have
> an exclusive lock, or else bad data could be written. But that doesn't
> have to do with marking the buffer dirty, that has to do with the data
> page change you make while you are marking it dirty -- if it's a single
> bit change, then there is no risk that I can see.
>
> In the current code, the only real code difference between the two is
> that SetBufferCommitInfoNeedsSave might fail to mark the buffer dirty if
> there is a race. So, in the current code, we could actually combine the
> two by passing a "force" flag (if true, behaves like MarkBufferDirty, if
> false, behaves like SetBufferCommitInfoNeedsSave).
>
> The checksums patch also introduces another behavior into
> SetBufferCommitInfoNeedsSave, which is to write an XLOG_HINT WAL record
> if checksums are enabled (to avoid torn page hazards). That's only
> necessary for changes where the caller does not write WAL itself and
> doesn't bump the LSN of the data page. (There's a reason the caller
> can't easily write the XLOG_HINT WAL itself.) So, we could introduce
> another flag "needsWAL" that would control whether we write the
> XLOG_HINT WAL or not (only applies with checksums on, of course).

I thought Simon had the idea, at some stage, of writing a WAL record
to cover hint-bit changes only at the time we *write* the buffer and
only if no FPI had already been emitted that checkpoint cycle.  I'm
not sure whether that approach was sound, but if so it seems more
efficient than this approach.

-- 
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] Question regarding Sync message and unnamed portal

2013-01-25 Thread Tom Lane

Bruce Momjian  writes:
> Oops, thanks.  Here is the right paragraph, same issue:

> If successfully created, a named portal object lasts till the end of the
> current transaction, unless explicitly destroyed.  An unnamed portal is
> destroyed at the end of the transaction, or as soon as the next Bind
> statement specifying the unnamed portal as destination is issued.  (Note

What is the issue exactly?  Seems clear and correct to me ... but then,
I think I wrote this text, a long time ago.

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] Question regarding Sync message and unnamed portal

2013-01-25 Thread Bruce Momjian

On Fri, Jan 25, 2013 at 03:24:27PM -0500, Robert Haas wrote:
> On Fri, Jan 25, 2013 at 2:33 PM, Bruce Momjian  wrote:
> > On Fri, Jan 25, 2013 at 02:02:39PM -0500, Robert Haas wrote:
> >> On Fri, Jan 25, 2013 at 1:28 PM, Bruce Momjian  wrote:
> >> > On Mon, Oct  1, 2012 at 02:04:00PM +0900, Tatsuo Ishii wrote:
> >> >> > Tatsuo Ishii  writes:
> >> >> >> From the manual:
> >> >> >> "An unnamed portal is destroyed at the end of the transaction"
> >> >> >
> >> >> > Actually, all portals are destroyed at end of transaction (unless
> >> >> > they're from holdable cursors).  Named or not doesn't enter into it.
> >> >>
> >> >> We need to fix the document then.
> >> >
> >> > I looked into this.  The text reads:
> >> >
> >> > If successfully created, a named prepared-statement object lasts 
> >> > till
> >> > the end of the current session, unless explicitly destroyed.  An 
> >> > unnamed
> >> > prepared statement lasts only until the next Parse statement 
> >> > specifying
> >> > the unnamed statement as destination is issued.
> >> >
> >> > While the first statement does say "named", the next sentence says
> >> > "unnamed", so I am not sure we can make this any clearer.
> >>
> >> I'm not sure what this has to do with the previous topic.  Aren't a
> >> prepared statement and a portal two different things?
> >
> > Oops, thanks.  Here is the right paragraph, same issue:
> >
> > If successfully created, a named portal object lasts till the end of the
> > current transaction, unless explicitly destroyed.  An unnamed portal is
> > destroyed at the end of the transaction, or as soon as the next Bind
> > statement specifying the unnamed portal as destination is issued.  (Note
> 
> OK.  Well, that seems clear enough.  I'm not sure what it has to do
> with the original complaint, though, because I don't quite understand
> the original complaint, which seems to involve not only when portals
> are destroyed but also what effect Sync messages have.

Yes, I am confused too.  Unless someone replies, we can consider this
closed.

-- 
  Bruce Momjian  http://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] LATERAL, UNNEST and spec compliance

2013-01-25 Thread Tom Lane

Stephen Frost  writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> SELECT ... FROM a WHERE a.x IN (SELECT ... FROM b, srf(y) WHERE ...)

> Actually, this appears to fail already, at least in 9.2.2:

> => select * from (values (1)) v(a) where v.a in (select x from (values (2)) 
> v2(a), 
> -> generate_series(1,a) x);
> ERROR:  function expression in FROM cannot refer to other relations of same 
> query level
> LINE 2: generate_series(1,a) x);
>   ^

Huh ... you're right, I'd forgotten about that.  That's an ancient bug
that got fixed in passing in the LATERAL work.  So, as long as we're not
going to fix that bug in the back branches (which would be difficult
anyway IIRC), we don't have a compatibility problem ...

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] Question regarding Sync message and unnamed portal

2013-01-25 Thread Robert Haas

On Fri, Jan 25, 2013 at 2:33 PM, Bruce Momjian  wrote:
> On Fri, Jan 25, 2013 at 02:02:39PM -0500, Robert Haas wrote:
>> On Fri, Jan 25, 2013 at 1:28 PM, Bruce Momjian  wrote:
>> > On Mon, Oct  1, 2012 at 02:04:00PM +0900, Tatsuo Ishii wrote:
>> >> > Tatsuo Ishii  writes:
>> >> >> From the manual:
>> >> >> "An unnamed portal is destroyed at the end of the transaction"
>> >> >
>> >> > Actually, all portals are destroyed at end of transaction (unless
>> >> > they're from holdable cursors).  Named or not doesn't enter into it.
>> >>
>> >> We need to fix the document then.
>> >
>> > I looked into this.  The text reads:
>> >
>> > If successfully created, a named prepared-statement object lasts 
>> > till
>> > the end of the current session, unless explicitly destroyed.  An 
>> > unnamed
>> > prepared statement lasts only until the next Parse statement 
>> > specifying
>> > the unnamed statement as destination is issued.
>> >
>> > While the first statement does say "named", the next sentence says
>> > "unnamed", so I am not sure we can make this any clearer.
>>
>> I'm not sure what this has to do with the previous topic.  Aren't a
>> prepared statement and a portal two different things?
>
> Oops, thanks.  Here is the right paragraph, same issue:
>
> If successfully created, a named portal object lasts till the end of the
> current transaction, unless explicitly destroyed.  An unnamed portal is
> destroyed at the end of the transaction, or as soon as the next Bind
> statement specifying the unnamed portal as destination is issued.  (Note

OK.  Well, that seems clear enough.  I'm not sure what it has to do
with the original complaint, though, because I don't quite understand
the original complaint, which seems to involve not only when portals
are destroyed but also what effect Sync messages 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] COPY FREEZE has no warning

2013-01-25 Thread Bruce Momjian

On Fri, Jan 25, 2013 at 11:55:12AM -0800, Jeff Janes wrote:
> On Fri, Jan 25, 2013 at 9:42 AM, Robert Haas  wrote:
> > On Fri, Jan 25, 2013 at 11:59 AM, Tom Lane  wrote:
> >> Bruce Momjian  writes:
> >>> On Fri, Jan 25, 2013 at 02:48:37AM +0100, Andres Freund wrote:
>  FWIW, and I won't annoy anyone further after this email, now that its
>  deterministic, I still think that this should be an ERROR not a WARNING.
> >>
> >>> As the FREEZE is just an optimization, I thought NOTICE, vs WARNING or
> >>> ERROR was fine.  If others want this changed, please reply.
> >>
> >> The previous argument about it was "if you bothered to specify FREEZE,
> >> you probably really want/need that behavior".  So I can definitely see
> >> Andres' point.  Perhaps WARNING would be a suitable compromise?
> >
> > I'll vote for ERROR.  I don't see why this sound be a best-effort thing.
> >
> 
> +1.  If I had no objection to my database getting stuffed to the gills
> with unfrozen tuples, I wouldn't have invoked the feature in the first
> place.
> 
> As far as can tell, this ERROR/WARNING must occur immediately, because
> once the first tuple is inserted frozen it is too late to change ones
> mind.  So the problem can be immediately fixed and retried.
> 
> Except, is there perhaps some way for the user to decide to promote
> WARNINGs to ERRORs on for a given command/transaction?

OK, updated patch attached that throws an error with a more specific
message.

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

  + It's impossible for everything to be true. +
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
new file mode 100644
index 8778e8b..ec7c311
*** a/src/backend/commands/copy.c
--- b/src/backend/commands/copy.c
*** CopyFrom(CopyState cstate)
*** 2009,2023 
  		 *
  		 * As noted above rd_newRelfilenodeSubid is not set in all cases
  		 * where we can apply the optimization, so in those rare cases
! 		 * where we cannot honour the request we do so silently.
  		 */
! 		if (cstate->freeze &&
! 			ThereAreNoPriorRegisteredSnapshots() &&
! 			ThereAreNoReadyPortals() &&
! 			(cstate->rel->rd_newRelfilenodeSubid == GetCurrentSubTransactionId() ||
! 			 cstate->rel->rd_createSubid == GetCurrentSubTransactionId()))
! 			hi_options |= HEAP_INSERT_FROZEN;
  	}
  
  	/*
  	 * We need a ResultRelInfo so we can use the regular executor's
--- 2009,2029 
  		 *
  		 * As noted above rd_newRelfilenodeSubid is not set in all cases
  		 * where we can apply the optimization, so in those rare cases
! 		 * where we cannot honor the request.
  		 */
! 		if (cstate->freeze)
! 		{
! 			if (ThereAreNoPriorRegisteredSnapshots() &&
! ThereAreNoReadyPortals() &&
! (cstate->rel->rd_newRelfilenodeSubid == GetCurrentSubTransactionId() ||
!  cstate->rel->rd_createSubid == GetCurrentSubTransactionId()))
! hi_options |= HEAP_INSERT_FROZEN;
! 			else
! ereport(ERROR, (errmsg("cannot perform FREEZE operation due to invalid table or transaction state")));
! 		}
  	}
+ 	else if (cstate->freeze)
+ 		ereport(ERROR, (errmsg("cannot perform FREEZE operation due to invalid table or transaction state")));
  
  	/*
  	 * We need a ResultRelInfo so we can use the regular executor's
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
new file mode 100644
index 78c601f..e19cc5b
*** a/src/test/regress/expected/copy2.out
--- b/src/test/regress/expected/copy2.out
*** SELECT * FROM vistest;
*** 334,355 
  COMMIT;
  TRUNCATE vistest;
  COPY vistest FROM stdin CSV FREEZE;
  BEGIN;
  INSERT INTO vistest VALUES ('z');
  SAVEPOINT s1;
  TRUNCATE vistest;
  ROLLBACK TO SAVEPOINT s1;
  COPY vistest FROM stdin CSV FREEZE;
! SELECT * FROM vistest;
!  a  
! 
!  p
!  g
!  z
!  d3
!  e
! (5 rows)
! 
  COMMIT;
  CREATE FUNCTION truncate_in_subxact() RETURNS VOID AS
  $$
--- 334,347 
  COMMIT;
  TRUNCATE vistest;
  COPY vistest FROM stdin CSV FREEZE;
+ ERROR:  cannot perform FREEZE operation due to invalid table or transaction state
  BEGIN;
  INSERT INTO vistest VALUES ('z');
  SAVEPOINT s1;
  TRUNCATE vistest;
  ROLLBACK TO SAVEPOINT s1;
  COPY vistest FROM stdin CSV FREEZE;
! ERROR:  cannot perform FREEZE operation due to invalid table or transaction state
  COMMIT;
  CREATE FUNCTION truncate_in_subxact() RETURNS VOID AS
  $$
diff --git a/src/test/regress/sql/copy2.sql b/src/test/regress/sql/copy2.sql
new file mode 100644
index 55568e6..87b847c
*** a/src/test/regress/sql/copy2.sql
--- b/src/test/regress/sql/copy2.sql
*** COPY vistest FROM stdin CSV FREEZE;
*** 242,248 
  d3
  e
  \.
- SELECT * FROM vistest;
  COMMIT;
  CREATE FUNCTION truncate_in_subxact() RETURNS VOID AS
  $$
--- 242,247 

-- 
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] Doc patch, normalize search_path in index

2013-01-25 Thread Karl O. Pinc

On 01/25/2013 12:35:49 PM, Tom Lane wrote:
> Bruce Momjian  writes:
> > I have applied a modified version of your patch that creates
> separate
> > secondary index references for search_path.
> 
> This patch seems pretty bizarre.  What is the difference between a
> "configuration parameter" and a "run-time setting"?  Why would you
> point people to two different places for those two terms?

One was the setting/querying of the parameter, the other the
purpose/impact of the setting.  Somewhere in there I'm also
thinking it's a matter of the lexical token used v.s.
the functionality manipulated.  One would have an _ in
between the words of the index entry, the other wouldn't.
The distinction would get all messed up by the browser 
when it underlined hyperlinks.

I no longer recall the point
in making the distinction, although I believe that it came
from something to do with the vocabulary already in place
involving indexing settings and what they do.  Or maybe I made it up.
In any case I don't think that the patch the Peter pushed 
when he closed the commitfest entry made any distinction
between the token and the concept.  Given that the difference
in sort order is the presence/absence of an _, and the
way hyperlinks are represented, I think this made the
the resulting index look best.

There was also a "use of search path to secure functions"
indexing wrapped in with the rest of the patch.

Hope this helps.

Karl 
Free Software:  "You don't pay back, you pay forward."
 -- Robert A. Heinlein

-- 
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] allowing privileges on untrusted languages

2013-01-25 Thread Kohei KaiGai

2013/1/20 Tom Lane :
> Robert Haas  writes:
>> Yeah.  We'd need to think a little bit about how to make this work,
>> since I think that adding a gajillion booleans to pg_authid will not
>> make anyone very happy.  But I like the idea.  GRANT
>> kill_sessions_of_other_users TO bob?  GRANT install_untrusted_pls TO
>> any_database_owner?  GRANT install_an_extension_called(hstore) TO
>> any_database_owner?  I know there are other ways of doing all of these
>> things, so don't take the specific proposals too seriously, but we
>> clearly have a need to parcel out controlled bits of the superuser
>> mojo to individual users in a nice, clean, convenient way.  Getting
>> agreement on the details is likely to be difficult, but it seems like
>> a good concept from 10,000 feet.
>
> The traditional answer to that, which not only can be done already in
> all existing releases but is infinitely more flexible than any
> hard-wired scheme we could implement, is that you create superuser-owned
> security-definer functions that can execute any specific operation you
> want to allow, and then GRANT EXECUTE on those functions to just the
> people who should have it.
>
I also agree it is a right solution to provide unprivileged users a trusted
way to handle privileged operations, as set-uid programs are widely
utilized for same purpose on operating system, however, it needs to
satisfy an assumption these trusted procedures have to be free from
bugs and vulnerabilities. In general, it is not an easy assumption.
Thus, it is the reason why fine-grained capability is preferred than
root as single-point-of-failure on security. For example, now /bin/ping
has no set-uid bit on recent Fedora system, to avoid a risk when this
program has any vulnerability.

  [kaigai@iwashi ~]$ ls -l /bin/ping
  -rwxr-xr-x. 1 root root 40880 Dec  7 15:19 /bin/ping
  [kaigai@iwashi ~]$ getcap /bin/ping
  /bin/ping = cap_net_admin,cap_net_raw+ep

I think, our situation is similar. If DBA is not 100% certain on safeness
of self-defined functions, I believe he want unprivileged users to call
security-definer functions owned by limited capable user, instead of
fully capable superuser.

I'm not positive to over-interpret this concept, like hundred of capabilities
being nightmare of maintenance. However, it is worthwhile as basis
of self-defined security-definer functions.
I'd like people to imagine a case when trusted procedures were buggy.

Thanks,
-- 
KaiGai Kohei 


-- 
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] COPY FREEZE has no warning

2013-01-25 Thread Jeff Janes

On Fri, Jan 25, 2013 at 9:42 AM, Robert Haas  wrote:
> On Fri, Jan 25, 2013 at 11:59 AM, Tom Lane  wrote:
>> Bruce Momjian  writes:
>>> On Fri, Jan 25, 2013 at 02:48:37AM +0100, Andres Freund wrote:
 FWIW, and I won't annoy anyone further after this email, now that its
 deterministic, I still think that this should be an ERROR not a WARNING.
>>
>>> As the FREEZE is just an optimization, I thought NOTICE, vs WARNING or
>>> ERROR was fine.  If others want this changed, please reply.
>>
>> The previous argument about it was "if you bothered to specify FREEZE,
>> you probably really want/need that behavior".  So I can definitely see
>> Andres' point.  Perhaps WARNING would be a suitable compromise?
>
> I'll vote for ERROR.  I don't see why this sound be a best-effort thing.
>

+1.  If I had no objection to my database getting stuffed to the gills
with unfrozen tuples, I wouldn't have invoked the feature in the first
place.

As far as can tell, this ERROR/WARNING must occur immediately, because
once the first tuple is inserted frozen it is too late to change ones
mind.  So the problem can be immediately fixed and retried.

Except, is there perhaps some way for the user to decide to promote
WARNINGs to ERRORs on for a given command/transaction?

Cheers,

Jeff


-- 
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] LATERAL, UNNEST and spec compliance

2013-01-25 Thread Stephen Frost

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> SELECT ... FROM a WHERE a.x IN (SELECT ... FROM b, srf(y) WHERE ...)

Actually, this appears to fail already, at least in 9.2.2:

=> select * from (values (1)) v(a) where v.a in (select x from (values (2)) 
v2(a), 
-> generate_series(1,a) x);
ERROR:  function expression in FROM cannot refer to other relations of same 
query level
LINE 2: generate_series(1,a) x);
  ^

Unless it's something else that you were referring to...?

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] setting per-database/role parameters checks them against wrong context

2013-01-25 Thread Bruce Momjian

On Sat, Oct  6, 2012 at 02:20:53PM -0700, Selena Deckelmann wrote:
> On Mon, Oct 1, 2012 at 2:28 PM, Selena Deckelmann  wrote:
> > On Mon, Oct 1, 2012 at 1:37 PM, Selena Deckelmann  
> > wrote:
> >> On Mon, Oct 1, 2012 at 1:00 PM, Tom Lane  wrote:
> >>> Selena Deckelmann  writes:
>  The check_temp_buffers() problem seems like a regression and blocks us
>  from upgrading to 9.2. The use case are functions that set
>  temp_buffers and occasionally are called in a series from a parent
>  session.  The work around is... a lot of work.
> >>>
> >>> Uh ... how is that a regression?  AFAIK it's been that way right along.
> >>
> >> We're running 9.0 - looks like it changed in 9.1, last revision to the
> >> relevant line was 6/2011. The group decided not to upgrade to 9.1 last
> >> year, but was going to just go directly to 9.2 in the next few weeks.
> >
> > And, I was basing the regression comment on the documentation for
> > temp_buffers: "The setting can be changed within individual sessions,
> > but only before the first use of temporary tables within the session;
> > subsequent attempts to change the value will have no effect on that
> > session." This statement has been there since at least 8.1.
> >
> > A warning, and then not failing seems more appropriate than an error,
> > given the documented behavior.
> 
> I tried out a few things, and then realized that perhaps just adding
> PGC_S_SESSION to the list of sources that are at elevel WARNING
> partially fixes this.
> 
> This doesn't fix the issue with log_statement_stats, but it makes the
> behavior of temp_buffers  the documented behavior (no longer errors
> out in a transaction), while still warning the user.

> diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
> index 6b202e0..0677059 100644
> --- a/src/backend/utils/misc/guc.c
> +++ b/src/backend/utils/misc/guc.c
> @@ -5150,7 +5150,7 @@ set_config_option(const char *name, const char *value,
>   elevel = IsUnderPostmaster ? DEBUG3 : LOG;
>   }
>   else if (source == PGC_S_DATABASE || source == PGC_S_USER ||
> -  source == PGC_S_DATABASE_USER)
> +  source == PGC_S_DATABASE_USER || source == 
> PG_S_SESSION)
>   elevel = WARNING;
>   else
>   elevel = ERROR;

Is there any opinion on whether we need this patch?  It basically allows
SET from a session to issue a warning rather than an error.  This is
certainly a much larger change than just fixing temp_buffers.  The user
complaint was that functions were setting temp_buffers and getting
errors because temp table has already been used:

The setting can be changed within individual sessions, but only
before the first use of temporary tables within the session;
subsequent attempts to change the value will have no effect on
that session.

The full thread is here:


http://www.postgresql.org/message-id/1348802984.3584.22.ca...@vanquo.pezone.net

Seems this changed in PG 9.1.

-- 
  Bruce Momjian  http://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] Question regarding Sync message and unnamed portal

2013-01-25 Thread Bruce Momjian

On Fri, Jan 25, 2013 at 02:02:39PM -0500, Robert Haas wrote:
> On Fri, Jan 25, 2013 at 1:28 PM, Bruce Momjian  wrote:
> > On Mon, Oct  1, 2012 at 02:04:00PM +0900, Tatsuo Ishii wrote:
> >> > Tatsuo Ishii  writes:
> >> >> From the manual:
> >> >> "An unnamed portal is destroyed at the end of the transaction"
> >> >
> >> > Actually, all portals are destroyed at end of transaction (unless
> >> > they're from holdable cursors).  Named or not doesn't enter into it.
> >>
> >> We need to fix the document then.
> >
> > I looked into this.  The text reads:
> >
> > If successfully created, a named prepared-statement object lasts 
> > till
> > the end of the current session, unless explicitly destroyed.  An 
> > unnamed
> > prepared statement lasts only until the next Parse statement 
> > specifying
> > the unnamed statement as destination is issued.
> >
> > While the first statement does say "named", the next sentence says
> > "unnamed", so I am not sure we can make this any clearer.
> 
> I'm not sure what this has to do with the previous topic.  Aren't a
> prepared statement and a portal two different things?

Oops, thanks.  Here is the right paragraph, same issue:

If successfully created, a named portal object lasts till the end of the
current transaction, unless explicitly destroyed.  An unnamed portal is
destroyed at the end of the transaction, or as soon as the next Bind
statement specifying the unnamed portal as destination is issued.  (Note

-- 
  Bruce Momjian  http://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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Robert Haas

On Fri, Jan 25, 2013 at 1:17 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Fri, Jan 25, 2013 at 12:35 PM, Andres Freund  
>> wrote:
>>> I don't think the first part is problematic. Which scenario do you have
>>> in mind where that would really cause adverse behaviour? autovacuum
>>> seldomly does full table vacuums on tables otherwise these days so
>>> tables get "old" in that sense pretty regularly and mostly uniform.
>
>> I'm worried about the case of a very, very frequently updated table
>> getting put ahead of a table that needs a wraparound vacuum, but only
>> just.  It doesn't sit well with me to think that the priority of that
>> goes from 0 (we don't even try to update it) to infinity (it goes
>> ahead of all tables needing to be vacuumed for dead tuples) the
>> instant we hit the vacuum_freeze_table_age.
>
> Well, really the answer to that is that we have multiple autovac
> workers, and even if the first one that comes along picks the wraparound
> job, the next one won't.

Sure, but you could easily have 10 or 20 cross the
vacuum_freeze_table_age threshold simultaneously - and you'll only be
able to process a few of those at a time, due to
autovacuum_max_workers.  Moreover, even if you don't hit the
autovacuum_max_workers limit (say it's jacked up to 100 or so), you're
still introducing a delay of up to N * autovacuum_naptime, where N is
the number of tables that cross the threshold at the same instant,
before any dead-tuple cleanup vacuums are initiated.  It's not
difficult to imagine that being bad.

> Having said that, I agree that it might be better to express the
> sort priority as some sort of continuous function of multiple figures of
> merit, rather than "sort by one then the next".  See Chris Browne's
> mail for another variant.

Ah, so.  I think, though, that my variant is a whole lot simpler and
accomplishes mostly the same purpose.  One difference between my
proposal and the others that have popped up thus far is that I am not
convinced table size matters, or at least not in the way that people
are proposing to make it matter.  The main reason I can see why big
tables matter more than small tables is that a big table takes
*longer* to autovacuum than a small table.  If you are 123,456
transactions from a cluster-wide shutdown, and there is one big table
and one small table that need to be autovacuumed, you had better start
on the big one first - because the next autovacuum worker to come
along will quite possibly be able to finish the small one before
doomsday, but if you don't start the big one now you won't finish in
time.  This remains true even if the small table has a slightly older
relfrozenxid than the large one, but ceases to be true when the
difference is large enough that vacuuming the small one first will
advance datfrozenxid enough to extend the time until a shutdown occurs
by more than the time it takes to vacuum it.

For dead-tuple vacuuming, the question of whether the table is large
or small does not seem to me to have a categorical right answer.  You
could argue that it's more important recover 2GB of space in a 20GB
table than 2MB of space in a 20MB table, because more space is being
wasted.  On the flip side you could argue that a small table becomes
bloated much more easily than a large table, because even a minute of
heavy update activity can turn over the entire table contents, which
is unlikely for a larger table.  I am inclined to think that the
percentage of dead tuples is a more important rubric - if things are
going well, it shouldn't ever be much different from the threshold
that triggers AV in the first place - but if somehow it is much
different (e.g. because the table's been locked for a while, or is
accumulating more bloat that the threshold in a single
autovacuum_naptime), that seems like good justification for doing it
ahead of other things that are less bloated.

We do need to make sure that the formula is defined in such a way that
something that is *severely* past vacuum_freeze_table_age always beats
an arbitrarily-bloated table.

-- 
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] Question regarding Sync message and unnamed portal

2013-01-25 Thread Robert Haas

On Fri, Jan 25, 2013 at 1:28 PM, Bruce Momjian  wrote:
> On Mon, Oct  1, 2012 at 02:04:00PM +0900, Tatsuo Ishii wrote:
>> > Tatsuo Ishii  writes:
>> >> From the manual:
>> >> "An unnamed portal is destroyed at the end of the transaction"
>> >
>> > Actually, all portals are destroyed at end of transaction (unless
>> > they're from holdable cursors).  Named or not doesn't enter into it.
>>
>> We need to fix the document then.
>
> I looked into this.  The text reads:
>
> If successfully created, a named prepared-statement object lasts till
> the end of the current session, unless explicitly destroyed.  An 
> unnamed
> prepared statement lasts only until the next Parse statement 
> specifying
> the unnamed statement as destination is issued.
>
> While the first statement does say "named", the next sentence says
> "unnamed", so I am not sure we can make this any clearer.

I'm not sure what this has to do with the previous topic.  Aren't a
prepared statement and a portal two different things?

-- 
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] Doc patch, normalize search_path in index

2013-01-25 Thread Bruce Momjian

On Fri, Jan 25, 2013 at 01:46:46PM -0500, Peter Eisentraut wrote:
> On 1/25/13 12:50 PM, Bruce Momjian wrote:
> > On Fri, Sep 28, 2012 at 12:40:38PM -0500, Karl O. Pinc wrote:
> >> Hi,
> >>
> >> The attached patch (against git head)
> >> normalizes "search_path" as the thing indexed
> >> and uses a secondary index term to distinguish
> >> the configuration parameter from the run-time
> >> setting.
> >>
> >> "search path" the concept remains distinguished
> >> in the index from "search_path" the setting/config param.
> >> It's hard to say whether it's useful to make this
> >> distinction.  From a practical perspective it's easy
> >> for the eye to stop scanning when the indent
> >> level changes and so fail to notice that both
> >> "search path" and "search_path" are index
> >> entries.  At least the index is a 
> >> lot more tidy than before.
> > 
> > I have applied a modified version of your patch that creates separate
> > secondary index references for search_path.
> 
> This matter was already closed:
> https://commitfest.postgresql.org/action/patch_view?id=949
> 
> It looks like your patch reverts part of that.

Uh, I am confused because the patch at:

https://commitfest.postgresql.org/action/patch_view?id=950
http://www.postgresql.org/message-id/1352874080.4647.0@mofo

shows "configuration parameter" being moved to , though this
commit:


http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=a301eb99c9537186f7dd46ba418e84d755227a94

shows it not as secondary.  Would you please suggest a patch or patch
it?  Thanks.

-- 
  Bruce Momjian  http://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_ctl promote" exit status

2013-01-25 Thread Peter Eisentraut

On 1/12/13 3:30 PM, Aaron W. Swenson wrote:
> The Linux Standard Base Core Specification 3.1 says this should return
> '3'. [1]
> 
> [1] 
> http://refspecs.freestandards.org/LSB_3.1.1/LSB-Core-generic/LSB-Core-generic/iniscrptact.html

The LSB spec doesn't say anything about a "promote" action.

And for the stop and reload actions that you tried to change, 3 is
"unimplemented".

There is an ongoing discussion about the exit status of the stop action
under , so
let's keep this item about the "promote" action.

-- 
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] LATERAL, UNNEST and spec compliance

2013-01-25 Thread Stephen Frost

* Tom Lane (t...@sss.pgh.pa.us) wrote:
> However ... David is wrong to claim that it's zero-risk.  It's true that
> an SRF can't contain any side-references today, but it can contain an
> outer reference.  Consider a case like
> 
> SELECT ... FROM a WHERE a.x IN (SELECT ... FROM b, srf(y) WHERE ...)

I see what you mean, but on the other hand, that looks like something we
might actually want to complain about as 'y' is pretty clearly ambiguous
here.  I'm a bit surprised that doesn't already throw an error.

> This is a little bit far-fetched, but it could happen.  As against that,
> we make incompatible changes in every release, and it does seem like
> assuming LATERAL for functions in FROM would be a usability gain most
> of the time.  And special-casing UNNEST to satisfy the standard seems
> *really* ugly.

It's definitely far-fetched, imv.  If it's possible, within reason, to
explicitly throw a "please disambiguate 'y'" type of error in those
specific cases, that'd be nice, but I don't think it'd be required.  A
mention in the release notes would be sufficient.

Thanks,

Stephen

signature.asc
Description: Digital signature

Re: [HACKERS] Doc patch, normalize search_path in index

2013-01-25 Thread Peter Eisentraut

On 1/25/13 12:50 PM, Bruce Momjian wrote:
> On Fri, Sep 28, 2012 at 12:40:38PM -0500, Karl O. Pinc wrote:
>> Hi,
>>
>> The attached patch (against git head)
>> normalizes "search_path" as the thing indexed
>> and uses a secondary index term to distinguish
>> the configuration parameter from the run-time
>> setting.
>>
>> "search path" the concept remains distinguished
>> in the index from "search_path" the setting/config param.
>> It's hard to say whether it's useful to make this
>> distinction.  From a practical perspective it's easy
>> for the eye to stop scanning when the indent
>> level changes and so fail to notice that both
>> "search path" and "search_path" are index
>> entries.  At least the index is a 
>> lot more tidy than before.
> 
> I have applied a modified version of your patch that creates separate
> secondary index references for search_path.

This matter was already closed:
https://commitfest.postgresql.org/action/patch_view?id=949

It looks like your patch reverts part of that.



-- 
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] Doc patch, normalize search_path in index

2013-01-25 Thread Bruce Momjian

On Fri, Jan 25, 2013 at 01:42:48PM -0500, Tom Lane wrote:
> Bruce Momjian  writes:
> > On Fri, Jan 25, 2013 at 01:35:49PM -0500, Tom Lane wrote:
> >> This patch seems pretty bizarre.  What is the difference between a
> >> "configuration parameter" and a "run-time setting"?  Why would you
> >> point people to two different places for those two terms?
> 
> > Should I make them both "configuration parameter" and leave the
> > "security" as a second one separate?
> 
> Works for me.  I think "configuration parameter" is the phrase we
> use most places.

OK, attached patch applied.

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

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
new file mode 100644
index 929d29c..f869b50
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
*** CREATE TABLE public.products ( ... );
*** 1736,1742 
  
 
  search_path
! run-time setting
 
  
 
--- 1736,1742 
  
 
  search_path
! configuration parameter
 
  
 

-- 
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] Doc patch, normalize search_path in index

2013-01-25 Thread Tom Lane

Bruce Momjian  writes:
> On Fri, Jan 25, 2013 at 01:35:49PM -0500, Tom Lane wrote:
>> This patch seems pretty bizarre.  What is the difference between a
>> "configuration parameter" and a "run-time setting"?  Why would you
>> point people to two different places for those two terms?

> Should I make them both "configuration parameter" and leave the
> "security" as a second one separate?

Works for me.  I think "configuration parameter" is the phrase we
use most places.

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] Doc patch, normalize search_path in index

2013-01-25 Thread Bruce Momjian

On Fri, Jan 25, 2013 at 01:35:49PM -0500, Tom Lane wrote:
> Bruce Momjian  writes:
> > I have applied a modified version of your patch that creates separate
> > secondary index references for search_path.
> 
> This patch seems pretty bizarre.  What is the difference between a
> "configuration parameter" and a "run-time setting"?  Why would you
> point people to two different places for those two terms?

I think he is trying to distinguish between the parameter and the
setting of the parameter, and also its use for security.  No one really
replied to the email so I thought the distinction was accpetable.

Should I make them both "configuration parameter" and leave the
"security" as a second one separate?

-- 
  Bruce Momjian  http://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_ctl promote" exit status

2013-01-25 Thread Dhruv Ahuja

Don't think the attachment made it in the last mail. Attaching now.


On 25 January 2013 18:33, Dhruv Ahuja  wrote:

> May I propose the attached patch.
>
> Points to note and possibly discuss:
> (a) Only exit codes in do_* functions have been changed.
> (b) The link to, and the version of, LSB specifications has been updated.
> (c) A significant change is the exit code of do_stop() on stopping a
> stopped server. Previous return is 1. Proposed return is 0. If this is
> accepted, I would highly suggest a mention in the Release Notes.
> (d) The exit code that raised this issue was the return of promoting a
> promoted server. If promotion fails because the server is running but not
> as standby, should that be considered a case of starting a started service,
> or an application specific failure? I am equally weighted to opt for the
> former, but have proposed differently in the patch.
>
>
>
> On 23 October 2012 17:29, Robert Haas  wrote:
>
>> On Tue, Oct 23, 2012 at 6:39 AM, Dhruv Ahuja 
>> wrote:
>> > The "pg_ctl promote" command returns an exit code of 1 when the server
>> > is not in standby mode, and the same exit code of 1 when the server
>> > isn't started at all. The only difference at the time being is the
>> > string output at the time, which FYI are...
>> >
>> > pg_ctl: cannot promote server; server is not in standby mode
>> >
>> > ...and...
>> >
>> > pg_ctl: PID file "/var/lib/pgsql/9.1/data/postmaster.pid" does not exist
>> > Is server running?
>> >
>> > ...respectively.
>> >
>> > I am in the process of developing a clustering solution around luci
>> > and rgmanager (in Red Hat EL 6) and for the time being, am basing it
>> > off the string output. Maybe each different exit reason should have a
>> > unique exit code, whatever my logic and approach to solving this
>> > problem be?
>>
>> That doesn't seem like a bad idea.  Got a patch?
>>
>> --
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>
>


pg_ctl-exit_codes_v-01.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] Doc patch, normalize search_path in index

2013-01-25 Thread Tom Lane

Bruce Momjian  writes:
> I have applied a modified version of your patch that creates separate
> secondary index references for search_path.

This patch seems pretty bizarre.  What is the difference between a
"configuration parameter" and a "run-time setting"?  Why would you
point people to two different places for those two terms?

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_ctl promote" exit status

2013-01-25 Thread Dhruv Ahuja

May I propose the attached patch.

Points to note and possibly discuss:
(a) Only exit codes in do_* functions have been changed.
(b) The link to, and the version of, LSB specifications has been updated.
(c) A significant change is the exit code of do_stop() on stopping a
stopped server. Previous return is 1. Proposed return is 0. If this is
accepted, I would highly suggest a mention in the Release Notes.
(d) The exit code that raised this issue was the return of promoting a
promoted server. If promotion fails because the server is running but not
as standby, should that be considered a case of starting a started service,
or an application specific failure? I am equally weighted to opt for the
former, but have proposed differently in the patch.

On 23 October 2012 17:29, Robert Haas  wrote:

> On Tue, Oct 23, 2012 at 6:39 AM, Dhruv Ahuja  wrote:
> > The "pg_ctl promote" command returns an exit code of 1 when the server
> > is not in standby mode, and the same exit code of 1 when the server
> > isn't started at all. The only difference at the time being is the
> > string output at the time, which FYI are...
> >
> > pg_ctl: cannot promote server; server is not in standby mode
> >
> > ...and...
> >
> > pg_ctl: PID file "/var/lib/pgsql/9.1/data/postmaster.pid" does not exist
> > Is server running?
> >
> > ...respectively.
> >
> > I am in the process of developing a clustering solution around luci
> > and rgmanager (in Red Hat EL 6) and for the time being, am basing it
> > off the string output. Maybe each different exit reason should have a
> > unique exit code, whatever my logic and approach to solving this
> > problem be?
>
> That doesn't seem like a bad idea.  Got a patch?
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: [HACKERS] LATERAL, UNNEST and spec compliance

2013-01-25 Thread Tom Lane

Stephen Frost  writes:
> * David Fetter (da...@fetter.org) wrote:
>> 3. Make all cases of SRFs in the FROM-clause implicitly LATERAL.
>> 
>> (As far as I can tell, those cases whose behaviour would be changed by
>> this actually produce errors in versions prior to 9.3, so no working
>> code should be affected.)

> +1 for me on this idea.  If you're calling an SRF, passing in a lateral
> value, 'LATERAL' seems like it's just a noise word, and apparently the
> SQL authors felt the same, as they don't require it for unnest().

At first I didn't like this idea, but it's growing on me.

However ... David is wrong to claim that it's zero-risk.  It's true that
an SRF can't contain any side-references today, but it can contain an
outer reference.  Consider a case like

SELECT ... FROM a WHERE a.x IN (SELECT ... FROM b, srf(y) WHERE ...)

In existing releases the "y" could be a valid outer reference to a.y.
If b also has a column y, David's proposal would cause us to prefer
that interpretation, since b.y would be more closely nested than a.y.
If you're lucky, you'd get a type-mismatch error, but if the two y's
are of similar datatypes the query would just silently do something
different than it used to.

This is a little bit far-fetched, but it could happen.  As against that,
we make incompatible changes in every release, and it does seem like
assuming LATERAL for functions in FROM would be a usability gain most
of the time.  And special-casing UNNEST to satisfy the standard seems
*really* ugly.

> I agree that we should really hammer this down before 9.3 is out the
> door.

Yeah, if we're going to do this it'd make the most sense to do it in the
same release that introduces LATERAL.

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] Question regarding Sync message and unnamed portal

2013-01-25 Thread Bruce Momjian

On Mon, Oct  1, 2012 at 02:04:00PM +0900, Tatsuo Ishii wrote:
> > Tatsuo Ishii  writes:
> >> From the manual:
> >> "An unnamed portal is destroyed at the end of the transaction"
> > 
> > Actually, all portals are destroyed at end of transaction (unless
> > they're from holdable cursors).  Named or not doesn't enter into it.
> 
> We need to fix the document then.

I looked into this.  The text reads:

If successfully created, a named prepared-statement object lasts till
the end of the current session, unless explicitly destroyed.  An unnamed
prepared statement lasts only until the next Parse statement specifying
the unnamed statement as destination is issued.

While the first statement does say "named", the next sentence says
"unnamed", so I am not sure we can make this any clearer.

-- 
  Bruce Momjian  http://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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Tom Lane

Robert Haas  writes:
> On Fri, Jan 25, 2013 at 12:35 PM, Andres Freund  
> wrote:
>> I don't think the first part is problematic. Which scenario do you have
>> in mind where that would really cause adverse behaviour? autovacuum
>> seldomly does full table vacuums on tables otherwise these days so
>> tables get "old" in that sense pretty regularly and mostly uniform.

> I'm worried about the case of a very, very frequently updated table
> getting put ahead of a table that needs a wraparound vacuum, but only
> just.  It doesn't sit well with me to think that the priority of that
> goes from 0 (we don't even try to update it) to infinity (it goes
> ahead of all tables needing to be vacuumed for dead tuples) the
> instant we hit the vacuum_freeze_table_age.

Well, really the answer to that is that we have multiple autovac
workers, and even if the first one that comes along picks the wraparound
job, the next one won't.

Having said that, I agree that it might be better to express the
sort priority as some sort of continuous function of multiple figures of
merit, rather than "sort by one then the next".  See Chris Browne's
mail for another variant.

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] LATERAL, UNNEST and spec compliance

2013-01-25 Thread Stephen Frost

* David Fetter (da...@fetter.org) wrote:
> As I see it, the current options are:
> 
> 1. Do nothing, and insist on non-standard use of the LATERAL keyword.

I'm not a big fan of this.  Providing a good error message saying "you
need to use LATERAL for this query to work" makes it slightly better,
but I don't feel like there's really any ambiguity here.

> 2. Add UNNEST to the grammar (or parse analysis) as a special case, making
>it implicitly LATERAL.
> 
>(This would make implementing S301 easier, but special cases are ugly.)

This I really don't like.

> 3. Make all cases of SRFs in the FROM-clause implicitly LATERAL.
> 
>(As far as I can tell, those cases whose behaviour would be changed by
>this actually produce errors in versions prior to 9.3, so no working
>code should be affected.)

+1 for me on this idea.  If you're calling an SRF, passing in a lateral
value, 'LATERAL' seems like it's just a noise word, and apparently the
SQL authors felt the same, as they don't require it for unnest().

> Since LATERAL is new in 9.3, I think the pros and cons of these choices
> should be considered now, rather than being allowed to slide by unexamined.

I agree that we should really hammer this down before 9.3 is out the
door.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] COPY FREEZE has no warning

2013-01-25 Thread Stephen Frost

* Robert Haas (robertmh...@gmail.com) wrote:
> On Fri, Jan 25, 2013 at 11:59 AM, Tom Lane  wrote:
> > Bruce Momjian  writes:
> >> On Fri, Jan 25, 2013 at 02:48:37AM +0100, Andres Freund wrote:
> >>> FWIW, and I won't annoy anyone further after this email, now that its
> >>> deterministic, I still think that this should be an ERROR not a WARNING.
> >
> >> As the FREEZE is just an optimization, I thought NOTICE, vs WARNING or
> >> ERROR was fine.  If others want this changed, please reply.
> >
> > The previous argument about it was "if you bothered to specify FREEZE,
> > you probably really want/need that behavior".  So I can definitely see
> > Andres' point.  Perhaps WARNING would be a suitable compromise?
> 
> I'll vote for ERROR.  I don't see why this sound be a best-effort thing.

Yeah, I tend to agree.  In part, I think having it error when the
conditions aren't met would actually reduce the chances of having this
'feature' end up as the default in some ORM somewhere...

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Event Triggers: adding information

2013-01-25 Thread Christopher Browne

On Fri, Jan 25, 2013 at 11:58 AM, Dimitri Fontaine
 wrote:
> Robert Haas  writes:
>> OK, but can we lay the issue of a *normalized* command string to the
>> side for just one minute, and talk about exposing the *raw* command
>> string?  It seems to me that this would be (1) very easy to do, (2)
>> reasonable to slip into 9.3, and (3) useful to some people.  Arguably,
>> the normalized command string would be useful to MORE people, but I
>> think the raw command string is not without its uses, and it's at
>> least one order of magnitude less work.
>
> My understanding is that if the command string we give to event triggers
> is ambiguous (sub-object names, schema qualifications, etc), it comes
> useless for logical replication use. I'll leave it to the consumers of
> that to speak up now.

"Useless" is a strong word, but it certainly injures usefulness pretty
substantially.

If it isn't normalized, then either we accept that:

a) If you fail to properly qualify your inputs, when generating DDL,
we can offer that it's pretty likely it'll all smash on the floor when
we try to replicate it, or

b) We need to capture the active search_path from the
environment at the instant of the DDL capture event, and carry it
over along with the DDL.  If we could assume that the
GUC, search_path, was the correct value, that's possibly not super
difficult, but I'm not certain that's the correct thing to capture.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"


-- 
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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Robert Haas

On Fri, Jan 25, 2013 at 12:35 PM, Andres Freund  wrote:
>> I think that to do this right, we need to consider not only the status
>> quo but the trajectory.  For example, suppose we have two tables to
>> process, one of which needs a wraparound vacuum and the other one of
>> which needs dead tuples removed.  If the table needing the wraparound
>> vacuum is small and just barely over the threshold, it isn't urgent;
>> but if it's large and way over the threshold, it's quite urgent.
>> Similarly, if the table which needs dead tuples removed is rarely
>> updated, postponing vacuum is not a big deal, but if it's being
>> updated like crazy, postponing vacuum is a big problem.  Categorically
>> putting autovacuum wraparound tables ahead of everything else seems
>> simplistic, and thinking that more dead tuples is more urgent than
>> fewer dead tuples seems *extremely* simplistic.
>
> I don't think the first part is problematic. Which scenario do you have
> in mind where that would really cause adverse behaviour? autovacuum
> seldomly does full table vacuums on tables otherwise these days so
> tables get "old" in that sense pretty regularly and mostly uniform.

I'm worried about the case of a very, very frequently updated table
getting put ahead of a table that needs a wraparound vacuum, but only
just.  It doesn't sit well with me to think that the priority of that
goes from 0 (we don't even try to update it) to infinity (it goes
ahead of all tables needing to be vacuumed for dead tuples) the
instant we hit the vacuum_freeze_table_age.

One idea would be to give each table a "badness".  So estimate the
percentage of the tuples in each table that are dead.  And then we
compute the percentage by which age(relfrozenxid) exceeds the table
age, and add those two percentages up to get total badness.  We
process tables that are otherwise-eligible for vacuuming in descending
order of badness.  So if autovacuum_vacuum_scale_factor = 0.2 and a
table is more than than 120% of vacuum_freeze_table_age, then it's
certain to be vacuumed before any table that only needs dead-tuple
processing.  But if it's only slightly past the cutoff, it doesn't get
to stomp all over the people who need dead tuples cleaned up.

The thing is, avoiding a full-cluster shutdown due to anti-wraparound
vacuum is important.  But, IME, that rarely happens.  What is much
more common is that an individual table gets bloated and CLUSTER or
VACUUM FULL is required to recover, and now the system is effectively
down for as long as that takes to complete.  I don't want to make that
case substantially more likely just to avoid a danger of full-cluster
shutdown that, for most users most of the time, is really a very
remote risk.  There's some point at which an anti-wraparound vacuum
should not only trump everything else, but probably also ignore the
configured cost delay settings - but equating that point with the
first point at which we consider doing it at all does not seem right
to me.

> I think a minimal version might be acceptable. Its a bug if the database
> regularly shuts down and you need to write manual vacuuming scripts to
> prevent it from happening.
>
> I don't think the argument that the pg_class order might work better
> than anything holds that much truth - its not like thats something
> really stable.

I freely admit that if pg_class order happens to work better, it's
just good luck.  But sometimes people get lucky.

-- 
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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Andres Freund

On 2013-01-25 12:52:46 -0500, Tom Lane wrote:
> Andres Freund  writes:
> > I think if we backpatch this we should only prefer wraparound tables and
> > leave the rest unchanged.
> 
> That's not a realistic option, at least not with anything that uses this
> approach to sorting the tables.  You'd have to assume that qsort() is
> stable which it probably isn't.

Well, comparing them equally will result in an about as arbitrary order
as right now, so I don't really see a problem with that. I am fine with
sorting them truly randomly as well (by assining a temporary value when
putting it into the list so the comparison is repeatable and conforms to
the triangle inequality etc).

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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Christopher Browne

On Fri, Jan 25, 2013 at 12:00 PM, Andres Freund  wrote:
> On 2013-01-25 11:51:33 -0500, Tom Lane wrote:
>> Alvaro Herrera  writes:
>> > 2. for other tables, consider floor(log(size)).  This makes tables of
>> > sizes in the same ballpark be considered together.
>>
>> > 3. For tables of similar size, consider
>> > (n_dead_tuples - threshold) / threshold.
>> > "threshold" is what gets calculated as the number of tuples over which
>> > a table is considered for vacuuming.  This number, then, is a relative
>> > measure of how hard is vacuuming needed.
>>
>> The floor(log(size)) part seems like it will have rather arbitrary
>> behavioral shifts when a table grows just past a log boundary.  Also,
>> I'm not exactly sure whether you're proposing smaller tables first or
>> bigger tables first, nor that either of those orderings is a good thing.
>
> That seems dubious to me as well.
>
>> I think sorting by just age(relfrozenxid) for for-wraparound tables, and
>> just the n_dead_tuples measurement for others, is probably reasonable
>> for now.  If we find out that has bad behaviors then we can look at how
>> to fix them, but I don't think we have enough understanding yet of what
>> the bad behaviors might be.
>
> If we want another ordering criterion than that it might be worth
> thinking about something like n_dead_tuples/relpages to make sure that
> small tables with a high dead tuples ratio get vacuumed in time.

I'd imagine it a good idea to reserve some autovacuum connections for small
tables, that is, to have a maximum relpages for some portion of the
connections.

That way you don't get stuck having all the connections busy working on
huge tables and leaving small tables starved.  That scenario seems pretty
obvious.

I'd be inclined to do something a bit more sophisticated than just
age(relfrozenxid) for wraparound; I'd be inclined to kick off large tables'
wraparound vacuums earlier than those for smaller tables.

With a little bit of noodling around, here's a thought for a joint function
that I *think* has reasonably common scales:

f(deadtuples, relpages, age) =
   deadtuples/relpages + e ^ (age*ln(relpages)/2^32)

When the age of the table is low, this is dominated by the deadtuple/relpages
part of the equation; you vacuum tables based on what has the largest % of
dead tuples.

But when a table is not vacuumed for a long time, the second term will kick
in, and we'll tend to:
 a) Vacuum the ones that are largest the earliest, but nonetheless
 b) Vacuum them as the ration of age/2^32 gets close to 1.

This function assumes relpages > 0, and there's a constant, 2^32, there which
might be fiddled with.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

-- 
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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Tom Lane

Andres Freund  writes:
> I think if we backpatch this we should only prefer wraparound tables and
> leave the rest unchanged.

That's not a realistic option, at least not with anything that uses this
approach to sorting the tables.  You'd have to assume that qsort() is
stable which it probably isn't.

> I don't think the argument that the pg_class order might work better
> than anything holds that much truth - its not like thats something
> really stable.

I find that less than credible as well.

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] Doc patch, normalize search_path in index

2013-01-25 Thread Bruce Momjian

On Fri, Sep 28, 2012 at 12:40:38PM -0500, Karl O. Pinc wrote:
> Hi,
> 
> The attached patch (against git head)
> normalizes "search_path" as the thing indexed
> and uses a secondary index term to distinguish
> the configuration parameter from the run-time
> setting.
> 
> "search path" the concept remains distinguished
> in the index from "search_path" the setting/config param.
> It's hard to say whether it's useful to make this
> distinction.  From a practical perspective it's easy
> for the eye to stop scanning when the indent
> level changes and so fail to notice that both
> "search path" and "search_path" are index
> entries.  At least the index is a 
> lot more tidy than before.

I have applied a modified version of your patch that creates separate
secondary index references for search_path.

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

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
new file mode 100644
index b7df8ce..5f47c1f
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
*** COPY postgres_log FROM '/full/path/to/lo
*** 4778,4784 
   
search_path (string)

!search_path configuration parameter

pathfor schemas

--- 4778,4785 
   
search_path (string)

!search_path
!configuration parameter

pathfor schemas

diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
new file mode 100644
index 207de9b..929d29c
*** a/doc/src/sgml/ddl.sgml
--- b/doc/src/sgml/ddl.sgml
*** CREATE TABLE public.products ( ... );
*** 1735,1741 
 
  
 
! search_path configuration parameter
 
  
 
--- 1735,1742 
 
  
 
! search_path
! run-time setting
 
  
 
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
new file mode 100644
index 48160b2..1f6c134
*** a/doc/src/sgml/ref/create_function.sgml
--- b/doc/src/sgml/ref/create_function.sgml
*** SELECT * FROM dup(42);
*** 674,680 
Writing SECURITY DEFINER Functions Safely
  

!search_path configuration parameter
 use in securing functions

  
--- 674,680 
Writing SECURITY DEFINER Functions Safely
  

!search_path
 use in securing functions

  

-- 
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] COPY FREEZE has no warning

2013-01-25 Thread Robert Haas

On Fri, Jan 25, 2013 at 11:59 AM, Tom Lane  wrote:
> Bruce Momjian  writes:
>> On Fri, Jan 25, 2013 at 02:48:37AM +0100, Andres Freund wrote:
>>> FWIW, and I won't annoy anyone further after this email, now that its
>>> deterministic, I still think that this should be an ERROR not a WARNING.
>
>> As the FREEZE is just an optimization, I thought NOTICE, vs WARNING or
>> ERROR was fine.  If others want this changed, please reply.
>
> The previous argument about it was "if you bothered to specify FREEZE,
> you probably really want/need that behavior".  So I can definitely see
> Andres' point.  Perhaps WARNING would be a suitable compromise?

I'll vote for ERROR.  I don't see why this sound be a best-effort thing.

-- 
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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Andres Freund

On 2013-01-25 12:19:25 -0500, Robert Haas wrote:
> On Fri, Jan 25, 2013 at 11:51 AM, Tom Lane  wrote:
> > The floor(log(size)) part seems like it will have rather arbitrary
> > behavioral shifts when a table grows just past a log boundary.  Also,
> > I'm not exactly sure whether you're proposing smaller tables first or
> > bigger tables first, nor that either of those orderings is a good thing.
> >
> > I think sorting by just age(relfrozenxid) for for-wraparound tables, and
> > just the n_dead_tuples measurement for others, is probably reasonable
> > for now.  If we find out that has bad behaviors then we can look at how
> > to fix them, but I don't think we have enough understanding yet of what
> > the bad behaviors might be.
 
> I think that to do this right, we need to consider not only the status
> quo but the trajectory.  For example, suppose we have two tables to
> process, one of which needs a wraparound vacuum and the other one of
> which needs dead tuples removed.  If the table needing the wraparound
> vacuum is small and just barely over the threshold, it isn't urgent;
> but if it's large and way over the threshold, it's quite urgent.
> Similarly, if the table which needs dead tuples removed is rarely
> updated, postponing vacuum is not a big deal, but if it's being
> updated like crazy, postponing vacuum is a big problem.  Categorically
> putting autovacuum wraparound tables ahead of everything else seems
> simplistic, and thinking that more dead tuples is more urgent than
> fewer dead tuples seems *extremely* simplistic.

I don't think the first part is problematic. Which scenario do you have
in mind where that would really cause adverse behaviour? autovacuum
seldomly does full table vacuums on tables otherwise these days so
tables get "old" in that sense pretty regularly and mostly uniform.

I agree that the second criterion isn't worth very much and that we need
something better there.

> I ran across a real-world case where a user had a small table that had
> to be vacuumed every 15 seconds to prevent bloat.  If we change the
> algorithm in a way that gives other things priority over that table,
> then that user could easily get hosed when they install a maintenance
> release containing this change.

I think if we backpatch this we should only prefer wraparound tables and
leave the rest unchanged.

> Which is exactly why back-patching this is not a good idea, IMHO.  We
> could easily run across a system where pg_class order happens to be
> better than anything else we come up with.  Such changes are expected
> in new major versions, but not in maintenance releases.

I think a minimal version might be acceptable. Its a bug if the database
regularly shuts down and you need to write manual vacuuming scripts to
prevent it from happening.

I don't think the argument that the pg_class order might work better
than anything holds that much truth - its not like thats something
really stable.

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] LATERAL, UNNEST and spec compliance

2013-01-25 Thread David Fetter

On Thu, Jan 24, 2013 at 09:12:41PM -0800, David Fetter wrote:
> On Thu, Jan 24, 2013 at 09:51:46AM -0800, David Fetter wrote:
> > Folks,
> > 
> > Andrew Gierth asked me to send this out as his email is in a parlous
> > state at the moment.  My comments will follow in replies.  Without
> > further ado:
> > [snip]
> > 
> > As I see it, the current options are:
> > 
> > 1. Do nothing, and insist on non-standard use of the LATERAL keyword.
> > 
> > 2. Add UNNEST to the grammar (or parse analysis) as a special case, making
> >it implicitly LATERAL.
> > 
> >(This would make implementing S301 easier, but special cases are ugly.)
> > 
> > 3. Make all cases of SRFs in the FROM-clause implicitly LATERAL.
> > 
> >(As far as I can tell, those cases whose behaviour would be changed by
> >this actually produce errors in versions prior to 9.3, so no working
> >code should be affected.)
> > 
> > Since LATERAL is new in 9.3, I think the pros and cons of these choices
> > should be considered now, rather than being allowed to slide by unexamined.
> 
> Please find attached a patch which implements approach 3.  The vast
> majority of it is changes to the regression tests.  The removed
> regression tests in join.{sql,out} are no longer errors, although some
> of them are pretty standard DoS attacks, hence they're all removed.
> 
> Cheers,
> David.

Oops.  Misspelled rtekind in the previous patch.  Here's a corrected
one, much shorter.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***
*** 9391,9397  table_ref:   relation_expr opt_alias_clause
| func_table func_alias_clause
{
RangeFunction *n = 
makeNode(RangeFunction);
!   n->lateral = false;
n->funccallnode = $1;
n->alias = linitial($2);
n->coldeflist = lsecond($2);
--- 9391,9397 
| func_table func_alias_clause
{
RangeFunction *n = 
makeNode(RangeFunction);
!   n->lateral = true;
n->funccallnode = $1;
n->alias = linitial($2);
n->coldeflist = lsecond($2);
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***
*** 7928,7934  get_from_clause_item(Node *jtnode, Query *query, 
deparse_context *context)
deparse_columns *colinfo = deparse_columns_fetch(varno, dpns);
boolprintalias;
  
!   if (rte->lateral)
appendStringInfoString(buf, "LATERAL ");
  
/* Print the FROM item proper */
--- 7928,7934 
deparse_columns *colinfo = deparse_columns_fetch(varno, dpns);
boolprintalias;
  
!   if (rte->lateral && rte->rtekind != RTE_FUNCTION)
appendStringInfoString(buf, "LATERAL ");
  
/* Print the FROM item proper */
*** a/src/test/regress/expected/join.out
--- b/src/test/regress/expected/join.out
***
*** 3577,3603  select * from
   Output: (COALESCE((COALESCE(b.q2, 42::bigint)), d.q2))
  (26 rows)
  
- -- test some error cases where LATERAL should have been used but wasn't
- select f1,g from int4_tbl a, generate_series(0, f1) g;
- ERROR:  column "f1" does not exist
- LINE 1: select f1,g from int4_tbl a, generate_series(0, f1) g;
- ^
- HINT:  There is a column named "f1" in table "a", but it cannot be referenced 
from this part of the query.
- select f1,g from int4_tbl a, generate_series(0, a.f1) g;
- ERROR:  invalid reference to FROM-clause entry for table "a"
- LINE 1: select f1,g from int4_tbl a, generate_series(0, a.f1) g;
- ^
- HINT:  There is an entry for table "a", but it cannot be referenced from this 
part of the query.
- select f1,g from int4_tbl a cross join generate_series(0, f1) g;
- ERROR:  column "f1" does not exist
- LINE 1: ...ct f1,g from int4_tbl a cross join generate_series(0, f1) g;
-  ^
- HINT:  There is a column named "f1" in table "a", but it cannot be referenced 
from this part of the query.
- select f1,g from int4_tbl a cross join generate_series(0, a.f1) g;
- ERROR:  invali

Re: [HACKERS] pg_retainxlog for inclusion in 9.3?

2013-01-25 Thread Andres Freund

On 2013-01-26 02:21:00 +0900, Fujii Masao wrote:
> On Sat, Jan 5, 2013 at 11:11 PM, Magnus Hagander  wrote:
> > On Fri, Jan 4, 2013 at 7:13 PM, Peter Eisentraut  wrote:
> >> On 1/3/13 12:30 PM, Robert Haas wrote:
> >>> On Thu, Jan 3, 2013 at 11:32 AM, Magnus Hagander  
> >>> wrote:
>  Any particular reason? It goes pretty tightly together with
>  pg_receivexlog, which is why I'd prefer putting it alongside that one.
>  But if you have a good argument against it, I can change my mind :)
> >>>
> >>> Mostly that it seems like a hack, and I suspect we may come up with a
> >>> better way to do this in the future.
> >>
> >> It does seem like a hack.  Couldn't this be implemented with a backend
> >> switch instead?
> >
> > It definitely is a bit of a hack.
> >
> > I assume by backend switch you mean guc, right? If so, no, not easily
> > so. Because it's the archiver process that does the deleting.
>
> The process which deletes the old WAL files is the checkpointer. The
> checkpointer can access to the shared memory and know the location
> of the WAL record which has been already replicated to the standby.
> ISTM it's not difficult to implement the logic which pg_retainxlog provides
> into the checkpointer. How about just changing the checkpointer so
> that it checks whether the WAL file to delete has been already not
> only archived but also replicated if GUC flag is enabled?

The problem with that is that to implement it robustly we would need
persistent state about the replicas.

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] pg_retainxlog for inclusion in 9.3?

2013-01-25 Thread Fujii Masao

On Sat, Jan 5, 2013 at 11:11 PM, Magnus Hagander  wrote:
> On Fri, Jan 4, 2013 at 7:13 PM, Peter Eisentraut  wrote:
>> On 1/3/13 12:30 PM, Robert Haas wrote:
>>> On Thu, Jan 3, 2013 at 11:32 AM, Magnus Hagander  
>>> wrote:
 Any particular reason? It goes pretty tightly together with
 pg_receivexlog, which is why I'd prefer putting it alongside that one.
 But if you have a good argument against it, I can change my mind :)
>>>
>>> Mostly that it seems like a hack, and I suspect we may come up with a
>>> better way to do this in the future.
>>
>> It does seem like a hack.  Couldn't this be implemented with a backend
>> switch instead?
>
> It definitely is a bit of a hack.
>
> I assume by backend switch you mean guc, right? If so, no, not easily
> so. Because it's the archiver process that does the deleting.

The process which deletes the old WAL files is the checkpointer. The
checkpointer can access to the shared memory and know the location
of the WAL record which has been already replicated to the standby.
ISTM it's not difficult to implement the logic which pg_retainxlog provides
into the checkpointer. How about just changing the checkpointer so
that it checks whether the WAL file to delete has been already not
only archived but also replicated if GUC flag is enabled?

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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Robert Haas

On Fri, Jan 25, 2013 at 11:51 AM, Tom Lane  wrote:
> The floor(log(size)) part seems like it will have rather arbitrary
> behavioral shifts when a table grows just past a log boundary.  Also,
> I'm not exactly sure whether you're proposing smaller tables first or
> bigger tables first, nor that either of those orderings is a good thing.
>
> I think sorting by just age(relfrozenxid) for for-wraparound tables, and
> just the n_dead_tuples measurement for others, is probably reasonable
> for now.  If we find out that has bad behaviors then we can look at how
> to fix them, but I don't think we have enough understanding yet of what
> the bad behaviors might be.

Which is exactly why back-patching this is not a good idea, IMHO.  We
could easily run across a system where pg_class order happens to be
better than anything else we come up with.  Such changes are expected
in new major versions, but not in maintenance releases.

I think that to do this right, we need to consider not only the status
quo but the trajectory.  For example, suppose we have two tables to
process, one of which needs a wraparound vacuum and the other one of
which needs dead tuples removed.  If the table needing the wraparound
vacuum is small and just barely over the threshold, it isn't urgent;
but if it's large and way over the threshold, it's quite urgent.
Similarly, if the table which needs dead tuples removed is rarely
updated, postponing vacuum is not a big deal, but if it's being
updated like crazy, postponing vacuum is a big problem.  Categorically
putting autovacuum wraparound tables ahead of everything else seems
simplistic, and thinking that more dead tuples is more urgent than
fewer dead tuples seems *extremely* simplistic.

I ran across a real-world case where a user had a small table that had
to be vacuumed every 15 seconds to prevent bloat.  If we change the
algorithm in a way that gives other things priority over that table,
then that user could easily get hosed when they install a maintenance
release containing this change.

-- 
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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Andres Freund

On 2013-01-25 11:51:33 -0500, Tom Lane wrote:
> Alvaro Herrera  writes:
> > 2. for other tables, consider floor(log(size)).  This makes tables of
> > sizes in the same ballpark be considered together.
>
> > 3. For tables of similar size, consider
> > (n_dead_tuples - threshold) / threshold.
> > "threshold" is what gets calculated as the number of tuples over which
> > a table is considered for vacuuming.  This number, then, is a relative
> > measure of how hard is vacuuming needed.
>
> The floor(log(size)) part seems like it will have rather arbitrary
> behavioral shifts when a table grows just past a log boundary.  Also,
> I'm not exactly sure whether you're proposing smaller tables first or
> bigger tables first, nor that either of those orderings is a good thing.

That seems dubious to me as well.

> I think sorting by just age(relfrozenxid) for for-wraparound tables, and
> just the n_dead_tuples measurement for others, is probably reasonable
> for now.  If we find out that has bad behaviors then we can look at how
> to fix them, but I don't think we have enough understanding yet of what
> the bad behaviors might be.

If we want another ordering criterion than that it might be worth
thinking about something like n_dead_tuples/relpages to make sure that
small tables with a high dead tuples ratio get vacuumed in time.

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] COPY FREEZE has no warning

2013-01-25 Thread Tom Lane

Bruce Momjian  writes:
> On Fri, Jan 25, 2013 at 02:48:37AM +0100, Andres Freund wrote:
>> FWIW, and I won't annoy anyone further after this email, now that its
>> deterministic, I still think that this should be an ERROR not a WARNING.

> As the FREEZE is just an optimization, I thought NOTICE, vs WARNING or
> ERROR was fine.  If others want this changed, please reply.

The previous argument about it was "if you bothered to specify FREEZE,
you probably really want/need that behavior".  So I can definitely see
Andres' point.  Perhaps WARNING would be a suitable compromise?

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] Event Triggers: adding information

2013-01-25 Thread Dimitri Fontaine

Robert Haas  writes:
> OK, but can we lay the issue of a *normalized* command string to the
> side for just one minute, and talk about exposing the *raw* command
> string?  It seems to me that this would be (1) very easy to do, (2)
> reasonable to slip into 9.3, and (3) useful to some people.  Arguably,
> the normalized command string would be useful to MORE people, but I
> think the raw command string is not without its uses, and it's at
> least one order of magnitude less work.

My understanding is that if the command string we give to event triggers
is ambiguous (sub-object names, schema qualifications, etc), it comes
useless for logical replication use. I'll leave it to the consumers of
that to speak up now.

  [… no access to tuples in per-statement triggers …]
> I agree that was a reasonable trade-off.  But it would be quite
> something else again if someone were to propose the idea of having NEW
> and OLD available for each-statement triggers, but only give the
> information about the first row affected by the statement, rather than
> all of them.  I think that proposal would quite rightly be rejected,
> and I think it's morally equivalent to what you're proposing, or what
> I understood you to be proposing, at any rate.

No it's not.

+   /*
+* we only support filling-in information for DROP command if we only
+* drop a single object at a time, in all other cases the ObjectID,
+* Name and Schema will remain NULL.
+*/
+   if (list_length(stmt->objects) != 1)

The current patch implementation is to fill in the object id, name and
schema with NULL when we have something else than a single object as the
target. I did that when I realized we have a precedent with statement
triggers and that we would maybe share the implementation of the "record
set variable" facility for PLs here.


> I'm not sure, either, but I think that exposing things as tables is a
> neat idea.  I had imagined passing either JSON or arrays, but tables
> would be easier to work with, at least for PL/pgsql triggers.

Any idea about a practical implementation that we can do in the 9.3
timeframe? Baring that my proposal is to allow ourselves not to provide
the information at all in that case in 9.3, and complete the feature by
9.4 time frame.

Possibly with OLD and NEW relations for per-statement triggers, if it
turns out as I think that we can re-use the same piece of PLpgSQL side
framework in both cases.

>> The only commands that will target more than one object are:
>>
>>   - DROP, either in the command or by means of CASCADE;
>>   - CREATE SCHEMA with its PROCESS_UTILITY_SUBCOMMAND usage;
>
> CREATE TABLE can also create subsidiary objects (indexes,
> constraints); and there could be more things that do this in the
> future.

Subsidiary objects are not the same thing at all as a command that
targets more than one object, and the difference is user visible.

>> The CASCADE case is something else entirely, and we need to be very
>> careful about its locking behavior. Also, in the CASCADE case I can
>> perfectly agree that we're not talking about a ddl_something event any
>> more, and that in 9.4 we will be able to provide a sql_drop generic
>> event that will now about that.
>
> I've felt from the beginning that we really need that to be able to do
> anything worthwhile with this feature, and I said so last year around
> this time.  Meanwhile, absent that, if we put something in here that
> only handles a subset of cases, the result will be DDL replication
> that randomly breaks.

So we have two proposals here:

  - Have the cascading drop calls back to process utility with a new
context value of PROCESS_UTILITY_CASCADE and its parse node, wherein
you only stuff the ObjectAdress, and provide event triggers support
for this new cascade command;

  - Implement a new event called "sql_drop" where you provide the same
amount of information than in a ddl_command event (same lookups,
etc), but without any parsetree nor I suppose the command string
that the user would have to type to drop just that object.

You objected to the first on modularity violation grounds, and on the
second on development cycle timing grounds. And now you're saying that
because we don't have a practical solution, I'm not sure, apparently
it's dead, but what is?

Please help me decipher your process of thoughs and conclusions.

> That doesn't bother me.  If the facility is useful enough that people
> want it in other PLs, they can submit patches to add it.  I don't
> believe there's any requirement that we must support this feature in
> every PL before we can think about releasing.

Ok, I won't bother either then.

> I'm not sure I can, but I think that the design I'm proposing gives a
> lot of flexibility.  If we create a pg_parse_tree type with no input
> function and an output function that merely returns a dummy value, and
> we pass a pg_parse_tree object to PL/pgsql event triggers, then it's a
> s

Re: [HACKERS] Using COPY FREEZE with pg_restore --single-transaction

2013-01-25 Thread Simon Riggs

On 25 January 2013 15:24, Bruce Momjian  wrote:
> pg_restore --single-transaction has the setup to make use of the new
> COPY FREEZE optimization.
>
> However, I don't see us using COPY FREEZE for pg_restore
> --single-transaction.  Shouldn't we do that?  The problem is we would
> need to have pg_dump emit the FREEZE, which would cause it not to load
> in earlier versions of Postgres, and to (soon) emit a NOTICE for
> non-single-transaction loads in all other cases.  :-(
>
> Can pg_restore inject FREEZE itself when it does the copy?

I've added this as a discussion topic to the 9.3 Open Items, for
discussion after the CF.

-- 
 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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Tom Lane

Alvaro Herrera  writes:
> So if we're to discuss this, here's what I had in mind:

> 1. for-wraparound tables always go first; oldest age(relfrozenxid) are
> sorted earlier.  For tables of the same age, consider size as below.

It seems unlikely that age(relfrozenxid) will be identical for multiple
tables often enough to worry about, so the second part of that seems
like overcomplication.

> 2. for other tables, consider floor(log(size)).  This makes tables of
> sizes in the same ballpark be considered together.

> 3. For tables of similar size, consider
> (n_dead_tuples - threshold) / threshold.
> "threshold" is what gets calculated as the number of tuples over which
> a table is considered for vacuuming.  This number, then, is a relative
> measure of how hard is vacuuming needed.

The floor(log(size)) part seems like it will have rather arbitrary
behavioral shifts when a table grows just past a log boundary.  Also,
I'm not exactly sure whether you're proposing smaller tables first or
bigger tables first, nor that either of those orderings is a good thing.

I think sorting by just age(relfrozenxid) for for-wraparound tables, and
just the n_dead_tuples measurement for others, is probably reasonable
for now.  If we find out that has bad behaviors then we can look at how
to fix them, but I don't think we have enough understanding yet of what
the bad behaviors might be.

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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Tom Lane

Alvaro Herrera  writes:
> Peter Eisentraut escribió:
>> Autovacuum has existed for N years and nobody complained about this
>> until just now, so I don't see a strong justification for backpatching.

> I disagree about people not complaining.  Maybe the complaints have not
> been specifically about the wraparound stuff and toast tables, but for
> sure there have been complaints about autovacuum not giving more
> priority to tables that need work more urgently.

FWIW, I don't see that this is too scary to back-patch.  It's unlikely
to make things worse than the current coding, which is more or less
pg_class tuple order.

I do suggest that it might be wise not to try to squeeze it into the
early-February update releases.  Put it in master as soon as we agree
on the behavior, then back-patch after the next updates.  That will
give us a couple months' testing, rather than a few days, before it
hits any release tarballs.

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] pg_isready (was: [WIP] pg_ping utility)

2013-01-25 Thread Fujii Masao

On Fri, Jan 25, 2013 at 4:10 AM, Phil Sorber  wrote:
> On Thu, Jan 24, 2013 at 1:12 PM, Fujii Masao  wrote:
>> set_pglocale_pgservice() should be called?
>>
>> I think that the command name (i.e., pg_isready) should be given to
>> PQpingParams() as fallback_application_name. Otherwise, the server
>> by default uses "unknown" as the application name of pg_isready.
>> It's undesirable.
>>
>> Why isn't the following message output only when invalid option is
>> specified?
>>
>> Try \"%s --help\" for more information.
>
> I've updated the patch to address these three issues. Attached.
>
>>
>> When the conninfo string including the hostname or port number is
>> specified in -d option, pg_isready displays the wrong information
>> as follows.
>>
>> $ pg_isready -d "port="
>> /tmp:5432 - no response
>>
>
> This is what i asked about in my previous email about precedence of
> the parameters. I can parse that with PQconninfoParse, but what are
> the rules for merging both individual and conninfo params together?

If I read conninfo_array_parse() correctly, PQpingParams() prefer the
option which is set to its keyword array later.

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] Skip checkpoint on promoting from streaming replication

2013-01-25 Thread Simon Riggs

On 25 January 2013 12:15, Heikki Linnakangas  wrote:

>> 1) an immediate checkpoint can cause a disk/resource usage spike,
>> which is definitely not what you need just when a spike of connections
>> and new SQL hits the system.
>
>
> It doesn't need to be an "immediate" checkpoint, ie. you don't need to rush
> through it with checkpoint_completion_target=0. I think you should initiate
> a regular, slow, checkpoint, right after writing the end-of-recovery record.
> It can take some time for it to finish, which is ok.

OK, will add.

-- 
 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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Alvaro Herrera

Peter Eisentraut escribió:
> On 1/25/13 10:29 AM, Alvaro Herrera wrote:
> > And I do want to get something back-patchable.
> 
> Autovacuum has existed for N years and nobody complained about this
> until just now, so I don't see a strong justification for backpatching.

I disagree about people not complaining.  Maybe the complaints have not
been specifically about the wraparound stuff and toast tables, but for
sure there have been complaints about autovacuum not giving more
priority to tables that need work more urgently.

> Or is this a regression from an earlier release?

Nope.

> In general, I think we should backpatch less.

I don't disagree with this general principle, but I certainly don't like
the idea of letting systems run with known flaws just because we're too
scared to patch them.  Now I don't object to a plan such as keep it in
master only for a while and backpatch after it has seen some more
testing.  But for large sites, this is a real problem and they have to
work around it manually which is frequently inconvenient; keep in mind
9.0 is going to be supported for years yet.

That said, if consensus here is to not backpatch this at all, I will go
with that; but let's have the argument first.

-- 
Á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] COPY FREEZE has no warning

2013-01-25 Thread Bruce Momjian

On Fri, Jan 25, 2013 at 10:30:40AM -0500, Stephen Frost wrote:
> * Bruce Momjian (br...@momjian.us) wrote:
> > On Fri, Jan 25, 2013 at 02:48:37AM +0100, Andres Freund wrote:
> > > On 2013-01-23 14:02:46 -0500, Bruce Momjian wrote:
> > > > As a reminder, COPY FREEZE still does not issue any warning/notice if
> > > > the freezing does not happen:
> > > 
> > > FWIW, and I won't annoy anyone further after this email, now that its
> > > deterministic, I still think that this should be an ERROR not a WARNING.
> > 
> > As the FREEZE is just an optimization, I thought NOTICE, vs WARNING or
> > ERROR was fine.  If others want this changed, please reply.
> 
> tbh, I tend to agree w/ Andres on this one.  COPY FREEZE means "do
> this", not "if you can get away with it, then do it".  That said, I can
> really see a use-case for both which would imply that we'd have a way to
> specify, ala DROP TABLE and IF EXISTS.  Not sure exactly what that'd
> look like though and having one or the other is better than nothing
> (presuming everyone is fine with the visibility impacts of this, which I
> still contend will cause our users to give us grief over in the
> future..).

Interesting.  I can see the visibility as making this more than an
optimization, because it has external visibility.  However, the
visibility problem is when it is silent (no NOTICE).  Do we need
a message that says we did honor FREEZE?

We could get fancy and make FREEZE more than a boolean, e.g. OFF,
PREFER, FORCE.

-- 
  Bruce Momjian  http://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] Hanging backends and possible index corruption

2013-01-25 Thread Bernd Helmle




--On 25. Januar 2013 16:28:16 +0100 Andres Freund  
wrote:



Did you reindex after upgrading to 9.1.6? Did you ever have any crashes
or failovers before upgrading to 9.1.6?
I have seen pretty similar symptoms caused by "Fix persistence marking
of shared buffers during WAL replay" in 9.1.6.


Hmm it happened only on a single heavily used table for now and this table 
was REINDEXed twice after updating to 9.1.6 (every time the issue occured).


--
Thanks

Bernd


--
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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Peter Eisentraut

On 1/25/13 10:29 AM, Alvaro Herrera wrote:
> And I do want to get something back-patchable.

Autovacuum has existed for N years and nobody complained about this
until just now, so I don't see a strong justification for backpatching.

Or is this a regression from an earlier release?

In general, I think we should backpatch less.



-- 
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 1/3] Fix x + y < x overflow checks

2013-01-25 Thread Robert Haas

On Thu, Jan 24, 2013 at 4:36 AM, Xi Wang  wrote:
> icc optimizes away the overflow check x + y < x (y > 0), because
> signed integer overflow is undefined behavior in C.  Instead, use
> a safe precondition test x > INT_MAX - y.

As you post these patches, please add them to:

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

This will ensure that they (eventually) get looked at.

-- 
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] Event Triggers: adding information

2013-01-25 Thread Robert Haas

On Thu, Jan 24, 2013 at 5:43 AM, Dimitri Fontaine
 wrote:
>> But I wonder: wouldn't it be better to just expose the raw string the
>> user typed?  I mean, in all the cases we really care about, the
>> information we can *reliably* expose at this point is going to be
>> pretty nearly the same as extracting the third space-delimited word
>> out of the command text and splitting it on a period.  And you can do
>> that much easily even in PL/pgsql.  You could also extract a whole lot
>
> Totally Agreed. That's another reason why I want to provide users with
> the Normalized Command String, it will be then be even easier for them
> to do what you just say.

OK, but can we lay the issue of a *normalized* command string to the
side for just one minute, and talk about exposing the *raw* command
string?  It seems to me that this would be (1) very easy to do, (2)
reasonable to slip into 9.3, and (3) useful to some people.  Arguably,
the normalized command string would be useful to MORE people, but I
think the raw command string is not without its uses, and it's at
least one order of magnitude less work.

>> Now, in a ddl_command_end trigger, there's a lot more information that
>> you can usefully expose.  In theory, if the command records "what it
>> did" somewhere, you can extract that information with as much
>> precision as it cared to record.  However, I'm pretty skeptical of the
>> idea of exposing a single OID.  I mean, if the "main" use case here is
>
> There's precedent in PostgreSQL: how do you get information about each
> row that were in the target from a FOR EACH STATEMENT trigger?

You don't.  But, the fact that you can't is an unpleasant limitation,
not a model of emulation.

We make trade-offs about the scope of features all the time.  Somebody
evidently thought it was reasonable for each-row triggers to expose
the tuple but to leave the corresponding feature for each-statement
triggers unimplemented, and as much as I'd like to have that feature,
I agree that was a reasonable trade-off.  But it would be quite
something else again if someone were to propose the idea of having NEW
and OLD available for each-statement triggers, but only give the
information about the first row affected by the statement, rather than
all of them.  I think that proposal would quite rightly be rejected,
and I think it's morally equivalent to what you're proposing, or what
I understood you to be proposing, at any rate.

>> array of OIDs.  Really, you're going to need something quite a bit
>> more complicated than that to describe something like ALTER TABLE, but
>> even for pretty simple cases a single OID doesn't cut it.
>
> If you want to solve that problem, let's talk about pseudo relations
> that you can SELECT FROM, please. We can already expose a tuplestore in
> PL code by means of cursors and SRF, but I'm not sure that's how we want
> to expose the "statement level information" here.

I'm not sure, either, but I think that exposing things as tables is a
neat idea.  I had imagined passing either JSON or arrays, but tables
would be easier to work with, at least for PL/pgsql triggers.

> The only commands that will target more than one object are:
>
>   - DROP, either in the command or by means of CASCADE;
>   - CREATE SCHEMA with its PROCESS_UTILITY_SUBCOMMAND usage;

CREATE TABLE can also create subsidiary objects (indexes,
constraints); and there could be more things that do this in the
future.

> The CASCADE case is something else entirely, and we need to be very
> careful about its locking behavior. Also, in the CASCADE case I can
> perfectly agree that we're not talking about a ddl_something event any
> more, and that in 9.4 we will be able to provide a sql_drop generic
> event that will now about that.

I've felt from the beginning that we really need that to be able to do
anything worthwhile with this feature, and I said so last year around
this time.  Meanwhile, absent that, if we put something in here that
only handles a subset of cases, the result will be DDL replication
that randomly breaks.  Bill Gates used to have a reputation for being
able to give demos of beta software where he carefully avoided doing
things that would trigger the known crash bugs, and thus make it look
like everything was working great.  That's fine for a marketing
presentation, but releasing in that kind of state gets you a bad
reputation.

> Mostly agreed. Do we want to ship with only PL/pgSQL support? I've not
> been following how those PL features usually reach the other PLs…

That doesn't bother me.  If the facility is useful enough that people
want it in other PLs, they can submit patches to add it.  I don't
believe there's any requirement that we must support this feature in
every PL before we can think about releasing.

> Well, try and get the name of the exclusion constraint created when
> running that command, then rewrite it with the name injected in the
> command:
>
>   CREATE TABLE xyz(i int, exclude (i WITH =) where (i > 10)

Re: [HACKERS] BUG #6510: A simple prompt is displayed using wrong charset

2013-01-25 Thread Andrew Dunstan



On 01/25/2013 10:26 AM, Peter Eisentraut wrote:

On 1/24/13 4:04 PM, Andrew Dunstan wrote:

On 01/24/2013 11:19 AM, Noah Misch wrote:

On Thu, Jan 24, 2013 at 08:50:36AM -0500, Andrew Dunstan wrote:

On 01/24/2013 03:42 AM, Craig Ringer wrote:

On 01/24/2013 01:06 AM, Alexander Law wrote:

Hello,
Please let me know if I can do something to get the bug fix
(https://commitfest.postgresql.org/action/patch_view?id=902)
committed.
I would like to fix other bugs related to postgres localization, but
I am not sure yet how to do it.

For anyone looking for the history, the 1st post on this topic is here:

http://www.postgresql.org/message-id/e1s3twb-0004oy...@wrigleys.postgresql.org


Yeah.

I'm happy enough with this patch. ISTM it's really a bug and should be
backpatched, no?

It is a bug, yes.  I'm neutral on whether to backpatch.


Well, that's what I did. :-)

The 9.0 and 9.1 branches are now failing to build.



Yeah, sorry, working on it.

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] COPY FREEZE has no warning

2013-01-25 Thread Stephen Frost

* Bruce Momjian (br...@momjian.us) wrote:
> On Fri, Jan 25, 2013 at 02:48:37AM +0100, Andres Freund wrote:
> > On 2013-01-23 14:02:46 -0500, Bruce Momjian wrote:
> > > As a reminder, COPY FREEZE still does not issue any warning/notice if
> > > the freezing does not happen:
> > 
> > FWIW, and I won't annoy anyone further after this email, now that its
> > deterministic, I still think that this should be an ERROR not a WARNING.
> 
> As the FREEZE is just an optimization, I thought NOTICE, vs WARNING or
> ERROR was fine.  If others want this changed, please reply.

tbh, I tend to agree w/ Andres on this one.  COPY FREEZE means "do
this", not "if you can get away with it, then do it".  That said, I can
really see a use-case for both which would imply that we'd have a way to
specify, ala DROP TABLE and IF EXISTS.  Not sure exactly what that'd
look like though and having one or the other is better than nothing
(presuming everyone is fine with the visibility impacts of this, which I
still contend will cause our users to give us grief over in the
future..).

Thanks,

Stephen

signature.asc
Description: Digital signature

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

2013-01-25 Thread Kohei KaiGai

2013/1/25 Kohei KaiGai :
> 2013/1/24 Magnus Hagander :
>> On Thu, Jan 24, 2013 at 10:11 AM, Kohei KaiGai  wrote:
>>> 2013/1/24 Tom Lane :
 John R Pierce  writes:
> On 1/23/2013 8:32 PM, Tom Lane wrote:
>> FWIW, in Fedora-land I see: ...

> I'd be far more interested in what is in RHEL and CentOS.Fedora,
> with its 6 month obsolescence cycle, is of zero interest to me for
> deploying database servers.

 But of course Fedora is also the upstream that will become RHEL7
 and beyond.
>>
>> Do we know which version of Fedora will become RHEL7, and thus, which
>> version of libselinux will go in RHEL7? (And do we know which version
>> of postgres will go in RHEL7, assuming release schedules hold)
>>
> I'm not certain...
>
 It might be that the update timing makes a bigger difference in some
 other distros, though.  To return to Heikki's original point about
 Debian, what are they shipping today?

>>> Even though I'm not good at release cycle of Debian, I tried to check
>>> the shipped version of postgresql and libselinux for stable, testing,
>>> unstable and experimental release.
>>> I'm not certain why they don't push postgresql-9.2 into experimental
>>> release yet. However, it seems to me optimistic libselinux-2.1.10 being
>>> bundled on the timeline of postgresql-9.3.
>>>
>>> If someone familiar with Debian's release cycle, I'd like to see the 
>>> suggestion.
>>>
>>> * Debian (stable) ... postgresql-8.4 + libselinux-2.0.96
>>> http://packages.debian.org/en/squeeze/postgresql
>>> http://packages.debian.org/en/source/squeeze/libselinux
>>>
>>> * Debian (testing) ... postgresql-9.1 + libselinux-2.1.9
>>> http://packages.debian.org/en/wheezy/postgresql
>>> http://packages.debian.org/en/source/wheezy/libselinux
>>
>> Just as a note, wheezy is the version that will be the next debian
>> stable, and it's in freeze since quite a while back. So we can safely
>> expect it will be 2.1.9 that's included in the next debian stable.
>>
> It seems to me this means pgsql-9.1 shall be bundled with
> libselinux-2.1.9, but not pgsql-9.3, so here is no matter.
>
> When pgsql-9.3 is released, Fedora 17 will exceed end-of-life.
> Debian already releases libselinux-2.1.12 on experimental package
> even though its pgsql is 9.1. Is it too optimistic estimation?
>
I asked folks of Debian-JP how and when does package maintainer
pushes new versions. Usually, new versions shall be pushed to
unstable branch, then testing and stable. But it is now feature freeze
period thus it is prohibited to push new features to unstable.
Thus, newer libselinux (2.1.12) is now in experimental branch, but not
in unstable branch.
He also said, the newer libselinux will likely moved to unstable when
feature freeze is unlocked soon. The pgsql-v9.3 shall be released
several months later, so it also shall be pushed to unstable branch
several months later at least. It does not make problems.

Due to same reason, RHEL7 does not make a problem even if it
ships with pgsql-9.3, because the latest libselinux already support
2.1.10 feature. Thus, required libselinux version should be sufficient
when pgsql-9.3 become available on Fedora.

Thanks,
-- 
KaiGai Kohei 


-- 
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] autovacuum not prioritising for-wraparound tables

2013-01-25 Thread Alvaro Herrera

Tom Lane escribió:

> As posted, what we've got here is sorting on a boolean condition, with
> the behavior within each group totally up to the whims of qsort().  That
> seems especially dangerous since the priority order is mostly undefined.
> 
> I was a bit surprised that Alvaro didn't propose sorting by the age of
> relfrozenxid, at least for the subset of tables that are considered
> wraparound hazards.  Not sure what a good criterion is for the rest.

Hah.  This patch began life with more complex prioritisation at first,
but before going much further I dumbed down the idea to avoid having to
discuss these issues, as it doesn't seem a particularly good timing.
And I do want to get something back-patchable.

So if we're to discuss this, here's what I had in mind:

1. for-wraparound tables always go first; oldest age(relfrozenxid) are
sorted earlier.  For tables of the same age, consider size as below.

2. for other tables, consider floor(log(size)).  This makes tables of
sizes in the same ballpark be considered together.

3. For tables of similar size, consider
(n_dead_tuples - threshold) / threshold.
"threshold" is what gets calculated as the number of tuples over which
a table is considered for vacuuming.  This number, then, is a relative
measure of how hard is vacuuming needed.

-- 
Á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] Hanging backends and possible index corruption

2013-01-25 Thread Andres Freund

On 2013-01-25 16:24:52 +0100, Bernd Helmle wrote:
> We are currently analyzing an issue at one of our customers PostgreSQL
> database.
> 
> The current version is 9.1.6 (update to 9.1.7 is scheduled for next monday,
> no downtime possible before). It runs on POWER7 (pSeries 740) on an RHEL6.3
> 64-bit LPAR. The packages are built from PGDG SVN sources, no special tweaks
> added. We saw no hardware related errors on this machine, nor any crashes.
> 
> What currently happens on this machine are hanging statements (SELECTs and
> INSERTs occasionally) with 100% CPU. After some investigation it turned out
> that the corresponding backends are seeking within an index file over and
> over again in a loop. Looking into the hanging queries i've recognized
> certain keys which seems to have the problem, other keys used in the WHERE
> condition run smoothly. Turning off index and bitmap index scans caused the
> suspicious keys to return results, too.
> 
> So i've saved the index file (normal BTree index with a single bigint
> column), did a REINDEX and the problem was gone. Looking at the index file
> with pg_filedump and pgbtreecheck from Alvaro gave me the following output:
> 
> pgbtreecheck gives warnings about pages' parents and then loops visiting the
> same pages over and over again:
> 
> NOTICE: fast root: block 290 at level 2
> NOTICE: setting page parents
> WARNING: block 12709 already had a parent (8840); new parent is 12177
> WARNING: block 12710 already had a parent (12439); new parent is 10835
> NOTICE: done setting parents
> NOTICE: Checking forward scan of level 0, starting at block 1
> 
> -- loop starts
> 
> WARNING: right sibling 12710 does not point left to me (11680); points to
> 10924 instead
> 
> Looking into the relevant pages and their prev and next pointers give me the
> following:
> 
> pg_filedump -i -R 11680 ~/tmp/100252789 | grep Blocks
>  Blocks: Previous (12651)  Next (12710)  Level (0)  CycleId (0)
> 
> pg_filedump -i -R 10924 ~/tmp/100252789 | grep Blocks
>  Blocks: Previous (10923)  Next (12710)  Level (0)  CycleId (0)
> 
> pg_filedump -i -R 12710 ~/tmp/100252789 | grep Blocks
>  Blocks: Previous (10924)  Next (10925)  Level (0)  CycleId (0)
> 
> pg_filedump -i -R 10925 ~/tmp/100252789 | grep Blocks
>  Blocks: Previous (12710)  Next (10926)  Level (0)  CycleId (0)
> 
> pg_filedump -i -R 12709 ~/tmp/100252789 | grep Blocks
>  Blocks: Previous (8849)  Next (8850)  Level (0)  CycleId (0)
> 
> pg_filedump -i -R 8840 ~/tmp/100252789 | grep Blocks
>  Blocks: Previous (8555)  Next (9125)  Level (1)  CycleId (0)
> 
> pg_filedump -i -R 12439 ~/tmp/100252789 | grep Blocks
>  Blocks: Previous (11405)  Next (11690)  Level (1)  CycleId (0)
> 
> $ pg_filedump -i -R 12177 ~/tmp/100252789 | grep Blocks
>  Blocks: Previous (11690)  Next (0)  Level (1)  CycleId (0)
> 
> $ pg_filedump -i -R 10835 ~/tmp/100252789 | grep Blocks
>  Blocks: Previous (10550)  Next (11120)  Level (1)  CycleId (0)
> 
> This symptom happened three times in the last couple of weeks now. Looking
> at the numbers doesn't give me the impression that some flaky hardware could
> be involved. What else can we do to track down this problem, any
> suggestions?

Did you reindex after upgrading to 9.1.6? Did you ever have any crashes
or failovers before upgrading to 9.1.6?
I have seen pretty similar symptoms caused by "Fix persistence marking
of shared buffers during WAL replay" in 9.1.6.

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] My first patch! (to \df output)

2013-01-25 Thread Heikki Linnakangas

On 23.01.2013 07:31, Jon Erdman wrote:

Done. Attached.

Thanks, committed.

On 29.12.2012 20:56, Stephen Frost wrote:
> No biggie, and to get the bike-shedding started, I don't really like the
> column name or the values.. :)  I feel like something clearer would be
> "Runs_As" with "caller" or "owner"..  Saying "Security" makes me think
> of ACLs more than what user ID the function runs as, to be honest.

I have to agree that calling the property "security definer/invoker" is 
a poor name in general. "security" is such on overloaded word that it 
could mean anything. "Run as" would make a lot more sense. But given 
that that's the nomenclature we have in the CREATE FUNCTION statement, 
the docs, prosecdef column name and everywhere, that's what we have to 
call it in \df+ too.

- 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] BUG #6510: A simple prompt is displayed using wrong charset

2013-01-25 Thread Peter Eisentraut

On 1/24/13 4:04 PM, Andrew Dunstan wrote:
> 
> On 01/24/2013 11:19 AM, Noah Misch wrote:
>> On Thu, Jan 24, 2013 at 08:50:36AM -0500, Andrew Dunstan wrote:
>>> On 01/24/2013 03:42 AM, Craig Ringer wrote:
 On 01/24/2013 01:06 AM, Alexander Law wrote:
> Hello,
> Please let me know if I can do something to get the bug fix
> (https://commitfest.postgresql.org/action/patch_view?id=902)
> committed.
> I would like to fix other bugs related to postgres localization, but
> I am not sure yet how to do it.
 For anyone looking for the history, the 1st post on this topic is here:

 http://www.postgresql.org/message-id/e1s3twb-0004oy...@wrigleys.postgresql.org

>>>
>>> Yeah.
>>>
>>> I'm happy enough with this patch. ISTM it's really a bug and should be
>>> backpatched, no?
>> It is a bug, yes.  I'm neutral on whether to backpatch.
>>
> 
> Well, that's what I did. :-)

The 9.0 and 9.1 branches are now failing to build.





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

[HACKERS] Hanging backends and possible index corruption

2013-01-25 Thread Bernd Helmle

We are currently analyzing an issue at one of our customers PostgreSQL 
database.


The current version is 9.1.6 (update to 9.1.7 is scheduled for next monday, 
no downtime possible before). It runs on POWER7 (pSeries 740) on an RHEL6.3 
64-bit LPAR. The packages are built from PGDG SVN sources, no special 
tweaks added. We saw no hardware related errors on this machine, nor any 
crashes.


What currently happens on this machine are hanging statements (SELECTs and 
INSERTs occasionally) with 100% CPU. After some investigation it turned out 
that the corresponding backends are seeking within an index file over and 
over again in a loop. Looking into the hanging queries i've recognized 
certain keys which seems to have the problem, other keys used in the WHERE 
condition run smoothly. Turning off index and bitmap index scans caused the 
suspicious keys to return results, too.


So i've saved the index file (normal BTree index with a single bigint 
column), did a REINDEX and the problem was gone. Looking at the index file 
with pg_filedump and pgbtreecheck from Alvaro gave me the following output:


pgbtreecheck gives warnings about pages' parents and then loops visiting 
the same pages over and over again:


NOTICE: fast root: block 290 at level 2
NOTICE: setting page parents
WARNING: block 12709 already had a parent (8840); new parent is 12177
WARNING: block 12710 already had a parent (12439); new parent is 10835
NOTICE: done setting parents
NOTICE: Checking forward scan of level 0, starting at block 1

-- loop starts

WARNING: right sibling 12710 does not point left to me (11680); points to 
10924 instead


Looking into the relevant pages and their prev and next pointers give me 
the following:


pg_filedump -i -R 11680 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (12651)  Next (12710)  Level (0)  CycleId (0)

pg_filedump -i -R 10924 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (10923)  Next (12710)  Level (0)  CycleId (0)

pg_filedump -i -R 12710 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (10924)  Next (10925)  Level (0)  CycleId (0)

pg_filedump -i -R 10925 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (12710)  Next (10926)  Level (0)  CycleId (0)

pg_filedump -i -R 12709 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (8849)  Next (8850)  Level (0)  CycleId (0)

pg_filedump -i -R 8840 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (8555)  Next (9125)  Level (1)  CycleId (0)

pg_filedump -i -R 12439 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (11405)  Next (11690)  Level (1)  CycleId (0)

$ pg_filedump -i -R 12177 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (11690)  Next (0)  Level (1)  CycleId (0)

$ pg_filedump -i -R 10835 ~/tmp/100252789 | grep Blocks
 Blocks: Previous (10550)  Next (11120)  Level (1)  CycleId (0)

This symptom happened three times in the last couple of weeks now. Looking 
at the numbers doesn't give me the impression that some flaky hardware 
could be involved. What else can we do to track down this problem, any 
suggestions?


--
Thanks

Bernd


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

[HACKERS] Using COPY FREEZE with pg_restore --single-transaction

2013-01-25 Thread Bruce Momjian

pg_restore --single-transaction has the setup to make use of the new
COPY FREEZE optimization.

However, I don't see us using COPY FREEZE for pg_restore
--single-transaction.  Shouldn't we do that?  The problem is we would
need to have pg_dump emit the FREEZE, which would cause it not to load
in earlier versions of Postgres, and to (soon) emit a NOTICE for
non-single-transaction loads in all other cases.  :-(

Can pg_restore inject FREEZE itself when it does the copy?

-- 
  Bruce Momjian  http://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] Skip checkpoint on promoting from streaming replication

2013-01-25 Thread Tom Lane

Heikki Linnakangas  writes:
> There's no hard correctness reason here for any particular behavior, I 
> just feel that that would make most sense. It seems prudent to initiate 
> a checkpoint right after timeline switch, so that you get a new 
> checkpoint on the new timeline fairly soon - it could take up to 
> checkpoint_timeout otherwise, but there's no terrible rush to finish it 
> ASAP.

+1.  The way I would think about it is that we're switching from a
checkpointing regime appropriate to a slave to one appropriate to a
master.  If the last restartpoint was far back, compared to the
configured checkpoint timing for master operation, we're at risk that a
crash could take longer than desired to recover.  So we ought to embark
right away on a fresh checkpoint, but do it in the same way it would be
done in normal master operation (thus, not immediate).  Once it's done
we'll be in the expected checkpointing state for a master.

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] Minor inheritance/check bug: Inconsistent behavior

2013-01-25 Thread Bruce Momjian

On Fri, Sep 14, 2012 at 02:04:51PM +, Amit kapila wrote:
> On Thu, 6 Sep 2012 14:50:05 -0400 Robert Hass wrote:
> 
> On Tue, Aug 28, 2012 at 6:40 AM, Amit Kapila  com> wrote:
> >>   AFAICT during Update also, it doesn't contain useful. The only chance it
> >> would have contain something useful is when it goes for EvalPlanQual and
> >> then again comes to check for constraints. However these attributes get
> >> filled in heap_update much later.
> >
> >> So now should the fix be that it returns an error for system column
> >> reference except for OID case?
> 
> > +1.
> 
>  
> 
> 1. I think in this scenario the error for system column except for tableOID
> should be thrown at Create/Alter time.
> 
> 2. After setting OID in ExecInsert/ExecUpdate may be setting of same inside
> heap functions can be removed.
> 
>But for now I have kept them as it is.
> 
>  
> 
> Please find the Patch for bug-fix.
> 
> If this is okay, I shall send you the test cases for same.

Did we ever make any progress on this?

-- 
  Bruce Momjian  http://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] Event Triggers: adding information

2013-01-25 Thread Robert Haas

[ changing subject line to keep threads of discussion separate ]

On Thu, Jan 24, 2013 at 5:51 AM, Dimitri Fontaine
 wrote:
> Something like this part:
>
> + -- now try something crazy to ensure we don't crash the backend
> + create function test_event_trigger_drop_function()
> +  returns event_trigger
> +  as $$
> + BEGIN
> + drop function test_event_trigger2() cascade;
> + END
> + $$ language plpgsql;
> +
> + create function test_event_trigger2() returns event_trigger as $$
> + BEGIN
> + RAISE NOTICE 'test_event_trigger2: % %', tg_event, tg_tag;
> + END
> + $$ language plpgsql;
> +
> + create event trigger drop_test_a on "ddl_command_start"
> + when tag in ('create table')
> +execute procedure test_event_trigger_drop_function();
> +
> + create event trigger drop_test_b on "ddl_command_start"
> +execute procedure test_event_trigger2();
> +
> + create table event_trigger_fire1 (a int);
> +
> + -- then cleanup the crazy test
> + drop event trigger drop_test_a;
> + drop event trigger drop_test_b;
> + drop function test_event_trigger_drop_function();
>
> The only problem for the regression tests being that there's an OID in
> the ouput, but with your proposed error message that would go away.

This seems reasonable, but looking into it a little further, fixing
the error message is not quite as simple as might be hoped.  It's
coming from fmgr_info, which can't be dissuaded from erroring out if
the function is gone.  We could do the fmgr_info lookup earlier, but
it doesn't really help: if the tuple disappears, then
plpgsql_compile() will eventually be reached and will go splat anyway.

As far as I can see, the only reasonable way to keep this from
exploding is to take AccessShareLock on the function once we've looked
it up, and then repeat the syscache lookup immediately thereafter to
verify that the OID is still present.  If it is, it can't subsequently
go away.  This seems like a pretty expensive solution, though.  At
present event triggers only fire during DDL commands so maybe this is
OK, but if you imagine using it for NOTIFY or DML then it starts to
sound like a cripplingly-high cost.

So I'm not sure what to do.  I'm not thrilled with the idea of leaving
this the way it is, but it's not a big enough problem for me to argue
for ripping event triggers back out in their entirety, either.  So
maybe we just have to live with it until somebody comes up with a
better idea.

Anybody got one?

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

1 2 >

1 - 100 of 120 matches

Mail list logo