Re: [HACKERS] A deprecation policy
Peter Eisentraut wrote: I would also extend this system to removed configuration settings, e.g., max_fsm_*. We should make these deprecated for one release, so (1) configuration files can be upgraded without manual work (relevant to in-place upgrade), and (2) users are alerted that their carefully crafted configuration might need a review. I'd prefer to have the upgrade tool convert the old configuration file, comment out max_fsm_* for example. The upgrade tool could well print out warnings about config file changes. I think you'll have a better chance of getting the administrators attention when he's running the upgrade tool than by printing warnings to the log. -- 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] A deprecation policy
Hi, Peter Eisentraut wrote: I have been thinking, with a semi-formal deprecation policy, we could make these decisions with more confidence. +1 (I'm reading this as a very general proposal also targeting C APIs, not only GUCs). Comments? With the proposed policy we'd have to recommend users to upgrade in steps of at least every 2nd release for these obsoleteness and deprecation warnings to have any effect. However, I'm often seeing users taking pretty large steps like upgrading from 7.4 to 8.3. OTOH those users are certainly prepared for major incompatibilities and prepared to adjust their code. However, the value of 2 in N+2 is certainly debatable. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 64 bit PostgreSQL 8.3.6 build on AIX 5300-09-02-0849 with IBM XL C/C++ 10.1.0.1 - initdb fails (could not dump unrecognized node type: 650)
Tom Lane wrote: So it seems we have a couple of problems here. Using xlc_r or xlC_r or adding -q64 to CC (rather than CFLAGS which is where it really belongs) will confuse this check. Correction: Flags that determine the architecture usually belong in CC, not in CFLAGS. Test case for when a flag belongs into CC instead of CFLAGS: To compile a file, you use $(CC) $(CPPFLAGS) $(CFLAGS). To preprocess a file, you use $(CPP) $(CPPFLAGS), where CPP is usually $(CC) -E. If you have a compiler flag that applies for preprocessing, you must put it into CC. Another class of flags that is typically put into CC are those selecting the C standards mode. Would it be reasonable to change the test quoted above to elif test $PORTNAME = aix; then ... that is try -qnoansialias anytime the compiler isn't gcc and the platform is aix? Is xlc used on any platform other than aix? That would probably make sense. I think we just never supported xlc_r, and the threading code rejects a separate thread-safe compiler. Also, has anyone got a clue what the switches selected in src/template/aix actually do, and whether they still make sense for modern AIX versions? -qmaxmem=16384 is the memory for optimizations (in bytes). -qsrcmsg prints the affected code line in a compiler error (instead of just file name and line number). -qlonglong is clear. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] DISCARD ALL failing to acquire locks on pg_listen
Hi everyone, I've been recently testing PostgreSQL 8.3.4 (upgrade to 8.3.6 is scheduled) with a large number of connections from separate boxes each using a locally installed pgpool-II set in connection pooling mode, for up to 2500 concurrent open connections. Given I was using 8.3, it seemed quite right to set the reset statement to ABORT; DISCARD ALL. Everything works fine, until a load spike happens and pgpool-II reset queries start to lag behind, with DISCARD ALL failing to acquire an exclusive locks on the pg_listen system table, although the application isn't using any LISTEN/NOTIFY. The reason was not obvious to me, but looking at the man page explained a lot: DISCARD ALL also performs an UNLISTEN *. Since then I've crafted the reset query to only reset what is actually used by the application, and things are going much better. I vaguely remember that a full LISTEN/NOTIFY overhaul is in the to-do list with low priority, but my point is that DISCARD can be a bottleneck when used in the scenario it is designed for, i.e. high concurrency access from connection poolers. I've been looking to the source code and I understand that async operations are performed acquiring an exclusive lock at the end of the transaction, but I have a proof of concept patch that avoids it in case there are no pending listens or notifies and the backend is not already listening. I didn't have time to test it yet, but I can devote a little bit more time to it in case it makes sense to you. Cheers -- Matteo Beccati OpenX - http://www.openx.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DISCARD ALL failing to acquire locks on pg_listen
Thanks for valuable info! I'm going to add a caution to pgpool-II's docs. DISCARD ALL will cause serious performance degration. -- Tatsuo Ishii SRA OSS, Inc. Japan Hi everyone, I've been recently testing PostgreSQL 8.3.4 (upgrade to 8.3.6 is scheduled) with a large number of connections from separate boxes each using a locally installed pgpool-II set in connection pooling mode, for up to 2500 concurrent open connections. Given I was using 8.3, it seemed quite right to set the reset statement to ABORT; DISCARD ALL. Everything works fine, until a load spike happens and pgpool-II reset queries start to lag behind, with DISCARD ALL failing to acquire an exclusive locks on the pg_listen system table, although the application isn't using any LISTEN/NOTIFY. The reason was not obvious to me, but looking at the man page explained a lot: DISCARD ALL also performs an UNLISTEN *. Since then I've crafted the reset query to only reset what is actually used by the application, and things are going much better. I vaguely remember that a full LISTEN/NOTIFY overhaul is in the to-do list with low priority, but my point is that DISCARD can be a bottleneck when used in the scenario it is designed for, i.e. high concurrency access from connection poolers. I've been looking to the source code and I understand that async operations are performed acquiring an exclusive lock at the end of the transaction, but I have a proof of concept patch that avoids it in case there are no pending listens or notifies and the backend is not already listening. I didn't have time to test it yet, but I can devote a little bit more time to it in case it makes sense to you. Cheers -- Matteo Beccati OpenX - http://www.openx.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] temporarily stop autovacuum
Joshua D. Drake wrote: On Tue, 2009-02-10 at 10:15 +0900, Tatsuo Ishii wrote: Hi, Is there any way to stop autovacuum temporarily?(other than edit postgresql.conf and reload it) Pgpool-II does not want autovacuum running while doing onlie recovery. It would be a significant hack but you could update pg_autovacuum to set all relations to false. Which will no longer work in 8.4. More generally, it was pointed out to me that users apparently do updates of pg_autovacuum to change settings on a bunch of tables at once. We might get some complaints if we remove that facility. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WIP: hooking parser
Hello
some years ago there was some plans about parser's extensibility. I am
able write bison extensions, but I thing, so lot of work should be
done via hooking of transform stage.
I did small example - real implementation of Oracle's decode function.
It's based on hooking transformExpr function.
It works. And I thing, this should to solve lot of special task
related to increase compatibility with Oracle, Informix, or it could
be usefull for some others (json support).
postgres=# load 'decode';
LOAD
postgres=# select
decode(null::integer,2,'ahoj',3,'Petr',1,'Pavel',null, 'jaja');
decode
jaja
(1 row)
postgres=# select decode(3,2,'ahoj',3,'Petr',1,'Pavel',null, 'jaja');
decode
Petr
(1 row)
postgres=# select decode(6,2,'ahoj',3,'Petr',1,'Pavel',null, 'jaja');
decode
(1 row)
postgres=# select decode(6,2,'ahoj',3,'Petr',1,'Pavel',null, 'jaja', 'Milos');
decode
Milos
(1 row)
Any ideas, notes?
regards
Pavel Stehule
/*-
*
* decode.c
*
*
* Copyright (c) 2008-2009, PostgreSQL Global Developent Group
*
* IDENTIFICATION
*$PostgreSQL: pgsql/contrib/auto_explain/auto_explain.c,v 1.4
2009/01/05 13:35:38 tgl Exp $
*
*-
*/
#include postgres.h
#include fmgr.h
#include catalog/namespace.h
#include nodes/makefuncs.h
#include nodes/nodeFuncs.h
#include catalog/pg_type.h
#include parser/parse_coerce.h
#include parser/parse_expr.h
PG_MODULE_MAGIC;
/* Saved hook value */
static ParseExprTransform_hook_type prev_transformExpr = NULL;
void_PG_init(void);
void_PG_fini(void);
static Node * transformDecode(ParseState *pstate, Node *expr);
/*
* Module load callback
*/
void
_PG_init(void)
{
/* Install hooks. */
prev_transformExpr = ParseExprTransform_hook;
ParseExprTransform_hook = transformDecode;
}
/*
* Module unload callback
*/
void
_PG_fini(void)
{
/* Uninstall hooks. */
ParseExprTransform_hook = prev_transformExpr;
}
/*
* Decode transform hook. When I diagnose decode func call, I transform it.
*/
Node *
transformDecode(ParseState *pstate, Node *expr)
{
if (IsA(expr, FuncCall))
{
FuncCall *fnc = (FuncCall *) expr;
char*schemaname;
char*funcname;
DeconstructQualifiedName(fnc-funcname, schemaname, funcname);
if (schemaname != NULL strncmp(schemaname, pg_catalog, 10)
!= 0)
goto not_decode_func;
if (strncmp(funcname, decode, 6) == 0)
{
CaseExpr *newc = makeNode(CaseExpr);
int pos = 0;
ListCell*l;
int def_pos;
int nargs = list_length(fnc-args);
CaseTestExpr *placeholder = NULL;
CaseWhen *neww = NULL;
List*newargs = NIL;
List*resultexprs = NIL;
Node*defresult = NULL;
Oid ptype;
def_pos = nargs % 2 == 0 ? nargs - 1: -1;
foreach(l, fnc-args)
{
/* first param, generate placeholder */
if (pos == 0)
{
Node *arg = transformExpr(pstate, (Node
*) lfirst(l));
if (exprType(arg) == UNKNOWNOID)
arg =
coerce_to_common_type(pstate, arg, TEXTOID, DECODE);
placeholder = makeNode(CaseTestExpr);
placeholder-typeId = exprType(arg);
placeholder-typeMod = exprTypmod(arg);
newc-arg = (Expr *) arg;
}
/* searched value, generate CaseWhen node */
if (pos % 2 != 0 pos != def_pos)
{
Node *warg;
Node *expr = (Node *) lfirst(l);
neww = makeNode(CaseWhen);
neww-location = exprLocation(expr);
warg = (Node *) makeA_Expr(AEXPR_NOT,
NIL,
Re: [HACKERS] [PATCH] Psql List Languages
Fernando Ike wrote: I know that this moment is inappropriate to submit patch, with the discussions about features for 8.4. But, if can added for commitfest to 8.5 version. I'm appreciate. Yes, please add it to the first 8.5 commit fest. You can edit the wiki yourself. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Note that it introduces a LEFT JOIN on pg_class to itself that's always present, even for server versions that do not support reloptions. Personally I'd be more worried about the unnest(). Also, please schema-qualify that function name; you can't assume anything about the search path here. Maybe it would be more elegant to put the search_path into proconfig? This would have some advantages: 0. Looks less weird. 1. We could quasi-automatically verify that all SQL-language functions have the correct search path (or even set it in initdb). 2. On things like unnest, which is a language element that we happen to implement as a function now, you don't have to worry about it one way or the other. In a shell script, you'd usually set the path at the top instead of writing out the directories of every command. It looks better (reason 0), it's easier to analyze (e.g., lintian) (reason 1), and it avoids confusion with shell built-ins (reason 2). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQinitSSL broken in some use casesf
On Feb 11, 2009, at 12:12 AM, Andrew Chernow a...@esilo.com wrote: Robert Haas wrote: I am not in love with the idea of using PQinitSSL(SOME_MAGIC_VALUE) The issue I see is the inability to toggle crypto initialization. I think crypto init and ssl init are 2 different things. Thus, I proposed the below: http://archives.postgresql.org/pgsql-hackers/2009-02/msg00488.php If that can be made transparent to existing apps, I'm all for it. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimization rules for semi and anti joins
On Tue, Feb 10, 2009 at 08:12:56PM -0500, Jonah H. Harris wrote: On Tue, Feb 10, 2009 at 8:09 PM, Jonah H. Harris jonah.har...@gmail.comwrote: On Tue, Feb 10, 2009 at 3:10 PM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote (in response to Kevin Grittner's recent issues): Reflecting on this further, I suspect there are also some bugs in the planner's rules about when semi/antijoins can commute with other joins; After doing some math I've concluded this is in fact the case. Anyone want to check my work? FWIW, the logic looks correct to me. Cripes! I just had an idea and it looks like the buggers beat me to it :( http://www.google.com/patents?id=4bqBEBAJdq=null+aware+anti-join As has been discussed here many, many times, the only kind of person who should be doing a patent search is a company's IP attorney, which you are not, and even if you were, under no circumstances would such a person paste that link in a public forum. Should we have a kick-off policy for this kind of misbehavior? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A deprecation policy
On Wed, Feb 11, 2009 at 2:47 AM, Peter Eisentraut pete...@gmx.net wrote: We often discuss changing user-visible behavior of various kinds and are usually clueless on the question of someone might rely on this or how many people are still using this etc. Still, it is clearly often useful to revise interfaces from time to time. I have been thinking, with a semi-formal deprecation policy, we could make these decisions with more confidence. My proposed policy goes like this: +1! This is a very good idea. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: B-Tree emulation for GIN
Looking through the code again, gin_compare_prefix_##type looks a little confusing. Is there a reason for using: (data-strategy == BTLessStrategyNumber || data-strategy == BTLessEqualStrategyNumber ) ? PointerGetDatum(data-datum) : a rather than just using: PointerGetDatum(data-datum) Added comments: /* * Datum a is a value from extract_query method and for BTLess* * strategy it is a left-most value. So, use original datum from * QueryInfo to decide stop scanning on not. Datum b is always * from index. */ Also, it might be a little less confusing if you used two separate variables rather than using res for two purposes. done -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ btree_gin-0.10.gz Description: Unix tar archive -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Copy PlannerInfo structure
Hi all, How can I copy the PlannerInfo structure? I have *root variable that is PlannerInfo and I would like to pass this value to another variable that I created (the same type - PlannerInfo). Can you help me? I only found function that copies PlannedStmt structure. Thanks, Ana Carolina
Re: [HACKERS] So, what locale should the regression tests run in?
Bruce Momjian wrote: Peter Eisentraut wrote: While regress/GNUmakefile appears to make an effort to run the regressions tests with or without locale depending on the users choice, .e.g., # locale NOLOCALE = ifdef NO_LOCALE NOLOCALE += --no-locale endif the pg_regress.c implementation spoils that completely by unconditionally unsetting all locale-related environment variables before initdb. One part of the code doesn't know what the other is doing here. To make it work as designed in GNUmakefile, the patch below would have to be applied. This leaves the question, what should really be the default behavior? Has this been resolved? Since nobody spoke up, I have changed it now so it runs with locale by default. Let's see if we get away with that. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQinitSSL broken in some use casesf
Robert Haas wrote: On Feb 11, 2009, at 12:12 AM, Andrew Chernow a...@esilo.com wrote: Robert Haas wrote: I am not in love with the idea of using PQinitSSL(SOME_MAGIC_VALUE) The issue I see is the inability to toggle crypto initialization. I think crypto init and ssl init are 2 different things. Thus, I proposed the below: http://archives.postgresql.org/pgsql-hackers/2009-02/msg00488.php If that can be made transparent to existing apps, I'm all for it. Defaulting the crypto initialization to TRUE should make this transparent. The downside is that this breaks backwards compatibility, since an unknown symbol is being referenced. Although, that only occurs when PQinitCrypto is actually used. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] Optimization rules for semi and anti joins
On Wed, Feb 11, 2009 at 8:05 AM, David Fetter da...@fetter.org wrote: As has been discussed here many, many times, the only kind of person who should be doing a patent search is a company's IP attorney, which you are not, and even if you were, under no circumstances would such a person paste that link in a public forum. First of all, it was not an intentional patent search. Secondly, I don't believe there's any restriction of explicitly what can and cannot be posted on a public Postgres mailing list. Should we have a kick-off policy for this kind of misbehavior? Shut up David. -- Jonah H. Harris, Senior DBA myYearbook.com
[HACKERS] HotStandby vs. flatfile updates
I'm currently facing with a strange behavior during HotStandby Testing. That's what i'm actually doing: MASTER: CREATE DATABASE foo; do something in there, e.g. restoring a dump wait until xlog segments get consumed by standby node (using archive_timeout) STANDBY: postgres=# SELECT oid, datname FROM pg_database; oid | datname ---+--- 1 | template1 11561 | template0 11562 | postgres 16384 | test 16390 | test2 17872 | bernd 17873 | foo $ psql foo DEBUG: forked new backend, pid=19967 socket=7 FATAL: database foo does not exist $ cat standby/global/pg_database template1 1 1663 666 template0 11561 1663 666 postgres 11562 1663 666 test 16384 1663 666 test2 16390 1663 666 bernd 17872 1663 666 Obviously, the pg_database file wasn't updated. I've traced through the recovery process a little bit and i see BuildFlatFile() called during xact_redo_commit(), however, write_database_file() doesn't write out the new database records. Funny thing: the same happened to the database bernd before, so it seems to me the new database record is not visible to write_database_file() during redo? -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A deprecation policy
On Wed, 11 Feb 2009 09:47:25 +0200 Peter Eisentraut pete...@gmx.net wrote: 1. In release N, an interface is declared obsolete, which means that [...] 2. In release N+1, obsolete interfaces are declared deprecated, which I like the idea but aren't these two terms reversed? In fact, isn't obsolete your third stage? Certainly obsolete suggests that it can't be used any longer. I'm not sure what the second stage should be called in that case though. Also, does the progression through releases have to be absolute? Can something stay in deprecated for two releases if it is felt that people need more time? Also, consider that we want to get in-place upgrade working, so essential interfaces such as basic commands and configuration files should at least be able to limp along after being moved to version N+1. Yes. Altering semantics of log_filename without placeholder (under discussion): Release 8.4: Declare current behavior obsolete. Release 8.5: Deprecation warning. Release 8.6: Implement whatever new behavior we like. But whatever works in 8.6 would also have to work in 8.4, right? We can't call something deprecated or obsolete without allowing the user to update their code/configuration right away. I would also extend this system to removed configuration settings, e.g., max_fsm_*. We should make these deprecated for one release, so (1) configuration files can be upgraded without manual work (relevant to in-place upgrade), and (2) users are alerted that their carefully crafted configuration might need a review. As long as they can remove the item giving the warning right away. -- D'Arcy J.M. Cain da...@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Peter Eisentraut pete...@gmx.net writes: Tom Lane wrote: Personally I'd be more worried about the unnest(). Also, please schema-qualify that function name; you can't assume anything about the search path here. Maybe it would be more elegant to put the search_path into proconfig? This is psql's describe.c, not a sql function. It hasn't got a proconfig, and it doesn't seem like a particularly good idea to make it try to replace the user's search path setting directly. 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] [PATCHES] GIN improvements
But the real bottom line is: if autovacuum is working properly, it should clean up the index before the list ever gets to the point where it'd be sane to turn off indexscans. So I don't see why we need to hack the planner for this at all. If any hacking is needed, it should be in the direction of making sure autovacuum puts sufficient priority on this task. Autovacuum will start if table has GIN index with fastupdate=on and number of inserted tuple since last vacuum autovacuum_vacuum_threshold. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GIN fast insert
What would be wrong with letting it degrade to lossy? I suppose the reason it's trying to avoid that is to avoid having to recheck all the rows on that page when it comes time to do the index insertion; but surely having to do that is better than having arbitrary, unpredictable failure conditions. No, I don't think that's it. See here, beginning with the problem with lossy tbm has two aspects: http://archives.postgresql.org/message-id/4974b002.3040...@sigaev.ru Right. Some comments to that points: - amgettuple interface hasn't possibility to work with page-wide result instead of exact ItemPointer. amgettuple can not return just a block number as amgetbitmap can. It's not so difficult to teach GIN to return ItemPointer one by one from pending list and eliminate this point. GIN will not collect matched ItemPointers in tidbitmap and will return them immediately. But: - Because of concurrent vacuum process: while we scan pending list, it's content could be transferred into regular structure of index and then we will find the same tuple twice. Again, amgettuple hasn't protection from that, only amgetbitmap has it. So, we need to filter results from regular GIN by results from pending list. ANd for filtering we can't use lossy tbm. Again, we talk about amgettuple interface. We need to filter results from regular GIN by results from pending list. Now GIN does it by lookup matched ItemPointer in tidbitmap constructed from pending list. We could use ordered array of ItemPointers instead of tidbitmap, but I don't believe that it will take less memory. It's impossible to rescan pending list for two reasons: a) too slow, b) concurrent cleanup process (vacuum or insert now), because they could move tuples into regular structure. Is it acceptable to add option to tidbitmap which will forbid tidbitmap to become lossy? That removes disabling index scan in gincostestimate and checking of non-lossy tidbitmap. In current version, cleanup of pending list starts much earlier than non-lossy limit is reached in typical use-cases. Insertion process will start cleanup with at least one fired trigger: - number of heap tuples in pending list could produce lossy tidbitmap - total size of pending list is greater than work_mem. This trigger is developed to speedup bulk insertion (which is used in cleanup), because it will collect whole pending list in memory at once. And this trigger is more strict than first one because in typical use-case of GIN heap tuple is rather big. I believe that user could get GIN's error about work_mem only intentionally: - turn off autovacuum - set big work_mem - populate table with GIN index (by needed number of insertion) - prepare query which will return a lot of results (possibly, with seqscan=off because cost of scan of pending list grows fast) - decrease work_mem for at least ten times - execute query -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GIN fast insert
I believe that user could get GIN's error about work_mem only intentionally: - turn off autovacuum Meanwhile, in the other thread, we're having a discussion about people wanting to do exactly this on a database-wide basis during peak load hours... - set big work_mem - populate table with GIN index (by needed number of insertion) - prepare query which will return a lot of results (possibly, with seqscan=off because cost of scan of pending list grows fast) - decrease work_mem for at least ten times - execute query Why would the new work_mem need to be 10x smaller than the old work mem? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Copy PlannerInfo structure
Ana Carolina Brito de Almeida ana...@ig.com.br writes: How can I copy the PlannerInfo structure? There's no support for that. If you want a shallow copy it's just a memcpy; a deep copy is a bit of a problem because of the circular linkages in some of the planner data structures (meaning a simple copyObject-like approach wouldn't work). Why would you need to copy it anyway? 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] advance local xmin more aggressively
On Tue, Feb 10, 2009 at 3:06 PM, Jeff Davis pg...@j-davis.com wrote: With the new snapshot maintenance code, it looks like we can advance the xmin more aggressively. Can you clarify the circumstances in which this patch would show a benefit over the current system? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] updated hash functions for postgresql v1
Did this change hashtext() visible to users? We have been using it quite widely for partitioning our databases. If so then it should be marked quite visibly in release notes as there might be others who will be hit by this. regards Asko On Mon, Feb 9, 2009 at 11:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kenneth Marshall k...@rice.edu writes: I have updated the patch posted by Jeff Davis on January 9th to include the micro-patch above as well as updated the polymorphism regressions tests. This applies cleanly to the latest CVS pull. Applied --- thanks for being persistent about resolving the doubts on this. One thing that apparently neither of you realized was that the polymorphism results were varying between bigendian and littleendian machines; I suppose you are using different hardware and that's why you didn't agree on what the results should be. Since we already agreed we were going to tolerate endianness dependence in the hash functions, I fixed that by adding some ORDER BYs. 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] temporarily stop autovacuum
On Tue, Feb 10, 2009 at 8:53 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: I'm not sure that this calls for a change in autovacuum itself; it seems to be that whatwe really need is the ability to change postgresql.conf settings from the SQL interface. This has been discussed at length elsewhere, and I think we need to bite the bullet eventually. I'd like to take a crack at identifying the bullet that needs to be bitten here: comments. People like to use comments to document old settings that they may once have had, and why they changed them, and we also ship comments that document the meaning of many of our settings. IIRC, much of the last round of this discussion centered on where new settings would be inserted into the file (which might involve trying to identify the commented-out version of that setting), whether to comment out the old line for a particular setting and insert a new line (or just replace the old line), what to do about comments on the same line as the GUC, etc. Any solution that we attempt to engineer this problem is unlikely to be able to pass the Turing test, and so it's likely to get some cases wrong, as judged by the human intelligence of the person who wrote the comment that got masticated. If we resign ourselves to the fact that this will not work very well unless our postgresql.conf file is intended to be read and written primarily by machines, and only secondarily by humans when necessary to recover from a bad situation, we can make some progress. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimization rules for semi and anti joins
On Wed, Feb 11, 2009 at 09:36:38AM -0500, Jonah H. Harris wrote: On Wed, Feb 11, 2009 at 8:05 AM, David Fetter da...@fetter.org wrote: As has been discussed here many, many times, the only kind of person who should be doing a patent search is a company's IP attorney, which you are not, and even if you were, under no circumstances would such a person paste that link in a public forum. First of all, it was not an intentional patent search. I don't see anything in patent law or case law that talks about intentional. Secondly, I don't believe there's any restriction of explicitly what can and cannot be posted on a public Postgres mailing list. We have plenty of such restrictions. Take the Nazi spammer, for example, and what he's doing is just offensive and silly. What you're doing exposes people to real, substantive harm. Should we have a kick-off policy for this kind of misbehavior? Shut up David. Not a chance. This is a very big deal, as you are exposing every US PostgreSQL contributor to triple damages for knowing infringement. Are you saying you're going to pay all that out of your own pocket? Are you making a legal commitment, say, with a few tens of million dollars in escrow to back it? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] updated hash functions for postgresql v1
Asko Oja asc...@gmail.com writes: Did this change hashtext() visible to users? We have been using it quite widely for partitioning our databases. If so then it should be marked quite visibly in release notes as there might be others who will be hit by this. The hash functions are undocumented, have changed in the past, and are likely to change again in the future. If you are using them in a way that depends on them to give the same answers across versions, you'd better stop. 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] A deprecation policy
D'Arcy J.M. Cain da...@druid.net wrote: On Wed, 11 Feb 2009 09:47:25 +0200 Peter Eisentraut pete...@gmx.net wrote: 1. In release N, an interface is declared obsolete, which means [...] 2. In release N+1, obsolete interfaces are declared deprecated, I like the idea but aren't these two terms reversed? In fact, isn't obsolete your third stage? Certainly obsolete suggests that it can't be used any longer. I'm not sure what the second stage should be called in that case though. I had a similar reaction to the proposed terminology. To me: Deprecated means that some other way of doing it is available and preferred. Obsolescent (or perhaps in end of life period) indicates that something is expected to be removed in a future release. Obsolete means it used to work, but doesn't anymore. -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] WIP: hooking parser
Pavel Stehule pavel.steh...@gmail.com writes: some years ago there was some plans about parser's extensibility. I am able write bison extensions, but I thing, so lot of work should be done via hooking of transform stage. This strikes me as next door to useless, because it can only handle things that look like valid expressions to the existing grammar. So pretty much all you can do is weird sorts of functions, which are already accommodated at less effort with existing features such as function overloading. A hook check in that particular place is not going to have negligible performance impact, since it's going to be hit tens or hundreds or thousands of times per query rather than just once. So it's going to require more than a marginal use case to persuade me we ought to have 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] DISCARD ALL failing to acquire locks on pg_listen
Matteo Beccati p...@beccati.com writes: Given I was using 8.3, it seemed quite right to set the reset statement to ABORT; DISCARD ALL. Everything works fine, until a load spike happens and pgpool-II reset queries start to lag behind, with DISCARD ALL failing to acquire an exclusive locks on the pg_listen system table, although the application isn't using any LISTEN/NOTIFY. The reason was not obvious to me, but looking at the man page explained a lot: DISCARD ALL also performs an UNLISTEN *. Seems like we could/should fix UNLISTEN * to not do anything if it is known that the current backend never did any LISTENs. 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] advance local xmin more aggressively
On Wed, 2009-02-11 at 10:20 -0500, Robert Haas wrote: On Tue, Feb 10, 2009 at 3:06 PM, Jeff Davis pg...@j-davis.com wrote: With the new snapshot maintenance code, it looks like we can advance the xmin more aggressively. Can you clarify the circumstances in which this patch would show a benefit over the current system? In the current code, if the process is always holding at least one snapshot, the process' xmin never advances. That means VACUUM will never be able to reclaim tuples visible during the first snapshot taken during the transaction. With the patch, as long as snapshots are being released, the process' xmin will keep advancing to reflect the oldest snapshot currently held by that process. In order to accomplish that, every time a snapshot is released I have to look at every snapshot that the process still holds to find the new local minimum xmin. The current code will only change the process' xmin if there are no snapshots at all. As Tom pointed out, one of the assumptions I made writing the patch is not always true. I am still trying to determine the implications of that. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A deprecation policy
D'Arcy J.M. Cain da...@druid.net writes: Peter Eisentraut pete...@gmx.net wrote: I would also extend this system to removed configuration settings, e.g., max_fsm_*. We should make these deprecated for one release, so (1) configuration files can be upgraded without manual work (relevant to in-place upgrade), and (2) users are alerted that their carefully crafted configuration might need a review. As long as they can remove the item giving the warning right away. Well, they could only remove the item if it was *already* the case that it didn't do anything. In general, I think Peter neglected to address the question of whether deprecated objects/functions/etc still have their original functionality, and where along the path the replacement functionality starts to exist. It's certainly a bad idea to be throwing warnings about something that people still have to use. 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] Optimization rules for semi and anti joins
On Wed, Feb 11, 2009 at 11:19 AM, David Fetter da...@fetter.org wrote: This is a very big deal, as you are exposing every US PostgreSQL contributor to triple damages for knowing infringement. Are you saying you're going to pay all that out of your own pocket? Are you making a legal commitment, say, with a few tens of million dollars in escrow to back it? Per IRC, this discussion will (and likely should) be taken elsewhere. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] DISCARD ALL failing to acquire locks on pg_listen
Hi Tom, Given I was using 8.3, it seemed quite right to set the reset statement to ABORT; DISCARD ALL. Everything works fine, until a load spike happens and pgpool-II reset queries start to lag behind, with DISCARD ALL failing to acquire an exclusive locks on the pg_listen system table, although the application isn't using any LISTEN/NOTIFY. The reason was not obvious to me, but looking at the man page explained a lot: DISCARD ALL also performs an UNLISTEN *. Seems like we could/should fix UNLISTEN * to not do anything if it is known that the current backend never did any LISTENs. Ok, I'll take sometime tonight to give my patch a try and eventually submit it. Cheers -- Matteo Beccati OpenX - http://www.openx.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimization rules for semi and anti joins
David, * David Fetter (da...@fetter.org) wrote: On Wed, Feb 11, 2009 at 09:36:38AM -0500, Jonah H. Harris wrote: Secondly, I don't believe there's any restriction of explicitly what can and cannot be posted on a public Postgres mailing list. We have plenty of such restrictions. Take the Nazi spammer, for example, and what he's doing is just offensive and silly. What you're doing exposes people to real, substantive harm. Do you have a court case backing that statement? If not, then I think you're playing a bit too much of the lawyer for a public mailing list. This is a very big deal, as you are exposing every US PostgreSQL contributor to triple damages for knowing infringement. Again, it's not at all clear that such a claim would stand up in court and threatening to kick people off of public mailing lists for talking about patents is patently ridiculous. You could make a similar claim that we should go through our mail archive and remove any post that ever talked about a patent in case we're required to provide web access logs that show someone looked at a page that talked about a patent. And all of that without even bringing up the fact that core folks have talked about patents on this list in the past. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] WIP: hooking parser
2009/2/11 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: some years ago there was some plans about parser's extensibility. I am able write bison extensions, but I thing, so lot of work should be done via hooking of transform stage. This strikes me as next door to useless, because it can only handle things that look like valid expressions to the existing grammar. So pretty much all you can do is weird sorts of functions, which are already accommodated at less effort with existing features such as function overloading. Usually we don't need change syntax. But we need to control of coercion stage. I afraid so function overloading is bad when there lot of combination, and polymorphic functions are not enough. for some cases we need more polymorphic types - anyelement1, anyelement2, anyarray1, ... A hook check in that particular place is not going to have negligible performance impact, since it's going to be hit tens or hundreds or thousands of times per query rather than just once. So it's going to require more than a marginal use case to persuade me we ought to have it. Because this stage isn't repeated (I don't expect bigger performance impact), it's similar to other's hooks. But, sure, wrong hook should do strange things. It's risk. + argument - it increase customisability and allows gentle syntax tuning. Function decode is first sample from today morning. regards Pavel Stehule regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: hooking parser
Pavel Stehule pavel.steh...@gmail.com writes: 2009/2/11 Tom Lane t...@sss.pgh.pa.us: This strikes me as next door to useless, because it can only handle things that look like valid expressions to the existing grammar. So pretty much all you can do is weird sorts of functions, which are already accommodated at less effort with existing features such as function overloading. Usually we don't need change syntax. But we need to control of coercion stage. I afraid so function overloading is bad when there lot of combination, and polymorphic functions are not enough. for some cases we need more polymorphic types - anyelement1, anyelement2, anyarray1, ... Well, then we should go fix those things. A hook function whose purpose is to fundamentally change query semantics strikes me as a very dangerous thing anyway, because your queries either stop working or suddenly do something completely different if the hook happens not to be loaded. The hooks we've accepted to date are intended for either monitoring or experimentation with planner behavior, neither of which will change query semantics. 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] Optimization rules for semi and anti joins
Hello, On Tue, Feb 10, 2009 at 09:41:46PM +0100, Dimitri Fontaine wrote: Hi, Le 10 févr. 09 à 21:10, Tom Lane a écrit : I wrote (in response to Kevin Grittner's recent issues): Reflecting on this further, I suspect there are also some bugs in the planner's rules about when semi/antijoins can commute with other joins; After doing some math I've concluded this is in fact the case. Anyone want to check my work? I don't know how easy it would be to do, but maybe the Coq formal proof management system could help us here: http://coq.inria.fr/ The harder part in using coq might well be to specify the problem the way you just did, so... formal theorem proving and mechanized mathematics happen to be one of my research topics in the last few years; so I think that my experience could be helpful with such problems. (Actually instead of Coq I use HOL Light, whereas other people in my research group work with Coq; but both of them are for the same purpose) Perhaps a way to begin would be to start writing a formalization of the above rules, in order to assess the problem quickly, and then to get back to pg-hackers soon. Any comments/warnings/suggestions ? Thank you, Dr. Gianni Ciolli - 2ndQuadrant Italia PostgreSQL Training, Services and Support gianni.cio...@2ndquadrant.it | www.2ndquadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimization rules for semi and anti joins
David Fetter da...@fetter.org writes: On Wed, Feb 11, 2009 at 09:36:38AM -0500, Jonah H. Harris wrote: Secondly, I don't believe there's any restriction of explicitly what can and cannot be posted on a public Postgres mailing list. We have plenty of such restrictions. Take the Nazi spammer, for example, and what he's doing is just offensive and silly. My take on the Nazi spammer is that we're banning him because he's off-topic for these lists. We'd ban someone spamming on a less offensive topic too, as long as it was unrelated to Postgres. Unfortunately, while software patents are pretty offensive, they can hardly be said to be off-topic. 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] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Tom Lane wrote:
Alvaro Herrera alvhe...@commandprompt.com writes:
Note that it introduces a LEFT JOIN on pg_class to itself that's always
present, even for server versions that do not support reloptions.
Personally I'd be more worried about the unnest(). Also, please
schema-qualify that function name; you can't assume anything about
the search path here.
This version should fix these issues. I refrained from adding more ? :
expressions because it starts getting ugly for my taste.
Index: src/bin/psql/describe.c
===
RCS file: /home/alvherre/cvs/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.198
diff -c -p -r1.198 describe.c
*** src/bin/psql/describe.c 22 Jan 2009 20:16:08 - 1.198
--- src/bin/psql/describe.c 11 Feb 2009 01:19:11 -
***
*** 8,14
*
* Copyright (c) 2000-2009, PostgreSQL Global Development Group
*
! * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.198 2009-01-22 20:16:08 tgl Exp $
*/
#include postgres_fe.h
--- 8,14
*
* Copyright (c) 2000-2009, PostgreSQL Global Development Group
*
! * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.197 2009/01/20 02:13:42 momjian Exp $
*/
#include postgres_fe.h
*** describeOneTableDetails(const char *sche
*** 871,876
--- 871,877
bool verbose)
{
PQExpBufferData buf;
+ PQExpBufferData reloptbuf;
PGresult *res = NULL;
printTableOpt myopt = pset.popt.topt;
printTableContent cont;
*** describeOneTableDetails(const char *sche
*** 908,923
initPQExpBuffer(title);
initPQExpBuffer(tmpbuf);
/* Get general table info */
printfPQExpBuffer(buf,
! SELECT relchecks, relkind, relhasindex, relhasrules, %s,
! relhasoids
! %s%s\n
! FROM pg_catalog.pg_class WHERE oid = '%s',
! (pset.sversion = 80400 ? relhastriggers : reltriggers 0),
! (pset.sversion = 80200 verbose ?
! , pg_catalog.array_to_string(reloptions, E', ') : ,''),
! (pset.sversion = 8 ? , reltablespace : ),
oid);
res = PSQLexec(buf.data, false);
if (!res)
--- 909,944
initPQExpBuffer(title);
initPQExpBuffer(tmpbuf);
+ if (verbose)
+ {
+ initPQExpBuffer(reloptbuf);
+ if (pset.sversion = 80400)
+ {
+ printfPQExpBuffer(reloptbuf,
+ pg_catalog.array_to_string(c.reloptions ||
+ array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n);
+ }
+ else if (pset.sversion = 80200)
+ {
+ printfPQExpBuffer(reloptbuf,
+ pg_catalog.array_to_string(c.reloptions, ', ')\n);
+ }
+ else
+ printfPQExpBuffer(reloptbuf, ''\n);
+ }
+
+
/* Get general table info */
printfPQExpBuffer(buf,
! SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, %s,
! c.relhasoids,
! %s%s\n
! FROM pg_catalog.pg_class c\n
! LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n
! WHERE c.oid = '%s'\n,
! (pset.sversion = 80400 ? c.relhastriggers : c.reltriggers 0),
! verbose ? reloptbuf.data : '',
! (pset.sversion = 8 ? , c.reltablespace : ),
oid);
res = PSQLexec(buf.data, false);
if (!res)
*** error_return:
*** 1672,1677
--- 1693,1700
termPQExpBuffer(buf);
termPQExpBuffer(title);
termPQExpBuffer(tmpbuf);
+ if (verbose)
+ termPQExpBuffer(reloptbuf);
if (seq_values)
{
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] advance local xmin more aggressively
Jeff Davis pg...@j-davis.com writes: On Wed, 2009-02-11 at 10:20 -0500, Robert Haas wrote: Can you clarify the circumstances in which this patch would show a benefit over the current system? In the current code, if the process is always holding at least one snapshot, the process' xmin never advances. Right, and the question is the scope of the circumstances in which that's the case and your patch makes things better. I believe that * a process outside a transaction has no snapshots, so your patch won't change anything * a process in a serializable transaction will hold the serializable snapshot till end of xact, so your patch won't change anything * a process in a read-committed transaction will typically hold snapshot(s) for the duration of each query, and then release them all, so your patch won't change anything You pointed out the case of opening a cursor in a read-committed transaction, and then closing it before end of transaction, as a place where your patch could hope to improve matters. Are there others? Could your application be made to close that cursor before opening another one (so that its set of open snapshots momentarily goes to zero)? It seems like the use case for more complex bookkeeping for open snapshots is a tad narrow. 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] temporarily stop autovacuum
Peter Eisentraut wrote: Joshua D. Drake wrote: It would be a significant hack but you could update pg_autovacuum to set all relations to false. Which will no longer work in 8.4. More generally, it was pointed out to me that users apparently do updates of pg_autovacuum to change settings on a bunch of tables at once. We might get some complaints if we remove that facility. Hmm, argh. Maybe we do need the rule on a fake pg_autovacuum that Itagaki-san was proposing. There's a problem however; for pg_autovacuum you used to need to insert some -1 values on columns on which you wanted to keep as defaults. On the new code you need to skip the value altogether, and a -1 is rejected with an error. Not sure how would we translate that. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimization rules for semi and anti joins
Gianni Ciolli gianni.cio...@2ndquadrant.it writes: On Tue, Feb 10, 2009 at 09:41:46PM +0100, Dimitri Fontaine wrote: I don't know how easy it would be to do, but maybe the Coq formal proof management system could help us here: http://coq.inria.fr/ The harder part in using coq might well be to specify the problem the way you just did, so... formal theorem proving and mechanized mathematics happen to be one of my research topics in the last few years; so I think that my experience could be helpful with such problems. Unless you've got a prover that already understands the concepts of outer joins etc, I'd think that teaching it about that would require enough work and introduce enough possibilities for human error so as to make the exercise pretty much moot. The identities I put up don't look that complicated to me... 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] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Alvaro Herrera alvhe...@commandprompt.com writes: This version should fix these issues. I refrained from adding more ? : expressions because it starts getting ugly for my taste. I think you might as well just introduce two separate code paths to produce the 8.4 and pre-8.4 queries, so that you don't need the LEFT JOIN in the pre-8.4 path. IMHO the cut-and-paste way that we usually do it in pg_dump is a whole lot easier to read and maintain than this sort of ?: spaghetti. 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] temporarily stop autovacuum
On Wed, 2009-02-11 at 14:21 -0300, Alvaro Herrera wrote: Peter Eisentraut wrote: Joshua D. Drake wrote: It would be a significant hack but you could update pg_autovacuum to set all relations to false. Which will no longer work in 8.4. More generally, it was pointed out to me that users apparently do updates of pg_autovacuum to change settings on a bunch of tables at once. We might get some complaints if we remove that facility. I got plenty of complaints that aren't being fixed :). pg_dump doesn't even know how to deal with pg_autovacuum, changing scripts to handle their autovacuum modifications won't take much. Hmm, argh. Maybe we do need the rule on a fake pg_autovacuum that Itagaki-san was proposing. I don't think so. A clean cut is the way to go. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Tom Lane wrote:
Alvaro Herrera alvhe...@commandprompt.com writes:
This version should fix these issues. I refrained from adding more ? :
expressions because it starts getting ugly for my taste.
I think you might as well just introduce two separate code paths to
produce the 8.4 and pre-8.4 queries, so that you don't need the LEFT
JOIN in the pre-8.4 path.
Right, see attached. (Separating the last two cases is probably
overkill ...?) I tested with HEAD, 8.2 and 8.0, seems to work fine.
IMHO the cut-and-paste way that we usually
do it in pg_dump is a whole lot easier to read and maintain than this
sort of ?: spaghetti.
Hmm, so should we try to get rid of them in a more consistent fashion?
Index: src/bin/psql/describe.c
===
RCS file: /home/alvherre/cvs/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.198
diff -c -p -r1.198 describe.c
*** src/bin/psql/describe.c 22 Jan 2009 20:16:08 - 1.198
--- src/bin/psql/describe.c 11 Feb 2009 17:57:03 -
***
*** 8,14
*
* Copyright (c) 2000-2009, PostgreSQL Global Development Group
*
! * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.198 2009-01-22 20:16:08 tgl Exp $
*/
#include postgres_fe.h
--- 8,14
*
* Copyright (c) 2000-2009, PostgreSQL Global Development Group
*
! * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.197 2009/01/20 02:13:42 momjian Exp $
*/
#include postgres_fe.h
*** describeOneTableDetails(const char *sche
*** 909,924
initPQExpBuffer(tmpbuf);
/* Get general table info */
! printfPQExpBuffer(buf,
! SELECT relchecks, relkind, relhasindex, relhasrules, %s,
! relhasoids
! %s%s\n
! FROM pg_catalog.pg_class WHERE oid = '%s',
! (pset.sversion = 80400 ? relhastriggers : reltriggers 0),
! (pset.sversion = 80200 verbose ?
! , pg_catalog.array_to_string(reloptions, E', ') : ,''),
! (pset.sversion = 8 ? , reltablespace : ),
! oid);
res = PSQLexec(buf.data, false);
if (!res)
goto error_return;
--- 909,959
initPQExpBuffer(tmpbuf);
/* Get general table info */
! if (pset.sversion = 80400)
! {
! printfPQExpBuffer(buf,
! SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
! c.relhastriggers, c.relhasoids,
! %s, c.reltablespace\n
! FROM pg_catalog.pg_class c\n
! LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n
! WHERE c.oid = '%s'\n,
! (verbose ?
! pg_catalog.array_to_string(c.reloptions ||
! array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n
! : ''),
! oid);
! }
! else if (pset.sversion = 80200)
! {
! printfPQExpBuffer(buf,
! SELECT relchecks, relkind, relhasindex, relhasrules,
! reltriggers 0, relhasoids,
! %s, reltablespace\n
! FROM pg_catalog.pg_class WHERE oid = '%s',
! (verbose ?
! pg_catalog.array_to_string(reloptions, E', ') : ,''),
! oid);
! }
! else if (pset.sversion = 8)
! {
! printfPQExpBuffer(buf,
! SELECT relchecks, relkind, relhasindex, relhasrules,
! reltriggers 0, relhasoids,
! '', reltablespace\n
! FROM pg_catalog.pg_class WHERE oid = '%s',
! oid);
! }
! else
! {
! printfPQExpBuffer(buf,
! SELECT relchecks, relkind, relhasindex, relhasrules,
! reltriggers 0, relhasoids,
! '', ''\n
! FROM pg_catalog.pg_class WHERE oid = '%s',
! oid);
! }
!
res = PSQLexec(buf.data, false);
if (!res)
goto error_return;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] temporarily stop autovacuum
Alvaro Herrera alvhe...@commandprompt.com writes: Peter Eisentraut wrote: More generally, it was pointed out to me that users apparently do updates of pg_autovacuum to change settings on a bunch of tables at once. We might get some complaints if we remove that facility. Hmm, argh. Maybe we do need the rule on a fake pg_autovacuum that Itagaki-san was proposing. AFAIR we pointed out from day one that pg_autovacuum was a temporary API that we were not promising to keep around. Anybody who was coding against it with the expectation that they'd not have to change that code later was willfully ignoring the warning label. There's a problem however; for pg_autovacuum you used to need to insert some -1 values on columns on which you wanted to keep as defaults. On the new code you need to skip the value altogether, and a -1 is rejected with an error. Not sure how would we translate that. Maybe use a real table with an ON INSERT trigger that could contain some actual logic? But it'd probably still have to be custom-tailored to whatever application code was inserting things into pg_autovacuum, so it's not clear there's much point to writing that instead of fixing the application. 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] temporarily stop autovacuum
On Wed, Feb 11, 2009 at 1:10 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Peter Eisentraut wrote: More generally, it was pointed out to me that users apparently do updates of pg_autovacuum to change settings on a bunch of tables at once. We might get some complaints if we remove that facility. Hmm, argh. Maybe we do need the rule on a fake pg_autovacuum that Itagaki-san was proposing. AFAIR we pointed out from day one that pg_autovacuum was a temporary API that we were not promising to keep around. Anybody who was coding against it with the expectation that they'd not have to change that code later was willfully ignoring the warning label. There's a problem however; for pg_autovacuum you used to need to insert some -1 values on columns on which you wanted to keep as defaults. On the new code you need to skip the value altogether, and a -1 is rejected with an error. Not sure how would we translate that. Maybe use a real table with an ON INSERT trigger that could contain some actual logic? But it'd probably still have to be custom-tailored to whatever application code was inserting things into pg_autovacuum, so it's not clear there's much point to writing that instead of fixing the application. In any case it's not difficult to write a script that loops over all of your tables with ALTER TABLE. It's probably not as fast as a single UPDATE statement, but I suspect you'd need to have an enormous number of tables for that to matter much. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: IMHO the cut-and-paste way that we usually do it in pg_dump is a whole lot easier to read and maintain than this sort of ?: spaghetti. Hmm, so should we try to get rid of them in a more consistent fashion? If you've got the time and interest to work on the rest of describe.c, it'd be fine with me. I don't feel a compulsion to go fix the rest right now, though. It just seemed that this particular query had gotten out of hand. 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] A deprecation policy
Peter Eisentraut pete...@gmx.net writes: I have been thinking, with a semi-formal deprecation policy, we could make these decisions with more confidence. My proposed policy goes like this: I've been thinking about this for a couple of hours, and I keep coming back to the conclusion that if we actually enforced a policy like this it would kill Postgres development dead. It already takes more than a year, on average, for a proposal to go from idea to out-in-the-field. This policy would add another two years onto that for anything that involved user-visible changes, which is most things. All but the most persistent developers are simply going to go away and not bother trying to shepherd their ideas through such a process. I can see the value of a more formal deprecation policy, but I think it's gotta have a shorter time constant than this. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GIN fast insert
Robert Haas wrote: I believe that user could get GIN's error about work_mem only intentionally: - turn off autovacuum Meanwhile, in the other thread, we're having a discussion about people wanting to do exactly this on a database-wide basis during peak load hours... - decrease work_mem for at least ten times - execute query Why would the new work_mem need to be 10x smaller than the old work mem? That is is way to get GIN's error emitted. Work_mem should be decreased to catch a chance to get lossy tidbitmap. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] HotStandby vs. flatfile updates
Hi Bernd, On Wed, Feb 11, 2009 at 03:49:24PM +0100, Bernd Helmle wrote: I'm currently facing with a strange behavior during HotStandby Testing. That's what i'm actually doing: it seems that this was a known bug (snapshot bug), which as noted in http://wiki.postgresql.org/wiki/Hot_Standby#Resolved_Items.2FIssues was fixed in version 9h. We actually checked that this particular bug had been fixed, using the test whose summary you can find below. Probably you are running an old version: it's not your fault, since in the same page I can read that 9g is the last published version (I know that Simon is having some difficulties with git). Thank you for testing, Dr. Gianni Ciolli - 2ndQuadrant Italia PostgreSQL Training, Services and Support gianni.cio...@2ndquadrant.it | www.2ndquadrant.it ---8--8--8--8--8--8--8--8--8--- STANDBY: try to connect to database foo (fails) PRIMARY: create database foo STANDBY: wait for propagation, then connect to database foo, and issue a simple query; PRIMARY; drop database foo STANDBY: wait for propagation, then try connect to database foo (fails) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SE-PostgreSQL and row level security
Hi,
[...]
In my understanding, the row-level ACLs feature (plus a bit enhancement)
can
help your requirements. I developed it with SE-PostgreSQL in parallel,
but also postponed to v8.5 series.
It enables to assign database ACLs on individual tuples, and filter out
violated tupled from the result set of SELECT, UPDATE and DELETE.
So, it is not very hard. At least, we already have an implementation. :)
Where is it ? I like to try it? If is working why is not included in 8.4?
IMHO this is a killer feature. I like to try this, and if you want I like
to give you more feedbacks.
[..]
I guess you concerned about:
- It is necessary to set up many trigger functions for each tables, which
provide similar functionality.
- Users have to specify different names between reference and
modification.
And, you want to make clear how the row-level access control resolves
it. Is it OK?
Yes.
Your requirement is a simple separation between different users. Thus,
what we have to do is:
- When a tuple is inserted, the backend automatically assigns an ACL
which
allows anything for the current user, but nothing for others.
- So, when user tries to select, update and delete this table, tuples
which
inserted by others to be filtered out from the result set or affected
rows.
- Normal users are disallowed to change automatically assigned ACLs.
(I don't think you want to restrict superuser's operations.)
The row-level ACLs have a functionality named as default acl.
It enables table's owner to specify ACLs to be assigned to newly
inserted tuple, like:
CREATE TABLE customer_products (
id serial,
:
) WITH (default_row_acl='{rwd=kaigai}');
Currently, it does not allow replacement rules like
{rwd=%current_user}, but it is not a hard enhancement. If such an ACL
is assigned, the tuple is not visible from other users without any
triggers.
For example, please consider when a user kaigai insert a tuple into
customer_products, the {rwd=kaigai} is assigned to the tuple, but
the {rwd=bogdan} is assigned when a user bogdan do same thing.
In this case, any users must not be an owner of the table, because owner
of the table is allowed to change the ACLs.
This is an aside. If you want different access controls, like read-only
for other's tuples but read-writable for own tuples, it will be possible
with different default acl configuration.
Does it help you to understand about the row-level security currently we
are in development?
Yes and I like to try it (with more complex situations).
I have C/C++ knowledge maybe I can help you with this.
BIG TANKS
BogDan,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: IMHO the cut-and-paste way that we usually do it in pg_dump is a whole lot easier to read and maintain than this sort of ?: spaghetti. Hmm, so should we try to get rid of them in a more consistent fashion? If you've got the time and interest to work on the rest of describe.c, it'd be fine with me. I don't feel a compulsion to go fix the rest right now, though. It just seemed that this particular query had gotten out of hand. Yeah, I think we can get away with fixing the queries one by one as we go over them in future psql improvements. I have committed this for now. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GIN fast insert
Teodor Sigaev teo...@sigaev.ru writes: Robert Haas wrote: Why would the new work_mem need to be 10x smaller than the old work mem? That is is way to get GIN's error emitted. Work_mem should be decreased to catch a chance to get lossy tidbitmap. But it only has to be marginally lower, not 10x lower. And there are plenty of scenarios where different backends might be running with different work_mem settings. But the *real* problem is that you simply can not guarantee that someone doesn't increase the size of the pending list between the time that someone else commits to an indexscan plan and the time that they execute that plan. This scheme will result in random failures for concurrent inserts/selects, and that's not acceptable. What did you think of the idea of simply abandoning support for conventional indexscans in GIN? I agree that we could probably kluge something to make conventional scans still work reliably, but it seems to me that it'd be ugly, fragile, and quite possibly slow enough to not ever beat bitmap scans anyway. 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] HotStandby vs. flatfile updates
On Wed, 2009-02-11 at 19:48 +0100, Gianni Ciolli wrote: Probably you are running an old version: it's not your fault, since in the same page I can read that 9g is the last published version (I know that Simon is having some difficulties with git). I did publish v9h to Hackers on 27 Jan, but did not put a new version onto the Wiki at that time. Sorry Bernd. I am having a few git issues, so new patch out as soon as I can loosen the grip of my now favourite repo system. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Alvaro Herrera wrote: ITAGAKI Takahiro wrote: I tested this changes and found two issues: 1. fillfactor.* options are silently ignored when the table doesn't have toast relation. Should we notice the behabior to users? ex. NOTICE: toast storage parameters are ignored because the table doesn't have toast relations. You mean toast.* options? If so, yes, they are silently ignored. Maybe issuing a warning is not a bad idea. Care to propose a patch? Any takers here? The second issue has been solved. Thanks for testing. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] HotStandby vs. flatfile updates
--On Mittwoch, Februar 11, 2009 19:27:51 + Simon Riggs si...@2ndquadrant.com wrote: I did publish v9h to Hackers on 27 Jan, but did not put a new version onto the Wiki at that time. Sorry Bernd. Great! I just thought its worth reporting it. Sorry for the noise and missing the new patch version. I am having a few git issues, so new patch out as soon as I can loosen the grip of my now favourite repo system. Heh, had my issues here, too ;) -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Alvaro Herrera alvhe...@commandprompt.com writes: Alvaro Herrera wrote: ITAGAKI Takahiro wrote: 1. fillfactor.* options are silently ignored when the table doesn't have toast relation. Should we notice the behabior to users? ex. NOTICE: toast storage parameters are ignored You mean toast.* options? If so, yes, they are silently ignored. Maybe issuing a warning is not a bad idea. Care to propose a patch? Any takers here? I tend to think this isn't a very good idea. It's difficult for applications to know whether a toast table will be created or not. They should be able to just set the toast options and not worry. 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] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Alvaro Herrera wrote: ITAGAKI Takahiro wrote: 1. fillfactor.* options are silently ignored when the table doesn't have toast relation. Should we notice the behabior to users? ex. NOTICE: toast storage parameters are ignored You mean toast.* options? If so, yes, they are silently ignored. Maybe issuing a warning is not a bad idea. Care to propose a patch? Any takers here? I tend to think this isn't a very good idea. It's difficult for applications to know whether a toast table will be created or not. They should be able to just set the toast options and not worry. The problem is where do we store the options? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Alvaro Herrera wrote: Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Alvaro Herrera wrote: ITAGAKI Takahiro wrote: 1. fillfactor.* options are silently ignored when the table doesn't have toast relation. Should we notice the behabior to users? ex. NOTICE: toast storage parameters are ignored You mean toast.* options? If so, yes, they are silently ignored. Maybe issuing a warning is not a bad idea. Care to propose a patch? Any takers here? I tend to think this isn't a very good idea. It's difficult for applications to know whether a toast table will be created or not. They should be able to just set the toast options and not worry. The problem is where do we store the options? How about in the reloptions of the main relation? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Heikki Linnakangas wrote: Alvaro Herrera wrote: Tom Lane wrote: I tend to think this isn't a very good idea. It's difficult for applications to know whether a toast table will be created or not. They should be able to just set the toast options and not worry. The problem is where do we store the options? How about in the reloptions of the main relation? Yes, perhaps that could be made to work. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 64 bit PostgreSQL 8.3.6 build on AIX 5300-09-02-0849 with IBM XL C/C++ 10.1.0.1 - initdb fails (could not dump unrecognized node type: 650)
Peter Eisentraut pete...@gmx.net writes: Tom Lane wrote: Would it be reasonable to change the test quoted above to elif test $PORTNAME = aix; then ... that is try -qnoansialias anytime the compiler isn't gcc and the platform is aix? Is xlc used on any platform other than aix? That would probably make sense. I think we just never supported xlc_r, and the threading code rejects a separate thread-safe compiler. Done in CVS HEAD. 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] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: I tend to think this isn't a very good idea. It's difficult for applications to know whether a toast table will be created or not. They should be able to just set the toast options and not worry. The problem is where do we store the options? We don't. If there's no toast table, we don't need them after all. Now the alternative position you could take is that if someone is setting toast reloptions, they should darn well know the implementation well enough to know whether the table will have a toast table or not. In which case you should argue that this case ought to be an ERROR, not a notice or warning. But I think that's probably unsustainably anal. For example consider the following scenario: create table foo (f1 int, f2 text); set some toast reloptions on foo alter table foo drop column f2; pg_dump At this point foo still has a toast table and presumably pg_dump will dump its options. At reload, however, no toast table will be created, and so throwing an error would be pretty embarrassing. It's not hard to scale this up to find situations where the creation of a toast table would be platform- or version-dependent (if the max tuple width is just under a page). If we are not able to teach pg_dump to predict whether the target DB will create a toast table, we certainly can't expect applications to know it. So I think setting toast reloptions on a table that has no toast table should just be a silent no-op. 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] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: I tend to think this isn't a very good idea. It's difficult for applications to know whether a toast table will be created or not. They should be able to just set the toast options and not worry. The problem is where do we store the options? We don't. If there's no toast table, we don't need them after all. Well, that's the position that the current code is taking. However, Takahiro-san and Euler's position is that if you do this: create table foo (f1 int) with (toast.fillfactor = 70); alter table foo add column f2 text; Then the toast table should have the fillfactor setting. Right now they are lost. If we agree that the options are OK to be lost, then there's nothing we need to do (and that's my opinion). If we don't, then we need some weird hack to make it work somehow. Personally I think that it needs a lot more work than it warrants. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Alvaro Herrera alvhe...@commandprompt.com writes: However, Takahiro-san and Euler's position is that if you do this: create table foo (f1 int) with (toast.fillfactor = 70); alter table foo add column f2 text; Then the toast table should have the fillfactor setting. Well, that might look sensible when phrased that way. But the more likely scenario would be that you add column f2 six months later, at which point there is room for pretty serious doubt that the option you specified way back when would still be the optimal choice. I'm just fine with the concept that if ADD COLUMN causes a toast table to get created, that table will have default reloptions. If you want nondefault toast reloptions, having to specify what you want after the table exists (and you know what's in it) seems reasonable to me. Or to put it another way: it seems to me that the use-case being argued here is really for being able to adjust the default toast reloptions. Not to have action at a distance on a table that doesn't exist and you have no way to know when you set the option what will be in it when it does exist. 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] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Tom Lane wrote: Or to put it another way: it seems to me that the use-case being argued here is really for being able to adjust the default toast reloptions. Not to have action at a distance on a table that doesn't exist and you have no way to know when you set the option what will be in it when it does exist. This argument makes perfect sense to me. Since we don't have a way to set default reloptions for main tables either, I don't think we should be pushing very hard for having one to set default reloptions for toast tables. Even if we were to argue that we should have both, it doesn't seem material for 8.4. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Alvaro Herrera alvhe...@commandprompt.com writes: Since we don't have a way to set default reloptions for main tables either, I don't think we should be pushing very hard for having one to set default reloptions for toast tables. Even if we were to argue that we should have both, it doesn't seem material for 8.4. Seems like a reasonable TODO entry, though. I think the use-case for such a default mechanism is a bit thin today, but if we continue to push more functionality into reloptions it's going to become valuable. 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] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
On Wed, Feb 11, 2009 at 3:40 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: However, Takahiro-san and Euler's position is that if you do this: create table foo (f1 int) with (toast.fillfactor = 70); alter table foo add column f2 text; Then the toast table should have the fillfactor setting. Well, that might look sensible when phrased that way. But the more likely scenario would be that you add column f2 six months later, at which point there is room for pretty serious doubt that the option you specified way back when would still be the optimal choice. I'm just fine with the concept that if ADD COLUMN causes a toast table to get created, that table will have default reloptions. If you want nondefault toast reloptions, having to specify what you want after the table exists (and you know what's in it) seems reasonable to me. FWIW, I don't really buy this argument. I can't see that it's all that implausible to think that the user might be able to prognosticate a reasonable value for a future TOAST table. The fact that they may end up being wrong is hardly grounds to silently ignore whatever value they tell us they want. On the other hand, since I've never had a reason to tune this knob myself, for TOAST tables or otherwise, I can't say I'm feeling a violent urge to be the one to fix it. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
Robert Haas robertmh...@gmail.com writes: FWIW, I don't really buy this argument. I can't see that it's all that implausible to think that the user might be able to prognosticate a reasonable value for a future TOAST table. Well, it still seems to me that such a user is really more interested in a way to set the default toast fillfactor (or whatever option is under discussion), ie what he really knows is a reasonable value for *all* future TOAST tables in his installation. Otherwise you're arguing that he knows exactly what the fillfactor should be for a specific toast table and not any other one ... except he doesn't know when that toast table is going to be created, which calls into question the quality of his judgment about its specific behavior otherwise. 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] Re: [COMMITTERS] pgsql: Update autovacuum to use reloptions instead of a system catalog,
On Wed, Feb 11, 2009 at 4:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: FWIW, I don't really buy this argument. I can't see that it's all that implausible to think that the user might be able to prognosticate a reasonable value for a future TOAST table. Well, it still seems to me that such a user is really more interested in a way to set the default toast fillfactor (or whatever option is under discussion), ie what he really knows is a reasonable value for *all* future TOAST tables in his installation. Maybe, or maybe he knows that this group of tables is typically pretty stable, but this group over here has more frequent updates, so different fillfactors are appropriate... doesn't have to be 100% site-wide. Otherwise you're arguing that he knows exactly what the fillfactor should be for a specific toast table and not any other one ... except he doesn't know when that toast table is going to be created, which calls into question the quality of his judgment about its specific behavior otherwise. Sure, but I think you're putting too much emphasis on the likely quality of the user's judgment. It's not really the place of the database to ignore user requests, even if they're likely stupid requests. WARNING: Type varchar(9) is likely inadequate for assumed purpose of column `telephone_number'. ...Robert -- Sent 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 #4284
Tom Lane Wrote: David Rowley dgrow...@gmail.com writes: My report contained a full re-creation script to reproduce the problem and tonight I'm having the same problem with CVS Head. To my untrained eye it looks like the planner is not properly pushing down the row count. It looks more like a multicolumn selectivity issue to me. The planner is supposing that the join condition ON t1.productiondate = t2.productiondate AND t1.lineid = t2.lineid AND t1.partcode = t2.partcode is going to eliminate some fair-size fraction of t1 rows, whereas in fact the construction of t2 is such that it won't eliminate any of them. This is less obviously true for the join to t4, but I imagine from the rowcounts that it's also true there. So you get an unreasonably small rowcount for whichever join gets done first, and then the nestloop plan looks like a good idea for the second join. I thought about this after sending my reply to this last night. I remembered when I created my test case I had to add the other tables to get the nest loop behaviour. I'm not sure your guess about the multicolumn selectivity issue is correct. I re-tested with the following query. EXPLAIN ANALYZE SELECT t1.productiondate, t1.partcode, t1.batchcode, t1.bestbefore FROM batches t1 LEFT OUTER JOIN (SELECT productiondate, lineid, partcode, SUM(quantity) AS quantity FROM production GROUP BY productiondate,partcode,lineid ) t4 ON t1.productiondate = t4.productiondate AND t1.lineid = t4.lineid AND t1.partcode = t4.partcode; The top line of the explain analyze is: Merge Left Join (cost=464.51..510.72 rows=4200 width=21) (actual time=107.872..157.882 rows=4200 loops=1) Perfect row estimate! It seems to be something to do with having those other tables in there. 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] WIP: hooking parser
2009/2/11 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2009/2/11 Tom Lane t...@sss.pgh.pa.us: This strikes me as next door to useless, because it can only handle things that look like valid expressions to the existing grammar. So pretty much all you can do is weird sorts of functions, which are already accommodated at less effort with existing features such as function overloading. Usually we don't need change syntax. But we need to control of coercion stage. I afraid so function overloading is bad when there lot of combination, and polymorphic functions are not enough. for some cases we need more polymorphic types - anyelement1, anyelement2, anyarray1, ... Well, then we should go fix those things. I am for it, and I doing on it. A hook function whose purpose is to fundamentally change query semantics strikes me as a very dangerous thing anyway, because your queries either stop working or suddenly do something completely different if the hook happens not to be loaded. The hooks we've accepted to date are intended for either monitoring or experimentation with planner behavior, neither of which will change query semantics. I agree, and I understand well this risk. But still it is better and wide used than custom patching. Look on executor hook. There are only three cases - useful cases. It is some corner, that is far for general using (integrating into core) and too sugar for ignore it for ever. It's possibility, nothing less, nothing more. regards Pavel Stehule 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] So, what locale should the regression tests run in?
Peter Eisentraut pete...@gmx.net writes: Bruce Momjian wrote: Has this been resolved? Since nobody spoke up, I have changed it now so it runs with locale by default. Let's see if we get away with that. Buildfarm member heron doesn't like it, for one. 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] Strange issue with CREATE OPERATOR CLASS
All, I've been working on some scripts (for pgfoundry) which help in cleaning up databases which have TSearch and other contrib modules installed to schema public. However, I ran into this odd issue: ERROR: btree operators must return boolean STATEMENT: CREATE OPERATOR CLASS contrib.tsquery_ops DEFAULT FOR TYPE tsquery USING btree AS OPERATOR 1 (tsquery,tsquery) , OPERATOR 2 =(tsquery,tsquery) , OPERATOR 3 =(tsquery,tsquery) , OPERATOR 4 =(tsquery,tsquery) , OPERATOR 5 (tsquery,tsquery) , FUNCTION 1 tsquery_cmp(tsquery,tsquery); What appears to be happening there is that PG isn't finding the tsearch operators or is selecting the wrong operators. Manually changing the search_path to contrib, public instead of public, contrib fixes the issue, but it seems odd that create opclass can't find operators unless they're in the first schema in the path, when other CREATE statements have no such difficulty. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A deprecation policy
Peter, 3. In release N+2, if there were no protests in response to step 2, deprecated features are removed. The main issue I can see with this is that most production sites only upgrade once every 2-3 years. So they'd tend to miss warnings entirely. I would also extend this system to removed configuration settings, e.g., max_fsm_*. We should make these deprecated for one release, so (1) configuration files can be upgraded without manual work (relevant to in-place upgrade), and (2) users are alerted that their carefully crafted configuration might need a review. I think the combination of a config file generator (in development now) with a config file checker (something we could use anyway) would take care of any config file issues. I did actually give some thought to a config file converter, but the number of options which are new, changed names, changed names and changed meanings, changed options, or went away makes it an n^2 issue and not really worth solving. Just tell the people to run the new version of the config file generator. The other thing we could use would be clearer documentation on this sort of thing. That is, a single page in the docs which talks about what was depreciated in specific versions. We kinda do this in the release notes, but the notices often aren't that clear and are mixed in with a lot of other stuff. Probably we should just clear this up in the release notes. --Josh -- Sent 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 #4284
David Rowley dgrow...@gmail.com writes: I thought about this after sending my reply to this last night. I remembered when I created my test case I had to add the other tables to get the nest loop behaviour. I'm not sure your guess about the multicolumn selectivity issue is correct. I re-tested with the following query. ... Perfect row estimate! The reason for that is that the planner knows that a LEFT JOIN result can't be smaller than the left input, so whatever join size estimate it comes up with from statistics will be clamped to be at least that much. If you change the test case to an inner join you still get the one-row rowcount estimate, same as for the join to t2. 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] Strange issue with CREATE OPERATOR CLASS
Josh Berkus j...@agliodbs.com writes: I've been working on some scripts (for pgfoundry) which help in cleaning up databases which have TSearch and other contrib modules installed to schema public. However, I ran into this odd issue: ERROR: btree operators must return boolean Is that the actual error message? The closest string I can find in 8.3 or HEAD is index operators must return boolean. What appears to be happening there is that PG isn't finding the tsearch operators or is selecting the wrong operators. It seems possible that you could get an error like that as a result of something creating a shell operator and not filling it in afterwards, but if so I'd blame the earlier something; the worst that can be laid at CREATE OPERATOR CLASS's door is giving a less helpful error message than it could. We really need to see a complete example of how to reproduce the problem before speculating about appropriate fixes. 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] A deprecation policy
On Wed, 11 Feb 2009, Josh Berkus wrote: I did actually give some thought to a config file converter, but the number of options which are new, changed names, changed names and changed meanings, changed options, or went away makes it an n^2 issue and not really worth solving. My next big push for pgtune is to backport the pg_settings additions I need to 8.3/8.2/8.1 and assemble a proper settings database for all those versions. Once I finish that, it will be trivial to flag and remove all the parameters that aren't even there anymore, which at least reduces the size of n quite a bit. For the specific case that's been mentioned here, does it even matter if somebody has some old settings for max_fsm_* in their 8.4 postgresql.conf file? Ditto for other deprecated parameters like bgwriter_all_percent. I think that if you ignore everything that just dropped altogether, and just worry about settings whose meaning has changed, the number you'd have left to worry about is much smaller. Unfortunately, those are the hard ones to identify, too. Anyway, I read Peter's suggestion as aiming more at SQL features and API changes, rather than configuration file ones. In trivial cases like sort_mem-work_mem, adding some backward compatibility concessions might make sense. Saddling GUC changes with any restrctions beyond what happens to be easy seems pretty impractical. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Strange issue with CREATE OPERATOR CLASS
Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: I've been working on some scripts (for pgfoundry) which help in cleaning up databases which have TSearch and other contrib modules installed to schema public. However, I ran into this odd issue: ERROR: btree operators must return boolean Is that the actual error message? The closest string I can find in 8.3 or HEAD is index operators must return boolean. Oh! Sorry, this is 8.2.12. I can't reproduce it in 8.3 for obvious reasons, since there the TSearch stuff comes built-in. It seems possible that you could get an error like that as a result of something creating a shell operator and not filling it in afterwards, but if so I'd blame the earlier something; the worst that can be laid at CREATE OPERATOR CLASS's door is giving a less helpful error message than it could. We really need to see a complete example of how to reproduce the problem before speculating about appropriate fixes. Yeah, I'm trying to figure out the minimum test case; the one I have now contains proprietary client data, and *just* using tsearch2.sql doesn't produce the problem. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A deprecation policy
Greg Smith gsm...@gregsmith.com writes: Anyway, I read Peter's suggestion as aiming more at SQL features and API changes, rather than configuration file ones. In trivial cases like sort_mem-work_mem, adding some backward compatibility concessions might make sense. [ rolls eyes ... ] $ psql regression psql (8.4devel) Type help for help. regression=# set sort_mem = 100; SET regression=# show sort_mem; work_mem -- 100kB (1 row) regression=# It's not like we go out of our way to break applications; if there's an easy compatibility workaround, we generally provide it. In particular I think that Peter's proposal is mainly thinking about cases where it's practical to provide a deprecated feature and its replacement behavior concurrently --- at worst being able to switch between one and the other via a GUC or some such. The hard part, and the question that's missing from this discussion, is exactly what we'll do when backwards compatibility is impractical (for whatever size of impractical suits you; there is *always* going to be a point where it's not worth it). Peter's proposal appears to require that changes in that category have to be agreed to and announced two years before the change is actually made. Frankly I don't believe that's going to happen. The original proposer will have lost interest, or the original patch will have bit-rotted beyond usefulness, or even more likely somebody will want the patch bad enough to convince us not to wait two years. (Witness the annual arguments about how feature FOO should go into the current release because its developer isn't willing to wait *one* year.) 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] Optimization rules for semi and anti joins
Tom Lane t...@sss.pgh.pa.us wrote: I wrote (in response to Kevin Grittner's recent issues): Reflecting on this further, I suspect there are also some bugs in the planner's rules about when semi/antijoins can commute with other joins; After doing some math I've concluded this is in fact the case. Anyone want to check my work? Hence semijoins can be rearranged just as freely as inner joins. Made sense. Agreed. A6. (A antijoin B on (Pab)) leftjoin C on (Pbc) = A antijoin (B leftjoin C on (Pbc)) on (Pab) The second form is in fact equivalent to null-extending the A/B antijoin --- the actual contents of C cannot affect the result. So we could just drop C altogether. (I'm not going to do anything about that now, but it's something to consider for the planned join-elimination optimization.) In the first form, if Pbc is strict on B then it must fail for all rows of the antijoin result so we get the null-extended A/B result. If Pbc is not strict then the first form might duplicate some rows in the antijoin result, or produce non-null-extended rows. So in this case the identity holds only if Pbc is strict, which is the same as for left joins. How do you get the first form as a starting point? Aren't we limited to NOT IN or NOT EXISTS clauses for B? Those can't really contribute columns to the result can they? (I'm probably missing something here.) The rest of it made sense, although two identities jumped to mind which weren't listed. (A semijoin B on (Pab)) antijoin C on (Pac) = (A antijoin C on (Pac)) semijoin B on (Pab) This one turned out, on closer inspection, to be a restatement of S4. (A semijoin B on (Pab)) antijoin C on (Pbc) = A semijoin (B antijoin C on (Pbc)) on (Pab) I think this one is true, and it doesn't seem to be mentioned, unless I'm missing something. It seems potentially useful. Hopefully I didn't miss your point entirely. -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] Strange issue with CREATE OPERATOR CLASS
Josh Berkus j...@agliodbs.com writes: Tom Lane wrote: Is that the actual error message? The closest string I can find in 8.3 or HEAD is index operators must return boolean. Oh! Sorry, this is 8.2.12. Oh, OK. It's the same case though. Look for operator definitions that specify a commutator or negator operator that never gets provided. 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] Optimization rules for semi and anti joins
I wrote: A6. [less coherent version of a question already asked and answered] Got that part on a reread of the thread. Sorry for asking that after it had been addressed. No need to answer again. -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] Optimization rules for semi and anti joins
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: A6. (A antijoin B on (Pab)) leftjoin C on (Pbc) = A antijoin (B leftjoin C on (Pbc)) on (Pab) How do you get the first form as a starting point? Not sure if you can in SQL, but the point of the identity is you can apply the transformation in either direction. Consider this version of the second form: select ... from A where not exists(select 1 from B left join C on B.y = C.y where B.x = A.x) The identity says that if B.y = C.y is strict we can antijoin A to B first (because, in fact, the join to C is pointless here). Anyway, whether the identity is really useful for antijoins isn't what I'm concerned about --- I was just trying to see if it was okay for the planner's join ordering logic to treat left and anti joins the same. Seems it's not :-( (A semijoin B on (Pab)) antijoin C on (Pbc) = A semijoin (B antijoin C on (Pbc)) on (Pab) I think this one is true, and it doesn't seem to be mentioned, unless I'm missing something. It seems potentially useful. Hmm, it doesn't seem terribly well-defined --- the values of B are indeterminate above the semijoin in the first case, so having Pbc refer to them doesn't seem like a good idea. In particular, it seems like in the first case the semijoin could randomly choose a B row that has a join partner in C, causing the A row to disappear from the result, when the same A row has another B partner that does not join to C --- and the second form would find that B partner and allow the A row to be output. 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] temporarily stop autovacuum
Alvaro Herrera alvhe...@commandprompt.com wrote: I'm not sure that this calls for a change in autovacuum itself; it seems to be that whatwe really need is the ability to change postgresql.conf settings from the SQL interface. Sure. 'SET GLOBAL autovacuum = off' is a TODO item. I have another idea that autovacuum will use 'autovacuum role' to process tables. We don't need to add syntax because we already have per-database and per-role settings. Something like: ALTER ROLE autovacuum SET autovacuum = off; We also need to adjust those variable can be set on-the-fly, though. The current version of postgres doesn't allow to set them. ERROR: parameter ... cannot be changed now Regards, --- ITAGAKI Takahiro 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] Fixing Grittner's planner issues
[ forgot to respond to this earlier, sorry ] Robert Haas robertmh...@gmail.com writes: On a related note, I have some vague unease about planning A SEMI JOIN B as A INNER JOIN (UNIQUE B), as make_one_rel currently attempts to do. For a merge join or nested loop, I don't see how this can ever be a win over teaching the executor to just not rescan B. For a hash join, it can be a win if B turns out to have duplicates, but then again you could also just teach the executor to skip the insertion of the duplicate into the table in the first place (it has to hash 'em anyway...). I think maybe I'm not understanding something about the logic here. The case where this is a win is where B is small (say a few rows) and not unique, and A is large, and there's a relevant index on A. Then considering this join approach lets us produce a plan that looks like NestLoop HashAggregate (or GroupAggregate) Scan B IndexScan A Index Condition : A.x = B.y Every other possible plan for this join involves reading all of A. If B produces too many rows for the nestloop indexscan to be a win, then one of the other join approaches will beat out this one in the cost comparisons. One thing I notice is that src/backend/optimizer/README should probably be updated with the rules for commuting SEMI and ANTI joins; it currently only mentions INNER, LEFT, RIGHT, and FULL. Yeah, I noticed that too. How embarrassing. Will fix it as part of the patch, which I hope to start on tomorrow. 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] advance local xmin more aggressively
On Wed, 2009-02-11 at 12:20 -0500, Tom Lane wrote: You pointed out the case of opening a cursor in a read-committed transaction, and then closing it before end of transaction, as a place where your patch could hope to improve matters. Are there others? Could your application be made to close that cursor before opening another one (so that its set of open snapshots momentarily goes to zero)? It seems like the use case for more complex bookkeeping for open snapshots is a tad narrow. I'm approaching this from the perspective of our system at Truviso. I used the cursor example to illustrate the issue in normal postgresql, but our problem isn't directly related to cursors. I don't have a compelling use case right now for normal postgresql, because of the reasons you mention. However, at Truviso, we have to come up with some kind of solution to this anyway. Ideally, I'd like to make something that's acceptable to postgres in general (meaning no performance impact or code ugliness). I could imagine some situations that this could be more useful in the future. For instance, Hot Standby will increase the consequences of not advancing xmin when it's possible to do so. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fixing Grittner's planner issues
On Wed, Feb 11, 2009 at 8:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: [ forgot to respond to this earlier, sorry ] Thanks for responding now. Robert Haas robertmh...@gmail.com writes: On a related note, I have some vague unease about planning A SEMI JOIN B as A INNER JOIN (UNIQUE B), as make_one_rel currently attempts to do. For a merge join or nested loop, I don't see how this can ever be a win over teaching the executor to just not rescan B. For a hash join, it can be a win if B turns out to have duplicates, but then again you could also just teach the executor to skip the insertion of the duplicate into the table in the first place (it has to hash 'em anyway...). I think maybe I'm not understanding something about the logic here. The case where this is a win is where B is small (say a few rows) and not unique, and A is large, and there's a relevant index on A. Then considering this join approach lets us produce a plan that looks like NestLoop HashAggregate (or GroupAggregate) Scan B IndexScan A Index Condition : A.x = B.y Right, so maybe I wasn't as clear as I could have been in asking the question. I do understand how it can be a win to unique B and use it as the OUTER relation (jointype JOIN_UNIQUE_OUTER). What I don't understand is how it can ever be a win to unique B and use it as the INNER relation (jointype JOIN_UNIQUE_INNER). One thing I notice is that src/backend/optimizer/README should probably be updated with the rules for commuting SEMI and ANTI joins; it currently only mentions INNER, LEFT, RIGHT, and FULL. Yeah, I noticed that too. How embarrassing. Will fix it as part of the patch, which I hope to start on tomorrow. Cool. On the topic of documentation, I find the following comment in joinrels.c rather impenetrable: /* * Do these steps only if we actually have a regular semijoin, * as opposed to a case where we should unique-ify the RHS. */ I don't think regular semijoin is a term of art, so I'm somewhat at a loss to understand what this means. And why as opposed to a case where we should unique-ify the RHS? ISTM the code will sometimes try both... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GIN fast insert database hang
While fooling around with the GIN fast insert patch tonight, I managed to hang my test database. :-( I'm going to try to reproduce this, but here's approximately what I did. create table foo (id serial, x int[], primary key (id)); create index foo_gin on foo using gin (x); insert into foo (x) select array[(random() * 100)::int, (random() * 90)::int, (random()*80)::int] from generate_series(1,100); The last insert command was run multiple times with various numbers in place of the constant 100 through several autovacuum cycles. Eventually, though, it froze up. Here's pg_stat_activity, with apologies for the crappy formatting: datid | datname | procpid | usesysid | usename | current_query | waiting | xact_start | query_ start | backend_start | client_addr | client_port ---+-+-+--+-+--- -+-+---+ ---+---+-+- 16384 | rhaas | 15071 | 10 | rhaas | insert into foo (x) select arr ay[(random() * 100)::int, (random() * 90)::int, (random()*80)::int] from generat e_series(1,100); | f | 2009-02-11 21:42:24.984759-05 | 2009-02-11 21:4 2:24.984759-05 | 2009-02-11 21:31:45.061903-05 | | -1 16384 | rhaas | 15530 | 10 | rhaas | autovacuum: VACUUM public.foo | f | 2009-02-11 21:42:10.046085-05 | 2009-02-11 21:4 2:10.046085-05 | 2009-02-11 21:42:09.947378-05 | | 16384 | rhaas | 15780 | 10 | rhaas | explain analyze select sum(1) from foo where array[1] @ x; | f | 2009-02-11 21:43:20.518837-05 | 2009-02-11 21:4 3:20.518837-05 | 2009-02-11 21:43:18.717031-05 | | -1 16384 | rhaas | 15994 | 10 | rhaas | IDLE | f | | 2009-02-11 21:4 Neither the INSERT nor the EXPLAIN ANALYZE nor the autovacuum died when sent SIGINT or SIGTERM. They all seem to be hung on semop: $ strace -p 15071 Process 15071 attached - interrupt to quit semop(3866630, 0x7fff14a46be0, 1^C unfinished ... Process 15071 detached $ strace -p 15530 Process 15530 attached - interrupt to quit semop(3866630, 0x7fff14a45740, 1^C unfinished ... Process 15530 detached $ strace -p 15780; Process 15780 attached - interrupt to quit semop(3866630, 0x7fff14a48c50, 1^C unfinished ... Process 15780 detached $ uname -a Linux njpen7.snipinc.net 2.6.27.12-78.2.8.fc9.x86_64 #1 SMP Mon Jan 19 19:25:03 EST 2009 x86_64 x86_64 x86_64 GNU/Linux $ cat /etc/fedora-release Fedora release 9 (Sulphur) Backtrace of pid 15071: #0 0x003b1bce6257 in semop () from /lib64/libc.so.6 #1 0x005a8117 in PGSemaphoreLock () #2 0x005c6409 in LockBufferForCleanup () #3 0x004944d1 in ginInsertCleanup () #4 0x0049501d in ginHeapTupleFastInsert () #5 0x0048a772 in gininsert () #6 0x0068fb39 in FunctionCall6 () #7 0x00469181 in index_insert () #8 0x00540ee2 in ExecInsertIndexTuples () #9 0x00536504 in standard_ExecutorRun () #10 0x005e07e1 in ProcessQuery () #11 0x005e0a06 in PortalRunMulti () #12 0x005e11b2 in PortalRun () #13 0x005dc507 in exec_simple_query () #14 0x005ddad7 in PostgresMain () #15 0x005b2e58 in ServerLoop () #16 0x005b3b39 in PostmasterMain () #17 0x0055e1e8 in main () Backtrace of pid 15530: #0 0x003b1bce6257 in semop () from /lib64/libc.so.6 #1 0x005a8117 in PGSemaphoreLock () #2 0x005d624d in LWLockAcquire () #3 0x00494322 in ginInsertCleanup () #4 0x004922ed in ginvacuumcleanup () #5 0x0068fddc in FunctionCall2 () #6 0x00530e1e in lazy_scan_heap () #7 0x005312d0 in lazy_vacuum_rel () #8 0x0052f935 in vacuum_rel () #9 0x0052fd4c in vacuum () #10 0x005aa338 in do_autovacuum () #11 0x005aa62d in AutoVacWorkerMain () #12 0x005aa756 in StartAutoVacWorker () #13 0x005b525e in sigusr1_handler () #14 signal handler called #15 0x003b1bcdcbb3 in __select_nocancel () from /lib64/libc.so.6 #16 0x005b281c in ServerLoop () #17 0x005b3b39 in PostmasterMain () #18 0x0055e1e8 in main () Backtrace of pid 15780: #0 0x003b1bce6257 in semop () from /lib64/libc.so.6 #1 0x005a8117 in PGSemaphoreLock () #2 0x005d624d in LWLockAcquire () #3 0x004913ac in scanFastInsert () #4 0x0049201c in gingetbitmap () #5 0x0068fddc in FunctionCall2 () #6 0x0046878e in index_getbitmap () #7 0x005459c5 in MultiExecBitmapIndexScan () #8
Re: [HACKERS] A deprecation policy
Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: I have been thinking, with a semi-formal deprecation policy, we could make these decisions with more confidence. My proposed policy goes like this: I've been thinking about this for a couple of hours, and I keep coming back to the conclusion that if we actually enforced a policy like this it would kill Postgres development dead. It already takes more than a year, on average, for a proposal to go from idea to out-in-the-field. This policy would add another two years onto that for anything that involved user-visible changes, which is most things. All but the most persistent developers are simply going to go away and not bother trying to shepherd their ideas through such a process. I can see the value of a more formal deprecation policy, but I think it's gotta have a shorter time constant than this. Agreed. Consider the downside of having to support two different APIs for two releases, and document them. Yuck. There are some cases where a 2-release buffer is warranted, others where it is not. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade project status
Peter Eisentraut wrote: Bruce Momjian wrote: Now that pg_migrator is BSD licensed, and already in C, I am going to spend my time trying to improve pg_migrator for 8.4: http://pgfoundry.org/projects/pg-migrator/ What is the plan now? Get pg_upgrade working, get pg_migrator working, ship pg_migrator in core or separately? Is there any essential functionality that we need to get into the server code before release? Should we try to get dropped columns working? It's quite late to be wondering about this, so unless we get a clear and definite plan this week, I say we stop kidding ourselves and drop it. Oh, a plan? ;-) Basically I am trying to add functionality to the code and clean it up so it is easier to maintain. You can grab the CVS to see my current version: http://pgfoundry.org/scm/?group_id=1000235 Everyone seemed to prefer a migration utility in C, and pg_migrator is in C so I am working on that. It has a BSD license now so we could include it if we wanted to. I am working on pg_migrator TODO items. My current list is: o Makefiles are not yet complete. o need to check crc when we reading the pg_control file o compare the pg_controls of old and new servers o fix loaded tables with dropped columns; the dropped column location is not part of pg_dump; pg_attribute.attisdropped must be checked and a replacement table created and the column dropped o must call vacuum freeze on system tables before clog is copied o restore pg_database.datfrozenxid to their original values o restore pg_class.relfrozenxid to their original values I am not sure about the first two items, but I want to try to address the other ones, though I am not sure how to handle the drop column case. Once I am complete the existing TODOs I will start on testing and getting more feedback on missing features. If folks want to help out, please let me know. I am not aware of any server changes needed for 8.3-8.4 migration. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GIN fast insert database hang
On Wed, Feb 11, 2009 at 10:03 PM, Robert Haas robertmh...@gmail.com wrote: I'm going to try to reproduce this, but here's approximately what I did. OK, I've managed to build a reproducible test case. Initial setup is just as I had before: create table foo (id serial, x int[], primary key (id)); create index foo_gin on foo using gin (x); Then just start these two commands running in different windows and wait: while true; do psql -c explain analyze select sum(1) from foo where array[1] @ x; done while true; do psql -c insert into foo (x) select array[(random() * 100)::int, (random() * 90)::int, (random()*80)::int] from generate_series(1,10);; done I did this four times, sometimes with the variant of adding set enable_bitmapscan to false; before the explain analyze. In three cases the database recovered succesfully after the immediate shutdown; in the other case, it crapped out much as described in my original email. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GIN fast insert database hang
I did this four times, sometimes with the variant of adding set enable_bitmapscan to false; before the explain analyze. In three cases the database recovered succesfully after the immediate shutdown; in the other case, it crapped out much as described in my original email. Sorry to keep replying to myself, but I've figured that autovacuum is not required to trigger this bug. In fact, I can reliably trigger it much more quickly just by starting two concurrent copies of: psql -c insert into foo (x) select array[(random() * 100)::int, (random() * 90)::int, (random()*80)::int] from generate_series(1,10); This freezes the whole system even with autovacuum = off in postgresql.conf. As before, the backends wait on a semop() call. I was able to reproduce the recovery failure this way once as well, but that part of the problem seems to be much more erratic. Most of the time after an immediate shutdown, pg_ctl start triggers recovery followed by normal startup. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] advance local xmin more aggressively
Jeff Davis pg...@j-davis.com writes: I could imagine some situations that this could be more useful in the future. For instance, Hot Standby will increase the consequences of not advancing xmin when it's possible to do so. The question wasn't really about the consequences; it was about whether there was any hope of this patch being able to advance xmin more than the code that's there, for common usage patterns. 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] temporarily stop autovacuum
On Wednesday 11 February 2009 20:10:46 Tom Lane wrote: AFAIR we pointed out from day one that pg_autovacuum was a temporary API that we were not promising to keep around. Anybody who was coding against it with the expectation that they'd not have to change that code later was willfully ignoring the warning label. Indeed. I'm just saying, there is now no way to conveniently change the settings for many tables at once. This is perhaps the same kind of issue as GRANT SELECT ON ALL TABLES etc. that people occassionally ask for. Doing DDL on a group of tables at once. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] fillfactor for toast tables is useless?
With reloption patch, we can set WITH options to toast tables. However, fillfactor for toast tables is useless, no? (autovacuum options will work as expected, though.) Tuples in toast tables are never updated. When the main tuple is updated, old toast tuples are deleted and new ones are inserted. Even if there are some freespaces in pages of toast table, they are never used by inserts. I think we should not allow users to modify fillfactor for toast tables and it should be always 100%. We could optimize a delete+insert operation for toast tuples to one update when the number of chunks are not changed by update. Fillfactor for toast tables will be only useful after the optimization. Regards, --- ITAGAKI Takahiro 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] Hot Standby: subxid cache changes
One independent change included in the Hot Standby patch is the change to the way subtransaction cache works. With the patch, only subtransactions that don't fit in the subxid cache in PGPROC are marked in pg_subtrans. To make that work, XidInMVCCSnapshot() always scans the subxid array in the snapshot, while currently it's only used if none of the subxid caches have overflowed. Attached is a patch for that, extracted from the latest hot standby patch. So far so good, but what about all the other callers of SubTransGetParent()? For example, XactLockTableWait will fail an assertion if asked to wait on a subtransaction which is then released. It occurs to me that we don't need this patch for hot standby if we abuse the main xid array (SnapshotData.xip) to store the unobserved xids instead of the subxid array. That one is always scanned in XidInMVCCSnapshot. I think we should do that rather than try to salvage this patch. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com *** a/src/backend/access/transam/README --- b/src/backend/access/transam/README *** *** 198,204 parent. This maintains the invariant that child transactions have XIDs later than their parents, which is assumed in a number of places. The subsidiary actions of obtaining a lock on the XID and and entering it into ! pg_subtrans and PG_PROC are done at the time it is assigned. A transaction that has no XID still needs to be identified for various purposes, notably holding locks. For this purpose we assign a virtual --- 198,204 than their parents, which is assumed in a number of places. The subsidiary actions of obtaining a lock on the XID and and entering it into ! PG_PROC and, in some cases, pg_subtrans are done at the time it is assigned. A transaction that has no XID still needs to be identified for various purposes, notably holding locks. For this purpose we assign a virtual *** *** 376,382 but since we allow arbitrary nesting of subtransactions, we can't fit all Xids in shared memory, so we have to store them on disk. Note, however, that for each transaction we keep a cache of Xids that are known to be part of the transaction tree, so we can skip looking at pg_subtrans unless we know the ! cache has been overflowed. See storage/ipc/procarray.c for the gory details. slru.c is the supporting mechanism for both pg_clog and pg_subtrans. It implements the LRU policy for in-memory buffer pages. The high-level routines --- 376,384 in shared memory, so we have to store them on disk. Note, however, that for each transaction we keep a cache of Xids that are known to be part of the transaction tree, so we can skip looking at pg_subtrans unless we know the ! cache has been overflowed. In 8.4 we skip updating pg_subtrans unless the ! cache has overflowed for that transaction, considerably reducing pg_subtrans ! activity. See storage/ipc/procarray.c for the gory details. slru.c is the supporting mechanism for both pg_clog and pg_subtrans. It implements the LRU policy for in-memory buffer pages. The high-level routines *** a/src/backend/access/transam/xact.c --- b/src/backend/access/transam/xact.c *** *** 415,421 AssignTransactionId(TransactionState s) */ s-transactionId = GetNewTransactionId(isSubXact); ! if (isSubXact) SubTransSetParent(s-transactionId, s-parent-transactionId); /* --- 415,428 */ s-transactionId = GetNewTransactionId(isSubXact); ! /* ! * If we have overflowed the subxid cache then we must mark subtrans ! * with the parent xid. Prior to 8.4 we marked subtrans for each ! * subtransaction, though that is no longer necessary because the ! * way snapshots are searched in XidInMVCCSnapshot() has changed to ! * allow searching of both subxid cache and subtrans, not either/or. ! */ ! if (isSubXact MyProc-subxids.overflowed) SubTransSetParent(s-transactionId, s-parent-transactionId); /* *** a/src/backend/storage/ipc/procarray.c --- b/src/backend/storage/ipc/procarray.c *** *** 680,685 GetSnapshotData(Snapshot snapshot) --- 680,686 int index; int count = 0; int subcount = 0; + bool suboverflowed = false; Assert(snapshot != NULL); *** *** 771,781 GetSnapshotData(Snapshot snapshot) } /* ! * Save subtransaction XIDs if possible (if we've already overflowed, ! * there's no point). Note that the subxact XIDs must be later than ! * their parent, so no need to check them against xmin. We could ! * filter against xmax, but it seems better not to do that much work ! * while holding the ProcArrayLock. * * The other backend can add more subxids concurrently, but cannot * remove any. Hence it's important to fetch nxids just once. Should --- 772,782 } /* ! * Save subtransaction XIDs, whether or not we have
