Re: [GENERAL] Backup/Restore of single table in multi TB database
On Wed, 2008-05-07 at 15:24 -0700, John Smith wrote:
Actually, I forgot to mention one more detail in my original post.
For the table that we're looking to backup, we also want to be able to
do incremental backups. pg_dump will cause the entire table to be
dumped out each time it is invoked.
With the pg_{start,stop}_backup approach, incremental backups could be
implemented by just rsync'ing the data files for example and applying
the incremental WALs. So if table foo didn't change very much since
the first backup, we would only need to rsync a small amount of data
plus the WALs to get an incremental backup for table foo.
Besides picking up data on unwanted tables from the WAL (e.g., bar
would appear in our recovered database even though we only wanted
foo), do you see any other problems with this pg_{start,stop}_backup
approach? Admittedly, it does seem a bit hacky.
You wouldn't be the first to ask to restore only a single table.
I can produce a custom version that does that if you like, though I'm
not sure that feature would be accepted into the main code.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Ubuntu question
Hello, I had postgresql 7.4 on ubuntu and over one year ago I moved to 8.2 Till now I was backing up my db via pgadmin remotely from windows but now I want to do it from the ubuntu server. When I run the command pgdump it said that the database is 8.2 but the tool is 7.4 - my question is, where in the world is the pgdump for 8.2 - I can't find it. pg_dump, pg_dumpall are all in /usr/bin but where are the 8.2 ones ? TIA, Q -- 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] Ubuntu question
On Thu, May 08, 2008 at 01:52:17AM -0500, Q Master wrote: I had postgresql 7.4 on ubuntu and over one year ago I moved to 8.2 Till now I was backing up my db via pgadmin remotely from windows but now I want to do it from the ubuntu server. I suggest looking at the README.Debian for postgres, it contains much important information you need to understand how multiple concurrently installed versions work. When I run the command pgdump it said that the database is 8.2 but the tool is 7.4 - my question is, where in the world is the pgdump for 8.2 - I can't find it. pg_dump, pg_dumpall are all in /usr/bin but where are the 8.2 ones ? First, check what you have installed with pg_lsclusters (this will give you the port number). Normally you can specify the cluster directly to pg_dump but if you want the actual binary go to: /usr/lib/postgresql/version/bin/pg_dump. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Ubuntu question
Q Master wrote: Hello, I had postgresql 7.4 on ubuntu and over one year ago I moved to 8.2 Till now I was backing up my db via pgadmin remotely from windows but now I want to do it from the ubuntu server. When I run the command pgdump it said that the database is 8.2 but the tool is 7.4 - my question is, where in the world is the pgdump for 8.2 - I can't find it. pg_dump, pg_dumpall are all in /usr/bin but where are the 8.2 ones ? You need to download the pgcontrib package from ubuntu package site. I use the gnome package manager from ubuntu to handle this plus it automatically handles the updates if any apply TIA, Q -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] TSearch2 Searching Text with Synonyms and Stop Dictionary
Hi, I´m trying to use the tsearch2 with postgre 8.2. What i am trying to do its: from a text search the text and synonyms excluding the words that doesn´t mean nothing like(what, the, of). How can i configure the dictionarys for use both synonyms and stop dictionarys? Can anyone create a function that do that? I´m using the portuguese dictionarys at http://snowball.tartarus.org/algorithms/portuguese/stemmer.html. Thanks very much -- 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] pg_dumpall: pg_conversion table not saved
Tom Lane schrieb: Michael Enke [EMAIL PROTECTED] writes: I updated pg_converion to set the condefault=false where I need it. Why are you needing to replace an existing default conversion? Do we have a bug in it? No, not a bug. But I need CP852 encoding conversion. Maybe you can take a look at http://archives.postgresql.org/pgsql-general/2008-05/msg00185.php and give your comment. Is the CREATE CONVERSION only for the case that there is a bug in existing default conversions? ;-) pg_dumpall does NOT write that table out and therefore it is not possible to restore, this update get lost. pg_dump does not (and shouldn't) dump system-defined objects. It has no way to know that you modified the definition of such an object. Why can not the CREATE DEFAULT CONVERSION ... a) return with an error if there is another default conversion existing or AFAICS it does. You can try with attached library: CREATE FUNCTION utf82cp852(integer,integer,cstring,internal,integer) RETURNS void AS '/tmp/libencodings' LANGUAGE 'C'; CREATE FUNCTION cp8522utf8(integer,integer,cstring,internal,integer) RETURNS void AS '/tmp/libencodings' LANGUAGE 'C'; CREATE DEFAULT CONVERSION utf82cp852 FOR 'UNICODE' TO 'LATIN2' FROM utf82cp852; CREATE DEFAULT CONVERSION cp8522utf8 FOR 'LATIN2' TO 'UNICODE' FROM cp8522utf8; It doesn't complain but it also is not the encoding used now since utf8_to_iso_8859_2 / iso_8859_2_utf8 has still condefault=true. If I now set condefault to false, my conversion function is used. Regards, Michael -- Wincor Nixdorf International GmbH Sitz der Gesellschaft: Paderborn Registergericht Paderborn HRB 3507 Geschäftsführer: Eckard Heidloff (Vorsitzender), Stefan Auerbach, Dr. Jürgen Wunram Vorsitzender des Aufsichtsrats: Karl-Heinz Stiller Steuernummer: 339/5884/0020 - Ust-ID Nr.: DE812927716 - WEEE-Reg.-Nr. DE44477193 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser E-Mail ist nicht gestattet. This e-mail may contain confidential information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden. libencodings.so Description: application/sharedlib -- 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] Problems with memory
Hi maybe i should give you some more explanations of my problem. The reason for which i think that postgresql run out of memory is that: I have a relation with 6 fields, 29 indexes and 32000 registers, the registers where made up using a pgsql language to save disk space, and they work (see the table schema under those lines) Column | Type | Modifiers +--+--- id | integer | p1 | character(1) | p4 | character(1) | p6 | character(1) | p7 | character(1) | p9 | character(1) | Indexes: h2iab btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying)) h2iad btree (idr(p1, p4, p6, p7, p9, 'H-2*IAd'::character varying)) h2iak btree (idr(p1, p4, p6, p7, p9, 'H-2*IAk'::character varying)) h2ied btree (idr(p1, p4, p6, p7, p9, 'H-2*IEd'::character varying)) hladqa10501 btree (idr(p1, p4, p6, p7, p9, 'HLA-DQA1*0501'::character varying)) hladqb10201 btree (idr(p1, p4, p6, p7, p9, 'HLA-DQB1*0201'::character varying)) hladr btree (idr(p1, p4, p6, p7, p9, 'HLA-DR'::character varying)) hladr1 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR1'::character varying)) hladr13 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR13'::character varying)) hladr3 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR3'::character varying)) hladr7 btree (idr(p1, p4, p6, p7, p9, 'HLA-DR7'::character varying)) hladrb10101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying)) hladrb10102 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0102'::character varying)) hladrb10301 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0301'::character varying)) hladrb10302 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0302'::character varying)) hladrb10401 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0401'::character varying)) hladrb10402 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0402'::character varying)) hladrb10701 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0701'::character varying)) hladrb10802 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0802'::character varying)) hladrb10901 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0901'::character varying)) hladrb11101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1101'::character varying)) hladrb11102 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1102'::character varying)) hladrb11103 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1103'::character varying)) hladrb11104 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1104'::character varying)) hladrb11301 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1301'::character varying)) hladrb11302 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1302'::character varying)) hladrb11501 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*1501'::character varying)) hladrb40101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB4*0101'::character varying)) hladrb50101 btree (idr(p1, p4, p6, p7, p9, 'HLA-DRB5*0101'::character varying)) when i do a query as: select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; it works and return 128030 registers if i do select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; 3071970 registers, it don't work ERROR: relation pssms does not exist CONTEXT: SQL statement select score from PSSMS where AA= $1 and POS=1 and MOLEC= $2 PL/pgSQL function idr line 11 at SQL statement if i ask for explanation for both queries works: mhc2db= explain select count(*) from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; QUERY PLAN Aggregate (cost=66188.88..66188.89 rows=1 width=0) - Bitmap Heap Scan on precalc (cost=17615.20..63522.21 rows=107 width=0) Recheck Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) -2::double precision) - Bitmap Index Scan on hladrb10101 (cost=0.00..17348.54 rows=107 width=0) Index Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) -2::double precision) (5 rows) mhc2db= explain select count(*) from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; QUERY PLAN Aggregate (cost=66188.88..66188.89 rows=1 width=0) - Bitmap Heap Scan on precalc (cost=17615.20..63522.21 rows=107 width=0) Recheck Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) -2::double precision) - Bitmap Index Scan on hladrb10101 (cost=0.00..17348.54 rows=107 width=0) Index Cond: (idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101'::character varying) -2::double precision) (5 rows) and the index used are the correct ones If for that reason that i think that my machine runs out of memory, by the way, this is not the biggest table that i have others have more than
Re: [GENERAL] statistics collector process is thrashing my cpu
William Temperley wrote: Dear All Sometimes postgres.exe will thrash one of the cores and won't stop until I kill the process. I know it's the statistics collector as I get this message when I kill the process: statistics collector process (PID 172) exited with exit code 1 Nothing other than this app is accessing my PG server. I'm developing a web mapping application which retrieves point data via multiple statements like this: select xmlelement(name amr:mapFeatureType, xmlattributes(featuretype as name),xmlagg( xmlelement(name amr:mapFeature, xmlforest(y(the_geom) as amr:latitude, x(the_geom) as amr:longitude, lable as amr:description)) )) from (select featuretype, the_geom, lable from poi WHERE the_geom setsrid( 'BOX3D(-5.625 52.482780222078205,0 55.7765730186677)'::box3d, 4326) and featureclass = 'layer3' limit 15) as ss group by ss.featuretype I don't even know why this process would run anyway, as I haven't updated the database for weeks (it's only a dummy dataset). I'm also getting hundreds of these messages in a few hours work: 2008-05-08 09:22:56 BST LOG: loaded library $libdir/plugins/plugin_debugger.dll 2008-05-08 09:22:56 BST LOG: loaded library $libdir/plugins/plugin_debugger.dll Why would it load it twice within the same second? You get one of those everytime you start a new backend. Select version() - PostgreSQL 8.3.1, compiled by Visual C++ build 1400 Machine - XP sp2, core 2 duo 7250 laptop The only non 'out of the box' setting I've changed in postgresql.conf is: log_statement = 'all' Any ideas why this might be happening, and how I can stop it? It'd be interesting to know what the stats collector is actually doing. Could you, using Process Explorer or a debugger, get a stack trace from that process while it's in the trashing state? //Magnus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] statistics collector process is thrashing my cpu
Dear All Sometimes postgres.exe will thrash one of the cores and won't stop until I kill the process. I know it's the statistics collector as I get this message when I kill the process: statistics collector process (PID 172) exited with exit code 1 Nothing other than this app is accessing my PG server. I'm developing a web mapping application which retrieves point data via multiple statements like this: select xmlelement(name amr:mapFeatureType, xmlattributes(featuretype as name),xmlagg( xmlelement(name amr:mapFeature, xmlforest(y(the_geom) as amr:latitude, x(the_geom) as amr:longitude, lable as amr:description)) )) from (select featuretype, the_geom, lable from poi WHERE the_geom setsrid( 'BOX3D(-5.625 52.482780222078205,0 55.7765730186677)'::box3d, 4326) and featureclass = 'layer3' limit 15) as ss group by ss.featuretype I don't even know why this process would run anyway, as I haven't updated the database for weeks (it's only a dummy dataset). I'm also getting hundreds of these messages in a few hours work: 2008-05-08 09:22:56 BST LOG: loaded library $libdir/plugins/plugin_debugger.dll 2008-05-08 09:22:56 BST LOG: loaded library $libdir/plugins/plugin_debugger.dll Why would it load it twice within the same second? Select version() - PostgreSQL 8.3.1, compiled by Visual C++ build 1400 Machine - XP sp2, core 2 duo 7250 laptop The only non 'out of the box' setting I've changed in postgresql.conf is: log_statement = 'all' Any ideas why this might be happening, and how I can stop it? Thanks, Will T -- 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] statistics collector process is thrashing my cpu
Any ideas why this might be happening, and how I can stop it? It'd be interesting to know what the stats collector is actually doing. Could you, using Process Explorer or a debugger, get a stack trace from that process while it's in the trashing state? //Magnus Certainly, but I'll have to wait 'til it does it again, it doesn't happen all the time. What would you like to know from Process Explorer? Will T -- 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] Problems with memory
Pau Marc Munoz Torres wrote: Hi maybe i should give you some more explanations of my problem. The reason for which i think that postgresql run out of memory is that: I have a relation with 6 fields, 29 indexes and 32000 registers, the registers where made up using a pgsql language to save disk space, and they work (see the table schema under those lines) You have 29 indexes on a table with 6 columns? But only 32000 rows? Column | Type | Modifiers +--+--- id | integer | p1 | character(1) | p4 | character(1) | p6 | character(1) | p7 | character(1) | p9 | character(1) | Indexes: h2iab btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying)) h2iad btree (idr(p1, p4, p6, p7, p9, 'H-2*IAd'::character varying)) h2iak btree (idr(p1, p4, p6, p7, p9, 'H-2*IAk'::character varying)) h2ied btree (idr(p1, p4, p6, p7, p9, 'H-2*IEd'::character varying)) etc. OK, so you have 29 different functional indexes which use your columns and then a fixed parameter. Looks odd to me, but I suppose you might have good reason. Oh - and it's not necessarily saving you any disk space - the index values need to be stored. when i do a query as: select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; it works and return 128030 registers if i do select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; 3071970 registers, it don't work ERROR: relation pssms does not exist CONTEXT: SQL statement select score from PSSMS where AA= $1 and POS=1 and MOLEC= $2 PL/pgSQL function idr line 11 at SQL statement Do you have a table/view called pssms in your search-path? Because that's what the error is about. Might it be a case-sensitive issue - do you have a table called PSSMS instead? if i ask for explanation for both queries works: mhc2db= explain select count(*) from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; [snip] mhc2db= explain select count(*) from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; [snip] and the index used are the correct ones If for that reason that i think that my machine runs out of memory, by the way, this is not the biggest table that i have others have more than 50300 registers, so if I try to do a cross select between tables it could be worse. For what reason? I still don't see any out-of-memory errors. -- Richard Huxton Archonet Ltd -- 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] Import German Decimal Numbers
Hi, Le 7 mai 08 à 15:57, Tino Wildenhain a écrit : There is also http://pgfoundry.org/projects/pgloader/ and if not already implemented it should be fairly easy to implement a data filter within this one. pgloader indeed support user reformating modules, and comes with a mysql to pgsql timestamp reformater. Adding a python .py module containing one function to handle the change should be easy, the documentation has needed details if you look for reformat_path and reformat options. http://pgloader.projects.postgresql.org/ Plus, pgloader supports setting the DateStyle before running copy, maybe this will be enough in your case? (didn't read all the thread). If you need more help than current documentation to setup your reformating module, please just ask! Regards, -- dim -- 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] Custom Base Type in C
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Le 8 mai 08 à 01:06, Toby Chavez a écrit : My custom type needs to have one biginteger and one text value... pretty straight forward. I guess my first question is if there are any examples out there that do something similar. I have looked extensively through the contrib directory and can't find anything very helpful. You could have a look at the prefix module, which defines a varlena prefix_range datatype, which is a C struct containing two char and a char* elements. http://pgfoundry.org/projects/prefix But maybe you just don't need to define the type in C, this I can't say. - -- dim -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (Darwin) iEYEARECAAYFAkgi7BsACgkQlBXRlnbh1bnqcgCfQhuzXrmRIc/k65w4Jb5mCHs6 OBgAn1h6g5eadNPetCBs59nnh5TGs+2Z =F2OW -END PGP SIGNATURE- -- 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] Cannot update table with OID with linked server in SQl Server
Yes the table does have a primary key defined. -Original Message- From: Adrian Klaver [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 06, 2008 7:07 PM To: Ken Allen; pgsql-general@postgresql.org Subject: Re: [GENERAL] Cannot update table with OID with linked server in SQl Server -- Original message -- From: Ken Allen [EMAIL PROTECTED] I have a linked server on SQL server 2005. I can update or write to a table in Postgres that does not have a OID. But the Table I have has an OID and I cannot write or update to that table. Anyone have any ideas. Does the table with the OID have a primary key defined? -- Adrian Klaver [EMAIL PROTECTED] -- This message has been scanned by MailScanner -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] regexp_replace in two times?
Hi all, I used the regexp_replace function to make a substitution over a table, but I got a strange behaviour (please consider I'm not an expert of regex). The idea is to remove the final part of a code, that could be TIF, ISTTIF, tif, isttif, and at the same time consider only the records depending on the join with another table. Now, the strange thing is that the first query updated the most of records, but then 4 records are still there, and in fact executing again the same update provides me another substitution. What could be the reason? db= begin; BEGIN raydb= update elementi_dettagliset codice = regexp_replace( upper(codice), '(IST)*TIF$', '') where id_elemento in ( select ed.id_elemento from elementi_dettagli ed, elementi e where ed.id_elemento = e.id_elemento and e.categoria = 'bozzetti' and ed.codice ~* '(IST)*TIF$' ); UPDATE 4679 db= select ed.id_elemento,ed.codice from elementi_dettagli ed, elementi e where ed.codice like '%TIF' and ed.id_elemento = e.id_elemento and e.categoria='bozzetti'; id_elemento |codice -+-- 68904 | 0M0809532TIF 67732 | Y07236TIF 67608 | 0D0731744TIF 65687 | 0M0708711TIF (4 rows) db= update elementi_dettagliset codice = regexp_replace( upper(codice), '(IST)*TIF$', '') where id_elemento in ( select ed.id_elemento from elementi_dettagli ed, elementi e where ed.id_elemento = e.id_elemento and e.categoria = 'bozzetti' and ed.codice ~* '(IST)*TIF$' ); UPDATE 4 db= select version(); version -- PostgreSQL 8.2.7 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2) (1 row) Thanks, Luca -- 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] regexp_replace in two times?
Luca Ferrari [EMAIL PROTECTED] writes: I used the regexp_replace function to make a substitution over a table, but I got a strange behaviour (please consider I'm not an expert of regex). The idea is to remove the final part of a code, that could be TIF, ISTTIF, tif, isttif, and at the same time consider only the records depending on the join with another table. Now, the strange thing is that the first query updated the most of records, but then 4 records are still there, and in fact executing again the same update provides me another substitution. What could be the reason? Maybe the original strings had more than one instance of 'TIF'? 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] auto-vacuum questions
Hi, I have a couple of questions on the auto-vacuum daemon: 1) How do I know it is running. I suspect it has not been, and I carefully checked the documentation and found a variable that wasn't set (stats_row_level defaults to off in 8.1, should be on). So I changed that and restarted, but how do I *know* that is or isn't vacuuming? 2) Every night I do pg_dump -c mydb | psql -d mydbtest so I create a copy of the database in a different name for testing purposes. But the new database, mydbtest, always has slow queries. I run an analyze and they speed up. There are NO transactions in mydbtest until I come in and start testing, does this mean auto-vacuum won't analyze it? Is this maybe related to question 1 where I think auto-vacuum actually wasn't running? Thanks, j -- John Gateley [EMAIL PROTECTED] -- 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] auto-vacuum questions
John Gateley wrote: Hi, I have a couple of questions on the auto-vacuum daemon: 1) How do I know it is running. I suspect it has not been, and I carefully checked the documentation and found a variable that wasn't set (stats_row_level defaults to off in 8.1, should be on). So I changed that and restarted, but how do I *know* that is or isn't vacuuming? Open a session and run SHOW autovacuum. If it says on, it's vacuuming. Keep an eye on the autovacuum_naptime setting too. 2) Every night I do pg_dump -c mydb | psql -d mydbtest so I create a copy of the database in a different name for testing purposes. But the new database, mydbtest, always has slow queries. I run an analyze and they speed up. There are NO transactions in mydbtest until I come in and start testing, does this mean auto-vacuum won't analyze it? Is this maybe related to question 1 where I think auto-vacuum actually wasn't running? It might mean it hasn't been analyzed _yet_. If there are too many databases, it may take a while. -- 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
Re: [GENERAL] auto-vacuum questions
On Thu, 8 May 2008 10:54:32 -0400 Alvaro Herrera [EMAIL PROTECTED] wrote: John Gateley wrote: Hi, I have a couple of questions on the auto-vacuum daemon: 1) How do I know it is running. Open a session and run SHOW autovacuum. If it says on, it's vacuuming. Keep an eye on the autovacuum_naptime setting too. Thank you, j -- John Gateley [EMAIL PROTECTED] -- 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] now i'm really confused. insert/update does autocast, where sometimes.
Am Dienstag, 6. Mai 2008 schrieb Daniel Schuchardt: so it depends on ? if i need an explicit cast? A type cast can be attempted in three different contexts (see also CREATE CAST reference page): - implicitly - storage assignment - explicitly The explicit case is if you call CAST() or ::. This is always allowed if an appropriate cast routine is defined. The implicit case is if an operator or function requires type A and the expression is of type B. This cast is only performed if the cast routine is defined and allowed for the implicit context. The change in 8.3 was to downgrade many casting functions from implicit to assignment or explicit. The storage assignment case is if the value has type A and is about to be stored into a column of type B. This is allowed if the cast routine is allowed for the assignment context. This is, perhaps surprisingly, a separate level between implicit and explicit casts. So in reference to your subject line, yes, INSERT and UPDATE do have different casting behavior than SELECT. (This is not really accurate, because the WHERE clause of an UPDATE would follow the same rules as a WHERE clause in SELECT. The assignment context only applies for values that are really going to be stored.) This is all in line with the SQL standard. -- 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] Problems with memory
Richard Huxton wrote: Pau Marc Munoz Torres wrote: Hi To your initial question all configurable options can be found and adjusted in the postgresql.conf which can be found in the top of your data dir. You can find more detail on what the options are for at http://www.postgresql.org/docs/8.3/interactive/runtime-config.html Although I doubt it will fix your problem you will most likely want to tune them to get the best performance from your db once it goes live. maybe i should give you some more explanations of my problem. The reason for which i think that postgresql run out of memory is that: I have a relation with 6 fields, 29 indexes and 32000 registers, the registers So how much ram does your machine have? have you looked at how much postgres is using while it is running the queries? What CPU's and disks/raid controller? when i do a query as: select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; it works and return 128030 registers if i do select * from precalc where idr(p1, p4, p6, p7, p9, 'HLA-DRB1*0101')-2; 3071970 registers, it don't work ERROR: relation pssms does not exist CONTEXT: SQL statement select score from PSSMS where AA= $1 and POS=1 and MOLEC= $2 PL/pgSQL function idr line 11 at SQL statement Start with the idr function - this is where the error comes from. The error doesn't mention out of memory. if i ask for explanation for both queries works: explain will show what the planner expects to do. explain analyse will actually run through the query and give exact row counts returned etc. and will hit the error that you get when running the query. If for that reason that i think that my machine runs out of memory, by the way, this is not the biggest table that i have others have more than 50300 registers, so if I try to do a cross select between tables it could be worse. You may hear from people on this list that have db's storing many TeraBytes of data. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] Using a composite SQL type in C
Check the documentation about using composite-type arguments in C http://www.postgresql.org/docs/8.2/static/xfunc-c.html#AEN37402 You can use GetAttributeByNum() or GetAttributeByName() to get each value from your custom type. You will need to get the HeapTupleHeader first by calling PG_GETARG_HEAPTUPLEHEADER(). A quick google search brought up this tutorial that might help http://linuxgazette.net/142/peterson.html Cheers My problem is, how do I access this type in my function (in C), and how do I create a new object of this type to return from the function? I need to store both a text and an int, to avoid doing an extra query around the result of this aggregate (to get the corresponding text value), which is what I'm currently doing. Any ideas?
Re: [GENERAL] statistics collector process is thrashing my cpu
William Temperley wrote: Any ideas why this might be happening, and how I can stop it? It'd be interesting to know what the stats collector is actually doing. Could you, using Process Explorer or a debugger, get a stack trace from that process while it's in the trashing state? //Magnus Certainly, but I'll have to wait 'til it does it again, it doesn't happen all the time. What would you like to know from Process Explorer? Get the backtrace from hung process. Find the process in the list, open it. Go to the tab Threads, find the thread that's using a lot of CPU (or at least has a lot of ocntext switchs), and click the Stack button. That should give you a window with a backtrace. //Magnus -- 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] Using a composite SQL type in C
Toby Chavez wrote: Check the documentation about using composite-type arguments in C http://www.postgresql.org/docs/8.2/static/xfunc-c.html#AEN37402 [...] A quick google search brought up this tutorial that might help http://linuxgazette.net/142/peterson.html Cheers I found the documentation... less than enlightening (my attempt at following it resulted in crashes), but it looks like that tutorial will help. I'll try it out and come back if I get stuck. Thanks, Dan -- 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] auto-vacuum questions
On Thursday 08 May 2008, John Gateley [EMAIL PROTECTED] wrote: But the new database, mydbtest, always has slow queries. I run an analyze and they speed up. Do the query plans actually change, or are you just seeing caching effects from running the analyze? -- Alan signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] auto-vacuum questions
On Thu, 8 May 2008 10:58:47 -0700 Alan Hodgson [EMAIL PROTECTED] wrote: On Thursday 08 May 2008, John Gateley [EMAIL PROTECTED] wrote: But the new database, mydbtest, always has slow queries. I run an analyze and they speed up. Do the query plans actually change, or are you just seeing caching effects from running the analyze? The query plans actually change. I'm pretty sure that the auto-vacuum wasn't happening, I'm going to test this tomorrow morning with a fresh copy. Thanks, j -- John Gateley [EMAIL PROTECTED] -- 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] statistics collector process is thrashing my cpu
On Thu, May 8, 2008 at 6:14 PM, Magnus Hagander [EMAIL PROTECTED] wrote: William Temperley wrote: Any ideas why this might be happening, and how I can stop it? It'd be interesting to know what the stats collector is actually doing. Could you, using Process Explorer or a debugger, get a stack trace from that process while it's in the trashing state? //Magnus Certainly, but I'll have to wait 'til it does it again, it doesn't happen all the time. What would you like to know from Process Explorer? Get the backtrace from hung process. Find the process in the list, open it. Go to the tab Threads, find the thread that's using a lot of CPU (or at least has a lot of ocntext switchs), and click the Stack button. That should give you a window with a backtrace. //Magnus Ok, got the little blighter. Below are 4 stack traces taken at random times. Doesn't seem to be doing much I/O (16 reads and 167 other in 3 hours). The memory usage seems to be static (3.7MB) even with high database usage (vacuum, read, update). ntoskrnl.exe+0x584d ntoskrnl.exe!MmCreateMdl+0x28a hal.dll+0x2ef2 ntoskrnl.exe!IoCheckFunctionAccess+0x1d8a6 ntoskrnl.exe!RtlFindUnicodePrefix+0x29 ntoskrnl.exe!MmPrefetchPages+0xc59 ntoskrnl.exe!ZwSetSystemInformation+0x23 ntdll.dll!KiFastSystemCallRet mswsock.dll+0x6e2d mswsock.dll+0x6039 WS2_32.dll!WSAAddressToStringW+0x1c9 WS2_32.dll!WSAAddressToStringW+0x113 WS2_32.dll!WSAEventSelect+0x2f postgres.exe!RemoveTSConfiguration+0x151837 postgres.exe!RemoveTSConfiguration+0x15a586 ntoskrnl.exe!ZwAssignProcessToJobObject+0x15 ntoskrnl.exe!MmCreateMdl+0x28a hal.dll!HalClearSoftwareInterrupt+0x342 hal.dll!HalRequestSoftwareInterrupt+0x3c ntoskrnl.exe!SeCaptureSecurityDescriptor+0x41e ntoskrnl.exe!ZwSetSystemInformation+0x23 ntdll.dll!KiFastSystemCallRet postgres.exe!RemoveTSConfiguration+0x151788 postgres.exe!RemoveTSConfiguration+0x15a586 ntoskrnl.exe!ZwAssignProcessToJobObject+0x15 ntoskrnl.exe!MmCreateMdl+0x28a hal.dll!HalClearSoftwareInterrupt+0x342 ntoskrnl.exe!IoCheckFunctionAccess+0x1d8a6 ntoskrnl.exe!RtlFindUnicodePrefix+0x29 ntoskrnl.exe!MmPrefetchPages+0xc59 ntoskrnl.exe!ZwSetSystemInformation+0x23 ntdll.dll!KiFastSystemCallRet mswsock.dll+0x6e2d mswsock.dll+0x6039 WS2_32.dll!WSAAddressToStringW+0x1c9 WS2_32.dll!WSAAddressToStringW+0x113 WS2_32.dll!WSAEventSelect+0x2f postgres.exe!RemoveTSConfiguration+0x151837 postgres.exe!RemoveTSConfiguration+0x15a586 ntoskrnl.exe!ZwAssignProcessToJobObject+0x15 ntoskrnl.exe!MmCreateMdl+0x28a hal.dll!HalClearSoftwareInterrupt+0x342 mswsock.dll+0x5f38 mswsock.dll+0x6e75 mswsock.dll+0x6e2d mswsock.dll+0x6039 WS2_32.dll!WSAAddressToStringW+0x1c9 WS2_32.dll!WSAAddressToStringW+0x113 WS2_32.dll!WSAEventSelect+0x2f postgres.exe!RemoveTSConfiguration+0x151837 postgres.exe!RemoveTSConfiguration+0x15a586 Hope that means more to you than me, Will -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] changing language of system messages
since the update from 8.3.0 to 8.3.1., postgresql system error messages as well as all logfile entries appear in german. i've already tried to change LC_MESSAGE in the postgres.conf without luck. the db's LC_COLLATE is 'German_Switzerland', and i do not want to change it. i'd just like to have the system/error messages in english... it's a windows 2003 system (english os with German_Switzerland locale). thanks, thomas -- 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] Fragments in tsearch2 headline
Where are we on this? --- Teodor Sigaev wrote: The patch takes into account the corner case of overlap. Here is the code for that // start check if (!startHL *currentpos = startpos) startHL = 1; The headline generation will not start until currentpos has gone past startpos. Ok You can also check how this headline function is working at my website indiankanoon.com. Some example queries are murder, freedom of speech, freedom of press etc. Looks good. Should I develop the patch for the current cvs head of postgres? I'd like to commit your patch, but if it should be: - for current HEAD - as extension of existing ts_headline. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using Epoch to save timestamps in 4 bytes?
While looking at a database I inheritted I noticed the database has tables with integers used to store epoch. I noticed that timestamp is 8 bytes and was wondering how come timestamp is 8 bytes and not 4. Is it to be able to support precission beyond a second? I am looking at tens of millions of rows, which is why my predecessor may have used integer to store epoch to save space. -- 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] Using Epoch to save timestamps in 4 bytes?
Francisco Reyes wrote: While looking at a database I inheritted I noticed the database has tables with integers used to store epoch. I noticed that timestamp is 8 bytes and was wondering how come timestamp is 8 bytes and not 4. Is it to be able to support precission beyond a second? I am looking at tens of millions of rows, which is why my predecessor may have used integer to store epoch to save space. Our timestamp has a much larger range than a 4-byte time_t, docs say: entry4713 BC/entry entry294276 AD/entry -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
