Re: [HACKERS] Patch for BUG #6480, psql incorrect indent for inherited tables names with UTF-8 NLS

2012-02-25 Thread Sergey Burladyan
Alvaro Herrera alvhe...@commandprompt.com writes:

 I'm sorry, but the releases are already tagged :-(  So they will contain
 the buggy output for a while yet.

Ah, I see, ok, wait next! :)

-- 
Sergey Burladyan

-- 
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] Checking pg_hba.conf in the child process

2012-02-25 Thread Magnus Hagander
On Sat, Feb 25, 2012 at 00:45, Tom Lane t...@sss.pgh.pa.us wrote:
 Alvaro Herrera alvhe...@commandprompt.com writes:
 Excerpts from Bruce Momjian's message of vie feb 24 19:19:10 -0300 2012:
 In looking over our authentication code, I noticed that we create the
 child process before we check any of the pg_hba.conf file.  Now, I
 realize we can't do authentication in the postmaster because of possible
 delay, and checking the user name and database name filters is just work
 that is better done in the child, but checking the IP address might
 prevent unauthorized clients from causing excessive process creation on
 the server.  I know we have listen_addresses, but that defaults to *
 on the click-through installers, and not everybody knows how to set up a
 firewall.

 Hm, one thing to keep in mind is that we allow hostnames there.  It'd be
 a pain to have postmaster hang while resolving names.

 Yes.  This cure would be a lot worse than the disease.  Bruce ought to
 remember that we intentionally moved all that logic *out* of the
 postmaster process, years ago, precisely because it was too hard to
 ensure that the postmaster wouldn't block and thus create DOS conditions
 of another sort.

As long as the block would only look at the IP it would also be
trivial - and more efficient - to do the same blocking in the
firewall, either local host firewall rules or the network firewall
depending on deployment...

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

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


Re: [HACKERS] Command Triggers, patch v11

2012-02-25 Thread Thom Brown
On 24 February 2012 23:43, Thom Brown t...@linux.com wrote:
 On 24 February 2012 23:01, Thom Brown t...@linux.com wrote:
 On 24 February 2012 22:39, Thom Brown t...@linux.com wrote:
 On 24 February 2012 22:32, Thom Brown t...@linux.com wrote:
 On 24 February 2012 22:04, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:
 Hi,

 Please find attached the latest version of the command triggers patch,
 in context diff format, with support for 79 commands and documentation
 about why only those, and with some limitations explained.

 I also cleaned up the node function support business that was still in
 there from the command rewriting stuff that we dropped, and did a merge
 from tonight's master branch (one of a few clean merges).

 This is now the whole of it, and I continue being available to make any
 necessary change, although I expect only minor changes now.  Thanks to
 all reviewers and participants into the previous threads, you all have
 allowed me to reach the current point by your precious advice, comments
 and interest.

 The patch implements :

  - BEFORE/AFTER ANY command triggers
  - BEFORE/AFTER command triggers for 79 documented commands
  - regression tests exercised by the serial schedule only
  - documentation updates with examples

 That means you need to `make installcheck` here. Installing the tests in
 the parallel schedule does not lead to consistent output as installing a
 command trigger will impact any other test using that command, and the
 output becomes subject to the exact ordering of the concurrent tests.

 The only way for a BEFORE command triggers to change the command's
 behaviour is by raising an exception that aborts the whole transaction.

 Command triggers are called with the following arguments:

  - the “event” (similar to TG_WHEN, either 'BEFORE' or 'AFTER')
  - the command tag (the real one even when an ANY trigger is called)
  - the object Id if available (e.g. NULL for a CREATE statement)
  - the schema name (can be NULL)
  - the object name (can be NULL)

 When the trigger's procedure we're calling is written in C, then another
 argument is passed next, which is the parse tree Node * pointer.

 I've been talking with Marko Kreen about supporting ALTER TABLE and such
 commands automatically in Londiste: given that patch, it requires
 writing code in C that will rewrite the command string.  It so happens
 that I already have worked on that code, so we intend on bringing
 support for ALTER TABLE and other commands in Skytools 3 for 9.2.

 I think the support code should be made into an extension that both
 Skytools and Slony would be able to share. The extension code will be
 able to adapt to major versions changes as they are released.  Bucardo
 would certainly be interested too, we could NOTIFY it from such an
 extension.  The design is yet to be done here, but it's clearly possible
 to implement such a feature given the current patch.

 The ANY trigger support is mainly there to allow people to stop any DDL
 traffic on their databases, then allowing it explicitly with an ALTER
 COMMAND TRIGGER ... SET DISABLE when appropriate only.  To that
 end, the ANY command trigger is supporting more commands than you can
 attach specific triggers too.

 It's also possible to ENABLE a command trigger on the REPLICA only
 thanks to the session_replication_role GUC.  Support for command
 triggers on an Hot Standby node is not provided in this patch.

 Hi Dimitri,

 I just tried building the docs with your patch and it appears
 doc/src/sgml/ref/allfiles.sgml hasn't been updated with the necessary
 references for alterCommandTrigger, createCommandTrigger and
 dropCommandTrigger.

 Also in ref/alter_command_trigger.sgml, you define SQL-CREATETRIGGER.
 Shouldn't this be SQL-CREATECOMMANDTRIGGER?  And there also appears to
 be orphaned text in the file too, such as Forbids the execution of
 any DDL command.  And there's a stray /para on line 299.

 I attach updated versions of both of those files, which seems to fix
 all these problems.

 I've just noticed there's an issue with
 doc/src/sgml/ref/alter_command_trigger.sgml too.  It uses indexterm
 zone=sql-altertrigger which should be sql-altercommandtrigger. (as
 attached)

 And upon trying to test the actual feature, it didn't work for me at
 all.  I thought I had applied the patch incorrectly, but I hadn't, it
 was the documentation showing the wrong information.  The CREATE
 COMMAND TRIGGER page actually just says CREATE TRIGGER BEFORE
 COMMAND command, which isn't the correct syntax.

 Also the examples on the page are incorrect in the same regard.  When
 I tested it with the correction, I got an error saying that the
 function used had to return void, but the example uses bool.  Upon
 also changing this, the example works as expected.

 Is there any reason why the list of commands that command triggers can
 be used with isn't in alphabetical order?  Also it appears to show
 CREATE/ALTER/DROP TYPE_P, and the same for 

Re: [HACKERS] Command Triggers, patch v11

2012-02-25 Thread Thom Brown
On 25 February 2012 12:00, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:

D'oh, just as I sent some more queries...

 Thom Brown t...@linux.com writes:
 Is there any reason why the list of commands that command triggers can
 be used with isn't in alphabetical order?  Also it appears to show

 Any reason why?  I don't suppose it's really important one way or the
 other, so I'm waiting on some more voices before working on it.

Just so it's easy to scan.  If someone is looking for CREATE CAST,
they'd kind of expect it near the drop of the CREATE list, but it's
actually toward the bottom.  It just looks random at the moment.

 The ALTER COMMAND TRIGGER page also doesn't show which commands it can
 be used against.  Perhaps, rather than repeat the list, there could be
 a note to say that a list of valid commands can be found on the CREATE
 COMMAND TRIGGER page?

 Well you can only alter a command that you were successful in creating,
 right?  So I'm not sure that's needed here.  By that count though, I
 maybe should remove the supported command list from DROP COMMAND TRIGGER
 reference page?

Sure, that would be more consistent.  You're right, it's not needed.
It just seemed odd that one of the statements lacked what both others
had.

Thanks

-- 
Thom

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

2012-02-25 Thread Magnus Hagander
On Fri, Feb 10, 2012 at 09:32, Fujii Masao masao.fu...@gmail.com wrote:
 On Fri, Feb 10, 2012 at 7:00 AM, Euler Taveira de Oliveira
 eu...@timbira.com wrote:
 On 08-02-2012 09:35, Fujii Masao wrote:

 Fujii, new patch attached. Thanks for your tests.

 Thanks for the new patch!

 But another problem happened. When I changed pg_proc.h so that the unused
 OID was assigned to pg_xlog_location_diff(), and executed the above again,
 I encountered the segmentation fault:

 I reproduced the problems in my old 32-bit laptop. I fixed it casting to
 int64. I also updated the duplicated OID.

 Yep, in the updated patch, I could confirm that the function works fine 
 without
 any error in my machine. The patch looks fine to me except the following minor
 comments:

I started working on this one to commit it, and came up with a few things more.

Do we even *need* the validate_xlog_location() function? If we just
remove those calls, won't we still catch all the incorrectly formatted
ones in the errors of the sscanf() calls? Or am I too deep into
weekend-mode and missing something obvious?

I've also removed tabs in the documentation, fixed the merge confllict
in pg_proc.h that happened during the wait, and fixed some indentation
(updated patch with these changes attached).

But I'm going to hold off committing it until someone confirms I'm not
caught too deeply in weekend-mode and am missing something obvious in
the comment above about validate_xlog_location.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e8e637b..4ae76e2 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -14454,11 +14454,15 @@ SELECT set_config('log_statement_stats', 'off', false);
indexterm
 primarypg_xlogfile_name_offset/primary
/indexterm
+   indexterm
+primarypg_xlog_location_diff/primary
+   /indexterm
 
para
 The functions shown in xref
 linkend=functions-admin-backup-table assist in making on-line backups.
-These functions cannot be executed during recovery.
+These functions cannot be executed during recovery (except
+functionpg_xlog_location_diff/function).
/para
 
table id=functions-admin-backup-table
@@ -14526,6 +14530,13 @@ SELECT set_config('log_statement_stats', 'off', false);
entrytypetext/, typeinteger//entry
entryConvert transaction log location string to file name and decimal byte offset within file/entry
   /row
+  row
+   entry
+literalfunctionpg_xlog_location_diff(parameterlocation/ typetext/, parameterlocation/ typetext/)/function/literal
+   /entry
+   entrytypenumeric//entry
+   entryCalculate the difference between two transaction log locations/entry
+  /row
  /tbody
 /tgroup
/table
@@ -14619,6 +14630,13 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
/para
 
para
+functionpg_xlog_location_diff/ calculates the difference in bytes
+between two transaction log locations. It can be used with
+structnamepg_stat_replication/structname or some functions shown in
+xref linkend=functions-admin-backup-table to get the replication lag.
+   /para
+
+   para
 For details about proper usage of these functions, see
 xref linkend=continuous-archiving.
/para
diff --git a/src/backend/access/transam/xlogfuncs.c b/src/backend/access/transam/xlogfuncs.c
index 2e10d4d..b8f8152 100644
--- a/src/backend/access/transam/xlogfuncs.c
+++ b/src/backend/access/transam/xlogfuncs.c
@@ -26,6 +26,7 @@
 #include replication/walreceiver.h
 #include storage/smgr.h
 #include utils/builtins.h
+#include utils/numeric.h
 #include utils/guc.h
 #include utils/timestamp.h
 
@@ -465,3 +466,87 @@ pg_is_in_recovery(PG_FUNCTION_ARGS)
 {
 	PG_RETURN_BOOL(RecoveryInProgress());
 }
+
+static void
+validate_xlog_location(char *str)
+{
+#define MAXLSNCOMPONENT		8
+
+	int			len1,
+len2;
+
+	len1 = strspn(str, 0123456789abcdefABCDEF);
+	if (len1  1 || len1  MAXLSNCOMPONENT || str[len1] != '/')
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg(invalid input syntax for transaction log location: \%s\, str)));
+
+	len2 = strspn(str + len1 + 1, 0123456789abcdefABCDEF);
+	if (len2  1 || len2  MAXLSNCOMPONENT || str[len1 + 1 + len2] != '\0')
+		ereport(ERROR,
+(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
+ errmsg(invalid input syntax for transaction log location: \%s\, str)));
+}
+
+/*
+ * Compute the difference in bytes between two WAL locations.
+ */
+Datum
+pg_xlog_location_diff(PG_FUNCTION_ARGS)
+{
+	text	   *location1 = PG_GETARG_TEXT_P(0);
+	text	   *location2 = PG_GETARG_TEXT_P(1);
+	char	   *str1,
+			   *str2;
+	XLogRecPtr	loc1,
+loc2;
+	Numeric		result;
+
+	/*
+	 * Read and parse input
+	 */
+	str1 = text_to_cstring(location1);
+	str2 = text_to_cstring(location2);
+
+	validate_xlog_location(str1);
+	validate_xlog_location(str2);
+
+	if 

Re: [HACKERS] Command Triggers, patch v11

2012-02-25 Thread Thom Brown
On 25 February 2012 12:07, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:00, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:

 D'oh, just as I sent some more queries...

 Thom Brown t...@linux.com writes:
 Is there any reason why the list of commands that command triggers can
 be used with isn't in alphabetical order?  Also it appears to show

 Any reason why?  I don't suppose it's really important one way or the
 other, so I'm waiting on some more voices before working on it.

 Just so it's easy to scan.  If someone is looking for CREATE CAST,
 they'd kind of expect it near the drop of the CREATE list, but it's
 actually toward the bottom.  It just looks random at the moment.

 The ALTER COMMAND TRIGGER page also doesn't show which commands it can
 be used against.  Perhaps, rather than repeat the list, there could be
 a note to say that a list of valid commands can be found on the CREATE
 COMMAND TRIGGER page?

 Well you can only alter a command that you were successful in creating,
 right?  So I'm not sure that's needed here.  By that count though, I
 maybe should remove the supported command list from DROP COMMAND TRIGGER
 reference page?

 Sure, that would be more consistent.  You're right, it's not needed.
 It just seemed odd that one of the statements lacked what both others
 had.

Yet another comment... (I should have really started looking at this
at an earlier stage)

It seems that if one were to enforce a naming convention for relations
as shown in the 2nd example for CREATE COMMAND TRIGGER, it could be
circumvented by someone using CREATE TABLE name AS...

test=# CREATE TABLE badname (id int, a int, b text);
ERROR:  invalid relation name: badname
test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b;
SELECT 1

This doesn't even get picked up by ANY COMMAND.

-- 
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] Website stylesheet for local docs

2012-02-25 Thread Magnus Hagander
I've asked for this a few times before, but it seems others aren't as
keen on it as me :-) Personally, I find the docs easier to read when
formatted with the new website styles that Thom put together, and I
also like to see things the way they're going to look when they go up
there.

Attached patch makes it possible to say make STYLE=website for the
docs, which will then simply replace the stylesheet reference with one
that goes to fetch docs.css on the website. I'm not suggesting we
change the default or anything, just making it reasonably easy to get
it done for one-off builds.

I don't really speak the DSSSL naugage, so there might be a better way
of doing it..

Comments?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/
diff --git a/doc/src/sgml/Makefile b/doc/src/sgml/Makefile
index e6c8a49..19e640b 100644
--- a/doc/src/sgml/Makefile
+++ b/doc/src/sgml/Makefile
@@ -89,6 +89,9 @@ man-stamp: stylesheet-man.xsl postgres.xml
 .PHONY: draft
 
 JADE.html.call = $(JADE) $(JADEFLAGS) $(SPFLAGS) $(SGMLINCLUDE) $(CATALOG) -d stylesheet.dsl -t sgml -i output-html
+ifeq ($(STYLE),website)
+JADE.html.call += -V website-stylesheet
+endif
 
 # The draft target creates HTML output in draft mode, without index (for faster build).
 draft: postgres.sgml $(ALMOSTALLSGML) stylesheet.dsl
diff --git a/doc/src/sgml/stylesheet.dsl b/doc/src/sgml/stylesheet.dsl
index 232fa58..4179643 100644
--- a/doc/src/sgml/stylesheet.dsl
+++ b/doc/src/sgml/stylesheet.dsl
@@ -29,6 +29,7 @@
 !-- (applicable to all output formats) --
 
 (define draft-mode  #f)
+(define website-stylesheet  #f)
 
 (define pgsql-docs-list pgsql-d...@postgresql.org)
 
@@ -190,7 +191,7 @@
 (define %root-filename% index)
 (define %link-mailto-url%   (string-append mailto: pgsql-docs-list))
 (define %use-id-as-filename%#t)
-(define %stylesheet%stylesheet.css)
+(define %stylesheet%(if website-stylesheet http://www.postgresql.org/media/css/docs.css; stylesheet.css))
 (define %graphic-default-extension% gif)
 (define %gentext-nav-use-ff%#t)
 (define %body-attr% '())

-- 
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] Website stylesheet for local docs

2012-02-25 Thread Pavel Stehule
2012/2/25 Magnus Hagander mag...@hagander.net:
 I've asked for this a few times before, but it seems others aren't as
 keen on it as me :-) Personally, I find the docs easier to read when
 formatted with the new website styles that Thom put together, and I
 also like to see things the way they're going to look when they go up
 there.

 Attached patch makes it possible to say make STYLE=website for the
 docs, which will then simply replace the stylesheet reference with one
 that goes to fetch docs.css on the website. I'm not suggesting we
 change the default or anything, just making it reasonably easy to get
 it done for one-off builds.

 I don't really speak the DSSSL naugage, so there might be a better way
 of doing it..

+1

Pavel


 Comments?

 --
  Magnus Hagander
  Me: http://www.hagander.net/
  Work: http://www.redpill-linpro.com/


 --
 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-02-25 Thread Thom Brown
On 25 February 2012 12:42, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:07, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:00, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:

 D'oh, just as I sent some more queries...

 Thom Brown t...@linux.com writes:
 Is there any reason why the list of commands that command triggers can
 be used with isn't in alphabetical order?  Also it appears to show

 Any reason why?  I don't suppose it's really important one way or the
 other, so I'm waiting on some more voices before working on it.

 Just so it's easy to scan.  If someone is looking for CREATE CAST,
 they'd kind of expect it near the drop of the CREATE list, but it's
 actually toward the bottom.  It just looks random at the moment.

 The ALTER COMMAND TRIGGER page also doesn't show which commands it can
 be used against.  Perhaps, rather than repeat the list, there could be
 a note to say that a list of valid commands can be found on the CREATE
 COMMAND TRIGGER page?

 Well you can only alter a command that you were successful in creating,
 right?  So I'm not sure that's needed here.  By that count though, I
 maybe should remove the supported command list from DROP COMMAND TRIGGER
 reference page?

 Sure, that would be more consistent.  You're right, it's not needed.
 It just seemed odd that one of the statements lacked what both others
 had.

 Yet another comment... (I should have really started looking at this
 at an earlier stage)

 It seems that if one were to enforce a naming convention for relations
 as shown in the 2nd example for CREATE COMMAND TRIGGER, it could be
 circumvented by someone using CREATE TABLE name AS...

 test=# CREATE TABLE badname (id int, a int, b text);
 ERROR:  invalid relation name: badname
 test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b;
 SELECT 1

 This doesn't even get picked up by ANY COMMAND.

CREATE COMMAND TRIGGER doesn't output in pg_dump or pg_dumpall.  I'd
expect ALTER COMMAND TRIGGER to output too for when individual
commands are disabled etc.

-- 
Thom

-- 
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] Runtime SHAREDIR for testing CREATE EXTENSION

2012-02-25 Thread Christoph Berg
Re: Peter Eisentraut 2012-02-24 1330107599.32452.15.ca...@vanquo.pezone.net
 On fre, 2012-02-24 at 11:53 -0500, Tom Lane wrote:
   We have the same problem with testing extensions at build-time in
  the
   Debian packages. The server's SHAREDIR /usr/share/postgresql/... is
   only writable by root, while the build is running as buildd user, so
   there is no way to do create extension whatimbuildingrightnow to
  be
   able to run regression tests, even if this is a cluster I have just
   created with initdb.
  
  This seems like nonsense.  If the build process has installed the
  software, you surely have got permissions to write in that directory.
 
 The build process just installs the software in a fake root where it
 will be wrapped up by the packaging software.
 
  If you haven't installed the software, you need to do testing in a
  temporary installation per make check, and we are able to test
  extensions that way too.
  
 That looks like the right answer.

Well, I'm trying to invoke the extension's make check target at
extension build time. I do have a temporary installation I own
somehwere in my $HOME, but that is still trying to find extensions in
/usr/share/postgresql/9.1/extension/*.control, because I am using the
system's postgresql version. The build process is not running as root,
so I cannot do an install of the extension to its final location.
Still it would be nice to run regression tests. All that seems to be
missing is the ability to put

extension_control_path = /home/buildd/tmp/extension

into the postgresql.conf of the temporary PG installation, or some
other way like CREATE EXTENSION foobar WITH CONTROL '/home/buildd/...'.

Christoph
-- 
c...@df7cb.de | http://www.df7cb.de/


signature.asc
Description: Digital signature


Re: [HACKERS] Command Triggers, patch v11

2012-02-25 Thread Thom Brown
On 25 February 2012 13:15, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:42, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:07, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:00, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:

 D'oh, just as I sent some more queries...

 Thom Brown t...@linux.com writes:
 Is there any reason why the list of commands that command triggers can
 be used with isn't in alphabetical order?  Also it appears to show

 Any reason why?  I don't suppose it's really important one way or the
 other, so I'm waiting on some more voices before working on it.

 Just so it's easy to scan.  If someone is looking for CREATE CAST,
 they'd kind of expect it near the drop of the CREATE list, but it's
 actually toward the bottom.  It just looks random at the moment.

 The ALTER COMMAND TRIGGER page also doesn't show which commands it can
 be used against.  Perhaps, rather than repeat the list, there could be
 a note to say that a list of valid commands can be found on the CREATE
 COMMAND TRIGGER page?

 Well you can only alter a command that you were successful in creating,
 right?  So I'm not sure that's needed here.  By that count though, I
 maybe should remove the supported command list from DROP COMMAND TRIGGER
 reference page?

 Sure, that would be more consistent.  You're right, it's not needed.
 It just seemed odd that one of the statements lacked what both others
 had.

 Yet another comment... (I should have really started looking at this
 at an earlier stage)

 It seems that if one were to enforce a naming convention for relations
 as shown in the 2nd example for CREATE COMMAND TRIGGER, it could be
 circumvented by someone using CREATE TABLE name AS...

 test=# CREATE TABLE badname (id int, a int, b text);
 ERROR:  invalid relation name: badname
 test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b;
 SELECT 1

 This doesn't even get picked up by ANY COMMAND.

 CREATE COMMAND TRIGGER doesn't output in pg_dump or pg_dumpall.  I'd
 expect ALTER COMMAND TRIGGER to output too for when individual
 commands are disabled etc.

Just found another case where a table can be created without a command
trigger firing:

SELECT * INTO badname FROM goodname;

-- 
Thom

-- 
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-02-25 Thread Thom Brown
On 25 February 2012 13:28, Thom Brown t...@linux.com wrote:
 On 25 February 2012 13:15, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:42, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:07, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:00, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:

 D'oh, just as I sent some more queries...

 Thom Brown t...@linux.com writes:
 Is there any reason why the list of commands that command triggers can
 be used with isn't in alphabetical order?  Also it appears to show

 Any reason why?  I don't suppose it's really important one way or the
 other, so I'm waiting on some more voices before working on it.

 Just so it's easy to scan.  If someone is looking for CREATE CAST,
 they'd kind of expect it near the drop of the CREATE list, but it's
 actually toward the bottom.  It just looks random at the moment.

 The ALTER COMMAND TRIGGER page also doesn't show which commands it can
 be used against.  Perhaps, rather than repeat the list, there could be
 a note to say that a list of valid commands can be found on the CREATE
 COMMAND TRIGGER page?

 Well you can only alter a command that you were successful in creating,
 right?  So I'm not sure that's needed here.  By that count though, I
 maybe should remove the supported command list from DROP COMMAND TRIGGER
 reference page?

 Sure, that would be more consistent.  You're right, it's not needed.
 It just seemed odd that one of the statements lacked what both others
 had.

 Yet another comment... (I should have really started looking at this
 at an earlier stage)

 It seems that if one were to enforce a naming convention for relations
 as shown in the 2nd example for CREATE COMMAND TRIGGER, it could be
 circumvented by someone using CREATE TABLE name AS...

 test=# CREATE TABLE badname (id int, a int, b text);
 ERROR:  invalid relation name: badname
 test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b;
 SELECT 1

 This doesn't even get picked up by ANY COMMAND.

 CREATE COMMAND TRIGGER doesn't output in pg_dump or pg_dumpall.  I'd
 expect ALTER COMMAND TRIGGER to output too for when individual
 commands are disabled etc.

 Just found another case where a table can be created without a command
 trigger firing:

 SELECT * INTO badname FROM goodname;

Right, hopefully this should be my last piece of list spam for the
time being. (apologies, I thought I'd just try it out at first, but
it's ended up being reviewed piecemeal)

On CREATE COMMAND TRIGGER page:

“The trigger will be associated with the specified command and will
execute the specified function function_name when that command is
run.”
should be:
“The trigger will be associated with the specified commands and will
execute the specified function function_name when those commands are
run.”

“A command trigger's function must return void, the only it can aborts
the execution of the command is by raising an exception.”
should be:
“A command trigger's function must return void.  It can then only
abort the execution of the command by raising an exception.”

Remove:
“For a constraint trigger, this is also the name to use when modifying
the trigger's behavior using SET CONSTRAINTS.”

Remove:
“That leaves out the following list of non supported commands.”

s/exercize/exercise/

“that's the case for VACUUM, CLUSTER CREATE INDEX CONCURRENTLY, and
REINDEX DATABASE.”
should be:
“that's the case for VACUUM, CLUSTER, CREATE INDEX CONCURRENTLY, and
REINDEX DATABASE.”

I don’t understand this sentence:
“Triggers on ANY command support more commands than just this list,
and will only provide the command tag argument as NOT NULL.”


On ALTER COMMAND TRIGGER page:

“ALTER COMMAND TRIGGER name ON command SET enabled”
should be:
“ALTER COMMAND TRIGGER name ON command [, ... ] SET enabled”


On DROP COMMAND TRIGGER page:

There’s a mention of CASCADE and RESTRICT.  I don’t know of any object
which could be dependant on a command trigger, so I don’t see what
these are for.


An oddity I’ve noticed is that you can add additional commands to an
existing command trigger, and you can also have them execute a
different function to the other commands referenced in the same
trigger.

-- 
Thom

-- 
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] pgstat documentation tables

2012-02-25 Thread Magnus Hagander
On Mon, Jan 16, 2012 at 02:03, Greg Smith g...@2ndquadrant.com wrote:
 On 01/15/2012 12:20 PM, Tom Lane wrote:

 Please follow the style already used for system catalogs; ie I think
 there should be a summary table with one entry per view, and then a
 separate description and table-of-columns for each view.


 Yes, that's a perfect precedent.  I think the easiest path forward here is
 to tweak the updated pg_stat_activity documentation, since that's being
 refactoring first anyway.  That can be reformatted until it looks just like
 the system catalog documentation.  And then once that's done, the rest of
 them can be converted over to follow the same style.  I'd be willing to work
 on doing that in a way that improves what is documented, too.  The
 difficulty of working with the existing tables has been the deterrent for
 improving that section to me.

I've applied a patch that does this now. Hopefully, I didn't create
too many spelling errors or such :-)

I also applied a separate patch that folded the list of functions into
the list of views, since that's where they are called, as a way to
reduce duplicate documentation. I did it as a spearate patch to make
it easier to back out if people think that was a bad idea...

I didn't add any new documentation at this point - I wanted to get
this patch in before it starts conflicting with work others are
potentially doing. I also noticed there were some stats functions that
were undocumented - the view fields were listed, but not the functions
themselves. I haven't added those either...

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

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


Re: [HACKERS] Command Triggers, patch v11

2012-02-25 Thread Thom Brown
On 25 February 2012 14:30, Thom Brown t...@linux.com wrote:
 On 25 February 2012 13:28, Thom Brown t...@linux.com wrote:
 On 25 February 2012 13:15, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:42, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:07, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:00, Dimitri Fontaine dimi...@2ndquadrant.fr 
 wrote:

 D'oh, just as I sent some more queries...

 Thom Brown t...@linux.com writes:
 Is there any reason why the list of commands that command triggers can
 be used with isn't in alphabetical order?  Also it appears to show

 Any reason why?  I don't suppose it's really important one way or the
 other, so I'm waiting on some more voices before working on it.

 Just so it's easy to scan.  If someone is looking for CREATE CAST,
 they'd kind of expect it near the drop of the CREATE list, but it's
 actually toward the bottom.  It just looks random at the moment.

 The ALTER COMMAND TRIGGER page also doesn't show which commands it can
 be used against.  Perhaps, rather than repeat the list, there could be
 a note to say that a list of valid commands can be found on the CREATE
 COMMAND TRIGGER page?

 Well you can only alter a command that you were successful in creating,
 right?  So I'm not sure that's needed here.  By that count though, I
 maybe should remove the supported command list from DROP COMMAND TRIGGER
 reference page?

 Sure, that would be more consistent.  You're right, it's not needed.
 It just seemed odd that one of the statements lacked what both others
 had.

 Yet another comment... (I should have really started looking at this
 at an earlier stage)

 It seems that if one were to enforce a naming convention for relations
 as shown in the 2nd example for CREATE COMMAND TRIGGER, it could be
 circumvented by someone using CREATE TABLE name AS...

 test=# CREATE TABLE badname (id int, a int, b text);
 ERROR:  invalid relation name: badname
 test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b;
 SELECT 1

 This doesn't even get picked up by ANY COMMAND.

 CREATE COMMAND TRIGGER doesn't output in pg_dump or pg_dumpall.  I'd
 expect ALTER COMMAND TRIGGER to output too for when individual
 commands are disabled etc.

 Just found another case where a table can be created without a command
 trigger firing:

 SELECT * INTO badname FROM goodname;

 Right, hopefully this should be my last piece of list spam for the
 time being. (apologies, I thought I'd just try it out at first, but
 it's ended up being reviewed piecemeal)

I was wrong.. a couple of corrections to my own response:

 On CREATE COMMAND TRIGGER page:

 “The trigger will be associated with the specified command and will
 execute the specified function function_name when that command is
 run.”
 should be:
 “The trigger will be associated with the specified commands and will
 execute the specified function function_name when those commands are
 run.”

Actually, perhaps ...when any of those commands...

 On ALTER COMMAND TRIGGER page:

 “ALTER COMMAND TRIGGER name ON command SET enabled”
 should be:
 “ALTER COMMAND TRIGGER name ON command [, ... ] SET enabled”

This one is nonsense, so please ignore it.

-- 
Thom

-- 
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] Fix PL/Python metadata when there is no result

2012-02-25 Thread Jean-Baptiste Quenot
2012/2/24 Peter Eisentraut pete...@gmx.net:
 On fre, 2012-02-10 at 17:44 +0100, Jean-Baptiste Quenot wrote:

 Please find attached a patch that solves this issue.  Instead of a PG
 crash, we get the following message:

 ERROR:  plpy.Error: no result fetched

 Hmm, should it be an error or just return None?  Python DB-API
 cursor.description returns None if no result set was returned.

IMO raising an error is much better because:

1) It is not a valid usecase to retrieve result metadata when no rows
are expected to be returned

2) The various metadata methods return a sequence.  Checking for null
value in this case is not a very good programming style.  I expect to
find an empty list when no data is available.

Cheers,
-- 
Jean-Baptiste Quenot

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

2012-02-25 Thread Kevin Grittner
Vik Reykja vikrey...@gmail.com wrote:
 Kevin Grittner kevin.gritt...@wicourts.govwrote:
 
 One of the problems that Florian was trying to address is that
 people often have a need to enforce something with a lot of
 similarity to a foreign key, but with more subtle logic than
 declarative foreign keys support.  One example would be the case
 Robert has used in some presentations, where the manager column
 in each row in a project table must contain the id of a row in a
 person table *which has the project_manager boolean column set to
 TRUE*.  Short of using the new serializable transaction isolation
 level in all related transactions, hand-coding enforcement of
 this useful invariant through trigger code (or application code
 enforced through some framework) is very tricky.  The change to
 SELECT FOR UPDATE that Florian was working on would make it
 pretty straightforward.
 
 I'm not sure what Florian's patch does, but I've been trying to
 advocate syntax like the following for this exact scenario:
 
 foreign key (manager_id, true) references person (id, is_manager)
 
 Basically, allow us to use constants instead of field names as
 part of foreign keys.
 
Interesting.  IMV, a declarative approach like that is almost always
better than the alternatives, so something like this (possibly with
different syntax) would be another step in the right direction.  I
suspect that there will always be a few corner cases where the
business logic required is too esoteric to be handled by a
generalized declarative construct, so I think Florian's idea still
has merit -- especially if we want to ease the transition to
PostgreSQL for large shops using other products.
 
 I have no idea what the implementation aspect of this is,
 but I need the user aspect of it and don't know the best way to
 get it.
 
There are those in the community who make their livings by helping
people get the features they want.  If you have some money to fund
development, I would bet you could get this addressed -- it sure
sounds reasonable to me.
 
-Kevin

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


Re: [HACKERS] COPY with hints, rebirth

2012-02-25 Thread Kevin Grittner
Simon Riggs si...@2ndquadrant.com wrote:
 
 This patch extends that and actually sets the tuple header flag as
 HEAP_XMIN_COMMITTED during the load.
 
Fantastic!
 
So, without bulk-load conditions, a long-lived tuple in PostgreSQL
is written to disk at least five times[1]:
 
(1) The WAL record for the inserted tuple is written.
(2) The inserted tuple is written.
(3) The HEAP_XMIN_COMMITTED bit is set and the tuple is re-written
in place some time after the inserting transaction's COMMIT.
(4) The WAL record for the freeze in write 5 is written.
(5) The xmin is set to frozen and the tuple is rewritten in place
some time after every other connection can see it.
 
Prior to your patch, bulk load omitted write 1.  With your patch we
will also omit write 3.
 
Since you've just been looking at this area, do you have any
thoughts about writes 4 and 5 being rendered unnecessary by writing
bulk-loaded tuples with a frozen xmin, and having transactions with
a snapshot which doesn't include the bulk load's transaction just
not seeing the table?  (Or am I just dreaming about those?)
 
-Kevin
 
[1] If you are archiving, it could be more.

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


Re: [HACKERS] COPY with hints, rebirth

2012-02-25 Thread Simon Riggs
On Sat, Feb 25, 2012 at 6:24 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
 Simon Riggs si...@2ndquadrant.com wrote:

 This patch extends that and actually sets the tuple header flag as
 HEAP_XMIN_COMMITTED during the load.

 Fantastic!

 So, without bulk-load conditions, a long-lived tuple in PostgreSQL
 is written to disk at least five times[1]:

 (1) The WAL record for the inserted tuple is written.
 (2) The inserted tuple is written.
 (3) The HEAP_XMIN_COMMITTED bit is set and the tuple is re-written
    in place some time after the inserting transaction's COMMIT.
 (4) The WAL record for the freeze in write 5 is written.
 (5) The xmin is set to frozen and the tuple is rewritten in place
    some time after every other connection can see it.

 Prior to your patch, bulk load omitted write 1.  With your patch we
 will also omit write 3.

Yes, well explained.

 Since you've just been looking at this area, do you have any
 thoughts about writes 4 and 5 being rendered unnecessary by writing
 bulk-loaded tuples with a frozen xmin, and having transactions with
 a snapshot which doesn't include the bulk load's transaction just
 not seeing the table?  (Or am I just dreaming about those?)

Setting straight to frozen breaks MVCC, unless/until we use MVCC for
catalog access because we can see the table immediately and then read
the contents as if they had always been there.

I think we could add that as an option on COPY, since breaking MVCC
in that way is only a bad thing if it happens accidentally without the
user's permission and knowledge - which they may wish to give in many
cases, such as reloading a database from a dump.

-- 
 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] CLOG contention, part 2

2012-02-25 Thread Simon Riggs
On Wed, Feb 8, 2012 at 11:26 PM, Robert Haas robertmh...@gmail.com wrote:

 Given that, I obviously cannot test this at this point,

Patch with minor corrections attached here for further review.

 but let me go
 ahead and theorize about how well it's likely to work.  What Tom
 suggested before (and after some reflection I think I believe it) is
 that the frequency of access will be highest for the newest CLOG page
 and then drop off for each page further back you go.  Clearly, if that
 drop-off is fast - e.g. each buffer further backward is half as likely
 to be accessed as the next newer one - then the fraction of accesses
 that will hit pages that are far enough back to benefit from this
 optimization will be infinitesmal; 1023 out of every 1024 accesses
 will hit the first ten pages, and on a high-velocity system those all
 figure to have been populated since the last checkpoint.

That's just making up numbers, so its not much help. The theory
would apply to one workload but not another, so may well be true for
some workload but I doubt whether all databases work that way. I ask
accept the long tail distribution as being very common, we just
don't know how long that tail is typically or even if there is a
dominant single use case.

 The best
 case for this patch should be an access pattern that involves a very
 long tail;

Agreed


 actually, pgbench is a pretty good fit for that

Completely disagree, as described in detail in the other patch about
creating a realistic test environment for this patch.

pgbench is *not* a real world test.

pgbench loads all the data in one go, then pretends the data got their
one transaction at a time. So pgbench with no mods is actually the
theoretically most unreal imaginable. You have to run pgbench for 1
million transactions before you even theoretically show any gain from
this patch, and it would need to be a long test indeed before the
averaged effect of the patch was large enough to avoid the zero
contribution from the first million transacts.

The only real world way to test this patch is to pre-create the
database using a scale factor of 100 using the modified pgbench, then
run a test. That correctly simulates the real world situation where
all data arrived in single transactions.


 assuming
 the scale factor is large enough.  For example, at scale factor 100,
 we've got 10,000,000 tuples: choosing one at random, we're almost
 exactly 90% likely to find one that hasn't been chosen in the last
 1,024,576 tuples (i.e. 32 CLOG pages @ 32K txns/page).  In terms of
 reducing contention on the main CLOG SLRU, that sounds pretty
 promising, but depends somewhat on the rate at which transactions are
 processed relative to the frequency of checkpoints, since that will
 affect how many pages back you have go to use the history path.

 However, there is a potential fly in the ointment: in other cases in
 which we've reduced contention at the LWLock layer, we've ended up
 with very nasty contention at the spinlock layer that can sometimes
 eat more CPU time than the LWLock contention did.   In that light, it
 strikes me that it would be nice to be able to partition the
 contention N ways rather than just 2 ways.  I think we could do that
 as follows.  Instead of having one control lock per SLRU, have N
 locks, where N is probably a power of 2.  Divide the buffer pool for
 the SLRU N ways, and decree that each slice of the buffer pool is
 controlled by one of the N locks.  Route all requests for a page P to
 slice P mod N.  Unlike this approach, that wouldn't completely
 eliminate contention at the LWLock level, but it would reduce it
 proportional to the number of partitions, and it would reduce spinlock
 contention according to the number of partitions as well.  A down side
 is that you'll need more buffers to get the same hit rate, but this
 proposal has the same problem: it doubles the amount of memory
 allocated for CLOG.  Of course, this approach is all vaporware right
 now, so it's anybody's guess whether it would be better than this if
 we had code for it.  I'm just throwing it out there.

We've already discussed that and my patch for that has already been
rules out by us for this CF.

A much better take is to list what options for scaling we have:
* separate out the history
* partition access to the most active parts

For me, any loss of performance comes from two areas:
(1) concurrent access to pages
(2) clog LRU is dirty and delays reading in new pages

For the most active parts, (1) is significant. Using partitioning at
the page level will be ineffective in reducing contention because
almost all of the contention is on the first 1-2 pages. If we do
partitioning, it should be done by *striping* the most recent pages
across many locks, as I already suggested. Reducing page size would
reduce page contention but increase number of new page events and so
make (2) more important. Increasing page size will amplify (1).

(2) is less significant but much more easily 

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

2012-02-25 Thread Cédric Villemain
Le vendredi 24 février 2012 14:18:44, Florian Weimer a écrit :
 * Alex Shulgin:
  It's ugly, but it's standard practice, and seems better than a separate
  -d parameter (which sort of defeats the purpose of URIs).
  
  Hm, do you see anything what's wrong with ?dbname=other if you don't
  like a separate -d?
 
 It's not nice URI syntax, but it's better than an out-of-band mechanism.

I've not followed all the mails about this feature but I don't find it is a 
nice syntax too.

?dbname=other looks like dbname is an argument, but dbname is a requirement 
for postgresql connexion.


-- 
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

-- 
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] VACUUM ANALYZE is faster than ANALYZE?

2012-02-25 Thread Cédric Villemain
Le mercredi 22 février 2012 20:12:35, Pavel Stehule a écrit :
 2012/2/22 Kevin Grittner kevin.gritt...@wicourts.gov:
  Pavel Stehule pavel.steh...@gmail.com wrote:
  usual pattern in our application is
  
  create table xx1 as select 
  analyze xx1
  create table xx2 as select  from xx1, 
  analyze xx2
  create table xx3 as select ... from xx3, 
  analyze xx3
  create table xx4 as select ... from xx1, ...
  
  tables xx** are use as cache.
  
  so we have to refresh statistic early.
  
  in this situation - and I found so in this case VACUUM ANALYZE is
  faster (30%) than ANALYZE. Size of xx** is usually between 500Kb
  and 8Kb
  
  This is not usual pattern for OLTP - Application is strictly OLAP.
  
  Is the VACUUM ANALYZE step faster, or is the overall job faster if
  VACUUM ANALYZE is run?  You may be running into the need to rewrite
  pages at an inopportune time or order without the VACUUM.  Have you
  tried getting a time VACUUM FREEZE ANALYZE on these cache tables
  instead of plain VACUUM ANALYZE?
  
  -Kevin
 
 vacuum freeze analyze is slower as expected. vacuum analyze is little
 bit faster or same in any step then analyze.
 
 I expected so just analyze should be significantly faster and it is not.
 
 Tom's demonstration is enough for me. ANALYZE doesn't read complete
 table, but uses random IO. VACUUM ANALYZE reads complete table, but it
 uses seq IO and vacuum is fast (because it does nothing) in our case.

VACUUM does read the 1st block to be sure readahead is done when ANALYSE does 
not.
For ANALYZE, maybe it is interesting to issue a read on the first block or use 
POSIX_FADVISE to (try) to force a readahead of the table when it is small 
enough (so ANALYSE can start working while blocks are read and put in cache).

That's being said, I am surprised that the pattern create table...analyze 
create table analyze of such smalls ones make the data being flush from OS 
cache so quickly that they need to be read again from disk.
Pavel, can you check the cache status of the tables just before the analyze ? 
(you can use OS tools or pgfincore extension for that)

-- 
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation

-- 
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-02-25 Thread Jeff Janes
On Tue, Feb 21, 2012 at 5:34 PM, Fujii Masao masao.fu...@gmail.com wrote:
 On Tue, Feb 21, 2012 at 8:19 PM, Fujii Masao masao.fu...@gmail.com 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.

 When I ran the long-running performance test, I encountered the following
 panic error.

    PANIC:  could not find WAL buffer for 0/FF00

I too see this panic when the system survives long enough to get to
that log switch.

But I'm also still seeing (with version 9) the assert failure at
xlog.c, Line: 2154 during the end-of-recovery checkpoint.

Here is a set up for repeating my tests.  I used this test simply
because I had it sitting around after having written it for other
purposes.  Indeed I'm not all that sure I should publish it.
Hopefully other people will write other tests which exercise other
corner cases, rather than exercising the same ones I am.

The patch creates a guc which causes the md writer routine to panic
and bring down the database, triggering recovery, after a given number
for writes.  In this context probably any other method of forcing a
crash and recovery would be just as good as this specific method of
crashing.

The choice of 400 for the cutoff for crashing is based on:

1) If the number is too low, you re-crash within recovery so you never
get a chance to inspect the database.  In my hands, recovery doesn't
need to do more than 400 writes. (I don't know how to make the
database use different guc setting during recovery than it did before
the crash).

2) If the number is too high, it takes too long for a crash to happen
and I'm not all that patient.

Some of the changes to postgresql.conf.sample are purely my
preferences and have nothing in particular to do with this set up.
But archive_timeout = 30 is necessary in order to get checkpoints, and
thus mdwrites, to happen often enough to trigger crashes often enough
to satisfy my impatience.

The Perl script exercises the integrity of the database by launching
multiple processes (4 by default) to run updates and memorize what
updates they have run.  After a crash, the Perl processes all
communicate their data up to the parent, which consolidates that
information and then queries the post-recovery database to make sure
it agrees.  Transactions that are in-flight at the time of a crash are
indeterminate.  Maybe the crash happened before the commit, and maybe
it happened after the commit but before we received notification of
the commit.  So whichever way those turn out, it is not proof of
corruption.

With the xloginsert-scale-9.patch, the above features are not needed
because the problem is not that the database is incorrect after
recovery, but that the database doesn't recover in the first place. So
just running pgbench would be good enough to detect that.  But in
earlier versions this feature did detect incorrect recovery.

This logs an awful lot of stuff, most of which merely indicates normal
operation.  The problem is that corruption is rare, so if you wait
until you see corruption before turning on logging, then you have to
wait l long time to get another instance of corruption so you can
dissect the log information.  So, I just log everything all of the
time.
A warning from 'line 63' which is not marked as in-flight indicates
database corruption.  A warning from 'line 66' indicates even worse
corruption. A failure of the entire outer script to execute for the
expected number of iterations (i.e. failure of the warning issued on
'line 18' to show up 100 times) indicates the database failed to
restart.

Also attached is a bash script that exercises the whole thing.  Note
that it has various directories hard coded that really ought not be,
and that it has no compunctions about calling rm -r /tmp/data.  I run
it is as ./do.sh  log and then inspect the log file for unusual
lines.

To run this, you first have to apply your own xlog patch, and apply my
crash-inducing patch, and build and install the resulting pgsql.  And
edit the shell script to point to it, etc..  The whole thing is a bit
of a idiosyncratic mess.

Cheers,

Jeff


crash_REL9_2CF4.patch
Description: Binary data


count.pl
Description: Binary data


do.sh
Description: Bourne shell script

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


Re: [HACKERS] FDW system columns

2012-02-25 Thread Thom Brown
On 14 November 2011 13:07, Thom Brown t...@linux.com wrote:
 2011/11/14 Shigeru Hanada shigeru.han...@gmail.com

 (2011/11/14 11:25), Robert Haas wrote:
  My vote is to nuke 'em all.  :-)

 +1.

 IIRC, main purpose of supporting tableoid for foreign tables was to be
 basis of foreign table inheritance, which was not included in 9.1, and
 we have not supported it yet.  Other system columns are essentially
 garbage, but they survived at 9.1 development because (maybe) it seemed
 little odd to have system columns partially at that time.

 So, IMHO removing all system columns from foreign tables seems
 reasonable, unless it doesn't break any external tool seriously (Perhaps
 there would be few tools which assume that foreign tables have system
 columns).

 If there seems to be a consensus on removing system column from foreign
 tables, I'd like to work on this issue.  Attached is a halfway patch,
 and ISTM there is no problem so far.


 I can say that at least PgAdmin doesn't use these columns.

So we still have all of these columns for foreign tables.  I've tested
Hanada-san's patch and it removes all of the system columns.  Could we
consider applying it, or has a use-case for them since been
discovered?

-- 
Thom

-- 
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] Memory usage during sorting

2012-02-25 Thread Jeff Janes
On Tue, Feb 14, 2012 at 1:44 AM, Hitoshi Harada umi.tan...@gmail.com wrote:
 On Sat, Feb 11, 2012 at 11:34 AM, Jeff Janes jeff.ja...@gmail.com wrote:
 On Wed, Feb 8, 2012 at 1:01 AM, Hitoshi Harada umi.tan...@gmail.com wrote:
 On Sun, Jan 15, 2012 at 4:59 PM, Jeff Janes jeff.ja...@gmail.com wrote:

 The attached patch allows it to reuse that memory.  On my meager
 system it reduced the building of an index on an integer column in a
 skinny 200 million row totally randomly ordered table by about 3% from
 a baseline of 25 minutes.


 Just to give a standard review, this patch is one line change and
 applies cleanly, builds ok.

 I'm not pretty sure what exactly you're trying to accomplish, but it
 seems to me that it's avoiding the first dumptuples cycle by forcing
 availMem = 0 even if it's negative.

 Yes.  Currently when it switches to the TSS_BUILDRUNS part of a
 tape-sort, it starts by calling WRITETUP a large number of time
 consecutively, to work off the memory deficit incurred by the 3 blocks
 per tape of tape overhead, and then after that calls WRITETUP about
 once per puttuple..   Under my patch, it would only call WRITETUP
 about once per puttuple, right from the beginning.

 I read your comments as it'd be
 avoiding to alternate reading/writing back and force with scattered
 memory failing memory cache much during merge phase, but actually it
 doesn't affect merge phase but only init-dump phase, does it?

 It effects the building of the runs.  But this building of the runs is
 not a simple dump, it is itself a mini merge phase, in which it merges
 the existing in-memory priority queue against the still-incoming
 tuples from the node which invoked the sort.  By using less memory
 than it could, this means that the resulting runs are smaller than
 they could be, and so will sometimes necessitate an additional layer
 of merging later on.   (This effect is particularly large for the very
 first run being built.  Generally by merging incoming tuples into the
 memory-tuples, you can create runs that are 1.7 times the size of fits
 in memory.  By wasting some memory, we are getting 1.7 the size of a
 smaller starting point.  But for the first run, it is worse than that.
  Most of the benefit that leads to that 1.7 multiplier comes at the
 very early stage of each run-build.  But by initially using the full
 memory, then writing out a bunch of tuples without doing any merge of
 the incoming, we have truncated the part that gives the most benefit.)

 My analysis that the freed memory is never reused (because we refuse
 to reuse it ourselves and it is too fragmented to be reused by anyone
 else, like the palloc or VM system) only applies to the run-building
 phase.  So never was a bit of an overstatement.  By the time the last
 initial run is completely written out to tape, the heap used for the
 priority queue should be totally empty.  So at this point the
 allocator would have the chance to congeal all of the fragmented
 memory back into larger chunks, or maybe it parcels the allocations
 back out again in an order so that the unused space is contiguous and
 could be meaningfully paged out.

 But, it is it worth worrying about how much we fragment memory and if
 we overshoot our promises by 10 or 20%?

 If so,
 I'm not so convinced your benchmark gave 3 % gain by this change.
 Correct me as I'm probably wrong.

 I've now done more complete testing.  Building an index on an
 200,000,000 row table with an integer column populated in random order
 with integers from 1..500,000,000, non-unique, on a machine with 2GB
 of RAM and 600MB of shared_buffers.

 It improves things by 6-7 percent at the end of working mem size, the
 rest are in the noise except at 77936 KB, where it reproducibly makes
 things 4% worse, for reasons I haven't figured out.  So maybe the best
 thing to do is, rather than micromanaging memory usage, simply don't
 set maintenance_work_mem way to low.  (But, it is the default).

 I've tested here with only a million rows mix of integer/text (table
 size is 80MB or so) with default setting, running CREATE INDEX
 continuously, but couldn't find performance improvement.  The number
 varies from -2% to +2%, which I think is just error.

 While I agree with your insist that avoiding the first dump would make
 sense, I guess it depends on situations; if the dump goes with lots of
 tuples (which should happen when availMem is big), writing tuples a
 lot at a time will be faster than writing little by little later.

 I'm not sure about the conclusion, but given this discussion, I'm
 inclined to mark this Returned with Feedback.

OK, thanks.  Does anyone have additional feed-back on how tightly we
wish to manage memory usage?  Is trying to make us use as much memory
as we are allowed to without going over a worthwhile endeavor at all,
or is it just academic nitpicking?

Also, since the default value of work_mem is quite low, should the
docs be more aggressive in suggesting that people using any

Re: [HACKERS] xlog location arithmetic

2012-02-25 Thread Euler Taveira de Oliveira
On 25-02-2012 09:23, Magnus Hagander wrote:
 Do we even *need* the validate_xlog_location() function? If we just
 remove those calls, won't we still catch all the incorrectly formatted
 ones in the errors of the sscanf() calls? Or am I too deep into
 weekend-mode and missing something obvious?
 
sscanf() is too fragile for input sanity check. Try
pg_xlog_location_diff('12/3', '-10/0'), for example. I won't object removing
that function if you protect xlog location input from silly users.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

-- 
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-02-25 Thread Thom Brown
On 25 February 2012 16:36, Thom Brown t...@linux.com wrote:
 On 25 February 2012 14:30, Thom Brown t...@linux.com wrote:
 On 25 February 2012 13:28, Thom Brown t...@linux.com wrote:
 On 25 February 2012 13:15, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:42, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:07, Thom Brown t...@linux.com wrote:
 On 25 February 2012 12:00, Dimitri Fontaine dimi...@2ndquadrant.fr 
 wrote:

 D'oh, just as I sent some more queries...

 Thom Brown t...@linux.com writes:
 Is there any reason why the list of commands that command triggers can
 be used with isn't in alphabetical order?  Also it appears to show

 Any reason why?  I don't suppose it's really important one way or the
 other, so I'm waiting on some more voices before working on it.

 Just so it's easy to scan.  If someone is looking for CREATE CAST,
 they'd kind of expect it near the drop of the CREATE list, but it's
 actually toward the bottom.  It just looks random at the moment.

 The ALTER COMMAND TRIGGER page also doesn't show which commands it can
 be used against.  Perhaps, rather than repeat the list, there could be
 a note to say that a list of valid commands can be found on the CREATE
 COMMAND TRIGGER page?

 Well you can only alter a command that you were successful in creating,
 right?  So I'm not sure that's needed here.  By that count though, I
 maybe should remove the supported command list from DROP COMMAND TRIGGER
 reference page?

 Sure, that would be more consistent.  You're right, it's not needed.
 It just seemed odd that one of the statements lacked what both others
 had.

 Yet another comment... (I should have really started looking at this
 at an earlier stage)

 It seems that if one were to enforce a naming convention for relations
 as shown in the 2nd example for CREATE COMMAND TRIGGER, it could be
 circumvented by someone using CREATE TABLE name AS...

 test=# CREATE TABLE badname (id int, a int, b text);
 ERROR:  invalid relation name: badname
 test=# CREATE TABLE badname AS SELECT 1::int id, 1::int a, ''::text b;
 SELECT 1

 This doesn't even get picked up by ANY COMMAND.

 CREATE COMMAND TRIGGER doesn't output in pg_dump or pg_dumpall.  I'd
 expect ALTER COMMAND TRIGGER to output too for when individual
 commands are disabled etc.

 Just found another case where a table can be created without a command
 trigger firing:

 SELECT * INTO badname FROM goodname;

 Right, hopefully this should be my last piece of list spam for the
 time being. (apologies, I thought I'd just try it out at first, but
 it's ended up being reviewed piecemeal)

 I was wrong.. a couple of corrections to my own response:

 On CREATE COMMAND TRIGGER page:

 “The trigger will be associated with the specified command and will
 execute the specified function function_name when that command is
 run.”
 should be:
 “The trigger will be associated with the specified commands and will
 execute the specified function function_name when those commands are
 run.”

 Actually, perhaps ...when any of those commands...

 On ALTER COMMAND TRIGGER page:

 “ALTER COMMAND TRIGGER name ON command SET enabled”
 should be:
 “ALTER COMMAND TRIGGER name ON command [, ... ] SET enabled”

 This one is nonsense, so please ignore it.

Further testing reveals a problem with FTS configurations when using
the example function provided in the docs:

test=# CREATE TEXT SEARCH CONFIGURATION test (
  PARSER = default
);
ERROR:  invalid relation name:
test=# CREATE TEXT SEARCH CONFIGURATION fr_test (
  PARSER = default
);
ERROR:  invalid relation name:

The 2nd one should work as it matches the naming convention checked in
the function.  The ALTER and DROP equivalents appear to be fine
though.

DROP CAST shares a similar issue too:

test=# DROP CAST (bigint as int4);
ERROR:  invalid relation name: �

The odd thing about this one is that CREATE CAST shouldn't match on
name at all, but it creates a cast successfully, whereas DROP CAST
disagrees with the name.

Command triggers for CREATE TYPE don't work, but fine for ALTER TYPE
and DROP TYPE.

Also command triggers for DROP CONVERSION aren't working.  A glance at
pg_cmdtrigger shows that the system views the command as DROP
CONVERSION_P.

What is DROP ASSERTION?  It's showing as a valid command for a command
trigger, but it's not documented.

I've noticed that ALTER object name OWNER TO role doesn't result in
any trigger being fired except for tables.

ALTER OPERATOR FAMILY  RENAME TO ... doesn't fire command triggers.

ALTER OPERATOR CLASS with RENAME TO or OWNER TO doesn't fire command
triggers, but with SET SCHEMA it does.

And there's no command trigger available for ALTER VIEW.

I'll hold off on testing any further until a new patch is available.

-- 
Thom

-- 
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] Initial 9.2 pgbench write results

2012-02-25 Thread Jeff Janes
On Tue, Feb 14, 2012 at 12:25 PM, Greg Smith g...@2ndquadrant.com wrote:
 On 02/14/2012 01:45 PM, Greg Smith wrote:

 scale=1000, db is 94% of RAM; clients=4
 Version TPS
 9.0  535
 9.1  491 (-8.4% relative to 9.0)
 9.2  338 (-31.2% relative to 9.1)


 A second pass through this data noted that the maximum number of buffers
 cleaned by the background writer is =2785 in 9.0/9.1, while it goes as high
 as 17345 times in 9.2.

There is something strange about the data for Set 4 (9.1) at scale 1000.

The number of buf_alloc varies a lot from run to run in that series
(by a factor of 60 from max to min).

But the TPS doesn't vary by very much.

How can that be?  If a transaction needs a page that is not in the
cache, it needs to allocate a buffer.  So the only thing that could
lower the allocation would be a higher cache hit rate, right?  How
could there be so much variation in the cache hit rate from run to run
at the same scale?


Cheers,

Jeff

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


Re: [HACKERS] Checkpoint sync pause

2012-02-25 Thread Jeff Janes
On Sun, Feb 12, 2012 at 10:49 PM, Amit Kapila amit.kap...@huawei.com wrote:
 Without sorted checkpoints (or some other fancier method) you have to
 write out the entire pool before you can do any fsyncs.  Or you have
 to do multiple fsyncs of the same file, with at least one occurring
 after the entire pool was written.  With a sorted checkpoint, you can
 start issuing once-only fsyncs very early in the checkpoint process.
 I think that on large servers, that would be the main benefit, not the
 actually more efficient IO.  (On small servers I've seen sorted
 checkpoints be much faster on shutdown checkpoints, but not on natural
 checkpoints, and presumably this improvement *is* due to better
 ordering).

 On your servers, you need big delays between fsyncs and not between
 writes (as they are buffered until the fsync).  But in other
 situations, people need the delays between the writes.  By using
 sorted checkpoints with fsyncs between each file, the delays between
 writes are naturally delays between fsyncs as well.  So I think the
 benefit of using sorted checkpoints is that code to improve your
 situations is less likely to degrade someone else's situation, without
 having to introduce an extra layer of tunables.

 What I understood is that you are suggesting, it is better to do sorted
 checkpoints which essentially means flush nearby buffers together.

More importantly, you can issue an fsync after all pages for any given
file are written, thus naturally spreading out the fsyncs instead of
reserving them to until the end, or some arbitrary fraction of the
checkpoint cycle.  For this purpose, the buffers only need to be
sorted by physical file they are in, not by block order within the
file.

 However if does this way, might be it will violate Oracle Patent
 (20050044311 - Reducing disk IO by full-cache write-merging). I am not very
 sure about it. But you can refer it once.

Thank you.  I was not aware of it, and am constantly astonished what
kinds of things are patentable.

 I think the linked list is a bit of a red herring.  Many of the
 concepts people discuss implementing on the linked list could just as
 easily be implemented with the clock sweep.  And I've seen no evidence
 at all that the clock sweep is the problem.  The LWLock that protects
 can obviously be a problem, but that seems to be due to the overhead
 of acquiring a contended lock, not the work done under the lock.
 Reducing the lock-strength around this might be a good idea, but that
 reduction could be done just as easily (and as far as I can tell, more
 easily) with the clock sweep than the linked list.

 with clock-sweep, there are many chances that backend needs to traverse more
 to find a suitable buffer.

Maybe, but I have not seen any evidence that this is the case.  My
analyses, experiments, and simulations show that when the buffer
allocations are high, the mere act of running the sweep that often
keeps average useagecount low, so the average sweep is very short.

 However, if clean buffer is put in freelist, it can be directly picked from
 there.

Not directly, you have to take a lock.

Cheers,

Jeff

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


[HACKERS] How to know a table has been modified?

2012-02-25 Thread Tatsuo Ishii
I'm working on implementing query cache in pgpool-II. I want to know
if a table has been modified because pgpool-II has to invalidate cache
if corresponding table is modified. For DDL/DML it would be doable
since pgpool-II knows all SQLs sent from clients. Problem is, implicit
table modifications done by CASCADE, TRIGGERS and so on.

create table t1(i int, j int);
create table t2(i int references t1.i);
drop table t1 cascade;

In this example, if t1 is dropped, t2 is dropped as well. So query
cache corresponding to t1 and t2 should be invalidated. The only way I
could thinking of is, looking into pg_depend. I would like to know if
there's any better/convenient way to know it.

For TRIGGER, I cannot thinking of any way. Any idea will be welcome.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

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


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

2012-02-25 Thread Alexander Shulgin


On 02/25/2012 09:37 PM, Cédric Villemain wrote:


I've not followed all the mails about this feature but I don't find it is a
nice syntax too.

?dbname=other looks like dbname is an argument, but dbname is a requirement
for postgresql connexion.


Ugh, not really.  AFAIK, dbname is a connection option which defaults to 
$USER, unless overridden on command line or in the environment (or via a 
service file.)


--
Alex

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