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_rest
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 >
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
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
nor version?)
- same CPU architecture
I was hoping it was a simple set of requirements, but that's life.
--
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/
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
-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
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:
http
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
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 |
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:
>
duce such contention.
--
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 03 5330 3171 | __
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 e
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
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 i
>
> + if (strcmp(te->desc,"CONSTRAINT") == 0
> ||
> + strcmp(te->desc,"FK
> CONSTRAINT") == 0 ||
> + strcmp(te->desc,"CHECK
> CONSTRAINT") == 0 ||
> +
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:
http://
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 databas
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 [mail,
>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
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 ca
>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 --
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 bigint)
stuff...
commit;
Also, the blunder-on-regardless approach is popular in pg_dump, or so I'm
told ;-).
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 65
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.
P
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) w
lem.
----
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5
utput validity) with 8.0b1.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83
and that this will affect
auto-created indexes as appropriate, whatever that means).
Thanks for all the work.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ -
e same command;
This is a hard 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.
-------
uable.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330
but not need half a dozen TSs)
- magic-tablespace-var that behaves like the schema search path
Are there any others?
And the best quote from the thread:
Philip Warner wrote:
>
> A fact I positively loath! Relying on the 'bluder-on-regardless' approach
> is not something I'
0 and be done with it.
The other 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.
--------
o parse, I'd add it in pg_dump 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.
-----
the TABLESPACE text for %%tablespace%%
in the DEFINTION.
Same would apply for indexes etc.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A
ing I don't understand?
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 050
then create a
virtual tablespace that points to pg_default.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 8
_dump will
achieve almost the same result. 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.
--------
Philip Warner
?
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 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 0
#x27;till beta2.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _
ing
for the command to work. If it's not there by beta3, I'll just use ALTER
commands.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/
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
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.
-------
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
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.
-------
ase.
----
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3
s the problem.
----
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61)
variable = some_ts;
CREATE SCHEMA foo;
to behave? Would foo have a default tablespace of some_ts?
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B
d. Otherwise, I'll need to use alter commands.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@)
o databases or schemas
though.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 |
.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03
work on schemas?
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81
/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. |/
best person for a quick
and clean 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| _
tablespace?
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | __
noone else wants to.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | __
in general.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03
TABLE and ALTER TABLE in different
commands, they will appear as such in the text file.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(
rrors will not stop a table being created.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172
er to the tablespace?
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 |
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 interactiv
, 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.
?
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330
/lib/pgsql-8.0b1/lib/plpgsql', 'plpgsql_validator'
LANGU...
----
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | ___
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 fr
cleanup.
I can't see another way that won't require constant updates & tweaking into
the future.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ -
ile 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
to present 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) | /(@) __---_
be parsed. Old dump files would be
treated 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|
can mode on that character 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|
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
s also not explicit 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| __---_
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
be vague, it's a long time since I wrote the code.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81
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
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
$
?
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172
error.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81
om
dump format was to allow for better error handling during a restore.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) _
kind of quotes can be ignored.
Is there any circumstance where an unquoted '$' is valid?
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ -
pgsql;
2004-08-12 01:45:02 EST: LOG: statement: ALTER FUNCTION
public.xxx() OWNER TO birds;
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
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: (+6
r feature.
----
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | ___
e'?
-D(?) --die-on-errors
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172 |
statement in a nested 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| __---
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 65
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| __---_
Alba
than carry the hack forward, doing it right for future versions
would be my vote.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /
ined, a better mechanism may become apparent.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172
check.
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03
with
heap_openr(ListenerRelationName, ExclusiveLock);
and see how it goes.
Thanks for the help.
--------
Philip Warner| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A
backend to read pg_listeners, or it is dying while ANALYZING
pg_listeners. 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?
----
Phil
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 t
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
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
deed try to lock
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.
------
sync.c does indeed try to lock
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| __---_
Albatross Consulting Pty. Ltd. |/ - \
(A.B.N. 75 008 659 498) | /(@) __---_
Tel: (+61) 0500 83 82 81 | _ \
Fax: (+61) 03 5330 3172
1 - 100 of 540 matches
Mail list logo
