Re: [HACKERS] 8.5 TODO: Add comments to output indicating version of pg_dump and of the database server
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 ... and you will get something like: ; ; Archive created at Sun Nov 29 12:34:24 2009 ; dbname: blah ; TOC Entries: 202 ; Compression: -1 ; Dump Version: 1.10-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 8.0.3 ; Dumped by pg_dump version: 8.0.3 ; so, all that is needed is to add the relevant statements into the output code. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Significantly larger toast tables on 8.4?
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. > I don't suppose you could export some random rows and see if you see > any size increase for your data? My gut says you wont see an > increase. > Will see what I can do. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Significantly larger toast tables on 8.4?
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 for the instance. We have a 200GB DB that is mostly large text (>1MB) that is not searched with substr. If we see a blowout in size of even 3x, we will not be able to upgrade due to disk space limitations (at least without paying for a lot of disks on mirror servers and hot-standy servers). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] parallel restore vs. windows
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 and > the pg_dump/pg_restore cases. > Here was I thinking it was more or less self-documenting and clear ;-). But, yes, it is complex, and I can still see no way to reduce the complexity. I should have some old notes on the code and am happy to expand them as much as necessary. If people want to nominate key areas of confusion, I will concentrate on those first. In terms of the current discussion, I am not sure I can help greatly; writing cross-platform thread code is non-trivial. One minor point: I noticed in early versions of the code that a global AH had been created -- it occurs to me that this could be problem. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PiTR and other architectures....
> > But, as Simon pointed out, is it really worth the risk? PITR is closer > to a physical process, and it's probably wise to just assume it's not > portable. > Yeah...I am getting that impression ;-). From this I will assume we need: - same OS (and OS minor version?) - same CPU architecture I 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) | /(@) __---_ Tel: (+61) 03 5330 3171 | _ \ Fax: (+61) 03 5330 3172 | ___ | http://www.rhyme.com.au <http://www.rhyme.com.au/> |/ \| |---- GPG key available upon request. | / |/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PiTR and other architectures....
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 systems, and compare the >> results. If the "Maximum data alignment", and all the values below it in >> the pg_controlinfo output match, the formats are compatible. >> > > I don't think these things will work for all differences that could be > problematic. For instance, a GNU system has a different collation for > the en_US locale than an OS X system. This means that the indexes built > using en_US on one system can't be moved to the other. > > How would either of these tests be able to determine that the systems > are incompatible? > > wow...that's a little scary. Sounds like there is no trustworthy test I can run. Other than the case of collation differences, are there any other kinds of problems that would not be detected by even a postmaster restart? |/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PiTR and other architectures....
Having just tried to restore a 64 bit BSD database to a 32 bit linux machine (using PiTR), I have now realized the (with hindsight, obvious) error in my ways. Now, I need to plan a way forward. From reading of other peoples similar problems, I now realize that I need a system with identical 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 (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Opening a recovering DB in for read-only access?
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://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Opening a recovering DB in for read-only access?
Sounds somewhat evil, I know, but I was wondering if it was even remotely possible with the current design? The reason: we are contemplating using pg_standy to create a warm-standby. It would be a bonus if we would run read-only queries against this DB to take some of the load off or production servers. We currently use slony to provide warm-standby *and* read-only access, but pg_standby is a great deal more appealing...especially if there was some way to do read-only access at the same time. FWIW, the data would not even need to be completely consistent ... the kinds of things we 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) | /(@) __---_ Tel: (+61) 03 5330 3171 | _ \ Fax: (+61) 03 5330 3172 | ___ | http://www.rhyme.com.au <http://www.rhyme.com.au/> |/ \| |---- GPG key available upon request. | / |/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Meaning of transaction pg_locks?
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 | | 62814 | RowExclusiveLock| t | | 1192675195 | 62814 | ExclusiveLock | t 925282207 | 925280527 | | 62814 | RowExclusiveLock| t 925282025 | 925280527 | | 62814 | AccessShareLock | t 925282025 | 925280527 | | 62814 | RowExclusiveLock| t 925282206 | 925280527 | | 62814 | RowExclusiveLock| t 925282212 | 925280527 | | 62814 | RowExclusiveLock| t 925282210 | 925280527 | | 62814 | RowExclusiveLock| t 925282209 | 925280527 | | 62814 | RowExclusiveLock| t 925281338 | 925280527 | | 62814 | AccessShareLock | t 925281338 | 925280527 | | 62814 | RowExclusiveLock| t 925282211 | 925280527 | | 62814 | RowExclusiveLock| t 925282213 | 925280527 | | 62814 | RowExclusiveLock| t So I assume the processes waiting on the TX were waiting for one or more of those rows. Now I just need to figure out why the rows were locked for such a long time (the row level locks are mostly on one table and various indexes of that table). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Meaning of transaction pg_locks?
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 explain the way to debug this kind of situation and/or > explain the meaning of these locks? > > Partial output of "select * from pg_locks": > >| | 1192675195 | 62860 | ShareLock | f >| | 1192675195 | 62814 | ExclusiveLock | t >| | 1192675195 | 62838 | ShareLock | f >| | 1192675195 | 63525 | ShareLock | f > > where 1192675195 is the 'transaction' field. > > I am not at all clear what the processes are waiting for, or if there is > a way to reduce such contention. > > -- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 03 5330 3171 | _ \ Fax: (+61) 03 5330 3172 | ___ | http://www.rhyme.com.au <http://www.rhyme.com.au/> |/ \| |---- GPG key available upon request. | / |/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Meaning of transaction pg_locks?
Hi, Can anyone explain the way to debug this kind of situation and/or explain the meaning of these locks? Partial output of "select * from pg_locks": | | 1192675195 | 62860 | ShareLock | f | | 1192675195 | 62814 | ExclusiveLock | t | | 1192675195 | 62838 | ShareLock | f | | 1192675195 | 63525 | ShareLock | f where 1192675195 is the 'transaction' field. I am not at all clear what the processes are waiting for, or if there is a way to reduce such contention. -- -------- Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 03 5330 3171 | _ \ Fax: (+61) 03 5330 3172 | ___ | http://www.rhyme.com.au <http://www.rhyme.com.au/> |/ \| |---- GPG key available upon request. | / |/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposed patch: make pg_dump --data-only consider FK constraints
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 type for comments or warnings that get output when the dump is used. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] parallel pg_restore design issues
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 robust (what if there aren't any?).
Drawing on your later comments, and bearing in mind that it is always
recommended to use the *new* pg_dump to upgrade, I'd suggest adding some
flags to the header of the dump that say 'contains data' (might already
be there), then adding flags to TOC entries that indicate useful
properties. ISTM there are effectively 5 types of TOC entry which can be
characterised by: table definitions (including schema -- anything
defining the structure of the db), data load, performance items (index
definitions), integrity items (constraints and triggers) and function
definitions (usually part of an application interface or triggers).
So, based on the current needs, adding:
- isData
- isStructural (must be a better name) -- for anything that is
required *before* data can be loaded.
- isPerformance (likely to improve database performance after theyy
are applied)
would seem to satisfy your need.
I'm making this up as I go along, so adding any other set of flags that
abstracts the questions you are asking in a generic and useful way would
be fine. I'm pretty sure we have places already in pg_dump that could
use such flags.
> Also, people have wanted to steer clear of hardcoding much knowledge
> of archive member types into pg_restore as a way of future-proofing it
> somewhat. I'm wondering if we should have pg_dump explicitly mark
> items as pre-data,data or post-data. For legacy archives we could
> still check for either a TABLEDATA item or something known to sort
> after those (i.e. a BLOB, BLOB COMMENT, CONSTRAINT, INDEX, RULE,
> TRIGGER or FK CONSTRAINT item).
This is, at least to some extent, answered above. Anything that is not
isData or isStructural is post data.
> Another item we have already discussed is how to prevent concurrent
> processes from trying to take conflicting locks. Her we really can't
> rely on pg_dump to help us out, as lock requirements might change (a
> little bird has already whispered in my ear about reducing the
> strength of FK CONSTRAINT locks taken). I haven't got a really good
> answer here.
Several things occurred to me here.
- you need to avoid shooting yourself in the foot by, for example,
trying to define an FK before relevant indexes are defined. This is
probably one area where checking the TOC entry type becomes necessary,
unless we create a flag 'isPerformance' flag (see above) in which case
you load all isPerformance entries immediately after data is loaded.
AFAICT, isPerformance becomes a synonym for entry_type='INDEX' (not
sure), but adding the flag (required for all toc entries) will aid in
future-proofing in ways that checking entry type values do not.
- It would be interesting to see benchmarks of pg_restore running with
*anything* that shared dependencies disallowed from running concurrently
vs. anything that's not an index vs. anything thats not 'isPerformance'.
- allowing lock failures might help; ie. if a statement produces a lock
failure, just wait a bit and put it back at the end of the queue. If it
fails a second time, mark it as 'single-thread-only'. Would be
interesting to see if this imroved the pre-data load, or made it slower.
- Ultimately, I suspect trying to second-guess backend locking will be
a hard-fought battle that will not be worth the resources expended.
Getting 95% of the way there will be good enough so long as lock
conflicts do not crash pg_restore (see previous point).
> Another possible algorithm would reorder the queue by elevating any
> item whose dependencies have been met.
Bad idea; FKs benefit from indexes.
I think, based on the benefit provided by 'isPerformance' items (by
definition), you probably need to introduce a choke point to ensure all
isPerformance items have been executed before moving on to other items.
The locking strategy above may effectively do that (shared dependencies
will be disallowed unless both are 'isPerformance').
> This will mean all the indexes for a table will tend to be grouped
> together, which might well be a good thing, and will tend to limit the
> tendency to do all the data loading at once.
Or it might be a bad thing. Not sure why you can't intermix data and
schema items ('isStructure') tho.
And, I'm curious: what is wrong with loading all the data at once? That
will be the result I suspect for simple queueing reasons: data takes a
long time, eventually without a scheduler and explicit thread limits
(see below), all threads will be loading data.
> Both of these could be modified by explicitly limiting TABLEDATA items
> to a certain proportion (say, one quarter) of the processing slots
> available, if other items are available.
Yep. But is there a benefit?
Re: [HACKERS] parallel pg_restore - WIP patch
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 indexes and thereby avoid (some kind of) contention. In any case, it would most likely do *something* else. It should only reduce performance if (a) it can do nothing or (b) there is a benefit in building multiple indexes on the same table at the same time. 2. Perhaps if there are a limited number of items that share dependencies but which are known to be OK (ie. indexes), maybe list them in the inner loop as exceptions and allow them to run parallel. This would mean a failure to list a new TOC item type would result in worse performance rather than a crash. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] parallel pg_restore - WIP patch
> > + if (strcmp(te->desc,"CONSTRAINT") == 0 > || > + strcmp(te->desc,"FK > CONSTRAINT") == 0 || > + strcmp(te->desc,"CHECK > CONSTRAINT") == 0 || > + strcmp(te->desc,"TRIGGER") == 0 > || > + > strcmp(slots[i].te->desc,"CONSTRAINT") == 0 || > + strcmp(slots[i].te->desc,"FK > CONSTRAINT") == 0 || > + strcmp(slots[i].te->desc,"CHECK > CONSTRAINT") == 0 || > + > strcmp(slots[i].te->desc,"TRIGGER") == 0) > Really just an observation from the peanut gallery here, but every time pg_restore hard-codes this kind of thing, it introduces yet another possible side-effect bug when someone, eg, adds a new TOC type. Would it substantially decrease the benefits of the patch to skip *any* toc entry that shares dependencies with another? (rather than just those listed above). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new feature: LDAP database name resolution
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://freshmeat.net/projects/postgresql-relay/ It sits on top of PG and intercepts the connection protocol, as I understand it. Seems to work... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Suggestions for post-mortem...
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 to be in an unknown, uncorrectable and unstable state. > These could all be manifestations of the ReadBuffer bug fixed in 8.0.6. > Tickling that bug would result in zeroing out a recently-added table page, > which would result in (a) more index entries than table entries, and > (b) possible bleating from other processes wondering where their freshly > inserted tuples went. This sounds consistent, I'd guess the 80 missing records correspond to 80 most recently updated. Not sure about the missing user -- I'll see what I can find. Thanks for the help -- we now have a probable cause, and a way forward. Alvaro Herrera wrote: > Can you confirm how long does the vacuum take to run? Usually very quick, runs every minute -- so the problem occurred between the two vacuums. > isolated to this one table, or does it manifest somewhere else? Do you > have other errors that may indicate a hardware problem? > No. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Suggestions for post-mortem...
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,93689]: WARNING: index "_pkey" contains 1416 row versions, but table contains 1410 row versions 2006-01-25 12:26:01 EST [mail,93689]: HINT: Rebuild the index with REINDEX. 2006-01-25 12:26:01 EST [mail,93689]: WARNING: index "" contains 1416 row versions, but table contains 1410 row versions ...repeated several times for several indexes of the same table. These messages occurred almost immediately before we noticed the dead state of the DB. Over an hour before these messages there was a deadlock, but that's not too worrying -- the DB was still OK. After the above messages, about 80 rows were missing from the table, and a REINDEX did not restore them (not really surprising). The table in question has only a small number of rows (1400-ish), but gets updated up to 5 to 10 times per second. Thankfully, we had replication in place and just failed over, but we'd like to try to understand what happened to the old DB. Any suggestions where to start? Or what the first error might signify? Or what to put in place to catch more details next time? It's been running fine for several months (until now) using PG 8.0.3. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Optimizer oddness, possibly compounded in 8.1
>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 choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Optimizer oddness, possibly compounded in 8.1
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. > > Sounds good; currently if you use the polymorphism of inherited tables, and happen to cross 2 such tables, you get O(n^2) performance. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Optimizer oddness, possibly compounded in 8.1
>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 -- workarounds etc? Any plans to address it? We've got a couple of places where it's beginning to bite us due to growth of tables. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Optimizer oddness, possibly compounded in 8.1
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) inherits (base);
create table inh4(f2 bigint) inherits (base);
insert into inh1(f2) values(1);
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
insert into inh1(f2) select f2 from inh1;
create unique index base_f1 on base(f1);
create unique index inh1_f1 on inh1(f1);
create unique index inh2_f1 on inh2(f1);
create unique index inh3_f1 on inh3(f1);
create unique index inh4_f1 on inh4(f1);
vacuum analyze base;
vacuum analyze inh1;
vacuum analyze inh2;
vacuum analyze inh3;
vacuum analyze inh4;
create table t2(f1 bigint);
insert into t2 values(1);
insert into t2 values(2);
insert into t2 values(128);
insert into t2 values(32768);
explain analyze select * from t2,base where base.f1=t2.f1;
gives:
Hash Join (cost=1.05..1546.04 rows=150 width=16) (actual
time=0.433..436.791 rows=4 loops=1)
Hash Cond: ("outer".f1 = "inner".f1)
-> Append (cost=0.00..1181.66 rows=72366 width=8) (actual
time=0.279..331.698 rows=65536 loops=1)
-> Seq Scan on base (cost=0.00..29.40 rows=1940 width=8)
(actual time=0.002..0.002 rows=0 loops=1)
-> Seq Scan on inh1 base (cost=0.00..1073.36 rows=65536
width=8) (actual time=0.273..148.326 rows=65536 loops=1)
-> Seq Scan on inh2 base (cost=0.00..26.30 rows=1630 width=8)
(actual time=0.002..0.002 rows=0 loops=1)
-> Seq Scan on inh3 base (cost=0.00..26.30 rows=1630 width=8)
(actual time=0.003..0.003 rows=0 loops=1)
-> Seq Scan on inh4 base (cost=0.00..26.30 rows=1630 width=8)
(actual time=0.002..0.002 rows=0 loops=1)
-> Hash (cost=1.04..1.04 rows=4 width=8) (actual time=0.132..0.132
rows=0 loops=1)
-> Seq Scan on t2 (cost=0.00..1.04 rows=4 width=8) (actual
time=0.111..0.119 rows=4 loops=1)
Total runtime: 436.880 ms
unwrapping the query into a series of UNIONS on the child tables reduces
the run time by a factor of several hundred under PG8.0:
explain analyze
select z.f1 from t2,only base z where z.f1=t2.f1
UNION ALL
select z.f1 from t2,inh1 z where z.f1=t2.f1
UNION ALL
select z.f1 from t2,inh2 z where z.f1=t2.f1
UNION ALL
select z.f1 from t2,inh3 z where z.f1=t2.f1
UNION ALL
select z.f1 from t2,inh4 z where z.f1=t2.f1
Append (cost=0.00..94.87 rows=20 width=8) (actual time=0.184..0.485
rows=4 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..20.42 rows=4 width=8)
(actual time=0.096..0.096 rows=0 loops=1)
-> Nested Loop (cost=0.00..20.38 rows=4 width=8) (actual
time=0.093..0.093 rows=0 loops=1)
-> Seq Scan on t2 (cost=0.00..1.04 rows=4 width=8)
(actual time=0.033..0.043 rows=4 loops=1)
-> Index Scan using base_f1 on base z (cost=0.00..4.82
rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=4)
Index Cond: (z.f1 = "outer".f1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..13.18 rows=4 width=8)
(actual time=0.084..0.194 rows=4 loops=1)
-> Nested Loop (cost=0.00..13.14 rows=4 width=8) (actual
time=0.081..0.178 rows=4 loops=1)
-> Seq Scan on t2 (cost=0.00..1.04 rows=4 width=8)
(actual time=0.002..0.012 rows=4 loops=1)
-> Index Scan using inh1_f1 on inh1 z (cost=0.00..3.01
rows=1 width=8) (actual time=0.031..0.033 rows=1 loops=4)
Index Cond: (z.f1 = "outer".f1)
-> Subquery Scan "*SELECT* 3" (cost=0.00..20.42 rows=4 width=8)
(actual time=0.061..0.061 rows=0 loops=1)
-> Nested Loop (cost=0.00..20.38 rows=4 width=8) (actual
time=0.057..0.057 rows=0 loops=1)
-> Seq Scan on t2 (cost=0.00..1.04 rows=4 width=8)
(actual time=0.003..0.011 rows=4 loops=1)
-> Index Scan using inh2_f1 on inh2 z (cost=0.00..4.82
rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=4)
Index Cond: (z.f1 = "outer".f1)
-> Subquery Scan "*SELECT* 4" (cost=0.00..20.42 rows=4 width=8)
(actual time=0.058..0.058 rows=0 loops=1)
-> Nested Loop (cost=0.00..20.38 rows=4 width=8) (actual
time=0.055..0.055 rows=0 loops=1)
-> Seq Scan on t2 (cost=0.00..1.04 rows=4 width=8)
(actual time=0.002..0.011 rows=4 loops=1)
Re: [HACKERS] [PATCHES] Continue transactions after errors in
At 12:28 AM 27/04/2005, Tom Lane wrote: Can you show a plausible use-case for such a thing? A not-uncommon case in other DBs is to handle insert/update code where insert is the most likely result. Not sure if this is relevant to scripts: Begin; ...do stuff... insert into update... ...more 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 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Escaping the ARC patent
At 09:02 AM 5/02/2005, Tom Lane wrote: That strikes me as a bad idea --- what will cause the queue size to revert to normal, if the batch process fails before resetting it? Just an idle thought, but each connection to the DB could add a fixed amount to some queueing parameter. The amount added 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 Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Allowing VACUUM to time out when waiting for
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 headaches. We have done both in the past, but found some tables still just grew (perhaps just because of infrequent locks that prevented the plain VACUUM). I'll go back to the plain VACUUM and monitor the table growth. Am I correct in saying that the FSM now tracks the entire table, and that the FSM parameters just determine how much is stored in memory? I think you could do that by setting a statement timeout. This would be a good solution if we still see growth with plain VACUUM. Is any type of opportunistic locking likely/planned for a future version (ie. a has lock, b asks for conflicting lock, c asks for lock that is OK with a but denied by b; so c's lock is allowed and b stays waiting). -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Allowing VACUUM to time out when waiting for locks?
We have a frequently updated (peak > 5/sec) table with about 1000 rows. We run VACCUM FULL on this table every 5 minutes. The regular updates are not long in duration, and the vacuum is fast, so they do not produce noticeable delays. When we run a pg_dump on the database: - the dump takes a long standing AccessShareLock lock on this table (the database is large, and the table is locked for the duration). - the regular updates run quite happily - the VACUUM FULL comes along and asks for a AccessExclusiveLock (which is not granted due to PG_DUMP). - the regular updates hang until the dump completes Is it possible to set up a vacuum to fail if a lock is not granted in a limited period of time (eg. 1 minute)? We could use lock files to synchronize our dumps with our vacuums, but were hoping for a way of managing this within the DB so that ad-hoc dumps will not cause a problem. ---- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 02:37 PM 6/11/2004, Tom Lane wrote: If you have a preliminary patch, you could pass it along and I'll finish it up. Attached. It has some trivial-looking rejects on current CVS. Let me know if you would prefer me to do the work, or want some testing done. It was tested (in terms of output validity) with 8.0b1. 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ pg_dump_040820_patch_with_funcs.gz Description: Binary data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 06:19 AM 6/11/2004, Tom Lane wrote: You had muttered something about wanting to add a TOC entry field for this --- do you still want to do the work? You can probably get it done faster than I could, but I dunno if you have time at the moment. I'd like to get it in over the weekend so that we can put out a new beta next week. Time is at a serious premium for me at the moment (I have several projects all due about now); but I wrote a patch for this a few weeks back, so it should not be a lot of work (unless pg_dump has changed in the last couple of months). I will *try* to get it done by Monday morning your time, and will let you know if I am going to miss this deadline as soon as I know. BTW, part of the backend changes was to stop emitting TABLESPACE clauses in pg_get_indexdef() and pg_get_constraintdef() output, so as of CVS tip pg_dump will in fact fail to restore index tablespaces accurately. I assume this is the backend behavior you want, but holler if not. Excellent. I assume that anything that can have a tablespace (database, schema(?), table and index -- anything else?) should emit a 'set default_tablespace="ts"' before creation (and that this will affect auto-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) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 09:28 AM 26/10/2004, Tom Lane wrote: I can't see what a search path would be good for. Nothing at this stage. It seems like we still need some notion of a database's schema, Yes. I'd be willing to jump this way if we can work out the default-tablespace inconsistencies that Bruce has on the open items list. I'll have a look in the next 18 hours... * A GUC variable named something like default_tablespace that controls which TS objects are created in when there's no explicit TABLESPACE clause. The factory default for this would of course be pg_default. Otherwise it's settable just like any other GUC var. Agree. * Get rid of TABLESPACE clause for CREATE SCHEMA, and pg_namespace.nsptablespace (ooops, another initdb). Agree. * Need to define exactly what TABLESPACE clause for a database controls; location of its catalogs of course, but anything else? Nothing else would be my call; make it like the tablespace on tables. * We could possibly say that a TABLESPACE clause attached to CREATE TABLE determines the default tablespace for indexes created by the 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. -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 08:00 AM 26/10/2004, Tom Lane wrote: I don't want a GUC variable that actively changes the default tablespace; at least not unless you want to abandon the current mechanisms for default tablespace choices entirely, and go over to making the GUC variable be the sole arbiter. Something consistent with Schemas does sound good to me; a tablespace search path (or just single default), and support for a TABLESPACE clause on table and INDEX definitions would be good. For the three largest databases I work on, the namespace/schema that a table resides in is irrelevant to the tablespace that it should be stored in. So default tablespaces on the schema are a bit of a pointless feature. The ability to have the features of schemas: default tablespace for given users, a GUC variable, and ACLs on tablespaces would be far more valuable. 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 12:38 PM 25/10/2004, Bruce Momjian wrote: o Anything that works only for pg_restore and hence doesn't work for ASCII dumps isn't an acceptable solution Agree; but don't forget that an ascii dump is implemented almost identically to "pg_dump | pg_restore", so when I refer to using pg_restore in this thread it almost certainly applies to ascii dumps as well. Eg. extra stuff in the TOC, and using the definition as a template *will* produce the requested output in ascii dumps. o Creating the tablespaces before the dump is restored is a good solution for moving tablespaces, but as Tom pointed out, it doesn't work well for non-super-user restores And for users who want to create a single database with no extra tablespaces (eg. development version vs. production instance). o Moving the indexes can't be dont easily after they are created because they are not zero-length files Pity. o The soft-failure GUC option for non-existant tablespaces is a hack just for use by pg_dump. It doesn't fix the problem that the tablespace clause makes the SQL nonstandard. If we can adopt the move-after-create solution, then we really only have two options: - virtual tablespaces (which do seem kind of useful, especially for development vs. production config where the local/personal dev version can use the same script as a production DB 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'd like to enshrine. > The 'bluder-on-regardless' phrase is very funny. Fame at last! Even with the typo. ---- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 05:41 AM 21/10/2004, Robert Treat wrote: I've been thinking of an alternative solution that sounds very similar to this. The idea is to output the CREATE TABLESPACE commands inside pg_dump ... 1) tablespace already exists to fail, but since we no longer stop on error during restore, A fact I positively loath! Relying on the 'bluder-on-regardless' approach is not something I'd like to enshrine. 2) if you have to restore on a machine with a different disk layout, give pg_restore a --override-tablespace command, which would substitute pg_default tablespace into the creation command of any tablespaces that get passed in. The bonus is that we would only have to parse on one specific command rather than worry about parsing several different commands. ... I'm still inclined to avoid any parsing if at all possible. We should hit the code that generates the definitions (90% in pg_dump) and turn the definitions into more intelligent templates. ... I was thinking that you could create two logical tablespaces on the same physical directory. This is basically the virtual/fake tablespace idea. Sometimes I think it is worth stepping back from a problem and ask what would we do if we had a clean slate, then use that to inform our current set of decisions. I'd be very interested in other people's ideas, but my thinking is: - we might not have a tablespace clause inside object definitions; we could add a STORE TABLE xxx IN TABLESPACE ts statement. Not really practical now; but worth bearing in mind as an approach for future non-standard syntax. - we'd have a full set of ALTER xxx SET TABLESPACE commands which moved relevant data etc. We have most of them. - pg_dump would issue the alter commands after creating the object; OK, it moves files, not very efficient, but using a 'SET MAGIC_TABLESPACE_VAR' has previously been discounted as a solution. *If* you accept this as a good approach in an ideal world, then I think we need to ask ourselves if we should implement the remaining ALTER commands in 8.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. -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 04:20 AM 20/10/2004, Tom Lane wrote: Nope. I can break that trivially, eg: Thats why in my first message I mentioned escaping and unescaping all '%' in the deinition. There's also the nontrivial matter of how pg_dump would decide where to insert the %%tablespace%% string into the CREATE INDEX command in the first place. I'd vote against parsing, and add a parameter to get_indexdef. If we're going to add code to parse CREATE INDEX and insert the tablespace in the correct place, meseems it'd be better to insert it on the pg_restore side. But if we have to 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. -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 03:25 AM 20/10/2004, Tom Lane wrote: Maybe there's something I don't understand. How are you expecting pg_restore to control whether it outputs the command with a TABLESPACE clause embedded or not, if pg_dump has already built the command string that way? This will only work if we modify the dump format (a new version) of dump/restore; the TOC entry for a table would have: DEFINITION: CREATE TABLE fred ... %%tablespace%% ... TABLESPACE: ' TABLESPACE t' pg_restore would read these, and use the settings from the command line to either substitute an empty string or the TABLESPACE text for %%tablespace%% in the DEFINTION. Same would apply for indexes etc. -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 03:06 AM 20/10/2004, Tom Lane wrote: I think the tricky part of that would be inserting the tablespace clause in the right place; for CREATE INDEX this seems to require nontrivial parsing. (Both the index column definitions and the WHERE clause could be arbitrarily complicated expressions.) If we can get around that part then this wouldn't be too hard. I may be missing something here; I was assuming that pg_dump would dump would build the CREATE INDEX/TABLE/etc commands with the %%tablespace%% already embedded. pg_restore would not need to do any parsing. Or is there something I don't understand? -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Using ALTER TABLESPACE in pg_dump
At 03:15 AM 19/10/2004, Bruce Momjian wrote: The only downside is that it prevents SQL-compliant CREATE syntax in dumps. One idea that may be worth considering: we currently dump a complete SQL statement including a TABLESPACE clause, which makes it hard to allow pg_restore to have a 'no tablespaces' option (it is fixed by pg_dump). To solve this, we should dump the table definition as a format string and dump the tablespace clause (ie. ' TABLESPACE ts') as a separate part of the table definition TOC entry. If the user wants the tablespace to be dumped, then we substitute the tablespace clause, otherwise a blank string. This could be a useful general approach in the future. The main issue will be quoting the clause identifiers (if we use '%%tablespace%%' then we have to handle columns or tables whose names contain '%%tablespace%%'. Simplest would be to backslash quote all '%' and '\' then do subs. Also, I like the option of a soft-tablespace option, but also liked the idea of the fake/logical/virtual tablespaces someone suggested earlier; if restoring into a database without a required tablespace, 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 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump as a bunch of PostgreSQL functions
At 06:00 PM 14/09/2004, Mark Gibson wrote: I have an idea, to break pg_dump into functions within PostgreSQL. This has been suggested before, and I think been generally accepted as the right broad approach (the basic idea that the pg backend should know how to describe itself). Recent versions of pg_dump have started using backend functions to dump database structures (eg. type definitions). As time goes by more functions will be written, but I don't think it's the highest priority on anybody's list. There are also the information schemas which are the ISO way of getting database definitions; these can/should be used where possible. However, there are some complications because pg_dump is also the upgrade tool; the backed can only know how to describe itself for the current dialect of SQL accepted by PG. As we upgrade and improve the SQL, and add features, pg_dump needs to talk to old backends and dump prior versions in a format compatible with current (new) versions. This means that for some purposes it will not be able to use backend functions, or at least will have to have it's own mutant version of them. There are other differences; for reasons of performance and atomicity, we try to keep the items dumped as simple as possible. eg. in 8.0, a table definition will ultimately be dumped as: 1. set default_tablespace=xxx 1. set search_path=xxx 2. create table (no constraints, tablespace or namespace clauses) 4. load table data 3. alter table add constraint... 5. set table acls A 'friendly' definition would at least contain the namespace & constraints, but pg_dump does not want that. So it's not a simple as it sounds. Perhaps it would be nice if, in each new version we created a library that could be built against old versions to provide the functions needed by pg_dump to upgrade, and a similar library would form part of the new version as well. Kind of a 'pg_dump translation plugin'. This may be way too expensive an option, when a few 'if' statements inside pg_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| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_restore segfault with pg-CVS
At 06:55 PM 14/09/2004, strk wrote: Current pg_restore segfaults. You might need to give a little more information; does it only segfault for a specific DB? Does it segfault for a trivial empty DB? Can you construct a minimal example? 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] open item: tablespace handing in
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) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] open item: tablespace handing in
At 08:53 PM 1/09/2004, Christopher Kings-Lynne wrote: Did you deal with the pg_get_indexdef problem where it automaticlaly adds the tablespace in index definitions? No; the SET stuff is not there, and Tom said he'd deal with the backend side of things when he gets a chance. Won't be 'till beta2. -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] open item: tablespace handing in
At 06:31 PM 1/09/2004, Fabien COELHO wrote: I've noticed that the item does not seem to appear in Bruce's list, thus I'm afraid it might be lost for 8.0 where I think it belongs... hence this little reminder. Sounds good; I've implemented using SET in pg_dump/restore, just waiting for the command 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) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] FYI: 8.0beta2 on Monday
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 Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] compile warnings in HEAD
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| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ pg_backup_db.c.patch.gz Description: Binary data ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] New warning in pg_dump
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. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] New warning in pg_dump
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| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] New warning in pg_dump
At 01:58 AM 24/08/2004, Alvaro Herrera wrote: static int _isIdentChar(unsigned char c) I think the correct thing to do is to leave it as (signed) char, and remove the comparison to \200 = -127. All chars will be >= -127. I will fix this in the next release. ---- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] compile warnings in HEAD
At 12:11 PM 24/08/2004, Neil Conway wrote: I get the following warnings compiling current sources: My fault; I'll fix it in the next patch to pg_dump etc. I compare a char to the range \200 through \377. It must always be >= \200...at least I assume that's the problem. ---- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] ALTER SCHEMA ... SET TABLESPACE
At 03:15 PM 23/08/2004, Tom Lane wrote: I thought the idea was for pg_dump to emit something like SET magic_tablespace_variable = some_ts; CREATE TABLE foo (columns...); rather than CREATE TABLE foo (columns...) TABLESPACE some_ts; the point being... Yep. I am quite unsure why you are pushing this while also insisting that we need "die_on_errors" mode for pg_restore. Because I expect scripts to die when they produce errors, and find the recent change to be a step backward. If you are going to die on the first error then these alternatives are equally brittle. Because I am told that many people like to continue regardless of errors, in which case allowing tables to be created is way more useful & helpful. The same is true for database & schema creation. On the question of schemas, how would you expect: SET magic_tablespace_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.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] ALTER SCHEMA ... SET TABLESPACE
At 02:28 PM 23/08/2004, Tom Lane wrote: There's been some talk of that, but AFAICS it's not related to an ALTER SCHEMA SET [DEFAULT] TABLESPACE command. So, if you do make the changes, will the schema definition be affected by those changes, or do you expect the tablespace to be embedded in the CREATE SCHEMA command? (And no, I've not yet lifted a finger on this, though I'm willing to handle the backend side of it if there's consensus to handle dumping this way.) Let me know when consensus happens. I've got a patch waiting on the syntax of the SET command. Otherwise, I'll need to use alter commands. -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] ALTER SCHEMA ... SET TABLESPACE
At 01:48 PM 23/08/2004, Marc G. Fournier wrote: Won't the dump/restore "store" a "CREATE SCHEMA" onto the new tablespace? Why would a dump/restore issue an ALTER SCHEMA part way through? My *belief* is that if the tablespace does not exist, then the restore (which creates the schema and refers to the non-existent tablespace) will fail to create the schema. We've had the same problem with CREATE TABLE statements. Tom is (I think) working on a SET DEFAULT_TABLESPACE command so that CREATE TABLE no longer refers to the tablespace. Not sure if it will apply to databases or schemas though. -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] ALTER SCHEMA ... SET TABLESPACE
At 01:04 PM 23/08/2004, Marc G. Fournier wrote: not having ALTER SCHEMA to move tablespaces is not a bug But it does make pg_dump/restore more inclined to fail, so increases the incidence of another bug, which can not be fixed without a global SET DEFAULT TABLESPACE or an ALTER SCHEMA. 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] ALTER SCHEMA ... SET TABLESPACE
At 06:07 AM 21/08/2004, Bruce Momjian wrote: I am inclined to agree. ALTER INDEX is an operation that will happen quite often, One argument for doing it in this release is pg_dump/restore. Do we want pg_dump to dump the CREATE SCHEMA followed by ALTER SCHEMA? Or will the SET DEFAULT TABLESPACE work on schemas? 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] tablespace and sequences?
At 03:14 PM 20/08/2004, Tom Lane wrote: If we attempt to reload this mess with a different default tablespace for the parent object, what happens to the child in each case? ISTM that for a table create with CREATE TABLE...TABLESPACE we should try to preserve the tablespace when doing a dump/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 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] tablespace and sequences?
At 06:14 PM 20/08/2004, Fabien COELHO wrote: This prior SET option looks much better and cleaner. Maybe the TOC entry update is not really necessary if the SET is separate? I'd prefer if it was separate since we want to minimize the number of multi-statement TOC entries...I think. A new TOC entry is close to zero cost. Reformatting the TOC to include the tablespace name is more expensive, but there are a few things I'd like to add, so it's worth it. If the SET fails, what tablespace is expected to be chose? Good question. Is there a name for the normal/default/whatever tablespace? Tom may need to implement: SET DEFAULT TABLESPACE AS FRED SET DEFAULT TABLESPACE DEFAULT or something less tacky, but allowing for the default to be derived from the schema & database rather than the last SET command. The pg_dump will need to check the result of the SET command and reset the tablespace if it fails...and probably die if that fails. I can give a hand about the implementation over the week-end, esp. as I'm the one taking a stand on this issue. However I do not know much about pg_dump format and issues, so I'm not sure I'm the 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| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] tablespace and sequences?
At 02:27 PM 20/08/2004, Tom Lane wrote: Actually I think we'd just revert the ruleutils.c Just to confirm; it's only tables and indexes that have tablespaces, and I can issue some kind of SET command. Any idea of the syntax? As an aside: should a database be allowed to have a default tablespace? -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] tablespace and sequences?
At 01:47 PM 20/08/2004, Tom Lane wrote: But someone needs to take a close look at pg_dump's logic to see if this can work. Not sure where the issues lie, but anything that can reside in a tablespace (table, index,...anything else?), needs to dump it's definition without reference to a tablespace, and pg_dump needs to be modified to dump the tablespace name in the TOC entry, and pg_restore needs to maintain 'current' tablespace the same way it does schemas. Backend then needs to obey the variable setting. What have I missed? I can do the pg_dump stuff if noone else wants to. -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] tablespace and sequences?
At 01:09 PM 20/08/2004, Tom Lane wrote: It seemed like a reasonable idea to me... Do we have a "SET DEFAULT TABLESPACE"? Can we add one for this release? If not, we probably need to go with the ALTER TABLE. Although a SET DEFAULT TABLESPACE would be convenent in general. -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] tablespace and sequences?
At 12:37 PM 20/08/2004, Bruce Momjian wrote: But that doesn't fix ascii dumps loaded via psql. It does; the ascii dump file is generated by exactly the same technique as pg_restore. Internally, pg_dump builds a TOC, then calls RestoreArchive to dump the text. It was designed this way for a bunch of reasons, and one was to avoid too much difference between the output of each format. Which is why it is very unlikely that "pg_dump -Fc | pg_restore" would produce output substantially different from that of "pg_dump". So, as long as pg_dump puts the CREATE 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) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] tablespace and sequences?
At 02:33 AM 20/08/2004, Bruce Momjian wrote: > Could we 'set default tablespace xxx', then have pg_dump/restore use a > 'create table' that does not refer to the tablespace? That is what I was assuming. You can't retroactively change the dump file during restore so we would have some SET varaiable you would set before doing the restore that said to handle create tablespace errors. Actually I was thinking of a little more than a setting to ignore errors; we would need to: - modify pg_dump to store the tablespace name as a separate part of the TOC entry, NOT as part of the CREATE TABLE. - modify pg_restore to issue 'set default tablespace ' before restoring a table OR, per Fabiens suggestion, issue an ALTER TABLE after the create. Then table-space related errors 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 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] tablespace and sequences?
At 12:21 AM 20/08/2004, Tom Lane wrote: You can give it a new paint job in 8.1, if you like. To side-step the issue, is there a tablespace equivalent of a default schema? Could we 'set default tablespace xxx', then have pg_dump/restore use a 'create table' that does not refer to the tablespace? -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Does psql use nested transactions?
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 of the following commands is irrelevant. -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Does psql use nested transactions?
At 04:44 AM 18/08/2004, Bruce Momjian wrote: it turns off when the transaction completes? Not sure I like this part; I would like to be able to set the variable at the start of a psql session and have it run for the entire session. Or, even better, set it in a psql initialization file. Also, 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) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Does psql use nested transactions?
At 07:10 AM 18/08/2004, Tom Lane wrote: I have not had an opportunity to benchmark it but I fear a savepoint may cost near as much as a full xact in practice. Out of curiosity, does this mean that using a savepoint per statement will be no worse than using psql in autocommit mode? 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump 'die_on_errors'
At 01:32 AM 16/08/2004, Tom Lane wrote: It'd be substantially *more* helpful if it reported the failing command. They are two different problems; the TOC entry is important for any multiline command or to rerun the command easily later. Whereas displaying the failed SQL command is a matter of fixing the error messages. The latter is complicated by failed COPY commands which, with die-on-errors off, results in the data being processed as a command, so dumping the command will dump all of the data. In the case of long commands, should the whole command be dumped? eg. (eg. several pages of function definition). In the case of the COPY command, I'm not sure what to do. Obviously, it would be best to avoid sending the data, but the data and command are combined (from memory). Also, the 'data' may be in the form of INSERT statements. Attached patch produces the first 125 chars of the command: pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC Entry 26; 1255 16449270 FUNCTION plpgsql_call_handler() pjw pg_restore: [archiver (db)] could not execute query: ERROR: function "plpgsql_call_handler" already exists with same argument types Command was: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '/var/lib/pgsql-8.0b1/lib/plpgsql', 'plpgsql_call_han... pg_restore: [archiver (db)] Error from TOC Entry 27; 1255 16449271 FUNCTION plpgsql_validator(oid) pjw pg_restore: [archiver (db)] could not execute query: ERROR: function "plpgsql_validator" already exists with same argument types Command was: CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS '/var/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 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ pg_dump_040815-1.patch.gz Description: Binary data ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_dump 'die_on_errors'
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; 1255 16438403 FUNCTION foo() pjw pg_restore: [archiver (db)] could not execute query: ERROR: no schema has been selected to create in pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" does not exist pg_restore: [archiver (db)] Error from TOC Entry 1475; 0 0 ACL public postgres pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" does not exist pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" does not exist pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" does not exist pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" does not exist -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ pg_dump_040815.patch.gz Description: Binary data ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Dump Version
At 10:30 PM 13/08/2004, strk wrote: >From PG73: 552; 2663984 CAST bytea (public.wkb) >From PG74: 547; 1915318 CAST CAST (public.wkb AS bytea) Since these two dumps have same file format version I asked.. The file format (unfortunately for you) does not define the format of the contents of the TOC entries. It may be messy, but you could use your source sql file to create an empty database in the same PG version, then do: pg_dump -Fc tempdatabase | pg_restore -l to get a compatible list. But you'll still have mismatched OIDs, and a temp database to create/cleanup. I 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) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Dump Version
At 08:53 PM 13/08/2004, strk wrote: Commenting out lines from the dump corresponding to objects that will be defined by another script. I may have misunderstood, but try: pg_restore -l dumpfile > listfile then delete lines from listfile that you do not want, and do: pg_restore -L listfile 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) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Dump Version
At 08:09 PM 13/08/2004, strk wrote: What does 'Dump Version' refer to then ? Are these ToC output changes encoded somehow ? The file format version of pg_dump; the actual contents depend on pg_dump version, and the source database since pg_dump asks the source (as much as possible) 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) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_restore (libpq? parser?) bug in 8
At 11:42 PM 12/08/2004, Tom Lane wrote: That's a possibility, but I'd rather work around it by finding a way for pg_restore not to need to parse the dollar-quoted literal in the first place. Two possibilities: 1) Parse the tags (I have the code working): it's not that hard, the only trick bit being recognizing the tags in the first place. I have assumed that any bare unquoted string that is not preceded by valid identifier name chars, and which starts with a '$' may be a dollar quote. This seems valid to me. 2) We could avoid special coding for TOC entry types (eg. pg_restore knowing 'FUNCTION' TOC entries should not be parsed), by changing the TOC data to include a flag/counter (set by pg_dump) indicating that the entry contains > 1 statements. Then we don't hard code knowledge of TOC entry types, and function definitions will not 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| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pg_restore (libpq? parser?) bug in 8
At 12:15 PM 12/08/2004, Tom Lane wrote: You might need to bite the bullet and implement a flex lexer. I'd like to avoid this if I can; AFAICT, for statement detection on pg_restore, I can require white space before the $tag. Since I also skip over all quoted text, the bodies of functions are ignored. The only issues will be attribute names with ' $' in them, but they will be quoted as well (so ignored). So to recognize a tag, I look for a '$' after white space, and assume it's a tag start. If I subsequently read an invalid tag char, I just go back into scan 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| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_dump 'die_on_errors'
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) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] dollar-quoting in psql and in general
At 01:07 PM 12/08/2004, Andrew Dunstan wrote: \ is not magical inside dollar quotes, Sorry, I was confused by the manual: the paragraph that starts "C-style backslash escapes are also available..." is right after the paragraphs on dollar-quoting. The section on dollar-quoting is 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 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] dollar-quoting in psql and in general
At 12:47 PM 12/08/2004, Tom Lane wrote: Backslashes are not special inside dollar quotes --- nor is anything else, except the matching close tag If they are not special, then shouldnt: select $a$\$a$; result in ?column? \ rather than an error? Oops. It does. 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] dollar-quoting in psql and in general
At 12:47 PM 12/08/2004, Tom Lane wrote: Backslashes are not special inside dollar quotes --- nor is anything else, except the matching close tag If they are not special, then shouldnt: select $a$\$a$; result in ?column? \ rather than an error? 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_restore (libpq? parser?) bug in 8
At 12:42 PM 12/08/2004, Tom Lane wrote:
Hm. But we could assume that a COPY will be all by itself in a TOC
entry, couldn't we?
Maybe. I know I hit a couple of nasty examples in the original code. Isn't
the COPY combined with the data? If so, we still have to scan for it's end.
The existing scanner is pretty trivial. The dollar-quoting will not make it
much worse (I hope). But I'll hold off on the changes if you want to
experiment -- I used to use my own DBs + the regression DB for testing.
Another possible issue - if I pass two statements in one string to libpq,
separated by semicolons, will it cope? If so, has that been true since 7.0?
If the answers are ('no',_), or ('yes', 'no') then that explains why
pg_restore has to parse statements - the TOC entry can have more than one
statement.
Sorry to 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 | _ \
Fax: (+61) 03 5330 3172 | ___ |
Http://www.rhyme.com.au |/ \|
|----
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_restore (libpq? parser?) bug in 8
At 12:15 PM 12/08/2004, Tom Lane wrote: Why exactly does pg_restore need to parse the SQL anyway? It just looks for complete statements. From memory it relates to the possibility that TOC entries can have more than one statement, or it may relate to handling COPY statements. I think it has 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. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] pg_restore (libpq? parser?) bug in 8
At 12:15 PM 12/08/2004, Tom Lane wrote: IIRC we tried to do ad-hoc code for dollar quoting in psql, and gave it up as a bad job. You might need to bite the bullet and implement a flex lexer. Looks like the psql side of things is not ideal (see other post). Any idea how backslashes should handled 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 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] dollar-quoting in psql and in general
While trying to understand dollar-quoting, I found the following in psql: select $a$ hello $a$; behaves as expected, but psql does not like select $a$ \ $a$; or select $a$ \\ $a$; Should it? How should a dollar-quote handle: $a$ \$a\$a $a$ ? 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_dump 'die_on_errors'
At 02:31 AM 12/08/2004, Tom Lane wrote: result of considerable experience that says die_on_errors is NOT the right behavior for pg_restore. Can you point me to examples? Is it just an expectation problem that could be fixed with better docs? I tend to expect scripts to die when they encounter an error. 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_dump 'die_on_errors'
At 02:33 AM 12/08/2004, Fabien COELHO wrote: Maybe the time has come;-) Sounds good to me. We've had the original behaviour since 7.1, I can understand there may be a desire to make it consistent with the carr-on-regardless behaviour of psql, but changing it in one release without the ability to revert to old behaviour is not ideal. BTW, Why is the default behavior such a pain? I expect a script (shell, perl, or sql) to die when it hits an error; carr-on-regardless is IMO dangerous and just a hangover from piping to psql. One possible problem is illustrated by: - dump a db - use pg_restore in 'create' mode - for some reason DB creation fails result: template1 (or other DB) ends up with junk. Or ends up with deleted tables if the initial connection was to a db with the same table names. One of my motivations in doing the original pg_dump restructure and custom 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) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_restore (libpq? parser?) bug in 8
At 03:33 AM 12/08/2004, Tom Lane wrote: Do you have time to fix it? Should do; I'll add the die-on-error option as well. Con someone confirm how dollar quoting works: '$[tag]$' where tag is alpha chars? any chars? \n? \r? and closing tag must match. All dollar-quotes inside any kind of quotes can be ignored. Is there any circumstance where an unquoted '$' is valid? -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] pg_restore (libpq? parser?) bug in 8
Just found this: createdb zzz createlang plpgsql zzz psql zzz create function xxx() returns integer as $$ begin return 1; end; $$ language plpgsql; \q pg_dump -Fc zzz > zzz.bck dropdb zzz createdb zzz createlang plpgsql zzz pg_restore --function="xxx()" -d zzz zzz.bck results in 'unterminated dollar-quoted string' errors. Doing: pg_restore --function="xxx()" zzz.bck > zzz.sql then running the sql file, works fine. The statements that are sent to the backend (using log_statements) by pg_restore include the comment strings. Not sure if this is relevant. The sql is: -- -- PostgreSQL database dump -- SET client_encoding = 'LATIN1'; SET check_function_bodies = false; SET search_path = public, pg_catalog; -- -- Name: xxx(); Type: FUNCTION; Schema: public; Owner: birds -- CREATE FUNCTION xxx() RETURNS integer AS $$ begin return 1; end; $$ LANGUAGE plpgsql; ALTER FUNCTION public.xxx() OWNER TO birds; -- -- PostgreSQL database dump complete -- The error log is: 2004-08-12 01:38:48 EST: LOG: statement: -- -- PostgreSQL database dump -- SET client_encoding = 'LATIN1'; 2004-08-12 01:38:48 EST: LOG: statement: SET check_function_bodies = false; 2004-08-12 01:38:48 EST: LOG: statement: SET search_path = public, pg_catalog 2004-08-12 01:38:48 EST: LOG: statement: -- -- Name: xxx(); Type: FUNCTION; Schema: public; Owner: birds -- CREATE FUNCTION xxx() RETURNS integer AS $$ begin return 1; 2004-08-12 01:38:48 EST: ERROR: unterminated dollar-quoted string at or near "$$ begin return 1;" at character 115 2004-08-12 01:38:48 EST: LOG: statement: end; 2004-08-12 01:38:48 EST: WARNING: there is no transaction in progress 2004-08-12 01:38:48 EST: LOG: statement: $$ LANGUAGE plpgsql; 2004-08-12 01:38:48 EST: ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE plpgsql;" at character 2 2004-08-12 01:38:48 EST: LOG: statement: ALTER FUNCTION public.xxx() OWNER TO birds; Not sure I see the problem; I can only guess it may relate to the whole comments being passed. Running the extracted sql in psql gives the following log: 2004-08-12 01:45:02 EST: LOG: statement: SET client_encoding = 'LATIN1'; 2004-08-12 01:45:02 EST: LOG: statement: SET check_function_bodies = false; 2004-08-12 01:45:02 EST: LOG: statement: SET search_path = public, pg_catalog; 2004-08-12 01:45:02 EST: LOG: statement: CREATE FUNCTION xxx() RETURNS integer AS $$ begin return 1; end; $$ LANGUAGE plpgsql; 2004-08-12 01:45:02 EST: LOG: statement: ALTER FUNCTION public.xxx() OWNER TO birds; 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] pg_restore (libpq? parser?) bug in 8
CREATE FUNCTION xxx() RETURNS integer AS $$ begin return 1; 2004-08-12 01:38:48 EST: ERROR: unterminated dollar-quoted string at or near "$$ begin return 1;" at character 115 Just realized the problem; pg_restore uses a trivial parser to work out when statements 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) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Does psql use nested transactions?
At 01:47 AM 12/08/2004, Tom Lane wrote: It might be reasonable to offer that behavior as an option, but I think I'd only want it on for interactive input. My preference would be for something like: set savepoint_per_statement=true then interactive & scripts could choose to use either feature. ---- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] pg_dump 'die_on_errors'
The default setting of 'false' is a pain. And the fact it can't be changed by an option is even more of a pain. Any objections to adding an option to pg_restore to allow 'die_on_errors' to be set to 'true'? -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 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Does psql use nested transactions?
In 8.0 beta 1 I just tried: psql template1 > begin; > select * from pg_class; ... got stuff ... > select * from aaa; ERROR: relation "zzz" does not exist > select * from pg_class; ERROR: current transaction is aborted Should psql run every 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| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pg_dump bug fixing
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) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] pg_dump bug fixing
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 Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Fixes for pg_dump and ownership/acl problems
At 12:19 PM 29/06/2004, Christopher Kings-Lynne wrote: It's hacky (although officially hacky :) ) in regards that it parses the dropStmt on each object to get the objects name. This is necessary because of an oversight in the initial design of the binary format. If there's something missing in the format, I'd be inclined to add it. We've never promised to make dumps made by version X readable (or usable) by version X-1. We *do* the opposite, however, so you will probably still need to parse dump statements from old dump files. Unless we can back-patch... So, rather 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) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_dump --comment?
At 12:15 AM 29/05/2004, Jon Jensen wrote: However, I would like to see an option to include the timestamp if someone wants it. The timestamp is already in the header for custom dumps. Have a look at the first few lines from a 'pg_restore -l'. In terms of general comments, there is already a 'WARNING' type TOC entry for custom dumps. This emits a warning message when pg_restore is called. A similar mechanism could be used to dump SQL comments. eg. new TOC type of 'SQL_COMMENT' with a defn starting with '--' would work with old & new pg_dump versions. It might be a good idea to investigate the likely uses for these comments, since the TOC entries may be suppressed depending on whether they are deemed to be part of the schema or the data. Once the modes of use are defined, 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 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] cvs: mislinked plpgsql.so ?
At 02:05 AM 29/02/2004, Tom Lane wrote: Your plpgsql.so may be CVS-tip, but your backend isn't... that function was just added a few days ago. I just got this error after upgrading to 7.4.2; I assume it may be because an old library was still present in memory, but wanted to check. 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
At 01:30 AM 4/05/2004, Tom Lane wrote: can only occur if other transactions running parallel to the ANALYZE perform sufficient catalog updating activity to fill the sinval message queue. And there must also be at least one long-term-idle backend, so that the queue doesn't get drained. Sounds quite likely; usually seems to occur at 'shoulder' load times; lots of updates still happening (several each second) and a server process pool that is larger than necessary to handle the load. I'll replace all: heap_openr(ListenerRelationName, AccessExclusiveLock); with heap_openr(ListenerRelationName, ExclusiveLock); and see how it goes. Thanks for the help. -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
At 11:04 PM 3/05/2004, Tom Lane wrote: Hm. What seems likely to have happened is that the sinval message queue got full. I agree (our emails crossed). That would have left all the idle backends trying to get exclusive lock on pg_listener, and if the ANALYZE subsequently reached pg_listener, its share lock would queue up behind those requests. What I see is that the ANALYZE job already has it in ACCESS SHARED mode, and keeps the lock until it dies with the 'concurrent update' error. What is not clear yet is why *all* of them are blocked. Seems something else must have some kind of lock already on pg_listener; but who? ANALYZE. Can you get a dump of the pg_locks view while this is happening? Attached. How confident are you in those "processes"? I don't know of any other mechanism for 'tuple concurrently updated' failures in ANALYZE than concurrent analyze runs ... Fairly. In this particular instance the error was probably caused bu a manually run VACUUM (part of me stressing it to encourage the error). Contrary to my other email, we haven't had the 'tuple concurrently updated' error since March (until today, with me messing around). What I do have is minute-by-minute dumps of pg_locks and ps for the day. At each hang there were many processes in 'async_notify waiting' and an ANALYZE job had the lock in shared mode. I do not have minute-by-minute logs for more than today, but there were 3 hangs today, and only one with the concurrent update error. It would be interesting if we could find a piece of backend code that did a 'select * from pg_listener', and hence locked it in ACCESS SHARED. At the moment, it looks like either the ANALYZE is triggering an error that causes it's 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? ---- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ 18-02-hackers.dat.gz Description: Binary data ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
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 to the postmaster. The postmaster will send * a SIGUSR2 signal (ordinarily a NOTIFY signal) to all the backends. * This will force idle backends to execute a transaction to look * through pg_listener for NOTIFY messages, and as a byproduct of the * transaction start they will read SI entries. * * This should never happen if all the backends are actively executing * queries, but if a backend is sitting idle then it won't be starting * transactions and so won't be reading SI entries. * * dz - 27 Jan 1998 */ Would a long-running ANALYZE (or other activity on a busy database) cause the shared buffers to get to the 70% threshold while doing a long-running ANALYZE? -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
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 emails until someone responds ;-) -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
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. -------- 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
Further to this, ProcessIncomingNotify seems to hold the lock on the listener relation until it's current transaction exits. If the ANALYZE was not the source of the error, but was just another victim, does that mean it might hold the lock for a very long time if the analyze is lengthy? At 02:54 PM 3/05/2004, Tom Lane wrote: Please dig deeper. I may have found the problem; all the hung processes show 'async_notify waiting' in ps, and the ANALYZE eventually dies with 'tuple concurrently updated'. The routine 'ProcessIncomingNotify' in async.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 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
At 02:54 PM 3/05/2004, Tom Lane wrote: Please dig deeper. I may have found the problem; all the hung processes show 'async_notify waiting' in ps, and the ANALYZE eventually dies with 'tuple concurrently updated'. The routine 'ProcessIncomingNotify' in async.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 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ANALYZE locks pg_listener in EXCLUSIVE for long
At 02:54 PM 3/05/2004, Tom Lane wrote: Please dig deeper. I will log everything I check next time; unfortunately, when it happens, the priority is on unlocking everything so I have a limited time to play. So far, killing the ANALYZE has fixed the problem each time. 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://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp.mit.edu:11371 |/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
