Re: [HACKERS] WIP: cross column correlation ...
Rod Taylor wrote: On Fri, Feb 25, 2011 at 14:26, Alvaro Herrera alvhe...@commandprompt.comwrote: Excerpts from Rod Taylor's message of vie feb 25 14:03:58 -0300 2011: How practical would it be for analyze to keep a record of response times for given sections of a table as it randomly accesses them and generate some kind of a map for expected response times for the pieces of data it is analysing? I think what you want is random_page_cost that can be tailored per tablespace. Yes, that can certainly help but does nothing to help with finding typical hot-spots or cached sections of the table and sending that information to the planner. Between Analyze random sampling and perhaps some metric during actual IO of random of queries we should be able to determine and record which pieces of data tend to be hot/in cache, or readily available and what data tends not to be. If the planner knew that the value 1 tends to have a much lower cost to fetch than any other value in the table (it is cached or otherwise readily available), it can choose a plan better suited toward that. Well, one idea I have always had is feeding things the executor finds back to the optimizer for use in planning future queries. One argument against that is that a planned query might run with different data behavior than seen by the executor in the past, but we know if the optimizer is planning something for immediate execution or later execution, so we could use executor stats only when planning for immediate execution. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: cross column correlation ...
Grzegorz Jaskiewicz wrote: On 25 Feb 2011, at 13:18, Robert Haas wrote: People coming from Oracle are not favorably impressed either by the amount of monitoring data PostgreSQL can gather or by the number of knobs that are available to fix problems when they occur. We don't need to have as many knobs as Oracle and we probably don't want to, and for that matter we probably couldn't if we did want to for lack of manpower, but that doesn't mean we should have none. Still, having more data a user can probe would be nice. I wonder why everyone avoids Microsoft's approach to the subject. Apparently, they go in the 'auto-tune as much as possible' direction. And tests we did a while ago, involving asking team from Microsoft and a team from oracle to optimise set of queries for the same set of data (bookies data, loads of it) showed that the auto-tuning Microsoft has in their sql server performed much better than a team of over-sweating oracle dba's. In my current work place/camp we have many deployments of the same system, over different types of machines, each with different customer data that vary so much that queries need to be rather generic. Postgresql shows its strength with planner doing a good job for different variants of data, however we do a very little tweaking to the configuration parameters. Just because it is just too hard to overlook all of them. I guess that the systems could behave much better, but no one is going to tweak settings for 50 different installations over 50 different type of data and 50 different sets of hardware. If there was even a tiny amount of automation provided in the postgresql, I would welcome it with open arms. I totally agree. If we add a tuning parameter that does 10x better than automatic, but only 1% of our users use it, we would be better off, overall, with the automatic tuning. See my blog post which talks about the same tradeoff when adding configuration variables: http://momjian.us/main/blogs/pgblog/2009.html#January_10_2009 -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: cross column correlation ...
Robert Haas wrote: On Sat, Feb 26, 2011 at 1:57 AM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: Actually, we *do* have some idea which tables are hot. ?Or at least, we could. ? Currently, pg_stats for tables are timeless; they just accumulate from the last reset, which has always been a problem in general for monitoring. ?If we could make top-level table and index stats time-based, even in some crude way, we would know which tables were currently hot. ?That would also have the benefit of making server performance analysis and autotuning easier. I think there would be value in giving the DBA an easier way to see which tables are hot, but I am really leery about the idea of trying to feed that directly into the query planner. ?I think this is one of those cases where we let people tune it manually for starters, and then wait for feedback. ?Eventually someone will say oh, I never tune that by hand any more, ever since I wrote this script which does the following computation... and I just run it out cron. ?And then we will get out the party hats. ?But we will never get the experience we need to say what that auto-tuning algorithm will be unless we first provide the knob for someone to fiddle with manually. It is also possible we will implement a manual way and never get around to automating it. ? :-( You make it sound as if we know how but are just too lazy to right the code. That is not one of the weaknesses that this community has. Well, several automatic idea have been floated, but rejected because they don't work well for queries that are planned and executed later. Perhaps we should consider auto-tuning of queries that are planned for immediate execution. I just posed that idea in an email to this thread. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Keywords in pg_hba.conf should be field-specific
Brendan Jurd wrote: On 26 February 2011 18:06, Bruce Momjian br...@momjian.us wrote: Any progress on this? I ended up doing most of the work, but never got around to finishing it off. Thanks for the reminder, though. I'll get that one ready and drop it onto the next CF. Added to TODO: Have pg_hba.conf consider replication special only in the database field * http://archives.postgresql.org/pgsql-hackers/2010-10/msg00632.php -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql: \dg off by one error
On Sun, Feb 27, 2011 at 01:21, Josh Kupershmidt schmi...@gmail.com wrote:
Hi all,
I noticed an off by one error in psql's verbose-mode display for \dg
and \du. In verbose mode, \dg and \du will not display the
Replication attribute:
test=# \dg rep
List of roles
Role name | Attributes | Member of
---+-+---
rep | Replication | {}
test=# \dg+ rep
List of roles
Role name | Attributes | Member of | Description
---++---+-
rep | | {} |
Attached is a one line patch to fix.
Applied, thanks.
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Snapshot synchronization, again...
On 23.02.2011 03:00, Joachim Wieland wrote: On Tue, Feb 22, 2011 at 3:34 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Yes, that's the point I was trying to make. I believe the idea of a hash was that it takes less memory than storing the whole snapshot (and more importantly, a fixed amount of memory per snapshot). But I'm not convinced either that dealing with a hash is any less troublesome. Both Tom and Robert voted quite explicitly against the store-in-shared-memory idea. Others don't want to allow people request arbitrary snapshots and again others wanted to pass the snapshot through the client so that in the future we could also request snapshots from standby servers. The hash idea seemed to at least make nobody unhappy. For easier review, here are a few links to the previous discusion: http://archives.postgresql.org/pgsql-hackers/2010-12/msg00361.php http://archives.postgresql.org/pgsql-hackers/2010-12/msg00383.php http://archives.postgresql.org/pgsql-hackers/2010-12/msg00481.php http://archives.postgresql.org/pgsql-hackers/2010-12/msg02454.php Why exactly, Heikki do you think the hash is more troublesome? It just feels wrong to rely on cryptography just to save some shared memory. I realize that there are conflicting opinions on this, but from user point-of-view the hash is just a variant of the idea of passing the snapshot through shared memory, just implemented in an indirect way. And how could we validate/invalidate snapshots without the checksum (assuming the through-the-client approach instead of storing the whole snapshot in shared memory)? Either you accept anything that passes sanity checks, or you store the whole snapshot in shared memory (or a temp file). I'm not sure which is better, but they both seem better than the hash. -- 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] pg_basebackup and wal streaming
On 2011-02-26 20:59, Magnus Hagander wrote: On Sat, Feb 26, 2011 at 20:48, Yeb Havingayebhavi...@gmail.com wrote: On 2011-02-26 18:19, Magnus Hagander wrote: Attached is an updated version of the patch that includes these changes, as well as Windows support and an initial cut at a ref page for pg_receivexlog (needs some more detail still). I'm testing a bit more (with the previous version, sorry) and got the following while doing a stream backup from a cluster that was at that moment doing a pgbench run with 1 synchronous standby. mgrid@mg79:~$ pg_basebackup --xlog=stream -D /data -vP -h mg73 -U repuser Password: xlog start point: 15/72C8 pg_basebackup: starting background WAL receiver pg_basebackup: got WAL data offset 14744, expected 16791960424) 5148915/5148026 kb g(100%) 1/1 tablespaces xlog end point: 15/80568878 pg_basebackup: waiting for background process to finish streaming... pg_basebackup: child process exited with error 1 Hmm, strange. What platform are you on? Both master and backup are on Linux mg79 2.6.31-22-server #60-Ubuntu SMP Thu May 27 03:42:09 UTC 2010 x86_64 GNU/Linux If I run a streaming backup with idle master server, things are ok. I can repeat the error by doing a pgbench run on the master (with a syncrep standby, don't know if that's of importance, other that there is another walsender) and then running a streaming pg_basebackup. I saw something similar *once* on Windows, but it then passed my tests a lot of times in a row so I figured it was just a didn't clean properly thing. Clearly there's a bug around. What's the size of the latest WAL file that it did work on? Is it 16791960424 bytes? That's way way to large, but perhaps it's not switching segment properly? (that value is supposedly the current write position in the file..) The files from that specific run are gone. mgrid@mg79:~$ pg_basebackup --xlog=stream -D /data -vP -h mg73 -U repuser Password: xlog start point: 47/8E81F300 pg_basebackup: starting background WAL receiver pg_basebackup: got WAL data offset 41432, expected 168186486424) 4763109/4762428 kb g(100%) 1/1 tablespaces xlog end point: 47/9D17FFE0 pg_basebackup: waiting for background process to finish streaming... pg_basebackup: child process exited with error 1 About the file sizes, every WAL file on the master is 16M big, though the sum of the size of all WAL files on the master is close to the 16G number. Maybe a coincidence.. /dev/sdc1 20970612 16798508 4172104 81% /xlog New initdb, pgbench with smaller db, fresh /xlog: mgrid@mg79:~$ pg_basebackup --xlog=stream -D /data -vP -h mg73 -U repuser Password: xlog start point: 0/8C6474E8 pg_basebackup: starting background WAL receiver pg_basebackup: got WAL data offset 10488, expected 167877046397) 1564067/1563386 kb g(100%) 1/1 tablespaces xlog end point: 0/99007798 pg_basebackup: waiting for background process to finish streaming... pg_basebackup: child process exited with error 1 yes a coincidence.. /dev/sdc1 20G 2.6G 18G 13% /xlog Another test with no sync standby server, only the master with a pgbench running: mgrid@mg79:~$ pg_basebackup --xlog=stream -D /data -vP -h mg73 -U repuser Password: xlog start point: 0/D5002120 pg_basebackup: starting background WAL receiver pg_basebackup: got WAL data offset 23752, expected 168009686397) 1572173/1570348 kb g(100%) 1/1 tablespaces xlog end point: 0/EC456968 pg_basebackup: waiting for background process to finish streaming... pg_basebackup: child process exited with error 1 Stopping pgbench and starting a basebackup., then it finishes correctly after a while (with in between something that looks like ~ 2 minutes inactivity). regards, Yeb Havinga -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Native XML
Anton antonin.hou...@gmail.com writes: I've been playing with 'native XML' for a while and now wondering if further development of such a feature makes sense for Postgres. ... Unlike 'libxml2', the parser uses palloc()/pfree(). The output format is independent from any 3rd party code. Hmm, so this doesn't rely on libxml2 at all? Given the amount of pain that library has caused us, getting out from under it seems like a mighty attractive idea. How big a chunk of code do you think it'd be by the time you complete the missing features? 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] wCTE: about the name of the feature
On 2011-02-24 6:40 PM, I wrote: I am planning on working on the documentation this weekend. And here's my attempt. The language is a bit poor at some places but I can't think of anything better. I tried to be more strict about using subquery when talking about WITHs in general since INSERT/UPDATE/DELETE is not a subquery in my book. Regards, Marko Tiikkaja *** a/doc/src/sgml/queries.sgml --- b/doc/src/sgml/queries.sgml *** *** 1526,1532 SELECT replaceableselect_list/replaceable FROM replaceabletable_expression sect1 id=queries-with ! titleliteralWITH/literal Queries (Common Table Expressions)/title indexterm zone=queries-with primaryWITH/primary --- 1526,1532 sect1 id=queries-with ! titleliteralWITH/literal Statements (Common Table Expressions)/title indexterm zone=queries-with primaryWITH/primary *** *** 1539,1549 SELECT replaceableselect_list/replaceable FROM replaceabletable_expression /indexterm para !literalWITH/ provides a way to write subqueries for use in a larger !query. The subqueries, which are often referred to as Common Table Expressions or acronymCTE/acronyms, can be thought of as defining !temporary tables that exist just for this query. One use of this feature !is to break down complicated queries into simpler parts. An example is: programlisting WITH regional_sales AS ( --- 1539,1559 /indexterm para !literalWITH/ provides a way to write auxiliary statements for use in a !larger query. These statements, which are often referred to as Common Table Expressions or acronymCTE/acronyms, can be thought of as defining !temporary tables that exist just for this query. ! /para ! ! sect2 id=subqueries-with !titleSELECT Queries/title ! !indexterm ! primary*/primary !/indexterm ! para !One use of this feature is to break down complicated queries into simpler !parts. An example is: programlisting WITH regional_sales AS ( *** *** 1806,1811 SELECT n FROM t LIMIT 100; --- 1816,1917 In each case it effectively provides temporary table(s) that can be referred to in the main command. /para + /sect2 + sect2 id=modifying-with +titleData-Modifying Statements/title + +indexterm + primary*/primary +/indexterm + +para + You can also use data-modifying statements commandINSERT/, + commandUPDATE/ and commandDELETE/ in literalWITH/. This allows + you to perform many different operations in the same query. An example is: + + programlisting + WITH moved_rows AS ( + DELETE FROM ONLY products + WHERE + date gt;= '2010-10-01' AND + date lt; '2010-11-01' + RETURNING * + ) + INSERT INTO products_log + SELECT * FROM moved_rows; + /programlisting + + which moves rows from products to products_log. In the example above, + the literalWITH/ clause is attached to the commandINSERT/, not the + commandSELECT/. This is important, because data-modifying statements + are not allowed in literalWITH/ clauses which are not attached to the + top level statement. However, normal literalWITH/ visibility rules + apply: it is possible to refer to a data-modifying literalWITH/ from a + subquery. +/para + +para + Recursive self-references in data-modifying statements are not + allowed. In some cases it is possible to work around this limitation by + referring to the output of a recursive literalWITH/: + + programlisting + WITH RECURSIVE included_parts(sub_part, part) AS ( + SELECT sub_part, part FROM parts WHERE part = 'our_product' + UNION ALL + SELECT p.sub_part, p.part + FROM included_parts pr, parts p + WHERE p.part = pr.sub_part + ) + DELETE FROM parts + WHERE part IN (SELECT part FROM included_parts); + /programlisting + + The above query would remove all direct and indirect subparts of a product. +/para + +para + The execution of data-modifying statements in literalWITH/ is + interleaved with the main plan, and the order in which the statements + are executed is arbitrary. The changes made by data-modifying statements + are not visible to the query. +/para +important + para + Trying to update the same row twice in a single command is not supported. + Only one of the modifications takes place, but it is not easy (and + sometimes not possible) to reliably predict which one. This also applies + to deleting a row that was already updated in the same command; only the + update is performed. You should generally avoid trying to modify a single + row twice in a single command. + /para +/important + +para + Data-modifying statements are executed exactly once, and always to + completion. If a literalWITH/ containing a data-modifying statement + is not
Re: [HACKERS] shmget error text reports funny max_connections numbers
I have applied a patch to improve shared memory failure reporting,
attached. We no longer report actual parameter _values_ and suggest
that other parameters might also cause such failures.
---
Alvaro Herrera wrote:
Excerpts from Robert Haas's message of jue oct 14 21:36:48 -0300 2010:
On Wed, Oct 13, 2010 at 2:39 PM, Peter Eisentraut pete...@gmx.net wrote:
Since MaxBackends is actually max_connections + autovacuum_max_workers +
1, when you get an error message from shmget() it will tell you
reduce ... its max_connections parameter (currently 104)
when you actually set
max_connections = 100
This looks a bit silly.
Should we just make the error messages report MaxBackends -
autovacuum_max_workers - 1, or is it worthwhile calling out
autovacuum_max_workers separately?
I suppose there are other reasons we could run out of shared memory,
too. max_locks_per_transaction, for example. It might be good to
revise the wording of the message so as to suggest that these are only
some of the possible causes.
Agreed. Something like reduce one or more of the following parameters:
shared_buffers (currently NN), max_connections (currently NN),
autovacuum_max_workers (currently MM),
I also suggest that it would be good to revise these things so that
sentences within those monstruous paragraphs can be translated
separately. Maybe changing the ErrorData stuff so that there can be
more than one errhint field? If that's too much trouble, perhaps having
%s. %s. %s. %s as the first errhint parameter, and have each sentence
be its own translatable unit.
I also just noticed that we use stars for emphasis here, This error
does *not* mean... which is maybe too cute.
--
??lvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/port/sysv_shmem.c b/src/backend/port/sysv_shmem.c
index aece026..aba6fa8 100644
--- a/src/backend/port/sysv_shmem.c
+++ b/src/backend/port/sysv_shmem.c
@@ -153,25 +153,25 @@ InternalIpcMemoryCreate(IpcMemoryKey memKey, Size size)
segment exceeded your kernel's SHMMAX parameter. You can either
reduce the request size or reconfigure the kernel with larger SHMMAX.
To reduce the request size (currently %lu bytes), reduce
- PostgreSQL's shared_buffers parameter (currently %d) and/or
- its max_connections parameter (currently %d).\n
+ PostgreSQL's shared memory usage, perhaps by reducing shared_buffers
+ or max_connections.\n
If the request size is already small, it's possible that it is less than
your kernel's SHMMIN parameter, in which case raising the request size or
reconfiguring SHMMIN is called for.\n
The PostgreSQL documentation contains more information about shared
memory configuration.,
- (unsigned long) size, NBuffers, MaxBackends) : 0,
+ (unsigned long) size) : 0,
(errno == ENOMEM) ?
errhint(This error usually means that PostgreSQL's request for a shared
memory segment exceeded available memory or swap space,
or exceeded your kernel's SHMALL parameter. You can either
reduce the request size or reconfigure the kernel with larger SHMALL.
To reduce the request size (currently %lu bytes), reduce
- PostgreSQL's shared_buffers parameter (currently %d) and/or
- its max_connections parameter (currently %d).\n
+ PostgreSQL's shared memory usage, perhaps by reducing shared_buffers
+ or max_connections.\n
The PostgreSQL documentation contains more information about shared
memory configuration.,
- (unsigned long) size, NBuffers, MaxBackends) : 0,
+ (unsigned long) size) : 0,
(errno == ENOSPC) ?
errhint(This error does *not* mean that you have run out of disk space.
It occurs either if all available shared memory IDs have been taken,
@@ -179,11 +179,10 @@ InternalIpcMemoryCreate(IpcMemoryKey memKey, Size size)
or because the system's overall limit for shared memory has been
reached. If you cannot increase the shared memory limit,
reduce PostgreSQL's shared memory request (currently %lu bytes),
- by reducing its shared_buffers parameter (currently %d) and/or
- its max_connections parameter (currently %d).\n
+ perhaps by reducing shared_buffers or max_connections.\n
The PostgreSQL documentation contains more information about shared
Re: [HACKERS] WIP: cross column correlation ...
On Sun, Feb 27, 2011 at 3:01 AM, Bruce Momjian br...@momjian.us wrote: Grzegorz Jaskiewicz wrote: On 25 Feb 2011, at 13:18, Robert Haas wrote: People coming from Oracle are not favorably impressed either by the amount of monitoring data PostgreSQL can gather or by the number of knobs that are available to fix problems when they occur. We don't need to have as many knobs as Oracle and we probably don't want to, and for that matter we probably couldn't if we did want to for lack of manpower, but that doesn't mean we should have none. Still, having more data a user can probe would be nice. I wonder why everyone avoids Microsoft's approach to the subject. Apparently, they go in the 'auto-tune as much as possible' direction. And tests we did a while ago, involving asking team from Microsoft and a team from oracle to optimise set of queries for the same set of data (bookies data, loads of it) showed that the auto-tuning Microsoft has in their sql server performed much better than a team of over-sweating oracle dba's. In my current work place/camp we have many deployments of the same system, over different types of machines, each with different customer data that vary so much that queries need to be rather generic. Postgresql shows its strength with planner doing a good job for different variants of data, however we do a very little tweaking to the configuration parameters. Just because it is just too hard to overlook all of them. I guess that the systems could behave much better, but no one is going to tweak settings for 50 different installations over 50 different type of data and 50 different sets of hardware. If there was even a tiny amount of automation provided in the postgresql, I would welcome it with open arms. I totally agree. If we add a tuning parameter that does 10x better than automatic, but only 1% of our users use it, we would be better off, overall, with the automatic tuning. It's not an either/or proposition. There is no reason why we can't let things be tuned automatically, but provide overrides for cases where the automatic tuning does not work well, of which there will always be some. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: cross column correlation ...
On Sun, Feb 27, 2011 at 3:03 AM, Bruce Momjian br...@momjian.us wrote: You make it sound as if we know how but are just too lazy to right the code. That is not one of the weaknesses that this community has. Well, several automatic idea have been floated, but rejected because they don't work well for queries that are planned and executed later. Perhaps we should consider auto-tuning of queries that are planned for immediate execution. I just posed that idea in an email to this thread. Which ideas were rejected for that reason? If we're talking about the idea of using the current contents of the buffer cache and perhaps the OS cache to plan queries, I think that's not likely to work well even if we do restrict it to queries that we're going to execute immediately. Upthread I listed four problems with the idea of planning queries based on the current contents of shared_buffers, and this certainly doesn't address all four. http://archives.postgresql.org/pgsql-hackers/2011-02/msg02206.php To reiterate my basic theme here one more time, we have a very good query planner, but it can fall on its face very badly when it is unable to correctly estimate selectivity, or due to caching effects, and we have very little to recommend to people who run afoul of those problems right now. The problems are real, significant, and affect a large number of users, some of whom give up on PostgreSQL as a direct result. I am glad that we are committed to having a system that is auto-tuning to the greatest degree possible, but I think it is very short-sighted of us not to provide workarounds for the cases where they are legitimately needed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] wCTE: why not finish sub-updates at the end, not the beginning?
I wrote: I'm inclined to think that it would be best to move the responsibility for calling AfterTriggerBeginQuery/AfterTriggerEndQuery into the executor. That would get us down to CreateQueryDesc(...); ExecutorStart(...); // now includes AfterTriggerBeginQuery ExecutorRun(...); // zero or more times ExecutorFinish(...); // ModifyTable cleanup, AfterTriggerEndQuery ExecutorEnd(...); // just does what it's always done FreeQueryDesc(...); where EXPLAIN without ANALYZE would skip ExecutorRun and ExecutorFinish. IMO the major disadvantage of a refactoring like this is the possibility of sins of omission in third-party code, in particular somebody not noticing the added requirement to call ExecutorFinish. We could help them out by adding an Assert in ExecutorEnd to verify that ExecutorFinish had been called (unless explain-only mode). A variant of that problem is an auto_explain-like add-on not noticing that they probably want to hook into ExecutorFinish if they'd previously been hooking ExecutorRun. I don't see any simple check for that though. The other possible failure mode is forgetting to remove calls to the two trigger functions, but we could encourage getting that right by renaming those two functions. This is committed. I desisted from the last change (renaming the trigger functions) because it seemed unnecessary. If someone does forget to remove redundant AfterTriggerBeginQuery/AfterTriggerEndQuery calls, it won't hurt them much, just waste a few cycles stacking and unstacking useless trigger contexts. 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] wCTE: about the name of the feature
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: On 2011-02-24 6:40 PM, I wrote: I am planning on working on the documentation this weekend. And here's my attempt. The language is a bit poor at some places but I can't think of anything better. Thanks, will work on this next. 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] Native XML
On 02/27/2011 10:45 AM, Tom Lane wrote: Antonantonin.hou...@gmail.com writes: I've been playing with 'native XML' for a while and now wondering if further development of such a feature makes sense for Postgres. ... Unlike 'libxml2', the parser uses palloc()/pfree(). The output format is independent from any 3rd party code. Hmm, so this doesn't rely on libxml2 at all? Given the amount of pain that library has caused us, getting out from under it seems like a mighty attractive idea. How big a chunk of code do you think it'd be by the time you complete the missing features? TBH, by the time it does all the things that libxml2, and libxslt, which depends on it, do for us, I think it will be huge. Do we really want to be maintaining a complete xpath and xslt implementation? I think that's likely to be a waste of our scarce resources. I use Postgres' XML functionality a lot, so I'm all in favor of improving it, but rolling our own doesn't seem like the best way to go. As for the pain, we seem to be over the worst of it, AFAICT. It would be nice to move the remaining pieces of the xml2 contrib module into the core. 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] pl/python explicit subtransactions
Committed. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Native XML
Andrew Dunstan and...@dunslane.net writes: On 02/27/2011 10:45 AM, Tom Lane wrote: Hmm, so this doesn't rely on libxml2 at all? Given the amount of pain that library has caused us, getting out from under it seems like a mighty attractive idea. How big a chunk of code do you think it'd be by the time you complete the missing features? TBH, by the time it does all the things that libxml2, and libxslt, which depends on it, do for us, I think it will be huge. Do we really want to be maintaining a complete xpath and xslt implementation? I think that's likely to be a waste of our scarce resources. Well, that's why I asked --- if it's going to be a huge chunk of code, then I agree this is the wrong path to pursue. However, I do feel that libxml pretty well sucks, so if we could replace it with a relatively small amount of code, that might be the right thing to do. I use Postgres' XML functionality a lot, so I'm all in favor of improving it, but rolling our own doesn't seem like the best way to go. As for the pain, we seem to be over the worst of it, AFAICT. No, because the xpath stuff is fundamentally broken, and nobody seems to know how to make libxslt do what we actually need. See the open bugs on the TODO list. 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] Native XML
On Feb 27, 2011, at 11:23 AM, Tom Lane wrote: Well, that's why I asked --- if it's going to be a huge chunk of code, then I agree this is the wrong path to pursue. However, I do feel that libxml pretty well sucks, so if we could replace it with a relatively small amount of code, that might be the right thing to do. I think that XML parsers must be hard to get really right, because of all those I've used in Perl, XML::LibXML is far and away the best. Its docs suck, but it does the work really well. No, because the xpath stuff is fundamentally broken, and nobody seems to know how to make libxslt do what we actually need. See the open bugs on the TODO list. XPath is broken? I use it heavily in the Perl module Test::XPath and now, in PostgreSQL, with my explanation extension. http://github.com/theory/explanation/ Is this something I need to worry about? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Native XML
David E. Wheeler da...@kineticode.com writes: On Feb 27, 2011, at 11:23 AM, Tom Lane wrote: No, because the xpath stuff is fundamentally broken, and nobody seems to know how to make libxslt do what we actually need. See the open bugs on the TODO list. XPath is broken? I use it heavily in the Perl module Test::XPath and now, in PostgreSQL, with my explanation extension. Well, if you're only using cases that work, you don't need to worry. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Native XML
On 27/02/11 19:37, David E. Wheeler wrote: On Feb 27, 2011, at 11:23 AM, Tom Lane wrote: Well, that's why I asked --- if it's going to be a huge chunk of code, then I agree this is the wrong path to pursue. However, I do feel that libxml pretty well sucks, so if we could replace it with a relatively small amount of code, that might be the right thing to do. I think that XML parsers must be hard to get really right, because of all those I've used in Perl, XML::LibXML is far and away the best. Its docs suck, but it does the work really well. No, because the xpath stuff is fundamentally broken, and nobody seems to know how to make libxslt do what we actually need. See the open bugs on the TODO list. XPath is broken? I use it heavily in the Perl module Test::XPath and now, in PostgreSQL, with my explanation extension. http://github.com/theory/explanation/ Is this something I need to worry about I don't believe that XPath is fundamentally broken, but I think Tom may have meant xslt. When reviewing a recent patch to xml2/xslt I found a few bugs in the way were using libxslt, as well as a bug in the library itself (see http://archives.postgresql.org/pgsql-hackers/2011-02/msg01878.php). However if Tom does mean that xpath is the culprit, it may be with the way the libxml2 library works. It's a very messy singleton. If I'm wrong, I'm sure I'll be corrected! Regards, -- Mike Fowler Registered Linux user: 379787 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Native XML
On Feb 27, 2011, at 11:43 AM, Tom Lane wrote: XPath is broken? I use it heavily in the Perl module Test::XPath and now, in PostgreSQL, with my explanation extension. Well, if you're only using cases that work, you don't need to worry. Okay then. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Native XML
Mike Fowler m...@mlfowler.com writes: I don't believe that XPath is fundamentally broken, but I think Tom may have meant xslt. When reviewing a recent patch to xml2/xslt I found a few bugs in the way were using libxslt, as well as a bug in the library itself (see http://archives.postgresql.org/pgsql-hackers/2011-02/msg01878.php). The case that I don't think we have any idea how to solve is http://archives.postgresql.org/pgsql-hackers/2010-02/msg02424.php Most of the other stuff on the TODO list looks like it just requires application of round tuits, although some of it seems to me to reinforce the thesis that libxml/libxslt don't do quite what we need. 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] Porting PostgreSQL to DragonFly BSD
From what I have checked, all work on making postgresql compile on new platforms should be posted to this list, so here it goes. The attached patch (also available at http://www.rumko.net/0001-DragonFly-BSD-support.patch ) applies cleanly to the master branch and can be cherry-picked to REL9_0_STABLE branch without conflicts. It's based on postgres' FreeBSD support with minimal changes (a few freebsd-dragonfly renames, removed mips support since dragonfly does not support it and it also includes a patch to properly define the linker on dfly as per PR pkg/44617 in http://www.netbsd.org/support/query-pr.html gnats database). -- Regards, Rumko 0001-DragonFly-BSD-support.patch Description: application/mbox -- Sent 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_terminate_backend and pg_cancel_backend by not administrator user
On Mon, Feb 14, 2011 at 8:58 AM, Anssi Kääriäinen anssi.kaariai...@thl.fi wrote: On 02/14/2011 02:10 PM, Torello Querci wrote: I suppose that give the right to the owner db user to terminate or cancel other session connected to the database which it is owner is a good thing. I not see any security problem because this user can cancel or terminate only the session related with the own database, but if you think that this is a problem, a configuration parameter can be used. For what it's worth, a big +1 from me. We have pretty much the same use case. It would be good if you could also terminate your own connections. The superuser-only restriction for pg_cancel_backend() has been a pet peeve of mine as well. I actually posted a patch a while back to let users pg_cancel_backend() their own queries, see: http://archives.postgresql.org/pgsql-admin/2010-02/msg00052.php IMO it'd be better to do away with this patch's check of: /* If the user not is the superuser, need to be the db owner. */ and instead just check if the target session's user matches that of the cancel requester. Additionally, this patch keeps all the permission checking inside pg_signal_backend(). That's fine if we're sure that we want pg_cancel_backend() and pg_terminate_backend() to undergo the same permissions check, but perhaps it's a bad idea to relax the permissions check on pg_terminate_backend() ? Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Refactor the executor's API to support data-modifying CTEs bette
On Sun, Feb 27, 2011 at 06:44:32PM +, Tom Lane wrote: Refactor the executor's API to support data-modifying CTEs better. Should something about this go into the release notes? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pika failing since the per-column collation patch
Le 18 févr. 2011 à 08:26, Tom Lane a écrit : =?iso-8859-1?Q?R=E9mi_Zara?= remi_z...@mac.com writes: Le 12 févr. 2011 à 18:51, Peter Eisentraut a écrit : It's only failing on this one machine, but there isn't anything platform-specific in this code, so I'd look for memory management faults on the code or a compiler problem. Try with lower optimization for a start. Same failure with -O0 (and more shared memory). Note that the query that is failing is an intentional probe of robustness: -- check that we can apply functions taking ANYARRAY to pg_stats ... -- such functions must protect themselves if varying element type isn't OK select max(histogram_bounds) from pg_stats; ERROR: cannot compare arrays of different element types pika is instead showing ERROR: locale operation to be invoked, but no collation was derived which some trawling through the code says is coming from varstr_cmp when fn_collation didn't get set on the call. Hypothesis: the platform-dependency here is just one of row ordering, to wit, on most platforms the scan of pg_statistic fails before it gets to the case where the collation issue is triggered. I'm suspicious that if two text arrays get compared via this code path, fn_collation fails to get set, and it's not a platform-specific omission. It'd be helpful if you could identify the specific values that are getting compared at the moment of the failure. Hi, Here is what I get after adding an elog in varstr_cmp: WARNING: Comparing B011 and fetch first clause in subqueries^?^?\xa0 ERROR: locale operation to be invoked, but no collation was derived STATEMENT: select max(histogram_bounds) from pg_stats; Regards, Rémi Zara -- Sent 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: cross column correlation ...
I think there would be value in giving the DBA an easier way to see which tables are hot, but I am really leery about the idea of trying to feed that directly into the query planner. I think this is one of those cases where we let people tune it manually for starters, and then wait for feedback. Eventually someone will say oh, I never tune that by hand any more, ever since I wrote this script which does the following computation... and I just run it out cron. And then we will get out the party hats. But we will never get the experience we need to say what that auto-tuning algorithm will be unless we first provide the knob for someone to fiddle with manually. I'm not disagreeing with that. I'm saying first, we give DBAs a way to see which tables are currently hot. Such a feature has multiple benefits, making it worth the overhead and/or coding effort. Whether we're shooting for autotuning or manual tuning, it starts with having the data. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Fwd: Re: [HACKERS] Native XML
Sorry for resending, I forgot to add 'pgsql-hackers' to CC. Original Message Subject:Re: [HACKERS] Native XML Date: Sun, 27 Feb 2011 23:18:03 +0100 From: Anton antonin.hou...@gmail.com To: Tom Lane t...@sss.pgh.pa.us On 02/27/2011 04:45 PM, Tom Lane wrote: Anton antonin.hou...@gmail.com writes: I've been playing with 'native XML' for a while and now wondering if further development of such a feature makes sense for Postgres. ... Unlike 'libxml2', the parser uses palloc()/pfree(). The output format is independent from any 3rd party code. Hmm, so this doesn't rely on libxml2 at all? Given the amount of pain that library has caused us, getting out from under it seems like a mighty attractive idea. How big a chunk of code do you think it'd be by the time you complete the missing features? regards, tom lane Right, no dependency, everything coded from scratch. For the initial stable version, my plan is to make the parser conform to the standard as much as possible and the same for XMLPath / XMLQuery. (In all cases the question is which version of the standard to start at.) Integration of SQL XML data in queries is my primary interest. I didn't really think to re-implement XSLT. For those who really need to use XSLT functionality at the database level, can't the API be left for optional installation? Also I'm not sure if document validation is necessary for the initial version - I still see a related item on the current TODO list. Sincerely, Tony,
Re: [HACKERS] Exposing an installation's default value of unix_socket_directory
Added to TODO: Allow simpler reporting of the unix domain socket directory and allow easier configuration of its default location * http://archives.postgresql.org/pgsql-hackers/2010-10/msg01555.php --- Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: On tor, 2010-10-21 at 16:59 -0400, Tom Lane wrote: Actually, the only reason this is even up for discussion is that there's no configure option to set DEFAULT_PGSOCKET_DIR. If there were, and debian were using it, then pg_config --configure would tell what I wish to know. I thought for a bit about proposing we add such an option, but given the current state of play it might be more misleading than helpful: as long as distros are accustomed to changing this setting via a patch, you couldn't trust pg_config --configure to tell you what a given installation actually has compiled into it. Presumably, if a configure option were added, they couldn't change it via patch anymore. Hm, you're right: we'd remove the pg_config_manual.h entry, so the existing patches would stop working, and presumably maintainers would figure out that they ought to use the configure switch instead. So that argument holds little water. Btw., a configure option for this was rejected years ago to discourage people from actually changing the default. Yeah, I remember that discussion now that you mention it. It still seems like a good policy ... but given that some popular packages are changing the default whether we think it's a good idea or not, maybe it's better to acknowledge that reality. We could still have some text in the manual pointing out the compatibility hazards of using the switch, I guess. 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 -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [OT] Christchurch
Folks, Sarah and I are fine, having missed out on the disaster, but Christchurch could use some help. http://www.christchurchearthquakeappeal.govt.nz/ http://www.redcross.org.nz/donate Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Porting PostgreSQL to DragonFly BSD
On sön, 2011-02-27 at 21:19 +0100, Rumko wrote: The attached patch (also available at http://www.rumko.net/0001-DragonFly-BSD-support.patch ) applies cleanly to the master branch and can be cherry-picked to REL9_0_STABLE branch without conflicts. It seems to me that it would be easier to just map dragonfly to the freebsd template. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Native XML
On sön, 2011-02-27 at 10:45 -0500, Tom Lane wrote: Hmm, so this doesn't rely on libxml2 at all? Given the amount of pain that library has caused us, getting out from under it seems like a mighty attractive idea. This doesn't replace the existing xml functionality, so it won't help getting rid of libxml. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Native XML
On 02/27/2011 03:06 PM, Tom Lane wrote: Mike Fowlerm...@mlfowler.com writes: I don't believe that XPath is fundamentally broken, but I think Tom may have meant xslt. When reviewing a recent patch to xml2/xslt I found a few bugs in the way were using libxslt, as well as a bug in the library itself (see http://archives.postgresql.org/pgsql-hackers/2011-02/msg01878.php). The case that I don't think we have any idea how to solve is http://archives.postgresql.org/pgsql-hackers/2010-02/msg02424.php I'd forgotten about this. But as ugly as it is, I don't think it's libxml2's fault. 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] [OT] Christchurch
David, Glad to hear that! -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp Folks, Sarah and I are fine, having missed out on the disaster, but Christchurch could use some help. http://www.christchurchearthquakeappeal.govt.nz/ http://www.redcross.org.nz/donate Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Snapshot synchronization, again...
On Sun, Feb 27, 2011 at 3:04 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Why exactly, Heikki do you think the hash is more troublesome? It just feels wrong to rely on cryptography just to save some shared memory. Remember that it's not only about saving shared memory, it's also about making sure that the snapshot reflects a state of the database that has actually existed at some point in the past. Furthermore, we can easily invalidate a snapshot that we have published earlier by deleting its checksum in shared memory as soon as the original transaction commits/aborts. And for these two a checksum seems to be a good fit. Saving memory then comes as a benefit and makes all those happy who don't want to argue about how many slots to reserve in shared memory or don't want to have another GUC for what will probably be a low-usage feature. I realize that there are conflicting opinions on this, but from user point-of-view the hash is just a variant of the idea of passing the snapshot through shared memory, just implemented in an indirect way. The user will never see the hash, why should he bother? The user point of view is that he receives data and can obtain the same snapshot if he passed that data back. This user experience is no different from any other way of passing the snapshot through the client. And from the previous discussions this seemed to be what most people wanted. And how could we validate/invalidate snapshots without the checksum (assuming the through-the-client approach instead of storing the whole snapshot in shared memory)? Either you accept anything that passes sanity checks, or you store the whole snapshot in shared memory (or a temp file). I'm not sure which is better, but they both seem better than the hash. True, both might work but I don't see a real technical advantage over the checksum approach for any of them, rather the opposite. Nobody has come up with a use case for the accept-anything option so far, so I don't see why we should commit ourselves to this feature at this point, given that we have a cheap and easy way of validating/invalidating snapshots. And I might be just paranoid but I also fear that someone could raise security issues for the fact that you would be able to request an arbitrary database state from the past and inspect changes of other peoples' transactions. We might want to allow that later though and I realize that we have to allow it for a standby server that would take over a snapshot from the master anyway, but I don't want to add this complexity into this first patch. I want however be able to possibly allow this in the future without touching the external API of the feature. And for the tempfile approach, I don't see that the creation and removal of the temp file is any less code complexity than flipping a number in shared memory. Also it seemed that people rather wanted to go with the through-the-client approach because it seems to be more flexible. Maybe you should just look at it as a conservative accept-anything approach that uses a checksum to allow only snapshots that we know have existed and have been published. Does the checksum still look so weird from this perspective? Joachim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Native XML
Andrew Dunstan and...@dunslane.net writes: On 02/27/2011 03:06 PM, Tom Lane wrote: The case that I don't think we have any idea how to solve is http://archives.postgresql.org/pgsql-hackers/2010-02/msg02424.php I'd forgotten about this. But as ugly as it is, I don't think it's libxml2's fault. Well, strictly speaking it's libxslt's fault, no? But AFAIK those two things are a package. 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] Native XML
On 02/27/2011 10:07 PM, Tom Lane wrote: Andrew Dunstanand...@dunslane.net writes: On 02/27/2011 03:06 PM, Tom Lane wrote: The case that I don't think we have any idea how to solve is http://archives.postgresql.org/pgsql-hackers/2010-02/msg02424.php I'd forgotten about this. But as ugly as it is, I don't think it's libxml2's fault. Well, strictly speaking it's libxslt's fault, no? But AFAIK those two things are a package. No, I think the xpath implementation is from libxml2. But in any case, I think the problem is in the whole design of the xpath_table function, and not in the library used for running the xpath queries. i.e it's our fault, and not the libraries. (mutters about workmen and tools) 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] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
On Thu, Jan 6, 2011 at 10:08 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: We could modify pg_dump to emit RESET AUTHORIZATION in --binary-upgrade mode. I am unclear if that might cause some other problems though. I finally figured out what was really bugging me about that proposal: it's a one-shot hack for fixing one problem that could arise from non-default ALTER DATABASE/ALTER ROLE settings. Who's to say there are not other such issues, either now or in the future? It occurs to me that a more principled way to deal with this class of problems would be to delay restoring ALTER DATABASE/ALTER ROLE settings until after everything else is done. Not sure what the implementation consequences of that would be. Ideally we'd make pg_dumpall output work that way in general, not just for pg_upgrade. Yep, it feels like a one-off that no one else will ever hit, and there are certainly other ALTER DATABASE SET commands that could also obstruct a restore. Did anything ever come of this discussion? On one of the databases I was upgrading, I ran into a similar problem with roles that are set as roles. The problem seems to stem from pg_dumpall dumping roles in alphabetical order: CREATE ROLE asha; ALTER ROLE asha SET role TO 'omniti'; .. sometime later ... CREATE ROLE omniti; It generates an error because the ALTER ROLE fails with the role not existing, which causes pg_upgrade to bail out (it's in the on error stop part). ISTM this fails in general, so not blaming pg_upgrade; I think there should probably be a fix in pg_dumpall to create all roles first before running the alters, but there might be some other options. Thoughts? Robert Treat play: xzilla.net work: omniti.com hiring: l42.org/Lg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with pg_upgrade (8.4 - 9.0) due to ALTER DATABASE SET ROLE
Robert Treat r...@xzilla.net writes: Did anything ever come of this discussion? I think it's a TODO --- nothing done about it as yet, AFAIR. On one of the databases I was upgrading, I ran into a similar problem with roles that are set as roles. The problem seems to stem from pg_dumpall dumping roles in alphabetical order: CREATE ROLE asha; ALTER ROLE asha SET role TO 'omniti'; .. sometime later ... CREATE ROLE omniti; That seems like a pretty bizarre thing to do. Why would you want such a setting? 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] wCTE: about the name of the feature
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: On 2011-02-24 6:40 PM, I wrote: I am planning on working on the documentation this weekend. And here's my attempt. The language is a bit poor at some places but I can't think of anything better. Applied after some rather heavy editorialization. I tried to be more strict about using subquery when talking about WITHs in general since INSERT/UPDATE/DELETE is not a subquery in my book. I undid most of those changes --- it didn't seem to add anything to be strict in this way, and anyway you hadn't done it consistently, eg the syntax still had with_query. 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
