pgloader an Indexes / was: Re: [GENERAL] CSV-bulk import and defaults
Hello, Am 03.01.11 00:06, schrieb Adrian Klaver: On Sunday 02 January 2011 2:22:14 pm Thomas Schmidt wrote: well, I'm new to postgres and this is my post on this list :-) Anyway, I've to batch-import bulk-csv data into a staging database (as part of an ETL-like pocess). The data ought to be read via STDIN, however for keeping in simple and stupid, saving it to a file and importing afterwards is also an option. Sticking my nose into the docs, I noticed that copy[1] as well as pg_import[2] are able to do it. However, there are some additional columns of the staging table (job id, etc.) that have to be set in order to identify imported rows. These attributes are not part of the data coming from STDIN (since its meta-data) and I see no way for specifying default values for missing cvs columns. (imho copy and pg_bulkload will use table defaults for missing rows - do I miss something?). [1] http://www.postgresql.org/docs/9.0/static/sql-copy.html [2] http://pgbulkload.projects.postgresql.org/pg_bulkload.html Check out pgloader: http://pgloader.projects.postgresql.org/ Thanks a lot - that's what I need. :-) Btw. What about indexes? http://www.postgresql.org/docs/9.0/interactive/populate.html suggests to remove indexes before importing via copy (for obvious reasons). Does pgloader take indexes into account or do I need to handle 'em manually? Thanks in adance, Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Need advise for database structure for non linear data.
Hi, I need advise about a database structure. I need to capture data from the web about one specific subject on few specific websites and insert that data to a database. I have done this question here before, but I think I have not explained very well. The problem with this task is that the information is not linear, if I try to design tables with fields for all possible data I will end up with many row fields with NULL values. There are any problem with this(end up with many row fields with NULL values)? Or should I user other kind of structure? For example store the data in one field and that field containing an associative array with data. What I mean with non linear data is the following: array( 'name' = 'Don', 'age' = '31' ); array( 'name' = 'Peter', 'age' = '28', 'car' = 'ford', 'km' = '2000' ); In a specific website search I will store only name and age, and in other website I will store name, age, car and km. I don't know If I explain weel my problem. My english is not very good. Best Regards.
Re: [GENERAL] Need advise for database structure for non linear data.
Hello, Am 03.01.11 12:11, schrieb Andre Lopes: Hi, I need advise about a database structure. I need to capture data from the web about one specific subject on few specific websites and insert that data to a database. I have done this question here before, but I think I have not explained very well. What I mean with non linear data is the following: array( 'name' = 'Don', 'age' = '31' ); array( 'name' = 'Peter', 'age' = '28', 'car' = 'ford', 'km' = '2000' ); In a specific website search I will store only name and age, and in other website I will store name, age, car and km. I don't know If I explain weel my problem. My english is not very good. In theory, using a single table having three columns (array-id,key,value) will suit your needs. However, providing a simple key/value store is not the idea behind DBMS like postgres ... See: http://en.wikipedia.org/wiki/NoSQL http://en.wikipedia.org/wiki/Relational_database_management_system 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] Need advise for database structure for non linear data.
Andre Lopes wrote on 03.01.2011 12:11: array( 'name' = 'Don', 'age' = '31' ); array( 'name' = 'Peter', 'age' = '28', 'car' = 'ford', 'km' = '2000' ); In a specific website search I will store only name and age, and in other website I will store name, age, car and km. I don't know If I explain weel my problem. My english is not very good. That's exactly what the hstore data type supports: http://www.postgresql.org/docs/current/static/hstore.html Regards 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] Need advise for database structure for non linear data.
I can propose you something like this: website(id int, url varchar); attr_def (id int, name varchar); attr_val (id int, def_id reference attr_def.id, website_id int references website.id, value varchar); If all of your attributes in website are single valued then you can remove id from attr_val and use PK from website_id, def_id. Depending on your needs one or many from following indexes: attr_val(value) - search for attributes with value; attr_val(def_id, value) - search for given attributes with values; attr_val(website_id, def_id, value) - checks, if given site has attribue, search by values for given site and attribute; attr_val(def_id, website_id) - like above, without value searching; attr_val(website_id, value) - search for attributes on given site with value. Probably you will use 2nd or 3rd index. Example of search on website select d.name, v.value from attre_def d join attr_val v on (v.def_id = d.id) join website w on (v.website_id = w.id) where d.name = '' and w.url='http://somtehing' This is common map structure. Kind regards, Radosław Smogura On Mon, 03 Jan 2011 12:26:45 +0100, Thomas Schmidt postg...@stephan.homeunix.net wrote: Hello, Am 03.01.11 12:11, schrieb Andre Lopes: Hi, I need advise about a database structure. I need to capture data from the web about one specific subject on few specific websites and insert that data to a database. I have done this question here before, but I think I have not explained very well. What I mean with non linear data is the following: array( 'name' = 'Don', 'age' = '31' ); array( 'name' = 'Peter', 'age' = '28', 'car' = 'ford', 'km' = '2000' ); In a specific website search I will store only name and age, and in other website I will store name, age, car and km. I don't know If I explain weel my problem. My english is not very good. In theory, using a single table having three columns (array-id,key,value) will suit your needs. However, providing a simple key/value store is not the idea behind DBMS like postgres ... See: http://en.wikipedia.org/wiki/NoSQL http://en.wikipedia.org/wiki/Relational_database_management_system Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Loading posgres 9.0 libpq library on Linux versionRed Hat 3.4.6-3
Hi, I am trying to load the libpq library of PostGreSQL 9.0 version on Linux(Red Hat 3.4.6-3) and it is failing. Where as I could successfully load the libpq library of PostGreSQL 8.4 version using same code base. Does postGreSQL 9.0 version support the Red Hat 3.4.6-3 version? Thanks, Trupti
[GENERAL] Re: [NOVICE] Loading posgres 9.0 libpq library on Linux versionRed Hat 3.4.6-3
On Mon, 2011-01-03 at 17:23 +0530, Trupti Ghate wrote: I am trying to load the libpq library of PostGreSQL 9.0 version on Linux(Red Hat 3.4.6-3) and it is failing. What is the exact Red Hat release? Please send the output of cat /etc/redhat-release Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
[GENERAL] Re: [NOVICE] Loading posgres 9.0 libpq library on Linux versionRed Hat 3.4.6-3
Exact release is: [r...@tndev-linux-32 Adm]# cat /etc/redhat-release *Red Hat Enterprise Linux AS release 4 (Nahant Update 5)* [r...@tndev-linux-32 Adm]# 2011/1/3 Devrim GÜNDÜZ dev...@gunduz.org On Mon, 2011-01-03 at 17:23 +0530, Trupti Ghate wrote: I am trying to load the libpq library of PostGreSQL 9.0 version on Linux(Red Hat 3.4.6-3) and it is failing. What is the exact Red Hat release? Please send the output of cat /etc/redhat-release Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
[GENERAL] Re: [NOVICE] Loading posgres 9.0 libpq library on Linux versionRed Hat 3.4.6-3
On Mon, 2011-01-03 at 17:26 +0530, Trupti Ghate wrote: [r...@tndev-linux-32 Adm]# cat /etc/redhat-release *Red Hat Enterprise Linux AS release 4 (Nahant Update 5)* Ok, here are the RPMs for that: For 32-bit: http://yum.pgrpms.org/9.0/redhat/rhel-4-i386/repoview/letter_p.group.html For 64-bit: http://yum.pgrpms.org/9.0/redhat/rhel-4-x86_64/repoview/letter_p.group.html Grab and install postgresql90-libs for libpq. Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Need advise for database structure for non linear data.
Hello, Am 03.01.11 12:46, schrieb Radosław Smogura: I can propose you something like this: website(id int, url varchar); attr_def (id int, name varchar); attr_val (id int, def_id reference attr_def.id, website_id int references website.id, value varchar); If all of your attributes in website are single valued then you can remove id from attr_val and use PK from website_id, def_id. Depending on your needs one or many from following indexes: attr_val(value) - search for attributes with value; (...) Probably you will use 2nd or 3rd index. Example of search on website select d.name, v.value from attre_def d join attr_val v on (v.def_id = d.id) join website w on (v.website_id = w.id) where d.name = '' and w.url='http://somtehing' Imho its hard - (if not impossible) to recommand a specific database scheme (incl indexes) without knowing the applications taking plance behind it. Your schema is nice for specific querying, but might blow up if lots of data is stored in the database (joins, index-building might be time consuming). On the other hand, google put some effort into their BigTable http://en.wikipedia.org/wiki/BigTable for storing tons of data... Thus - it all depends on the usage :-) Thomas -- 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: [NOVICE] Loading posgres 9.0 libpq library on Linux versionRed Hat 3.4.6-3
Where can I find the binary distribution for Rhel4-i386? 2011/1/3 Devrim GÜNDÜZ dev...@gunduz.org On Mon, 2011-01-03 at 17:26 +0530, Trupti Ghate wrote: [r...@tndev-linux-32 Adm]# cat /etc/redhat-release *Red Hat Enterprise Linux AS release 4 (Nahant Update 5)* Ok, here are the RPMs for that: For 32-bit: http://yum.pgrpms.org/9.0/redhat/rhel-4-i386/repoview/letter_p.group.html For 64-bit: http://yum.pgrpms.org/9.0/redhat/rhel-4-x86_64/repoview/letter_p.group.html Grab and install postgresql90-libs for libpq. Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
[GENERAL] HNY-2011
Dear all, A very-very Happy New Year 2011 to all. May God Bless all of us to solve future problems. Thanks and Regards Adarsh Sharma -- 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] HNY-2011
Happy new year but spare me from any blessings, please /Nicklas 2011-01-03 skrev Adarsh Sharma : Dear all, A very-very Happy New Year 2011 to all. May God Bless all of us to solve future problems. Thanks and Regards Adarsh Sharma -- 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] Need advise for database structure for non linear data.
Hi, Thanks for the reply's. I was tempted to accept the Rodoslaw Smogura proposal. There will be about 100 websites to capture data on daily basis. Each website adds per day(average) 2 articles. Thomas talked about the noSQL possibility. What do you think would be better? I have no experience in noSQL and that could be a weakness. Best Regards, André On Mon, Jan 3, 2011 at 11:58 AM, Thomas Schmidt postg...@stephan.homeunix.net wrote: Hello, Am 03.01.11 12:46, schrieb Radosław Smogura: I can propose you something like this: website(id int, url varchar); attr_def (id int, name varchar); attr_val (id int, def_id reference attr_def.id, website_id int references website.id, value varchar); If all of your attributes in website are single valued then you can remove id from attr_val and use PK from website_id, def_id. Depending on your needs one or many from following indexes: attr_val(value) - search for attributes with value; (...) Probably you will use 2nd or 3rd index. Example of search on website select d.name, v.value from attre_def d join attr_val v on (v.def_id = d.id) join website w on (v.website_id = w.id) where d.name = '' and w.url='http://somtehing' Imho its hard - (if not impossible) to recommand a specific database scheme (incl indexes) without knowing the applications taking plance behind it. Your schema is nice for specific querying, but might blow up if lots of data is stored in the database (joins, index-building might be time consuming). On the other hand, google put some effort into their BigTable http://en.wikipedia.org/wiki/BigTable for storing tons of data... Thus - it all depends on the usage :-) 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] Cursors WITH HOLD
2010/12/30 pasman pasmański pasma...@gmail.com
Hello.
I use Postgres 8.4.5 via perl DBI.
And i try to use cursors WITH HOLD to materialize
often used queries.
My question is how many cursors may be
declared per session and which memory setting s
to adjust for them ?
I believe there's no maximum limit to the number of cursors in Postgres. It
is limited only by the amount of memory available to the Postgres process.
Regards,
--
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.EnterpriseDB.com
singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet
Mail sent from my BlackLaptop device
Re: [GENERAL] Cursors WITH HOLD
Thanks for reply. I do some checking and some queries boost very well :) pasman -- 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] Need advise for database structure for non linear data.
Hello, Am 03.01.11 14:14, schrieb Andre Lopes: Hi, Thanks for the reply's. I was tempted to accept the Rodoslaw Smogura proposal. There will be about 100 websites to capture data on daily basis. Each website adds per day(average) 2 articles. Thomas talked about the noSQL possibility. What do you think would be better? I have no experience in noSQL and that could be a weakness. Imho RDBMS do a very good job in managing data on a relational basis. However - there are alternatives and use cases for 'em and there is no holy grail... Not having any experience is a good point for not using it in production :-). However, if you've time to spare, looking into database design (plain-sql and not-only-sql) will help. I don't think that you get in trouble with a few hundered rows per day, but keep in mind, what queries are used. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Merging 2 rows in a table
Hello, through some obscure error (probably on my side) I have several thousand entries for Jan 1 and Jan 2 ending up in the ISO week 2011-52 instead of 2010-52 which breaks the bar chart at the top of my script http://preferans.de/user.php?id=OK504891003571 # select * from pref_money where id='OK324712148886'; id | money | yw +---+- OK324712148886 | 203 | 2010-46 OK324712148886 | 219 | 2010-49 OK324712148886 | 115 | 2010-51 OK324712148886 |63 | 2010-52 OK324712148886 |20 | 2011-01 OK324712148886 |10 | 2011-52 # \d pref_money Table public.pref_money Column | Type |Modifiers +---+- id | character varying(32) | money | integer | not null yw | character(7) | default to_char(now(), '-IW'::text) Indexes: pref_money_yw_index btree (yw) Foreign-key constraints: pref_money_id_fkey FOREIGN KEY (id) REFERENCES pref_users(id) I would like to fix that problem by adding 2011-52 values of money (10 above) to the 2010-52 values of money (63 above, should become 73) and then dropping all rows with the wrong week number yw='2011-52'. So I'm trying: # update pref_money set money=money+ (select money from pref_money where yw='2011-52') where yw='2010-52'; ERROR: more than one row returned by a subquery used as an expression ok, I probably need to specify the id for the subquery in round brackets above - so I try again: # update pref_money as m1 set money=money+ (select coalesce(money,0) from pref_money as m2 where m1.id=m2.id and m2.yw='2011-52') where m1.yw='2010-52'; ERROR: null value in column money violates not-null constraint Can anybody please help me here? Thank you Alex -- 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] Merging 2 rows in a table
2011/1/3 Alexander Farber alexander.far...@gmail.com: Hello, through some obscure error (probably on my side) I have several thousand entries for Jan 1 and Jan 2 ending up in the ISO week 2011-52 instead of 2010-52 which breaks the bar chart at the top of my script http://preferans.de/user.php?id=OK504891003571 # select * from pref_money where id='OK324712148886'; id | money | yw +---+- OK324712148886 | 203 | 2010-46 OK324712148886 | 219 | 2010-49 OK324712148886 | 115 | 2010-51 OK324712148886 | 63 | 2010-52 OK324712148886 | 20 | 2011-01 OK324712148886 | 10 | 2011-52 # \d pref_money Table public.pref_money Column | Type | Modifiers +---+- id | character varying(32) | money | integer | not null yw | character(7) | default to_char(now(), '-IW'::text) Indexes: pref_money_yw_index btree (yw) Foreign-key constraints: pref_money_id_fkey FOREIGN KEY (id) REFERENCES pref_users(id) I would like to fix that problem by adding 2011-52 values of money (10 above) to the 2010-52 values of money (63 above, should become 73) and then dropping all rows with the wrong week number yw='2011-52'. So I'm trying: # update pref_money set money=money+ (select money from pref_money where yw='2011-52') where yw='2010-52'; ERROR: more than one row returned by a subquery used as an expression ok, I probably need to specify the id for the subquery in round brackets above - so I try again: # update pref_money as m1 set money=money+ (select coalesce(money,0) from pref_money as m2 where m1.id=m2.id and m2.yw='2011-52') where m1.yw='2010-52'; ERROR: null value in column money violates not-null constraint update tab set money = money + COALESCE((SELECT ...) , 0) .. Regards Pavel Stehule Can anybody please help me here? Thank you Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) 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
Re: [GENERAL] Merging 2 rows in a table
Thank you Pavel, has worked: # update pref_money as m1 set money=money+coalesce((select money from pref_money as m2 where m1.id=m2.id and m2.yw='2011-52'),0) where m1.yw='2010-52'; UPDATE 2081 -- 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] problem updating from form
On 2011-01-03 06:29, Karen Springer wrote: We are running RHEL 4.1 which is why the newer version did not install with RHEL. RHEL 4.1 should be offering pgsql 8.1.15 in the apps channel (Red Hat Application Stack v1). - Jeremy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: pgloader an Indexes / was: Re: [GENERAL] CSV-bulk import and defaults
On Monday 03 January 2011 12:48:22 am Thomas Schmidt wrote: Thanks a lot - that's what I need. :-) Btw. What about indexes? http://www.postgresql.org/docs/9.0/interactive/populate.html suggests to remove indexes before importing via copy (for obvious reasons). Does pgloader take indexes into account or do I need to handle 'em manually? Thanks in adance, Thomas I don't know. I have used it to load data into holding tables that have no indexes. My guess is you will have to handle them manually. -- Adrian Klaver adrian.kla...@gmail.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] Finding recursive dependencies
Joel Jacobson j...@gluefinance.com writes: 2011/1/2 Tom Lane t...@sss.pgh.pa.us The thing you're missing is that implicit dependencies are really bidirectional: So, basically it's not possible to define a recursive query only making use of pg_depend to build an entire dependency tree of all oids? It appears to me it's necessary to join the object type specific tables, such as pg_rewrite, to build a complete tree? No, that's nonsense. The information is in pg_depend; it's just not expressed in a way that makes it easy to scan it in a single recursive query. If you could do something like select base-case union all select objid ... where refobjid matches union all select refobjid ... where objid matches and deptype = 'i' then it'd be easy, but you only get one UNION ALL per recursive query. 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] problem updating from form
On Sunday 02 January 2011 11:12:25 pm Karen Springer wrote: Hi Adrian, Yes, the complaints have increased with the number or rows and the number of users accessing the DB. The problem record looks like this. BarCode: W205179 PartNumber: 380-013 LRU: 380-013 PartsListRev SerialNumber MTN DocNum: 150-2380-XXX Comments SupplierNo DrawingNoRev WorkOrder BldStdRev: 1.02 Quantity Salesorder WOQuantity: 1 xmin: 1933434795 There is nothing that stands out with the data. I have been trying to update the WorkOrder field in my tests. The users complain that they have problems updating the WorkOrder, MTN Comments fields. This particular record has consistently had a problem (though not always), but users are unable to update other records as well. It doesn't occur with all records though. Most of the time the updates work fine. I test using a new Access DB that contains only a copy of this table and a form with the BarCode and WorkOrder fields. There isn't any code behind the form. Best regards, Karen I agree nothing really stands out. I think you will find more information, as you suggested in another post, on the psqlodbc list. If you do that, might I suggest giving the full configuration for the ODBC connection. -- Adrian Klaver adrian.kla...@gmail.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] Finding recursive dependencies
2011/1/3 Tom Lane t...@sss.pgh.pa.us: select refobjid ... where objid matches and deptype = 'i' then it'd be easy, but you only get one UNION ALL per recursive query. Ah, I see! Thanks for explaining. Now I get it. -- Best regards, Joel Jacobson Glue Finance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] WAL Archiving Stopped
Hi all I have a PostgreSQL 9.0.1 instance, with WAL Archiving. Today, after some failed tries to archive a WAL file, it stopped trying to archive the files, but the number of logfiles in the pg_xlog directory keep growing. Any ideas of what is going on? Norberto -- 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] Shit happens
On Sun, 2011-01-02 at 10:31 +0100, Dick Kniep wrote: Hi list, Thanks for the clear answer. However, this is the simple answer that is also in the manual. Yes I know it is not directly possible to get that data, but I am quite desparate to get the data back. If one way or another the data is (except for the 4 days we really have no data for) accessible, we will write a program to recover the data into the production database. So if anyone of you knows about a way to access the actual data in the WAL file (or a reference where to find enough information to do this) I would be very happy. The WAL segment files will contain a lot of information (including some full page images), which may be enough to recover some of your data. Obviously, you'll be missing most of the data from that 4-day period, but there is some hope. However, pulling that data out of the WAL and making sense out of it will be tricky, indeed (as Radosław already mentioned). Email lists are probably not the best way to make it through this kind of tricky recovery. You might consider contacting one of the PostgreSQL consulting companies: http://www.postgresql.org/support/professional_support where someone experienced with WAL recovery can help you personally. Some of these organizations employ people who have done extensive development on the WAL recovery system and know it _very_ well. Regards, Jeff Davis -- 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] problem updating from form
Le dimanche 02 janvier 2011 à 01:31 -0700, Karen Springer a écrit : We are using PostgreSQL 8.1.4 on Red Hat, Microsoft Access 2002 That is one of the worst versions of Access ever. Lots of bugs. Do try an other version (2K, 2003 are much better) and see if the problem persists. -- Vincent Veyron http://marica.fr/ Logiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique -- 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] CSV-bulk import and defaults
On 3 January 2011 11:22, Thomas Schmidt postg...@stephan.homeunix.net wrote: Thus - do you have any clue on designing an fast bulk-import for staging data? As you're talking about STDIN ... have you considered piping the input-data through awk or sed to achieve a pre-populated empty meta data field? Easy enough, low CPU overhead. Thanks in advance, Thomas Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.georgedillon.com/web/html_email_is_evil.shtml -- 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] WAL Archiving Stopped
archiver process will retry later; it never stops trying, sleep time is just longer. 2011/1/3, Norberto Delle betode...@gmail.com: Hi all I have a PostgreSQL 9.0.1 instance, with WAL Archiving. Today, after some failed tries to archive a WAL file, it stopped trying to archive the files, but the number of logfiles in the pg_xlog directory keep growing. Any ideas of what is going on? Norberto -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) 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
Re: [GENERAL] Need advise for database structure for non linear data.
On 01/03/2011 12:11 PM, Andre Lopes wrote: [snip] The problem with this task is that the information is not linear, if I try to design tables with fields for all possible data I will end up with many row fields with NULL values. There are any problem with this(end up with many row fields with NULL values)? Or should I user other kind of structure? For example store the data in one field and that field containing an associative array with data. As far as I understand NULL values are not really stored and a column with many NULLs is not a problem as such, but if it is part of an index the index might not be very useful. At least that's my understanding of how SQL databases work. If I got this wrong I hope someone will correct me. [snip] /Fredric attachment: Fredric_Fredricson.vcf -- 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] Need advise for database structure for non linear data.
Andre, From a distant view of your problem I would like to vote for Thomas Kellerer's proposal: Maintain only the data you need (to enhance import/sync performance) and use the hstore data type (as long as query performance is ok). Yours, S. 2011/1/3 Fredric Fredricson fredric.fredric...@bonetmail.com: On 01/03/2011 12:11 PM, Andre Lopes wrote: [snip] The problem with this task is that the information is not linear, if I try to design tables with fields for all possible data I will end up with many row fields with NULL values. There are any problem with this(end up with many row fields with NULL values)? Or should I user other kind of structure? For example store the data in one field and that field containing an associative array with data. As far as I understand NULL values are not really stored and a column with many NULLs is not a problem as such, but if it is part of an index the index might not be very useful. At least that's my understanding of how SQL databases work. If I got this wrong I hope someone will correct me. [snip] /Fredric -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) 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
Re: [GENERAL] Merging 2 rows in a table
On 2011-01-03, Alexander Farber alexander.far...@gmail.com wrote: Hello, through some obscure error (probably on my side) Column | Type |Modifiers +---+- id | character varying(32) | money | integer | not null yw | character(7) | default to_char(now(), '-IW'::text) '-IW' above should be 'IYYY-IW' (you may have made a similar mistake in other places too.) I have several thousand entries for Jan 1 and Jan 2 ending up in the ISO week 2011-52 instead of 2010-52 which breaks the bar chart at the top of my script http://preferans.de/user.php?id=OK504891003571 I would like to fix that problem by adding 2011-52 values of money (10 above) to the 2010-52 values of money (63 above, should become 73) and then dropping all rows with the wrong week number yw='2011-52'. perhaps something like this: update pref_money as dat set money=dast.money+ foo.money from pref_money as foo where dat.id=foo.id and dat.yw='2011-52' and foo.yw ='2012-52' ; -- ⚂⚃ 100% natural -- 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] Merging 2 rows in a table
This explains my problem, thanks! On Mon, Jan 3, 2011 at 7:52 PM, Jasen Betts ja...@xnet.co.nz wrote: On 2011-01-03, Alexander Farber alexander.far...@gmail.com wrote: through some obscure error (probably on my side) Column | Type | Modifiers +---+- id | character varying(32) | money | integer | not null yw | character(7) | default to_char(now(), '-IW'::text) '-IW' above should be 'IYYY-IW' -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Date Parameter To Query Confusing Optimizer
I have a JDBC-based application which passes date/time parameters using JDBC query parameters, which is performing very badly (ie. doing full table scans). In an effort to try to narrow down the problem, I am taking the query and running it in interactive SQL mode, but changing the date parameters (which are BETWEEN ? and ? clauses) and placing a date literal instead, using the date '2011-01-01' syntax. When I do this, the query runs instantly, obviously using indices on the tables involved. Now, I suspect the optimizer is taking the wrong path based on the usage of query parameters. I'm pretty surprised by this, because I would think the optimizer would do the same thing for any query parameter, however it arrived. Unfortunately for this situation, the code which forms the query actually is used for several database back-ends, and I don't want to change it to use postgres-specific query syntax if I can help it. I'm trying to avoid this at all costs. What's really weird, and makes me suspect an optimizer or protocol bug, is that if I place protocolVersion=2 as a JDBC parameter, the problem goes away. That is, I'm seeing the query take 1sec as opposed to 3min when using the legacy protocol. I stumbled on this based on reading that the older protocol sent everything as string, and inferred the type on the server side. Now, that's a reasonable workaround, but it does seem like I've hit either a Postgres server bug, optimizer or other, or a JDBC bug of some kind. Any help in narrowing down the problem is appreciated!
Re: [GENERAL] Date Parameter To Query Confusing Optimizer
On Mon, Jan 3, 2011 at 2:48 PM, Kurt Westerfeld kwesterf...@novell.com wrote: I have a JDBC-based application which passes date/time parameters using JDBC query parameters, which is performing very badly (ie. doing full table scans). In an effort to try to narrow down the problem, I am taking the query and running it in interactive SQL mode, but changing the date parameters (which are BETWEEN ? and ? clauses) and placing a date literal instead, using the date '2011-01-01' syntax. When I do this, the query runs instantly, obviously using indices on the tables involved. Try using PREPARE to simulate your issue. We ran into a similar issue using PHP and prepared statements, where the plan choices were occasionally abysmal depending what the filters were and their relative distributions within the table. http://www.postgresql.org/docs/current/interactive/sql-prepare.html -- 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] Shit happens
On 01/02/2011 11:19 PM, Dick Kniep wrote: Hi list, Thanks for the clear answer. However, this is the simple answer that is also in the manual. Yes I know it is not directly possible to get that data, but I am quite desparate to get the data back. If one way or another the data is (except for the 4 days we really have no data for) accessible, we will write a program to recover the data into the production database. So if anyone of you knows about a way to access the actual data in the WAL file (or a reference where to find enough information to do this) I would be very happy. What happened to the original data? You might have more luck with that than working with an incomplete PITR backup, depending on what went wrong. Otherwise, you may be able to use xlog analysis tools to extract information from the transaction logs, then attempt to manually reconstruct your database minus the lost changes for the period you have no records for. These tools are both rather old, and may need porting to new versions of Pg, but will serve as a good starting point. If you're as desperate as you sound, you may want to hire someone experienced with Pg's source code to port the tools and enhance them if necessary to fit your needs. http://xlogviewer.projects.postgresql.org/ http://pgfoundry.org/projects/xlogviewer/ Tom Lane wrote the original xlogviewer code, which was then enhanced by Diogo Biazus and later Euler Taveira de Oliveira. Tom is a core postgresql developer. I haven't seen Diogo or Euler around on the mailing lists, but that doesn't mean they're not involved. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_temp 101 question
My application creates/uses a temporary table X via multiple connections at the same time. Is there a way to determine which pg_temp_N belongs to the current connection? I need this to obtain list of attributes for the temporary table... All connections are using the same temp table name (by design made long time ago for another DB). So if I query: select T.schemaname, T.tablename, A.attname, A.atttypid, TY.typname, D.adsrc from pg_attribute A inner join pg_class C on (A.attrelid=C.oid) inner join pg_tables T on (C.relname=T.tablename) inner join pg_namespace NS on (NS.oid=C.relnamespace and NS.nspname=T.schemaname) inner join pg_type TY on (TY.oid=A.atttypid) left outer join pg_attrdef D on (D.adrelid=C.oid and D.adnum=A.attnum) where A.attnum0 and A.attisdropped='f' and T.schemaname like 'pg_temp%' and T.tablename='X' it returns list of all columns in all temporary tables. I just need columns list for temp table for the current connection. Thank you! -- 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_temp 101 question
Konstantin Izmailov pgf...@gmail.com writes: My application creates/uses a temporary table X via multiple connections at the same time. Is there a way to determine which pg_temp_N belongs to the current connection? It seems unlikely that you need to determine that explicitly to solve your problem. Just use WHERE C.oid = 'X'::regclass to constrain the pg_class query, and forget matching to the schema name at all. 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] pg_temp 101 question
Tom, thank you for the suggestion - it looks like it is working! I've found another solution by looking into psql source code: nspname like 'pg_temp%' AND pg_catalog.pg_table_is_visible(C.oid) can also be added to the query for the purpose. On 1/3/11, Tom Lane t...@sss.pgh.pa.us wrote: Konstantin Izmailov pgf...@gmail.com writes: My application creates/uses a temporary table X via multiple connections at the same time. Is there a way to determine which pg_temp_N belongs to the current connection? It seems unlikely that you need to determine that explicitly to solve your problem. Just use WHERE C.oid = 'X'::regclass to constrain the pg_class query, and forget matching to the schema name at all. 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] is it good to disable autovacuum and schedule routine vacuum process?
I am using Postgresql 9.0.1. I want to know which one is good regarding VACUUM - Routine VACUUM manualy or AutoVacuum. Recently, I found in my production that some tables were not vacuumed for a good period of time when the autovacuum was enabled and the DB was slow. I vacuumed the DB manually and the performance was good. Can anyone explain why this was happened and is it good to disable autovacuum and schedule routine vacuum process?
Re: [GENERAL] is it good to disable autovacuum and schedule routine vacuum process?
On 01/04/2011 04:45 PM, AI Rumman wrote: I am using Postgresql 9.0.1. I want to know which one is good regarding VACUUM - Routine VACUUM manualy or AutoVacuum. Recently, I found in my production that some tables were not vacuumed for a good period of time when the autovacuum was enabled and the DB was slow. I vacuumed the DB manually and the performance was good. Can anyone explain why this was happened and is it good to disable autovacuum and schedule routine vacuum process? Keep autovacuum enabled, and if required make it run *more* not less. If autovacuum is keeping up with your database's write load on 8.4 and above, you should not need to vacuum manually. See the PostgreSQL documentation and wiki for information on tuning autovacuum. -- Craig Ringer -- 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] Date Parameter To Query Confusing Optimizer
On 3 Jan 2011, at 23:48, Kurt Westerfeld wrote: I have a JDBC-based application which passes date/time parameters using JDBC query parameters, which is performing very badly (ie. doing full table scans). In an effort to try to narrow down the problem, I am taking the query and running it in interactive SQL mode, but changing the date parameters (which are BETWEEN ? and ? clauses) and placing a date literal instead, using the date '2011-01-01' syntax. When I do this, the query runs instantly, obviously using indices on the tables involved. Now, I suspect the optimizer is taking the wrong path based on the usage of query parameters. I'm pretty surprised by this, because I would think the optimizer would do the same thing for any query parameter, however it arrived. Unfortunately for this situation, the code which forms the query The problem here is that JDBC uses prepared statements for parameterised queries. By the very definition of a prepared statement the query plan gets stored before the parameter values are known, which forces the database to use a query plan that would work for every possible value of those parameters. Thus you end up with a generic query plan. This isn't often a problem, but if a significant number of your possible parameter values exist in a high percentage of your table rows, then chances are you'll end up with a plan with a sequential scan. You didn't tell what version of Postgres you're using - I recall recent versions (since 8.3?) are smarter about this particular scenario. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4d22ca9a11548321074132! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
