Re: [HACKERS] WIP patch for TODO Item: Add prompt escape to display the client and server versions
2009/5/7 Dickson S. Guedes lis...@guedesoft.net: Em Qui, 2009-05-07 às 10:11 +0300, Peter Eisentraut escreveu: On Thursday 07 May 2009 05:23:41 Dickson S. Guedes wrote: This is a WIP patch (for the TODO item in the subject) that I'm putting in the Commit Fest queue for 8.5. How about you just put the values in a variable and use the existing facility to put those variables in the prompt? Change all pset.version calculations in case 'V' to something like case 'v' is doing with PG_VERSION? Yes, could be better. are we waiting for a new version of this one? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY WITH CSV FORCE QUOTE *
Jaime Casanova jcasa...@systemguards.com.ec wrote: i can find value for FORCE QUOTE * but what's the use case for FORCE NOT NULL? NULLs are not quoted (to be ,, ) because empty strings are written as . It comes from original implementation and not from my patch. I think we don't need to change the behavior. 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] Upgrading our minimum required flex version for 8.5
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Tom Lane Sent: Monday, July 13, 2009 7:43 PM To: Andrew Dunstan Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Upgrading our minimum required flex version for 8.5 Andrew Dunstan and...@dunslane.net writes: Well, it looks like there's a reason GnuWin32 hasn't advanced beyond 2.5.4a - after that the flex developers proceeded to make flex use a filter chain methodology that requires the use of fork(). Making it run on Windows without the support of Msys or Cygwin would involve some significant surgery, I suspect. Egad, this is a mess :-(. I noticed in the flex changelog that they'd switched to using m4 instead of implementing all the text processing themselves. I suppose this is a consequence of that. But I'm not prepared to agree that M$ lameness should restrict us to using only a 1990s version of flex. Didn't somebody mention upthread that there is a Windows port of 2.5.33 available? Maybe for the time being we need to think about keeping scan.c in CVS. It's not like scan.l gets updated all that often. We could if we had to, though it amounts to saying that Windows-based developers don't get to touch the scanner. regards, tom lane Flex 2.5.33 and bison 2.3 are available from mingw for windows. http://sourceforge.net/projects/mingw/files/ Since mingw programs don't need Cygwin installed, these should probably be OK for most Windows people. But if really needed, flex 2.5.33 could be ported (m4 is already ported). I'm also wonderings why flex is a problem, since there is a GNUwin32 (native) port of Bison 2.4.1 and m4, And Bison uses m4 these days, doesn't it? Perhaps it wouldn't be so hard to update flex to use the same m4 calling that bison uses? Chuck McDevitt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY WITH CSV FORCE QUOTE *
Jaime Casanova jcasa...@systemguards.com.ec wrote: i can find value for FORCE QUOTE * but what's the use case for FORCE NOT NULL? Oh, sorry. I misread your mail. The patch adds * options FORCE QUOTE and FORCE NOT NULL, too. Both of * mean all-columns for each options. The attached is an updated version of patch; just add documenation to copy.sgml. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center force_quote_all-20090714.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump Add dumping of comments on index columns
On Thu, Mar 26, 2009 at 2:39 AM, higeponhige...@gmail.com wrote:
Hi.
Here is a patch for pg_dump Commenting on a composite-type column.
This patch is for Todo item named Add dumping of comments on index
columns and composite type columns.
this one looks good to me, the only adjust i made to the patch is
change the name for the function that dump the comments from the
composite types columns for: dumpCompositeTypeColsComment that seems
more clearer to me...
the patch works just fine...
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
Index: src/bin/pg_dump/pg_dump.c
===
RCS file: /home/postgres/pgrepo/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.540
diff -c -r1.540 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 2 Jul 2009 21:34:32 - 1.540
--- src/bin/pg_dump/pg_dump.c 14 Jul 2009 07:22:38 -
***
*** 133,138
--- 133,139
static void dumpEnumType(Archive *fout, TypeInfo *tinfo);
static void dumpDomain(Archive *fout, TypeInfo *tinfo);
static void dumpCompositeType(Archive *fout, TypeInfo *tinfo);
+ static void dumpCompositeTypeColsComment(Archive *fout, TypeInfo *tinfo);
static void dumpShellType(Archive *fout, ShellTypeInfo *stinfo);
static void dumpProcLang(Archive *fout, ProcLangInfo *plang);
static void dumpFunc(Archive *fout, FuncInfo *finfo);
***
*** 6708,6716
--- 6709,6818
destroyPQExpBuffer(q);
destroyPQExpBuffer(delq);
destroyPQExpBuffer(query);
+
+ /* Dump column comments */
+ dumpCompositeTypeColsComment(fout, tinfo);
}
/*
+ * dumpCompositeTypeColsComment
+ * writes out to fout the comments on
+ * columns of composite type
+ */
+ static void
+ dumpCompositeTypeColsComment(Archive *fout, TypeInfo *tinfo)
+ {
+ CommentItem *comments;
+ int ncomments;
+ PGresult *res;
+ PQExpBuffer query;
+ PQExpBuffer attrquery = createPQExpBuffer();
+ PQExpBuffer target;
+ Oid colTableOid;
+ int i;
+ int ntups;
+ int i_attname;
+ int i_attnum;
+
+ appendPQExpBuffer(attrquery,
+ SELECT pg_class.tableoid,
+ pg_attribute.attname,
+ pg_attribute.attnum
+ FROM pg_class, pg_attribute
+ WHERE pg_class.oid = '%u' and pg_class.oid = pg_attribute.attrelid
+ ORDER BY pg_attribute.attnum
+ , tinfo-typrelid);
+
+ /* Fetch column's attname */
+ res = PQexec(g_conn, attrquery-data);
+ check_sql_result(res, g_conn, attrquery-data, PGRES_TUPLES_OK);
+ ntups = PQntuples(res);
+ if (ntups 1)
+ {
+ write_msg(NULL, query returned no rows: %s\n, attrquery-data);
+ exit_nicely();
+ }
+ colTableOid = atooid(PQgetvalue(res, 0, PQfnumber(res, tableoid)));
+
+ /* Search for comments associated with relation, using table */
+ ncomments = findComments(fout,
+ colTableOid,
+ tinfo-typrelid,
+ comments);
+
+ /* If comments exist, build COMMENT ON statements */
+ if (ncomments = 0)
+ return;
+
+ query = createPQExpBuffer();
+ target = createPQExpBuffer();
+
+ i_attnum = PQfnumber(res, attnum);
+ i_attname = PQfnumber(res, attname);
+ while (ncomments 0)
+ {
+ const char *descr = comments-descr;
+ /* Just to be safe */
+ const char *attname = unknown;
+ for (i = 0; i ntups; i++)
+ {
+ if (atoi(PQgetvalue(res, i, i_attnum)) == comments-objsubid)
+ {
+ attname = PQgetvalue(res, i, i_attname);
+ break;
+ }
+ }
+ resetPQExpBuffer(target);
+ appendPQExpBuffer(target, COLUMN %s.,
+ fmtId(tinfo-dobj.name));
+ appendPQExpBuffer(target, %s,
+ fmtId(attname));
+
+ resetPQExpBuffer(query);
+ appendPQExpBuffer(query, COMMENT ON %s IS , target-data);
+ appendStringLiteralAH(query, descr, fout);
+ appendPQExpBuffer(query, ;\n);
+
+ ArchiveEntry(fout, nilCatalogId, createDumpId(),
+ target-data,
+ tinfo-dobj.namespace-dobj.name,
+ NULL,
+ tinfo-rolname,
+ false, COMMENT, SECTION_NONE, query-data, , NULL,
+ (tinfo-dobj.dumpId), 1,
+ NULL, NULL);
+
+ comments++;
+ ncomments--;
+ }
+ destroyPQExpBuffer(attrquery);
+ destroyPQExpBuffer(query);
+ destroyPQExpBuffer(target);
+ PQclear(res);
+ }
+
+
+ /*
* dumpShellType
* writes out to fout the queries to create a shell type
*
--
Sent 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 patch for TODO Item: Add prompt escape to display the client and server versions
On Tuesday 14 July 2009 09:12:21 Jaime Casanova wrote: 2009/5/7 Dickson S. Guedes lis...@guedesoft.net: Em Qui, 2009-05-07 às 10:11 +0300, Peter Eisentraut escreveu: On Thursday 07 May 2009 05:23:41 Dickson S. Guedes wrote: This is a WIP patch (for the TODO item in the subject) that I'm putting in the Commit Fest queue for 8.5. How about you just put the values in a variable and use the existing facility to put those variables in the prompt? Change all pset.version calculations in case 'V' to something like case 'v' is doing with PG_VERSION? Yes, could be better. are we waiting for a new version of this one? At first glance it looked OK to me. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Upgrading our minimum required flex version for 8.5
Chuck McDevitt wrote: Flex 2.5.33 and bison 2.3 are available from mingw for windows. http://sourceforge.net/projects/mingw/files/ Since mingw programs don't need Cygwin installed, these should probably be OK for most Windows people. But if really needed, flex 2.5.33 could be ported (m4 is already ported). I'm also wonderings why flex is a problem, since there is a GNUwin32 (native) port of Bison 2.4.1 and m4, And Bison uses m4 these days, doesn't it? Perhaps it wouldn't be so hard to update flex to use the same m4 calling that bison uses? It is part of the MSys utilities, and requires that you have MSys installed. MSys is in effect a Cygwin equivalent. The flex from MinGW will NOT run standalone. I have tried. If you think it can be ported to run standalone (i.e. without either Cygwin or MSys), have at it. Or take my advice and don't waste time trying. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY WITH CSV FORCE QUOTE *
On Tue, Jul 14, 2009 at 2:26 AM, Itagaki Takahiroitagaki.takah...@oss.ntt.co.jp wrote: Jaime Casanova jcasa...@systemguards.com.ec wrote: i can find value for FORCE QUOTE * but what's the use case for FORCE NOT NULL? Oh, sorry. I misread your mail. The patch adds * options FORCE QUOTE and FORCE NOT NULL, too. Both of * mean all-columns for each options. and i misunderstood your patch... is actually an extend of an existing functionality, and both options are necesary for consistency... do you hear an echo here? ;) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Index-only scans
Simon Riggs wrote: On Mon, 2009-07-13 at 10:16 +0300, Heikki Linnakangas wrote: Implementing index-only scans requires a few changes: I would like to see a clear exposition of the use cases and an an analysis of the costs and benefits of doing this. It sounds cool, but I want to know it is cool before we spend time solving all of the juicy problems. Perhaps a glue-and-string patch would help. There's a working prototype at in my git repository at git.postgresql.org. Extra buffer accesses for vismap, crash-safe vismap sound like performance issues, as well as planner time, not to mention all the tuits needed. Will it damage the general case? It does add some work to the planner, but I don't think it's noticeable. The visibility map accesses are only needed when we're doing an index-only scan, not in the general case, so the impact of those come down to how well we can estimate the cost of index-only scans, so that an index-only scan is not chosen when not beneficial. The single SQL example mentioned already has at least two mechanisms for improving performance of that type of query. We probably don't need another, or at least we need a good analysis of why. Well, another class of queries where index-only scans are beneficial is when you fetch a range of rows from index, where the heap fetches result in a lot of random I/O. Clustering helps with that, but you can only cluster a table on one column. A classic example where that's a problem is a many-to-many relationship: CREATE TABLE a (aid integer, ...); CREATE TABLE b (bid integer, ...); CREATE TABLE manytomany (aid integer, bid integer); CREATE INDEX a_b ON manytomany (aid, bid); CREATE INDEX b_a ON manytomany (bid, aid); If you need to query the many-to-many relationship in both directions, ie: SELECT bid FROm manytomany WHERE aid = ? SELECT aid FROM manytomany WHERE bid = ? You have to choose which index you cluster the table on, which will be fast, and the other query will be slow. The benefit that occurs to me most is covered indexes, i.e. it opens up new and interesting indexing strategies. Covered indexes are also one kind of materialized view. It may be better to implement mat views and gain wider benefits too. Materialized view sure would be nice, but doesn't address quite the same use cases. Doesn't help with the many-to-many example above, for example. We should have both. Or maybe index-only scans are mat views, via some cunning plan? Heh, no :-). -- 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] Index-only scans
Simon Riggs wrote: On Mon, 2009-07-13 at 10:16 +0300, Heikki Linnakangas wrote: Implementing index-only scans requires a few changes: I would like to see a clear exposition of the use cases and an an analysis of the costs and benefits of doing this. It sounds cool, but I want to know it is cool before we spend time solving all of the juicy problems. BTW, there's another trick that I'm *not* going to implement yet, which is to allow joins using data from indexes only, and fetching the rest of the columns after the join. For example: CREATE TABLE a (aid integer PRIMARY KEY, adata text); CREATE TABLE b (bid integer PRIMARY KEY, bdata text); SELECT aid, adata, bid, bdata FROM a, b WHERE aid = bid; If the join is very selective, IOW there's only a few matching rows, it is a lot more efficient to perform the join first using just the indexes, and fetch adata and bdata columns and check visibility for the matching rows only. You can get pretty close by clustering the tables, but the wider the tables the bigger the difference. -- 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] Index-only scans
On Tue, 2009-07-14 at 11:23 +0300, Heikki Linnakangas wrote: The single SQL example mentioned already has at least two mechanisms for improving performance of that type of query. We probably don't need another, or at least we need a good analysis of why. Well, another class of queries where index-only scans are beneficial is when you fetch a range of rows from index, where the heap fetches result in a lot of random I/O. Which we just optimised, no? I see it could be even better, but do we really need that class of query to be optimized again? (Possibly...) I'm not doubting your ability to solve every problem we face, just advising that we crunch a few numbers before we spend months on implementing all of this. It would be useful to have projected gains and a useful test case ahead of time, so we can measure the potential as early as possible. Yes, you don't need to convince me before you proceed, but if you can at least share the thoughts that have convinced you that would help. -- 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
[HACKERS] Index-only quals
I already posted the patch to satisfy quals using data from index yesterday (http://archives.postgresql.org/message-id/4a5b5386.3070...@enterprisedb.com), but here's some more thoughts about the patch: 1, The patch adds a flag to IndexScanDesc (needIndexTuple) that you can set to request the indexam to return index tuples. If set, amgettuple stores the current index tuple to a new xs_itup field in IndexScanDesc. needIndexTuple is just a humble request - the indexam can ignore it if it can't return the index tuple. For example in B-tree, if new tuples are inserted on the leaf page, we fail to find the index tuple and bail out. Alternatively, b-tree could copy all the matching tuples to private memory when we step to next page, like it copies all the TIDs, but that seems expensive. We already discussed how to know which indexams and which opclasses can return index tuples at all. I'm thinking of just adding a new boolean column to pg_am. In GiST, it depends on the opclass whether it can regurgitate the original data, but I'm only going to support b-trees for now so I'd like to not bother with a more complex scheme yet. 2. Before the patch, there is two ways an index scan node can check restriction clauses. If the restriction matches an index column and uses a suitable operator, it can be used as an index key. All other restrictions are handled by fetching the heap tuple, and using ExecQual. This patch introduces a third way: fetch the index tuple, and use ExecQual against it. I'm calling these quals index-only quals. In EXPLAIN, they are shown as Index-Only Filters. In find_usable_indexes(), we scan through restrictinfos and pick those that are not used as index keys, but can be evaluated using data from the index. The existence of such quals affects the estimated cost, and whether we consider using the index at all. The cost estimation is quite dummy still, I haven't given it much thought or testing yet. create_indexscan_plan() contains roughly the same logic as find_usable_indexes(), I couldn't figure out a good way to eliminate the duplication. Perhaps I'm doing this at the wrong place, I hope someone has ideas on how this should be done. Index-only quals are stored in IndexScan in form where varattnos of Vars have been replaced with varattnos of the index. This is the same format used for indexqual. The comments in planmain.h suggest that we might want to refactor that: * indexqual has the same form, but the expressions have been commuted if * necessary to put the indexkeys on the left, and the indexkeys are replaced * by Var nodes identifying the index columns (varattno is the index column * position, not the base table's column, even though varno is for the base * table). This is a bit hokey ... would be cleaner to use a special-purpose * node type that could not be mistaken for a regular Var. But it will do * for now. (We do the same for indexonlyqual) The executor needs the index-only quals in form that can be evaluated It works as it is, but maybe we should bite that bullet now. As this work continues to support returning data from index without accessing the heap at all, we'll need to have an index-only equivalent of the target list as well. Replacing the Vars that refer to heap atts with index atts is done in new make_indexonly_expr() function. The logic is almost identical to fix_indexqual_references(). They probably should be merged, but are separate now. -- 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
[HACKERS] Comments on automatic DML routing and explicit partitioning subcommands
(submitting review slightly earlier than start of commitfest) Kedar, Thank you very much for the patch. Well done for getting to this stage. There is definitely much support for your work. My thoughts after initial review are fairly wide ranging. Overall, the patch is not ready/close to commit in this commitfest. My estimate is that it will take next 2 commitfests before we could commit it; that is still comfortably within this release. PARTITIONING * I have one main comment that effects about 50% of the patch: We should not be using triggers. Triggers are a useful external solution, but they are not the best or even a desirable approach internally. Inserting data using triggers creates a huge overhead in AfterTrigger events held in memory. In my view this is totally unsuitable for use with VLDBs, which is exactly the point of partitioning. There is nothing we can do to reduce or aggregate these trigger events as is possible with RI trigger checks (not yet done, but possible...). It should be possible to avoid triggers altogether by having a function that dynamically calculates the target partition relid from an input tuple, somewhere around ExecInsert() and during DoCopy(). Thus, current executor has minor changes and we do all required cleverness in a partition function evaluation module. You should be able to rework much of the code into that form. [bsearch()] Dynamic evaluation would also help SQL such as in-bound FK checks, i.e. FK checks against the partitioned table. That needs some other work also, but the guts of it will be similar to both problems. Avoiding triggers will also avoid the horrible looking stuff with pg_dump. Might see a few errors is a phrase unlikely to result in patch application, in my experience. :-) Whether we accept that or not, there should be a clear focus on measuring and reducing the time taken to route an inserted row through to its target partition, rather than just automating it. There may be an alternative that achieves better efficiencies. We should be measuring timings with 100s-1000s of partitions, not just 2 or 3. Dynamic partition evaluation will also be important in other places. Partition evaluation * I don't see any need or purpose for HASH partitioning. I suggest that is added as a later patch once everything else is accepted, if there is clear separate reason for it to exist. (I'm certain it has meaning for your sponsors; we must differentiate between the needs of add-on products and the needs of Postgres core). There isn't much point discussing this issue until other parts are committed anyway and a smaller patch will be more easily committed. Skip it for now, please. * It appears that *every* update is handled as a delete plus a re-insertion into the parent(s). Moving between partitions is usually an indicator of a poor choice of partitioning scheme, so we should not be de-optimising normal updates because of corner cases. I think we need to handle/optimize the stays-in-same-partition and the moves-partition cases differently. For me it would be an acceptable limitation to disallow row movement, at least until we get the first part of this committed. * I think we should be making some clear assumptions that partitions have identical row types to their parent and that we have only a single parent for each partition. There seems like extra code to handle additional cases. PATCH * There is no explanatory or command syntax documentation. That prevents people from understanding what you are attempting to deliver, apart from reading the test case. Many people would probably have comments on how partitioning works and syntax if they could see docs. (Please don't refer me to earlier design docs - we need to see how this patch works; external docs go stale very quickly. And no need to rehash the description about the need for and benefits of partitioning - we already have chapters on it in the pg docs.) * The test case is nowhere near long enough for the number of additional commands you are adding. I would expect to see about 10x as many test cases to cover all the options, side cases and complexity. Multiple data types, etc * There are very few comments, and those that do exist are typically one-liners. Look through other parts of the existing code and judge how much commenting is necessary. Basically, lots of long explanatory comments. Why this way? Why here? Why this API? Why not another way, What assumptions, approximations have been made etc.. (I've found this helps the author very much, not just the reviewer. Once extensive comments have been received you will be on a later version and you start to forget how parts work between all the different versions) * The patch is 8000 lines long. I feel like it is trying to do too much in one go and we should break it down. * I suggest that you strip out all the stuff about complex partition functions until we get the main items agreed, e.g. SPLIT PARTITION etc.. This will help you to concentrate
[HACKERS] Sampling profiler updated
I updated Sampling profiler patch to be applied to HEAD cleanly. Basic concept of the patch is same as DTrace probes: Call pgstat_push_condition(condition) before a operation and call pgstat_pop_condition() at the end of the operation. Those functions should be light-weight because they only change a variable on shared memory without any locks. Stats collector process checks those shard variables periodically and sums the status in collector's local memory. We cannot know exact numbers of each operation, but can expect the sampling numbers reflect the tendency of times spend in each operation. The sampling result can be retrived with pg_profiles system view. Of course the profiler could be implemented on the top of DTrace or SystemTap, but it is not so easy if we try to avoid any performance regressions and to provide the information by VIEW-like interface. Also, this approach is platform-independent. A new feature compared with previous patch is function pgstat_register_condition(condition, name). We can add user-defined conditions in extended modules. Comments welcome. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center profiler-20090714.gz Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] could not reattach to shared memory on Windows
Hello,
This patch using VirtualAlloc()/VirtualFree() to avoid failing in
reattach to shared memory.
Can this be added to CommitFest ?
Recent threads in pgsql-bugs are
http://archives.postgresql.org/pgsql-bugs/2009-07/msg00036.php
This fix is almost same as previous patch. debug code is deleted.
http://archives.postgresql.org/pgsql-bugs/2009-07/msg00078.php
Regards,
--
Tsutomu Yamada
SRA OSS, Inc. Japan
Index: src/backend/port/win32_shmem.c
===
RCS file: /mnt/prj/pg/cvsmirror/pg/pgsql/src/backend/port/win32_shmem.c,v
retrieving revision 1.11
diff -c -r1.11 win32_shmem.c
*** src/backend/port/win32_shmem.c 11 Jun 2009 14:49:00 - 1.11
--- src/backend/port/win32_shmem.c 14 Jul 2009 10:11:44 -
***
*** 18,23
--- 18,24
unsigned long UsedShmemSegID = 0;
void *UsedShmemSegAddr = NULL;
+ static Size UsedShmemSegSize = 0;
static void pgwin32_SharedMemoryDelete(int status, Datum shmId);
***
*** 233,238
--- 234,240
/* Save info for possible future use */
UsedShmemSegAddr = memAddress;
+ UsedShmemSegSize = size;
UsedShmemSegID = (unsigned long) hmap2;
return hdr;
***
*** 257,262
--- 259,273
Assert(UsedShmemSegAddr != NULL);
Assert(IsUnderPostmaster);
+ /* release memory region
+* that reserved by parant process
+*/
+ if (VirtualFree(UsedShmemSegAddr, 0, MEM_RELEASE) == 0)
+ {
+ elog(LOG, failed to release reserved memory region (addr=%p):
%lu,
+UsedShmemSegAddr, GetLastError());
+ }
+
hdr = (PGShmemHeader *) MapViewOfFileEx((HANDLE) UsedShmemSegID,
FILE_MAP_READ | FILE_MAP_WRITE, 0, 0, 0, UsedShmemSegAddr);
if (!hdr)
elog(FATAL, could not reattach to shared memory (key=%d,
addr=%p): %lu,
***
*** 302,304
--- 313,335
if (!CloseHandle((HANDLE) DatumGetInt32(shmId)))
elog(LOG, could not close handle to shared memory: %lu,
GetLastError());
}
+
+ /*
+ * pgwin32_ReserveSharedMemory(HANDLE pChild)
+ * Reserve shared memory area,
+ * BEFORE child process allocates memory for DLL and/or others.
+ */
+ void
+ pgwin32_ReserveSharedMemory(HANDLE pChild)
+ {
+ void *memAddress;
+
+ Assert(UsedShmemSegAddr != NULL);
+ Assert(UsedShmemSegSize != 0);
+ memAddress = VirtualAllocEx(pChild, UsedShmemSegAddr, UsedShmemSegSize,
+ MEM_RESERVE,
PAGE_READWRITE);
+ if (memAddress == NULL) {
+ elog(LOG, could not reserve shared memory region (addr=%p):
%lu,
+UsedShmemSegAddr, GetLastError());
+ }
+ }
Index: src/backend/postmaster/postmaster.c
===
RCS file: /mnt/prj/pg/cvsmirror/pg/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.584
diff -c -r1.584 postmaster.c
*** src/backend/postmaster/postmaster.c 8 Jul 2009 18:55:35 - 1.584
--- src/backend/postmaster/postmaster.c 13 Jul 2009 08:40:36 -
***
*** 3643,3648
--- 3643,3655
elog(LOG, could not close handle to backend parameter file:
error code %d,
(int) GetLastError());
+ {
+ /* reserve shared memory area before ResumeThread() */
+ /* XXX: if it fail ? */
+ extern void pgwin32_ReserveSharedMemory(HANDLE);
+ pgwin32_ReserveSharedMemory(pi.hProcess);
+ }
+
/*
* Now that the backend variables are written out, we start the child
* thread so it can start initializing while we set up the rest of the
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] *_collapse_limit, geqo_threshold
On Thu, Jul 9, 2009 at 5:38 AM, Noah Mischn...@leadboat.com wrote: z Describing in those terms illuminates much. While the concepts do suggest 2^N worst-case planning cost, my artificial test case showed a rigid 4^N pattern; what could explain that? Isn`t that just so that the planner has to examine O(2^N) subsets of relations, and do O(2^N) work for each of them? To create level N join the planner chooses pairs of level k and level N-k joins. the count of level k joins is O(2^k), the count of level N-k ones is O(2^(N-k)). Together it is O(N) * O(2^N) * O(2^k) * O(2^(N-k)) which is O(N* 4^N) . This is for the worst case. If we could make a better estimate of the required planning time (I believe that the input data for a good heuristic is a matrix which says which relation is constrained to which relation), we could make better decisions about when to flatten subqueries, collapse joins, launch geqo... Greetings Marcin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Index-only scans
On Monday 13 July 2009 16:38:18 Bruce Momjian wrote: Heikki Linnakangas wrote: Even if we don't solve the visibility map problem, just allowing the executor to evaluate quals that are not directly indexable using data from the index, would be useful. For example, SELECT * FROM foo WHERE textcol LIKE '%bar%', and you have a b-tree index on textcol, the planner could choose a full-index-scan, apply the '%bar%' filter on the index tuples, and only fetch those heap tuples that match that qual. Interesting, I had not considered that. You are using the index as a single-column table that can be scanned more quickly than the heap. On slightly bizarre application of this could be that you create a poor man's column storage by creating heap indexes on tables. These would just be narrower copies of the original heap, but allow faster fetching. This might actually be useful for data types that don't support btree indexing. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] could not reattach to shared memory on Windows
On Tue, Jul 14, 2009 at 6:22 AM, Tsutomu Yamadatsut...@sraoss.co.jp wrote: Hello, This patch using VirtualAlloc()/VirtualFree() to avoid failing in reattach to shared memory. Can this be added to CommitFest ? Patches for CommitFest should be added here: http://commitfest.postgresql.org/action/commitfest_view/open ...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] [GENERAL] pg_migrator not setting values of sequences?
bruce wrote:
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
Tilmann Singer wrote:
However, all of the sequences were at the initial values and not
bumped up to the last used value as I would have expected. The first
nextval call on any sequence in the migrated 8.4 database always
returned 1.
Wow, that is also surprising. I am going to have to run some tests to
find the cause, but it certainly is not intended.
Looks like pg_migrator neglects to include relkind 'S' in the set of
tables that it needs to physically migrate.
Thanks, I have fixed pg_migrator with the attached patch. Once we find
the cause of the lovacuum problem, I will make a new pg_migrator release.
The patch I posted definately fixes a bug. Not sure how I missed the
regression changes caused by not migrating sequences; I thought it was
an issue with cached sequence values, not an actual bug.
However, I with the patch, I am now seeing another difference; a
database with:
SELECT pg_catalog.setval('clstr_tst_a_seq', 33, true);
becomes:
SELECT pg_catalog.setval('clstr_tst_a_seq', 33, false);
and the is_called column of the migrated sequences is NULL:
regression= \d check_seq
Sequence public.check_seq
Column | Type |Value
---+-+-
sequence_name | name| check_seq
last_value| bigint | 1
start_value | bigint | 1
increment_by | bigint | 9223372036854775807
max_value | bigint | 1
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 25387551686656
is_cycled | boolean | f
is_called | boolean |
regression= select * from check_seq where is_called is null;
sequence_name | last_value | start_value |increment_by |
max_value | min_value | cache_value |log_cnt | is_cycled |
is_called
---++-+-+---+---+-++---+---
check_seq | 1 | 1 | 9223372036854775807 |
1 | 1 | 1 | 25387551686656 | f |
(1 row)
Something is certainly wrong. Did we change sequence table format from
8.3 to 8.4?
--
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] [GENERAL] pg_migrator not setting values of sequences?
Bruce Momjian br...@momjian.us writes: Did we change sequence table format from 8.3 to 8.4? Oh, yes we did: we added a start_value column. So this is going to take more work than that :-( regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_migrator not setting values of sequences?
Bruce Momjian wrote: Something is certainly wrong. Did we change sequence table format from 8.3 to 8.4? 8.3 does not have start_value. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] could not reattach to shared memory on Windows
Tsutomu Yamada wrote: This patch using VirtualAlloc()/VirtualFree() to avoid failing in reattach to shared memory. Can this be added to CommitFest ? Since this fixes a very annoying bug present in older versions, I think this should be backpatched all the way back to 8.2. Some notes about the patch itself: - please use ereport() instead of elog() for error messages - Are you really putting the pgwin32_ReserveSharedMemory declaration inside a function? Please move that into the appropriate header file. - Failure to reserve memory in pgwin32_ReserveSharedMemory should be a FATAL error I think, not simply LOG. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Comments on automatic DML routing and explicit partitioning subcommands
Simon, Simon Riggs escribió: Kedar, Thank you very much for the patch. Well done for getting to this stage. There is definitely much support for your work. Thanks for the thorough review. Please when you add a comment to the commitfest app, make sure you specify the message-id where you posted the review to pgsql-hackers on the comment so that there's a link to the archives. I added a new comment with the message-id, but please edit your comment and add it, at which point I'll remove mine. Thanks. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: generalized index constraints
Updated patch attached.
Changes:
* Added syntax support:
CREATE INDEX foo_idx ON foo ... (a CONSTRAINT =, b CONSTRAINT );
* More aggressively clear the shared memory entries to avoid
unnecessary checks
* Code cleanup
TODO:
* When adding constraint to table with data already in it, verify that
existing data satisfies constraint.
* Clean up error messages a little
* Docs
The following are possible TODO items, but I'd like to get some feedback
first:
* It seems like an alternative language would be better:
ALTER TABLE foo ADD INDEX CONSTRAINT optional_name (a =, b )
USING foo_idx;
This language would be more like a table constraint that happens to
use an index. I think it's better because it allows multiple
constraints to be enforced by the same index.
* Right now it only supports index AMs that offer amgettuple, which
excludes GIN. Consider adding a crude implementation of gingettuple
that just calls gingetbitmap internally (obviously providing no
performance advantage over gingetbitmap).
Regards,
Jeff Davis
diff --git a/src/backend/access/index/indexam.c b/src/backend/access/index/indexam.c
index 1515d9f..d88387b 100644
--- a/src/backend/access/index/indexam.c
+++ b/src/backend/access/index/indexam.c
@@ -26,6 +26,7 @@
* index_vacuum_cleanup - post-deletion cleanup of an index
* index_getprocid - get a support procedure OID
* index_getprocinfo - get a support procedure's lookup info
+ * index_check_constraint - check index constraints
*
* NOTES
* This file contains the index_ routines which used
@@ -64,9 +65,13 @@
#include access/relscan.h
#include access/transam.h
+#include miscadmin.h
#include pgstat.h
#include storage/bufmgr.h
#include storage/lmgr.h
+#include storage/lwlock.h
+#include storage/procarray.h
+#include utils/lsyscache.h
#include utils/relcache.h
#include utils/snapmgr.h
#include utils/tqual.h
@@ -116,6 +121,19 @@ do { \
static IndexScanDesc index_beginscan_internal(Relation indexRelation,
int nkeys, ScanKey key);
+typedef struct
+{
+ Oid relid;
+ TransactionId xid;
+ ItemPointerData tid;
+} CurrentIndexInsertEntry;
+
+static CurrentIndexInsertEntry *CurrentIndexInsertsTable = NULL;
+
+static bool index_check_constraint_conflict(TupleTableSlot *slot,
+ HeapTuple tup, int2 *heap_attnums,
+ int2 index_natts,
+ Oid *constraint_procs);
/*
* index_ interface functions
@@ -846,3 +864,303 @@ index_getprocinfo(Relation irel,
return locinfo;
}
+
+void
+index_check_constraint(Relation heap, Relation index,
+ ItemPointer tid, TupleTableSlot *slot)
+{
+ IndexScanDesc index_scan;
+ HeapTuple tup;
+ ScanKeyData *scankeys;
+ int2vector *constr_strats;
+ Oid*constr_procs;
+ int i;
+ int2 *heap_attnums = index-rd_index-indkey.values;
+ int2 index_natts = index-rd_index-indnatts;
+ SnapshotData DirtySnapshot;
+ int nkeys = 0;
+
+ CurrentIndexInsertEntry potential_conflicts[MaxBackends];
+ int n_potential_conflicts = 0;
+
+ /* Find constraint strategy numbers */
+ constr_strats = RelationGetIndexConstraintStrategies(index);
+
+ /* return if no constraint */
+ if (constr_strats == NULL)
+ return;
+
+ /*
+ * if any of the indexed columns are NULL, the constraint
+ * is satisfied
+ */
+ for (i = 0; i index_natts; i++)
+ if (slot_attisnull(slot, heap_attnums[i]))
+return;
+
+ /*
+ * Find the function that tests for a conflict based on the
+ * strategy number, operator family, and types.
+ */
+ constr_procs = palloc(sizeof(Oid) * index_natts);
+ for (i = 0; i index_natts; i++)
+ {
+ /*
+ * Find the procedure implementing the strategy for the
+ * index for two arguments both with the type of the
+ * indexed attribute.
+ */
+ Oid oper;
+ Oid opfamily = index-rd_opfamily[i];
+ StrategyNumber strategy = constr_strats-values[i];
+ Oid typeOid = heap-rd_att-attrs[heap_attnums[i] - 1]-atttypid;
+
+ if (strategy == InvalidStrategy)
+continue;
+
+ oper = get_opfamily_member(opfamily, typeOid, typeOid, strategy);
+
+ if(OidIsValid(oper))
+constr_procs[i] = get_opcode(oper);
+ else
+elog(ERROR, cannot determine operator for type %d and
+ strategy %d, typeOid, strategy);
+ }
+
+ /*
+ * Check for conflicts with concurrent inserts. These are
+ * inserts that are actually in-progress now, and have not
+ * actually been put in the index yet.
+ */
+
+ Assert (CurrentIndexInsertsTable != NULL);
+
+ LWLockAcquire(IndexConstraintLock, LW_SHARED);
+
+ for (i = 0; i MaxBackends; i++)
+ {
+ CurrentIndexInsertEntry entry = CurrentIndexInsertsTable[i];
+
+ if (RelationGetRelid(heap) == entry.relid
+!TransactionIdIsCurrentTransactionId(entry.xid)
+TransactionIdIsInProgress(entry.xid))
+ {
+ potential_conflicts[n_potential_conflicts++] = entry;
+
Re: [HACKERS] [PATCH] could not reattach to shared memory on Windows
Alvaro Herrera alvhe...@commandprompt.com writes: Tsutomu Yamada wrote: This patch using VirtualAlloc()/VirtualFree() to avoid failing in reattach to shared memory. Since this fixes a very annoying bug present in older versions, I think this should be backpatched all the way back to 8.2. Agreed, but first we need some evidence that it actually fixes the problem. How can we acquire such evidence? - please use ereport() instead of elog() for error messages This is only appropriate if they're user-facing messages, which probably errors in this area are not ... 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] Prefix support for synonym dictionary
Hi there,
attached is our patch for CVS HEAD, which adds prefix support for synonym
dictionary.
Quick example:
cat $SHAREDIR/tsearch_data/synonym_sample.syn
postgrespgsql
postgresql pgsql
postgre pgsql
gogle googl
indices index*
=# create text search dictionary syn(
template=synonym,synonyms='synonym_sample');
=# select ts_lexize('syn','indices');
ts_lexize
---
{index}
(1 row)
=# create text search configuration tst ( copy=simple);
=# alter text search configuration tst alter mapping for asciiword with syn;
=# select to_tsquery('tst','indices');
to_tsquery
'index':*
(1 row)
=# select 'indexes are very useful'::tsvector @@ to_tsquery('tst','indices');
?column?
--
t
(1 row)
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
synonym_prefix.gz
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] could not reattach to shared memory on Windows
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Tsutomu Yamada wrote: This patch using VirtualAlloc()/VirtualFree() to avoid failing in reattach to shared memory. Since this fixes a very annoying bug present in older versions, I think this should be backpatched all the way back to 8.2. Agreed, but first we need some evidence that it actually fixes the problem. How can we acquire such evidence? Send the patch to the people who has reported trouble and see if it seems gone? If somebody is able to build patched Win32 packages I could point a couple of guys in the spanish list to them. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH 1/2 v3] [libpq] rework sigpipe-handling macros
Jeremy Kerr wrote: Also, since we don't need to declare variables in the macros, we can change the code to be implemented as static inlines. Does this work in compilers other than GCC? I think we use some kludges to protect against them ... see pg_list.h for the canonical example. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] could not reattach to shared memory on Windows
Alvaro Herrera wrote: Tsutomu Yamada wrote: This patch using VirtualAlloc()/VirtualFree() to avoid failing in reattach to shared memory. Can this be added to CommitFest ? Since this fixes a very annoying bug present in older versions, I think this should be backpatched all the way back to 8.2. That doesn't sound like a good idea, at least not before we have more experience of how the patch is working in the field. -- 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] Sampling profiler updated
On Tue, Jul 14, 2009 at 4:47 AM, Itagaki Takahiroitagaki.takah...@oss.ntt.co.jp wrote: I updated Sampling profiler patch to be applied to HEAD cleanly. shouldn't pg_stat_reset() reset these values? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Predicate migration on complex self joins
On Mon, Jul 13, 2009 at 07:06:40PM +0100, Simon Riggs wrote: On Mon, 2009-07-13 at 13:33 -0400, Tom Lane wrote: It's hard to believe this sort of case comes up often enough to justify the cycles that would be expended (on *every* join query) to try to recognize it. Yeh, damn ORMs seem to spring up faster than vines. Not just because of this but I wonder if we might benefit from an optimizer setting specifically aimed at the foolishnesses of automatically generated SQL. The best suggestion I heard was to carry on optimizing until the plan looked cheap enough or all the options had been exhausted. In practical terms; I think this means doing the planning in two stages, try with all the simple optimizations and see if it results in less than n page accesses, if it's above this level then try again with all the optimizations turned on. -- Sam http://samason.me.uk/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Launching commitfest.postgresql.org
Brendan Jurd wrote: 2009/7/14 Robert Haas robertmh...@gmail.com: +1 for redirecting the whole site. I don't think the extra CPU load of SSL is going to bother anyone for the amount of traffic we're likely to have on that site. Simplicity is good. +1 for SSL on all pages from me also. yeah fine with me - who is going to do this? Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] could not reattach to shared memory on Windows
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Since this fixes a very annoying bug present in older versions, I think this should be backpatched all the way back to 8.2. Agreed, but first we need some evidence that it actually fixes the problem. How can we acquire such evidence? Apply to CVS HEAD and have people test it. I wouldn'ẗ be opposed to back-patching to 8.4 where it would receive more testing in real life. If we're really uneasy about it, provide a switch to turn it off if it causes problems. - please use ereport() instead of elog() for error messages This is only appropriate if they're user-facing messages, which probably errors in this area are not ... Heh, that's what we hope :-). -- 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] COPY WITH CSV FORCE QUOTE *
Itagaki Takahiro wrote: Jaime Casanova jcasa...@systemguards.com.ec wrote: i can find value for FORCE QUOTE * but what's the use case for FORCE NOT NULL? Oh, sorry. I misread your mail. The patch adds * options FORCE QUOTE and FORCE NOT NULL, too. Both of * mean all-columns for each options. I still don't understand the use case for FORCE NOT NULL *. FORCE QUOTE * is relatively benign. In particular, it doesn't affect the null-preserving properties of our CSV implementation, since it still won't (or shouldn't) quote null values. FORCE NOT NULL is in any case a fairly blunt instrument - it doesn't work for a column of any type that doesn't accept an empty string as valid input, such as numeric types. I note that this patch was (I think) originally submitted without prior discussion. That's not following best practice. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Deferrable unique constraints
On Sun, 2009-07-12 at 14:14 +0100, Dean Rasheed wrote: Here is an updated version of this patch which should apply to HEAD, with updated docs, regression tests, pg_dump and psql \d. It works well for small numbers of temporary uniqueness violations, and at least as well (in fact about twice as fast) as deferred FK checks for large numbers of deferred checks. I took a brief look at this. You're extending the index AM, and that might not be necessary. It might be fine, but usually there is a lot of discussion around the changing of important APIs, so it might be worth looking at alternatives. With the patch I'm working on for generalized index constraints, there would be no need to extend the index AM. However, I don't expect my mechanism to replace the existing unique btree constraints, because I would expect the existing unique constraints to be faster (I haven't tested yet, though). Perhaps we could instead use the TRY/CATCH mechanism. It's generally difficult to figure out from the code exactly what happened, but in this case we have the error code ERRCODE_UNIQUE_VIOLATION. So, just check for that error code rather than passing back a boolean. You might want to change the signature of _bt_check_unique() so that it doesn't have to raise the error inside, and you can raise the error from _bt_doinsert(). The only problem there is telling the btree AM whether or not to do the insert or not (i.e. fake versus real insert). Perhaps you can just do that with careful use of a global variable? Sure, all of this is a little ugly, but we've already acknowledged that there is some ugliness around the existing unique constraint and the btree code that supports it (for one, the btree AM accesses the heap). I propose trying to improve performance and scalability for large numbers of deferred checks in a separate patch. Would it be possible to just check how long the list of potential conflicts is growing, and if it gets to big, just replace them all with a bulk check event? 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] [PATCH] could not reattach to shared memory on Windows
On Tue, Jul 14, 2009 at 10:28 AM, Alvaro Herreraalvhe...@commandprompt.com wrote: Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Tsutomu Yamada wrote: This patch using VirtualAlloc()/VirtualFree() to avoid failing in reattach to shared memory. Since this fixes a very annoying bug present in older versions, I think this should be backpatched all the way back to 8.2. Agreed, but first we need some evidence that it actually fixes the problem. How can we acquire such evidence? Send the patch to the people who has reported trouble and see if it seems gone? If somebody is able to build patched Win32 packages I could point a couple of guys in the spanish list to them. - identify some people with the problem and talk to them for: 1) get a way to reproduce the error (a lot dificult, IIRC we try a few times i fail to fail) or 2) get their support for test - commit it for the first alpha release, or the just talked nigthly stable builds... - let the tests begin :) so, apply it just before the alpha and if it not works remove it just after the alpha... last time i build a win32 binary (not whole package) for windows users to test a patch they disappear very quickly... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Upgrading our minimum required flex version for 8.5
Andrew Dunstan wrote: Chuck McDevitt wrote: Flex 2.5.33 and bison 2.3 are available from mingw for windows. http://sourceforge.net/projects/mingw/files/ Since mingw programs don't need Cygwin installed, these should probably be OK for most Windows people. But if really needed, flex 2.5.33 could be ported (m4 is already ported). I'm also wonderings why flex is a problem, since there is a GNUwin32 (native) port of Bison 2.4.1 and m4, And Bison uses m4 these days, doesn't it? Perhaps it wouldn't be so hard to update flex to use the same m4 calling that bison uses? It is part of the MSys utilities, and requires that you have MSys installed. MSys is in effect a Cygwin equivalent. The flex from MinGW will NOT run standalone. I have tried. If you think it can be ported to run standalone (i.e. without either Cygwin or MSys), have at it. Or take my advice and don't waste time trying. What I have done is built a version of flex 2.5.35 with Cygwin, and bundled the .exe with the Cygwin DLL (the only other thing it should need, for our purposes) in a zip file. It can currently be fetched from http://developer.postgresql.org/~adunstan/windows-flex-2.5.35.zip. That way people won't have to do a full Cygwin or MSys install to use it. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Upgrading our minimum required flex version for 8.5
Andrew Dunstan and...@dunslane.net writes: What I have done is built a version of flex 2.5.35 with Cygwin, and bundled the .exe with the Cygwin DLL (the only other thing it should need, for our purposes) in a zip file. It can currently be fetched from http://developer.postgresql.org/~adunstan/windows-flex-2.5.35.zip. That way people won't have to do a full Cygwin or MSys install to use it. Sounds good. The other idea that was suggested (putting scan.c into CVS) seems problematic on reflection. I don't believe that CVS will guarantee that scan.c has a same-or-newer timestamp as scan.l if both are pulled from CVS; so you might find make trying to rebuild scan.c 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] Index-only scans
Now I'm back where I can go look at the source code :-) Thanks. -Original Message- From: Jaime Casanova [mailto:jcasa...@systemguards.com.ec] Sent: Monday, July 13, 2009 8:40 PM To: Mischa Sandberg Cc: Heikki Linnakangas; PostgreSQL-development Subject: Re: [HACKERS] Index-only scans On Mon, Jul 13, 2009 at 5:38 PM, Mischa Sandbergmischa.sandb...@sophos.com wrote: Does PG have an intermediate execution node to sort/batch index entries (heap tuple ptrs) by heap page prior to lookup? Something mssql does ... it sounds a lot like a bitmap index scan -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Alpha release process
== Announcing == Announce to: * PostgreSQL News * pgsql-hackers * pgsql-general(?) * pgsql-packagers Also pgsql-announce, no? I'm happy to write announcements. I'm also happy to give someone else some practice ... I'll see if anyone is interested on -advocacy. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] could not reattach to shared memory on Windows
On Tuesday, July 14, 2009, Alvaro Herrera alvhe...@commandprompt.com wrote: Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Tsutomu Yamada wrote: This patch using VirtualAlloc()/VirtualFree() to avoid failing in reattach to shared memory. Since this fixes a very annoying bug present in older versions, I think this should be backpatched all the way back to 8.2. Agreed, but first we need some evidence that it actually fixes the problem. How can we acquire such evidence? Send the patch to the people who has reported trouble and see if it seems gone? If somebody is able to build patched Win32 packages I could point a couple of guys in the spanish list to them. I built a version which a guy is currently testing. He could reproduce the bug easily, but last i heard, the patch was looking good. Don't have the details here tho. -- Dave Page EnterpriseDB UK: 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] [PATCH] could not reattach to shared memory on Windows
Jaime Casanova wrote: - identify some people with the problem and talk to them for: 1) get a way to reproduce the error (a lot dificult, IIRC we try a few times i fail to fail) or 2) get their support for test For back-patching, we'd be maybe even more interested in getting people who *don't* experience the problem to test it, to make sure it doesn't break installations that work without it. -- 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] Alpha release process
On 7/13/09 12:49 PM, Bruce Momjian wrote: Peter Eisentraut wrote: * Release notes [Note: We'll have to work out exactly how to do this one as we go.] I am not planning to assist with this item for alpha releases. Anyone want to volunteer to organize the alpha release notes? This seems like a golden opportuntity to shorten the release note process for the final release. I'd see Alpha release notes looking like: IN THIS ALPHA ... things added since last alpha ... IN 8.5 ... everything else, cumulative ... Also, what about producing Docs? I suppose we have the developer docs ... -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Deferrable unique constraints
On Tue, Jul 14, 2009 at 09:56:48AM -0700, Jeff Davis wrote: On Sun, 2009-07-12 at 14:14 +0100, Dean Rasheed wrote: Here is an updated version of this patch which should apply to HEAD, with updated docs, regression tests, pg_dump and psql \d. It works well for small numbers of temporary uniqueness violations, and at least as well (in fact about twice as fast) as deferred FK checks for large numbers of deferred checks. I took a brief look at this. You're extending the index AM, and that might not be necessary. It might be fine, but usually there is a lot of discussion around the changing of important APIs, so it might be worth looking at alternatives. With the patch I'm working on for generalized index constraints, there would be no need to extend the index AM. However, I don't expect my mechanism to replace the existing unique btree constraints, because I would expect the existing unique constraints to be faster (I haven't tested yet, though). Perhaps we could instead use the TRY/CATCH mechanism. It's generally difficult to figure out from the code exactly what happened, but in this case we have the error code ERRCODE_UNIQUE_VIOLATION. So, just check for that error code rather than passing back a boolean. You might want to change the signature of _bt_check_unique() so that it doesn't have to raise the error inside, and you can raise the error from _bt_doinsert(). The only problem there is telling the btree AM whether or not to do the insert or not (i.e. fake versus real insert). Perhaps you can just do that with careful use of a global variable? Sure, all of this is a little ugly, but we've already acknowledged that there is some ugliness around the existing unique constraint and the btree code that supports it (for one, the btree AM accesses the heap). I am looking at adding unique support to hash indexes for 8.5 and they will definitely need to visit the heap. Regards, Ken I propose trying to improve performance and scalability for large numbers of deferred checks in a separate patch. Would it be possible to just check how long the list of potential conflicts is growing, and if it gets to big, just replace them all with a bulk check event? 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] Synch Rep for CommitFest 2009-07
Fujii Masao wrote: On Fri, Jul 3, 2009 at 1:32 PM, Fujii Masaomasao.fu...@gmail.com wrote: This patch no longer applies cleanly. Can you rebase and resubmit it for the upcoming CommitFest? It might also be good to go through and clean up the various places where you have trailing whitespace and/or spaces preceding tabs. Sure. I'll resubmit the patch after fixing some bugs and finishing the documents. Here is the updated version of Synch Rep patch. I adjusted the patch against CVS HEAD, fixed some bugs and updated the documents. The attached tarball contains some patches which were split to be reviewed easily. Description of each patches, a brief procedure to set up Synch Rep and the functional overview of it are in wiki. http://wiki.postgresql.org/wiki/NTT's_Development_Projects If you notice anything, please feel free to comment! Here's one little thing in addition to all the stuff already discussed: The only caller that doesn't pass XLogSyncReplication as the new 'mode' argument to XLogFlush is this CreateCheckPoint: *** *** 6569,6575 XLOG_CHECKPOINT_ONLINE, rdata); ! XLogFlush(recptr); /* * We mustn't write any new WAL after a shutdown checkpoint, or it will --- 7667,7677 XLOG_CHECKPOINT_ONLINE, rdata); ! /* !* Don't shutdown until all outstanding xlog records are replicated and !* fsynced on the standby, regardless of synchronization mode. !*/ ! XLogFlush(recptr, shutdown ? REPLICATION_MODE_FSYNC : XLogSyncReplication); /* * We mustn't write any new WAL after a shutdown checkpoint, or it will If that's the only such caller, let's introduce a new function for that and keep the XLogFlush() api unchanged. -- 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] WIP: Deferrable unique constraints
Jeff Davis wrote: The only problem there is telling the btree AM whether or not to do the insert or not (i.e. fake versus real insert). Perhaps you can just do that with careful use of a global variable? Sure, all of this is a little ugly, but we've already acknowledged that there is some ugliness around the existing unique constraint and the btree code that supports it (for one, the btree AM accesses the heap). My 2c on this issue: if this is ugly (and it is) and needs revisiting to extend it, please by all means let's make it not ugly instead of moving the ugliness around. I didn't read the original proposal in detail so IMBFOS, but it doesn't seem like using our existing deferred constraints to handle uniqueness checks unuglifies this code enough ... For example I think we'd like to support stuff like UPDATE ... SET a = -a where the table is large. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] large object does not exist after pg_migrator
Jamie Fox wrote: Here's what I have found that got broken during pg_migrate: In two side by side databases (an 8.3.7 copy and 8.4.0 migrated with pg_migrator) the pg_largeobject table has the same number of rows. However, in the 8.4 database any select for an loid in pg_largeobject returns zero rows. If I select all loids to a file, and compare to select all loids from 8.3.7 they're the same. When I select != an loid it seems to exclude the one and return the rest, but all other comparisons , or = return zero rows. Or I'm completely batty. Dereferencing via lo_open of blob_data (an oid) in other tables fails in the 8.4 database with 'large object id does not exist'. Oh, so maybe it's pg_largeobject's index that's borked ... Did you try reindexing it? How are we transferring pg_largeobject, and are we transferring its index too? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] large object does not exist after pg_migrator
On Mon, Jul 13, 2009 at 8:03 PM, Bruce Momjian br...@momjian.us wrote: Alvaro Herrera wrote: Bruce Momjian wrote: Alvaro Herrera wrote: Bruce Momjian wrote: Jamie Fox wrote: I can also see that the pg_largeobject table is different, in the pg_restore version the Rows (estimated) is 316286 and Rows (counted) is the same, in the pg_migrator version the Rows (counted) is only 180507. Wow, I didn't test large objects specifically, and I am confused why there would be a count discrepancy. I will need to do some research unless someone else can guess about the cause. Maybe pg_largeobject is not getting frozen? That would explain the change in count, but I thought we froze _everything_, and had to. After a quick chat with Bruce it was determined that we don't freeze anything (it would be horrid for downtime if we did so in pg_migrator; and it would be useless if ran anywhere else). What we do is migrate pg_clog from the old cluster to the new. So never mind that hypothesis. Bruce noticed that the pg_dump/pg_migrator combo is failing to restore pg_largeobject's relfrozenxid. We're not sure how this is causing the errors Jamie is seeing, because what I think should happen is that scans of the table should fail with failures to open pg_clog files such-and-such, but not missing tuples ... Jamie, is it possible for you to apply the attached patch to the 8.4 server, install the new pg_dump, and run the test again to see if pg_largeobject is fixed? This patch properly sets the relfrozenxid in the system tables for each database. Sorry for the confusion, an addendum meant to be helpful fell out of this thread during the move from -general. I will try this patch now, but to be clear - the number of rows in pg_largeobject changed after I ran vacuumlo (that eventually failed). Here's what I have found that got broken during pg_migrate: In two side by side databases (an 8.3.7 copy and 8.4.0 migrated with pg_migrator) the pg_largeobject table has the same number of rows. However, in the 8.4 database any select for an loid in pg_largeobject returns zero rows. If I select all loids to a file, and compare to select all loids from 8.3.7 they're the same. When I select != an loid it seems to exclude the one and return the rest, but all other comparisons , or = return zero rows. Or I'm completely batty. Dereferencing via lo_open of blob_data (an oid) in other tables fails in the 8.4 database with 'large object id does not exist'. Thanks again, Jamie
Re: [HACKERS] WIP: Deferrable unique constraints
On Tue, 2009-07-14 at 13:29 -0500, Kenneth Marshall wrote: I am looking at adding unique support to hash indexes for 8.5 and they will definitely need to visit the heap. Have you seen this patch? http://archives.postgresql.org/message-id/1246840119.19547.126.ca...@jdavis This patch will support unique constraints for hash indexes as well. There may still be a use-case for specialized hash index unique constraints, similar to btree, but please follow the work to make sure that no work is wasted. Also, I don't see a problem with using the same hacks in the hash index code as is used in the btree index code. If you see a better way, or if you think index AM changes would be useful to you as well, you should probably open that discussion. I was trying to provide an alternative to an index AM API change, because I thought there might be some resistance to that. However, if there are multiple index AMs that can make use of it, there is a stronger case for an API change. 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
[HACKERS] Filtering dictionaries support and unaccent dictionary
Hi there, we'd like to introduce filtering dictionaries support for text search and new contrib module unaccent, which provides useful example of filtering dictionary. It finally solves the known problem of incorrect generation of headlines of text with accents. Also, this module provides unaccent() functions, which is a simple wrapper on unaccent dictionary. Regards, Oleg PS. I hope it's not late for July commitfest ! _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 unaccent.gz Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [pgsql-www] Launching commitfest.postgresql.org
On Jul 14, 2009, at 12:21 PM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: Brendan Jurd wrote: 2009/7/14 Robert Haas robertmh...@gmail.com: +1 for redirecting the whole site. I don't think the extra CPU load of SSL is going to bother anyone for the amount of traffic we're likely to have on that site. Simplicity is good. +1 for SSL on all pages from me also. yeah fine with me - who is going to do this? If you have time, that would be great; if not I will do 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] WIP: Deferrable unique constraints
On Tue, 2009-07-14 at 15:00 -0400, Alvaro Herrera wrote: My 2c on this issue: if this is ugly (and it is) and needs revisiting to extend it, please by all means let's make it not ugly instead of moving the ugliness around. I didn't read the original proposal in detail so IMBFOS, but it doesn't seem like using our existing deferred constraints to handle uniqueness checks unuglifies this code enough ... For example I think we'd like to support stuff like UPDATE ... SET a = -a where the table is large. I don't entirely understand what you're suggesting. 1. Are you saying that an AM API change is the best route? If so, we should probably start a discussion along those lines. Heikki is already changing the API for index-only scans, and Dean's API change proposal may be useful for Kenneth's unique hash indexes. You might as well all attack the current API in unison ;) 2. Even if we allow some kind of bulk constraint check to optimize the set a = a + 1 case, we should still allow some much cheaper mechanism to defer retail constraint violations. For that, why not make use of the existing constraint trigger mechanism? 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] Merge Append Patch merged up to 85devel
I've added this to the July commitfest. Gregory Stark wrote: Here's a copy of the merge-append patch that I sent months ago merged up to head. I haven't really added any additional functionality since then. Heikki suggested I separate the Append and MergeAppend nodes into two executor nodes. I had that half done in my tree but looking it over it leads to a lot of duplicated code and a strange effect that there's on Path node but two Executor nodes which seems strange. I'm not sure which way to go here but at least for now I'm leaving it this way since it's less code to write. If we want it the other way to commit then I'll do it. The other pending question is the same I had back when I originally submitted it. I don't really understand what's going on with eclasses and what invariants we're aiming to maintain with them. I don't see a problem tossing all the child relation attributes into the same eclass even though they're not strictly speaking equivalent. No join above the append path is going to see the child attributes anyways. But that might be shortsighted as I'm not really sure what the consequences are and what other uses we have envisioned for eclasses in the future. -- 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] WIP: Deferrable unique constraints
Jeff Davis wrote: 1. Are you saying that an AM API change is the best route? If so, we should probably start a discussion along those lines. Heikki is already changing the API for index-only scans, and Dean's API change proposal may be useful for Kenneth's unique hash indexes. You might as well all attack the current API in unison ;) Yeah, I don't think there's any point in keeping the API stable just for the sake of keeping it stable. I mean surely we don't want to break it for no reason, but if we can clean up the uniqueness check situation somehow by breaking the API, for all means let's explore that ... (I don't think anybody likes the way btree currently abuses the heap API; it's just that it's so damn fast to do it that way. Certainly we don't want to make it slower!) 2. Even if we allow some kind of bulk constraint check to optimize the set a = a + 1 case, we should still allow some much cheaper mechanism to defer retail constraint violations. For that, why not make use of the existing constraint trigger mechanism? Sure, perhaps you're right, which is why I added the disclaimer that I hadn't actually read the patch in any detail ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] more than one index in a single heap pass?
I was just wondering idly today if we could usefully build a number of indexes at the same time in a single pass over the heap, or could it be that we wouldn't gain much? I haven't even got around to thinking about any syntax for it. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Deferrable unique constraints
2009/7/14 Alvaro Herrera alvhe...@commandprompt.com: Jeff Davis wrote: The only problem there is telling the btree AM whether or not to do the insert or not (i.e. fake versus real insert). Perhaps you can just do that with careful use of a global variable? Sure, all of this is a little ugly, but we've already acknowledged that there is some ugliness around the existing unique constraint and the btree code that supports it (for one, the btree AM accesses the heap). Well the ugliness referred to here (btree accessing the heap) seems like a necessary evil. I don't think I want to add to it by introducing global variables. My 2c on this issue: if this is ugly (and it is) and needs revisiting to extend it, please by all means let's make it not ugly instead of moving the ugliness around. I didn't read the original proposal in detail so IMBFOS, but it doesn't seem like using our existing deferred constraints to handle uniqueness checks unuglifies this code enough ... For example I think we'd like to support stuff like UPDATE ... SET a = -a where the table is large. This patch works OK for around 1M rows. 10M is a real stretch (for me it took around 1.7GB of backend memory). Any larger than that is not going to be feasible. There is a separate TODO item to tackle this scalability limit for deferred triggers, and I'd like to tackle that in a separate patch. I think more discussion needs to be had on ways to fix this (and hopefully unuglify that code in the process). ITSM that it is not simply a matter of spooling the current queues to disk. There is code in there which scans whole queues shuffling things around. So perhaps a queue per trigger would help optimise this, allowing us to move a whole queue cheaply, or drop it in favour of a bulk check. I've not thought it through much more than that so far. - Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] more than one index in a single heap pass?
Andrew Dunstan wrote: I was just wondering idly today if we could usefully build a number of indexes at the same time in a single pass over the heap, or could it be that we wouldn't gain much? I haven't even got around to thinking about any syntax for it. Could we make it work on two backends building one index each in synchronized scans? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Deferrable unique constraints
On Tue, 2009-07-14 at 20:32 +0100, Dean Rasheed wrote: Well the ugliness referred to here (btree accessing the heap) seems like a necessary evil. I don't think I want to add to it by introducing global variables. Ok, try to coordinate with Kenneth to make sure that the API change satisfies deferrable uniques for both btree and hash indexes. I don't have a strong opinion one way or another about the API change. 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] more than one index in a single heap pass?
Alvaro Herrera alvhe...@commandprompt.com writes: Andrew Dunstan wrote: I was just wondering idly today if we could usefully build a number of indexes at the same time in a single pass over the heap, or could it be that we wouldn't gain much? I haven't even got around to thinking about any syntax for it. Could we make it work on two backends building one index each in synchronized scans? Don't we more or less have that already? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] large object does not exist after pg_migrator
Here's what I have found that got broken during pg_migrate: In two side by side databases (an 8.3.7 copy and 8.4.0 migrated with pg_migrator) the pg_largeobject table has the same number of rows. However, in the 8.4 database any select for an loid in pg_largeobject returns zero rows. If I select all loids to a file, and compare to select all loids from 8.3.7 they're the same. When I select != an loid it seems to exclude the one and return the rest, but all other comparisons , or = return zero rows. Or I'm completely batty. Dereferencing via lo_open of blob_data (an oid) in other tables fails in the 8.4 database with 'large object id does not exist'. Oh, so maybe it's pg_largeobject's index that's borked ... Did you try reindexing it? How are we transferring pg_largeobject, and are we transferring its index too? Hi - REINDEX INDEX pg_largeobject_loid_pn_index; This seems to have fixed the problem, lo_open of lob data is working again - now to see how vacuumlo likes it. Thanks, Jamie
[HACKERS] improvements for dict_xsyn extended synonym dictionary
Greetings,
attached is a simple patch that extends the functionality of dict_xsyn
extended synonym dictionary (from contrib) by adding the following
configuration option:
- mode option controls the current dictionary mode of operation. Can be one
of:
- in simple mode it accepts the original word and returns all synonyms
as ORed lis.
- when mode is symmetric, the dictionary accepts the original word or
any of its synonyms, and return all others as ORed list.
- in map regime it accepts any synonym and returns the original word
instead of it. Also, it accepts and returns the original word
itself, even if keeporig is false.
Default for this option is simple to keep compatibility with original
version.
Quick example:
cat $SHAREDIR/tsearch_data/my_rules.syn
word syn1 syn2 syn3
mydb# ALTER TEXT SEARCH DICTIONARY xsyn (RULES='my_rules', KEEPORIG=false,
MODE='simple');
ALTER TEXT SEARCH DICTIONARY
mydb=# SELECT ts_lexize('xsyn', 'word');
ts_lexize
---
{syn1,syn2,syn3}
mydb# ALTER TEXT SEARCH DICTIONARY xsyn (RULES='my_rules', KEEPORIG=true,
MODE='simple');
ALTER TEXT SEARCH DICTIONARY
mydb=# SELECT ts_lexize('xsyn', 'word');
ts_lexize
---
{word,syn1,syn2,syn3}
mydb# ALTER TEXT SEARCH DICTIONARY xsyn (RULES='my_rules', KEEPORIG=false,
MODE='symmetric');
ALTER TEXT SEARCH DICTIONARY
mydb=# SELECT ts_lexize('xsyn', 'syn1');
ts_lexize
---
{word,syn2,syn3}
mydb# ALTER TEXT SEARCH DICTIONARY xsyn (RULES='my_rules', KEEPORIG=false,
MODE='map');
ALTER TEXT SEARCH DICTIONARY
mydb=# SELECT ts_lexize('xsyn', 'syn1');
ts_lexize
---
{word}
Thanks for your attention.
Sergey Karpov.
dict_xsyn_extended.diff.gz
Description: Binary data
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] navigation menu for documents
For a very long time I have thought that it would be useful to have some sort of navigation menu for the docs similar to what you get with doxygen. I had a brief look at it today but I am lost for a place to start with it. Does anyone sufficiently understand how the docs work to be able to say how one might achieve such a thing? Navigating the docs requires far too much use of the back button and up links, IMNSHO. A menu frame would make them far more usable. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Alpha release process
On Tuesday 14 July 2009 21:20:51 Josh Berkus wrote: Also, what about producing Docs? I'm working on making the documentation build part of the normal release building rules. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Alpha release process
On Tuesday 14 July 2009 21:17:14 Josh Berkus wrote: == Announcing == Announce to: * PostgreSQL News * pgsql-hackers * pgsql-general(?) * pgsql-packagers Also pgsql-announce, no? Yes. -- Sent 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: Deferrable unique constraints
On Tue, Jul 14, 2009 at 12:13:33PM -0700, Jeff Davis wrote: On Tue, 2009-07-14 at 13:29 -0500, Kenneth Marshall wrote: I am looking at adding unique support to hash indexes for 8.5 and they will definitely need to visit the heap. Have you seen this patch? http://archives.postgresql.org/message-id/1246840119.19547.126.ca...@jdavis This patch will support unique constraints for hash indexes as well. There may still be a use-case for specialized hash index unique constraints, similar to btree, but please follow the work to make sure that no work is wasted. Also, I don't see a problem with using the same hacks in the hash index code as is used in the btree index code. If you see a better way, or if you think index AM changes would be useful to you as well, you should probably open that discussion. I was trying to provide an alternative to an index AM API change, because I thought there might be some resistance to that. However, if there are multiple index AMs that can make use of it, there is a stronger case for an API change. Regards, Jeff Davis I will take a look at that patch. My thought was to use the same process as the btree support for unique indexes since it has been well tested and optimized. Thanks, Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] DefaultACLs
Hello, this is first public version of our DefaultACLs patch as described on http://wiki.postgresql.org/wiki/DefaultACL . It allows GRANT/REVOKE permissions to be inherited by objects based on schema permissions at create type by use of ALTER SCHEMA foo SET DEFAULT PRIVILEGES ON TABLE SELECT TO bar syntax. There is also ADD and DROP for appending and removing those default privileges. It works for tables, views, sequences and functions. More info about syntax and some previous discussion is on wiki. There is also GRANT DEFAULT PRIVILEGES ON tablename which *replaces* current object privileges with the default ones. Only owner can do both of those commands (ALTER SCHEMA can be done only by schema owner and GRANT can be done only by object owner). It adds new catalog table which stores the default permissions for given schema and object type. We didn't add syscache entry for that as Stephen Frost didn't feel we should do that (yet). Three functions were also exported from aclchk.c because most of the ALTER SCHEMA stuff is done in schemacmds.c. The current version is fully working and includes some regression tests. There is however no documentation at this moment. Patch is against current Git HEAD (it is context diff). -- Regards Petr Jelinek (PJMODOS) defaultacls.diff.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
Re: [HACKERS] [pgsql-www] Launching commitfest.postgresql.org
Robert Haas wrote: On Jul 14, 2009, at 12:21 PM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: Brendan Jurd wrote: 2009/7/14 Robert Haas robertmh...@gmail.com: +1 for redirecting the whole site. I don't think the extra CPU load of SSL is going to bother anyone for the amount of traffic we're likely to have on that site. Simplicity is good. +1 for SSL on all pages from me also. yeah fine with me - who is going to do this? If you have time, that would be great; if not I will do it. well you just volunteered... have fun :) Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] navigation menu for documents
Hi, Le 14 juil. 09 à 22:17, Andrew Dunstan a écrit : For a very long time I have thought that it would be useful to have some sort of navigation menu for the docs Oh yes, pretty please :) Navigating the docs requires far too much use of the back button and up links, IMNSHO. A menu frame would make them far more usable. No, please, no frame! Really... What I'm thinking about is to extend current breadcumb at the top of the page to include chapter, section, subsection. So that for exemple the following page: http://www.postgresql.org/docs/8.3/interactive/datatype-geometric.html#AEN5480 Would have at its top: Home → Documentation → Manuals → PostgreSQL 8.3 → Data Types → Geometric Types → Points Well the last entry, Points, I'm not so sure about. But I think you get the idea. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sampling profiler updated
Hi! Itagaki Takahiro writes: I updated Sampling profiler patch to be applied to HEAD cleanly. [...] Comments welcome. I believe the profiler could give us a better understanding of where different parts of the user visible response time originate from. The problem with DTrace in my opinion is the lack of support on certain platforms (e.g. Windows) and the need to have kernel support and root access, which might not be available to the DBA or developer. Have you thought about keeping the counters for each backend isolated? I think in the end it would be beneficial to be able to break down the response time for a critical business transaction in isolation instead of having all backends in one figure. Do you know the work of Cary Millsap at http://www.method-r.com/ who has been working on response time based tuning in Oracle? Regards, Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] navigation menu for documents
Dimitri Fontaine wrote: Hi, Le 14 juil. 09 à 22:17, Andrew Dunstan a écrit : For a very long time I have thought that it would be useful to have some sort of navigation menu for the docs Oh yes, pretty please :) Navigating the docs requires far too much use of the back button and up links, IMNSHO. A menu frame would make them far more usable. No, please, no frame! Really... Yes, really. What you suggest here is just not adequate, IMNSHO. I don't want to have to scroll to the top or bottom of the page to get navigation, and I want to be able to see the navigation and go where I want directly. I know some people have a violent aversion to frames, but I don't. They have legitimate uses. What I'm thinking about is to extend current breadcumb at the top of the page to include chapter, section, subsection. So that for exemple the following page: http://www.postgresql.org/docs/8.3/interactive/datatype-geometric.html#AEN5480 Would have at its top: Home → Documentation → Manuals → PostgreSQL 8.3 → Data Types → Geometric Types → Points Well the last entry, Points, I'm not so sure about. But I think you get the idea. Compared with what things like doxygen or webhelp give you it's not remotely what I want. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Make planning via GEQO repeatable
Hi, Query planning via GEQO currently can yield a different plan on every invokation of the planner due to its non-exhaustive nature. This often can be inconvenient because at times there may be a very bad plan. It also makes it very hard to reproduce a problem with GEQO. [PATCH 1/3] Add erand48() implementation for non-unixoid systems. I could not find any suitable PRNG with a visible/changeable state for windows. Currently random() on windows is mapped to drand48() copied out of FreeBSD. I copied erand48(state) from there as well. As I have no windows with a buildsystem available at the moment this is untested on windows! [PATCH 2/3] Support a 'geqo_seed' GUC to make planning via GEQO repeatable. This patch adds a GUC geqo_seed to control whether the PRNG should be repeatable or not. If geqo_seed = 0 a global/per-backend state is used, thus the planning is not repeatable. If set to a value in (0,1] that number is used to initialize the generator on every planning. It adds a void *join_search_private variable to PlannerInfo to hold the random number generator. This variable could also be used by a join_order plugin. Alternatively it would be possible to start passing GeqoEvalData around everywhere, but the suggestion to extend PlannerInfo came from Tom... PlannerInfo *root is now passed to all non-static geqo related functions. It seems cleaner to do this for all functions than only functions internally using the random generator. GeqoEvalData, which is sparsely used, is replaced GeqoPrivateData which is passed via join_search_private. [PATCH 3/3] Document geqo_seed variable. I will submit this to the commitfest. I guess thats OK? Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH 1/3] Add erand48() implementation for non-unixoid systems.
---
src/include/port.h |2 ++
src/port/rand.c|8
2 files changed, 10 insertions(+), 0 deletions(-)
diff --git a/src/include/port.h b/src/include/port.h
index cbf9dcf..af96620 100644
*** a/src/include/port.h
--- b/src/include/port.h
*** extern FILE *pgwin32_fopen(const char *,
*** 320,325
--- 320,327
extern long lrand48(void);
extern void srand48(long seed);
+ extern long erand48(unsigned short[3]);
+
/* New versions of MingW have gettimeofday, old mingw and msvc don't */
#ifndef HAVE_GETTIMEOFDAY
/* Last parameter not used */
diff --git a/src/port/rand.c b/src/port/rand.c
index 6c14a64..6904bb1 100644
*** a/src/port/rand.c
--- b/src/port/rand.c
*** srand48(long seed)
*** 80,82
--- 80,90
_rand48_mult[2] = RAND48_MULT_2;
_rand48_add = RAND48_ADD;
}
+
+ double erand48(unsigned short xseed[3])
+ {
+ _dorand48(xseed);
+ return ldexp((double) xseed[0], -48) +
+ ldexp((double) xseed[1], -32) +
+ ldexp((double) xseed[2], -16);
+ }
--
1.6.3.3.335.ge09a8
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH 3/3] Document geqo_seed variable.
--- doc/src/sgml/config.sgml | 16 1 files changed, 16 insertions(+), 0 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 99d25d7..5d8eca9 100644 *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *** archive_command = 'copy %p C:\\server *** 2150,2155 --- 2150,2171 /listitem /varlistentry + varlistentry id=guc-geqo-seed xreflabel=geqo_seed + termvarnamegeqo_seed/varname + (typefloating point/type)/term + indexterm +primaryvarnamegeqo_seed/ configuration parameter/primary + /indexterm + listitem +para + Controls the initialization of the random number generator used + by GEQO to select random paths through the join order search space. + With the default setting of zero the join order planning is not repeatable. + For repeatable planning set a value between 0 (exclusive) and 1 (inclusive). +/para + /listitem + /varlistentry + /variablelist /sect2 sect2 id=runtime-config-query-other -- 1.6.3.3.335.ge09a8 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH 2/3] Support a 'geqo_seed' GUC to make planning via GEQO repeatable.
---
src/backend/optimizer/geqo/Makefile |1 +
src/backend/optimizer/geqo/geqo_copy.c |4 +-
src/backend/optimizer/geqo/geqo_cx.c|6 +-
src/backend/optimizer/geqo/geqo_erx.c | 47 ++--
src/backend/optimizer/geqo/geqo_eval.c | 28
src/backend/optimizer/geqo/geqo_main.c | 90 ---
src/backend/optimizer/geqo/geqo_mutation.c | 10 +-
src/backend/optimizer/geqo/geqo_ox1.c |8 +-
src/backend/optimizer/geqo/geqo_ox2.c |7 +-
src/backend/optimizer/geqo/geqo_pmx.c |7 +-
src/backend/optimizer/geqo/geqo_pool.c | 23 +++---
src/backend/optimizer/geqo/geqo_px.c|8 +-
src/backend/optimizer/geqo/geqo_random.c| 42 +++
src/backend/optimizer/geqo/geqo_recombination.c |9 +-
src/backend/optimizer/geqo/geqo_selection.c | 19 +++--
src/backend/optimizer/path/allpaths.c |2 +
src/backend/utils/misc/guc.c|9 ++
src/include/nodes/relation.h|3 +
src/include/optimizer/geqo.h| 17 ++---
src/include/optimizer/geqo_copy.h |3 +-
src/include/optimizer/geqo_mutation.h |3 +-
src/include/optimizer/geqo_pool.h | 14 ++--
src/include/optimizer/geqo_random.h |9 +-
src/include/optimizer/geqo_recombination.h | 33 +---
src/include/optimizer/geqo_selection.h |4 +-
25 files changed, 244 insertions(+), 162 deletions(-)
create mode 100644 src/backend/optimizer/geqo/geqo_random.c
diff --git a/src/backend/optimizer/geqo/Makefile
b/src/backend/optimizer/geqo/Makefile
index dbc6c28..e5a01d7 100644
*** a/src/backend/optimizer/geqo/Makefile
--- b/src/backend/optimizer/geqo/Makefile
*** top_builddir = ../../../..
*** 14,19
--- 14,20
include $(top_builddir)/src/Makefile.global
OBJS =geqo_copy.o geqo_eval.o geqo_main.o geqo_misc.o \
+ geqo_random.o \
geqo_mutation.o geqo_pool.o geqo_recombination.o \
geqo_selection.o \
geqo_erx.o geqo_pmx.o geqo_cx.o geqo_px.o geqo_ox1.o geqo_ox2.o
diff --git a/src/backend/optimizer/geqo/geqo_copy.c
b/src/backend/optimizer/geqo/geqo_copy.c
index 83af33a..373a924 100644
*** a/src/backend/optimizer/geqo/geqo_copy.c
--- b/src/backend/optimizer/geqo/geqo_copy.c
***
*** 35,40
--- 35,41
#include postgres.h
#include optimizer/geqo_copy.h
+ #include optimizer/geqo_copy.h
/* geqo_copy
*
***
*** 42,48
*
*/
void
! geqo_copy(Chromosome *chromo1, Chromosome *chromo2, int string_length)
{
int i;
--- 43,50
*
*/
void
! geqo_copy(PlannerInfo *root, Chromosome *chromo1, Chromosome *chromo2,
! int string_length)
{
int i;
diff --git a/src/backend/optimizer/geqo/geqo_cx.c
b/src/backend/optimizer/geqo/geqo_cx.c
index 3d5102f..ad861ce 100644
*** a/src/backend/optimizer/geqo/geqo_cx.c
--- b/src/backend/optimizer/geqo/geqo_cx.c
***
*** 35,40
--- 35,41
#include postgres.h
+ #include optimizer/geqo.h
#include optimizer/geqo_recombination.h
#include optimizer/geqo_random.h
***
*** 44,50
* cycle crossover
*/
int
! cx(Gene *tour1, Gene *tour2, Gene *offspring, int num_gene, City *city_table)
{
int i,
--- 45,52
* cycle crossover
*/
int
! cx(PlannerInfo *root, Gene *tour1, Gene *tour2, Gene *offspring,
!int num_gene, City *city_table)
{
int i,
*** cx(Gene *tour1, Gene *tour2, Gene *offsp
*** 62,68
}
/* choose random cycle starting position */
! start_pos = geqo_randint(num_gene - 1, 0);
/* child inherits first city */
offspring[start_pos] = tour1[start_pos];
--- 64,70
}
/* choose random cycle starting position */
! start_pos = geqo_randint(root, num_gene - 1, 0);
/* child inherits first city */
offspring[start_pos] = tour1[start_pos];
diff --git a/src/backend/optimizer/geqo/geqo_erx.c
b/src/backend/optimizer/geqo/geqo_erx.c
index 35e1a28..5bae059 100644
*** a/src/backend/optimizer/geqo/geqo_erx.c
--- b/src/backend/optimizer/geqo/geqo_erx.c
***
*** 36,46
#include optimizer/geqo_random.h
! static intgimme_edge(Gene gene1, Gene gene2, Edge *edge_table);
! static void remove_gene(Gene gene, Edge edge, Edge *edge_table);
! static Gene gimme_gene(Edge edge, Edge *edge_table);
! static Gene edge_failure(Gene *gene, int index, Edge *edge_table, int
num_gene);
/* alloc_edge_table
--- 36,46
#include optimizer/geqo_random.h
! static intgimme_edge(PlannerInfo *root, Gene gene1, Gene gene2, Edge
*edge_table);
! static void
Re: [HACKERS] navigation menu for documents
Le 15 juil. 09 à 00:21, Andrew Dunstan a écrit : Yes, really. What you suggest here is just not adequate, IMNSHO. I don't want to have to scroll to the top or bottom of the page to get navigation, and I want to be able to see the navigation and go where I want directly. Ok we don't share the same needs here, and I get what you're after. I know some people have a violent aversion to frames, but I don't. They have legitimate uses. Agreed, but even when they do, I find I'm not shortening the time needed to find my link. Here the navigation frame will either contain too much entries to be useful (you'll need to scroll around), or will have some expand/collapse tree that will make you click way to much to be proficient in your search. IMO. Compared with what things like doxygen or webhelp give you it's not remotely what I want. Ok. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] navigation menu for documents
Andrew Dunstan wrote: Yes, really. What you suggest here is just not adequate, IMNSHO. I don't want to have to scroll to the top or bottom of the page to get navigation, and I want to be able to see the navigation and go where I want directly. Are you talking about the online manuals, or something else here? -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] more than one index in a single heap pass?
On Tue, Jul 14, 2009 at 8:50 PM, Tom Lanet...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Andrew Dunstan wrote: I was just wondering idly today if we could usefully build a number of indexes at the same time in a single pass over the heap, or could it be that we wouldn't gain much? I haven't even got around to thinking about any syntax for it. Could we make it work on two backends building one index each in synchronized scans? Don't we more or less have that already? Wasn't that a big part of the point of the parallel pg_restore feature? -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] navigation menu for documents
Richard Huxton wrote: Andrew Dunstan wrote: Yes, really. What you suggest here is just not adequate, IMNSHO. I don't want to have to scroll to the top or bottom of the page to get navigation, and I want to be able to see the navigation and go where I want directly. Are you talking about the online manuals, or something else here? I don't care if we don't provide this for the online manuals on postgresql.org - I'm quite happy to install it on my own server if necessary. But I am talking about the HTML docs that come from our /doc directory. And I bet if we had the option of better navigation, our online users would want us to provide it. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] navigation menu for documents
Andrew Dunstan wrote: Richard Huxton wrote: Andrew Dunstan wrote: Yes, really. What you suggest here is just not adequate, IMNSHO. I don't want to have to scroll to the top or bottom of the page to get navigation, and I want to be able to see the navigation and go where I want directly. Are you talking about the online manuals, or something else here? I don't care if we don't provide this for the online manuals on postgresql.org - I'm quite happy to install it on my own server if necessary. But I am talking about the HTML docs that come from our /doc directory. And I bet if we had the option of better navigation, our online users would want us to provide it. Shouldn't be too hard to come up with something reasonable with a little css. Something only activated if javascript is turned on or some such. Give me 48 hours and I'll have a play. -- Richard Huxton Archonet Ltd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] more than one index in a single heap pass?
Greg Stark wrote: On Tue, Jul 14, 2009 at 8:50 PM, Tom Lanet...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Andrew Dunstan wrote: I was just wondering idly today if we could usefully build a number of indexes at the same time in a single pass over the heap, or could it be that we wouldn't gain much? I haven't even got around to thinking about any syntax for it. Could we make it work on two backends building one index each in synchronized scans? Don't we more or less have that already? Wasn't that a big part of the point of the parallel pg_restore feature? Well, yes, it's some of it, and in theory Tom's late addition of a queue that gets all the dependencies of a table as soon as the table data is restored should make that work better. But of course, that's not the only time indexes are created, and each index creation command will be doing its own heap processing, albeit that synchronised scanning will make that lots cheaper. As I said originally, it was just an idle thought that came to me today. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CommitFest 2009-07 is Now Closed
The time is now Wed Jul 15 00:18:00 UTC 2009 and CommitFest 2009-07 is now closed. Please submit all new patches at: http://commitfest.postgresql.org/action/commitfest_view/open Reviewing assignments will be sent to pgsql-rrreviewers in the next few hours. I believe everyone who has volunteered to review is now a member of that mailing list; if you want to review and don't get a reviewing assignment by 04:00:00, please email me. If there are any stragglers out there who are still interesting in reviewing, it's not too late to speak up. There are still more patches than there are reviewers, and obviously it would be much better if it were the other way around. I will be asking the reviewers to please complete their initial review within 4 days, meaning by end of day on Saturday. I am sure that not everyone will be able hit that deadline, but it's good to have something to aim for, and the more people hit it the better our chances of getting a lot of good stuff done during this CommitFest. Once the initial round of reviews is complete, I will reassign all reviewers who still have bandwidth to new patches; I'm hoping that we'll eventually be able to get a couple of people helping with each of the big ones, but that won't be the case to start out, because I think it's important to make sure that everyone gets at least some feedback. ...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] Index-only scans
Heikki Linnakangas wrote: ... CREATE TABLE manytomany (aid integer, bid integer); CREATE INDEX a_b ON manytomany (aid, bid); CREATE INDEX b_a ON manytomany (bid, aid); ... new and interesting indexing strategies. Covered indexes are also one kind of materialized view. It may be better to implement mat views and gain wider benefits too. Materialized view sure would be nice, but doesn't address quite the same use cases. Doesn't help with the many-to-many example above, for example. We should have both. Really? I'd have thought that index is similar to materializing these views: create view a_b as select aid,bid from manytomany order by aid,bid; create view b_a as select bid,aid from manytomany order by bid,aid; Or perhaps create view a_b as select aid,array_agg(bid) from manytomany group by aid; But I like the index-only scan better anyway because I already have the indexes so the benefit would come to me automatically rather than having to pick and choose what views to materialize. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] more than one index in a single heap pass?
Andrew Dunstan wrote: Well, yes, it's some of it, and in theory Tom's late addition of a queue that gets all the dependencies of a table as soon as the table data is restored should make that work better. But of course, that's not the only time indexes are created, and each index creation command will be doing its own heap processing, albeit that synchronised scanning will make that lots cheaper. As I said originally, it was just an idle thought that came to me today. Sounds to me like another reason to separate index definition from creation. If an index can be defined but not yet created or valid, then you could imagine syntax like: DEFINE INDEX blahblah1 ON mytable (some fields); DEFINE INDEX blahblah2 ON mytable (some other fields); [RE]INDEX TABLE mytable; ...provided that REINDEX TABLE could recreate all indexes simultaneously as you suggest. -Glen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH 1/2 v3] [libpq] rework sigpipe-handling macros
Hi, Alvaro, Does this work in compilers other than GCC? I think we use some kludges to protect against them ... see pg_list.h for the canonical example. As I understand it, we're not using static inlines in pg_list.h to prevent multiple objects from exporting the same symbols if the functions don't end up as 'static inline' (ie, because the compiler doesn't support that). In this case, we're only compiling the inlines into a single object, so even if the compiler doesn't support inlines, we'll just end up with out-of-line function calls, which should work too. However, this is only my assumption about those compilers (I don't have access to other compilers to test); happy to fix these up if the inlines won't work. Cheers, Jeremy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Index-only scans
On Wed, Jul 15, 2009 at 1:21 AM, Ron Mayerrm...@cheapcomplexdevices.com wrote: Really? I'd have thought that index is similar to materializing these views: create view a_b as select aid,bid from manytomany order by aid,bid; create view b_a as select bid,aid from manytomany order by bid,aid; Or perhaps create view a_b as select aid,array_agg(bid) from manytomany group by aid; How do any of these views help you answer a query like select aid from manytomany where bid in (subquery)? The last one could help you answer the dual of that but not without rewriting the query quite heavily to use array operations. The first two I'm puzzled how they're useful at all since unless you add indexes to the materialized views they'll just contain a complete copy of the original table -- the most they could help with is avoiding a sort but they'll never be updatable so they'll rarely actually be usable. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CommitFest 2009-07 is Now Closed
On Tue, Jul 14, 2009 at 8:55 PM, Tatsuo Ishiiis...@postgresql.org wrote: Tsutomu was asking to add his patches to July commit festa: http://archives.postgresql.org/pgsql-hackers/2009-07/msg00782.php But it does not seem all subsequent replies agree that. What shall he do? Just waits for next commitfest? Adding a patch to the CommitFest doesn't require permission. It's just asking that the patch be reviewed, not guaranteeing anything. Patch authors are supposed to add patches to the commitfest site themselves, but I will add this one. ...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] CommitFest 2009-07 is Now Closed
Tsutomu was asking to add his patches to July commit festa: http://archives.postgresql.org/pgsql-hackers/2009-07/msg00782.php But it does not seem all subsequent replies agree that. What shall he do? Just waits for next commitfest? Adding a patch to the CommitFest doesn't require permission. It's just asking that the patch be reviewed, not guaranteeing anything. Patch authors are supposed to add patches to the commitfest site themselves, but I will add this one. Thanks! -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CommitFest 2009-07 is Now Closed
Robert, Tsutomu was asking to add his patches to July commit festa: http://archives.postgresql.org/pgsql-hackers/2009-07/msg00782.php But it does not seem all subsequent replies agree that. What shall he do? Just waits for next commit festa? -- Tatsuo Ishii SRA OSS, Inc. Japan The time is now Wed Jul 15 00:18:00 UTC 2009 and CommitFest 2009-07 is now closed. Please submit all new patches at: http://commitfest.postgresql.org/action/commitfest_view/open Reviewing assignments will be sent to pgsql-rrreviewers in the next few hours. I believe everyone who has volunteered to review is now a member of that mailing list; if you want to review and don't get a reviewing assignment by 04:00:00, please email me. If there are any stragglers out there who are still interesting in reviewing, it's not too late to speak up. There are still more patches than there are reviewers, and obviously it would be much better if it were the other way around. I will be asking the reviewers to please complete their initial review within 4 days, meaning by end of day on Saturday. I am sure that not everyone will be able hit that deadline, but it's good to have something to aim for, and the more people hit it the better our chances of getting a lot of good stuff done during this CommitFest. Once the initial round of reviews is complete, I will reassign all reviewers who still have bandwidth to new patches; I'm hoping that we'll eventually be able to get a couple of people helping with each of the big ones, but that won't be the case to start out, because I think it's important to make sure that everyone gets at least some feedback. ...Robert -- 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] navigation menu for documents
2009/7/15 Andrew Dunstan and...@dunslane.net: Dimitri Fontaine wrote: What I'm thinking about is to extend current breadcumb at the top of the page to include chapter, section, subsection. So that for exemple the following page: http://www.postgresql.org/docs/8.3/interactive/datatype-geometric.html#AEN5480 Would have at its top: Home → Documentation → Manuals → PostgreSQL 8.3 → Data Types → Geometric Types → Points Yes, really. What you suggest here is just not adequate, IMNSHO. I don't want to have to scroll to the top or bottom of the page to get navigation, and I want to be able to see the navigation and go where I want directly. Even if we are going to have some kind of tree-based navigation sidebar beastie, I would be a hearty +1 in favour of doing Dmitri's breadcrumb suggestion anyway. I think we can agree that a full breadcrumb/ancestry trail is superior to Up/Home, and unlike the sidebar nav idea doesn't require any fancy scripting shenanigans or possible browser compatibility strife. Compared with what things like doxygen or webhelp give you it's not remotely what I want. So let's have both. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] execute on log_rotation
Hi, I was thinking that could be useful to have a guc to indicate some command to execute on rotation of logs (ok, i'm not inventing warm water here, but...). it could be useful for: - load data from a csvlog to a table - execute some magic to analyze logs - archive logs (compress, move to another site, etc) - other things i haven't thinked of yes, we can create a cron for that but doing it at rotation time is good because we don't need to complicate scripts to try to find if there is some log not processed we know there is... and exactly wich one... if people i agree i will try this... after the commitfest, of course... but i ask now because my memory is a lot worst every single day... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sampling profiler updated
Stefan Moeding pg...@moeding.net wrote:
Have you thought about keeping the counters for each backend isolated?
I think in the end it would be beneficial to be able to break down the
response time for a critical business transaction in isolation instead
of having all backends in one figure.
I think per-backend profiling is confusable because one backend might be
reused by multiple jobs if you use connection pooling. If we need more
detailed profiling, it should be grouped by query variations.
I have another plan for detailed profiling by extending pg_stat_statements
for such purposes. It'll include functionalities of log_{parser|planner|
executor|statement}_stats parameters. They are log-based profiler, but a
view-based approach seems to be more easy-to-use.
Do you know the work of Cary Millsap at http://www.method-r.com/ who has
been working on response time based tuning in Oracle?
I didn't know that. What is the point of the works? Are there some
knowledge we should learn from?
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] [PATCH 3/3] Document geqo_seed variable.
On Tue, Jul 14, 2009 at 6:34 PM, Andres Freundand...@anarazel.de wrote: --- doc/src/sgml/config.sgml | 16 1 files changed, 16 insertions(+), 0 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 99d25d7..5d8eca9 100644 *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *** archive_command = 'copy %p C:\\server *** 2150,2155 --- 2150,2171 /listitem /varlistentry + varlistentry id=guc-geqo-seed xreflabel=geqo_seed + termvarnamegeqo_seed/varname + (typefloating point/type)/term + indexterm + primaryvarnamegeqo_seed/ configuration parameter/primary + /indexterm + listitem + para + Controls the initialization of the random number generator used + by GEQO to select random paths through the join order search space. + With the default setting of zero the join order planning is not repeatable. + For repeatable planning set a value between 0 (exclusive) and 1 (inclusive). + /para + /listitem + /varlistentry + /variablelist /sect2 sect2 id=runtime-config-query-other -- 1.6.3.3.335.ge09a8 I don't understand why people (including yourself, but you're not the only one) have begun submitting relatively trivial patches in multiple parts. This just creates multiple threads on the mailing list without adding any value. The doc changes are part of the patch; one email containing all the changes seems vastly preferable to me. IMHO, the only reason for submitting multiple patches if it there are pieces that are separately commitable. ...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] navigation menu for documents
Brendan Jurd escribió: 2009/7/15 Andrew Dunstan and...@dunslane.net: Dimitri Fontaine wrote: Would have at its top: Home → Documentation → Manuals → PostgreSQL 8.3 → Data Types → Geometric Types → Points Yes, really. What you suggest here is just not adequate, IMNSHO. I don't want to have to scroll to the top or bottom of the page to get navigation, and I want to be able to see the navigation and go where I want directly. Even if we are going to have some kind of tree-based navigation sidebar beastie, I would be a hearty +1 in favour of doing Dmitri's breadcrumb suggestion anyway. I would agree that the breadcrumbs are a good idea, except that I'm confused about it including the complete website navigation (instead of just the docs). IMO the list should be Home → Data Types → Geometric → Types → Points (where Home points to the top of 8.3 docs). What happened to the contest for a website redesign anyway? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH 1/2 v3] [libpq] rework sigpipe-handling macros
On Tue, Jul 14, 2009 at 8:42 PM, Jeremy Kerrj...@ozlabs.org wrote: Hi, Alvaro, Does this work in compilers other than GCC? I think we use some kludges to protect against them ... see pg_list.h for the canonical example. As I understand it, we're not using static inlines in pg_list.h to prevent multiple objects from exporting the same symbols if the functions don't end up as 'static inline' (ie, because the compiler doesn't support that). In this case, we're only compiling the inlines into a single object, so even if the compiler doesn't support inlines, we'll just end up with out-of-line function calls, which should work too. Perhaps we should use macros. ...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] pg_dump Add dumping of comments on index columns
Jaime Casanova wrote: this one looks good to me, the only adjust i made to the patch is Thank you for your review! --- Taro Minowa(Higepon) http://www.monaos.org/ http://code.google.com/p/mosh-scheme/ On Tue, Jul 14, 2009 at 4:34 PM, Jaime Casanovajcasa...@systemguards.com.ec wrote: On Thu, Mar 26, 2009 at 2:39 AM, higeponhige...@gmail.com wrote: Hi. Here is a patch for pg_dump Commenting on a composite-type column. This patch is for Todo item named Add dumping of comments on index columns and composite type columns. this one looks good to me, the only adjust i made to the patch is change the name for the function that dump the comments from the composite types columns for: dumpCompositeTypeColsComment that seems more clearer to me... the patch works just fine... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synch Rep for CommitFest 2009-07
Hi,
On Wed, Jul 15, 2009 at 3:56 AM, Heikki
Linnakangasheikki.linnakan...@enterprisedb.com wrote:
Here's one little thing in addition to all the stuff already discussed:
Thanks for the comment!
If that's the only such caller, let's introduce a new function for that
and keep the XLogFlush() api unchanged.
OK. How about the following function?
--
/*
* Ensure that shutdown-related XLOG data through the given position is
* flushed to local disk, and also flushed to the disk in the standby
* if replication is in progress.
*/
void
XLogShutdownFlush(XLogRecPtr record)
{
int save_mode = XLogSyncReplication;
XLogSyncReplication = REPLICATION_MODE_FSYNC;
XLogFlush(record);
XLogSyncReplication = save_mode;
}
--
In a shutdown checkpoint case, CreateCheckPoint calls
XLogShutdownFlush, otherwise XLogFlush. And,
XLogFlush uses XLogSyncReplication directly instead of
obsolete 'mode' argument.
If the above is OK, should I update the patch ASAP? or
suspend that update until many other comments arrive?
I'm concerned that frequent small updating interferes with
a review.
Regards,
--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Index AM API changes for deferability
I am reviewing the following patch: http://archives.postgresql.org/message-id/8e2dbb700907071138y4ebe75cw81879aa513cf8...@mail.gmail.com In order to provide useful feedback, I would like to reach a consensus on a possible index AM API change to make it easier to support deferrable constraints for index access methods that enforce the constraints themselves. I am trying to word this question carefully, because there is a lot of context: * Dean Rasheed is implementing deferrable unique constraints for BTree (in the patch linked above) * Kenneth Marshall has indicated that he would like to implement unique hash indexes in a way similar to the current btree implementation: http://archives.postgresql.org/pgsql-hackers/2009-07/msg00812.php http://archives.postgresql.org/pgsql-hackers/2009-07/msg00834.php * I have a patch up for review that implements more general constraints that are enforced outside of AM-specific code, and therefore do not require index AM changes to support deferrable constraints: http://archives.postgresql.org/pgsql-hackers/2009-07/msg00302.php The btree unique code is already a serious failure of modularity: http://archives.postgresql.org/pgsql-hackers/2008-06/msg00427.php So, assuming that we support all of these features together, we have two options that I see: 1. Extend the index AM API in a manner similar to Dean's patch. 2. Try to come up with some workaround to avoid changing the AM API I was originally leaning toward approach #2 because I saw btree as the only index AM that needed it, so extending the API seemed a little excessive. However, seeing as it's potentially useful for unique hash indexes, too, I am now leaning toward approach #1. Also, we don't have performance numbers for either my feature or a unique constraint implemented inside the hash index AM, so we don't know whether that's a big win to enforce the constraint in the AM-specific code or not. So, should we proceed assuming an index AM API change, or try to avoid it? If we should change the AM API, is Dean's API change acceptable? 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
