Re: [HACKERS] Another swing at JSON
--On 16. Juni 2011 17:38:07 -0400 Tom Lane t...@sss.pgh.pa.us wrote: After reading Joseph's comment upthread, I don't see any consensus wether the existing pre-9.1 support is required or even desired. Maybe i missed it, but do we really expect an extension (or contrib module) to be backwards compatible to earlier major releases, when shipped in contrib/ ? No, we don't. You won't find any attempt in any contrib module to build against prior releases. There's not much point, since they're shipped with a specific release of the core. Okay, then we should remove this code. It doesn't do any complicated, but it seems a waste of code in this case (and from a maintenance point of view). Joseph, are you able to remove the compatibility code for this CF? -- 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] Parameterized aggregate subquery (was: Pull up aggregate subquery)
2011/6/10 Hitoshi Harada umi.tan...@gmail.com: 2011/6/9 Robert Haas robertmh...@gmail.com: On Thu, Jun 9, 2011 at 2:28 AM, Hitoshi Harada umi.tan...@gmail.com wrote: BTW, as I changed title and design from the previous post, should I throw away the old commit fest entry and make the new one? Nah, just edit the existing entry and change the title. Also add a link to the new patch, of course. Ok, done. While reviewing the gist/box patch, I found some planner APIs that can replace parts in my patch. Also, comments in includes wasn't updated appropriately. Revised patch attached. Regards, -- Hitoshi Harada aggjoin-20110617.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] deadlock_timeout at PGC_SIGHUP?
(2011/06/12 6:43), Noah Misch wrote:
On Wed, Mar 30, 2011 at 04:48:26PM -0400, Robert Haas wrote:
Me neither. If making the deadlock timeout PGC_SUSET is independently
useful, I don't object to doing that first, and then we can wait and
see if anyone feels motivated to do more.
Here's the patch for that. Not much to it.
I've reviewed the patch following the article in the PostgreSQL wiki.
It seems fine except that it needs to be rebased, so I'll mark this
Ready for committers'. Please see below for details of my review.
Submission review
=
The patch is in context diff format, and can be applied with shifting
a hunk. I attached rebased patch.
The patch fixes the document about deadlock_timeout. Changing GUC
setting restriction would not need test.
Usability review
The purpose of the patch is to allow only superusers to change
deadlock_timeout GUC parameter. That seems to fit the conclusion of the
thread:
http://archives.postgresql.org/pgsql-hackers/2011-03/msg01727.php
Feature test
After applying the patch, non-superuser's attempt to change
deadlock_timeout is rejected with proper error:
ERROR: permission denied to set parameter deadlock_timeout
But superusers still can do that.
The fix for the document is fine, and it follows the wording used for
similar cases.
This patch doesn't need any support of external tools such as pg_dump
and psql.
Performance review
==
This patch would not cause any performance issue.
Coding review
=
The patch follows coding guidelines, and seems to have no portability
issue. It includes proper comment which describes why the parameter
should not be changed by non-superuser.
The patch produces no compiler warning for both binaries and documents.
Architecture review
===
AFAICS, this patch adopts the GUC parameter's standards.
Regards,
--
Shigeru Hanada
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index e835e4b..7329281 100644
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
*** dynamic_library_path = 'C:\tools\postgre
*** 5266,5272
practice. On a heavily loaded server you might want to raise it.
Ideally the setting should exceed your typical transaction time,
so as to improve the odds that a lock will be released before
! the waiter decides to check for deadlock.
/para
para
--- 5266,5273
practice. On a heavily loaded server you might want to raise it.
Ideally the setting should exceed your typical transaction time,
so as to improve the odds that a lock will be released before
! the waiter decides to check for deadlock. Only superusers can change
! this setting.
/para
para
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 92391ed..48ffe95 100644
*** a/src/backend/utils/misc/guc.c
--- b/src/backend/utils/misc/guc.c
*** static struct config_int ConfigureNamesI
*** 1532,1539
},
{
! /* This is PGC_SIGHUP so all backends have the same value. */
! {deadlock_timeout, PGC_SIGHUP, LOCK_MANAGEMENT,
gettext_noop(Sets the time to wait on a lock before
checking for deadlock.),
NULL,
GUC_UNIT_MS
--- 1532,1539
},
{
! /* This is PGC_SUSET to prevent hiding from log_lock_waits. */
! {deadlock_timeout, PGC_SUSET, LOCK_MANAGEMENT,
gettext_noop(Sets the time to wait on a lock before
checking for deadlock.),
NULL,
GUC_UNIT_MS
--
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] ALTER TABLE lock strength reduction patch is unsafe
On Thu, Jun 16, 2011 at 11:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: 2. In response, some other backend starts to reload its relcache entry for pgbench_accounts when it begins its next command. It does an indexscan with SnapshotNow on pg_class to find the updated pg_class row. 3. Meanwhile, some third backend commits another ALTER TABLE, updating the pg_class row another time. Since we have removed the AccessExclusiveLock that all variants of ALTER TABLE used to take, this commit can happen while backend #2 is in process of scanning pg_class. This part is the core of the problem: We must not be able to update the catalog entry while a relcache rebuild scan is in place. So I'm prototyping something that allows LockRelationDefinitionOid(targetRelId, ShareLock); -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Boolean operators without commutators vs. ALL/ANY
On Jun17, 2011, at 03:42 , Alvaro Herrera wrote: To make matters worse, our delimiters for regexes are the same as for strings, the single quote. So you get foo =~ 'bar' /* foo is the text column, bar is the regex */ 'bar' =~ foo /* no complaint but it's wrong */ 'bar' ~= foo /* okay */ 'foo' ~= bar /* no complaint but it's wrong */ How do I tell which is the regex here? If we used, say, /, that would be a different matter: How is this different from the situation today where the operator is just ~? 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] ALTER TABLE lock strength reduction patch is unsafe
On Fri, Jun 17, 2011 at 9:32 AM, simon si...@2ndquadrant.com wrote: On Thu, Jun 16, 2011 at 11:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: 2. In response, some other backend starts to reload its relcache entry for pgbench_accounts when it begins its next command. It does an indexscan with SnapshotNow on pg_class to find the updated pg_class row. 3. Meanwhile, some third backend commits another ALTER TABLE, updating the pg_class row another time. Since we have removed the AccessExclusiveLock that all variants of ALTER TABLE used to take, this commit can happen while backend #2 is in process of scanning pg_class. This part is the core of the problem: We must not be able to update the catalog entry while a relcache rebuild scan is in place. So I'm prototyping something that allows LockRelationDefinitionOid(targetRelId, ShareLock); Similar to the way we lock a relation for extension, as a sub-lock of the main relation lock. Relcache rebuilds use a ShareLock, ALTER TABLE uses an ExclusiveLock. I've written the patch, just need to test later today gotta step out now. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Nested CASE-WHEN scoping
On 16.06.2011 23:56, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: The complicated part is to ensure that levelsup is always set correctly. At parse time, levelsup is always set to 0, as the syntax doesn't allow referencing upper levels directly. When an SQL function is inlined, any ExpressionParams in the expressions that are substituted for Params need to have their levelsup adjusted, so that it still refers to the right value if there's CASE expressions in the inlined function. Also, when an ExpressionParam is replaced with a Const, the levelsup fields of any other ExpressionParams within the CaseExpr referring to higher levels need to have their levelsup decremented to account for the fact that the CaseExpr doesn't push the expression parameter anymore. I believe this is an unworkably complex, and almost certainly buggy Rube Goldberg device. Even if it manages to work today, it's going to be impossible to maintain those levelsup values correctly during any sort of expression rearrangement or optimization. Please take another look at just assigning a PARAM_EXEC parameter per Case expression. I've added this to the TODO list, hopefully someone more skilled with the planner than me will pick this up... -- 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] Re: patch review : Add ability to constrain backend temporary file space
2011/6/17 Mark Kirkwood mark.kirkw...@catalyst.net.nz:
On 17/06/11 13:08, Mark Kirkwood wrote:
On 17/06/11 09:49, Cédric Villemain wrote:
I have issues applying it.
Please can you remove trailing space?
Also, you can generate a cool patch like this :
get git-external-diff from postgres/src/tools to /usr/lib/git-core/
chmod +x it
export GIT_EXTERNAL_DIFF=git-external-diff
git format-patch --ext-diff origin
I think I have the trailing spaces removed, and patch is updated for the
variable renaming recently done in fd.c
I have no idea why I can't get the git apply to work (obviously I have
exceeded by git foo by quite a ways), but it should apply for you I hope (as
it patches fine).
If I didn't made mistake the attached patch does not have trailling
space anymore and I did a minor cosmetic in FileClose. It is not in
the expected format required by postgresql commiters but can be
applyed with git apply...
It looks like the issue is that patch generated with the git-ext-diff
can not be git applyed (they need to use patch).
Either I did something wrong or git-ext-diff format is not so great.
I didn't test and all yet. From reading, the patch looks sane. I'll
review it later this day or this week-end.
--
Cédric Villemain 2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index e835e4b..80d7c35 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -1025,6 +1025,43 @@ SET ENABLE_SEQSCAN TO OFF;
/variablelist
/sect2
+ sect2 id=runtime-config-resource-disk
+ titleDisk/title
+ variablelist
+
+ varlistentry id=guc-temp-file-limit xreflabel=temp_file_limit
+ termvarnametemp_file_limit/varname (typeinteger/type)/term
+ indexterm
+ primaryvarnametemp_file_limit/ configuration parameter/primary
+ /indexterm
+ listitem
+ para
+Specifies the amount of disk space used by internal sort operations
+and hash tables whist writing to temporary disk files. The value
+defaults to unlimited (literal-1/). Values larger than zero
+constraint the temporary file space usage to be that number of
+kilobytes.
+ /para
+ para
+A given sort or hash operation may write a number of temporary files,
+the total space used by all the files produced by one backend is
+constrained to be this value or less. If further bytes are written
+the current query is canceled. Only superusers can change this
+setting.
+ /para
+ para
+It should be noted that this parameter does emphasisnot/emphasis
+constrain disk space used for temporary table storage. However if
+the temporary table is created from a query then the any sort
+and hash files used in query execution will have their space
+controlled as above.
+ /para
+ /listitem
+ /varlistentry
+
+ /variablelist
+ /sect2
+
sect2 id=runtime-config-resource-kernel
titleKernel Resource Usage/title
variablelist
diff --git a/src/backend/storage/file/fd.c b/src/backend/storage/file/fd.c
index 820e6db..5c00889 100644
--- a/src/backend/storage/file/fd.c
+++ b/src/backend/storage/file/fd.c
@@ -131,6 +131,11 @@ static int max_safe_fds = 32; /* default if not changed */
/* Flag to tell whether there are files to close/delete at end of transaction */
static bool have_pending_fd_cleanup = false;
+/*
+ * Track the total size of all temporary files
+ */
+static double temporary_files_size = 0.0;
+
typedef struct vfd
{
int fd;/* current FD, or VFD_CLOSED if none */
@@ -140,6 +145,7 @@ typedef struct vfd
File lruMoreRecently; /* doubly linked recency-of-use list */
File lruLessRecently;
off_t seekPos; /* current logical file position */
+ off_t fileSize; /* current size of file */
char *fileName; /* name of file, or NULL for unused VFD */
/* NB: fileName is malloc'd, and must be free'd when closing the VFD */
int fileFlags; /* open(2) flags for (re)opening the file */
@@ -887,6 +893,7 @@ PathNameOpenFile(FileName fileName, int fileFlags, int fileMode)
vfdP-fileFlags = fileFlags ~(O_CREAT | O_TRUNC | O_EXCL);
vfdP-fileMode = fileMode;
vfdP-seekPos = 0;
+ vfdP-fileSize = 0;
vfdP-fdstate = 0x0;
vfdP-resowner = NULL;
@@ -1123,6 +1130,13 @@ FileClose(File file)
if (unlink(vfdP-fileName))
elog(LOG, could not unlink file \%s\: %m, vfdP-fileName);
}
+
+ if (temp_file_limit = 0)
+ {
+ /* subtract the unlinked file size from the total */
+ temporary_files_size -= (double)vfdP-fileSize;
+ vfdP-fileSize = 0;
+ }
}
/* Unregister it from the resource owner */
@@ -1251,7 +1265,27 @@ retry:
errno = ENOSPC;
if (returnCode = 0)
+ {
VfdCache[file].seekPos += returnCode;
+
+ if (temp_file_limit = 0 VfdCache[file].fdstate FD_TEMPORARY)
+ {
+ /*
+ * if we
[HACKERS] XPATH evaluation
Hello,
During review of
https://commitfest.postgresql.org/action/patch_view?id=580 I found
following problems with XPath.
1.
SELECT (XPATH('/root/*', 'root xmlns:o=http://olacle.com/db;
xmlns:p=http://postgresql.org/db;o:dbab/b/a/o:dbp:db/p:db/root'));
Produces:
{o:db
a
b/
/a
/o:db,p:db/}
In above b/b was reduced to b/ this is different infoset then
input, and those notations are differently interpreted e.g. by XML
Binding WebServices. The 1st one will may be mapped to empty string,
and 2nd one to to null.
As well result was formatted which produces again different infoset.
Both of above may cause problems with XML digesting.
2.
SELECT (XPATH('/root/*', 'root xmlns:o=http://olacle.com/db;
xmlns:p=http://postgresql.org/db;o:db/o:dbp:db/p:db/root'));
{o:db/,p:db/}
In above I missing namespaces.
I may take on assignment 1st (fix is simple), but for 2nd I have mixed
fillings. I think 2nd should transfer namespaces in some way to client.
What do You think?
Regards,
Radosław Smogura
--
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] XPATH evaluation
On Jun17, 2011, at 11:09 , Radosław Smogura wrote:
1.
SELECT (XPATH('/root/*', 'root xmlns:o=http://olacle.com/db;
xmlns:p=http://postgresql.org/db;o:dbab/b/a/o:dbp:db/p:db/root'));
Produces:
{o:db
a
b/
/a
/o:db,p:db/}
In above b/b was reduced to b/ this is different infoset then input,
and those notations are differently interpreted e.g. by XML Binding
WebServices. The 1st one will may be mapped to empty string, and 2nd one to
to null.
Oh, joy :-(
Does this happen only with my patch applied or also with unpatched HEAD?
2.
SELECT (XPATH('/root/*', 'root xmlns:o=http://olacle.com/db;
xmlns:p=http://postgresql.org/db;o:db/o:dbp:db/p:db/root'));
{o:db/,p:db/}
In above I missing namespaces.
Hm, that's a hard problem a think. Your problem (1) basically tells us that
ideally we'd return the matching parts of an XML document unmodified. Now,
(2) tells us that isn't to most sensible thing to do either.
I may take on assignment 1st (fix is simple)
Whats your proposed fix for (1)?
, but for 2nd I have mixed fillings. I think 2nd should transfer namespaces
in some way to client.
I don't see how XPATH() can do that without breaking it's API. The only
thing we could do AFAICS is the define a second XPATH evaluation function
which returns a list of namespace declarations (prefix and uri) for every
node.
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] per-column generic option
(2011/06/17 8:44), David Fetter wrote: Sorry not to respond sooner. First, the per-column generic options are a great thing for us to have. :) Thanks for the comments. :-) I have an idea I've been using for the next release of DBI-Link that has varying levels of data type mapping. In general, these mappings would be units of executable code, one in-bound, and one out-bound, for each of: Universe (everything, default mapping is the identity map, i.e. a no-op) Database type (e.g. MySQL) Instance (e.g. mysql://foo.bar.com:5432) Database Schema Table Column Some of them seem to be able to be mapped to FDW object, e.g. Database to SERVER and Table to FOREIGN TABLE. I didn't include row in the hierarchy because I couldn't think of a way to identify rows across DBMSs and stable over time. The finest-grain transformation that's been set would be the one actually used. Here's an example of a non-trivial mapping. Database type: MySQL Foreign data type: datetime PostgreSQL data type: timestamptz Transformation direction: Import Transformation: CASE WHEN DATA = '-00-00 00:00:00' THEN NULL ELSE DATA END Here, I'm making the simplifying assumption that there is a bijective mapping between data types. Is there some way to fit the per-column part of such a mapping into this scheme? We'd need to do some dependency tracking in order to be able to point to the appropriate code... IIUC, you are talking about using FDW options as storage of data type mapping setting, or mapping definition itself, right? If so, a foreign table needs to be created to use per-column FDW options. Does it suit to your idea? BTW, I couldn't get what you mean by dependency tracking. You mean the dependency between foreign column and local column? It might include essence of your idea... Would you explain the detail? Regards, -- Shigeru Hanada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] SSPI authentication fails on Windows when server parameter is localhost or domain name
On 15 June 2011 12:16, Dave Page dp...@pgadmin.org wrote:
On Wed, Jun 15, 2011 at 10:53 AM, Ahmed Shinwari
ahmed.shinw...@gmail.com wrote:
Hi All,
I faced a bug on Windows while connecting via SSPI authentication. I was
able to find the bug and have attached the patch. Details listed below;
Postgres Installer: Version 9.0.4
OS: Windows Server 2008 R2/Windows 7
Bug Description:
=
If database Server is running on Windows ('Server 2008 R2' or 'Windows 7')
with authentication mode SSPI and one try to connect from the same machine
via 'psql' with server parameter as 'localhost' or 'fully qualified domain
name', the database throws error;
I've been able to reproduce this issue, and the patch does indeed fix
it. One of our customers has also confirmed it fixed it for them.
I can confirm this affects versions back to 8.3.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
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
Re: [HACKERS] Patch: add GiST support for BOX @ POINT queries
On Fri, Jun 10, 2011 at 22:16, Hitoshi Harada umi.tan...@gmail.com wrote:
I reviewed the patch and worried about hard-wired magic number as
StrategyNumber. At least you should use #define to indicate the
number's meaning.
In addition, the modified gist_box_consistent() is too dangerous;
q_box is declared in the if block locally and is referenced, which
pointer is passed to the outer process of the block. AFAIK if the
local memory of each block is alive outside if block is
platform-dependent.
Isn't it worth adding new consistent function for those purposes? The
approach in the patch as stands looks kludge to me.
Thanks for your review. Coming back to this patch after a few months'
time, I have to say it looks pretty hackish to my eyes as well. :)
I've attempted to add a new consistent function,
gist_boxpoint_consistent(), but the GiST subsystem doesn't call it --
it continues to call gist_box_consistent(). My very simple testcase
is:
CREATE TABLE test (key TEXT PRIMARY KEY, boundary BOX NOT NULL);
CREATE INDEX ON test USING gist (boundary);
INSERT INTO test VALUES ('a', '(2,2,5,5)'), ('b', '(4,4,8,8)'), ('c',
'(7,7,11,11)');
SELECT * FROM test WHERE boundary @ '(4,4)'::POINT;
Prior to my patch, this query is executed as a straightforward seqscan.
Once I add a new strategy to pg_amop.h:
+ DATA(insert ( 2593 603 600 7 s 433 783 0 ));
(603 is the BOX oid, 600 is the POINT oid, and 433 is the @ operator oid):
...the plan switches to an index scan and gist_box_consistent() is
called; at this point, the query fails to return the correct results.
But even after adding the new consistent proc to pg_proc.h:
+ DATA(insert OID = 8000 ( gist_boxpoint_consistentPGNSP PGUID 12
1 0 0 f f f t f i 5 0 16 2281 600 23 26 2281 _null_ _null_ _null_
_null_ gist_boxpoint_consistent _null_ _null_ _null_ ));
And adding it as a new support function in pg_amproc.h:
+ DATA(insert ( 2593 603 600 1 8000 ));
+ DATA(insert ( 2593 603 600 2 2583 ));
+ DATA(insert ( 2593 603 600 3 2579 ));
+ DATA(insert ( 2593 603 600 4 2580 ));
+ DATA(insert ( 2593 603 600 5 2581 ));
+ DATA(insert ( 2593 603 600 6 2582 ));
+ DATA(insert ( 2593 603 600 7 2584 ));
...my gist_boxpoint_consistent() function still doesn't get called.
At this point I'm a bit lost -- while pg_amop.h has plenty of examples
of crosstype comparison operators for btree index methods, there are
none for GiST. Is GiST somehow a special case in this regard?
-Andrew
diff --git a/src/backend/access/gist/gistproc.c b/src/backend/access/gist/gistproc.c
index 43c4b12..ac4fb7f 100644
*** a/src/backend/access/gist/gistproc.c
--- b/src/backend/access/gist/gistproc.c
*** gist_box_consistent(PG_FUNCTION_ARGS)
*** 110,115
--- 110,155
strategy));
}
+ /*
+ * GiST consistent function for traversing a BOX index using a POINT query.
+ */
+ Datum
+ gist_boxpoint_consistent(PG_FUNCTION_ARGS)
+ {
+ GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
+ Point *query = PG_GETARG_POINT_P(1);
+ BOX query_box;
+ StrategyNumber strategy = (StrategyNumber) PG_GETARG_UINT16(2);
+
+ /* Oid subtype = PG_GETARG_OID(3); */
+ bool *recheck = (bool *) PG_GETARG_POINTER(4);
+
+ /* All cases served by this function are exact */
+ *recheck = false;
+
+ elog(NOTICE, gist_boxpoint_consistent() called);
+
+ if (DatumGetBoxP(entry-key) == NULL || query == NULL)
+ PG_RETURN_BOOL(FALSE);
+
+ /* Turn our POINT query into a BOX. */
+ query_box.low = *query;
+ query_box.high = *query;
+
+ /*
+ * if entry is not leaf, use rtree_internal_consistent, else use
+ * gist_box_leaf_consistent
+ */
+ if (GIST_LEAF(entry))
+ PG_RETURN_BOOL(gist_box_leaf_consistent(DatumGetBoxP(entry-key),
+ query_box,
+ strategy));
+ else
+ PG_RETURN_BOOL(rtree_internal_consistent(DatumGetBoxP(entry-key),
+ query_box,
+ strategy));
+ }
+
static void
adjustBox(BOX *b, BOX *addon)
{
diff --git a/src/include/catalog/pg_amop.h b/src/include/catalog/pg_amop.h
index 3b88c41..f3645d5 100644
*** a/src/include/catalog/pg_amop.h
--- b/src/include/catalog/pg_amop.h
*** DATA(insert ( 2593 603 603 4 s 495 783
*** 588,593
--- 588,594
DATA(insert ( 2593 603 603 5 s 496 783 0 ));
DATA(insert ( 2593 603 603 6 s 499 783 0 ));
DATA(insert ( 2593 603 603 7 s 498 783 0 ));
+ DATA(insert ( 2593 603 600 7 s 433 783 0 ));
DATA(insert ( 2593 603 603 8 s 497 783 0 ));
DATA(insert ( 2593 603 603 9 s 2571 783 0 ));
DATA(insert ( 2593 603 603 10 s 2570 783 0 ));
diff --git a/src/include/catalog/pg_amproc.h b/src/include/catalog/pg_amproc.h
index 9e2da2c..bdfc57d 100644
*** a/src/include/catalog/pg_amproc.h
--- b/src/include/catalog/pg_amproc.h
*** DATA(insert ( 2593 603 603 4 2580 ));
*** 170,175
--- 170,182
DATA(insert ( 2593 603 603 5 2581 ));
DATA(insert ( 2593 603 603 6 2582 ));
DATA(insert (
Re: [HACKERS] per-column generic option
On Fri, Jun 17, 2011 at 07:19:39PM +0900, Shigeru Hanada wrote: (2011/06/17 8:44), David Fetter wrote: Sorry not to respond sooner. First, the per-column generic options are a great thing for us to have. :) Thanks for the comments. :-) I have an idea I've been using for the next release of DBI-Link that has varying levels of data type mapping. In general, these mappings would be units of executable code, one in-bound, and one out-bound, for each of: Universe (everything, default mapping is the identity map, i.e. a no-op) Database type (e.g. MySQL) Instance (e.g. mysql://foo.bar.com:5432) Database Schema Table Column Some of them seem to be able to be mapped to FDW object, e.g. Database to SERVER and Table to FOREIGN TABLE. Yes, I see there are a few missing. Universe doesn't really need much of anything, as far as I can tell, except if we wanted to do something that affected SQL/MED globally. Is that hierarchy otherwise OK? DB2 may have one more level between Instance and Database Type, that latter being the province of an individual FDW. I didn't include row in the hierarchy because I couldn't think of a way to identify rows across DBMSs and stable over time. The finest-grain transformation that's been set would be the one actually used. Here's an example of a non-trivial mapping. Database type: MySQL Foreign data type: datetime PostgreSQL data type: timestamptz Transformation direction: Import Transformation: CASE WHEN DATA = '-00-00 00:00:00' THEN NULL ELSE DATA END Here, I'm making the simplifying assumption that there is a bijective mapping between data types. Is there some way to fit the per-column part of such a mapping into this scheme? We'd need to do some dependency tracking in order to be able to point to the appropriate code... IIUC, you are talking about using FDW options as storage of data type mapping setting, or mapping definition itself, right? If so, a foreign table needs to be created to use per-column FDW options. Does it suit to your idea? Yes. The only mildly disturbing thing about how that would work is that magic key names would actually point to executable code, so there would be some kind of non-uniform processing of the options, and (possibly quite unlikely) ways to escalate privilege. BTW, I couldn't get what you mean by dependency tracking. You mean the dependency between foreign column and local column? It might include essence of your idea... Would you explain the detail? I think the dependency between the mapping between the foreign column and the local one is already handled. On that subject, it's possible to make an argument that this mapping might need to be expanded so that in general, M foreign columns map to N local ones (distinct M and N), but that's a research topic, so let's not worry about it now. The dependency tracking I have in mind is of the actual executable code. If the inbound mapping has what amounts to a pointer to a function, it shouldn't be possible to drop that function without CASCADE, and if we're caching such functions, the cache needs to be refreshed any time the function changes. 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] pg_upgrade using appname to lock out other users
Bruce Momjian wrote: Robert Haas wrote: On Thu, Jun 16, 2011 at 11:47 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: We can pick different options for 9.0, 9.1, and 9.2. ?(For PG 9.0 probably only #1 is appropriate.) I don't like any of these options as well as what I already proposed. I proposed a complicated approach that actually fixes the problem for real; you're proposing a whole bunch of simpler approaches all of which have pretty obvious holes. ?We already have something that only sorta works; replacing it with a different system that only sorta works is not going to be a great leap forward. What is your proposal? ?Write a password into a file that is read by the postmaster on startup and used for connections? ?That would remove the modify pg_hba.conf to 'trust' step, but again only for new servers. Yeah, as noted upthread, I'd probably create a binary_upgrade.conf that works like recovery.conf, if it were me. Well, I know exactly where the data directories are. We will still have a problem for anyone upgrading from pre-9.2. We could go with the idea of documenting the suggestion of using unused ports in pre-9.2 and use that file for 9.2. We would still have to mention the ports idea in 9.2 as well because of people upgrading from pre-9.2. We can have that file be read only in -b binary-upgrade mode so there is little risk if the file accidentally isn't deleted. -- 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] Boolean operators without commutators vs. ALL/ANY
Excerpts from Florian Pflug's message of vie jun 17 04:46:32 -0400 2011: On Jun17, 2011, at 03:42 , Alvaro Herrera wrote: To make matters worse, our delimiters for regexes are the same as for strings, the single quote. So you get foo =~ 'bar'/* foo is the text column, bar is the regex */ 'bar' =~ foo/* no complaint but it's wrong */ 'bar' ~= foo/* okay */ 'foo' ~= bar/* no complaint but it's wrong */ How do I tell which is the regex here? If we used, say, /, that would be a different matter: How is this different from the situation today where the operator is just ~? Err, we don't have commutators today? -- Á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] XPATH evaluation
On 06/17/2011 05:41 AM, Florian Pflug wrote:
On Jun17, 2011, at 11:09 , Radosław Smogura wrote:
1.
SELECT (XPATH('/root/*', 'root xmlns:o=http://olacle.com/db;
xmlns:p=http://postgresql.org/db;o:dbab/b/a/o:dbp:db/p:db/root'));
Produces:
{o:db
a
b/
/a
/o:db,p:db/}
In aboveb/b was reduced tob/ this is different infoset then input, and
those notations are differently interpreted e.g. by XML Binding WebServices. The 1st one will
may be mapped to empty string, and 2nd one to to null.
Oh, joy :-(
I thought these were basically supposed to be the same.
The XML Information Set for example specifically excludes:
The difference between the two forms of an empty element: |foo/ |
and |foo/foo|.
|
See http://www.w3.org/TR/2004/REC-xml-infoset-20040204/ Appendix D.
Note that this implies that foo/foo does not have content of an
empty string, but that it has no content.
|
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] Patch: add GiST support for BOX @ POINT queries
Andrew Tipton andrew.t.tip...@gmail.com writes: At this point I'm a bit lost -- while pg_amop.h has plenty of examples of crosstype comparison operators for btree index methods, there are none for GiST. Is GiST somehow a special case in this regard? AFAIR, GIST doesn't use the concept of a crosstype opclass entry. It only works with primary opclass entries. You have to set both amproclefttype and amprocrighttype to the datatype of the indexable column, regardless of what the other argument actually is. (I think this implies that you can't have more than one consistent function per opclass, which means you have to do whatever it is you have in mind by patching the existing consistent function, not adding another one alongside it.) 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] Boolean operators without commutators vs. ALL/ANY
On Jun17, 2011, at 15:36 , Alvaro Herrera wrote: Excerpts from Florian Pflug's message of vie jun 17 04:46:32 -0400 2011: On Jun17, 2011, at 03:42 , Alvaro Herrera wrote: To make matters worse, our delimiters for regexes are the same as for strings, the single quote. So you get foo =~ 'bar'/* foo is the text column, bar is the regex */ 'bar' =~ foo/* no complaint but it's wrong */ 'bar' ~= foo/* okay */ 'foo' ~= bar/* no complaint but it's wrong */ How do I tell which is the regex here? If we used, say, /, that would be a different matter: How is this different from the situation today where the operator is just ~? Err, we don't have commutators today? So? How does that reduce that risk of somebody writing pattern ~ text instead of text ~ pattern? Modifying your quote from above foo ~ 'bar'/* foo is the text column, bar is the regex */ 'bar' ~ foo/* no complaint but it's wrong */ How do I tell which is the regex here? How is that worse than the situation with =~ and ~=? =~ and ~= at least don't *look* symmetric when they really are not, which is the heart of the complaint, and also what makes defining a sensible commutator impossible. Also, do you have a better suggestion for how we can fix my original gripe? Adding support for 'ANY/ALL op scalar was shot down by Tom, so it looks like we need a commutator for ~. @ is severely disliked by Tom, on the grounds that it's already been deprecated in other places. =~ is argued against by you and Robert Haas (I think). We're running out of options here... 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] Boolean operators without commutators vs. ALL/ANY
Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011: On Jun17, 2011, at 15:36 , Alvaro Herrera wrote: Excerpts from Florian Pflug's message of vie jun 17 04:46:32 -0400 2011: On Jun17, 2011, at 03:42 , Alvaro Herrera wrote: To make matters worse, our delimiters for regexes are the same as for strings, the single quote. So you get foo =~ 'bar'/* foo is the text column, bar is the regex */ 'bar' =~ foo/* no complaint but it's wrong */ 'bar' ~= foo/* okay */ 'foo' ~= bar/* no complaint but it's wrong */ How do I tell which is the regex here? If we used, say, /, that would be a different matter: How is this different from the situation today where the operator is just ~? Err, we don't have commutators today? So? How does that reduce that risk of somebody writing pattern ~ text instead of text ~ pattern? Modifying your quote from above foo ~ 'bar'/* foo is the text column, bar is the regex */ 'bar' ~ foo/* no complaint but it's wrong */ How do I tell which is the regex here? The regex is always to the right of the operator. How is that worse than the situation with =~ and ~=? With =~ it is to the right, with ~= it is to the left. I have sometimes needed to look up which is which on ~ and ~~. I assume that whichever way we go here, we're still going to have to look up operator definitions in docs or online help. This kind of help doesn't, err, help all that much: alvherre=# \doS ~ Listado de operadores Esquema | Nombre | Tipo arg izq | Tipo arg der | Tipo resultado | Descripción ++--+--++ ... pg_catalog | ~ | text | text | boolean| matches regular expression, case-sensitive Note that there's no way to tell which is the regex here. It'd be a lot better if the description was explicit about it. (Or, alternatively, use a different data type for regexes than plain text ... but that has been in the Todo list for years ...) =~ and ~= at least don't *look* symmetric when they really are not, which is the heart of the complaint, and also what makes defining a sensible commutator impossible. Also, do you have a better suggestion for how we can fix my original gripe? Adding support for 'ANY/ALL op scalar was shot down by Tom, so it looks like we need a commutator for ~. @ is severely disliked by Tom, on the grounds that it's already been deprecated in other places. =~ is argued against by you and Robert Haas (I think). We're running out of options here... Have ~ keep its existing semantics, use ~= for the commutator? There are a lot more chars allowed in operator names anyway, it doesn't seem to me like we need to limit ourselves to ~, = and @. I *do* like the idea of having commutate-ability for ANY/ALL, having needed it a couple of times in the past. -- Á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] [GENERAL] Issues with generate_series using integer boundaries
On 17 June 2011 04:44, Robert Haas robertmh...@gmail.com wrote: On Wed, Feb 9, 2011 at 4:50 AM, Thom Brown t...@linux.com wrote: On 9 February 2011 02:11, Robert Haas robertmh...@gmail.com wrote: On Tue, Feb 8, 2011 at 8:30 PM, Andrew Dunstan and...@dunslane.net wrote: Quite right, but the commitfest manager isn't meant to be a substitute for one. Bug fixes aren't subject to the same restrictions of feature changes. Another option would be to add this here: http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Open_Items I've removed it from the commitfest because it really doesn't belong there, and I've added it to the open items list. So, I finally got around to look at this, and I think there is a simpler solution. When an overflow occurs while calculating the next value, that just means that the value we're about to return is the last one that should be generated. So we just need to frob the context state so that the next call will decide we're done. There are any of number of ways to do that; I just picked what looked like the easiest one. I knew there'd be a much simpler way of solving this. Works for me. Thanks Robert. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 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
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On Wed, Feb 9, 2011 at 4:50 AM, Thom Brown t...@linux.com wrote: On 9 February 2011 02:11, Robert Haas robertmh...@gmail.com wrote: On Tue, Feb 8, 2011 at 8:30 PM, Andrew Dunstan and...@dunslane.net wrote: Quite right, but the commitfest manager isn't meant to be a substitute for one. Bug fixes aren't subject to the same restrictions of feature changes. Another option would be to add this here: http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Open_Items I've removed it from the commitfest because it really doesn't belong there, and I've added it to the open items list. So, I finally got around to look at this, and I think there is a simpler solution. When an overflow occurs while calculating the next value, that just means that the value we're about to return is the last one that should be generated. So we just need to frob the context state so that the next call will decide we're done. There are any of number of ways to do that; I just picked what looked like the easiest one. Tangential comment but have you considered emitting a warning (and/or log entry) when you are 10,000-50,000 away from issuing the last available number in the sequence so that some recognition exists that any code depending on the sequence is going to fail soon? Also, during sequence creation you know the integer type being used so that maximum value is known and an overflow should not need to come into play (I guess the trade-off is the implicit try-catch [or whatever mechanism C uses] performance hit versus the need to store another full integer in the data structure). You could also give access to the warning threshold value so that the developer can change it to whatever value is desired (with a meaningful default of course). David J. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On Fri, Jun 17, 2011 at 10:39 AM, David Johnston pol...@yahoo.com wrote: Tangential comment but have you considered emitting a warning (and/or log entry) when you are 10,000-50,000 away from issuing the last available number in the sequence so that some recognition exists that any code depending on the sequence is going to fail soon? Also, during sequence creation you know the integer type being used so that maximum value is known and an overflow should not need to come into play (I guess the trade-off is the implicit try-catch [or whatever mechanism C uses] performance hit versus the need to store another full integer in the data structure). You could also give access to the warning threshold value so that the developer can change it to whatever value is desired (with a meaningful default of course). There are already tools out there that can monitor this stuff - for example, check_postgres.pl. http://bucardo.org/check_postgres/check_postgres.pl.html#sequence We tend to avoid emitting warnings for this kind of thing because they can consume vast amounts of disk space, and a lot of times no one's looking at them anyway. -- 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] Boolean operators without commutators vs. ALL/ANY
On 06/17/2011 10:20 AM, Alvaro Herrera wrote: alvherre=# \doS ~ Listado de operadores Esquema | Nombre | Tipo arg izq | Tipo arg der | Tipo resultado | Descripción ++--+--++ ... pg_catalog | ~ | text | text | boolean| matches regular expression, case-sensitive Note that there's no way to tell which is the regex here. It'd be a lot better if the description was explicit about it. (Or, alternatively, use a different data type for regexes than plain text ... but that has been in the Todo list for years ...) +1 for improving the description. Have ~ keep its existing semantics, use ~= for the commutator? There are a lot more chars allowed in operator names anyway, it doesn't seem to me like we need to limit ourselves to ~, = and @. Yeah, maybe something like ~ for the commutator. (I know, we're bikeshedding somewhat.) I *do* like the idea of having commutate-ability for ANY/ALL, having needed it a couple of times in the past. Indeed. me too. 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] Boolean operators without commutators vs. ALL/ANY
On Jun17, 2011, at 16:20 , Alvaro Herrera wrote: Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011: So? How does that reduce that risk of somebody writing pattern ~ text instead of text ~ pattern? Modifying your quote from above foo ~ 'bar'/* foo is the text column, bar is the regex */ 'bar' ~ foo/* no complaint but it's wrong */ How do I tell which is the regex here? The regex is always to the right of the operator. Which is something you have to remember... It's not in any way deducible from foo ~ bar alone. How is that worse than the situation with =~ and ~=? With =~ it is to the right, with ~= it is to the left. It's always where the tilde is. Yeah, you have to remember that. Just as today you have to remember that the pattern goes on the right side. I have sometimes needed to look up which is which on ~ and ~~. I assume that whichever way we go here, we're still going to have to look up operator definitions in docs or online help. This kind of help doesn't, err, help all that much: alvherre=# \doS ~ Listado de operadores Esquema | Nombre | Tipo arg izq | Tipo arg der | Tipo resultado | Descripción ++--+--++ ... pg_catalog | ~ | text | text | boolean| matches regular expression, case-sensitive Note that there's no way to tell which is the regex here. It'd be a lot better if the description was explicit about it. I'm all for it, let's change the description then! Shall I submit a patch? (Or, alternatively, use a different data type for regexes than plain text ... but that has been in the Todo list for years ...) I actually like that idea. Since we probably don't want a type for every kind of pattern we support (like, similar to, regexp), such a type wouldn't be much more than a synonym for text though. I personally don't have a problem with that, but I somehow feel there's gonna be quite some pushback... Also, do you have a better suggestion for how we can fix my original gripe? Adding support for 'ANY/ALL op scalar was shot down by Tom, so it looks like we need a commutator for ~. @ is severely disliked by Tom, on the grounds that it's already been deprecated in other places. =~ is argued against by you and Robert Haas (I think). We're running out of options here... Have ~ keep its existing semantics, use ~= for the commutator? So how does that make it any easier to tell what foo ~ bar and foo ~= bar mean? With that, neither the pattern is always on the right nor the pattern goes where the tilde is mnemonic works. Also, do we really want to end up with a large number of commutator pairs with totally unrelated names? I fear that this *will* seriously harm readability of SQL statements, and we'll regret it badly. 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] XPATH evaluation
Andrew Dunstan and...@dunslane.net Friday 17 of June 2011 15:47:04
On 06/17/2011 05:41 AM, Florian Pflug wrote:
On Jun17, 2011, at 11:09 , Radosław Smogura wrote:
1.
SELECT (XPATH('/root/*', 'root xmlns:o=http://olacle.com/db;
xmlns:p=http://postgresql.org/db;o:dbab/b/a/o:dbp:db/p
:db/root')); Produces:
{o:db
a
b/
/a
/o:db,p:db/}
In aboveb/b was reduced tob/ this is different infoset then
input, and those notations are differently interpreted e.g. by XML
Binding WebServices. The 1st one will may be mapped to empty string,
and 2nd one to to null.
Oh, joy :-(
I thought these were basically supposed to be the same.
The XML Information Set for example specifically excludes:
The difference between the two forms of an empty element: |foo/ |
and |foo/foo|.
See http://www.w3.org/TR/2004/REC-xml-infoset-20040204/ Appendix D.
Note that this implies that foo/foo does not have content of an
empty string, but that it has no content.
cheers
andrew
Indeed, Infoset Spec, and XML Canonization Spec treats foo/foo same, as
foo/ - my wrong, but XML canonization preservs whitespaces, if I remember
well, I think there is example.
In any case if I will store image in XML (I've seen this), preservation of
white spaces and new lines is important.
Regards,
Radek.
--
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] [v9.2] Start new timeline for PITR
On Fri, Jun 10, 2011 at 11:30 AM, David Fetter da...@fetter.org wrote: This also allows subsequent PITR to other times on the original timeline. Josh B pointed out that since this option to true conflicts with another option, having both should prevent recovery from even starting, and I'll work up a patch for this tonight or at latest tomorrow. Hi, Are you still working on this? should we expect a new patch? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] XPATH evaluation
On 06/17/2011 10:55 AM, Radosław Smogura wrote:
Andrew Dunstanand...@dunslane.net Friday 17 of June 2011 15:47:04
On 06/17/2011 05:41 AM, Florian Pflug wrote:
On Jun17, 2011, at 11:09 , Radosław Smogura wrote:
1.
SELECT (XPATH('/root/*', 'root xmlns:o=http://olacle.com/db;
xmlns:p=http://postgresql.org/db;o:dbab/b/a/o:dbp:db/p
:db/root')); Produces:
{o:db
a
b/
/a
/o:db,p:db/}
In aboveb/b was reduced tob/ this is different infoset then
input, and those notations are differently interpreted e.g. by XML
Binding WebServices. The 1st one will may be mapped to empty string,
and 2nd one to to null.
Oh, joy :-(
I thought these were basically supposed to be the same.
The XML Information Set for example specifically excludes:
The difference between the two forms of an empty element: |foo/ |
and |foo/foo|.
Seehttp://www.w3.org/TR/2004/REC-xml-infoset-20040204/ Appendix D.
Note that this implies thatfoo/foo does not have content of an
empty string, but that it has no content.
cheers
andrew
Indeed, Infoset Spec, and XML Canonization Spec treatsfoo/foo same, as
foo/ - my wrong, but XML canonization preservs whitespaces, if I remember
well, I think there is example.
In any case if I will store image in XML (I've seen this), preservation of
white spaces and new lines is important.
If you store images you should encode them anyway, in base64 or hex.
More generally, data that needs that sort of preservation should
possibly be in CDATA nodes.
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] XPATH evaluation
Florian Pflug f...@phlo.org Friday 17 of June 2011 11:41:08
On Jun17, 2011, at 11:09 , Radosław Smogura wrote:
1.
SELECT (XPATH('/root/*', 'root xmlns:o=http://olacle.com/db;
xmlns:p=http://postgresql.org/db;o:dbab/b/a/o:dbp:db/p:
db/root')); Produces:
{o:db
a
b/
/a
/o:db,p:db/}
In above b/b was reduced to b/ this is different infoset then
input, and those notations are differently interpreted e.g. by XML
Binding WebServices. The 1st one will may be mapped to empty string,
and 2nd one to to null.
Oh, joy :-(
Does this happen only with my patch applied or also with unpatched HEAD?
2.
SELECT (XPATH('/root/*', 'root xmlns:o=http://olacle.com/db;
xmlns:p=http://postgresql.org/db;o:db/o:dbp:db/p:db/root'));
{o:db/,p:db/}
In above I missing namespaces.
Hm, that's a hard problem a think. Your problem (1) basically tells us that
ideally we'd return the matching parts of an XML document unmodified. Now,
(2) tells us that isn't to most sensible thing to do either.
I may take on assignment 1st (fix is simple)
Whats your proposed fix for (1)?
, but for 2nd I have mixed fillings. I think 2nd should transfer
namespaces in some way to client.
I don't see how XPATH() can do that without breaking it's API. The only
thing we could do AFAICS is the define a second XPATH evaluation function
which returns a list of namespace declarations (prefix and uri) for every
node.
best regards,
Florian Pflug
No this is not about Your patch, but was inspired by it.
Regards,
Radek
--
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] Boolean operators without commutators vs. ALL/ANY
On Fri, Jun 17, 2011 at 10:20:04AM -0400, Alvaro Herrera wrote: Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011: How is that worse than the situation with =~ and ~=? With =~ it is to the right, with ~= it is to the left. To throw my user opinion into this ring (as a long time user of regexes in many different systems) I've always taken the ~ to be short hand for the 'approximately' notation (a squiggly equals) which has good semantic match in my mind: a regex match is sort of a fuzzy equality. With that model, the suggested pair is fairly mnemonic - the 'fuzzy' part i(the pattern) is next to the squiggles, the 'concrete' part goes by the equals. I have sometimes needed to look up which is which on ~ and ~~. which has no such directionality, so yeah, no hinting there. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- 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] XPATH evaluation
On Jun17, 2011, at 17:09 , Andrew Dunstan wrote: If you store images you should encode them anyway, in base64 or hex. More generally, data that needs that sort of preservation should possibly be in CDATA nodes. All very true. Still, ideally we'd return the XML exactly as stored, though, even for the results of XPATH queries. But I've no idea if this is easily done with libxml or not. 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] Boolean operators without commutators vs. ALL/ANY
On Jun17, 2011, at 17:15 , Ross J. Reedstrom wrote: On Fri, Jun 17, 2011 at 10:20:04AM -0400, Alvaro Herrera wrote: Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011: How is that worse than the situation with =~ and ~=? With =~ it is to the right, with ~= it is to the left. To throw my user opinion into this ring (as a long time user of regexes in many different systems) I've always taken the ~ to be short hand for the 'approximately' notation (a squiggly equals) which has good semantic match in my mind: a regex match is sort of a fuzzy equality. With that model, the suggested pair is fairly mnemonic - the 'fuzzy' part i(the pattern) is next to the squiggles, the 'concrete' part goes by the equals. Hey, that's my mnemonic device! ;-) 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] XPATH evaluation
2011/6/17, Andrew Dunstan and...@dunslane.net: On 06/17/2011 10:55 AM, Radosław Smogura wrote: XML canonization preservs whitespaces, if I remember well, I think there is example. In any case if I will store image in XML (I've seen this), preservation of white spaces and new lines is important. If you store images you should encode them anyway, in base64 or hex. Whitespace that is not at certain obviously irrelevant places (such as right after , between attributes, outside of the whole document, etc), and that is not defined to be irrelevant by some schema (if the parser is schema-aware), is relevant. You cannot just muck around with it and consider that correct. More generally, data that needs that sort of preservation should possibly be in CDATA nodes. CDATA sections are just syntactic sugar (a form of escaping): URL:http://www.w3.org/TR/xml-infoset/#omitted Appendix D: What is not in the Information Set [..] 19. The boundaries of CDATA marked sections. Therefore, there is not such thing as a CDATA node that would be different from just text (Infoset-wise). Note that that does not mean that binary data is never supposed to be altered or that all binary data is to be accepted: e.g., whether newlines are represented using \n, \r, or \r\n is irrelevant; also, binary data that is not valid according to the used encoding must of course not be accepted. Nicolas -- A. Because it breaks the logical sequence of discussion. Q. Why is top posting bad? -- 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] Boolean operators without commutators vs. ALL/ANY
Excerpts from Florian Pflug's message of vie jun 17 10:49:46 -0400 2011: On Jun17, 2011, at 16:20 , Alvaro Herrera wrote: Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011: So? How does that reduce that risk of somebody writing pattern ~ text instead of text ~ pattern? Modifying your quote from above foo ~ 'bar'/* foo is the text column, bar is the regex */ 'bar' ~ foo/* no complaint but it's wrong */ How do I tell which is the regex here? The regex is always to the right of the operator. Which is something you have to remember... It's not in any way deducible from foo ~ bar alone. Maybe, but the mnemonic rule seems quite a bit easier (to me anyway). In my head I think of ~ as matches, so text matches regex, whereas regex matches text doesn't make as much sense. (Hmm now that I see it, maybe in english this is not so clear, but in spanish the difference is pretty obvious). How is that worse than the situation with =~ and ~=? With =~ it is to the right, with ~= it is to the left. It's always where the tilde is. Yeah, you have to remember that. Just as today you have to remember that the pattern goes on the right side. Well, the mnemonic would be that ~ is still text matches regex, while ~= is the weird operator that goes the other way around, so it's still pretty clear. I have sometimes needed to look up which is which on ~ and ~~. I assume that whichever way we go here, we're still going to have to look up operator definitions in docs or online help. This kind of help doesn't, err, help all that much: alvherre=# \doS ~ Listado de operadores Esquema | Nombre | Tipo arg izq | Tipo arg der | Tipo resultado | Descripción ++--+--++ ... pg_catalog | ~ | text | text | boolean| matches regular expression, case-sensitive Note that there's no way to tell which is the regex here. It'd be a lot better if the description was explicit about it. I'm all for it, let's change the description then! Shall I submit a patch? Yes, please. (Or, alternatively, use a different data type for regexes than plain text ... but that has been in the Todo list for years ...) I actually like that idea. Since we probably don't want a type for every kind of pattern we support (like, similar to, regexp), such a type wouldn't be much more than a synonym for text though. I personally don't have a problem with that, but I somehow feel there's gonna be quite some pushback... Hmm, why? Maybe that's something we can discuss. Also, do we really want to end up with a large number of commutator pairs with totally unrelated names? I fear that this *will* seriously harm readability of SQL statements, and we'll regret it badly. Hmm. I guess this wouldn't be much of a problem if you could use ANY/ALL with a function instead of an operator, c.f. map(). -- Á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] XPATH evaluation
Andrew Dunstan and...@dunslane.net Friday 17 of June 2011 17:09:25
On 06/17/2011 10:55 AM, Radosław Smogura wrote:
Andrew Dunstanand...@dunslane.net Friday 17 of June 2011 15:47:04
On 06/17/2011 05:41 AM, Florian Pflug wrote:
On Jun17, 2011, at 11:09 , Radosław Smogura wrote:
1.
SELECT (XPATH('/root/*', 'root xmlns:o=http://olacle.com/db;
xmlns:p=http://postgresql.org/db;o:dbab/b/a/o:dbp:db/
p
:db/root')); Produces:
{o:db
a
b/
/a
/o:db,p:db/}
In aboveb/b was reduced tob/ this is different infoset then
input, and those notations are differently interpreted e.g. by XML
Binding WebServices. The 1st one will may be mapped to empty
string, and 2nd one to to null.
Oh, joy :-(
I thought these were basically supposed to be the same.
The XML Information Set for example specifically excludes:
The difference between the two forms of an empty element: |foo/
| and |foo/foo|.
Seehttp://www.w3.org/TR/2004/REC-xml-infoset-20040204/ Appendix D.
Note that this implies thatfoo/foo does not have content of an
empty string, but that it has no content.
cheers
andrew
Indeed, Infoset Spec, and XML Canonization Spec treatsfoo/foo same,
as foo/ - my wrong, but XML canonization preservs whitespaces, if I
remember well, I think there is example.
In any case if I will store image in XML (I've seen this), preservation
of white spaces and new lines is important.
If you store images you should encode them anyway, in base64 or hex.
More generally, data that needs that sort of preservation should
possibly be in CDATA nodes.
cheers
andrew
I know this answer, because this solution is better. But, during one work I
created XSL-FO with whitespace preserve attribute, if I would like to get part
of such XSL-FO I could destroy output document.
But those use-cases doesn't change fact that XPATH output doesn't preserves
whitepsaces, newlines, and produces different node, then was in original. It
same as regexp form varchar will trim result without control.
I emphasize this because it may cause problems with XML Digest algorithms
which are quite popular and may cause some legal! problems when you try to use
Advance Signature in Europe Union, as well with other application.
With XML Binding it's quite popular to interpret foo/ as null, foo/foo
as empty string. In particulary mantoined Infoset Spec doesn't matters here.
I think no-formatting is reasonable requirement for XPATH function.
Regards,
Radek.
--
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] XPATH evaluation
On 06/17/2011 11:29 AM, Nicolas Barbier wrote: 2011/6/17, Andrew Dunstanand...@dunslane.net: On 06/17/2011 10:55 AM, Radosław Smogura wrote: XML canonization preservs whitespaces, if I remember well, I think there is example. In any case if I will store image in XML (I've seen this), preservation of white spaces and new lines is important. If you store images you should encode them anyway, in base64 or hex. Whitespace that is not at certain obviously irrelevant places (such as right after , between attributes, outside of the whole document, etc), and that is not defined to be irrelevant by some schema (if the parser is schema-aware), is relevant. You cannot just muck around with it and consider that correct. Sure, but if you're storing arbitrary binary data such as images whitespace is the least of your problems. That's why I've always encoded them in base64. More generally, data that needs that sort of preservation should possibly be in CDATA nodes. CDATA sections are just syntactic sugar (a form of escaping): URL:http://www.w3.org/TR/xml-infoset/#omitted Yeah. OTOH doesn't an empty CDATA section force a child element, where a pure empty element does not? Anyway, we're getting a bit far from what Postgres needs to be doing. 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] Boolean operators without commutators vs. ALL/ANY
On Fri, Jun 17, 2011 at 11:46 AM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
I guess this wouldn't be much of a problem if you could use ANY/ALL with
a function instead of an operator, c.f. map().
Yeah. Or really what you want is a lambda-expression, rather than a
predefined function.
fold(bool_and, map { val ~ $0 } array)
I suspect that's darn hard to make work though.
--
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] Boolean operators without commutators vs. ALL/ANY
On Fri, Jun 17, 2011 at 05:21:10PM +0200, Florian Pflug wrote: On Jun17, 2011, at 17:15 , Ross J. Reedstrom wrote: On Fri, Jun 17, 2011 at 10:20:04AM -0400, Alvaro Herrera wrote: Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011: How is that worse than the situation with =~ and ~=? With =~ it is to the right, with ~= it is to the left. To throw my user opinion into this ring (as a long time user of regexes in many different systems) I've always taken the ~ to be short hand for the 'approximately' notation (a squiggly equals) which has good semantic match in my mind: a regex match is sort of a fuzzy equality. With that model, the suggested pair is fairly mnemonic - the 'fuzzy' part i(the pattern) is next to the squiggles, the 'concrete' part goes by the equals. Hey, that's my mnemonic device! ;-) Ah, good, so since this is almost mathematics, and we have two instances, that's a proof then. :-) Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- 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] Boolean operators without commutators vs. ALL/ANY
On Jun17, 2011, at 18:00 , Robert Haas wrote:
On Fri, Jun 17, 2011 at 11:46 AM, Alvaro Herrera
alvhe...@commandprompt.com wrote:
I guess this wouldn't be much of a problem if you could use ANY/ALL with
a function instead of an operator, c.f. map().
Yeah. Or really what you want is a lambda-expression, rather than a
predefined function.
fold(bool_and, map { val ~ $0 } array)
Yeah, to bad we can't just write
SELECT BOOL_AND(val ~ e) FROM UNNEST(array)
Hey...wait a minute... ;-)
(I guess you actually meant
fold(bool_and, map { val ~ $0 } array)
which the equivalent sub-select
SELECT BOOL_AND(e ~ val) FROM UNNEST(array))
Still, you can't put that into a CHECK constraint (because it
counts as sub-select) and it's considerable longer and harder
to read then
val = ANY(array)
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] Boolean operators without commutators vs. ALL/ANY
On Jun17, 2011, at 17:46 , Alvaro Herrera wrote: Excerpts from Florian Pflug's message of vie jun 17 10:49:46 -0400 2011: Maybe, but the mnemonic rule seems quite a bit easier (to me anyway). In my head I think of ~ as matches, so text matches regex, whereas regex matches text doesn't make as much sense. (Hmm now that I see it, maybe in english this is not so clear, but in spanish the difference is pretty obvious). I can't really argue with that, only state for that record that it's different for me. I think of ~ as similar or approximately equal, and hence intuitively expect it to be symmetric. Whether or not matches technically implies some direction or not I cannot say as I'm not an english native speaker myself. But if I had to guess, I'd say it doesn't. How is that worse than the situation with =~ and ~=? With =~ it is to the right, with ~= it is to the left. It's always where the tilde is. Yeah, you have to remember that. Just as today you have to remember that the pattern goes on the right side. Well, the mnemonic would be that ~ is still text matches regex, while ~= is the weird operator that goes the other way around, so it's still pretty clear. Again, that depends on a person's background. For me it'd be ~= is the regexp matching operator and ~ is for some strange reasons its commutator. 'm all for it, let's change the description then! Shall I submit a patch? Yes, please. Will do, but after we've reached an overall agreement about the fate or ~ and friends. (Or, alternatively, use a different data type for regexes than plain text ... but that has been in the Todo list for years ...) I actually like that idea. Since we probably don't want a type for every kind of pattern we support (like, similar to, regexp), such a type wouldn't be much more than a synonym for text though. I personally don't have a problem with that, but I somehow feel there's gonna be quite some pushback... Hmm, why? Maybe that's something we can discuss. Ok, I'll start a new thread for this. 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] XPATH evaluation
2011/6/17, Andrew Dunstan and...@dunslane.net: On 06/17/2011 11:29 AM, Nicolas Barbier wrote: CDATA sections are just syntactic sugar (a form of escaping): Yeah. OTOH doesn't an empty CDATA section force a child element, where a pure empty element does not? Wow, some Googling around shows that there is much confusion about this. I thought that it was obvious that adding ![CDATA[]] shouldn't change the content at all, but quite a few people seem to disagree :-/. Nicolas -- A. Because it breaks the logical sequence of discussion. Q. Why is top posting bad? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 9.1beta2 / UNLOGGED + CHECK + INHERITS
I wanted to see how much faster unlogged tables might be for an app I have, so as a quick test I did: s/CREATE TABLE/CREATE UNLOGGED TABLE/ to get some numbers. Which lead to a crash. Here is a trimmed down test case: $ cat unlog-test.sql CREATE UNLOGGED TABLE leases ( mac macaddr NOT NULL, ip inet NOT NULL, start_ts timestamp with time zone NOT NULL, end_ts timestamp with time zone NOT NULL, id bigint NOT NULL, truncated integer, router_ip inet, CONSTRAINT leases_check CHECK ((start_ts end_ts)) ); CREATE UNLOGGED TABLE closed_leases ( ) INHERITS (leases); CREATE UNLOGGED TABLE open_leases ( ) INHERITS (leases); ^D $ psql91 -U postgres postgres -c '\i unlog-test.sql' CREATE TABLE psql91:unlog-test.sql:13: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql91:unlog-test.sql:13: connection to server was lost The CHECK CONSTRAINT on leases is required to cause the crash on creating closed_leases. Garick -- 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] crash-safe visibility map, take five
On Thu, Jun 16, 2011 at 11:17 PM, Noah Misch n...@leadboat.com wrote:
I took a look at this patch.
No kidding! Thanks for the very detailed review.
+1 for Buffer over Buffer * when the value isn't mutated for the caller.
I changed this.
I suggest revisiting the suggestion in
http://archives.postgresql.org/message-id/27743.1291135...@sss.pgh.pa.us and
including a latestRemovedXid in xl_heap_visible. The range of risky xids is
the same for setting a visibility map bit as for deleting a dead tuple, and
the same operation (VACUUM) produces both conflicts.
See Heikki's follow-up email. The standby has to ignore all-visible
bits anyway, because the fact that a transaction is all-visible on the
master does not imply that it is all-visible on the standby. So I
don't think there's a problem here.
lazy_scan_heap() has two calls to visibilitymap_set(), but the patch only
changed the recptr argument for one of them. This has the effect that we only
emit WAL for empty pages and pages that happened to have pd_lsn == {0,0}, such
as those not modified since the transaction creating the table. I fixed this
before testing further.
Good catch, thanks. I also added the Assert() that you recommended
further down.
This happens due to heap_xlog_redo() calling UnlockReleaseBuffer() despite
having taken no buffer content lock. I added
LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE);
before the if to get past this.
Fixed, thanks.
I proceeded to do some immediate-shutdown tests to see if we get the bits set
as expected. I set up a database like this:
CREATE EXTENSION pageinspect;
CREATE TABLE t (c int);
INSERT INTO t VALUES (2);
CHECKPOINT;
I normally cleared bits with UPDATE t SET c = 1; CHECKPOINT; and set them
with VACUUM t. I checked bits with this query:
SELECT to_hex(get_byte(get_raw_page('t', 'vm', 0), 24)),
to_hex(flags::int) FROM page_header(get_raw_page('t', 0));
The row from that query should generally be 0,1 (bits unset) or 1,5 (bits
set). 0,5 is fine after a crash. 1,1 means we've broken our contract: the VM
bit is set while PD_ALL_VISIBLE is not set.
First test was to clear bits, checkpoint, then VACUUM and SIGQUIT the
postmaster. The system came back up with 1/1 bits. I poked around enough to
see that XLByteLE(lsn, PageGetLSN(page)) was failing, but I did not dig any
deeper toward a root cause. If you have trouble reproducing this, let me know
so I can assemble a complete, self-contained test case.
Thank you for putting these test cases together. As you can probably
tell, I was having difficulty figuring out exactly how to test this.
I think that the problem here is that the sense of that test is
exactly backwards from what it should be. IIUC, the word precedes
in the previous comment should in fact say follows.
I would delete this comment. We were done earlier, but we needed to finish
the
critical section.
Done.
Concerning the optimization in xlog_heap_delete() et al. of not changing the
page when its LSN is newer -- am I correct that it only ever triggers when
full_page_writes = off? Assuming yes ...
I believe that's right.
+ /*
+ * Even we skipped the heap page update due to the LSN interlock, it's
+ * still safe to update the visibility map. Any WAL record that clears
+ * the visibility map bit does so before checking the page LSN, so any
+ * bits that need to be cleared will still be cleared.
+ */
+ if (record-xl_info XLR_BKP_BLOCK_1)
+ RestoreBkpBlocks(lsn, record, false);
+ else
+ {
+ Relation reln;
+ Buffer vmbuffer = InvalidBuffer;
+
+ reln = CreateFakeRelcacheEntry(xlrec-node);
+ visibilitymap_pin(reln, xlrec-block, vmbuffer);
+ /* Don't set the bit if replay has already passed this point.
*/
+ if (XLByteLE(lsn, PageGetLSN(BufferGetPage(vmbuffer
+ visibilitymap_set(reln, xlrec-block, lsn, vmbuffer);
... wouldn't it be better to do this unconditionally? Some later record will
unset it if needed, because all replay functions that clear the bit do so
without consulting the vm page LSN. On the other hand, the worst consequence
of the way you've done it is VACUUM visiting the page one more time to set the
bit.
Hmm, now that I look at it, I think this test is backwards too. I
think you might be right that it wouldn't hurt anything to go ahead
and set it, but I'm inclined to leave it in for now.
I think it's worth noting, perhaps based on your explanation in the
second-to-last paragraph of
http://archives.postgresql.org/message-id/BANLkTi=b7jvmq6fa_exlcygzuyv1u9a...@mail.gmail.com
that the answer may be incorrect again after the recheck. We don't care:
redundant clearings of the visibility bit are no problem.
I added a comment. See what you think.
Suppose you pin one VM page for a
Re: [HACKERS] ALTER TABLE lock strength reduction patch is unsafe
Robert Haas robertmh...@gmail.com writes: On Thu, Jun 16, 2011 at 6:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: I believe that this is fundamentally unavoidable so long as we use SnapshotNow to read catalogs --- which is something we've talked about changing, but it will require a pretty major RD effort to make it happen. Ouch. I wonder if we could avoid this anomaly by taking a throwaway MVCC snapshot at the beginning of each system catalog scan and using it just for the duration of that scan. If nothing that has touched the catalog commits while the scan is open, then this is logically equivalent to SnapshotNow. If something does commit in mid-scan, then we might not get the latest version of the row, but we should end up with exactly one. If it's not the latest one, we'll do the rebuild again upon seeing the next sinval message; in the meantime, the version we're using mustn't be too intolerably bad or it was an error not to use AccessExclusiveLock in the first place. Yeah, this seems like a possibly workable direction to explore. I like this better than what Simon is proposing, because it would fix the generic issue for all types of catalog SnapshotNow scans. IIUC, the problem with this approach is not correctness but performance. Taking snapshots is (currently) expensive. Yeah. After mulling it for awhile, what about this idea: we could redefine SnapshotNow as a snapshot type that includes a list of transactions-in-progress, somewhat like an MVCC snapshot, but we don't fill that list from the PGPROC array. Instead, while running a scan with SnapshotNow, anytime we determine that a particular XID is still-in-progress, we add that XID to the snapshot's list. Subsequently, the SnapshotNow code assumes that XID to be still-in-progress without consulting its actual state. We reset the XID list to empty when starting a new SnapshotNow scan. (We might be able to do so less often than that, like only when we do AcceptInvalidationMessages, but it's not clear to me that there's any real benefit in hanging onto the state longer.) This costs no performance; if anything it should be faster than now, because we'll be replacing expensive transaction state probes with relatively-cheap searches of an XID array that should almost always be quite short. With this approach, we would have no serialization anomalies from single transactions committing while a scan is in progress. There could be anomalies resulting from considering an earlier XID to be in-progress while a later XID is considered committed (because we didn't observe it until later). So far as I can see offhand, the impact of that would be that there might be multiple versions of a tuple that are considered good, but never that there would be no version considered good (so long as the other XIDs simply updated the tuple and didn't delete it). I think this would be all right, since the scan would just seize on the first good version it finds. As you argue above, if that's not good enough for our purposes then the updater(s) should have taken a stronger lock. I am not, however, particularly pleased with the idea of trying to make this work in 9.1. I still think that we should back off the attempt to reduce lock strength in 9.1, and take it up for 9.2. We need to be stabilizing 9.1 for release, not introducing new untested mechanisms in it. 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] 9.1beta2 / UNLOGGED + CHECK + INHERITS
On Fri, Jun 17, 2011 at 1:01 PM, Garick Hamlin gham...@isc.upenn.edu wrote: I wanted to see how much faster unlogged tables might be for an app I have, so as a quick test I did: s/CREATE TABLE/CREATE UNLOGGED TABLE/ to get some numbers. Which lead to a crash. Here is a trimmed down test case: $ cat unlog-test.sql CREATE UNLOGGED TABLE leases ( mac macaddr NOT NULL, ip inet NOT NULL, start_ts timestamp with time zone NOT NULL, end_ts timestamp with time zone NOT NULL, id bigint NOT NULL, truncated integer, router_ip inet, CONSTRAINT leases_check CHECK ((start_ts end_ts)) ); CREATE UNLOGGED TABLE closed_leases ( ) INHERITS (leases); CREATE UNLOGGED TABLE open_leases ( ) INHERITS (leases); ^D $ psql91 -U postgres postgres -c '\i unlog-test.sql' CREATE TABLE psql91:unlog-test.sql:13: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. psql91:unlog-test.sql:13: connection to server was lost Fixed, thanks for the report! -- 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] [GENERAL] Issues with generate_series using integer boundaries
Robert Haas robertmh...@gmail.com writes: So, I finally got around to look at this, and I think there is a simpler solution. When an overflow occurs while calculating the next value, that just means that the value we're about to return is the last one that should be generated. So we just need to frob the context state so that the next call will decide we're done. There are any of number of ways to do that; I just picked what looked like the easiest one. +1 for this solution. BTW, there was some mention of changing the timestamp versions of generate_series as well, but right offhand I'm not convinced that those need any change. I think you'll get overflow detection there automatically from the functions being used --- and if not, it's a bug in those functions, not in generate_series. 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] ALTER TABLE lock strength reduction patch is unsafe
On Fri, Jun 17, 2011 at 1:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Yeah, this seems like a possibly workable direction to explore. I like this better than what Simon is proposing, because it would fix the generic issue for all types of catalog SnapshotNow scans. It would also avoid adding more lock manager traffic which - as recently discussed on the relevant threads - turns out to be a significant performance bottleneck for us right now on some workloads. IIUC, the problem with this approach is not correctness but performance. Taking snapshots is (currently) expensive. Yeah. After mulling it for awhile, what about this idea: we could redefine SnapshotNow as a snapshot type that includes a list of transactions-in-progress, somewhat like an MVCC snapshot, but we don't fill that list from the PGPROC array. Instead, while running a scan with SnapshotNow, anytime we determine that a particular XID is still-in-progress, we add that XID to the snapshot's list. Subsequently, the SnapshotNow code assumes that XID to be still-in-progress without consulting its actual state. We reset the XID list to empty when starting a new SnapshotNow scan. (We might be able to do so less often than that, like only when we do AcceptInvalidationMessages, but it's not clear to me that there's any real benefit in hanging onto the state longer.) I think that something like that might possibly work, but what if the XID array overflows? A while back I proposed the idea of a lazy snapshot, by which I had in mind something similar to what you are suggesting but different in detail. Initially, when asked to acquire a snapshot, the snapshot manager acknowledges having taken one but does not actually do any work. As long as it sees only XIDs that either precede the oldest XID still running anywhere in the cluster, or have aborted, it can provide answers that are 100% correct without any further data. If it ever sees a newer, non-aborted XID then it goes and really gets an MVCC snapshot at that point, which it can uses from that point onward. I think that it might be possible to make such a system work even for MVCC snapshots generally, but even if not, it might be sufficient for this purpose. Unlike your approach, it would avoid both the see no rows and the see multiple rows cases, which might be thought an advantage. I am not, however, particularly pleased with the idea of trying to make this work in 9.1. I still think that we should back off the attempt to reduce lock strength in 9.1, and take it up for 9.2. We need to be stabilizing 9.1 for release, not introducing new untested mechanisms in it. I like this feature a lot, but it's hard to imagine that any of the fixes anyone has so far suggested can be implemented without collateral damage. Nor is there any certainty that this is the last bug. -- 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] - GSoC - snapshot materialized view (work-in-progress) patch
2010/7/12 Kevin Grittner kevin.gritt...@wicourts.gov: Pavel Barošbaro...@seznam.cz wrote: Dne 9.7.2010 21:33, Robert Haas napsal(a): Please add your patch here, so that it will be reviewed during the about-to-begin CommitFest. https://commitfest.postgresql.org/action/commitfest_view/open OK, but will you help me with that form? Do you think I can fill it like that? I'm not sure about few fields .. Name: Snapshot materialized views CommitFest Topic: [ Miscellaneous | SQL Features ] ??? SQL Features seems reasonable to me. Patch Status: Needs review Author: me Reviewers: You? Leave empty. Reviewers will sign up or be assigned. Commiters: who? That comes much later -- when the patch is complete and has a favorable review, then a committer will pick it up. and I quess fields 'Date Closed' and 'Message-ID for Original Patch' will be filled later. Date closed is only set for patches which are committed, returned with feedback (for a later CommitFest), or rejected. When you make an entry which references a post to the lists, you should fill in the Message-ID from the email header of the post. You may be able to get this from your email software as soon as you send the post; if not, you can find it on the archive page for the post. This topic hasn't been touched on in nearly a year, but is the work that's been done so far salvageable? I'm not sure what happens to GSoC project work that doesn't get finished in time. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 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
Re: [HACKERS] [GENERAL] Issues with generate_series using integer boundaries
On Fri, Jun 17, 2011 at 2:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: So, I finally got around to look at this, and I think there is a simpler solution. When an overflow occurs while calculating the next value, that just means that the value we're about to return is the last one that should be generated. So we just need to frob the context state so that the next call will decide we're done. There are any of number of ways to do that; I just picked what looked like the easiest one. +1 for this solution. BTW, there was some mention of changing the timestamp versions of generate_series as well, but right offhand I'm not convinced that those need any change. I think you'll get overflow detection there automatically from the functions being used --- and if not, it's a bug in those functions, not in generate_series. Maybe not, because those functions probably throw an error if an overflow is detected, and that's not really correct. By definition, the second generate_series() is the point at which we should stop generating, and that point has to be within the range of the underlying data type, by definition. So if an overflow occurs, that's just another way of saying that we've certainly gone past the stop point and needn't generate anything further. The error is an artifact of the method we've used to generate the next point. I'm not sure how much energy it's worth expending on that case. Using really large dates may be less common that using values that strain the range of a 4-byte integer. But it might at least be worth a TODO. -- 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] ALTER TABLE lock strength reduction patch is unsafe
Robert Haas robertmh...@gmail.com writes: On Fri, Jun 17, 2011 at 1:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Yeah. After mulling it for awhile, what about this idea: we could redefine SnapshotNow as a snapshot type that includes a list of transactions-in-progress, somewhat like an MVCC snapshot, but we don't fill that list from the PGPROC array. Instead, while running a scan with SnapshotNow, anytime we determine that a particular XID is still-in-progress, we add that XID to the snapshot's list. I think that something like that might possibly work, but what if the XID array overflows? Well, you repalloc it bigger. In either this idea or yours below, I fear SnapshotNow snaps will have to become dynamically-allocated structures instead of being simple references to a shared constant object. (This is because we can sometimes do a SnapshotNow scan when another one is already in progress, and we couldn't let the inner one change the outer one's state.) That's not really a performance problem; one more palloc to do a catalog scan is a non-issue. But it is likely to be a large notational change compared to what we've got now. A while back I proposed the idea of a lazy snapshot, by which I had in mind something similar to what you are suggesting but different in detail. Initially, when asked to acquire a snapshot, the snapshot manager acknowledges having taken one but does not actually do any work. As long as it sees only XIDs that either precede the oldest XID still running anywhere in the cluster, or have aborted, it can provide answers that are 100% correct without any further data. If it ever sees a newer, non-aborted XID then it goes and really gets an MVCC snapshot at that point, which it can uses from that point onward. I think that it might be possible to make such a system work even for MVCC snapshots generally, but even if not, it might be sufficient for this purpose. Unlike your approach, it would avoid both the see no rows and the see multiple rows cases, which might be thought an advantage. Hmm, yeah, I think this idea is probably better than mine, just because of the less dubious semantics. I don't see how you'd make it work for generic MVCC scans, because the behavior will be the database state as of some hard-to-predict time after the scan starts, which is not what we want for MVCC. But it ought to be fine for SnapshotNow. 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] [GENERAL] Issues with generate_series using integer boundaries
Robert Haas robertmh...@gmail.com writes: On Fri, Jun 17, 2011 at 2:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: BTW, there was some mention of changing the timestamp versions of generate_series as well, but right offhand I'm not convinced that those need any change. I think you'll get overflow detection there automatically from the functions being used --- and if not, it's a bug in those functions, not in generate_series. Maybe not, because those functions probably throw an error if an overflow is detected, and that's not really correct. Oh, good point. I'm not sure how much energy it's worth expending on that case. Using really large dates may be less common that using values that strain the range of a 4-byte integer. But it might at least be worth a TODO. Yeah, I can't get excited about it either; restructuring that code enough to avoid an error seems like a lot more work than the case is worth. Maybe someday somebody will hit the case in practice and then be motivated to work on it, but in the meantime ... 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] [v9.2] Start new timeline for PITR
On Fri, Jun 17, 2011 at 09:57:13AM -0500, Jaime Casanova wrote: On Fri, Jun 10, 2011 at 11:30 AM, David Fetter da...@fetter.org wrote: This also allows subsequent PITR to other times on the original timeline. Josh B pointed out that since this option to true conflicts with another option, having both should prevent recovery from even starting, and I'll work up a patch for this tonight or at latest tomorrow. Hi, Are you still working on this? should we expect a new patch? Yes, sorry about that. I let work get on top of me. Will try for a new patch this evening. 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] Latch implementation that wakes on postmaster death on both win32 and Unix
On 16 June 2011 16:30, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
This patch breaks silent_mode=on. In silent_mode, postmaster forks early on,
to detach from the controlling tty. It uses fork_process() for that, which
with patch closes the write end of the postmaster-alive pipe, but that's
wrong because the child becomes the postmaster process.
Attached patch revision addresses that issue. There is a thin
macro-based wrapper around fork_process(), depending on whether or not
it is desirable to ReleasePostmasterDeathWatchHandle() after forking.
All callers to fork_process() are unchanged.
On a stylistic note, the extern declaration in unix_latch.c is ugly,
extern declarations should be in header files.
Just an oversight.
Come to think of it, I feel
the Init- and ReleasePostmasterDeathWatchHandle() functions should go to
postmaster.c. postmaster_alive_fds[] and PostmasterHandle serve the same
purpose, declaration and initialization of both should be kept together,
perhaps by moving the initialization of PostmasterHandle into Init- and
ReleasePostmasterDeathWatchHandle().
I've removed the no coinciding wakeEvents comment that you objected
to (or clarified that other wakeEvents can coincide), and have
documented the fact that we make no guarantees about reporting all
events that caused a latch wake-up. We will report at least one
though.
I've moved Init- and ReleasePostmasterDeathWatchHandle() into postmaster.c .
I have to disagree with the idea of moving initialisation of
PostmasterHandle into InitPostmasterDeathWatchHandle(). Both Init-,
and Release- functions, which only exist on Unix builds, initialise
and subsequently release the watching handle. There's a symmetry to
it. If we created a win32 InitPostmasterDeathWatchHandle(), we'd have
no reason to create a win32 Release-, so the symmetry would be lost.
Also, PostmasterHandle does not exist for the express purpose of latch
clients monitoring postmaster death, unlike postmaster_alive_fds[] -
it existed before now. I guess I don't feel too strongly about it
though. It just doesn't seem like a maintainability win.
On 16 June 2011 15:49, Florian Pflug f...@phlo.org wrote:
I noticed to your patch doesn't seem to register a SIGIO handler, i.e.
it doesn't use async IO machinery (or rather a tiny part thereof) to
get asynchronously notified if the postmaster dies.
If that is on purpose, you can remove the fsetown() call, as it serves
no purpose without such a handler I think. Or, you might want to add
such a signal handler, and make it simply do kill(getpid(), SIGTERM).
It is on purpose - I'm not interested in asynchronous notification for
the time being at least, because it doesn't occur to me how we can
handle that failure usefully in an asynchronous fashion. Anyway, that
code has been simplified, and my intent clarified. Thanks.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index aa0b029..691ac42 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -10161,7 +10161,7 @@ retry:
/*
* Wait for more WAL to arrive, or timeout to be reached
*/
- WaitLatch(XLogCtl-recoveryWakeupLatch, 500L);
+ WaitLatch(XLogCtl-recoveryWakeupLatch, WL_LATCH_SET | WL_TIMEOUT, 500L);
ResetLatch(XLogCtl-recoveryWakeupLatch);
}
else
diff --git a/src/backend/port/unix_latch.c b/src/backend/port/unix_latch.c
index 6dae7c9..e88631d 100644
--- a/src/backend/port/unix_latch.c
+++ b/src/backend/port/unix_latch.c
@@ -93,7 +93,9 @@
#endif
#include miscadmin.h
+#include postmaster/postmaster.h
#include storage/latch.h
+#include storage/pmsignal.h
#include storage/shmem.h
/* Are we currently in WaitLatch? The signal handler would like to know. */
@@ -188,22 +190,26 @@ DisownLatch(volatile Latch *latch)
* backend-local latch initialized with InitLatch, or a shared latch
* associated with the current process by calling OwnLatch.
*
- * Returns 'true' if the latch was set, or 'false' if timeout was reached.
+ * Returns bit field indicating which condition(s) caused the wake-up.
+ * Note that there is guarantee that callers will have all wake-up conditions
+ * returned, but we will report at least one.
*/
-bool
-WaitLatch(volatile Latch *latch, long timeout)
+int
+WaitLatch(volatile Latch *latch, int wakeEvents, long timeout)
{
- return WaitLatchOrSocket(latch, PGINVALID_SOCKET, false, false, timeout) 0;
+ return WaitLatchOrSocket(latch, wakeEvents, PGINVALID_SOCKET, timeout);
}
/*
* Like WaitLatch, but will also return when there's data available in
- * 'sock' for reading or writing. Returns 0 if timeout was reached,
- * 1 if the latch was set, 2 if the socket became readable or writable.
+ * 'sock' for reading or writing.
+ *
+ * Returns bit field indicating which condition(s) caused the
Re: [HACKERS] ALTER TABLE lock strength reduction patch is unsafe
On Fri, Jun 17, 2011 at 7:24 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Jun 17, 2011 at 1:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Yeah, this seems like a possibly workable direction to explore. I like this better than what Simon is proposing, because it would fix the generic issue for all types of catalog SnapshotNow scans. It would also avoid adding more lock manager traffic which - as recently discussed on the relevant threads - turns out to be a significant performance bottleneck for us right now on some workloads. I am not, however, particularly pleased with the idea of trying to make this work in 9.1. I still think that we should back off the attempt to reduce lock strength in 9.1, and take it up for 9.2. We need to be stabilizing 9.1 for release, not introducing new untested mechanisms in it. I like this feature a lot, but it's hard to imagine that any of the fixes anyone has so far suggested can be implemented without collateral damage. Nor is there any certainty that this is the last bug. Not so. The extra locking would only occur on the first lock acquisition after DDL operations occur. If that was common then your other performance patch would not be an effective optimisation. There is no additional locking from what I've proposed in the common code path - that's why we have a relcache. Any effects from the additional locking will only be felt by people issuing a stream of DDL statements against a table. Even assuming there are some effects of real note. So there is no collateral damage and additional locking is a viable solution for 9.1. It's possible that we may have a better solution in 9.2+ but then we've said that before and have it never happen, many times. Having spent a few hours mulling through this, I think there is a reasonable solution for 9.1 and I continue to work on it. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER TABLE lock strength reduction patch is unsafe
On Fri, Jun 17, 2011 at 2:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: Hmm, yeah, I think this idea is probably better than mine, just because of the less dubious semantics. I don't see how you'd make it work for generic MVCC scans, because the behavior will be the database state as of some hard-to-predict time after the scan starts, which is not what we want for MVCC. But it ought to be fine for SnapshotNow. Department of second thoughts: I think I see a problem. Suppose we have a tuple that has not been updated for a long time. Its XMIN is committed and all-visible, and its XMAX is invalid. As we're scanning the table (transaction T1), we see that tuple and say, oh, it's visible. Now, another transaction (T2) begins, updates the tuple, and commits. Our scan then reaches the page where the new tuple is located, and says, oh, this is recent, I'd better take a real snapshot. Of course, the new snapshot can see the new version of the tuple, too. Of course, if T1 had taken its snapshot before starting the scan, the second tuple would have been invisible. But since we didn't take it until later, after T2 had already committed, we see a duplicate. That's still no worse than your idea, which rests on the theory that duplicates don't matter anyway, but the case for it being better is a lot thinner. I'd sure prefer something that had less crazy semantics than either of these ideas, if we can think of something. -- 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] ALTER TABLE lock strength reduction patch is unsafe
On Fri, Jun 17, 2011 at 3:08 PM, Simon Riggs si...@2ndquadrant.com wrote: Not so. The extra locking would only occur on the first lock acquisition after DDL operations occur. If that was common then your other performance patch would not be an effective optimisation. There is no additional locking from what I've proposed in the common code path - that's why we have a relcache. The extra locking would also occur when *initially* building relcache entries. In other words, this would increase - likely quite significantly - the overhead of backend startup. It's not going to be sufficient to do this just for pg_class; I think you'll have to do it for pg_attribute, pg_attrdef, pg_constraint, pg_index, pg_trigger, pg_rewrite, and maybe a few others I'm not thinking of right now. -- 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] possible connection leak in dblink?
On ons, 2011-06-15 at 11:41 +0900, Fujii Masao wrote: ISTM that the root problem is that dblink_send_query calls DBLINK_GET_CONN though it doesn't accept the connection string as an argument. Since the first argument in dblink_send_query must be the connection name, dblink_send_query should call DBLINK_GET_NAMED_CONN instead. The variable 'freeconn' is used only when DBLINK_GET_CONN is called. So, if dblink_send_query uses DBLINK_GET_NAMED_CONN instead, the variable 'freeconn' is no longer necessary. The similar problem exists in dblink_get_result and dblink_record_internal. Attached patch fixes those problems. Is this a bug fix that should be backpatched? -- 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] ALTER TABLE lock strength reduction patch is unsafe
On Fri, Jun 17, 2011 at 8:15 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Jun 17, 2011 at 3:08 PM, Simon Riggs si...@2ndquadrant.com wrote: Not so. The extra locking would only occur on the first lock acquisition after DDL operations occur. If that was common then your other performance patch would not be an effective optimisation. There is no additional locking from what I've proposed in the common code path - that's why we have a relcache. The extra locking would also occur when *initially* building relcache entries. In other words, this would increase - likely quite significantly - the overhead of backend startup. It's not going to be sufficient to do this just for pg_class; I think you'll have to do it for pg_attribute, pg_attrdef, pg_constraint, pg_index, pg_trigger, pg_rewrite, and maybe a few others I'm not thinking of right now. Nothing you say here is accurate, regrettably. The extra locking would be one call to the lock manager per relation. Taken in shared mode, so it doesn't block. I see no reason at all to have separate locks for each catalog table, since it's the relation lock that is the top level lock. Locking is a very well known solution to such problems. We use it everywhere and we can use it here, and now. I think you'd better wait to see the patch. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER TABLE lock strength reduction patch is unsafe
Robert Haas robertmh...@gmail.com writes: Department of second thoughts: I think I see a problem. Um, yeah, so that doesn't really work any better than my idea. On further reflection, there's a problem at a higher level than this anyway. Even if we can get a single SnapshotNow scan to produce guaranteed-self-consistent results, that doesn't ensure consistency between the results of scans occurring serially. An example here is ALTER COLUMN DROP DEFAULT, which is currently imagined to impact only writers. However, suppose that a concurrent relcache load fetches the pg_attribute row, notes that it has atthasdef = true, and then the ALTER commits before we start to scan pg_attrdef. The consistency checks in AttrDefaultFetch() will complain about a missing pg_attrdef entry, and rightly so. We could lobotomize those checks, but it doesn't feel right to do so; and anyway there may be other cases that are harder to kluge up. So really we need consistency across *at least* one entire relcache load cycle. We could maybe arrange to take an MVCC snap (or some lighter weight version of that) at the start, and use that for all the resulting scans, but I think that would be notationally messy. It's not clear that it'd solve everything anyhow. There are parts of a relcache entry that we fetch only on-demand, so they are typically loaded later than the core items, and probably couldn't use the same snapshot. Worse, there are lots of places where we assume that use of catcache entries or direct examination of the catalogs will yield results consistent with the relcache. I suspect these latter problems will impact Simon's idea as well. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER TABLE lock strength reduction patch is unsafe
On Fri, Jun 17, 2011 at 3:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Department of second thoughts: I think I see a problem. Um, yeah, so that doesn't really work any better than my idea. On further reflection, there's a problem at a higher level than this anyway. Even if we can get a single SnapshotNow scan to produce guaranteed-self-consistent results, that doesn't ensure consistency between the results of scans occurring serially. An example here is ALTER COLUMN DROP DEFAULT, which is currently imagined to impact only writers. However, suppose that a concurrent relcache load fetches the pg_attribute row, notes that it has atthasdef = true, and then the ALTER commits before we start to scan pg_attrdef. The consistency checks in AttrDefaultFetch() will complain about a missing pg_attrdef entry, and rightly so. We could lobotomize those checks, but it doesn't feel right to do so; and anyway there may be other cases that are harder to kluge up. So really we need consistency across *at least* one entire relcache load cycle. We could maybe arrange to take an MVCC snap (or some lighter weight version of that) at the start, and use that for all the resulting scans, but I think that would be notationally messy. It's not clear that it'd solve everything anyhow. There are parts of a relcache entry that we fetch only on-demand, so they are typically loaded later than the core items, and probably couldn't use the same snapshot. Worse, there are lots of places where we assume that use of catcache entries or direct examination of the catalogs will yield results consistent with the relcache. I suspect these latter problems will impact Simon's idea as well. I suspect we're going to be told that they don't. I suspect I'm not going to believe it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
On ons, 2011-06-15 at 17:50 -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Peter Eisentraut wrote: On non-Windows servers you could get this even safer by disabling the TCP/IP socket altogether, and placing the Unix-domain socket in a private temporary directory. The port wouldn't actually matter then. Yes, it would be nice to just create the socket in the current directory. The fact it doesn't work on Windows would cause our docs to have to differ for Windows, which seems unfortunate. It still wouldn't be bulletproof against someone running as the postgres user, so probably not worth the trouble. But the postgres user would normally be the DBA itself, so it'd be his own fault. I don't see how you can easily make any process safe from interference by the same user account. -- 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] possible connection leak in dblink?
Peter Eisentraut pete...@gmx.net writes: Is this a bug fix that should be backpatched? I pinged Joe Conway about this. He is jetlagged from a trip to the Far East but promised to take care of it soon. I think we can wait for his review. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
Peter Eisentraut pete...@gmx.net writes: On ons, 2011-06-15 at 17:50 -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Peter Eisentraut wrote: On non-Windows servers you could get this even safer by disabling the TCP/IP socket altogether, and placing the Unix-domain socket in a private temporary directory. The port wouldn't actually matter then. Yes, it would be nice to just create the socket in the current directory. The fact it doesn't work on Windows would cause our docs to have to differ for Windows, which seems unfortunate. It still wouldn't be bulletproof against someone running as the postgres user, so probably not worth the trouble. But the postgres user would normally be the DBA itself, so it'd be his own fault. I don't see how you can easily make any process safe from interference by the same user account. Well, the point here is that it's not bulletproof, it's just making it incrementally harder to connect accidentally. Given that Windows wouldn't be covered, I don't see that it's worth the trouble compared to just switching to a nondefault port number. (Am I wrong to think that Windows users are more likely to mess up here?) 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] ALTER TABLE lock strength reduction patch is unsafe
Excerpts from Tom Lane's message of vie jun 17 13:22:40 -0400 2011: With this approach, we would have no serialization anomalies from single transactions committing while a scan is in progress. There could be anomalies resulting from considering an earlier XID to be in-progress while a later XID is considered committed (because we didn't observe it until later). So far as I can see offhand, the impact of that would be that there might be multiple versions of a tuple that are considered good, but never that there would be no version considered good (so long as the other XIDs simply updated the tuple and didn't delete it). I think this would be all right, since the scan would just seize on the first good version it finds. As you argue above, if that's not good enough for our purposes then the updater(s) should have taken a stronger lock. Hmm, would there be a problem if a scan on catalog A yields results from supposedly-running transaction X but another scan on catalog B yields result from transaction Y? (X != Y) For example, a scan on pg_class says that there are N triggers but scanning pg_trigger says N-1? -- Á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] ALTER TABLE lock strength reduction patch is unsafe
Robert Haas robertmh...@gmail.com writes: I like this feature a lot, but it's hard to imagine that any of the fixes anyone has so far suggested can be implemented without collateral damage. Nor is there any certainty that this is the last bug. And in fact, here's something else to worry about: consider pg_dump. pg_dump is pretty heavily reliant on backend catalog-interpretation code (such as ruleutils.c) that mostly runs on SnapshotNow time. But it also does a fair amount of work on the basis of its own inspection of the catalogs, which is done according to the serializable snapshot it gets at the beginning of the dump run. If these two views of a table's schema aren't consistent, you might get a pg_dump error, but it's at least as likely that you'll get a silently incorrect dump. pg_dump tries to minimize the risk by taking AccessShareLock right away on each table it's going to dump. This is not perfect but it at least results in a narrow window for conflicting table changes to occur. However, that strategy has been blown out of the water by the ALTER TABLE lock strength reduction. There is now a *very* wide window for concurrent ALTERs to occur and possibly break the dump results. As far as I can see, the only simple way to return pg_dump to its previous level of safety while retaining this patch is to make it take ShareUpdateExclusiveLocks, so that it will still block all forms of ALTER TABLE. This is rather unpleasant, since it will also block autovacuum for the duration of the dump. In the long run, we really ought to fix things so that ruleutils.c runs on the transaction snapshot, but that's a massive rewrite that is certainly not getting done for 9.1, and will likely result in considerable code bloat :-(. (BTW, I just noticed that dumpSchema does a pretty fair amount of work before it gets around to calling getTables, which is where said locks get taken. Seems like we'd better rearrange the order of operations there...) 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] ALTER TABLE lock strength reduction patch is unsafe
Alvaro Herrera alvhe...@commandprompt.com writes: Hmm, would there be a problem if a scan on catalog A yields results from supposedly-running transaction X but another scan on catalog B yields result from transaction Y? (X != Y) For example, a scan on pg_class says that there are N triggers but scanning pg_trigger says N-1? Yeah, I came to that same conclusion downthread. 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] ALTER TABLE lock strength reduction patch is unsafe
Excerpts from Tom Lane's message of vie jun 17 17:08:25 -0400 2011: Alvaro Herrera alvhe...@commandprompt.com writes: Hmm, would there be a problem if a scan on catalog A yields results from supposedly-running transaction X but another scan on catalog B yields result from transaction Y? (X != Y) For example, a scan on pg_class says that there are N triggers but scanning pg_trigger says N-1? Yeah, I came to that same conclusion downthread. Something is seriously wrong with my email :-( -- Á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] ALTER TABLE lock strength reduction patch is unsafe
On Fri, Jun 17, 2011 at 5:02 PM, Tom Lane t...@sss.pgh.pa.us wrote: As far as I can see, the only simple way to return pg_dump to its previous level of safety while retaining this patch is to make it take ShareUpdateExclusiveLocks, so that it will still block all forms of ALTER TABLE. This is rather unpleasant, since it will also block autovacuum for the duration of the dump. I have been thinking for a while now that it would be sensible to make vacuum use a different lock type, much as we do for relation extension. DROP TABLE and CLUSTER and at least some forms of ALTER TABLE and maybe a few other things like CREATE INDEX would need to grab that lock in addition to the ones they already acquire, but a whole lot of other things wouldn't. In particular, it's currently not possible to lock a table against SELECT without also locking it against VACUUM - and booting off any auto-vacuum worker that happens to already be processing it. If you imagine a large table with a bunch of short-duration exclusive locks, it's not too hard to see how you can end up with VACUUM starvation. But that's not something I want to do in 9.1, and I doubt it would completely solve this problem anyway. -- 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
[HACKERS] Moved WIP patches
All, For easy visibility, I've moved all WIP patches to their own section in the current commitfest, at the bottom of the list of pending patches. Hopefully this way there will be less confusion about what needs to be committed and what doesn't. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER TABLE lock strength reduction patch is unsafe
Robert Haas robertmh...@gmail.com writes: I have been thinking for a while now that it would be sensible to make vacuum use a different lock type, much as we do for relation extension. Hmm. I had just been toying with the idea of introducing a new user-visible locking level to allow separation of anti-vacuum locks from anti-schema-alteration locks. But I think you're probably right that it could be done as a specialized LockTag. That would make it not easily user-accessible, but it's hard to think of reasons for users to lock out vacuum anyway, unless they want to lock out everything via AccessExclusiveLock. ... In particular, it's currently not possible to lock a table against SELECT without also locking it against VACUUM Well, it still wouldn't be, since AccessExclusiveLock certainly had better lock out vacuum. As said above, I think the important thing is to distinguish vacuum from schema changes. But that's not something I want to do in 9.1, Definitely. 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
[HACKERS] SSI tuning points
The attached patch addresses one of the open non-blockers for beta3. These are tuning points which emerged in testing. The first is more likely to be helpful. The second may be very important in a few types of transaction mixes, but I threw in a lot of weasel words and qualifiers because someone could easily try this to bring down the transaction retry rate, but suffer a net loss in throughput because less efficient plans could be chosen. I hope I made that point in a reasonable fashion, although I'm certainly open to suggestions for better wording. -Kevin *** a/doc/src/sgml/mvcc.sgml --- b/doc/src/sgml/mvcc.sgml *** *** 658,663 ERROR: could not serialize access due to read/write dependencies among transact --- 658,680 protections automatically provided by Serializable transactions. /para /listitem + listitem + para +If you are seeing a lot of serialization failures because multiple +page locks are being combined into relation locks, you might want to +increase xref linkend=guc-max-pred-locks-per-transaction. + /para + /listitem + listitem + para +If you are experiencing a lot of serialization failures due to +table-scan plans being used, you might want to try reducing +xref linkend=guc-random-page-cost and/or increasing +xref linkend=guc-cpu-tuple-cost. Be sure to weigh any decrease +in transaction rollbacks and restarts against any overall change in +query execution time. + /para + /listitem /itemizedlist /para -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another swing at JSON
On Fri, Jun 17, 2011 at 2:29 AM, Bernd Helmle maili...@oopsware.de wrote: Joseph, are you able to remove the compatibility code for this CF? Done. Note that this module builds, tests, and installs successfully with USE_PGXS=1. However, building without USE_PGXS=1 produces the following: CREATE EXTENSION json; ERROR: incompatible library /usr/lib/postgresql/json.so: version mismatch DETAIL: Server is version 9.1, library is version 9.2. Similar problems occur with a couple other modules I tried (hstore, intarray). Joey json-contrib-no-compat-20110617.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER TABLE lock strength reduction patch is unsafe
On Thu, Jun 16, 2011 at 6:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: 4. Backend #2 visits the new, about-to-be-committed version of pgbench_accounts' pg_class row just before backend #3 commits. It sees the row as not good and keeps scanning. By the time it reaches the previous version of the row, however, backend #3 *has* committed. So that version isn't good according to SnapshotNow either. thinks some more Why isn't this a danger for every pg_class update? For example, it would seem that if VACUUM updates relpages/reltuples, it would be prone to this same hazard. -- 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] ALTER TABLE lock strength reduction patch is unsafe
Robert Haas robertmh...@gmail.com writes: On Thu, Jun 16, 2011 at 6:54 PM, Tom Lane t...@sss.pgh.pa.us wrote: 4. Backend #2 visits the new, about-to-be-committed version of pgbench_accounts' pg_class row just before backend #3 commits. It sees the row as not good and keeps scanning. By the time it reaches the previous version of the row, however, backend #3 *has* committed. So that version isn't good according to SnapshotNow either. thinks some more Why isn't this a danger for every pg_class update? For example, it would seem that if VACUUM updates relpages/reltuples, it would be prone to this same hazard. VACUUM does that with an in-place, nontransactional update. But yes, this is a risk for every transactional catalog update. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: On ons, 2011-06-15 at 17:50 -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Peter Eisentraut wrote: On non-Windows servers you could get this even safer by disabling the TCP/IP socket altogether, and placing the Unix-domain socket in a private temporary directory. The port wouldn't actually matter then. Yes, it would be nice to just create the socket in the current directory. The fact it doesn't work on Windows would cause our docs to have to differ for Windows, which seems unfortunate. It still wouldn't be bulletproof against someone running as the postgres user, so probably not worth the trouble. But the postgres user would normally be the DBA itself, so it'd be his own fault. I don't see how you can easily make any process safe from interference by the same user account. Well, the point here is that it's not bulletproof, it's just making it incrementally harder to connect accidentally. Given that Windows wouldn't be covered, I don't see that it's worth the trouble compared to just switching to a nondefault port number. (Am I wrong to think that Windows users are more likely to mess up here?) Windows is not covered if we shut off TCP and just use unix domain sockets --- that is the only Windows-specific part I know. Windows does work with the non-default port, and with writing the password to a file. (FYI, I think we would need to use PGPASSWORD for the password file option, and we don't recommend PGPASSWORD use in our docs.) PG 9.1 already has code to lock out non-super users, but only for 9.1+ servers --- writing a password to a file would have the same only 9.2+ restriction. Non-default port numbers would work for all PG versions because that is tied to the pg_upgrade binary. Again, everything is easy to do --- we just have to decide. I hoped my listing 5 items would unleash a flood of votes --- no such luck. -- 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] procpid?
On Jun 16, 2011, at 9:31 AM, Greg Smith wrote: -A case could be made for making some of these state fields null, instead true or false, in situations where the session is not visible. If you don't have rights to see the connection activity, setting idle, idle_transaction, and active all to null may be the right thing to do. More future bikeshedding is likely on this part, once an initial patch is ready for testing. I'd want to get some specific tests against the common monitoring goals of tools like check_postgres and the Munin plug-in to see which implementation makes more sense for them as input on that. ISTM this should be driven by what data we actually expose. If we're willing to expose actual information for idle, idle_transaction and waiting for backends that you don't have permission to see the query for, then we should expose the actual information (I personally think this would be useful). OTOH, if we are not willing to expose that information, then we should certainly set those fields to null instead of some default value. -- 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
[HACKERS] Fwd: Keywords in pg_hba.conf should be field-specific
On 16 June 2011 00:22, Pavel Stehule pavel.steh...@gmail.com wrote: I try to apply your patch, but it is finished with some failed hinks. Please, can you refresh your patch Hi Pavel, Thanks for taking a look. I have attached v2 of the patch, as against current HEAD. I've also added the new patch to the CF app. I look forward to your comments. Cheers, BJ hba-keywords-v2.diff.bz2 Description: BZip2 compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Don't use cp -i in the example WAL archive_command.
Wow, this is the first I am hearing GNU cp -i can return zero exit if it doesn't do the copy. I tested this on Ubuntu 10.04 using cp 7.4 and got: $ touch x y $ cp -i x y; echo $? cp: overwrite `y'? n 0 I see the same on my anchent BSD/OS machine too: $ touch x y $ cp -i x y; echo $? overwrite y? n 0 Were we expecting an error if the file already existed? Assuming that, we should assume the file will always exist so basically archiving will never progress. Is this what we want? I just wasn't aware we were expecting an already-existing this to be an error --- I thought we just didn't want to overwrite it. --- Tom Lane wrote: Don't use cp -i in the example WAL archive_command. This is a dangerous example to provide because on machines with GNU cp, it will silently do the wrong thing and risk archive corruption. Worse, during the 9.0 cycle somebody improved the discussion by removing the warning that used to be there about that, and instead leaving the impression that the command would work as desired on most Unixen. It doesn't. Try to rectify the damage by providing an example that is safe most everywhere, and then noting that you can try cp -i if you want but you'd better test that. In back-patching this to all supported branches, I also added an example command for Windows, which wasn't provided before 9.0. Branch -- REL8_3_STABLE Details --- http://git.postgresql.org/pg/commitdiff/23843d242f00e6597af91d4f4d08b655b2b362ba Modified Files -- doc/src/sgml/backup.sgml | 25 ++--- 1 files changed, 14 insertions(+), 11 deletions(-) -- Sent via pgsql-committers mailing list (pgsql-committ...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-committers -- 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] minor patch submission: CREATE CAST ... AS EXPLICIT
On 22 May 2011 07:27, Fabien COELHO coe...@cri.ensmp.fr wrote: Hello Tom, Add AS EXPLICIT to CREATE CAST This gives a name to the default case of CREATE CAST, which creates a cast which must be explicitely invoked. I'm not sure this is a good idea. The CREATE CAST syntax is in the SQL standard, and this isn't it. Now I realize that we've extended that statement already to cover some functionality that's not in the standard, but that doesn't mean we should create unnecessarily nonstandard syntax for cases that are in the standard. The standard provides only one case, so CAST is good enough a name. Once you start creating alternatives with distinct semantics, then it helps to give the initial one a name as well to be able to discuss them with something else that the remaining case, or when there is no option, especially as there is something to discuss. Note that the standard is still supported just the same, and the documentation already underlines that AS * stuff is a pg extension, nothing is really changed. Maybe the documentation could be clearer about where the standard stops and where extensions start, even now without an AS EXPLICIT clause. If a commercial vendor did that, wouldn't you castigate them for trying to create vendor lock-in? I'm more concerned with explaining things to students, and its good to have words and logic for that. With respect to the standard, it seems good enough to me if (1) the standard is well supported and (2) the documentation clearly says which parts are extensions. If you really want to keep to the standard, then do not offer any extension. Moreover, this stuff is really minor compared to RULEs or many other things specifics to pg, and the lock-in is light, you just have to remove AS EXPLICIT to get away, no big deal. Hi Fabien, I'm taking a look at this patch for the commitfest. On first reading of the patch, it looked pretty sensible to me, but I had some trouble applying it to HEAD: error: patch failed: doc/src/sgml/ref/create_cast.sgml:20 error: doc/src/sgml/ref/create_cast.sgml: patch does not apply error: patch failed: src/backend/parser/gram.y:499 error: src/backend/parser/gram.y: patch does not apply error: patch failed: src/include/parser/kwlist.h:148 error: src/include/parser/kwlist.h: patch does not apply error: patch failed: src/test/regress/expected/create_cast.out:27 error: src/test/regress/expected/create_cast.out: patch does not apply error: patch failed: src/test/regress/sql/create_cast.sql:27 error: src/test/regress/sql/create_cast.sql: patch does not apply Perhaps the patch could use a refresh? Also, for what it's worth, I buy into the argument for adding AS EXPLICIT. This stuff is all an extension to the SQL standard already; it might as well have a well-rounded syntax. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
On 06/17/2011 06:59 PM, Bruce Momjian wrote: (FYI, I think we would need to use PGPASSWORD for the password file option, and we don't recommend PGPASSWORD use in our docs.) er what? did you mean PGPASSFILE? 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] [v9.2] Start new timeline for PITR
On Fri, Jun 17, 2011 at 1:54 PM, David Fetter da...@fetter.org wrote: On Fri, Jun 17, 2011 at 09:57:13AM -0500, Jaime Casanova wrote: Are you still working on this? should we expect a new patch? Yes, sorry about that. I let work get on top of me. Will try for a new patch this evening. ok... i will wait it to review... just in advance, i really don't like this name create_new_timeline... it will drive confusion -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade using appname to lock out other users
Andrew Dunstan wrote: On 06/17/2011 06:59 PM, Bruce Momjian wrote: (FYI, I think we would need to use PGPASSWORD for the password file option, and we don't recommend PGPASSWORD use in our docs.) er what? did you mean PGPASSFILE? I meant the PGPASSWORD environment variable: indexterm primaryenvarPGPASSWORD/envar/primary /indexterm envarPGPASSWORD/envar behaves the same as the xref linkend=libpq-connect-password connection parameter. Use of this environment variable is not recommended for security reasons, as some operating systems allow non-root users to see process environment variables via applicationps/; instead consider using the filename~/.pgpass/ file (see xref linkend=libpq-pgpass). The only other way to do this is to pass it on the command line, but some options don't allow that (pg_ctl), and PGPASSFILE is going to require me to create a dummy .pgpass password file in a valid format and use that. -- 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] Re: [COMMITTERS] pgsql: Don't use cp -i in the example WAL archive_command.
Bruce Momjian wrote: Wow, this is the first I am hearing GNU cp -i can return zero exit if it doesn't do the copy. I tested this on Ubuntu 10.04 using cp 7.4 and got: $ touch x y $ cp -i x y; echo $? cp: overwrite `y'? n 0 I see the same on my anchent BSD/OS machine too: $ touch x y $ cp -i x y; echo $? overwrite y? n 0 Were we expecting an error if the file already existed? Assuming that, we should assume the file will always exist so basically archiving will never progress. Is this what we want? I just wasn't aware we were expecting an already-existing this to be an error --- I thought we just didn't want to overwrite it. I tested on FreeBSD 7.4 and saw a 1 error return: $ touch x y $ cp -i x y; echo $? overwrite y? (y/n [n]) n not overwritten 1 -- 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] possible connection leak in dblink?
On 06/17/2011 01:05 PM, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Is this a bug fix that should be backpatched? I pinged Joe Conway about this. He is jetlagged from a trip to the Far East but promised to take care of it soon. I think we can wait for his review. Sorry for the delay. I'm finally caught up on most of my obligations, and have plowed through the 1500 or so pgsql mailing list messages that I was behind. But if everyone is OK with it I would like to aim to commit a fix mid next week, because I still have to get through my daughter's high school graduation tomorrow, and an out of state funeral for my father-in-law Sunday/Monday. That said, I really would like to commit this myself, as I have yet to be brave enough to commit anything under git :-( Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Fwd: Keywords in pg_hba.conf should be field-specific
Excerpts from Brendan Jurd's message of vie jun 17 19:31:41 -0400 2011: On 16 June 2011 00:22, Pavel Stehule pavel.steh...@gmail.com wrote: I try to apply your patch, but it is finished with some failed hinks. Please, can you refresh your patch Hi Pavel, Thanks for taking a look. I have attached v2 of the patch, as against current HEAD. I've also added the new patch to the CF app. I look forward to your comments. Is this really a WIP patch? I'm playing a bit with it currently, seems fairly sane. -- Á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] Fwd: Keywords in pg_hba.conf should be field-specific
On 18 June 2011 13:43, Alvaro Herrera alvhe...@commandprompt.com wrote: Is this really a WIP patch? I'm playing a bit with it currently, seems fairly sane. In this case, the WIP designation is meant to convey warning: only casual testing has beeen done. I tried it out with various permutations of pg_hba.conf, and it worked as advertised in those tests, but I have not made any attempt to formulate a more rigorous testing regimen. In particular I haven't tested that the more exotic authentication methods still work properly, and I can't recall whether I tested recursive file inclusion and group membership. Is that a wrongful use of the WIP designation? Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
