Re: [HACKERS] On-disk bitmap index patch

[Luke Lonergan]

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

Re: [HACKERS] On-disk bitmap index patch

[mark]

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] Possible explanation for Win32 stats regression test

[Bruce Momjian]

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] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]

[Tom Lane]

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] The vacuum-ignore-vacuum patch

[Alvaro Herrera]

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
{
snapsho

Re: [HACKERS] The vacuum-ignore-vacuum patch

[Alvaro Herrera]

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] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]

[Andrew Dunstan]

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] DTrace enabled build fails

[Tom Lane]

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

[HACKERS] Formulating an sql query with CTID

[Tzahi Fadida]

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] pg_dump: add option to ignore TABLE DATA for failed

[Bruce Momjian]

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

Re: [HACKERS] On-disk bitmap index patch

[Hannu Krosing]

Ü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] On-disk bitmap index patch

[Hannu Krosing]

Ü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] The vacuum-ignore-vacuum patch

[Jim Nasby]

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

[Andrew Dunstan]

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

[HACKERS] DTrace enabled build fails

[Peter Eisentraut]

/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] The vacuum-ignore-vacuum patch

[Hannu Krosing]

Ü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

Re: [HACKERS] On-disk bitmap index patch

[Luke Lonergan]

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] [PATCHES] [PATCH] Provide 8-byte transaction IDs to user level

[Marko Kreen]

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] lastval exposes information that currval does not

[Martijn van Oosterhout]

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  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[HACKERS] SubPostmasterMain startup should be a critical section?

[Tom Lane]

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

[Alvaro Herrera]

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

Re: [HACKERS] lastval exposes information that currval does not

[Phil Frost]

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] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]

[Tom Lane]

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] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]

[Andrew Dunstan]

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]

[Tom Lane]

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] On-disk bitmap index patch

[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.  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] On-disk bitmap index patch

[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.

> 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] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]

[Stefan Kaltenbrunner]

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: [PATCHES] [HACKERS] pg_regress breaks on msys

[Tom Lane]

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: [PATCHES] [HACKERS] pg_regress breaks on msys

[Bruce Momjian]

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

[Dann Corbit]

> -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]

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

[Tom Lane]

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: [HACKERS] lastval exposes information that currval does not

[Martijn van Oosterhout]

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  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] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]

[Tom Lane]

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] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]

[Stefan Kaltenbrunner]

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] Hash indexes (was: On-disk bitmap index patch)

[Jim C. Nasby]

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] Hash indexes (was: On-disk bitmap index patch)

[Tom Lane]

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] On-disk bitmap index patch

[Bruce Momjian]

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] On-disk bitmap index patch

[Luke Lonergan]

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] Hash indexes (was: On-disk bitmap index patch)

[Alvaro Herrera]

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] The vacuum-ignore-vacuum patch

[Chris Browne]

[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] Role incompatibilities

[Tom Lane]

"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] A couple remarks on TODO

[Bruce Momjian]

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

[HACKERS] A couple remarks on TODO

[Tom Lane]

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] Do we need multiple forms of the SQL2003 statistics aggregates?

[Tom Lane]

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

Re: [HACKERS] The vacuum-ignore-vacuum patch

[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.

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] [CORE] Attack against postgresql.org ...

[Marc G. Fournier]

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] On-disk bitmap index patch

[Jim C. Nasby]

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] Hash indexes (was: On-disk bitmap index patch)

[Jim C. Nasby]

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] [PATCHES] Resurrecting per-page cleaner for btree

[Jim C. Nasby]

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 n 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] Role incompatibilities

[Stephen Frost]

* 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] GUC with units, details

[Jim C. Nasby]

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]

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] [CORE] Attack against postgresql.org ...

[D'Arcy J.M. Cain]

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  |  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] [PATCHES] pgstattuple extension for indexes

[Bruce Momjian]

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] Re: [hackers-jp: 219] Re: postgresql-8.1.4文字セットサポート

[Tom Lane]

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] GUC with units, details

[Tom Lane]

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]

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] The vacuum-ignore-vacuum patch

[Tom Lane]

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] [PATCHES] pgstattuple extension for indexes

[Alvaro Herrera]

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] The vacuum-ignore-vacuum patch

[Tom Lane]

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] [CORE] Attack against postgresql.org ...

[Csaba Nagy]

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]

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 ...

[Tom Lane]

"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

[HACKERS] Attack against postgresql.org ...

[Marc G. Fournier]

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] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]

[Tom Lane]

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

Re: [HACKERS] The vacuum-ignore-vacuum patch

[Bruce Momjian]

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

[HACKERS] facing problem while using CVS

[Saurabh Vyas]

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 
) 



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] Re: [hackers-jp: 219] Re: postgresql-8.1.4文字セットサポート

[Tom Lane]

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

Re: [HACKERS] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]

[Tom Lane]

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] The vacuum-ignore-vacuum patch

[Bruce Momjian]

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] The vacuum-ignore-vacuum patch

[Tom Lane]

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] [PATCHES] pgstattuple extension for indexes

[Bruce Momjian]

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] [PATCHES] putting CHECK_FOR_INTERRUPTS in qsort_comparetup()

[Tom Lane]

"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] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]

[Andrew Dunstan]

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] Better name/syntax for "online" index creation

[Paul Silveira]

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]

[Andrew Dunstan]

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] [Pgbuildfarm-members] [Fwd: RE: Build farm on Windows]

[Stefan Kaltenbrunner]

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

[HACKERS] [Fwd: RE: Build farm on Windows]

[Andrew Dunstan]

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] GUC with units, details

[Hannu Krosing]

Ü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] LWLock statistics collector (was: CSStorm occurred again by postgreSQL8.2)

[ITAGAKI Takahiro]

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

[HACKERS] request: support of array in plperl OUT arguments

[Pavel Stehule]

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

Re: [HACKERS] The vacuum-ignore-vacuum patch

[Hannu Krosing]

Ü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

Re: [HACKERS] [PATCHES] putting CHECK_FOR_INTERRUPTS in qsort_comparetup()

[Charles Duffy]

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.21&content-type=text/x-cvsweb-markup&cvsroot=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] [PATCHES] extension for sql update

[Susanne Ebrecht]

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] GUC with units, details

[Peter Eisentraut]

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