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
> >>
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 poli
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
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
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)
timestamp
> 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
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 condit
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: yo
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 the
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
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
> 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 serializ
"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
-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 uniq
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
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?
"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 st
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;
> 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 b
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 configuratio
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 dat
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
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).
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
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. O
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 currentl
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,
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
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?
I
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
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 op
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 use
> > 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 f
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.
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 w
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:
> Y
36 matches
Mail list logo
