Re: [HACKERS] [PATCH] Fix leaky VIEWs for RLS
(2010/06/08 11:15), Robert Haas wrote: 2010/6/7 KaiGai Koheikai...@ak.jp.nec.com: Our headache is on functions categorized to middle-threat. It enables to leak the given arguments using error messages. Here are several ideas, but they have good and bad points. I think we are altogether off in the weeds here. We ought to start with an implementation that pushes nothing down, and then try to figure out how much we can relax that without too much compromising security. The attached patch tries to prevent pushing down anything into subqueries from outside of them. The distribute_qual_to_rels() tries to distribute the given qualifier into a certain scanning-plan based on the dependency of qualifier. E.g) SELECT * FROM (SELECT * FROM t1 JOIN t2 ON t1.a = t2.x WHERE f_policy(t1.a)) WHERE f_user(t2.x); In this case, f_user() function depends on only t2 table, so it is reasonable to attach on the scanning plan of t2 from perspective of performance. However, f_user() may have a side-effect which writes arguments into somewhere. If here is such a possibility, f_user() should not be called before the joined tuples being filtered by f_policy(). In the case when we can ensure all functions within the qualifier are enough trustable, we don't need to prevent them to be pushed down. But the algorithm to determine it is under discussion. So, right now, we prevent all the possible pushing down. Example.1) CREATE VIEW v1 AS SELECT * FROM t1 JOIN t2 ON a = x WHERE f_policy(a); SELECT * FROM v1 WHERE f_malicious(b); * without this patch postgres=# EXPLAIN SELECT * FROM v1 WHERE f_malicious(b); QUERY PLAN --- Hash Join (cost=639.01..667.29 rows=137 width=72) Hash Cond: (t2.x = t1.a) - Seq Scan on t2 (cost=0.00..22.30 rows=1230 width=36) - Hash (cost=637.30..637.30 rows=137 width=36) - Seq Scan on t1 (cost=0.00..637.30 rows=137 width=36) Filter: (f_policy(a) AND f_malicious(b)) (6 rows) * with this patch postgres=# EXPLAIN SELECT * FROM v1 WHERE f_malicious(b); QUERY PLAN --- Hash Join (cost=334.93..468.44 rows=137 width=72) Hash Cond: (t2.x = t1.a) Join Filter: f_malicious(t1.b) - Seq Scan on t2 (cost=0.00..22.30 rows=1230 width=36) - Hash (cost=329.80..329.80 rows=410 width=36) - Seq Scan on t1 (cost=0.00..329.80 rows=410 width=36) Filter: f_policy(a) (7 rows) It prevents to push down f_malicious() inside of the join loop. Example.2) CREATE VIEW v1 AS SELECT * FROM t1 JOIN t2 ON a = x WHERE f_policy(a); SELECT * FROM v1 JOIN t3 ON v1.a=t3.s WHERE f_malicious(b); * without this patch postgres=# EXPLAIN SELECT * FROM v1 JOIN t3 ON v1.a=t3.s WHERE f_malicious(b); QUERY PLAN --- Hash Join (cost=669.01..697.29 rows=137 width=108) Hash Cond: (t3.s = t1.a) - Seq Scan on t3 (cost=0.00..22.30 rows=1230 width=36) - Hash (cost=667.29..667.29 rows=137 width=72) - Hash Join (cost=639.01..667.29 rows=137 width=72) Hash Cond: (t2.x = t1.a) - Seq Scan on t2 (cost=0.00..22.30 rows=1230 width=36) - Hash (cost=637.30..637.30 rows=137 width=36) - Seq Scan on t1 (cost=0.00..637.30 rows=137 width=36) Filter: (f_policy(a) AND f_malicious(b)) (10 rows) * with this patch postgres=# EXPLAIN SELECT * FROM v1 JOIN t3 ON v1.a=t3.s WHERE f_malicious(b); QUERY PLAN --- Hash Join (cost=470.15..498.43 rows=137 width=108) Hash Cond: (t3.s = t1.a) - Seq Scan on t3 (cost=0.00..22.30 rows=1230 width=36) - Hash (cost=468.44..468.44 rows=137 width=72) - Hash Join (cost=334.93..468.44 rows=137 width=72) Hash Cond: (t2.x = t1.a) Join Filter: f_malicious(t1.b) - Seq Scan on t2 (cost=0.00..22.30 rows=1230 width=36) - Hash (cost=329.80..329.80 rows=410 width=36) - Seq Scan on t1 (cost=0.00..329.80 rows=410 width=36) Filter: f_policy(a) (11 rows) It also prevents f_malisious() to be pushed down into the join loop within view, but we can push it down into same level of the query. Please note that it specially handles equality operator at the bottom half of the distribute_qual_to_rels(), so this patch does not care about these cases. However, I'm not in hustle to
Re: [HACKERS] How about closing some Open Items?
On Tue, 2010-06-08 at 15:46 -0400, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: Let's get 9.0 out the door, hey? What we actually need is some testing effort. The lack of bug reports against Hot Standby, in particular, is proof positive that no meaningful testing is happening. Absence of evidence is not evidence of absence. Please don't confuse things by claiming proof positive does, or even can, exist here. A lack of bugs usually indicates there are no bugs in the areas being tested. Which can also mean the areas being tested don't cover the full code or that tests of anything are not being performed. I raised the topic of how to increase the amount of testing earlier; my proposed solution was more betas before we go live. We just guaranteed even less testing for HS by not fixing things for Beta2. Would you like me to patch, or are you still intending to look at max_standby_delay yourself? (If you think it means HS is bug-free, I have a nice bridge I'd like to interest you in.) How should we proceed? -- Simon Riggs www.2ndQuadrant.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] hstore == and deprecate =
2010/6/8 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: p.s. I hope so in 9.1 will be complete hstore module marked as deprecated Really? And replaced with what? And why wouldn't the replacement use the same operator names? We talked about integrated hash tables support. regards Pavel 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] LLVM / clang
On Jun 8, 2010, at 12:12 , P. Caillaud wrote: I'd like to experiment on compiling postgres with LLVM (either llvm-gcc or clang) on Linux, is it supported ? Where should I start ? Setting the environment variables CC and perhabs LD to your favorite compile before running ./configure should do the trick. If the compilation succeeds, should should probably try to run the regression tests with make check. The most heavily platform dependent part of the code is the spinlock implementation. You might want to check that it actually uses the version optimized for your platform, not the (much slower) generic implementation based on semaphores. BTW, last time I tried compiling with clang basically worked on OSX, despite triggering a helluva lot of warnings. 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] Git: Unable to get pack file
On Tue, Jun 8, 2010 at 13:55, Leonardo F m_li...@yahoo.it wrote: Hi, I tried getting the source using: git clone http://git.postgresql.org/git/postgresql.git postgresql-git but after a while (252MB) I always get: [...] Getting pack 61e1395a5bdacda95de5432123a0f8124fff05e6 which contains 476418893d3a2f366f47dbe4ce6d7522cc427545 error: Unable to get pack file http://git.postgresql.org/git/postgresql.git/objects/pack/pack-61e1395a5bdacda95de5432123a0f8124fff05e6.pack couldn't connect to host error: Unable to find 476418893d3a2f366f47dbe4ce6d7522cc427545 under http://git.postgresql.org/git/postgresql.git Cannot obtain needed blob 476418893d3a2f366f47dbe4ce6d7522cc427545 while processing commit ee6ddc7575ab1ce16d8f4eb2cdbcc525d43a6437. fatal: Fetch failed. I'm behind proxy/firewall, so I used the http://; protocol. wget http://git.postgresql.org/git/postgresql.git/objects/pack/pack-61e1395a5bdacda95de5432123a0f8124fff05e6.pack works... What am I doing wrong? I've re-run git repack on it, please try again. At least that file is accessible from here now.. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Server crash while trying to read expression using pg_get_expr()
(moving to pgsql-hackers)
On 03/06/10 10:37, Heikki Linnakangas wrote:
However, I'm afraid we're lacking in input validation of read-funcs in
general. ...
Does anyone have an idea on how
to validate the input in a more wholesale fashion, so that we don't need
to plug these holes one by one?
Apparently not :-(.
We have two options:
1. Make pg_get_expr() handle arbitrary (possibly even malicious) input
gracefully.
2. Restrict pg_get_expr() to superusers only.
Does anyone want to argue for option 2? We could create views using
pg_get_expr() over the internal catalogs that store trees, so that
regular users could access those without being able to pass arbitrary
input to pg_get_expr(). However, it would break existing applications,
at least pgAdmin uses pg_get_expr().
Assuming we want to make pg_get_expr() check its input, we need to:
* plug the hole Rushabh reported, and not crash on premature end of string
* check all Node types, so that you e.g. can't pass an Integer in a
field that's supposed to hold a CaseWhenExpr
* similarly, check that all Lists contain elements of the right type.
This can all be done in a straightforward way in readfuncs.c, we just
need a bit more decoration to all the READ_* macros. However, that's
still not enough; the functions in ruleutils.c make a number of other
assumptions, like that an OpExpr always has exactly one or two
arguments. That kind of assumptions will need to be explicitly checked.
Many of them already have Asserts, they need to be turned into elogs.
Thoughts? Attached is a patch for the readfuncs.c changes. Unless
someone has a better idea, I'll start going through ruleutils.c and add
explicit checks for any unsafe assumptions. It's going to be a lot of
work, as there's a lot of code in ruleutils.c and the changes need to be
backpatched as well.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
diff --git a/src/backend/nodes/read.c b/src/backend/nodes/read.c
index 5af15c2..d0157d0 100644
--- a/src/backend/nodes/read.c
+++ b/src/backend/nodes/read.c
@@ -50,7 +50,7 @@ stringToNode(char *str)
pg_strtok_ptr = str; /* point pg_strtok at the string to read */
- retval = nodeRead(NULL, 0); /* do the reading */
+ retval = nodeRead(NULL, 0, 0); /* do the reading */
pg_strtok_ptr = save_strtok;
@@ -266,15 +266,18 @@ nodeTokenType(char *token, int length)
* The return value is declared void *, not Node *, to avoid having to
* cast it explicitly in callers that assign to fields of different types.
*
- * External callers should always pass NULL/0 for the arguments. Internally
+ * External callers should always pass NULL/0 for the token/tok_lenarguments. Internally
* a non-NULL token may be passed when the upper recursion level has already
* scanned the first token of a node's representation.
*
+ * If expectedType is non-zero, the node must of the given type, or an error
+ * is thrown.
+ *
* We assume pg_strtok is already initialized with a string to read (hence
* this should only be invoked from within a stringToNode operation).
*/
void *
-nodeRead(char *token, int tok_len)
+nodeRead(char *token, int tok_len, int expectedType)
{
Node *result;
NodeTag type;
@@ -358,7 +361,7 @@ nodeRead(char *token, int tok_len)
/* We have already scanned next token... */
if (token[0] == ')')
break;
- l = lappend(l, nodeRead(token, tok_len));
+ l = lappend(l, nodeRead(token, tok_len, 0));
token = pg_strtok(tok_len);
if (token == NULL)
elog(ERROR, unterminated List structure);
@@ -419,5 +422,9 @@ nodeRead(char *token, int tok_len)
break;
}
+ if (expectedType != 0 nodeTag(result) != expectedType)
+ elog(ERROR, node type %d found, expected %d,
+ nodeTag(result), expectedType);
+
return (void *) result;
}
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index bc6e2a6..cb814fd 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -57,66 +57,90 @@
/* Read an integer field (anything written as :fldname %d) */
#define READ_INT_FIELD(fldname) \
- token = pg_strtok(length); /* skip :fldname */ \
- token = pg_strtok(length); /* get field value */ \
+ token = pg_strtok_e(length); /* skip :fldname */ \
+ token = pg_strtok_e(length); /* get field value */ \
local_node-fldname = atoi(token)
/* Read an unsigned integer field (anything written as :fldname %u) */
#define READ_UINT_FIELD(fldname) \
- token = pg_strtok(length); /* skip :fldname */ \
- token = pg_strtok(length); /* get field value */ \
+ token = pg_strtok_e(length); /* skip :fldname */ \
+ token = pg_strtok_e(length); /* get field value */ \
local_node-fldname = atoui(token)
/* Read an OID field (don't hard-wire assumption that OID is same as uint) */
#define READ_OID_FIELD(fldname) \
- token = pg_strtok(length); /* skip :fldname */ \
- token = pg_strtok(length); /* get field value */ \
+ token =
[HACKERS] Custom index structure and strange count problem
Hi *,
during the last few months I've been building a new index structure as part of
a research project.
Everything seems to work properly, however I have some strange issues with the
count sql command.
I introduced some custom structures (mainly document and hybrid_query) with
which my index access method is supposed to work.
There is an operator which is supposed to use my index structure (what
also works properly).
The function that maps to the operator is called hybrid_index_query, which
I use to compare my results given from the index with the real results that are
supposed to appear in the final result set.
Having described the outer circumstances (in a very short way), I will now show
the strange stuff that happens:
test=# select id from documents where hybrid_index_query(to_document(words,
points), row('radio pleas news'::tsvector, '[(-90,-180),(90, 180)]')) order by
id;
id
--
2137
2151
2168
2207
2208
2209
2210
2211
2266
2296
(10 rows)
This query takes a sequential scan and works properly (returning 10 rows).
test=# select id from documents where to_document(words, points) row('pleas
radio news'::tsvector, '[(-90,-180),(90,180)]') order by id;
id
--
2137
2151
2168
2207
2208
2209
2210
2211
2266
2296
(10 rows)
This query uses my index structure and returns the same result as in the
sequential scan above.
Until here, everything seems to work fine. However, if I issue the same queries
using the count aggregate function in SQL, there are some odd results:
test=# select count(*) from documents where
hybrid_index_query(to_document(words, points), row('radio pleas
news'::tsvector, '[(-90,-180),(90, 180)]'));
count
---
10
(1 row)
Using the sequential scan, still, everything seems fine.
However, if I now do the index scan (my function will be called 11 times,
returning false at the end), I get the following result:
test=# select count(*) from documents where to_document(words, points)
row('pleas radio news'::tsvector, '[(-90,-180),(90,180)]');
count
---
7
(1 row)
This seems strange, because the same query returned 10 rows (when I didn't use
the aggregate). If I issue queries that count the id column, I receive the
following:
test=# select count(id) from documents where
hybrid_index_query(to_document(words, points), row('radio pleas
news'::tsvector, '[(-90,-180),(90, 180)]'));
count
---
10
(1 row)
test=# select count(id) from documents where to_document(words, points)
row('pleas radio news'::tsvector, '[(-90,-180),(90,180)]');
count
---
10
(1 row)
These two queries do again return the same results.
Thus, I don't know, what's wrong here, does anybody know about that behaviour,
or is it my fault that the results are wrong, somehow?
Thanks in advance
Carsten Kropf
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] failover vs. read only queries
Hi, When the trigger file is created while the recovery keeps waiting for the release of the lock by read only queries, it might take a very long time for the standby to become the master. The recovery cannot go ahead until those read only queries have gone away. This would increase the downtime at the failover, and degrade the high availability. To fix the problem, when the trigger file is found, I think that we should cancel all the running read only queries immediately (or forcibly use -1 as the max_standby_delay since that point) and make the recovery go ahead. If some people prefer queries over failover even when they create the trigger file, we can make the trigger behavior selectable in response to the content of the trigger file like pg_standby does. This problem looks like a bug, so I'd like to fix that for 9.0. But the amount of code change might not be small. Thought? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] failover vs. read only queries
When the trigger file is created while the recovery keeps waiting for the release of the lock by read only queries, it might take a very long time for the standby to become the master. The recovery cannot go ahead until those read only queries have gone away. This would increase the downtime at the failover, and degrade the high availability. To fix the problem, when the trigger file is found, I think that we should cancel all the running read only queries immediately (or forcibly use -1 as the max_standby_delay since that point) and make the recovery go ahead. If some people prefer queries over failover even when they create the trigger file, we can make the trigger behavior selectable in response to the content of the trigger file like pg_standby does. This problem looks like a bug, so I'd like to fix that for 9.0. But the amount of code change might not be small. Thought? +1. Down time of HA system is really important for HA users. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] failover vs. read only queries
Fujii Masao masao.fu...@gmail.com wrote: To fix the problem, when the trigger file is found, I think that we should cancel all the running read only queries immediately (or forcibly use -1 as the max_standby_delay since that point) and make the recovery go ahead. Hmmm, does the following sequence work as your expect instead of the chanage? It requires text-file manipulation in 1, but seems to be more flexible. 1. Reset max_standby_delay = 0 in postgresql.conf 2. pg_ctl reload 3. Create a trigger file BTW, I hope we will have pg_ctl failover --timeout=N in 9.1 instead of the trigger file based management. Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] failover vs. read only queries
On Wed, Jun 9, 2010 at 5:47 PM, Fujii Masao masao.fu...@gmail.com wrote: To fix the problem, when the trigger file is found, I think that we should cancel all the running read only queries immediately (or forcibly use -1 as the max_standby_delay since that point) and make the recovery go ahead. Oops! I made an error. I meant 0 instead of -1, as the max_standby_delay. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] failover vs. read only queries
On Wed, Jun 9, 2010 at 6:13 PM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: To fix the problem, when the trigger file is found, I think that we should cancel all the running read only queries immediately (or forcibly use -1 as the max_standby_delay since that point) and make the recovery go ahead. Hmmm, does the following sequence work as your expect instead of the chanage? It requires text-file manipulation in 1, but seems to be more flexible. 1. Reset max_standby_delay = 0 in postgresql.conf 2. pg_ctl reload 3. Create a trigger file As far as I read the HS code, SIGHUP is not checked while a recovery is waiting for queries :( So pg_ctl reload would have no effect on the conflicting queries. Independently from the problem I raised, I think that we should call HandleStartupProcInterrupts() in that sleep loop. BTW, I hope we will have pg_ctl failover --timeout=N in 9.1 instead of the trigger file based management. Please feel free to try that ;) Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Adding XMLEXISTS to the grammar
Hi, I've been working to improve the syntax of the XMLEXISTS function that I put a patch forward for and have been attempting to get my head around how you modify the grammar. I admit I'm not getting much anywhere probably as I don't know bison but I'm starting to wonder if it's worth the pain given recent comments on this list about not changing the grammar for JSON support. At this point I can see a way of implementing the following abridged syntax (abridged as I'm not doing full XQuery support at this stage) in a conventional plain function call by handling the PG_FUNCTION_ARGS approriately, but would this acceptable? XMLEXISTS ( xpath_expression [ PASSING BY REF xml_expression [BY REF] ] ) In case it isn't, and indeed to help me with the XML schema validation work I'm doing, I would still like some help on how the grammar works. From what I've greped and seen in the comments you need to modify the following files: - src/backend/parser/gram.y - src/backend/parser/parse_expr.c - src/backend/utils/ruleutils.c - src/include/parser/kwlist.h From what I can tell, you add the keywords to the lists in gram.y and kwlist.h. At the appropriate place in gram.y you define the syntax and pull out what you need and stuff it into a node (in my case using the makeXmlExpr). You then modify parse_expr.c and ruleutils.c to handle the new values in the fields of the XmlExpr node. Assuming I'm right so far, the step I'm failing to figure out is where the actual c function that implements the function gets called/associated within the grammar. What am I missing? Thanks in advance, -- Mike Fowler Registered Linux user: 379787 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How about closing some Open Items?
On Wed, Jun 9, 2010 at 7:27 AM, Simon Riggs si...@2ndquadrant.com wrote: ... Absence of evidence is not evidence of absence. ... A lack of bugs usually indicates there are no bugs in the areas being tested. Would the real Simon Riggs please speak up? Isn't that precisely what absence of evidence is not evidence of absence is meant to refute? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
On Tue, Jun 8, 2010 at 8:07 PM, Robert Haas robertmh...@gmail.com wrote: I believe that the consensus was mostly in favor of deprecating = as an operator name, with the intent to abolish it completely in a future release. Attached is a patch to implement == as an alternative operator name for hstore, and to make the backend throw a warning when = is used as an operator name. I don't think we can throw warnings for DML except in the most dire circumstances. In an OLTP system with high throughput a warning would fill the logs quickly, effectively making it impossible to use the syntax being warned about. If we're willing to do that we would just use an ERROR anyways which I don't think we're willing to do. People might need a transition period when they still use = because they need to write code which will work with either old or new versions. One wart is that = is used not only as a SQL-level operator, but also by hstore_in() when interpreting hstore-type literals, and by hstore_out() when generating them. My gut feeling is that we should leave this part alone and only muck with the SQL operator, but perhaps someone will care to argue the point. I hate these kinds of inconsistencies. I would prefer both operators be consistent. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
On Wed, Jun 9, 2010 at 6:41 AM, Greg Stark gsst...@mit.edu wrote: On Tue, Jun 8, 2010 at 8:07 PM, Robert Haas robertmh...@gmail.com wrote: I believe that the consensus was mostly in favor of deprecating = as an operator name, with the intent to abolish it completely in a future release. Attached is a patch to implement == as an alternative operator name for hstore, and to make the backend throw a warning when = is used as an operator name. I don't think we can throw warnings for DML except in the most dire circumstances. The patch doesn't. One wart is that = is used not only as a SQL-level operator, but also by hstore_in() when interpreting hstore-type literals, and by hstore_out() when generating them. My gut feeling is that we should leave this part alone and only muck with the SQL operator, but perhaps someone will care to argue the point. I hate these kinds of inconsistencies. I would prefer both operators be consistent. Sure, me too, but I think this is the best we can do. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] hstore == and deprecate =
Greg Stark gsst...@mit.edu writes: On Tue, Jun 8, 2010 at 8:07 PM, Robert Haas robertmh...@gmail.com wrote: I believe that the consensus was mostly in favor of deprecating = as an operator name, with the intent to abolish it completely in a future release. Attached is a patch to implement == as an alternative operator name for hstore, and to make the backend throw a warning when = is used as an operator name. I don't think we can throw warnings for DML except in the most dire circumstances. What about a WARNING at CREATE OPERATOR time? One wart is that = is used not only as a SQL-level operator, but also by hstore_in() when interpreting hstore-type literals, and by hstore_out() when generating them. My gut feeling is that we should leave this part alone and only muck with the SQL operator, but perhaps someone will care to argue the point. I hate these kinds of inconsistencies. I would prefer both operators be consistent. dim=# select 'foo' = 'bar', 'foo = bar'::hstore; ?column? |hstore --+-- foo=bar | foo=bar (1 ligne) Well it's not an operator in the second case, it's part of the input language of the datatype. So consistency would be good enough if we had both the legacy input syntax support and the new one, right? Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] walwriter not closing old files
I've just applied the attached file to the walwriter, to solve a case when it keeps handles around to old xlog segments, preventing them from actually being removed, and as such also causing alerts in some monitoring systems. The way to provoke the problem is: 1. Do something that makes the walwriter active. For example, open a transaction, do something, wait a while, do something, commit. 2. Now feed the system a steady state of *small*, short-running transactions. It's easier to provoke if you just do a simple insert and then pg_switch_xlog(), but it can be done with a regular stream of inserts. The important thing is that the updates must be short-lived enough that walwriter *doesn't* trigger to flush anything out. If you're unlucky (or lucky) you'll hit a walwriter run anyway, in which case you just repeat the test. This will leave walwriter holding the old segment open. If your system *only* does these small and fairly quick transactions, it'll keep the file open forever. This is obviously only likely to happen on lightly loaded systems, but it does keep the file from being properly removed. The attached patch will close the open xlog file if it's no longer in use. The check runs only if there was nothing for walwriter to do - if it had something to do, the regular xlog routines will switch the file for us. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ walwriter.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] hstore == and deprecate =
On Wed, Jun 9, 2010 at 6:53 AM, Dimitri Fontaine dfonta...@hi-media.com wrote: Greg Stark gsst...@mit.edu writes: On Tue, Jun 8, 2010 at 8:07 PM, Robert Haas robertmh...@gmail.com wrote: I believe that the consensus was mostly in favor of deprecating = as an operator name, with the intent to abolish it completely in a future release. Attached is a patch to implement == as an alternative operator name for hstore, and to make the backend throw a warning when = is used as an operator name. I don't think we can throw warnings for DML except in the most dire circumstances. What about a WARNING at CREATE OPERATOR time? That's what the patch I sent already does. One wart is that = is used not only as a SQL-level operator, but also by hstore_in() when interpreting hstore-type literals, and by hstore_out() when generating them. My gut feeling is that we should leave this part alone and only muck with the SQL operator, but perhaps someone will care to argue the point. I hate these kinds of inconsistencies. I would prefer both operators be consistent. dim=# select 'foo' = 'bar', 'foo = bar'::hstore; ?column? | hstore --+-- foo=bar | foo=bar (1 ligne) Well it's not an operator in the second case, it's part of the input language of the datatype. So consistency would be good enough if we had both the legacy input syntax support and the new one, right? I'm not following this part. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] walwriter not closing old files
Magnus Hagander wrote: I've just applied the attached file to the walwriter, to solve a case when it keeps handles around to old xlog segments, preventing them from actually being removed, and as such also causing alerts in some monitoring systems. Thanks! I wasted some time on these a while back; I'm sure this will save others that kind of bother. The way to provoke the problem is: The way I ran into it was to have a web application which only ran read-only transactions. Sooner or later it would need to write a page from the buffer to make space to read a new page, and then it would forever be holding a WAL file open, even after it was deleted. Previous thread on the topic starts here: http://archives.postgresql.org/pgsql-hackers/2009-11/msg01754.php continuing here: http://archives.postgresql.org/pgsql-hackers/2009-12/msg00060.php Resulting in a TODO listed with this description: Close deleted WAL files held open in *nix by long-lived read-only backends -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] walwriter not closing old files
On Wed, Jun 9, 2010 at 14:04, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Magnus Hagander wrote: I've just applied the attached file to the walwriter, to solve a case when it keeps handles around to old xlog segments, preventing them from actually being removed, and as such also causing alerts in some monitoring systems. Thanks! I wasted some time on these a while back; I'm sure this will save others that kind of bother. The way to provoke the problem is: The way I ran into it was to have a web application which only ran read-only transactions. Sooner or later it would need to write a page from the buffer to make space to read a new page, and then it would forever be holding a WAL file open, even after it was deleted. Previous thread on the topic starts here: http://archives.postgresql.org/pgsql-hackers/2009-11/msg01754.php continuing here: http://archives.postgresql.org/pgsql-hackers/2009-12/msg00060.php Resulting in a TODO listed with this description: Close deleted WAL files held open in *nix by long-lived read-only backends That seems to be about the same, but not actually fixed by this one. This fix *only* fixes it when this happens in the walwriter. Not in regular backends. OTOH, this happened in non-readonly mode :) My understanding is that the issue you're talking about happens with the regular backends, not walwriter, right? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] walwriter not closing old files
On 09/06/10 15:04, Kevin Grittner wrote: Magnus Hagander wrote: The way to provoke the problem is: The way I ran into it was to have a web application which only ran read-only transactions. Sooner or later it would need to write a page from the buffer to make space to read a new page, and then it would forever be holding a WAL file open, even after it was deleted. Previous thread on the topic starts here: http://archives.postgresql.org/pgsql-hackers/2009-11/msg01754.php continuing here: http://archives.postgresql.org/pgsql-hackers/2009-12/msg00060.php Resulting in a TODO listed with this description: Close deleted WAL files held open in *nix by long-lived read-only backends This patch only helps with walwriter, though, not backends. Your scenario is probably even more common, but will need a different fix. -- 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] pgstatindex still throws ERROR: value 3220078592 is out of range for type integer
On Mon, Jun 7, 2010 at 8:30 PM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: Dave Cramer p...@fastcrypt.com wrote: I noted on line 169 that max_avail is still an int ? Where else would it be having problems ? It should not a problem because the local variable only stores byte size in a page. It will be at most only BLCKSZ (=8192). I wonder why you had ERROR: value ... is out of range for type integer message because we don't use any integer data types for sizes in pgstatindex. The error should have been thrown by SQL typin functions rather than C routines. Takahiro, Yes, this was the problem. I patched the C code but did not change the functions. Thanks, Dave -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Git: Unable to get pack file
I've re-run git repack on it, please try again. At least that file is accessible from here now.. It worked, thank you very much -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
Robert Haas robertmh...@gmail.com writes: What about a WARNING at CREATE OPERATOR time? That's what the patch I sent already does. Great :) I read comments in the email instead of the commit… I'm not following this part. I'm wondering if deprecating = as an SQL operator, we should too deprecate its usage in input text for hstore. I don't think so. -- Dimitri Fontaine PostgreSQL DBA, Architecte -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
On Wed, Jun 9, 2010 at 9:06 AM, Dimitri Fontaine dfonta...@hi-media.com wrote: Robert Haas robertmh...@gmail.com writes: What about a WARNING at CREATE OPERATOR time? That's what the patch I sent already does. Great :) I read comments in the email instead of the commit… I'm not following this part. I'm wondering if deprecating = as an SQL operator, we should too deprecate its usage in input text for hstore. I don't think so. I don't think so, either. The most someone might want to do is make == work wherever = does now, but I wouldn't want to start monkeying with that without some input from Andrew Gierth; and I don't think it's a stop-ship issue for 9.0. Unless there is more than one vote for some alternative other than what I've done here (which so far there isn't), I'm going to add some docs to this patch and commit it later today. Because the SQL committee has standardized an incompatible syntax, there are no perfect alternatives here. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Adding XMLEXISTS to the grammar
On Wed, Jun 9, 2010 at 6:32 AM, Mike Fowler m...@mlfowler.com wrote: I've been working to improve the syntax of the XMLEXISTS function that I put a patch forward for and have been attempting to get my head around how you modify the grammar. I admit I'm not getting much anywhere probably as I don't know bison but I'm starting to wonder if it's worth the pain given recent comments on this list about not changing the grammar for JSON support. I think we're willing to change the parser to comply with the SQL standard, but not for add-on datatypes. At this point I can see a way of implementing the following abridged syntax (abridged as I'm not doing full XQuery support at this stage) in a conventional plain function call by handling the PG_FUNCTION_ARGS approriately, but would this acceptable? XMLEXISTS ( xpath_expression [ PASSING BY REF xml_expression [BY REF] ] ) I don't see how you're going to make this work without parser changes, and even if you can I think it would be too ugly to consider. In case it isn't, and indeed to help me with the XML schema validation work I'm doing, I would still like some help on how the grammar works. From what I've greped and seen in the comments you need to modify the following files: - src/backend/parser/gram.y - src/backend/parser/parse_expr.c - src/backend/utils/ruleutils.c - src/include/parser/kwlist.h From what I can tell, you add the keywords to the lists in gram.y and kwlist.h. At the appropriate place in gram.y you define the syntax and pull out what you need and stuff it into a node (in my case using the makeXmlExpr). You then modify parse_expr.c and ruleutils.c to handle the new values in the fields of the XmlExpr node. Assuming I'm right so far, the step I'm failing to figure out is where the actual c function that implements the function gets called/associated within the grammar. What am I missing? Look at how the POSITION() pseudofunction is defined around gram.y line 9651. Essentially any special syntax of this type gets converted to a regular function call internally. So in your case I think there will be some function that gets called something ike this: xmlexists(xpath_expression, xml_expression) ...but the grammar can be modified to allow a different syntax for that function call. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] [BUGS] Server crash while trying to read expression using pg_get_expr()
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: We have two options: 1. Make pg_get_expr() handle arbitrary (possibly even malicious) input gracefully. 2. Restrict pg_get_expr() to superusers only. I think #1 is a fool's errand. There is far too much structure to a node tree that is outside the scope of what readfuncs.c is capable of understanding. 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] [BUGS] Server crash while trying to read expression using pg_get_expr()
On 09/06/10 17:34, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: We have two options: 1. Make pg_get_expr() handle arbitrary (possibly even malicious) input gracefully. 2. Restrict pg_get_expr() to superusers only. I think #1 is a fool's errand. There is far too much structure to a node tree that is outside the scope of what readfuncs.c is capable of understanding. That's why I said that ruleutils.c will need to understand and complain about the rest. Are you thinking we should retrict pg_get_expr() to superusers then? -- 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] [BUGS] Server crash while trying to read expression using pg_get_expr()
On 09/06/10 17:34, Tom Lane wrote: Heikki Linnakangasheikki.linnakan...@enterprisedb.com writes: We have two options: 1. Make pg_get_expr() handle arbitrary (possibly even malicious) input gracefully. 2. Restrict pg_get_expr() to superusers only. I think #1 is a fool's errand. There is far too much structure to a node tree that is outside the scope of what readfuncs.c is capable of understanding. That's why I said that ruleutils.c will need to understand and complain about the rest. Are you thinking we should restrict pg_get_expr() to superusers then? -- 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] failover vs. read only queries
Fujii Masao masao.fu...@gmail.com writes: When the trigger file is created while the recovery keeps waiting for the release of the lock by read only queries, it might take a very long time for the standby to become the master. The recovery cannot go ahead until those read only queries have gone away. This would increase the downtime at the failover, and degrade the high availability. To fix the problem, when the trigger file is found, I think that we should cancel all the running read only queries immediately (or forcibly use -1 as the max_standby_delay since that point) and make the recovery go ahead. If some people prefer queries over failover even when they create the trigger file, we can make the trigger behavior selectable in response to the content of the trigger file like pg_standby does. This problem looks like a bug, so I'd like to fix that for 9.0. But the amount of code change might not be small. Thought? -1. This looks like 9.1 material to me, and besides I'm not even convinced that what you propose is a good solution. 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] [BUGS] Server crash while trying to read expression using pg_get_expr()
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 09/06/10 17:34, Tom Lane wrote: I think #1 is a fool's errand. There is far too much structure to a node tree that is outside the scope of what readfuncs.c is capable of understanding. That's why I said that ruleutils.c will need to understand and complain about the rest. And that's what I'm telling you is a hopeless task. Are you thinking we should retrict pg_get_expr() to superusers then? I think that's the only solution that will actually fix the problem, rather than lead to a never-ending series of security bugs. In hindsight we should never have exposed that function in that form. 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] Streaming Replication: Checkpoint_segment and wal_keep_segments on standby
On 09/06/10 05:26, Fujii Masao wrote: On Wed, Jun 2, 2010 at 10:24 PM, Fujii Masaomasao.fu...@gmail.com wrote: On Wed, Jun 2, 2010 at 8:40 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 02/06/10 06:23, Fujii Masao wrote: On Mon, May 31, 2010 at 7:17 PM, Fujii Masaomasao.fu...@gmail.com wrote: 4) Change it so that checkpoint_segments takes effect in standby mode, but not during recovery otherwise I revised the patch to achieve 4). This will enable checkpoint_segments to trigger a restartpoint like checkpoint_timeout already does, in standby mode (i.e., streaming replication or file-based log shipping). Hmm, XLogCtl-Insert.RedoRecPtr is not updated during recovery, so this doesn't work. Oops! I revised the patch, which changes CreateRestartPoint() so that it updates XLogCtl-Insert.RedoRecPtr. This is one of open items. Please review the patch I submitted, and please feel free to comment! Ok, committed with some cosmetic changes. I thought hard if we should do this at all, since the original decision to do time-based restartpoints was deliberate. I concluded that the tradeoffs have changed enough since then to make this reasonable. We now perform restartpoints is bgwriter, so the replay will continue while the restartpoint is being performed, making it less disruptive than it used to be, and secondly SR stores the streamed WAL files in pg_xlog, making it important to perform restartpoints often enough to clean them up and avoid out-of-disk space. BTW, should there be doc changes for this? I didn't find anything explaining how restartpoints are triggered, we should add a paragraph somewhere. -- 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] [BUGS] Invalid YAML output from EXPLAIN
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Robert Haas wrote: When I get some free time, I'll make a patch to implement as much of the spec as we sanely can. Saying that you'll fix it but not on any particular timetable is basically equivalent to saying that you're not willing to fix it at all. It's not equivalent at all. If I wasn't willing to fix it all, I'd say so. We are trying to get a release out the door. I'm not trying to be rude, but it's frustrating to me when people object to having their code ripped out but also won't commit to getting it fixed in a timely fashion. You might not be trying, but you are coming across as quite rude. The bug was only reported Monday morning, and you are yelling at me on a Tuesday night for not being willing to drop everything I'm doing and fix it right now? Yes, we're heading towards 9.0 and yes, I'd sure hate to see YAML ripped out (especially now that it's been listed near and far as one of our new features), but I've got bills to pay and writing a patch is a volunteer effort for me. Since you seem so keen on telling other people what they should be doing, here's some of your own medicine: why not focus on something other than YAML, which myself and many other people can write, and work more on the 9.0 open issues that your energy and expertise would be more suited for? - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 201006091156 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkwPuawACgkQvJuQZxSWSshqSwCgyUoNhi8r/ug/joERXJfJF4mu 3h4AoOtLUHWcN3udePN1Ne2jc+gBa/uS =OtxW -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
Robert Haas robertmh...@gmail.com writes: + if (!strcmp(oprName, =)) BTW, project standard is to spell that like + if (strcmp(oprName, =) == 0) The other way looks confusingly like a not equal test. + (errmsg(The use of = as an operator name is deprecated and may be disallowed in a future release.))); Also, this doesn't follow message style guidelines. Possibly better: errmsg(= is deprecated as an operator name), errdetail(This name may be disallowed altogether in future versions of PostgreSQL.) 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] hstore == and deprecate =
Robert == Robert Haas robertmh...@gmail.com writes: Robert I don't think so, either. The most someone might want to do Robert is make == work wherever = does now, but I wouldn't want to Robert start monkeying with that without some input from Andrew Robert Gierth; and I don't think it's a stop-ship issue for 9.0. I'd really like to find a better operator name than ==. But I'm not convinced one exists. While I don't like the inconsistency between == or whatever and the use of = in type input and output, I regard the text representation as being much harder to change safely, since client code will be parsing it. In this case the inconsistency seems like a much smaller problem than changing the text representation. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
Andrew Gierth and...@tao11.riddles.org.uk writes: I'd really like to find a better operator name than ==. But I'm not convinced one exists. I agree. While I don't like the inconsistency between == or whatever and the use of = in type input and output, I regard the text representation as being much harder to change safely, since client code will be parsing it. In this case the inconsistency seems like a much smaller problem than changing the text representation. Perhaps it would be sane to make hstore_in accept either = or ==, but not change hstore_out (for now)? 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] Performance of Bit String
Hi, I tried to store a BitString of length 2 million in a Postgres table (see code below), but it did not complete even in 3 mins and then I cancelled it. Surprisingly, it only took few seconds when BitString was of length 500K. Is there any restriction of length of BitString or am I missing something here? create table bit_test( id smallint, memset bit(20) ) ; DECLARE memset bit varying:= B'0'; BEGIN --PERFORM memset; FOR i In 1..200 LOOP memset := (memset || B'1') ; -- (B'1' i); END LOOP; INSERT INTO bit_test VALUES(1,B'1',memset :: bit(200)); RETURN bit_length(memset); END; Thanks, Rupendra
Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages
Robert Haas robertmh...@gmail.com writes: On Tue, Jun 8, 2010 at 6:35 PM, Tom Lane t...@sss.pgh.pa.us wrote: But none of this accomplishes a damn thing towards the original goal, which was to avoid an extra disk write associated with freezing (not to mention an extra write for setting the transaction-committed hint bit). Setting a bit is no cheaper from that standpoint than changing the xmin field. Except for insert-only tables, I don't believe this is true. But insert-only tables are exactly the case that Josh complained about to start with. If you freeze all tuples by the time the pages are marked all-visible, perhaps via the xmin-preserving mechanism Simon suggested, then you can use the visibility map to skip anti-wraparound vacuum as well as regular vacuum. That sounds to me like it's accomplishing something. Is it a complete solution? No. Is it better than what we have now? Yes. I do like the idea of using a status bit rather than FrozenXid to mark a frozen tuple, because that eliminates the conflict between wanting to freeze aggressively for performance reasons and wanting to preserve Xids for forensic reasons. But it doesn't seem to do much for Josh's original problem. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages
On Wed, Jun 9, 2010 at 12:35 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Tue, 2010-06-08 at 18:03 -0400, Robert Haas wrote: OK, yes, I see what you're getting at now. There are two possible ways to do freeze the tuples and keep the xmin: we can either rely on the PD_ALL_VISIBLE page-level bit (as I previously proposed) or we can additionally have a HEAP_XMIN_FROZEN bit as you propose here. I am not sure which way is better. Doing it at tuple level is more flexible and allows more aggressive freezing. It also works better with existing tuple visibility code. I agree, relying on a page-level bit (or field) is unpleasant in a number of ways. But none of this accomplishes a damn thing towards the original goal, which was to avoid an extra disk write associated with freezing (not to mention an extra write for setting the transaction-committed hint bit). Setting a bit is no cheaper from that standpoint than changing the xmin field. Could a tuple wih the bit set be considered frozen already? Would we actually ever need to rewrite the xmin, even for anti-wraparound reasons? Greetings Marcin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages
On Tue, 2010-06-08 at 18:35 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Tue, 2010-06-08 at 18:03 -0400, Robert Haas wrote: OK, yes, I see what you're getting at now. There are two possible ways to do freeze the tuples and keep the xmin: we can either rely on the PD_ALL_VISIBLE page-level bit (as I previously proposed) or we can additionally have a HEAP_XMIN_FROZEN bit as you propose here. I am not sure which way is better. Doing it at tuple level is more flexible and allows more aggressive freezing. It also works better with existing tuple visibility code. I agree, relying on a page-level bit (or field) is unpleasant in a number of ways. But none of this accomplishes a damn thing towards the original goal, which was to avoid an extra disk write associated with freezing (not to mention an extra write for setting the transaction-committed hint bit). Setting a bit is no cheaper from that standpoint than changing the xmin field. No, it doesn't of itself, but if you raise a complaint then we must first address the complaint as a sub-topic before we continue the main discussion around $TOPIC. My proposal removes the barrier that early freezing would overwrite xmin values, so early freezing need not have any negative effects. The general idea is to hide the third write (freezing) on a tuple by making it happen at the same time as another tuple's second write (hint bit setting). I'm happy to let that continue by the OPs. -- Simon Riggs www.2ndQuadrant.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] hstore == and deprecate =
On Jun 9, 2010, at 9:30 AM, Tom Lane wrote: Andrew Gierth and...@tao11.riddles.org.uk writes: I'd really like to find a better operator name than ==. But I'm not convinced one exists. I agree. +1 No one liked my suggestion of ~ ? Too similar to - ? Other ideas: 'foo' : 'bar' 'foo' @ 'bar' 'foo' # 'bar' 'foo' 'bar' 'foo' * 'bar' 'foo' + 'bar' 'foo' 'bar' 'bar' = 'foo' I actually like : pretty well. It looks more like =, and has nice correspondence to := for named function params. Hey, why not Unicode? 'bar' ➡ 'foo' ;-) Perhaps it would be sane to make hstore_in accept either = or ==, but not change hstore_out (for now)? +1 David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
On Wed, Jun 9, 2010 at 12:54 PM, David E. Wheeler da...@kineticode.com wrote: Perhaps it would be sane to make hstore_in accept either = or ==, but not change hstore_out (for now)? +1 Anyone want to take a crack at coding that? I took a brief look at the code but it looked a bit intimidating at first glance. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Performance of Bit String
Hi, Youre on the wrong list for this. This is not a -hackers (i.e. developer targeted) but a -general (user targeted) question. On Wednesday 09 June 2010 15:11:41 rupendra.chulya...@gmail.com wrote: I tried to store a BitString of length 2 million in a Postgres table (see code below), but it did not complete even in 3 mins and then I cancelled it. Surprisingly, it only took few seconds when BitString was of length 500K. Is there any restriction of length of BitString or am I missing something here? I think youre missing that your algorithm for assembling the string has quadratic complexity. For each loop iteratoring the whole string will be newly allocated and then copied over. A faster way to create such a long string might be: SELECT array_to_string(array_agg(1),'')::bit(200) FROM generate_series(1, 200); Btw, your table definition has only the length bit(200k), but youre inserting bit(2000k)... What are you trying to achieve with such a long bitstring? Actually I cannot think of any database design where I would consider that a valid design- choice. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] hstore == and deprecate =
David E. Wheeler da...@kineticode.com writes: I actually like : pretty well. It looks more like =, and has nice correspondence to := for named function params. Colon was removed from the set of allowed operator-name characters years ago. There are conflicts with various usages (ecpg psql variables). This is actually a place where the current := behavior could cause some issues, though I'm not aware of any at the moment. 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] Custom index structure and strange count problem
On Wed, Jun 9, 2010 at 4:35 AM, Carsten Kropf ckro...@fh-hof.de wrote:
Hi *,
during the last few months I've been building a new index structure as part
of a research project.
Everything seems to work properly, however I have some strange issues with
the count sql command.
I introduced some custom structures (mainly document and hybrid_query) with
which my index access method is supposed to work.
There is an operator which is supposed to use my index structure (what
also works properly).
The function that maps to the operator is called hybrid_index_query,
which I use to compare my results given from the index with the real results
that are supposed to appear in the final result set.
Having described the outer circumstances (in a very short way), I will now
show the strange stuff that happens:
test=# select id from documents where hybrid_index_query(to_document(words,
points), row('radio pleas news'::tsvector, '[(-90,-180),(90, 180)]')) order
by id;
id
--
2137
2151
2168
2207
2208
2209
2210
2211
2266
2296
(10 rows)
This query takes a sequential scan and works properly (returning 10 rows).
test=# select id from documents where to_document(words, points)
row('pleas radio news'::tsvector, '[(-90,-180),(90,180)]') order by id;
id
--
2137
2151
2168
2207
2208
2209
2210
2211
2266
2296
(10 rows)
This query uses my index structure and returns the same result as in the
sequential scan above.
Until here, everything seems to work fine. However, if I issue the same
queries using the count aggregate function in SQL, there are some odd results:
test=# select count(*) from documents where
hybrid_index_query(to_document(words, points), row('radio pleas
news'::tsvector, '[(-90,-180),(90, 180)]'));
count
---
10
(1 row)
Using the sequential scan, still, everything seems fine.
However, if I now do the index scan (my function will be called 11 times,
returning false at the end), I get the following result:
test=# select count(*) from documents where to_document(words, points)
row('pleas radio news'::tsvector, '[(-90,-180),(90,180)]');
count
---
7
(1 row)
This seems strange, because the same query returned 10 rows (when I didn't
use the aggregate). If I issue queries that count the id column, I receive
the following:
test=# select count(id) from documents where
hybrid_index_query(to_document(words, points), row('radio pleas
news'::tsvector, '[(-90,-180),(90, 180)]'));
count
---
10
(1 row)
test=# select count(id) from documents where to_document(words, points)
row('pleas radio news'::tsvector, '[(-90,-180),(90,180)]');
count
---
10
(1 row)
These two queries do again return the same results.
Thus, I don't know, what's wrong here, does anybody know about that
behaviour, or is it my fault that the results are wrong, somehow?
Thanks in advance
I am guessing this is a bug in your code - have you used EXPLAIN to
verify that the second-to-last of the above queries is really hitting
your code? If so, I'd recommend attaching with gdb and setting a
breakpoint wherever you return the tuples, and then poke around...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] hstore == and deprecate =
On Jun 9, 2010, at 10:04 AM, Tom Lane wrote: I actually like : pretty well. It looks more like =, and has nice correspondence to := for named function params. Colon was removed from the set of allowed operator-name characters years ago. There are conflicts with various usages (ecpg psql variables). This is actually a place where the current := behavior could cause some issues, though I'm not aware of any at the moment. Oh, that's a shame. In that case, how about: 'foo' 'bar' Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Adding XMLEXISTS to the grammar
Robert Haas robertmh...@gmail.com writes: Look at how the POSITION() pseudofunction is defined around gram.y line 9651. Essentially any special syntax of this type gets converted to a regular function call internally. So in your case I think there will be some function that gets called something ike this: xmlexists(xpath_expression, xml_expression) ...but the grammar can be modified to allow a different syntax for that function call. Note also that we typically try to allow the function to be called with the generic comma-separated syntax as well as the keyword-based syntax that the SQL committee has such weird love for. This makes life easier for users, and it also means that we don't need special cases in ruleutils.c. 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] [BUGS] Server crash while trying to read expression using pg_get_expr()
On Wed, 9 Jun 2010, Heikki Linnakangas wrote: Are you thinking we should retrict pg_get_expr() to superusers then? That seems like it will cause problems for both pg_dump and drivers which want to return metadata as pg_get_expr has been the recommended way of fetching this information. The JDBC driver uses pg_get_expr to decode both pg_attrdef.adbin and pg_index.indpred. Kris Jurka -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Invalid YAML output from EXPLAIN
On Wed, Jun 9, 2010 at 11:57 AM, Greg Sabino Mullane g...@turnstep.com wrote: The bug was only reported Monday morning, and you are yelling at me on a Tuesday night for not being willing to drop everything I'm doing and fix it right now? I am not saying and have not said that you needed to drop everything you were doing and fix it right now. Had you said, I will get a patch for this out this week, that would have been fine with me. What I did and do object to is that your commitment to fix it was completely open-ended. From reading your email, there's no way for someone to know whether you'll get to this in two days or a month, and a month, at least in my opinion, is too long, maybe at any time but certainly at this point in the release cycle. Since you seem so keen on telling other people what they should be doing, here's some of your own medicine: why not focus on something other than YAML, which myself and many other people can write, and work more on the 9.0 open issues that your energy and expertise would be more suited for? I think this comment is a little snide, but it deserves a serious response. I spent most of yesterday afternoon and evening working on every open item that I had a clue about, and another two hours this morning. Most of the remaining items are either things that I am not qualified to fix or for which there are currently patches out for comment. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] hstore == and deprecate =
On Wed, Jun 9, 2010 at 1:15 PM, David E. Wheeler da...@kineticode.com wrote: On Jun 9, 2010, at 10:04 AM, Tom Lane wrote: I actually like : pretty well. It looks more like =, and has nice correspondence to := for named function params. Colon was removed from the set of allowed operator-name characters years ago. There are conflicts with various usages (ecpg psql variables). This is actually a place where the current := behavior could cause some issues, though I'm not aware of any at the moment. Oh, that's a shame. In that case, how about: 'foo' 'bar' Well, that doesn't look much like an arrow, at least not to me... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] [BUGS] Server crash while trying to read expression using pg_get_expr()
Kris Jurka bo...@ejurka.com writes: On Wed, 9 Jun 2010, Heikki Linnakangas wrote: Are you thinking we should retrict pg_get_expr() to superusers then? That seems like it will cause problems for both pg_dump and drivers which want to return metadata as pg_get_expr has been the recommended way of fetching this information. Yes, it's not a trivial fix either. We'll have to provide functions or views that replace the current usages without letting the user insert untrusted strings. 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] [BUGS] Server crash while trying to read expression using pg_get_expr()
On Wed, Jun 9, 2010 at 1:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kris Jurka bo...@ejurka.com writes: On Wed, 9 Jun 2010, Heikki Linnakangas wrote: Are you thinking we should retrict pg_get_expr() to superusers then? That seems like it will cause problems for both pg_dump and drivers which want to return metadata as pg_get_expr has been the recommended way of fetching this information. Yes, it's not a trivial fix either. We'll have to provide functions or views that replace the current usages without letting the user insert untrusted strings. Maybe I'm all wet here, but don't we need to come up with something we can back-patch? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] hstore == and deprecate =
On Jun 9, 2010, at 10:33 AM, Robert Haas wrote: Well, that doesn't look much like an arrow, at least not to me... It's a pointer, though. Not in the C sense, of course. But I often use » for read more style links in HTML. Its the same idea: move from this to that. Anyway, for comparison's purpose: 'foo' == 'bar' 'foo' 'bar' Pity about :. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Out of date docs: DISABLE/ENABLE TRIGGER
Excerpts from Dean Rasheed's message of dom jun 06 05:11:02 -0400 2010: Hi, I just spotted that the docs for ALTER TABLE .. DISABLE/ENABLE TRIGGER are out of date, now that we have deferrable uniqueness and exclusion constraints. applied, thanks Also, I think that the original comment in the ENABLE/DISABLE TRIGGER section was misleading because it suggested that only superusers can disable constraint triggers, which is not true for *user-defined* constraint triggers. right. -- Á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] [BUGS] Server crash while trying to read expression using pg_get_expr()
Robert Haas robertmh...@gmail.com writes: On Wed, Jun 9, 2010 at 1:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: Yes, it's not a trivial fix either. We'll have to provide functions or views that replace the current usages without letting the user insert untrusted strings. Maybe I'm all wet here, but don't we need to come up with something we can back-patch? Well, ideally yes, but if it's not actually *secure* then there's no point --- and I don't believe that the approach of making readfuncs.c secure against malicious input has the proverbial snowball's chance of ever being bulletproof. [ thinks for awhile... ] I wonder whether there is any way of locking down pg_get_expr so that it throws an error if called with anything except a suitable field from one of the system catalogs. There are only a few usage patterns that we need to allow, no? At least in recent PG versions it is possible for the function to check that its input expression is a Var. If we had some (probably horridly ugly) way to obtain the rangetable entry the Var refers to, we could put code into pg_get_expr to barf if it's not used in a context like select pg_get_expr(adbin) from pg_attrdef. 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] Custom index structure and strange count problem
Hi,
thanks so far.
However, if I attach a Debugger (which I did in advance, too) and I use
explain, I get the same results.
My first guess in each case is always that it is my fault. However, I don't
know exactly, why this strange behaviour occurs here. The problem I have is
that EXPLAIN, too, always tells me that it uses an index scan (in the cases
where the query is supposed to use one).
The query plan looks exactly the same in any case (if I apply count(id) or
count(*), respectively). However, the results differ.
The query plan is also the same, if I use the select * or select id query
without applying an aggregate with the small difference that the aggregate is
used, where it is supposed to be.
I just thought, that somebody has already had problems with something like that
(actually it is no real problem, except that the aggregate applied to *
queries causes a different count).
The query data my index structure is called with, stays the same in all cases
(mentioned in the previous mail).
Does anybody have some hints according to which checks to perform in order to
determine the problem here?
Best regards
Carsten Kropf
Am 09.06.2010 um 19:09 schrieb Robert Haas:
On Wed, Jun 9, 2010 at 4:35 AM, Carsten Kropf ckro...@fh-hof.de wrote:
Hi *,
during the last few months I've been building a new index structure as part
of a research project.
Everything seems to work properly, however I have some strange issues with
the count sql command.
I introduced some custom structures (mainly document and hybrid_query) with
which my index access method is supposed to work.
There is an operator which is supposed to use my index structure (what
also works properly).
The function that maps to the operator is called hybrid_index_query,
which I use to compare my results given from the index with the real results
that are supposed to appear in the final result set.
Having described the outer circumstances (in a very short way), I will now
show the strange stuff that happens:
test=# select id from documents where hybrid_index_query(to_document(words,
points), row('radio pleas news'::tsvector, '[(-90,-180),(90, 180)]')) order
by id;
id
--
2137
2151
2168
2207
2208
2209
2210
2211
2266
2296
(10 rows)
This query takes a sequential scan and works properly (returning 10 rows).
test=# select id from documents where to_document(words, points)
row('pleas radio news'::tsvector, '[(-90,-180),(90,180)]') order by id;
id
--
2137
2151
2168
2207
2208
2209
2210
2211
2266
2296
(10 rows)
This query uses my index structure and returns the same result as in the
sequential scan above.
Until here, everything seems to work fine. However, if I issue the same
queries using the count aggregate function in SQL, there are some odd
results:
test=# select count(*) from documents where
hybrid_index_query(to_document(words, points), row('radio pleas
news'::tsvector, '[(-90,-180),(90, 180)]'));
count
---
10
(1 row)
Using the sequential scan, still, everything seems fine.
However, if I now do the index scan (my function will be called 11 times,
returning false at the end), I get the following result:
test=# select count(*) from documents where to_document(words, points)
row('pleas radio news'::tsvector, '[(-90,-180),(90,180)]');
count
---
7
(1 row)
This seems strange, because the same query returned 10 rows (when I didn't
use the aggregate). If I issue queries that count the id column, I receive
the following:
test=# select count(id) from documents where
hybrid_index_query(to_document(words, points), row('radio pleas
news'::tsvector, '[(-90,-180),(90, 180)]'));
count
---
10
(1 row)
test=# select count(id) from documents where to_document(words, points)
row('pleas radio news'::tsvector, '[(-90,-180),(90,180)]');
count
---
10
(1 row)
These two queries do again return the same results.
Thus, I don't know, what's wrong here, does anybody know about that
behaviour, or is it my fault that the results are wrong, somehow?
Thanks in advance
I am guessing this is a bug in your code - have you used EXPLAIN to
verify that the second-to-last of the above queries is really hitting
your code? If so, I'd recommend attaching with gdb and setting a
breakpoint wherever you return the tuples, and then poke around...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] hstore == and deprecate =
Tom == Tom Lane t...@sss.pgh.pa.us writes: While I don't like the inconsistency between == or whatever and the use of = in type input and output, I regard the text representation as being much harder to change safely, since client code will be parsing it. In this case the inconsistency seems like a much smaller problem than changing the text representation. Tom Perhaps it would be sane to make hstore_in accept either = or Tom ==, but not change hstore_out (for now)? I'd be happy with that. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] release notes
On Sun, May 30, 2010 at 6:58 PM, Andrew Dunstan and...@dunslane.net wrote:
Tim Bunce wrote:
p.s. It also turned to be insufficiently useful for NYTProf since it
doesn't also update some internals to record the 'filename' and line
number range of the sub. So PostgreSQL::PLPerl::NYTProf works around
that by wrapping mkfuncsrc() to edit the generated perl code to include
a subname in the usual sub $name { ... } style. I may offer a patch
for 9.1 to to make it work that way.
I put this aside to think about it.
If the feature is not any use should we rip it out? We pretty much
included it because you said it was what you needed for the profiler.
I'm seriously nervous about adding function names - making functions
directly callable like that could be a major footgun recipe, since now we
are free to hide some stuff in the calling mechanism, and can (and have in
the past) changed that to suit our needs, e.g. when we added trigger support
via a hidden function argument. That has been safe precisely because nobody
has had a handle on the subroutine which would enable it to be called
directly from perl code. There have been suggestions in the past of using
this for other features, so we should not assume that the calling mechanism
is fixed in stone.
Perhaps we could decorate the subs with attributes, or is that not doable
for anonymous subs?
This is still on our open items list - should we do anything about it?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] hstore == and deprecate =
On Wed, Jun 9, 2010 at 1:15 PM, David E. Wheeler da...@kineticode.com wrote: On Jun 9, 2010, at 10:04 AM, Tom Lane wrote: I actually like : pretty well. It looks more like =, and has nice correspondence to := for named function params. Colon was removed from the set of allowed operator-name characters years ago. There are conflicts with various usages (ecpg psql variables). This is actually a place where the current := behavior could cause some issues, though I'm not aware of any at the moment. Oh, that's a shame. In that case, how about: 'foo' 'bar' +1 merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Open item: slave to standby in docs
On Thu, Jun 3, 2010 at 8:44 PM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: Ther is an open item: Standby instead of slave in documentation http://archives.postgresql.org/message-id/1273682033.12754.1.ca...@vanquo.pezone.net I replacesd almost all slave to standby or standby servers not only in HS+SR but also in other places like third-party tools. There are still 3 places where slave is used. - Terminology: ... are called standby or slave servers. - Words in old release notes for 8.2 and 8.4. Could you check those replacements are proper? Some of these read OK, but others just don't sound right. In particular, master/standby replication just sounds odd. I think the word standby implies a server that could take over for the master if it died while slave doesn't necessarily have that connotation. So for example the changes to protocol.sgml read OK to me, but the changes to high-availability.sgml I'm not a big fan of. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] failover vs. read only queries
Josh Berkus j...@agliodbs.com writes: The fact that failover current does *not* terminate existing queries and transactions was regarded as a feature by the audience, rather than a bug, when I did demos of HS/SR. Of course, they might not have been thinking of the delay for writes. If there were an easy way to make the trigger file cancel all running queries, apply remaining logs and come up, then I'd vote for that for 9.0. I think it's the more desired behavior by most users. However, I'm opposed to any complex solutions which might delay 9.0 release. My feeling about it is that if you want fast failover you should not have your failover target server configured as hot standby at all, let alone hot standby with a long max_standby_delay. Such a slave could be very far behind on applying WAL when the crunch comes, and no amount of query killing will save you from that. Put your long-running standby queries on a different slave instead. We should consider whether we can improve the situation in 9.1, but it is not a must-fix for 9.0; especially when the correct behavior isn't immediately obvious. 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] Command to prune archive at restartpoints
On Wed, 2010-06-09 at 11:18 +0900, Takahiro Itagaki wrote: Robert Haas robertmh...@gmail.com wrote: I think we're replacing restartpoint_command, not recovery_end_command. Ah, sorry. I did the same replacement for restartpoint_command in _, -, and camel case words. BTW, should we also have a release note for the command? I added a simple description for it in the patch. I don't think so, its not a separate feature. It's a change as part of SR. -- Simon Riggs www.2ndQuadrant.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] failover vs. read only queries
To fix the problem, when the trigger file is found, I think that we should cancel all the running read only queries immediately (or forcibly use -1 as the max_standby_delay since that point) and make the recovery go ahead. If some people prefer queries over failover even when they create the trigger file, we can make the trigger behavior selectable in response to the content of the trigger file like pg_standby does. Well, the question is: are there users who would prefer not to have slave queries cancelled and are willing to wait for failover? If so, behavior of failover should really be slaved to max_standby_delay. If not, there should be new behavior (i.e. when the trigger file is found, cancel all running queries). One could argue that there are no users of the first case. The fact that failover current does *not* terminate existing queries and transactions was regarded as a feature by the audience, rather than a bug, when I did demos of HS/SR. Of course, they might not have been thinking of the delay for writes. If there were an easy way to make the trigger file cancel all running queries, apply remaining logs and come up, then I'd vote for that for 9.0. I think it's the more desired behavior by most users. However, I'm opposed to any complex solutions which might delay 9.0 release. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] failover vs. read only queries
On Wed, Jun 9, 2010 at 3:22 PM, Josh Berkus j...@agliodbs.com wrote: To fix the problem, when the trigger file is found, I think that we should cancel all the running read only queries immediately (or forcibly use -1 as the max_standby_delay since that point) and make the recovery go ahead. If some people prefer queries over failover even when they create the trigger file, we can make the trigger behavior selectable in response to the content of the trigger file like pg_standby does. Well, the question is: are there users who would prefer not to have slave queries cancelled and are willing to wait for failover? If so, behavior of failover should really be slaved to max_standby_delay. If not, there should be new behavior (i.e. when the trigger file is found, cancel all running queries). One could argue that there are no users of the first case. The fact that failover current does *not* terminate existing queries and transactions was regarded as a feature by the audience, rather than a bug, when I did demos of HS/SR. Of course, they might not have been thinking of the delay for writes. If there were an easy way to make the trigger file cancel all running queries, apply remaining logs and come up, then I'd vote for that for 9.0. I think it's the more desired behavior by most users. However, I'm opposed to any complex solutions which might delay 9.0 release. One complication here is that, at least as I understand it, Tom is planning to overhaul max_standby_delay. So it might be premature to try to figure out how this should work until the dust settles. But my intuition is similar to yours, overall. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Command to prune archive at restartpoints
On Tue, 2010-06-08 at 18:30 -0400, Andrew Dunstan wrote: I prefer archive_cleanup_command. We should name things after their principal function, not an implementation detail, IMNSHO. More importantly, we should include an example in the docs. I created one the other day when this was actually bothering me a bit (see http://people.planetpostgresql.org/andrew/index.php?/archives/85-Keeping-a-hot-standby-log-archive-clean.html). That seemed to work ok, but maybe it's too long, and maybe people would prefer a shell script to perl. I submitted a patch to make the command pg_standby -a %r That's a more portable solution, ISTM. I'll commit that and fix the docs. -- Simon Riggs www.2ndQuadrant.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] [BUGS] Server crash while trying to read expression using pg_get_expr()
On Wed, Jun 9, 2010 at 2:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Jun 9, 2010 at 1:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: Yes, it's not a trivial fix either. We'll have to provide functions or views that replace the current usages without letting the user insert untrusted strings. Maybe I'm all wet here, but don't we need to come up with something we can back-patch? Well, ideally yes, but if it's not actually *secure* then there's no point --- and I don't believe that the approach of making readfuncs.c secure against malicious input has the proverbial snowball's chance of ever being bulletproof. I don't really see how it could be *impossible* to securely parse text input. It's certainly possible not to crash on trivially malformed input. Completely validating the input MIGHT cost more in performance than we want to pay in CPU cycles, but I guess I'm not seeing why it would be an unsolvable problem apart from that. [ thinks for awhile... ] I wonder whether there is any way of locking down pg_get_expr so that it throws an error if called with anything except a suitable field from one of the system catalogs. There are only a few usage patterns that we need to allow, no? At least in recent PG versions it is possible for the function to check that its input expression is a Var. If we had some (probably horridly ugly) way to obtain the rangetable entry the Var refers to, we could put code into pg_get_expr to barf if it's not used in a context like select pg_get_expr(adbin) from pg_attrdef. That's sort of clever... in a really ugly sort of way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] [BUGS] Server crash while trying to read expression using pg_get_expr()
Robert Haas robertmh...@gmail.com writes: On Wed, Jun 9, 2010 at 2:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, ideally yes, but if it's not actually *secure* then there's no point --- and I don't believe that the approach of making readfuncs.c secure against malicious input has the proverbial snowball's chance of ever being bulletproof. I don't really see how it could be *impossible* to securely parse text input. It's certainly possible not to crash on trivially malformed input. The operative word in that claim is trivial. The problem that I see is that there are many assumptions in the system about the structure and interrelationships of expression node trees, for instance that certain List fields contain only certain node types. I don't believe that it's practical to make the node reading code enforce every one of those assumptions, or that it'd be maintainable if we did manage to get it right to start with. Certainly we can make the node reading code do more checking than it does now, but the odds of making things bulletproof against malicious input are negligible. I don't want to be going back to fix another hole every other month for the lifetime of the project, but that's exactly what we'll be doing if we try to fix it that way. 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] hot_standby = on
On Tue, 2010-06-08 at 17:24 -0400, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Well, yes. But then to stop that you could just lock users out using pg_hba.conf, no? It just doesn't seem to be buying all that much to me. The main reason to turn it off is to disable a whole lot of very poorly tested code, and thereby improve the reliability of your warm standby server. There might be (almost certainly are) significant performance benefits as well. I would be happy to look over any performance results you have that show this to be true. I only know of one area I thought was a significant loss in some cases, which you canned because we had no evidence it was a problem... -- Simon Riggs www.2ndQuadrant.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] [BUGS] Server crash while trying to read expression using pg_get_expr()
I wrote: [ thinks for awhile... ] I wonder whether there is any way of locking down pg_get_expr so that it throws an error if called with anything except a suitable field from one of the system catalogs. I did a bit of research into this idea. It looks at least somewhat feasible: * All PG versions back to 7.4 will pass the calling expression tree via fcinfo-flinfo-fn_expr. Lack of that would be a showstopper, because we can't change the FmgrInfo struct in back branches (ABI break). So we can get the arguments and determine whether they are Vars. * To determine which table a Var actually refers to, we must have access to the rangetable, and in join cases we also need access to the plan tree node containing the Var (since we have to drill down to the plan node's inputs to resolve OUTER and INNER references). The rangetable is reachable from the PlanState node, so it's sufficient to make that one pointer available to functions. The obvious way to handle this is to add a field to FmgrInfo, and I would suggest doing it that way as of 9.0. In the back branches, we could probably hack it without an ABI break by having fn_expr point to some intermediate node type that contains both the actual expression tree and the PlanState pointer (probably, we'd make it point to FuncExprState instead of directly to the FuncExpr, and then add the field to FuncExprState, which is far less dangerous than redefining struct FmgrInfo). Now this depends on the assumption that no external functions are doing anything with fn_expr except passing it to the related fmgr.c routines, which we would teach about this convention. * Once we've got the above data it's a simple matter of adapting the Var-interpretation logic used by EXPLAIN in order to find out the table OID and column number, if any, represented by the Var. I'd suggest adding such functions in fmgr.c to extend the API currently offered by get_fn_expr_argtype() and friends. It seems possible that other functions besides pg_get_expr might have use for such a capability. What I'm suggesting is definitely not a trivial patch, and I would never consider back-patching it if it weren't a security matter. But I think it's doable and we'd be fixing the hole with a determinate amount of work, whereas trying to verify the validity of pg_get_expr input directly would be a never-ending source of more security bugs. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keepalive for max_standby_delay
Simon Riggs si...@2ndquadrant.com writes: On Thu, 2010-06-03 at 19:02 -0400, Tom Lane wrote: I decided there wasn't time to get anything useful done on it before the beta2 deadline (which is, more or less, right now). I will take another look over the next few days. We all really need you to fix up max_standby_delay, or, let me do it. Yes, I'll get with 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] Keepalive for max_standby_delay
On Thu, 2010-06-03 at 19:02 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Thu, 2010-06-03 at 18:18 +0100, Simon Riggs wrote: Are you planning to work on these things now as you said? Are you? Or do you want me to? I decided there wasn't time to get anything useful done on it before the beta2 deadline (which is, more or less, right now). I will take another look over the next few days. Esteemed colleague Tom, We all really need you to fix up max_standby_delay, or, let me do it. It appears we both agree that more testing would be beneficial. The only way we are going to get that is by finishing this off and declaring a new HS-beta. Please let me know how you wish to proceed. (No answer means I do it.) -- Simon Riggs www.2ndQuadrant.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] Idea for getting rid of VACUUM FREEZE on cold pages
marcin mank marcin.m...@gmail.com writes: Could a tuple wih the bit set be considered frozen already? Would we actually ever need to rewrite the xmin, even for anti-wraparound reasons? That's exactly what Simon is suggesting: if we had a tuple status flag with the semantics of this xmin is known visible to all current and future transactions, we could consider setting that bit to be the moral equivalent of freezing the tuple. The tuple visibility tests would never actually consult clog for such an xmin and thus we'd never have to replace it with FrozenXid. But this doesn't in itself save us any work: we'd still need to treat setting that bit as a WAL-logged operation, and we'd still need to have VACUUM track the oldest not-thus-hinted xmins. What it does do is eliminate the conflict between wanting to freeze tuples aggressively for various performance reasons and wanting to preserve original xmin values for forensic reasons. I wonder how this might play into Heikki's ideas about making the visibility map trustworthy. If we WAL-logged the operation of set all the per-tuple VISIBLE-TO-ALL bits on this page, as well as the page's bit in the visibility map, then that end of things would be trustworthy. And all the operations that have to unset the map bit are already WAL-logged. 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] Idea for getting rid of VACUUM FREEZE on cold pages
On Wed, Jun 9, 2010 at 12:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: If you freeze all tuples by the time the pages are marked all-visible, perhaps via the xmin-preserving mechanism Simon suggested, then you can use the visibility map to skip anti-wraparound vacuum as well as regular vacuum. That sounds to me like it's accomplishing something. Is it a complete solution? No. Is it better than what we have now? Yes. I do like the idea of using a status bit rather than FrozenXid to mark a frozen tuple, because that eliminates the conflict between wanting to freeze aggressively for performance reasons and wanting to preserve Xids for forensic reasons. But it doesn't seem to do much for Josh's original problem. OK, I see. So maybe we add a Todo to implement that, and then keep thinking about how to fix Josh's problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] failover vs. read only queries
On Wed, 2010-06-09 at 12:22 -0700, Josh Berkus wrote: To fix the problem, when the trigger file is found, I think that we should cancel all the running read only queries immediately (or forcibly use -1 as the max_standby_delay since that point) and make the recovery go ahead. If some people prefer queries over failover even when they create the trigger file, we can make the trigger behavior selectable in response to the content of the trigger file like pg_standby does. Well, the question is: are there users who would prefer not to have slave queries cancelled and are willing to wait for failover? If so, behavior of failover should really be slaved to max_standby_delay. If not, there should be new behavior (i.e. when the trigger file is found, cancel all running queries). One could argue that there are no users of the first case. The fact that failover current does *not* terminate existing queries and transactions was regarded as a feature by the audience, rather than a bug, when I did demos of HS/SR. Of course, they might not have been thinking of the delay for writes. +1 Just to add: there is only a delay in triggering *if* the standby is waiting on a query at or after triggering. If there is a wait, it is never more than max_standby_delay, which is what the user said they would be happy to accept. If there were an easy way to make the trigger file cancel all running queries, apply remaining logs and come up, then I'd vote for that for 9.0. I think it's the more desired behavior by most users. However, I'm opposed to any complex solutions which might delay 9.0 release. In 8.4 you could specify fast failover or smart failover. In 9.0, AFAICS we have only implemented smart failover, which means it will continue until the end of the WAL stream before triggering. So under heavy streaming load or with considerable lag the trigger won't cause failover for some time. So there is less function in 9.0 than was available in 8.4. If that removal was intended, it wasn't discussed. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bug or feature? Timestamp parsing
select 'NOW?'::TIMESTAMP; timestamp 2010-06-09 14:08:21.020259 postgres=# select ';;;infinity?...@$%$'::TIMESTAMP; timestamp --- infinity (1 row) It appears that the ts parser will ignore any punctuation surrounding the special value calls. In general, this isn't a potential problem. However, it could cause some confusion with careless value replacement by users. Imagine a case like this: create or replace function epoch(integer) returns timestamp language sql as 'SELECT ''epoch''::timestamp + $1 * interval ''1 second'';'; Then later you fail on your client quoting rules and do the following in your app code: UPDATE some_table SET timestamp_field = 'epoch(15)' WHERE id = 501; The above will result in 1970-01-01 00:00:00 UTC getting into the field, not 1970-01-02 17:40:00 as the user intended, since the '(15)' will be ignored. And given the lack of an error message, a lot of debugging time. On the other hand, it appears that our timestamps have had this bug since at least 8.0, so it clearly isn't a widespread problem for most users. And likely some users have been taking advantage of letting garbage into their timestamp casts, so there would be some application breakage. Thoughts? -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] How about closing some Open Items?
On Wed, 2010-06-09 at 11:34 +0100, Greg Stark wrote: On Wed, Jun 9, 2010 at 7:27 AM, Simon Riggs si...@2ndquadrant.com wrote: ... Absence of evidence is not evidence of absence. ... A lack of bugs usually indicates there are no bugs in the areas being tested. Would the real Simon Riggs please speak up? LOL, its me, flaws included. Isn't that precisely what absence of evidence is not evidence of absence is meant to refute? You snipped out the part where I give other possible explanations also, so the two statements above do not counterpoise each other of themselves in my original text. I stand by my whole statement, as intended. Now, I think I misread Tom's comments. I agree with Tom that the new bug discovery rate has fallen to zero and that probably indicates that no new/ground-breaking tests are taking place. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] parser handling of large object OIDs
gram.y treats large object identifiers inconsistently. The privileges
stuff treats them as IConst:
| LARGE_P OBJECT_P Iconst_list
{
PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget));
n-targtype = ACL_TARGET_OBJECT;
n-objtype = ACL_OBJECT_LARGEOBJECT;
n-objs = $3;
$$ = n;
}
| ALTER LARGE_P OBJECT_P Iconst OWNER TO RoleId
{
AlterOwnerStmt *n = makeNode(AlterOwnerStmt);
n-objectType = OBJECT_LARGEOBJECT;
n-object = list_make1(makeInteger($4));
n-newowner = $7;
$$ = (Node *)n;
}
But the comment code treats them as NumericOnly.
| COMMENT ON LARGE_P OBJECT_P NumericOnly IS comment_text
{
CommentStmt *n = makeNode(CommentStmt);
n-objtype = OBJECT_LARGEOBJECT;
n-objname = list_make1($5);
n-objargs = NIL;
n-comment = $7;
$$ = (Node *) n;
}
I believe that the comment code is probably right, because I think
IConst can only handle values 2^31, whereas OIDs can be as large as
2^32-1.
Thoughts?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres 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] parser handling of large object OIDs
On Wed, Jun 9, 2010 at 5:02 PM, Robert Haas robertmh...@gmail.com wrote:
I believe that the comment code is probably right, because I think
IConst can only handle values 2^31, whereas OIDs can be as large as
2^32-1.
I investigated this a little more and the above analysis turns out to
be correct. ALTER LARGE OBJECT OWNER and GRANT ... ON LARGE OBJECT
don't work for large objects outside the range of a signed integer.
Session demonstrating the problem and proposed patch attached.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company
rhaas=# select lo_create('40');
lo_create
40
(1 row)
rhaas=# comment on large object 40 is 'big!';
COMMENT
rhaas=# create user bob;
CREATE ROLE
rhaas=# grant all privileges on large object 40 to bob;
ERROR: syntax error at or near 40
LINE 1: grant all privileges on large object 40 to bob;
^
rhaas=# select lo_create('8');
lo_create
---
8
(1 row)
rhaas=# grant all privileges on large object 8 to bob;
GRANT
rhaas=# comment on large object 8 is 'smaller';
COMMENT
lobjoidfix.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] failover vs. read only queries
Fujii Masao masao.fu...@gmail.com wrote: 1. Reset max_standby_delay = 0 in postgresql.conf 2. pg_ctl reload 3. Create a trigger file As far as I read the HS code, SIGHUP is not checked while a recovery is waiting for queries :( So pg_ctl reload would have no effect on the conflicting queries. Independently from the problem I raised, I think that we should call HandleStartupProcInterrupts() in that sleep loop. Hmmm, if reload doesn't work, can we write a query like below? SELECT pg_terminate_backend(pid) FROM pg_locks WHERE conflicted-with-recovery-process; Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 'create or replace function' no longer allows parameters
In 8.4.4 I used to be able to rename input parameters via create or replace function. In 9.0 beta2 this no longer is allowed, and I get a descriptive message informing me to use drop function instead, but I couldn't find this documented anywhere as a change between 8.4 and 9.0. -- test=# CREATE FUNCTION test_plpgsql(IN a integer) RETURNS integer AS test-# $BODY$ test$# BEGIN test$# RETURN $1; test$# END; test$# $BODY$ test-# LANGUAGE 'plpgsql' STABLE; CREATE FUNCTION test=# SELECT * FROM test_plpgsql(34); test_plpgsql -- 34 (1 row) test=# CREATE OR REPLACE FUNCTION test_plpgsql(IN b integer) RETURNS integer AS $BODY$ BEGIN RETURN $1; END; $BODY$ LANGUAGE 'plpgsql' STABLE; CREATE FUNCTION test=# SELECT * FROM test_plpgsql(34); test_plpgsql -- 34 (1 row) test=# \df+ test_plpgsql List of functions Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | Source code | Description +--+--+-+++--+--++- public | test_plpgsql | integer | b integer | normal | stable | dgardner | plpgsql || : BEGIN : RETURN $1; : END; : (1 row) --- psql (9.0beta2) Type help for help. test=# CREATE FUNCTION test_plpgsql(IN a integer) RETURNS integer AS test-# $BODY$ test$# BEGIN test$# RETURN $1; test$# END; test$# $BODY$ test-# LANGUAGE 'plpgsql' STABLE; CREATE FUNCTION test=# CREATE OR REPLACE FUNCTION test_plpgsql(IN b integer) RETURNS integer AS test-# $BODY$ test$# BEGIN test$# RETURN $1; test$# END; test$# $BODY$ test-# LANGUAGE 'plpgsql' STABLE; ERROR: cannot change name of input parameter a HINT: Use DROP FUNCTION first. -- David Gardner Pipeline Tools Programmer Jim Henson Creature Shop dgard...@creatureshop.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] How about closing some Open Items?
Excerpts from Robert Haas's message of mar jun 08 17:01:28 -0400 2010: I think only one of them has an actual patch associated with it, viz: ALTER TABLE .. DISABLE/ENABLE TRIGGER are out of date I took a look at that patch, and I expect it's probably correct, but I haven't actually looked at the code to verify that it's correct, so I didn't apply it. I checked the behavior and the patch is correct, so I applied it. Thanks for clearing the list. There are only 5 remaining items, which is kinda exciting, though Tom's assertion that HS is still bug-ridden is a bit off-putting. -- Á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] 'create or replace function' no longer allows parameters
On Wed, Jun 9, 2010 at 8:55 PM, David Gardner dgard...@creatureshop.com wrote: In 8.4.4 I used to be able to rename input parameters via create or replace function. In 9.0 beta2 this no longer is allowed, and I get a descriptive message informing me to use drop function instead, but I couldn't find this documented anywhere as a change between 8.4 and 9.0. This is a consequence of the change to allow functions to be called using named notation rather than positional notation. http://developer.postgresql.org/pgdocs/postgres/sql-syntax-calling-funcs.html There could be a view somewhere that depends on calling the function using the old input parameter name, and we have no way to detect that case, so we disallow changing or dropping names (but you can add them where they aren't already present) so as to avoid silent view breakage. It's a bit unfortunate, but I'm not sure there's much help for it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] InvalidXLogRecPtr in docs
I found a term InvalidXLogRecPtr in 9.0 docs. http://developer.postgresql.org/pgdocs/postgres/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE | ... then the return value will be InvalidXLogRecPtr (0/0). I think it should not appear in docs because it's a name for an internal constant variable. I'd like to rewrite the description like: ... then the return value will be 0/0, that is never used in normal cases. Comments? Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 'create or replace function' no longer allows parameters
Excerpts from David Gardner's message of mié jun 09 20:55:36 -0400 2010: In 8.4.4 I used to be able to rename input parameters via create or replace function. In 9.0 beta2 this no longer is allowed, and I get a descriptive message informing me to use drop function instead, but I couldn't find this documented anywhere as a change between 8.4 and 9.0. It's this patch: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=e7eb1113f8a95e9927fdbe9cc6fb0ac101612be2#patch7 It should probably be mentioned in the incompatibilities section of the 9.0 release notes. -- Á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] InvalidXLogRecPtr in docs
On Wed, Jun 9, 2010 at 9:46 PM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: I found a term InvalidXLogRecPtr in 9.0 docs. http://developer.postgresql.org/pgdocs/postgres/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE | ... then the return value will be InvalidXLogRecPtr (0/0). I think it should not appear in docs because it's a name for an internal constant variable. I'd like to rewrite the description like: ... then the return value will be 0/0, that is never used in normal cases. Comments? Maybe we should be returning NULL instead of 0/0. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] failover vs. read only queries
The fact that failover current does *not* terminate existing queries and transactions was regarded as a feature by the audience, rather than a bug, when I did demos of HS/SR. Of course, they might not have been thinking of the delay for writes. Probably you would hear different respose from serious users who are willing to have usable HA systems. I have number of customers who are using our HA systems (they use several technologies such as commercial HA solutions, pgpool-II and Slony-I). The one of top 3 questions I got when we propose them our HA solution is, how long will it take to do failover when the master DB crashes? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] parser handling of large object OIDs
Robert Haas robertmh...@gmail.com writes: On Wed, Jun 9, 2010 at 5:02 PM, Robert Haas robertmh...@gmail.com wrote: I believe that the comment code is probably right, because I think IConst can only handle values 2^31, whereas OIDs can be as large as 2^32-1. I investigated this a little more and the above analysis turns out to be correct. ALTER LARGE OBJECT OWNER and GRANT ... ON LARGE OBJECT don't work for large objects outside the range of a signed integer. Yup. Session demonstrating the problem and proposed patch attached. This patch seems extremely grotty, though. Surely that's not the way we were doing it in the comment code? 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] failover vs. read only queries
On 10/06/10 14:07, Tatsuo Ishii wrote: The one of top 3 questions I got when we propose them our HA solution is, how long will it take to do failover when the master DB crashes? Same here +1 -- Sent 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 or feature? Timestamp parsing
Josh Berkus j...@agliodbs.com writes: It appears that the ts parser will ignore any punctuation surrounding the special value calls. The datetime parser ignores extraneous punctuation all over the place, not only with regards to special values. I'm hesitant to monkey with that, because there are so many weird date formats out there. On the other hand, it appears that our timestamps have had this bug since at least 8.0, so it clearly isn't a widespread problem for most users. It's had that behavior since Tom Lockhart was messing with it, maybe even before that. 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] InvalidXLogRecPtr in docs
Robert Haas robertmh...@gmail.com writes: On Wed, Jun 9, 2010 at 9:46 PM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: I found a term InvalidXLogRecPtr in 9.0 docs. http://developer.postgresql.org/pgdocs/postgres/functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE | ... then the return value will be InvalidXLogRecPtr (0/0). Maybe we should be returning NULL instead of 0/0. +1 for using NULL instead of an artificially chosen value, for both of those functions. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Add _PG_init to PL language handler documentation
On Tue, Jun 08, 2010 at 04:18:08PM -0400, Robert Haas wrote: On Wed, May 26, 2010 at 11:24 PM, Joshua Tolley eggyk...@gmail.com wrote: On Wed, May 26, 2010 at 03:47:25PM -0400, Robert Haas wrote: On Tue, May 25, 2010 at 4:34 AM, Jonathan Leto jal...@gmail.com wrote: This tiny doc patch adds _PG_init to the skeleton example code for a PL. The information is quite valuable to PL authors, who might miss it when it is described in the shared library documentation. I'm not sure it does much good to add it to the template without any explanation of what it does. What might make more sense is to add a cross-reference to the section on dynamic loading, where this is documented. +1. How about the attached (which, incidentally, tested successfully on my box, because I've managed to achieve doc building nirvana through blindly flailing about until it worked...)? I've committed a doc change along these lines, but I thought your version was a little wordy and maybe not in the best spot, so I did it a bit differently. Hopefully it's good - if not, I can always change it again... You're far from the first to label one of my productions wordy :) Your version suits me fine (btw, in case anyone else is looking for it, it's here: http://archives.postgresql.org/pgsql-committers/2010-06/msg00060.php) -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
[HACKERS] fix use of posix_fadvise in xlog.c
Hi all, I wanted to propose a fix for to xlog.c regarding the use of posix_fadvise() for 9.1 (unless someone feels it's ok for 9.0). Currently posix_fadvise() is used right before a log file is closed so it's effectively not doing anything, when posix_fadvise is to be called. This patch moves the posix_fadvise() call into 3 other locations within XLogFileInit() where a file handle is returned. The first case is where an existing open file handle is returned. The next case is when a file is to be zeroed out. The third case is returning a file handle, which may be the file that was just zeroed out. Does this look ok? Regards, Mark 0001-Fix-use-of-posix_fadvise-in-xlog.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
