Re: [HACKERS] stats_command_string default?

2003-02-16 Thread Kevin Brown
I wrote: Would it make more sense to enable stats_command_string by default? It could be a problem if doing so would have a significant impact on performance, but that's the only reason I can think of for not doing it. Are there others? and Christopher Kings-Lynne responded: You can

Re: [HACKERS] stats_command_string default?

2003-02-16 Thread Kevin Brown
Tom Lane wrote: Kevin Brown [EMAIL PROTECTED] writes: Would it make more sense to enable stats_command_string by default? I'd vote against it. If we turn it on by default, people are paying for a feature they may not even know exists. Once they find out about it and decide they want it,

[HACKERS] online reindex

2003-02-16 Thread Greg Stark
It sounds to me like it should be fairly straightforward to implement online reindex. That is, reindex without locking the table. This is an important feature for 24x7 operation. Since postgres doesn't modify data in place the only thing required for online reindex is to ensure that the reindex

Re: [HACKERS] Linux.conf.au 2003 Report

2003-02-16 Thread Gavin Sherry
On Sat, 15 Feb 2003, Christopher Kings-Lynne wrote: I think so - Gavin? As far as I'm aware there's not really anything else on the open source circuit. There is often a MySQL rep there as well apparently. Chris is right. David Axmark (MySQL AB) usually turns up, but he didn't this year.

Re: [HACKERS] client_encoding directive is ignored in

2003-02-16 Thread Tatsuo Ishii
Actually the problem can be divided into two parts: 1) backend does not process GUC client_encoding. 2) libpq does not ask the backend's client_encoding, instead it asks datanbase encoding when it starts up the connection. This is just a mistake. I think we could fix 1)

Re: [HACKERS] location of the configuration files

2003-02-16 Thread Peter Eisentraut
Tom Lane writes: AFAICS, you can either set -C to /etc if you want your PG config files loose in /etc, or you can set it to /etc/postgresql/ if you want them in a privately-owned directory. Which other arrangements are needed? People might want to share them between servers, or allow a user

Re: [HACKERS] psql and readline

2003-02-16 Thread Patrick Welche
On Sat, Feb 15, 2003 at 03:10:19PM -0600, Ross J. Reedstrom wrote: On Fri, Feb 14, 2003 at 11:32:02AM -0500, Tom Lane wrote: Patrick Welche [EMAIL PROTECTED] writes: On Thu, Feb 13, 2003 at 10:25:52AM -0500, Tom Lane wrote: Well, is that a bug in your wrapper? Or must we add a configure

Re: [HACKERS] client_encoding directive is ignored in

2003-02-16 Thread Tom Lane
Tatsuo Ishii [EMAIL PROTECTED] writes: Why? No matter how the backend's behavior regarding client_encoding changes, libpq won't be affected by it since 7.2 and 7.3 libpq does not use client_encoding anyway. Doesn't client_encoding determine what encoding the backend sends to the client? It's

Re: [HACKERS] location of the configuration files

2003-02-16 Thread Tom Lane
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: AFAICS, you can either set -C to /etc if you want your PG config files loose in /etc, or you can set it to /etc/postgresql/ if you want them in a privately-owned directory. Which other arrangements are needed? People might want to

Re: [HACKERS] location of the configuration files

2003-02-16 Thread Tom Lane
Lamar Owen [EMAIL PROTECTED] writes: The script's been out there for awhile. It does some things well, and some things not so well. The config files are still coresident with the database, and backup is more difficult than it can be. Meeting all these needs (with configure switches,

Re: [HACKERS] location of the configuration files

2003-02-16 Thread Tom Lane
Kevin Brown [EMAIL PROTECTED] writes: So in your case, what's the advantage of having initdb write anything to a config file, when you're probably also relying on PGDATA or -D to start the database (if you're not, then fair enough. But see below)? Keep in mind that initdb doesn't currently

Re: [HACKERS] stats_command_string default?

2003-02-16 Thread Tom Lane
Kevin Brown [EMAIL PROTECTED] writes: Tom Lane wrote: Kevin Brown [EMAIL PROTECTED] writes: Would it make more sense to enable stats_command_string by default? I'd vote against it. If we turn it on by default, people are paying for a feature they may not even know exists. Once they find

Re: [HACKERS] online reindex

2003-02-16 Thread Tom Lane
Greg Stark [EMAIL PROTECTED] writes: If reindex sets a flag that causes all new inserts and updates to allocate new space at the end of the heap without checking for free space, then a simple linear scan should be guaranteed to catch all the data. Oh? If people are inserting tuples at the

Re: [HACKERS] stats_command_string default?

2003-02-16 Thread Tom Lane
Kevin Brown [EMAIL PROTECTED] writes: It's not unmeasurable, but it is reasonably low (guess it depends on your definition of reasonable :-). I wrote a small perl script which would do a SELECT 1 in a loop as many times as I specified on the command line (autocommit was turned off). I

Re: [HACKERS] psql and readline

2003-02-16 Thread Peter Eisentraut
Ross J. Reedstrom writes: O.K., I found the 'editline' wrapper around 'libedit' that provides a subset of readline functionality, and used that for testing. On my Debian Linux systems, editline installs readline compatability headers (readline.h, history.h) into /usr/include/editline/, so I

Re: [HACKERS] online reindex

2003-02-16 Thread Greg Stark
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: If reindex sets a flag that causes all new inserts and updates to allocate new space at the end of the heap without checking for free space, then a simple linear scan should be guaranteed to catch all the data. Oh?

Re: [HACKERS] stats_command_string default?

2003-02-16 Thread Christopher Kings-Lynne
Averaging over three trials on an unloaded system, I got 21.0 seconds with stats_command_string off, 27.7 with it on, or about 32% overhead. My conclusion is that stats_command_string overhead is non-negligible for simple commands. So I stand by my previous opinion that it should not be

[HACKERS] Hard problem with concurrency

2003-02-16 Thread Christopher Kings-Lynne
OK, this is the problem: I want to write a bit of SQL that if a row exists in a table, then update it, otherwise it will update it. Problem is, there is a very high chance of simultaneous execute of this query on the same row (the rows have a unique index). So, strategy one: begin; update row;

Re: [HACKERS] stats_command_string default?

2003-02-16 Thread Tom Lane
Christopher Kings-Lynne [EMAIL PROTECTED] writes: My conclusion is that stats_command_string overhead is non-negligible for simple commands. So I stand by my previous opinion that it should not be turned on without the DBA taking explicit action to turn it on. How about with the

Re: [HACKERS] Hard problem with concurrency

2003-02-16 Thread greg
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Solution one: use sequences for the unique value. Solution two: use another table to effect the exclusive locking and use it to store the unique values: begin; update row; if (no rows affected) { lock table foo in exclusive mode; find a

Re: [HACKERS] Hard problem with concurrency

2003-02-16 Thread Tom Lane
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I can't try the insert and then the update because the INSERT, in Postgres, will cause an outright transaction failure. Do the update, then try to insert if the update found nothing, and put a retry loop around the whole transaction in case you

Re: [HACKERS] location of the configuration files

2003-02-16 Thread Lamar Owen
On Sunday 16 February 2003 13:15, Tom Lane wrote: Sure. I'm happy to change the software in a way that *allows* moving the config files elsewhere. So we agree. Perfect. But it's not apparent to me why you insist on forcing people who are perfectly happy with their existing configuration

Re: [HACKERS] location of the configuration files

2003-02-16 Thread mlw
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: I would favor a setup that allows a -C *directory* (not file) to be specified as a postmaster parameter separately from the -D directory; A directory is not going to satisfy people. Why not?

Re: [HACKERS] location of the configuration files

2003-02-16 Thread Tom Lane
mlw [EMAIL PROTECTED] writes: The idea of using a directory puts us back to using symlinks to share files. So? If you want to share files, you're probably sharing all three config files and don't need a separate directory at all. This is not a sufficient argument to make me buy into the mess

Re: [HACKERS] Hard problem with concurrency

2003-02-16 Thread Christopher Kings-Lynne
Do the update, then try to insert if the update found nothing, and put a retry loop around the whole transaction in case you fail because of concurrent inserts. Realistically you will need a retry loop in all but the most trivial cases anyway --- certainly so if you want to use serializable

Re: [HACKERS] location of the configuration files

2003-02-16 Thread Kevin Brown
Tom Lane wrote: Keep in mind that initdb doesn't currently *need* to put the datadir location into the config file. It *will* need to do so if we separate config and data dirs. Or at least, *somebody* will need to do so. It's not apparent to me how it simplifies life not to have initdb do

Re: [HACKERS] stats_command_string default?

2003-02-16 Thread Kevin Brown
Tom Lane wrote: Kevin Brown [EMAIL PROTECTED] writes: Tom Lane wrote: Kevin Brown [EMAIL PROTECTED] writes: Would it make more sense to enable stats_command_string by default? I'd vote against it. If we turn it on by default, people are paying for a feature they may not even know

Re: [HACKERS] location of the configuration files

2003-02-16 Thread Kevin Brown
mlw wrote: symlinks suck. Sorry Tom, but they are *BAD* in a production server. Well, at least they're better than hard links. ;-) -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you

Re: [HACKERS] Hard problem with concurrency

2003-02-16 Thread Greg Stark
Hm, odd, nobody mentioned this solution: If you don't have a primary key already, create a unique index on the combination you want to be unique. Then: . Try to insert the record . If you get a duplicate key error then do update instead No possibilities of duplicate records due to race

Re: [HACKERS] Hard problem with concurrency

2003-02-16 Thread Christopher Kings-Lynne
If you don't have a primary key already, create a unique index on the combination you want to be unique. Then: . Try to insert the record . If you get a duplicate key error then do update instead No possibilities of duplicate records due to race conditions. If two people try to

[HACKERS] Questions about indexes?

2003-02-16 Thread Ryan Bradetich
Hello postgres hackers, Been a while since I have participated on this list ... but I have a new itch to scratch Although the table schema is immaterial, I will provide it so we have a common framework for this discussion: host_id integer (not null)

Re: [HACKERS] Questions about indexes?

2003-02-16 Thread Tom Lane
Ryan Bradetich [EMAIL PROTECTED] writes: Although the table schema is immaterial, I will provide it so we have a common framework for this discussion: host_id integer (not null) timestamp datetime(not null) categorytext(not

Re: [HACKERS] location of the configuration files

2003-02-16 Thread mlw
Tom Lane wrote: mlw [EMAIL PROTECTED] writes: The idea of using a directory puts us back to using symlinks to share files. So? If you want to share files, you're probably sharing all three config files and don't need a separate directory at all. This is not a sufficient argument to

Re: [HACKERS] Questions about indexes?

2003-02-16 Thread Ryan Bradetich
On Sun, 2003-02-16 at 23:34, Tom Lane wrote: Ryan Bradetich [EMAIL PROTECTED] writes: Although the table schema is immaterial, I will provide it so we have a common framework for this discussion: host_id integer (not null) timestamp datetime(not

Re: [HACKERS] Questions about indexes?

2003-02-16 Thread Tom Lane
Ryan Bradetich [EMAIL PROTECTED] writes: On Sun, 2003-02-16 at 23:34, Tom Lane wrote: It's not real clear to me why you bother enforcing a constraint that the complete row be unique. Wouldn't a useful constraint be that the first three columns be unique? The table holds system policy

Re: [HACKERS] Questions about indexes?

2003-02-16 Thread Ryan Bradetich
On Mon, 2003-02-17 at 00:15, Tom Lane wrote: Ryan Bradetich [EMAIL PROTECTED] writes: On Sun, 2003-02-16 at 23:34, Tom Lane wrote: It's not real clear to me why you bother enforcing a constraint that the complete row be unique. Wouldn't a useful constraint be that the first three columns