date:20120305

On Sat, Mar 3, 2012 at 12:01 AM, Robert Haas robertmh...@gmail.com wrote:

 On Fri, Mar 2, 2012 at 4:56 PM, Simon Riggs si...@2ndquadrant.com wrote:
 Checksums patch isn't sucking much attention at all but admittedly
 there are some people opposed to the patch that want to draw out the
 conversation until the patch is rejected,

 Wow.  Sounds like a really shitty thing for those people to do -
 torpedoing a perfectly good patch for no reason.

You've explained to me how you think I do that elsewhere and how that
annoyed you, so I think that topic deserves discussion at the
developers meeting to help us understand one another rather than
perpetuate this.


 I have an alternative theory, though: they have sincere objections and
 don't accept your reasons for discounting those objections.


That's exactly the problem though and the discussion on it is relevant here.

Nobody thinks objections on this patch, checksums or others are made
insincerely. It's what happens next that matters. The question should
be about acceptance criteria. What do we need to do to get something
useful committed? Without a clear set of criteria for resolution we
cannot move forward swiftly enough to do useful things. My thoughts
are always about salvaging what we can, trying to find a way through
the maze of objections and constraints not just black/white decisions
based upon the existence of an objection, as if that single point
trumps any other consideration and blocks all possibilities.

So there is a clear difference between an objection to any progress on
a topic (I sincerely object to the checksum patch), and a technical
objection to taking a particular course of action (We shouldn't use
bits x1..x3 because). The first is not viable, however sincerely
it is made, because it leaves the author with no way of resolving
things and it also presumes that the patch only exists in one version
and that the author is somehow refusing to make agreed changes.
Discussion started *here* because it was said Person X is trying to
force patch Y thru, which is true - but that doesn't necessarily mean
the version of the patch that current objections apply to, only that
the author has an equally sincere wish to do something useful.

The way forwards here and elsewhere is to list out the things we can't
do and list out the things that must change - a clear list of
acceptance criteria. If we do that as early as possible we give the
author a good shot at being able to make those changes in time to
commit something useful. Again, only *something* useful: the full
original vision is not always possible.

In summary: What can be done in this release, given the constraints discussed?

So for Peter's patch - what do we need to do to allow some/all of this
to be committed?

And for the checksum patch please go back to the checksum thread and
list out all the things you consider unresolved. In some cases,
resolutions have been suggested but not yet implemented so it would
help if those are either discounted now before they are written, or
accepted in principle to allow work to proceed.

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

2012-03-05 Thread Etsuro Fujita

(2012/03/05 18:21), Shigeru Hanada wrote:
 (2012/02/21 20:25), Etsuro Fujita wrote:
 Please find attached an updated version of the patch.
 
 This v2 patch can be applied on HEAD cleanly.  Compile completed with
 only one expected warning of scan.c, and all regression tests for both
 core and contrib modules passed.
 
 This patch allows FDWs to return multiple ForeignPath nodes per a
 PlanForeignScan call.  It also get rid of FdwPlan, FDW-private
 information container, by replacing with simple List.
 
 I've reviewed the patch closely, and have some comments about its design.

Thank you for your review.

 Basically a create_foo_path is responsible for creating a node object
 with a particular Path-derived type, but this patch changes
 create_foreignscan_path to just call PlanForeignScan and return void.
 This change seems breaking module design.

create_index_path builds multiple index paths for a plain relation.  How
about renaming the function to create_foreign_paths?

Best regards,
Etsuro Fujita

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

2012-03-05 Thread Etsuro Fujita

(2012/03/05 21:00), Etsuro Fujita wrote:
 (2012/03/05 18:21), Shigeru Hanada wrote:
 (2012/02/21 20:25), Etsuro Fujita wrote:
 Please find attached an updated version of the patch.

 This v2 patch can be applied on HEAD cleanly.  Compile completed with
 only one expected warning of scan.c, and all regression tests for both
 core and contrib modules passed.

 This patch allows FDWs to return multiple ForeignPath nodes per a
 PlanForeignScan call.  It also get rid of FdwPlan, FDW-private
 information container, by replacing with simple List.

 I've reviewed the patch closely, and have some comments about its design.
 
 Thank you for your review.
 
 Basically a create_foo_path is responsible for creating a node object
 with a particular Path-derived type, but this patch changes
 create_foreignscan_path to just call PlanForeignScan and return void.
 This change seems breaking module design.
 
 create_index_path builds multiple index paths for a plain relation.  How
 about renaming the function to create_foreign_paths?

I meant create_foreignscan_paths.  I'm sorry about that.

Best regards,
Etsuro Fujita

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

2012-03-05 Thread Shigeru Hanada

(2012/03/05 21:05), Etsuro Fujita wrote:
 (2012/03/05 21:00), Etsuro Fujita wrote:
 create_index_path builds multiple index paths for a plain relation.  How
 about renaming the function to create_foreign_paths?

 I meant create_foreignscan_paths.  I'm sorry about that.

Perhaps you are confusing create_index_path with create_index_paths.
Former creates a IndexScan path node (so it's similar to
create_foreignscan_path), and latter builds multiple IndexScan paths for
a plain relation.

So, just renaming create_foreignscan_path to plural form seems missing
the point.

-- 
Shigeru Hanada

-- 
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] Re: pg_stat_statements normalisation without invasive changes to the parser (was: Next steps on pg_stat_statements normalisation)

2012-03-05 Thread Andrew Dunstan




On 03/05/2012 05:12 AM, Simon Riggs wrote:

On Sat, Mar 3, 2012 at 12:01 AM, Robert Haasrobertmh...@gmail.com  wrote:


On Fri, Mar 2, 2012 at 4:56 PM, Simon Riggssi...@2ndquadrant.com  wrote:

Checksums patch isn't sucking much attention at all but admittedly
there are some people opposed to the patch that want to draw out the
conversation until the patch is rejected,

Wow.  Sounds like a really shitty thing for those people to do -
torpedoing a perfectly good patch for no reason.

You've explained to me how you think I do that elsewhere and how that
annoyed you, so I think that topic deserves discussion at the
developers meeting to help us understand one another rather than
perpetuate this.




No matter how much we occasionally annoy each other, I think we all need 
to accept that we're all dealing in good faith. Suggestions to the 
contrary are ugly, have no foundation in fact that I'm aware of, and 
reflect badly on our community.


Postgres has a well deserved reputation for not having the sort of 
public bickering that has caused people to avoid certain other projects. 
Please keep it that way.


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] review: CHECK FUNCTION statement

Hello

2012/3/5 Alvaro Herrera alvhe...@commandprompt.com:

 Excerpts from Pavel Stehule's message of dom mar 04 16:33:08 -0300 2012:

 Hello

 2012/3/4 Alvaro Herrera alvhe...@commandprompt.com:

                      CHECK FUNCTION
  -
   In function: 'f()'
   error:42P01:2:sentencia SQL:no existe la relación «foo»
   query:select                                           +
   var                                                    +
   from                                                   +
   foo
                        ^
  (4 filas)
 

 this should be fixed. I checked expressions, that works (I expect)
 correctly. Caret helps - (really). Sometimes man is blind :).

 Agreed.


I don't have your last version, so I am sending just part of
CheckFunctionById function - this fragment ensures a output

or please, send me your last patch and I'll do merge

now result is better

postgres= create function f() returns int language plpgsql as $$
postgres$ begin select
postgres$ var
postgres$ from
postgres$ foo; end; $$;
CREATE FUNCTION
postgres= check function f();
  CHECK FUNCTION
---
 In function: f()
 error:42P01:2:SQL statement:relation foo does not exist
 query:select
   var
   from
   foo
   ^
(7 rows)

and some utf8 fce

postgres= check function fx(int);
  CHECK FUNCTION
--
 In function: fx(integer)
 error:42703:3:RETURN:column ýšý does not exist
 query:SELECT (select žlutý
 from jj
   /* ýšý */
   where /*ýšýšý8*/ ýšý = 10)
^
(7 rows)

postgres= check function fx(int);
 CHECK FUNCTION
-
 In function: fx(integer)
 error:42703:3:RETURN:column xx does not exist
 query:SELECT (select t.a
 from t
   /* ýšý */
   where /*ýšýšý8*/ xx = 10)
^
(7 rows)

caret is ok

regards

Pavel

 --
 Álvaro Herrera alvhe...@commandprompt.com
 The PostgreSQL Company - Command Prompt, Inc.
 PostgreSQL Replication, Consulting, Custom Development, 24x7 support
		else
		{
			resetStringInfo(sinfo);
			appendStringInfo(sinfo, In function: %s, funcname);
			do_text_output_oneline(tstate, sinfo.data);
			
			for (i = 0; i  SPI_processed; i++)
			{
char		*query;

resetStringInfo(sinfo);
appendStringInfo(sinfo, %s:%s:%s:%s:%s,
	SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 8),
	SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 4),
	SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 2),
	SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 3),
	SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 5));

do_text_output_oneline(tstate, sinfo.data);
resetStringInfo(sinfo);

query = SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 10);
if (query != NULL)
{
	bool	isnull;
	char 	*query_line;		/* pointer to begin of processed line */
	int	query_line_caret;
	int	caret;
	bool	is_first_line = true;

	/*
	 * put any query line to separate output line. And append
	 * a curet, when is defined and related to processed rows.
	 */
	caret = SPI_getbinval(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 9, isnull);
	if (isnull)
		caret = -1;

	query_line = query;
	query_line_caret = caret;

	while (*query != '\0')
	{
		int	   len;

		if (*query == '\n')
		{
			/* now we found end of line */
			*query = '\0';
			if (is_first_line)
			{
appendStringInfo(sinfo, query:%s, query_line);
is_first_line = false;
			}
			else
appendStringInfo(sinfo,   %s, query_line);

			do_text_output_oneline(tstate, sinfo.data);
			resetStringInfo(sinfo);

			if (query_line_caret  0  caret == 0)
			{
appendStringInfo(sinfo,   %*s,
			query_line_caret, ^);
do_text_output_oneline(tstate, sinfo.data);
resetStringInfo(sinfo);
query_line_caret = 0;
			}

			/* store caret offset for next line */
			if (caret  0)
query_line_caret = caret - 1;

			/* go to next line */
			query_line = query + 1;
		}

		len = pg_mblen(query);
		query += len;

		if (caret  0)
			caret--;
	}

	/* last line output */
	if (query_line != NULL)
	{
		if (is_first_line)
		{
			appendStringInfo(sinfo, query:%s, query_line);
		}
		else
			appendStringInfo(sinfo,   %s, query_line);

		do_text_output_oneline(tstate, sinfo.data);
		resetStringInfo(sinfo);

		if (query_line_caret  0  caret == 0)
		{
			appendStringInfo(sinfo,   %*s,
		query_line_caret, ^);
			do_text_output_oneline(tstate, sinfo.data);
			resetStringInfo(sinfo);
		}
	}
}
			}
		}

--

Re: [HACKERS] Collect frequency statistics for arrays

2012-03-05 Thread Alexander Korotkov

On Mon, Mar 5, 2012 at 1:11 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 BTW, one other thing about the count histogram: seems like we are
 frequently generating uselessly large ones.  For instance, do ANALYZE
 in the regression database and then run

 select tablename,attname,elem_count_histogram from pg_stats
  where elem_count_histogram is not null;

 You get lots of entries that look like this:

  pg_proc | proallargtypes |
 {1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,5,5,5,6,6,6,2.80556}
  pg_proc | proargmodes|
 {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,1.6}
  pg_proc | proargnames|
 {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,3,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,4,5,5,5,5,5,5,5,5,6,6,6,7,7,7,7,8,8,8,14,14,15,16,3.8806}
  pg_proc | proconfig  |
 {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}
  pg_class| reloptions |
 {1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}

 which seems to me to be a rather useless expenditure of space.
 Couldn't we reduce the histogram size when there aren't many
 different counts?

 It seems fairly obvious to me that we could bound the histogram
 size with (max count - min count + 1), but maybe something even
 tighter would work; or maybe I'm missing something and this would
 sacrifice accuracy.


True. If (max count - min count + 1) is small, enumerating of frequencies
is both more compact and more precise representation. Simultaneously,
if (max count - min count + 1) is large, we can run out of
statistics_target with such representation. We can use same representation
of count distribution as for scalar column value: MCV and HISTOGRAM, but it
would require additional statkind and statistics slot. Probably, you've
better ideas?

--
With best regards,
Alexander Korotkov.

Re: [HACKERS] autovacuum locks

2012-03-05 Thread Kevin Grittner

Gregg Jaskiewicz  wrote:
 
 Also, on the subject of prepared transactions (2PC), the select *
 from pg_prepared_xacts ; query simply does not reveal anything,
 despite the fact that I know that there should be at least two of
 those open.
 Unless it only list saved transactions, not a transaction in the
 middle of operation.
 
Transactions only appear there between the PREPARE statement and the
COMMIT.  Hopefully that is a small window of time for each
transaction.
 
-Kevin

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

[HACKERS] Checksums, state of play

To avoid any confusion as to where this proposed feature is now, I'd
like to summarise my understanding, make proposals and also request
clear feedback on them.

Checksums have a number of objections to them outstanding.

1. We don't need them because there will be something better in a
later release. I don't think anybody disagrees that a better solution
is possible in the future; doubts have been expressed as to what will
be required and when that is likely to happen. Opinions differ. We can
and should do something now unless there is reason not to.

2. Turning checksums on/off/on/off in rapid succession can cause false
positive reports of checksum failure if crashes occur and are ignored.
That may lead to the feature and PostgreSQL being held in disrepute.
This can be resolved, if desired, by having having a two-stage
enabling process where we must issue a command that scans every block
in the database before checksum checking begins. VACUUM is easily
modified to the task, we just need to agree that is suitable and agree
syntax.
A suggestion is VACUUM ENABLE CHECKSUMS; others are possible.

3. Pages with checksums set need to have a version marking to show
that they are a later version of the page layout. That version number
needs to be extensible to many later versions. Pages of multiple
versions need to exist within the server to allow simple upgrades and
migration.

4. Checksums that are dependent upon a bit setting on the block are
somewhat fragile. Requests have been made to add bits in certain
positions and also to remove them again. No set of bits seems to
please everyone.

(3) and (4) are in conflict with each other, but there is a solution.
We mark the block with a version number, but we don't make the
checking dependant upon the version number. We simply avoid making any
checks until the command to scan all blocks is complete, per point
(2). That way we need to use 1 flag bit to mark the new version and
zero flag bits to indicate checks should happen.

(Various other permutations of solutions for (2), (3), (4) have been
discussed and may also be still open)

5. The part of the page header that can be used as a checksum has been
disputed. Using the 16 bits dedicated to a version number seems like
the least useful consecutive 2 bytes of data in the page header. It
can't be  16 bits because that wouldn't be an effective checksum for
database blocks. We might prefer 32 bits, but that would require use
of some other parts of the page header and possibly split that into
two parts. Splitting the checksum into 2 parts will cause the code to
be more complex and fragile.

6. Performance impacts. Measured to be a small regression.

7. Hint bit setting requires WAL logging. The worst case for that
would be setting hints on newly loaded tables. Work has been done on
other patches to remove that case. If those don't fly, this would be a
cost paid by those that wish to take advantage of this feature.

If there are other points I've missed for whatever reason, please add
them here again for clarity.

My own assessment of the above is that the checksum feature can be
added to 9.2, as long as we agree the changes above and then proceed
to implement them and also that no further serious problems emerge.

-- 
 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] xlog min recovery request ... is past current point ...

On Sun, Mar 4, 2012 at 2:41 PM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
 Hmm, I think I see how that can happen:

 0. A heap page has its bit set in visibility map to begin with

 1. A heap tuple is inserted/updated/deleted. This clears the VM bit.
 2. time passes, and more WAL is generated
 3. The page is vacuumed, and the visibility map bit is set again.

Note that on 9.0.X, which Christophe is using, the setting of the
visibility map bit in step #3 is not WAL-logged.

 In the standby, this can happen while replaying the WAL, if you restart the
 standby so that some WAL is re-replayed:

 1. The update of the heap tuple is replayed. This clears the VM bit.
 2. The VACUUM is replayed, setting the VM bit again, and updating the VM
 page's LSN.

Therefore I think this won't happen either, on that version.  Do we
somehow emit an FPI for the VM page?

 3. Shutdown and restart standby
 4. The heap update is replayed again. This again clears the VM bit, but does
 not set the LSN

 If the VM page is now evicted from the buffer cache, you get the WARNING you
 saw, because the page is dirty, yet its LSN is beyond the current point in
 recovery.

 AFAICS that's totally harmless, but the warning is quite alarming, so we'll
 have to figure out a way to fix that. Not sure how; perhaps we need to set
 the LSN on the VM page when the VM bit is cleared, but I don't remember off
 the top of my head if there was some important reason why we don't do that
 currently.

I suspect that it was never done just because there was no clear
benefit, since heap_{insert,update,delete} all clear the bit
regardless of the page LSN.  But this might be a reason to do it.  I
can't swear it's safe, though, although I also can't see why it
wouldn't be.  Note also that 9.2devel behaves quite differently than
previous releases: every visibilitymap_set is WAL-logged and bumps the
vm page's LSN; whereas in prior releases no WAL record is emitted and
the vm page's LSN is advanced to the heap page's LSN if it lags it.
So we'd better think pretty carefully before assuming that any logic
about what is safe here is true for all branches.

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

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

Re: [HACKERS] Patch review for logging hooks (CF 2012-01)

On Sun, Mar 4, 2012 at 10:45 AM, Andrew Dunstan and...@dunslane.net wrote:
 I'm just looking at this patch, and I agree, it should be testable. I'm
 wondering if it wouldn't be a good idea to have a module or set of modules
 for demonstrating and testing bits of the API that we expose. src/test/api
 or something similar? I'm not sure how we'd automate a test for this case,
 though. I guess we could use something like pg_logforward and have a UDP
 receiver catch the messages and write them to a file. Something like that
 should be possible to rig up in Perl. But all that seems a lot of work at
 this stage of the game. So the question is do we want to commit this patch
 without it?

The latest version of this patch looks sound to me.  We haven't
insisted on having even a sample application for every hook before,
let alone a regression test, so I don't think this patch needs one
either.  Now, it might be fairly said that we ought to have regression
tests for a lot more things than we do right now, but that's basically
a limitation of our regression-testing environment which the author of
this patch shouldn't be obliged to fix.

So my vote is to go ahead and commit it.

-- 
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] review: CHECK FUNCTION statement

small fix of CheckFunctionById function

Regards

p.s. Alvaro, please, send your patch and I'll merge it
		/*
		 * Connect to SPI manager
		 */
		if (SPI_connect() != SPI_OK_CONNECT)
			elog(ERROR, SPI_connect failed);

		values[0] = ObjectIdGetDatum(funcOid);
		values[1] = ObjectIdGetDatum(relid);
		values[2] = PointerGetDatum(options);
		values[3] = BoolGetDatum(fatal_errors);

		SPI_execute_with_args(sinfo.data,
		4, argtypes,
		values, nulls, 
			true, 0);

		result = SPI_processed == 0;

		if (result)
		{
			resetStringInfo(sinfo);
			appendStringInfo(sinfo, Function is valid: '%s', funcname);
			do_text_output_oneline(tstate, sinfo.data);
		}
		else
		{
			resetStringInfo(sinfo);
			appendStringInfo(sinfo, In function: %s, funcname);
			do_text_output_oneline(tstate, sinfo.data);
			
			for (i = 0; i  SPI_processed; i++)
			{
char		*query;

resetStringInfo(sinfo);
appendStringInfo(sinfo, %s:%s:%s:%s:%s,
	SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 8),
	SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 4),
	SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 2),
	SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 3),
	SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 5));

do_text_output_oneline(tstate, sinfo.data);
resetStringInfo(sinfo);

query = SPI_getvalue(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 10);
if (query != NULL)
{
	bool	isnull;
	char 	*query_line;		/* pointer to begin of processed line */
	int	query_line_caret;
	int	caret;
	bool	is_first_line = true;

	/*
	 * put any query line to separate output line. And append
	 * a curet, when is defined and related to processed rows.
	 */
	caret = SPI_getbinval(SPI_tuptable-vals[i], SPI_tuptable-tupdesc, 9, isnull);
	if (isnull)
		caret = -1;
	else
		caret;

	query_line = query;
	query_line_caret = caret;

	while (*query != '\0')
	{
		int	   len;

		if (*query == '\n')
		{
			/* now we found end of line */
			*query = '\0';
			if (is_first_line)
			{
appendStringInfo(sinfo, query:%s, query_line);
is_first_line = false;
			}
			else
appendStringInfo(sinfo,   %s, query_line);

			do_text_output_oneline(tstate, sinfo.data);
			resetStringInfo(sinfo);

			if (query_line_caret  0  caret == 0)
			{
appendStringInfo(sinfo, --%*s,
			query_line_caret, ^);
do_text_output_oneline(tstate, sinfo.data);
resetStringInfo(sinfo);
query_line_caret = 0;
			}

			/* store caret offset for next line */
			if (caret  1)
query_line_caret = caret - 1;

			/* go to next line */
			query_line = query + 1;
		}

		len = pg_mblen(query);
		query += len;

		if (caret  0)
			caret--;
	}

	/* last line output */
	if (query_line != NULL)
	{
		if (is_first_line)
		{
			appendStringInfo(sinfo, query:%s, query_line);
		}
		else
			appendStringInfo(sinfo,   %s, query_line);

		do_text_output_oneline(tstate, sinfo.data);
		resetStringInfo(sinfo);

		if (query_line_caret  0  caret == 0)
		{
			appendStringInfo(sinfo, --%*s,
		query_line_caret, ^);
			do_text_output_oneline(tstate, sinfo.data);
			resetStringInfo(sinfo);
		}
	}
}
			}
		}

-- 
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] poll: CHECK TRIGGER?

On Sat, Mar 3, 2012 at 9:23 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Uh!  Now that I read this I realize that what you're supposed to give to
 CHECK TRIGGER is the trigger name, not the function name!  In that
 light, using CHECK FUNCTION for this doesn't make a lot of sense.

 Okay, CHECK TRIGGER it is.

I confess to some bafflement about why we need dedicated syntax for
this, or even any kind of core support at all.  What would be wrong
with defining a function that takes regprocedure as an argument and
does whatever?  Sure, it's nicer syntax, but we've repeatedly rejected
patches that only provided nicer syntax on the grounds that syntax is
not free, and therefore syntax alone is not a reason to change the
core grammar.  What makes this case different?

-- 
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] poll: CHECK TRIGGER?

2012/3/5 Robert Haas robertmh...@gmail.com:
 On Sat, Mar 3, 2012 at 9:23 PM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:
 Uh!  Now that I read this I realize that what you're supposed to give to
 CHECK TRIGGER is the trigger name, not the function name!  In that
 light, using CHECK FUNCTION for this doesn't make a lot of sense.

 Okay, CHECK TRIGGER it is.

 I confess to some bafflement about why we need dedicated syntax for
 this, or even any kind of core support at all.  What would be wrong
 with defining a function that takes regprocedure as an argument and
 does whatever?  Sure, it's nicer syntax, but we've repeatedly rejected
 patches that only provided nicer syntax on the grounds that syntax is
 not free, and therefore syntax alone is not a reason to change the
 core grammar.  What makes this case different?

Fo checking trigger handler (trigger function) you have to know
trigger definition (only joined relation now), but it can be enhanced
for other tests based on trigger data.

Regards

Pavel


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

-- 
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] Our regex vs. POSIX on longest match

Robert Haas robertmh...@gmail.com writes:
 I think the right way to imagine this is as though the regular
 expression were being matched to the source text in left-to-right
 fashion.

No, it isn't.  You are headed down the garden path that leads to a
Perl-style definition-by-implementation, and in particular you are going
to end up with an implementation that fails to satisfy the POSIX
standard.  POSIX requires an *overall longest* match (at least for cases
where all quantifiers are greedy), and that sometimes means that the
quantifiers can't be processed strictly left-to-right greedy.  An
example of this is 

regression=# select substring('aabab' from '(a*(ab)*)');
 substring 
---
 aabab
(1 row)

If the a* is allowed to match as much as it wants, the (ab)* will not be
able to match at all, and then you fail to find the longest possible
overall match.

I suspect that it is possible to construct similar cases where, for an
all-non-greedy pattern, finding the overall shortest match sometimes
requires that individual quantifiers eat more than the local minimum.
I've not absorbed enough caffeine yet this morning to produce an example
though.

I probably shouldn't guess too much at Henry Spencer's thought
processes, but I think that he was looking for an extension of this
POSIX concept to mixed-greediness cases, ie you first define what the
overall RE matches and then let the individual quantifiers fight it out
as to which one gets how much of that.  The particular way he did that
is obviously leaving a lot of people unsatisfied, but I think we need to
keep looking for rules of that sort, and not revert to defining the
behavior by a search algorithm.

 I think it's right to view every RE construct as greedy unless it's got
 an explicit not-greedy flag attached to it; after all, that's the
 traditional behavior of REs from time immemorial.  Someone could
 invent a non-greedy form of alternation if they were so inclined.

I think you can do that already: (foo|bar){1,1}? (if this doesn't
result in a non-greedy alternation then it's a bug).  The notation is
a bit ugly admittedly.

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] RFC: Making TRUNCATE more MVCC-safe

On Sun, Mar 4, 2012 at 11:39 AM, Simon Riggs si...@2ndquadrant.com wrote:
 Marti, please review this latest version which has new isolation tests added.

 This does both TRUNCATE and CREATE TABLE.

I don't see any need for a GUC to control this behavior.  The current
behavior is wrong, so if we're going to choose this path to fix it,
then we should just fix it, period.  The narrow set of circumstances
in which it might be beneficial to disable this behavior doesn't seem
to me to be sufficient to justify a behavior-changing GUC.

It does not seem right that the logic for detecting the serialization
error is in heap_beginscan_internal().  Surely this is just as much of
a problem for an index-scan or index-only-scan.  We don't want to
patch all those places individually, either: I think the check should
happen right around the time we initially lock the relation and build
its relcache entry.

The actual text of the error message could use some work.  Maybe
something like could not serialize access due to concurrent DDL,
although I think we try to avoid using acronyms like DDL in
translatable strings.

-- 
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: Scaling XLog insertion (was Re: [HACKERS] Moving more work outside WALInsertLock)

2012-03-05 Thread Heikki Linnakangas


On 21.02.2012 13:19, Fujii Masao wrote:

On Sat, Feb 18, 2012 at 12:36 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com  wrote:

Attached is a new version, fixing that, and off-by-one bug you pointed out
in the slot wraparound handling. I also moved code around a bit, I think
this new division of labor between the XLogInsert subroutines is more
readable.


This patch includes not only xlog scaling improvement but also other ones.
I think it's better to extract them as separate patches and commit them first.
If we do so, the main patch would become more readable.


Good point.


For example, I think that the followings can be extracted as a separate patch.

   (1) Make walwriter try to initialize as many of the no-longer-needed
WAL buffers
   for future use as we can.


This is pretty hard to extract from the larger patch. The current code 
in master assumes that there's only one page that is currently inserted 
to, and relies on WALInsertLock being held in AdvanceXLInsertBuffer(). 
The logic with the scaling patch is quite different.



   (2) Refactor the update full_page_writes code.
   (3) Get rid of XLogCtl-Write.LogwrtResult and XLogCtl-Insert.LogwrtResult.


Attached are patches for these two items. Barring objections, I'll 
commit these.



   (4) Call TRACE_POSTGRESQL_XLOG_SWITCH() even if the xlog switch has no
work to do.


Actually, I think I'll just move it in the patch to keep the existing 
behavior.



I'm not sure if (3) makes sense. In current master, those two shared variables
are used to reduce the contention of XLogCtl-info_lck and WALWriteLock.
You think they have no effect on reducing the lock contention?


XLogCtl-Write.LogwrtResult certainly seems redundant with 
XLogCtl-LogwrtResult. There might be some value in 
XLogCtl-Insert.LogwrtResult, it's used in AdvanceXLInsertBuffer() to 
before acquiring info_lck. But I doubt that makes any difference in 
practice either. At best it's saving one spinlock acquisition per WAL 
buffer, which isn't all much compared to all the other work involved. 
(once the scaling patch is committed, this point is moot anyway)


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 266c0de..eb7932e 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -731,8 +731,7 @@ XLogInsert(RmgrId rmid, uint8 info, XLogRecData *rdata)
 	unsigned	i;
 	bool		updrqst;
 	bool		doPageWrites;
-	bool		isLogSwitch = false;
-	bool		fpwChange = false;
+	bool		isLogSwitch = (rmid == RM_XLOG_ID  info == XLOG_SWITCH);
 	uint8		info_orig = info;
 
 	/* cross-check on whether we should be here or not */
@@ -746,30 +745,11 @@ XLogInsert(RmgrId rmid, uint8 info, XLogRecData *rdata)
 	TRACE_POSTGRESQL_XLOG_INSERT(rmid, info);
 
 	/*
-	 * Handle special cases/records.
+	 * In bootstrap mode, we don't actually log anything but XLOG resources;
+	 * return a phony record pointer.
 	 */
-	if (rmid == RM_XLOG_ID)
+	if (IsBootstrapProcessingMode()  rmid != RM_XLOG_ID)
 	{
-		switch (info)
-		{
-			case XLOG_SWITCH:
-isLogSwitch = true;
-break;
-
-			case XLOG_FPW_CHANGE:
-fpwChange = true;
-break;
-
-			default:
-break;
-		}
-	}
-	else if (IsBootstrapProcessingMode())
-	{
-		/*
-		 * In bootstrap mode, we don't actually log anything but XLOG resources;
-		 * return a phony record pointer.
-		 */
 		RecPtr.xlogid = 0;
 		RecPtr.xrecoff = SizeOfXLogLongPHD;		/* start of 1st chkpt record */
 		return RecPtr;
@@ -1232,15 +1212,6 @@ begin:;
 		WriteRqst = XLogCtl-xlblocks[curridx];
 	}
 
-	/*
-	 * If the record is an XLOG_FPW_CHANGE, we update full_page_writes
-	 * in shared memory before releasing WALInsertLock. This ensures that
-	 * an XLOG_FPW_CHANGE record precedes any WAL record affected
-	 * by this change of full_page_writes.
-	 */
-	if (fpwChange)
-		Insert-fullPageWrites = fullPageWrites;
-
 	LWLockRelease(WALInsertLock);
 
 	if (updrqst)
@@ -8517,6 +8488,22 @@ UpdateFullPageWrites(void)
 	if (fullPageWrites == Insert-fullPageWrites)
 		return;
 
+	START_CRIT_SECTION();
+
+	/*
+	 * It's always safe to take full page images, even when not strictly
+	 * required, but not the other round. So if we're setting full_page_writes
+	 * to true, first set it true and then write the WAL record. If we're
+	 * setting it to false, first write the WAL record and then set the
+	 * global flag.
+	 */
+	if (fullPageWrites)
+	{
+		LWLockAcquire(WALInsertLock, LW_EXCLUSIVE);
+		Insert-fullPageWrites = true;
+		LWLockRelease(WALInsertLock);
+	}
+
 	/*
 	 * Write an XLOG_FPW_CHANGE record. This allows us to keep
 	 * track of full_page_writes during archive recovery, if required.
@@ -8532,12 +8519,15 @@ UpdateFullPageWrites(void)
 
 		XLogInsert(RM_XLOG_ID, XLOG_FPW_CHANGE, rdata);
 	}
-	else
+
+
+	if (!fullPageWrites)
 	{
 		LWLockAcquire(WALInsertLock, LW_EXCLUSIVE);
-		Insert-fullPageWrites = fullPageWrites;
+

Re: [HACKERS] poll: CHECK TRIGGER?

Robert Haas robertmh...@gmail.com writes:
 I confess to some bafflement about why we need dedicated syntax for
 this, or even any kind of core support at all.  What would be wrong
 with defining a function that takes regprocedure as an argument and
 does whatever?  Sure, it's nicer syntax, but we've repeatedly rejected
 patches that only provided nicer syntax on the grounds that syntax is
 not free, and therefore syntax alone is not a reason to change the
 core grammar.  What makes this case different?

There's definitely something to be said for that, since it entirely
eliminates the problem of providing wildcards and control over which
function(s) to check --- the user could write a SELECT from pg_proc
that slices things however he wants.

The trigger case would presumably take arguments matching pg_trigger's
primary key, viz check_trigger(trig_rel regclass, trigger_name name).

But as for needing core support, we do need to extend the API for PL
validators, so it's not like this could be done as an external project.

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] RFC: Making TRUNCATE more MVCC-safe

On Mon, Mar 5, 2012 at 4:32 PM, Robert Haas robertmh...@gmail.com wrote:
 On Sun, Mar 4, 2012 at 11:39 AM, Simon Riggs si...@2ndquadrant.com wrote:
 Marti, please review this latest version which has new isolation tests added.

 This does both TRUNCATE and CREATE TABLE.

 I don't see any need for a GUC to control this behavior.  The current
 behavior is wrong, so if we're going to choose this path to fix it,
 then we should just fix it, period.  The narrow set of circumstances
 in which it might be beneficial to disable this behavior doesn't seem
 to me to be sufficient to justify a behavior-changing GUC.

I agree behaviour is wrong, the only question is whether our users
rely in some way on that behaviour. Given the long discussion on that
point earlier I thought it best to add a GUC. Easy to remove, now or
later.

 It does not seem right that the logic for detecting the serialization
 error is in heap_beginscan_internal().  Surely this is just as much of
 a problem for an index-scan or index-only-scan.

err, very good point. Doh.

 We don't want to
 patch all those places individually, either: I think the check should
 happen right around the time we initially lock the relation and build
 its relcache entry.

OK, that makes sense and works if we need to rebuild relcache.

 The actual text of the error message could use some work.  Maybe
 something like could not serialize access due to concurrent DDL,
 although I think we try to avoid using acronyms like DDL in
 translatable strings.

Yeh that was designed-to-be-replaced text. We do use DDL already
elsewhere without really explaining it; its also one of those acronyms
that doesn't actually explain what it really means very well. So I
like the phrase you suggest.

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

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

Re: [HACKERS] Patch review for logging hooks (CF 2012-01)

Robert Haas robertmh...@gmail.com writes:
 On Sun, Mar 4, 2012 at 10:45 AM, Andrew Dunstan and...@dunslane.net wrote:
 I'm just looking at this patch, and I agree, it should be testable. I'm
 wondering if it wouldn't be a good idea to have a module or set of modules
 for demonstrating and testing bits of the API that we expose. src/test/api
 or something similar? I'm not sure how we'd automate a test for this case,
 though. I guess we could use something like pg_logforward and have a UDP
 receiver catch the messages and write them to a file. Something like that
 should be possible to rig up in Perl. But all that seems a lot of work at
 this stage of the game. So the question is do we want to commit this patch
 without it?

 The latest version of this patch looks sound to me.  We haven't
 insisted on having even a sample application for every hook before,
 let alone a regression test, so I don't think this patch needs one
 either.

What we've generally asked for with hooks is a working sample usage of
the hook, just as a cross-check that something useful can be done with
it and you didn't overlook any obvious usability problems.  I agree that
a regression test is often not practical, especially not if you're not
prepared to create a whole contrib module to provide a sample usage.

In the case at hand, ISTM there are some usability questions around
where/when the hook is called: in particular, if I'm reading it right,
the hook could not override a log_min_messages-based decision that a
given message is not to be emitted.  Do we care?  Also, if the hook
is meant to be able to change the data that gets logged, as seems to be
the case, do we care that it would also affect what gets sent to the
client?

I'd like to see a spec for exactly which fields of ErrorData the hook is
allowed to change, and some rationale.

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: Scaling XLog insertion (was Re: [HACKERS] Moving more work outside WALInsertLock)

Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
 On 21.02.2012 13:19, Fujii Masao wrote:
 In some places, the spinlock insertpos_lck is taken while another
 spinlock info_lck is being held. Is this OK? What if unfortunately
 inner spinlock takes long to be taken?

 Hmm, that's only done at a checkpoint (and a restartpoint), so I doubt 
 that's a big issue in practice. We had the same pattern before the 
 patch, just with WALInsertLock instead of insertpos_lck. Holding a 
 spinlock longer is much worse than holding a lwlock longer, but 
 nevertheless I don't think that's a problem.

No, that's NOT okay.  A spinlock is only supposed to be held across a
short straight-line sequence of instructions.  Something that could
involve a spin loop, or worse a sleep() kernel call, is right out.
Please change this.

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

On Sun, Mar 4, 2012 at 1:20 AM, Jeff Janes jeff.ja...@gmail.com wrote:

 Does this patch have any user-visible effect?  I thought it would make
 pg_last_xact_replay_timestamp() advance, but it does not seem to.  I
 looked through the source a bit, and as best I can tell this only sets
 some internal state which is never used, except under DEBUG2

Thanks for the review. I'll look into that.

-- 
 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] Our regex vs. POSIX on longest match

On Mon, Mar 5, 2012 at 11:28 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 I think the right way to imagine this is as though the regular
 expression were being matched to the source text in left-to-right
 fashion.

 No, it isn't.  You are headed down the garden path that leads to a
 Perl-style definition-by-implementation, and in particular you are going
 to end up with an implementation that fails to satisfy the POSIX
 standard.  POSIX requires an *overall longest* match (at least for cases
 where all quantifiers are greedy), and that sometimes means that the
 quantifiers can't be processed strictly left-to-right greedy.  An
 example of this is

 regression=# select substring('aabab' from '(a*(ab)*)');
  substring
 ---
  aabab
 (1 row)

 If the a* is allowed to match as much as it wants, the (ab)* will not be
 able to match at all, and then you fail to find the longest possible
 overall match.

Oh.  Right.

 I suspect that it is possible to construct similar cases where, for an
 all-non-greedy pattern, finding the overall shortest match sometimes
 requires that individual quantifiers eat more than the local minimum.
 I've not absorbed enough caffeine yet this morning to produce an example
 though.

Probably true.   I guess, then, that the issue here is that there
isn't really any principled way to decide whether the RE overall
should be greedy or non-greedy.  And similarly with every sub-RE.  The
problem with the non-greedy quantifiers is that they apply only to
the quantified bit specifically, which leaves us guessing as to the
user's intent with regards to everything else.

-- 
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] RFC: Making TRUNCATE more MVCC-safe

On Mon, Mar 5, 2012 at 4:46 PM, Simon Riggs si...@2ndquadrant.com wrote:

 It does not seem right that the logic for detecting the serialization
 error is in heap_beginscan_internal().  Surely this is just as much of
 a problem for an index-scan or index-only-scan.

 err, very good point. Doh.

 We don't want to
 patch all those places individually, either: I think the check should
 happen right around the time we initially lock the relation and build
 its relcache entry.

 OK, that makes sense and works if we need to rebuild relcache.

Except the reason to do it at the start of the scan is that is the
first time a specific snapshot has been associated with a relation and
also the last point we can apply the check before the errant behaviour
occurs.

If we reject locks against tables that might be used against an
illegal snapshot then we could easily prevent valid snapshot use cases
when a transaction has multiple snapshots, one illegal, one not.

We can certainly have a looser test when we first get the lock and
then another test later, but I don't think we can avoid making all
scans apply this test. And while I'm there, we have to add tests for
things like index build scans.

-- 
 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] RFC: Making TRUNCATE more MVCC-safe

On Mon, Mar 5, 2012 at 11:46 AM, Simon Riggs si...@2ndquadrant.com wrote:
 I agree behaviour is wrong, the only question is whether our users
 rely in some way on that behaviour. Given the long discussion on that
 point earlier I thought it best to add a GUC. Easy to remove, now or
 later.

AFAICT, all the discussion upthread was about whether we ought to be
trying to implement this in some entirely-different way that doesn't
rely on storing XIDs in the catalog.  I have a feeling that there are
a whole lot more cases like this and that we're in for a lot of
unpleasant nastiness if we go very far down this route; pg_constraint
is another one, as SnapshotNow can see constraints that may not be
valid under the query's MVCC snapshot.  On the other hand, if someone
comes up with a better way, I suppose we can always rip this out.  In
any case, I don't remember anyone saying that this needed to be
configurable.

Speaking of that, though, I have one further thought on this: we need
to be absolutely certain that autovacuum is going to prevent this XID
value from wrapping around.  I suppose this is safe since, even if
autovacuum is turned off, we'll forcibly kick it off every so often to
advance relfrozenxid, and that will reset relvalidxid while it's
there.  But then again on second thought, what if relvalidxid lags
relfrozenxid?  Then the emergency autovacuum might not kick in until
relvalidxid has already wrapped around.  I think that could happen
after a TRUNCATE, perhaps, since I think that would leave relfrozenxid
alone while advancing relvalidxid.

-- 
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] RFC: Making TRUNCATE more MVCC-safe

On Mon, Mar 5, 2012 at 12:42 PM, Simon Riggs si...@2ndquadrant.com wrote:
 On Mon, Mar 5, 2012 at 4:46 PM, Simon Riggs si...@2ndquadrant.com wrote:
 It does not seem right that the logic for detecting the serialization
 error is in heap_beginscan_internal().  Surely this is just as much of
 a problem for an index-scan or index-only-scan.

 err, very good point. Doh.

 We don't want to
 patch all those places individually, either: I think the check should
 happen right around the time we initially lock the relation and build
 its relcache entry.

 OK, that makes sense and works if we need to rebuild relcache.

 Except the reason to do it at the start of the scan is that is the
 first time a specific snapshot has been associated with a relation and
 also the last point we can apply the check before the errant behaviour
 occurs.

 If we reject locks against tables that might be used against an
 illegal snapshot then we could easily prevent valid snapshot use cases
 when a transaction has multiple snapshots, one illegal, one not.

 We can certainly have a looser test when we first get the lock and
 then another test later, but I don't think we can avoid making all
 scans apply this test. And while I'm there, we have to add tests for
 things like index build scans.

Well, there's no point that I can see in having two checks.  I just
dislike the idea that we have to remember to add this check for every
method of accessing the relation - doesn't seem terribly future-proof.
 It gets even worse if you start adding checks to DDL code paths - if
we're going to do that, we really need to cover them all, and that
doesn't seem very practical if they're going to spread out all over
the place.

I don't understand your comment that a snapshot doesn't get associated
with a relation until scan time.  I believe we associated a snapshot
with each query before we even know what relations are involved; that
query then gets passed down to all the individual scans.  The query
also opens and locks those relations.  We ought to be able to arrange
for the query snapshot to be cross-checked at that point, rather than
waiting until scan-start time.

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

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

Re: [HACKERS] Patch review for logging hooks (CF 2012-01)

2012-03-05 Thread Andrew Dunstan




On 03/05/2012 12:08 PM, Tom Lane wrote:

Robert Haasrobertmh...@gmail.com  writes:

On Sun, Mar 4, 2012 at 10:45 AM, Andrew Dunstanand...@dunslane.net  wrote:

I'm just looking at this patch, and I agree, it should be testable. I'm
wondering if it wouldn't be a good idea to have a module or set of modules
for demonstrating and testing bits of the API that we expose. src/test/api
or something similar? I'm not sure how we'd automate a test for this case,
though. I guess we could use something like pg_logforward and have a UDP
receiver catch the messages and write them to a file. Something like that
should be possible to rig up in Perl. But all that seems a lot of work at
this stage of the game. So the question is do we want to commit this patch
without it?

The latest version of this patch looks sound to me.  We haven't
insisted on having even a sample application for every hook before,
let alone a regression test, so I don't think this patch needs one
either.

What we've generally asked for with hooks is a working sample usage of
the hook, just as a cross-check that something useful can be done with
it and you didn't overlook any obvious usability problems.  I agree that
a regression test is often not practical, especially not if you're not
prepared to create a whole contrib module to provide a sample usage.

In the case at hand, ISTM there are some usability questions around
where/when the hook is called: in particular, if I'm reading it right,
the hook could not override a log_min_messages-based decision that a
given message is not to be emitted.  Do we care?



That's what I understand too. We could relax that at some stage in the 
future if we had a requirement, I guess.




   Also, if the hook
is meant to be able to change the data that gets logged, as seems to be
the case, do we care that it would also affect what gets sent to the
client?

I'd like to see a spec for exactly which fields of ErrorData the hook is
allowed to change, and some rationale.




Good question. I'd somewhat be inclined to say that it should only be 
able to change output_to_server and output_to_client, and possibly only 
to change them from true to false (i.e. I'm not sure the hook should be 
able to induce more verbose logging.) But maybe that's too restrictive. 
I doubt we can enforce good behaviour, though, only state that if you 
break things you get to keep all the pieces.


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] poll: CHECK TRIGGER?

On Mon, Mar 5, 2012 at 11:41 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 I confess to some bafflement about why we need dedicated syntax for
 this, or even any kind of core support at all.  What would be wrong
 with defining a function that takes regprocedure as an argument and
 does whatever?  Sure, it's nicer syntax, but we've repeatedly rejected
 patches that only provided nicer syntax on the grounds that syntax is
 not free, and therefore syntax alone is not a reason to change the
 core grammar.  What makes this case different?

 There's definitely something to be said for that, since it entirely
 eliminates the problem of providing wildcards and control over which
 function(s) to check --- the user could write a SELECT from pg_proc
 that slices things however he wants.
 The trigger case would presumably take arguments matching pg_trigger's
 primary key, viz check_trigger(trig_rel regclass, trigger_name name).

Yes...

 But as for needing core support, we do need to extend the API for PL
 validators, so it's not like this could be done as an external project.

Well, the plpgsql extension could install a function
pg_check_plpgsql_function() that only works on PL/pgsql functions, and
other procedural languages could do the same at their option.  I think
we only need to extend the API if we want to provide a dispatch
function so that you can say check this function, whatever language
it's written in and have the right checker get called.  But since
we've already talked about the possibility of having more than one
checker per language doing different kinds of checks, I'm not even
sure that the checker for a language is a concept that we want to
invent.

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

Shigeru Hanada shigeru.han...@gmail.com writes:
 So, just renaming create_foreignscan_path to plural form seems missing
 the point.

I agree that that wouldn't be an improvement.  What bothers me about the
patch's version of this function is that it just creates a content-free
Path node and leaves it to the caller to fill in everything.  That
doesn't accomplish much, and it leaves the caller very exposed to errors
of omission.  It's also unlike the other create_xxx_path functions,
which generally hand back a completed Path ready to pass to add_path.

I'm inclined to think that if we provide this function in core at all,
it should take a parameter list long enough to let it fill in the Path
completely.  That would imply that any future changes in Path structs
would result in a change in the parameter list, which would break
callers --- but it would break them in an obvious way that the C
compiler would complain about.  If we leave it as-is, those same callers
would be broken silently, because they'd just be failing to fill in
the new Path fields.

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 review for logging hooks (CF 2012-01)

On Mon, Mar 5, 2012 at 12:50 PM, Andrew Dunstan and...@dunslane.net wrote:
 The latest version of this patch looks sound to me.  We haven't
 insisted on having even a sample application for every hook before,
 let alone a regression test, so I don't think this patch needs one
 either.

 What we've generally asked for with hooks is a working sample usage of
 the hook, just as a cross-check that something useful can be done with
 it and you didn't overlook any obvious usability problems.  I agree that
 a regression test is often not practical, especially not if you're not
 prepared to create a whole contrib module to provide a sample usage.

 In the case at hand, ISTM there are some usability questions around
 where/when the hook is called: in particular, if I'm reading it right,
 the hook could not override a log_min_messages-based decision that a
 given message is not to be emitted.  Do we care?

 That's what I understand too. We could relax that at some stage in the
 future if we had a requirement, I guess.

   Also, if the hook
 is meant to be able to change the data that gets logged, as seems to be
 the case, do we care that it would also affect what gets sent to the
 client?

 I'd like to see a spec for exactly which fields of ErrorData the hook is
 allowed to change, and some rationale.


 Good question. I'd somewhat be inclined to say that it should only be able
 to change output_to_server and output_to_client, and possibly only to change
 them from true to false (i.e. I'm not sure the hook should be able to induce
 more verbose logging.) But maybe that's too restrictive. I doubt we can
 enforce good behaviour, though, only state that if you break things you get
 to keep all the pieces.

It sort of looks like it's intended to apply only to server output, so
I'd find it odd to say that it should be able to mess with
output_to_client.

-- 
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] Parameterized-path cost comparisons need some work

Robert Haas robertmh...@gmail.com writes:
 On Sun, Mar 4, 2012 at 12:20 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 One annoying thing about that is that it will reduce the usefulness of
 add_path_precheck, because that's called before we compute the rowcount
 estimates (and indeed not having to make the rowcount estimates is one
 of the major savings from the precheck).  I think what we'll have to do
 is assume that a difference in parameterization could result in a
 difference in rowcount, and hence only a dominant path with exactly the
 same parameterization can result in failing the precheck.

 I wish we had some way of figuring out how much this - and maybe some
 of the other new planning possibilities like index-only scans - were
 going to cost us on typical medium-to-large join problems.  In the
 absence of real-world data it's hard to know how worried we should be.

I have been doing testing against a couple of complex queries supplied
by Kevin and Andres.  It'd be nice to have a larger sample though ...

I'm a bit concerned that this change will end up removing most of the
usefulness of add_path_precheck.  I would not actually cry if that went
away again, because hacking things like that greatly complicated the API
of the join cost functions.  But it's nervous-making to be making
decisions like that on the basis of rather small sets of queries.

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] poll: CHECK TRIGGER?

2012/3/5 Robert Haas robertmh...@gmail.com:
 On Mon, Mar 5, 2012 at 11:41 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 I confess to some bafflement about why we need dedicated syntax for
 this, or even any kind of core support at all.  What would be wrong
 with defining a function that takes regprocedure as an argument and
 does whatever?  Sure, it's nicer syntax, but we've repeatedly rejected
 patches that only provided nicer syntax on the grounds that syntax is
 not free, and therefore syntax alone is not a reason to change the
 core grammar.  What makes this case different?

 There's definitely something to be said for that, since it entirely
 eliminates the problem of providing wildcards and control over which
 function(s) to check --- the user could write a SELECT from pg_proc
 that slices things however he wants.
 The trigger case would presumably take arguments matching pg_trigger's
 primary key, viz check_trigger(trig_rel regclass, trigger_name name).

 Yes...

 But as for needing core support, we do need to extend the API for PL
 validators, so it's not like this could be done as an external project.

 Well, the plpgsql extension could install a function
 pg_check_plpgsql_function() that only works on PL/pgsql functions, and
 other procedural languages could do the same at their option.  I think
 we only need to extend the API if we want to provide a dispatch
 function so that you can say check this function, whatever language
 it's written in and have the right checker get called.  But since
 we've already talked about the possibility of having more than one
 checker per language doing different kinds of checks, I'm not even
 sure that the checker for a language is a concept that we want to
 invent.

There is not multiple PL checker function - or I don't know about it.

Pavel





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

-- 
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] review: CHECK FUNCTION statement

Excerpts from Pavel Stehule's message of lun mar 05 13:02:50 -0300 2012:
 small fix of CheckFunctionById function
 
 Regards
 
 p.s. Alvaro, please, send your patch and I'll merge it

Here it is, with your changes already merged.  I also added back the
new reference doc files which were dropped after the 2012-01-01 version.
Note I haven't touched or read the plpgsql checker code at all (only
some automatic indentation changes IIRC).  I haven't verified the
regression tests either.

FWIW I'm not going to participate in the other thread; neither I am
going to work any more on this patch until the other thread sees some
reasonable conclusion.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


check_function-2012-03-05-1.patch.gz
Description: GNU Zip compressed data

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

Re: [HACKERS] Parameterized-path cost comparisons need some work

On Mon, Mar 5, 2012 at 1:02 PM, Tom Lane t...@sss.pgh.pa.us wrote:
  But it's nervous-making to be making
 decisions like that on the basis of rather small sets of queries.

I heartily agree.

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

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

Re: [HACKERS] Patch review for logging hooks (CF 2012-01)

Robert Haas robertmh...@gmail.com writes:
 On Mon, Mar 5, 2012 at 12:50 PM, Andrew Dunstan and...@dunslane.net wrote:
 I'd like to see a spec for exactly which fields of ErrorData the hook is
 allowed to change, and some rationale.

 Good question. I'd somewhat be inclined to say that it should only be able
 to change output_to_server and output_to_client, and possibly only to change
 them from true to false (i.e. I'm not sure the hook should be able to induce
 more verbose logging.) But maybe that's too restrictive. I doubt we can
 enforce good behaviour, though, only state that if you break things you get
 to keep all the pieces.

The reason it can't sensibly expect to change them from off to on is
that control will never get here in the first place if they're both off.
That is a feature not a bug: the ereport mechanisms are designed to skip
most of the message-construction work for messages that are so low
priority that they're not going to get printed anywhere.  If we wanted
the hook to be able to override that, it would have to be called from
errstart and it would then have much less information to work with.

 It sort of looks like it's intended to apply only to server output, so
 I'd find it odd to say that it should be able to mess with
 output_to_client.

OK, so let's document that the only supported change in ErrorData is to
turn output_to_server from on to off.  I can see how that constitutes
a potential feature: the hook might be intended as a filter that allows
logging or not logging according to some rule not supported by the core
system.  You'd have to ensure that log_min_messages is set low enough
for all desired messages to get generated in the first place, but then
the hook could control things beyond that.  I can also imagine a hook
that's meant as some sort of aggregator, so that it would accumulate a
summary form of messages that it then told the core not to print.

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] performance results on IBM POWER7

On Thu, Mar 1, 2012 at 11:38 AM, Robert Haas robertmh...@gmail.com wrote:
 On Thu, Mar 1, 2012 at 11:23 AM, Ants Aasma ants.aa...@eesti.ee wrote:
 On Thu, Mar 1, 2012 at 4:54 PM, Robert Haas robertmh...@gmail.com wrote:
 ... After that I think maybe some testing of the
 remaining CommitFest patches might be in order (though personally I'd
 like to wrap this CommitFest up fairly soon) to see if any of those
 improve things.

 Besides performance testing, could you check how clocksources behave
 on this kind of machine?
 You can find pg_test_timing tool attached here:
 http://archives.postgresql.org/pgsql-hackers/2012-01/msg00937.php

 To see which clocksources are available, you can do:
 # cat /sys/devices/system/clocksource/clocksource0/available_clocksource
 To switch the clocksource, just write the desired clocksource like this:
 # echo hpet  
 /sys/devices/system/clocksource/clocksource0/current_clocksource

 Sure, I'll check that as soon as it's back up.

It seems that timebase is the only available clock source.
pg_test_timing says:

Testing timing overhead for 3 seconds.
Per timing duration including loop overhead: 38.47 ns
Histogram of timing durations:
usec:  count   percent
   32:  6  0.1%
   16:  4  0.1%
8:  8  0.1%
4:282  0.00036%
2:2999189  3.84628%
1:   74976816 96.15333%

-- 
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] foreign key locks, 2nd attempt

On Mon, Feb 27, 2012 at 2:47 AM, Robert Haas robertmh...@gmail.com wrote:
 On Thu, Feb 23, 2012 at 11:01 AM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:
 This
 seems like a horrid mess that's going to be unsustainable both from a
 complexity and a performance standpoint.  The only reason multixacts
 were tolerable at all was that they had only one semantics.  Changing
 it so that maybe a multixact represents an actual updater and maybe
 it doesn't is not sane.

 As far as complexity, yeah, it's a lot more complex now -- no question
 about that.

 Regarding performance, the good thing about this patch is that if you
 have an operation that used to block, it might now not block.  So maybe
 multixact-related operation is a bit slower than before, but if it
 allows you to continue operating rather than sit waiting until some
 other transaction releases you, it's much better.

 That's probably true, although there is some deferred cost that is
 hard to account for.  You might not block immediately, but then later
 somebody might block either because the mxact SLRU now needs fsyncs or
 because they've got to decode an mxid long after the relevant segment
 has been evicted from the SLRU buffers.  In general, it's hard to
 bound that latter cost, because you only avoid blocking once (when the
 initial update happens) but you might pay the extra cost of decoding
 the mxid as many times as the row is read, which could be arbitrarily
 many.  How much of a problem that is in practice, I'm not completely
 sure, but it has worried me before and it still does.  In the worst
 case scenario, a handful of frequently-accessed rows with MXIDs all of
 whose members are dead except for the UPDATE they contain could result
 in continual SLRU cache-thrashing.

Cases I regularly see involve wait times of many seconds.

When this patch helps, it will help performance by algorithmic gains,
so perhaps x10-100.

That can and should be demonstrated though, I agree.

 From a performance standpoint, we really need to think not only about
 the cases where the patch wins, but also, and maybe more importantly,
 the cases where it loses.  There are some cases where the current
 mechanism, use SHARE locks for foreign keys, is adequate.  In
 particular, it's adequate whenever the parent table is not updated at
 all, or only very lightly.  I believe that those people will pay
 somewhat more with this patch, and especially in any case where
 backends end up waiting for fsyncs in order to create new mxids, but
 also just because I think this patch will have the effect of
 increasing the space consumed by each individual mxid, which imposes a
 distributed cost of its own.

That is a concern also.

It's taken me a while reviewing the patch to realise that space usage
is actually 4 times worse than before.

 I think we should avoid having a theoretical argument about how
 serious these problems are; instead, you should try to construct
 somewhat-realistic worst case scenarios and benchmark them.  Tom's
 complaint about code complexity is basically a question of opinion, so
 I don't know how to evaluate that objectively, but performance is
 something we can measure.  We might still disagree on the
 interpretation of the results, but I still think having some real
 numbers to talk about based on carefully-thought-out test cases would
 advance the debate.

It's a shame that the isolation tester can't be used directly by
pgbench - I think we need something similar for performance regression
testing.

So yes, performance testing is required.

-- 
 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] foreign key locks, 2nd attempt


Excerpts from Simon Riggs's message of lun mar 05 15:28:59 -0300 2012:
 
 On Mon, Feb 27, 2012 at 2:47 AM, Robert Haas robertmh...@gmail.com wrote:

  From a performance standpoint, we really need to think not only about
  the cases where the patch wins, but also, and maybe more importantly,
  the cases where it loses.  There are some cases where the current
  mechanism, use SHARE locks for foreign keys, is adequate.  In
  particular, it's adequate whenever the parent table is not updated at
  all, or only very lightly.  I believe that those people will pay
  somewhat more with this patch, and especially in any case where
  backends end up waiting for fsyncs in order to create new mxids, but
  also just because I think this patch will have the effect of
  increasing the space consumed by each individual mxid, which imposes a
  distributed cost of its own.
 
 That is a concern also.
 
 It's taken me a while reviewing the patch to realise that space usage
 is actually 4 times worse than before.

Eh.  You're probably misreading something.  Previously each member of a
multixact used 4 bytes (the size of an Xid).  With the current patch a
member uses 5 bytes (same plus a flags byte).  An earlier version used
4.25 bytes per multi, which I increased to leave space for future
expansion.

So it's 1.25x worse, not 4x worse.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] ECPG FETCH readahead

2012-03-05 Thread Noah Misch

On Sun, Mar 04, 2012 at 05:16:06PM +0100, Michael Meskes wrote:
 On Fri, Mar 02, 2012 at 11:41:05AM -0500, Noah Misch wrote:
  I suggest enabling the feature by default but drastically reducing the 
  default
  readahead chunk size from 256 to, say, 5.  That still reduces the FETCH 
  round
  trip overhead by 80%, but it's small enough not to attract pathological
  behavior on a workload where each row is a 10 MiB document.  I would not 
  offer
  an ecpg-time option to disable the feature per se.  Instead, let the user 
  set
  the default chunk size at ecpg time.  A setting of 1 effectively disables 
  the
  feature, though one could later re-enable it with ECPGFETCHSZ.
 
 Using 1 to effectively disable the feature is fine with me, but I strongly
 object any default enabling this feature. It's farily easy to create cases 
 with
 pathological behaviour and this features is not standard by any means. I 
 figure
 a normal programmer would expect only one row being transfered when fetching
 one.

On further reflection, I agree with you here.  The prospect for queries that
call volatile functions changed my mind; they would exhibit different
functional behavior under readahead.  We mustn't silently give affected
programs different semantics.

Thanks,
nm

-- 
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] ECPG FETCH readahead

2012-03-05 Thread Noah Misch

On Sun, Mar 04, 2012 at 04:33:32PM +0100, Boszormenyi Zoltan wrote:
 2012-03-02 17:41 keltez?ssel, Noah Misch ?rta:
  On Thu, Dec 29, 2011 at 10:46:23AM +0100, Boszormenyi Zoltan wrote:

  I suggest enabling the feature by default but drastically reducing the 
  default
  readahead chunk size from 256 to, say, 5. 
 
 
That still reduces the FETCH round
  trip overhead by 80%, but it's small enough not to attract pathological
  behavior on a workload where each row is a 10 MiB document.
 
 I see. How about 8? Nice round power of 2 value, still small and avoids
 87.5% of overhead.

Having pondered the matter further, I now agree with Michael that the feature
should stay disabled by default.  See my response to him for rationale.
Assuming that conclusion holds, we can recommended a higher value to users who
enable the feature at all.  Your former proposal of 256 seems fine.

 BTW, the default disabled behaviour was to avoid make check breakage,
 see below.
 
I would not offer
  an ecpg-time option to disable the feature per se.  Instead, let the user 
  set
  the default chunk size at ecpg time.  A setting of 1 effectively disables 
  the
  feature, though one could later re-enable it with ECPGFETCHSZ.
 
 This means all code previously going through ECPGdo() would go through
 ECPGopen()/ECPGfetch()/ECPGclose(). This is more intrusive and all
 regression tests that were only testing certain features would also
 test the readahead feature, too.

It's a good sort of intrusiveness, reducing the likelihood of introducing bugs
basically unrelated to readahead that happen to afflict only ECPGdo() or only
the cursor.c interfaces.  Let's indeed not have any preexisting test cases use
readahead per se, but having them use the cursor.c interfaces anyway will
build confidence in the new code.  The churn in expected debug output isn't
ideal, but I don't prefer the alternative of segmenting the implementation for
the sake of the test cases.

 Also, the test for WHERE CURRENT OF at ecpg time would have to be done
 at runtime, possibly making previously working code fail if ECPGFETCHSZ is 
 enabled.

Good point.

 How about still allowing NO READAHEAD cursors that compile into plain 
 ECPGdo()?
 This way, ECPGFETCHSZ don't interfere with WHERE CURRENT OF. But this would
 mean code changes everywhere where WHERE CURRENT OF is used.

ECPGFETCHSZ should only affect cursors that make no explicit mention of
READAHEAD.  I'm not sure whether that should mean actually routing READHEAD 1
cursors through ECPGdo() or simply making sure that cursor.c achieves the same
outcome; see later for a possible reason to still do the latter.

 Or how about a new feature in the backend, so ECPG can do
 UPDATE/DELETE ... WHERE OFFSET N OF cursor
 and the offset of computed from the actual cursor position and the position 
 known
 by the application? This way an app can do readahead and do work on rows 
 collected
 by the cursor with WHERE CURRENT OF which gets converted to WHERE OFFSET OF
 behind the scenes.

That's a neat idea, but I would expect obstacles threatening our ability to
use it automatically for readahead.  You would have to make the cursor a
SCROLL cursor.  We'll often pass a negative offset, making the operation fail
if the cursor query used FOR UPDATE.  Volatile functions in the query will get
more calls.  That's assuming the operation will map internally to something
like MOVE N; UPDATE ... WHERE CURRENT OF; MOVE -N.  You might come up with
innovations to mitigate those obstacles, but those innovations would probably
also apply to MOVE/FETCH.  In any event, this would constitute a substantive
patch in its own right.


One way out of trouble here is to make WHERE CURRENT OF imply READHEAD
1/READHEAD 0 (incidentally, perhaps those two should be synonyms) on the
affected cursor.  If the cursor has some other readahead quantity declared
explicitly, throw an error during preprocessing.

Failing a reasonable resolution, I'm prepared to withdraw my suggestion of
making ECPGFETCHSZ always-usable.  It's nice to have, not critical.

  +bool
  +ECPGopen(const int lineno, const int compat, const int force_indicator,
  +  const char *connection_name, const bool questionmarks,
  +  const char *curname, const int st, const char *query, ...)
  +{
  +  va_list args;
  +  boolret, scrollable;
  +  char   *new_query, *ptr, *whold, *noscroll, *scroll, *dollar0;
  +  struct sqlca_t *sqlca = ECPGget_sqlca();
  +
  +  if (!query)
  +  {
  +  ecpg_raise(lineno, ECPG_EMPTY, 
  ECPG_SQLSTATE_ECPG_INTERNAL_ERROR, NULL);
  +  return false;
  +  }
  +  ptr = strstr(query, for );
  +  if (!ptr)
  +  {
  +  ecpg_raise(lineno, ECPG_INVALID_STMT, 
  ECPG_SQLSTATE_ECPG_INTERNAL_ERROR, NULL);
  +  return false;
  +  }
  +  whold = strstr(query, with hold );
  +  dollar0 = strstr(query, $0);
  +
  +  noscroll = strstr(query, no scroll );
  +  scroll = strstr(query, scroll );
  A

[HACKERS] elegant and effective way for running jobs inside a database

Dear Developers,
   I am looking for elegant and effective way for running jobs inside a
database or cluster - for now I can not find that solution.
OK if You say use cron or pgAgent I say I know that solutions, but
the are not effective and elegant. Compilation of pgAgent is a pain
(especially wxWidgets usage on system with no X) - it can run jobs with
minimal 60s periods but what when someone needs run it faster for eg.
with 5s period ? Of course using cron I can do that but it is not
effective and elegant solution. Why PostgreSQL can not have so elegant
solution like Oracle database ? I am working with Oracle databases for
many years, but I like much more PostgreSQL project but this one
thing... I can not understand - the lack of jobs inside the database...

Best regards,
Artur


0xAF4A859D.asc
Description: application/pgp-keys

-- 
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] RFC: Making TRUNCATE more MVCC-safe

2012-03-05 Thread Noah Misch

On Mon, Feb 13, 2012 at 09:29:56AM -0500, Robert Haas wrote:
 On Fri, Feb 10, 2012 at 11:46 PM, Noah Misch n...@leadboat.com wrote:
  I've yet to see an MVCC anomaly that one can reproduce at REPEATABLE READ 
  and
  not at READ COMMITTED. ?They tend to be narrow race conditions at READ
  COMMITTED, yet easy to demonstrate at REPEATABLE READ. ?Related:
  http://archives.postgresql.org/pgsql-performance/2011-02/msg00451.php
 
 Yeah.  Well, that's actually an interesting example, because it
 illustrates how general this problem is.  We could potentially get
 ourselves into a situation where just about every system catalog table
 needs an xmin field to store the point at which the object came into
 existence - or for that matter, was updated.

I can see this strategy applying to many relation-pertinent system catalogs.
Do you foresee applications to non-relation catalogs?

In any event, I think a pg_class.relvalidxmin is the right starting point.
One might imagine a family of relvalidxmin, convalidxmin, indcheckxmin
(already exists), inhvalidxmin, and attvalidxmin.  relvalidxmin is like the
AccessExclusiveLock of that family; it necessarily blocks everything that
might impugn the others.  The value in extending this to more catalogs is the
ability to narrow the impact of failing the check.  A failed indcheckxmin
comparison merely excludes plans involving the index.  A failed inhvalidxmin
check might just skip recursion to the table in question.  Those are further
refinements, much like using weaker heavyweight lock types.

 But it's not quite the
 same as the xmin of the row itself, because some updates might be
 judged not to matter.  There could also be intermediate cases where
 updates are invalidating for some purposes but not others.  I think
 we'd better get our hands around more of the problem space before we
 start trying to engineer solutions.

I'm not seeing that problem.  Any operation that would update some xmin
horizon should set it to the greater of its current value and the value the
operation needs for its own correctness.  If you have something in mind that
needs more, could you elaborate?

  Incidentally, people use READ COMMITTED because they don't question the
  default, not because they know hazards of REPEATABLE READ. ?I don't know the
  bustedness you speak of; could we improve the documentation to inform folks?
 
 The example that I remember was related to SELECT FOR UPDATE/SELECT
 FOR SHARE.  The idea of those statements is that you want to prevent
 the row from being updated or deleted until some other concurrent
 action is complete; for example, in the case of a foreign key, we'd
 like to prevent the referenced row from being deleted or updated in
 the relevant columns until the inserting transaction is committed.
 But it doesn't work, because when the updating or deleting process
 gets done with the lock wait, they are still using the same snapshot
 as before, and merrily do exactly the the thing that the lock-wait was
 supposed to prevent.  If an actual UPDATE is used, it's safe (I
 think): anyone who was going to UPDATE or DELETE the row will fail
 with some kind of serialization error.  But a SELECT FOR UPDATE that
 commits is treated more like an UPDATE that rolls back: it's as if the
 lock never existed.  Someone (Florian?) proposed a patch to change
 this, but it seemed problematic for reasons I no longer exactly
 remember.

Thanks.  I vaguely remember that thread.

nm

-- 
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] foreign key locks, 2nd attempt

On Mon, Mar 5, 2012 at 6:37 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:

 Excerpts from Simon Riggs's message of lun mar 05 15:28:59 -0300 2012:

 On Mon, Feb 27, 2012 at 2:47 AM, Robert Haas robertmh...@gmail.com wrote:

  From a performance standpoint, we really need to think not only about
  the cases where the patch wins, but also, and maybe more importantly,
  the cases where it loses.  There are some cases where the current
  mechanism, use SHARE locks for foreign keys, is adequate.  In
  particular, it's adequate whenever the parent table is not updated at
  all, or only very lightly.  I believe that those people will pay
  somewhat more with this patch, and especially in any case where
  backends end up waiting for fsyncs in order to create new mxids, but
  also just because I think this patch will have the effect of
  increasing the space consumed by each individual mxid, which imposes a
  distributed cost of its own.

 That is a concern also.

 It's taken me a while reviewing the patch to realise that space usage
 is actually 4 times worse than before.

 Eh.  You're probably misreading something.  Previously each member of a
 multixact used 4 bytes (the size of an Xid).  With the current patch a
 member uses 5 bytes (same plus a flags byte).  An earlier version used
 4.25 bytes per multi, which I increased to leave space for future
 expansion.

 So it's 1.25x worse, not 4x worse.

Thanks for correcting me. That sounds better.

It does however, illustrate my next review comment which is that the
comments and README items are sorely lacking here. It's quite hard to
see how it works, let along comment on major design decisions. It
would help myself and others immensely if we could improve that.

Is there a working copy on a git repo? Easier than waiting for next
versions of a patch.

My other comments so far are

* some permutations commented out - no comments as to why
Something of a fault with the isolation tester that it just shows
output, there's no way to record expected output in the spec

Comments required for these points

* Why do we need multixact to be persistent? Do we need every page of
multixact to be persistent, or just particular pages in certain
circumstances?

* Why do we need to expand multixact with flags? Can we avoid that in
some cases?

* Why do we need to store just single xids in multixact members?
Didn't understand comments, no explanation

-- 
 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] elegant and effective way for running jobs inside a database

2012-03-05 Thread Fabrízio de Royes Mello

2012/3/5 Artur Litwinowicz ad...@ybka.com

 Dear Developers,
   I am looking for elegant and effective way for running jobs inside a
 database or cluster - for now I can not find that solution.
 OK if You say use cron or pgAgent I say I know that solutions, but
 the are not effective and elegant. Compilation of pgAgent is a pain
 (especially wxWidgets usage on system with no X) - it can run jobs with
 minimal 60s periods but what when someone needs run it faster for eg.
 with 5s period ? Of course using cron I can do that but it is not
 effective and elegant solution. Why PostgreSQL can not have so elegant
 solution like Oracle database ? I am working with Oracle databases for
 many years, but I like much more PostgreSQL project but this one
 thing... I can not understand - the lack of jobs inside the database...


IMHO it is not necessary add this feature to the PostgreSQL core, because
the OS already has the capability to schedule and maintain the tasks.

Best regards,

-- 
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
 Blog sobre TI: http://fabriziomello.blogspot.com
 Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
 Twitter: http://twitter.com/fabriziomello

Re: [HACKERS] foreign key locks, 2nd attempt


Excerpts from Simon Riggs's message of lun mar 05 16:34:10 -0300 2012:
 On Mon, Mar 5, 2012 at 6:37 PM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:

 It does however, illustrate my next review comment which is that the
 comments and README items are sorely lacking here. It's quite hard to
 see how it works, let along comment on major design decisions. It
 would help myself and others immensely if we could improve that.

Hm.  Okay.

 Is there a working copy on a git repo? Easier than waiting for next
 versions of a patch.

No, I don't have an external mirror of my local repo.

 My other comments so far are
 
 * some permutations commented out - no comments as to why
 Something of a fault with the isolation tester that it just shows
 output, there's no way to record expected output in the spec

The reason they are commented out is that they are invalid, that is,
it requires running a command on a session that's blocked in the
previous command.  Obviously, that cannot happen in real life.

isolationtester now has support for detecting such conditions; if the
spec specifies running a command in a locked session, the permutation is
killed with an error message invalid permutation and just continues
with the next permutation.  It used to simply die, aborting the test.
Maybe we could just modify the specs so that all permutations are there
(this can be done by simply removing the permutation lines), and the
invalid permutation messages are part of the expected file.  Would
that be better?

 Comments required for these points
 
 * Why do we need multixact to be persistent? Do we need every page of
 multixact to be persistent, or just particular pages in certain
 circumstances?

Any page that contains at least one multi with an update as a member
must persist.  It's possible that some pages contain no update (and this
is even likely in some workloads, if updates are rare), but I'm not sure
it's worth complicating the code to cater for early removal of some
pages.

 * Why do we need to expand multixact with flags? Can we avoid that in
 some cases?

Did you read my blog post?
http://www.commandprompt.com/blogs/alvaro_herrera/2011/08/fixing_foreign_key_deadlocks_part_three/
This explains the reason -- the point is that we need to distinguish the
lock strength acquired by each locker.

 * Why do we need to store just single xids in multixact members?
 Didn't understand comments, no explanation

This is just for SELECT FOR SHARE.  We don't have a hint bit to indicate
this tuple has a for-share lock, so we need to create a multi for it.
Since FOR SHARE is probably going to be very uncommon, this isn't likely
to be a problem.  We're mainly catering for users of SELECT FOR SHARE so
that it continues to work, i.e. maintain backwards compatibility.

(Maybe I misunderstood your question -- what I think you're asking is,
why are there some multixacts that have a single member?)

I'll try to come up with a good place to add some paragraphs about all
this.  Please let me know if answers here are unclear and/or you have
further questions.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] elegant and effective way for running jobs inside a database


Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012:
 Dear Developers,
I am looking for elegant and effective way for running jobs inside a
 database or cluster - for now I can not find that solution.

Yeah, it'd be good to have something.  Many people say it's not
necessary, and probably some hackers would oppose it; but mainly I think
we just haven't agreed (or even discussed) what the design of such a
scheduler would look like.  For example, do we want it to be able to
just connect and run queries and stuff, or do we want something more
elaborate able to start programs such as running pg_dump?  What if the
program crashes -- should it cause the server to restart?  And so on.
It's not a trivial problem.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] poll: CHECK TRIGGER?

2012/3/5 Robert Haas robertmh...@gmail.com:
 On Sat, Mar 3, 2012 at 9:23 PM, Alvaro Herrera
 alvhe...@commandprompt.com wrote:
 Uh!  Now that I read this I realize that what you're supposed to give to
 CHECK TRIGGER is the trigger name, not the function name!  In that
 light, using CHECK FUNCTION for this doesn't make a lot of sense.

 Okay, CHECK TRIGGER it is.

 I confess to some bafflement about why we need dedicated syntax for
 this, or even any kind of core support at all.  What would be wrong
 with defining a function that takes regprocedure as an argument and
 does whatever?  Sure, it's nicer syntax, but we've repeatedly rejected
 patches that only provided nicer syntax on the grounds that syntax is
 not free, and therefore syntax alone is not a reason to change the
 core grammar.  What makes this case different?


before argumentation for CHECK TRIGGER I show a proposed PL checker function:

FUNCTION checker_function(regprocedure, regclass, options text[],
fatal_errors boolen)
RETURNS TABLE (functionoid oid, lineno int, statement text, sqlstate
text, message text, detail text, hint text, position int, query)

this function is worker for CHECK FUNCTION and CHECK TRIGGER
statements. The possibility to call this function directly can enable
thousands combinations - all functions, all functions from schema, all
functions that has name starts with, ...

for user friendly there are interface: CHECK FUNCTION and CHECK TRIGGER

* provides more practical reports with caret positioning than SRF function
* support often used combinations of requests - all functions from one
language, all functions from schema, all functions by one user


CHECK FUNCTION is clear - and there are no disagreement

There are two possibilities for checking triggers

a) some like CHECK FUNCTION trgfunc() ON table_name

b) existing CHECK TRIGGER t1_f1 ON table_name;

these forms are almost equal, although CREATE TRIGGER can provide more
unique information for checking. And joining table_name to TRIGGER has
bigger sense then to FUNCTION (in one statement).

When I try to look on some multicheck form:

a) CHECK FUNCTION ALL ON table_name
b) CHECK TRIGGER ALL ON table_name

then more natural form is @b (for me). Personally, I can live with
one, both or second form, although I prefer CHECK TRIGGER.

notes?

Regards

Pavel


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

-- 
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] Our regex vs. POSIX on longest match

2012-03-05 Thread Martijn van Oosterhout

On Mon, Mar 05, 2012 at 11:28:24AM -0500, Tom Lane wrote:
 Robert Haas robertmh...@gmail.com writes:
  I think the right way to imagine this is as though the regular
  expression were being matched to the source text in left-to-right
  fashion.
 
 No, it isn't.  You are headed down the garden path that leads to a
 Perl-style definition-by-implementation, and in particular you are going
 to end up with an implementation that fails to satisfy the POSIX
 standard.  POSIX requires an *overall longest* match (at least for cases
 where all quantifiers are greedy), and that sometimes means that the
 quantifiers can't be processed strictly left-to-right greedy.  An
 example of this is 

On the otherhand, I think requiring an overall longest match makes
your implementation non-polynomial complexity. The simplest example I
can think of is the knapsack problem, where given weights x_n and a
total W, can be converted to a regex problem as matching a string with
W a's against the regex:

a{x_1}?a{x_2}?a{x_3}? etc...

Yes, Perl (and others) don't guarentee an overall longest match. I
think they want you to consider regular expressions as a specialised
parsing language where you can configure a state machine to process
your strings. Not ideal, but predicatable.

The question is, what are users expecting of the PostgreSQL regex
implementation?

Have a nice day,
-- 
Martijn van Oosterhout   klep...@svana.org   http://svana.org/kleptog/
 He who writes carelessly confesses thereby at the very outset that he does
 not attach much importance to his own thoughts.
   -- Arthur Schopenhauer


signature.asc
Description: Digital signature

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

On Mon, Mar 5, 2012 at 5:49 PM, Robert Haas robertmh...@gmail.com wrote:

 Well, there's no point that I can see in having two checks.  I just
 dislike the idea that we have to remember to add this check for every
 method of accessing the relation - doesn't seem terribly future-proof.
  It gets even worse if you start adding checks to DDL code paths - if
 we're going to do that, we really need to cover them all, and that
 doesn't seem very practical if they're going to spread out all over
 the place.

Understood. Will look.

 I don't understand your comment that a snapshot doesn't get associated
 with a relation until scan time.  I believe we associated a snapshot
 with each query before we even know what relations are involved; that
 query then gets passed down to all the individual scans.  The query
 also opens and locks those relations.  We ought to be able to arrange
 for the query snapshot to be cross-checked at that point, rather than
 waiting until scan-start time.

What's to stop other code using an older snapshot explicitly? That
fear may be bogus.

Any suggestions? ISTM we don't know whether we're already locked until
we get to LockAcquire() and there's no easy way to pass down snapshot
information through that, let alone handle RI snapshots. Ideas please.

-- 
 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] elegant and effective way for running jobs inside a database

Hello

2012/3/5 Alvaro Herrera alvhe...@commandprompt.com:

 Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012:
 Dear Developers,
    I am looking for elegant and effective way for running jobs inside a
 database or cluster - for now I can not find that solution.

 Yeah, it'd be good to have something.  Many people say it's not
 necessary, and probably some hackers would oppose it; but mainly I think
 we just haven't agreed (or even discussed) what the design of such a
 scheduler would look like.  For example, do we want it to be able to
 just connect and run queries and stuff, or do we want something more
 elaborate able to start programs such as running pg_dump?  What if the
 program crashes -- should it cause the server to restart?  And so on.
 It's not a trivial problem.


I agree - it is not simple

* workflow support
* dependency support

a general ACID scheduler can be nice (in pg) but it is not really
simple. There was some proposal about using autovacuum demon like
scheduler.

Pavel

 --
 Álvaro Herrera alvhe...@commandprompt.com
 The PostgreSQL Company - Command Prompt, Inc.
 PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

-- 
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] Our regex vs. POSIX on longest match

Martijn van Oosterhout klep...@svana.org writes:
 On the otherhand, I think requiring an overall longest match makes
 your implementation non-polynomial complexity.

Only if you don't know how to implement it -- a DFA-based implementation
doesn't have much trouble with this.

 [ equivalence of knapsack problem to regexes with bounded repetition ]

Interesting, but note that neither the POSIX spec nor our implementation
permit arbitrarily large repetition counts, so the theoretical
NP-completeness is only theoretical.

 The question is, what are users expecting of the PostgreSQL regex
 implementation?

I think a minimum expectation is that we adhere to the POSIX
specification.

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] foreign key locks, 2nd attempt

On Mon, Mar 5, 2012 at 7:53 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:

My other comments so far are

* some permutations commented out - no comments as to why
Something of a fault with the isolation tester that it just shows
output, there's no way to record expected output in the spec

The reason they are commented out is that they are invalid, that is,
it requires running a command on a session that's blocked in the
previous command. Obviously, that cannot happen in real life.

isolationtester now has support for detecting such conditions; if the
spec specifies running a command in a locked session, the permutation is
killed with an error message invalid permutation and just continues
with the next permutation. It used to simply die, aborting the test.
Maybe we could just modify the specs so that all permutations are there
(this can be done by simply removing the permutation lines), and the
invalid permutation messages are part of the expected file. Would
that be better?

It would be better to have an isolation tester mode that checks to see
it was invalid and if not, report that.

At the moment we can't say why you commented something out. There's no
comment or explanation, and we need something, otherwise 3 years from
now we'll be completely in the dark.

Comments required for these points

* Why do we need multixact to be persistent? Do we need every page of
multixact to be persistent, or just particular pages in certain
circumstances?

Any page that contains at least one multi with an update as a member
must persist. It's possible that some pages contain no update (and this
is even likely in some workloads, if updates are rare), but I'm not sure
it's worth complicating the code to cater for early removal of some
pages.

If the multixact contains an xid and that is being persisted then you
need to set an LSN to ensure that a page writes causes an XLogFlush()
before the multixact write. And you need to set do_fsync, no? Or
explain why not in comments...

I was really thinking we could skip the fsync of a page if we've not
persisted anything important on that page, since that was one of
Robert's performance points.

* Why do we need to expand multixact with flags? Can we avoid that in
some cases?

Did you read my blog post?
http://www.commandprompt.com/blogs/alvaro_herrera/2011/08/fixing_foreign_key_deadlocks_part_three/
This explains the reason -- the point is that we need to distinguish the
lock strength acquired by each locker.

Thanks, I will, but it all belongs in a README please.

* Why do we need to store just single xids in multixact members?
Didn't understand comments, no explanation

This is just for SELECT FOR SHARE. We don't have a hint bit to indicate
this tuple has a for-share lock, so we need to create a multi for it.
Since FOR SHARE is probably going to be very uncommon, this isn't likely
to be a problem. We're mainly catering for users of SELECT FOR SHARE so
that it continues to work, i.e. maintain backwards compatibility.

Good, thanks.

Are we actively recommending people use FOR KEY SHARE rather than FOR
SHARE, in explicit use?

(Maybe I misunderstood your question -- what I think you're asking is,
why are there some multixacts that have a single member?)

I'll try to come up with a good place to add some paragraphs about all
this. Please let me know if answers here are unclear and/or you have
further questions.

Thanks

I think we need to define some test workloads to measure the
performance impact of this patch. We need to be certain that it has a
good impact in target cases, plus a known impact in other cases.

Suggest

* basic pgbench - no RI

* inserts into large table, RI checks to small table, no activity on small table

* large table parent, large table: child
20 child rows per parent, fk from child to parent
updates of multiple children at same time
low/medium/heavy locking

* large table parent, large table: child
20 child rows per parent,fk from child to parent
updates of parent and child at same time
low/medium/heavy locking

--
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] Command Triggers, patch v11

2012-03-05 Thread Dimitri Fontaine

Hi,

Thanks for the extensive testing.  I'm adding your tests to the
regression suite, and keep wondering if you saw that lots of them were
already covered?  Did you try make installcheck?

Thom Brown t...@linux.com writes:
 Creating a command trigger using ANY COMMAND results in oid,
 schemaname, objectname (function parameters 4  5) not being set for
 either BEFORE or AFTER.

Yes, that's documented.  It could be better documented though, it seems.

 There is no support for ALTER CONVERSION.

It was missing in the grammar and docs only, added.

 WARNING:  CREATE INDEX CONCURRENTLY is not supported
 DETAIL:  The command trigger will not get fired.

 This should probably say that it’s not supported on AFTER command
 triggers yet rather than the general DDL itself.

Edited.

 Command triggers for AFTER creating rules don’t return OIDs.

Fixed.

 Command triggers for creating sequences don’t show the schema:

Documented already, it's uneasy to get at it in the code and I figured I
might as well drop the ball on that in the current patch's form.

 Command triggers for AFTER creating extensions with IF NOT EXISTS
 don’t fire, but do in the ANY COMMAND instance:

Fixed.

 Command triggers on CREATE TEXT SEARCH DICTIONARY show the name as garbage:

Fixed, test case added.

 Command triggers for BEFORE CREATE TYPE (exluding ANY COMMAND) don’t
 fire if the type isn’t created due to an error:

Per design, although we might want to talk about it.  I made it so that
specific command triggers are only fired after errors checks have been
made.

That's not the case with ANY command triggers so that you can actually
block DDLs on your instances, as has been asked on list.

 The ANY COMMAND trigger fires on creating roles, but there’s no
 corresponding allowance to create the trigger explicitly for creating
 roles.

Roles are global objects, you don't want the behavior of role commands
to depend on which database you happen to have been logged in when
issuing the command.  That would call for removing the ANY command
support for them too, but I can't seem to decide about that.

Any input?

 Command triggers for AFTER CREATE VIEW don’t show the schema:

Couldn't reproduce, added test cases.

 Command triggers for BEFORE and AFTER ALTER DOMAIN show a garbage name
 and no schema when dropping a constraint:

Fixed, added test cases.

 Continuing with this same trigger, we do get a schema but a garbage
 name for OWNER TO:

Fixed, added test cases.

 When an ALTER EXTENSION fails to upgrade, the AFTER ANY COMMAND
 trigger fires, but not command triggers specifically for ALTER
 EXTENSION:

 Same on ALTER EXTENSION, when failing to add a member, the BEFORE ANY
 COMMAND trigger fires, but not the one specifically for ALTER
 EXTENSION:

Again, per design. Let's talk about it, it will probably need at least
documentation.

 Specific command triggers against ALTER FOREIGN TABLE (i.e. not ANY
 COMMAND) for BEFORE and AFTER aren’t working when renaming columns:

 Specific command triggers agains ALTER FUNCTION (i.e. not ANY COMMAND)
 don’t fire for any changes except renaming, changing owner or changing
 schema.  Everything else fails to trigger (cost, rows, setting
 configuration parameters, setting strict, security invoker etc.).:

I kept some TODO items as I feared I would get bored tomorrow otherwise…

 There doesn’t appear to be command trigger support for ALTER LARGE OBJECT.

Do we want to have some?  Those are in between data and command.

 Specific command triggers on ALTER SEQUENCE don’t fire:
 Specific command triggers on ALTER TABLE don’t fire for renaming columns:
 Also renaming attributes doesn’t fire specific triggers:
 Specific command triggers on ALTER VIEW don’t fire for any type of change:

Kept on the TODO.

 Command triggers on ALTER TYPE when changing owner produce a garbage name:

Fixed along with the DOMAIN test case (same code).

 Specific command triggers on DROP AGGREGATE don’t fire in the IF
 EXISTS scenario if the target object doesn’t exist:

So, do we want to run the command triggers here?  Is the IF EXISTS check
to be considered like the other error conditions?

 When adding objects to an extension, then dropping the extension with
 a cascade, the objects are dropped with it, but triggers aren’t fired
 to the removal of those dependant objects:

Yes, that's expected and needs documenting.

 Using DROP OWNED BY allows objects to be dropped without their
 respective specific triggers firing.

Expected too.

 Using DROP SCHEMA … CASACDE also allows objects to be dropped without
 their respective specific triggers firing:

Again, same expectation here.

 Command triggers on all DROP commands for TEXT SEARCH
 CONFIGURATION/DICTIONARY/PARSER/TEMPLATE show the schema name as the
 relation name:

Now that's strange and will keep me awake longer tomorrow.

 Still no command triggers firing for CREATE TABLE AS:

Yes, Andres made CTAS a utility command, he didn't add the code that
make them fire command triggers. I

Re: [HACKERS] RFC: Making TRUNCATE more MVCC-safe

On Mon, Mar 5, 2012 at 2:22 PM, Noah Misch n...@leadboat.com wrote:
 On Mon, Feb 13, 2012 at 09:29:56AM -0500, Robert Haas wrote:
 On Fri, Feb 10, 2012 at 11:46 PM, Noah Misch n...@leadboat.com wrote:
  I've yet to see an MVCC anomaly that one can reproduce at REPEATABLE READ 
  and
  not at READ COMMITTED. ?They tend to be narrow race conditions at READ
  COMMITTED, yet easy to demonstrate at REPEATABLE READ. ?Related:
  http://archives.postgresql.org/pgsql-performance/2011-02/msg00451.php

 Yeah.  Well, that's actually an interesting example, because it
 illustrates how general this problem is.  We could potentially get
 ourselves into a situation where just about every system catalog table
 needs an xmin field to store the point at which the object came into
 existence - or for that matter, was updated.

 I can see this strategy applying to many relation-pertinent system catalogs.
 Do you foresee applications to non-relation catalogs?

Well, in theory, we have similar issues if, say, a query uses a
function that didn't exist at the time the snapshot as taken; the
actual results the user sees may not be consistent with any serial
execution schedule.  And the same could be true for any other SQL
object.  It's unclear that those cases are as compelling as this one,
but then again it's unclear that no one will ever want to fix them,
either.  For example, suppose we have a view v over a table t that
calls a function f.  Somebody alters f to give different results and,
in the same transaction, modifies the contents of t (but no DDL).
This doesn't strike me as a terribly unlikely scenario; the change to
t could well be envisioned as a compensating transaction.  But now if
somebody uses the new definition of f against the old contents of t,
the user may fail to get what they were hoping for out of bundling
those changes together in one transaction.

Now, maybe we're never going to fix those kinds of anomalies anyway,
but if we go with this architecture, then I think the chances of it
ever being palatable to try are pretty low.

 In any event, I think a pg_class.relvalidxmin is the right starting point.
 One might imagine a family of relvalidxmin, convalidxmin, indcheckxmin
 (already exists), inhvalidxmin, and attvalidxmin.  relvalidxmin is like the
 AccessExclusiveLock of that family; it necessarily blocks everything that
 might impugn the others.  The value in extending this to more catalogs is the
 ability to narrow the impact of failing the check.  A failed indcheckxmin
 comparison merely excludes plans involving the index.  A failed inhvalidxmin
 check might just skip recursion to the table in question.  Those are further
 refinements, much like using weaker heavyweight lock types.

Yes, good parallel.

 But it's not quite the
 same as the xmin of the row itself, because some updates might be
 judged not to matter.  There could also be intermediate cases where
 updates are invalidating for some purposes but not others.  I think
 we'd better get our hands around more of the problem space before we
 start trying to engineer solutions.

 I'm not seeing that problem.  Any operation that would update some xmin
 horizon should set it to the greater of its current value and the value the
 operation needs for its own correctness.  If you have something in mind that
 needs more, could you elaborate?

Well, consider something like CLUSTER.  It's perfectly OK for CLUSTER
to operate on a table that has been truncated since CLUSTER's snapshot
was taken, and no serialization anomaly is created that would not have
already existed as a result of the non-MVCC-safe TRUNCATE.  On the
other hand, if CLUSTER operates on a table that was created since
CLUSTER's snapshot was taken, then you have a bona fide serialization
anomaly.  Maybe not a very important one, but does that prove that
there's no significant problem of this type in general, or just
nobody's thought through all the cases yet?  After all, the issues
with CREATE TABLE/TRUNCATE vs. a concurrent SELECT have been around
for a very long time, and we're only just getting around to looking at
them, so I don't have much confidence that there aren't other cases
floating around out there.

I guess another way to put this is that you could need locks of a
great number of different strengths to really handle all the cases.
It's going to be unappealing to, say, set the relation xmin when
setting the constraint xmin would do, or to fail for a concurrent
TRUNCATE as well as a concurrent CREATE TABLE when only the latter
logically requires a failure.

-- 
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] RFC: Making TRUNCATE more MVCC-safe

On Mon, Mar 5, 2012 at 8:46 PM, Robert Haas robertmh...@gmail.com wrote:

 In any event, I think a pg_class.relvalidxmin is the right starting point.
 One might imagine a family of relvalidxmin, convalidxmin, indcheckxmin
 (already exists), inhvalidxmin, and attvalidxmin.  relvalidxmin is like the
 AccessExclusiveLock of that family; it necessarily blocks everything that
 might impugn the others.  The value in extending this to more catalogs is the
 ability to narrow the impact of failing the check.  A failed indcheckxmin
 comparison merely excludes plans involving the index.  A failed inhvalidxmin
 check might just skip recursion to the table in question.  Those are further
 refinements, much like using weaker heavyweight lock types.

 Yes, good parallel.

Did you guys get my comment about not being able to use an xmin value,
we have to use an xid value and to a an XidInMVCCSnapshot() test? Just
checking whether you agree/disagree.

-- 
 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] elegant and effective way for running jobs inside a database

W dniu 2012-03-05 20:56, Alvaro Herrera pisze:
 
 Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012:
 Dear Developers,
I am looking for elegant and effective way for running jobs inside a
 database or cluster - for now I can not find that solution.
 
 Yeah, it'd be good to have something.  Many people say it's not
 necessary, and probably some hackers would oppose it; but mainly I think
 we just haven't agreed (or even discussed) what the design of such a
 scheduler would look like.  For example, do we want it to be able to
 just connect and run queries and stuff, or do we want something more
 elaborate able to start programs such as running pg_dump?  What if the
 program crashes -- should it cause the server to restart?  And so on.
 It's not a trivial problem.
 

Yes, yes it is not a trivial problem... - tools like pgAgent are good
when someone starts play with PostgreSQL - but this great environment
(only one serious against something like Oracle or DB2) needs something
professional, production ready. It can not happen when we are upgrading
database or OS and can not compile pgAgent because of strange
dependences... and for example whole sofisticated solution like web
application with complicated data flow has a problem... For example I am
using stored functions developed in Lua language, which are writing and
reading data to and from Redis server with a periods less then one
minute. Without heart beat like precise job manager it can not works
as professional as it can. Every one can use CRON or something like that
- yes it works but PostgreSQL has so many features and something like
job manager is inalienable in mine mind.

Best regards,
Artur



0xAF4A859D.asc
Description: application/pgp-keys

-- 
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] elegant and effective way for running jobs inside a database

2012/3/5 Artur Litwinowicz ad...@ybka.com:
 W dniu 2012-03-05 20:56, Alvaro Herrera pisze:

 Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012:
 Dear Developers,
    I am looking for elegant and effective way for running jobs inside a
 database or cluster - for now I can not find that solution.

 Yeah, it'd be good to have something.  Many people say it's not
 necessary, and probably some hackers would oppose it; but mainly I think
 we just haven't agreed (or even discussed) what the design of such a
 scheduler would look like.  For example, do we want it to be able to
 just connect and run queries and stuff, or do we want something more
 elaborate able to start programs such as running pg_dump?  What if the
 program crashes -- should it cause the server to restart?  And so on.
 It's not a trivial problem.


 Yes, yes it is not a trivial problem... - tools like pgAgent are good
 when someone starts play with PostgreSQL - but this great environment
 (only one serious against something like Oracle or DB2) needs something
 professional, production ready. It can not happen when we are upgrading
 database or OS and can not compile pgAgent because of strange
 dependences... and for example whole sofisticated solution like web
 application with complicated data flow has a problem... For example I am
 using stored functions developed in Lua language, which are writing and
 reading data to and from Redis server with a periods less then one
 minute. Without heart beat like precise job manager it can not works
 as professional as it can. Every one can use CRON or something like that
 - yes it works but PostgreSQL has so many features and something like
 job manager is inalienable in mine mind.

Long time a strategy for PostgreSQL was a minimal core and extensible
modules without duplication some system services. This strategy is
valid still but some services are in core - example should be
replication.

Some proposals about custom scheduler exists
http://archives.postgresql.org/pgsql-hackers/2010-02/msg01701.php and
it is part of ToDo - so this feature should be in core (in next 2-4
years).

Why this is not in core? Nobody wrote it :).

Regards

Pavel Stehule


 Best regards,
 Artur



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


-- 
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] Command Triggers, patch v11

2012-03-05 Thread Andres Freund

On Monday, March 05, 2012 09:42:00 PM Dimitri Fontaine wrote:
  Still no command triggers firing for CREATE TABLE AS:
 Yes, Andres made CTAS a utility command, he didn't add the code that
 make them fire command triggers. I would expect his patch to get in
 first, so I don't expect him to be adding that support, I think I will
 have to add it when rebasing once his patch has landed.
That was my assumption as well.

Any opinions about adding the patch to the commitfest other than from dim? I 
feel a bit bad adding it to the in-progress one even if its belongs there 
because is a part of the command trigger stuff...

Andres

-- 
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] Command Triggers, patch v11

On Sat, Mar 3, 2012 at 2:25 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:
 Kevin Grittner kevin.gritt...@wicourts.gov writes:
 Right.  What I thought I was agreeing with was the notion that you
 should need to specify more than the trigger name to drop the
 trigger.  Rather like how you can create a trigger AFTER INSERT OR
 UPDATE OR DELETE, but you don't need to specify all those events to
 drop the trigger -- just the name will do.

 The parallel between INSERT/UPDATE/DELETE and the trigger's command is
 not working well enough, because in the data trigger case we're managing
 a single catalog entry with a single command, and in the command trigger
 case, in my model at least, we would be managing several catalog entries
 per command.

 To take an example:

  CREATE COMMAND TRIGGER foo AFTER create table, create view;
  DROP COMMAND TRIGGER foo;

 The first command would create two catalog entries, and the second one
 would delete the same two entries.  It used to work this way in the
 patch, then when merging with the new remove object infrastructure I
 lost that ability.  From the beginning Robert has been saying he didn't
 want that behavior, and Tom is now saying the same, IIUC.

 So we're back to one command, one catalog entry.

I hadn't made the connection here until you read this, but I agree
there's a problem there.  One command, one catalog entry is, I think,
pretty important.  So that means that if want to support a trigger on
CREATE TABLE OR CREATE VIEW OR DROP EXTENSION, then the command names
(or integers that serve as proxies for them) need to go into an array
somewhere, and we had to look for arrays that contain the command
we're looking for, rather than just the command name.  That might seem
prohibitively slow, but I bet if you put a proper cache in place it
isn't, because pg_cmdtrigger should be pretty small and not updated
very often.  You can probably afford to seq-scan it and rebuild your
entire cache across all command types every time it changes in any
way.

But just supporting one command type per trigger seems fine for a
first version, too.  There's nothing to prevent us from adding that
later.

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

I wrote:
 I'm inclined to think that if we provide this function in core at all,
 it should take a parameter list long enough to let it fill in the Path
 completely.  That would imply that any future changes in Path structs
 would result in a change in the parameter list, which would break
 callers --- but it would break them in an obvious way that the C
 compiler would complain about.  If we leave it as-is, those same callers
 would be broken silently, because they'd just be failing to fill in
 the new Path fields.

I've committed the PlanForeignScan API change, with that change and
some other minor editorialization.  The pgsql_fdw patch now needs an
update, so I set it back to Waiting On Author state.

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] elegant and effective way for running jobs inside a database

W dniu 2012-03-05 22:09, Pavel Stehule pisze:
 2012/3/5 Artur Litwinowicz ad...@ybka.com:
 W dniu 2012-03-05 20:56, Alvaro Herrera pisze:

 Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012:
 Dear Developers,
I am looking for elegant and effective way for running jobs inside a
 database or cluster - for now I can not find that solution.

 Yeah, it'd be good to have something.  Many people say it's not
 necessary, and probably some hackers would oppose it; but mainly I think
 we just haven't agreed (or even discussed) what the design of such a
 scheduler would look like.  For example, do we want it to be able to
 just connect and run queries and stuff, or do we want something more
 elaborate able to start programs such as running pg_dump?  What if the
 program crashes -- should it cause the server to restart?  And so on.
 It's not a trivial problem.


 Yes, yes it is not a trivial problem... - tools like pgAgent are good
 when someone starts play with PostgreSQL - but this great environment
 (only one serious against something like Oracle or DB2) needs something
 professional, production ready. It can not happen when we are upgrading
 database or OS and can not compile pgAgent because of strange
 dependences... and for example whole sofisticated solution like web
 application with complicated data flow has a problem... For example I am
 using stored functions developed in Lua language, which are writing and
 reading data to and from Redis server with a periods less then one
 minute. Without heart beat like precise job manager it can not works
 as professional as it can. Every one can use CRON or something like that
 - yes it works but PostgreSQL has so many features and something like
 job manager is inalienable in mine mind.
 
 Long time a strategy for PostgreSQL was a minimal core and extensible
 modules without duplication some system services. This strategy is
 valid still but some services are in core - example should be
 replication.
 
 Some proposals about custom scheduler exists
 http://archives.postgresql.org/pgsql-hackers/2010-02/msg01701.php and
 it is part of ToDo - so this feature should be in core (in next 2-4
 years).
 
 Why this is not in core? Nobody wrote it :).
 
 Regards
 
 Pavel Stehule
 

 Best regards,
 Artur



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

 

Ouch... in next 2-4 years - it broke my heart like a bullet - You
should not write it... ;)
I feel that I need to set aside SQL, Python, PHP and so on and take to
my hands old book about C programming language from university ;)
I hope my words are like drops of water for this idea and in the future
some people will be happy to use professional job manager :)

Best regards,
Artur


0xAF4A859D.asc
Description: application/pgp-keys

-- 
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] Command Triggers, patch v11

2012-03-05 Thread Thom Brown

On 5 March 2012 20:42, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:
 Hi,

 Thanks for the extensive testing.  I'm adding your tests to the
 regression suite, and keep wondering if you saw that lots of them were
 already covered?  Did you try make installcheck?

Yes, but I felt it better that I come up with my own separate tests.

 Thom Brown t...@linux.com writes:
 Creating a command trigger using ANY COMMAND results in oid,
 schemaname, objectname (function parameters 4  5) not being set for
 either BEFORE or AFTER.

 Yes, that's documented.  It could be better documented though, it seems.

Is there a reason why we can't provide the OID for ANY COMMAND... if
it's available?  I'm guessing it would end up involving having to
special case for every command. :/

 Command triggers for creating sequences don’t show the schema:

 Documented already, it's uneasy to get at it in the code and I figured I
 might as well drop the ball on that in the current patch's form.

Fair enough.

 Command triggers for BEFORE CREATE TYPE (exluding ANY COMMAND) don’t
 fire if the type isn’t created due to an error:

 Per design, although we might want to talk about it.  I made it so that
 specific command triggers are only fired after errors checks have been
 made.

 That's not the case with ANY command triggers so that you can actually
 block DDLs on your instances, as has been asked on list.

I don't have any strong feelings about it, so I'll bear it in mind for
future tests.

 The ANY COMMAND trigger fires on creating roles, but there’s no
 corresponding allowance to create the trigger explicitly for creating
 roles.

 Roles are global objects, you don't want the behavior of role commands
 to depend on which database you happen to have been logged in when
 issuing the command.  That would call for removing the ANY command
 support for them too, but I can't seem to decide about that.

 Any input?

If that's your reasoning, then it would make sense to remove ANY
command support for it too.

 There doesn’t appear to be command trigger support for ALTER LARGE OBJECT.

 Do we want to have some?  Those are in between data and command.

*shrug* But ANY COMMAND triggers fire for it.  So I'd say either
remove support for that, or add a specific trigger.

 Specific command triggers on DROP AGGREGATE don’t fire in the IF
 EXISTS scenario if the target object doesn’t exist:

 So, do we want to run the command triggers here?  Is the IF EXISTS check
 to be considered like the other error conditions?

Maybe.  If that's expected behaviour, I'll start expecting it then.

 When adding objects to an extension, then dropping the extension with
 a cascade, the objects are dropped with it, but triggers aren’t fired
 to the removal of those dependant objects:

 Yes, that's expected and needs documenting.

 Using DROP OWNED BY allows objects to be dropped without their
 respective specific triggers firing.

 Expected too.

 Using DROP SCHEMA … CASACDE also allows objects to be dropped without
 their respective specific triggers firing:

 Again, same expectation here.

If these are all expected, does it in any way compromise the
effectiveness of DDL triggers in major use-cases?

 I'm not sending a revised patch, please use the github branch if you
 want to do some more tests already, or ask me for either a new patch
 version or a patch-on-patch, as you see fit.

Hmm... how does that work with regards to the commitfest process?

But I'll re-test when you let me know when you've committed your
remaining fixes to Github.

-- 
Thom

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

[HACKERS] CLUSTER VERBOSE (9.1.3)

2012-03-05 Thread Larry Rosenman

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Is there any way to get more info out of CLUSTER VERBOSE so it says
what index it's working on AFTER the table re-write?

INFO:  clustering public.values using sequential scan and sort
INFO:  values: found 0 removable, 260953511 nonremovable row
versions in 4224437 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 168.02s/4324.68u sec elapsed 8379.12 sec.


And at this point it's doing something(tm), I assume re-doing the indexes.

It would be nice(tm) to get more info.

Ideas?


- -- 
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 512-248-2683 E-Mail: l...@lerctr.org
US Mail: 430 Valona Loop, Round Rock, TX 78681-3893
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.17 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJPVTLJAAoJENC8dtAvA1zmolAIAIgfqXTe5cWZ2ZGVXXVgzv3A
pBhi1bVOEB8Xjcie82gMTyqBZKuTtIqNFHXWaB4xVxG6U93YGlru7DnUa8ArzbvW
31b0GHIeXpemUFz0OnuKv6h0Bt+H755YNuDXykN7a7VEdzwIrv/iSSGlBsbEywhG
SdC1VvHrmUaRCfCV/XBF4tynC3rocRIyf29SJNPZJl9cJtkK2BDigUeHANN3mydQ
1H1WZ8CMfnTvi8vROGFuk5HCZDv0e9K9dYthfMEqIgKzBRu5jLagijADyEhVCJfO
/JYP+t1eGPP1zYqf+R/OfMGTM0RYcP/XVRK8qS+8FPBTUPTphStjmOBPuHRYWDU=
=GWPN
-END PGP SIGNATURE-

-- 
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] elegant and effective way for running jobs inside a database


Excerpts from Artur Litwinowicz's message of lun mar 05 18:32:44 -0300 2012:

 Ouch... in next 2-4 years - it broke my heart like a bullet - You
 should not write it... ;)
 I feel that I need to set aside SQL, Python, PHP and so on and take to
 my hands old book about C programming language from university ;)
 I hope my words are like drops of water for this idea and in the future
 some people will be happy to use professional job manager :)

Keep in mind that it's not about coding in C but mostly about figuring
out what a sane design out to look like.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] elegant and effective way for running jobs inside a database

W dniu 2012-03-05 22:44, Alvaro Herrera pisze:
 
 Excerpts from Artur Litwinowicz's message of lun mar 05 18:32:44 -0300 2012:
 
 Ouch... in next 2-4 years - it broke my heart like a bullet - You
 should not write it... ;)
 I feel that I need to set aside SQL, Python, PHP and so on and take to
 my hands old book about C programming language from university ;)
 I hope my words are like drops of water for this idea and in the future
 some people will be happy to use professional job manager :)
 
 Keep in mind that it's not about coding in C but mostly about figuring
 out what a sane design out to look like.
 

I understand it... (I meant if you wanna something... do it for your
self - it is the fastest way).
Regarding a functional area I can help... but I can not understand why
this idea is so unappreciated?
It will be so powerfull feature - I am working with systems made for
goverment (Orcale) - jobs are the core gears for data flow between many
systems and other goverment bureaus.

Best regards,
Artur


0xAF4A859D.asc
Description: application/pgp-keys

-- 
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] elegant and effective way for running jobs inside a database

2012-03-05 Thread Jaime Casanova

On Mon, Mar 5, 2012 at 5:03 PM, Artur Litwinowicz ad...@ybka.com wrote:

 I understand it... (I meant if you wanna something... do it for your
 self - it is the fastest way).

other way is to fund the work so someone can use his/her time to do it

 Regarding a functional area I can help... but I can not understand why
 this idea is so unappreciated?

is not unappreciated, is just a problem that already *has* a solution
if it were something that currently you can't do it then there would
be more people after it

 It will be so powerfull feature - I am working with systems made for
 goverment (Orcale) - jobs are the core gears for data flow between many
 systems and other goverment bureaus.


me too, and we solve it with cron

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación

-- 
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] performance-test farm

2012-03-05 Thread Tomas Vondra

On 12.5.2011 08:54, Greg Smith wrote:
 Tomas Vondra wrote:

 The idea is that buildfarm systems that are known to have a) reasonable
 hardware and b) no other concurrent work going on could also do
 performance tests.  The main benefit of this approach is it avoids
 duplicating all of the system management and source code building work
 needed for any sort of thing like this; just leverage the buildfarm
 parts when they solve similar enough problems.  Someone has actually
 done all that already; source code was last sync'd to the build farm
 master at the end of March:  https://github.com/greg2ndQuadrant/client-code
 
 By far the #1 thing needed to move this forward from where it's stuck at
 now is someone willing to dig into the web application side of this. 
 We're collecting useful data.  It needs to now be uploaded to the
 server, saved, and then reports of what happened generated.  Eventually
 graphs of performance results over time will be straighforward to
 generate.  But the whole idea requires someone else (not Andrew, who has
 enough to do) sits down and figures out how to extend the web UI with
 these new elements.

Hi,

I'd like to revive this thread. A few days ago we have finally got our
buildfarm member working (it's called magpie) - it's spending ~2h a
day chewing on the buildfarm tasks, so we can use the other 22h to do
some useful work.

I suppose most of you are busy with 9.2 features, but I'm not so I'd
like to spend my time on this.

Now that I had to set up the buildfarm member I'm somehow aware of how
the buildfarm works. I've checked the PGBuildFarm/server-code and
greg2ndQuadrant/client-code repositories and while I certainly am not a
perl whiz, I believe I can tweak it to handle the performance-related
result too.

What is the current state of this effort? Is there someone else working
on that? If not, I propose this (for starters):

  * add a new page Performance results to the menu, with a list of
members that uploaded the perfomance-results

  * for each member, there will be a list of tests along with a running
average for each test, last test and indicator if it improved, got
worse or is the same

  * for each member/test, a history of runs will be displayed, along
with a simple graph


I'm not quite sure how to define which members will run the performance
tests - I see two options:

  * for each member, add a flag run performance tests so that we can
choose which members are supposed to be safe

  OR

  * run the tests on all members (if enabled in build-farm.conf) and
then decide which results are relevant based on data describing the
environment (collected when running the tests)


I'm also wondering if

  * using the buildfarm infrastructure the right thing to do, if it can
provide some 'advanced features' (see below)

  * we should use the current buildfarm members (although maybe not all
of them)

  * it can handle one member running the tests with different settings
(various shared_buffer/work_mem sizes, num of clients etc.) and
various hw configurations (for example magpie contains a regular
SATA drive as well as an SSD - would be nice to run two sets of
tests, one for the spinner, one for the SSD)

  * this can handle 'pushing' a list of commits to test (instead of
just testing the HEAD) so that we can ask the members to run the
tests for particular commits in the past (I consider this to be
very handy feature)


regards
Tomas

-- 
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] elegant and effective way for running jobs inside a database

W dniu 2012-03-05 23:09, Jaime Casanova pisze:
 On Mon, Mar 5, 2012 at 5:03 PM, Artur Litwinowicz ad...@ybka.com wrote:

 I understand it... (I meant if you wanna something... do it for your
 self - it is the fastest way).
 
 other way is to fund the work so someone can use his/her time to do it
 
 Regarding a functional area I can help... but I can not understand why
 this idea is so unappreciated?
 
 is not unappreciated, is just a problem that already *has* a solution
 if it were something that currently you can't do it then there would
 be more people after it
 
 It will be so powerfull feature - I am working with systems made for
 goverment (Orcale) - jobs are the core gears for data flow between many
 systems and other goverment bureaus.

 
 me too, and we solve it with cron
 

And You can modulate the jobs frequency, stop them and start from inside
the database automatically using only algorithms and interenal events
without administrator hand work... with cron... I can not belive... I do
not meant just simple: run stored procedure... I am using cron as well,
but in my work I like elegant, complex solutions - many lego blocks is
not always the best and simplest solution...


0xAF4A859D.asc
Description: application/pgp-keys

-- 
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] elegant and effective way for running jobs inside a database

2012-03-05 Thread Christopher Browne

On Mon, Mar 5, 2012 at 4:44 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:

 Excerpts from Artur Litwinowicz's message of lun mar 05 18:32:44 -0300 2012:

 Ouch... in next 2-4 years - it broke my heart like a bullet - You
 should not write it... ;)
 I feel that I need to set aside SQL, Python, PHP and so on and take to
 my hands old book about C programming language from university ;)
 I hope my words are like drops of water for this idea and in the future
 some people will be happy to use professional job manager :)

 Keep in mind that it's not about coding in C but mostly about figuring
 out what a sane design out to look like.

Just so.

And it seems to me that the Right Thing here is to go down the road to
having the fabled Stored Procedure Language, which is *not* pl/pgsql,
in that iIt needs to run *outside* transactional context.  It needs to
be able to start transactions, not to run inside them.

Given a language which can do some setup of transactions and then run
them, this could be readily used for a number of useful purposes, of
which a job scheduler would be just a single example.

It would enable turning some backend processes from hand-coded C into
possibly more dynamically-flexible scripted structures.

I'd expect this to be useful for having more customizable/dynamic
policies for the autovacuum process, for instance.
-- 
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] elegant and effective way for running jobs inside a database

2012-03-05 Thread Daniel Farina

On Mon, Mar 5, 2012 at 12:17 PM, Pavel Stehule pavel.steh...@gmail.com wrote:
 Hello

 2012/3/5 Alvaro Herrera alvhe...@commandprompt.com:

 Excerpts from Artur Litwinowicz's message of lun mar 05 16:18:56 -0300 2012:
 Dear Developers,
    I am looking for elegant and effective way for running jobs inside a
 database or cluster - for now I can not find that solution.

 Yeah, it'd be good to have something.  Many people say it's not
 necessary, and probably some hackers would oppose it; but mainly I think
 we just haven't agreed (or even discussed) what the design of such a
 scheduler would look like.  For example, do we want it to be able to
 just connect and run queries and stuff, or do we want something more
 elaborate able to start programs such as running pg_dump?  What if the
 program crashes -- should it cause the server to restart?  And so on.
 It's not a trivial problem.


 I agree - it is not simple

 * workflow support
 * dependency support

 a general ACID scheduler can be nice (in pg) but it is not really
 simple. There was some proposal about using autovacuum demon like
 scheduler.

I've been thinking about making autovacuum a special case of a general
*non*-transactional job-running system because dealing with large
physical changes to a database (where one wants to rewrite 300GB of
data, or whatever) that are prohibitive in a transaction are -- to
understate things -- incredibly painful.  Painful enough that people
will risk taking their site down with a large UPDATE or ALTER TABLE,
hoping that they can survive the duration (and then when they cancel
it and are left with huge volumes of dead tuples, things get a lot
more ugly).

The closest approximation a client program can make is well, I guess
I'll paginate through the database and rewrite small chunks. Instead,
it may make more sense to have the database spoon-feed work to do the
transformations little-at-a-time ala autovacuum.

--
fdr

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

Re: [HACKERS] WIP: URI connection string support for libpq

2012-03-05 Thread Peter Eisentraut

On ons, 2012-02-22 at 12:26 -0500, Greg Smith wrote:
 I started collecting up all the variants that do work as an 
 initial shell script regression test, so that changes don't break 
 something that already works.  Here are all the variations that
 already work, setup so that a series of 1 outputs is passing: 

Let's please add something like this to the patch.  Otherwise, I foresee
a lot of potential to break corner cases in the future.



-- 
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] elegant and effective way for running jobs inside a database

2012-03-05 Thread David Johnston

 
  Keep in mind that it's not about coding in C but mostly about figuring
  out what a sane design out to look like.
 
 

While I can straddle the fence pretty my first reaction is that we are talking 
about application functionality that falls outside what belongs in core 
PostgreSQL.  I'd rather see pgAgent be improved and act as a basic 
implementation while, for more complex use-cases, letting the 
community/marketplace provide solutions.

Even with simple use-cases you end up having a separate process continually 
running anyway.  The main benefit to linking with core would be the ability to 
startup that process after the server starts and shutdown the process before 
the server shutdown.  That communication channel is something to consider 
outside this specific application and, if done, could be used to talk with 
whatever designated pgAgent-like application the user chooses.  Other 
applications could also be communicated with in this way.  Basically some form 
of API where in the postgres.conf file you specify which IP addresses and ports 
you wish to synchronize and which executable to launch just prior to 
communicating on said port.  If the startup routine succeeds that Postgres 
will, within reason, attempt to communicate and wait for these external process 
to finish before shutting down.  If the external application closes it should 
proactively notify Postgres that it is doing so AND if you startup a program 
manually it can look for and talk with a running Postgres instance.

David J.


 


-- 
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] Checksums, state of play

2012-03-05 Thread Josh Berkus


 3. Pages with checksums set need to have a version marking to show
 that they are a later version of the page layout. That version number
 needs to be extensible to many later versions. Pages of multiple
 versions need to exist within the server to allow simple upgrades and
 migration.

This is a statement of a problem; do you have a proposed solution for it?

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

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

[HACKERS] Dropping PL language retains support functions

2012-03-05 Thread Bruce Momjian

I have a report related to pg_upgrade where the user states that
dropping a PL language retains the PL support functions, and retains the
dependency on the PL library, which causes pg_upgrade to complain.  The
exact case is that the user was using plpython2u in PG 9.0, but the PG
9.1 one-click installer only supplies plpython3u.

Pg_upgrade rightly complains that the $libdir/plpython2 is missing.  The
user removed their plpython2 functions, and then tried pg_upgrade again,
and they still got the report of the missing $libdir/plpython2 library.

I tested this myself on PG HEAD, and got the same results:

CREATE LANGUAGE plpython2u;
CREATE LANGUAGE

CREATE OR REPLACE FUNCTION pymax (a integer, b integer) RETURNS integer
AS
 $$
 if a  b:
   return a
 return b
 $$ LANGUAGE plpython2u;
CREATE FUNCTION

DROP LANGUAGE plpython2u CASCADE;
NOTICE:  drop cascades to function pymax(integer,integer)
DROP LANGUAGE

SELECT proname,probin FROM pg_proc WHERE probin LIKE '%python%';
 proname  |  probin
--+---
 plpython2_call_handler   | $libdir/plpython2
 plpython2_inline_handler | $libdir/plpython2
 plpython2_validator  | $libdir/plpython2
(3 rows)

I looked at our C code, and we basically set up this dependency:

user plpython2 function 
depends on
plpython2 language
depends on
plpython2_* support functions

By doing a DROP CASCADE on plpython2, you drop the user functions, but
not the support functions.

This certainly looks like a bug.  Should I work on a patch?
 
-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

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

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

Re: [HACKERS] Speed dblink using alternate libpq tuple storage

2012-03-05 Thread Kyotaro HORIGUCHI

Hello, I'm sorry for the abesnce.

 But it's broken in V3 protocol - getAnotherTuple() will be called
 only if the packet is fully read.  If the packet contents do not
 agree with packet header, it's protocol error.  Only valid EOF
 return in V3 getAnotherTuple() is when row processor asks
 for early exit.

 Original code of getAnotherTuple returns EOF when the bytes to
be read is not fully loaded. I understand that this was
inappropriately (redundant checks?) written at least for the
pqGetInt() for the field length in getAnotherTuple.  But I don't
understand how to secure the rows (or table data) fully loaded at
the point of getAnotherTuple called...

Nevertheles the first pgGetInt() can return EOF when the
previsous row is fully loaded but the next row is not loaded so
the EOF-rerurn seems necessary even if the each row will passed
after fully loaded.

 * Convert old EOFs to protocol errors in V3 getAnotherTuple()

Ok. I will do that.

 * V2 getAnotherTuple() can leak PGresult when handling custom
   error from row processor.

mmm. I will confirm it.

 * remove pqIsnonblocking(conn) check when row processor returned 2.
   I missed that it's valid to call PQisBusy/PQconsumeInput/PQgetResult
   on sync connection.

mmm. EOF from getAnotherTuple makes PQgetResult try furthur
reading until asyncStatus != PGASYNC_BUSY as far as I saw. And It
seemed to do so when I tried to remove 'return 2'. I think that
it is needed at least one additional state for asyncStatus to
work EOF as desied here.


 * It seems the return codes from callback should be remapped,
   (0, 1, 2) is unusual pattern.  Better would be:
 
-1 - error
 0 - stop parsing / early exit (I'm not done yet)
 1 - OK (I'm done with the row)

I almost agree with it. I will consider the suggestion related to
pqAddRow together.


 * Please drop PQsetRowProcessorErrMsg() / PQresultSetErrMsg().
   Main problem is that it needs to be synced with error handling
   in rest of libpq, which is unlike the rest of row processor patch,
   which consists only of local changes.  All solutions here
   are either ugly hacks or too complex to be part of this patch.

Ok, I will take your advice. 

 Also considering that we have working exceptions and PQgetRow,
 I don't see much need for custom error messages.  If really needed,
 it should be introduced as separate patch, as the area of code it
 affects is completely different.

I agree with it.

 Currently the custom error messaging seems to be the blocker for
 this patch, because of raised complexity when implementing it and
 when reviewing it.  Considering how unimportant the provided
 functionality is, compared to rest of the patch, I think we should
 simply drop it.

Ok.

 My suggestion - check in getAnotherTuple whether resultStatus is
 already error and do nothing then.  This allows internal pqAddRow
 to set regular out of memory error.  Otherwise give generic
 row processor error.

regards, 

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

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

Re: [HACKERS] pg_upgrade --logfile option documentation

2012-03-05 Thread Bruce Momjian

On Tue, Feb 28, 2012 at 09:45:41PM -0500, Bruce Momjian wrote:
 On Tue, Feb 28, 2012 at 02:15:30PM -0500, Bruce Momjian wrote:
  On Tue, Feb 28, 2012 at 01:24:45PM -0500, Robert Haas wrote:
Running this script will delete the old cluster's data files:
   /usr/local/pgdev/pg_upgrade/delete_old_cluster.sh
   
   I think you should rename the old control file just before the step
   that says linking user relation files.  That's the point after which
   it becomes unsafe to start the old cluster, right?
  
  Yes, it is true that that is the danger point, and also it is much less
  likely to fail at that point --- it usually happens during the schema
  creation.  I would have to add some more conditional wording without
  clearly stating if the old suffix is present.
 
 OK, I have implemented both Roberts and Àlvaro's ideas in my patch. 
 I only add the .old suffix to pg_controldata when link mode is used, and
 I now do it after the schema has been created (the most common failure
 case for pg_upgrade), and just before we actually link files --- both
 very good ideas.
 
 Patch attached;  new pg_upgrade output with link mode below.

Patch applied.  I will now work on the change to keep the schema restore
and server logs around in case of a failure.

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

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

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

Re: [HACKERS] Dropping PL language retains support functions

Bruce Momjian br...@momjian.us writes:
 By doing a DROP CASCADE on plpython2, you drop the user functions, but
 not the support functions.

Well, yeah.  The language depends on the support functions, not the
other way around.

 This certainly looks like a bug.  Should I work on a patch?

It's not a bug, and it's unlikely you can fix it in pg_upgrade without
making things worse.

The long-run plan is that the procedural language and its support
functions are all part of an extension and what you do is drop the
extension.  We're not quite there yet.  As of 9.1, if you do create
extension plpython2 to start with, dropping the extension does drop the
support functions too ... but if you use the legacy create language
syntax, that doesn't happen, because an extension object isn't created.

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] ECPG FETCH readahead

2012-03-05 Thread Boszormenyi Zoltan

2012-03-05 19:56 keltezéssel, Noah Misch írta:

 Having pondered the matter further, I now agree with Michael that the feature
 should stay disabled by default.  See my response to him for rationale.
 Assuming that conclusion holds, we can recommended a higher value to users who
 enable the feature at all.  Your former proposal of 256 seems fine.

OK.


 BTW, the default disabled behaviour was to avoid make check breakage,
 see below.

   I would not offer
 an ecpg-time option to disable the feature per se.  Instead, let the user 
 set
 the default chunk size at ecpg time.  A setting of 1 effectively disables 
 the
 feature, though one could later re-enable it with ECPGFETCHSZ.
 This means all code previously going through ECPGdo() would go through
 ECPGopen()/ECPGfetch()/ECPGclose(). This is more intrusive and all
 regression tests that were only testing certain features would also
 test the readahead feature, too.
 It's a good sort of intrusiveness, reducing the likelihood of introducing bugs
 basically unrelated to readahead that happen to afflict only ECPGdo() or only
 the cursor.c interfaces.  Let's indeed not have any preexisting test cases use
 readahead per se, but having them use the cursor.c interfaces anyway will
 build confidence in the new code.  The churn in expected debug output isn't
 ideal, but I don't prefer the alternative of segmenting the implementation for
 the sake of the test cases.

I see.


 Also, the test for WHERE CURRENT OF at ecpg time would have to be done
 at runtime, possibly making previously working code fail if ECPGFETCHSZ is 
 enabled.
 Good point.

 How about still allowing NO READAHEAD cursors that compile into plain 
 ECPGdo()?
 This way, ECPGFETCHSZ don't interfere with WHERE CURRENT OF. But this would
 mean code changes everywhere where WHERE CURRENT OF is used.
 ECPGFETCHSZ should only affect cursors that make no explicit mention of
 READAHEAD.  I'm not sure whether that should mean actually routing READHEAD 1
 cursors through ECPGdo() or simply making sure that cursor.c achieves the same
 outcome; see later for a possible reason to still do the latter.

 Or how about a new feature in the backend, so ECPG can do
 UPDATE/DELETE ... WHERE OFFSET N OF cursor
 and the offset of computed from the actual cursor position and the position 
 known
 by the application? This way an app can do readahead and do work on rows 
 collected
 by the cursor with WHERE CURRENT OF which gets converted to WHERE OFFSET OF
 behind the scenes.
 That's a neat idea, but I would expect obstacles threatening our ability to
 use it automatically for readahead.  You would have to make the cursor a
 SCROLL cursor.  We'll often pass a negative offset, making the operation fail
 if the cursor query used FOR UPDATE.  Volatile functions in the query will get
 more calls.  That's assuming the operation will map internally to something
 like MOVE N; UPDATE ... WHERE CURRENT OF; MOVE -N.  You might come up with
 innovations to mitigate those obstacles, but those innovations would probably
 also apply to MOVE/FETCH.  In any event, this would constitute a substantive
 patch in its own right.

I was thinking along the lines of a Portal keeping the ItemPointerData
for each tuple in the last FETCH statement. The WHERE OFFSET N OF cursor
would treat the offset value relative to the tuple order returned by FETCH.
So, OFFSET 0 OF == CURRENT OF and other values of N are negative.
This way, it doesn't matter if the cursor is SCROLL, NO SCROLL or have
 the default behaviour with SCROLL in some cases. Then ECPGopen()
doesn't have to play games with the DECLARE statement. Only ECPGfetch()
needs to play with MOVE statements, passing different offsets to the backend,
not what the application passed.

 One way out of trouble here is to make WHERE CURRENT OF imply READHEAD
 1/READHEAD 0 (incidentally, perhaps those two should be synonyms) on the
 affected cursor.  If the cursor has some other readahead quantity declared
 explicitly, throw an error during preprocessing.

I played with this idea a while ago, from a different point of view.
If the ECPG code had the DECLARE mycur, DML ... WHERE CURRENT OF mycur
and OPEN mycur in exactly this order, i.e. WHERE CURRENT OF appears in
a standalone function between DECLARE and the first OPEN for the cursor,
then ECPG disabled readahead automatically for that cursor and for that
cursor only. But this requires effort on the user of ECPG and can be very
fragile. Code cleanup with reordering functions can break previously
working code.

 Failing a reasonable resolution, I'm prepared to withdraw my suggestion of
 making ECPGFETCHSZ always-usable.  It's nice to have, not critical.

 +bool
 +ECPGopen(const int lineno, const int compat, const int force_indicator,
 +  const char *connection_name, const bool questionmarks,
 +  const char *curname, const int st, const char *query, ...)
 +{
 +  va_list args;
 +  boolret, scrollable;
 +  char

Re: [HACKERS] review: CHECK FUNCTION statement