Re: [HACKERS] [PATCHES] pgstattuple extension for indexes
Hi folks,
As I said on -PATCHES, I've been working on an utility to get
a b-tree index information. I'm happy to introduce
my new functions to you.
pgstattuple module provides a `pgstatindex()`, and other small
functions, which allow you to get b-tree internal information.
I believe this module will be helpful to know b-tree index deeply.
So please try it, send comment to me, and have fun.
Thanks,
--
NAGAYASU Satoshi [EMAIL PROTECTED]
-
pgbench=# \x
Expanded display is on.
pgbench=# SELECT * FROM pgstatindex('accounts_pkey');
-[ RECORD 1 ]--+
version| 2
tree_level | 1
index_size | 3588096
root_block_no | 3
internal_pages | 0
leaf_pages | 437
empty_pages| 0
deleted_pages | 0
avg_leaf_density | 59.5
leaf_fragmentation | 49.89
-
pgstatindex.tar.gz
Description: GNU Zip compressed data
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] GUC with units, details
Tom Lane wrote: If that actually worked, it'd be one thing, but it doesn't work and isn't going to do so in 8.2. So I think people will indeed be trying to use setting || unit for display purposes. In any case 8kB isn't a valid unit. I thought we set SHOW ALL aside for display purposes and pg_settings for processing purposes? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] extension for sql update
Am Donnerstag, den 27.07.2006, 08:30 -0400 schrieb Tom Lane: Susanne Ebrecht [EMAIL PROTECTED] writes: ... We could provide the mixed update syntax and leave the typed row value expression for the next release. Do you agree? I don't really see the point --- the patch won't provide any new functionality in anything like its current form, because you can always just write the separate expressions in the simple one to one way. If we do offer the row-on-the-left syntax then people will try to put sub-selects on the right, and won't get anything beyond an unhelpful syntax error message. So my vote would be to leave it alone until we have a more complete implementation. Look at my intention, why I wrote this patch: In recent years I migrated many customers applications from oracle or informix to postgresql. Every time it was a very painful and annoying job to grep through the code of functions and the whole software, to find all updates and change them manually. Far ago at university, I learned both syntax as standard syntax. Example: set a=1, b=2, c=3 and set (a,b,c)=(1,2,3) I admit, I prefered the second form, too, when I only used informix and it seems also my customers do so. Still now, I never found this syntax with select statement. I am not sure if this is possible with informix or oracle. regards Susanne regards, tom lane signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [PATCHES] putting CHECK_FOR_INTERRUPTS in qsort_comparetup()
On 7/15/06, Tom Lane [EMAIL PROTECTED] wrote: Anyway, Qingqing's question still needs to be answered: how can a sort of under 30k items take so long? It happens because (as previously suggested by Tom) the dataset for the 'short' (~10k rows, .3 sec) sort has no rows whose leftmost fields evaluate to 'equal' when passed to the qsort compare function. The 'long' sort, (~30k rows, 78 sec) has plenty of rows whose first 6 columns all evaluate as 'equal' when the rows are compared. For the 'long' data, the compare moves on rightward until it encounters 'flato', which is a TEXT column with an average length of 7.5k characters (with some rows up to 400k). The first 6 columns are mostly INTEGER, so compares on them are relatively inexpensive. All the expensive compares on 'flato' account for the disproportionate difference in sort times, relative to the number of rows in each set. As for the potential for memory leaks - thinking about it. Thanks, Charles Duffy. Peter Eisentraut [EMAIL PROTECTED] writes: The merge sort is here: http://sourceware.org/cgi-bin/cvsweb.cgi/libc/stdlib/msort.c?rev=1.21content-type=text/x-cvsweb-markupcvsroot=glibc It uses alloca, so we're good here. Uh ... but it also uses malloc, and potentially a honkin' big malloc at that (up to a quarter of physical RAM). So I'm worried again. Anyway, Qingqing's question still needs to be answered: how can a sort of under 30k items take so long? regards, tom lane Column | Type | Modifiers ---+-+--- record| integer | commr1| integer | envr1 | oid | docin | integer | creat | integer | flati | text| flato | text| doc | text| docst | integer | vlord | integer | vl0 | integer | vl1 | date| vl2 | text| vl3 | text| vl4 | text| vl5 | text| vl6 | text| vl7 | date| vl8 | text| vl9 | integer | ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] The vacuum-ignore-vacuum patch
Ühel kenal päeval, N, 2006-07-27 kell 22:05, kirjutas Bruce Momjian: Another idea Jan had today was whether we could vacuum more rows if a long-running backend is in serializable mode, like pg_dump. I don't see how this gives us ability to vacuum more rows, as the snapshot of a serializable transaction is the oldest one. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] request: support of array in plperl OUT arguments
Hello,
I miss better support OUT arguments in plerlu:
create or replace function foo(out p varchar[]) as $$ return { p = [pavel,
jana] }; $$ language plperlu;
postgres=# select foo();
ERROR: array value must start with { or dimension information
postgres=#
I starting work on it. I hope It will be done before current feature freeze.
Regards
Pavel Stehule
_
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] LWLock statistics collector (was: CSStorm occurred again by postgreSQL8.2)
Hi hackers, I tackled the performance problem on SUBTRANS module with Okano. He and I reach a conclusion that SubTrans log is heavily read on a specific access pattern in my TPC-W implementation. There seems to be awful traffic on SUBTRANS to check visivility of tuples in HeapTupleSatisfiesSnapshot(). I'll report more details later. BTW, I wrote a patch to collect statistics of Light-weight locks for analysis. We have already had Trace_lwlocks option, but it can collect statistics with less impact. The following is an output of the patch (on 8.1). Are you interested in the feature? and I'll port it to HEAD and post it. # SELECT * FROM pg_stat_lwlocks; kind | pg_stat_get_lwlock_name | sh_call | sh_wait | ex_call | ex_wait | --+++---+---+---+- 0 | BufMappingLock | 559375542 | 33542 |320092 | 24025 | 1 | BufFreelistLock| 0 | 0 |370709 | 47 | 2 | LockMgrLock| 0 | 0 | 41718885 | 734502 | 3 | OidGenLock | 33 | 0 | 0 | 0 | 4 | XidGenLock | 12572279 | 10095 | 11299469 | 20089 | 5 | ProcArrayLock |8371330 | 72052 | 16965667 | 603294 | 6 | SInvalLock | 38822428 | 435 | 25917 | 128 | 7 | FreeSpaceLock | 0 | 0 | 16787 | 4 | 8 | WALInsertLock | 0 | 0 | 1239911 | 885 | 9 | WALWriteLock | 0 | 0 | 69907 | 5589 | 10 | ControlFileLock| 0 | 0 | 16686 | 1 | 11 | CheckpointLock | 0 | 0 |34 | 0 | 12 | CheckpointStartLock| 69509 | 0 |34 | 1 | 13 | CLogControlLock| 0 | 0 |236763 | 183 | 14 | SubtransControlLock| 0 | 0 | 753773945 | 205273395 | 15 | MultiXactGenLock | 66 | 0 | 0 | 0 | 16 | MultiXactOffsetControlLock | 0 | 0 |35 | 0 | 17 | MultiXactMemberControlLock | 0 | 0 |34 | 0 | 18 | RelCacheInitLock | 0 | 0 | 0 | 0 | 19 | BgWriterCommLock | 0 | 0 | 61457 | 1 | 20 | TwoPhaseStateLock | 33 | 0 | 0 | 0 | 21 | TablespaceCreateLock | 0 | 0 | 0 | 0 | 22 | BufferIO | 0 | 0 |695627 | 16 | 23 | BufferContent | 3568231805 | 1897 | 1361394 | 829 | 24 | CLog | 0 | 0 | 0 | 0 | 25 | SubTrans | 138571621 | 143208883 | 8122181 | 8132646 | 26 | MultiXactOffset| 0 | 0 | 0 | 0 | 27 | MultiXactMember| 0 | 0 | 0 | 0 | (28 rows) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] GUC with units, details
Ühel kenal päeval, N, 2006-07-27 kell 01:03, kirjutas Tom Lane: Michael Glaesemann [EMAIL PROTECTED] writes: I've seen this mentioned a couple of times. I'm not nearly as familiar with these settings as I should be, but it seems to me that if the memory size *does* need to be a integral multiple of page size, e.g., n * page_size = memory_size, why isn't that memory configured as the integer n rather than memory_size? It is. For instance shared_buffers is configured as the number of buffers. What we're talking about here is ways to specify the intended usage with other units (eg I want N megabytes of shared buffers) but that's not going to magically let you allocate half a shared buffer. What are the plans for SHOW command ? Will it show actual number of buffers allocated, original number requested or actual amount allocated in units requested ? Or some combination of above ? Peter's not said exactly how he plans to deal with this, but I suppose it'll round off one way or the other ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] [Fwd: RE: Build farm on Windows]
Can one of the Windows buildfarm owners please try building and running make check by hand rather than using the buildfarm script? It looks like they all stopped reporting around the same time, and this might give us a better clue about when things fall over. Also, if you're up for it, please try reversing this patch, which looks innocuous enough, but is the only thing I can see in the relevant time period that looks at all suspicious: http://archives.postgresql.org/pgsql-committers/2006-07/msg00256.php cheers andrew Original Message Subject:RE: Build farm on Windows Date: Fri, 28 Jul 2006 13:53:18 +1000 From: Phil Cairns [EMAIL PROTECTED] To: 'Andrew Dunstan' [EMAIL PROTECTED] Hi Andrew, this is yak calling from Australia. I think I have a problem here with the HEAD build. The last few times I've run the build, it has sat in make check for a long time (well over an hour). According to the Task Manager, postmaster.exe is taking most of this time, and it also seems to be leaking memory. After about an hour of running today, postmaster.exe is using about 100MB of RAM, and is still busily firing off instances of postgres.exe. The process is hard to kill as well. It doesn't respond to a Ctrl+C in the MSYS window, so I kill it by stopping postmaster.exe from within the Task Manager, and it cleans things up from there. Does this sound like something wrong with my setup? I'm pretty sure I haven't changed anything since my last successful run 3 days ago. All the best, Phil. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]
Andrew Dunstan wrote: Can one of the Windows buildfarm owners please try building and running make check by hand rather than using the buildfarm script? It looks like they all stopped reporting around the same time, and this might give us a better clue about when things fall over. Also, if you're up for it, please try reversing this patch, which looks innocuous enough, but is the only thing I can see in the relevant time period that looks at all suspicious: http://archives.postgresql.org/pgsql-committers/2006-07/msg00256.php will see what i can do(it definitly hangs in make check here too) - but this issue seem to kill my box up to the point where it is impossible to login(!) and i have to hard-reboot it. Looks like it is churning CPU like mad when that happens ... Stefan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]
Stefan Kaltenbrunner wrote: Andrew Dunstan wrote: Can one of the Windows buildfarm owners please try building and running make check by hand rather than using the buildfarm script? It looks like they all stopped reporting around the same time, and this might give us a better clue about when things fall over. Also, if you're up for it, please try reversing this patch, which looks innocuous enough, but is the only thing I can see in the relevant time period that looks at all suspicious: http://archives.postgresql.org/pgsql-committers/2006-07/msg00256.php will see what i can do(it definitly hangs in make check here too) - but this issue seem to kill my box up to the point where it is impossible to login(!) and i have to hard-reboot it. Looks like it is churning CPU like mad when that happens ... Does it get past the initdb stage? Past db startup? Past creating the regression db? Run any tests and report results? cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Better name/syntax for online index creation
I really like the CREATE INDEX CONCURRENTLY suggestion that I've seem in this thread. That seems like a good alternative to ONLINE and is very easy to understand. Regards, Paul -- View this message in context: http://www.nabble.com/Better-name-syntax-for-%22online%22-index-creation-tf1992993.html#a5538009 Sent from the PostgreSQL - hackers forum at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]
The TimeZone changes are looking might suspicious ... cheers andrew Stefan Kaltenbrunner wrote: Andrew Dunstan wrote: Can one of the Windows buildfarm owners please try building and running make check by hand rather than using the buildfarm script? It looks like they all stopped reporting around the same time, and this might give us a better clue about when things fall over. Also, if you're up for it, please try reversing this patch, which looks innocuous enough, but is the only thing I can see in the relevant time period that looks at all suspicious: http://archives.postgresql.org/pgsql-committers/2006-07/msg00256.php looks like the postmaster fails to startup up: ./pg_regress --temp-install=./tmp_check --top-builddir=../../.. --temp-port=55678 --schedule=./parallel_schedule --multibyte=SQL_ASCII --load-language=plpgsql == removing existing temp installation== == creating temporary installation== == initializing database system == == starting postmaster== pg_regress: postmaster did not start within 60 seconds Examine ./log/postmaster.log for the reason make[2]: *** [check] Error 2 make[2]: Leaving directory `/home/pgbuild/pgfarmbuild/HEAD/pgsql.1436/src/test/regress' make[1]: *** [check] Error 2 make[1]: Leaving directory `/home/pgbuild/pgfarmbuild/HEAD/pgsql.1436/src/test' make: *** [check] Error 2 and the logfile is full of: FATAL: failed to initialize timezone_abbreviations to Default FATAL: failed to initialize timezone_abbreviations to Default LOG: background writer process (PID 1568) exited with exit code 0 LOG: terminating any other active server processes FATAL: failed to initialize timezone_abbreviations to Default LOG: all server processes terminated; reinitializing FATAL: failed to initialize timezone_abbreviations to Default FATAL: failed to initialize timezone_abbreviations to Default LOG: background writer process (PID 244) exited with exit code 0 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing FATAL: failed to initialize timezone_abbreviations to Default FATAL: failed to initialize timezone_abbreviations to Default LOG: background writer process (PID 468) exited with exit code 0 LOG: terminating any other active server processes ... Stefan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] putting CHECK_FOR_INTERRUPTS in qsort_comparetup()
Charles Duffy [EMAIL PROTECTED] writes: ... For the 'long' data, the compare moves on rightward until it encounters 'flato', which is a TEXT column with an average length of 7.5k characters (with some rows up to 400k). The first 6 columns are mostly INTEGER, so compares on them are relatively inexpensive. All the expensive compares on 'flato' account for the disproportionate difference in sort times, relative to the number of rows in each set. Yeah, and it's not just that it's text either. At those sizes, all the values will be toasted, which means each compare is paying the price of fetching multiple rows from the toast table. And decompressing them too, no doubt. These costs are most likely swamping the actual strcoll() (not that that's not bad enough compared to int4cmp). We could probably tweak the sorting code to forcibly detoast sort keys before beginning the sort, but I'm not entirely convinced that would be a win: reading and writing enormous sort keys won't be cheap either. Meanwhile, for a cheap solution: do you really need to sort on flato at all? Maybe sorting on substr(flato,1,100) would be good enough? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] pgstattuple extension for indexes
I thought these new functions were going to be merged into
/contrib/pgstattuple.
---
satoshi nagayasu wrote:
Hi folks,
As I said on -PATCHES, I've been working on an utility to get
a b-tree index information. I'm happy to introduce
my new functions to you.
pgstattuple module provides a `pgstatindex()`, and other small
functions, which allow you to get b-tree internal information.
I believe this module will be helpful to know b-tree index deeply.
So please try it, send comment to me, and have fun.
Thanks,
--
NAGAYASU Satoshi [EMAIL PROTECTED]
-
pgbench=# \x
Expanded display is on.
pgbench=# SELECT * FROM pgstatindex('accounts_pkey');
-[ RECORD 1 ]--+
version| 2
tree_level | 1
index_size | 3588096
root_block_no | 3
internal_pages | 0
leaf_pages | 437
empty_pages| 0
deleted_pages | 0
avg_leaf_density | 59.5
leaf_fragmentation | 49.89
-
[ application/x-gzip is not supported, skipping... ]
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] The vacuum-ignore-vacuum patch
Bruce Momjian [EMAIL PROTECTED] writes: Good question. Imagine you have a serializable transaction like pg_dump, and then you have lots of newer transactions. If pg_dump is xid=12, and all the new transactions start at xid=30, any row created and expired between 12 and 30 can be removed because they are not visible. This reasoning is bogus. It would probably be safe for pg_dump because it's a read-only operation, but it fails badly if the serializable transaction is trying to do updates. An update needs to chase the chain of newer versions of the row forward from the version that's visible to the xact's serializable snapshot, to see if anyone has committed a newer version. Your proposal would remove elements of that chain, thereby possibly allowing the serializable xact to conclude it may update the tuple when it should have given an error. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] The vacuum-ignore-vacuum patch
Hannu Krosing wrote: ?hel kenal p?eval, N, 2006-07-27 kell 22:05, kirjutas Bruce Momjian: Another idea Jan had today was whether we could vacuum more rows if a long-running backend is in serializable mode, like pg_dump. I don't see how this gives us ability to vacuum more rows, as the snapshot of a serializable transaction is the oldest one. Good question. Imagine you have a serializable transaction like pg_dump, and then you have lots of newer transactions. If pg_dump is xid=12, and all the new transactions start at xid=30, any row created and expired between 12 and 30 can be removed because they are not visible. For a use case, imagine an UPDATE chain where a rows was created by x=15 and expired by xid=19. Right now, we don't remove that row, though we could. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]
I wrote: Andrew Dunstan [EMAIL PROTECTED] writes: The TimeZone changes are looking might suspicious ... FATAL: failed to initialize timezone_abbreviations to Default Hm. It looks like this is working in the postmaster but failing in subprocesses. I'll see if I can duplicate it using EXEC_BACKEND. Nope, works fine with EXEC_BACKEND, so it's something Windows-specific. I'm not sure why you're not getting any more specific messages --- they should be coming out at WARNING level AFAICS. You'll need to trace through load_tzoffsets() and see why it's failing in the subprocess. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Re: [hackers-jp: 219] Re: postgresql-8.1.4文字セットサポート
I wrote: Michael Glaesemann [EMAIL PROTECTED] writes: Just to confirm, it does look like there's a discrepancy between what is in the documentation and the actual behavior of the server. The documentation indicates that SJIS is a valid server encoding: This is a documentation error --- SJIS is certainly not a valid server encoding (it's not 8-bit-safe). Will fix --- thanks for pointing it out. Actually, table 21-2 does say that SJIS isn't supported as a server encoding, but the text at the top of the page (before table 21-1) is pretty misleading --- it implies that every character set we have is allowed as a server-side encoding. I'm going to change that text, and also add a column to table 21-1 marking the supported server encodings. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] facing problem while using CVS
Hi All, I am facing problem while using CVS. I am working on Solaris 10 and trying to fetch source code of Postgres as Anonymous CVS as follows : /$ cvs -d :pserver:[EMAIL PROTECTED]:/projects/cvsroot login / (url : http://developer.postgresql.org/docs/postgres/anoncvs.html http://www.google.com/url?sa=Dq=http://developer.postgresql.org/docs/postgres/anoncvs.html) after providing password I am getting error : /Unknown host anoncvs.postgresql.org. / I even tried it with : / $ /usr/dist/share/socks/bin/runsocks cvs -d :pserver:[EMAIL PROTECTED]:/projects/cvsrooot login / still i got the following problem : / cvs [login aborted]: connect to 66.98.251.159(66.98.251.159):2041 failed: Connection refused / can anyone help me in this regards Thanks in advance, Saurabh ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] The vacuum-ignore-vacuum patch
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Good question. Imagine you have a serializable transaction like pg_dump, and then you have lots of newer transactions. If pg_dump is xid=12, and all the new transactions start at xid=30, any row created and expired between 12 and 30 can be removed because they are not visible. This reasoning is bogus. It would probably be safe for pg_dump because it's a read-only operation, but it fails badly if the serializable transaction is trying to do updates. An update needs to chase the chain of newer versions of the row forward from the version that's visible to the xact's serializable snapshot, to see if anyone has committed a newer version. Your proposal would remove elements of that chain, thereby possibly allowing the serializable xact to conclude it may update the tuple when it should have given an error. So in fact members of the chain are not visible, but vacuum doesn't have a strong enough lock to remove parts of the chain. What seems strange is that vacuum can trim the chain, but only if you do members starting from the head. I assume this is because you don't need to rejoin the chain around the expired tuples. (bogus seems a little strong.) -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]
Andrew Dunstan [EMAIL PROTECTED] writes: The TimeZone changes are looking might suspicious ... FATAL: failed to initialize timezone_abbreviations to Default Hm. It looks like this is working in the postmaster but failing in subprocesses. I'll see if I can duplicate it using EXEC_BACKEND. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Attack against postgresql.org ...
There are some days where High Speed Internet for Personal use just should never have been invented ... Over the past 24 hours, we've been experiencing a problem with the network that has taken us a bit to identify as being at our end, and a little bit longer to identify as being with the postgresql.org vServer ... someone is attacking it ... our provider has blocked the IP for now, so that direct access to the vServer isn't possible, but due to the delivery rules, and MXs, email should still flow properly ... The attacking IP, from the logs, appears to be 87.230.6.96 ... I'm lowering the TTL for the the DNS right now, and, if this persists past a few hours, I will change the IP and hope that they are attacking the IP, and not the domain ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [CORE] Attack against postgresql.org ...
Marc G. Fournier [EMAIL PROTECTED] writes: The attacking IP, from the logs, appears to be 87.230.6.96 ... Perhaps a complaint to their ISP is in order --- RIPE suggests [EMAIL PROTECTED] regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] The vacuum-ignore-vacuum patch
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Uh, why? Because it's used to determine the Xmin that our vacuum will use. If there is a transaction whose Xmin calculation included the Xid of a transaction running vacuum, we have gained nothing from directly excluding said vacuum's Xid, because it will affect us anyway indirectly via that transaction's Xmin. But the patch changes things so that *everyone* excludes the vacuum from their xmin. Or at least I thought that was the plan. We shouldn't do that, because that Xmin is also used to truncate SUBTRANS. Unless we are prepared to say that vacuum does not use subtransactions so it doesn't matter. This is true currently, so we could go ahead and do it (unless I'm missing something) -- but it means lazy vacuum will never be able to use subtransactions. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [CORE] Attack against postgresql.org ...
On Fri, 2006-07-28 at 17:37, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: The attacking IP, from the logs, appears to be 87.230.6.96 ... Perhaps a complaint to their ISP is in order --- RIPE suggests [EMAIL PROTECTED] That looks 1 level too high, the immediate source seems to be http://www.ehost.pl/onas.php They could probably act faster and more at the source... down on the page from the link above you can find [EMAIL PROTECTED] for complaints. Cheers, Csaba. $ nslookup 87.230.6.96 Server: 192.168.1.4 Address:192.168.1.4#53 Non-authoritative answer: 96.6.230.87.in-addr.arpaname = vpsdws.xip.pl. Authoritative answers can be found from: 6.230.87.in-addr.arpa nameserver = dns.hosteurope.de. 6.230.87.in-addr.arpa nameserver = dns2.hosteurope.de. dns.hosteurope.de internet address = 80.237.128.156 dns2.hosteurope.de internet address = 80.237.129.61 $ whois xip.pl [Querying whois.dns.pl] [whois.dns.pl] % This is the NASK WHOIS Server. % This server provides information only for PL domains. % For more info please see http://www.dns.pl/english/whois.html Domain object: domain: xip.pl registrant's handle: dinz5du40 (CORPORATE) nservers: ns1.ehost.pl.[80.237.184.22] ns2.ehost.pl.[83.149.119.142] created:2003.10.06 last modified: 2005.09.19 registrar: Dinfo Systemy Internetowe ul. Mostowa 5 43-300 Bielsko-Biala Polska/Poland +48.33 8225471 [EMAIL PROTECTED] option: the domain name has not option Subscribers Contact object: company: eHost s.c. organization: eHost.pl street: Cichockiego 13/6 city: 24-100 Pulawy location: PL handle: dinz5du40 phone: +48.50253 last modified: 2004.11.03 registrar: Dinfo Systemy Internetowe ul. Mostowa 5 43-300 Bielsko-Biala Polska/Poland +48.33 8225471 [EMAIL PROTECTED] Technical Contact: company: eHost s.c. organization: eHost.pl street: Cichockiego 13/6 city: 24-100 Pulawy location: PL handle: dinz5du40 phone: +48.50253 last modified: 2004.11.03 registrar: Dinfo Systemy Internetowe ul. Mostowa 5 43-300 Bielsko-Biala Polska/Poland +48.33 8225471 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] The vacuum-ignore-vacuum patch
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Uh, why? Because it's used to determine the Xmin that our vacuum will use. If there is a transaction whose Xmin calculation included the Xid of a transaction running vacuum, we have gained nothing from directly excluding said vacuum's Xid, because it will affect us anyway indirectly via that transaction's Xmin. But the patch changes things so that *everyone* excludes the vacuum from their xmin. Or at least I thought that was the plan. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] The vacuum-ignore-vacuum patch
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: But the patch changes things so that *everyone* excludes the vacuum from their xmin. Or at least I thought that was the plan. We shouldn't do that, because that Xmin is also used to truncate SUBTRANS. Yeah, but you were going to change that, no? Truncating SUBTRANS will need to include the vacuum xact's xmin, but we don't need it for any other purpose. but it means lazy vacuum will never be able to use subtransactions. This patch already depends on the assumption that lazy vacuum will never do any transactional updates, so I don't see what it would need subtransactions for. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Re: [hackers-jp: 219] Re: postgresql-8.1 .4文字セット サポート
On Jul 29, 2006, at 0:20 , Tom Lane wrote: I'm going to change that text, and also add a column to table 21-1 marking the supported server encodings. Thanks, Tom. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] GUC with units, details
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: If that actually worked, it'd be one thing, but it doesn't work and isn't going to do so in 8.2. So I think people will indeed be trying to use setting || unit for display purposes. In any case 8kB isn't a valid unit. I thought we set SHOW ALL aside for display purposes and pg_settings for processing purposes? Right, but it's difficult to get at the result of SHOW from SQL. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Re: [hackers-jp: 219] Re: postgresql-8.1.4文字セットサポート
Michael Glaesemann [EMAIL PROTECTED] writes: Just to confirm, it does look like there's a discrepancy between what is in the documentation and the actual behavior of the server. The documentation indicates that SJIS is a valid server encoding: This is a documentation error --- SJIS is certainly not a valid server encoding (it's not 8-bit-safe). Will fix --- thanks for pointing it out. createdb: database creation failed: ERROR: SJIS is not a valid encoding name I wonder if it would be worth changing the code so that you get a complaint saying the encoding is known but not allowed on the server side. It hasn't come up before, so maybe it's not worth the trouble ... but this message seems a bit confusing. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [CORE] Attack against postgresql.org ...
On Fri, 28 Jul 2006 17:51:11 +0200 Csaba Nagy [EMAIL PROTECTED] wrote: Perhaps a complaint to their ISP is in order --- RIPE suggests [EMAIL PROTECTED] That looks 1 level too high, the immediate source seems to be http://www.ehost.pl/onas.php I would go to both. ehost.pl could very well be some kid in his parent's basement and may be the problem. RIPE says that hosteurope.de is responsible for that IP. You have to take them at their word. -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Role incompatibilities
Sorry to ressurect this thread. However, I've been playing with the new role system and I'd prefer to keep CURRENT_USER as the login user, and not making it a synonymn for CURRENT_ROLE. In my application, I love the ability to shed privleges by SET ROLE dataentry;. However, I need CURRENT_USER to remain as 'clark' for audit trail triggers (recording that 'dataentry' changed a particular order is kinda useless). I have a related information_schema question. Tom said that I could probably use login or inherit to determine which 'roles' are users, and which are really roles. Is this still the advice? That said, shouldn't PostgreSQL just call this mixed-thingy an 'authority' to reduce confusion. Then role-is-authority and user-is-authority. Probably too late, but, just in case it is still changable... My deeper question is... from the information_schema, is it possible (both in theory via definition, and in pratice via implementation) to obtain two things: (a) the roles to which I can do SET ROLE with, I guess this is my granted roles? (b) the roles to which I currently am using for my permission(s), or simply, the role inherit graph and my current role Thanks for your time, Clark P.S. There isn't a way to list all roles from the information_schema, except via DISTINCT on a table that refers to them? On Mon, Apr 10, 2006 at 03:41:59PM -0400, Bruce Momjian wrote: | | Is there a TODO here? | | --- | | Peter Eisentraut wrote: | Am Samstag, 25. M?rz 2006 16:10 schrieb Tom Lane: | No, the current implementation is a compromise between exact standards | compatibility and backwards compatibility with our historical groups | behavior. I'm not really prepared to toss the latter overboard. | | My two major sticking points here are the SET ROLE command and the noinherit | feature. The SET ROLE command is not required by our historical group | behavior (because we didn't have it before) and does not do what the SQL | standard says it should do. The noinherit feature is not required by the | historical group behavior (because groups are yes-inherit) and is not in the | SQL standard either. So these two features were just mistakes as far as I | can tell. | | I'm not passing judgement on whether a command like the currently implemented | SET ROLE command or a feature like the currently implemented noinherit | feature is useful. They are just not in line with either the historical | group behavior or the SQL standard. | | -- | Peter Eisentraut | http://developer.postgresql.org/~petere/ | | ---(end of broadcast)--- | TIP 1: if posting/reading through Usenet, please send an appropriate | subscribe-nomail command to [EMAIL PROTECTED] so that your | message can get through to the mailing list cleanly | | | -- | Bruce Momjian http://candle.pha.pa.us | EnterpriseDBhttp://www.enterprisedb.com | | + If your life is a hard drive, Christ can be your backup. + | | ---(end of broadcast)--- | TIP 3: Have you checked our extensive FAQ? | |http://www.postgresql.org/docs/faq | ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] GUC with units, details
On Fri, Jul 28, 2006 at 01:03:00AM +0200, Peter Eisentraut wrote: Accepting page (or block?) as a unit might be a reasonable You hit on something that's always irked me a bit... we tend to toss out 'page' and 'block' (and sometimes even 'buffer') randomly when referring to different things that are keyed to BLCKSZ; perhaps we should pick one as the standard? I know all of us know what we're talking about, but I suspect this could be confusing to users. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Role incompatibilities
* Clark C. Evans ([EMAIL PROTECTED]) wrote: Sorry to ressurect this thread. However, I've been playing with the new role system and I'd prefer to keep CURRENT_USER as the login user, and not making it a synonymn for CURRENT_ROLE. In my application, I love the ability to shed privleges by SET ROLE dataentry;. However, I need CURRENT_USER to remain as 'clark' for audit trail triggers (recording that 'dataentry' changed a particular order is kinda useless). This sounds like a reasonable point. I'm not sure it's something we can actually do something about but I believe it's something worth thinking about. I have a related information_schema question. Tom said that I could probably use login or inherit to determine which 'roles' are users, and which are really roles. Is this still the advice? That said, Yes, this there isn't really any real difference between the two... shouldn't PostgreSQL just call this mixed-thingy an 'authority' to reduce confusion. Then role-is-authority and user-is-authority. Probably too late, but, just in case it is still changable... I'm not really sure this would buy us all that much... My deeper question is... from the information_schema, is it possible (both in theory via definition, and in pratice via implementation) to obtain two things: (a) the roles to which I can do SET ROLE with, I guess this is my granted roles? (b) the roles to which I currently am using for my permission(s), or simply, the role inherit graph and my current role These should be 'applicable_roles' and 'enabled_roles', respectively. One possible issue I just noticed was that they both seem to follow through 'noinherit' roles (even though actual permissions do not). Only 'applicable_roles' should follow through 'noinherit' roles, 'enabled_roles' shouldn't. They do work correctly otherwise, from what I can tell: abc= select * from applicable_roles; grantee | role_name | is_grantable -+-+-- admin | postgres| NO sfrost | admin | NO sfrost | app1_admin | NO (3 rows) abc= select * from enabled_roles ; role_name - sfrost postgres admin app1_admin (4 rows) abc= set role app1_admin; SET abc= select * from enabled_roles ; role_name - app1_admin (1 row) abc= select * from applicable_roles ; grantee | role_name | is_grantable -+---+-- (0 rows) P.S. There isn't a way to list all roles from the information_schema, except via DISTINCT on a table that refers to them? I'm not sure a way is defined by the SQL spec, which we try to follow in information_schema. pg_authid will give you the list but you need extra permissisons to view that. I don't think it'd be out of the question to add a 'pg_roles' view that provided the full list if there was enough demand for it... Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Hash indexes (was: On-disk bitmap index patch)
On Thu, Jul 27, 2006 at 01:46:01PM -0400, Alvaro Herrera wrote: Jim Nasby wrote: On Jul 25, 2006, at 3:31 PM, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: What would be the use-case for hash indexes ? And what should be done to make them faster than btree ? If we knew, we'd do it ;-) But no one's put enough effort into it to find out. Do they use the same hash algorithm as hash joins/aggregation? If so, wouldn't hash indexes be faster for those operations than regular indexes? The main problem doesn't seem to be in the hash algorithm (which I understand to mean the hashing function), but in the protocol for concurrent access of index pages, and the distribution of keys in pages of a single hash key. This is described in a README file or a code comment somewhere in the hash AM code. Someone needs to do some profiling to find out what the bottleneck really is, and ideally find a way to fix it. What I'm getting at is that I've never seen any explanation for the theoretical use cases where a hash index would outperform a btree. If we knew what kind of problems hash indexes were supposed to solve, we could try and interest people who are solving those kinds of problems in fixing hash indexes. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] On-disk bitmap index patch
On Thu, Jul 27, 2006 at 09:13:21AM -0700, Jie Zhang wrote: On 7/26/06 11:50 PM, Tom Lane [EMAIL PROTECTED] wrote: Jie Zhang [EMAIL PROTECTED] writes: On 7/26/06 10:14 PM, Tom Lane [EMAIL PROTECTED] wrote: ... A nonuniform distribution would probably mean that some of the bitmaps compress better-than-expected and others worse. I have no idea how to model that and guess what the overall result is ... The paper Optimizing Bitmap Indices With Efficient Compression by Kesheng Wu et al gave an approximate answer for this question. Assume that there are c distinct values. Let the i-th value has a probability of p_i, the number of rows r, and the word size w. then the total size of the compressed bitmap index is about (N/(w-1))(c- \sum(1-p_i)^(2w-2) - \sum(p_i)^(2w-2)), where in both \sum's, i is from 1 to c. Hm, but that's still begging the question no? It's still assuming that any one value is uniformly distributed. ISTM the cases that would break my simplistic calculation involve clustering of particular values, such that some areas of the bitmap are all-zero while other areas have lots of ones. Yes, you are right -- each value is still uniformly distributed. But this will be the worst case in terms of the size of a bitmap vector. As for how to model the size of a bitmap vector for an non-uniformly distributed value, that's a good question. I don't really know. But we do know the best case and the worse case. If the usefulness of bitmap indexes is still in doubt, could someone at Greenplum provide data from actual data warehouses from actual customers? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [CORE] Attack against postgresql.org ...
I have email'd both, thanks ... On Fri, 28 Jul 2006, Csaba Nagy wrote: On Fri, 2006-07-28 at 17:37, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: The attacking IP, from the logs, appears to be 87.230.6.96 ... Perhaps a complaint to their ISP is in order --- RIPE suggests [EMAIL PROTECTED] That looks 1 level too high, the immediate source seems to be http://www.ehost.pl/onas.php They could probably act faster and more at the source... down on the page from the link above you can find [EMAIL PROTECTED] for complaints. Cheers, Csaba. $ nslookup 87.230.6.96 Server: 192.168.1.4 Address:192.168.1.4#53 Non-authoritative answer: 96.6.230.87.in-addr.arpaname = vpsdws.xip.pl. Authoritative answers can be found from: 6.230.87.in-addr.arpa nameserver = dns.hosteurope.de. 6.230.87.in-addr.arpa nameserver = dns2.hosteurope.de. dns.hosteurope.de internet address = 80.237.128.156 dns2.hosteurope.de internet address = 80.237.129.61 $ whois xip.pl [Querying whois.dns.pl] [whois.dns.pl] % This is the NASK WHOIS Server. % This server provides information only for PL domains. % For more info please see http://www.dns.pl/english/whois.html Domain object: domain: xip.pl registrant's handle: dinz5du40 (CORPORATE) nservers: ns1.ehost.pl.[80.237.184.22] ns2.ehost.pl.[83.149.119.142] created:2003.10.06 last modified: 2005.09.19 registrar: Dinfo Systemy Internetowe ul. Mostowa 5 43-300 Bielsko-Biala Polska/Poland +48.33 8225471 [EMAIL PROTECTED] option: the domain name has not option Subscribers Contact object: company: eHost s.c. organization: eHost.pl street: Cichockiego 13/6 city: 24-100 Pulawy location: PL handle: dinz5du40 phone: +48.50253 last modified: 2004.11.03 registrar: Dinfo Systemy Internetowe ul. Mostowa 5 43-300 Bielsko-Biala Polska/Poland +48.33 8225471 [EMAIL PROTECTED] Technical Contact: company: eHost s.c. organization: eHost.pl street: Cichockiego 13/6 city: 24-100 Pulawy location: PL handle: dinz5du40 phone: +48.50253 last modified: 2004.11.03 registrar: Dinfo Systemy Internetowe ul. Mostowa 5 43-300 Bielsko-Biala Polska/Poland +48.33 8225471 [EMAIL PROTECTED] Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] Yahoo . yscrappy Skype: hub.orgICQ . 7615664 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] pgstattuple extension for indexes
Bruce Momjian wrote: I thought these new functions were going to be merged into /contrib/pgstattuple. Well, that's exactly what this patch seems to do ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] pgstattuple extension for indexes
Alvaro Herrera wrote: Bruce Momjian wrote: I thought these new functions were going to be merged into /contrib/pgstattuple. Well, that's exactly what this patch seems to do ... Well, looking at the tarball it puts everything in pgstatindex, and the Makefile is: #- # # pgstatindex Makefile # # $PostgreSQL$ # #- SRCS= pgstatindex.c MODULE_big = pgstatindex OBJS= $(SRCS:.c=.o) DOCS= DATA_built = pgstatindex.sql ifdef USE_PGXS PGXS := $(shell pg_config --pgxs) include $(PGXS) else subdir = contrib/pgstatindex top_builddir = /home/snaga/pgsql/sources/postgresql-8.1.3 include $(top_builddir)/src/Makefile.global include $(top_srcdir)/contrib/contrib-global.mk endif -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Resurrecting per-page cleaner for btree
On Thu, Jul 27, 2006 at 05:24:35PM -0400, Greg Stark wrote: Jim Nasby [EMAIL PROTECTED] writes: Even if we stopped right there it would still be a huge win in many (most?) cases. How often do the indexes on a table comprise even 50% of the table's size? I would say they're usually roughly comparable actually. It depends on how wide your table is of course but the wider your table rows the more indexes you're likely to have on the table too. I think the number of fields in a table will correlate with the number of indexes, but I don't think the width of those fields matters. Even in the 50% case, you've gone from 1.5X to .6X Sure, and a 3x speedup is nothing to sneeze at, that would be a great improvement to vacuum. But it's still just a linear speedup and doesn't address the algorithmic problem. The fundamental problem is we have a process that's O(m) where m is the total space taken by a table and its indexes. The actual amount of space it has to reclaim is n. Other than nm there's basically no relationship between these figures. As long as that's the case vacuum may as well be O(n^2) or O(n!). We frequently assume -- and often it's a valid assumption -- that these figures are roughly proportional. Hence all the talk about databases reaching a steady-state where the amount of dead space is constantly being reclaimed at more or less the same speed it's being generated. But there are also plenty of use cases where a complete vacuum pass takes thousands of times longer than the i/o it took to generate those dead tuples. Currently Postgres just isn't that great a tool for those use cases. This is exactly why I'm suggesting that we stop waiting for the perfect vacuum that will only hit the exact tuples in both the heap and indexes that it needs to and at least take the giant leap forward of only hitting heap tuples/pages that we know are dead. Even if indexes are the same size as the heap, you've still gained nearly a 2x speed improvement (depending on how long you wait to vacuum). Unfortunately while I'm convinced of the problem I'm equally unconvinced of the solution. I tried to solve online index builds using retail index lookups in a very similar way to what's being discussed here. And ran into the same problems. I eventually decided that while it could be made to work that way it would be far too much code, far too unsafe, and far too invasive in the index access methods to be the right approach. Our existing method works with minimal help from the index access methods which allows for an enormous degree of freedom in the index design.. To be able to support retail vacuum you would have to force index method implementors to keep information in a way that allowed them to look up a particular value/tid efficiently which would limit the kinds of indexes you could support drastically. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] The vacuum-ignore-vacuum patch
On Fri, Jul 28, 2006 at 03:08:08AM +0300, Hannu Krosing wrote: The other POV is that we don't really care about long-running transaction in other databases unless they are lazy vacuum, a case which is appropiately covered by the patch as it currently stands. This seems to be the POV that Hannu takes: the only long-running transactions he cares about are lazy vacuums. Yes. The original target audience of this patch are users running 24/7 OLTP databases with big slow changing tables and small fast-changing tables which need to stay small even at the time when the big ones are vacuumed. The other possible transactions which _could_ possibly be ignored while VACUUMING are those from ANALYSE and non-lazy VACUUMs. There are other transactions to consider: user transactions that will run a long time, but only hit a limited number of relations. These are as big a problem in an OLTP environment as vacuum is. Rather than coming up with machinery that will special-case vacuum or pg_dump, etc., I'd suggest thinking about a generic framework that would work for any long-runnnig transaction. One possibility: Transaction flags itself as 'long-running' and provides a list of exactly what relations it will be touching. That list is stored someplace a future vacuum can get at. The transaction runs, with additional checks that ensure it will not touch any relations that aren't in the list it provided. Any vacuums that start will take into account these lists of relations from long-running transactions and build a list of XIDs that have provided a list, and the minimum XID for every relation that was listed. If vacuum wants to vacuum a relation that has been listed as part of a long-running transaction, it will use the oldest XID in the database/cluster or the oldest XID listed for that relation, whichever is older. If it wants to vacuum a relation that is not listed, it will use the oldest XID in the database/cluster, excluding those XIDs that have listed exactly what relations they will be looking at. That scheme won't help pg_dump... in order to do so, you'd need to allow transactions to drop relations from their list. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Do we need multiple forms of the SQL2003 statistics aggregates?
I wrote: There is room to argue that the numeric-arithmetic version would be worth having on the grounds of greater precision or range, but it's a big chunk of code and the public demand for the functionality has not exactly been overwhelming. Comments? Since no one's even bothered to respond, I take it there's insufficient interest in the numeric versions of these aggregates. I've committed just the float8 versions. I added some very trivial regression tests, which we'll have to keep an eye on to see if they have any portability problems. We may need to back off the number of displayed fraction digits to get them to pass everywhere. If anyone wants to do better tests, feel free... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] A couple remarks on TODO
TODO has an entry o Allow UPDATE to handle complex aggregates [update]? which I think is now obsolete, because we've settled on rejecting aggregates at the top level of UPDATE, as the spec tells us to do. Also, there's one remaining unfinished feature in the multi-argument aggregate patch, which I think we should have a TODO item for: * Allow DISTINCT to work in multiple-argument aggregate calls The SQL2003 spec doesn't require this (it forbids DISTINCT in all its two-argument aggregates) but it seems like we should do it someday for orthogonality's sake. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] A couple remarks on TODO
Thanks, done. --- Tom Lane wrote: TODO has an entry o Allow UPDATE to handle complex aggregates [update]? which I think is now obsolete, because we've settled on rejecting aggregates at the top level of UPDATE, as the spec tells us to do. Also, there's one remaining unfinished feature in the multi-argument aggregate patch, which I think we should have a TODO item for: * Allow DISTINCT to work in multiple-argument aggregate calls The SQL2003 spec doesn't require this (it forbids DISTINCT in all its two-argument aggregates) but it seems like we should do it someday for orthogonality's sake. regards, tom lane -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Role incompatibilities
Clark C. Evans [EMAIL PROTECTED] writes: Sorry to ressurect this thread. However, I've been playing with the new role system and I'd prefer to keep CURRENT_USER as the login user, and not making it a synonymn for CURRENT_ROLE. In my application, I love the ability to shed privleges by SET ROLE dataentry;. However, I need CURRENT_USER to remain as 'clark' for audit trail triggers (recording that 'dataentry' changed a particular order is kinda useless). Aren't you looking for SESSION_USER? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] The vacuum-ignore-vacuum patch
[EMAIL PROTECTED] (Jim C. Nasby) writes: There are other transactions to consider: user transactions that will run a long time, but only hit a limited number of relations. These are as big a problem in an OLTP environment as vacuum is. Rather than coming up with machinery that will special-case vacuum or pg_dump, etc., I'd suggest thinking about a generic framework that would work for any long-runnnig transaction. One possibility: Transaction flags itself as 'long-running' and provides a list of exactly what relations it will be touching. That list is stored someplace a future vacuum can get at. The transaction runs, with additional checks that ensure it will not touch any relations that aren't in the list it provided. One thought that's a bit different... How about we mark transactions that are in serializable mode? That would merely be a flag... We would know that, for each such transaction, we could treat all tuples deadified after those transactions as being dead and cleanable. That doesn't require any knowledge of relations that are touched/locked... -- cbbrowne,@,cbbrowne.com http://www.ntlug.org/~cbbrowne/nonrdbms.html To err is human, to moo bovine. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Hash indexes (was: On-disk bitmap index patch)
Jim C. Nasby wrote: What I'm getting at is that I've never seen any explanation for the theoretical use cases where a hash index would outperform a btree. If we knew what kind of problems hash indexes were supposed to solve, we could try and interest people who are solving those kinds of problems in fixing hash indexes. The btree index needs to descend potentially many pages before getting to the leaf page, where the actual index is stored. The hash index can get at the leaf node in --supposedly-- one fetch. Btree is O(logN) to get a single key, while hash is O(1). Our problem lies in the constants; for btree they are smaller than for hash, so in practice that O(logN) is always smaller than O(1). I've heard other database systems manage to have hash indexes that are actually faster than btree, so either (1) our btree absolutely rocks, or (2) their hash implementations are better (probably both). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] On-disk bitmap index patch
Jim, On 7/28/06 10:17 AM, Jim C. Nasby [EMAIL PROTECTED] wrote: If the usefulness of bitmap indexes is still in doubt, could someone at Greenplum provide data from actual data warehouses from actual customers? First, is anyone in doubt? - Luke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] On-disk bitmap index patch
Luke Lonergan wrote: Jim, On 7/28/06 10:17 AM, Jim C. Nasby [EMAIL PROTECTED] wrote: If the usefulness of bitmap indexes is still in doubt, could someone at Greenplum provide data from actual data warehouses from actual customers? First, is anyone in doubt? Sure. I think we are going to have to see the final patch and have users test it with their workload to find the useful range. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Hash indexes (was: On-disk bitmap index patch)
Alvaro Herrera [EMAIL PROTECTED] writes: The btree index needs to descend potentially many pages before getting to the leaf page, where the actual index is stored. The hash index can get at the leaf node in --supposedly-- one fetch. Btree is O(logN) to get a single key, while hash is O(1). Our problem lies in the constants; for btree they are smaller than for hash, so in practice that O(logN) is always smaller than O(1). I've heard other database systems manage to have hash indexes that are actually faster than btree, so either (1) our btree absolutely rocks, or (2) their hash implementations are better (probably both). I think the problem may well be that we use hash buckets that are too large (ie, whole pages). After we fetch the page, we have to grovel through every tuple on it to find the one(s) that really match the query, whereas btree has a much more intelligent strategy (viz binary search) to do its intrapage searches. Smaller buckets would help make up for this. Another issue is that we don't store the raw hashcode in the index tuples, so the only way to test a tuple is to actually invoke the datatype equality function. If we stored the whole 32-bit hashcode we could eliminate non-matching hashcodes cheaply. I'm not sure how painful it'd be to do this though ... hash uses the same index tuple layout as everybody else, and so there's no convenient place to put the hashcode. Anyway the bottom line here is that no one's tried hard to fix it, but there are certainly things that might help. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Hash indexes (was: On-disk bitmap index patch)
On Fri, Jul 28, 2006 at 03:14:33PM -0400, Alvaro Herrera wrote: Jim C. Nasby wrote: What I'm getting at is that I've never seen any explanation for the theoretical use cases where a hash index would outperform a btree. If we knew what kind of problems hash indexes were supposed to solve, we could try and interest people who are solving those kinds of problems in fixing hash indexes. The btree index needs to descend potentially many pages before getting to the leaf page, where the actual index is stored. The hash index can get at the leaf node in --supposedly-- one fetch. Btree is O(logN) to get a single key, while hash is O(1). Our problem lies in the constants; for btree they are smaller than for hash, so in practice that O(logN) is always smaller than O(1). I've heard other database systems manage to have hash indexes that are actually faster than btree, so either (1) our btree absolutely rocks, or (2) their hash implementations are better (probably both). In that case, perhaps this is something Greenplum might be interested in, since it might fit nicely between bitmap and btree indexes. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]
Tom Lane wrote:
I wrote:
Andrew Dunstan [EMAIL PROTECTED] writes:
The TimeZone changes are looking might suspicious ...
FATAL: failed to initialize timezone_abbreviations to Default
Hm. It looks like this is working in the postmaster but failing
in subprocesses. I'll see if I can duplicate it using EXEC_BACKEND.
Nope, works fine with EXEC_BACKEND, so it's something Windows-specific.
I'm not sure why you're not getting any more specific messages ---
they should be coming out at WARNING level AFAICS. You'll need to trace
through load_tzoffsets() and see why it's failing in the subprocess.
that was a bit painful but we failed to see a useful error message due
to the fact that we have been activly suppressing it - with a quick hack
like:
---
/home/pgbuild/pgfarmbuild/HEAD/pgsql/src/backend/utils/misc/tzparser.c
Tue Jul 25 05:51:21 2006
+++ src/backend/utils/misc/tzparser.c Fri Jul 28 19:33:24 2006
@@ -326,7 +326,6 @@
if (!tzFile)
{
/* at level 0, if file doesn't exist, guc.c's complaint
is enough */
- if (errno != ENOENT || depth 0)
ereport(tz_elevel,
(errcode_for_file_access(),
errmsg(could not read time
zone file \%s\: %m,
(will probably get mangled by my mailer)
I get a much more useful:
WARNING: could not read time zone file Default: No such file or directory
FATAL: failed to initialize timezone_abbreviations to Default
WARNING: could not read time zone file Default: No such file or directory
FATAL: failed to initialize timezone_abbreviations to Default
LOG: background writer process (PID 3776) exited with exit code 0
LOG: terminating any other active server processes
WARNING: could not read time zone file Default: No such file or directory
FATAL: failed to initialize timezone_abbreviations to Default
LOG: all server processes terminated; reinitializing
WARNING: could not read time zone file Default: No such file or directory
which gives a strong further hint at the underlying issue.
Stefan
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]
Stefan Kaltenbrunner [EMAIL PROTECTED] writes: I get a much more useful: WARNING: could not read time zone file Default: No such file or directory FATAL: failed to initialize timezone_abbreviations to Default Hm, but why would the file not be there? Try hacking it to print the whole path it's trying to open, maybe that will help. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] lastval exposes information that currval does not
On Thu, Jul 27, 2006 at 09:37:22PM -0400, Stephen Frost wrote: Got any others beyond 'lastval'? Is 'lastval' even doing what you're claiming (looking at the actual catalog on disk by using the OID)? My recollection was that it was actually just storing the value in a bit of backend-local memory, but I havn't gone and looked at the code yet. Have you looked at the code behind 'lastval'? Well, you got me curious and so I looked at the code in question. The code does have a check, but it just checks if the user has access to the sequence. If the user doesn't have SELECT or USAGE on the sequence in question, lastval() will indeed fail with an error. Again, stretching a relatively minor point about lastval to some kind of systemic problem, with the servers or the developers, isn't going to get anyone anywhere. Not the least of which is that arguments involving people can install C code into the backend and break security are truisms: installed C code can do *anything* which is why only superusers can install such functions... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [PATCHES] [HACKERS] pg_regress breaks on msys
Bruce Momjian [EMAIL PROTECTED] writes: I checked on MinGW and system() just returns the value returned by the application. There isn't any special two-values-in-one layering like is done on Unix for wait() and the return value from system(). It seems if the child dies from a signal, the parent dies too, at least in my C tests. The cases that I think we most need to defend against are (A) diff program not found (B) diff fails to read one of the input files I think your proposal handles case B, because diff should return exit code 2 which we will detect, but what happens in case A? Please test it. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] pg_regress breaks on msys
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I checked on MinGW and system() just returns the value returned by the application. There isn't any special two-values-in-one layering like is done on Unix for wait() and the return value from system(). It seems if the child dies from a signal, the parent dies too, at least in my C tests. The cases that I think we most need to defend against are (A) diff program not found (B) diff fails to read one of the input files I think your proposal handles case B, because diff should return exit code 2 which we will detect, but what happens in case A? Please test it. It returns 1. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] pg_regress breaks on msys
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I checked on MinGW and system() just returns the value returned by the application. There isn't any special two-values-in-one layering like is done on Unix for wait() and the return value from system(). It seems if the child dies from a signal, the parent dies too, at least in my C tests. The cases that I think we most need to defend against are (A) diff program not found (B) diff fails to read one of the input files I think your proposal handles case B, because diff should return exit code 2 which we will detect, but what happens in case A? Please test it. It returns 1. In summary, on MinGW, files differ or 'diff' not found, returns 1. If one of the files to be compared does not exist, it returns 2. And of course, if the files are the same, it returns zero. I assume MSVC builds will have problem with the diff call. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] On-disk bitmap index patch
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Luke Lonergan Sent: Friday, July 28, 2006 12:18 PM To: Jim C. Nasby; Jie Zhang Cc: Tom Lane; Mark Kirkwood; Josh Berkus; Gavin Sherry; pgsql- [EMAIL PROTECTED] Subject: Re: [HACKERS] On-disk bitmap index patch Jim, On 7/28/06 10:17 AM, Jim C. Nasby [EMAIL PROTECTED] wrote: If the usefulness of bitmap indexes is still in doubt, could someone at Greenplum provide data from actual data warehouses from actual customers? First, is anyone in doubt? Others have looked into the usefulness of bitmap indexes. Here is what they found: http://www.oracle.com/technology/pub/articles/sharma_indexes.html http://citeseer.ist.psu.edu/stockinger02bitmap.html Oracle, IBM, and even Microsoft[1] supports them. Probably not just to be trendy. [1] Microsoft SQL Server creates temporary bitmap indexes during some queries, though you cannot declaratively create a bitmap index. - Luke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] pg_regress breaks on msys
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: The cases that I think we most need to defend against are (A) diff program not found In summary, on MinGW, files differ or 'diff' not found, returns 1. If one of the files to be compared does not exist, it returns 2. And of course, if the files are the same, it returns zero. OK. The problem here is that pg_regress is coded to assume that zero-length output file represents success. Given the above Windows behavior that is *clearly* not good enough, because that's probably exactly what we will see after diff-not-found (if the Windows shell acts like a Unix shell does and creates the target first). I'd suggest modifying the logic so that zero-length output file with a nonzero return from the child be treated as a fatal condition (not just a difference, but bail out). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]
Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: I get a much more useful: WARNING: could not read time zone file Default: No such file or directory FATAL: failed to initialize timezone_abbreviations to Default Hm, but why would the file not be there? Try hacking it to print the whole path it's trying to open, maybe that will help. WARNING: could not read time zone file /home/pgbuild/devel/pginst/share/postgresql/timezonesets/Default: No such file or directory FATAL: failed to initialize timezone_abbreviations to Default WARNING: could not read time zone file /home/pgbuild/devel/pginst/share/postgresql/timezonesets/Default: No such file or directory FATAL: failed to initialize timezone_abbreviations to Default LOG: background writer process (PID 1460) exited with exit code 0 LOG: terminating any other active server processes WARNING: could not read time zone file /home/pgbuild/devel/pginst/share/postgresql/timezonesets/Default: No such file or directory $ ls -l /home/pgbuild/devel/pginst/share/postgresql/timezonesets/Default -rw-r--r--1 pgbuild Administ28630 Jul 28 20:03 /home/pgbuild/devel/pginst/share/postgresql/timezonesets/Default so it's there but as a msys-virtual path - is that get passed to some win32 function expecting a windows-style path ? Stefan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] On-disk bitmap index patch
Dann Corbit [EMAIL PROTECTED] writes: Others have looked into the usefulness of bitmap indexes. Here is what they found: http://www.oracle.com/technology/pub/articles/sharma_indexes.html I like this guy's style of argument: he admits a bitmap index on a unique column will be much bigger than a btree, and then airily dismisses it as not a problem. Not real convincing. http://citeseer.ist.psu.edu/stockinger02bitmap.html Both of these pages say up front that they are considering read-only data. So one of the questions that has to be answered (and the submitters have been entirely mum about) is exactly how bad is the update performance? If it's really awful that's going to constrain the use cases quite a lot, whereas merely a bit slower than btree wouldn't be such a problem. In any case, arguing that other DBs find it's a win will cut no ice with me. See adjacent discussion about hash indexes --- those *ought* to be a win, but they aren't in Postgres, for reasons that are still guesses. The translation gap between other DBs' experience and ours can be large. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] On-disk bitmap index patch
What we don't want to happen is for us to release bitmapped indexes, and find out later that btree is better in all cases. Then we have to tell people not to use bitmapped indexes until we fix it in the next major releasse. FYI, that is basically where we are right now with hash indexes. --- Tom Lane wrote: Dann Corbit [EMAIL PROTECTED] writes: Others have looked into the usefulness of bitmap indexes. Here is what they found: http://www.oracle.com/technology/pub/articles/sharma_indexes.html I like this guy's style of argument: he admits a bitmap index on a unique column will be much bigger than a btree, and then airily dismisses it as not a problem. Not real convincing. http://citeseer.ist.psu.edu/stockinger02bitmap.html Both of these pages say up front that they are considering read-only data. So one of the questions that has to be answered (and the submitters have been entirely mum about) is exactly how bad is the update performance? If it's really awful that's going to constrain the use cases quite a lot, whereas merely a bit slower than btree wouldn't be such a problem. In any case, arguing that other DBs find it's a win will cut no ice with me. See adjacent discussion about hash indexes --- those *ought* to be a win, but they aren't in Postgres, for reasons that are still guesses. The translation gap between other DBs' experience and ours can be large. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]
Stefan Kaltenbrunner [EMAIL PROTECTED] writes: WARNING: could not read time zone file /home/pgbuild/devel/pginst/share/postgresql/timezonesets/Default: No such file or directory $ ls -l /home/pgbuild/devel/pginst/share/postgresql/timezonesets/Default -rw-r--r--1 pgbuild Administ28630 Jul 28 20:03 /home/pgbuild/devel/pginst/share/postgresql/timezonesets/Default so it's there but as a msys-virtual path - is that get passed to some win32 function expecting a windows-style path ? Hm. We pass it to fopen(). The equivalent code in pgtz.c generates the path to /timezone files exactly the same way, but uses open() ... is there a difference in what they'll take? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]
Stefan Kaltenbrunner wrote: WARNING: could not read time zone file /home/pgbuild/devel/pginst/share/postgresql/timezonesets/Default: No such file or directory This is an MSys virtual path, of which postgres naturally knows nothing.We should have made the appropriate calls to turn it into a genuine Windows path. (Darn, not having a Windows box to test on is annoying). cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]
Stefan Kaltenbrunner [EMAIL PROTECTED] writes: WARNING: could not read time zone file /home/pgbuild/devel/pginst/share/postgresql/timezonesets/Default: No such file or directory so it's there but as a msys-virtual path - is that get passed to some win32 function expecting a windows-style path ? Doh, I see what's the problem: we calculate the sharedir path using my_exec_path, and falling back to the hardwired PGSHAREDIR path if my_exec_path isn't correct. The problem is that in a Windows subprocess, my_exec_path isn't correct until read_backend_variables has been done, and *that happens after InitializeGUCOptions* in SubPostmasterMain(). So we're trying to set up the tz name data before we have the path we need. The reason I didn't notice this in testing with EXEC_BACKEND is that I wasn't testing in a relocated installation, and so the fallback get_share_path calculation got the right answer anyway. Not sure about a clean fix. Probably we'll have to do something similar to the way TimeZone is handled, where we don't try to read in the data until later on in the initialization sequence. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] lastval exposes information that currval does not
On Fri, Jul 28, 2006 at 09:54:38PM +0200, Martijn van Oosterhout wrote: Not the least of which is that arguments involving people can install C code into the backend and break security are truisms: installed C code can do *anything* which is why only superusers can install such functions... My argument was not that installing C code can break things. My argument was that authors of C code are likely to forget about this feature and unknowingly open new security holes. Obviously no one can force C extension author to not do stupid or horrible things, but we can at least help him not unknowingly do horrible things. Again, fix is really simple. Document the issue, making it damn clear in the docs that the schema usage check means *nothing* when accessing an object by OID, and advising users that the ways to access things by OID are obscure but present and changing, so relying on the schema usage privilege is not a good idea. I'm not asking for a 2000 line patch here. A simple documentation change will do -- one that doesn't try to skirt around the issue like a dirty little secret. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] lastval exposes information that currval does not
Phil Frost wrote: Again, fix is really simple. Document the issue, making it damn clear in the docs that the schema usage check means *nothing* when accessing an object by OID, and advising users that the ways to access things by OID are obscure but present and changing, so relying on the schema usage privilege is not a good idea. I'm not asking for a 2000 line patch here. A simple documentation change will do -- one that doesn't try to skirt around the issue like a dirty little secret. Such as? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] SubPostmasterMain startup should be a critical section?
Now that we've isolated the reason why regression tests are failing on the Windows buildfarm members, it seems to me that there's a systemic problem as well as the immediate bug. The problem is that guc.c is doing an elog(ERROR) during InitializeGucOptions in postmaster children, which ends up doing exit(0), which the postmaster interprets as successful termination of the startup process, which is seriously wrong. And then we get into a loop where the postmaster spawns a background writer, the bgwriter immediately exit(0)'s for the same reason, the postmaster respawns it, etc. This apparently is a sufficiently tight loop to cause a Windows machine to nearly lock up (not but what fork bombs aren't problems for Unixen too). I'm thinking we should put CRITICAL_SECTION macros around the startup sequence in SubPostmasterMain, so that an elog(ERROR) there can't look like it's business as usual to the postmaster. Anyone see a better/more complete solution? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] lastval exposes information that currval does not
On Fri, Jul 28, 2006 at 04:42:11PM -0400, Phil Frost wrote: Again, fix is really simple. Document the issue, making it damn clear in the docs that the schema usage check means *nothing* when accessing an object by OID, and advising users that the ways to access things by OID are obscure but present and changing, so relying on the schema usage privilege is not a good idea. I'm not asking for a 2000 line patch here. A simple documentation change will do -- one that doesn't try to skirt around the issue like a dirty little secret. Well, I suppose you could add something like the following: If you use use low-level functions like relation_open/index_open/etc no permission checks are done at all. No schema check, nothing. There is also no check for possible deadlock issues, no check whether you got a strong enough lock for the operation you are trying to do. Caveat emptor. If you want to be sure you are not bypassing security checks, use the SPI interface. Would that help? You're talking about the schema check as if it's a special case, but when people use low-level functions they have to check *everything* themselves. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] [PATCH] Provide 8-byte transaction IDs to user level
On 7/27/06, Darcy Buskermolen [EMAIL PROTECTED] wrote: In one of those 3am lightbulbs I belive I have a way to make use of the 64-bit XID counter and still maintain the ability to have backwards compatibility. Is there any chance you could break this patch up into the 2 separate componenets that Hannu mentions, and rework the XID stuff into TransactionIdAdvance as per tom's recommendation. And in the meantime I'll pencil out the slony stuff to utilize this. Yes, I can. As I am on vacation right now, my computer-time is rather unstable, hopefully I can do it on weekend. -- marko ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] On-disk bitmap index patch
Bruce, On 7/28/06 1:25 PM, Bruce Momjian [EMAIL PROTECTED] wrote: What we don't want to happen is for us to release bitmapped indexes, and find out later that btree is better in all cases. Then we have to tell people not to use bitmapped indexes until we fix it in the next major releasse. FYI, that is basically where we are right now with hash indexes. On this thread people have presented results that show clear and irrefutable evidence that there are use cases where bitmap indexes outperform Btree for many datatypes on realistic problems, including the TPC-H benchmark. In many cases the bitmap indexes outperform BTREE by a factor of 50 and are a tiny fraction of the size and also take dramatically less time to build. Of the cases presented, we need to have someone specifically address them and point out why they aren't proof of bitmap index performance. So far this has not been done, rather there are some unsupported opinions about other cases that might be problematic. - Luke ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] The vacuum-ignore-vacuum patch
Ühel kenal päeval, R, 2006-07-28 kell 12:38, kirjutas Jim C. Nasby: On Fri, Jul 28, 2006 at 03:08:08AM +0300, Hannu Krosing wrote: The other POV is that we don't really care about long-running transaction in other databases unless they are lazy vacuum, a case which is appropiately covered by the patch as it currently stands. This seems to be the POV that Hannu takes: the only long-running transactions he cares about are lazy vacuums. Yes. The original target audience of this patch are users running 24/7 OLTP databases with big slow changing tables and small fast-changing tables which need to stay small even at the time when the big ones are vacuumed. The other possible transactions which _could_ possibly be ignored while VACUUMING are those from ANALYSE and non-lazy VACUUMs. There are other transactions to consider: user transactions that will run a long time, but only hit a limited number of relations. These are as big a problem in an OLTP environment as vacuum is. These transactions are better kept out of an OLTP database, by their nature they belong to OLAP db :) The reason I addressed the VACUUM first, was the fact that you can't avoid VACUUM on OLTP db. Rather than coming up with machinery that will special-case vacuum or pg_dump, etc., I'd suggest thinking about a generic framework that would work for any long-runnnig transaction. So instead of actually *solving* one problem you suggest *thinking* about solving the general case ? We have been *thinking* about dead-space-map for at least three years by now. One possibility: Transaction flags itself as 'long-running' and provides a list of exactly what relations it will be touching. That list is stored someplace a future vacuum can get at. The transaction runs, with additional checks that ensure it will not touch any relations that aren't in the list it provided. I have thought abou that too, but checking on each data change seemed too expensive to me, at least for the first cut. There seems to be some ways to avoid actual checking for table-in-list, but you still have to check weather you have to check . Any vacuums that start will take into account these lists of relations from long-running transactions and build a list of XIDs that have provided a list, and the minimum XID for every relation that was listed. If vacuum wants to vacuum a relation that has been listed as part of a long-running transaction, it will use the oldest XID in the database/cluster or the oldest XID listed for that relation, whichever is older. If it wants to vacuum a relation that is not listed, it will use the oldest XID in the database/cluster, excluding those XIDs that have listed exactly what relations they will be looking at. That scheme won't help pg_dump... in order to do so, you'd need to allow transactions to drop relations from their list. The whole thing is probably doable, but I doubt it will be done before 8.2 (or even 8.5, considering that I had the first vacuum-ignore-vacuum patch ready by 8.0 (i think)) -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] DTrace enabled build fails
/usr/sbin/dtrace -G -s utils/probes.d access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o commands/SUBSYS.o executor/SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o main/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o postmaster/SUBSYS.o regex/SUBSYS.o rewrite/SUBSYS.o storage/SUBSYS.o tcop/SUBSYS.o utils/SUBSYS.o ../../src/timezone/SUBSYS.o -o utils/probes.o gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -L../../src/port -L/opt/csw/lib/ -Wl,-R'/export/home/pei/devel/pg-install/lib' access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o commands/SUBSYS.o executor/SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o main/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o postmaster/SUBSYS.o regex/SUBSYS.o rewrite/SUBSYS.o storage/SUBSYS.o tcop/SUBSYS.o utils/SUBSYS.o ../../src/timezone/SUBSYS.o utils/probes.o ../../src/port/libpgport_srv.a -lrt -lsocket -lm -o postgres Undefined first referenced symbol in file AbortTransactionutils/probes.o CommitTransaction utils/probes.o ld: fatal: Symbol referencing errors. No output written to postgres collect2: ld returned 1 exit status gmake: *** [postgres] Error 1 Does it not like static functions? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] On-disk bitmap index patch
Tom Lane wrote: Both of these pages say up front that they are considering read-only data. So one of the questions that has to be answered (and the submitters have been entirely mum about) is exactly how bad is the update performance? If it's really awful that's going to constrain the use cases quite a lot, whereas merely a bit slower than btree wouldn't be such a problem. In any case, arguing that other DBs find it's a win will cut no ice with me. See adjacent discussion about hash indexes --- those *ought* to be a win, but they aren't in Postgres, for reasons that are still guesses. The translation gap between other DBs' experience and ours can be large. Notwithstanding that, I have a couple of non-postgres data points / anecdotes on this. Back in my days as an Ingres DBA in the mid 90s, our fairly highly tuned system used hash organised tables only for small fairly static lookup-type tables (state codes, postcodes, etc). Everything that was more dynamic was done with btree indexed tables. A few years later, I was producing very large tables of email addresses using BDB. I quickly stopped using hash tables when I found that the reorganisation penalty was huge. Switching to btree worked just fine, with no sudden performance blip. This might not be directly relevant, but clearly the bucket size is. I guess what we need to demonstrate is that the better hash performance will actually persist to a scale where it is actually worth it - surely for very small tables the index method won't matter much anyway. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] The vacuum-ignore-vacuum patch
On Jul 28, 2006, at 5:05 PM, Hannu Krosing wrote: Ühel kenal päeval, R, 2006-07-28 kell 12:38, kirjutas Jim C. Nasby: There are other transactions to consider: user transactions that will run a long time, but only hit a limited number of relations. These are as big a problem in an OLTP environment as vacuum is. These transactions are better kept out of an OLTP database, by their nature they belong to OLAP db :) Sure, but that's not always possible/practical. Rather than coming up with machinery that will special-case vacuum or pg_dump, etc., I'd suggest thinking about a generic framework that would work for any long-runnnig transaction. So instead of actually *solving* one problem you suggest *thinking* about solving the general case ? We have been *thinking* about dead-space-map for at least three years by now. No, I just wanted anyone who was actually going to work on this to think about a more general fix. If the vacuum-only fix has a chance of getting into core a version before the general case, I'll happily take what I can get. One possibility: Transaction flags itself as 'long-running' and provides a list of exactly what relations it will be touching. That list is stored someplace a future vacuum can get at. The transaction runs, with additional checks that ensure it will not touch any relations that aren't in the list it provided. I have thought abou that too, but checking on each data change seemed too expensive to me, at least for the first cut. There seems to be some ways to avoid actual checking for table-in- list, but you still have to check weather you have to check . Well, presumably the check to see if you have to check would be extremely cheap. As for checking that only approved relations are touched, you can do that by analyzing the rules/triggers/etc that are on all the tables involved. Or for a start, just disallow this on tables with rules or triggers (well, we'd probably have to allow for RI). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] On-disk bitmap index patch
Ühel kenal päeval, R, 2006-07-28 kell 16:18, kirjutas Tom Lane: Dann Corbit [EMAIL PROTECTED] writes: Others have looked into the usefulness of bitmap indexes. Here is what they found: http://www.oracle.com/technology/pub/articles/sharma_indexes.html I like this guy's style of argument: he admits a bitmap index on a unique column will be much bigger than a btree, and then airily dismisses it as not a problem. Not real convincing. This problem can be easyly avoided by not creating bitmap indexes on unique columns. So I think it is ok to dismiss it. http://citeseer.ist.psu.edu/stockinger02bitmap.html Both of these pages say up front that they are considering read-only data. So one of the questions that has to be answered (and the submitters have been entirely mum about) is exactly how bad is the update performance? If it's really awful that's going to constrain the use cases quite a lot, whereas merely a bit slower than btree wouldn't be such a problem. May be. OTOH, in OLAP databases you may be better off dropping the indexes before data loading and rebuilding them after. And it has been shown that bitmap indexes build a lot faster than btrees. In any case, arguing that other DBs find it's a win will cut no ice with me. How about a more general argument. I claim that an index that is small and fits in RAM is faster than a big one that does not fit in RAM. See adjacent discussion about hash indexes --- those *ought* to be a win, but they aren't in Postgres, for reasons that are still guesses. The translation gap between other DBs' experience and ours can be large. IIRC the tests showing bitmap indexes being much faster on TPC-H were done on postgresql, no ? You pointed out that btree indexes are more bloated in this case as they store padding spaces for all CHAR(N) fields whereas bitmap index stores padding spaces only once for each distinct value. Are there any plans to start optimising btree storage model in forseeable future ? -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] On-disk bitmap index patch
Ühel kenal päeval, R, 2006-07-28 kell 16:25, kirjutas Bruce Momjian: What we don't want to happen is for us to release bitmapped indexes, and find out later that btree is better in all cases. Actually I'd love it if adding bitmap indexes to core pg would magically make btree several times faster for the cases where bitmap indexes are faster now :) -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_dump: add option to ignore TABLE DATA for failed
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Martin Pitt wrote: -- Start of PGP signed section. Hi PostgreSQL developers, some time ago I started a discussion [1] here about modifying pg_dump to not restore TABLE DATA objects if the corresponding TABLE oject failed to be created (usually because it already exists, but it might fail due to a different error like a nonexisting data type). We need this to provide automatic major version upgrades for databases with extensions like PostGIS. Tom's reply [3] seemed to indicate that this was not entirely crackful, so I implemented his approach, and after some feedback I now have a fairly clean patch that works very well. The patch was scheduled for review and inclusion [4], and indeed the page had the patch for a while, but after some time it vanished. Can you please reconsider this? If there is still a problem with the patch, I'd like to work on it until it meets your standards. For your convenience I attach the current patch version; a test script [5] is also available (the ML kills shell script attachments, so I put it on a Debian server). It does not alter the default behaviour, it just adds a new option -X no-data-for-failed-tables. If you think this mode should be the default, I'm happy to change it that way. Thank you a lot! Martin [1] http://archives.postgresql.org/pgsql-hackers/2006-02/msg00694.php [2] http://bugs.debian.org/351571 [3] http://archives.postgresql.org/pgsql-hackers/2006-02/msg00716.php [4] http://archives.postgresql.org/pgsql-hackers/2006-02/msg01253.php [5] http://people.debian.org/~mpitt/test-pg_restore-existing.sh -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org In a world without walls and fences, who needs Windows and Gates? [ Attachment, skipping... ] -- End of PGP section, PGP failed! -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Formulating an sql query with CTID
Hi, I have a query i am having trouble to formulate: I used to do: SELECT DISTINCT a0,a1 FROM public.t1 However, now i need to add the CTID attribute, but CTID is unique thus, distinct is useless. In addition, i can't seem to be able to use max() etc... or casting to anything on CTID in order to use group by technique. What are my options? (I am using 8.2 or 8.1) -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] DTrace enabled build fails
Peter Eisentraut [EMAIL PROTECTED] writes: Does it not like static functions? I seem to recall Robert mentioning that they'd only recently fixed DTrace to cope with probes in static functions. Maybe you need to get an update? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]
Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: WARNING: could not read time zone file /home/pgbuild/devel/pginst/share/postgresql/timezonesets/Default: No such file or directory so it's there but as a msys-virtual path - is that get passed to some win32 function expecting a windows-style path ? Doh, I see what's the problem: we calculate the sharedir path using my_exec_path, and falling back to the hardwired PGSHAREDIR path if my_exec_path isn't correct. The problem is that in a Windows subprocess, my_exec_path isn't correct until read_backend_variables has been done, and *that happens after InitializeGUCOptions* in SubPostmasterMain(). So we're trying to set up the tz name data before we have the path we need. Is there a reason we have to do things in this order? Could we just postpone the call to InitializeGUCOptions() for a couple of lines? If not, then ... The reason I didn't notice this in testing with EXEC_BACKEND is that I wasn't testing in a relocated installation, and so the fallback get_share_path calculation got the right answer anyway. Not sure about a clean fix. Probably we'll have to do something similar to the way TimeZone is handled, where we don't try to read in the data until later on in the initialization sequence. I guess we'd need to set a flag that would postpone reading the data just during the startup phase, but have it called immediately in all other cases. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] The vacuum-ignore-vacuum patch
Jim Nasby wrote: On Jul 28, 2006, at 5:05 PM, Hannu Krosing wrote: So instead of actually *solving* one problem you suggest *thinking* about solving the general case ? We have been *thinking* about dead-space-map for at least three years by now. No, I just wanted anyone who was actually going to work on this to think about a more general fix. If the vacuum-only fix has a chance of getting into core a version before the general case, I'll happily take what I can get. Well, the vacuum-only fix has the advantage that the patch has already been written, tested, discussed, beaten to death, resurrected, rewritten, and is ready to be committed, while the general solution is not even past the handwaving phase, let alone *thinking*. And we have only three days before feature freeze, so if you want the general solution for 8.2 you should start *thinking* really fast :-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] The vacuum-ignore-vacuum patch
Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
Tom Lane wrote:
But the patch changes things so that *everyone* excludes the vacuum from
their xmin. Or at least I thought that was the plan.
We shouldn't do that, because that Xmin is also used to truncate
SUBTRANS.
Yeah, but you were going to change that, no? Truncating SUBTRANS will
need to include the vacuum xact's xmin, but we don't need it for any
other purpose.
That's correct.
but it means
lazy vacuum will never be able to use subtransactions.
This patch already depends on the assumption that lazy vacuum will never
do any transactional updates, so I don't see what it would need
subtransactions for.
Here is a patch pursuant to there ideas. The main change is that in
GetSnapshotData, a backend is skipped entirely if inVacuum is found to
be true.
I've been trying to update my SSH CVS several times today but I can't
reach the server. Maybe it's the DoS attach that it's been under, I
don't know.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/access/transam/twophase.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/twophase.c,v
retrieving revision 1.21
diff -c -p -r1.21 twophase.c
*** src/backend/access/transam/twophase.c 14 Jul 2006 14:52:17 -
1.21
--- src/backend/access/transam/twophase.c 28 Jul 2006 21:59:42 -
*** MarkAsPreparing(TransactionId xid, const
*** 279,284
--- 279,285
gxact-proc.pid = 0;
gxact-proc.databaseId = databaseid;
gxact-proc.roleId = owner;
+ gxact-proc.inVacuum = false;
gxact-proc.lwWaiting = false;
gxact-proc.lwExclusive = false;
gxact-proc.lwWaitLink = NULL;
Index: src/backend/access/transam/xact.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.224
diff -c -p -r1.224 xact.c
*** src/backend/access/transam/xact.c 24 Jul 2006 16:32:44 - 1.224
--- src/backend/access/transam/xact.c 28 Jul 2006 21:59:42 -
*** CommitTransaction(void)
*** 1529,1534
--- 1529,1535
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
MyProc-xid = InvalidTransactionId;
MyProc-xmin = InvalidTransactionId;
+ MyProc-inVacuum = false; /* must be cleared with
xid/xmin */
/* Clear the subtransaction-XID cache too while holding the
lock */
MyProc-subxids.nxids = 0;
*** PrepareTransaction(void)
*** 1764,1769
--- 1765,1771
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
MyProc-xid = InvalidTransactionId;
MyProc-xmin = InvalidTransactionId;
+ MyProc-inVacuum = false; /* must be cleared with xid/xmin */
/* Clear the subtransaction-XID cache too while holding the lock */
MyProc-subxids.nxids = 0;
*** AbortTransaction(void)
*** 1927,1932
--- 1929,1935
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
MyProc-xid = InvalidTransactionId;
MyProc-xmin = InvalidTransactionId;
+ MyProc-inVacuum = false; /* must be cleared with
xid/xmin */
/* Clear the subtransaction-XID cache too while holding the
lock */
MyProc-subxids.nxids = 0;
Index: src/backend/access/transam/xlog.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.244
diff -c -p -r1.244 xlog.c
*** src/backend/access/transam/xlog.c 14 Jul 2006 14:52:17 - 1.244
--- src/backend/access/transam/xlog.c 28 Jul 2006 21:59:42 -
*** CreateCheckPoint(bool shutdown, bool for
*** 5413,5419
* StartupSUBTRANS hasn't been called yet.
*/
if (!InRecovery)
! TruncateSUBTRANS(GetOldestXmin(true));
if (!shutdown)
ereport(DEBUG2,
--- 5413,5419
* StartupSUBTRANS hasn't been called yet.
*/
if (!InRecovery)
! TruncateSUBTRANS(GetOldestXmin(true, false));
if (!shutdown)
ereport(DEBUG2,
Index: src/backend/catalog/index.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/catalog/index.c,v
retrieving revision 1.269
diff -c -p -r1.269 index.c
*** src/backend/catalog/index.c 13 Jul 2006 16:49:13 - 1.269
--- src/backend/catalog/index.c 28 Jul 2006 21:59:42 -
*** IndexBuildHeapScan(Relation heapRelation
*** 1367,1373
else
{
snapshot = SnapshotAny;
! OldestXmin =
Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]
Andrew Dunstan [EMAIL PROTECTED] writes: Is there a reason we have to do things in this order? Could we just postpone the call to InitializeGUCOptions() for a couple of lines? Maybe, but I'm disinclined to mess with that. I have a patch that makes it work like TimeZone, but am having difficulty committing ... looks like that Polish script kiddie is at it again ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Possible explanation for Win32 stats regression test
Is anyone working on this? --- Tom Lane wrote: korry [EMAIL PROTECTED] writes: The problem is that, each time you go through pgwin32_waitforsinglesocket(), you tie the *same* kernel object (waitevent is static) to each socket. The fix is pretty simple - just call WSAEventSelect( s, waitevent, 0 ) after WaitForMultipleObjectsEx() returns. That disassociates the socket from the Event (it will get re-associated the next time pgwin32_waitforsingleselect() is called. Hmm. Presumably we don't do this a whole lot (use multiple sockets) or we'd have noticed before. Perhaps better would be to keep an additional static variable saying which socket the event is currently associated to, and only issue the extra WSAEventSelect calls if we need to change it. Or is WSAEventSelect fast enough that it doesn't matter? Anyway, someone with a Windows machine needs to code and test this ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] On-disk bitmap index patch
On Fri, Jul 28, 2006 at 02:43:23PM -0700, Luke Lonergan wrote: On 7/28/06 1:25 PM, Bruce Momjian [EMAIL PROTECTED] wrote: What we don't want to happen is for us to release bitmapped indexes, and find out later that btree is better in all cases. Then we have to tell people not to use bitmapped indexes until we fix it in the next major releasse. FYI, that is basically where we are right now with hash indexes. On this thread people have presented results that show clear and irrefutable evidence that there are use cases where bitmap indexes outperform Btree for many datatypes on realistic problems, including the TPC-H benchmark. Irrefutable is a little optimistic, don't you think? :-) There is reason to believe that a bitmap index is useful in some scenarios. We're not yet clear on what these are, whether they apply to production use scenarios, or whether b-tree could not be optimized to be better. I support you - I want to see these great things for myself. But irrefutable? Irrefutable is not true. :-) Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] On-disk bitmap index patch
Mark, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, July 28, 2006 9:26 PM But irrefutable? Irrefutable is not true. :-) How about unrefuted. The evidence has not been refuted, and not directly discussed or discounted. BTREE can not be optimized to produce the results we've presented, the discussion about char(n) datatypes was irrelevant as we had shown results for INT, numeric and char/varchar and they were all dramatically better than BTREE. I am hopeful this discussion takes a rapid turn toward the quantitative assessment of the results. - Luke ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
