Re: [PATCHES] [HACKERS] Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

[daveg]

On Tue, Jun 24, 2008 at 10:41:07PM -0400, Tom Lane wrote:
> daveg <[EMAIL PROTECTED]> writes:
> > Are we talking about the same patch?
> 
> Maybe not --- I thought you were talking about a backend-side behavioral
> change.
> 
> > Because I don't know what you are
> > refering to with "timer management code" and "scheduling the interrupt" in
> > the context of pg_dump.
> 
> I'm not sure that I see a good argument for making pg_dump deliberately
> fail, if that's what you're proposing.  Maybe I'm just too old-school,
> but there simply is not any other higher priority for a database than
> safeguarding your data.

We agree about that. The intent of my patch it to give the user a chance to
take corrective action in a case where pg_dump cannot be relied on to succeed.

The problem is that pg_dump can get blocked behind locks and then fail hours
later when the locks are released because some table it had not locked yet
changed. In the worst case:

  - no backup,
  - no notice until too late to restart the backup,
  - lost production due to other processes waiting on locks pg_dump holds.

So the intent of the patch is to optionally allow pg_dump to fail quickly
if it cannot get all the access share locks it needs. This gives the user
an opportunity to notice and retry in a timely way.

Please see http://archives.postgresql.org/pgsql-patches/2008-05/msg00351.php
for the orginal patch and problem description.

A sample failure instance from a very heavy batch environment with a lot of
materialized views being maintained concurrently with pg_dump. DB size
is about 300 GB:

---
20080410 14:53:49 dumpdb c04_20080410_public: dumping c04 to 
/backups/c04_20080410_public
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  cache lookup failed for index 
22619852
pg_dump: The command was: SELECT t.tableoid, t.oid, t.relname as indexname, 
pg_catalog.pg_get_indexdef(i.indexrelid) as indexdef, t.relnatts as indnkeys, 
i.indkey, i.indisclustered, c.contype, c.conname, c.tableoid as contableoid, 
c.oid as conoid, (SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = 
t.reltablespace) as tablespace, array_to_string(t.reloptions, ', ') as options 
FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) 
LEFT JOIN pg_catalog.pg_depend d ON (d.classid = t.tableoid AND d.objid = t.oid 
AND d.deptype = 'i') LEFT JOIN pg_catalog.pg_constraint c ON (d.refclassid = 
c.tableoid AND d.refobjid = c.oid) WHERE i.indrelid = 
'22615005'::pg_catalog.oid ORDER BY indexname
20080411 06:12:17 dumpdb FATAL: c04_20080410_public: dump failed
---

Note that the dump started at 14:53, but did not fail until 06:12 the next day,
and it never got to the actual copy out phase. Meanwhile other DDL using
processes were hung on the access share locks aready held by pg_dump.

Regards

-dg

-- 
David Gould   [EMAIL PROTECTED]  510 536 1443510 282 0869
If simplicity worked, the world would be overrun with insects.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

[Tom Lane]

"Jeffrey Baker" <[EMAIL PROTECTED]> writes:
> I guess nobody has any interest in my proposal, only in the departure of my
> described experience from expected behavior :-(

Well, we certainly should try to understand the unexpected behavior
in detail before we consider solutions.  Per Sir A.C. Doyle, it is a
capital mistake to theorize in advance of the data.

(It's probably also worth noting that this community's historical
interest has not been in read-only or even read-mostly data.  We'd
not be willing to pay all that MVCC overhead if we thought we were
just a warehouse of static data.  If that's what you want, maybe
you need some other DBMS.)

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PATCHES] [HACKERS] Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

[Tom Lane]

daveg <[EMAIL PROTECTED]> writes:
> Are we talking about the same patch?

Maybe not --- I thought you were talking about a backend-side behavioral
change.

> Because I don't know what you are
> refering to with "timer management code" and "scheduling the interrupt" in
> the context of pg_dump.

I'm not sure that I see a good argument for making pg_dump deliberately
fail, if that's what you're proposing.  Maybe I'm just too old-school,
but there simply is not any other higher priority for a database than
safeguarding your data.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] MSVC 2003 compile error with pg8.3.3

[Hiroshi Saito]

Hi.

It is strange...
Problem is not reproduced although I use VC2005.

The result of my nmake -f win32.mak is this. 
...

Microsoft (R) Manifest Tool version 5.2.3790.2014
Copyright (c) Microsoft Corporation 2005.
All rights reserved.
   cd ..\..
   echo All Win32 parts have been built!
All Win32 parts have been built!

C:\MinGW\home\HIROSHI\postgresql-8.3.3\src>

It may be necessary to investigate the reference relation of VC2003.
Does someone notice some? 


Regards,
Hiroshi Saito
- Original Message - 
From: "Jeff McKenna" <[EMAIL PROTECTED]>




Hello,

I am trying to compile libpq.dll with MSVC 2003 on windows, using  
postgresql-8.3.3, but I get the following compile error:


   Creating library .\Release\libpqdll.lib and object .\Release 
\libpqdll.exp
libpq.lib(dirmod.obj) : error LNK2019: unresolved external symbol  
__dosmaperr r

ferenced in function _pgwin32_safestat
libpq.lib(dirent.obj) : error LNK2001: unresolved external symbol  
__dosmaperr

.\Release\libpq.dll : fatal error LNK1120: 1 unresolved externals
NMAKE : fatal error U1077: 'link.exe' : return code '0x460'
Stop.
NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual  
Studio .NET 2003

VC7\BIN\nmake.exe"' : return code '0x2'
Stop.

Does anyone have any ideas how to solve this??

thanks.

-jeff





---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/






--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

[Jaime Casanova]

On Tue, Jun 24, 2008 at 3:50 PM, Jonah H. Harris <[EMAIL PROTECTED]> wrote:
> On Tue, Jun 24, 2008 at 4:34 PM, Jeffrey Baker <[EMAIL PROTECTED]> wrote:
>> Supposing the table is generally or strictly ordered by the column to be
>> indexed, it would be more compact if the index stored ranges of tuples.
>> Instead of storing the TID of every tuple with that value, the index would
>> store a first and last TID, between which all tuples have the value.
>
> There are several databases which implement this idea.  Unfortunately,
> Postgres does not yet ensure that indexed tables remain indexed.
>

Just for the records. you mean *ordered* tables, don't you?
Postgres does not yet ensure that ordered tables remain ordered.

-- 
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PATCHES] [HACKERS] Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

[daveg]

On Tue, Jun 24, 2008 at 05:34:50PM -0400, Tom Lane wrote:
> daveg <[EMAIL PROTECTED]> writes:
> > lock-timeout sets statement_timeout to a small value while locks are being
> > taken on all the tables. Then it resets it to default. So it could reset it
> > to whatever the new default is.
> 
> "reset to default" is *surely* not the right behavior; resetting to the
> setting that had been in effect might be a bit sane.  But the whole
> design sounds pretty broken to start with.  The timer management code
> already understands the concept of scheduling the interrupt for the
> nearest of a couple of potentially active timeouts.  ISTM any patch
> intended to add a feature like this ought to extend that logic rather
> than hack around changing the values of global variables.

Are we talking about the same patch? Because I don't know what you are
refering to with "timer management code" and "scheduling the interrupt" in
the context of pg_dump.

-dg


-- 
David Gould   [EMAIL PROTECTED]  510 536 1443510 282 0869
If simplicity worked, the world would be overrun with insects.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] stat() vs cygwin

[Andrew Dunstan]

Magnus Hagander wrote:

More to the point: I thought this had been tested. I will test it today
so we can put this whole thread to rest.



IIRC it was only tested insofar that it doesn't actually break. Not if
it returns proper results.
  


I have tested it using the suggested script (corrected) and it passes 
(both sizes the same) consistently, as I expected.

Buf if my memory isn't completely off, there are other such cases as
well around the code, where we've done proper fixes for native win32 and
left cygwin alone. The argument being that for a developer system, it
doesn't really matter if things aren't entirely reliable, and that
nobody should be using cygwin for a production server. (I have nothing
against using it for a dev box, though I wouldn't do it myself)


  


I don't recall any. But I could be wrong.

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

[Jeffrey Baker]

On Tue, Jun 24, 2008 at 3:08 PM, Tom Lane <[EMAIL PROTECTED]> wrote:

> "Kevin Grittner" <[EMAIL PROTECTED]> writes:
> > Tom Lane <[EMAIL PROTECTED]> wrote:
> >> Now, *why* it is a mistake is interesting to speculate about, but
> >> let's confirm the theory first.
>
> > Could this be related to hint bit rewrites during indexing?
>
> If so, changing maintenance_work_mem won't improve the situation.
>
> What I personally suspect is that Jeff's index build is swapping like
> crazy, or else there's just some problem in the sort code for such a
> large sort arena.  But let's get some evidence about how the index build
> time varies with maintenance_work_mem before jumping to conclusions.


Well it definitely isn't that, because the machine doesn't even have a swap
area defined.  vmstat during the table creation and index creation look
really quite different.  During the table sort there's a heavy r/w traffic
12-20MB/s, during the index creation it's lower.  But seem to be CPU limited
(i.e. one CPU is maxed out the whole time, and iowait is not very high).

I guess nobody has any interest in my proposal, only in the departure of my
described experience from expected behavior :-(

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

[Tom Lane]

"Kevin Grittner" <[EMAIL PROTECTED]> writes:
> Tom Lane <[EMAIL PROTECTED]> wrote: 
>> Now, *why* it is a mistake is interesting to speculate about, but
>> let's confirm the theory first.
 
> Could this be related to hint bit rewrites during indexing?

If so, changing maintenance_work_mem won't improve the situation.

What I personally suspect is that Jeff's index build is swapping like
crazy, or else there's just some problem in the sort code for such a
large sort arena.  But let's get some evidence about how the index build
time varies with maintenance_work_mem before jumping to conclusions.

> Would a vacuum between creation and indexing be a good way to tell?

Yeah, that might be a useful experiment to try too.  It wouldn't improve
the overall time AFAICS, but it would give us some idea how much of the
indexing time was really spent on hintbits.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

[Kevin Grittner]

>>> On Tue, Jun 24, 2008 at  4:54 PM, in message
<[EMAIL PROTECTED]>,
Tom Lane <[EMAIL PROTECTED]> wrote: 
> "Jeffrey Baker" <[EMAIL PROTECTED]> writes:
>> Creating the table in this case takes half an
>> hour and then indexing it requires almost an hour.
> 
> These numbers seem to me to be pretty strong evidence that
> maintenance_work_mem = 1GB is a mistake.  Try it at 100MB and then
some
> intermediate values.
> 
> Now, *why* it is a mistake is interesting to speculate about, but
> let's confirm the theory first.
 
Could this be related to hint bit rewrites during indexing?
 
Would a vacuum between creation and indexing be a good way to tell?
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

[Tom Lane]

"Jeffrey Baker" <[EMAIL PROTECTED]> writes:
> For this query, work_mem is 100MB and maintenance_work_mem is 1GB, on a
> system with 8GB of memory.  Notably I just installed a new storage subsystem
> and upgraded to 8.3.1 less than a week ago, so my experience with this
> instance is somewhat limited.  Creating the table in this case takes half an
> hour and then indexing it requires almost an hour.

These numbers seem to me to be pretty strong evidence that
maintenance_work_mem = 1GB is a mistake.  Try it at 100MB and then some
intermediate values.

Now, *why* it is a mistake is interesting to speculate about, but
let's confirm the theory first.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

[Jeffrey Baker]

On Tue, Jun 24, 2008 at 2:38 PM, Tom Lane <[EMAIL PROTECTED]> wrote:

> "Jeffrey Baker" <[EMAIL PROTECTED]> writes:
> > I'm quite aware of the problems of maintaining such a table and index,
> but
> > the fact is that data warehouse type tables may never be updated after
> being
> > created.  The particular application I'm struggling with does a SELECT
> ...
> > INTO ... ORDER BY to make an ordered table for querying every night.  The
> > problem is it takes longer, much longer, to create the index than to
> create
> > the table, and in the end the index is as big as half the table anyway.
>
> There's something wrong with that: sorting the table rows surely ought
> to take longer than sorting the same number of (smaller) index entries.
> Have you done any profiling to find out what the problem is?  Perhaps
> there's something wrong with the setting of maintenance_work_mem (vs
> work_mem).


For this query, work_mem is 100MB and maintenance_work_mem is 1GB, on a
system with 8GB of memory.  Notably I just installed a new storage subsystem
and upgraded to 8.3.1 less than a week ago, so my experience with this
instance is somewhat limited.  Creating the table in this case takes half an
hour and then indexing it requires almost an hour.  Subsequently analyzing
the table takes less than a minute, with statistics set to maximum.

Query performance is excellent.  I was just brainstorming on ways to save
time on the creation.

-jwb

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

[Tom Lane]

"Jeffrey Baker" <[EMAIL PROTECTED]> writes:
> I'm quite aware of the problems of maintaining such a table and index, but
> the fact is that data warehouse type tables may never be updated after being
> created.  The particular application I'm struggling with does a SELECT ...
> INTO ... ORDER BY to make an ordered table for querying every night.  The
> problem is it takes longer, much longer, to create the index than to create
> the table, and in the end the index is as big as half the table anyway.

There's something wrong with that: sorting the table rows surely ought
to take longer than sorting the same number of (smaller) index entries.
Have you done any profiling to find out what the problem is?  Perhaps
there's something wrong with the setting of maintenance_work_mem (vs
work_mem).

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

[Zoltan Boszormenyi]

Jeffrey Baker írta:
> On Tue, Jun 24, 2008 at 1:59 PM, Zoltan Boszormenyi <[EMAIL PROTECTED]
> > wrote:
>
> Jeffrey Baker írta:
> > The way I read it, the current btree index stores the index
> value and
> > the TID of every tuple having that value.  When you have a table
> with
> > three columns, you index one of them and you get an index which is
> > practically as large as the table itself.
> >
> > Supposing the table is generally or strictly ordered by the
> column to
> > be indexed, it would be more compact if the index stored ranges of
> > tuples.  Instead of storing the TID of every tuple with that value,
> > the index would store a first and last TID, between which all tuples
> > have the value.
> >
> > Example: table with one million rows indexed on a column having one
> > thousand distinct values.  Table is in-order by the indexed column.
> > The traditional index would contain a million TIDs, whereas a range
> > index would contain only two thousand.  The range index would be 500
> > times smaller, more likely to be cached, etc.
> >
> > Thoughts?
> >
> > -jwb
>
> Example with your theory:
> One (not yet committed) transaction changes one tuple
> that was in the middle of a range before but the tuple's indexed
> column changed. What would you do?
>
>
> Insert the new tuple at the end of the table and add another range to
> the index.  Leave the old tuple in place and don't touch the original
> index range.

This is what I described below but I only mentioned the index part:

> You need to keep track of multiple index versions:
> 1. the range has to be split for the not-yet-committed modifier
> transaction,
>it might need to re-read the same table.
> 2. the old range has to be kept for reader transactions that still see
> the old data
>
>
> This is only true if you update the tuple in-place.

Why? If you update in-place then the above is not needed.
You just need to serialize transactions but there goes concurrency.

> Imagine you have thousands of UPDATEs in flight on different rows.
>
>
> I'm quite aware of the problems of maintaining such a table and index,
> but the fact is that data warehouse type tables may never be updated
> after being created.  The particular application I'm struggling with
> does a SELECT ... INTO ... ORDER BY to make an ordered table for
> querying every night.  The problem is it takes longer, much longer, to
> create the index than to create the table, and in the end the index is
> as big as half the table anyway.
>
> So this type of index would only be useful for an essentially
> read-only table.  I agree.
>
> Quite another proposal would be to somehow instruct the database that
> the table is strictly in-order by a column and allow a binary search
> access method.  Then you don't need any index at all.

CLUSTER tablename USING indexname;
It's useful for little changing very large tables.

> -jwb
>


-- 
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PATCHES] [HACKERS] Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

[Tom Lane]

daveg <[EMAIL PROTECTED]> writes:
> lock-timeout sets statement_timeout to a small value while locks are being
> taken on all the tables. Then it resets it to default. So it could reset it
> to whatever the new default is.

"reset to default" is *surely* not the right behavior; resetting to the
setting that had been in effect might be a bit sane.  But the whole
design sounds pretty broken to start with.  The timer management code
already understands the concept of scheduling the interrupt for the
nearest of a couple of potentially active timeouts.  ISTM any patch
intended to add a feature like this ought to extend that logic rather
than hack around changing the values of global variables.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dept of ugly hacks: eliminating padding space in system indexes

[Stephen R. van den Berg]

Mark Mielke wrote:
>saved - we're talking about 154 Kbytes saved on both those indices 
>combined. Minor? Major? I bet I wouldn't notice unless my database 
>requirements used up all RAM, and even then I'm suspecting it wouldn't 
>matter except for border line cases (like all pages required for 
>everything else happened to equal 4 Gbytes near exactly).

There is always only so much of 1st level and 2nd level cache; for those
the savings might well make a difference, even on multigigabyte
databases.
-- 
Sincerely,
   Stephen R. van den Berg.

Life is that brief interlude between nothingness and eternity.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

[Jeffrey Baker]

On Tue, Jun 24, 2008 at 1:59 PM, Zoltan Boszormenyi <[EMAIL PROTECTED]> wrote:

> Jeffrey Baker írta:
> > The way I read it, the current btree index stores the index value and
> > the TID of every tuple having that value.  When you have a table with
> > three columns, you index one of them and you get an index which is
> > practically as large as the table itself.
> >
> > Supposing the table is generally or strictly ordered by the column to
> > be indexed, it would be more compact if the index stored ranges of
> > tuples.  Instead of storing the TID of every tuple with that value,
> > the index would store a first and last TID, between which all tuples
> > have the value.
> >
> > Example: table with one million rows indexed on a column having one
> > thousand distinct values.  Table is in-order by the indexed column.
> > The traditional index would contain a million TIDs, whereas a range
> > index would contain only two thousand.  The range index would be 500
> > times smaller, more likely to be cached, etc.
> >
> > Thoughts?
> >
> > -jwb
>
> Example with your theory:
> One (not yet committed) transaction changes one tuple
> that was in the middle of a range before but the tuple's indexed
> column changed. What would you do?


Insert the new tuple at the end of the table and add another range to the
index.  Leave the old tuple in place and don't touch the original index
range.


> You need to keep track of multiple index versions:
> 1. the range has to be split for the not-yet-committed modifier
> transaction,
>it might need to re-read the same table.
> 2. the old range has to be kept for reader transactions that still see
> the old data


This is only true if you update the tuple in-place.


> Imagine you have thousands of UPDATEs in flight on different rows.
>

I'm quite aware of the problems of maintaining such a table and index, but
the fact is that data warehouse type tables may never be updated after being
created.  The particular application I'm struggling with does a SELECT ...
INTO ... ORDER BY to make an ordered table for querying every night.  The
problem is it takes longer, much longer, to create the index than to create
the table, and in the end the index is as big as half the table anyway.

So this type of index would only be useful for an essentially read-only
table.  I agree.

Quite another proposal would be to somehow instruct the database that the
table is strictly in-order by a column and allow a binary search access
method.  Then you don't need any index at all.

-jwb

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

[Zoltan Boszormenyi]

Jeffrey Baker írta:
> The way I read it, the current btree index stores the index value and
> the TID of every tuple having that value.  When you have a table with
> three columns, you index one of them and you get an index which is
> practically as large as the table itself.
>
> Supposing the table is generally or strictly ordered by the column to
> be indexed, it would be more compact if the index stored ranges of
> tuples.  Instead of storing the TID of every tuple with that value,
> the index would store a first and last TID, between which all tuples
> have the value.
>
> Example: table with one million rows indexed on a column having one
> thousand distinct values.  Table is in-order by the indexed column. 
> The traditional index would contain a million TIDs, whereas a range
> index would contain only two thousand.  The range index would be 500
> times smaller, more likely to be cached, etc.
>
> Thoughts?
>
> -jwb

Example with your theory:
One (not yet committed) transaction changes one tuple
that was in the middle of a range before but the tuple's indexed
column changed. What would you do?
You need to keep track of multiple index versions:
1. the range has to be split for the not-yet-committed modifier transaction,
it might need to re-read the same table.
2. the old range has to be kept for reader transactions that still see
the old data
Imagine you have thousands of UPDATEs in flight on different rows.

Or you introduce "readers has to wait for writers locks" and
"updaters has to wait for other updaters on the same range"
that the MVCC implementation nicely avoids.

Look at MaxDB once, you'll appreciate PostgreSQL then.
MaxDB stores table tuples in the order of its primary key,
it uses a balanced btree for that. This means slower INSERTs and
UPDATEs and decreased concurrency compared to PostgreSQL.

Best regards,
Zoltán Böszörményi

-- 
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PATCHES] [HACKERS] Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

[daveg]

On Mon, Jun 23, 2008 at 07:30:53PM -0400, Bruce Momjian wrote:
> daveg wrote:
> > On Mon, Jun 23, 2008 at 06:51:28PM -0400, Bruce Momjian wrote:
> > > Alex Hunsaker wrote:
> > > > On Wed, Apr 16, 2008 at 4:54 PM, Alvaro Herrera
> > > > <[EMAIL PROTECTED]> wrote:
> > > > > Joshua D. Drake escribi?:
> > > > >
> > > > > > That is an interesting idea. Something like:
> > > > >  >
> > > > >  > pg_restore -E "SET STATEMENT_TIMEOUT=0; SET 
> > > > > MAINTENANCE_WORK_MEM=1G" ?
> > > > >
> > > > >  We already have it -- it's called PGOPTIONS.
> > > > >
> > > > 
> > > > Ok but is not the purpose of the patch to turn off statement_timeout
> > > > by *default* in pg_restore/pg_dump?
> > > > 
> > > > Here is an updated patch for I posted above (with the command line
> > > > option --use-statement-timeout) for pg_dump and pg_restore.
> > > 
> > > I would like to get do this without adding a new --use-statement-timeout
> > > flag.  Is anyone going to want to honor statement_timeout during
> > > pg_dump/pg_restore?  I thought we were just going to disable it.
> > 
> > I have a patch in the queue to use set statement timeout while pg_dump is
> > taking locks to avoid pg_dump hanging for other long running transactions
> > that may have done ddl. Do I need to repost for discussion now?
> 
> I see it now, but I forgot how it would interact with this patch.  We
> would have to prevent --use-statement-timeout when lock timeout was
> being used, but my point is that I see no value in having
> --use-statement-timeout.

lock-timeout sets statement_timeout to a small value while locks are being
taken on all the tables. Then it resets it to default. So it could reset it
to whatever the new default is.

Do I need to adjust my patch or something?

-dg

-- 
David Gould   [EMAIL PROTECTED]  510 536 1443510 282 0869
If simplicity worked, the world would be overrun with insects.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal for smaller indexes on index-ordered tables

[Jonah H. Harris]

On Tue, Jun 24, 2008 at 4:34 PM, Jeffrey Baker <[EMAIL PROTECTED]> wrote:
> Supposing the table is generally or strictly ordered by the column to be
> indexed, it would be more compact if the index stored ranges of tuples.
> Instead of storing the TID of every tuple with that value, the index would
> store a first and last TID, between which all tuples have the value.

There are several databases which implement this idea.  Unfortunately,
Postgres does not yet ensure that indexed tables remain indexed.  As
such, an index such as this would soon be ineffective.  IIRC, Heikki
has done some work on keeping clustered tables clustered, but it
hasn't yet made it into core.

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | [EMAIL PROTECTED]
Edison, NJ 08837 | http://www.enterprisedb.com/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] proposal for smaller indexes on index-ordered tables

[Jeffrey Baker]

The way I read it, the current btree index stores the index value and the
TID of every tuple having that value.  When you have a table with three
columns, you index one of them and you get an index which is practically as
large as the table itself.

Supposing the table is generally or strictly ordered by the column to be
indexed, it would be more compact if the index stored ranges of tuples.
Instead of storing the TID of every tuple with that value, the index would
store a first and last TID, between which all tuples have the value.

Example: table with one million rows indexed on a column having one thousand
distinct values.  Table is in-order by the indexed column.  The traditional
index would contain a million TIDs, whereas a range index would contain only
two thousand.  The range index would be 500 times smaller, more likely to be
cached, etc.

Thoughts?

-jwb

Re: [HACKERS] Git Repository for WITH RECURSIVE and others

[Magnus Hagander]

David Fetter wrote:
> On Tue, Jun 24, 2008 at 05:27:38PM +0200, Magnus Hagander wrote:
>> David Fetter wrote:
>>> On Tue, Jun 24, 2008 at 09:52:22AM +0200, Magnus Hagander wrote:
 David Fetter wrote:
> Folks,
>
> With lots of help from Greg Sabino Mullane, I've set up a git
> repository for the WITH RECURSIVE patches on
> .
>
> What other patches would people like to try maintaining this way
> until commitfest?
>
> It looks like gitosis is a good way to grant write access to git
> repositories, but it's not yet packaged for FreeBSD.  Any ideas
> about how to handle this?
 As you were answered the last time you asked about it, people are
 already working on this.
>>> Which people, and what are they doing?  The silence here has been
>>> deafening.
>> Peter is in charge of the GIT repository, and I've offered to make
>> the changes once we've agreed exactly on what should be done.
> 
> I think it's time for a few more people--yes, I'm volunteering for the
> work--to get the needed access for this :)

Heh :)


 Unfortunately, the requirements have also been raised a bit (such
 as allowing a user to delegate access to another user)
>>> Who raised those requirements, and where did that discussion take
>>> place? 
>> Peter did.
> 
> So, no public discussion anywhere, even though this is something that
> the future development of Postgres is supposed to depend on.  That's
> just great.

I don't know about that part. My POC has been Peter, I don't know whom
else he has talked to before he told me. I may not have been clear about
that part, sorry.


 which means it will take longer.

 Now, if you can give us a step-by-step on how to set it up, that
 would certainly help ;-)
>>> Gitosis does not, as far as I can tell, have that delegation
>>> capability, but I've come up with a way to do this:
>>>
>>> 1.  Use git-shell.  Yes, this does involve creating one shell account
>>> for each project, but git-shell is, by design, very short on
>>> exploitable capability.
>>>
>>> 2.  Make the .ssh directory a git repository.
>>>
>>> 3.  Edit .ssh/authorized_keys and push via git.
>> I was looking into being able to do it using gitosis, with an
>> interface on top of it's existing GIT repository for being able to
>> delegate this.
> 
> I discussed this with gitosis's author, and he wants to keep gitosis
> from becoming "a sourceforge reimplementation."  He did, however,
> commit to stamping it 1.0 and putting up a TODO list.  I'd like to
> package it up for FreeBSD and Fedora, those being two common
> platforms.

That would be good.


>> I think it can be done without modifying gitosis itself, by just
>> writing some simple frontend script on top of it.
> 
> Would the front-end script just modify gitosis.conf?  If so, it's got
> to be pretty bullet-proof because it can step on access to all the git
> repositories.

Yes, that's what I thought.


>> What do you think of this idea?
> 
> It's complicated :(
> 
> Wouldn't it be easier to have a gitosis admin team with the needed
> access?

Yes, that'd probably be easier, and it's what I'd start the
implementation out at.

//Magnus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] MSVC 2003 compile error with pg8.3.3

[Jeff McKenna]

Hello,

I am trying to compile libpq.dll with MSVC 2003 on windows, using  
postgresql-8.3.3, but I get the following compile error:


   Creating library .\Release\libpqdll.lib and object .\Release 
\libpqdll.exp
libpq.lib(dirmod.obj) : error LNK2019: unresolved external symbol  
__dosmaperr r

ferenced in function _pgwin32_safestat
libpq.lib(dirent.obj) : error LNK2001: unresolved external symbol  
__dosmaperr

.\Release\libpq.dll : fatal error LNK1120: 1 unresolved externals
NMAKE : fatal error U1077: 'link.exe' : return code '0x460'
Stop.
NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual  
Studio .NET 2003

VC7\BIN\nmake.exe"' : return code '0x2'
Stop.

Does anyone have any ideas how to solve this??

thanks.

-jeff





---
Jeff McKenna
FOSS4G Consulting and Training Services
http://www.gatewaygeomatics.com/






--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Git Repository for WITH RECURSIVE and others

[David Fetter]

On Tue, Jun 24, 2008 at 02:58:38PM -0300, Marc G. Fournier wrote:
>> Well I will grant that I don't know that there is a better forum 
>> because we don't have a [EMAIL PROTECTED] :) but I am pretty 
>> certain that discussion of the Git repo administration doesn't have 
>> much to do with -hackers.
>
> How about some generic list?  [EMAIL PROTECTED] or something like  
> that?

For administration, definitely.  For hacking discussions, -hackers is
the appropriate place :)

Cheers,
David.
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Need "Round Robin Reviewer" volunteers

[Josh Berkus]

Josh Berkus wrote:

Hackers,

Hey!  So far, a sum total of *2* people have signed up to be assigned 
reviewers, and one of them I put down myself.  If only Greg and Alvaro 
are available to review patches, no wonder our commit fests take so long!


Oh, also -- you don't have to be a committer to be a reviewer.  If you 
can read C code, and know the PostgreSQL coding conventions, you can be 
a reviewer!


--Josh

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Git Repository for WITH RECURSIVE and others

[Marc G. Fournier]

Well I will grant that I don't know that there is a better forum because 
we don't have a [EMAIL PROTECTED] :) but I am pretty certain that 
discussion of the Git repo administration doesn't have much to do with 
-hackers.


How about some generic list?  [EMAIL PROTECTED] or something like 
that?


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Git Repository for WITH RECURSIVE and others

[David Fetter]

On Tue, Jun 24, 2008 at 05:27:38PM +0200, Magnus Hagander wrote:
> David Fetter wrote:
> > On Tue, Jun 24, 2008 at 09:52:22AM +0200, Magnus Hagander wrote:
> >> David Fetter wrote:
> >>> Folks,
> >>>
> >>> With lots of help from Greg Sabino Mullane, I've set up a git
> >>> repository for the WITH RECURSIVE patches on
> >>> .
> >>>
> >>> What other patches would people like to try maintaining this way
> >>> until commitfest?
> >>>
> >>> It looks like gitosis is a good way to grant write access to git
> >>> repositories, but it's not yet packaged for FreeBSD.  Any ideas
> >>> about how to handle this?
> >> As you were answered the last time you asked about it, people are
> >> already working on this.
> > 
> > Which people, and what are they doing?  The silence here has been
> > deafening.
> 
> Peter is in charge of the GIT repository, and I've offered to make
> the changes once we've agreed exactly on what should be done.

I think it's time for a few more people--yes, I'm volunteering for the
work--to get the needed access for this :)

> >> Unfortunately, the requirements have also been raised a bit (such
> >> as allowing a user to delegate access to another user)
> > 
> > Who raised those requirements, and where did that discussion take
> > place? 
> 
> Peter did.

So, no public discussion anywhere, even though this is something that
the future development of Postgres is supposed to depend on.  That's
just great.

> > I don't recall any decision to do any of this by star chamber and
> > secret cabal, and frankly, moving the goalposts on this is a great
> > way to have it never actually happen.  Is that your intention?
> 
> Not mine :-)  My suggestion is to fix what we have now, and then add
> more stuff later.

Excellent!

> >> which means it will take longer.
> >>
> >> Now, if you can give us a step-by-step on how to set it up, that
> >> would certainly help ;-)
> > 
> > Gitosis does not, as far as I can tell, have that delegation
> > capability, but I've come up with a way to do this:
> > 
> > 1.  Use git-shell.  Yes, this does involve creating one shell account
> > for each project, but git-shell is, by design, very short on
> > exploitable capability.
> > 
> > 2.  Make the .ssh directory a git repository.
> > 
> > 3.  Edit .ssh/authorized_keys and push via git.
> 
> I was looking into being able to do it using gitosis, with an
> interface on top of it's existing GIT repository for being able to
> delegate this.

I discussed this with gitosis's author, and he wants to keep gitosis
from becoming "a sourceforge reimplementation."  He did, however,
commit to stamping it 1.0 and putting up a TODO list.  I'd like to
package it up for FreeBSD and Fedora, those being two common
platforms.

> I think it can be done without modifying gitosis itself, by just
> writing some simple frontend script on top of it.

Would the front-end script just modify gitosis.conf?  If so, it's got
to be pretty bullet-proof because it can step on access to all the git
repositories.

> What do you think of this idea?

It's complicated :(

Wouldn't it be easier to have a gitosis admin team with the needed
access?

Cheers,
David.
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] My upcoming events

[Bruce Momjian]

I recently mentioned I am speaking in New York City tonight.  I am also
speaking about open source in Massachusetts in July;  see my events web
page for details on both events:

http://momjian.us/main/events.html

Also, I assume people like these details posted to hackers and advocacy,
but _not_ to the events web page or the announce email list.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Git Repository for WITH RECURSIVE and others

[Joshua D. Drake]

On Tue, 2008-06-24 at 09:27 -0700, David Fetter wrote:
> On Tue, Jun 24, 2008 at 09:21:27AM -0700, Joshua D. Drake wrote:
> > On Tue, 2008-06-24 at 07:55 -0700, David Fetter wrote:

> > Lastly, you could also perhaps take the 10 seconds it takes to find
> > this page:
> > 
> > http://git.postgresql.org/static/serviceinfo.html
> > 
> > Where it tells you exactly how to participate. 
> 
> Yes, and since you brought that up, it appears that mail to
> [EMAIL PROTECTED] goes to /dev/null.  I specifically asked
> for a couple of different things at that email, and have gotten 'tude
> but no action.

Well I can appreciate that problem but its their project, if they are
unwilling to answer you... 

> 
> As far as discussion goes, if there's a forum more appropriate than
> -hackers, please feel free to specify exactly what that forum is and
> explain why you believe that. :)

Well I will grant that I don't know that there is a better forum because
we don't have a [EMAIL PROTECTED] :) but I am pretty certain that
discussion of the Git repo administration doesn't have much to do with
-hackers.

Either way it would seem to me the place for this to happen would be
between yourself, Magnus and Peter. Once everything is done, put it on
wiki... and be done with it :)

Sincerely,

Joshua D. Drake





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Need "Round Robin Reviewer" volunteers

[Josh Berkus]

Hackers,

Hey!  So far, a sum total of *2* people have signed up to be assigned 
reviewers, and one of them I put down myself.  If only Greg and Alvaro 
are available to review patches, no wonder our commit fests take so long!


At the developer meeting, we determined that one thing needed to speed 
up the commitfests is a list of people who were available to review 
assigned patches, and someone to do the assigning.  Well, for July I'm 
the assignor, and I'm looking for some assignees.  Here's how it will work:


July 1: commitfest starts.
July 7: reviewer assignment, 1st round
July 10: reviewer assignment, 2nd round
July 15 (hopefully): commitfest complete.

Reviewer assgnment: I look over the list of submitted patches and see 
which ones aren't getting attention.  shared_buffer fashion, I start 
assigning them round-robin from the people our "pool" of reviewers who 
haven't already taken on patches.  I'll e-mail you with a patch I want 
you to review, and you will accept or reject the assignment promptly. 
I'll continue this until everything is assigned.


So, can everyone who is qualified to be part of the pool please put your 
name on the RRR list?


http://wiki.postgresql.org/wiki/RRReviewers

Thanks!

--Josh Berkus




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Git Repository for WITH RECURSIVE and others

[David Fetter]

On Tue, Jun 24, 2008 at 09:21:27AM -0700, Joshua D. Drake wrote:
> On Tue, 2008-06-24 at 07:55 -0700, David Fetter wrote:
> > On Tue, Jun 24, 2008 at 09:52:22AM +0200, Magnus Hagander wrote:
> > > David Fetter wrote:
> > > > Folks,
> 
> > > Unfortunately, the requirements have also been raised a bit
> > > (such as allowing a user to delegate access to another user)
> > 
> > Who raised those requirements, and where did that discussion take
> > place?  I don't recall any decision to do any of this by star
> > chamber and secret cabal, and frankly, moving the goalposts on
> > this is a great way to have it never actually happen.  Is that
> > your intention?
> 
> You could take your complaints to an appropriate forum, which is not
> hackers.  Then you could take it up with the people that are
> actually in charge of the repository. 
> 
> Lastly, you could also perhaps take the 10 seconds it takes to find
> this page:
> 
> http://git.postgresql.org/static/serviceinfo.html
> 
> Where it tells you exactly how to participate. 

Yes, and since you brought that up, it appears that mail to
[EMAIL PROTECTED] goes to /dev/null.  I specifically asked
for a couple of different things at that email, and have gotten 'tude
but no action.

As far as discussion goes, if there's a forum more appropriate than
-hackers, please feel free to specify exactly what that forum is and
explain why you believe that. :)

Cheers,
David.
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Git Repository for WITH RECURSIVE and others

[Joshua D. Drake]

On Tue, 2008-06-24 at 07:55 -0700, David Fetter wrote:
> On Tue, Jun 24, 2008 at 09:52:22AM +0200, Magnus Hagander wrote:
> > David Fetter wrote:
> > > Folks,

> 
> > Unfortunately, the requirements have also been raised a bit (such as
> > allowing a user to delegate access to another user)
> 
> Who raised those requirements, and where did that discussion take
> place?  I don't recall any decision to do any of this by star chamber
> and secret cabal, and frankly, moving the goalposts on this is a great
> way to have it never actually happen.  Is that your intention?

You could take your complaints to an appropriate forum, which is not
hackers. Then you could take it up with the people that are actually in
charge of the repository. 

Lastly, you could also perhaps take the 10 seconds it takes to find this
page:

http://git.postgresql.org/static/serviceinfo.html

Where it tells you exactly how to participate. 


Joshua D. Drake





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PATCHES] [HACKERS] Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

[Bruce Momjian]

Joshua D. Drake wrote:
> Alex Hunsaker wrote:
> > On Mon, Jun 23, 2008 at 4:51 PM, Bruce Momjian <[EMAIL PROTECTED]> wrote:
> >> I would like to get do this without adding a new --use-statement-timeout
> >> flag.  Is anyone going to want to honor statement_timeout during
> >> pg_dump/pg_restore?  I thought we were just going to disable it.
> > 
> > I believe so.  This was when not everyone was convinced.  Im fairly
> > certain Josh original patch is in the commit fest. So feel free to
> > drop this one.
> > 
> 
> My patch has been committed.

Ah, I see, but with no switch.  Thanks.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Git Repository for WITH RECURSIVE and others

[Magnus Hagander]

David Fetter wrote:
> On Tue, Jun 24, 2008 at 09:52:22AM +0200, Magnus Hagander wrote:
>> David Fetter wrote:
>>> Folks,
>>>
>>> With lots of help from Greg Sabino Mullane, I've set up a git
>>> repository for the WITH RECURSIVE patches on
>>> .
>>>
>>> What other patches would people like to try maintaining this way
>>> until commitfest?
>>>
>>> It looks like gitosis is a good way to grant write access to git
>>> repositories, but it's not yet packaged for FreeBSD.  Any ideas
>>> about how to handle this?
>> As you were answered the last time you asked about it, people are
>> already working on this.
> 
> Which people, and what are they doing?  The silence here has been
> deafening.

Peter is in charge of the GIT repository, and I've offered to make the
changes once we've agreed exactly on what should be done.


>> Unfortunately, the requirements have also been raised a bit (such as
>> allowing a user to delegate access to another user)
> 
> Who raised those requirements, and where did that discussion take
> place? 

Peter did.


> I don't recall any decision to do any of this by star chamber
> and secret cabal, and frankly, moving the goalposts on this is a great
> way to have it never actually happen.  Is that your intention?

Not mine :-) My suggestion is to fix what we have now, and then add more
stuff later.


>> which means it will take longer.
>>
>> Now, if you can give us a step-by-step on how to set it up, that
>> would certainly help ;-)
> 
> Gitosis does not, as far as I can tell, have that delegation
> capability, but I've come up with a way to do this:
> 
> 1.  Use git-shell.  Yes, this does involve creating one shell account
> for each project, but git-shell is, by design, very short on
> exploitable capability.
> 
> 2.  Make the .ssh directory a git repository.
> 
> 3.  Edit .ssh/authorized_keys and push via git.

I was looking into being able to do it using gitosis, with an interface
on top of it's existing GIT repository for being able to delegate this.
I think it can be done without modifying gitosis itself, by just writing
some simple frontend script on top of it. What do you think of this idea?

//Magnus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PATCHES] [HACKERS] Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

[Joshua D. Drake]

Alex Hunsaker wrote:

On Mon, Jun 23, 2008 at 4:51 PM, Bruce Momjian <[EMAIL PROTECTED]> wrote:

I would like to get do this without adding a new --use-statement-timeout
flag.  Is anyone going to want to honor statement_timeout during
pg_dump/pg_restore?  I thought we were just going to disable it.


I believe so.  This was when not everyone was convinced.  Im fairly
certain Josh original patch is in the commit fest. So feel free to
drop this one.



My patch has been committed.

Joshua D. Drake

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dept of ugly hacks: eliminating padding space in system indexes

[Joshua D. Drake]

Josh Berkus wrote:

Shane Ambler wrote:

Mark Mielke wrote:

Not that I disagree with your change, but < 5 Mbytes in 4 Gbytes of 
RAM for my main PostgreSQL system that I manage seems like a drop in 
the bucket. Even if 40% of pg_class_relname and pg_proc_proname 
indices was saved - we're talking about 154 Kbytes saved on both 
those indices combined. Minor? Major? I bet I wouldn't notice unless 
my database requirements used up all RAM, and even then I'm 
suspecting it wouldn't matter except for border line cases (like all 
pages required for everything else happened to equal 4 Gbytes near 
exactly).


Guess the mileage will vary depending on the complexity of the db 
structure. Shorter names will also benefit more than longer ones.


There are PostgreSQL users out there with more than 100,000 tables per 
server instance.  This will make more of a difference to them.


More than I think people realize.

Joshua D. Drake



--Josh





--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dept of ugly hacks: eliminating padding space in system indexes

[Josh Berkus]

Shane Ambler wrote:

Mark Mielke wrote:

Not that I disagree with your change, but < 5 Mbytes in 4 Gbytes of 
RAM for my main PostgreSQL system that I manage seems like a drop in 
the bucket. Even if 40% of pg_class_relname and pg_proc_proname 
indices was saved - we're talking about 154 Kbytes saved on both those 
indices combined. Minor? Major? I bet I wouldn't notice unless my 
database requirements used up all RAM, and even then I'm suspecting it 
wouldn't matter except for border line cases (like all pages required 
for everything else happened to equal 4 Gbytes near exactly).


Guess the mileage will vary depending on the complexity of the db 
structure. Shorter names will also benefit more than longer ones.


There are PostgreSQL users out there with more than 100,000 tables per 
server instance.  This will make more of a difference to them.


--Josh


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Git Repository for WITH RECURSIVE and others

[David Fetter]

On Tue, Jun 24, 2008 at 09:52:22AM +0200, Magnus Hagander wrote:
> David Fetter wrote:
> > Folks,
> > 
> > With lots of help from Greg Sabino Mullane, I've set up a git
> > repository for the WITH RECURSIVE patches on
> > .
> > 
> > What other patches would people like to try maintaining this way
> > until commitfest?
> > 
> > It looks like gitosis is a good way to grant write access to git
> > repositories, but it's not yet packaged for FreeBSD.  Any ideas
> > about how to handle this?
> 
> As you were answered the last time you asked about it, people are
> already working on this.

Which people, and what are they doing?  The silence here has been
deafening.

> Unfortunately, the requirements have also been raised a bit (such as
> allowing a user to delegate access to another user)

Who raised those requirements, and where did that discussion take
place?  I don't recall any decision to do any of this by star chamber
and secret cabal, and frankly, moving the goalposts on this is a great
way to have it never actually happen.  Is that your intention?

> which means it will take longer.
> 
> Now, if you can give us a step-by-step on how to set it up, that
> would certainly help ;-)

Gitosis does not, as far as I can tell, have that delegation
capability, but I've come up with a way to do this:

1.  Use git-shell.  Yes, this does involve creating one shell account
for each project, but git-shell is, by design, very short on
exploitable capability.

2.  Make the .ssh directory a git repository.

3.  Edit .ssh/authorized_keys and push via git.

Cheers,
David.
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Git Repository for WITH RECURSIVE and others

[David Fetter]

On Tue, Jun 24, 2008 at 03:26:36PM +0900, Yoshiyuki Asaba wrote:
> Hi,
> 
> From: David Fetter <[EMAIL PROTECTED]>
> Subject: [HACKERS] Git Repository for WITH RECURSIVE and others
> Date: Mon, 23 Jun 2008 21:38:11 -0700
> 
> > With lots of help from Greg Sabino Mullane, I've set up a git
> > repository for the WITH RECURSIVE patches on
> > .
> 
> Thank you very much.
> 
> I tried git-clone, but I could not access the repository.
> 
>   % git-clone git://git.postgresql.org/git/~davidfetter/postgresql/.git
>   Initialized empty Git repository in /home/y-asaba/x/postgresql/.git/
>   fatal: The remote end hung up unexpectedly
>   fetch-pack from 'git://git.postgresql.org/git/~davidfetter/postgresql/.git' 
> failed.

I ran git-update-server-info on the server, and it should work now. :)

Cheers,
David.
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: [EMAIL PROTECTED]

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dept of ugly hacks: eliminating padding space in system indexes

[Tom Lane]

Teodor Sigaev <[EMAIL PROTECTED]> writes:
>> dead easy to implement this: effectively, we just decree that the
>> index column storage type for NAME is always CSTRING.  Because the

> Isn't it a reason to add STORAGE option of CREATE OPERATOR CLASS to BTree? as
> it's done for GiST and GIN indexes.

Hmm ... I don't see a point in exposing that as a user-level facility,
unless you can point to other use-cases besides NAME.  But it would be
cute to implement the hack by changing the initial contents of
pg_opclass instead of inserting code in the backend.  I'll give that
a try.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] stat() vs cygwin

[Magnus Hagander]

Andrew Dunstan wrote:
> 
> 
> Magnus Hagander wrote:
>> Kenneth Marshall wrote:
>>  
>>> One motivation for keeping it working on Cygwin, is that in some
>>> environments it is not allowed to install native Windows apps but
>>> they allow the use of the Cygwin environment. Of course if it takes
>>> too many resources to support, then dropping support would be an
>>> option. I would check this for you, but I am in the middle of moving
>>> and my Windows/Cygwin box is not available right now.
>>> 
>>
>> Does anybody seriously have such a broken policy? I know a lot of places
>> who have inverse policy, where they don't allow cygwin, but I've never
>> heard of anybody refusing native programs and only allowing cygwin. Just
>> like I've heard of no linux shops requiring that you run your database
>> under wine...
>>
>>
>>   
> 
> This whole argument is pointless, ISTM. We are not in the business of
> telling people what environment to use Postgres in.

Well, agreed, the argument was that it should be dropped *IF* it turns
into a maintenance burden. Which it hasn't yet done.


> More to the point: I thought this had been tested. I will test it today
> so we can put this whole thread to rest.

IIRC it was only tested insofar that it doesn't actually break. Not if
it returns proper results.

Buf if my memory isn't completely off, there are other such cases as
well around the code, where we've done proper fixes for native win32 and
left cygwin alone. The argument being that for a developer system, it
doesn't really matter if things aren't entirely reliable, and that
nobody should be using cygwin for a production server. (I have nothing
against using it for a dev box, though I wouldn't do it myself)

//Magnus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] stat() vs cygwin

[Andrew Dunstan]

Magnus Hagander wrote:

Kenneth Marshall wrote:
  

One motivation for keeping it working on Cygwin, is that in some
environments it is not allowed to install native Windows apps but
they allow the use of the Cygwin environment. Of course if it takes
too many resources to support, then dropping support would be an
option. I would check this for you, but I am in the middle of moving
and my Windows/Cygwin box is not available right now.



Does anybody seriously have such a broken policy? I know a lot of places
who have inverse policy, where they don't allow cygwin, but I've never
heard of anybody refusing native programs and only allowing cygwin. Just
like I've heard of no linux shops requiring that you run your database
under wine...


  


This whole argument is pointless, ISTM. We are not in the business of 
telling people what environment to use Postgres in.


Using Cygwin is still the best way I know of to use psql on Windows, and 
it works just fine as a development environment.


By contrast, setting up a development environment for the native build 
in either supported flavor is distinctly non-trivial.


(And yes I know about the problems Cygwin causes if you put it in the 
System PATH. Don't do that.)


More to the point: I thought this had been tested. I will test it today 
so we can put this whole thread to rest.


cheers

andrew



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] stat() vs cygwin

[Kenneth Marshall]

The case I am referring to has a "developer" clause that allows
Cygwin applications to be used for development only. I agree that
the policy is odd.

Ken

On Tue, Jun 24, 2008 at 02:35:50PM +0200, Magnus Hagander wrote:
> Kenneth Marshall wrote:
> > One motivation for keeping it working on Cygwin, is that in some
> > environments it is not allowed to install native Windows apps but
> > they allow the use of the Cygwin environment. Of course if it takes
> > too many resources to support, then dropping support would be an
> > option. I would check this for you, but I am in the middle of moving
> > and my Windows/Cygwin box is not available right now.
> 
> Does anybody seriously have such a broken policy? I know a lot of places
> who have inverse policy, where they don't allow cygwin, but I've never
> heard of anybody refusing native programs and only allowing cygwin. Just
> like I've heard of no linux shops requiring that you run your database
> under wine...
> 
> //Magnus
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
> 

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] stat() vs cygwin

[Magnus Hagander]

Kenneth Marshall wrote:
> One motivation for keeping it working on Cygwin, is that in some
> environments it is not allowed to install native Windows apps but
> they allow the use of the Cygwin environment. Of course if it takes
> too many resources to support, then dropping support would be an
> option. I would check this for you, but I am in the middle of moving
> and my Windows/Cygwin box is not available right now.

Does anybody seriously have such a broken policy? I know a lot of places
who have inverse policy, where they don't allow cygwin, but I've never
heard of anybody refusing native programs and only allowing cygwin. Just
like I've heard of no linux shops requiring that you run your database
under wine...

//Magnus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [PATCHES] [HACKERS] Patch for Prevent pg_dump/pg_restore from being affected by statement_timeout

[Bruce Momjian]

Alex Hunsaker wrote:
> On Mon, Jun 23, 2008 at 4:51 PM, Bruce Momjian <[EMAIL PROTECTED]> wrote:
> > I would like to get do this without adding a new --use-statement-timeout
> > flag.  Is anyone going to want to honor statement_timeout during
> > pg_dump/pg_restore?  I thought we were just going to disable it.
> 
> I believe so.  This was when not everyone was convinced.  Im fairly
> certain Josh original patch is in the commit fest. So feel free to
> drop this one.

I certainly don't see any version of Drake's patch in the July
commitfest:

http://wiki.postgresql.org/wiki/CommitFest

I am thinking I will just remove the option and commit it.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] stat() vs cygwin

[Kenneth Marshall]

One motivation for keeping it working on Cygwin, is that in some
environments it is not allowed to install native Windows apps but
they allow the use of the Cygwin environment. Of course if it takes
too many resources to support, then dropping support would be an
option. I would check this for you, but I am in the middle of moving
and my Windows/Cygwin box is not available right now.

Cheers,
Ken

On Tue, Jun 24, 2008 at 10:32:08AM +0200, Magnus Hagander wrote:
> Yes.
> 
> As in the cygwin build does build. Nobody really has verified if the fix
> is needed there. But frankly, if you are likely to care about the
> effects of this issue, you won't be running cygwin anyway. It's mostly a
> dead platform for postgresql anyway, AFAICS we only keep it building for
> legacy compatibility. Once it starts taking lots of resources to keep
> building (which it doesn't now), I think we should just drop it instead...
> 
> //Magnus
> 
> Bruce Momjian wrote:
> > Magnus, was this fixed/resolved?
> > 
> > ---
> > 
> > Magnus Hagander wrote:
> >> It seems my fix for stat() broke cygwin, because it doesn't have
> >> dosmaperr() available. The way I see it there are two ways to fix this:
> >>
> >> 1) Don't apply the stat fix for cygwin.
> >>
> >> 2) Make our dosmaperr() function be used on cygwin.
> >>
> >>
> >> I don't know if the fix is actually needed on cygwin. Can someone with
> >> access to such an environment test it and see?
> >>
> >> The easy check, easier than the table, goes something along the line
> >> of:
> >> CREATE TABLE test(t int);
> >> INSERT INTO test(t) SELECT * FROM generate_series(1,10);
> >> SELECT pg_relation_size('t');
> >> SELECT pg_sleep(5);
> >> SELECT pg_relation_size('t');
> >>
> >>
> >> Without the patch on win32, the first pg_relation_size comes out as 0,
> >> and the second one correct. With the patch, they come out equal. They
> >> should, of course, always come out equal.
> >>
> >> //Magnus
> >>
> >> -- 
> >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-hackers
> > 
> 
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
> 

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Dept of ugly hacks: eliminating padding space in system indexes

[Heikki Linnakangas]

Shane Ambler wrote:
My question is whether this is limited to system catalogs? or will this 
benefit char() index used on any table? The second would make it more 
worthwhile.


char(n) fields are already stored as variable-length on disk. This isn't 
applicable to them.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] stat() vs cygwin

[Dave Page]

On Tue, Jun 24, 2008 at 9:32 AM, Magnus Hagander <[EMAIL PROTECTED]> wrote:
> Yes.
>
> As in the cygwin build does build. Nobody really has verified if the fix
> is needed there. But frankly, if you are likely to care about the
> effects of this issue, you won't be running cygwin anyway. It's mostly a
> dead platform for postgresql anyway, AFAICS we only keep it building for
> legacy compatibility. Once it starts taking lots of resources to keep
> building (which it doesn't now), I think we should just drop it instead...

FWIW, the most recent packages from Cygwin themselves are 8.2.5.

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] stat() vs cygwin

[Magnus Hagander]

Yes.

As in the cygwin build does build. Nobody really has verified if the fix
is needed there. But frankly, if you are likely to care about the
effects of this issue, you won't be running cygwin anyway. It's mostly a
dead platform for postgresql anyway, AFAICS we only keep it building for
legacy compatibility. Once it starts taking lots of resources to keep
building (which it doesn't now), I think we should just drop it instead...

//Magnus

Bruce Momjian wrote:
> Magnus, was this fixed/resolved?
> 
> ---
> 
> Magnus Hagander wrote:
>> It seems my fix for stat() broke cygwin, because it doesn't have
>> dosmaperr() available. The way I see it there are two ways to fix this:
>>
>> 1) Don't apply the stat fix for cygwin.
>>
>> 2) Make our dosmaperr() function be used on cygwin.
>>
>>
>> I don't know if the fix is actually needed on cygwin. Can someone with
>> access to such an environment test it and see?
>>
>> The easy check, easier than the table, goes something along the line
>> of:
>> CREATE TABLE test(t int);
>> INSERT INTO test(t) SELECT * FROM generate_series(1,10);
>> SELECT pg_relation_size('t');
>> SELECT pg_sleep(5);
>> SELECT pg_relation_size('t');
>>
>>
>> Without the patch on win32, the first pg_relation_size comes out as 0,
>> and the second one correct. With the patch, they come out equal. They
>> should, of course, always come out equal.
>>
>> //Magnus
>>
>> -- 
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
> 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Git Repository for WITH RECURSIVE and others

[Magnus Hagander]

David Fetter wrote:
> Folks,
> 
> With lots of help from Greg Sabino Mullane, I've set up a git
> repository for the WITH RECURSIVE patches on
> .
> 
> What other patches would people like to try maintaining this way until
> commitfest?
> 
> It looks like gitosis is a good way to grant write access to git
> repositories, but it's not yet packaged for FreeBSD.  Any ideas about
> how to handle this?

As you were answered the last time you asked about it, people are
already working on this. Unfortunately, the requirements have also been
raised a bit (such as allowing a user to delegate access to another
user) which means it will take longer.

Now, if you can give us a step-by-step on how to set it up, that would
certainly help ;-)

//Magnus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_stat_statements

[Simon Riggs]

On Tue, 2008-06-24 at 12:14 +0900, ITAGAKI Takahiro wrote:
> Simon Riggs <[EMAIL PROTECTED]> wrote:
> 
> > I think you want to see the distribution of execution times for
> > particular queries without needing to log *every* execution, including
> > parameters. I think I understand now what you are asking for and why you
> > are asking for it.
> 
> Yes. In many case, major influential queries don't depend on thier
> parameters. Also, averages of execution time are useful if you want to
> log indivisual queries that takes long time compared to the averages.
> For example, you can use configuration like:
> "If an execution time is 3 times longer than the average of the same plan,
> write the explain-tree into log".

Yes, thats good.

> That reminds me, it might have association with Auto-explain patch.
> Is it given up?
> http://archives.postgresql.org/pgsql-patches/2008-01/msg00123.php

Not by me. If the author doesn't finish it someone else will.

> > We could also have a function that causes each backend to dump the
> > current averages of all plans through to the stats collector, so you can
> > assemble a global view. But that should be on-demand, not a continuous
> > flow of stats, IMHO.
> 
> Hmm, it's interesting idea. It doesn't require any additional shared
> memory and max length of stat message would be enough for typical
> queries (PGSTAT_MSG_PAYLOAD is 1000 bytes or less). 

There is a related issue here. Sending the text of every query to the
stat collector is an overhead for the same reason sending the text to
the log is an overhead. The solution should be the same also: don't send
the text repeatedly.

The reason we don't increase PGSTAT_MSG_PAYLOAD is that we don't want to
experience more overhead. But if we work out a way to reduce the
frequency of messages, we might be able to increase PGSTAT_MSG_PAYLOAD,
so we see the whole text when we want it, but not send it when nobody is
looking. Now that's a gorgias solution. 

For protocol 3 we should be sending an id, not the whole text each time
we execute. But that won't solve the problem for simple queries.

So pg_stat_get_activity() should poll backends for the SQL of the
currently executing query, not be continually sent.

> I'm not sure
> how to *notify* all backends to send plans to collector, but we could
> send plans periodically instead. Bloated pgstat.stat file might be
> another problem, and I have no idea about it.

Code is already there, and as of a few days ago, unused.
PMSIGNAL_WAKEN_CHILDREN

> I think the stats collector way is not more than the shared memory way in
> efficiency, but it is more flexible. I'll reconsider and compare them...

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers