Re: [HACKERS] [ADMIN] postgresql in FreeBSD jails: proposal

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - --On Thursday, January 17, 2008 01:12:54 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: >> [EMAIL PROTECTED] (Mischa Sandberg) writes: >>> Unfortunately, with multiple jails running PG servers and (due

Re: [HACKERS] [ADMIN] postgresql in FreeBSD jails: proposal

"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] (Mischa Sandberg) writes: >> Unfortunately, with multiple jails running PG servers and (due to app >> limitations) all servers having same PGPORT, you get the situation that >> when jail#2 (,jail#3,...) server comes up, it: >> - det

Re: [HACKERS] postgresql in FreeBSD jails: proposal

[EMAIL PROTECTED] (Mischa Sandberg) writes: >Unfortunately, with multiple jails running PG servers and (due to app >limitations) all servers having same PGPORT, you get the situation that >when jail#2 (,jail#3,...) server comes up, it: >- detects that there is a shm seg with ipc key 5432001 >- che

Re: [HACKERS] Postgresql Materialized views

Simon Riggs wrote: My thinking was if you load a 1000 rows and they all have the same key in your summary table then you'll be doing 1000 updates on a single row. This is true because the statement level triggers are still rudimentary, with no OLD and NEW support. A single AFTER statement tr

Re: [HACKERS] Renaming a constraint's index

Tom Lane wrote: There was some discussion last week on -bugs about how renaming an index that belongs to a unique or primary key constraint is allowed, but can lead to situations that can't be dumped/restored properly. This isn't really pg_dump's fault, IMHO. We should rather make the backend

[HACKERS] Renaming a constraint's index

There was some discussion last week on -bugs about how renaming an index that belongs to a unique or primary key constraint is allowed, but can lead to situations that can't be dumped/restored properly. This isn't really pg_dump's fault, IMHO. We should rather make the backend enforce that the in

Re: [HACKERS] VACUUM FULL out of memory

On Wed, 2008-01-16 at 21:53 +0100, Michael Omotayo Akinde wrote: > As far as I can see, the ulimits are set up as they should; and on a > 64-bit machine with 16GB RAM, I don't see there should be a problem > with allocating 2 GB maintenance work memory. In any case, I have > serious difficulty bel

Re: [HACKERS] Postgresql Materialized views

On Wed, 2008-01-16 at 11:16 +, Heikki Linnakangas wrote: > Merlin Moncure wrote: > > On Jan 12, 2008 4:19 PM, Simon Riggs <[EMAIL PROTECTED]> wrote: > >> On Sat, 2008-01-12 at 13:27 +0100, Jean-Michel Pouré wrote: > >> > >>> Please pick-up this important issue for developpers. There is no need

Re: [HACKERS] to_char incompatibility

Peter Eisentraut wrote: > Am Donnerstag, 10. Januar 2008 schrieb Roberts, Jon: > > > On PostgreSQL: > > > > > > select to_date('31-DEC-200700:00:00', 'dd-mon- hh24:mi:ss'); > > >to_date > > > -- > > > 200700-12-31 > > > Oracle removes all white spaces in the date you pass in a

Re: [HACKERS] COPY encoding

Andrew Dunstan <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Yes, it is. Not sure if it's adequately documented. > Will this cover the case? Text looks OK. I think it might fit better a bit further up, adjacent to the para about DateStyle which is a somewhat comparable consideration.

Re: [HACKERS] to_char incompatibility

Andrew Dunstan wrote: > > > Tom Lane wrote: > > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > > >> A further example shows that to_date seems to have little error checking > >> altogether: > >> > > > > Yeah, that's been one of the main knocks on that code since day one. > > Somebody n

Re: [HACKERS] Some ideas about Vacuum

On Wed, 16 Jan 2008, Kevin Grittner wrote: I haven't seen any benchmarks on the list or in our environment where the separate spindles gave more than a 1% increase in performance when using a good-quality BBC controller. Well, even 1% isn't nothing, which is the main point I was making--it do

Re: [HACKERS] VACUUM FULL out of memory

Just to conclude on the issue we had here. As far as I can see, the ulimits are set up as they should; and on a 64-bit machine with 16GB RAM, I don't see there should be a problem with allocating 2 GB maintenance work memory. In any case, I have serious difficulty believing that the ulimits can be

Re: [HACKERS] Some ideas about Vacuum

Alvaro Herrera wrote: Heikki Linnakangas escribió: For more usefulness, we'd need to keep databases more separate from each other than we do now. Databases would need to have their own transaction counters, for example. Hmm, why? Perhaps you are right but I don't see the reason. If each da

Re: [HACKERS] Some ideas about Vacuum

Heikki Linnakangas escribió: > I don't think it's going to work too well, though, not without major > changes at least. What would happen when you restore a PITR backup of just > one database? Would the other databases still be there in the restored > cluster? What state would they be in? After

Re: [HACKERS] postgresql in FreeBSD jails: proposal

Stephen Frost <[EMAIL PROTECTED]> writes: > I've got a couple of concerns about this- > #1: Having the shared memory be global is a rather large problem when it > comes to something like PG which can have a fair bit of data going > through that area that could be sensitive. Well, you'd

Re: [HACKERS] COPY encoding

Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: In helping someome on IRC it has become apparent that unless I am mistaken "COPY foo from 'filename'" is reading the file according to the client encoding. Is that the expected behaviour? Yes, it is. Not sure if

Re: [HACKERS] Some ideas about Vacuum

On Jan 16, 2008 7:41 PM, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > I don't think it's going to work too well, though, not without major > changes at least. Well, I know it's really not doable with the current behaviour of WAL. I just wanted to point this feature request because we had it a f

Re: [HACKERS] Some ideas about Vacuum

> > > For more usefulness, we'd need to keep databases more separate from each > other than we do now. Databases would need to have their own transaction > counters, for example. Shared relations would obviously need major > changes for that to work. If we ultimately could separate databases so > t

Re: [HACKERS] Some ideas about Vacuum

Guillaume Smet wrote: On Jan 16, 2008 6:12 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Tom Lane escribió: Possibly true, but if that's the underlying hardware then there's no performance benefit in breaking WAL up at all, no? Selective PITR shipping. If it was possible to launch a PITR onl

Re: [HACKERS] Some ideas about Vacuum

>>> On Wed, Jan 16, 2008 at 11:40 AM, in message <[EMAIL PROTECTED]>, Greg Smith <[EMAIL PROTECTED]> wrote: > On Wed, 16 Jan 2008, Alvaro Herrera wrote: > >> Keep in mind that there are claims that a write-cache-enabled >> battery-backed RAID controller negates the effect of a separate spindle. >

Re: [HACKERS] postgresql in FreeBSD jails: proposal

* Mischa Sandberg ([EMAIL PROTECTED]) wrote: > Here (@sophos.com) we run machine cluster tests using FreeBSD jails. A > jail is halfway between a chroot and a VM. Jails blow a number of > assumptions about a unix environment: sysv ipc's are global to all > jails; but a process can only "see" other

Re: [HACKERS] Some ideas about Vacuum

On Wed, 16 Jan 2008, Alvaro Herrera wrote: Keep in mind that there are claims that a write-cache-enabled battery-backed RAID controller negates the effect of a separate spindle. "Negates" is a bit strong; there's still some performance advantage on systems that write a serious amount of data.

Re: [HACKERS] postgresql in FreeBSD jails: proposal

Mischa Sandberg <[EMAIL PROTECTED]> writes: > Quoting Tom Lane <[EMAIL PROTECTED]>: >> Mischa Sandberg <[EMAIL PROTECTED]> writes: >>> + if (kill(1,0) && errno == ESRCH && >>> PGSharedMemoryIsInUse(0,NextShmemSegID)) >>> + continue; >> >> Isn't the last part of

Re: [HACKERS] Some ideas about Vacuum

On Jan 16, 2008 6:12 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Tom Lane escribió: > > Possibly true, but if that's the underlying hardware then there's no > > performance benefit in breaking WAL up at all, no? > > Selective PITR shipping. If it was possible to launch a PITR only on a given d

Re: [HACKERS] Some ideas about Vacuum

"Tom Lane" <[EMAIL PROTECTED]> writes: > Alvaro Herrera <[EMAIL PROTECTED]> writes: >> Heikki Linnakangas escribió: >>> Another issue is that reading WAL is inherently not very scalable. There's >>> only one WAL for the whole cluster, and it needs to be read sequentially, >>> so it can easily be

Re: [HACKERS] postgresql in FreeBSD jails: proposal

Quoting Tom Lane <[EMAIL PROTECTED]>: > Mischa Sandberg <[EMAIL PROTECTED]> writes: > > + /* In a FreeBSD jail, you can't "kill -0" a > postmaster > > +* running in a different jail, so the shm seg > might > > +* still be in use. Safer to test nattch ?

Re: [HACKERS] Some ideas about Vacuum

Tom Lane escribió: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Tom Lane escribió: > >> It would only be useful to have one per spindle-dedicated-to-WAL, so > >> tying the division to databases doesn't seem like it'd be a good idea. > > > Keep in mind that there are claims that a write-cache-e

Re: [HACKERS] postgresql in FreeBSD jails: proposal

Mischa Sandberg <[EMAIL PROTECTED]> writes: > + /* In a FreeBSD jail, you can't "kill -0" a postmaster > +* running in a different jail, so the shm seg might > +* still be in use. Safer to test nattch ? > +*/ > + if (kill(1

Re: [HACKERS] to_char incompatibility

Tom Lane wrote: Peter Eisentraut <[EMAIL PROTECTED]> writes: A further example shows that to_date seems to have little error checking altogether: Yeah, that's been one of the main knocks on that code since day one. Somebody needs to spend a whole lot of time on it, and the original a

Re: [HACKERS] Some ideas about Vacuum

Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane escribió: >> It would only be useful to have one per spindle-dedicated-to-WAL, so >> tying the division to databases doesn't seem like it'd be a good idea. > Keep in mind that there are claims that a write-cache-enabled > battery-backed RAID c

Re: [HACKERS] Transaction Snapshot Cloning

"Florian G. Pflug" <[EMAIL PROTECTED]> writes: > I seem to remember there being some pushback to the idea of changing the > semantics of "set transaction isolation read only" from "soft" to "hard" > semantics though - on the basis that it might break existing > applications. If that has changed

[HACKERS] postgresql in FreeBSD jails: proposal

Here (@sophos.com) we run machine cluster tests using FreeBSD jails. A jail is halfway between a chroot and a VM. Jails blow a number of assumptions about a unix environment: sysv ipc's are global to all jails; but a process can only "see" other processes also running in the jail. In fact, the quic

Re: [HACKERS] Some ideas about Vacuum

Tom Lane escribió: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Heikki Linnakangas escribi�: > >> Another issue is that reading WAL is inherently not very scalable. There's > >> only one WAL for the whole cluster, and it needs to be read sequentially, > >> so it can easily become a bottleneck

Re: [HACKERS] Some ideas about Vacuum

Alvaro Herrera <[EMAIL PROTECTED]> writes: > Heikki Linnakangas escribió: >> Another issue is that reading WAL is inherently not very scalable. There's >> only one WAL for the whole cluster, and it needs to be read sequentially, >> so it can easily become a bottleneck on large systems. > I have

Re: [HACKERS] Password policy

> -Original Message- > From: D'Arcy J.M. Cain [mailto:[EMAIL PROTECTED] > Sent: Wednesday, January 16, 2008 9:39 AM > To: Andrew Dunstan > Cc: Roberts, Jon; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] Password policy > > On Wed, 16 Jan 2008 08:32:12 -0500 > Andrew Dunstan <[EMAI

Re: [HACKERS] Transaction Snapshot Cloning

Tom Lane wrote: Chris Browne <[EMAIL PROTECTED]> writes: Note that we required that the "provider transaction" have the attributes IsXactIsoLevelSerializable and XactReadOnly both being true, so we have the mandates that the resultant backend process: a) Is in read only mode, and b) Is in ser

Re: [HACKERS] Password policy

On Wed, 16 Jan 2008 08:32:12 -0500 Andrew Dunstan <[EMAIL PROTECTED]> wrote: > >> I need to set a basic password policy for accounts but I don't see any > > Look at my chkpass type in contrib. There is a function to verify the > > password. It is just a placeholder now but you can modify it to do

Re: [HACKERS] WAL logging of hash indexes

On Tue, Jan 15, 2008 at 07:18:17PM -0800, John Smith wrote: > http://archives.postgresql.org/pgsql-hackers/2007-10/msg01468.php, > > "... I very much want to encourage authors of new Resource Managers and it > looks like we may be getting at least 3 new RMs that produce WAL > records: hash indexes

Re: [HACKERS] to_char incompatibility

Peter Eisentraut <[EMAIL PROTECTED]> writes: > A further example shows that to_date seems to have little error checking > altogether: Yeah, that's been one of the main knocks on that code since day one. Somebody needs to spend a whole lot of time on it, and the original author has left the projec

Re: [HACKERS] Some ideas about Vacuum

> > > I haven't been paying close attention to this thread, but there is a > couple general issues with using the WAL for this kind of things. First > of all, one extremely cool feature of PostgreSQL is that transaction > size is not limited by WAL space, unlike on many other DBMSs. I think > many

[HACKERS] Thick indexes - a look at count(1) query

Hi, I have submitted a new patch against thick indexes(indexes with snapshot) http://archives.postgresql.org/pgsql-hackers/2007-10/msg00220.php. I did look closely at improving the performance of count(1) queries. It worked well

Re: [HACKERS] Some ideas about Vacuum

Heikki Linnakangas escribió: > Another issue is that reading WAL is inherently not very scalable. There's > only one WAL for the whole cluster, and it needs to be read sequentially, > so it can easily become a bottleneck on large systems. I have wondered why do we do it this way. Is there a pr

Re: [HACKERS] Password policy

D'Arcy J.M. Cain wrote: On Tue, 15 Jan 2008 16:11:16 -0600 "Roberts, Jon" <[EMAIL PROTECTED]> wrote: I need to set a basic password policy for accounts but I don't see any documentation on how to do it. I'm assuming there is a way to do this, maybe even with a trigger. The policy would be

Re: [HACKERS] Declarative partitioning grammar

> > Personally I find the automatic partition idea intriguing, where you > > only have to choose an expression that equates to one value (value > > group) per partition (and possibly a way to derive a > partition name). > > IMO, better go right to a fully automated approach. Or why would you >

Re: [HACKERS] Some ideas about Vacuum

Gokulakannan Somasundaram wrote: Well, one of the principal arguments for having VACUUM at all is that it off-loads required maintenance effort from foreground transaction code paths. I'm not really going to be in favor of solutions that put more work into the transaction code paths (HOT alread

Re: [HACKERS] Postgresql Materialized views

Merlin Moncure wrote: On Jan 12, 2008 4:19 PM, Simon Riggs <[EMAIL PROTECTED]> wrote: On Sat, 2008-01-12 at 13:27 +0100, Jean-Michel Pouré wrote: Please pick-up this important issue for developpers. There is no need to concentrate on complex issues, when handling materialized views could boost

Re: [HACKERS] to_char incompatibility

Am Donnerstag, 10. Januar 2008 schrieb Roberts, Jon: > > On PostgreSQL: > > > > select to_date('31-DEC-200700:00:00', 'dd-mon- hh24:mi:ss'); > >to_date > > -- > > 200700-12-31 > Oracle removes all white spaces in the date you pass in and the date > format. I don't have a stro

Re: [HACKERS] Array behavior oddities

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bruce Momjian wrote: > An array subscript expression will return null if either the array itself > or any of the subscript expressions are null. Also, null is returned > if a subscript is outside the array bounds (this case does not raise an >

Re: [HACKERS] WAL logging of hash indexes

> > There is absolutely 0 value in tackling that until someone can fix > hash's performance problems. If there is no real-world scenario for > using it ... which there really isn't ... then adding WAL support > still leaves you with no real-world scenario for using it. > > This is not to suggest t

Re: [HACKERS] Some ideas about Vacuum

> > > Well, one of the principal arguments for having VACUUM at all is that it > off-loads required maintenance effort from foreground transaction code > paths. I'm not really going to be in favor of solutions that put more > work into the transaction code paths (HOT already did more of that than

Re: [HACKERS] Some ideas about Vacuum

One more application of the same is Asynchronous Materialized views. I hope you agree that the asynchronous materialized views have to get updated only through WAL. If WAL can be used for that purpose, why can't we multiplex it? Thanks, Gokul.

Re: [HACKERS] Some ideas about Vacuum

Hi, Please find my answers inline > > Do you have evidence of that contention being so worse, that it > justifies the additional WAL reading from disk? (Assuming no WAL > archiving). On a broader sense, DSM is a bitmap index with some optimization that has been placed to make the updates more ef