Re: [HACKERS] Proof of concept: auto updatable views
On 30 August 2012 20:05, Robert Haas robertmh...@gmail.com wrote: On Sun, Aug 12, 2012 at 5:14 PM, Dean Rasheed dean.a.rash...@gmail.com wrote: None of this new code kicks in for non-security barrier views, so the kinds of plans I posted upthread remain unchanged in that case. But now a significant fraction of the patch is code added to handle security barrier views. Of course we could simply say that such views aren't updatable, but that seems like an annoying limitation if there is a feasible way round it. Maybe it'd be a good idea to split this into two patches: the first could implement the feature but exclude security_barrier views, and the second could lift that restriction. Yes, I think that makes sense. I should hopefully get some time to look at it over the weekend. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Cascading replication and recovery_target_timeline='latest'
When a cascading standby launches a new walsender, it fetches the current recovery timeline: /* * Use the recovery target timeline ID during recovery */ if (am_cascading_walsender) ThisTimeLineID = GetRecoveryTargetTLI(); Comment in GetRecoveryTargetTLI() does this: /* RecoveryTargetTLI doesn't change so we need no lock to copy it */ return XLogCtl-RecoveryTargetTLI; That comment is not true. RecoveryTargetTLI can change during recovery, if you set recovery_target_timeline='latest'. In 'latest' mode, when the (apparent) end of WAL is reached, the archive is scanned for any new timeline history files that may have appeared. If a new timeline is found, RecoveryTargetTLI is updated, and recovery is continued on the new timeline. Aside from the missing locking, I wonder what that does to a cascaded standby. If there is an active walsender running while RecoveryTargetTLI is changed, I think what will happen is that the walsender will continue to stream WAL from the old timeline, but because the startup process is now actually replaying from a different timeline, the walsender will send bogus WAL to the standby. When a standby ends recovery, creates a new timeline, and switches to normal operation, postmaster terminates all walsenders because of the timeline change. But don't we have a race condition there, with similar effect? It might take a while for a walsender to die, and in that window, it might send bogus WAL to the cascaded standby. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [v9.3] Extra Daemons (Re: [HACKERS] elegant and effective way for running jobs inside a database)
2012/6/21 Simon Riggs si...@2ndquadrant.com: On 21 June 2012 19:13, Jaime Casanova ja...@2ndquadrant.com wrote: On Sun, Jun 10, 2012 at 4:15 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: 2012/6/8 Simon Riggs si...@2ndquadrant.com: I have a prototype that has some of these characteristics, so I see our work as complementary. At present, I don't think this patch would be committable in CF1, but I'd like to make faster progress with it than that. Do you want to work on this more, or would you like me to merge our prototypes into a more likely candidate? I'm not favor in duplicate similar efforts. If available, could you merge some ideas in my patch into your prototypes? so, we are waiting for a new patch? is it coming from Simon or Kohei? There is an updated patch coming from me. I thought I would focus on review of other work first. Simon, what about the current status of this patch? Do I have something to help for the integration by the upcoming CF? Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] fairly useless psql compatibility warning?
On Fri, Aug 31, 2012 at 4:07 AM, Peter Eisentraut pete...@gmx.net wrote: psql has supported older servers for a great while now, so this sort of things seems pretty useless now: psql (9.2rc1, server 9.1.4) WARNING: psql version 9.2, server version 9.1. Some psql features might not work I think it should be reduced to warning when connected to a newer server. Seems like a good idea to me. +1. (Obviously keeping the first line that shows that the version numbers are different, but not WARNING) -- 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] Event Triggers reduced, v1
Robert Haas robertmh...@gmail.com writes: I guess I don't particularly like either of these changes. The first Fair enough. one is mostly harmless, but I don't really see why it's any better, and it does have the downside of traversing the string twice (once for strlen and a second time in str_toupper) instead of just once. It also makes a line wider than 80 columns, which is a bit ugly. In the It's much easier to read, I think. The line is 79 columns here given the project Emacs setup wrt tabs, see src/tools/editors/emacs.samples. second hunk, the point is that we never have to do CreateCommandTag() here at all unless either casserts are enabled or EventCacheLookup returns a non-empty list. That means that in a non-assert-enabled build, we get to skip that work altogether in the presumably-common case where there are no relevant event triggers. Your proposed change would avoid doing it twice when asserts are disabled, but the cost would be that we'd have to do it once when asserts were disabled even if no event triggers exist. I don't think that's a good trade-off. Well I needed more exercise before sending a patch then, I just missed that. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] pg_dump incorrect output in plaintext mode
On Aug 28, 2012 9:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: On Tue, Aug 28, 2012 at 6:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: I don't see anything particularly incorrect about that. The point of the --verbose switch is to track what pg_dump is doing, and if what it's doing involves going through RestoreArchive(), why should we try to hide the fact? restoring data for table 't' makes you think it's actuall restoring things. It's not. That dumping is implemented by calling an internal function called RestoreArchive() has to be an implementation detail... It certainly confuses users that we say restoring when we're not doing that... Well, why don't we just s/restoring/processing/ in the debug message, and call it good? Sure, that would work for me... I can go do that if there are no objections. /Magnus
Re: [HACKERS] patch: shared session variables
Pavel Stehule pavel.steh...@gmail.com writes: a motivation for this patch was discussion about parametrised DO statement - and simple possibility of access to host variables (psql) variables from server - PL scripts. Pavel, you didn't say what you think about the WITH FUNCTION proposal? And you didn't say how do you want to turn a utility statement into something that is able to return a result set. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] patch: shared session variables
2012/8/31 Dimitri Fontaine dimi...@2ndquadrant.fr: Pavel Stehule pavel.steh...@gmail.com writes: a motivation for this patch was discussion about parametrised DO statement - and simple possibility of access to host variables (psql) variables from server - PL scripts. Pavel, you didn't say what you think about the WITH FUNCTION proposal? I don't like it - this proposal is too lispish - it is not SQL And you didn't say how do you want to turn a utility statement into something that is able to return a result set. if we support real procedures ala sybase procedures (MySQL, MSSQL..) - then we can return result with same mechanism - there are no significant difference between DO and CALL statements - you don't know what will be result type before you call it. Regards Pavel Stehule Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] rows changed in current transaction
Hi, is there any way to check if row have already been modified by the current transaction? I tried condition txid_current() = xmin, but there is problem with the savepoints. After every savepoint rows are getting higher xmin values, but txid_current() remains the same. Regards, Miroslav Simulcik
Re: [HACKERS] hunspell and tsearch2 ?
Hi Robert, there is a note in the pg documentation chapter 12.6.5 Ispell Dictionary *Note:*MySpell does not support compound words. Hunspell has sophisticated support for compound words. At present, PostgreSQL implements only the basic compound word operations of Hunspell. Regards Dirk On 08/30/2012 05:39 PM, Robert Haas wrote: On Mon, Aug 27, 2012 at 8:31 AM, Dirk Lutzebäck dirk.lutzeba...@thinkproject.com wrote: we have issues with compound words in tsearch2 using the german (ispell) dictionary. This has been discussed before but there is no real solution using the recommended german dictionary at http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2 (convert old openoffice dict file to ispell suitable for tsearch): # select ts_lexize('german_ispell', 'vollklimatisiert'); ts_lexize {vollklimatisiert} (1 row) This should return atleast {vollklimatisiert, voll, klimatisiert} The issue with compound words in ispell has been addressed in hunspell. But this has not been integrated fully to tsearch2 (according to the documentation). Just out of curiosity, which part of the documentation are you looking at? The only mention of hunspell I see in the documentation is a mention that we apparently support their dictionary-file format. Are there any plans to fully integrate hunspell into tsearch2? What is needed to do this? What is the functional delta which is missing? Maybe we can help... -- Mit freundlichen Grüßen / Best regards, *think project! International GmbH Co. KG* Dirk Lutzebäck Geschäftsführer / Managing Director, CTO Tel +49 30 921 017 90 Fax +49 30 921 017 50 dirk.lutzeba...@thinkproject.com Rechtliche Informationen zum Absender (Impressum): www.thinkproject.com/de/info http://www.thinkproject.com/de/info Legal information (imprint): www.thinkproject.com/en/info http://www.thinkproject.com/en/info smime.p7s Description: S/MIME Cryptographic Signature
Re: [HACKERS] compiler barriers (was: New statistics for WAL buffer dirty writes)
Was there any conclusion from these ideas? --- On Wed, Aug 1, 2012 at 11:35:56AM -0400, Robert Haas wrote: On Wed, Aug 1, 2012 at 10:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: I think you may be right that using __asm__ __volatile__ in gcc S_UNLOCK cases would be a big step forward, but it needs more research to see if that's the only fix needed. Looking through the spinlock implementations in s_lock.h, we start with a bunch of GCC-ish things: - i386 uses __asm__ __volatile__ - x86_65 uses __asm__ __volatile__ - ia64 uses __asm__ __volatile__ on GCC, and _InterlockedExchange on the Intel compiler - arm uses GCC integer atomics if available, and __asm__ __volatile__ otherwise - s390 uses __asm__ __volatile__ - sparc uses __asm__ __volatile__ - ppc uses __asm__ __volatile__ - m68k uses __asm__ __volatile__ - vax uses __asm__ __volatile__ - ns32k uses __asm__ __volatile__ - alpha uses __asm__ __volatile__ - mips uses __asm__ __volatile__ - m32r calls what appears to be a system-provided tas() function - SuperH uses __asm__ __volatile__ Presumably all the ones that are using __asm__ __volatile__ for TAS() could also use that for a compiler barrier at release time, so the interesting cases are ia64, arm, and m32r. The ARM case is not a problem, because the GCC builtins are defined as compiler barriers. For the Intel compiler case on ia64, I believe the existing definition of pg_compiler_barrier() in storage/barrier.h should suffice for a release barrier. I have no idea what to do about m32r. The remaining implementations are for non-GCC compilers, which is where things obviously get harder: - Univel CC (is that sco cc?) uses an idiosyncratic asm inline syntax - Tru64/alpha uses a system-provided primitive called __LOCK_LONG_RETRY - hppa uses __asm__ __volatile__ on GCC; otherwise, it uses hpux_hppa.s - itanium uses _Asm_xchg - sgi uses OS or compiler primitives called test_and_set and test_then_and - sinix uses OS or compiler primitives called acquire_lock and release_lock - aix uses OS or compiler primitives called _check_lock and _clear_lock - sparc uses pg_atomic_cas, which is implemented in sunstudio_x86.s or sunstudio_sparc.s - win32 uses InterlockedCompareExchange() Of these, Itanium and Win32 are not too hard to handle because there's are already compiler barrier definitions in storage/barrier.h: _Asm_sched_fence() for Itanium and _ReadWriteBarrier() for Win32. The rest are anybody's guess. I suspect that SGI, SINIX, and AIX are *probably* fine as they are, because if the primitives they are using are provided by the platform, then the compiler ought to be smart enough to know what they mean. Even if they're just functions in a system library somewhere, we're already relying on the fact that a call to a globally accessible function acts as a compiler barrier, so I doubt we are any worse off if we rely on it here, too. However, the remaining cases - Univel CC, Tru64/alpha, hppa non-GCC, and sparc - probably need work. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade's exec_prog() coding improvement
On 08/24/2012 11:44 AM, Alvaro Herrera wrote: Again, win32 testing would be welcome. Sadly, buildfarm does not run pg_upgrade's make check. Yesterday I added a new module to the buildfarm client code to run this (https://github.com/PGBuildFarm/client-code/commit/ab812cb9920c65e39ec7358dc816371f1fef31eb). It required a couple of tweaks in the base code. This will be in a new buildfarm client release fairly shortly. It's running on crake now, and I will add it to pitta to get some Windows coverage. It would be a lot nicer is the test were written in Perl, since we don't necessarily have a Bourne shell available for MSVC builds, but we definitely have Perl available. None of this does what I think we really need, which is cross-release pg_upgrade testing, which remains on my TODO list as a fairly high priority item. 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] _USE_32BIT_TIME_T Patch
I've added this to the release blockers section for 9.2 on the wiki, as without it, pl/perl is unusable on Win32. On Thu, Aug 30, 2012 at 1:39 PM, Dave Page dp...@pgadmin.org wrote: On Thu, Aug 30, 2012 at 6:34 AM, Owais Khan owais.k...@enterprisedb.com wrote: Hello, We are getting crash while using plperl on Win32 as ActiveState perl(Win32) uses 32-bit time_t structures. So, We have to compile DB Server's code also with 32-bit time_t structure. Patch is adding _USE_32BIT_TIME_T in preprocessor definitions in case platform is Windows-32 for all project files. For additional background info, we did originally define this macro for compatibility with third party code: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=22867ab9867a145b676f906b98f491c4496a70da however it got removed here for some reason: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=cd004067742ee16ee63e55abfb4acbd5f09fbaab The bottom line is, without it, pl/perl will crash with modern versions of ActiveState Perl on Win32 (Windows users cannot use Strawberry Perl as it doesn't contain the shared library we need). This should definitely go in 9.2, and ideally the earlier branches that didn't have it defined as well (this has been reported in the past for 9.1 - for example; http://archives.postgresql.org/pgsql-bugs/2012-04/msg00054.php) - though I'm a little worried that adding it there may cause other existing addons to require recompilation. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] _USE_32BIT_TIME_T Patch
On 08/31/2012 11:05 AM, Dave Page wrote: I've added this to the release blockers section for 9.2 on the wiki, as without it, pl/perl is unusable on Win32. I'll have a look at it today. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] _USE_32BIT_TIME_T Patch
On Fri, Aug 31, 2012 at 4:10 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:05 AM, Dave Page wrote: I've added this to the release blockers section for 9.2 on the wiki, as without it, pl/perl is unusable on Win32. I'll have a look at it today. Thanks Andrew - minor clarification; unusable on MSVC/Win32. I suspect Mingw builds may be fine, as they use a much older runtime. Of course, we've used MSVC++ for the installer builds for years now. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] _USE_32BIT_TIME_T Patch
On 08/31/2012 11:14 AM, Dave Page wrote: On Fri, Aug 31, 2012 at 4:10 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:05 AM, Dave Page wrote: I've added this to the release blockers section for 9.2 on the wiki, as without it, pl/perl is unusable on Win32. I'll have a look at it today. Thanks Andrew - minor clarification; unusable on MSVC/Win32. I suspect Mingw builds may be fine, as they use a much older runtime. Of course, we've used MSVC++ for the installer builds for years now. What exactly is the known combination of things that don't work, and things that do work? My only 32 bit test environment for this (ASPerl 5.12.2 build 1202 [293621], built Sep 6, 2010, Visual C++ Express 2008, Windows XP SP3) doesn't seem to have any problem building and running plperl. That makes it tough to test if I don't know what exactly needs to change to break things. 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] _USE_32BIT_TIME_T Patch
On Fri, Aug 31, 2012 at 4:57 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:14 AM, Dave Page wrote: On Fri, Aug 31, 2012 at 4:10 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:05 AM, Dave Page wrote: I've added this to the release blockers section for 9.2 on the wiki, as without it, pl/perl is unusable on Win32. I'll have a look at it today. Thanks Andrew - minor clarification; unusable on MSVC/Win32. I suspect Mingw builds may be fine, as they use a much older runtime. Of course, we've used MSVC++ for the installer builds for years now. What exactly is the known combination of things that don't work, and things that do work? My only 32 bit test environment for this (ASPerl 5.12.2 build 1202 [293621], built Sep 6, 2010, Visual C++ Express 2008, Windows XP SP3) doesn't seem to have any problem building and running plperl. That makes it tough to test if I don't know what exactly needs to change to break things. We're using VC++ 2010 Pro with ASPerl 5.14.2.1402 for 9.2, and VC++ 2008 Pro with ASPerl 5.14.1.1401 at present. Our CM team have tried multiple versions of Perl though, and seen the issue with 5.10 and 5.12 as well though. 5.8 seemed to be OK. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade's exec_prog() coding improvement
On 08/31/2012 10:52 AM, Andrew Dunstan wrote: On 08/24/2012 11:44 AM, Alvaro Herrera wrote: Again, win32 testing would be welcome. Sadly, buildfarm does not run pg_upgrade's make check. Yesterday I added a new module to the buildfarm client code to run this (https://github.com/PGBuildFarm/client-code/commit/ab812cb9920c65e39ec7358dc816371f1fef31eb). It required a couple of tweaks in the base code. This will be in a new buildfarm client release fairly shortly. It's running on crake now, and I will add it to pitta to get some Windows coverage. but it's not working on pitta :-( It will take me some time to work out why, and I have several more urgent things on my plate. Meanwhile at least we have Linux coverage. 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] _USE_32BIT_TIME_T Patch
On Fri, Aug 31, 2012 at 5:18 PM, Dave Page dp...@pgadmin.org wrote: On Fri, Aug 31, 2012 at 4:57 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:14 AM, Dave Page wrote: On Fri, Aug 31, 2012 at 4:10 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:05 AM, Dave Page wrote: I've added this to the release blockers section for 9.2 on the wiki, as without it, pl/perl is unusable on Win32. I'll have a look at it today. Thanks Andrew - minor clarification; unusable on MSVC/Win32. I suspect Mingw builds may be fine, as they use a much older runtime. Of course, we've used MSVC++ for the installer builds for years now. What exactly is the known combination of things that don't work, and things that do work? My only 32 bit test environment for this (ASPerl 5.12.2 build 1202 [293621], built Sep 6, 2010, Visual C++ Express 2008, Windows XP SP3) doesn't seem to have any problem building and running plperl. That makes it tough to test if I don't know what exactly needs to change to break things. We're using VC++ 2010 Pro with ASPerl 5.14.2.1402 for 9.2, and VC++ 2008 Pro with ASPerl 5.14.1.1401 at present. Our CM team have tried multiple versions of Perl though, and seen the issue with 5.10 and 5.12 as well though. 5.8 seemed to be OK. Oh - and a further data point; we discussed the issue with one of the senior engineers at ActiveState who confirmed that they do use _USE_32BIT_TIME_T on Win32, and that not using it when compiling apps that link with Perl is a known cause of crashes amongst their users. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Use of systable_beginscan_ordered in event trigger patch
Tom Lane t...@sss.pgh.pa.us writes: Or maybe we should disable event triggers altogether in standalone mode? Would something as simple as the attached work for doing that? (passes make check and I did verify manually that postmaster --single is happy with it and skipping Event Triggers). Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support *** a/src/backend/commands/event_trigger.c --- b/src/backend/commands/event_trigger.c *** *** 567,572 EventTriggerDDLCommandStart(Node *parsetree) --- 567,585 EventTriggerData trigdata; /* + * Event Triggers are completely disabled in standalone mode so as not to + * prevent fixing a problematic situation. + * + * To enable Event Triggers in standalone mode we would have to stop using + * systable_beginscan_ordered so that it's still possible to rebuild + * corrupt indexes (thanks to ignore_system_indexes). One way to do that is + * implementing a heapscan-and-sort code path to use when + * ignore_system_indexes is set. + */ + if (!IsUnderPostmaster) + return; + + /* * We want the list of command tags for which this procedure is actually * invoked to match up exactly with the list that CREATE EVENT TRIGGER * accepts. This debugging cross-check will throw an error if this -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] _USE_32BIT_TIME_T Patch
On 08/31/2012 12:18 PM, Dave Page wrote: On Fri, Aug 31, 2012 at 4:57 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:14 AM, Dave Page wrote: On Fri, Aug 31, 2012 at 4:10 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:05 AM, Dave Page wrote: I've added this to the release blockers section for 9.2 on the wiki, as without it, pl/perl is unusable on Win32. I'll have a look at it today. Thanks Andrew - minor clarification; unusable on MSVC/Win32. I suspect Mingw builds may be fine, as they use a much older runtime. Of course, we've used MSVC++ for the installer builds for years now. What exactly is the known combination of things that don't work, and things that do work? My only 32 bit test environment for this (ASPerl 5.12.2 build 1202 [293621], built Sep 6, 2010, Visual C++ Express 2008, Windows XP SP3) doesn't seem to have any problem building and running plperl. That makes it tough to test if I don't know what exactly needs to change to break things. We're using VC++ 2010 Pro with ASPerl 5.14.2.1402 for 9.2, and VC++ 2008 Pro with ASPerl 5.14.1.1401 at present. Our CM team have tried multiple versions of Perl though, and seen the issue with 5.10 and 5.12 as well though. 5.8 seemed to be OK. OK so from that I'm guessing the issue is probably VC++ 2010, which I don't have at all, let alone on a 32-bit machine :-( Oh, well, I'll look and see if I feel comfortable about the patch anyway. 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] _USE_32BIT_TIME_T Patch
On Fri, Aug 31, 2012 at 5:37 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 12:18 PM, Dave Page wrote: On Fri, Aug 31, 2012 at 4:57 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:14 AM, Dave Page wrote: On Fri, Aug 31, 2012 at 4:10 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:05 AM, Dave Page wrote: I've added this to the release blockers section for 9.2 on the wiki, as without it, pl/perl is unusable on Win32. I'll have a look at it today. Thanks Andrew - minor clarification; unusable on MSVC/Win32. I suspect Mingw builds may be fine, as they use a much older runtime. Of course, we've used MSVC++ for the installer builds for years now. What exactly is the known combination of things that don't work, and things that do work? My only 32 bit test environment for this (ASPerl 5.12.2 build 1202 [293621], built Sep 6, 2010, Visual C++ Express 2008, Windows XP SP3) doesn't seem to have any problem building and running plperl. That makes it tough to test if I don't know what exactly needs to change to break things. We're using VC++ 2010 Pro with ASPerl 5.14.2.1402 for 9.2, and VC++ 2008 Pro with ASPerl 5.14.1.1401 at present. Our CM team have tried multiple versions of Perl though, and seen the issue with 5.10 and 5.12 as well though. 5.8 seemed to be OK. OK so from that I'm guessing the issue is probably VC++ 2010, which I don't have at all, let alone on a 32-bit machine :-( Oh, well, I'll look and see if I feel comfortable about the patch anyway. It's only 2010 for 9.2. We're using 2008 with 9.1, which also exhibits the problem (see the bug report linked in my first post on this thread). -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] _USE_32BIT_TIME_T Patch
On 08/31/2012 12:41 PM, Dave Page wrote: On Fri, Aug 31, 2012 at 5:37 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 12:18 PM, Dave Page wrote: On Fri, Aug 31, 2012 at 4:57 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:14 AM, Dave Page wrote: On Fri, Aug 31, 2012 at 4:10 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:05 AM, Dave Page wrote: I've added this to the release blockers section for 9.2 on the wiki, as without it, pl/perl is unusable on Win32. I'll have a look at it today. Thanks Andrew - minor clarification; unusable on MSVC/Win32. I suspect Mingw builds may be fine, as they use a much older runtime. Of course, we've used MSVC++ for the installer builds for years now. What exactly is the known combination of things that don't work, and things that do work? My only 32 bit test environment for this (ASPerl 5.12.2 build 1202 [293621], built Sep 6, 2010, Visual C++ Express 2008, Windows XP SP3) doesn't seem to have any problem building and running plperl. That makes it tough to test if I don't know what exactly needs to change to break things. We're using VC++ 2010 Pro with ASPerl 5.14.2.1402 for 9.2, and VC++ 2008 Pro with ASPerl 5.14.1.1401 at present. Our CM team have tried multiple versions of Perl though, and seen the issue with 5.10 and 5.12 as well though. 5.8 seemed to be OK. OK so from that I'm guessing the issue is probably VC++ 2010, which I don't have at all, let alone on a 32-bit machine :-( Oh, well, I'll look and see if I feel comfortable about the patch anyway. It's only 2010 for 9.2. We're using 2008 with 9.1, which also exhibits the problem (see the bug report linked in my first post on this thread). Well, that makes things harder to diagnose. Why isn't my 2008 / ASPerl 5.12.2 setup exhibiting the problem? 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] _USE_32BIT_TIME_T Patch
On Fri, Aug 31, 2012 at 5:51 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 12:41 PM, Dave Page wrote: On Fri, Aug 31, 2012 at 5:37 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 12:18 PM, Dave Page wrote: On Fri, Aug 31, 2012 at 4:57 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:14 AM, Dave Page wrote: On Fri, Aug 31, 2012 at 4:10 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:05 AM, Dave Page wrote: I've added this to the release blockers section for 9.2 on the wiki, as without it, pl/perl is unusable on Win32. I'll have a look at it today. Thanks Andrew - minor clarification; unusable on MSVC/Win32. I suspect Mingw builds may be fine, as they use a much older runtime. Of course, we've used MSVC++ for the installer builds for years now. What exactly is the known combination of things that don't work, and things that do work? My only 32 bit test environment for this (ASPerl 5.12.2 build 1202 [293621], built Sep 6, 2010, Visual C++ Express 2008, Windows XP SP3) doesn't seem to have any problem building and running plperl. That makes it tough to test if I don't know what exactly needs to change to break things. We're using VC++ 2010 Pro with ASPerl 5.14.2.1402 for 9.2, and VC++ 2008 Pro with ASPerl 5.14.1.1401 at present. Our CM team have tried multiple versions of Perl though, and seen the issue with 5.10 and 5.12 as well though. 5.8 seemed to be OK. OK so from that I'm guessing the issue is probably VC++ 2010, which I don't have at all, let alone on a 32-bit machine :-( Oh, well, I'll look and see if I feel comfortable about the patch anyway. It's only 2010 for 9.2. We're using 2008 with 9.1, which also exhibits the problem (see the bug report linked in my first post on this thread). Well, that makes things harder to diagnose. Why isn't my 2008 / ASPerl 5.12.2 setup exhibiting the problem? No idea. Differences in the SDK perhaps? You're using VC++ Express which (if memory serves) you have to download the SDK independently, whereas we get a bundled, and possibly slightly different version with the Pro edition. As a side note - I'm not sure why _USE_32BIT_TIME_T was removed in the first place; it was added specifically to avoid this sort of problem, though iirc at the time we were thinking of extensions like Slony and PostGIS being built with Mingw for use with the VC++ built server. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Cascading replication and recovery_target_timeline='latest'
On Fri, Aug 31, 2012 at 5:03 PM, Heikki Linnakangas hlinn...@iki.fi wrote: When a cascading standby launches a new walsender, it fetches the current recovery timeline: /* * Use the recovery target timeline ID during recovery */ if (am_cascading_walsender) ThisTimeLineID = GetRecoveryTargetTLI(); Comment in GetRecoveryTargetTLI() does this: /* RecoveryTargetTLI doesn't change so we need no lock to copy it */ return XLogCtl-RecoveryTargetTLI; That comment is not true. RecoveryTargetTLI can change during recovery, if you set recovery_target_timeline='latest'. In 'latest' mode, when the (apparent) end of WAL is reached, the archive is scanned for any new timeline history files that may have appeared. If a new timeline is found, RecoveryTargetTLI is updated, and recovery is continued on the new timeline. Right. We need lock there for now. Aside from the missing locking, I wonder what that does to a cascaded standby. If there is an active walsender running while RecoveryTargetTLI is changed, I think what will happen is that the walsender will continue to stream WAL from the old timeline, but because the startup process is now actually replaying from a different timeline, the walsender will send bogus WAL to the standby. Good catch! That's really problem. To address that, we should terminate all cascading walsenders when the timeline history file is read and the recovery target timeline is changed? When a standby ends recovery, creates a new timeline, and switches to normal operation, postmaster terminates all walsenders because of the timeline change. But don't we have a race condition there, with similar effect? It might take a while for a walsender to die, and in that window, it might send bogus WAL to the cascaded standby. No, I think. The cascading walsender can send only WAL data, up to min(replay_location, receive_location, restore_location). IOW, it sends only replayed, received (i.e., streamed), and restored WAL files. These WAL files belong to old timeline, so ISTM that the cascading walsender cannot send any new-timeline WAL files. Regards, -- Fujii Masao -- Sent 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: shared session variables
Pavel Stehule pavel.steh...@gmail.com writes: Pavel, you didn't say what you think about the WITH FUNCTION proposal? I don't like it - this proposal is too lispish - it is not SQL We're not doing lambda here, only extending a facility that we rely on today. The function would be named, for one. I don't know what you mean by SQL being lispish here, and I can't imagine why it would be something to avoid. And you didn't say how do you want to turn a utility statement into something that is able to return a result set. if we support real procedures ala sybase procedures (MySQL, MSSQL..) - then we can return result with same mechanism - there are no significant difference between DO and CALL statements - you don't know what will be result type before you call it. Currently we don't have CALL, and we have DO which is not a query but a utility statement. Are you proposing to implement CALL? What would be the difference between making DO a query and having CALL? Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] _USE_32BIT_TIME_T Patch
On 08/31/2012 01:10 PM, Dave Page wrote: On Fri, Aug 31, 2012 at 5:51 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 12:41 PM, Dave Page wrote: On Fri, Aug 31, 2012 at 5:37 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 12:18 PM, Dave Page wrote: On Fri, Aug 31, 2012 at 4:57 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:14 AM, Dave Page wrote: On Fri, Aug 31, 2012 at 4:10 PM, Andrew Dunstan and...@dunslane.net wrote: On 08/31/2012 11:05 AM, Dave Page wrote: I've added this to the release blockers section for 9.2 on the wiki, as without it, pl/perl is unusable on Win32. I'll have a look at it today. Thanks Andrew - minor clarification; unusable on MSVC/Win32. I suspect Mingw builds may be fine, as they use a much older runtime. Of course, we've used MSVC++ for the installer builds for years now. What exactly is the known combination of things that don't work, and things that do work? My only 32 bit test environment for this (ASPerl 5.12.2 build 1202 [293621], built Sep 6, 2010, Visual C++ Express 2008, Windows XP SP3) doesn't seem to have any problem building and running plperl. That makes it tough to test if I don't know what exactly needs to change to break things. We're using VC++ 2010 Pro with ASPerl 5.14.2.1402 for 9.2, and VC++ 2008 Pro with ASPerl 5.14.1.1401 at present. Our CM team have tried multiple versions of Perl though, and seen the issue with 5.10 and 5.12 as well though. 5.8 seemed to be OK. OK so from that I'm guessing the issue is probably VC++ 2010, which I don't have at all, let alone on a 32-bit machine :-( Oh, well, I'll look and see if I feel comfortable about the patch anyway. It's only 2010 for 9.2. We're using 2008 with 9.1, which also exhibits the problem (see the bug report linked in my first post on this thread). Well, that makes things harder to diagnose. Why isn't my 2008 / ASPerl 5.12.2 setup exhibiting the problem? No idea. Differences in the SDK perhaps? You're using VC++ Express which (if memory serves) you have to download the SDK independently, whereas we get a bundled, and possibly slightly different version with the Pro edition. As a side note - I'm not sure why _USE_32BIT_TIME_T was removed in the first place; it was added specifically to avoid this sort of problem, though iirc at the time we were thinking of extensions like Slony and PostGIS being built with Mingw for use with the VC++ built server. OK. Well, I didn't quite like the submitted patch for a couple of reasons. First, it only affected VC2010 builds, and you said these weren't the only ones affected. And second it didn't really highlight what was being done. So here are two patches, one for HEAD/9.2 and one for earlier releases, that do this in a different way that is more obvious, and for all versions of VC. Please test. I will also test these. cheers andrew diff --git a/src/tools/msvc/MSBuildProject.pm b/src/tools/msvc/MSBuildProject.pm index ac99345..2e3eab6 100644 --- a/src/tools/msvc/MSBuildProject.pm +++ b/src/tools/msvc/MSBuildProject.pm @@ -61,16 +61,22 @@ EOF print $f EOF; /PropertyGroup EOF + + # We have to use this flag on 32 bit targets because the 32bit perls + # are built with it and sometimes crash if we don't. + my $use_32bit_time_t = + $self-{platform} eq 'Win32' ? '_USE_32BIT_TIME_T;' : ''; + $self-WriteItemDefinitionGroup( $f, 'Debug', - { defs= '_DEBUG;DEBUG=1;', + { defs= _DEBUG;DEBUG=1;$use_32bit_time_t, opt = 'Disabled', strpool = 'false', runtime = 'MultiThreadedDebugDLL' }); $self-WriteItemDefinitionGroup( $f, 'Release', - { defs= '', + { defs= $use_32bit_time_t, opt = 'Full', strpool = 'true', runtime = 'MultiThreadedDLL' }); diff --git a/src/tools/msvc/VCBuildProject.pm b/src/tools/msvc/VCBuildProject.pm index 1022329..6246826 100644 --- a/src/tools/msvc/VCBuildProject.pm +++ b/src/tools/msvc/VCBuildProject.pm @@ -32,9 +32,16 @@ sub WriteHeader PlatformsPlatform Name=$self-{platform}//Platforms Configurations EOF + + # We have to use this flag on 32 bit targets because the 32bit perls + # are built with it and sometimes crash if we don't. + my $use_32bit_time_t = + $self-{platform} eq 'Win32' ? '_USE_32BIT_TIME_T;' : ''; + + $self-WriteConfiguration( $f, 'Debug', - { defs = '_DEBUG;DEBUG=1;', + { defs = _DEBUG;DEBUG=1;$use_32bit_time_t, wholeopt = 0, opt = 0, strpool = 'false', @@ -42,7 +49,7 @@ EOF $self-WriteConfiguration( $f, 'Release', - { defs = '', + { defs = $use_32bit_time_t, wholeopt = 0, opt = 3, strpool = 'true', diff --git a/src/tools/msvc/Project.pm b/src/tools/msvc/Project.pm index 66752f9..ad62272 100644 --- a/src/tools/msvc/Project.pm +++ b/src/tools/msvc/Project.pm @@ -471,10 +471,23 @@ sub WriteHeader PlatformsPlatform Name=$self-{platform}//Platforms Configurations EOF + # We have to use this flag on
Re: [HACKERS] patch: shared session variables
2012/8/31 Dimitri Fontaine dimi...@2ndquadrant.fr: Pavel Stehule pavel.steh...@gmail.com writes: Pavel, you didn't say what you think about the WITH FUNCTION proposal? I don't like it - this proposal is too lispish - it is not SQL We're not doing lambda here, only extending a facility that we rely on today. The function would be named, for one. I don't know what you mean by SQL being lispish here, and I can't imagine why it would be something to avoid. And you didn't say how do you want to turn a utility statement into something that is able to return a result set. if we support real procedures ala sybase procedures (MySQL, MSSQL..) - then we can return result with same mechanism - there are no significant difference between DO and CALL statements - you don't know what will be result type before you call it. Currently we don't have CALL, and we have DO which is not a query but a utility statement. Are you proposing to implement CALL? What would be the difference between making DO a query and having CALL? defacto a CALL statement implementation can solve this issue. The core of this issue is an impossibility using parameters for utility statements. CALL and DO are utility statements - and if we can use parameters for CALL, then we can do it for DO too. CALL statement starts a predefined batch - inside this batch, you can do anything - can use COMMIT, ROLLBACK, SELECTs, ... DO is some batch with immediate start. Sure, there is relative significant between stored procedures implemented in popular RDBMS and although I don't like T-SQL too much, I like sybase concept of stored procedures - it is strong and very useful for maintaining tasks. Regards Pavel Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] _USE_32BIT_TIME_T Patch
Dave Page dp...@pgadmin.org writes: As a side note - I'm not sure why _USE_32BIT_TIME_T was removed in the first place; it was added specifically to avoid this sort of problem, though iirc at the time we were thinking of extensions like Slony and PostGIS being built with Mingw for use with the VC++ built server. We removed it when we changed our internal time_t usage to 64 bits: http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=cd004067742ee16ee63e55abfb4acbd5f09fbaab Possibly that was just a brain fade caused by failing to think about the distinction between pg_time_t and system time_t. However, the code has been like that since 8.4, and nobody complained before. I share Andrew's unease about whether this issue is fully understood. 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] fairly useless psql compatibility warning?
On Fri, Aug 31, 2012 at 4:59 AM, Magnus Hagander mag...@hagander.net wrote: On Fri, Aug 31, 2012 at 4:07 AM, Peter Eisentraut pete...@gmx.net wrote: psql has supported older servers for a great while now, so this sort of things seems pretty useless now: psql (9.2rc1, server 9.1.4) WARNING: psql version 9.2, server version 9.1. Some psql features might not work I think it should be reduced to warning when connected to a newer server. Seems like a good idea to me. +1. (Obviously keeping the first line that shows that the version numbers are different, but not WARNING) +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: pgbench - aggregation of info written into log
On Thu, Aug 30, 2012 at 5:55 PM, Tomas Vondra t...@fuzzy.cz wrote: It does, but AFAIK the -l means logging. I suppose --aggregate-interval would be a good option name, I don't see a reason to put there the additional word when there are other aggregated values (e.g. num of transactions). Oh, I was thinking that the l was for latency, but maybe not. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] _USE_32BIT_TIME_T Patch
On 08/31/2012 03:36 PM, Tom Lane wrote: Dave Page dp...@pgadmin.org writes: As a side note - I'm not sure why _USE_32BIT_TIME_T was removed in the first place; it was added specifically to avoid this sort of problem, though iirc at the time we were thinking of extensions like Slony and PostGIS being built with Mingw for use with the VC++ built server. We removed it when we changed our internal time_t usage to 64 bits: http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=cd004067742ee16ee63e55abfb4acbd5f09fbaab Possibly that was just a brain fade caused by failing to think about the distinction between pg_time_t and system time_t. However, the code has been like that since 8.4, and nobody complained before. I share Andrew's unease about whether this issue is fully understood. OTOH, the fact that we used to have it and nothing broke that we know of is somewhat reassuring. I'm not sure what we need to do to progress on this, especially re the back branches. 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] _USE_32BIT_TIME_T Patch
Andrew Dunstan and...@dunslane.net writes: I'm not sure what we need to do to progress on this, especially re the back branches. The calendar might help us here. 9.2 is due to wrap next week, but it will likely be a couple of months before we contemplate new back-branch releases. So we could push a fix that we don't have 100% confidence in, knowing that there is time to recover before it will ship in any of the proven branches. Releasing it in 9.2.0 will afford an opportunity for more testing than we can do by ourselves. That's not to take anything away from the fact that we ought to test as many cases as we can now. But we do have some margin for error. 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] _USE_32BIT_TIME_T Patch
On 08/31/2012 06:39 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: I'm not sure what we need to do to progress on this, especially re the back branches. The calendar might help us here. 9.2 is due to wrap next week, but it will likely be a couple of months before we contemplate new back-branch releases. So we could push a fix that we don't have 100% confidence in, knowing that there is time to recover before it will ship in any of the proven branches. Releasing it in 9.2.0 will afford an opportunity for more testing than we can do by ourselves. That's not to take anything away from the fact that we ought to test as many cases as we can now. But we do have some margin for error. OK, so I have tested it on my 32bit setup and it's working, so I'm going to commit this for HEAD/9.2 now, so we can get that wider testing. 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] effective_io_concurrency
On Thu, Aug 30, 2012 at 1:25 PM, Peter Geoghegan pe...@2ndquadrant.com wrote: Just how helpful is effective_io_concurrency? Did someone produce a benchmark at some point? Attached is a benchmark I put together a while ago. I don't know how close to real world it might be. I haven't seen it in the wild, but I'm anticipating I will see something like it soon. The benefit is of course reduced if you were to apply high levels of -c $clients, but cases where -c 1 are frequent in data mining and such. Obviously it would be better to cluster the giant table on abalance, but that might be hard to do and then hard to maintain. Size of generated database is about 70GB. Server had 16GB of RAM. It is a virtual server, and I don't know the real hardware, but am told it has 8 spindles. (if the range used in the query is increased from +100 to +1000, even more speed up is available) effective_io_concurrencytps 0 2.06273064 1 2.1693092 2 4.11726948 3 5.90785352 4 6.65748384 5 7.58297556 6 8.36130404 7 8.86561116 8 9.2673546 9 9.57076168 10 9.8558758 11 10.11641752 12 10.316673 13 10.46953468 14 10.65962516 15 10.76328636 16 10.86442376 17 10.96362168 18 11.04371008 19 11.19470171 20 11.30110867 21 11.39553967 22 11.45420263 23 11.54764725 24 11.61949146 25 11.65659225 26 11.68992392 27 11.75944667 28 11.7456135 29 11.80111779 30 11.72897188 31 11.7210945 32 11.73292504 33 11.734458 34 11.75195196 35 11.79079175 36 11.73687979 37 11.79583758 38 11.75879063 39 11.77868596 40 11.74685896 41 11.76294508 42 11.7213265 43 11.68458158 44 11.71036729 45 11.72728229 46 11.72063796 47 11.80322429 48 11.83563058 49 11.81916996 50 11.73395892 Cheers, Jeff bitmap.sql Description: Binary data bench.sh Description: Bourne shell script setup.sql Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] too much pgbench init output
When initializing a large database, pgbench writes tons of %d tuples done lines. I propose to change this to a sort of progress counter that stays on the same line, as in the attached patch. diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c index 00cab73..b5f3054 100644 --- a/contrib/pgbench/pgbench.c +++ b/contrib/pgbench/pgbench.c @@ -1420,8 +1420,9 @@ } if (j % 10 == 0) - fprintf(stderr, %d tuples done.\n, j); + fprintf(stderr, %d of %d tuples done.\r, j, naccounts * scale); } + fprintf(stderr, \n); if (PQputline(con, \\.\n)) { fprintf(stderr, very last PQputline failed\n); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Does the SQL standard actually define LATERAL anywhere?
As implemented in HEAD, LATERAL means to run a nestloop in which the lateral-referencing query is run once per row of the referenced table, and the resulting rows are joined to just that row of the referenced table. So for example: # select * from (values (2),(4)) v(x), lateral generate_series(1,x); x | generate_series ---+- 2 | 1 2 | 2 4 | 1 4 | 2 4 | 3 4 | 4 (6 rows) It suddenly struck me though that there's another plausible interpretation of this syntax: perhaps we should generate all the rows of the referencing query as above, and then join them to *all* rows of the rest of the query. That is, should the above query generate x | generate_series ---+- 2 | 1 2 | 1 2 | 2 2 | 2 2 | 3 2 | 4 4 | 1 4 | 1 4 | 2 4 | 2 4 | 3 4 | 4 (12 rows) This behavior doesn't seem as useful to me --- I think you'd nearly always end up adding additional WHERE clauses to get rid of the extra rows. However, there should not be any judgment calls involved here; this is a spec-defined syntax so surely the SQL standard ought to tell us what to do. But I'm darned if I see anything in the standard that defines the actual *behavior* of a LATERAL query. Please point out chapter and verse of what I'm missing. Or, perhaps we can hold some committee members' feet to the fire for a ruling? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for LATERAL subqueries
Merlin Moncure mmonc...@gmail.com writes: This is just awesome. Anyways, I was looking around the docs for references to the old methodology of select list SRF function calls. This paragraph: http://www.postgresql.org/docs/devel/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET could probably use some enhancement describing best practices in a LATERAL world and more examples of dealing with set returning functions in general. Done, thanks for the suggestion. I also noticed that the build in SRF page (http://www.postgresql.org/docs/devel/static/functions-srf.html) lies with the comment This section describes functions that possibly return more than one row. Currently the only functions in this class are series generating functions since at minimum we have 'unnest' so that page could use some wordsmithing as well. Yeah, there's also some regexp-related SRFs, as well as a boatload of built-in SRFs that are mainly meant to underlie views. I guess we could try to force all of those into this page, but it doesn't really seem like it'd be an improvement. I took out the claim that these were all such functions, instead. 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] Does the SQL standard actually define LATERAL anywhere?
2012/9/1 Tom Lane t...@sss.pgh.pa.us: As implemented in HEAD, LATERAL means to run a nestloop in which the lateral-referencing query is run once per row of the referenced table, and the resulting rows are joined to just that row of the referenced table. So for example: # select * from (values (2),(4)) v(x), lateral generate_series(1,x); x | generate_series ---+- 2 | 1 2 | 2 4 | 1 4 | 2 4 | 3 4 | 4 (6 rows) It suddenly struck me though that there's another plausible interpretation of this syntax: perhaps we should generate all the rows of the referencing query as above, and then join them to *all* rows of the rest of the query. That is, should the above query generate x | generate_series ---+- 2 | 1 2 | 1 2 | 2 2 | 2 2 | 3 2 | 4 4 | 1 4 | 1 4 | 2 4 | 2 4 | 3 4 | 4 (12 rows) This behavior doesn't seem as useful to me --- I think you'd nearly always end up adding additional WHERE clauses to get rid of the extra rows. However, there should not be any judgment calls involved here; this is a spec-defined syntax so surely the SQL standard ought to tell us what to do. But I'm darned if I see anything in the standard that defines the actual *behavior* of a LATERAL query. The second variant is really useless - I don't see sense too. Regards Pavel Please point out chapter and verse of what I'm missing. Or, perhaps we can hold some committee members' feet to the fire for a ruling? 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