--- ow [EMAIL PROTECTED] wrote:
IMHO, not only data need to loaded before FK constraints are created but also
there has got to be a feature to allow creation of an FK constraint WITHOUT
doing the verification that all loaded/existing records satisfy the FK
constraint. The ability to create a
Recently I have come across a simple issue which made me think about it.
When we create a tmp table (SELECT INTO, CREATE TABLE AS) the planner
won't know anything about its content after creating it.
Many people use temp tables heavy when the amount of data for a certain
analysis has to be
ow wrote:
It appears there's not a lot of interest in discussing the possibility of FK
constraint creation WITHOUT the verification check. How then should one handle
the situation with pg_restore and large dbs where creation of FK constraint(s)
may take hours?
I'd prefer a backup/restore
Andreas Pflug kirjutas K, 26.11.2003 kell 12:09:
ow wrote:
It appears there's not a lot of interest in discussing the possibility of FK
constraint creation WITHOUT the verification check. How then should one handle
the situation with pg_restore and large dbs where creation of FK
Hannu Krosing wrote:
Andreas Pflug kirjutas K, 26.11.2003 kell 12:09:
ow wrote:
It appears there's not a lot of interest in discussing the possibility of FK
constraint creation WITHOUT the verification check. How then should one handle
the situation with pg_restore and large dbs where
--- Andreas Pflug [EMAIL PROTECTED] wrote:
Yes, I mentioned it just a few days when discussing dependency in pg_dump.
This is somewhat complementary to WAL and PITR. I'm seeking for a fast
way to dump and restore a complete database, like physical file copy,
without shutting down the
ow wrote:
--- Andreas Pflug [EMAIL PROTECTED] wrote:
Yes, I mentioned it just a few days when discussing dependency in pg_dump.
This is somewhat complementary to WAL and PITR. I'm seeking for a fast
way to dump and restore a complete database, like physical file copy,
without shutting down
Thansk Tom,
I had vague memory about that :)
Better check he?
On Tue, 25 Nov 2003, Tom Lane wrote:
Date: Tue, 25 Nov 2003 19:35:07 -0500
From: Tom Lane [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: pgsql-hackers list [EMAIL PROTECTED]
Subject: Re: [HACKERS] 7.4final regression failure on uw713
I know this is an attempt to save myself reading the mailing list, but still
the issue remains:
the psql from version 7.4 does not talk to a 7.2.4 database.
The CHANGELOG indicates, that both server and libraries keep compatibility
with versions after 6.3 - still there is no switch in psql to
Daniel Kalchev writes:
The CHANGELOG indicates, that both server and libraries keep compatibility
with versions after 6.3 - still there is no switch in psql to specify usage of
the pre-7.3 protocol and the 7.2 server and 7.4 psql apparently do not
negotiate.
It's automatic. If you disagree,
Hello
I was asking about this too, one or two weeks ago.
It appears there's not a lot of interest in discussing the
possibility of FK
constraint creation WITHOUT the verification check. How then should
one handle
the situation with pg_restore and large dbs where creation of FK
constraint(s)
Q2: New situation: Why is it not a good idea to backup the database
files of a cluster incl. all c_log and x_log (log files last) to get a
physicaly hot backup.
In principle it is the same situation like a server which is crashing
(not a once but during some time). After restoring, it
Andreas Pflug [EMAIL PROTECTED] writes:
This is somewhat complementary to WAL and PITR. I'm seeking for a fast
way to dump and restore a complete database, like physical file copy,
without shutting down the backend. I was thinking of a BACKUP command
that streams out the files including any
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes:
Recently I have come across a simple issue which made me think about it.
When we create a tmp table (SELECT INTO, CREATE TABLE AS) the planner
won't know anything about its content after creating it.
Run ANALYZE on the temp
Tom Lane wrote:
Shridhar Daithankar [EMAIL PROTECTED] writes:
I was looking thr. the source and thought it would be worth to seek
opinion on this proposal.
This has been discussed and rejected before. See the archives.
I went thr. this for details.
--- Tom Lane [EMAIL PROTECTED] wrote:
This doesn't really replace pg_dump/pg_restore, because it probably
wouldn't be able to upgrade a cluster.
Right, any such physical dump would be limited to restoring a whole
cluster as-is: no imports into other clusters, no selectivity, no fancy
FYI, I've successfully tested on NetBSD Sparc and added it to the
supported list for 7.4.1.
--
Peter Eisentraut [EMAIL PROTECTED]
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Neil == Neil Conway [EMAIL PROTECTED] writes:
Neil It occurred to me that these kinds of poor planning
Neil decisions could easily be detected by PostgreSQL itself:
Neil after we've finished executing a plan, we can trivially
Neil compare the # of results produced by each node in
Neil Conway [EMAIL PROTECTED] writes:
It occurred to me that these kinds of poor planning decisions could
easily be detected by PostgreSQL itself: after we've finished
executing a plan, we can trivially compare the # of results produced
by each node in the query tree with the # of results the
There are no such libraries. I keep hearing ICU, but that is much too
bloated.
At least it is kind of standard and also something what will be
maintained for foreseeable future, it also has a compatible license and
is available on all platforms of interest to postgresql.
And it is used
Shridhar Daithankar [EMAIL PROTECTED] writes:
I covered only first point in my post. IMO it is not such a unsolvable
problem. If a postmaster crashes hard but leaves a backend running,
would it clean pid file etc? I don't think so. So if a postmaster can
start on a 'pid-clean' state, then it
Neil Conway [EMAIL PROTECTED] writes:
It occurred to me that these kinds of poor planning decisions could easily
be detected by PostgreSQL itself: after we've finished executing a plan, we
can trivially compare the # of results produced by each node in the query
tree with the # of results
ow [EMAIL PROTECTED] writes:
--- Tom Lane [EMAIL PROTECTED] wrote:
Right, any such physical dump would be limited to restoring a whole
cluster as-is: no imports into other clusters, no selectivity, no fancy
games.
But that would not help people who would HAVE to use pg_dump/pg_restore (e.g.
On Tue, 25 Nov 2003, Tom Lane wrote:
[EMAIL PROTECTED] writes:
Don't know if it's bad, but make check reports a regression failure on
join.
I believe we'd determined that this is an acceptable platform-specific
behavior.
Yep. Due to the new qsort() in 713UP3.
LER
Tom Lane [EMAIL PROTECTED] writes:
I think such a thing would have such a low signal-to-noise ratio as
to be useless :-(. As you note, there are many places where the
planner's estimate is routinely off by more than 3x (or any other
threshold you might pick instead).
I wonder, perhaps we
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I apologize if this post is inappropriate.
Doing some development work, me and my co-worker discussed some
optimizations strategies. One of the ideas that came up was materialized
views. Trading disk space to summarize queries, and paying for a
--- Tom Lane [EMAIL PROTECTED] wrote:
Quite honestly, I think they should check their foreign keys.
Generally speaking, I agree. The problem is that verification of FK
constraint(s) may take too long, depending on the size of the db and other
conditions. In my case, on test data, it takes about
Building PostgreSQL outside the source tree is slightly broken:
(Using the current CVS HEAD code)
$ mkdir ~/test-pg-build
$ cd ~/test-pg-build
$ ../pgsql/configure
[ output omitted]
$ make
[ output omitted; make succeeds ]
$ touch ../pgsql/configure # i.e. cvs up
$ make
The final make
Jonathan Gardner kirjutas K, 26.11.2003 kell 19:03:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I apologize if this post is inappropriate.
Doing some development work, me and my co-worker discussed some
optimizations strategies. One of the ideas that came up was materialized
views.
ow [EMAIL PROTECTED] writes:
--- Tom Lane [EMAIL PROTECTED] wrote:
Quite honestly, I think they should check their foreign keys.
What should I do if I *know* there will be a FK failure but I want to correct
it manually. Perhaps by creating all the necessary target records, perhaps by
Tom Lane wrote:
Andreas Pflug [EMAIL PROTECTED] writes:
This is somewhat complementary to WAL and PITR. I'm seeking for a fast
way to dump and restore a complete database, like physical file copy,
without shutting down the backend. I was thinking of a BACKUP command
that streams out the
On Wed, Nov 26, 2003 at 11:59:33AM -0500, Neil Conway wrote:
In some situations that doesn't really matter, as the same plan
would have gotten picked anyway.
The hint is NOT the chosen plan was non-optimal; the hint is the
query planner did not produce an accurate row count estimate for
It seems to me that the root cause of some of the optimizer failures that come
is the optimizer's attempt to assign a single expected cost value to every
choice. In fact it seems it should have also a minimum cost and maximum
cost in addition to the expected cost. Often the optimizer is faced
Greg Stark wrote:
If I could disable and reenable the constraint the danger that I would get the
definition wrong would be eliminated. And if I had already done the work to
ensure there were no broken relationships I would optionally be able to skip
the redundant automatic check. I could even
Andreas Pflug [EMAIL PROTECTED] writes:
In principle you could do this today, but we don't have enough
support code in place to make it work smoothly, eg WAL segment files
aren't labeled with enough identifying information to let you manage
an archive full of 'em. Still it doesn't seem that
Tom Lane wrote:
Shridhar Daithankar [EMAIL PROTECTED] writes:
I covered only first point in my post. IMO it is not such a unsolvable
problem. If a postmaster crashes hard but leaves a backend running,
would it clean pid file etc? I don't think so. So if a postmaster can
start on a
Tom Lane wrote:
- how to restore a single database
You don't. As I said, any physical backup is going to be
all-or-nothing. These techniques are not a replacement for pg_dump.
That's sad. I've been backing up and restoring single databases from a
cluster frequently, so I'd really like
On Wed, 26 Nov 2003, ow wrote:
People might be more interested in debating this topic with you if we
hadn't discussed it at length just a couple months back. There wasn't
consensus then that we had to offer an escape hatch, and you've not
offered any argument that wasn't made before.
Hannu Krosing wrote:
You are just shifting the interface problems to a place needing way more
changes in the backend. There will be some problems either way.
Not quite. Certainly, basing internal storage on attstoragenum is more
work in the backend, but less (precisely: zero) work on an
On Thu, 2003-11-20 at 08:34, Rod Taylor wrote:
I think maybe the simplest thing is for me to prepare a patch that rips
that code out and replaces it with a (slightly simpler - less umask
hacking required, I think) piece of code that I will write.
The FreeBSD folks sorted it out for us.
On Tue, Nov 25, 2003 at 04:19:05PM -0500, Tom Lane wrote:
UCS-2 is impractical without some *extremely* wide-ranging changes in
the backend. To take just the most obvious point, doesn't it require
allowing embedded zero bytes in text strings?
If you're going to use unicode in the rest of
On Wed, 26 Nov 2003, Tom Lane wrote:
Quite honestly, I think they should check their foreign keys. In a
partial restore situation there is no guarantee that the referenced
table and the referencing table are being restored at the same time from
the same dump. An override in that situation
Greg Stark [EMAIL PROTECTED] writes:
There's a dual to this as well. If the results were very close but
the actual time taken to run the node doesn't match the cost
calculated then some optimizer parameter needs to be adjusted.
I was thinking about this, but I couldn't think of how to get it
On Wed, 2003-11-26 at 12:43, Andreas Pflug wrote:
Greg Stark wrote:
If I could disable and reenable the constraint the danger that I would get the
definition wrong would be eliminated. And if I had already done the work to
ensure there were no broken relationships I would optionally be able
Neil Conway [EMAIL PROTECTED] writes:
I was thinking about this, but I couldn't think of how to get it to
work properly:
(1) The optimizer's cost metric is somewhat bogus to begin with.
ISTM that translating a cost of X into an expected runtime of
Y msecs is
Greg Stark [EMAIL PROTECTED] writes:
At least for all the possible plans of a given query at a specific
point in time the intention is that the cost be proportional to the
execution time.
Why is this relevant?
Given a cost X at a given point in time, the system needs to derive an
expected
ow [EMAIL PROTECTED] writes:
--- Tom Lane [EMAIL PROTECTED] wrote:
Quite honestly, I think they should check their foreign keys.
Generally speaking, I agree. The problem is that verification of FK
constraint(s) may take too long, depending on the size of the db and other
conditions. In my
Joshua D. Drake [EMAIL PROTECTED] writes:
pg_largeobject uses a loid identifier for the loid. What do we think it
would take to move that identifier to something like bigint?
Breaking all the client-visible LO APIs, for one thing ...
I don't really
know the underlying internals of
Greg Stark [EMAIL PROTECTED] writes:
That's a valid point. The ms/cost factor may not be constant over time.
However I think in the normal case this number will tend towards a fairly
consistent value across queries and over time. It will be influenced somewhat
by things like cache contention
Breaking all the client-visible LO APIs, for one thing ...
Erck.
1. A larger identifier
2. An identifier that is not typed to the underlying system (oid)
3. The ability to be indexed
We may benefit. Am I on crack?
I don't see what you're getting at with #2 and #3 at all.
Joshua D. Drake [EMAIL PROTECTED] writes:
The ability to be indexed is obviously there but one problem we have is
that you can't create an index on a system table at least not a user
level index. Is there system level indexes that I am unaware of?
pg_largeobject already has an index (which is
pg_largeobject already has an index (which is used by all the LO
operations). Again, don't see what the width of the object ID column
has to do with it.
I was more after the not having an OID than the width of the ID column.
regards, tom lane
---(end of
On Wed, 26 Nov 2003, Tom Lane wrote:
ow [EMAIL PROTECTED] writes:
--- Tom Lane [EMAIL PROTECTED] wrote:
Quite honestly, I think they should check their foreign keys.
Generally speaking, I agree. The problem is that verification of FK
constraint(s) may take too long, depending on the
Stephan Szabo wrote:
IIRC, he was. I think the thing causing the difference between his times
and the ones we saw typically when doing the tests was that he didn't have
an index on the fktable's referencing column.
A common mistake, can't count how often I created this one... And not
easy
On further thought the real problem is that these numbers are only available
when running with explain on. As shown recently on one of the lists, the
cost of the repeated gettimeofday calls can be substantial. It's not really
feasible to suggest running all queries with that profiling.
Joshua D. Drake [EMAIL PROTECTED] writes:
I was more after the not having an OID than the width of the ID column.
We're still at cross-purposes then. pg_largeobject doesn't have OIDs
(in the sense of per-row OIDs). What I thought you were complaining
about was the chosen datatype of the LO
Stephan Szabo [EMAIL PROTECTED] writes:
On Wed, 26 Nov 2003, Tom Lane wrote:
If you're seeing this on 7.4, I'd like to see the details of the exact
commands being issued. If it's not 7.4, it's not a relevant
IIRC, he was. I think the thing causing the difference between his times
and the
--- Andreas Pflug [EMAIL PROTECTED] wrote:
Stephan Szabo wrote:
IIRC, he was. I think the thing causing the difference between his times
and the ones we saw typically when doing the tests was that he didn't have
an index on the fktable's referencing column.
A common mistake,
We're still at cross-purposes then. pg_largeobject doesn't have OIDs
(in the sense of per-row OIDs). What I thought you were complaining
about was the chosen datatype of the LO identifier column (loid), which
happens to be OID.
O.k. that was my main concern, which per your statement is
A common mistake, can't count how often I created this one... And not
easy to find, because EXPLAIN won't explain triggers.
I'm planning to create some kind of fk index wizard in pgAdmin3, which
finds out about fks using columns that aren't covered by an appropriate
index. Maybe this check
Hi everyone,
I'm just interested in what everyone's personal plans for 7.5
development are?
Shridar, Gavin and myself are trying to get the tablespaces stuff off
the ground. Hopefully we'll have a CVS set up for us to work in at some
point (we didn't think getting a branch and commit privs
Tom Lane wrote:
Andreas Pflug [EMAIL PROTECTED] writes:
In principle you could do this today, but we don't have enough
support code in place to make it work smoothly, eg WAL segment files
aren't labeled with enough identifying information to let you manage
an archive full of 'em. Still
Gavin Sherry wrote:
On further thought the real problem is that these numbers are only available
when running with explain on. As shown recently on one of the lists, the
cost of the repeated gettimeofday calls can be substantial. It's not really
feasible to suggest running all queries
On Thu, Nov 27, 2003 at 12:40:28AM +0100, Andreas Pflug wrote:
A common mistake, can't count how often I created this one... And not
easy to find, because EXPLAIN won't explain triggers.
That's a pity. And the lack of EXPLAINing function execution, too.
Maybe it's not that hard to do?
--
On Thu, Nov 27, 2003 at 10:27:22AM +0800, Christopher Kings-Lynne wrote:
What's everyone else wanting to work on?
I want to get the nested transaction patch thingie sorted out.
I feel it's not that far away. After that, maybe
- try using a pg_shareddepend shared catalog to check user
Kevin Brown [EMAIL PROTECTED] writes:
Tom Lane wrote:
You don't. As I said, any physical backup is going to be
all-or-nothing. These techniques are not a replacement for pg_dump.
But this is just an artifact of the fact that the WAL is a single
instance-wide entity, rather than a
Alvaro Herrera [EMAIL PROTECTED] writes:
That's a pity. And the lack of EXPLAINing function execution, too.
Maybe it's not that hard to do?
Not sure if it's hard or not, but it'd sure be a nice thing to have.
regards, tom lane
---(end of
Tom Lane wrote:
Kevin Brown [EMAIL PROTECTED] writes:
Tom Lane wrote:
You don't. As I said, any physical backup is going to be
all-or-nothing. These techniques are not a replacement for pg_dump.
But this is just an artifact of the fact that the WAL is a single
instance-wide entity,
On Thu, 27 Nov 2003, Christopher Kings-Lynne wrote:
Changing all our $Id$ tags to $Postgres$ would make a separate CVS a lot
easier, hint hint :)
Why? From the info pages:
`$Header$'
A standard header containing the full pathname of the RCS file,
the revision number, the date
So, what does changing it to $PostgreSQL$ do? Or am I reading the wrong
part of the manual?
After applying the patch in -patches to CVSROOT and running the update
script...
It will allow Chris and other to import the PostgreSQL source into their
own CVS tree without having do to a ton of diff
On Wed, 26 Nov 2003, Rod Taylor wrote:
So, what does changing it to $PostgreSQL$ do? Or am I reading the wrong
part of the manual?
After applying the patch in -patches to CVSROOT and running the update
script...
It will allow Chris and other to import the PostgreSQL source into their
Rod Taylor [EMAIL PROTECTED] writes:
So, what does changing it to $PostgreSQL$ do? Or am I reading the wrong
part of the manual?
The BSDs wen't through similar measures to ensure they could maintain
multiple CVS sources without diff / patch going nuts.
Yeah, I have gotten similar requests
Marc G. Fournier [EMAIL PROTECTED] writes:
'k, but why can't that be accomplished with $Id$?
$Id$ isn't much better than $Header$ --- the point is to avoid keywords
that downstream people's CVS repositories will want to replace.
Are there any caveats to the change? Ie. if Tom has a checkout
On Wed, 2003-11-26 at 23:32, Marc G. Fournier wrote:
On Wed, 26 Nov 2003, Rod Taylor wrote:
So, what does changing it to $PostgreSQL$ do? Or am I reading the wrong
part of the manual?
After applying the patch in -patches to CVSROOT and running the update
script...
It will allow
Based on discussions on -hackers, and baring any objections betwen now and
then, I'm going to go through all files in CVS and change:
$Id$ - $PostgreSQL$
I will do this the evening of Friday, November 29th ...
Marc G. Fournier Hub.Org Networking Services
--- Alvaro Herrera [EMAIL PROTECTED] wrote:
On Thu, Nov 27, 2003 at 12:40:28AM +0100, Andreas Pflug wrote:
A common mistake, can't count how often I created this one... And not
easy to find, because EXPLAIN won't explain triggers.
That's a pity. And the lack of EXPLAINing function
Christopher Kings-Lynne wrote:
Hi everyone,
I'm just interested in what everyone's personal plans for 7.5
development are?
Shridar, Gavin and myself are trying to get the tablespaces stuff off
the ground. Hopefully we'll have a CVS set up for us to work in at some
point (we didn't think
Rod Taylor [EMAIL PROTECTED] writes:
On Wed, 2003-11-26 at 23:32, Marc G. Fournier wrote:
'k, but why can't that be accomplished with $Id$?
When you import the files into the other CVS system the version and file
information $Id$ represents will be replaced by the other system. So,
when you
Tom Lane wrote:
Shridhar Daithankar [EMAIL PROTECTED] writes:
I covered only first point in my post. IMO it is not such a unsolvable
problem. If a postmaster crashes hard but leaves a backend running,
would it clean pid file etc? I don't think so. So if a postmaster can
start on a 'pid-clean'
Tom Lane wrote:
1. You can't easily generate a clean diff of your local version against
the original imported from postgresql.org. The changes you actually
made get buried in a mass of useless $Foo$ diff lines. Stripping those
out is possible in theory but painful.
Is that the reason linux does
80 matches
Mail list logo
