[GENERAL] Foreign tables using postgresql_fdw or odbc_fdw
Tried both, but they don't work for 9.1. I want to thank Adrian, without his help I would never find the link that confirmed the postgresql_fdw is not working. Would be nice to make all this information clear in Foreign Data Wrapper documentation at PostgreSQL manuals. I wasted at least 12 hours trying to make it work. The only real option is file_fdw, that is not that useful (I would connect my database to files just for curiosity - otherwise, I would use only an application that validates the file before importing into database for fast operations). odbc_fdw would work, but it's near to impossible for a normal human bean to compile using MSVC; and it's not available in binary format (see, I'm not a C developer for many, many years now... and I work in an hybrid environment with Windows and Linux machines). Sorry for the rant... I hope to save time for others who wish to use FDW in a real scenario. Regards, Edson Richter. -- 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] Alternative JDBC driver
Thanks for answer, we will try this driver, because it's more promising, if we will found some bugs or suggestion, You will be informed (I assume driver is stage that some ideas may be included with no problem). Btw, when I wanted to download driver from other station, I was saw Beta 1 driver (and I got 404) - after few refreshes We got Beta 2. Last time we think, about creating complete and modern free database system for j2ee platforms. Good luck! But, I think it's unneeded. Just use apply your patches, and make release, next day we will test it wit no doubt. If I will have time, I could support this as well too, just for clear fun. Regards, Jimmy 2011/9/16 Radosław Smogura rsmog...@softperience.eu: On Tue, 13 Sep 2011 10:18:46 +0200, Jimmy K. wrote: Hello, I asked this question on JDBC list, but it seems dead. I found alternative JDBC driver marked as Beta 2 (actually last time bumped to Beta 2.1) http://softperience.eu/pages/cmn/ngpgjdbc.xhtml. We still test it. Those features are, mainly, in our interest - binary transfer - possibility of loading large bytea in JVM with small heap (we loaded about 1GB in JVM with 256MB) - XA But, I would like to ask, if someone of You have tested it, and / or compared. We actually looking for general opinion if it's suitable for JavaEE + JPA/Hibernate Regards, J. Hello, The driver I develop is mainly focused for JavaEE + JPA. We and friendly company already, uses it during test and for some less critical, but live systems (in J2EE 6 environment) (during this tests we had found two problems, one is fixed, and 2nd looks like comes from PostgreSQL, but it will be fixed). If You ask for performance there is available test of original driver with some binary patches (http://blogs.oracle.com/jkshah/entry/postgres_8_4_testing_with). The driver started from our internal interest for making our apps stable (statement timeout), and faster (binary protocol), rest of changes came from propositions on forum (JDBC4 exceptions, possibility of loading large bytea in JVM with small heap, UDTs). Some of patches was sent for official version, but topics died. We have, as well, different conception, then is visible in original driver - we want to remove as much processing as possible from database, because app server is clusterizable, and db not. If you have some ideas or if you found bug, You may contact me or submit data through our web side. Last time we think, about creating complete and modern free database system for j2ee platforms. Best regards, Radoslaw Smogura http://softperience.eu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] New feature in file_fdw
Hi. I propose that some options for foreign tables may be stored as defaults in server object: format, encoding, delimiter, quote. -- 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] Foreign tables using postgresql_fdw or odbc_fdw
On Sunday, September 18, 2011 6:32:43 am Edson Carlos Ericksson Richter wrote: Tried both, but they don't work for 9.1. I want to thank Adrian, without his help I would never find the link that confirmed the postgresql_fdw is not working. Would be nice to make all this information clear in Foreign Data Wrapper documentation at PostgreSQL manuals. To be fair FDW is a brand new feature on a just released for production version of Postgres. As good as the Postgres docs are, they often take some time to catch up with all the ins and outs of the new bright and shiny features. I wasted at least 12 hours trying to make it work. The only real option is file_fdw, that is not that useful (I would connect my database to files just for curiosity - otherwise, I would use only an application that validates the file before importing into database for fast operations). Google is your friend:) It took me about 5 minutes using this search string- 'postgres dblink fdw' to find the link I posted previously. As to options take a look at: http://pgxn.org/tag/fdw/ odbc_fdw would work, but it's near to impossible for a normal human bean to compile using MSVC; and it's not available in binary format (see, I'm not a C developer for many, many years now... and I work in an hybrid environment with Windows and Linux machines). Development of Postgres and its associated software tends to happen on Unixen machines, so there somewhat of a lack of info on doing development under Windows. I personally have not tried to compile under Windows. From what I have read though, one heavily promoted option is to use MingW(http://www.mingw.org/) as the development environment in Windows. Sorry for the rant... I hope to save time for others who wish to use FDW in a real scenario. Another way to save someone time is to use MingW or MSVC to compile an extension and report back to the community your successes or failures. There are a lot of bright energetic people on this list who would be willing and able to help you in your endeavor. The knowledge gained would further the cause and make life easier on down the road for everyone. Regards, Edson Richter. Thanks, -- 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] Indexes not allowed on (read-only) views: Why?
Hi Craig, Thank you for the explanations. 2011/9/17 Craig Ringer ring...@ringerc.id.au: It sounds like they probably use a materialized view, possibly stored as an index-oriented table. That'd be a cool thing to support, but if done that way would require TWO new major features PostgreSQL doesn't have. Just to understand you saying two new major features: 1. native support for materialized views, 2. indexed (materialized) views, right? What do you mean by index-oriented table? (see my other thread about Index containing data values instead of pointers to data?) Stefan 2011/9/17 Craig Ringer ring...@ringerc.id.au: On 09/17/2011 05:47 AM, Stefan Keller wrote: A (read-only) view should behave like a table, right? CREATE INDEX t1_idx ON t1 (rem); ERROR: »v1« not a table SQL state: 42809 = Why should'nt it be possible to create indexes on views in PG? It's not so much that it's not allowed, as that it's not implemented and not very practical for non-materialized views. A normal (non-materialized) view doesn't have any data of its own, it pulls it from one or more other tables on the fly during query execution. The execution of a view is kind of similar to a set-returning function or a subquery, almost as if you'd substituted the view definition into the original query. That means that the view will use any indexes on the original table(s), but there isn't really even an opportunity to check for indexes on the view its self because the view's definition is effectively substituted into the query. If the view definition is complex enough that it does a lot of work where indexes on the original table(s) don't help, that work has to be done every time. It only really makes sense to have indexes on materialized views. PostgreSQL doesn't have any native support for materialized views, so it doesn't support indexes on views. What you *CAN* do is use triggers to maintain your own materialized views as regular tables, and have indexes on the tables you maintain using triggers. This is widely discussed on the mailing list and isn't hard to do, though it's tricky to make updates perform well with some kinds of materialized view query. And there is no practical reason since SQL Server can do it! See Creating Indexes on Views http://msdn.microsoft.com/en-us/library/aa933124%28v=sql.80%29.aspx It sounds like they probably use a materialized view, possibly stored as an index-oriented table. That'd be a cool thing to support, but if done that way would require TWO new major features PostgreSQL doesn't have. -- 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] Alternative JDBC driver
Jimmy, What was your question ? The JDBC list is not dead. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca 2011/9/18 Jimmy K. jimmypg...@gmail.com: Thanks for answer, we will try this driver, because it's more promising, if we will found some bugs or suggestion, You will be informed (I assume driver is stage that some ideas may be included with no problem). Btw, when I wanted to download driver from other station, I was saw Beta 1 driver (and I got 404) - after few refreshes We got Beta 2. Last time we think, about creating complete and modern free database system for j2ee platforms. Good luck! But, I think it's unneeded. Just use apply your patches, and make release, next day we will test it wit no doubt. If I will have time, I could support this as well too, just for clear fun. Regards, Jimmy 2011/9/16 Radosław Smogura rsmog...@softperience.eu: On Tue, 13 Sep 2011 10:18:46 +0200, Jimmy K. wrote: Hello, I asked this question on JDBC list, but it seems dead. I found alternative JDBC driver marked as Beta 2 (actually last time bumped to Beta 2.1) http://softperience.eu/pages/cmn/ngpgjdbc.xhtml. We still test it. Those features are, mainly, in our interest - binary transfer - possibility of loading large bytea in JVM with small heap (we loaded about 1GB in JVM with 256MB) - XA But, I would like to ask, if someone of You have tested it, and / or compared. We actually looking for general opinion if it's suitable for JavaEE + JPA/Hibernate Regards, J. Hello, The driver I develop is mainly focused for JavaEE + JPA. We and friendly company already, uses it during test and for some less critical, but live systems (in J2EE 6 environment) (during this tests we had found two problems, one is fixed, and 2nd looks like comes from PostgreSQL, but it will be fixed). If You ask for performance there is available test of original driver with some binary patches (http://blogs.oracle.com/jkshah/entry/postgres_8_4_testing_with). The driver started from our internal interest for making our apps stable (statement timeout), and faster (binary protocol), rest of changes came from propositions on forum (JDBC4 exceptions, possibility of loading large bytea in JVM with small heap, UDTs). Some of patches was sent for official version, but topics died. We have, as well, different conception, then is visible in original driver - we want to remove as much processing as possible from database, because app server is clusterizable, and db not. If you have some ideas or if you found bug, You may contact me or submit data through our web side. Last time we think, about creating complete and modern free database system for j2ee platforms. Best regards, Radoslaw Smogura http://softperience.eu -- 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] different unnest function [SOLVED]
Hi, to get the output OP wants, you need to expand and rewrap: create or replace function unnest2(anyarray) returns setof anyarray AS $BODY$ select array(select unnest($1[i:i])) from generate_series(array_lower($1,1), array_upper($1,1)) i; $BODY$ language 'sql'; Yup, this is what I need. Thanks -- Ondrej Ivanic (ondrej.iva...@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] PostgreSQL benchmarked on XFS vs ZFS vs btrfs vs ext4
On 17/09/11 00:09, Vick Khera wrote: On Tue, Sep 13, 2011 at 9:15 PM, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: However we have a new contender - ZFS performed *extremely* well on the latest Ubuntu setup - achieving triple the performance of regular ext4! Did you do any tuning to ZFS? There are many tweaks to it, like putting a cache disk in front of it, or moving the logs to SSD and such. I haven't run any produciton DBs on ZFS yet, but it sure is tempting. The speed penalty for the features it gives you (snapshots, robust against power fails, etc.) is worth the tradeoff. No, I didn't do that kind of tuning - agreed, it'd improve the performance. But then putting an SSD in the mix and storing journals on it would have improved the performance of XFS and ext4 as well.. I'll re-run the tests again in the future, no doubt, and hopefully I'll have a spare SSD by then. Also maybe I'll have learnt more about ZFS; I'm a bit of a noob at the moment. I agree that ZFS does seem to offer some rather nice features though! I'm tempted to start using it on my personal server now; although I'll be leaving it for some time before considering using it in production at work. Cheers, Toby -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] duplicate sequence, it is possible?
Hi everyone.
I have a table with a PK, this table has a lot of insert per second (100 ~
150 insert /sec)
Sometimes, a get a duplicate key error, but ID is generated from a function
(VOLATILE). This function has just one line (select nextval('XXX'))
Is possible that this function return same ID for two diff call?
Postgres version is 9.0
Thanks!
Re: [GENERAL] warm standby - apply wal archives
Syncing just WAL archive directory every minute should not be a problem at all (running rsync every minute for a data directory is not recommended). As said earlier, we had configured warm standby for a db of size 2 TB and wal archive generation was in 100s. We did not encounter any issues in running an rsync job with a frequency of even less than a minute. We made sure that rsync job is running on standby server (we were pulling the wal archives to standby). 1. compress the wal archives 2. rsync on standby site 3. uncompress on standby site Thanks Venkat On Sat, Sep 17, 2011 at 6:06 PM, MirrorX mirr...@gmail.com wrote: just another update since the system is up and running and one more question :p the secondary server is able to restore the wal archives practically immediately after they arrive. i have set a rsync cron job to send the new wals every 5 minutes. the procedure to transfer the files and to restore them takes about 30 seconds (the number of archives is about 20-30). i ve tried to set it to 2 minutes, and then the procedure takes about 20 seconds (both transfer and restoration) while i didnt notice any impact on the primary server (the procedure is initiated on the secondary server). what is your opinion about the time interval that the cron job should run? i ve read many articles online indicating that rsync should not run every 1 minute, but in my case isn't it different since it just syncs two folder containing only wals and not the whole disks? plus both folders on the servers are in different partitions. thx in advance for your insight -- View this message in context: http://postgresql.1045698.n5.nabble.com/warm-standby-apply-wal-archives-tp4770567p4813659.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] index row requires 10040 bytes, maximum size is 8191
Craig Ringer writes: Thoughts, folks? Does this matter in practice, since anything you'd want to index will in practice be small enough or a candidate for full-text indexing? Here's my case: the field is meant to hold a person's name, so it's usually well under the 8191-byte limit. I want it indexed so that I can sort on it quickly. I also want it to be robust against abuse, so if someone does enter the text of _War and Peace_ as their name, I want to handle that gracefully. I can achieve that by my own programming, outside of postgresql, and that's fine if I'm the only one who has gotten into this fix. Otoh if it's a common problem then there's a reason why removing the limitation might matter in practice. Regards, --- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
