Re: [GENERAL] dblink() cursor error/issue (TopMemoryContext)
On Mon, June 2, 2008 6:53 pm, Tom Lane wrote: > I don't think your problem has anything to do with dblink per se. > The repeated begin/exception blocks are apparently managing to leak > some memory per iteration. I can't tell whether this represents > a known (and perhaps already fixed) bug; it very likely depends on > details you haven't shown us. Do you want to try to put together a > self-contained test case? Sorry for the delay in responding to this. It looks like there's definitely a memory leak problem in 8.2.6 when executing begin/exception in a loop. After upgrading to 8.3.1, the same code ran to conclusion without error. One other thing: the docs mention that functions use cursors automatically to prevent OOM errors on large selects (as in my case). Well, the second part of my function does this: FOR rec in SELECT * FROM bigtable LOOP ...begin/insert/exception... END LOOP; and bang, OOM. This is in 8.3.1. I'll rewrite this to use cursors, but was hoping to take advantage of the implicit cursors to keep the code nice and simple... or am I misunderstanding "...FOR loops automatically use a cursor internally to avoid memory problems." from section 37.8 in the manual? Regards Henry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dblink() cursor error/issue (TopMemoryContext)
"Henry - Zen Search SA" <[EMAIL PROTECTED]> writes: > One other thing: the docs mention that functions use cursors > automatically to prevent OOM errors on large selects (as in my case). > Well, the second part of my function does this: > FOR rec in SELECT * FROM bigtable > LOOP >...begin/insert/exception... > END LOOP; > and bang, OOM. How soon is "bang"? The above causes one subtransaction to be instantiated per loop execution, since we have to have a new XID for each inserted row (else it's not possible to roll back just that row on failure). The memory overhead per subtransaction is not zero, though I think it's fairly small if you don't have any triggers pending as a result of the insert. (Hm ... any foreign keys on the table being inserted into?) > This is in 8.3.1. I'll rewrite this to use cursors, but > was hoping to take advantage of the implicit cursors to keep the code nice > and simple... or am I misunderstanding "...FOR loops automatically use a > cursor internally to avoid memory problems." from section 37.8 in the > manual? The FOR loop is not your problem. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Backup using GiT?
I have recently had to teach myself how to use git and the thought came to me that this tool might provide a fairly low setup cost way of passing pg_dumps over the network to our off site data store. Think Rsync, but on a file content basis; just the content diff gets transmitted. GiT works by compressing deltas of the contents of successive versions of file systems under repository control. It treats binary objects as just another object under control. The question is, are successive (compressed) dumps of an altered database sufficiently similar to make the deltas small enough to warrant this approach? Comments? (not my my sanity, please) -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:[EMAIL PROTECTED] Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup using GiT?
James B. Byrne wrote: > GiT works by compressing deltas of the contents of successive versions of file > systems under repository control. It treats binary objects as just another > object under control. The question is, are successive (compressed) dumps of > an altered database sufficiently similar to make the deltas small enough to > warrant this approach? Hmm, perhaps a not completely insane thing to do would be to use another pg_dump "format" (i.e. a new -F option) which installs stuff on a GIT repo. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Source RPM for 8.3.3?
I was browsing the postgresql download site, but I wasn't able to find the source RPM for 8.3.3. Is it available on the site? Thanks, Kevin
[GENERAL] Lost psql.exe on 8.3.3 upgrade
Windows 2000 Prof. Used upgrade option, not full install.
I upgraded my 8.3.1 to 8.3.3. Looks like all went ok except I had no
psql.exe in my bin dir.
I searched for it & found it in
C:\WINNT\Installer\{B823632F-3B72-4514-8861-B961CE263224} dir.
It is the 8.3.3 version. When I copied it to my bin dir, all worked
ok. Is this an installer bug, & if so, is it possible any other files
got misplaced?
Thanks for any tips,
Dennis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dblink() cursor error/issue (TopMemoryContext)
On Fri, June 13, 2008 7:05 pm, Tom Lane wrote: > How soon is "bang"? I'll run it again and post back. > The memory overhead per subtransaction is > not zero, though I think it's fairly small if you don't have any > triggers pending as a result of the insert. Two triggers are fired for each insert (before and after trigs). > (Hm ... any foreign keys on the table being inserted into?) Not on the table being inserted into, but it has a couple of tables with foreign keys referencing it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup using GiT?
James B. Byrne wrote: I have recently had to teach myself how to use git and the thought came to me that this tool might provide a fairly low setup cost way of passing pg_dumps over the network to our off site data store. Think Rsync, but on a file content basis; just the content diff gets transmitted. GiT works by compressing deltas of the contents of successive versions of file systems under repository control. It treats binary objects as just another object under control. The question is, are successive (compressed) dumps of an altered database sufficiently similar to make the deltas small enough to warrant this approach? Comments? (not my my sanity, please) It probably depends on the number of changes in the database. For example, a vacuum followed by an insert could result in records that were previously at the start of the dump being somewhere else -like the middle of the dump (i.e., a dead tuple is marked as available, then the space is "used" for an insert). In such a case, you would end up with a row that was unchanged, but in a different location in the file. Would GIT then back that up? I would think so. So in essence you'd be getting "at least a diff, but likely more" . Of course, I'm assuming you are just dumping the data in a table using pg_dumponce you start talking about a dumpall, you might find that smaller changes (i.e., give a user a new privilege) causes stuff to be offset more Add compression into the mix and I think you could find that there are little/no similarities.. On the other hand, if you were only doing inserts into an optimized (no dead tuples) table, I would think that you'd get a much better result. Perhaps you would be better off using PITR in such cases? -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/877-258-8987 http://www.otg-nc.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup using GiT?
"James B. Byrne" <[EMAIL PROTECTED]> writes: > I have recently had to teach myself how to use git and the thought came to me > that this tool might provide a fairly low setup cost way of passing pg_dumps > over the network to our off site data store. Think Rsync, but on a file > content basis; just the content diff gets transmitted. Huh? rsync does within-file diff transmission already. > GiT works by compressing deltas of the contents of successive versions of file > systems under repository control. It treats binary objects as just another > object under control. The question is, are successive (compressed) dumps of > an altered database sufficiently similar to make the deltas small enough to > warrant this approach? No. If you compress it, you can be pretty certain that the output will be different from the first point of difference to the end of the file. You'd have to work on uncompressed output, which might cost more than you'd end up saving ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] dblink() cursor error/issue (TopMemoryContext)
"Henry - Zen Search SA" <[EMAIL PROTECTED]> writes: > On Fri, June 13, 2008 7:05 pm, Tom Lane wrote: >> The memory overhead per subtransaction is >> not zero, though I think it's fairly small if you don't have any >> triggers pending as a result of the insert. > Two triggers are fired for each insert (before and after trigs). The after trigger is probably the main source of the problem --- have you any way of avoiding that? (I wonder whether we can't improve on that, at least for the case of a non-deferred after trigger, which'd have already been fired before we exit the subtransaction. The trick is how to know that there's nothing useful left in the subtransaction's per-transaction memory context ...) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backup using GiT?
Tom Lane wrote: > "James B. Byrne" <[EMAIL PROTECTED]> writes: > > GiT works by compressing deltas of the contents of successive versions of > > file > > systems under repository control. It treats binary objects as just another > > object under control. The question is, are successive (compressed) dumps of > > an altered database sufficiently similar to make the deltas small enough to > > warrant this approach? > > No. If you compress it, you can be pretty certain that the output will > be different from the first point of difference to the end of the file. > You'd have to work on uncompressed output, which might cost more than > you'd end up saving ... The other problem is that since the tables are not dumped in any consistent order, it's pretty unlikely that you'd get any similarity between two dumps of the same table. To get any benefit, you'd need to get pg_dump to dump sorted tuples. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Win32 8.3.3 install fail (sufficient privileges to install system services).
I have found in most instances if you leave the services window open the installer will fail to install (or upgrade) the postgres service. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] file system level backup
@craig i am on mac os x 10.5 and i installed via macports using robby russel pg install guide. here is the initdb... Init your new PostgreSQL database with: initdb -D pgdata Start up PostgreSQL with: pg_ctl -D pgdata -l pgdata/psql.log start Create a new database with: createdb argon_development Test the new database with: psql argon_development Did it load up your new database? If so, great! If not… check your steps… :-) so i read the pg manual and figured the db path should be what i tried and then replace the path from another instruction. which didn't work. i also tried the tar db path and move it to the new location. that worked but i think it changes the ownership. i did chown posgres (user) on the folder, but i don't think it applies to the contents of the folder. that could be my problem. i will look at the .conf file and then try the pg_dump and pg_restore. once you do a pg_dump will it leave the current db at it states. i was concerned that it might do some damage and then my data would be destroyed. i also read about -o during pg_dump but frankly i didn't understand it. yes, i am using foreign keys in ruby. i really don't understand the 2nd half pg_restore, do you copy the file and move it to the new server, if so where, before you run pg_restore. thanks. On Jun 12, 5:54 pm, [EMAIL PROTECTED] (Craig Ringer) wrote: > iztech wrote: > > On Jun 12, 6:31 am, iztech <[EMAIL PROTECTED]> wrote: > >> i have just switched to posgresql and installed for use with ruby. > > >> i need to move my app and database to a new server. since i can shut > >> down the server i think it will be easier for me to do a file system > >> level back up. > > pg_dump and pg_restore should be just as good. If you're having trouble > manging postgresql they're probably a safer bet. > > >> when i try to shut the server down i get this message > > >> sudo -u postgres pg_ctl -D /opt/local/var/db/postgresql82/defaultdb > >> stop > >> pg_ctl: PID file "/opt/local/var/db/postgresql82/defaultdb/ > > can anyone let me know how i can tell where the db are located? > > You've left out some key information, like your operating system, OS > version, postgresql version, and how you installed postgresql. > > In general you should use the OS init script if provided to start and > stop postgresql. With most packaged Pg installs you can find the data > directory by examining the data_directory parameter in postgresql.conf, > which will normally be in a 'postgresql' directory under /etc . > > If you hand-installed it (which looks like the case based on your > command line, above) then you should really know where the data > directory is because you had to run initdb to create it. > > The -D argument on the command line above is the path to your data > directory. It's not clear whether that command is just copied & pasted > from somewhere else, because I'm a bit confused as to why you'd be > asking where the data directory was if you understood that. Check > postgresql.conf to confirm that the -D argumenet above is actually correct. > > >> this is my first time attempting this. can someone suggest an easy > >> tutorial on this. > > If you're new to Pg, just get the packages for your operating system if > they're reasonably recent. You can then use the OS init scripts and > normal management tools to handle Pg. > > >> i have set up the new server with the same permissions so i should be > >> able to move the db to the new location. > > Maybe. The version must be the same except for the patchlevel, eg > "8.2.1" is compatible with "8.2.3" but not "8.3.1". In general it is > MUCH SAFER to just use pg_dump and pg_restore to migrate your data. > > -- > Craig Ringer > > -- > Sent via pgsql-general mailing list ([EMAIL PROTECTED]) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Advice for "hot-swapping" databases
Hi. I'm trying to automate the updating of a database. This entails creating the new database from scratch (which takes a long time), under a different name, say mydb_tmp, and once this new database is ready, doing a "hot swap", i.e. renaming the existing database to something like mydb_20080613 and renaming the newly created database mydb. I have automated everything up to the creation of mydb_tmp. (This is done with an unholy mix of Perl and shell scripts.) I'm trying to figure out the best way to perform the hot swap. Any thoughts? TIA! Kynn
[GENERAL] Overloading
I never did get an answer to this. I get: ERROR: cannot change return type of existing function HINT: Use DROP FUNCTION first. ** Error ** ERROR: cannot change return type of existing function SQL state: 42P13 Hint: Use DROP FUNCTION first. When I try to: CREATE OR REPLACE FUNCTION check_date_ymd(given_date varchar) RETURNS BOOLEAN AS and CREATE OR REPLACE FUNCTION check_date_ymd(given_date varchar, OUT isvalid boolean, OUT ryear int, OUT rmonth int, OUT rday int) AS I wanted to an 'is it valid' version, and another that would save runtime by also returning values that would speed up the function date_to_utime(). in pg_proc there is no 'check_date_ymd', so there is no 'collision'. Can someone elucidate me on this? Thanks! Ralph
Re: [GENERAL] Overloading
On Fri, Jun 13, 2008 at 06:11:43PM -0700, Ralph Smith wrote: > I get: > ERROR: cannot change return type of existing function > HINT: Use DROP FUNCTION first. Don't use CREATE OR REPLACE for the second one. The OR REPLACE is trying to replace a function of the same name. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
