Re: [GENERAL] Decreasing WAL size effects

[Kyle Cordes]

Jason Long wrote:

Sure I would rather have synchronous WAL shipping, but  if that is going 
to be a while or synchronous would slow down my applicaton I  can get 
comfortably close enough for my purposes with some highly compressible 
WALs.


I'm way out here on the outskirts (just a user with a small pile of 
servers running PG)... I would also find any improvements in WAL 
shipping helpful, between now and when continuous streaming is ready.



--
Kyle Cordes
http://kylecordes.com

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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Tom Lane]

"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> Sure, bash Microsoft it's easy.   But it doesn't address the point, is
> a database safe on top of a compressed file system and if not, why?

It is certainly *less* safe than it is on top of an uncompressed
filesystem.  Any given hardware failure will affect more stored bits
(if the compression is effective) in a less predictable way.

If you assume that hardware failure rates are below your level of
concern, this doesn't matter.  But DBAs are paid to be paranoid.

regards, tom lane

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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Scott Marlowe]

On Thu, Oct 30, 2008 at 7:37 PM, Alvaro Herrera
<[EMAIL PROTECTED]> wrote:
> Scott Marlowe escribió:
>
>> What is the torn page problem?  Note I'm no big fan of compressed file
>> systems, but I can't imagine them not working with databases, as I've
>> seen them work quite reliably under exhange server running a db
>> oriented storage subsystem.  And I can't imagine them not being
>> invisible to an application, otherwise you'd just be asking for
>> trouble.
>
> Exchange, isn't that the thing that's very prone to corrupted databases?
> I've heard lots of horror stories about that (and also about how you
> have to defragment the database once in a while, so what kind of
> database it really is?)

Sure, bash Microsoft it's easy.   But it doesn't address the point, is
a database safe on top of a compressed file system and if not, why?

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

Re: [GENERAL] Decreasing WAL size effects

[Jason Long]

Greg Smith wrote:

On Thu, 30 Oct 2008, Tom Lane wrote:


The real reason not to put that functionality into core (or even
contrib) is that it's a stopgap kluge.  What the people who want this
functionality *really* want is continuous (streaming) log-shipping, not
WAL-segment-at-a-time shipping.


Sure, and that's why I didn't care when this got kicked out of the 
March CommitFest; was hoping a better one would show up.  But if 8.4 
isn't going out the door with the feature people really want, it would 
be nice to at least make the stopgap kludge more easily available.

+1
Sure I would rather have synchronous WAL shipping, but  if that is going 
to be a while or synchronous would slow down my applicaton I  can get 
comfortably close enough for my purposes with some highly compressible WALs.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD




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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Alvaro Herrera]

Scott Marlowe escribió:

> What is the torn page problem?  Note I'm no big fan of compressed file
> systems, but I can't imagine them not working with databases, as I've
> seen them work quite reliably under exhange server running a db
> oriented storage subsystem.  And I can't imagine them not being
> invisible to an application, otherwise you'd just be asking for
> trouble.

Exchange, isn't that the thing that's very prone to corrupted databases?
I've heard lots of horror stories about that (and also about how you
have to defragment the database once in a while, so what kind of
database it really is?)

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [GENERAL] Decreasing WAL size effects

[Greg Smith]

On Thu, 30 Oct 2008, Tom Lane wrote:


The real reason not to put that functionality into core (or even
contrib) is that it's a stopgap kluge.  What the people who want this
functionality *really* want is continuous (streaming) log-shipping, not
WAL-segment-at-a-time shipping.


Sure, and that's why I didn't care when this got kicked out of the March 
CommitFest; was hoping a better one would show up.  But if 8.4 isn't going 
out the door with the feature people really want, it would be nice to at 
least make the stopgap kludge more easily available.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Scott Marlowe]

On Thu, Oct 30, 2008 at 6:03 PM, Gregory Stark <[EMAIL PROTECTED]> wrote:
> "Scott Marlowe" <[EMAIL PROTECTED]> writes:
>> Sounds kinda hand wavy to me.  If compressed file systems didn't give
>> you back what you gave them I couldn't imagine them being around for
>> very long.
>
> I don't know, NFS has lasted quite a while.
>
> So you tell me, I write 512 bytes of data to a compressed filesystem, how does
> it handle the torn page problem? Is it going to have to WAL log all data
> operations again?

What is the torn page problem?  Note I'm no big fan of compressed file
systems, but I can't imagine them not working with databases, as I've
seen them work quite reliably under exhange server running a db
oriented storage subsystem.  And I can't imagine them not being
invisible to an application, otherwise you'd just be asking for
trouble.

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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Gregory Stark]

"Scott Marlowe" <[EMAIL PROTECTED]> writes:

> On Thu, Oct 30, 2008 at 4:41 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>> "Scott Marlowe" <[EMAIL PROTECTED]> writes:
>>> On Thu, Oct 30, 2008 at 4:01 PM, Gregory Stark <[EMAIL PROTECTED]> wrote:
 I can't really see trusting Postgres on a filesystem that felt free to
 compress portions of it. Would the filesystem still be able to guarantee 
 that
 torn pages won't "tear" across adjacent blocks? What about torn pages that
 included hint bits being set?
>>
>>> I can't see PostgreSQL noticing it. PostgreSQL hands the OS a 512byte
>>> block, the OS compresses it and it's brethren as the go to disk,
>>> uncompresses as they come out, and as long as what you put in is what
>>> you get back it shouldn't really matter.
>>
>> I think Greg's issue is exactly about what guarantees you'll have left
>> after the data that comes back fails to be the data that went in.
>
> Sounds kinda hand wavy to me.  If compressed file systems didn't give
> you back what you gave them I couldn't imagine them being around for
> very long.

I don't know, NFS has lasted quite a while.

So you tell me, I write 512 bytes of data to a compressed filesystem, how does
it handle the torn page problem? Is it going to have to WAL log all data
operations again?

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

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

Re: [GENERAL] autovacuum

[Matthew T. O'Connor]

Noah Freire wrote:
On Wed, Oct 29, 2008 at 4:46 PM, Matthew T. O'Connor <[EMAIL PROTECTED] 
> wrote:
 


Is the table being excluded? (see the pg_autovacuum system table
settings)

 
there's an entry for this table on pg_autovacuum, and it's enabled.
 


  Are you sure that it's not getting processed? Perhaps one worker
is / has been churning on this table for a  *LONG* time (that is a
fairly big table). 

 
Right. I was wrong :-) the table is being processed by autovacuum (I 
checked via pg_stat_activity). However, as you pinpointed, it's 
already running for hours (the test workload ended hours ago, 
basically it is just this autovacuum worker running on the system). 
 
Is there a way to make a more aggressive autovacuum setting for this 
table? it does not matter if it will affect performance, my concern is 
that it finishes as soon as possible. I wonder if a manual vacuum 
wouldn't be faster.



Yes, in the pg_autovacuum table, you can set per-relation vacuum cost 
delay settings etc...



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

Re: [GENERAL] a LEFT JOIN problem

[Thomas]

I have found a trick to fool the system: I use an ORDER BY
response_code 0 ASC LIMIT 1

As unpaid orders receive a response_code > 0, then necessarily the
first record has response_code of 0.

However if more and more orders come into the equation, this means
PgSQL will have to process more records, how is it optimized? I guess
PgSQL will have to find all the records, than order them by
response_code ASC, and then pick the first one.

This is probably not a good practice? Should I go for 2 distinct queries?

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

Re: [GENERAL] psql screen size

[Tom Lane]

Gregory Stark <[EMAIL PROTECTED]> writes:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
>> I can confirm that when the pager is open, psql does not resize
>> properly.  Maybe psql is ignoring signals while the pager is open, or
>> something.

> Hm, system() is documented to ignore SIGINT and SIGQUIT I wonder if it's
> (erroneously?) ignoring SIGWINCH as well.

So far as I can see, psql itself doesn't cache screen dimension info
anyplace --- it does an ioctl(TIOCGWINSZ) every time it wants the
numbers.  So if there is a problem here, it's not our bug; must be
readline's fault.

regards, tom lane

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

Re: [GENERAL] a LEFT JOIN problem

[Thomas]

Ok I get the problem. It is the LIMIT 1 which was misleading me.

If I remove this limit, I get many returned results, some where orders
were paid, some where orders were not paid, therefore the LIMIT1 picks
the first one, and by chance it lands on an unpaid order.

Am I trying to achieve something which is not possible to do? Do I
compulsory need to make 2 queries? One for the order only and one for
the product only?

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

Re: [GENERAL] a LEFT JOIN problem

[Thomas]

Here is the SQL I am working with:
--
SELECT products.*, orders.response_code FROM "products" JOIN items ON
products.id = items.product_id
 LEFT OUTER JOIN orders ON (items.order_id = orders.id AND
 orders.response_code = '0' AND orders.user_id = 2) WHERE (permalink =
E'product-1' AND products.site_id = 1) LIMIT 1
--

An order has been paid when its response_code is 0.

One problem I have is that, if the user has not placed an order for
the product, than I still want the product fields to be returned so I
can't put this condition in a WHERE. If a user has paid an order for
that product, I want all fields from Product to be returned and I want
also the response_code to be returned (it will obviously be 0). But in
the current state of the query, even if there exists a paid order for
the current user_id, then the query misses it and therefore never
returns the response_code.

What's wrong with it?

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

Re: [GENERAL] psql screen size

[Gregory Stark]

>> Alvaro Herrera <[EMAIL PROTECTED]> writes:
>
>> > I can confirm that when the pager is open, psql does not resize
>> > properly.  Maybe psql is ignoring signals while the pager is open, or
>> > something.

Hm, system() is documented to ignore SIGINT and SIGQUIT I wonder if it's
(erroneously?) ignoring SIGWINCH as well.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Scott Marlowe]

On Thu, Oct 30, 2008 at 4:41 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Scott Marlowe" <[EMAIL PROTECTED]> writes:
>> On Thu, Oct 30, 2008 at 4:01 PM, Gregory Stark <[EMAIL PROTECTED]> wrote:
>>> I can't really see trusting Postgres on a filesystem that felt free to
>>> compress portions of it. Would the filesystem still be able to guarantee 
>>> that
>>> torn pages won't "tear" across adjacent blocks? What about torn pages that
>>> included hint bits being set?
>
>> I can't see PostgreSQL noticing it. PostgreSQL hands the OS a 512byte
>> block, the OS compresses it and it's brethren as the go to disk,
>> uncompresses as they come out, and as long as what you put in is what
>> you get back it shouldn't really matter.
>
> I think Greg's issue is exactly about what guarantees you'll have left
> after the data that comes back fails to be the data that went in.

Sounds kinda hand wavy to me.  If compressed file systems didn't give
you back what you gave them I couldn't imagine them being around for
very long.

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

Re: [GENERAL] a LEFT JOIN problem

[Thomas]

For some reason, I now can include the date range search in my ON (...) clause.

However I would like to know if there is a limit to the number of
conditions I can put. It seems that more than 2 conditions misses some
records.

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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Tom Lane]

"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> On Thu, Oct 30, 2008 at 4:01 PM, Gregory Stark <[EMAIL PROTECTED]> wrote:
>> I can't really see trusting Postgres on a filesystem that felt free to
>> compress portions of it. Would the filesystem still be able to guarantee that
>> torn pages won't "tear" across adjacent blocks? What about torn pages that
>> included hint bits being set?

> I can't see PostgreSQL noticing it. PostgreSQL hands the OS a 512byte
> block, the OS compresses it and it's brethren as the go to disk,
> uncompresses as they come out, and as long as what you put in is what
> you get back it shouldn't really matter.

I think Greg's issue is exactly about what guarantees you'll have left
after the data that comes back fails to be the data that went in.

regards, tom lane

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

Re: [GENERAL] Decreasing WAL size effects

[Tom Lane]

Greg Smith <[EMAIL PROTECTED]> writes:
> That pushes the problem of writing a little chunk of code that reads only 
> the right amount of data and doesn't bother compressing the rest onto the 
> person writing the archive command.  Seems to me that leads back towards 
> wanting to bundle a contrib module with a good implementation of that with 
> the software.  The whole tail clearing bit is in the same situation 
> pg_standby was circa 8.2:  the software is available, and it works, but it 
> seems kind of sketchy to those not familiar with the source of the code. 
> Bundling it into the software as a contrib module just makes that problem 
> go away for end-users.

The real reason not to put that functionality into core (or even
contrib) is that it's a stopgap kluge.  What the people who want this
functionality *really* want is continuous (streaming) log-shipping, not
WAL-segment-at-a-time shipping.  Putting functionality like that into
core is infinitely more interesting than putting band-aids on a
segmented approach.

regards, tom lane

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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Scott Marlowe]

On Thu, Oct 30, 2008 at 4:01 PM, Gregory Stark <[EMAIL PROTECTED]> wrote:
> "Scott Marlowe" <[EMAIL PROTECTED]> writes:
>
>> I'm sure this makes for a nice brochure or power point presentation,
>> but in the real world I can't imagine putting that much effort into it
>> when compressed file systems seem the place to be doing this.
>
> I can't really see trusting Postgres on a filesystem that felt free to
> compress portions of it. Would the filesystem still be able to guarantee that
> torn pages won't "tear" across adjacent blocks? What about torn pages that
> included hint bits being set?

I can't see PostgreSQL noticing it. PostgreSQL hands the OS a 512byte
block, the OS compresses it and it's brethren as the go to disk,
uncompresses as they come out, and as long as what you put in is what
you get back it shouldn't really matter.

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

Re: [GENERAL] Decreasing WAL size effects

[Greg Smith]

On Thu, 30 Oct 2008, Gregory Stark wrote:


Wouldn't it be just as good to indicate to the archive command the amount of
real data in the wal file and have it only bother copying up to that point?


That pushes the problem of writing a little chunk of code that reads only 
the right amount of data and doesn't bother compressing the rest onto the 
person writing the archive command.  Seems to me that leads back towards 
wanting to bundle a contrib module with a good implementation of that with 
the software.  The whole tail clearing bit is in the same situation 
pg_standby was circa 8.2:  the software is available, and it works, but it 
seems kind of sketchy to those not familiar with the source of the code. 
Bundling it into the software as a contrib module just makes that problem 
go away for end-users.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

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

Re: [GENERAL] Decreasing WAL size effects

[Kyle Cordes]

Gregory Stark wrote:

Greg Smith <[EMAIL PROTECTED]> writes:

Wouldn't it be just as good to indicate to the archive command the amount of
real data in the wal file and have it only bother copying up to that point? 


That sounds like a great solution to me; ideally it would be done in a 
way that is always on (i.e. no setting, etc.).


On the log-recovery side, PG would need to be willing to accept 
shorter-than-usual segments, if it's not already willing.



--
Kyle Cordes
http://kylecordes.com

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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Tom Lane]

Chris Browne <[EMAIL PROTECTED]> writes:
> [EMAIL PROTECTED] (Tom Lane) writes:
>> We already have the portions of this behavior that seem to me to be
>> likely to be worthwhile (such as NULL elimination and compression of
>> large field values).  Shaving a couple bytes from a bigint doesn't
>> strike me as interesting.

> I expect that there would be value in doing this with the inet type,
> to distinguish between the smaller IPv4 addresses and the larger IPv6
> ones.  We use the inet type (surprise! ;-)) and would benefit from
> having it "usually smaller" (notably since IPv6 addresses are a
> relative rarity, at this point).

Uh ... inet already does that.  Now it's true you could save a byte or
two more with a bespoke IPv4-only type, but the useful lifespan of such a
type probably isn't very long.

regards, tom lane

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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Gregory Stark]

"Scott Marlowe" <[EMAIL PROTECTED]> writes:

> I'm sure this makes for a nice brochure or power point presentation,
> but in the real world I can't imagine putting that much effort into it
> when compressed file systems seem the place to be doing this.

I can't really see trusting Postgres on a filesystem that felt free to
compress portions of it. Would the filesystem still be able to guarantee that
torn pages won't "tear" across adjacent blocks? What about torn pages that
included hint bits being set?

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

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

Re: [GENERAL] Decreasing WAL size effects

[Christophe]

On Oct 30, 2008, at 2:54 PM, Gregory Stark wrote:
Wouldn't it be just as good to indicate to the archive command the  
amount of
real data in the wal file and have it only bother copying up to  
that point?


Hm!  Interesting question: Can the WAL files be truncated, rather  
than zeroed, safely?


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

Re: [GENERAL] Schema Upgrade Howto

[Berend Tober]

Thomas Guettler wrote:

Hi,

is there a schema upgrade howto? I could not find much with google.

There is a running DB and a development DB. The development DB
has some tables, columns and indexes added. What is the preferred way
to upgrade?

I see these solutions:
 - pg_dump production DB. Install schema only from dev DB, restore data
only from dump.
 - Use alter table.
 - Use a tool like apgdiff (never tried it).

I guess all ways will be possible. But what do you suggest?


Use three instances of the database: development, quality 
assurance testing, and production. DEV and QAT are occasionally 
refreshed from a pg_dump of PRD. Developers work against DEV for 
modify-compile-test-(doh, I broke it)-refix-compile-test cycles. 
All structural or development-related changes required to the 
data base are done with a SQL text file script. The script files 
are managed along with the source code in SVN. When developers 
are satisfied, the script is applied to QAT and then end-users 
test the modified application against QAT. When end-users sign 
off that they are satisfied, the same (*unmodifed from as run 
against QAT*) script is run on PRD at the same time the same 
(*unmodifed from as run against QAT*) application is deployed for 
production use.




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

Re: [GENERAL] Decreasing WAL size effects

[Gregory Stark]

Greg Smith <[EMAIL PROTECTED]> writes:

> Now, it would be possible to have that less sensitive archive code path zero
> things out, but you'd need to introduce a way to note when it's been done (so
> you don't do it for a segment twice) and a way to turn it off so everybody
> doesn't go through that overhead (which probably means another GUC).  That's a
> bit much trouble to go through just for a feature with a fairly limited
> use-case that can easily live outside of the engine altogether.

Wouldn't it be just as good to indicate to the archive command the amount of
real data in the wal file and have it only bother copying up to that point? 

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

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

Re: [GENERAL] speed up restore from dump

[Alan Hodgson]

On Thursday 30 October 2008, Joao Ferreira <[EMAIL PROTECTED]> 
wrote:
> well. see for yourself... (360 RAM , 524 SWAP) that's what it is...
> it supposed to be somewhat an embedded product...
>

Clearly your hardware is your speed limitation. If you're swapping at all, 
anything running on the machine is going to be slow.

-- 
Alan

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

Re: [GENERAL] Decreasing WAL size effects

[Kyle Cordes]

Greg Smith wrote:

On Thu, 30 Oct 2008, Kyle Cordes wrote:

It sure would be nice if there was a way for PG itself to zero the 
unused portion of logs as they are completed, perhaps this will make 



The overhead of clearing out the whole thing is just large enough that 
it can be disruptive on systems generating lots of WAL traffic, so you 


Hmm.  My understanding is that it wouldn't need to clear out the whole 
thing, just the unused portion at the end. This wouldn't add any 
initialize effort at startup / segment creation at all, right?  The 
unused portions at the end only happen when a WAL segment needs to be 
finished "early" for some reason.  I'd expect in a heavily loaded 
system, that PG would be filling each segment, not ending them early.


However, there could easily be some reason that I am not familiar with, 
that would cause a busy PG to nonetheless end a lot of segments early.


--
Kyle Cordes
http://kylecordes.com

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

Re: [GENERAL] Decreasing WAL size effects

[Greg Smith]

On Thu, 30 Oct 2008, Kyle Cordes wrote:

It sure would be nice if there was a way for PG itself to zero the unused 
portion of logs as they are completed, perhaps this will make it in as part 
of the ideas discussed on this list a while back to make a more "out of the 
box" log-ship mechanism?


The overhead of clearing out the whole thing is just large enough that it 
can be disruptive on systems generating lots of WAL traffic, so you don't 
want the main database processes bothering with that.  A related fact is 
that there is a noticable slowdown to clients that need a segment switch 
on a newly initialized and fast system that has to create all its WAL 
segments, compared to one that has been active long enough to only be 
recycling them.  That's why this sort of thing has been getting pushed 
into the archive_command path; nothing performance-sensitive that can slow 
down clients is happening there, so long as your server is powerful enough 
to handle that in parallel with everything else going on.


Now, it would be possible to have that less sensitive archive code path 
zero things out, but you'd need to introduce a way to note when it's been 
done (so you don't do it for a segment twice) and a way to turn it off so 
everybody doesn't go through that overhead (which probably means another 
GUC).  That's a bit much trouble to go through just for a feature with a 
fairly limited use-case that can easily live outside of the engine 
altogether.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

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

Re: [GENERAL] Decreasing WAL size effects

[Jason Long]

Kyle Cordes wrote:

Greg Smith wrote:

there's no chance it can accidentally look like a valid segment.  But 
when an existing segment is recycled, it gets a new header and that's 
it--the rest of the 16MB is still left behind from whatever was in 
that segment before.  That means that even if you only write, say, 
1MB of new 


[...]

What clearxlogtail does is look inside the WAL segment, and it clears 
the "tail" behind the portion of that is really used.  So our example 
file would end up with just the 1MB of useful data, followed by 15MB of 



It sure would be nice if there was a way for PG itself to zero the 
unused portion of logs as they are completed, perhaps this will make 
it in as part of the ideas discussed on this list a while back to make 
a more "out of the box" log-ship mechanism?
*I agree totally.  I looked at the code for clearxlogtail and it seems 
short and not very complex.  Hopefully something like this will at least 
be a trivial to set up option in 8.4.**

*

Re: [GENERAL] Decreasing WAL size effects

[Kyle Cordes]

Greg Smith wrote:

there's no chance it can accidentally look like a valid segment.  But 
when an existing segment is recycled, it gets a new header and that's 
it--the rest of the 16MB is still left behind from whatever was in that 
segment before.  That means that even if you only write, say, 1MB of new 


[...]

What clearxlogtail does is look inside the WAL segment, and it clears 
the "tail" behind the portion of that is really used.  So our example 
file would end up with just the 1MB of useful data, followed by 15MB of 



It sure would be nice if there was a way for PG itself to zero the 
unused portion of logs as they are completed, perhaps this will make it 
in as part of the ideas discussed on this list a while back to make a 
more "out of the box" log-ship mechanism?



--
Kyle Cordes
http://kylecordes.com

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

Re: [GENERAL] speed up restore from dump

[Alvaro Herrera]

Alan Hodgson wrote:
> On Thursday 30 October 2008, Joao Ferreira gmail 
> > During restore:
> > # vmstat
> > procs memory--- ---swap-- -io -system-- cpu
> > r  b   swpd   free   buff  cache   si  so   bi   bo  in  cs us sy id wa
> > 3  1 230204   4972   1352 110128   21   17   63  24  56 12  2 85  0
> > #
> 
> Does that machine really have only 256MB of RAM? And it's over 200MB into 
> swap? 

Huh, if that's the case then you should drop maintenance_work_mem a lot
(and not increase work_mem too much either), because having it high
enough that it causes swapping leads to worse performance.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [GENERAL] array_cat null reasoning

[Tom Lane]

Kev <[EMAIL PROTECTED]> writes:
> ... should I be careful how to code because this
> might change in the future?

Probably.  We couldn't even handle nulls within arrays until a release
or two ago.  I wouldn't be surprised if someone comes up with a proposal
to make null-array handling a bit more consistent in the future.

regards, tom lane

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

Re: [GENERAL] speed up restore from dump

[Alan Hodgson]

On Thursday 30 October 2008, Joao Ferreira gmail 
> During restore:
> # vmstat
> procs memory--- ---swap-- -io -system-- cpu
> r  b   swpd   free   buff  cache   si  so   bi   bo  in  cs us sy id wa
> 3  1 230204   4972   1352 110128   21   17   63  24  56 12  2 85  0
> #

Does that machine really have only 256MB of RAM? And it's over 200MB into 
swap? 


-- 
Alan

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

Re: [GENERAL] speed up restore from dump

[Sam Mason]

On Thu, Oct 30, 2008 at 07:28:57PM +, Joao Ferreira gmail wrote:
> On Thu, 2008-10-30 at 11:39 -0700, Alan Hodgson wrote: 
> > You're probably just 
> > disk-bound, though. What does vmstat say during the restore?
> 
> During restore:
> # vmstat
> procs memory--- ---swap-- -io -system-- cpu
> r  b   swpd   free   buff  cache   si  so   bi   bo  in  cs us sy id wa
> 3  1 230204   4972   1352 110128   21   17   63  24  56 12  2 85  0
> 
> 
> After restore has finished
> # vmstat
> procs memory ---swap-- ---io -system-- cpu
> r  b   swpd   free   buff  cache   si  so   bi   bo   in   cs us sy id wa
> 1  0 246864  59972   2276 186420   2   118   63   28   56 12  2 85  0

>From the output you've given it doesn't look as though you left vmstat
running while the processing is running, the first set of numbers it
prints out are rarely representational values for the IO usage.  Try
running "vmstat 5" to get output every 5 seconds, you should be able
to see things happening a bit more easily that way.  Another tool I'd
recommend is iostat, I tend to invoke it as "iostat -mx 5 /dev/sd?" to
get it to print out values for each individual disk.


  Sam

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

[GENERAL] array_cat null reasoning

[Kev]

Hi,

I'm a bit confused as to the logic of nulls.  I understand that null
is to represent an unknown value.  So it makes sense that the result
of tacking an unknown value onto a known one is unknown, because you
don't know what exactly you just tacked on.  So


   select null::text || 'hello';


...returning NULL makes sense.  But then why doesn't


   select array_cat(null::integer[], '{3}'::integer[]);


...also return null?  Somehow it's known what the result is when
combining an unknown array with a known one, but not when combining an
unknown text with a known one?  Doesn't this seem inconsistent?  If it
does seem inconsistent, should I be careful how to code because this
might change in the future?

Thanks,
Kev

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

Re: [GENERAL] Need schema design advice

[Lew]

Martin Gainty wrote:

could you provide a brief explanation of EAV ?


Please avoid HTML and eschew top-posting.  The post from Jeff Soules in this 
thread included the advice:

See e.g. http://en.wikipedia.org/wiki/Entity-Attribute-Value_model


which points to an explanation.

--
Lew

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

Re: [GENERAL] Decreasing WAL size effects

[Jason Long]

Greg Smith wrote:

On Thu, 30 Oct 2008, Joshua D. Drake wrote:

This reminds me yet again that pg_clearxlogtail should probably get 
added
to the next commitfest for inclusion into 8.4; it's really essential 
for a

WAN-based PITR setup and it would be nice to include it with the
distribution.


What is to be gained over just using rsync with -z?


When a new XLOG segment is created, it gets zeroed out first, so that 
there's no chance it can accidentally look like a valid segment.  But 
when an existing segment is recycled, it gets a new header and that's 
it--the rest of the 16MB is still left behind from whatever was in 
that segment before.  That means that even if you only write, say, 1MB 
of new data to a recycled segment before a timeout that causes you to 
ship it somewhere else, there will still be a full 15MB worth of junk 
from its previous life which may or may not be easy to compress.


I just noticed that recently this project has been pushed into 
pgfoundry, it's at 
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/clearxlogtail/clearxlogtail/


What clearxlogtail does is look inside the WAL segment, and it clears 
the "tail" behind the portion of that is really used.  So our example 
file would end up with just the 1MB of useful data, followed by 15MB 
of zeros that will compress massively.  Since it needs to know how 
XLogPageHeader is formatted and if it makes a mistake your archive 
history will be silently corrupted, it's kind of a scary utility to 
just download and use.

I would really like to add something like this to my application.
1.  Should I be scared or is it just scary in general?
2.  Is this safe to use with 8.3.4?
3.  Any pointers on how to install and configure this?
That's why I'd like to see it turn into a more official contrib 
module, so that it will never lose sync with the page header format 
and be available to anyone using PITR.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD



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

Re: [GENERAL] speed up restore from dump

[Joao Ferreira gmail]

On Thu, 2008-10-30 at 11:39 -0700, Alan Hodgson wrote: 
> On Thursday 30 October 2008, Joao Ferreira gmail 
> <[EMAIL PROTECTED]> wrote:
> > What other cfg paramenters shoud I touch ?
> 
> work_mem set to most of your free memory might help. 

I've raised work_mem to 128MB.

still get the same 20 minutes !

> You're probably just 
> disk-bound, though. What does vmstat say during the restore?

During restore:
# vmstat
procs memory--- ---swap-- -io -system-- cpu
r  b   swpd   free   buff  cache   si  so   bi   bo  in  cs us sy id wa
3  1 230204   4972   1352 110128   21   17   63  24  56 12  2 85  0
# 


After restore has finished
# vmstat
procs memory ---swap-- ---io -system-- cpu
r  b   swpd   free   buff  cache   si  so   bi   bo   in   cs us sy id wa
1  0 246864  59972   2276 186420   2   118   63   28   56 12  2 85  0
# 

joao

> 
> -- 
> Alan
> 


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

Re: [GENERAL] bytea field, a c function and pgcrypto driving me mad

[Glyn Astill]

> ISTM that in this line:
> 
> keying = (text *)palloc( keylen + unamelen );
> 
> You forgot to include the length of the header VARHDRSZ.
> 

Aha, that'd be it, it's been a long day.

Thanks Martijn




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

Re: [GENERAL] Decreasing WAL size effects

[Greg Smith]

On Thu, 30 Oct 2008, Joshua D. Drake wrote:


This reminds me yet again that pg_clearxlogtail should probably get added
to the next commitfest for inclusion into 8.4; it's really essential for a
WAN-based PITR setup and it would be nice to include it with the
distribution.


What is to be gained over just using rsync with -z?


When a new XLOG segment is created, it gets zeroed out first, so that 
there's no chance it can accidentally look like a valid segment.  But when 
an existing segment is recycled, it gets a new header and that's it--the 
rest of the 16MB is still left behind from whatever was in that segment 
before.  That means that even if you only write, say, 1MB of new data to a 
recycled segment before a timeout that causes you to ship it somewhere 
else, there will still be a full 15MB worth of junk from its previous life 
which may or may not be easy to compress.


I just noticed that recently this project has been pushed into pgfoundry, 
it's at 
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/clearxlogtail/clearxlogtail/


What clearxlogtail does is look inside the WAL segment, and it clears the 
"tail" behind the portion of that is really used.  So our example file 
would end up with just the 1MB of useful data, followed by 15MB of zeros 
that will compress massively.  Since it needs to know how XLogPageHeader 
is formatted and if it makes a mistake your archive history will be 
silently corrupted, it's kind of a scary utility to just download and use. 
That's why I'd like to see it turn into a more official contrib module, so 
that it will never lose sync with the page header format and be available 
to anyone using PITR.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

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

Re: [GENERAL] speed up restore from dump

[Alan Hodgson]

On Thursday 30 October 2008, Joao Ferreira gmail 
<[EMAIL PROTECTED]> wrote:
> What other cfg paramenters shoud I touch ?

work_mem set to most of your free memory might help. You're probably just 
disk-bound, though. What does vmstat say during the restore?

-- 
Alan

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

[GENERAL] speed up restore from dump

[Joao Ferreira gmail]

Hello all,

I've been tring to speed up the restore operation of my database without
success.

I have a 200MB dump file obtained with 'pg_dumpall --clean --oids'.
After restore is produces a database with one single table (1.000.000)
rows. I have also some indexes on that table. that's it.

It always takes me about 20 minutes to reload the data, whatever
settings I change.

I have so far touched these settings:
- fsync = off
- shared_buffers = 24MB
- temp_buffers = 24Mb
- maintenance_work_mem = 128MB
- full_page_writes = off
- wal_writer_delay = 1
- checkpoint_segments = 200
- checkpoint_timeout = 1800
- autovacuum = off

I started with a default instalation. first I changed fsync to off, then
I started touching other cfg params. but I always get around 20 minutes
(21, 19, 18)

Can I expect these 20 minutes to be significantly reduced ?

What other cfg paramenters shoud I touch ?

Can anyone shed some light on this ?

any faster approach to upgrade from 8.1 to 8.3 ?

thank you

Joao



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

Re: [GENERAL] bytea field, a c function and pgcrypto driving me mad

[Martijn van Oosterhout]

On Thu, Oct 30, 2008 at 05:27:58PM +, Glyn Astill wrote:
> Hi chaps,
> 
> I think I'm going to struggle to describe this, but hopefully someone can 
> squint and see where I'm going wrong.
> 
> I've got a c function called "ftest", all it does is take some text and 
> prepend "abcdefghijklmnopqr" onto it. I use it to pass a key into 
> pgp_sym_encrypt/decrypt working on a bytea field in a table. The problem is 
> that once the string I pass to "ftest" is longer than 10 characters it stops 
> working when I use it with the bytea column and pgp_sym_decrypt, but it 
> appears to work fine on it's own.
> 
> 1) The source is here:
> 
> http://privatepaste.com/890Bj3FGW0

ISTM that in this line:

keying = (text *)palloc( keylen + unamelen );

You forgot to include the length of the header VARHDRSZ.

Have  anice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Chris Browne]

[EMAIL PROTECTED] (Tom Lane) writes:
> We already have the portions of this behavior that seem to me to be
> likely to be worthwhile (such as NULL elimination and compression of
> large field values).  Shaving a couple bytes from a bigint doesn't
> strike me as interesting.

I expect that there would be value in doing this with the inet type,
to distinguish between the smaller IPv4 addresses and the larger IPv6
ones.  We use the inet type (surprise! ;-)) and would benefit from
having it "usually smaller" (notably since IPv6 addresses are a
relative rarity, at this point).

That doesn't contradict you; just points out one of the cases where
there might be some value in *a* form of compression...

(Of course, this may already be done; I'm not remembering just now...)
-- 
output = ("cbbrowne" "@" "cbbrowne.com")
http://cbbrowne.com/info/nonrdbms.html
Fatal Error: Found [MS-Windows] System -> Repartitioning Disk for
Linux...  
-- <[EMAIL PROTECTED]> Christopher Browne

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

Re: [GENERAL] Group BY and Chart of Accounts

[WaGathoni]

Works like a charm.  Thank you very much Justin.

On Thu, Oct 30, 2008 at 3:49 AM, justin <[EMAIL PROTECTED]> wrote:
> There was a number of code mistakes  in my examples as i was just doing it
> off the top of my head,  just went through it and got it all working.
> I had to change the function around as it was double dipping accounts
> just run this and it does work.
> --
>
> Create table coa (
>   coa_id serial not null,
>   parent_id int not null default 0,
>   doIhaveChildren boolean default false,
>   account_name text null );
>
>
> Create Table general_ledger_transactions(
>   transaction_id serial not null,
>   coa_id integer,
>   accounting_period integer,
>   debit numeric(20,10) ,
>   credit numeric(20,10),
>   transaction_date timestamp);
>
>
> Create table  accounting_periods (
>   accounting_period serial not null,
>   start_date date,
>   end_date date,
>   accounting_period_Open boolean);
>
> Insert into coa values (10, default, True, 'ParentAccount1');
> Insert into coa values (11, 10, True, 'ChildAccount1');
> Insert into coa values (12, 11, false, 'ChildAccount2');
> Insert into coa values (13, default, false, 'ChildAccount3');
>
> Insert into Accounting_Periods values ( 1, '2008-10-01', '2008-10-31', true
> );
> Insert into Accounting_Periods values ( 2, '2008-11-01', '2008-11-30', true
> );
>
> Insert into general_ledger_transactions values(  default, 11,  1, 30.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 11,  1, 20.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 12,  1, 10.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 12,  1, 50.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 11,  1, 1.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 13,  1, 0.0,
> 111.0, current_timestamp);
>
>
> Insert into general_ledger_transactions values(  default, 11,  2, 0.0, 30.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 11,  2, 0.0, 20.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 12,  2, 0.0, 10.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 12,  2, 0.0, 50.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 11,  2, 0.0, 1.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 13,  2, 111.0,
> 0.0, current_timestamp);
>
>
>
> CREATE OR REPLACE  FUNCTION GetChildAccountDebits(PassedAccountID
> integer, PassedPeriodID integer) RETURNS NUMERIC AS
> $FunctionCode$
> DECLARE
>   retval NUMERIC = 0.0 ;
> begin
> return (SELECT
>   coalesce ( (select Sum(general_ledger_transactions.debit ) from
> general_ledger_transactions where general_ledger_transactions.coa_id =
> coa.coa_id and general_ledger_transactions.accounting_period =
> PassedPeriodID), 0 ) +
>   (CASE WHEN coa.doIhaveChildren THEN
>   GetChildAccountDebits(coa.coa_id, PassedPeriodID )
>   ELSE
>  0.0
>   END)
>   FROM coa
>  WHERE  coa.parent_id = PassedAccountID);
>  end;
> $FunctionCode$
> LANGUAGE 'plpgsql' VOLATILE ;
>
> select 10, getchildaccountdebits(10,1)
> union
> select 11, getchildaccountdebits(11,1)
> union
> select 12, getchildaccountdebits(12,1);
>
>
> --
>
> WaGathoni wrote:
>>
>> Justin was recommending a solution to the Chart of Accounts Problem
>> posted by jamhitz:
>>
>> MQUOTE>
>> One has you chart of Accounts
>>   Create table coa (
>>  coa_id serial not null,
>>  parent_id int not null default 0,
>>  doIhaveChildren boolean default false
>>   account_name text null )
>> primary key(coa_id)
>>
>> Create Table general_ledger_transactions(
>>  transaction_id serial not null
>>  coad_id integer,
>>  accounting_period integer,
>>  debit numeric(20,10) ,
>>  credit numeric(20,10),
>>  transaction_date datestamp)
>> primary key (transaction_id)
>>
>> ...
>>
>> Create table  accounting_periods (
>>   accounting_period serial not null,
>>   start_date date,
>>   end_date date,
>>   accounting_period_Open boolean)
>>
>> 
>>
>> Would someone please assist me.  Why is the following function:...
>>
>>
>> CREATE OR REPLACE  FUNCTION GetChildAccountDebits(PassedAccountID
>> integer, PassedPeriodID integer) RETURNS NUMERIC AS
>> $FunctionCode$
>> DECLARE retval NUMERIC :=0.0;
>> begin
>>SELECT Sum(gl_transactions.debit) +
>>CASE WHEN coa.doIhaveChildren THEN
>>GetChildAccountDebits(coa.coa_id, PassedPeriodID )
>>ELSE
>>   0.0
>>END
>>INTO retval
>>FROM gl_transactions, coa
>>WHERE gl_transactions.coa_id= coa.coa_id
>>AND coa.parent

[GENERAL] bytea field, a c function and pgcrypto driving me mad

[Glyn Astill]

Hi chaps,

I think I'm going to struggle to describe this, but hopefully someone can 
squint and see where I'm going wrong.

I've got a c function called "ftest", all it does is take some text and prepend 
"abcdefghijklmnopqr" onto it. I use it to pass a key into 
pgp_sym_encrypt/decrypt working on a bytea field in a table. The problem is 
that once the string I pass to "ftest" is longer than 10 characters it stops 
working when I use it with the bytea column and pgp_sym_decrypt, but it appears 
to work fine on it's own.

1) The source is here:

http://privatepaste.com/890Bj3FGW0

2) I created a little makefile, as follows:

MODULES = testf
PGXS := $(shell pg_config --pgxs)
include $(PGXS)

3) Then I did make, make install and created the function in the database:

CREATE OR REPLACE FUNCTION
  testf( TEXT )
RETURNS
  TEXT
AS
  'testf.so', 'testf'
LANGUAGE
  C
STRICT
IMMUTABLE;

REVOKE ALL ON FUNCTION testf( TEXT ) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION testf( TEXT ) TO admins;

4) I created a table mytest as follows:

CREATE TABLE mytest(
  username TEXT PRIMARY KEY,
  password BYTEA NOT NULL
);

5) Now with a 10 character string passed to ftest this works:

TEST=# insert into mytest (username,password) values ('short_user', 
pgp_sym_encrypt('testword', testf('short_user')));
INSERT 0 1
TEST=# select pgp_sym_decrypt(password, testf('short_user')) from mytest where 
username = 'short_user';
 pgp_sym_decrypt
-
 testword
(1 row)

6) However if the I make the string longer, the decryption fails:

TEST=# insert into mytest (username,password) values ('longer_user', 
pgp_sym_encrypt('testword', testf('longer_user')));
INSERT 0 1
TEST=# select pgp_sym_decrypt(password, testf('longer_user')) from mytest where 
username = 'longer_user';
ERROR:  Wrong key or corrupt data

But the C function appears to be working on it's own:

TEST=# select testf('longer_user');
 testf
---
 abcdefghijklmnopqrlonger_user
(1 row)

7) But, if I insert the data into the table without using my function it works:

TEST=# insert into mytest (username,password) values ('longer_user', 
pgp_sym_encrypt('testword', 'abcdefghijklmnopqrlonger_user'));
INSERT 0 1
TEST=# select pgp_sym_decrypt(password, testf('longer_user')) from mytest where 
username = 'longer_user';
 pgp_sym_decrypt
-
 testword
(1 row)


So it appears that my function is only working in conjunction with 
pgp_sym_encrypt on an insert when the text value I pass into it is less than 10 
characters long.

It's driving me nuts, can anyone see what I'm doing wrong?

Thanks
Glyn




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

Re: [GENERAL] Decreasing WAL size effects

[Joshua D. Drake]

On Wed, 2008-10-29 at 09:05 -0400, Greg Smith wrote:
> On Tue, 28 Oct 2008, Jason Long wrote:
> 
> > I also have to ship them off site using a T1 so setting the time to 
> > automatically switch files will just waste bandwidth if they are still 
> > going 
> > to be 16 MB anyway.
> 
> The best way to handle this is to clear the unused portion of the WAL file 
> and then compress it before sending over the link.  There is a utility 
> named pg_clearxlogtail available at 
> http://www.2ndquadrant.com/replication.htm that handles the first part of 
> that you may find useful here.
> 
> This reminds me yet again that pg_clearxlogtail should probably get added 
> to the next commitfest for inclusion into 8.4; it's really essential for a 
> WAN-based PITR setup and it would be nice to include it with the 
> distribution.

What is to be gained over just using rsync with -z?

Joshua D. Drake

> 
> --
> * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD
> 
-- 


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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Joshua D. Drake]

Grzegorz Jaśkiewicz wrote:



On Thu, Oct 30, 2008 at 3:27 PM, Christophe <[EMAIL PROTECTED] 
> wrote:


I'm a bit surprised to hear that; what would pg be doing, unique to
it, that would cause it to be slower on a RAID-1 cluster than on a
plain drive?

yes, it is slower on mirror-raid from single drive.
I can give you all the /proc/* dumps if you want, as far as computer 
goes, it isn't anything fancy. dual way p4, and sata drives of some sort.


O.k. that doesn't actually surprise me all that much. Software RAID 1 on 
SATA Drives for specific workloads would be slower than a single drive. 
It should still be faster for reads assuming some level of concurrency 
but not likely faster for a single thread. Writes would be expected to 
be slower because you are managing across two spindles, identical writes 
and SATA is slow for that type of thing.


Joshua D. Drake

 

 





--
GJ



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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Grzegorz Jaśkiewicz]

On Thu, Oct 30, 2008 at 3:27 PM, Christophe <[EMAIL PROTECTED]> wrote:

> I'm a bit surprised to hear that; what would pg be doing, unique to it,
> that would cause it to be slower on a RAID-1 cluster than on a plain drive?
>
yes, it is slower on mirror-raid from single drive.
I can give you all the /proc/* dumps if you want, as far as computer goes,
it isn't anything fancy. dual way p4, and sata drives of some sort.


>
>



-- 
GJ

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Joshua D. Drake]

Grzegorz Jaśkiewicz wrote:


What? PostgreSQL is slower on RAID? Care to define that better?

up to 8.3 it was massively slower on raid1 (software raid on linux), 
starting from 8.3 things got lot lot better (we speak 3x speed 
improvement here), but it still isn't same as on 'plain' drive. 



Slower on RAID1 than what and doing what?

Joshua D. Drake

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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Christophe]

On Oct 30, 2008, at 8:10 AM, Grzegorz Jaśkiewicz wrote:
up to 8.3 it was massively slower on raid1 (software raid on  
linux), starting from 8.3 things got lot lot better (we speak 3x  
speed improvement here), but it still isn't same as on 'plain' drive.


I'm a bit surprised to hear that; what would pg be doing, unique to  
it, that would cause it to be slower on a RAID-1 cluster than on a  
plain drive?

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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Grzegorz Jaśkiewicz]

On Thu, Oct 30, 2008 at 2:58 PM, Joshua D. Drake <[EMAIL PROTECTED]>wrote:

> Grzegorz Jaśkiewicz wrote:
>
>> currently postgresql is slower on RAID, so something tells me that little
>> bit of compression underneeth will make it far more worse, than better. But
>> I guess, Tom will be the man to know more about it.
>>
>
> What? PostgreSQL is slower on RAID? Care to define that better?
>
> up to 8.3 it was massively slower on raid1 (software raid on linux),
starting from 8.3 things got lot lot better (we speak 3x speed improvement
here), but it still isn't same as on 'plain' drive.



-- 
GJ

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Joshua D. Drake]

Grzegorz Jaśkiewicz wrote:
currently postgresql is slower on RAID, so something tells me that 
little bit of compression underneeth will make it far more worse, than 
better. But I guess, Tom will be the man to know more about it.


What? PostgreSQL is slower on RAID? Care to define that better?



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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[小波 顾]

Yes, we are in a data warehouse like environments, where the database server is 
used to hold very large volumn of read only historical data, CPU, memory, I/O 
and network are all OK now except storage space, the only goal of compression 
is to reduce storage consumption.
 



> Date: Thu, 30 Oct 2008 10:53:27 +1100> From: [EMAIL PROTECTED]> To: 
> pgsql-general@postgresql.org> Subject: Re: [GENERAL] Are there plans to add 
> data compression feature to postgresql?> > Tom Lane wrote:> > 
> =?utf-8?Q?=E5=B0=8F=E6=B3=A2_=E9=A1=BE?= <[EMAIL PROTECTED]> writes:> > > >> 
> [ snip a lot of marketing for SQL Server ]> >> > >> > I think the part of 
> this you need to pay attention to is> >> > > >> Of course, nothing is 
> entirely free, and this reduction in space and> >> time come at the expense 
> of using CPU cycles.> >> > >> > We already have the portions of this behavior 
> that seem to me to be> > likely to be worthwhile (such as NULL elimination 
> and compression of> > large field values). Shaving a couple bytes from a 
> bigint doesn't> > strike me as interesting.> > Think about it on a fact table 
> for a warehouse. A few bytes per bigint > multiplied by several 
> billions/trillions of bigints (not an exaggeration > in a DW) and you're 
> talking some significant storage savi
 ng on the main > storage hog in a DW. Not to mention the performance 
_improvements_ you > can get, even with some CPU overhead for dynamic 
decompression, if the > planner/optimiser understands how to work with the 
compression index/map > to perform things like range/partition elimination etc. 
Admittedly this > depends heavily on the storage mechanics and optimisation 
techniques of > the DB, but there is value to be had there ... IBM is seeing 
typical > storage savings in the 40-60% range, mostly based on boring, > 
bog-standard int, char and varchar data.> > The IDUG (so DB2 users themselves, 
not IBM's marketing) had a > competition to see what was happening in the real 
world, take a look if > interested: 
http://www.idug.org/wps/portal/idug/compressionchallenge> > Other big benefits 
come with XML ... but that is even more dependent on > the starting point. 
Oracle and SQL Server will see big benefits in > compression with this, because 
their XML technology is so > mind-bogglin
 gly broken in the first place.> > So there's certainly utility in this kind of 
feature ... but whether it > rates above some of the other great stuff in the 
PostgreSQL pipeline is > questionable.> > Ciao> Fuzzy> :-)> > 
> Dazed and confused about 
technology for 20 years> http://fuzzydata.wordpress.com/> > > -- > Sent via 
pgsql-general mailing list (pgsql-general@postgresql.org)> To make changes to 
your subscription:> http://www.postgresql.org/mailpref/pgsql-general
_
News, entertainment and everything you care about at Live.com. Get it now!
http://www.live.com/getstarted.aspx

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Andrew Sullivan]

On Thu, Oct 30, 2008 at 10:53:27AM +1100, Grant Allen wrote:
> Other big benefits come with XML ... but that is even more dependent on the 
> starting point.  Oracle and SQL Server will see big benefits in compression 
> with this, because their XML technology is so mind-bogglingly broken in the 
> first place.

It seems to me that for this use case, you can already get the
interesting compression advantages in Postgres, and have been getting
them since TOAST was introduced back when the 8k row limit was broken.
It's recently been enhanced, ISTR, so that you can SET STORAGE with
better granularity.

Indeed, it seems to me that in some ways, the big databases are only
catching up with Postgres now on this front.  That alone oughta be
news :)


-- 
Andrew Sullivan
[EMAIL PROTECTED]
+1 503 667 4564 x104
http://www.commandprompt.com/

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

Re: [GENERAL] excluding tables from VACUUM ANALYZE

[David Fetter]

On Thu, Oct 30, 2008 at 09:17:00AM -0400, Igor Neyman wrote:
> This question didn't get any "traction on "admin" list, so I'll try
> here: 
> 
> I want to analyze the entire database with the exception of several
> tables.  When I run "VACUUM ANALYZE" (or "vacuumdb -z") on the
> database, how can

Why are you doing this in the first place?  Autovacuum works just
great for modern PostgreSQL versions, and if you're not using one of
those, you should be planning your migration, not propping up the old
one :)

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-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Schema Upgrade Howto

[David Fetter]

On Thu, Oct 30, 2008 at 02:37:43PM +0100, Thomas Guettler wrote:
> Hi,
> 
> I found a way to do it.

It's the wrong way.  Trust me on this.

> One problem remains: The order of the columns can't be changed.  Any
> change to make postgres support this in the future?

It's been proposed several times :)

> My way:
> 
> pg_dump -s prod  | strip-schema-dump.py - > prod.schema
> pg_dump -s devel | strip-schema-dump.py - > devel.schema
> 
> strip-schema-dump.py removes some stuff which I don't care about (Owner, 
> Comments, ...)
> 
> kdiff3 prod.schema devel.schema
> 
> You need to create an upgrade script by looking at the diff. 

No.  Really, no.  You need to create the upgrade script by creating
upgrade scripts, not by reverse engineering.

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-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] excluding tables from VACUUM ANALYZE

[Nikolas Everett]

I generally write bash one liners for this kind of thing:

for table in $(psql -U postgres --tuples-only -c "SELECT schemaname || '.'
|| tablename FROM pg_tables WHERE tablename NOT IN ('table1', 'table2')") ;
do psql -U postgres -c "VACUUM ANALYZE $table"; done

This is nice because you can bring all kinds of awk/sed/grep to bear on your
problems.

On Thu, Oct 30, 2008 at 9:17 AM, Igor Neyman <[EMAIL PROTECTED]> wrote:

>  This question didn't get any "traction on "admin" list, so I'll try
> here:
>
> I want to analyze the entire database with the exception of several
> tables.
>  When I run "VACUUM ANALYZE" (or "vacuumdb -z") on the database, how can I
> exclude specific tables from being analyzed?
> Is there any place in system dictionary, where the table could be marked ,
> so it's not processed (skipped) by "vacuum analyze"?
>
> Igor
>

Re: [GENERAL] Weird problem concerning tsearch functions built into postgres 8.3, assistance requested

[Teodor Sigaev]

One of the tables we're using in the 8.1.3 setups currently running 
includes phone numbers as a searchable field (fti_phone), with the 
results of a select on the field generally looking like this: 'MMM':2 
'':3 'MMM-':1.  MMM is the first three digits,  is the 
fourth-seventh.


The weird part is this: On the old systems running 8.1.3, I can look up 
a record by
fti_phone using any of the three above items; first three, last four, or 
entire number including dash.  On the new system running 8.3.1, I can do 
a lookup by the first three or the last four and get the results I'm 
after, but any attempt to do a lookup by the entire MMM- version 
returns no records.


Parser was changed:
postgres=# select  * from ts_debug('123-4567');
 alias |   description| token | dictionaries | dictionary | lexemes
---+--+---+--++-
 uint  | Unsigned integer | 123   | {simple} | simple | {123}
 int   | Signed integer   | -4567 | {simple} | simple | {-4567}
(2 rows)
postgres=# select  * from ts_debug('abc-defj');
  alias  |   description   |  token   |  dictionaries 
|  dictionary  |  lexemes

-+-+--++--+
 asciihword  | Hyphenated word, all ASCII  | abc-defj | {english_stem} 
| english_stem | {abc-defj}
 hword_asciipart | Hyphenated word part, all ASCII | abc  | {english_stem} 
| english_stem | {abc}
 blank   | Space symbols   | -| {} 
|  |
 hword_asciipart | Hyphenated word part, all ASCII | defj | {english_stem} 
| english_stem | {defj}


Parser in 8.1 threats any [alnum]+-[alnum]+ as a hyphenated word, but 8.3 treats 
[digit]+-[digit]+ as two separated numbers.


So, you can play around pre-process texts before indexing or have a look on
regex dictionary (http://vo.astronet.ru/arxiv/dict_regex.html)
--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

Re: [GENERAL] Schema Upgrade Howto

[Thomas Guettler]

Hi,

I found a way to do it. One problem remains: The order of the columns
can't be changed.
Any change to make postgres support this in the future?

My way:

pg_dump -s prod  | strip-schema-dump.py - > prod.schema
pg_dump -s devel | strip-schema-dump.py - > devel.schema

strip-schema-dump.py removes some stuff which I don't care about (Owner, 
Comments, ...)

kdiff3 prod.schema devel.schema

You need to create an upgrade script by looking at the diff. 
But it is not difficult:

-- update--MM-DD.sql
begin;
alter table ... add column ...;
...
commit;

Execute on production:
cat update--MM-DD.sql | psql 

See http://www.djangosnippets.org/snippets/1160/


David Fetter schrieb:
> On Thu, Oct 30, 2008 at 10:54:46AM +0100, Thomas Guettler wrote:
>   
>> Hi,
>>
>> is there a schema upgrade howto? I could not find much with google.
>>
>> There is a running DB and a development DB. The development DB
>> has some tables, columns and indexes added.
>> 
>
> The only sure way to track such changes is by changing the
> databases--especially in development--only via scripts, all of which
> go into your source code management system.
>
>   


-- 
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de


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

Re: [GENERAL] Piping CSV data to psql when executing COPY .. FROM STDIN (Solved)

[Roberts, Jon]

> I am however unable to do the same successfully (the Java code simply
> hangs, probably as a result of the second psql not getting the input
to
> it) from Java code using objects of ProcessBuilder and Process. I have
> used threads consume the STDOUT and STDERR streams (I write the STDOUT
> stream to file) do the waitFor(), then I read the file contents and
> write them to STDIN stream of the second call to psql.


> I have therefore resorted to password-less ssh. So far all is well. Am
> writing to CSV file which I scp to the remote server then I issue
> another call to psql to connect to the remote server's PostgreSQL and
> execute an sql having a COPY abc FROM ..

Why aren't you using \COPY from psql rather than COPY?  With \COPY, you
can execute the commands from your remote client without having to do
the SSH stuff.  It may run a bit longer but it is easier to maintain and
looks to be more secure.



Jon

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

[GENERAL] excluding tables from VACUUM ANALYZE

[Igor Neyman]

This question didn't get any "traction on "admin" list, so I'll try
here: 

I want to analyze the entire database with the exception of several
tables. 
When I run "VACUUM ANALYZE" (or "vacuumdb -z") on the database, how can
I exclude specific tables from being analyzed?
Is there any place in system dictionary, where the table could be marked
, so it's not processed (skipped) by "vacuum analyze"?
 
Igor 

Re: [GENERAL] Schema Upgrade Howto

[David Fetter]

On Thu, Oct 30, 2008 at 10:54:46AM +0100, Thomas Guettler wrote:
> Hi,
> 
> is there a schema upgrade howto? I could not find much with google.
> 
> There is a running DB and a development DB. The development DB
> has some tables, columns and indexes added.

The only sure way to track such changes is by changing the
databases--especially in development--only via scripts, all of which
go into your source code management system.

> What is the preferred way to upgrade?

Via scripts, all of which go in a transaction.  It's here that
PostgreSQL's transactional DDL (CREATE, ALTER, DROP, for example)
really shines.

> I see these solutions:
>  - pg_dump production DB. Install schema only from dev DB, restore
>  data only from dump.

This won't scale, but may work for now while you institute the
development process outlined above.  Test this very carefully, just as
you would any other database change.

>  - Use alter table.

Yep.  See above for how.

>  - Use a tool like apgdiff (never tried it).

These tools never have enough information to make a decision
guaranteed to be correct, so the whole class of them is bogus.

> I guess all ways will be possible. But what do you suggest?

See above :)

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-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Can't restart Postgres

[Tom Lane]

"Thom Brown" <[EMAIL PROTECTED]> writes:
> The script is actually one which came with the Gentoo package.
> ...
> Okay, so I've manually tried starting the server now and told it to
> output any log to /tmp.  This is telling me that the request for a
> shared memory segment is higher than my kernel's SHMMAX parameter.  My
> bad, I've put my settings in incorrectly, and as it states in the
> config file, changes to that setting require a restart.  I've reset
> all values to back to how they were and it is running again.

Yeah, SHMMAX overrun is a pretty common problem.  You really need to
complain to whoever maintains the Gentoo package that their start script
is so utterly, noisily unhelpful in the presence of a postmaster startup
issue.

regards, tom lane

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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Grzegorz Jaśkiewicz]

currently postgresql is slower on RAID, so something tells me that little
bit of compression underneeth will make it far more worse, than better. But
I guess, Tom will be the man to know more about it.

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Sam Mason]

On Thu, Oct 30, 2008 at 03:50:20PM +1100, Grant Allen wrote:
> One other thing I forgot to mention:  Compression by the DB trumps 
> filesystem compression in one very important area - shared_buffers! (or 
> buffer_cache, bufferpool or whatever your favourite DB calls its working 
> memory for caching data).  Because the data stays compressed in the 
> block/page when cached by the database in one of its buffers, you get 
> more bang for you memory buck in many circumstances!  Just another angle 
> to contemplate :-)

The database research project known as MonetDB/X100 has been looking at
this recently; the first paper below gives a bit of an introduction into
the design of the database and the second into the effects of different
compression schemes:

  http://www.cwi.nl/htbin/ins1/publications?request=pdf&key=ZuBoNeHe:DEBULL:05
  http://www.cwi.nl/htbin/ins1/publications?request=pdf&key=ZuHeNeBo:ICDE:06

The important thing seems to be is that you don't want a storage
efficient compression scheme, decent RAID subsystems demand a very
lightweight scheme that can be decompressed at several GB/s (i.e. two or
three cycles per tuple, not 50 to 100 like traditional schemes like zlib
or bzip).  It's very interesting reading (references to "commercial DBMS
`X'" being somewhat comical), but it's a *long* way from being directly
useful to Postgres.

It's interesting to bear in mind some of the things they talk about when
writing new code, the importance of designing cache conscious algorithms
(and then when writing the code) seem to have stuck in my mind the most.
Am I just old fashioned, or is this focus on cache conscious design
quite a new thing and somewhat undervalued in the rest of the software
world?


  Sam

p.s. if you're interested, there are more papers about MonetDB here:

  
http://monetdb.cwi.nl/projects/monetdb/Development/Research/Articles/index.html

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

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

[Grzegorz Jaśkiewicz]

it should, every book on encryption says, that if you compress your data
before encryption - its better.

Re: [GENERAL] Can't restart Postgres

[Thom Brown]

Well that can't really be the problem since it isn't running when
trying to start.

But yes, I've noticed that before which I actually find very useful.
It's a shame there isn't a way for postgres to broadcast to clients
that it wants to shutdown so things like pgAdmin III will say "Hey,
the server's about to go down.  Do what you need to go and get the
frag outta here!"

On Thu, Oct 30, 2008 at 10:42 AM, Thomas <[EMAIL PROTECTED]> wrote:
> I myself noticed that if a client is still connected to the DB server,
> then PgSQL won't restart. Are you sure all your clients are/were
> disconnected? I myself have the DB on remote a virtual machine.
>

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

Re: [GENERAL] Can't restart Postgres

[Thomas]

I myself noticed that if a client is still connected to the DB server,
then PgSQL won't restart. Are you sure all your clients are/were
disconnected? I myself have the DB on remote a virtual machine.

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

[GENERAL] Schema Upgrade Howto

[Thomas Guettler]

Hi,

is there a schema upgrade howto? I could not find much with google.

There is a running DB and a development DB. The development DB
has some tables, columns and indexes added. What is the preferred way
to upgrade?

I see these solutions:
 - pg_dump production DB. Install schema only from dev DB, restore data
only from dump.
 - Use alter table.
 - Use a tool like apgdiff (never tried it).

I guess all ways will be possible. But what do you suggest?

  Thomas


-- 
Thomas Guettler, http://www.thomas-guettler.de/
E-Mail: guettli (*) thomas-guettler + de


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

Re: [GENERAL] Can't restart Postgres

[Thom Brown]

I think I must have only done a reload on the live server as now I've
tried to restart the service and I've got exactly the same error, so
it's no longer a discrepancy between environments.

The script is actually one which came with the Gentoo package.  I can
see it is using both $PGOPTS and $PGDATA, neither which are populated
with anything on either server.  I've assigned $PGDATA to the database
cluster path but it still doesn't start.  I've also checked
/etc/conf.d/postgresql-8.3 which contains correct settings.

Okay, so I've manually tried starting the server now and told it to
output any log to /tmp.  This is telling me that the request for a
shared memory segment is higher than my kernel's SHMMAX parameter.  My
bad, I've put my settings in incorrectly, and as it states in the
config file, changes to that setting require a restart.  I've reset
all values to back to how they were and it is running again.  I didn't
think my changes were that demanding, but obviously there were.  I'll
have to look into it more.

Thanks for the suggestions.

Thom

On Thu, Oct 30, 2008 at 2:19 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Thom Brown" <[EMAIL PROTECTED]> writes:
>> Actually I did "ps aux | grep post" just to cover all bases, but still
>> nothing.. except of course the grep itself.
>
> The overwhelming impression from here is of a seriously brain-dead
> startup script.  It's spending all its effort on being chatty and none
> on actually dealing with unusual cases correctly :-(.  Whose script
> is it anyway?
>
> My bet is that there's some discrepancy between what the script is
> expecting and what your intended configuration is.  I'm not sure if
> the discrepancy is exactly the PID-file location or if it's more subtle
> than that, but anyway I'd suggest reading through that script carefully
> to see what it's actually doing.
>
>regards, tom lane
>

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

Re: [GENERAL] postgresql and Mac OS X

[Grzegorz Jaśkiewicz]

I feel good about control here, and I certainly don't have any problems. So,
please don't whine :) Especially since I want to run cvs head, and be able
to actually update it from cvs when I want to, that's the only choice.
Postgresql is so easy to get from sources, compared to other software
packages, I can't understand people even with slightest expierence in unix
to have any problems with it.