Re: [HACKERS] libpq SSL with non-blocking sockets

2011-07-25 Thread Martin Pihlak
On 07/24/2011 11:33 PM, Tom Lane wrote:
 I've applied the simplified fix (just set SSL_MODE_ACCEPT_MOVING_WRITE_BUFFER)
 as well as a patch to improve the error reporting situation.
 

Cool that this turned out to be a one-line fix. Thanks!

regards,
Martin

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


Re: [HACKERS] Environment checks prior to regression tests?

2011-07-25 Thread Kohei KaiGai
The attached patch enables to check prerequisites to run regression
test of sepgsql module.
It adds a dependency to installcheck that allows us to launch
a script to check environment of operating system.
I'd like to add this patch next commit-fest.

E.g, this example does not turn on sepgsql_regression_test_mode
   boolean parameter of selinux.

[kaigai@iwashi sepgsql]$ make installcheck
:
== checking selinux environment   ==
test unconfined_t domain  ... ok
test runon command... ok
test sestatus command ... ok
test getsebool command... ok
test enforcing mode   ... ok
test sepgsql-regtest policy   ... ok
test selinux boolean  ... failed

The boolean variable of 'sepgsql_regression_test_mode' must be
turned. It affects an internal state of SELinux policy, then
a set of rules to run regression test will be activated.
You can turn on this variable as follows:

  $ su -
  # setsebool sepgsql_regression_test_mode 1

Also note that we recommend to turn off this variable after the
regression test, because it activates unnecessary rules.

make: *** [check_selinux_environment] Error 1
[kaigai@iwashi sepgsql]$

Then, we can turn on it according to the suggestion.

[kaigai@iwashi sepgsql]$ su -
Password:
[root@iwashi ~]# setsebool sepgsql_regression_test_mode 1
[root@iwashi ~]# logout
[kaigai@iwashi sepgsql]$ make installcheck
:
== checking selinux environment   ==
test unconfined_t domain  ... ok
test runon command... ok
test sestatus command ... ok
test getsebool command... ok
test enforcing mode   ... ok
test sepgsql-regtest policy   ... ok
test selinux boolean  ... ok
test label of psql... ok
test sepgsql installation ... ok
test template1 database   ... ok

../../src/test/regress/pg_regress --inputdir=.
--psqldir='/usr/local/pgsql/bin'   --dbname=contrib_regression
--launcher ../../contrib/sepgsql/launcher label dml misc
(using postmaster on Unix socket, default port)
== dropping database contrib_regression ==
DROP DATABASE
== creating database contrib_regression ==
CREATE DATABASE
ALTER DATABASE
== running regression test queries==
test label... ok
test dml  ... ok
test misc ... ok

=
 All 3 tests passed.
=

Thanks,

2011/7/22 Joe Conway m...@joeconway.com:
 On 07/21/2011 05:35 AM, Robert Haas wrote:
 On Thu, Jul 21, 2011 at 6:16 AM, Kohei Kaigai kohei.kai...@emea.nec.com 
 wrote:
 How about an idea that allows to launch environment checker (typically 
 shell scripts) prior
 to regression tests?

 The following stuffs should be preconfigured to run sepgsql's regression 
 test.
 - SELinux must be run and configured to enforcing mode.
 - The sepgsql-regtest policy module must be loaded.
 - The boolean of sepgsql_regression_test_mode must be turned on.
 - The psql command should be labeled as 'bin_t'

 If checkinstall optionally allows to launch an environment checker on 
 regression test,
 we may be possible to suggest users to fix up their configuration. It seems 
 to me quite
 helpful.

 For example, one idea is to inject a dummy variable (mostly, initialized to 
 empty) as
 dependency of installcheck, being available to overwrite in Makefile of 
 contrib, as follows:

  # against installed postmaster
  installcheck: submake $(REGRESS_PRE)
          $(pg_regress_installcheck) $(REGRESS_OPTS) $(REGRESS)

 Seems reasonable.

 +1
 it would have been helpful to me last month while looking at this.

 Joe

 --
 Joe Conway
 credativ LLC: http://www.credativ.us
 Linux, PostgreSQL, and general Open Source
 Training, Service, Consulting,  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




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


pgsql-v9.2-check-regtest-environment.patch
Description: Binary data

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


Re: Initial Review: JSON contrib modul was: Re: [HACKERS] Another swing at JSON

2011-07-25 Thread Florian Pflug
On Jul25, 2011, at 07:35 , Joey Adams wrote:
 On Mon, Jul 25, 2011 at 1:05 AM, Joey Adams joeyadams3.14...@gmail.com 
 wrote:
 Should we mimic IEEE floats and preserve -0 versus +0 while treating
 them as equal?  Or should we treat JSON floats like numeric and
 convert -0 to 0 on input?  Or should we do something else?  I think
 converting -0 to 0 would be a bad idea, as it would violate the
 intuitive assumption that JSON can be used to marshal double-precision
 floats.
 
 On the other hand, JavaScript's own .toString and JSON.stringify turn
 -0 into 0, so JSON can't marshal -0 around, anyway (in practice).  Now
 I think turning -0 into 0 would be fine for canonicalizing numbers in
 json_in.

+1.

best regards,
Florian Pflug


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


Re: Initial Review: JSON contrib modul was: Re: [HACKERS] Another swing at JSON

2011-07-25 Thread Florian Pflug
On Jul25, 2011, at 02:03 , Florian Pflug wrote:
 On Jul25, 2011, at 00:48 , Joey Adams wrote:
 Should we follow the JavaScript standard for rendering numbers (which
 my suggestion approximates)?  Or should we use the shortest encoding
 as Florian suggests?
 
 In the light of the above, consider my suggestion withdrawn. I now think
 we should just follow the JavaScript standard as closely as possible.
 As you said, it's pretty much the same as your suggestion, just more precise
 in the handling of some corner-cases like infinity, nan, +/-0, some
 questions of leading and trailing zeros, ...

Just FYI, I browsed through the ECMA Standard you referenced again, and realized
that they explicitly forbid JSON numeric values to be NaN or (-)Infinity
(Page 205, Step 9 at the top of the page). RFC 4627 seems to take the same 
stand.

I fail to see the wisdom in that, but it's what the standard says...

best regards,
Florian Pflug


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


Re: [HACKERS] Policy on pulling in code from other projects?

2011-07-25 Thread Robert Haas
On Sun, Jul 24, 2011 at 10:12 PM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Excerpts from Dave Page's message of sáb jul 23 02:25:30 -0400 2011:

 Also consider if the library is widely available on common distros or
 not. If not, packagers are going to have to start packaging that
 first, in order to build the PostgreSQL packages. This is a *huge*
 issue for use if we want to use wxWidgets addon libraries with
 pgAdmin.

 More likely, they are going to ignore it and pass the --disable-liburi
 (whatever) configure parameter and the functionality is going to be
 absent most of the time anyway.

That wouldn't be too good either...

-- 
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] Policy on pulling in code from other projects?

2011-07-25 Thread Dave Page
On Mon, Jul 25, 2011 at 3:12 AM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
 Excerpts from Dave Page's message of sáb jul 23 02:25:30 -0400 2011:

 Also consider if the library is widely available on common distros or
 not. If not, packagers are going to have to start packaging that
 first, in order to build the PostgreSQL packages. This is a *huge*
 issue for use if we want to use wxWidgets addon libraries with
 pgAdmin.

 More likely, they are going to ignore it and pass the --disable-liburi
 (whatever) configure parameter and the functionality is going to be
 absent most of the time anyway.

Yup.


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: 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


[HACKERS] Access to current database from C-language function

2011-07-25 Thread Achim Domma
Hi,

I have read http://www.postgresql.org/docs/9.1/static/xfunc-c.html and my idea 
is, to write a C function which returns a set of rows. To generate the result 
set, I would like to access indexes directly using the information I found at 
http://www.postgresql.org/docs/9.1/static/indexam.html. But I don't get the 
idea how to glue both parts together!? Could somebody give me a starting point? 
How do I get a handle to the current database inside a C function?

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


Re: [HACKERS] Access to current database from C-language function

2011-07-25 Thread Florian Pflug
On Jul25, 2011, at 13:40 , Achim Domma wrote:
 I have read http://www.postgresql.org/docs/9.1/static/xfunc-c.html and my 
 idea is, to write a C function which returns a set of rows. To generate the 
 result set, I would like to access indexes directly using the information I 
 found at http://www.postgresql.org/docs/9.1/static/indexam.html. But I don't 
 get the idea how to glue both parts together!? Could somebody give me a 
 starting point? How do I get a handle to the current database inside a C 
 function?

If you want to access the database using SQL from C-language functions, there 
the SPI API for that. 
(http://www.postgresql.org/docs/9.0/interactive/spi.html). The API provided by 
SPI is conceptually similar to the one provided by the client library libpq, 
i.e. it works in terms of statements, cursors, ...). SPI takes care of handling 
all the low-level details like making sure you're using a valid snapshot, are 
inside a transaction, correctly handle locked rows, ...

A more low-level API is provided by {heap,index}_{beginscan,endscan}, 
heap_{insert,update,delete} and index_insert. However, correct handling of 
transactions using this API isn't easy - for example, to update a row you'd 
first have to find the latest version of that row, then decide if you're 
allowed to update it, and finally create a new version.

best regards,
Florian Pflug


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


Re: [HACKERS] pgbench --unlogged-tables

2011-07-25 Thread Robert Haas
On Fri, Jul 22, 2011 at 5:15 PM, Greg Smith g...@2ndquadrant.com wrote:
 That looks straightforward enough.

OK, committed.

 The other thing I keep realizing would
 be useful recently is to allow specifying a different tablespace to switch
 to when creating all of the indexes.  The old data here, indexes on faster
 storage here trick was already popular in some environments.  But it's
 becoming a really big win for environments that put indexes on SSD, and
 being able to simulate that easily with pgbench would be nice.

Hearing no objections, I did this, too.

At some point, we also need to sort out the scale factor limit issues,
so you can make these things bigger.

-- 
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] vacuumlo patch

2011-07-25 Thread Tim
Updated the patch to also apply when the no-action flag is enabled.

git diff HEAD -- contrib/vacuumlo/vacuumlo.c
diff --git a/contrib/vacuumlo/vacuumlo.c b/contrib/vacuumlo/vacuumlo.c
index f6e2a28..8e9c342 100644
--- a/contrib/vacuumlo/vacuumlo.c
+++ b/contrib/vacuumlo/vacuumlo.c
@@ -48,6 +48,7 @@ struct _param
 char   *pg_host;
 intverbose;
 intdry_run;
+inttransaction_limit;
 };

 intvacuumlo(char *, struct _param *);
@@ -282,10 +283,18 @@ vacuumlo(char *database, struct _param * param)
 fprintf(stderr, %s, PQerrorMessage(conn));
 }
 else
+{
 deleted++;
+if(param-transaction_limit!=0 
deleted=param-transaction_limit)
+break;
+}
 }
 else
+{
 deleted++;
+if(param-transaction_limit!=0 
deleted=param-transaction_limit)
+break;
+}
 }
 PQclear(res);

@@ -313,6 +322,7 @@ usage(const char *progname)
 printf(  -h HOSTNAME  database server host or socket directory\n);
 printf(  -n   don't remove large objects, just show what would
be done\n);
 printf(  -p PORT  database server port\n);
+printf(  -l LIMIT stop after removing LIMIT LOs\n);
 printf(  -U USERNAME  user name to connect as\n);
 printf(  -w   never prompt for password\n);
 printf(  -W   force password prompt\n);
@@ -342,6 +352,7 @@ main(int argc, char **argv)
 param.pg_port = NULL;
 param.verbose = 0;
 param.dry_run = 0;
+param.transaction_limit = 0;

 if (argc  1)
 {
@@ -359,7 +370,7 @@ main(int argc, char **argv)

 while (1)
 {
-c = getopt(argc, argv, h:U:p:vnwW);
+c = getopt(argc, argv, h:U:p:l:vnwW);
 if (c == -1)
 break;

@@ -395,6 +406,14 @@ main(int argc, char **argv)
 }
 param.pg_port = strdup(optarg);
 break;
+case 'l':
+param.transaction_limit = strtol(optarg, NULL, 10);
+if ((param.transaction_limit  0) ||
(param.transaction_limit  2147483647))
+{
+fprintf(stderr, %s: invalid transaction limit number:
%s, valid range is form 0(disabled) to 2147483647.\n, progname, optarg);
+exit(1);
+}
+break;
 case 'h':
 param.pg_host = strdup(optarg);
 break;


Re: [HACKERS] Questions and experiences writing a Foreign Data Wrapper

2011-07-25 Thread Robert Haas
On Fri, Jul 22, 2011 at 8:09 AM, Albe Laurenz laurenz.a...@wien.gv.at wrote:
 Heikki Linnakangas wrote:
 I guess I misunderstood the concept of user mapping.

 I guess it is time to read my SQL Standard, but some clarification
 in the documentation sure wouldn't hurt.

 Agreed, there doesn't seem to be any documentation on user mappings,
 aside from the reference page for the CREATE USER MAPPING command. The
 5.10 Foreign Data section should explain what user mappings are.
 Want
 to give it a shot?

 Sure, see the attached 'fdw-usermapping-doc.patch'.

 I also include a 'fdw-scan-doc.patch' that tells FDW implementors *not*
 to start the scan in BeginForeignScan.

I've applied these with some modifications.

Thanks for the patches!

-- 
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] python cleanup

2011-07-25 Thread Andrew Dunstan



On 07/24/2011 11:46 PM, Tom Lane wrote:

[python headers set _POSIX_C_SOURCE and _XOPEN_SOURCE]

What in the world are the python headers doing fooling with these
macros, anyway??


Good question.  It seems unfriendly. It looks like you're just about guaranteed 
to get a warning if you include any system header before you include Python.h.

So either we have to dance around that or we have to give up the idea that 
postgres.h must come first. It wouldn't be the first time we've had to do that 
sort of dance.

The reason we get warnings about these and not about many other things it 
defines (such as the HAVE_foo macros) is that these are set to values different 
from those encountered in the previously included headers.

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] python cleanup

2011-07-25 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes:
 On 07/24/2011 11:46 PM, Tom Lane wrote:
 [python headers set _POSIX_C_SOURCE and _XOPEN_SOURCE]

 What in the world are the python headers doing fooling with these
 macros, anyway??

 The reason we get warnings about these and not about many other things it 
 defines (such as the HAVE_foo macros) is that these are set to values 
 different from those encountered in the previously included headers.

That's pretty scary in itself, since it suggests that the Python guys
know or think that changing those values will do something magic.

I'm worried that they are trying to do the same kind of thing that
we are trying to do with our put-postgres.h-first rule, namely ensure
that all loadable modules match the core's idea of libc properties.
If that's what's going on here, and their idea of those properties
is different from our standard build, then we may have worse problems
than a compiler warning.

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] python cleanup

2011-07-25 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes:
 [python headers set _POSIX_C_SOURCE and _XOPEN_SOURCE]

BTW ... so far as I can find, there is no attempt anywhere in the
Postgres sources to set either of these macros.  And my understanding of
their purpose is that *system* headers should not be setting them at
all, rather the application sets them to indicate which POSIX feature
level it would like.  So perhaps the real question here is where the
heck are your conflicting values coming from ...

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] python cleanup

2011-07-25 Thread Andrew Dunstan



On 07/25/2011 10:36 AM, Tom Lane wrote:

Andrew Dunstanand...@dunslane.net  writes:

[python headers set _POSIX_C_SOURCE and _XOPEN_SOURCE]

BTW ... so far as I can find, there is no attempt anywhere in the
Postgres sources to set either of these macros.  And my understanding of
their purpose is that *system* headers should not be setting them at
all, rather the application sets them to indicate which POSIX feature
level it would like.  So perhaps the real question here is where the
heck are your conflicting values coming from ...





_POSIX_C_SOURCE at least is defined in features.h, which is included by 
huge numbers of system headers, many of which are included by c.h.


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] Environment checks prior to regression tests?

2011-07-25 Thread Robert Haas
On Mon, Jul 25, 2011 at 4:36 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote:
 The attached patch enables to check prerequisites to run regression
 test of sepgsql module.
 It adds a dependency to installcheck that allows us to launch
 a script to check environment of operating system.

Committed.

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

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


Re: [HACKERS] python cleanup

2011-07-25 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes:
 On 07/25/2011 10:36 AM, Tom Lane wrote:
 Andrew Dunstanand...@dunslane.net  writes:
 [python headers set _POSIX_C_SOURCE and _XOPEN_SOURCE]

 BTW ... so far as I can find, there is no attempt anywhere in the
 Postgres sources to set either of these macros.  And my understanding of
 their purpose is that *system* headers should not be setting them at
 all, rather the application sets them to indicate which POSIX feature
 level it would like.  So perhaps the real question here is where the
 heck are your conflicting values coming from ...

 _POSIX_C_SOURCE at least is defined in features.h, which is included by 
 huge numbers of system headers, many of which are included by c.h.

What is features.h, and have its authors read the POSIX standard?
AFAICS they have no business defining this symbol.

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] python cleanup

2011-07-25 Thread Andrew Dunstan



On 07/25/2011 10:52 AM, Tom Lane wrote:

Andrew Dunstanand...@dunslane.net  writes:

On 07/25/2011 10:36 AM, Tom Lane wrote:

Andrew Dunstanand...@dunslane.net   writes:

[python headers set _POSIX_C_SOURCE and _XOPEN_SOURCE]

BTW ... so far as I can find, there is no attempt anywhere in the
Postgres sources to set either of these macros.  And my understanding of
their purpose is that *system* headers should not be setting them at
all, rather the application sets them to indicate which POSIX feature
level it would like.  So perhaps the real question here is where the
heck are your conflicting values coming from ...

_POSIX_C_SOURCE at least is defined in features.h, which is included by
huge numbers of system headers, many of which are included by c.h.

What is features.h, and have its authors read the POSIX standard?
AFAICS they have no business defining this symbol.




   [andrew@emma ~]$ rpm -q -f /usr/include/features.h
   glibc-headers-2.13-1.x86_64


   [andrew@emma ~]$ cat foo.c
   #include stdio.h
   #include Python.h

   main() {}

   [andrew@emma ~]$ gcc -I/usr/include/python2.7/ -c foo.c
   In file included from /usr/include/python2.7/pyconfig.h:6:0,
 from /usr/include/python2.7/Python.h:8,
 from foo.c:2:
   /usr/include/python2.7/pyconfig-64.h:1158:0: warning:
   _POSIX_C_SOURCE redefined
   /usr/include/features.h:214:0: note: this is the location of the
   previous definition



See now?

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] Tracing in Postgres

2011-07-25 Thread Robert Haas
On Mon, Jul 25, 2011 at 12:59 AM, Harshitha S hershe...@gmail.com wrote:
 I want to retain all the error messages, error report that is used by
 Postgres.
 I don't intend to log any information extra other than what is provided by
 Postgres.
 But I just want to replace the implementation of the logging/tracing in
 Postgres, so that the existing messages can be redirected to a file, a USB
 etc., There is an existing tracing frameworkfor this,I intend to use the API
 s provided by this framework.

I'd suggest that you look at adding the functionality you are
interested in to the logging collector, rather than putting it
directly in elog.c.  That's pretty much exactly what the logging
collector is designed to do, so you'd be extending or adjusting
existing functionality, rather than reinventing the wheel.

-- 
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] WIP fix proposal for bug #6123

2011-07-25 Thread Robert Haas
On Fri, Jul 22, 2011 at 5:01 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
 Your scenario is a BEFORE DELETE trigger that does an UPDATE on
 the same row, but I think this problem also occurs if you have a
 BEFORE UPDATE trigger that does an UPDATE on the same row.  I
 believe the second update gets silently ignored.

 My testing shows that the primary update gets ignored, while all the
 triggered effects of that update are persisted.  Yuck.  :-(

That was my recollection...

 It
 certainly seems possible to turn that around, but that hardly seems
 better.

Agreed.

 In asking application programmers here what they would
 *expect* to happen, they all seem to think that it is surprising
 that the BEFORE trigger functions *return a record*, rather than a
 boolean to say whether to proceed with the operation.  They feel it
 would be less confusing if a value set into NEW was effective if the
 operation does take effect, and the boolean controls whether or not
 that happens.  They rather expect that if an update happens from the
 same transaction while a before trigger is running, that the NEW
 record will reflect the change.

I think this is mostly a matter of what you get familiar with, and, as
you say, not worth breaking compatibility for.

 I recognize how hard it would be to create that expected behavior,
 and how unlikely it is that the community would accept such a change
 at this point.  But current behavior is to silently do something
 really dumb, so I think some change should be considered -- even if
 that change is to throw an error where we now allow nonsense.

 INSERT is not a problem -- if a BEFORE INSERT trigger inserts a row
 with a conflicting primary key (or other unique index key), the
 operation will be rolled back.  That's fine.

 I think DELETE can be cleanly fixed with a patch similar to what I
 posted earlier in the thread.  I found one more value that looks
 like it should be set, and it could use some comments, but I believe
 that we can get DELETE behavior which is every bit as sensible as
 INSERT behavior with a very small change.

 The worms do come crawling out of the can on BEFORE UPDATE triggers,
 though.  When faced with an UPDATE which hasn't yet been applied,
 and other UPDATEs triggering from within the BEFORE UPDATE trigger
 which touch the same row, it doesn't seem like you can honor both
 the original UPDATE which was requested *and* the triggered UPDATEs.
 Of course, if you discard the original UPDATE, you can't very well
 do anything with the record returned from the BEFORE UPDATE trigger
 for that update.  Since it seems equally evil to allow the update
 while ignoring some of the work caused by its trigger functions as
 to show the work of the triggered updates while suppressing the
 original update, I think the right thing is to throw an error if the
 old row for a BEFORE UPDATE is updated by the same transaction and
 the trigger function ultimately returns a non-NULL value.

 Thoughts?

Well, it seems to me that if the trigger update and the main update
were executed as separate commands (with no triggers involved) it
would often be the case that they'd dovetail nicely.  When this has
come up for me, it's usually been the case that the sets of fields
being updated are completely non-overlapping.  So ideally what I'd
like to happen is to have EPQ, or something like it, test whether the
newest version of the row still satisfies the UPDATE criteria.  If so,
it applies the update to the new row version; if not, it either
discards the main UPDATE or throws an error.  There's still some room
here for surprising results, but I think they would be surprising
results arising out of having done something intrinsically
complicated, rather than surprising results arising out of an odd
implementation artifact.

-- 
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] python cleanup

2011-07-25 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes:
 On 07/25/2011 10:52 AM, Tom Lane wrote:
 What is features.h, and have its authors read the POSIX standard?
 AFAICS they have no business defining this symbol.

 [andrew@emma ~]$ rpm -q -f /usr/include/features.h
 glibc-headers-2.13-1.x86_64

Oh, for some reason I was thinking this was mingw-specific.

[ pokes around ... ]  I still think it's a bad idea for the header
files to be defining this, but they'll probably point at the part
of the POSIX spec that says the results are undefined if the macro
is changed after the first system header is #included.

I can't immediately think of any way to actually do what you were
trying to do (ie, save and restore the definition of the macro).
I wonder whether it would be good enough to do this:

#include postgres.h

#include everything else we want except python headers

#undef _POSIX_C_SOURCE
#undef _XOPEN_SOURCE

#include python headers

... rest of .c file ...

This should only fail if (a) some macro imported from system headers
attempts to test the value of a feature macro, and (b) the results
vary between the system default setting and the setting the python
headers selected.  Neither of these things seem very probable.

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] WIP fix proposal for bug #6123

2011-07-25 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote:
 
 Well, it seems to me that if the trigger update and the main
 update were executed as separate commands (with no triggers
 involved) it would often be the case that they'd dovetail nicely. 
 When this has come up for me, it's usually been the case that the
 sets of fields being updated are completely non-overlapping.
 
Agreed that this is typically the case -- that's why the application
programmers here expected NEW to be effectively a dynamic
representation of the WIP state of the row.  A lot of things would
just work that way.  Of course, they're blissfully unaware of what
a huge revamp of the guts of PostgreSQL that would be.
 
 So ideally what I'd like to happen is to have EPQ, or something
 like it, test whether the newest version of the row still
 satisfies the UPDATE criteria.  If so, it applies the update to
 the new row version; if not, it either discards the main UPDATE or
 throws an error.  There's still some room here for surprising
 results, but I think they would be surprising results arising out
 of having done something intrinsically complicated, rather than
 surprising results arising out of an odd implementation artifact.
 
So, you're advocating a logical merge of the results with
something exceptional done on a conflicting update of the same
columns?  That would effectively get you to the same end result as a
live NEW tuple, but without such a radical revamp of the guts of
things.  Still, not trivial to do properly, and I would argue for
throwing an error rather than silently doing something surprising on
conflict.
 
This issue has already forced the rearrangement of our release
schedule here, so I'm going to do the simple fix of just throwing an
error on update from the BEFORE UPDATE trigger (of the row for with
the trigger is firing).  That fix is very simple and seems very safe
to me, and should allow us to deploy without further schedule
slippage; then I'll see if I can code up what you're suggesting.  I
had a new patch I was about to post with new error language, a
different SQLSTATE, comments, and regression test changes; but
unless someone wants to see that I won't clutter the list with it
until I've had a chance to see if I can manage to handle it the way
you're requesting.
 
There's no doubt that it would be better the way you're suggesting;
but it looks to me like about five times as many lines of code,
harder to be sure it's right, and probably forcing me to learn a few
new subsystems of PostgreSQL internals to accomplish.
 
Thanks for the feedback.
 
-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] psql: display of object comments

2011-07-25 Thread Robert Haas
On Fri, Jul 22, 2011 at 10:44 PM, Josh Kupershmidt schmi...@gmail.com wrote:
 On Sat, Jul 9, 2011 at 1:16 PM, Josh Kupershmidt schmi...@gmail.com wrote:

 Attached is an updated version of this patch, lifted out of the recent
 pg_comments patch. With this v2 patch, \dd should properly show just
 its five object types, and the psql documentation and help strings
 should be fixed.

I took a look at this patch today and I think some of these queries
are not quite right.  When you do a left join against pg_description,
you have this sort of thing in the WHERE clause:

(d.objsubid IS NULL OR d.objsubid = 0)

I think what you actually want is AND d.objsubid = 0 in the LEFT
JOIN's ON clause.  Then you are, in effect, only left joining
against the rows from pg_description where objsubid = 0, and
null-extending if none such is found.  I think that's what you want.

I think you can remove the XXX comments, too.  Unless I'm
misunderstanding something, using the table to test visibility for
constraints, rules, and triggers seems just right, and opclasses and
opfamilies you have a suitable function available, so those don't seem
problematic.  Or am I confused?

-- 
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] Another issue with invalid XML values

2011-07-25 Thread Bernd Helmle



--On 20. Juli 2011 13:06:17 -0400 Tom Lane t...@sss.pgh.pa.us wrote:


I've committed this patch with the discussed changes and some other
editorialization.  I have to leave for an appointment and can't write
anything now about the changes, but feel free to ask questions if you
have any.


Hmm, when building against libxml2 2.7.8 i get reproducible failing regression 
tests on OSX 10.6.7. It is griping with


WARNING:  libxml error handling state is out of sync with xml.c

all over the place.

A quick check with compiling against the libxml2 shipped with OSX (which seems 
libxml2 2.7.3) causes everything to work as expected, however.


--
Thanks

Bernd

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


Re: [HACKERS] WIP fix proposal for bug #6123

2011-07-25 Thread Robert Haas
On Mon, Jul 25, 2011 at 12:26 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
 There's no doubt that it would be better the way you're suggesting;
 but it looks to me like about five times as many lines of code,
 harder to be sure it's right, and probably forcing me to learn a few
 new subsystems of PostgreSQL internals to accomplish.

Sorry, I didn't mean to make homework for you.  Nor am I sure that the
solution will pass must all around even if I think it's the best thing
since sliced bread.  I was just throwing it out there as what I would
like to have happen in an ideal world...

-- 
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] Another issue with invalid XML values

2011-07-25 Thread Florian Pflug
On Jul25, 2011, at 18:53 , Bernd Helmle wrote:
 --On 20. Juli 2011 13:06:17 -0400 Tom Lane t...@sss.pgh.pa.us wrote:
 I've committed this patch with the discussed changes and some other
 editorialization.  I have to leave for an appointment and can't write
 anything now about the changes, but feel free to ask questions if you
 have any.
 
 Hmm, when building against libxml2 2.7.8 i get reproducible failing
 regression tests on OSX 10.6.7. It is griping with
 
 WARNING:  libxml error handling state is out of sync with xml.c
 
 all over the place.
 
 A quick check with compiling against the libxml2 shipped with OSX
 (which seems libxml2 2.7.3) causes everything to work as expected, however.


Hm, I have libxml2 2.7.8, installed via Mac Ports, and I cannot reproduce
this. Maybe Mac Ports uses a modified libxml2, though. I'll check that.

Where did you obtain libxml2 from?

best regards,
Florian Pflug


-- 
Sent 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 fix proposal for bug #6123

2011-07-25 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote:
 On Mon, Jul 25, 2011 at 12:26 PM, Kevin Grittner
 kevin.gritt...@wicourts.gov wrote:
 There's no doubt that it would be better the way you're
 suggesting; but it looks to me like about five times as many
 lines of code, harder to be sure it's right, and probably forcing
 me to learn a few new subsystems of PostgreSQL internals to
 accomplish.
 
 Sorry, I didn't mean to make homework for you.  Nor am I sure that
 the solution will pass must all around even if I think it's the
 best thing since sliced bread.  I was just throwing it out there
 as what I would like to have happen in an ideal world...
 
Well, if it can be done, it will be better and less likely to break
existing code, so it's at least worth looking at.  I don't object to
broadening my horizons.  ;-)  Sorry if it sounded like a complaint;
my intention was to communicate that I'm going to be looking at it,
but I've got a few more urgent tasks to deal with first to get our
application release out the door.
 
By the way, my current patch does break two existing UPDATE
statements in the regression test misc.sql file:
 
| -- This non-func update stuff needs to be examined
| -- more closely.  - jolly (2/22/96)
| --
| UPDATE tmp
|SET stringu1 = reverse_name(onek.stringu1)
|FROM onek
|WHERE onek.stringu1 = 'JB' and
|   onek.stringu1 = tmp.stringu1;
| 
| UPDATE tmp
|SET stringu1 = reverse_name(onek2.stringu1)
|FROM onek2
|WHERE onek2.stringu1 = 'JC' and
|   onek2.stringu1 = tmp.stringu1;
 
Perhaps it's time
 
-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] Another issue with invalid XML values

2011-07-25 Thread Bernd Helmle



--On 25. Juli 2011 19:07:50 +0200 Florian Pflug f...@phlo.org wrote:


Hm, I have libxml2 2.7.8, installed via Mac Ports, and I cannot reproduce
this. Maybe Mac Ports uses a modified libxml2, though. I'll check that.

Where did you obtain libxml2 from?


This is MacPorts, too:

% port installed libxml2
The following ports are currently installed:
 libxml2 @2.7.8_0 (active)

I've reduced my configure line to the least required options

./configure --with-libxml --with-includes=/opt/local/include 
--with-libraries=/opt/local/lib


but still get the WARNINGs in the regression.diffs. Which settings do you use?

--
Thanks

Bernd

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


Re: [HACKERS] Problem with pg_upgrade's directory write check on Windows

2011-07-25 Thread Robert Haas
On Sun, Jul 24, 2011 at 5:27 PM, Bruce Momjian br...@momjian.us wrote:
 Alvaro Herrera wrote:
 Excerpts from Bruce Momjian's message of dom jul 24 01:46:08 -0400 2011:
  Robert Haas wrote:

Should I fix this in pg_upgrade 9.1 for Windows or just in 9.2? ?The
check works fine on non-Windows.
  
   Seems worth back-patching to me.
 
  Attached patch applied and backpatched to 9.1.  I was able to test both
  code paths on my BSD machine by modifying the ifdefs.  I will have
  EnterpriseDB do further testing.

 Err, why not 9.0?

 The check did not exist in 9.0 -- I mentioned that in the commit
 message.  I could add the check into 9.0, but we usually don't backpatch
 such things.

What do you mean by such things?

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

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


Re: [HACKERS] Another issue with invalid XML values

2011-07-25 Thread Florian Pflug
On Jul25, 2011, at 19:37 , Bernd Helmle wrote:
 --On 25. Juli 2011 19:07:50 +0200 Florian Pflug f...@phlo.org wrote:
 Hm, I have libxml2 2.7.8, installed via Mac Ports, and I cannot reproduce
 this. Maybe Mac Ports uses a modified libxml2, though. I'll check that.
 
 Where did you obtain libxml2 from?
 
 This is MacPorts, too:
 
 % port installed libxml2
 The following ports are currently installed:
 libxml2 @2.7.8_0 (active)

'bout the same here:

$ port installed libxml2
The following ports are currently installed:
  libxml2 @2.7.8_0+universal (active)

 I've reduced my configure line to the least required options
 
 ./configure --with-libxml --with-includes=/opt/local/include 
 --with-libraries=/opt/local/lib
 
 but still get the WARNINGs in the regression.diffs.

I got a theory. We do distinguish between libxml2 versions for which
the structured and the generic error context handler share the error
context (older ones), and those with don't (newer ones). Our configure
scripts checks for the availability of xmlStructuredErrorContext, and
defined HAVE_XMLSTRUCTUREDERRORCONTEXT if it is. Now, if for some reason
that test fails on your machine, even though libxml *does* provide
xmlStructuredErrorContext, then the safety-check in the error handler
would check whether xmlGenericErrorContext is set as expected, when
it really should check xmlStructuredErrorContext.

Could you check if configure defines that macro? You should find
it in the pg_config.h generated by configure.

 Which settings do you use?

configure \
--prefix=/Users/fgp/Installs/pg.master.max.noassert.O1 \
--with-includes=/opt/local/include \
--with-libraries=/opt/local/lib \
--enable-debug \
--enable-depend \
--enable-thread-safety \
--with-pgport=54320 \
--without-tcl \
--with-perl \
--with-python \
--without-gssapi \
--without-krb5 \
--without-pam \
--without-ldap \
--without-bonjour \
--without-openssl \
--without-ossp-uuid \
--with-libxml \
--with-libxslt CFLAGS=-pipe -O1 -g

I also checked with otool -L that it really uses the libxml from /opt.

$ otool -L 
.//src/test/regress/tmp_check/install/Users/fgp/Installs/pg.master.max.noassert.O1/bin/postgres
.//src/test/regress/tmp_check/install/Users/fgp/Installs/pg.master.max.noassert.O1/bin/postgres:
/opt/local/lib/libxml2.2.dylib (compatibility version 10.0.0, current 
version 10.8.0)
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current 
version 125.2.11)

Despite the file name, that should be libxml 2.7.8. Here's the output of
xml2-config

$ /opt/local/bin/xml2-config --version
2.7.8

And there's no other libxml2 in /opt.

best regards,
Florian Pflug


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


Re: [HACKERS] Deferred partial/expression unique constraints

2011-07-25 Thread Jeff Davis
On Fri, 2011-07-22 at 23:35 +0300, Peter Eisentraut wrote:
 On ons, 2011-07-13 at 11:26 -0400, Tom Lane wrote:
  Our standard reason for not implementing UNIQUE constraints on
  expressions has been that then you would have a thing that claims to be
  a UNIQUE constraint but isn't representable in the information_schema
  views that are supposed to show UNIQUE constraints.  We avoid this
  objection in the current design by shoving all that functionality into
  EXCLUDE constraints, which are clearly outside the scope of the spec.
 
 I have never heard that reason before, and I think it's a pretty poor
 one.  There are a lot of other things that are not representable in the
 information schema.

I think what Tom is saying is that the information_schema might appear
inconsistent to someone following the spec.

Can you give another example where we do something like that?

Regards,
Jeff Davis


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


Re: [HACKERS] Another issue with invalid XML values

2011-07-25 Thread Bernd Helmle



--On 25. Juli 2011 19:57:40 +0200 Florian Pflug f...@phlo.org wrote:


I got a theory. We do distinguish between libxml2 versions for which
the structured and the generic error context handler share the error
context (older ones), and those with don't (newer ones). Our configure
scripts checks for the availability of xmlStructuredErrorContext, and
defined HAVE_XMLSTRUCTUREDERRORCONTEXT if it is. Now, if for some reason
that test fails on your machine, even though libxml *does* provide
xmlStructuredErrorContext, then the safety-check in the error handler
would check whether xmlGenericErrorContext is set as expected, when
it really should check xmlStructuredErrorContext.

Could you check if configure defines that macro? You should find
it in the pg_config.h generated by configure.


This is what pg_config.h says:

% grep HAVE_XMLSTRUCTUREDERRORCONTEXT src/include/pg_config.h
/* #undef HAVE_XMLSTRUCTUREDERRORCONTEXT */

Ah, but i got now what's wrong here: configure is confusing both libxml2 
installations, and a quick look into config.log proves that: it uses the 
xml2-config from the OSX libs (my $PATH has /usr in front of the bindir of 
MacPorts, though i seem to recall to have changed this in the past).


So, all i need to do is

XML2_CONFIG=/opt/local/bin/xml2-config ./configure --with-libxml 
--with-includes=/opt/local/include/ --with-libraries=/opt/local/lib


and everything is smooth:

% grep HAVE_XMLSTRUCTUREDERRORCONTEXT src/include/pg_config.h#define 
HAVE_XMLSTRUCTUREDERRORCONTEXT 1


Regression tests passes now. This was too obvious...

--
Thanks

Bernd

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


Re: [HACKERS] WIP: Fast GiST index build

2011-07-25 Thread Heikki Linnakangas

On 22.07.2011 12:38, Alexander Korotkov wrote:

Patch with my try to detect ordered datasets is attached. The implemented
idea is desribed below.
Index tuples are divided by chunks of 128. On each chunk we measure how much
leaf pages where index tuples was inserted don't match those of previous
chunk. Based on statistics of several chunks we estimate distribution of
accesses between lead pages (exponential distribution law is accumed and
it's seems to be an error). After that we can estimate portion of index
tuples which can be processed without actual IO. If this estimate exceeds
threshold then we should switch to buffering build.
Now my implementation successfully detects randomly mixed datasets and well
ordered datasets, but it's seems to be too optimistic about intermediate
cases. I believe it's due to wrong assumption about distribution law.
Do you think this approach is acceptable? Probably there are some researches
about distribution law for such cases (while I didn't find anything relevant
in google scholar)?


Great! It would be nice to find a more scientific approach to this, but 
that's probably fine for now. It's time to start cleaning up the patch 
for eventual commit.


You got rid of the extra page pins, which is good, but I wonder why you 
still pre-create all the GISTLoadedPartItem structs for the whole 
subtree in loadTreePart() ? Can't you create those structs on-the-fly, 
when you descend the tree? I understand that it's difficult to update 
all the parent-pointers as trees are split, but it feels like there's 
way too much bookkeeping going on. Surely it's possible to simplify it 
somehow..


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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


Re: [HACKERS] proposal: a validator for configuration files

2011-07-25 Thread Alexey Klyukin

On Jul 16, 2011, at 9:55 PM, Tom Lane wrote:

 I wrote:
 I think that it might be sensible to have the following behavior:
 
 1. Parse the file, where parse means collect all the name = value
 pairs.  Bail out if we find any syntax errors at that level of detail.
 (With this patch, we could report some or all of the syntax errors
 first.)
 
 2. Tentatively apply the new custom_variable_classes setting if any.
 
 3. Check to see whether all the names are valid.  If not, report
 the ones that aren't and bail out.
 
 4. Apply each value.  If some of them aren't valid, report that,
 but continue, and apply all the ones that are valid.
 
 We can expect that the postmaster and all backends will agree on the
 results of steps 1 through 3.  They might differ as to the validity
 of individual values in step 4 (as per my example of a setting that
 depends on database_encoding), but we should never end up with a
 situation where a globally correct value is not globally applied.
 

Attached is my first attempt to implement your plan. Basically, I've
reshuffled pieces of the ProcessConfigFile on top of my previous patch,
dropped verification calls of set_config_option and moved the check for
custom_variable_class existence right inside the loop that assigns new values
to GUC variables.

I'd think that removal of custom_variable_classes or setting it from the
extensions could be a separate patch.

I appreciate your comments and suggestions.

 I thought some more about this, and it occurred to me that it's not that
 hard to foresee a situation where different backends might have
 different opinions about the results of step 3, ie, different ideas
 about the set of valid GUC names.  This could arise as a result of some
 of them having a particular extension module loaded and others not.
 
 Right now, whether or not you have say plpgsql loaded will not affect
 your ability to do SET plpgsql.junk = foobar --- as long as plpgsql
 is listed in custom_variable_classes, we'll accept the command and
 create a placeholder variable for plpgsql.junk.  But it seems perfectly
 plausible that we might someday try to tighten that up so that once a
 module has done EmitWarningsOnPlaceholders(plpgsql), we'll no longer
 allow creation of new placeholders named plpgsql.something.  If we did
 that, we could no longer assume that all backends agree on the set of
 legal GUC variable names.
 
 So that seems like an argument --- not terribly strong, but still an
 argument --- for doing what I suggested next:
 
 The original argument for the current behavior was to avoid applying
 settings from a thoroughly munged config file, but I think that the
 checks involved in steps 1-3 would be sufficient to reject files that
 had major problems.  It's possible that step 1 is really sufficient to
 cover the issue, in which case we could drop the separate step-3 pass
 and just treat invalid GUC names as a reason to ignore the particular
 line rather than the whole file.  That would make things simpler and
 faster, and maybe less surprising too.
 
 IOW, I'm now pretty well convinced that so long as the configuration
 file is syntactically valid, we should go ahead and attempt to apply
 each name = value setting individually, without allowing the invalidity
 of any one name or value to prevent others from being applied.


--
Command Prompt, Inc.  http://www.CommandPrompt.com
PostgreSQL Replication, Consulting, Custom Development, 24x7 support




pg_parser_continue_on_error_v4.patch
Description: Binary data

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


Re: [HACKERS] problem with compiling beta3 on mingw32+WinXP

2011-07-25 Thread pasman pasmański
After googling i found that mingw's gcc works with 64 bit integers.
But printf is incompatible :( . Possible workaround: include
inttypes.h , define macros and convert printf strings:

printf(% LL,(long long)100)

2011/7/25, pasman pasmański pasma...@gmail.com:
 Hi.

 When i try to compile postgresql-beta3 on mingw32 ./configure pass ok,
 but there is error when i do make:


 gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith
 -Wdeclaration-after-statement
  -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv
 -I../../src/port
 -DFRONTEND -I../../src/include -I./src/include/port/win32 -DEXEC_BACKEND
 -I../
 ../src/include/port/win32  -c -o crypt.o crypt.c
 In file included from crypt.c:44:0:
 ../../src/include/c.h:284:2: error: #error must have a working 64-bit
 integer da
 tatype
 In file included from ../../src/include/c.h:851:0,
  from crypt.c:44:
 ../../src/include/port.h:390:0: warning: fseeko redefined
 ../../src/include/pg_config_os.h:228:0: note: this is the location of the
 previo
 us definition
 ../../src/include/port.h:391:0: warning: ftello redefined
 ../../src/include/pg_config_os.h:229:0: note: this is the location of the
 previo
 us definition
 make[2]: *** [crypt.o] Error 1
 make[2]: Leaving directory `/home/rosinkr1/postgresql-9.1beta3/src/port'
 make[1]: *** [all-port-recurse] Error 2
 make[1]: Leaving directory `/home/rosinkr1/postgresql-9.1beta3/src'
 make: *** [all-src-recurse] Error 2

 Make version 3.81.
 Status file included.
 What is wrong ?


 
 pasman



-- 

pasman

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


Re: [HACKERS] Another issue with invalid XML values

2011-07-25 Thread Florian Pflug
On Jul25, 2011, at 20:37 , Bernd Helmle wrote:
 Ah, but i got now what's wrong here: configure is confusing both libxml2
 installations, and a quick look into config.log proves that: it uses the
 xml2-config from the OSX libs (my $PATH has /usr in front of the bindir of
 MacPorts, though i seem to recall to have changed this in the past).
 
 So, all i need to do is
 
 XML2_CONFIG=/opt/local/bin/xml2-config ./configure --with-libxml
 --with-includes=/opt/local/include/ --with-libraries=/opt/local/lib
 
 and everything is smooth:
 
 % grep HAVE_XMLSTRUCTUREDERRORCONTEXT src/include/pg_config.h
 #define HAVE_XMLSTRUCTUREDERRORCONTEXT 1
 
 Regression tests passes now. This was too obvious...


Hm, but I still think there's a bug lurking there. Using a different libxml2
version for the configure checks than for actual builds surely isn't good...

From looking at configure.in, it seems that we use xml2-config to figure out
the CFLAGS and LDFLAGS required to build and link against libxml. I guess we
somehow end up not using these flags when we later test for
xmlStructuredErrorContext, but do use them during the actual build. Or maybe
the order of the -I and -L flags just ends up being different in the two cases.

My skills in the black art that are autotools are severely lacking, so it's
quite likely that I somehow botched the incantations we use to test for
xmlStructuredErrorContext. I don't really know where to start looking for the
error, though. Ideas, anyone?

best regards,
Florian Pflug


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


Re: [HACKERS] problem with compiling beta3 on mingw32+WinXP

2011-07-25 Thread Andrew Dunstan




On 07/25/2011 02:56 PM, pasman pasmański wrote:

After googling i found that mingw's gcc works with 64 bit integers.
But printf is incompatible :( . Possible workaround: include
inttypes.h , define macros and convert printf strings:

printf(% LL,(long long)100)


Postgres builds under mingw and runs perfectly well on 32 bit Windows 
XP. See
http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=frogmouthdt=2011-07-25%2006%3A30%3A01 
for example.


(Also, please avoid top-posting).

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


[HACKERS] write scalability

2011-07-25 Thread Robert Haas
I've long harbored a suspicion, based on some testing I did on my home
machine, that WALInsertLock is a big performance bottleneck.  But I
just did some benchmarking that doesn't entirely support that
contention.  This is on Nate Boley's 32-core machine, with the
following settings:

max_connections = 100
shared_buffers = 8GB
synchronous_commit = off
checkpoint_segments = 100
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9

I did 5-minute pgbench runs with unlogged tables and with permanent
tables, restarting the database server and reinitializing the tables
between each run.  The number at the beginning of each line is the
number of clients, while the p/u indicates which type of tables were
used:

01p tps = 628.626815 (including connections establishing)
01p tps = 635.394288 (including connections establishing)
01p tps = 634.972789 (including connections establishing)
08p tps = 3342.787325 (including connections establishing)
08p tps = 3883.876813 (including connections establishing)
08p tps = 3941.253567 (including connections establishing)
32p tps = 5597.472192 (including connections establishing)
32p tps = 5738.139895 (including connections establishing)
32p tps = 5794.490934 (including connections establishing)
80p tps = 4499.685286 (including connections establishing)
80p tps = 4917.060441 (including connections establishing)
80p tps = 5050.931933 (including connections establishing)

01u tps = 672.469142 (including connections establishing)
01u tps = 671.256686 (including connections establishing)
01u tps = 670.421003 (including connections establishing)
08u tps = 4087.749529 (including connections establishing)
08u tps = 3797.750851 (including connections establishing)
08u tps = 4181.393560 (including connections establishing)
32u tps = 8956.346905 (including connections establishing)
32u tps = 8898.442517 (including connections establishing)
32u tps = 8971.591569 (including connections establishing)
80u tps = 7287.550952 (including connections establishing)
80u tps = 7266.816989 (including connections establishing)
80u tps = 7255.968109 (including connections establishing)

The speed-up from using unlogged tables was not as large as I
expected.  Turning off synchronous_commit here removes commit rate as
the bottleneck, and I think perhaps the main benefit of unlogged
tables in that case is the avoidance of I/O, and apparently this
machine has enough I/O bandwidth, and just enough memory in general,
that that's not an issue.

With either type of tables, the 8 client results are about 6.1 times
the single core results - not great, but not terrible, either.  With
32 clients, there is some improvement: 13.4x vs. 9.1x, but even 13.4x
is a long way from linear.  vmstat reveals that CPU usage is
substantially less than 100%.  After some investigation, I found that
using unlogged tables wasn't actually getting rid of all the
write-ahead logging - the commit records were still being issued.  So
I hacked up RecordTransactionCommit() not to emit transaction commit
records in that case.  That doesn't actually completely eliminate the
WAL activity, because it still emits records for zeroing new SLRU
pages for CLOG, but it takes a big chunk out of it.  The upshot is
that this improved both raw performance and scalability, but not
dramatically.  Unlogged table results, with this change:

01h tps = 708.189337 (including connections establishing)
01h tps = 704.030175 (including connections establishing)
01h tps = 701.644199 (including connections establishing)
08h tps = 5196.615955 (including connections establishing)
08h tps = 5126.162200 (including connections establishing)
08h tps = 5067.568727 (including connections establishing)
32h tps = 10661.275636 (including connections establishing)
32h tps = 10621.085226 (including connections establishing)
32h tps = 10575.267197 (including connections establishing)
80h tps = 7557.965666 (including connections establishing)
80h tps = 7545.697547 (including connections establishing)
80h tps = 7556.379921 (including connections establishing)

Now the 32-client numbers have risen to 15.1x the single-client
numbers, but that's still not great.

What does this mean?  Well, XLogInsert does strikes me as an awfully
complex piece of code to be running with a hot LWLock held in
exclusive mode.  But even so, I think this certainly means that
WALInsertLock, at least on this workload, is not the whole problem...
in this test, I'm not only eliminating the overhead of inserting the
WAL, but also the overhead of writing it, flushing it, and generating
it.   So there is something, other than WAL insertion, which is taking
a big bite out of performance here.

As to what that something might be, I reran this last test with
LWLOCK_STATS enabled and here are the top things that are blocking:

lwlock 310: shacq 96846 exacq 108433 blk 16275
lwlock 3: shacq 64 exacq 2628381 blk 36027
lwlock 7: shacq 0 exacq 2628615 blk 85220
lwlock 11: shacq 84913908 exacq 4539551 blk 2119423

Re: [HACKERS] Access to current database from C-language function

2011-07-25 Thread Achim Domma
Am 25.07.2011 um 14:48 schrieb Florian Pflug:

 A more low-level API is provided by {heap,index}_{beginscan,endscan}, 
 heap_{insert,update,delete} and index_insert. However, correct handling of 
 transactions using this API isn't easy - for example, to update a row you'd 
 first have to find the latest version of that row, then decide if you're 
 allowed to update it, and finally create a new version.
 

I see the problems with the second approach, but that's definitively what I 
would like to do. But I'm only interested in reading, which will hopefully make 
it a bit easier. Could you guide me to a starting point? Assuming my database 
has a table T with an index I. How do I get access to that index?

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


Re: [HACKERS] pgbench --unlogged-tables

2011-07-25 Thread Greg Smith

On 07/25/2011 09:23 AM, Robert Haas wrote:

At some point, we also need to sort out the scale factor limit issues,
so you can make these things bigger.
   


I had a patch to improve that whole situation, but it hasn't seem to nag 
at me recently.  I forget why it seemed less important, but I doubt I'll 
make it another six months without coming to some resolution there.


The two systems I have in for benchmarking right now have 128GB and 
192GB of RAM in them, so large scales should have been tested.  
Unfortunately, it looks like the real-world limiting factor on doing 
lots of tests at big scales is how long it takes to populate the data 
set.  For example, here's pgbench creation time on a big server (48 
cores, 128GB RAM) with a RAID10 array, when scale=2 (292GB):


real174m12.055s
user17m35.994s
sys 0m52.358s

And here's the same server putting the default tablespace (but not the 
WAL) on [much faster flash device I can't talk about yet]:


Creating new pgbench tables, scale=2
real169m59.541s
user18m19.527s
sys0m52.833s

I was hoping for a bigger drop here; maybe I needed to use unlogged 
tables? (ha!)  I think I need to start looking at the pgbench data 
generation stage as its own optimization problem.  Given how expensive 
systems this large are, I never get them for very long before they are 
rushed into production.  People don't like hearing that just generating 
the data set for a useful test is going to take 3 hours; that tends to 
limit how many of them I can schedule running.


And, yes, I'm going to try and sneak in some time to test fastpatch 
locking on one of these before they head into production.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us


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


Re: [HACKERS] write scalability

2011-07-25 Thread Merlin Moncure
On Mon, Jul 25, 2011 at 3:07 PM, Robert Haas robertmh...@gmail.com wrote:
 I've long harbored a suspicion, based on some testing I did on my home
 machine, that WALInsertLock is a big performance bottleneck.  But I
 just did some benchmarking that doesn't entirely support that
 contention.  This is on Nate Boley's 32-core machine, with the
 following settings:

 max_connections = 100
 shared_buffers = 8GB
 synchronous_commit = off
 checkpoint_segments = 100
 checkpoint_timeout = 15min
 checkpoint_completion_target = 0.9

 I did 5-minute pgbench runs with unlogged tables and with permanent
 tables, restarting the database server and reinitializing the tables
 between each run.  The number at the beginning of each line is the
 number of clients, while the p/u indicates which type of tables were
 used:

 01p tps = 628.626815 (including connections establishing)
 01p tps = 635.394288 (including connections establishing)
 01p tps = 634.972789 (including connections establishing)
 08p tps = 3342.787325 (including connections establishing)
 08p tps = 3883.876813 (including connections establishing)
 08p tps = 3941.253567 (including connections establishing)
 32p tps = 5597.472192 (including connections establishing)
 32p tps = 5738.139895 (including connections establishing)
 32p tps = 5794.490934 (including connections establishing)
 80p tps = 4499.685286 (including connections establishing)
 80p tps = 4917.060441 (including connections establishing)
 80p tps = 5050.931933 (including connections establishing)

 01u tps = 672.469142 (including connections establishing)
 01u tps = 671.256686 (including connections establishing)
 01u tps = 670.421003 (including connections establishing)
 08u tps = 4087.749529 (including connections establishing)
 08u tps = 3797.750851 (including connections establishing)
 08u tps = 4181.393560 (including connections establishing)
 32u tps = 8956.346905 (including connections establishing)
 32u tps = 8898.442517 (including connections establishing)
 32u tps = 8971.591569 (including connections establishing)
 80u tps = 7287.550952 (including connections establishing)
 80u tps = 7266.816989 (including connections establishing)
 80u tps = 7255.968109 (including connections establishing)

 The speed-up from using unlogged tables was not as large as I
 expected.  Turning off synchronous_commit here removes commit rate as
 the bottleneck, and I think perhaps the main benefit of unlogged
 tables in that case is the avoidance of I/O, and apparently this
 machine has enough I/O bandwidth, and just enough memory in general,
 that that's not an issue.

 With either type of tables, the 8 client results are about 6.1 times
 the single core results - not great, but not terrible, either.  With
 32 clients, there is some improvement: 13.4x vs. 9.1x, but even 13.4x
 is a long way from linear.  vmstat reveals that CPU usage is
 substantially less than 100%.  After some investigation, I found that
 using unlogged tables wasn't actually getting rid of all the
 write-ahead logging - the commit records were still being issued.  So
 I hacked up RecordTransactionCommit() not to emit transaction commit
 records in that case.  That doesn't actually completely eliminate the
 WAL activity, because it still emits records for zeroing new SLRU
 pages for CLOG, but it takes a big chunk out of it.  The upshot is
 that this improved both raw performance and scalability, but not
 dramatically.  Unlogged table results, with this change:

 01h tps = 708.189337 (including connections establishing)
 01h tps = 704.030175 (including connections establishing)
 01h tps = 701.644199 (including connections establishing)
 08h tps = 5196.615955 (including connections establishing)
 08h tps = 5126.162200 (including connections establishing)
 08h tps = 5067.568727 (including connections establishing)
 32h tps = 10661.275636 (including connections establishing)
 32h tps = 10621.085226 (including connections establishing)
 32h tps = 10575.267197 (including connections establishing)
 80h tps = 7557.965666 (including connections establishing)
 80h tps = 7545.697547 (including connections establishing)
 80h tps = 7556.379921 (including connections establishing)

 Now the 32-client numbers have risen to 15.1x the single-client
 numbers, but that's still not great.

 What does this mean?  Well, XLogInsert does strikes me as an awfully
 complex piece of code to be running with a hot LWLock held in
 exclusive mode.  But even so, I think this certainly means that
 WALInsertLock, at least on this workload, is not the whole problem...
 in this test, I'm not only eliminating the overhead of inserting the
 WAL, but also the overhead of writing it, flushing it, and generating
 it.   So there is something, other than WAL insertion, which is taking
 a big bite out of performance here.

 As to what that something might be, I reran this last test with
 LWLOCK_STATS enabled and here are the top things that are blocking:

 lwlock 310: shacq 96846 

Re: [HACKERS] write scalability

2011-07-25 Thread Alvaro Herrera
Excerpts from Merlin Moncure's message of lun jul 25 17:19:58 -0400 2011:
 On Mon, Jul 25, 2011 at 3:07 PM, Robert Haas robertmh...@gmail.com wrote:

  Experience
  with the read scalability stuff has taught me also to look at which
  LWLocks have the most shared acquisitions, as that can cause spinlock
  and cache line contention.  The top few culprits are:
 
  lwlock 504: shacq 5315030 exacq 0 blk 0
  lwlock 45: shacq 5967317 exacq 13284 blk 1722
  lwlock 39: shacq 8219988 exacq 13342 blk 2291
  lwlock 5: shacq 26306020 exacq 0 blk 0
  lwlock 4: shacq 28667307 exacq 2628524 blk 3356651
  lwlock 11: shacq 84913908 exacq 4539551 blk 2119423
 
  In all, there were 238777533 shared LWLock acquisitions during this
  test: 35% CLogControlLock, 12% ProcArrayLock, 11% SInvalReadLock (soon
  to be dealt with, as discussed elsewhere on-list), and then it gets
  down into the lock manager locks and a few others.
 
 hm, all the CLogControlLock acquisitions in clog.c appear to be
 exclusive...or did you mean shared in some other sense?

SLRU control locks are also acquired indirectly by slru.c, see
SimpleLruReadPage_ReadOnly.

 TransactionIdGetStatus is taking an exclusive lock which is a red flag
 and a good optimization target, I think.

In fact, if the page that TransactionIdGetStatus is looking for is in
the buffers, it'll only take a shared lock.

-- 
Á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] sinval synchronization considered harmful

2011-07-25 Thread Noah Misch
On Fri, Jul 22, 2011 at 03:54:03PM -0400, Robert Haas wrote:
 On Fri, Jul 22, 2011 at 3:28 PM, Noah Misch n...@2ndquadrant.com wrote:
  This is attractive, and I don't see any problems with it.  (In theory, you 
  could
  hit a case where the load of resetState gives an ancient false just as the
  counters wrap to match.  Given that the wrap interval is 100x as long 
  as the
  reset interval, I'm not worried about problems on actual silicon.)
 
 It's actually 262,144 times as long - see MSGNUMWRAPAROUND.

Ah, so it is.

 It would be pretty easy to eliminate even the theoretical possibility
 of a race by getting rid of resetState altogether and using nextMsgNum
 = -1 to mean that.  Maybe I should go ahead and do that.

Seems like a nice simplification.

-- 
Noah Mischhttp://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] storing TZ along timestamps

2011-07-25 Thread Jim Nasby
On Jul 22, 2011, at 10:33 AM, Robert Haas wrote:
 On Thu, Jul 21, 2011 at 7:05 PM, Jim Nasby j...@nasby.net wrote:
 On Jul 21, 2011, at 5:30 PM, Christopher Browne wrote:
 - I'd commend capturing NOW() in a timestamptz field.  That gives you:
 1.  What time the DB server thought it was, in terms of UT1
 2.  What timezone it thought was tied to that connection.
 
 Except that it doesn't, and that's exactly the problem I'm trying to solve 
 here. I want to know what timezone we were using when we put a value into 
 timestamptz, which then got converted to UT1. Without a reliable way to 
 store what the timezone *was* at that time, we have no way to go back to it.
 
 Now, we can debate whether it makes more sense to store the original time 
 without conversion to UT1, or whether we should store the time after 
 converting it to UT1 (or whether we should offer both options), but that 
 debate is pointless without a good way to remember what timezone it started 
 out in.
 
 Arguably, we could just create an add-on data type for storing that timezone 
 information, but that seems pretty daft to me: you're stuck either storing 
 raw text which takes what should be a 12 byte datatype up to a 20-30 byte 
 type (8 byte timestamp + varlena + text of timezone name), or you end up 
 with major problems trying to keep an enum in sync with what the database 
 has available in it's ZIC database.
 
 You have those same problems trying to include the time zone
 information in some new timestampreallyhasthetz data type, though.
 
 This problem reminds me a great deal of the problems associated with
 managing security labels for SE-Linux.  There aren't that many
 distinct values, so ideally it would be nice to store an OID - string
 mapping somewhere and just store the OIDs in the main table.  But a
 new security label can appear at any time, and it doesn't work to have
 the transaction that discovers it do the insert into the mapping
 table.  Time zones have the same problem, more or less.  Now, maybe if
 we had non-transactional tables like Alvaro keeps muttering about...

Hrm, don't we only pull in ZIC info on a reload? Or do we actually refer to it 
dynamically all the time? Perhaps we can enforce that we'll only recognize new 
TZ info as part of a config reload?

Josh Berkus also made a good point that this does introduce the risk that you 
could end up moving data to a different server, that has a different ZIC 
database (perhaps via replication); at which point the fit could hit the shan 
(or the excrement could impact the cooling device...). So perhaps the only 
reasonable way to handle this is to actually load ZIC data into the database 
itself.
--
Jim C. Nasby, Database Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net



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


Re: [HACKERS] libedit memory stomp is apparently fixed in OS X Lion

2011-07-25 Thread Jim Nasby
On Jul 22, 2011, at 11:01 AM, Peter Geoghegan wrote:
 On 22 July 2011 03:24, Tom Lane t...@sss.pgh.pa.us wrote:
 I had a bug filed with Apple about that, and today I got some auto-mail
 indicating they'd fixed that bug as of OS X 10.7 (Lion).  I don't have
 Lion installed here, but I grabbed the libedit sources from
 www.opensource.apple.com and indeed it looks fixed.  So, if any early
 adopters want to try it out ...
 
 I'll add that I've heard reports that Lion Server comes with
 PostgreSQL as standard, and Lion Desktop comes with psql.

Interesting... I assume that they're using it for something internal?

IIRC this has actually caused some issues in the past... they had some rather 
old version installed that was being used by an internal tool... if you tried 
to install your own version some rather interesting issues could then crop up.
--
Jim C. Nasby, Database Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net



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


Re: [HACKERS] Access to current database from C-language function

2011-07-25 Thread Florian Pflug
On Jul25, 2011, at 22:31 , Achim Domma wrote:
 Am 25.07.2011 um 14:48 schrieb Florian Pflug:
 A more low-level API is provided by {heap,index}_{beginscan,endscan}, 
 heap_{insert,update,delete} and index_insert. However, correct handling of 
 transactions using this API isn't easy - for example, to update a row you'd 
 first have to find the latest version of that row, then decide if you're 
 allowed to update it, and finally create a new version.
 
 I see the problems with the second approach, but that's definitively what I 
 would like to do.

You're in for a lot of work, then. I still suggest that you explain your 
ultimate goals before you embark on your endeavor - people might be able to 
point our easier ways to achieve those.

 But I'm only interested in reading, which will hopefully make it a bit easier.

Maybe. But note that if you want your code to be correct for all transaction 
isolation level, you have to be quite careful even when only reading. 
Especially from postgres 9.1 forward, due to the new true serializability 
feature of that release.

 Could you guide me to a starting point? Assuming my database has a table T 
 with an index I. How do I get access to that index?

I suggest you start by reading nodeIndexScan.c. This is the code the query 
executor uses to implement index scans. However, before you embark on your 
endeavor.

best regards,
Florian Pflug


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


Re: [HACKERS] Update releases

2011-07-25 Thread David Fetter
On Fri, Jul 01, 2011 at 11:59:55PM +0100, Thom Brown wrote:
 On 1 July 2011 23:57, David Fetter da...@fetter.org wrote:
  Folks,
 
  Now that there's a (very minor) crypto fix and a new DST ruleset, when
  can we get the next set of minor revs out the door?
 
 Do we know how many identified bugs are still outstanding?  There's at
 least the SSPI issue for which a patch has been submitted and requires
 review, commit and back-patching.

I'm thinking whatever such bugs are outstanding can wait until the
next minor rev, which is to say that the CVE involved with that crypto
fix should take precedence.  I know it's minor, but a known-unfixed
access control bug looks very bad, no matter how trivial that bug is.

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

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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


[HACKERS] Error calling PG_RETURN_NULL()

2011-07-25 Thread Alexandre Savaris
Hi! I'm working on an implementation for a new data type (PostgreSQL 
version 9.1 beta 3 on Windows 7 32 bits), according to the following rules:

- 1. NULL values are stored as is;
- 2. character strings (up to 16 bytes) are stored without leading or 
trailing spaces;

- 3. empty character strings are stored as NULL values.

Using the extension support for new data types, the following source 
code in C was written.


cs_type.h
--
#include postgres.h
#include fmgr.h

///
/// Export DLL functions.
///
#if defined(_WIN32)
#define DLLEXPORT __declspec(dllexport)
#else
#define DLLEXPORT
#endif

///
/// PostgreSQL magic block.
///
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

///
/// Function prototypes.
///
DLLEXPORT Datum cs_type_in(PG_FUNCTION_ARGS);
DLLEXPORT Datum cs_type_out(PG_FUNCTION_ARGS);


cs_type.c
--
#include cs_type.h

///
/// Version-1 calling convention.
/// Input function.
///
PG_FUNCTION_INFO_V1(cs_type_in);
DLLEXPORT Datum cs_type_in(PG_FUNCTION_ARGS) {
char *cp1; // Trimming routine - for parsing the whole string.
char *cp2; // Trimming routine - for shifting  padding.
VarChar *v; // Return value.

char *c1 = PG_GETARG_CSTRING(0);

char *c2 = (char *)palloc(strlen(c1) + 1);
strcpy(c2, c1);

///
/// Trimming routine. 
(http://stackoverflow.com/questions/656542/trim-a-string-in-c)

///
// skip leading spaces, shift remaining chars
for(cp1 = c2;isspace(*cp1);cp1++ ) // skip leading spaces, via cp1
;
for(cp2 = c2;*cp1;cp1++,cp2++) // shift left remaining chars, via cp2
*cp2 = *cp1;
*cp2-- = 0; // mark new end of string for str
// replace trailing spaces with '\0'
while(cp2  c2  isspace(*cp2))
   *cp2-- = 0; // pad with '\0's

if(strlen(c2) == 0) { // Empty string: return NULL.
PG_RETURN_NULL();
}
else if(strlen(c2)  16) { // Value too long: error.
ereport(ERROR, (errcode(ERRCODE_STRING_DATA_LENGTH_MISMATCH), 
errmsg(value too long for type cs_type), errhint(type cs_type 
supports up to 16 bytes)));

}

///
/// Result as varchar.
///
v = (VarChar *)palloc(VARHDRSZ + strlen(c2) + 1);
SET_VARSIZE(v, VARHDRSZ + strlen(c2) + 1);
strcpy(VARDATA(v), c2);
PG_RETURN_VARCHAR_P(v);
}

///
/// Version-1 calling convention.
/// Output function.
///
PG_FUNCTION_INFO_V1(cs_type_out);
DLLEXPORT Datum cs_type_out(PG_FUNCTION_ARGS) {

VarChar *v = PG_GETARG_VARCHAR_P(0);

///
/// Result as cstring.
///
char *c = (char *)palloc(VARSIZE(v) - VARHDRSZ + 1);
strcpy(c, VARDATA(v));
PG_RETURN_CSTRING(c);
}

On the PostgreSQL side, the following objects were created.

CREATE OR REPLACE FUNCTION cs_type_in(cstring)
  RETURNS cs_type AS
'$libdir/cs_type', 'cs_type_in'
  LANGUAGE c STRICT
  COST 1;
ALTER FUNCTION cs_type_in(cstring) OWNER TO postgres;

CREATE OR REPLACE FUNCTION cs_type_out(cs_type)
  RETURNS cstring AS
'$libdir/cs_type', 'cs_type_out'
  LANGUAGE c STRICT
  COST 1;
ALTER FUNCTION cs_type_out(cs_type) OWNER TO postgres;

CREATE TYPE cs_type (
  INPUT = cs_type_in(cstring),
  OUTPUT = cs_type_out(cs_type),
  LIKE = varchar
);
ALTER TYPE cs_type OWNER TO postgres;

CREATE TABLE test_cs_type
(
  cs_value cs_type
)
WITH (
  OIDS=FALSE
);
ALTER TABLE test_cs_type OWNER TO postgres;

When called directly, the function cs_type_in(cstring) works as 
expected, attending the three rules described above. For example:


SELECT cs_type_in('TEST'); -- returns 'TEST'
SELECT cs_type_in(NULL); -- returns NULL
SELECT cs_type_in(''); -- returns NULL
SELECT cs_type_in('   '); -- returns NULL

However, on INSERT clauses, only the rules 1 and 2 work; an attempt to 
insert an empty string (or a string with white spaces) generates an 
error. For example:


INSERT INTO test_cs_type VALUES (NULL); -- works fine
INSERT INTO test_cs_type VALUES ('TEST'); -- works fine
INSERT INTO test_cs_type VALUES (''); -- error!
INSERT INTO test_cs_type VALUES ('   '); -- error!

The error message displayed is:

ERRO:  input function 49344 returned NULL
LINE 1: INSERT INTO dicom_data.test_cs_type VALUES ('   ');
^

** Error **

ERRO: input function 49344 returned NULL
SQL state: XX000
Character: 45

It seems like the call to PG_RETURN_NULL() on the input function is 
causing the error. Is this the correct behaviour? There's another way to 
return a NULL value as the result of a data type's input function?


Best regards,
Alexandre

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


Re: [HACKERS] pgbench --unlogged-tables

2011-07-25 Thread David Fetter
On Fri, Jul 22, 2011 at 10:15:08PM -0400, Greg Smith wrote:
 On 07/22/2011 08:15 PM, David Fetter wrote:
 Do you have any theories as to how indexing on SSD speeds things
 up?  IIRC you found only marginal benefit in putting WALs there.
 Are there cases that SSD helps more than others when it comes to
 indexing?
 
 Yes, I've found a variety of workloads where using a SSD turns out
 to be slower than the old-school array of drives with a
 battery-backed write cache.  Tiny commits are slower, sequential
 writes can easily be slower, and if there isn't a random I/O
 component to the job the SSD won't get any way to make up for that.

So you're saying this is more of a flash thing than an SSD thing?  I
haven't heard of systems with PCM having this limitation.

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

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

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


Re: [HACKERS] storing TZ along timestamps

2011-07-25 Thread Robert Haas
On Mon, Jul 25, 2011 at 6:26 PM, Jim Nasby j...@nasby.net wrote:
 Hrm, don't we only pull in ZIC info on a reload? Or do we actually refer to 
 it dynamically all the time? Perhaps we can enforce that we'll only recognize 
 new TZ info as part of a config reload?

Hmm.  That might work in theory, but I don't see any good way to
update every database's tz table on each reload.

-- 
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] vacuumlo patch

2011-07-25 Thread Robert Haas
On Mon, Jul 25, 2011 at 9:37 AM, Tim elatl...@gmail.com wrote:
 Updated the patch to also apply when the no-action flag is enabled.

You may want to read this:

http://wiki.postgresql.org/wiki/Submitting_a_Patch

And add your patch here:

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

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

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


Re: [HACKERS] Another issue with invalid XML values

2011-07-25 Thread Noah Misch
On Mon, Jul 25, 2011 at 09:06:41PM +0200, Florian Pflug wrote:
 On Jul25, 2011, at 20:37 , Bernd Helmle wrote:
  Ah, but i got now what's wrong here: configure is confusing both libxml2
  installations, and a quick look into config.log proves that: it uses the
  xml2-config from the OSX libs (my $PATH has /usr in front of the bindir of
  MacPorts, though i seem to recall to have changed this in the past).

 Hm, but I still think there's a bug lurking there. Using a different libxml2
 version for the configure checks than for actual builds surely isn't good...
 
 From looking at configure.in, it seems that we use xml2-config to figure out
 the CFLAGS and LDFLAGS required to build and link against libxml. I guess we
 somehow end up not using these flags when we later test for
 xmlStructuredErrorContext, but do use them during the actual build. Or maybe
 the order of the -I and -L flags just ends up being different in the two 
 cases.

I can reproduce similar behavior on GNU/Linux.  If my setup was sufficiently
similar, Bernd's problematic build would have used this sequence of directives
during both configuration and build:

  -I/usr/include/libxml2  -I/opt/local/include   -L/opt/local/lib

The directories passed using --with-includes and --with-libraries took
priority over those from xml2-config.  Since libxml2 headers live in a
`libxml2' subdirectory, --with-includes=/opt/local/include did not affect
finding them.  --with-libraries=/opt/local/lib *did* affect finding the
library binaries, though.  Therefore, he built entirely against /usr headers
and /opt/local libraries.  We could rearrange things so the xml2-config -L
flags (or lack thereof) take priority over a --with-libraries directory for
the purpose of finding libxml2.

As a side note, we don't add an rpath for libxml2 like we do for Perl and
Python.  That doesn't matter on Darwin, but with GNU libc, it entails setting
LD_LIBRARY_PATH or updating /etc/ld.so.conf to make the run time linker find
the library binary used at build time.

-- 
Noah Mischhttp://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] pgbench--new transaction type

2011-07-25 Thread Jeff Janes
On Sun, Jun 19, 2011 at 9:34 PM, Itagaki Takahiro
itagaki.takah...@gmail.com wrote:
 On Mon, Jun 20, 2011 at 07:30, Greg Smith g...@2ndquadrant.com wrote:
 I applied Jeff's patch but changed this to address concerns about the
 program getting stuck running for too long in the function:

 #define plpgsql_loops   512

 Is it OK to define the value as constant?

I think so.  I think anyone in a position to want to change it would
not be adverse to recompiling.

I consider it analogous to nbranches, ntellers, and naccounts, which
are also constants.


 Also, it executes much more queries if -t option (transactions) specified;
 Of course it runs the specified number of transactions, but actually
 runs plpgsql_loops times than other modes.

Am I being overly punctilious in maintaining the distinction between a
transaction proper, and a select?

In a similar vane, the reporting where I have both a tps and a select
per second, seems a bit messy, but I wanted to be overly-explicit, at
least until someone recommended a less confusing alternative.

 I think this is a really nice new workload to demonstrate.  One of the
 things we tell people is that code works much faster when moved server-side,

 What is the most important part of the changes? The proposal includes
 3 improvements. It might defocus the most variable tuning point.

  #1 Execute multiple queries in one transaction.
  #2 Run multiple queries in the server with stored procedure.
  #3 Return only one value instead of 512.

#2 is the most important change.  The other changes are just along
for the ride as a side effect of #2.

I think #1 issue is probably minor in single-client cases, although it
can avoid major contention in multi client cases (although recent work
by Robert Haas may alleviate much of that).
Since transactions cannot be started and ended inside server-side
code, I am not able to isolate and remove #1 from the rest of my
changes.  One can take the other approach, however, by running queries
the normal way except all in one transaction, as a comparison.  The
-1 option of the attached toy patch does that (applies to head,
minor conflict at getopt if applied over the main patch of this
thread).  Numbers for various combination in single client
(unfortunately, run on slightly slower CPU than my previous example):

 9,164.85   -S
10,144.71   -S -1
13,980.64   -S -M prepared
16,004.97   -S -M prepared -1
39,600.67   -P


I had never even considered #3--it is just an accident of how I wrote
the code.  I only returned anything at all because a) in early code I
wanted to see the sum, just as a sanity check that the returned value
seemed reasonable, to indicate it was doing what I thought it was
doing, and b) I was worried some optimizer might decide to avoid
executing the selects altogether, if it detected the results of them
were never used.  Should I find a way to return 512 values from a
single function call, either as part of the submitted code, or just as
a side test to show if it makes any difference?


 Anyway, I'm not sure we need to include the query mode into the pgbench's
 codes. Instead, how about providing a sample script as a separate sql
 file? pgbench can execute any script files with -f option.

In the absence of -s and presence of -f, :scale gets set to 1, rather
than to select count(*) from pgbench_branches.

I don't think it is nice to rely on people to correctly specify -s.  I
would like to change -f so that in the absence of -s it uses the same
scale as -S, etc., do.  But that would probably be too backwards
incompatible to be an acceptable change.

The other thing would be doing initialization, like creating the
function in this case.  Perhaps this could be solved by adding a new
line prefix category to the -f language.  Now \ indicates a
metacommand to be done by pgbench itself.  Maybe ! could indicate a
real SQL command, but one that would be submitted only upon reading
the -f file, and not once per execution.  This one might be backwards
compatible, as I don't see why anyone would have historical sql files
sitting around that have lines starting with !.

Cheers,

Jeff
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index bb18c89..0002498 100644
*** a/contrib/pgbench/pgbench.c
--- b/contrib/pgbench/pgbench.c
*** char	   *pgoptions = NULL;
*** 142,147 
--- 142,148 
  char	   *pgtty = NULL;
  char	   *login = NULL;
  char	   *dbName;
+ int			transact = 0;		/* run in single transaction */
  
  volatile bool timer_exceeded = false;	/* flag from signal handler */
  
*** usage(const char *progname)
*** 349,354 
--- 350,356 
  		 -r   report average latency per command\n
  		 -s NUM   report this scale factor in output\n
  		 -S   perform SELECT-only transactions\n
+ 		 -1   Perform in single transaction (only makes sense for -S, probably)
  	   -t NUM   number of transactions each client runs (default: 

Re: [HACKERS] Problem with pg_upgrade's directory write check on Windows

2011-07-25 Thread Bruce Momjian
Robert Haas wrote:
 On Sun, Jul 24, 2011 at 5:27 PM, Bruce Momjian br...@momjian.us wrote:
  Alvaro Herrera wrote:
  Excerpts from Bruce Momjian's message of dom jul 24 01:46:08 -0400 2011:
   Robert Haas wrote:
 
 Should I fix this in pg_upgrade 9.1 for Windows or just in 9.2? ?The
 check works fine on non-Windows.
   
Seems worth back-patching to me.
  
   Attached patch applied and backpatched to 9.1. ?I was able to test both
   code paths on my BSD machine by modifying the ifdefs. ?I will have
   EnterpriseDB do further testing.
 
  Err, why not 9.0?
 
  The check did not exist in 9.0 -- I mentioned that in the commit
  message. ?I could add the check into 9.0, but we usually don't backpatch
  such things.
 
 What do you mean by such things?

The check to see if the directory is writable was only in 9.1+ --- this
is a fix for that check.  The check was not backpatched because we don't
ordinarly backpatch sanity checks for uncommon problems.  We certainly
can't backpatch just the fix.

-- 
  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] pgbench--new transaction type

2011-07-25 Thread Greg Smith

On 07/25/2011 08:12 PM, Jeff Janes wrote:

In the absence of -s and presence of -f, :scale gets set to 1, rather
than to select count(*) from pgbench_branches.

I don't think it is nice to rely on people to correctly specify -s.  I
would like to change -f so that in the absence of -s it uses the same
scale as -S, etc., do.  But that would probably be too backwards
incompatible to be an acceptable change.
   


Auto-detecting scale only works if you have a database populated with 
the pgbench tables.  You can use pgbench -f to run arbitrary bits of 
SQL, using pgbench as the driver program for all sorts of benchmarking 
tasks against other data sets.  For example, at 
http://projects.2ndquadrant.it/sites/default/files/pgbench-intro.pdf I 
show how to use it for testing how fast INSERT statements of various 
sizes can execute.


The very concept of a scale may not make sense for other data sets 
that pgbench will happily run against when using -f.  The only sort of 
heuristic I have considered adding here when running in that mode is:


1) Check if pgbench_branches exists.
2) If so, count the records to derive a scale, as currently done in the 
non -f cases

3) Should that scale not match the value of -s, issue a warning.

You have to assume anyone sophisticated enough to be playing with -f 
may be doing something the program doesn't expect or understand, and let 
them do that without trying to fix what may be intentional behavior.  
But a check for the most common mistake made in this area wouldn't 
bother people who aren't using pgbench in its original form at all, 
while it would help those new to the program from screwing this up.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us


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


Re: [HACKERS] write scalability

2011-07-25 Thread Robert Haas
On Mon, Jul 25, 2011 at 4:07 PM, Robert Haas robertmh...@gmail.com wrote:
 As to what that something might be, I reran this last test with
 LWLOCK_STATS enabled and here are the top things that are blocking:

 lwlock 310: shacq 96846 exacq 108433 blk 16275
 lwlock 3: shacq 64 exacq 2628381 blk 36027
 lwlock 7: shacq 0 exacq 2628615 blk 85220
 lwlock 11: shacq 84913908 exacq 4539551 blk 2119423
 lwlock 4: shacq 28667307 exacq 2628524 blk 3356651

 During this 5-minute test run, an LWLock acquisition blocked 6180335
 times.  As you can see from the above results, ProcArrayLock accounts
 for 54% of that blocking, and CLogControlLock accounts for another
 34%.  lwlock 7 is WALInsertLock, which manages to account for more
 than 1% of the blocking despite the fact that WAL has been largely
 eliminated in this test...

I reran this test on master with permanent (not unlogged) tables, and
got the following stats for blocking:

lwlock 11: shacq 58376164 exacq 2122076 blk 1130974
lwlock 4: shacq 15168924 exacq 1367303 blk 1555799
lwlock 7: shacq 0 exacq 8440615 blk 1726896
grand total: shacq 144563929 exacq 41618873 blk 4886107

So that's 35% WALInsertLock, 32% ProcArrayLock, and 23%
CLogControlLock.  No other single lock accounted for more than 1% of
the blocking.  It's a good guess we're going to have to fix more than
one thing to really make this zippy.

Also note that CLogControlLock accounts for better than 40% of the
shared-lock acquisitions across all LWLocks.  The next-most-frequently
share-locked LWLock is ProcArrayLock, with 10% of the total
shared-lock acquisitions, followed by SInvalReadLock, at 9%.

-- 
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] write scalability

2011-07-25 Thread Greg Smith

On 07/25/2011 04:07 PM, Robert Haas wrote:

I did 5-minute pgbench runs with unlogged tables and with permanent
tables, restarting the database server and reinitializing the tables
between each run.


Database scale?  One or multiple pgbench worker threads?  A reminder on 
the amount of RAM in the server would be helpful for interpreting the 
results too.


The other thing I'd recommend if you're running more write-heavy tests 
is to turn off autovacuum.  Whether or not it kicks in depends on the 
test duration and the TPS rate, which adds a source of variability 
better avoided here. It also means that faster tests end up getting 
penalized by having it run near their end, which makes them no longer 
look like fast results.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us


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


Re: [HACKERS] psql: bogus descriptions displayed by \d+

2011-07-25 Thread Josh Kupershmidt
On Fri, Jul 22, 2011 at 12:44 PM, Robert Haas robertmh...@gmail.com wrote:
 On Thu, Jul 21, 2011 at 9:17 PM, Josh Kupershmidt schmi...@gmail.com wrote:
 Here's a small patch against branch 8.4 to mention support for COMMENT
 ON index_name.column_name.

 I am not in favor of this - because we'd also need to mention every
 other relkind that can support comments.  I think if we want to do
 something here we should change it to say relation_name, and then
 clarify what that means further down.  Similarly with the patch for
 master.

 Also, if we're going to make a change here, we probably should make
 sure it matches the actual behavior.  In master, that's to allow
 comments on columns of tables, views, composite types, and foreign
 tables.

That seems like a good way to document this; patch for master updated.
I avoided mucking with the documentation for COMMENT ON RULE and
COMMENT ON TRIGGER this time; they both say table when they really
mean table or view, but maybe trying to differentiate between
table, table_or_view, and relation will make things overly
complicated.

 Also, a patch against master to:
  * get rid of the bogus Description outputs for \d+ sequence_name
 and \d+ index_name

 This part looks OK, but instead of doing a negative test (not-index,
 not-sequence) let's have it do a positive test, for the same types
 comment.c allows.

Right, fixed.

 And while I'm messing with this, some further nitpicks about psql not
 addressed by these patches:
  * The Storage column for \d+ sequence_name is correct, I suppose,
 but repetitive

 I'm OK with removing that.

Hrm, would it be better to keep that  Storage bit around in some
non-repetitive form, maybe on its own line below the table output?

  * The Type column for \dv+ view_name, \di+ index_name, \ds+
 sequence_name , etc. seems borderline useless.. shouldn't you know
 what type you're looking at based on the backslash command you're
 using?

 Not really.  You can do something like this, for example:

 \dti+

 ...to show both indexes and tables.

I see. Didn't know about that trick.

Josh
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index ab12614..736907e 100644
*** a/doc/src/sgml/ref/comment.sgml
--- b/doc/src/sgml/ref/comment.sgml
*** COMMENT ON
*** 26,32 
AGGREGATE replaceable class=PARAMETERagg_name/replaceable (replaceable class=PARAMETERagg_type/replaceable [, ...] ) |
CAST (replaceablesource_type/replaceable AS replaceabletarget_type/replaceable) |
COLLATION replaceable class=PARAMETERobject_name/replaceable |
!   COLUMN replaceable class=PARAMETERtable_name/replaceable.replaceable class=PARAMETERcolumn_name/replaceable |
CONSTRAINT replaceable class=PARAMETERconstraint_name/replaceable ON replaceable class=PARAMETERtable_name/replaceable |
CONVERSION replaceable class=PARAMETERobject_name/replaceable |
DATABASE replaceable class=PARAMETERobject_name/replaceable |
--- 26,32 
AGGREGATE replaceable class=PARAMETERagg_name/replaceable (replaceable class=PARAMETERagg_type/replaceable [, ...] ) |
CAST (replaceablesource_type/replaceable AS replaceabletarget_type/replaceable) |
COLLATION replaceable class=PARAMETERobject_name/replaceable |
!   COLUMN replaceable class=PARAMETERrelation_name/replaceable.replaceable class=PARAMETERcolumn_name/replaceable |
CONSTRAINT replaceable class=PARAMETERconstraint_name/replaceable ON replaceable class=PARAMETERtable_name/replaceable |
CONVERSION replaceable class=PARAMETERobject_name/replaceable |
DATABASE replaceable class=PARAMETERobject_name/replaceable |
*** COMMENT ON
*** 97,105 
  
variablelist
 varlistentry
- termreplaceable class=parameterobject_name/replaceable/term
- termreplaceable class=parametertable_name.column_name/replaceable/term
  termreplaceable class=parameteragg_name/replaceable/term
  termreplaceable class=parameterconstraint_name/replaceable/term
  termreplaceable class=parameterfunction_name/replaceable/term
  termreplaceable class=parameteroperator_name/replaceable/term
--- 97,104 
  
variablelist
 varlistentry
  termreplaceable class=parameteragg_name/replaceable/term
+ termreplaceable class=parameterobject_name/replaceable/term
  termreplaceable class=parameterconstraint_name/replaceable/term
  termreplaceable class=parameterfunction_name/replaceable/term
  termreplaceable class=parameteroperator_name/replaceable/term
*** COMMENT ON
*** 143,148 
--- 142,158 
/para
   /listitem
  /varlistentry
+ 
+ varlistentry
+  termreplaceablerelation_name.column_name/replaceable/term
+  listitem
+   para
+For comments on columns, the name of the relation and column. Column
+comments may be used with tables, views, composite types, and
+foreign tables.
+   /para
+  /listitem
+ /varlistentry
  
 varlistentry
  termreplaceable 

[HACKERS] Check constraints on partition parents only?

2011-07-25 Thread Jerry Sievers
Hackers;

I just noticed that somewhere between 8.2 and 8.4, an exception is
raised trying to alter table ONLY some_partition_parent ADD CHECK
(foo).

I can understand why it makes sense to handle this as an error.

Howeverin practice on a few systems that I used to manage this would
be a problem.

1. I got into the habit of putting CHECK (false) on the parent table
   if it was an always empty base table,

   This is just really documentation indicating that this table can't
   hold rows and of course, having the partition selector trigger
   raise exception if falling through the if/else logic on a new row
   insertion enforces the constraint but is less obvious.

   Ok, so no real problem here.  Just one example.

2. Atypical partitioning implementation where the parent table was for
   initial insert/update of live records in an OLTP system with high
   update/insert ratio.  This table was partitioned retroactively in
   such a way transparent to the application.  The app would
   eventually update a row one final time and set a status field to
   some terminal status, at which time we'd fire a trigger to move the
   row down into a partition.  Record expiry took place periodically
   by dropping a partition and creating a new one.

   In that case, imagine the application user runs with
   sql_inheritance to off and so, sees only the live data which
   resulted in a huge performance boost.  Reporting apps and in fact
   all other users ran with sql_inheritance to on as usual and so, see
   all the data.

   Suppose the status field had several non-terminal values and one or
   a few terminal values.  The differing check constraints on parent
   and child tables made it easy to see the intent and I presume with
   constraint_exclusion set to on, let queries on behalf of regular
   users that had specified a non-terminal state visit only the tiny
   parent table.
   
   Parent might have CHECK (status in (1,2,3)) and children CHECK
   (status = 4).

   I'll assume not many sites are architected this way but #2 here
   shows a more compelling example of why it might be useful to allow
   check constraints added to only a partition parent.

   Comments?

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net
p: 305.321.1144

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


Re: [HACKERS] Check constraints on partition parents only?

2011-07-25 Thread Andrew Dunstan



On 07/25/2011 10:31 PM, Jerry Sievers wrote:

Hackers;

I just noticed that somewhere between 8.2 and 8.4, an exception is
raised trying to alter table ONLY some_partition_parent ADD CHECK
(foo).

I can understand why it makes sense to handle this as an error.

Howeverin practice on a few systems that I used to manage this would
be a problem.

1. I got into the habit of putting CHECK (false) on the parent table
if it was an always empty base table,

This is just really documentation indicating that this table can't
hold rows and of course, having the partition selector trigger
raise exception if falling through the if/else logic on a new row
insertion enforces the constraint but is less obvious.

Ok, so no real problem here.  Just one example.

2. Atypical partitioning implementation where the parent table was for
initial insert/update of live records in an OLTP system with high
update/insert ratio.  This table was partitioned retroactively in
such a way transparent to the application.  The app would
eventually update a row one final time and set a status field to
some terminal status, at which time we'd fire a trigger to move the
row down into a partition.  Record expiry took place periodically
by dropping a partition and creating a new one.

In that case, imagine the application user runs with
sql_inheritance to off and so, sees only the live data which
resulted in a huge performance boost.  Reporting apps and in fact
all other users ran with sql_inheritance to on as usual and so, see
all the data.

Suppose the status field had several non-terminal values and one or
a few terminal values.  The differing check constraints on parent
and child tables made it easy to see the intent and I presume with
constraint_exclusion set to on, let queries on behalf of regular
users that had specified a non-terminal state visit only the tiny
parent table.

Parent might have CHECK (status in (1,2,3)) and children CHECK
(status = 4).

I'll assume not many sites are architected this way but #2 here
shows a more compelling example of why it might be useful to allow
check constraints added to only a partition parent.



8.4 had this change:

   *

 Force child tables to inherit CHECK constraints from parents
 (Alex Hunsaker, Nikhil Sontakke, Tom)

 Formerly it was possible to drop such a constraint from a
 child table, allowing rows that violate the constraint to be
 visible when scanning the parent table. This was deemed
 inconsistent, as well as contrary to SQL standard.


You're not the only one who occasionally bangs his head against it.

cheers

andrew





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


Re: [HACKERS] Error calling PG_RETURN_NULL()

2011-07-25 Thread Tom Lane
Alexandre Savaris alexandre.sava...@gmail.com writes:
 Hi! I'm working on an implementation for a new data type (PostgreSQL 
 version 9.1 beta 3 on Windows 7 32 bits), according to the following rules:
 - 1. NULL values are stored as is;
 - 2. character strings (up to 16 bytes) are stored without leading or 
 trailing spaces;
 - 3. empty character strings are stored as NULL values.

*buzzz* wrong answer, thanks for playing.

Data types do not get to editorialize on null versus not null.  Forget
your cute idea #3, and you'll be much happier.

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] Check constraints on partition parents only?

2011-07-25 Thread Alvaro Herrera
Excerpts from Andrew Dunstan's message of lun jul 25 22:44:32 -0400 2011:
 
 On 07/25/2011 10:31 PM, Jerry Sievers wrote:
  Hackers;
 
  I just noticed that somewhere between 8.2 and 8.4, an exception is
  raised trying to alter table ONLY some_partition_parent ADD CHECK
  (foo).

 8.4 had this change:
 
 *
   Force child tables to inherit CHECK constraints from parents
   (Alex Hunsaker, Nikhil Sontakke, Tom)

 You're not the only one who occasionally bangs his head against it.

Yeah.  I think it's good that there's a barrier to blindly dropping a
constraint that may be important to have on children, but there should
be a way to override that.

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