Re: [GENERAL] Trade Study on Oracle vs. PostgreSQL
2010/9/25 Uwe Schroeder u...@oss4u.com: On 25/09/2010 4:55 AM, Leif Biberg Kristensen wrote: On Friday 24. September 2010 20.04.26 Lincoln Yeoh wrote: Then again, does Tom ever sleep? Maybe he sleeps in the afternoon? Or is that when he does intensive coding? Once there was a suggestion on this list to give Tom every other week off to level the competition with the other RDBMSes. That being said, the PostgreSQL mail lists is an excellent and totally free support channel. Speaking for myself, I've never needed any support outside the mail lists. And Tom is of course a great big part of that. I couldn't agree more. I really appreciate the time and effort people here have spent helping out. Again, Tom Lane particularly so, for amazing patience, psychic debugging skills, and endless willingness to help anybody and everybody. Don't omit other major contributors like Joshua or Scott... (and others I didn't mention by name). Certainly Tom doesn't seem to need sleep, but there's quite a few more people willing and able to contribute and answer questions. Helped me out on more than one occasion and I've been using postgresql long before it was named postgresql (even back in the days when the linux kernel had a 0.9 version number). Sure many of the current contributers weren't around back then, but they picked up where others left off and made postgresql what it is today: a quite viable alternative to commercial pay lots of dollars databases. Suffice it to say, I have yet to see a showstopper (provided that you run postgresql on decent hardware). Since they just opened a beer tasting room around the corner of my home, here's to all the active contributors - cheers. (and if any of the core developers/contributors ever come to Santa Rosa, CA - drop me a note and I'll have you over for a beer or wine and a nice BBQ) nice to know it :) Regards Pavel Stehule -- 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] Trade Study on Oracle vs. PostgreSQL
@OP - One of the things you _might_ be interested in is the difference in tracing and performance framework between Oracle and Postgres. There was a recent thread about it - http://osdir.com/ml/pgsql-general/2010-09/msg00308.html You could get your company to sponsor some work on that front.
Re: [GENERAL] Index on points
But how do I do it without Postgis? Right now I have a table fleet (id bigserial primary key, location point); and I have filled it with random data and then tried selecting explain analyze select count(1) from fleet where location @ box(point(300,300),point(600,600)); to gather runningtime data, and then I have created and index (or I think I have atleast) CREATE INDEX fleet_location ON fleet USING GIST ( box(location,location) ); but I still get almost exaclty the same run time of the query explain analyze select count(1) from fleet where location @ box(point(300,300),point(600,600)); Aggregate (cost=100597.89..100597.90 rows=1 width=0) (actual time=706.604..706.605 rows=1 loops=1) - Seq Scan on fleet (cost=0.00..94347.90 rows=246 width=0) (actual time=0.252..701.624 rows=4533 loops=1) Filter: (location @ '(600,600),(300,300)'::box) Total runtime: 706.675 ms I have then tried to avoid the seq. scan by set enable_seqscan=off; set seq_page_cost=4000; (which would make it more expensive to scan, wouldn't it?) and the result is the same Aggregate (cost=10127460749.89..10127460749.90 rows=1 width=0) (actual time=799.077..799.078 rows=1 loops=1) - Seq Scan on fleet (cost=100.00..10127454499.90 rows=246 width=0) (actual time=0.221..792.374 rows=4533 loops=1) Filter: (location @ '(600,600),(300,300)'::box) Total runtime: 799.117 ms So how do I create an index that gets used? (I've run the queries a thousand times to make sure the total runtime is consistent, and it is) 2010/9/23 Jeff Davis pg...@j-davis.com: On Thu, 2010-09-23 at 12:45 +0200, A B wrote: Hello. If I have a table like this create table fleet ( ship_id integer, location point); I recommend taking a look into PostGIS: http://postgis.org 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] Libpq memory leak
Hey Sergey, As i can understand Vladimir, he have many clients reading bytea in one server like this and he is trying to reduce memory consumption. Why not use large objects for this purpose? -- Regards, Dmitriy
Re: [GENERAL] Libpq memory leak
Hey Sergey, As i can understand Vladimir, he have many clients reading bytea in one server like this and he is trying to reduce memory consumption. Why not use large objects for this purpose? // Dmitriy.
[GENERAL] How to dump only the the data without schema?
Hi, I need to generate the dump of a PostgreSQL database only with the data with INSERT's. It is possible to do this? Best Regards,
Re: [GENERAL] How to dump only the the data without schema?
Hey Andre, Try pg_dump -a --inserts ... // Dmitriy
Re: [GENERAL] UPDATE/DELETE with ORDER BY and LIMIT
On 24 Sep 2010, at 21:20, Bartłomiej Korupczyński wrote: Hi guys, I've found some posts from 2007 about UPDATE/DELETE ... LIMIT N syntax and I'd like to raise it again. Current PostgreSQL of UPDATE/DELETE implement RETURNING statement, so extending by ORDER and LIMIT would be really useful. All that with just one query. In this specific example, the ORDER BY statement could be even omitted if we don't care how slots are distributed between users. This probably came up in the discussion from back then as well, but what stops you from using a sub-select? UPDATE slots FROM (SELECT id FROM slots WHERE user IS NULL ORDER BY id LIMIT 1) AS available SET user='joe' WHERE id = available.id RETURNING *; Admittedly that's longer and would be slightly less efficient, but it is available now (and has been for a while) and it's still in one query. Also: CREATE TABLE slots ( id INTEGER UNIQUE NOT NULL, user VARCHAR(32), expires TIMESTAMP WITH TIMEZONE, -- some other columns ); I'd declare a primary key as what it is, not as some generic UNIQUE NOT NULL column ;) It won't make much difference in practice, but for example, that way it's intended use is immediately clear from the table definition if people look it up. 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,4c9dcfe7678304776795795! -- 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 on points
On 23/09/10 11:45, A B wrote: Hello. If I have a table like this create table fleet ( ship_id integer, location point); and fill it with a lot of ships and their locations and then want to create an index on this to speed up operations on finding ships within a certain region (let's say its a rectangular region), how do I do this? I tried: CREATE INDEX my_index ON fleet USING gist ( box(location,location)); ? That's the idea, but you'll need to be careful about how you're searching against it. Remember, the index is on a box based on the location, not the point location itself. CREATE TABLE fleet (ship int, locn point); INSERT INTO fleet SELECT (x*1000 + y), point(x,y) FROM generate_series(0,999) x, generate_series(0,999) y; CREATE INDEX fleet_locn_idx ON fleet USING gist( box(locn,locn) ); ANALYSE fleet; EXPLAIN ANALYSE SELECT count(*) FROM fleet WHERE box(locn,locn) @ box '(10,10),(20,20)'; QUERY PLAN --- Aggregate (cost=2654.84..2654.85 rows=1 width=0) (actual time=4.611..4.612 rows=1 loops=1) - Bitmap Heap Scan on fleet (cost=44.34..2652.33 rows=1000 width=0) (actual time=4.344..4.491 rows=121 loops=1) Recheck Cond: (box(locn, locn) @ '(20,20),(10,10)'::box) - Bitmap Index Scan on fleet_locn_idx (cost=0.00..44.09 rows=1000 width=0) (actual time=4.311..4.311 rows=121 loops=1) Index Cond: (box(locn, locn) @ '(20,20),(10,10)'::box) Total runtime: 4.694 ms (6 rows) DROP INDEX fleet_locn_idx; EXPLAIN ANALYSE SELECT count(*) FROM fleet WHERE box(locn,locn) @ box '(10,10),(20,20)'; QUERY PLAN Aggregate (cost=20885.50..20885.51 rows=1 width=0) (actual time=551.756..551.757 rows=1 loops=1) - Seq Scan on fleet (cost=0.00..20883.00 rows=1000 width=0) (actual time=5.142..551.624 rows=121 loops=1) Filter: (box(locn, locn) @ '(20,20),(10,10)'::box) Total runtime: 551.831 ms (4 rows) -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to use pg_restore with *.sql file?
Hi, I have generate an *.sql file with the command: [quote] pg_dump -a --inserts databasename exportfilename.sql [/quote] How can I use the pg_restore to import the data to the database? I have tried: [quote] pg_restore -d databasename exportfilename.sql [/quote] But without success... What is the best way to import the *.sql file to the database? Best Regards,
Re: [GENERAL] How to use pg_restore with *.sql file?
On Sat, Sep 25, 2010 at 6:19 AM, Andre Lopes lopes80an...@gmail.com wrote: Hi, I have generate an *.sql file with the command: [quote] pg_dump -a --inserts databasename exportfilename.sql [/quote] How can I use the pg_restore to import the data to the database? Don't. Just use psql with plain sql dumps: psql databasename -f exportfilename.sql -- 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] How to use pg_restore with *.sql file?
Hey Andre, You should not use pg_restore with plain format dump. Use psql for it. // Dmitriy.
Re: [GENERAL] How to use pg_restore with *.sql file?
Thanks a lot! It works. On Sat, Sep 25, 2010 at 1:23 PM, Scott Marlowe scott.marl...@gmail.comwrote: On Sat, Sep 25, 2010 at 6:19 AM, Andre Lopes lopes80an...@gmail.com wrote: Hi, I have generate an *.sql file with the command: [quote] pg_dump -a --inserts databasename exportfilename.sql [/quote] How can I use the pg_restore to import the data to the database? Don't. Just use psql with plain sql dumps: psql databasename -f exportfilename.sql
Re: [GENERAL] Listen and do something daemon
On Fri, Sep 24, 2010 at 9:42 PM, KM k...@xacrasis.netx wrote: Does this program already exist? $ listen-and-do --database=foo --listen-for=somenotification \ --then-do=some-script It will daemonize itself, issue a 'LISTEN somenotification' on the database foo, and on each NOTIFY will run some-script. I think the libpq Example Program 2 could be very easily adapted for this: http://www.postgresql.org/docs/current/static/libpq-example.html Josh -- 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] psql copy command - 1 char limitation on delimiter
rey reyw...@optonline.net writes: Why limit this to a single character? Performance. Believe it or not, breaking fields at the delimiter is a significant factor in COPY speed. 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] Index on points
On Sat, 2010-09-25 at 09:18 +0200, A B wrote: fleet (id bigserial primary key, location point); ... CREATE INDEX fleet_location ON fleet USING GIST ( box(location,location) ); but I still get almost exaclty the same run time of the query explain analyze select count(1) from fleet where location @ box(point(300,300),point(600,600)); Aggregate (cost=100597.89..100597.90 rows=1 width=0) (actual time=706.604..706.605 rows=1 loops=1) - Seq Scan on fleet (cost=0.00..94347.90 rows=246 width=0) (actual time=0.252..701.624 rows=4533 loops=1) Filter: (location @ '(600,600),(300,300)'::box) Total runtime: 706.675 ms ... So how do I create an index that gets used? (I've run the queries a thousand times to make sure the total runtime is consistent, and it is) Looks like there's no entry in the box_ops opclass for point @ box, but there is an entry for box @ box. So, try: explain analyze select count(1) from fleet where box(location,location) @ box(point(300,300),point(600,600)); There's no reason that there couldn't be a point @ box operator in the opclass, but nobody really uses these geometric types that come with core postgres (at least, not that I can tell). PostGIS is a dedicated project that has removed most of the justification for trying to improve the built-in geometric types. However, keep in mind that PostGIS is under a different license (GPL, I think). 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
[GENERAL] 9.0RC1 error variable not found in subplan target lists
In 9.0 RC1 query below causes error Variable not found in subplan target lists In earlier version this query works OK. How to fix ? Andrus. SELECT * FROM (select 1) xtoode LEFT JOIN (SELECT hmguid.toode, COALESCE(hinnamtr.hinnak,klient.hinnak ) AS hinnak FROM ( SELECT hmhinnak.toode, ''::char as guid FROM ( select ''::char as toode, 1 as prioriteet ) prioriteet JOIN ( SELECT ''::char as toode, ''::text as guid, 1 as prioriteet ) hmhinnak USING (toode,prioriteet) ) hmguid JOIN (select ''::char as guid, ''::char as hinnak) hinnamtr USING (guid) LEFT JOIN klient ON klient.kood='' ) temphinnaks on true; -- 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] 9.0RC1 error variable not found in subplan target lists
Hey Andrus, Did you tried it on 9.0 release? -- // Dmitriy.
Re: [GENERAL] Index on points
Jeff Davis pg...@j-davis.com writes: There's no reason that there couldn't be a point @ box operator in the opclass, but nobody really uses these geometric types that come with core postgres (at least, not that I can tell). Actually, as of 9.0 there is a point_ops opclass for GIST, with these indexable operators: ^(point,point) (point,point) (point,point) ^(point,point) ~=(point,point) @(point,box) @(point,polygon) @(point,circle) I agree that for any more than light-duty geometric work, you ought to look at PostGIS. 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] 9.0RC1 error variable not found in subplan target lists
Andrus kobrule...@hot.ee writes: In 9.0 RC1 query below causes error Variable not found in subplan target lists Could we see a complete test case for this? No one can guess at the tables, indexes, etc that might be needed to provoke the problem. 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 8.4 crashing.
Scott Marlowe scott.marl...@gmail.com writes: On Thu, Sep 23, 2010 at 2:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: If you can apply this patch: http://archives.postgresql.org/pgsql-committers/2010-08/msg00365.php it should tell you which index page is causing the problem. Then please dump that page with pg_filedump and send it in. OK, got an error on it today. Looks like a corrupted index. Note that this is on a machine that was very well tested, but who knows, a single bit error could have occurred. pg_filedump attached. Hm, I should've specified that -i -f options produce the most useful output from pg_filedump :-(. It's pretty obvious that you've got 500+ bytes worth of seriously corrupted data there, but at least in this view it's hard to tell if there's any clear pattern to the bad data. I'd be inclined to suspect disk subsystem malfeasance not a RAM problem. In particular I'm suspicious that the original corruption amounted to exactly one 512-byte sector. If the index page wasn't full at the time, it's possible that additional insertions could have occurred without provoking obvious errors. That could have shifted and enlarged the damaged area to what we see here, which looks to be 540 bytes starting at page offset 552 (if I counted on my fingers correctly). I count seven sane-looking line pointer items within the damaged-looking range, which account for 7*4 = 28 bytes, so if those got inserted later then there was exactly 512 bytes worth of damage initially. It's harder to tell whether there were exactly ten insertions before the damaged area to shift it up from offset 512 to offset 552, but given that the other math comes out right I'm prepared to bet a nickle or two on this theory. 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] psql copy command - 1 char limitation on delimiter
On 09/25/2010 10:03 AM, Tom Lane wrote: reyreyw...@optonline.net writes: Why limit this to a single character? Performance. Believe it or not, breaking fields at the delimiter is a significant factor in COPY speed. regards, tom lane True, but just for 5% to 10% degradation here. For RDBMS, correct indexes and good logical design we are talking about 10 times or more performance gains. Who cares about 10% waste here? Is it Oracle and other commercial RDBMS no such limitation. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Streaming Replication
I have several databases running under on Postgres 9.0 installation in production. Is there a way to specify with which onces will be replicated on another server or must all of them be included?
Re: [GENERAL] Streaming Replication
On 25 September 2010 21:34, Jason Long mailing.li...@octgsoftware.com wrote: I have several databases running under on Postgres 9.0 installation in production. Is there a way to specify with which onces will be replicated on another server or must all of them be included? Yes, they must all be included as it works at the cluster level. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- 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] 9.0RC1 error variable not found in subplan target lists
Could we see a complete test case for this? No one can guess at the tables, indexes, etc that might be needed to provoke the problem. Complete testcase is below. Should I try 9.0 release or other idea how to fix without removing primary key and keeping query structure so that query runs in all =8.1 servers ? Andrus. create temp table klient (kood char(12) primary key, hinnak char(5)) on commit drop; SELECT * FROM (select 1) xtoode LEFT JOIN (SELECT hmguid.toode, COALESCE(hinnamtr.hinnak,klient.hinnak ) AS hinnak FROM ( SELECT hmhinnak.toode, ''::char as guid FROM ( select ''::char as toode, 1 as prioriteet ) prioriteet JOIN ( SELECT ''::char as toode, ''::text as guid, 1 as prioriteet ) hmhinnak USING (toode,prioriteet) ) hmguid JOIN (select ''::char as guid, ''::char as hinnak) hinnamtr USING (guid) LEFT JOIN klient ON klient.kood='' ) temphinnaks on true -- 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] 9.0RC1 error variable not found in subplan target lists
Andrus kobrule...@hot.ee writes: Could we see a complete test case for this? No one can guess at the tables, indexes, etc that might be needed to provoke the problem. Complete testcase is below. Thanks, I can reproduce it now. Should I try 9.0 release No, it's still busted in HEAD :-(. Probably won't be too hard to fix, but I need to go find the bug. 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] 9.0RC1 error variable not found in subplan target lists
Andrus kobrule...@hot.ee writes:
Should I try 9.0 release
No, it's still busted in HEAD :-(. Probably won't be too hard to fix,
but I need to go find the bug.
Here's the patch if it helps.
regards, tom lane
diff --git a/src/backend/optimizer/plan/analyzejoins.c
b/src/backend/optimizer/plan/analyzejoins.c
index
5fc056e2a572db2299f2624f19e8988e671df034..1355c18317a7399e028c16f3b8edffc410687a27
100644
*** a/src/backend/optimizer/plan/analyzejoins.c
--- b/src/backend/optimizer/plan/analyzejoins.c
***
*** 26,31
--- 26,32
#include optimizer/pathnode.h
#include optimizer/paths.h
#include optimizer/planmain.h
+ #include optimizer/var.h
/* local functions */
static bool join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo);
*** join_is_removable(PlannerInfo *root, Spe
*** 197,212
}
/*
!* Similarly check that the inner rel doesn't produce any
PlaceHolderVars
!* that will be used above the join.
*/
foreach(l, root-placeholder_list)
{
PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(l);
! if (bms_is_subset(phinfo-ph_eval_at, innerrel-relids)
! !bms_is_subset(phinfo-ph_needed, joinrelids))
! return false;
}
/*
--- 198,220
}
/*
!* Similarly check that the inner rel isn't needed by any
PlaceHolderVars
!* that will be used above the join. We only need to fail if such a PHV
!* actually references some inner-rel attributes; but the correct check
!* for that is relatively expensive, so we first check against
ph_eval_at,
!* which must mention the inner rel if the PHV uses any inner-rel attrs.
*/
foreach(l, root-placeholder_list)
{
PlaceHolderInfo *phinfo = (PlaceHolderInfo *) lfirst(l);
! if (bms_is_subset(phinfo-ph_needed, joinrelids))
! continue; /* PHV is not used
above the join */
! if (!bms_overlap(phinfo-ph_eval_at, innerrel-relids))
! continue; /* it definitely
doesn't reference innerrel */
! if (bms_overlap(pull_varnos((Node *) phinfo-ph_var),
! innerrel-relids))
! return false; /* it does reference innerrel */
}
/*
--
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] A note on pg_upgrade and missing pg_upgrade_support.so
2010/9/21 Bruce Momjian br...@momjian.us: Bruce Momjian wrote: Ian Barwick wrote: Hi Just a quick note for anyone else building 9.0 from source and experimenting with pg_upgrade - if you get a message like the following when running the pg_upgrade binary: pg_upgrade_support.so must be created and installed in /path/to/pg90/lib/postgresql/pg_upgrade_support.so you need to build the the separate pg_upgrade_support as well. Well, that is step #4: http://www.postgresql.org/docs/9.0/static/pgupgrade.html 4. Install pg_upgrade Install pg_upgrade and pg_upgrade_support in the new PostgreSQL cluster Was that not clear enough? I hope my comment didn't sound insulting. I really want to know how that doc item can be made clearer. No insult taken :) .With the benefit of hindsight it's plenty clear; the problem was: a) I was doing this in a hurry (had a small amount of time to kill and a dev machine with an older beta on it) b) got sidetracked by this thread which appears to describe the same problem: http://archives.postgresql.org/pgsql-testers/2010-06/msg0.php and which was popping up pretty high in Google. Looking over the doc page again, if scanning over it, it's a bit easy to misread it as something like Install pg_upgrade for pg_upgrade support in the new PostgreSQL cluster. To my shame I hadn't even looked at pg_upgrade before, so was coming to it from a point of zero knowledge. How about a slightly more pedantic phrasing such as: -- 4. Install pg_upgrade pg_upgrade requires the installation of the contrib modules pg_upgrade and pg_upgrade_support in the new PostgreSQL cluster -- (maybe pg_upgrade and pg_upgrade_support could be highlighted in some way, e.g. with the courier-style font if that fits in with the doc functions). Anyway, it worked fine and I have since used it to upgrade to 9.0 on my personal production server with minimal downtime. Thanks for yet another great release Ian Barwick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
