Re: [GENERAL] Moving avg using SQL
In response to ? : How can I do a moving avg by only using SQL? Which version do you have? Since 8.4 we have CTE aka windowing functions, a simple axample: test=*# select n, last_value(n) over mywin , avg(n) over mywin from generate_series(1,20) n window mywin as (partition by (n-1)/4 rows between unbounded preceding and unbounded following); n | last_value | avg ++- 1 | 4 | 2.5000 2 | 4 | 2.5000 3 | 4 | 2.5000 4 | 4 | 2.5000 5 | 8 | 6.5000 6 | 8 | 6.5000 7 | 8 | 6.5000 8 | 8 | 6.5000 9 | 12 | 10.5000 10 | 12 | 10.5000 11 | 12 | 10.5000 12 | 12 | 10.5000 13 | 16 | 14.5000 14 | 16 | 14.5000 15 | 16 | 14.5000 16 | 16 | 14.5000 17 | 20 | 18.5000 18 | 20 | 18.5000 19 | 20 | 18.5000 20 | 20 | 18.5000 (20 rows) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) -- 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] N + 1 replication
Hi, Does anyone know if there is any N + 1 replication for Postgres? Could someone please point me to the right direction? I would build up a multimaster-cluster with the common software like Bucardo, PgPool, PgCLuster or Sequoia and connect it with Slony-I + Heartbeat. -- Greetings, Robert -- 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] [Q] optmizing postgres for 'single client' / many small queries
Hi, Tom Lane t...@sss.pgh.pa.us writes: Just out of curiosity, does anyone know of any ORM anywhere that doesn't suck? They seem to be uniformly awful, at least in terms of their interfaces to SQL databases. If there were some we could recommend, maybe people would be less stuck with these bogus legacy architectures. It seems like people interrested into ORMs are the one who do not want to tackle SQL... and for people having some time to spend on the possibility of finding a good ORM: http://blogs.tedneward.com/2006/06/26/The+Vietnam+Of+Computer+Science.aspx A more practical discussion seems to be here (I still have to read it): http://omniti.com/seeds/orms-done-right Regards, -- dim PS: In short my advice is always to choose an ORM where it's easy to bypass query generation, and stick to not letting it generate SQL. Sometime basic CRUD is ok though (INSERT/UPDATE/DELETE one object/row at a time). -- 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 help in copying a table from one database to other
Hi, I want to copy a table say employeedetails from employee database to library database. Can you help me giving the detailed steps to do this. Thanks And Regards, Rekha. -- Rekha Pai Senior Software Consultant SoftJin Technologies Pvt. Ltd. #102, Mobius Tower, SJR I-Park, EPIP, Whitefield, Bangalore 560066 Phone: +91-80-4177 Fax: +91-80-41157070 Business Disclaimer This e-mail message and any files transmitted with it are intended solely for the use of the individual or entity to which they are addressed. It may contain confidential, proprietary or legally privileged information. If you are not the intended recipient please be advised that you have received this message in error and any use is strictly prohibited. Please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender by return mail. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. ___ -- 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] easy task: concurrent select-updates
Andy Colson wrote: Kevin McConnell wrote: I think you could also do something roughly similar in a statement by using a RETURNING clause on the update, such as: update msg set busy = true where id = (select min(id) from msg where busy = false) returning *; I had thought of that, but you'd need to add one thing, in the update ' and busy = false ', cuz two people may get the same id from the select min(id). update msg set busy = true where busy = false and id = (select min(id) from msg where busy = false) returning *; but then you'd have to fire it over-and-over until you actually got a row updated. Seemed easer to put the loop in function, then you can: select id from getmsg(); Thanks a lot for your solution! It works great for now. Here is the thing I did following your advice: CREATE TYPE queued_msg_row AS (id bigint ,sender character varying ,text text ... ,msg_type integer); CREATE OR REPLACE FUNCTION public.get_queued_msg (_route_idinteger ,_channel_id integer) RETURNS queued_msg_row LANGUAGE plpgsql AS $function$ declare rec queued_msg_row; begin for rec in SELECT id,sender,text, ... , msg_type FROM msg_queue WHERE busy=false AND route_id=_route_id ORDER BY priority DESC, date_time ASC LIMIT 10 loop UPDATE msg_queue SET busy=true, channel_id=_channel_id WHERE id = rec.id AND busy=false; if found then return rec; end if; end loop; return NULL; end; $function$ The only problem that remains is that this function returns an empty row when it should return NULL (no row), but that's not a critical issue. Best regards, Nick. -- 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 help in copying a table from one database to other
In response to Rekha Ravi Pai : Hi, I want to copy a table say employeedetails from employee database to library database. Can you help me giving the detailed steps to do this. pg_dump -t insert the table-name insert the source-db | psql insert destination db In you case: pg_dump -t employeedetails employee | psql library Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] strange error occurs when adding index
Hi, When I tried to add the following index, I get some strange error. Does anyone know what these errors mean and how to fix it? Here is the index query: create index idx_product_items_digits on product_items using gist (digits gist_prefix_range_ops,product_id) Here is the error: NOTICE: __pr_penalty(sa[], a1928901[]) orig-first=0 orig-last=0NOTICE: __pr_penalty(sa[], 1206323[]) orig-first=0 orig-last=0 NOTICE: __pr_penalty(a1206329[], 1206370[]) orig-first=0 orig-last=0NOTICE: __pr_penalty(a1206329[], a1206328[]) orig-first=0 orig-last=0NOTICE: __pr_penalty(a120632[8-9], 1206369[]) orig-first=56 orig-last=57 NOTICE: __pr_penalty(a120632[8-9], a1206327[]) orig-first=56 orig-last=57NOTICE: __pr_penalty(a120632[7-9], 1206368[]) orig-first=55 orig-last=57NOTICE: __pr_penalty(a120632[7-9], a1206326[]) orig-first=55 orig-last=57NOTICE: __pr_penalty(a120632[6-9], 1206367[]) orig-first=54 orig-last=57NOTICE: __pr_penalty(a120632[6-9], a1206325[]) orig-first=54 orig-last=57NOTICE: __pr_penalty(a120632[5-9], 1206366[]) orig-first=53 orig-last=57NOTICE: __pr_penalty(a120632[5-9], a1206324[]) orig-first=53 orig-last=57NOTICE: __pr_penalty(a120632[4-9], 1206365[]) orig-first=52 orig-last=57NOTICE: __pr_penalty(a120632[4-9], a1206323[]) orig-first=52 orig-last=57NOTICE: __pr_penalty(a120632[3-9], 1206364[]) orig-first=51 orig-last=57NOTICE: __pr_penalty(a120632[3-9], a1206322[]) orig-first=51 orig-last=57NOTICE: __pr_penalty(a120632[2-9], 1206363[]) orig-first=50 orig-last=57NOTICE: __pr_penalty(a120632[2-9], a1206321[]) orig-first=50 orig-last=57NOTICE: __pr_penalty(a120632[1-9], 1206362[]) orig-first=49 orig-last=57NOTICE: __pr_penalty(a120632[1-9], a1206320[]) orig-first=49 orig-last=57NOTICE: __pr_penalty(a120632[0-9], 1206361[]) orig-first=48 orig-last=57NOTICE: __pr_penalty(a120632[0-9], a1206319[]) orig-first=48 orig-last=57NOTICE: __pr_penalty(a12063[1-2], 1206359[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[1-2], a1206318[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[1-2], 1206358[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[1-2], a1206315[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[1-2], 1206357[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[1-2], a1206314[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[1-2], 1206356[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[1-2], a1206313[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[1-2], 1206355[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[1-2], a1206312[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[1-2], 1206354[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[1-2], a1206310[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[1-2], 1206353[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[1-2], a1206309[]) orig-first=49 orig-last=50NOTICE: __pr_penalty(a12063[0-2], 1206352[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], a1206307[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], 1206351[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], a1206306[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], 1206350[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], a1206304[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], 1206349[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], a1206303[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], 1206347[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], a1206302[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], a1952240[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], a1206301[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], a1952240[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], a1206300[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], a1952240[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a12063[0-2], a1206299[]) orig-first=48 orig-last=50NOTICE: __pr_penalty(a1206[2-3], a1952240[]) orig-first=50 orig-last=51NOTICE: __pr_penalty(a1206[2-3], a1206298[]) orig-first=50 orig-last=51NOTICE: __pr_penalty(a1206[2-3], a1952240[]) orig-first=50 orig-last=51NOTICE: __pr_penalty(a1206[2-3], a1206297[]) orig-first=50 orig-last=51NOTICE: __pr_penalty(a1206[2-3], a1952240[]) orig-first=50 orig-last=51NOTICE: __pr_penalty(a1206[2-3], a1206296[]) orig-first=50 orig-last=51NOTICE: __pr_penalty(a1206[2-3], a1952240[]) orig-first=50 orig-last=51NOTICE: __pr_penalty(a1206[2-3], a1206295[]) orig-firs Thanks for your help. jb
Re: [GENERAL] query speed question
On 3 Sep 2009, at 23:11, Christopher Condit wrote: I have two tables that are georeferenced (although in this case I'm not using PostGIS) that I need to join. A ( lat | lon | depth | value) |A| = 1,100,000 B ( lat | lon | attributes) |B| = 14,000,000 A is a special case because the lat / lon values are all at half degree intervals (ie 33.5, -12.5). The lat/lons in B are arbitrary. I've written a function in B called getSpecialLat(latitude) and getSpecialLon(longitude) to calculate the correct A latitude and built an index on both functions. My guess is that those functions round lat and lon values to their nearest half-degree interval counterpart as in table A? I assume you marked that function immutable? Is the return type indeed a numeric, as there are some explicit casts in the query plan? Here's the query that I'm trying, but it's rather slow: SELECT B.* FROM B, (SELECT lat, lon FROM A WHERE value 0 AND value 2 AND depth = 0) AS foo WHERE getSpecialLat(B.lat) = foo.lat AND getSpecialLon(B.lon) = foo.lon Nested Loop (cost=3569.88..32055.02 rows=1414 width=422) - Index Scan using A_valueidx on A (cost=0.00..555.26 rows=6 width=16) Index Cond: ((value 0) AND (value 2)) Filter: (depth = 0) - Bitmap Heap Scan on B (cost=3569.88..5029.48 rows=424 width=422) Recheck Cond: ((getSpecialLon((B.lon)::numeric) = A.lon) AND (getSpecialLat((B.lat)::numeric) = A.lat)) - BitmapAnd (cost=3569.88..3569.88 rows=424 width=0) - Bitmap Index Scan on Blonidx (cost=0.00..1760.38 rows=84859 width=0) Index Cond: (getSpecialLon((B.lon)::numeric) = A.lon) - Bitmap Index Scan on Blatidx (cost=0.00..1766.81 rows=84859 width=0) Index Cond: (getSpeicalLat ((B.latitude)::numeric) = A.lat) Am I missing something in terms of speeding up this query? Hard to tell without knowing where most time gets spent. An EXPLAIN ANALYSE would tell. You could try comparing integers instead of numerics, simply by multiplying your half-degree values by 10 (or by 2) and cast them to int. Integer comparisons are typically faster than numerics. It's hard to tell whether that does indeed take up a significant amount of time without the above ;) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4aa0f4d811866722913219! -- 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] query speed question
In response to Christopher Condit con...@sdsc.edu: I have two tables that are georeferenced (although in this case I'm not using PostGIS) that I need to join. A ( lat | lon | depth | value) |A| = 1,100,000 B ( lat | lon | attributes) |B| = 14,000,000 A is a special case because the lat / lon values are all at half degree intervals (ie 33.5, -12.5). The lat/lons in B are arbitrary. I've written a function in B called getSpecialLat(latitude) and getSpecialLon(longitude) to calculate the correct A latitude and built an index on both functions. Here's the query that I'm trying, but it's rather slow: SELECT B.* FROM B, (SELECT lat, lon FROM A WHERE value 0 AND value 2 AND depth = 0) AS foo WHERE getSpecialLat(B.lat) = foo.lat AND getSpecialLon(B.lon) = foo.lon Nested Loop (cost=3569.88..32055.02 rows=1414 width=422) - Index Scan using A_valueidx on A (cost=0.00..555.26 rows=6 width=16) Index Cond: ((value 0) AND (value 2)) Filter: (depth = 0) - Bitmap Heap Scan on B (cost=3569.88..5029.48 rows=424 width=422) Recheck Cond: ((getSpecialLon((B.lon)::numeric) = A.lon) AND (getSpecialLat((B.lat)::numeric) = A.lat)) - BitmapAnd (cost=3569.88..3569.88 rows=424 width=0) - Bitmap Index Scan on Blonidx (cost=0.00..1760.38 rows=84859 width=0) Index Cond: (getSpecialLon((B.lon)::numeric) = A.lon) - Bitmap Index Scan on Blatidx (cost=0.00..1766.81 rows=84859 width=0) Index Cond: (getSpeicalLat((B.latitude)::numeric) = A.lat) Am I missing something in terms of speeding up this query? I'd be interested to see if the query rewritten as a JOIN would be faster. I can write it like this: select b.* from b join a on (getwoalatitude(b.latitude::numeric) = a.lat and getwoalongitude(b.longitude::numeric) = a.lon) where a.value 0 and a.value 2 and a.depth = 0 which results in this plan: Nested Loop (cost=1387.20..13152982.35 rows=1625767 width=422) - Index Scan using a_depthidx on a_(cost=0.00..1464.07 rows=6897 width=16) Index Cond: (depth = 0) Filter: ((value 0::numeric) AND (value 2::numeric)) - Bitmap Heap Scan on b (cost=1387.20..1686.37 rows=424 width=422) Recheck Cond: ((getSpecialLon((b.lon)::numeric) = a.lon) AND (getSpecialLat((b.lat)::numeric) = a.lat)) - BitmapAnd (cost=1387.20..1387.20 rows=424 width=0) - Bitmap Index Scan on Blonidx (cost=0.00..672.15 rows=84859 width=0) Index Cond: (getSpecialLon((b.lon)::numeric) = a.lon) - Bitmap Index Scan on Blatidx (cost=0.00..672.36 rows=84859 width=0) Index Cond: (getSpecialLat((b.lat)::numeric) = a.lat) However it's still taking ages to execute (over five minutes - I stopped it before it finished) Do you really expect that query to return 1.6M rows? I doubt it, since the subselect version only returns 1400. If you do a vacuum analyze on those two tables, does it speed either of the queries up? Try set enable_nestloop=off and rerun the two queries. If that helps and analyze didn't then perhaps you need to increase the statistics target on those two tables, or perhaps you'll just have to use that set command to force the planner to avoid a nested loop. Hope one of these helps. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] strange error occurs when adding index
On Fri, Sep 04, 2009 at 05:42:31PM +0800, Juan Backson wrote: When I tried to add the following index, I get some strange error. Does anyone know what these errors mean and how to fix it? Here is the index query: create index idx_product_items_digits on product_items using gist (digits gist_prefix_range_ops,product_id) these are not errors, just notices. are you sure you have the latest prefix version? Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] where clause question
Hello, Is it possible to perform selects in a where clause of a statement? Given a statement as follows: SELECT foo.foo_id, foo.name FROM foo, bar WHERE foo.bar_id = bar.bar_id AND bar.name = 'martini'; I'm looking for a way to recast it so that the select and from clauses refer to a single table and the join referencing the second table occurs in the where clause. For example, something like this: SELECT foo.foo_id, foo.name FROM foo WHERE (SELECT * FROM foo, bar WHERE ...) foo.bar_id = bar.bar_id AND bar.name = 'martini'; I've explored the where exists clause, but it's not supported by the application toolkit I'm using. AFAIK, I've only got access to where ... Thanks in advance! Scott -- 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] PL/Perl 64-bit and sending emails
Hi, Steve Atkins st...@blighty.com writes: On Sep 3, 2009, at 11:30 AM, Mark Lubratt wrote: Or, does someone know of another way to get the backend to send an email? Have a queue table in the database you put your emails into and an external process that polls the table, sends the email and deletes the entry from the queue. Apart from avoiding the ickiness of doing high latency work from a database function this also makes sending email transaction safe - if the transaction rolls back after sending the email, the email doesn't get sent. Using listen/notify based on a trigger on the table makes it a little more responsive. This comes up fairly often. It's probably worth doing a tidy perl daemon to handle it and stashing it up on pgfoundry. Or have a look at PGQ which is made to handle this kind of queue processing: http://wiki.postgresql.org/wiki/Skytools http://wiki.postgresql.org/wiki/PGQ_Tutorial http://kaiv.wordpress.com/2007/10/19/skytools-database-scripting-framework-pgq/ 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] Got could not truncate directory pg_multixact/offsets: apparent wraparound
Gordon Shannon escribió: Hello, running 8.4 on Centos. Been running production for 6 months. Never saw this message until tonight: LOG: could not truncate directory pg_multixact/offsets: apparent wraparound My caffeing level is too low yet to know for sure, but I think this is innocuous (particularly so if there's a single file in that directory, because then there's nothing to truncate anyway). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] comment on constraint
Hi, There is a question in the german pg-forum: It is possible to add a comment on a constraint, but \dd doesn't display that comment. There is also a old question in this mailing-list without an answer: http://archives.postgresql.org/pgsql-general/2003-07/msg01448.php I think, this is a bug, isn't it? Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] comment on constraint
Andreas Kretschmer akretsch...@spamfence.net wrote: There is a question in the german pg-forum: It is possible to add a comment on a constraint, but \dd doesn't display that comment. There is also a old question in this mailing-list without an answer: http://archives.postgresql.org/pgsql-general/2003-07/msg01448.php I think, this is a bug, isn't it? Why? The man page for psql clearly says: |\dd[S] [ pattern ] | Shows the descriptions of objects matching the | pattern, or of all visible objects if no argu- | ment is given. But in either case, only objects | that have a description are listed. By default, | only user-created objects are shown; supply a | pattern or the S modifier to include system ob- | jects. ‘‘Object’’ covers aggregates, functions, | operators, types, relations (tables, views, in- ^^^ | dexes, sequences), large objects, rules, and | triggers. [...] ^ So no comments are shown for constraints or table columns or ... Tim -- 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] where clause question
On 4 Sep 2009, at 15:47, Scott Frankel wrote: Hello, Is it possible to perform selects in a where clause of a statement? Given a statement as follows: SELECT foo.foo_id, foo.name FROM foo, bar WHERE foo.bar_id = bar.bar_id AND bar.name = 'martini'; I've explored the where exists clause, but it's not supported by the application toolkit I'm using. AFAIK, I've only got access to where ... Have you tried a view? Is that some in-house toolkit you're using? If not, could you tell what it is so that people can chime in with ways to use that toolkit to get it do what you want or at least know what toolkit to avoid? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4aa1375011861997820494! -- 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] Add Large Object support to database programmatically
Wow, do I feel stupid now! After creating the database, 15 tables and a trigger using the same exact process, I somehow overlooked adding the spaces to the front of each line. %-| Thanks, that worked great! I guess I stared at the code too long to see it. acordner wrote: I have been working to create a VB6 program to automatically create a PostgreSQL database, tables and triggers for an application I am updating. I have everything working great, except one of my tables needs to store a bitmap image. I am using the Large Object (lo) contrib module to do this. Using pgAdmin III, I can run the Query Tool and load the lo.sql file from the \share\contrib folder and execute it on my database and it succeeds. What I need to be able to do is load this contrib module on the fly from VB6 after creating my database. Using the content of the lo.sql file, I created the following code: Private Function AddLargeObjectDataType(connConnection As ADODB.Connection) As Boolean Dim cmdCommand As New ADODB.Command With cmdCommand .ActiveConnection = conConnection .CommandType = adCmdText .CommandText = SET search_path = public; CREATE DOMAIN lo AS pg_catalog.oid; _ CREATE FUNCTION lo_oid(lo) RETURNS pg_catalog.oid AS _ 'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT IMMUTABLE; _ CREATE FUNCTION lo_manage() RETURNS pg_catalog.trigger _ AS '$libdir/lo' LANGUAGE C; Call .Execute End With Set cmdCommand = Nothing End Function However, when I execute this code, I get a SQL syntax error at or near '$libdir/lo' and the contrib module is not loaded. So I tried using a hard coded path 'C:\Program Files\PostgreSQL\8.1\lib\lo' instead of '$libdir/lo' and it also fails. I tried using double backslashes, same result. Forward slashes, same result. Any ideas? -- View this message in context: http://www.nabble.com/Add-Large-Object-support-to-database-programmatically-tp25283311p25295203.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] where clause question
On Fri, Sep 4, 2009 at 9:47 AM, Scott Frankellekn...@pacbell.net wrote: Hello, Is it possible to perform selects in a where clause of a statement? Given a statement as follows: SELECT foo.foo_id, foo.name FROM foo, bar WHERE foo.bar_id = bar.bar_id AND bar.name = 'martini'; I'm looking for a way to recast it so that the select and from clauses refer to a single table and the join referencing the second table occurs in the where clause. For example, something like this: SELECT foo.foo_id, foo.name FROM foo WHERE (SELECT * FROM foo, bar WHERE ...) foo.bar_id = bar.bar_id AND bar.name = 'martini'; I've explored the where exists clause, but it's not supported by the application toolkit I'm using. AFAIK, I've only got access to where ... where clauses is basically a set of boolean expressions. It's not completely clear how to wrap that inside what you are trying to do. you can do this: WHERE something = (SELECT * FROM foo, bar WHERE ...) or this: WHERE (SELECT count(*) FROM foo, bar WHERE ...) 0 for example. however, I'd advise dumping the application framework as a long term objective. Another general tactic to try and express what you are looking for in a view and query the view in a more regular way. This is likely your best bet. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Verifying a PITR
What would be the best way to verify that a PITR came up with *all* the expected data? This is mostly for a controlled failover, where I manually bring down the primary server, and shouldn't ever lose a transaction. If I need to use something like txid_current(), how do I ensure that it's the last transaction before shutdown, and the first after recovery? Thanks -jim -- 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] Moving avg using SQL
On Fri, 2009-09-04 at 08:03 +0200, A. Kretschmer wrote: Which version do you have? Since 8.4 we have CTE aka windowing functions, a simple axample: Minor terminology correction: CTE stands for Common Table Expression, i.e. WITH [RECURSIVE]. 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] where clause question
On Fri, Sep 04, 2009 at 06:47:24AM -0700, Scott Frankel wrote: Hello, Is it possible to perform selects in a where clause of a statement? Given a statement as follows: SELECT foo.foo_id, foo.name FROM foo, bar WHERE foo.bar_id = bar.bar_id AND bar.name = 'martini'; I'm looking for a way to recast it so that the select and from clauses refer to a single table and the join referencing the second table occurs in the where clause. For example, something like this: SELECT foo.foo_id, foo.name FROM foo WHERE (SELECT * FROM foo, bar WHERE ...) foo.bar_id = bar.bar_id AND bar.name = 'martini'; I've explored the where exists clause, but it's not supported by the application toolkit I'm using. AFAIK, I've only got access to where ... Sounds like a great reason to modify, or if you can't modify, replace, that application toolkit. This won't be the last time it will get in your way. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] where clause question
Hello, Is it possible to perform selects in a where clause of a statement? Given a statement as follows: SELECT foo.foo_id, foo.name FROM foo, bar WHERE foo.bar_id = bar.bar_id AND bar.name = 'martini'; I'm looking for a way to recast it so that the select and from clauses refer to a single table and the join referencing the second table occurs in the where clause. For example, something like this: SELECT foo.foo_id, foo.name FROM foo WHERE (SELECT * FROM foo, bar WHERE ...) foo.bar_id = bar.bar_id AND bar.name = 'martini'; I've explored the where exists clause, but it's not supported by the application toolkit I'm using. AFAIK, I've only got access to where ... Thanks in advance! Scott -- 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] [Q] optmizing postgres for 'single client' / many small queries
Tom Lane t...@sss.pgh.pa.us writes: Just out of curiosity, does anyone know of any ORM anywhere that doesn't suck? They seem to be uniformly awful, at least in terms of their interfaces to SQL databases. If there were some we could recommend, maybe people would be less stuck with these bogus legacy architectures. I personally like sqlalchemy - http://www.sqlalchemy.org/. Some of the good things: 1. Table reflection, i.e. dynamic generation of mapped python classes - great at development time - no mismatch between db and client side code. 2. Easily extensible - about 10 lines of code gives you an interface to Geos geometries retrieved from PostGIS 3. It seems to be possible to produce arbitrarily complex sql statements. OK, they're usually less readable than sql, but for dynamic query generation it definitely beats manually chopping up text. Cheers, 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] Create language PLPERL error
Shakil Shaikh wrote: I tried installing this but am now getting the following error when trying to install plperl: ERROR: could not access file $libdir/plperl: No such file or directory Apparently this means that the version of Postgresql I have wasn't compiled with support for plperl. How would I find this out, and hopefully fix it? Did you install the postgresql-plperl package? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Create language PLPERL error
From: Alvaro Herrera alvhe...@commandprompt.com Remove that, and install them from Martin Pitt's repository: https://launchpad.net/~pitti/+archive/postgresql The one-click installer does not integrate well with the platform. Avoid using them. Hi, thanks for the tip. I tried installing this but am now getting the following error when trying to install plperl: ERROR: could not access file $libdir/plperl: No such file or directory Apparently this means that the version of Postgresql I have wasn't compiled with support for plperl. How would I find this out, and hopefully fix it? Shak -- 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_ctl with unix domain socket?
Josef Wolf j...@raven.inka.de writes: pg_ctl -Ddb -o -h '' -k `pwd`/db -l postgreslog start This works, but when I add the -w option, it waits all the 60 seconds. I don't believe pg_ctl is smart enough to dredge the -k option out of -o and figure out that it has to look there for the socket. In general I'd advise against using -k, as that will break nearly all clients not only pg_ctl. If you need to put the socket in a nonstandard place, it's most convenient to wire the knowledge into libpq by changing DEFAULT_PGSOCKET_DIR at build time. AFAIR that requires manually editing pg_config_manual.h. 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] strange error occurs when adding index
hubert depesz lubaczewski dep...@depesz.com writes:
On Fri, Sep 04, 2009 at 05:42:31PM +0800, Juan Backson wrote:
When I tried to add the following index, I get some strange error. Does
anyone know what these errors mean and how to fix it?
Here is the index query:
create index idx_product_items_digits on product_items using gist (digits
gist_prefix_range_ops,product_id)
these are not errors, just notices. are you sure you have the latest
prefix version?
I just realized earlier this week that the pgfoundry main page for
prefix was proposing the very old (as in avoid it) 0.2 version. It's now
fixed to list the current 1.0~rc2 version, which you'll find also in
debian testing and sid:
http://pgfoundry.org/projects/prefix/
http://packages.debian.org/search?searchon=sourcenameskeywords=prefix
This version still comes with #define DEBUG (hey, it's a release
candidate) and penalty() is chatty on some cases where it finds that
your prefix ranges are not containing only numbers, because the penalty
computation isn't really verified against the general case... but should
work: you just won a non-numeric-only prefix_range testing ticket :)
Have you got anything to report performance wise?
Given:
__pr_penalty(sa[], a1928901[]) orig-first=0 orig-last=0
NOTICE: __pr_penalty(sa[], 1206323[]) orig-first=0 orig-last=0
Could you report the result of:
SELECT pr_penalty('sa', 'a1928901'), pr_penalty('sa', '1206323');
If you're happy with performances as is, I'll remove the NOTICE and
Assert(), if not, we'll have to either find a more general algorithm or
limit the accepted inputs.
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] where clause question
you'll need to create an alias beforehand SELECT foo.foo_id, foo.name FROM foo, (SELECT * FROM foo, bar WHERE ...) bar WHERE foo.bar_id = bar.bar_id AND bar.name = 'martini'; Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Fri, 4 Sep 2009 10:21:24 -0700 From: da...@fetter.org To: lekn...@pacbell.net CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] where clause question On Fri, Sep 04, 2009 at 06:47:24AM -0700, Scott Frankel wrote: Hello, Is it possible to perform selects in a where clause of a statement? Given a statement as follows: SELECT foo.foo_id, foo.name FROM foo, bar WHERE foo.bar_id = bar.bar_id AND bar.name = 'martini'; I'm looking for a way to recast it so that the select and from clauses refer to a single table and the join referencing the second table occurs in the where clause. For example, something like this: SELECT foo.foo_id, foo.name FROM foo WHERE (SELECT * FROM foo, bar WHERE ...) foo.bar_id = bar.bar_id AND bar.name = 'martini'; I've explored the where exists clause, but it's not supported by the application toolkit I'm using. AFAIK, I've only got access to where ... Sounds like a great reason to modify, or if you can't modify, replace, that application toolkit. This won't be the last time it will get in your way. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ Windows Live: Keep your friends up to date with what you do online. http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLMTAGL:ON:WL:en-US:SI_SB_online:082009
[GENERAL] Full-Text Searching: to_tsquery() vs. plainto_tsquery()
Hello, I'm working on setting up a full-text search for some descriptions stored in my database. I'm running pg 8.4. From what I can tell, there are two functions. One function, to_tsquery(), requires that boolean operators (, |, !) are between every word. The other function, plainto_tsquery() does not accept boolean operators, and adds between every word passed to it. It seems that these two functions only cover the extremes for full-text searching. I don't want to force my users to put in or | in every single search they do, so to_tsquery() wont work. However, if they want to use something like apples AND bananas it will not work with plainto_tsquery(). How can I have a normal search on my site? Normal being defined as the following: -A search for `apples bananas` returns apples OR bananas (with results containing both ranked high) -A search for `apples AND bananas` returns results containing both. -A search for `apple*` returning results that contain apple, apples, applesauce, etc. -A search for `Good Apples` in quotes returning results that contain EXACTLY that phrase. This seems impossible to do with PostgreSQL with the full-text search functions provided without manually parsing the search string from the user in my code before querying the DB. However, even simply parsing the string has it's problems. Simply replacing spaces with | to OR search terms together and replacing AND with would break the query apart due to formatting issues. How does everyone implement the typical boolean full-text search in their database? -- 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] query speed question
I have two tables that are georeferenced (although in this case I'm not using PostGIS) that I need to join. A ( lat | lon | depth | value) |A| = 1,100,000 B ( lat | lon | attributes) |B| = 14,000,000 A is a special case because the lat / lon values are all at half degree intervals (ie 33.5, -12.5). The lat/lons in B are arbitrary. I've written a function in B called getSpecialLat(latitude) and getSpecialLon(longitude) to calculate the correct A latitude and built an index on both functions. Here's the query that I'm trying, but it's rather slow: SELECT B.* FROM B, (SELECT lat, lon FROM A WHERE value 0 AND value 2 AND depth = 0) AS foo WHERE getSpecialLat(B.lat) = foo.lat AND getSpecialLon(B.lon) = foo.lon Nested Loop (cost=3569.88..32055.02 rows=1414 width=422) - Index Scan using A_valueidx on A (cost=0.00..555.26 rows=6 width=16) Index Cond: ((value 0) AND (value 2)) Filter: (depth = 0) - Bitmap Heap Scan on B (cost=3569.88..5029.48 rows=424 width=422) Recheck Cond: ((getSpecialLon((B.lon)::numeric) = A.lon) AND (getSpecialLat((B.lat)::numeric) = A.lat)) - BitmapAnd (cost=3569.88..3569.88 rows=424 width=0) - Bitmap Index Scan on Blonidx (cost=0.00..1760.38 rows=84859 width=0) Index Cond: (getSpecialLon((B.lon)::numeric) = A.lon) - Bitmap Index Scan on Blatidx (cost=0.00..1766.81 rows=84859 width=0) Index Cond: (getSpeicalLat((B.latitude)::numeric) = A.lat) Am I missing something in terms of speeding up this query? I'd be interested to see if the query rewritten as a JOIN would be faster. I can write it like this: select b.* from b join a on (getwoalatitude(b.latitude::numeric) = a.lat and getwoalongitude(b.longitude::numeric) = a.lon) where a.value 0 and a.value 2 and a.depth = 0 which results in this plan: Nested Loop (cost=1387.20..13152982.35 rows=1625767 width=422) - Index Scan using a_depthidx on a_(cost=0.00..1464.07 rows=6897 width=16) Index Cond: (depth = 0) Filter: ((value 0::numeric) AND (value 2::numeric)) - Bitmap Heap Scan on b (cost=1387.20..1686.37 rows=424 width=422) Recheck Cond: ((getSpecialLon((b.lon)::numeric) = a.lon) AND (getSpecialLat((b.lat)::numeric) = a.lat)) - BitmapAnd (cost=1387.20..1387.20 rows=424 width=0) - Bitmap Index Scan on Blonidx (cost=0.00..672.15 rows=84859 width=0) Index Cond: (getSpecialLon((b.lon)::numeric) = a.lon) - Bitmap Index Scan on Blatidx (cost=0.00..672.36 rows=84859 width=0) Index Cond: (getSpecialLat((b.lat)::numeric) = a.lat) However it's still taking ages to execute (over five minutes - I stopped it before it finished) Do you really expect that query to return 1.6M rows? I doubt it, since the subselect version only returns 1400. No - I do not expect it to return 1.6M... If you do a vacuum analyze on those two tables, does it speed either of the queries up? Here are the new results (with explain analyze): EXPLAIN ANALYZE SELECT B.* FROM B, (SELECT lat, lon FROM A WHERE value 0 AND value 2 AND depth = 0) AS foo WHERE getwoalatitude(B.latitude::numeric) = foo.lat AND getwoalongitude(B.longitude::numeric) = foo.lon after vacuum analyze: Merge Join (cost=17873237.91..26594735.94 rows=1984509 width=420) (actual time=752983.201..941125.197 rows=226941 loops=1) Merge Cond: ((a.lat = (getwoalatitude((b.latitude)::numeric))) AND (a.lon = (getwoalongitude((b.longitude)::numeric - Sort (cost=2019.51..2037.61 rows=7239 width=16) (actual time=30.704..32.171 rows=2111 loops=1) Sort Key: a.lat, a.lon Sort Method: quicksort Memory: 212kB - Index Scan using depthidx on a (cost=0.00..1555.43 rows=7239 width=16) (actual time=0.533..24.631 rows=2111 loops=1) Index Cond: (depth = 0) Filter: ((value 0::numeric) AND (value 2::numeric)) - Materialize (cost=17871190.21..18083338.96 rows=16971900 width=420) (actual time=751324.751..919278.574 rows=16963350 loops=1) - Sort (cost=17871190.21..17913619.96 rows=16971900 width=420) (actual time=751324.744..820522.604 rows=16963350 loops=1) Sort Key: (getwoalatitude((b.latitude)::numeric)), (getwoalongitude((b.longitude)::numeric)) Sort Method: external merge Disk: 4599344kB - Seq Scan on b (cost=0.00..750696.00 rows=16971900 width=420) (actual time=1.781..229158.949 rows=16971901 loops=1) Total runtime: 942295.914 ms EXPLAIN ANALYZE SELECT b.* FROM b JOIN a ON (getwoalatitude(b.latitude::numeric) = a.lat AND getwoalongitude(b.longitude::numeric) = a.lon) WHERE a.value 0 AND a.value 2 AND a.depth = 0 Merge Join (cost=17873237.91..26594735.94
Re: [GENERAL] Full-Text Searching: to_tsquery() vs. plainto_tsquery()
APseudoUtopia, you invented your own query language, so you should write your own function, which transforms user's query to ::tsquery, if to_tsquery() and plainto_tsquery() doesn't satisfy your input language. Notice, that phrase search will be able only in 8.5 version. Oleg On Fri, 4 Sep 2009, APseudoUtopia wrote: Hello, I'm working on setting up a full-text search for some descriptions stored in my database. I'm running pg 8.4. From what I can tell, there are two functions. One function, to_tsquery(), requires that boolean operators (, |, !) are between every word. The other function, plainto_tsquery() does not accept boolean operators, and adds between every word passed to it. It seems that these two functions only cover the extremes for full-text searching. I don't want to force my users to put in or | in every single search they do, so to_tsquery() wont work. However, if they want to use something like apples AND bananas it will not work with plainto_tsquery(). How can I have a normal search on my site? Normal being defined as the following: -A search for `apples bananas` returns apples OR bananas (with results containing both ranked high) -A search for `apples AND bananas` returns results containing both. -A search for `apple*` returning results that contain apple, apples, applesauce, etc. -A search for `Good Apples` in quotes returning results that contain EXACTLY that phrase. This seems impossible to do with PostgreSQL with the full-text search functions provided without manually parsing the search string from the user in my code before querying the DB. However, even simply parsing the string has it's problems. Simply replacing spaces with | to OR search terms together and replacing AND with would break the query apart due to formatting issues. How does everyone implement the typical boolean full-text search in their database? Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Creating Superuser and password
Hey Folks, We are trying to integrate postgres with our product. Integrating installation process with our installer, so for setting up password for superuser there is an option in initdb to prompt a person who is running initdb for password. Or other option is to provide password from file. But this approach seems insecure as password has to be in clear in the file. So is there any other way so that I can store password for super user before starting up server through installer. Thanks Regards, Vikram
Re: [GENERAL] maximum count of contiguous years
thanks tim. will read up on rank() and pl/pgsql. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
