Re: [GENERAL] PgInstallerfor 8.1 beta 3 missing Postgis?
> Magnus, > I have replied to general as well as the only mailing list I > could find on PG Foundry for pginstaller was the Devel list. > > I actually do have all those files - the trouble is that the > name changes make pg_restore fail on a dump from a 8.0.3 > Database with spatial (postgis) tables. > > Possibly the Perl scripts would take care of this? should I > be installing Perl ? Okay. Then you really have the whole thing instlaled, and I think the installer did its job right. I don't know about the postgis upgrade stuff in particular, you'd better check that with the postgis people (www.postgis.org). It sounds like it should do it, but I can't tell you for sure. //Magnus ---(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
[GENERAL] pg_dump with low priority?
We have a huge database which must be backed up every day with pg_dump. The problem is, it takes around half an hour to produce the dump file, and all other processes on the same box are starved for cycles (presumably due to I/O) during the dump. It's not just an inconvenience, it's now evolved into a serious problem that needs to be addressed. Is there any mechanism for running pg_dump with a lower priority? I don't mind if the backup takes two hours instead of half an hour, as long as other processes were getting their fair share of cycles. Thank you for any advice, Bryan
Re: [GENERAL] PgInstallerfor 8.1 beta 3 missing Postgis?
Magnus, Well I checked out with the PostGIS people and their advice was that if you are restoring Postgis Databases you need Perl installed so that the restore.pl script can be run. This affects any upgrade from 0.9 or lower versions of PostGIS to 1.0 or higher. Given the other changes between 8.0 and 8.1 in Postgres it is probably worthwhile having a README that opens as soon as the installer is done with information on upgrading from previous installations as well as pointers to the change log. Cheers Johan Wehtje Magnus Hagander wrote: Magnus, I have replied to general as well as the only mailing list I could find on PG Foundry for pginstaller was the Devel list. I actually do have all those files - the trouble is that the name changes make pg_restore fail on a dump from a 8.0.3 Database with spatial (postgis) tables. Possibly the Perl scripts would take care of this? should I be installing Perl ? Okay. Then you really have the whole thing instlaled, and I think the installer did its job right. I don't know about the postgis upgrade stuff in particular, you'd better check that with the postgis people (www.postgis.org). It sounds like it should do it, but I can't tell you for sure. //Magnus . ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump with low priority?
Bryan Field-Elliot <[EMAIL PROTECTED]> schrieb: > Is there any mechanism for running pg_dump with a lower priority? I don't mind > if the backup takes two hours instead of half an hour, as long as other > processes were getting their fair share of cycles. You can use 'nice', see see the man-page. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(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: [GENERAL] pg_dump with low priority?
Bryan Field-Elliot <[EMAIL PROTECTED]> writes: > We have a huge database which must be backed up every day with pg_dump. > The problem is, it takes around half an hour to produce the dump file, and > all other processes on the same box are starved for cycles (presumably due > to I/O) during the dump. It's not just an inconvenience, it's now evolved > into a serious problem that needs to be addressed. You should probably use 'top' and 'vmstat' or 'iostat' to make sure the problem is what you think it is. Guessing is usually a bad idea. :) That said, I/O starvation is the most likely candidate. > Is there any mechanism for running pg_dump with a lower priority? I don't > mind if the backup takes two hours instead of half an hour, as long as > other processes were getting their fair share of cycles. Unfortunately, changing the CPU priority with 'nice' doesn't generally affect I/O bandwidth (since an I/O bound process doesn't use much CPU). I think there has been some work on I/O priorities in the Linux kernel, but I'm not sure where that is. Are you putting the dump file on the same device as the database lives on? If so, moving it to a different device/controller would take some of the write load off your database disk. You could also send the dump file over the network to another machine rather than saving it locally, which would do the above and also (probably) slow down the whole dump process, depending on the relative speeds of your disk and your network. -Doug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How much slower are numerics?
Tom Lane wrote: On modern hardware, I'd expect float operations to be at least an order of magnitude faster than numerics [snip] regression$# declare x float8 := 0; regression=# select timeit(100); Time: 13700.960 ms > [snip] regression$# declare x numeric := 0; regression=# select timeit(100); Time: 22145.408 ms But, this is less than a factor of 2 difference, similar to what one of the previous INSERT examples showed. Or am I missing something? -- -**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*--- Jon Lapham <[EMAIL PROTECTED]>Rio de Janeiro, Brasil Personal: http://www.jandr.org/ ***-*--**---***--- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Large Table Performance
List, I've got a problem where I need to make a table that is going to grow by an average of 230,000 records per day. There are only 20 columns in the table, mostly char and integer. It's FK'd in two places to another table for import/export transaction id's and I have a serial primary key and an index on a date column for when I need to search (every search is done inside a date range). I thought it would be OK but after a few weeks of operation I have more than five million records in there. Some queries take more than five minutes to complete and I'm sad about that. How can I make this faster? I could munge dates into integers if their faster, I'm OK with that. What can I tweak in the configuration file to speed things up? What about some drastic schema change that more experience would have shown me? I cannot show the full schema but it's like this: -- My import/export data information table ie_data (id serial primary key, date date, [12 other columns here]) big_transaction_table(id serial primary key, import_id int w/FK, export_id int w/FK, date date, [20 other necessary transaction detail columns]) So when I say select x,y,z from big_transaction_table where date>='10/2/2005' and date<='10/4/2005' and transaction_status in (1,2,3) order by date; it takes five+ minutes. TIA for any suggestions. /djb ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How much slower are numerics?
Jon Lapham <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> On modern hardware, I'd expect float operations to be at least an order >> of magnitude faster than numerics > But, this is less than a factor of 2 difference, similar to what one of > the previous INSERT examples showed. Or am I missing something? You're not thinking about the overhead imposed by the plpgsql loop construct. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to cluster Postgresql
Hi, Replying to all the questions at once : - The application was developped in PHP. It's mostly a B2B application for exchanging digital content (video) over the internet. Since we also provide some hosted scripts for the customer oriented websites using our plateform, there are a lot of requests on the database. - Each connexion involves some read and write queries to the database, though we may be able to separate the database in two. (Many of the write queries are mostly used for accounting/statistics and session tracking (there are already 5 http server machines in the farm, so session is in database). If you exclude statistics and session tracking, there are very few writes in the database. - For slony, i installed it a few weeks ago to give it a try. Installation was painful. I had to run the script many times to get it and to modify the script every time, to avoid asking the script to do the work twice. After installation was done, i had a heavy load average on the slave machine. (Heavier than on the master). Franck On Fri, 2005-10-21 at 09:54 -0500, Scott Marlowe wrote: > On Fri, 2005-10-21 at 11:01, Franck Coppola wrote: > > Hi, > > > > We have a postgresql database which we would like to clusterize. By > > clusterize, i mean be able to use multiple servers to read and write in > > the database. Simple replication is not enough. (However if you have any > > suggestion for replication which are fit for production environements, i > > would be interested too : i don't feel very confident with slony). > > > > Are there any tools to do this ? (I would prefer opensource, but if > > there is a well proven and affordable commercial app to do it, why > > not). > > You are saying that you want multiple servers for read and write, but > you're not saying why you want it. Since what you're trying to do > dictates what tools you should use to do it, it might help us to answer > your question if you tell us what parameters of your problem have led > you to decide on this particular requirement. It may well be that there > are the right tools to do this, but I can't really say, because I don't > have enough info. > > So, what, exactly, are you going to be doing with this system in terms > of transactional throughput, distributed input, things like that? What > percentage of your database traffic is input versus output? Is this > OLTP, or more OLAP? Would read only replicants of your db help your > users? etc... -- Franck Coppola <[EMAIL PROTECTED]> Hexaglobe signature.asc Description: This is a digitally signed message part
[GENERAL] out of memory for query result
I am trying to select a result set from a 2-table join, which should be returning 5,045,358 rows. I receive this error: DBD::Pg::st execute failed: out of memory for query result I am using Perl with DBI cursor (so i think) to retreive the data (prepare, execute, fetchrow_hashref, ..., finish). Perhaps either the DBD or libpq or something is buffering the result and not passing individual rows from the server (which runs on the same server as the application). I am using Postgres 7.4.7, under 4.11-RELEASE FreeBSD 4.11-RELEASE using perl v5.8.6 and DBD-Pg-1.32_1. Any suggestions on how to avoid this? Should i be using the API differently with Perl? Thanks, Allen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] out of memory for query result
On Sat, Oct 22, 2005 at 03:46:18PM -0400, Allen wrote:
> I am trying to select a result set from a 2-table join, which should be
> returning 5,045,358 rows. I receive this error:
>
> DBD::Pg::st execute failed: out of memory for query result
AFAIK, DBD:Pg never uses a cursor unless you ask it to. So you probably
want to code a loop like:
DECLARE CURSOR blah AS ...
while( FETCH 1000 )
{
process rows...
}
If you don't use a cursor in the backend, then DBI will try to pull the
*entire* result and store it in memory, which is why you don't have
enough...
Hope this helps,
--
Martijn van Oosterhout http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
pgphuL0S7b2EL.pgp
Description: PGP signature
[GENERAL] Transaction IDs not the same in same transaction?
So I was finally able to get a compiled binary for the code in this thread(thanks Magnus): http://archives.postgresql.org/pgsql-general/2005-06/msg00709.php So everything seemed to be fine with my GetCurrentTransactionID() function call returning the txn ID for each query I would run(as far as I could tell). Then I tried running a txn with multiple queries, and instead of just having one txn ID, each query had it's own. Does that make any sense? I was under the impression that a regular transaction block would have one txn ID assigned to it for its duration. Here's the query I ran: BEGIN; UPDATE partners SET partner_name = 'partner #5', activity_status_id = 1 WHERE partner_id = 5; UPDATE partners SET partner_name = 'partner #7' WHERE partner_id = 3; COMMIT; Should that have had the same txn ID for both of those? The GetCurrentTransactionID call occurs in a trigger, would that have an impact on the ID? Thanks, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] pg_autovacuum (8.0.4) as Windows service ?
When installing pg_autovacuum as Windows service, should user that makes connection be the service account or a superuser ? For example, I have service account "postgres_service" and database superuser "postgres". Which one should be used in following script: @echo off set TARGET_DISC=C:echo target disc is %TARGET_DISC% cd %TARGET_DISC%\Program Files\PostgreSQL\8.0\binpg_autovacuum.exe pgsql-8.0.4 -I -U user -P password pause Thanks, Zlatko
[GENERAL] Ann: PgBrowser-1.1
PgBrowse ver 1.1 is a generic Postgresql database browser that works on Windows, Macintosh and Linux platforms that is written in Tcl/Tk. A couple of features that help differentiate this (free) product. 1) No postresql software is actually needed on the client. ( but both functionality and speed will suffer). 2) PgBrowser can display graphical images stored in the database as bytea or large objects (via the Img package). 3) PgBrower supports SQL libraries of your favorite queries. At startup PgBrowser looks for "~/SQLScripts" and builds a menu of all members of the directory that end in ".sql". Subdirectories will generate the appropriate submenu. 4) PgBrowser supports a "history" of queries/commands passed to the backend. Previous commands can be easily recalled from the keyboard. Gracefully exiting the program will cause the history to be stored in "~/SQLScripts" as HiStOrY.tcl. This file will be "sourced" at program startup to recover the command history. 5) If PgBrowser is running on a Mac or Linux system that has "psql" located in a standard location, it is possible to execute "psql" commands from within PgBrowser. For more information and download visit: http://homepage.mac.com/levanj/TclTk Suggestions for improvements and bug fixes gladly accepted. Thanks, Jerry ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Transaction IDs not the same in same transaction?
On Sat, Oct 22, 2005 at 01:30:32PM -0700, Steve V wrote: > So I was finally able to get a compiled binary for the code in this > thread(thanks Magnus): > http://archives.postgresql.org/pgsql-general/2005-06/msg00709.php > > So everything seemed to be fine with my GetCurrentTransactionID() > function call returning the txn ID for each query I would run(as far > as I could tell). Then I tried running a txn with multiple queries, > and instead of just having one txn ID, each query had it's own. Does > that make any sense? I was under the impression that a regular > transaction block would have one txn ID assigned to it for its > duration. It makes sense if you're running PostgreSQL 8.0 or later and are using subtransactions, whether explicitly or implicitly. The example you posted didn't show the trigger definition or function -- does the function do any error trapping? Maybe you need GetTopTransactionId() instead of GetCurrentTransactionID(). Why do you need the transaction ID at all? Might the xmin system column serve your purpose? -- Michael Fuhr ---(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
Re: [GENERAL] Transaction IDs not the same in same transaction?
On 10/22/05, Michael Fuhr <[EMAIL PROTECTED]> wrote: > > It makes sense if you're running PostgreSQL 8.0 or later and are > using subtransactions, whether explicitly or implicitly. The example > you posted didn't show the trigger definition or function -- does > the function do any error trapping? Maybe you need GetTopTransactionId() > instead of GetCurrentTransactionID(). > > Why do you need the transaction ID at all? Might the xmin system > column serve your purpose? Using 8.0.4 right now. No subtransactions that I know of. The trigger call is an AFTER I/U/D. The function called is somewhat lengthy, but it does not explicitly intiate any transactions, and does not perform any error trapping. Maybe GetTopTransactionId() is a better function call. I have no experience with it though. I played around with GetCurrentTransactionID(), and noticed the following behavior. If I add an extra integer field to my table to hold txn IDs; I get the same txn ID stored if both of my updates in the initial post call GetCurrentTransactionID() as part of their updated field lists. However, the txn IDs in from each of the triggers is different. So with the previous scenario, I end up with a total of three distinct txn IDs. I'm using this for an auditing script, and want to use the txn ID to indicate an atomic set of changes(the pg txn ID is mapped to my own txn ID to avoid wraparound issues). I would rather not use xmin, as it is not available directly in any of the triggers(as far as I know). So I would have to construct select statements on the fly, and I would rather not do that. I'm open to suggestions though. Thanks, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [pgsql-advocacy] Oracle buys Innobase
On Wed, Oct 19, 2005 at 10:07:05 -0500, [EMAIL PROTECTED] wrote: > > Yep. It is not just limited to empty strings; An all blank string, no > matter the number of characters, is stored as NULL. And a corollary to > that idiocy is that a string with two blank characters is not equal to a > string with a single blank character in Oracle. 'a ' is not equal to 'a > '. 'a ' is not equal to 'a'. Port that to another database. Seen the > JOIN syntax? *sigh* I don't believe this is true. The following example is from Oracle 9i: SQL> select 1 from dual where ' ' is null; no rows selected SQL> select 1 from dual where '' is null; 1 -- 1 Peoplesoft uses ' ' in a lot of fields as sort of a missing value code. My theory about this is that they want to avoid database specific weirdness involving nulls and oracles treatment of null strings. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
