[HACKERS] Somebody has broken autovacuum's abort path
http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jaguardt=2010-01-05%2004:00:03 (and the same a couple times before this...) Core was generated by `postgres: autovacuum worker process regression '. Program terminated with signal 6, Aborted. [New process 9209] #0 0x0091c402 in __kernel_vsyscall () #0 0x0091c402 in __kernel_vsyscall () #1 0x007a9d80 in raise () from /lib/libc.so.6 #2 0x007ab691 in abort () from /lib/libc.so.6 #3 0x083393be in ExceptionalCondition ( conditionName=0x8498e40 !(rel-rd_refcnt 0), errorType=0x836d218 FailedAssertion, fileName=0x8498d55 relcache.c, lineNumber=1612) at assert.c:57 #4 0x083324c2 in RelationDecrementReferenceCount (rel=0xb5c641e0) at relcache.c:1612 #5 0x0835b562 in ResourceOwnerReleaseInternal (owner=0x92d1058, phase=RESOURCE_RELEASE_BEFORE_LOCKS, isCommit=0 '\0', isTopLevel=1 '\001') at resowner.c:251 #6 0x0835b86f in ResourceOwnerRelease (owner=0x92d1058, phase=RESOURCE_RELEASE_BEFORE_LOCKS, isCommit=0 '\0', isTopLevel=1 '\001') at resowner.c:185 #7 0x080cc5d9 in AbortTransaction () at xact.c:2179 #8 0x080cc7c7 in AbortOutOfAnyTransaction () at xact.c:3676 #9 0x0824063e in do_autovacuum () at autovacuum.c:2259 #10 0x08240b25 in AutoVacWorkerMain (argc=value optimized out, argv=value optimized out) at autovacuum.c:1602 #11 0x08240c51 in StartAutoVacWorker () at autovacuum.c:1406 #12 0x0824c0f5 in sigusr1_handler (postgres_signal_arg=10) at postmaster.c:4307 #13 signal handler called #14 0x0091c402 in __kernel_vsyscall () #15 0x0084b1dd in ___newselect_nocancel () from /lib/libc.so.6 #16 0x082486e0 in ServerLoop () at postmaster.c:1364 #17 0x08249d96 in PostmasterMain (argc=6, argv=0x924d918) at postmaster.c:1069 #18 0x081eb080 in main (argc=6, argv=0x0) at main.c:188 I think this can likely be blamed on the HS changes in transaction abort, since I'm not aware of any other recent changes near here. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] KNNGiST for knn-search (WIP)
Robert, On Mon, 4 Jan 2010, Robert Haas wrote: On Mon, Jan 4, 2010 at 5:33 PM, Paul Ramsey pram...@cleverelephant.ca wrote: I'm sure whatever conclusion -hackers comes to in the end will be the best for pgsql, and I'll be supportive. But until then, let me note from the PostGIS point-of-view: sure would be great to get this in for 8.5 :) That's good to know. The current status is that I've been waiting for a patch that applies cleanly for 6 days, and we have 41 days left until the end of the last CommitFest. There's not much I can do to move this along until I have a clean patch to work with. sorry, it's a long holiday in Russia, we'll be able to sync next week. 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] tribble.postgresql.org - planned maintenance downtime
Hi all! There will be planned downtime on tribble.postgresql.org Jan 6(tomorrow) from 10:00-12:30 GMT(estimated) affecting the following services: cvs.postgresql.org wwwmaster.postgresql.org www.pgadmin.org doxygen.postgresql.org Downtime is necessary to implement some OS level updates (both for the host and the jails) as well as a routine HW check. The required work is going to result in intermediate outages during the above mentioned timeframe so I would advise holding of from any commits or other changes on the above mentioned systems until you see an explicit It's done :) 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
[HACKERS] global\pg_auth
Hi, Why PG uses a flat file (global\pg_auth) for md5 authentication!? Is it forced to do it instead of accessing relevant table is system catalog? Thanks - B. L.
Re: [HACKERS] [PATCH] Windows x64 [repost]
Magnus Hagander mag...@hagander.net wrote:
On Fri, Jan 1, 2010 at 20:45, Magnus Hagander mag...@hagander.net wrote:
On Fri, Dec 4, 2009 at 11:42, Tsutomu Yamada tsut...@sraoss.co.jp wrote:
2) use appropriate macro and datatypes for Windows API.
enables more than 32bits shared memory.
Are you sure this one should use __noop, and not __nop?
__noop: http://msdn.microsoft.com/en-us/library/s6btaxcs.aspx
__nop: http://msdn.microsoft.com/en-us/library/aa983381.aspx
I think __nop is what we want?
Also, that turns it into nop and not rep nop, no?
I did some more research, and __nop() is at least closer than
__noop(), but it's still not the same.
Should we perhaps instead use __yield, per:
http://msdn.microsoft.com/en-us/library/2b2h26kx.aspx
On further reading, __yield() is only available on Itanium.
This spinlock code was came from below.
http://archives.postgresql.org/pgsql-hackers/2008-07/msg00307.php
Sorry, I didn't care which macro was better.
I found 'YieldProcessor' in MSDN.
http://msdn.microsoft.com/en-us/library/ms687419%28VS.85%29.aspx
YieldProcessor was defined in winnt.h.
The definition changes depending on architecture and compiler version.
__asm { rep nop };
__mm_pause();
__yield();
YieldProcessor become __mm_pause() in _AMD64_.
So __mm_pause() is better?
// test program
#include windows.h
main()
{
YieldProcessor();
}
// end
// cl /E test.c out.i
// tail out.i
// # I recommend redirecting to file, The output become large.
Tsutomu Yamada
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] global\pg_auth
On Tuesday 05 January 2010 09:37:57 black light wrote: Hi, Why PG uses a flat file (global\pg_auth) for md5 authentication!? Is it forced to do it instead of accessing relevant table is system catalog? It does not anymore: http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=7b0a92a0b6f5ef41f1547381cbd0e2604ec2bf87 The reason earlier was that the authentication check was done before a backend was started... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: I: [HACKERS] TODO: Allow substring/replace() to get/set bit values
You might want to search the archives (or the wiki history, or the CVS history if it's been there since before we moved the TODO list to the wiki) for discussion of why that item was added to the TODO in the first place. I read the thread: http://archives.postgresql.org/pgsql-hackers/2004-02/msg00478.php 1) it is true that getbit sounds a lot like what substring() does, but the same could be said for binary string substring/get_byte; so IMHO get/set_bit should be present for bit string 2) it is not very clear to me how setbit could actually be handled by replace() (maybe overlay style?) 3) since I'm looking at byte string get/set_bit to understand how that works, I'm having a hard time understanding why the bit indexes in get/set_bit are low-first based: select get_bit(E'\\376\\376'::bytea, s) as b,s from generate_series(0,15,1) as s b s 0 0 1 1 1 2 1 3 1 4 1 5 1 6 1 7 0 8 1 9 1 10 1 11 1 12 1 13 1 14 1 15 I understand this is the internal representation, but still: if someone asked me what the 8th bit in 11101110 is, I would have said 1, not 0 (assuming the first bit has index '0'). Actually, David Helgason's patch (http://archives.postgresql.org/pgsql-hackers/2004-01/msg00498.php) goes in this direction: note the bitNo = 7 - (n % 8); part. Using that algorithm would mean get/set_bit in bit string would behave differently from what they do in binary string (IMHO it's the binary string implementation that is wrong). Leonardo -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Submitting a query inside the backend
As part of a research project I would like to change the source code of Postgres. There, I want to do the following: I want to stop the optimizer at some place, issue a query from the optimizer and use the result of the query to continue the optimization process. Is there a good and clean way how I could do this? I want to submit another query inside the existing connection and optimizer code. Is there some function I could use? I don’t want to do a hardcoded scan of a table, I want to build a query string and use the parser, analyzer, optimizer, and executer to submit the query. Thanks a lot! Robert
[HACKERS] libpq naming on Win64
There is likely to be a long period where many Windows packages for PostgreSQL are 32 bit only. Due to the way Windows searches for DLLs, Windows installations of PostgreSQL tend to install libpq.dll into the bin/ directory of the installation. This will cause obvious problems with 32 bit packages like pgAdmin which are currently included in that directory in most installers, to avoid the need to have multiple copies of DLLs around. After chatting with Magnus, we feel that a good solution would be to rename libpq on Win64 to libpq64.dll to distinguish it from the 32 bit equivalent. It would also be a nice touch to have the 64 bit MSVC build system create both the 64 and 32 bit libraries. That would make it much easier for those of us that need to combine 32 and 64 bit packages together, saving the pain of building 32 and 64 bit separately. Thoughts? -- 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] Windows x64 [repost]
On Tue, Jan 5, 2010 at 09:14, Tsutomu Yamada tsut...@sraoss.co.jp wrote:
Magnus Hagander mag...@hagander.net wrote:
On Fri, Jan 1, 2010 at 20:45, Magnus Hagander mag...@hagander.net wrote:
On Fri, Dec 4, 2009 at 11:42, Tsutomu Yamada tsut...@sraoss.co.jp
wrote:
2) use appropriate macro and datatypes for Windows API.
enables more than 32bits shared memory.
Are you sure this one should use __noop, and not __nop?
__noop: http://msdn.microsoft.com/en-us/library/s6btaxcs.aspx
__nop: http://msdn.microsoft.com/en-us/library/aa983381.aspx
I think __nop is what we want?
Also, that turns it into nop and not rep nop, no?
I did some more research, and __nop() is at least closer than
__noop(), but it's still not the same.
Should we perhaps instead use __yield, per:
http://msdn.microsoft.com/en-us/library/2b2h26kx.aspx
On further reading, __yield() is only available on Itanium.
This spinlock code was came from below.
http://archives.postgresql.org/pgsql-hackers/2008-07/msg00307.php
Sorry, I didn't care which macro was better.
I found 'YieldProcessor' in MSDN.
http://msdn.microsoft.com/en-us/library/ms687419%28VS.85%29.aspx
YieldProcessor was defined in winnt.h.
The definition changes depending on architecture and compiler version.
__asm { rep nop };
__mm_pause();
__yield();
YieldProcessor become __mm_pause() in _AMD64_.
So __mm_pause() is better?
Yeah, that seems right. I'll change it to that.
--
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] [PATCH] Windows x64 [repost]
Magnus Hagander mag...@hagander.net wrote: 2009/12/4 Tsutomu Yamada tsut...@sraoss.co.jp: Thanks to suggestion. I send pathces again by another mailer for the archive. Sorry to waste resources, below is same content that I send before. I have a couple of comments about the first patch (I'll get to the others later): config.win32.h has: + #ifdef _MSC_VER + /* #undef HAVE_STDINT_H */ + #else #define HAVE_STDINT_H 1 + #endif Is that really necessary? config.h.win32 is only used on MSVC builds, no? I also think it is unnecessary. But pg_config.h.win32 was referred from src/bcc32.mak. Is Borland C++ still supported? It is an unnecessary code if not becoming a problem to Borland. -- Tsutomu Yamada 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] Testing with concurrent sessions
Hi, Kevin Grittner wrote: It's very soon going to be critical that I be able to test particular interleavings of statements in particular concurrent transaction sets to be able to make meaningful progress on the serializable transaction work. I've something in place for Postgres-R, as I also need to test concurrent transactions there. It's based on python/twisted and is able to start multiple Postgres instances (as required for testing replication) and query them concurrently (as you seem to need as well). It uses an asynchronous event loop (from twisted) and basically controls processes, issues queries and checks results and ordering constraints (e.g. transaction X must commit and return a result before transaction Y). I'm still under the impression that this testing framework needs cleanup. However, others already showed interest as well... Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Thoughts on statistics for continuously advancing columns
Hi, My suggestion is to keep two sets of histograms. One which is generated by running ANALYZE and the other which is dynamically generated histograms using the entries from logging (that is done in insert/update/delete operations). I am not sure how difficult is it to read such record details from logs. Basically from the details mentioned here what i understand is that the table data (timestamp) is added in incremental way, ie existing data is not modified to great extent and the new data is merely appended to old data. In this case, the only work for analyse/statistics generation is to merge the histograms of newly added records to old histograms. if we can treat this case as similar to that of merging of histograms in case of joins involving 2 tables and generating the histograms for the cartesian (result) node, then all we need to do is somehow generate temporary histogram for the new set of records and merge them with the old histogram. The information of interesting columns from the new records can be read from the logging section. We must be already having the part of merging of histograms and I hope that it wont be very costly to make these calls so as to effect planner. (Further my opinion is to calculate this cost of histogram generation and use it in costing in some way) Further we can put some threshold limit to make this merge happen automatically. Say if the temporary histograms reach some set threshold, only then these will be merged with the older histograms. Please pass on your inputs. Regards, Chetan On Wed, Dec 30, 2009 at 8:38 AM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: My thoughts on dealing with this intelligently without a major change to statstics gathering went along these lines: 1. add columns to pg_statistic to hold estimates of upper and lower bounds growth between analyzes. This seems like a fundamentally broken approach, first because time between analyzes is not even approximately a constant, and second because it assumes that we have a distance metric for all datatypes. (Note that convert_to_scalar does not assume that it can measure arbitrary distances, but only fractions *within* a histogram bucket; and even that is pretty shaky.) I don't have a better idea at the moment :-( regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] quoting psql varible as identifier
2010/1/5 Tom Lane t...@sss.pgh.pa.us: Robert Haas robertmh...@gmail.com writes: On Mon, Jan 4, 2010 at 2:51 PM, Pavel Stehule pavel.steh...@gmail.com wrote: I don't have a problem to write second and safe fmtId function (with technique used in dumputils don't need to modify libpq), although fmtId do exactly what I need. I would to understand to behave. I think you mean that you would need to understand how it should behave - in which case I agree, but I think Tom spelled that out pretty clearly upthread: close PQescapeStringConn and adapt it to be PQescapeIdentifier. The more important point here is that fmtId doesn't do exactly what you need in any case. fmtId is safe to use in pg_dump because pg_dump is only expected to work with the same or older version of the backend. It would not be safe to use it in libpq, which is expected to still work with newer backends that might have more reserved words. So I finnaly moved to libpq PQescapeIdentConn function patch is attached. regards Pavel regards, tom lane variable_escaping.diff 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] Change to config.pl processing in the msvc build environment
On Sun, Jan 3, 2010 at 05:13, Andrew Dunstan and...@dunslane.net wrote: Peter Eisentraut wrote: On fre, 2010-01-01 at 16:32 +0100, Magnus Hagander wrote: I therefor propose that we rename this file to config.pl.default, and change the scripts to first load config.pl.default, and then load config.pl if it exists. I'd keep the naming so that the extension .pl is preserved. Helps editors and such. Right ... let's call it default_config.pl or some such. Otherwise it looks sane enough. I don't think the parens on trailing conditions issue is anything other than just a matter of taste. I often use them because I've occasionally been caught by not doing so. Ok, I've applied this patch with the name change to config_default.pl (that way it still sorts next to config.pl etc, which was how Dave convinced me of the config.pl.default name in the first place :D) -- 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] [PATCH] Windows x64 [repost]
On Tue, Jan 5, 2010 at 12:58, Tsutomu Yamada tsut...@sraoss.co.jp wrote: Magnus Hagander mag...@hagander.net wrote: 2009/12/4 Tsutomu Yamada tsut...@sraoss.co.jp: Thanks to suggestion. I send pathces again by another mailer for the archive. Sorry to waste resources, below is same content that I send before. I have a couple of comments about the first patch (I'll get to the others later): config.win32.h has: + #ifdef _MSC_VER + /* #undef HAVE_STDINT_H */ + #else #define HAVE_STDINT_H 1 + #endif Is that really necessary? config.h.win32 is only used on MSVC builds, no? I also think it is unnecessary. But pg_config.h.win32 was referred from src/bcc32.mak. Is Borland C++ still supported? It is an unnecessary code if not becoming a problem to Borland. Hmm. Yeah, that's a good question. I'd say it's supported on 8.4, because it's in the docs, and I think it works. As for 8.5, it will only be supported if somebody steps up and does the testing around RC time. Having accidentally configured that thing wrong for no change for libpq (I think it was), I'm not convinced it wil actually break Borland. So let's leave it this way unless someone confirms it's broken. -- 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] Submitting a query inside the backend
Robert Wittauer wrote: As part of a research project I would like to change the source code of Postgres. There, I want to do the following: I want to stop the optimizer at some place, issue a query from the optimizer and use the result of the query to continue the optimization process. Is there a good and clean way how I could do this? I want to submit another query inside the existing connection and optimizer code. Is there some function I could use? I don’t want to do a hardcoded scan of a table, I want to build a query string and use the parser, analyzer, optimizer, and executer to submit the query. Not necessarily a good idea, but you can use the SPI family of functions to run queries from inside the backend. Not sure how well it will work from the optimizer (consider infinite recursion) -- 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] libpq naming on Win64
On 5/01/2010 6:52 PM, Dave Page wrote: There is likely to be a long period where many Windows packages for PostgreSQL are 32 bit only. Due to the way Windows searches for DLLs, Windows installations of PostgreSQL tend to install libpq.dll into the bin/ directory of the installation. This will cause obvious problems with 32 bit packages like pgAdmin which are currently included in that directory in most installers, to avoid the need to have multiple copies of DLLs around. After chatting with Magnus, we feel that a good solution would be to rename libpq on Win64 to libpq64.dll to distinguish it from the 32 bit equivalent. +1 from me. I've had to deal with a few DLLs that may come in either 32- or 64-bit flavours with the same name, and it's absolutely awful. It makes debug- and non-debug DLLs with the same names look fun in comparison. It would also be a nice touch to have the 64 bit MSVC build system create both the 64 and 32 bit libraries. That would make it much easier for those of us that need to combine 32 and 64 bit packages together, saving the pain of building 32 and 64 bit separately. Are there plans to move to building with VC++ 2008 at the same time? I'd be somewhat concerned about building and shipping libp64 with VC++ 2005. -- Craig Ringer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Streaming replication and postmaster signaling
Looking at the latest streaming replication patch, I don't much like the signaling between WAL sender and postmaster. It seems complicated, and as a rule of thumb postmaster shouldn't be accessing shared memory. The current signaling is: 1. A new connection arrives. A new backend process is forked forked like for a normal connection. 2. When the new process is done with the initialization, it allocates itself a slot from WalSndCtlData shared memory array. It marks its pid there, sets registered = false, and signals postmaster with PMSIGNAL_REGISTER_WALSENDER 3. Upon receiving that signal, postmaster scans the WalSndCtlData array looking for entries with registered==false. For such entries, it scans the postmaster-private backend list for a matching entry with the same pid, marks the entry in the list as a walsender, and sets registered=true in the shared memory entry. This way postmaster knows which child processes are walsenders, when it's time to signal them. I think it would be better to utilize the existing array of child processes in pmsignal.c. Instead of having postmaster peek into WalSndCtlData, let's add a new state to PMChildFlags, PM_CHILD_WALSENDER, which is just like PM_CHILD_ACTIVE but tells postmaster that the child is not a normal backend but a walsender. I've done that in my git branch. -- 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] libpq naming on Win64
2010/1/5 Craig Ringer cr...@postnewspapers.com.au: It would also be a nice touch to have the 64 bit MSVC build system create both the 64 and 32 bit libraries. That would make it much easier for those of us that need to combine 32 and 64 bit packages together, saving the pain of building 32 and 64 bit separately. Are there plans to move to building with VC++ 2008 at the same time? I'd be somewhat concerned about building and shipping libp64 with VC++ 2005. Yes, Magnus has already made that work. -- 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] execute sql commands in core
On Tue, Jan 5, 2010 at 12:56 AM, black light blacklight1...@gmail.com wrote: Hi, I want to add some hard-wired extra authentication codes in my PG. The only problem is how to access tables to get/change information from core (auth.c)? I have changed the SPI functions to use them but it was not effective (lots of different errors!) In fact, i want to add a table to system catalog and SELECT/UPDATE it from auth.c. In general you need to look at existing code that does something similar to what you want to do and model your new code after it. It's not really possible to speculate as to what you might have done wrong or what you ought to do instead from the information you've provided here. If you are considering proposing your patch for inclusion in the PostgreSQL upstream sources, you should first discuss why you are trying to do this and what you hope to get out of it. There might be a way to do whatever it is you are trying to do without modifying the source code. ...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] Streaming replication and postmaster signaling
Heikki Linnakangas escribió: Looking at the latest streaming replication patch, I don't much like the signaling between WAL sender and postmaster. It seems complicated, and as a rule of thumb postmaster shouldn't be accessing shared memory. The current signaling is: 1. A new connection arrives. A new backend process is forked forked like for a normal connection. This was probably discussed to death earlier, but: why was it decided to not simply use a different port for listening for walsender connections? -- 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] Thoughts on statistics for continuously advancing columns
On Tue, Jan 5, 2010 at 7:49 AM, Chetan Suttraway chetan.suttra...@enterprisedb.com wrote: if we can treat this case as similar to that of merging of histograms in case of joins involving 2 tables and generating the histograms for the cartesian (result) node, ...which you can't, because it's totally different, so I think the rest of this is a dead end. ...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: I: [HACKERS] TODO: Allow substring/replace() to get/set bit values
On Tue, Jan 5, 2010 at 3:58 AM, Leonardo F m_li...@yahoo.it wrote: You might want to search the archives (or the wiki history, or the CVS history if it's been there since before we moved the TODO list to the wiki) for discussion of why that item was added to the TODO in the first place. I read the thread: http://archives.postgresql.org/pgsql-hackers/2004-02/msg00478.php 1) it is true that getbit sounds a lot like what substring() does, but the same could be said for binary string substring/get_byte; so IMHO get/set_bit should be present for bit string 2) it is not very clear to me how setbit could actually be handled by replace() (maybe overlay style?) 3) since I'm looking at byte string get/set_bit to understand how that works, I'm having a hard time understanding why the bit indexes in get/set_bit are low-first based: select get_bit(E'\\376\\376'::bytea, s) as b,s from generate_series(0,15,1) as s b s 0 0 1 1 1 2 1 3 1 4 1 5 1 6 1 7 0 8 1 9 1 10 1 11 1 12 1 13 1 14 1 15 I understand this is the internal representation, but still: if someone asked me what the 8th bit in 11101110 is, I would have said 1, not 0 (assuming the first bit has index '0'). Actually, David Helgason's patch (http://archives.postgresql.org/pgsql-hackers/2004-01/msg00498.php) goes in this direction: note the bitNo = 7 - (n % 8); part. Using that algorithm would mean get/set_bit in bit string would behave differently from what they do in binary string (IMHO it's the binary string implementation that is wrong). Well, I'm not really clear on what you're trying to accomplish here. As you say, there's really no point in changing the internal representation, and if you don't find replace() useful either, then why are you even working on this at all? Since the latest discussion of this is more than five years old, it's unclear that anyone even cares any more. It seems to me that making replace overlay a substring of bits could be a reasonable thing to do, but if nobody actually wants it, then the simplest thing to do is remove this from the TODO and call it good. ...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] libpq naming on Win64
Dave Page dp...@pgadmin.org writes: After chatting with Magnus, we feel that a good solution would be to rename libpq on Win64 to libpq64.dll to distinguish it from the 32 bit equivalent. Isn't that going to break applications? Where by break I mean have to explicitly link with 'libpq64', thereby rendering them unportable to any other platform. I would have thought Microsoft would have a better solution than this for managing 64-bit libraries. Or am I too optimistic about Redmond's competence? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch - per-tablespace random_page_cost/seq_page_cost
On Mon, Jan 4, 2010 at 1:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: My only objection to that is that if we're going to add attoptions also, I'd like to get this committed first before I start working on that, and we're running short on time. If you can commit his patch in the next day or two, then I am fine with rebasing mine afterwards, but if it needs more work than that then I would prefer to commit mine so I can move on. Is that reasonable? Fair enough --- if I can't get it done today I will let you know and hold off. OK, so since you got this done, I'm going to go ahead and rebase commit mine today, after a final read-through or two, unless you or anyone else wants to insert some last-minute objections? ...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] Does parallel make require guards against duplicate actions?
On mån, 2010-01-04 at 21:58 -0500, Tom Lane wrote: The old Gen_fmgrtab.sh script used temporary file names that included its process PID. It had this comment about that: # We use the temporary files to avoid problems with concurrent runs # (which can happen during parallel make). The new implementation uses temp files that just have .tmp appended to the target file name. If there is a risk that make -j will run the same action twice in parallel, this isn't good enough. While it wouldn't be too tough to add the PID to the scripts, I wonder whether this comment is about a real problem or just a flight of fancy. It doesn't seem to me that parallel make ought to be stupid enough to do the same action twice. Anybody know? When you have only one makefile, this shouldn't happen if the rules are written correctly. But when the parallel make is initiated from the top, plus a decade-old buggy gmake, anything can happen. :-/ It's probably worth the small extra effort to be robust against this when the alternative is possible slightly butchered catalog files. -- Sent 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_migrator issues
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Alvaro Herrera wrote: I thought it was impossible to use bare mountpoints as tablespaces due to ownership problems ... Is that not the case? -1 for special hacks that work around bogus setups, if that means intrusive changes to the core code. I talked to the person who reported the problem and he and I confirmed that it is quite easy to make the mount point be owned by the postgres user and have that function as a tablespace. Is that not a supported setup? It might be *possible*, but that doesn't make it a good idea. The traditional sysadmin advice in this area is that mount points should be owned by root. I don't really remember the reasoning but I'm pretty sure I remember the principle. Yea, I think the logic is that files under that directory disappear after the mount, so you don't want users putting things in there accidentally. In fact, the user said they mount a pg_xlog directory under the $PGDATA directory (rather than use a symlink), which I also thought was an odd approach and prone to problems if the mount failed intermittently. It was actually Tom's idea months ago to put a version-specific directory in the tablespace. I was just about to re-suggest that. Why do you think it's such a bad idea? I liked the idea, but I listed it as item #2 and no one else said they liked it. The only complexity I can see with the idea is that doing an upgrade from one alpha to another would have the same major version number and would therefore not be possible, so maybe we have to use the catalog version number in there. -- 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] execute sql commands in core
Robert Haas robertmh...@gmail.com writes: On Tue, Jan 5, 2010 at 12:56 AM, black light blacklight1...@gmail.com wrote: In fact, i want to add a table to system catalog and SELECT/UPDATE it from auth.c. If you are considering proposing your patch for inclusion in the PostgreSQL upstream sources, you should first discuss why you are trying to do this and what you hope to get out of it. There might be a way to do whatever it is you are trying to do without modifying the source code. One point worth noting is that there is precisely zero hope of making that work in any existing release series, because auth.c doesn't run inside a transaction. You could probably make it work with recent CVS HEAD though (ie, since we pulled the plug on the flat auth file). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Remove too-smart-for-its-own-good optimization of not overwriting
On mån, 2010-01-04 at 22:54 -0500, Robert Haas wrote: I think you're dismissing the idea too cavalierly. If A generates B, A is inevitably changed frequently, but the changes to A affect B only rarely, this is a good trick. If that is the case, you might want to consider splitting up A or refactoring B so you don't have so many useless dependencies. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Does parallel make require guards against duplicate actions?
Peter Eisentraut pete...@gmx.net writes: On mån, 2010-01-04 at 21:58 -0500, Tom Lane wrote: The new implementation uses temp files that just have .tmp appended to the target file name. If there is a risk that make -j will run the same action twice in parallel, this isn't good enough. While it wouldn't be too tough to add the PID to the scripts, I wonder whether this comment is about a real problem or just a flight of fancy. It doesn't seem to me that parallel make ought to be stupid enough to do the same action twice. Anybody know? When you have only one makefile, this shouldn't happen if the rules are written correctly. But when the parallel make is initiated from the top, plus a decade-old buggy gmake, anything can happen. :-/ It's probably worth the small extra effort to be robust against this when the alternative is possible slightly butchered catalog files. OK, I'll go fix 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: I: [HACKERS] TODO: Allow substring/replace() to get/set bit values
As you say, there's really no point in changing the internal representation, and if you don't find replace() useful either, then why are you even working on this at all? I would like a get_bit / set_bit for bit strings, as I find them useful. get_bit could be a simple call to substring, but there's no way of doing a set_bit on a bit string as far as I know. I don't like the replace syntax for bit strings since it won't give you the same functionality of set_bit, plus I don't really see how someone would want to look for a bit string and replace it with another bit string. But I see that someone might want to overlay a bit string with another (this is different from replace since you have to tell the position where the replacing would start, instead of looking for a bit string). To sum up: 1) a new function, get_bit, that calls substring 2) a new function, overlay, that replaces bits (starting at a certain position) 3) a new function, set_bit, that calls overlay Since the latest discussion of this is more than five years old, it's unclear that anyone even cares any more. It seems to me that making replace overlay a substring of bits could be a reasonable thing to do, but if nobody actually wants it, then the simplest thing to do is remove this from the TODO and call it good. I understand: it would be both a useful feature to me and a way to start coding postgres. But, of course, if there's no interest, I'll pass... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] We no longer have a fallback for machines without working int64
As pointed out here http://archives.postgresql.org/pgsql-general/2010-01/msg00145.php the current zic code doesn't cope gracefully with lack of working int64. Considering the trouble we've gone to throughout the rest of the system to support such compilers, it's a bit annoying to have this little detail break it. On the other hand, it's unclear that anybody still cares. (Other than people running SCO Openserver, for whom I have little sympathy anyway.) Thoughts? Is it worth expending any energy on? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issues
Bruce Momjian br...@momjian.us writes: I liked the idea, but I listed it as item #2 and no one else said they liked it. The only complexity I can see with the idea is that doing an upgrade from one alpha to another would have the same major version number and would therefore not be possible, so maybe we have to use the catalog version number in there. Good point. Using catversion for the purpose seems a bit ugly but I have no better ideas. 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] We no longer have a fallback for machines without working int64
Tom Lane wrote: As pointed out here http://archives.postgresql.org/pgsql-general/2010-01/msg00145.php the current zic code doesn't cope gracefully with lack of working int64. Considering the trouble we've gone to throughout the rest of the system to support such compilers, it's a bit annoying to have this little detail break it. On the other hand, it's unclear that anybody still cares. (Other than people running SCO Openserver, for whom I have little sympathy anyway.) Thoughts? Is it worth expending any energy on? Yeah, I'd say this much: #ifdef INT64_IS_BUSTED #error unsupported platform #endif -- 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: I: [HACKERS] TODO: Allow substring/replace() to get/set bit values
On Tue, Jan 5, 2010 at 10:45 AM, Leonardo F m_li...@yahoo.it wrote: As you say, there's really no point in changing the internal representation, and if you don't find replace() useful either, then why are you even working on this at all? I would like a get_bit / set_bit for bit strings, as I find them useful. get_bit could be a simple call to substring, but there's no way of doing a set_bit on a bit string as far as I know. I don't like the replace syntax for bit strings since it won't give you the same functionality of set_bit, plus I don't really see how someone would want to look for a bit string and replace it with another bit string. But I see that someone might want to overlay a bit string with another (this is different from replace since you have to tell the position where the replacing would start, instead of looking for a bit string). To sum up: 1) a new function, get_bit, that calls substring 2) a new function, overlay, that replaces bits (starting at a certain position) 3) a new function, set_bit, that calls overlay That seems reasonable to me. Not sure what others think. Since the latest discussion of this is more than five years old, it's unclear that anyone even cares any more. It seems to me that making replace overlay a substring of bits could be a reasonable thing to do, but if nobody actually wants it, then the simplest thing to do is remove this from the TODO and call it good. I understand: it would be both a useful feature to me and a way to start coding postgres. But, of course, if there's no interest, I'll pass... I wouldn't jump to that conclusion. I just wasn't sure what you were trying to do, but it's more clear now. ...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_migrator issues
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I liked the idea, but I listed it as item #2 and no one else said they liked it. The only complexity I can see with the idea is that doing an upgrade from one alpha to another would have the same major version number and would therefore not be possible, so maybe we have to use the catalog version number in there. Good point. Using catversion for the purpose seems a bit ugly but I have no better ideas. I thought we had rejected the idea of being able to migrate between alphas. Is migrating between major versions not difficult enough? -- 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] pg_migrator issues
On Tue, Jan 5, 2010 at 11:06 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I liked the idea, but I listed it as item #2 and no one else said they liked it. The only complexity I can see with the idea is that doing an upgrade from one alpha to another would have the same major version number and would therefore not be possible, so maybe we have to use the catalog version number in there. Good point. Using catversion for the purpose seems a bit ugly but I have no better ideas. I thought we had rejected the idea of being able to migrate between alphas. Is migrating between major versions not difficult enough? We like a challenge. ...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_migrator issues
Robert Haas robertmh...@gmail.com writes: On Tue, Jan 5, 2010 at 11:06 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Tom Lane wrote: Good point. Using catversion for the purpose seems a bit ugly but I have no better ideas. I thought we had rejected the idea of being able to migrate between alphas. Is migrating between major versions not difficult enough? We like a challenge. The problem with using just major version there is that then we are *wiring into the on-disk representation* the assumption that pg_migrator only goes from one major version to the next. I agree that we're not likely to start supporting cross-alpha-version migration any time soon, but I don't think it's wise to foreclose the possibility of ever doing it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] We no longer have a fallback for machines without working int64
On Tue, Jan 05, 2010 at 10:47:33AM -0500, Tom Lane wrote: As pointed out here http://archives.postgresql.org/pgsql-general/2010-01/msg00145.php the current zic code doesn't cope gracefully with lack of working int64. Considering the trouble we've gone to throughout the rest of the system to support such compilers, it's a bit annoying to have this little detail break it. On the other hand, it's unclear that anybody still cares. (Other than people running SCO Openserver, for whom I have little sympathy anyway.) Thoughts? There was a use case for supporting non-working int64, but reality has changed. Is it worth expending any energy on? Not IMHO. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator issues
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Jan 5, 2010 at 11:06 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Tom Lane wrote: Good point. ?Using catversion for the purpose seems a bit ugly but I have no better ideas. I thought we had rejected the idea of being able to migrate between alphas. ?Is migrating between major versions not difficult enough? We like a challenge. The problem with using just major version there is that then we are *wiring into the on-disk representation* the assumption that pg_migrator only goes from one major version to the next. I agree that we're not likely to start supporting cross-alpha-version migration any time soon, but I don't think it's wise to foreclose the possibility of ever doing it. I know people are trying to make things easier on pg_migrator, but frankly going from alpha to alpha does not require any new code in pg_migrator. And pg_migrator already supports cross-alpha-version migration just using the existing code --- no new code was added to enable this. -- 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] krb_server_keyfile setting doesn't work on Windows
Magnus Hagander wrote: On Thu, Dec 31, 2009 at 00:57, Magnus Hagander mag...@hagander.net wrote: 2009/12/31 Hiroshi Inoue in...@tpf.co.jp: Magnus Hagander wrote: 2009/12/30 Hiroshi Inoue in...@tpf.co.jp: Hi, As far as I tested, the krb_server_keyfile setting in postgres.conf doesn't work on Windows. Because gssapi32.dll(krb5_32.dll) seems to call getenv(KRB5_KTNAME) in msvcr71, postgres.exe should call putenv(KRB5_KTNAME=...) in msvcr71. The attached patch fixes the problem in my test case. Isn't the main backend linked with msvcr71.dll anyway? What main backend directly links is msvcr80 or msvcr90 according to the msvc build environment. Arrgh. My bad, I thought msvcr71 was vs2005. Now that you put it like this, I know it's vs2003. Then the regular putenv should put it in th eenv of msvcr71.dll, and the stuff that's wrapped through src/port/win32env.c will put it in the regular msvcr file. I wonder if you're possibly being hit with the bug I patched the other day, but didn't backpatch. (http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=f8bcd7220b1166f7c037ceaf0a53958cbc6a7630). Can you see if that fix solves your problem as well? (Either directly or by testing HEAD) I'm testing using the current cvs. If not, the fix should still go in win32env.c, not directly in auth.c I don't object to it. Possibly we would have to add msvcr80 or msvcr90 as well in the future. To be safe, yes, we should have that. Do you want to work on such a complete solution, or should I look at it? Mail to you seem not to be properly delivered atm :-( Hopefully you can read this through the list. Sorry for the delay - I've been on new year holiday in Japan. I've applied a patch that should fix this problem, by always updating *all* available msvcrt libraries. Please check that it solves your problem as well. I checked the behavior using the current cvs and it works well. Thanks. regards, Hiroshi Inoue -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Testing with concurrent sessions
Markus Wanner mar...@bluegap.ch wrote: Kevin Grittner wrote: It's very soon going to be critical that I be able to test particular interleavings of statements in particular concurrent transaction sets to be able to make meaningful progress on the serializable transaction work. I've something in place for Postgres-R, as I also need to test concurrent transactions there. It's based on python/twisted and is able to start multiple Postgres instances (as required for testing replication) and query them concurrently (as you seem to need as well). It uses an asynchronous event loop (from twisted) and basically controls processes, issues queries and checks results and ordering constraints (e.g. transaction X must commit and return a result before transaction Y). Where would I find this (and any related documentation)? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG SQLDA support
On Mon, Jan 04, 2010 at 07:39:14PM +0100, Boszormenyi Zoltan wrote: new patch attached. ... Great job Zoltan, committed. The sqlda.h header switches between the two different structures depending on a new #define introduced in and added to the generated C source by the preprocessor I changed this to use the already existing _ECPG_INFORMIX_H define. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org VfL Borussia! Forca Barca! Go SF 49ers! Use: Debian GNU/Linux, PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG SQLDA support
On Mon, Jan 04, 2010 at 02:32:56PM -0500, Tom Lane wrote: I think checking SIZEOF_LONG would be preferred, since that's what we use elsewhere. Although actually I wonder why this code exists at all --- wouldn't it be easier to make these depend on int64? It does use int64. However, ecpg uses HAVE_LONG_LONG_INT64 to decide whether it's datatype ECPGt_long_long exists. I changed the patch to use the same define as usual. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org VfL Borussia! Forca Barca! Go SF 49ers! Use: Debian GNU/Linux, PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq naming on Win64
On Tue, Jan 5, 2010 at 3:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dave Page dp...@pgadmin.org writes: After chatting with Magnus, we feel that a good solution would be to rename libpq on Win64 to libpq64.dll to distinguish it from the 32 bit equivalent. Isn't that going to break applications? Where by break I mean have to explicitly link with 'libpq64', thereby rendering them unportable to any other platform. I'm really not concerned about that - a build rule to link with the right library based on pointer size is trivial. I would have thought Microsoft would have a better solution than this for managing 64-bit libraries. Or am I too optimistic about Redmond's competence? They have two separate installation directories for 32 and 64 bit packages. With PostgreSQL though, we'll quite possibly be shipping both 32 and 64 bit components in the same installer, and thus going into the same installation directory. We may have no choice about that, as we can't force all the dependent libraries to add 64 bit support when we need it. -- 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] Writeable CTEs
On Tue, Jan 5, 2010 at 4:42 PM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: = with t as (delete from foo returning *) - insert into bar - select * from t; INSERT 0 2 It correctly reports 2 affected rows (one deleted and one inserted), but is this the answer we want? It doesn't seem all that useful to know the total amount of affected rows. My first thought is that the number should correspond to the INSERT. It didn't INSERT two rows so it seems wrong. More importantly in a case like with t as (delete from foo returning *) select * from t where x=? applications will almost certainly expect the number to match the actual number of rows returned and may well misbehave if they don't. -- greg -- 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: Get rid of the need for manual maintenance of the initial
Tom Lane wrote: Log Message: --- Get rid of the need for manual maintenance of the initial contents of pg_attribute, by having genbki.pl derive the information from the various catalog header files. This greatly simplifies modification of the bootstrapped catalogs. This patch finally kills genbki.sh and Gen_fmgrtab.sh; we now rely entirely on Perl scripts for those build steps. To avoid creating a Perl build dependency where there was not one before, the output files generated by these scripts are now treated as distprep targets, ie, they will be built and shipped in tarballs. But you will need a reasonably modern Perl (probably at least 5.6) if you want to build from a CVS pull. this broke the build on spoonbill: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=spoonbilldt=2010-01-05%2015:05:08 manually executing the command shows that the perl process eats more than 250MB of RAM at closely afterwards fails with an out of memory due to hitting the process limit on that box. I don't think that is in any way sane :) # perl -v This is perl, v5.8.8 built for sparc64-openbsd 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] Writeable CTEs
On Tue, Jan 05, 2010 at 05:21:12PM +, Greg Stark wrote: On Tue, Jan 5, 2010 at 4:42 PM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: = with t as (delete from foo returning *) - insert into bar - select * from t; INSERT 0 2 It correctly reports 2 affected rows (one deleted and one inserted), but is this the answer we want? It doesn't seem all that useful to know the total amount of affected rows. My first thought is that the number should correspond to the INSERT. It didn't INSERT two rows so it seems wrong. More importantly in a case like with t as (delete from foo returning *) select * from t where x=? applications will almost certainly expect the number to match the actual number of rows returned and may well misbehave if they don't. I'm not sure how relevant this could be, as existing apps can't use future functionality. We have precedents with RULEs, which can make the arguments pretty meaningless. In some future version, we may want to redo the infrastructure to support modified values for multiple statements, but for now, that seems like an unnecessary frill. 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] [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: this broke the build on spoonbill: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=spoonbilldt=2010-01-05%2015:05:08 manually executing the command shows that the perl process eats more than 250MB of RAM at closely afterwards fails with an out of memory due to hitting the process limit on that box. I don't think that is in any way sane :) Bizarre. Gen_fmgrtab.pl doesn't eat anywhere near that much RAM here. Anybody else seeing the same? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
On Tue, Jan 5, 2010 at 12:24 PM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: Tom Lane wrote: Log Message: --- Get rid of the need for manual maintenance of the initial contents of pg_attribute, by having genbki.pl derive the information from the various catalog header files. This greatly simplifies modification of the bootstrapped catalogs. This patch finally kills genbki.sh and Gen_fmgrtab.sh; we now rely entirely on Perl scripts for those build steps. To avoid creating a Perl build dependency where there was not one before, the output files generated by these scripts are now treated as distprep targets, ie, they will be built and shipped in tarballs. But you will need a reasonably modern Perl (probably at least 5.6) if you want to build from a CVS pull. this broke the build on spoonbill: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=spoonbilldt=2010-01-05%2015:05:08 manually executing the command shows that the perl process eats more than 250MB of RAM at closely afterwards fails with an out of memory due to hitting the process limit on that box. I don't think that is in any way sane :) # perl -v This is perl, v5.8.8 built for sparc64-openbsd I just tried this with ulimit -v 131072 and it worked. At 65536 and 32768 it emited an error about being unable to set the locale but still seemed to run. At 32768 it couldn't load all its shared libraries any more so it croaked, but with a different error message. Can we get the output of ulimit -a on that machine? Is there by any chance some other, conflicting Catalog.pm on that machine? ...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] Writeable CTEs
On 2010-01-05 19:21 +0200, Greg Stark wrote: with t as (delete from foo returning *) select * from t where x=? applications will almost certainly expect the number to match the actual number of rows returned and may well misbehave if they don't. I probably wasn't clear about the actual problem in the original post. The problem only affects INSERT, UDPATE and DELETE where you are actually counting affected rows (i.e. PQcmdTuples(), not PQntuples()) so the this example would work as expected. Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Robert Haas wrote: On Tue, Jan 5, 2010 at 12:24 PM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: Tom Lane wrote: Log Message: --- Get rid of the need for manual maintenance of the initial contents of pg_attribute, by having genbki.pl derive the information from the various catalog header files. This greatly simplifies modification of the bootstrapped catalogs. This patch finally kills genbki.sh and Gen_fmgrtab.sh; we now rely entirely on Perl scripts for those build steps. To avoid creating a Perl build dependency where there was not one before, the output files generated by these scripts are now treated as distprep targets, ie, they will be built and shipped in tarballs. But you will need a reasonably modern Perl (probably at least 5.6) if you want to build from a CVS pull. this broke the build on spoonbill: http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=spoonbilldt=2010-01-05%2015:05:08 manually executing the command shows that the perl process eats more than 250MB of RAM at closely afterwards fails with an out of memory due to hitting the process limit on that box. I don't think that is in any way sane :) # perl -v This is perl, v5.8.8 built for sparc64-openbsd I just tried this with ulimit -v 131072 and it worked. At 65536 and 32768 it emited an error about being unable to set the locale but still seemed to run. At 32768 it couldn't load all its shared libraries any more so it croaked, but with a different error message. Can we get the output of ulimit -a on that machine? $ ulimit -a time(cpu-seconds)unlimited file(blocks) unlimited coredump(blocks) unlimited data(kbytes) 524288 stack(kbytes)4096 lockedmem(kbytes)334589 memory(kbytes) 1000456 nofiles(descriptors) 128 processes64 Is there by any chance some other, conflicting Catalog.pm on that machine? as I said I can reproduce it manually withe the Catalog.pm from the failing build as well. I can succeed building it using the root account but that runs the box more or less out of memory as it eats up to ~550MB RSS and 990MB of SIZE... 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] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Robert Haas robertmh...@gmail.com writes: Is there by any chance some other, conflicting Catalog.pm on that machine? Even if there is, shouldn't the use of perl -I ensure our version gets loaded? Also, Stefan's log shows that it got through genbki.pl, so whatever the problem is, I don't think it's Catalog.pm's fault. 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] Stats for inheritance trees
On Tue, Dec 29, 2009 at 9:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Yep. It would also lower the barrier to future innovations of that type, which would be a good thing, IMO. Unfortunately we'd likely need to continue to support the existing syntax at least for attstattarget, which is kind of a bummer, but seems managable. I think we could throw over the syntax for ALTER TABLE ... ADD STATISTICS DISTINCT since it is an 8.5-ism. Yeah --- if we think we might want to do this, now is the time, before we're stuck with supporting that syntax. (I was thinking earlier today that attdistinct was already in 8.4, but it's not.) I am just starting to look at this now. One of the questions I have is what we should call the options. We could call the regular options something like ndistinct or distinct, but I'm not too sure what to call the for-inheritance-trees version of that. I suppose we could just use the familiar inh prefix and call it inhndistinct, but that looks suspiciously like gobbledygook. Someone's understanding of just what that is supposed to mean might be a little... indistinct (ba dum). Another option would be to call it inherits_ndistinct, or something like that, which seems a little more readable, but not great: I don't think there's going to be any getting around the need to RTFM before setting these parameters. In terms of syntax, I'm thinking something like: ALTER TABLE name ALTER COLUMN column SET ( column_parameter = value [, ...] ) I am also very tempted before beginning this work to rename reloptions.c to options.c or genoptions.c or somesuch. If we're going to use it for relations, attributes, and tablespaces, chances are good we're going to use it for other things, too. The FDW stuff is already borrowing from it as well. ...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] Stats for inheritance trees
Robert Haas robertmh...@gmail.com writes: Another option would be to call it inherits_ndistinct, or something like that, which seems a little more readable, but not great: I don't think there's going to be any getting around the need to RTFM before setting these parameters. Well, the previously agreed-to syntax was SET STATISTICS DISTINCT. I don't see a problem with using distinct and inherited_distinct or something like that. ndistinct is probably not a good name to expose to non-programmers. The must-RTFM argument is fairly weak, though, since these are knobs that only advanced users would twiddle 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] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
On Tue, Jan 5, 2010 at 12:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Is there by any chance some other, conflicting Catalog.pm on that machine? Even if there is, shouldn't the use of perl -I ensure our version gets loaded? I would certainly think so. Also, Stefan's log shows that it got through genbki.pl, so whatever the problem is, I don't think it's Catalog.pm's fault. Beats me. I don't have any other ideas at the moment. ...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] Stats for inheritance trees
On Tue, Jan 5, 2010 at 1:00 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Another option would be to call it inherits_ndistinct, or something like that, which seems a little more readable, but not great: I don't think there's going to be any getting around the need to RTFM before setting these parameters. Well, the previously agreed-to syntax was SET STATISTICS DISTINCT. I don't see a problem with using distinct and inherited_distinct or something like that. ndistinct is probably not a good name to expose to non-programmers. I like ndistinct because it makes the whole thing sound related to statistics, which it is. But I'll do it your way unless there are other votes. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Proposal: XML helper functions
One of the problem with shredding XML is that it is very kludgy to get a
scalar value back from xpath. The xpath function always returns an array
of XML. So for example, to extract a numeric value you need to:
1) use xpath to get the node
2) get the first element of the XML array
3) cast that to varchar
4) cast that to numeric
So I wrote the following function:
CREATE OR REPLACE FUNCTION xmlvalue(
VARCHAR,
XML
) RETURNS TEXT AS
$$
SELECT CASE WHEN $1 ~ '@[[:alnum:]_]+$'
THEN (xpath($1, $2))[1]
WHEN $1 ~* '/text()$'
THEN (xpath($1, $2))[1]
WHEN $1 LIKE '%/'
THEN (xpath($1 || 'text()', $2))[1]
ELSE (xpath($1 || '/text()', $2))[1]
END::text;
$$ LANGUAGE 'sql' IMMUTABLE;
It's pretty simple. It just does a check to see if you are extracting an
attribute or an element and if element, it makes sure to get the text value.
So query that used to look like:
SELECT CAST(
CAST(
(xpath('/foo/bar/text()', myxml))[1]
AS varchar)
AS numeric) AS bar
now becomes:
SELECT xmlvalue('/foo/bar/text(), myxml)::numeric AS bar
Second function just checks that the xpath expression finds at least one
node.
CREATE OR REPLACE FUNCTION xmlexists(
VARCHAR,
XML
) RETURNS BOOLEAN AS
$$
SELECT CASE WHEN array_upper(xpath($1, $2), 1) 0
THEN true ELSE false END;
$$ LANGUAGE 'sql' IMMUTABLE;
On naming, SQL/XML specifies xmlexists and xmlcast. Latest db2 provides
xmlcast(), Oracle has equivalent extractvalue() function, MSSQL uses
xml.value(). The xmlvalue does only part of what is required by xmlcast
(it won't cast scalar to xml).
So would these functions need to be rewritten in c in order to be accepted?
Regards,
Scott Bailey
Further reading:
http://scottrbailey.wordpress.com/2009/06/19/xml-parsing-postgres/
http://en.wikibooks.org/wiki/SQL_Dialects_Reference/SQL_XML
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions052.htm#i1131042
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0023486.htm
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Robert Haas robertmh...@gmail.com writes: Beats me. I don't have any other ideas at the moment. Stefan, could you try adding some debugging printouts to the Gen_fmgrtab.pl script to see how far it gets before blowing up? 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] Stats for inheritance trees
On Tue, Jan 5, 2010 at 1:09 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Jan 5, 2010 at 1:00 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Another option would be to call it inherits_ndistinct, or something like that, which seems a little more readable, but not great: I don't think there's going to be any getting around the need to RTFM before setting these parameters. Well, the previously agreed-to syntax was SET STATISTICS DISTINCT. I don't see a problem with using distinct and inherited_distinct or something like that. ndistinct is probably not a good name to expose to non-programmers. I like ndistinct because it makes the whole thing sound related to statistics, which it is. But I'll do it your way unless there are other votes. It's probably also worth noting that the reason I used DISTINCT originally is because it's already a keyword. That's a moot point here. But as I say I'll stick with your names unless there are contravening votes. ...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] Stats for inheritance trees
Robert Haas robertmh...@gmail.com writes: It's probably also worth noting that the reason I used DISTINCT originally is because it's already a keyword. True. It occurs to me that the pg_stats view already exposes n_distinct as a column name. I wouldn't object to using n_distinct and n_distinct_inherited or some such. 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] Stats for inheritance trees
On Tue, Jan 5, 2010 at 1:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: It's probably also worth noting that the reason I used DISTINCT originally is because it's already a keyword. True. It occurs to me that the pg_stats view already exposes n_distinct as a column name. I wouldn't object to using n_distinct and n_distinct_inherited or some such. OK. So we have: 1. distinct and inherited_distinct, or 2. n_distinct and n_distinct_inherited Any other votes/thoughts/opinions/color commentary? ...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] Proposal: XML helper functions
On Tue, Jan 5, 2010 at 1:14 PM, Scott Bailey arta...@comcast.net wrote: One of the problem with shredding XML is that it is very kludgy to get a scalar value back from xpath. The xpath function always returns an array of XML. So for example, to extract a numeric value you need to: 1) use xpath to get the node 2) get the first element of the XML array 3) cast that to varchar 4) cast that to numeric I just happen to be dealing with XML right now as well and my initial thought is that your suggestion doesn't buy you a whole lot: the root problem IMO is not dealing with what xpath gives you but that there is no DOMish representation of the xml document for you to query. You have to continually feed the entire document to xpath which is absolutely not scalable (if it works the way I think it does -- haven't looked at the code). xpath is great for simple things but it's too texty and you need a more robust API to handle documents for serious parsing on the backend. In the short term i'd advise doing work in another pl like perl. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: XML helper functions
2010/1/5 Scott Bailey arta...@comcast.net:
One of the problem with shredding XML is that it is very kludgy to get a
scalar value back from xpath. The xpath function always returns an array of
XML. So for example, to extract a numeric value you need to:
1) use xpath to get the node
2) get the first element of the XML array
3) cast that to varchar
4) cast that to numeric
So I wrote the following function:
CREATE OR REPLACE FUNCTION xmlvalue(
VARCHAR,
XML
) RETURNS TEXT AS
$$
SELECT CASE WHEN $1 ~ '@[[:alnum:]_]+$'
THEN (xpath($1, $2))[1]
WHEN $1 ~* '/text()$'
THEN (xpath($1, $2))[1]
WHEN $1 LIKE '%/'
THEN (xpath($1 || 'text()', $2))[1]
ELSE (xpath($1 || '/text()', $2))[1]
END::text;
$$ LANGUAGE 'sql' IMMUTABLE;
It's pretty simple. It just does a check to see if you are extracting an
attribute or an element and if element, it makes sure to get the text value.
So query that used to look like:
SELECT CAST(
CAST(
(xpath('/foo/bar/text()', myxml))[1]
AS varchar)
AS numeric) AS bar
now becomes:
SELECT xmlvalue('/foo/bar/text(), myxml)::numeric AS bar
Second function just checks that the xpath expression finds at least one
node.
CREATE OR REPLACE FUNCTION xmlexists(
VARCHAR,
XML
) RETURNS BOOLEAN AS
$$
SELECT CASE WHEN array_upper(xpath($1, $2), 1) 0
THEN true ELSE false END;
$$ LANGUAGE 'sql' IMMUTABLE;
On naming, SQL/XML specifies xmlexists and xmlcast.
I am for SQL/XML naming convention.
Regards
Pavel Stehule
Latest db2 provides
xmlcast(), Oracle has equivalent extractvalue() function, MSSQL uses
xml.value(). The xmlvalue does only part of what is required by xmlcast (it
won't cast scalar to xml).
So would these functions need to be rewritten in c in order to be accepted?
Regards,
Scott Bailey
Further reading:
http://scottrbailey.wordpress.com/2009/06/19/xml-parsing-postgres/
http://en.wikibooks.org/wiki/SQL_Dialects_Reference/SQL_XML
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions052.htm#i1131042
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0023486.htm
--
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] Writeable CTEs
Marko Tiikkaja marko.tiikk...@cs.helsinki.fi writes: = with t as (delete from foo returning *) - insert into bar - select * from t; INSERT 0 2 It correctly reports 2 affected rows (one deleted and one inserted), but is this the answer we want? No. The returned tag should consider only the top-level operation, not what happened inside any CTEs. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Tom Lane wrote:
Robert Haas robertmh...@gmail.com writes:
Beats me. I don't have any other ideas at the moment.
Stefan, could you try adding some debugging printouts to the
Gen_fmgrtab.pl script to see how far it gets before blowing up?
did that and it seems the problem is in the loop that does:
foreach my $row (@$data)
{
# To construct fmgroids.h and fmgrtab.c, we need to inspect some
# of the individual data fields. Just splitting on whitespace
# won't work, because some quoted fields might contain internal
# whitespace. We handle this by folding them all to a simple
# xxx. Fortunately, this script doesn't need to look at any
# fields that might need quoting, so this simple hack is
# sufficient.
...
}
it does after around 1050 iterations of that loop at it seems to leak
exactly 240kbyte per iteration which sums up to around 250MB in total
for the process...
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] Somebody has broken autovacuum's abort path
On Tue, 2010-01-05 at 03:09 -0500, Tom Lane wrote: I think this can likely be blamed on the HS changes in transaction abort, since I'm not aware of any other recent changes near here. I'll take a look. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New VACUUM FULL
On Mon, 2010-01-04 at 08:04 +, Simon Riggs wrote: I would prefer this slightly modified version 1. Commit your patch, as-is (you/me) I assume this is OK with you now? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes:
did that and it seems the problem is in the loop that does:
foreach my $row (@$data)
{
# To construct fmgroids.h and fmgrtab.c, we need to inspect some
# of the individual data fields. Just splitting on whitespace
Huh. It's weird that I don't see a leak in either 5.8.7 or 5.10.1,
which are the two closest perl versions I have handy here. I think
this may be a platform-specific Perl bug. Still, it would be nice
to work around it if we can.
I'm not nearly good enough in Perl to be sure about the semantics
of this loop. Is it possible that it's changing the global contents
of the @$data structure, rather than just hacking a local copy of
each row before pushing some values into @fmgr?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Stefan Kaltenbrunner escribió:
foreach my $row (@$data)
{
# To construct fmgroids.h and fmgrtab.c, we need to inspect some
# of the individual data fields. Just splitting on whitespace
# won't work, because some quoted fields might contain internal
# whitespace. We handle this by folding them all to a simple
# xxx. Fortunately, this script doesn't need to look at any
# fields that might need quoting, so this simple hack is
# sufficient.
...
}
it does after around 1050 iterations of that loop at it seems to
leak exactly 240kbyte per iteration which sums up to around 250MB in
total for the process...
Hmm, is this running some old Perl version? Perhaps it's not freeing
memory timely ... maybe unsetting/deleting $row after each iteration?
--
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] Testing with concurrent sessions
Hi, Kevin Grittner wrote: Where would I find this (and any related documentation)? Sorry, if that didn't get clear. I'm trying to put together something I can release real soon now (tm). I'll keep you informed. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: XML helper functions
Merlin Moncure wrote:
On Tue, Jan 5, 2010 at 1:14 PM, Scott Bailey arta...@comcast.net wrote:
One of the problem with shredding XML is that it is very kludgy to get a
scalar value back from xpath. The xpath function always returns an array of
XML. So for example, to extract a numeric value you need to:
1) use xpath to get the node
2) get the first element of the XML array
3) cast that to varchar
4) cast that to numeric
I just happen to be dealing with XML right now as well and my initial
thought is that your suggestion doesn't buy you a whole lot: the root
problem IMO is not dealing with what xpath gives you but that there is
no DOMish representation of the xml document for you to query. You
have to continually feed the entire document to xpath which is
absolutely not scalable (if it works the way I think it does --
haven't looked at the code).
No typically you'll only be passing the xml for a single row so what
we end up doing in Postgres typically looks something like this:
SELECT xmlvalue('/row/@id', bitesizexml)::int AS id,
xmlvalue('/row/@lat', bitesizexml)::numeric AS lat,
xmlvalue('/row/@lon', bitesizexml)::numeric,
xmlvalue('/row/comment', bitesizexml) AS cmt
FROM (
SELECT unnest(xpath('/foo/row', mybigxmldoc)) AS bitesizexml
) sub
So only the one call has to work with the entire document. All the calls
to xmlvalue are passed a much smaller node to work with.
xpath is great for simple things but it's too texty and you need a
more robust API to handle documents for serious parsing on the
backend. In the short term i'd advise doing work in another pl like
perl.
This is basically the method used for Oracle too until they provided
XMLTable functionality. They had a function xmlsequence that basically
did the unnest(xpath()) part. Hopefully we'll get xmltable support soon.
Scott
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] true serializability and predicate locking
I have a question regarding true serializability and predicate locking.
There's some context on the wiki page:
http://wiki.postgresql.org/wiki/Serializable
under the heading Predicate Locking.
If you have the following DDL:
create table mytable(mycircle circle);
create index mytable_mycircle_idx on mytable
using gist (mycircle);
and two transactions:
T1:
BEGIN;
SELECT * FROM mytable WHERE mycircle '(0, 0), 10';
-- if any rows are returned, ROLLBACK
INSERT INTO mytable(mycircle) VALUES('(0, 0), 10');
COMMIT;
T2:
BEGIN;
SELECT * FROM mytable WHERE mycircle '(5, 5), 5';
-- if any rows are returned, ROLLBACK
INSERT INTO mytable(mycircle) VALUES('(5, 5), 5');
COMMIT;
Clearly one of those transactions should abort, because that will happen
in either serialized order. But I don't see where any lock is stored,
nor how the conflict is detected.
There has been a lot of theoretical discussion on this matter, but I'd
like to know how it will work in this specific case. You can't merely
lock a few index pages, because the INSERT might put the tuple in
another page.
I'm still trying to catch up on this discussion as well as relevant
papers, but this question has been on my mind.
One approach that might work for GiST is to get some kind of lock
(SIREAD?) on the predicates for the pages that the search does not
match. That way, the conflict can be detected if an INSERT tries to
update the predicate of a page to something that the search may have
matched.
If the index was GIN instead of GiST, I think the fastupdate feature
would cause a problem, though (as Greg brought up). Fastupdate may need
to be disabled when using truly serializable transactions.
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] Proposal: XML helper functions
On tis, 2010-01-05 at 10:14 -0800, Scott Bailey wrote: One of the problem with shredding XML is that it is very kludgy to get a scalar value back from xpath. The xpath function always returns an array of XML. So for example, to extract a numeric value you need to: 1) use xpath to get the node 2) get the first element of the XML array 3) cast that to varchar 4) cast that to numeric There has been talk about adding something like xpath_string, xpath_number, xpath_boolean for fetching xpath expressions that don't return nodesets. I think that would fit your use case. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq naming on Win64
On tis, 2010-01-05 at 16:48 +, Dave Page wrote: On Tue, Jan 5, 2010 at 3:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: I would have thought Microsoft would have a better solution than this for managing 64-bit libraries. Or am I too optimistic about Redmond's competence? They have two separate installation directories for 32 and 64 bit packages. With PostgreSQL though, we'll quite possibly be shipping both 32 and 64 bit components in the same installer, and thus going into the same installation directory. Can't the installer install things into two separate directories? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Tom Lane wrote:
Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes:
did that and it seems the problem is in the loop that does:
foreach my $row (@$data)
{
# To construct fmgroids.h and fmgrtab.c, we need to inspect some
# of the individual data fields. Just splitting on whitespace
Huh. It's weird that I don't see a leak in either 5.8.7 or 5.10.1,
which are the two closest perl versions I have handy here. I think
this may be a platform-specific Perl bug. Still, it would be nice
to work around it if we can.
yeah it is probably some strange platform specific issue - however with
some help from the IRC channel I came up with the following patch that
considerable reduces the memory bloat (but still needs ~55MB max) and
allows the build to succeed.
I'm not nearly good enough in Perl to be sure about the semantics
of this loop. Is it possible that it's changing the global contents
of the @$data structure, rather than just hacking a local copy of
each row before pushing some values into @fmgr?
hmm it is leaking a constant amount of 240kbyte per loop iteration with
the original code but yeah very weird issue...
Stefan
Index: src/backend/utils/Gen_fmgrtab.pl
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/Gen_fmgrtab.pl,v
retrieving revision 1.4
diff -u -r1.4 Gen_fmgrtab.pl
--- src/backend/utils/Gen_fmgrtab.pl 5 Jan 2010 01:06:56 - 1.4
+++ src/backend/utils/Gen_fmgrtab.pl 5 Jan 2010 19:14:10 -
@@ -56,7 +56,7 @@
}
my $data = $catalogs-{pg_proc}-{data};
-foreach my $row (@$data)
+while( my $row = shift @$data )
{
# To construct fmgroids.h and fmgrtab.c, we need to inspect some
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: yeah it is probably some strange platform specific issue - however with some help from the IRC channel I came up with the following patch that considerable reduces the memory bloat (but still needs ~55MB max) and allows the build to succeed. What about Alvaro's idea of adding $row = undef; at the bottom of the loop? That seems marginally less ugly... 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] libpq naming on Win64
On Tuesday, January 5, 2010, Peter Eisentraut pete...@gmx.net wrote: On tis, 2010-01-05 at 16:48 +, Dave Page wrote: On Tue, Jan 5, 2010 at 3:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: I would have thought Microsoft would have a better solution than this for managing 64-bit libraries. Or am I too optimistic about Redmond's competence? They have two separate installation directories for 32 and 64 bit packages. With PostgreSQL though, we'll quite possibly be shipping both 32 and 64 bit components in the same installer, and thus going into the same installation directory. Can't the installer install things into two separate directories? Not really. Aside from looking really odd to the end user, the installer is either running in 64 or 32 bit mode, and Windows may apply path redirection so the installer doesn't even see the other path. Also, given that there are likely to be other 32 bit-only apps using libpq for quite some time, having both builds will be genuinely useful. The only other option that seems feasible might be to have a seperate bin dir in the main installation directory for 32 bit libraries, but that'll be pretty ugly. Mind you, on reflection that may be required if any dependency libraries have the same name. /D -- 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] Proposal: XML helper functions
Pavel Stehule wrote:
2010/1/5 Scott Bailey arta...@comcast.net:
One of the problem with shredding XML is that it is very kludgy to get a
scalar value back from xpath. The xpath function always returns an array of
XML. So for example, to extract a numeric value you need to:
1) use xpath to get the node
2) get the first element of the XML array
3) cast that to varchar
4) cast that to numeric
So I wrote the following function:
CREATE OR REPLACE FUNCTION xmlvalue(
VARCHAR,
XML
) RETURNS TEXT AS
$$
SELECT CASE WHEN $1 ~ '@[[:alnum:]_]+$'
THEN (xpath($1, $2))[1]
WHEN $1 ~* '/text()$'
THEN (xpath($1, $2))[1]
WHEN $1 LIKE '%/'
THEN (xpath($1 || 'text()', $2))[1]
ELSE (xpath($1 || '/text()', $2))[1]
END::text;
$$ LANGUAGE 'sql' IMMUTABLE;
It's pretty simple. It just does a check to see if you are extracting an
attribute or an element and if element, it makes sure to get the text value.
So query that used to look like:
SELECT CAST(
CAST(
(xpath('/foo/bar/text()', myxml))[1]
AS varchar)
AS numeric) AS bar
now becomes:
SELECT xmlvalue('/foo/bar/text(), myxml)::numeric AS bar
Second function just checks that the xpath expression finds at least one
node.
CREATE OR REPLACE FUNCTION xmlexists(
VARCHAR,
XML
) RETURNS BOOLEAN AS
$$
SELECT CASE WHEN array_upper(xpath($1, $2), 1) 0
THEN true ELSE false END;
$$ LANGUAGE 'sql' IMMUTABLE;
On naming, SQL/XML specifies xmlexists and xmlcast.
I am for SQL/XML naming convention.
Well I'm shying away from the name xmlcast because it is supposed to
cast xml to scalar, scalar to xml and xml to xml. For instance these
would all work on db2.
SELECT xmlcast(null AS XML),
xmlcast(bar AS XML),
xmlcast(xmlquery('$x/baz/bar' PASSING foo.barxml AS x) AS VARCHAR(30))
FROM foo
But I just found that Oracle added xmlcast in 11g and it only does xml
to scalar (and only number, varchar2 and date/time types). So maybe
you're right.
Scott
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Garick Hamlin gham...@isc.upenn.edu writes: On Tue, Jan 05, 2010 at 02:02:51PM -0500, Tom Lane wrote: I'm not nearly good enough in Perl to be sure about the semantics of this loop. Is it possible that it's changing the global contents of the @$data structure, rather than just hacking a local copy of each row before pushing some values into @fmgr? Yes, that is what would happen. Is that a problem? (I haven't read the script) Well, it *shouldn't* be a problem, but what it looks like to me is that Stefan's perl version is somehow leaking one copy of the whole $data structure each time through the loop. If we were to copy and then modify each row individually, maybe that'd dodge the bug. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
On Tue, Jan 05, 2010 at 02:02:51PM -0500, Tom Lane wrote:
Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes:
did that and it seems the problem is in the loop that does:
foreach my $row (@$data)
{
# To construct fmgroids.h and fmgrtab.c, we need to inspect some
# of the individual data fields. Just splitting on whitespace
Huh. It's weird that I don't see a leak in either 5.8.7 or 5.10.1,
which are the two closest perl versions I have handy here. I think
this may be a platform-specific Perl bug. Still, it would be nice
to work around it if we can.
I'm not nearly good enough in Perl to be sure about the semantics
of this loop. Is it possible that it's changing the global contents
of the @$data structure, rather than just hacking a local copy of
each row before pushing some values into @fmgr?
Yes, that is what would happen.
I have not read this script at all but that is how perl works...
$row is an aliased to each scalar in the list '(@$data)'
There is no copying. Copying happens on list assignment,
but not with for/foreach.
If you wanted a copy you need something like:
foreach my $row (@{[ @$data ]}) {
for a shallow copy.
Is that a problem? (I haven't read the script)
Garick
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Tom Lane wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: yeah it is probably some strange platform specific issue - however with some help from the IRC channel I came up with the following patch that considerable reduces the memory bloat (but still needs ~55MB max) and allows the build to succeed. What about Alvaro's idea of adding $row = undef; at the bottom of the loop? That seems marginally less ugly... not sure I want to argue about the ugliness of perl but that has the same effect as my patch so either way would do to get spoonbill green again. 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] true serializability and predicate locking
On Tue, Jan 5, 2010 at 2:14 PM, Jeff Davis pg...@j-davis.com wrote:
I have a question regarding true serializability and predicate locking.
There's some context on the wiki page:
http://wiki.postgresql.org/wiki/Serializable
under the heading Predicate Locking.
If you have the following DDL:
create table mytable(mycircle circle);
create index mytable_mycircle_idx on mytable
using gist (mycircle);
and two transactions:
T1:
BEGIN;
SELECT * FROM mytable WHERE mycircle '(0, 0), 10';
-- if any rows are returned, ROLLBACK
INSERT INTO mytable(mycircle) VALUES('(0, 0), 10');
COMMIT;
T2:
BEGIN;
SELECT * FROM mytable WHERE mycircle '(5, 5), 5';
-- if any rows are returned, ROLLBACK
INSERT INTO mytable(mycircle) VALUES('(5, 5), 5');
COMMIT;
Clearly one of those transactions should abort, because that will happen
in either serialized order. But I don't see where any lock is stored,
nor how the conflict is detected.
There has been a lot of theoretical discussion on this matter, but I'd
like to know how it will work in this specific case. You can't merely
lock a few index pages, because the INSERT might put the tuple in
another page.
I'm still trying to catch up on this discussion as well as relevant
papers, but this question has been on my mind.
One approach that might work for GiST is to get some kind of lock
(SIREAD?) on the predicates for the pages that the search does not
match. That way, the conflict can be detected if an INSERT tries to
update the predicate of a page to something that the search may have
matched.
If the index was GIN instead of GiST, I think the fastupdate feature
would cause a problem, though (as Greg brought up). Fastupdate may need
to be disabled when using truly serializable transactions.
It seems to me that we shouldn't pre-judge too much about how
predicate locking will ultimately be implemented. Suppose the first
query in your example were:
SELECT * FROM mytable WHERE [some incredibly complex condition
involving all sorts of strange magic]
It seems to me that in a case like this our only realistic option is
to lock the entire table until T1 commits.
Now, in certain cases, we can optimize this, if we want. For example,
if the first query were:
SELECT * FROM mytable WHERE id = 42;
...and if further we know that there is a unique B-tree index on the
id column, and if there is an existing record with id = 42, then we
can lock just that record. If no such record exists, we can either
fall back to locking the whole table, or we can take some sort of
key-range lock that will block any future attempts to read or update a
row with id = 42.
With GIST and GIN and so on, the situation is similar. If the index
AM can be modified to provide certain kinds of key-range locking then
we can take weaker locks for queries that involve those types of
conditions. If not, we have to fall back to a full-table lock.
...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] true serializability and predicate locking
Jeff Davis pg...@j-davis.com wrote: Clearly one of those transactions should abort, because that will happen in either serialized order. But I don't see where any lock is stored, nor how the conflict is detected. That depends on where in the development cycle of this feature you are. I'm anticipating that throughout, the locks to support SSI will be kept in RAM, probably in the existing lock heap table or something based on it. Near the beginning, all locking will be at the table level, as the fastest way to develop something which is correct in the sense of not allowing any of the snapshot anomalies. Later in development, we will try to optimize initial locks to smaller granularity and promote to coarser granularity only as needed to keep RAM usage reasonable. Behavior will be no more correct with such optimizations, but it should become more acceptable in terms of performance and rollback rates. I will not spend any significant amount of time looking at the specifics of any particular optimizations yet, because such premature optimization is certain to kill the whole project. There has been a lot of theoretical discussion on this matter, but I'd like to know how it will work in this specific case. You can't merely lock a few index pages, because the INSERT might put the tuple in another page. I don't yet know a lot about GiST indexes beyond the high-level theory (it's an area where I haven't yet delved into the code), but it's pretty easy to get to page level locks if (and only if) an index search is guaranteed to look at some page which will be modified if a later conflicting INSERT or UPDATE will be required to modify either that page or a logically adjacent page. My initial intuition is that a search can't decide that there are no matching rows unless it has looked at some page which would be different if a matching row existed. One approach that might work for GiST is to get some kind of lock (SIREAD?) on the predicates for the pages that the search does not match. That way, the conflict can be detected if an INSERT tries to update the predicate of a page to something that the search may have matched. That sounds right to me. If the index was GIN instead of GiST, I think the fastupdate feature would cause a problem, though (as Greg brought up). Fastupdate may need to be disabled when using truly serializable transactions. Again, if I spent the time to evaluate all such details now, we would never get to the point where such ideas can be examined in context or quickly tested. I'm trying to keep this process as open as possible. If I hid in a corner and worked on this in isolation I could probably (eventually) present it with answers to all such questions at the ready. I think there are obvious down-sides to such a strategy, so I'm forced into the position of saying, with regards to most potential optimizations, we'll cross that bridge when we come to it -- knowing full well that many optimizations will indeed be necessary before the patch is acceptable. I hope that helps. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: XML helper functions
Peter Eisentraut wrote: On tis, 2010-01-05 at 10:14 -0800, Scott Bailey wrote: One of the problem with shredding XML is that it is very kludgy to get a scalar value back from xpath. The xpath function always returns an array of XML. So for example, to extract a numeric value you need to: 1) use xpath to get the node 2) get the first element of the XML array 3) cast that to varchar 4) cast that to numeric There has been talk about adding something like xpath_string, xpath_number, xpath_boolean for fetching xpath expressions that don't return nodesets. I think that would fit your use case. The first two sound very much like what I'm looking for. I'm unsure about the third. Is it's purpose to extract the scalar value of an expression and cast to bool as the other two do, or is it to identify if the xpath expression returned any nodes like xmlexists? Scott -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: What about Alvaro's idea of adding $row = undef; at the bottom of the loop? That seems marginally less ugly... not sure I want to argue about the ugliness of perl but that has the same effect as my patch so either way would do to get spoonbill green again. OK, done. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Tom Lane wrote:
I'm not nearly good enough in Perl to be sure about the semantics
of this loop. Is it possible that it's changing the global contents
of the @$data structure, rather than just hacking a local copy of
each row before pushing some values into @fmgr?
That's exactly what it does. The loop variable is an alias. See
perlsyn(1) for details.
These two lines appear to be suspicious:
$row-{bki_values} =~ s/[^]*/xxx/g;
@{$ro...@attnames} = split /\s+/, $row-{bki_values};
Something like:
(my $bkival = $row-{bki_values}) =~ s/[^]*/xxx/g;
my $atts = {};
@{$att...@attnames} = split /\s+/, $bkival;
might work better.
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] Re: [COMMITTERS] pgsql: Get rid of the need for manual maintenance of the initial
Andrew Dunstan and...@dunslane.net writes:
Something like:
(my $bkival = $row-{bki_values}) =~ s/[^]*/xxx/g;
my $atts = {};
@{$att...@attnames} = split /\s+/, $bkival;
might work better.
I committed Alvaro's suggestion (undef at the bottom), but feel
free to clean it up if you like this way better.
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] We no longer have a fallback for machines without working int64
David Fetter da...@fetter.org writes: On Tue, Jan 05, 2010 at 10:47:33AM -0500, Tom Lane wrote: As pointed out here http://archives.postgresql.org/pgsql-general/2010-01/msg00145.php the current zic code doesn't cope gracefully with lack of working int64. Considering the trouble we've gone to throughout the rest of the system to support such compilers, it's a bit annoying to have this little detail break it. On the other hand, it's unclear that anybody still cares. (Other than people running SCO Openserver, for whom I have little sympathy anyway.) There was a use case for supporting non-working int64, but reality has changed. Yeah, maybe it's time to forget about that. If so, we ought to change configure to spit up if it can't find a working 64-bit type. Failing much later on with a strange message from zic isn't too acceptable. I propose doing that in both HEAD and 8.4, since both those branches are broken for someone with such a compiler. 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] Status of plperl inter-sp calling
On Thu, Dec 31, 2009 at 09:47:24AM -0800, David E. Wheeler wrote:
On Dec 30, 2009, at 2:54 PM, Tim Bunce wrote:
That much works currently. Behind the scenes, when a stored procedure is
loaded into plperl the code ref for the perl sub is stored in a cache.
Effectively just
$cache{$name}[$nargs] = $coderef;
An SP::AUTOLOAD sub intercepts any SP::* call and effectively does
lookup_sp($name, \...@_)-(@_);
For SPs that are already loaded lookup_sp returns $cache{$name}[$nargs]
so the overhead of the call is very small.
Definite benefit, there. How does it handle the difference between
IMMUTABLE | STABLE | VOLATILE, as well as STRICT functions?
It doesn't at the moment. I think IMMUTABLE, STABLE and VOLATILE can be
(documented as being) ignored in this context.
Supporting STRICT probably wouldn't be too hard.
And what does it do if the function called is not actually a Perl function?
(See fallback-to-SQL two paragraphs below)
For SPs that are not cached, lookup_sp returns a code ref of a closure
that will invoke $name with the args in @_ via
spi_exec_query(select * from $name($encoded_args));
The fallback-to-SQL behaviour neatly handles non-cached SPs (forcing
them to be loaded and thus cached), and inter-language calling (both
plperl-plperl and other PLs).
Is there a way for such a function to be cached? If not, I'm not sure
where cached functions come from.
The act of calling the function via spi_exec_query will load it, and
thereby cache it in the perl interpreter as a side effect (if the
language is the is the same: e.g., plperlu-plperlu).
Limitations:
* It's not meant to handle type polymorphism, only the number of args.
Well, spi_exec_query() handles the type polymorphism. So might it be
possible to call SP::function() and have it not use a cached query?
That way, one gets the benefit of polymorphism. Maybe there's a SP
package that does caching, and an SPI package that does not? (Better
named, though.)
The underlying issue here is perl's lack of strong typing.
See http://search.cpan.org/~mlehmann/JSON-XS-2.26/XS.pm#PERL_-%3E_JSON
especially the simple scalars section and used as string example.
As far as I can see there's no way for perl to support the kind of
rich type polymorphism that PostgreSQL offers via the kind of make it
look like a perl function call interface that we're discussing.
[I can envisage a more complex interface where you ask for a code ref to
a sub with a specific type signature and then use that code ref to make the
call. Ah, I've just had a better idea but it needs a little more thought.
I'll send a another email later.]
* When invoked via SQL, because the SP isn't cached, all non-ref args
are all expressed as strings via quote_nullable(). Any array refs
are encoded as ARRAY[...] via encode_array_constructor().
Hrm. Why not use spi_prepare() and let spi_exec_prepared() handle the quoting?
No reason, assuming spi_exec_prepared handles array refs properly
[I was just doing simplest thing that could possibly work at this stage]
I don't see either of those as significant issues: If you need more
control for a particular SP then don't use SP::* to call that SP.
If there was a non-cached version that was essentially just sugar for
the SPI stuff, I think that would be more predicable, no? I'm not
saying there shouldn't be a cached interface, just that it should not
be the first choice when using polymorphic functions and non-PL/Perl
functions.
So you're suggesting SP::foo(...) _always_ executes foo(...) via bunch
of spi_* calls. Umm. I thought performance was a major driving factor.
Sounds like you're more keen on syntactic sugar.
Tim.
--
Sent 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_migrator issues
Alvaro Herrera wrote: Bruce Momjian wrote: pg_migrator has become more popular recently, so it seems time to look at some enhancements that would improve pg_migrator. None of these are required, but rather changes that would be nice to have: 1) Right now pg_migrator preserves relfilenodes for TOAST files because this is required for proper migration. Now that we have shown that strategically-placed global variables with a server-side function to set them is a viable solution, it would be nice to preserve all relfilenodes from the old server. This would simplify pg_migrator by no long requiring place-holder relfilenodes or the renaming of TOAST files. A simpler solution would just be to allow TOAST table creation to automatically remove placeholder files and create specified relfilenodes via global variables. Getting rid of the need for placeholders is a good idea. +1 on getting TOAST tables created with the correct relfilenode from the start. I don't know that preserving any other relfilenode is useful; however if it means you no longer have to rename the files underlying each table, -- it would probably also be a good idea. (I don't know how does -- pg_migrator deal with such things currently -- does it keep a map of -- table name to relfilenode?) Yes, and it will still need that because we don't want to transfer over any of the system tables or pg_catalog files. -- 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] Status of plperl inter-sp calling
On Jan 5, 2010, at 12:59 PM, Tim Bunce wrote: So you're suggesting SP::foo(...) _always_ executes foo(...) via bunch of spi_* calls. Umm. I thought performance was a major driving factor. Sounds like you're more keen on syntactic sugar. I'm saying do both. Make the cached version the one that will be used most often, but make available a second version that doesn't cache so that you get the sugar and the polymorphic dispatch. Such would only have to be used in cases where there is more than one function that takes the same number of arguments. The rest of the time -- most of the time, that is -- one can use the cached version. 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] true serializability and predicate locking
On Tue, 2010-01-05 at 13:47 -0600, Kevin Grittner wrote: I will not spend any significant amount of time looking at the specifics of any particular optimizations yet, because such premature optimization is certain to kill the whole project. I'm certainly not trying to derail the project, I'm just trying to see some light at the end of the tunnel. Is a full table lock acceptable in the end? If so, then predicate locking is just optimization, and we should leave it until later. If not, then reasonably efficient predicate locking is a part of the design. We can still leave it until later, but we shouldn't call design issues premature optimization. I don't yet know a lot about GiST indexes beyond the high-level theory (it's an area where I haven't yet delved into the code), but it's pretty easy to get to page level locks if (and only if) an index search is guaranteed to look at some page which will be modified if a later conflicting INSERT or UPDATE will be required to modify either that page or a logically adjacent page. My initial intuition is that a search can't decide that there are no matching rows unless it has looked at some page which would be different if a matching row existed. Well, that's my concern. Technically, I think you're correct. But that might not be very helpful in the case of GIN fastupdate, for instance. Every insert will modify the fastupdate buffer, and every search will read it. One approach that might work for GiST is to get some kind of lock (SIREAD?) on the predicates for the pages that the search does not match. That way, the conflict can be detected if an INSERT tries to update the predicate of a page to something that the search may have matched. That sounds right to me. With GiST, the picture looks a little more promising. I'm still a little concerned that it will cause significant performance pitfalls, however. I think there are obvious down-sides to such a strategy, so I'm forced into the position of saying, with regards to most potential optimizations, we'll cross that bridge when we come to it -- knowing full well that many optimizations will indeed be necessary before the patch is acceptable. That's fine with me, and I'll hold off on issues like this one. 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] Proposal: XML helper functions
On tis, 2010-01-05 at 11:50 -0800, Scott Bailey wrote: There has been talk about adding something like xpath_string, xpath_number, xpath_boolean for fetching xpath expressions that don't return nodesets. I think that would fit your use case. The first two sound very much like what I'm looking for. I'm unsure about the third. Is it's purpose to extract the scalar value of an expression and cast to bool as the other two do, or is it to identify if the xpath expression returned any nodes like xmlexists? XPath as a language has four data types: nodeset, string, number, boolean. So an XPath expression could return any of those types. Then, I suppose, the xpath_foo() function would evaluate the expression and return the result as type foo, possibly raising an error if the types don't match. Details to be determined. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Writeable CTEs
On 1/5/10 9:45 AM, Marko Tiikkaja wrote: On 2010-01-05 19:21 +0200, Greg Stark wrote: with t as (delete from foo returning *) select * from t where x=? applications will almost certainly expect the number to match the actual number of rows returned and may well misbehave if they don't. I probably wasn't clear about the actual problem in the original post. The problem only affects INSERT, UDPATE and DELETE where you are actually counting affected rows (i.e. PQcmdTuples(), not PQntuples()) so the this example would work as expected. I don't think there is an as expected for this situation; people won't know what to expect. So what do we think is resonable? The current behavior, which reports the total count of rows expected, works for me. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] true serializability and predicate locking
Jeff Davis pg...@j-davis.com wrote: Is a full table lock acceptable in the end? If so, then predicate locking is just optimization, and we should leave it until later. I think that the predicate locking will need to be RAM-based to provide acceptable performance, and that we will need a multi-granularity approach with granularity promotion which will include table locks in some situations. If not, then reasonably efficient predicate locking is a part of the design. We can still leave it until later, but we shouldn't call design issues premature optimization. Well, technically table locking can be used to provide predicate locking; it is just way too coarse-grained to be acceptable for production as the *only* technique. The optimization phase will involve many types of optimization, but a lot of it will be balancing the overhead of finer-grained locks against the higher rate of false positives with coarser-grained locks. I really think that the correct way to view this is to view backing off to finer-grained resolutions as optimization, albeit absolutely necessary optimization. I totally understand the impulse to work on these details up front -- I'm fighting against such an impulse myself. I've just convinced myself, rationally, that such work is better deferred. On the other hand, perhaps if I'm working the development path in the wiki page, it *could* make sense, if you're interested, to look at issues like this now and get them all documented and ready to go once I get far enough along -- we could meet in the middle. Another interesting thing which crossed my mind (and I should probably add a section for such things in the wiki) is that, given the overhead and conflict implications of using table scans in serializable transactions, we should perhaps try to discourage table scans from being chosen for those using serializable transactions. I figure we can probably fudge this to a workable degree by adjusting tuple cost GUCs, but if you wanted to think about this issue in more depth, it might be useful. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Status of plperl inter-sp calling
On Tue, Jan 05, 2010 at 01:05:40PM -0800, David E. Wheeler wrote: On Jan 5, 2010, at 12:59 PM, Tim Bunce wrote: So you're suggesting SP::foo(...) _always_ executes foo(...) via bunch of spi_* calls. Umm. I thought performance was a major driving factor. Sounds like you're more keen on syntactic sugar. I'm saying do both. Make the cached version the one that will be used most often, but make available a second version that doesn't cache so that you get the sugar and the polymorphic dispatch. Such would only have to be used in cases where there is more than one function that takes the same number of arguments. The rest of the time -- most of the time, that is -- one can use the cached version. I think I have a best-of-both solution. E-mail to follow... Tim. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Stats for inheritance trees
On Tue, Dec 29, 2009 at 9:12 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
Yep. It would also lower the barrier to future innovations of that
type, which would be a good thing, IMO. Unfortunately we'd likely
need to continue to support the existing syntax at least for
attstattarget, which is kind of a bummer, but seems managable. I
think we could throw over the syntax for ALTER TABLE ... ADD
STATISTICS DISTINCT since it is an 8.5-ism.
Yeah --- if we think we might want to do this, now is the time,
before we're stuck with supporting that syntax. (I was thinking
earlier today that attdistinct was already in 8.4, but it's not.)
[ Hack, hack, hack. ]
I'm not quite sure what the correct approach is to making attoptions
available to examine_attribute(), which can't get at them in any very
obvious way because the relation descriptor it gets passed as an
argument only includes the fixed-size portion of each pg_attribute
tuple. The obvious approaches are:
1. Extract attrelid and attnum from the tuple and issue a syscache
lookup to get the full tuple.
2. Make RelationBuildTupleDesc() parse the attoptions and store them
into a new RelationData member.
I'm leaning toward the latter method. The upside of that method is
that making attoptions part of the Relation descriptor means that
they'll be conveniently available to all clients of the relcache. The
downside is that right now, only ANALYZE actually needs to care at the
moment, and yet we're incurring the overhead across the board. My
thought is that that's OK, but I wonder if anyone thinks it might
cause a measurable performance hit?
WIP patch attached. Right now this just adds the ability to set and
store attoptions, but doesn't actually do anything useful with them.
No doc changes, no pg_dump support, no psql support, doesn't remove
the SET STATISTICS DISTINCT code. All these warts will be fixed in a
future version once I decide what to do about the problem mentioned
above.
...Robert
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 24480e3..596591c 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -21,6 +21,7 @@
#include access/reloptions.h
#include catalog/pg_type.h
#include commands/defrem.h
+#include commands/tablecmds.h
#include commands/tablespace.h
#include nodes/makefuncs.h
#include utils/array.h
@@ -196,6 +197,22 @@ static relopt_real realRelOpts[] =
},
-1, 0.0, DBL_MAX
},
+ {
+ {
+ n_distinct,
+ Sets the planner's estimate of the number of distinct values appearing in a column (excluding child relations).,
+ RELOPT_KIND_ATTRIBUTE
+ },
+ 0, -1.0, DBL_MAX
+ },
+ {
+ {
+ n_distinct_inherited,
+ Sets the planner's estimate of the number of distinct values appearing in a column (including child relations).,
+ RELOPT_KIND_ATTRIBUTE
+ },
+ 0, -1.0, DBL_MAX
+ },
/* list terminator */
{{NULL}}
};
@@ -1187,6 +1204,37 @@ index_reloptions(RegProcedure amoptions, Datum reloptions, bool validate)
}
/*
+ * Option parser for attribute reloptions
+ */
+bytea *
+attribute_reloptions(Datum reloptions, bool validate)
+{
+ relopt_value *options;
+ AttributeOpts *aopts;
+ int numoptions;
+ static const relopt_parse_elt tab[] = {
+ {n_distinct, RELOPT_TYPE_REAL, offsetof(AttributeOpts, n_distinct)},
+ {n_distinct_inherited, RELOPT_TYPE_REAL, offsetof(AttributeOpts, n_distinct_inherited)}
+ };
+
+ options = parseRelOptions(reloptions, validate, RELOPT_KIND_ATTRIBUTE,
+ numoptions);
+
+ /* if none set, we're done */
+ if (numoptions == 0)
+ return NULL;
+
+ aopts = allocateReloptStruct(sizeof(AttributeOpts), options, numoptions);
+
+ fillRelOptions((void *) aopts, sizeof(AttributeOpts), options, numoptions,
+ validate, tab, lengthof(tab));
+
+ pfree(options);
+
+ return (bytea *) aopts;
+}
+
+/*
* Option parser for tablespace reloptions
*/
bytea *
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index b54520f..8acdde8 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -362,7 +362,7 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
return false;
if (attr1-attinhcount != attr2-attinhcount)
return false;
- /* attacl is ignored, since it's not even present... */
+ /* attacl and attoptions are not even present... */
}
if (tupdesc1-constr != NULL)
@@ -479,7 +479,7 @@ TupleDescInitEntry(TupleDesc desc,
att-attisdropped = false;
att-attislocal = true;
att-attinhcount = 0;
- /* attacl is not set because it's not present in tupledescs */
+ /* attacl and attoptions are not present in tupledescs */
tuple = SearchSysCache(TYPEOID,
ObjectIdGetDatum(oidtypeid),
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 7a51b09..35a13e4 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@
Re: [HACKERS] Writeable CTEs
On Tue, Jan 5, 2010 at 4:20 PM, Josh Berkus j...@agliodbs.com wrote: On 1/5/10 9:45 AM, Marko Tiikkaja wrote: On 2010-01-05 19:21 +0200, Greg Stark wrote: with t as (delete from foo returning *) select * from t where x=? applications will almost certainly expect the number to match the actual number of rows returned and may well misbehave if they don't. I probably wasn't clear about the actual problem in the original post. The problem only affects INSERT, UDPATE and DELETE where you are actually counting affected rows (i.e. PQcmdTuples(), not PQntuples()) so the this example would work as expected. I don't think there is an as expected for this situation; people won't know what to expect. So what do we think is resonable? The current behavior, which reports the total count of rows expected, works for me. I agree with Tom's statement upthread that we should only count the rows affected by the top-level query. Anything else seems extremely counter-intuitive. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
