shakahsha...@gmail.com wrote:
-- PostgreSQL database dump
--
-- pg_dump version: 8.5devel
--
-- remote database version: 8.5devel (80500)
--
FWIW, and I havent read the entire thread, but pg_dump already *stores*
this information in a custom format. Try:
pg_dump -Fc blah
pg_restore -L
Tom Lane wrote:
It would be fairly easy, I think, to add some reloption fields that
would let these parameters be controlled on a per-table level.
Per-column would be much more painful; do we really need that?
Another +1 on the per-table setting. Or a config file setting to disable
this
Alex Hunsaker wrote:
For the record I just imported a production database that sits at
about ~20G right now with *zero* size increase (rounding to the
nearest gigabyte). That's with basically the exact same schema just
different data.
Guessing you don't have many plain text rows 1M.
Tom Lane wrote:
I think pretty much everybody except Philip Warner has found the stuff
around the TOC data structure and the archiver API to be confusing.
I'm not immediately sure about a better design though, at least not if
you don't want to duplicate a lot of code between the plain pg_dump
on-disk
formats. Question is:
Is there a simple way to determine compatibility? (eg. a small
well-defined list of requirements)
In the specific instance I am working with, I'd like to copy from 64 bit
AMD BSD system to a 64 bit Linux system.
Philip Warner
--
Sent via pgsql-hackers mailing list
Jeff Davis wrote:
On Tue, 2008-12-02 at 16:21 +0200, Heikki Linnakangas wrote:
initdb on one platform, copy the data directory over to the other
system, and try to start postmaster. It will complain if the on-disk
format is not compatible.
You can also run pg_controlinfo on both
was hoping it was a simple set of requirements, but that's life.
--
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
are looking at offloading are large summary-type
sequential scans of big tables.
--
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
Alex Hunsaker wrote
Uhh sounds like you are describing hot standby (currently in the works
for 8.4) see:
Yep. That's exactly what I'm talking about. Thanks for the links!
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
.
--
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 03 5330 3171 | _ \
Fax: (+61) 03
Sorry, should RTFM more closely:
If a transaction is waiting for a row-level lock, it will usually
appear in the view as waiting for the transaction ID
of the current holder of that row lock.
so I need to look at the row locks on the blocker.
Philip Warner wrote:
Hi,
Can anyone
Tom Lane wrote:
Neither are we, because you left out all the columns that might tell
that ...
The columns are actually blankit's the other rows I left out with
the row-level locks:
925282231 | 925280527 | | 62814 | RowExclusiveLock| t
925282208 | 925280527 |
Tom Lane wrote:
How about printing that notice at the top of the dump file as well?
Hmm ... that might be feasible in plain text output, but I don't see
any easy way to get a similar effect in archive modes.
Just saw this, obviously very late, but from memory there is a TOC entry
Another 0.02c, bringing the grand total to 0.04c.
Andrew Dunstan wrote:
First, we need a way to decide the boundary between the serially run
pre-data section and the remainder of the items in the TOC.
Currently the code uses the first TABLEDATA item as the boundary.
That's not terribly
+ if (strcmp(te-desc,CONSTRAINT) == 0
||
+ strcmp(te-desc,FK
CONSTRAINT) == 0 ||
+ strcmp(te-desc,CHECK
CONSTRAINT) == 0 ||
+
Andrew Dunstan wrote:
Unfortunately, it quite possibly would. You would not be able to build
two indexes on the same table in parallel, even though they wouldn't
have conflicting locks.
I suppose so, but:
1. By the same logic it might speed things up; it might build two
completely separate
Albe Laurenz wrote:
We sometimes need to move a database from one machine to another
(hardware old/broken, upgrades, etc.). Now whenever a database is
moved to a different computer, all the clients have to address the
database at the new hostname.
A lower-tech solution is:
Tom Lane wrote:
Define die quite nastily ... you haven't really said what went wrong.
We lost data from this table, and ended up with transactions rolled back
that were in fact committed (ie. data was in DB, program code thought
they were rolled back).
End result was we deemed the database
We just had a DB die quite nastily, and have no clear idea why.
Looking in the system logs shows nothing out of the ordinary, and
looking in the db logs shows a few odd records:
2006-01-25 12:25:31 EST [mail,5017]: ERROR: failed to fetch new tuple
for AFTER trigger
2006-01-25 12:26:01 EST
There's a number of things that can be pushed down over a union set, in
certain circumstances.
FWIW, you should also be able to push the unions up.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
The optimizer seems to want to use sequential scans on inherited tables
when crossed with another table, as the following seems to demonstrate:
Create Table base(f1 bigserial);
create table inh1(f2 bigint) inherits (base);
create table inh2(f2 bigint) inherits (base);
create table inh3(f2
Is it intentional that your test case omits an analyze on t2?
No; my mistake.
(The larger point that joins of inheritance unions aren't well-planned
is true, but it's always been true...)
It also seems to have a probkem with unions in views.
Is there anything that can be done about this --
Tom Lane wrote:
It's something that's on the ever-growing TODO list ... I dunno if
anyone has any near-term plans to work on it. It'd definitely be
nice to teach the planner to do joins-over-unions well, and then
make inheritance just invoke that behavior instead of being a crocky
special case.
to be set per backend,
and the client could use a SET variable to adjust the standard amount for
it's own backend. When the client dies/disconnects, the queueing parameter
(whatever it is) would be reduced appropriately.
Philip
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330
At 02:53 AM 30/01/2005, Tom Lane wrote:
Philip Warner [EMAIL PROTECTED] writes:
We have a frequently updated (peak 5/sec) table with about 1000 rows.
We run VACCUM FULL on this table every 5 minutes.
Plain vacuum (perhaps executed even more often, like
once a minute) will cause fewer locking
validity) with 8.0b1.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81
-created indexes as appropriate, whatever that means).
Thanks for all the work.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172
one. We need ALTER INDEX or STORE INDEX or whatever if we
can't nicely put each index in it's own tablespace. We're only talking PKs
aren't we? I'll have to think about this.
Philip Warner
-tablespace-var that behaves like the schema search path
Are there any others?
And the best quote from the thread:
Philip Warner wrote:
soapbox
A fact I positively loath! Relying on the 'bluder-on-regardless' approach
is not something I'd like to enshrine.
/soapbox
The 'bluder-on-regardless' phrase
solutions: magic-tablespace-var, virtual-tablespaces...all seem
to add clunky functionality that will only be used in pg_dump. If we're
going to add something, I'd prefer not to add clutter.
Philip Warner
don't understand?
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81
for %%tablespace%%
in the DEFINTION.
Same would apply for indexes etc.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
so all items that are
relevant can be dumped with '%%tablespace%%'. pg_dump still constructs
CREATE TABLE statements, so that is the natural place to add the tablespace
marker and avoid parsing for tables.
Philip Warner
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03
?
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172
. It would remove/reduce bloat in pg_dump and
make the functions available more generally, at the expense of duplicating
lots of code for each supported version.
/random_idea
Philip Warner| __---_
Albatross
to work. If it's not there by beta3, I'll just use ALTER
commands.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax
At 10:51 PM 1/09/2004, Philip Warner wrote:
Won't be 'till beta2.
...sorry, beta3
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
At 03:25 AM 28/08/2004, Tom Lane wrote:
If you've got any must fix issues, please get 'em in over the weekend.
Is the 'magic_tablespace' variable going to be there?
Philip Warner| __---_
Albatross
At 12:57 AM 27/08/2004, Bruce Momjian wrote:
Has this been fixed?
In my local files, yes. I've been waiting on the 'set magic_tablespace'
code, but patch to fix the warnings only is attached.
Philip Warner
;
CREATE SCHEMA foo;
to behave? Would foo have a default tablespace of some_ts?
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172
At 01:27 PM 24/08/2004, Tom Lane wrote:
I prefer declaring it as unsigned, which means you drop the
\377 end instead ...
...I've used explicit values (128) since '\200' is -127.
Philip Warner
At 01:27 PM 24/08/2004, Tom Lane wrote:
I prefer declaring it as unsigned, which means you drop the
\377 end instead ...
No problem.
Philip Warner| __---_
Albatross Consulting Pty. Ltd
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax
alter commands.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81
work on schemas?
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81
implementation.
I'm happy to do the pg_dump changes, assuming Tom gets the SET stuff sorted
out. But would appreciate it if you could do some testing.
Philip Warner| __---_
Albatross Consulting Pty. Ltd
/restore. If the table
'inherited' it's tablespace, then a dump/restore should do nothing (ie. not
issue a SET TABLESPACE).
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75
?
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172 | ___ |
Http
commands, they will appear as such in the text file.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172
to.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03
?
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax
?
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330
, will the 'interactive-session' check consider an included file (\i)
to be interactive? I'd vote yes.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
At 09:26 AM 18/08/2004, Philip Warner wrote:
Also, will the 'interactive-session' check consider an included file (\i)
to be interactive? I'd vote yes.
In retrospect, I assume the interactive-session limit would not be a
problem here since the command would be entered interactively. The source
At 02:32 PM 12/08/2004, Philip Warner wrote:
At 01:27 PM 12/08/2004, Bruce Momjian wrote:
Set client_min_messages to WARNING?
Sounds like a plan.
Attached patch sets client_min_messages as above and gives some
context to errors messages, eg:
pg_restore: [archiver (db)] Error from TOC Entry 19
...
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172 | ___ |
Http
it's data in a textual format.
What are you trying to do?
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500
dumpfile
and it will only restore the items corresponding to lines in listfile.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
can't see another way that won't require constant updates tweaking into
the future.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
as multi-statement, and still be parsed.
If my assumption in (1) is valid, then I have a very mild preference for
it, but am happy with either.
Philip Warner| __---_
Albatross Consulting Pty. Ltd
transaction and only rollback
that TX? Or is that not possible/desirable. If possible, this would be a
*great* feature for those of use prone to mis-spellings.
Philip Warner| __---_
Albatross Consulting Pty
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172 | ___ |
Http
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61
start/end. It knows about quotes but not about dollar-quotes.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61
; $$
LANGUAGE plpgsql;
2004-08-12 01:45:02 ESTzzz,birds: LOG: statement: ALTER FUNCTION
public.xxx() OWNER TO birds;
Philip Warner| __---_
Albatross Consulting Pty. Ltd
in and out of the tag?
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81
to look for
PQresultStatus(...) == PGRES_COPY_IN for each statement it executes, so it
needs to pass statements one at a time.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172
?
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172
about valid tags, zero
or more characters is all I can see. Can you give me a definitive answer
as to what is valid? AFAICT, must be [A-Z,a-z,0-9,_]*, with non-numeric
start. Is that right?
Philip Warner
At 01:27 PM 12/08/2004, Bruce Momjian wrote:
Set client_min_messages to WARNING?
Sounds like a plan.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
and assume the '$...' was some other valid
sql element.
From other threads, it sounds like removing the statement detection code
entirely is not an option.
Philip Warner| __---_
Albatross Consulting Pty
At 01:17 PM 3/08/2004, Christopher Kings-Lynne wrote:
Would be a bit of work though.
I've been looking at this for a while now, and will probably give it a go
for 7.6/8.
Philip Warner| __---_
Albatross
At 02:00 PM 3/08/2004, Christopher Kings-Lynne wrote:
I'd be interested in collaborating.
Sounds good.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
forward, doing it right for future versions
would be my vote.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel
pg_listener (even if we're not using NOTIFY/LISTEN). Not sure why the
ANALYZE is locking the relation, though...but it is locked in AccessShareLock.
I can send a log of my investigations if necessary.
Philip Warner
if we're not using NOTIFY/LISTEN). Not sure why the
ANALYZE is locking the relation, though...but it is locked in AccessShareLock.
I can send a log of my investigations if necessary.
Philip Warner
At 06:21 PM 3/05/2004, Philip Warner wrote:
'tuple concurrently updated'
The database logs show the same error in each case where a long delay has
occurred. And before anyone suggests it, we already have processes in place
to prevent to ANALYZEs running at the same time
At 06:21 PM 3/05/2004, Philip Warner wrote:
'tuple concurrently updated'
I lied. The database DO NOT logs show the same error in each case where a
long delay has occurred. It happens sometimes; recent process logs do show
the 'async_notify waiting' status, however.
I'll try not to send any more
At 07:33 PM 3/05/2004, Philip Warner wrote:
I'll try not to send any more emails until someone responds ;-)
I also noticed this in SIInsertDataEntry sinvaladt.c:
/*
* Try to prevent table overflow. When the table is 70% full send a
* WAKEN_CHILDREN request
. The latter seems unlikely since it hangs frequently, and
pg_listeners is empty.
Does ANALYZE rollback if it dies? Could this account for the delay?
Philip Warner| __---_
Albatross Consulting Pty. Ltd
(ListenerRelationName, ExclusiveLock);
and see how it goes.
Thanks for the help.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330
...
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61
.
The data in the database is extremely dynamic, so the analyze needs to be
run regularly; we could run less often but this will just mean the problem
happens once per week instead of once per day.
Would ACCESS SHARE be OK?
Philip
pg_catalog.pg_listener
INFO: pg_listener: found 0 removable, 0 nonremovable row versions in 0 pages
VACUUM
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498
At 02:21 PM 3/05/2004, Tom Lane wrote:
[blinks...] There's something pretty strange about that. Are you using
LISTEN/NOTIFY at all?
Nope.
Philip Warner| __---_
Albatross Consulting Pty. Ltd
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172
--verify dump-file
if file integrity is the objective.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83
At 03:13 AM 17/03/2004, Bruce Momjian wrote:
* Allow pg_dumpall to use non-text output formats
Is anyone working on this?
Philip Warner| __---_
Albatross Consulting Pty. Ltd
1 - 100 of 438 matches
Mail list logo
