Re: [GENERAL] Drastic select count performance hit when jsonb GIN indices are present
Hi Maxim,
Thanks for the suggestions. Unfortunately, it does appear to be an IO issue
but not one I can get around (on my laptop at least!). Postgres seems to
refuse to put any of the index or table in shared buffers at all. For some
reason, there seems to be a very great increase in space required for
having each event (item) on its own row as opposed to having it with the
original items array within far fewer database rows:
relation |size
-+
public.myevents | 3502 MB
...
pg_toast.pg_toast_57487 | 800 MB
...
public.events | 2232 kB
...
myevents is where each event has a row to itself and events has 100 events
in an array within the JSON rows (and the toast table above is where the
data seem to be stored) - actually the events table has more data in it as
there are the extra paging objects which have been removed from myevents.
Performing vacuum analyze seems to have no effect either.
Getting back to my original point - you pointed out that for queries that
need a decent % of the table it will be cheaper to do a scan, which is
exactly what the query planner does for the relational version. If it only
needs a small % of the values it looks at the index and for a large % it
goes for a scan (it also puts everything in shared buffers and is
lightening quick!). Is this just a lack of maturity in the jsonb planner or
am I missing something?
Thanks again,
Anton
On 26 December 2014 at 14:19, Maxim Boguk wrote:
>-> Bitmap Heap Scan on myevents (cost=35.80..3615.09 rows=3716
> width=0) (actual time=351.510..77669.907 rows=1417152 loops=1)
>
>> Recheck Cond: (event @> '{"event": "delivered"}'::jsonb)
>> Heap Blocks: exact=298362
>>
>> Buffers: shared hit=1 read=298589
>>
> ...
>
>> Execution time: 80986.340 ms
>>
>
>
>>-> Bitmap Heap Scan on
>>
>> myevents (cost=42.80..3622.09 rows=3716 width=0) (actual
>> time=534.816..78526.944 rows=1417152 loops=1)
>> Recheck Cond: (event @> '{"event": "delivered"}'::jsonb)
>> Heap Blocks: exact=298362
>> Buffers: shared hit=383 read=299133
>>
> ...
>> Execution time: 81898.578 ms
>>
>
> Hi Anton,
>
> What you see there (i think) - it's a performance hit of random disk read
> for non-cached database.
> Try increase a shared buffers to value when table and index could fit
> into, and redo queries few time until you see something like
> Buffers: shared hit=bigvalue read=0 and compare performance, it might
> change timing quite a lot.
>
> Also, I recommend set track_io_timing=on in postgresql.conf and after it
> use explain (analyze, buffers, timing) to see check how much time database
> spent doing IO operations.
> Also try perform vacuum analyze myevents; before testing because it seems
> that you have no up to date visibility map on the table.
>
>
> However, even in fully cached case selecting 40% on the table rows almost
> always will be faster via sequential scan, so I don't expect miracles.
>
> --
> Maxim Boguk
> Senior Postgresql DBA
> http://www.postgresql-consulting.ru/
>
[GENERAL] Drastic select count performance hit when jsonb GIN indices are present
Hi,
I've been playing with jsonb for storing and querying data from the Mailgun
Events API (http://documentation.mailgun.com/api-events.html#examples). I
already have a system that parses the JSON to csv and loads into standard
tables but what better way to spend the holidays than nerding out on new
postgres tech :-)? I am using the official postgres-supplied 9.4 Ubuntu
repo on 14.04.
I have been testing both just inserting the raw API call JSON
({"items":[{"item1"],"paging":...}) and extracting the actual events
from the items array ({"item1":...}, {"item2":...}) and inserting per-event
rather than per page of 100 events in an "items" array. I did this to try
and benefit from GIN indices but much to my surprise, adding indices when
there is a line per-event actually drastically *reduces* performance!
mgevents=# create table myevents (event jsonb);
mgevents=# create index idx_myevents on myevents using gin (event
jsonb_path_ops);
mgevents=# create index idx_myevents_no_path on myevents using gin (event);
mgevents=# select count(*) from myevents;
count
-
3715600
(1 row)
mgevents=# explain (analyze, buffers) select count(*) from myevents where
event ->> 'event' = 'delivered';
QUERY PLAN
---
Aggregate (cost=279884.45..279884.46 rows=1 width=0) (actual
time=14626.213..14626.216 rows=1 loops=1)
Buffers: shared read=448208
-> Seq Scan on myevents (cost=0.00..279838.00 rows=18578 width=0)
(actual time=0.719..11432.283 rows=1417152 loops=1)
Filter: ((event ->> 'event'::text) = 'delivered'::text)
Rows Removed by Filter: 2298448
Buffers: shared read=448208
Planning time: 0.074 ms
Execution time: 14626.955 ms
(8 rows)
mgevents=# explain (analyze, buffers) select count(*) from myevents where
event @> '{"event": "delivered"}';
QUERY PLAN
-
Aggregate (cost=3624.38..3624.39 rows=1 width=0) (actual
time=80984.095..80984.098 rows=1 loops=1)
Buffers: shared hit=1 read=298589
-> Bitmap Heap Scan on myevents (cost=35.80..3615.09 rows=3716
width=0) (actual time=351.510..77669.907 rows=1417152 loops=1)
Recheck Cond: (event @> '{"event": "delivered"}'::jsonb)
Heap Blocks: exact=298362
Buffers: shared hit=1 read=298589
-> Bitmap Index Scan on idx_myevents (cost=0.00..34.87 rows=3716
width=0) (actual time=257.219..257.219 rows=1417152 loops=1)
Index Cond: (event @> '{"event": "delivered"}'::jsonb)
Buffers: shared hit=1 read=227
Planning time: 3.197 ms
Execution time: 80986.340 ms
(11 rows)
mgevents=# drop index idx_myevents;
mgevents=# explain (analyze, buffers) select count(*) from myevents where
event @> '{"event": "delivered"}';
QUERY
PLAN
-
Aggregate (cost=3631.38..3631.39 rows=1 width=0) (actual
time=81898.250..81898.252 rows=1 loops=1)
Buffers: shared hit=383 read=299133
-> Bitmap Heap Scan on myevents (cost=42.80..3622.09 rows=3716
width=0) (actual time=534.816..78526.944 rows=1417152 loops=1)
Recheck Cond: (event @> '{"event": "delivered"}'::jsonb)
Heap Blocks: exact=298362
Buffers: shared hit=383 read=299133
-> Bitmap Index Scan on idx_myevents_no_path (cost=0.00..41.87
rows=3716 width=0) (actual time=453.412..453.412 rows=1417152 loops=1)
Index Cond: (event @> '{"event": "delivered"}'::jsonb)
Buffers: shared hit=383 read=771
Planning time: 2.322 ms
Execution time: 81898.578 ms
(11 rows)
mgevents=# drop index idx_myevents_no_path;
mgevents=# explain (analyze, buffers) select count(*) from myevents where
event @> '{"event": "delivered"}';
QUERY PLAN
--
Aggregate (cost=270558.29..270558.30 rows=1 width=0) (actual
time=19834.530..19834.532 rows=1 loops=1)
Buffers: shared hit=130935 read=317273
-> Seq Scan on myevents (cost=0.00..270549.00 rows=3716 width=0)
(actual time=4.650..16764.726 rows=1417152 loops=1)
Filter: (event @> '{"event": "delivered"}'::jsonb)
Rows Removed by Filter: 2298448
Buffers: shared hit=130935 read=317273
Planning time: 0.238 ms
Execution time: 19834.605 ms
(8 rows)
So it doesn't even appear to be a problem with the operator (->> vs @>) but
rather that the planner is using the index rather tha
[GENERAL] Script to export all privileges to csv or similar
Hi, Does anyone know of a script/tool that allows one to export all users with all privileges? I realise I could construct a query to do it but google turned up nothing and if someone else has done the good work... Cheers Anton -- echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq' | dc This will help you for 99.9% of your problems ... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] move database from the default tablespace to a new one?
Hi all, Is this possible? I have a db that has been living in the default tablespace, and I am trying to separate out the transaction logs (and log archiving) and the data. It seems that tablespaces are the way to go... but the database exists and I need to separate them out. Any pointers? Cheers Anton -- 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] recommended way of separating data from indexes
Hi, >> I have a 6 disk (x146GB) system running Centos 5.1 - RAID 1 (2 disks) >> and RAID 1+0 (4 disks). This seemed to be the recommended way of doing >> it but I may have been looking in the wrong place. The system is >> replicated using log shipping, so we have the archive command >> activated. >> What are the recommendations for this kind of system? > > That's going to depend on your disk activity, which will depend on usage > patterns. It's a website, with probably around 98% read to 2% write, and the writes only to a few tables. >> The system is on >> >> the RAID 1 disk. My idea was to separate only the data onto the RAID >> 1+0 and leave the logs and archive and rest on the RAID 1. Is this the >> way to go? What is the recommended way of doing this? Just using >> symbolic links? Help! > > Read the tablespaces chapter of the manuals. > > If you've got a fair amount of writing then you'll want to make sure the WAL > can be written to as quickly as possibly. If you've got other write activity > occurring on your system disks (e.g. lots of system-log activity, mailserver > etc) then that's probably not the best place for the WAL. On the other hand, > database reads+writes will interfere if you put everything on the RAID 10. > > So - it will depend. The machine is only doing this one DB, with the odd email being sent by postfix (20 p/d, so nothing) and for the moment we aren't doing any real logging apart from postgres internal (so not even activity logs or query logs, etc). So given that disk usage is pretty much 100% pgsql, and it's mainly read, does my architecture stand up? And thanks, I'll have a read of the tablespaces chapter - I looked pretty much everywhere but there! Cheers Anton -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] recommended way of separating data from indexes
Hi, I looked for quite a while but must have been looking in the wrong place... I have a 6 disk (x146GB) system running Centos 5.1 - RAID 1 (2 disks) and RAID 1+0 (4 disks). This seemed to be the recommended way of doing it but I may have been looking in the wrong place. The system is replicated using log shipping, so we have the archive command activated. What are the recommendations for this kind of system? The system is on the RAID 1 disk. My idea was to separate only the data onto the RAID 1+0 and leave the logs and archive and rest on the RAID 1. Is this the way to go? What is the recommended way of doing this? Just using symbolic links? Help! Cheers Anton -- echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq' | dc This will help you for 99.9% of your problems ... -- 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] database 1.2G, pg_dump 73M?!
> I am still trying to figure out if the database was getting any > automatic vacuuming at all. The Postgres documentation (the database is > 8.2, though I'm moving to 8.3 soon) sounds as if it's on automatically, > but the Debian-specific documentation suggests I may need to do some > additional things to enable it. This is probably far too late but anyway. You do indeed have to enable autovacuum with 8.2, as it isn't enabled by default, at least with most distributions. 8.3 it's by default with most distributions. # show autovacuum; will tell you if it's on. Cheers Anton -- 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] Client-requested cast mode to emulate Pg8.2 on v8.3
On 25/03/2008, Tom Lane <[EMAIL PROTECTED]> wrote: > "Anton Melser" <[EMAIL PROTECTED]> writes: > > >> You'd have to do something like > >> DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc; > >> since there isn't any higher-level command that will let you delete a > >> built-in operator. > >> > >> I recommend practicing on a scratch database ;-) > > > Thanks for the tip, though alas that didn't seem to fix it... > > > Did you remove the other one too? Actually, I hadn't even properly deleted the first one (don't know where I did delete it, but it wasn't in the right place!) :-(. This is not my day! The app appears to be working again now. I won't bother you again with this - promised! Thanks a million. Cheers Anton ps for reference... DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc; DELETE FROM pg_operator WHERE oprcode = 'textanycat'::regproc; -- 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] Client-requested cast mode to emulate Pg8.2 on v8.3
> You'd have to do something like > DELETE FROM pg_operator WHERE oprcode = 'anytextcat'::regproc; > since there isn't any higher-level command that will let you delete a > built-in operator. > > I recommend practicing on a scratch database ;-) Thanks for the tip, though alas that didn't seem to fix it... select 1 || '/' ERROR: operator is not unique: integer || unknown LINE 1: select 1 || '/' ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. and even select 1 || '/'::text ERROR: operator is not unique: integer || text LINE 1: select 1 || '/'::text ^ HINT: Could not choose a best candidate operator. You might need to add explicit type casts. Am I in between a rock and a hard place here? Thanks again, Anton -- 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] Client-requested cast mode to emulate Pg8.2 on v8.3
> > Anyway, maybe I spoke too soon :-(. > > > ERROR: operator is not unique: integer || unknown > > > I did, of course, not follow the instructions and just blinding > > applied them all, but from reading them it doesn't look like the issue > > here. Does this error mean there are too many operators or not enough? > > > Too many. You might have to remove the anynonarray || text and > text || anynonarray operators if you're going to continue to rely > on implicit casts to text. Thanks for that. Any chance someone could give me more newbie instructions? :-) I suppose you are talking about anytextcat(anynonarray, text) and textanycat(text, anynonarray) But I can't see anywhere obvious where I can "deactivate" them... I looked for likely suspects in pg_operator, pg_cast... but I'm not really sure what I'm doing. Anyone? Thanks heaps. Anton -- 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] Client-requested cast mode to emulate Pg8.2 on v8.3
> > I have the suspicion that his mother is named Lois, his father is > > unknown and he has a sensitivity to Kryptonite. But that's just > > speculation of course... > > > > Alban Hertroys > > > Superman married Lois, I hope that isn't his Mom's name. I got that he was the *son* of Superman... and really, in which episode does he marry Lois (I admit I am not a devotee...)? I thought the whole point was the sexual tension between the two... Anyway, maybe I spoke too soon :-(. ERROR: operator is not unique: integer || unknown I did, of course, not follow the instructions and just blinding applied them all, but from reading them it doesn't look like the issue here. Does this error mean there are too many operators or not enough? Meaning another function + cast would solve it? Or maybe making the function more complex (by taking into account more possible cases)? Cheers Anton -- 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] Client-requested cast mode to emulate Pg8.2 on v8.3
On 21/03/2008, Tom Lane <[EMAIL PROTECTED]> wrote: > "Anton Melser" <[EMAIL PROTECTED]> writes: > > ... But it is COMPLETELY out of the > > > question to redo the db abstraction layer, and without these implicit > > casts that is what will be needed. Is there REALLY no way to reenable > > it? > > > http://people.planetpostgresql.org/peter/index.php?/archives/18-Readding-implicit-casts-in-PostgreSQL-8.3.html Tom the Champion strikes again! Cheers Anton -- 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] Client-requested cast mode to emulate Pg8.2 on v8.3
> > - Is there a way to turn it back to the old behaviour with a > >warning going to the logs? > > > No. > > > > - Is there a way to get v8.2.x to warn on the dubious casts > >so we can tighten the application side while on v8.2? > > > Seems to me the easiest way would be to try it out on an 8.3 > installation and exercise each query once. There may be a better way > but I don't know it... Hi, This seems like it is one of the most frustrating (for me) decisions that has ever been made by the postgres developers... My situation is the following : I inherited an application based on a dead project (byline, and don't even mention aplaws, it's about as alive a zombie from Resident Evil... it moves, but it ain't alive!) and we currently use postgres 8.1. The performance sucks, and there are several things in 8.3 that are very interesting, notably synchronous_commit, plus all the perfermance goodies since 8.1. But it is COMPLETELY out of the question to redo the db abstraction layer, and without these implicit casts that is what will be needed. Is there REALLY no way to reenable it? I fully realise and respect the logic in doing this but not having a fallback (even if it means recompiling from source) is painful! Am I really stuck with pre-8.3? Cheers Anton -- 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] shared_buffers and shmmax what are the max recommended values?
That's crystal. Thanks for your advice! Cheers Anton On 07/03/2008, Greg Smith <[EMAIL PROTECTED]> wrote: > On Fri, 7 Mar 2008, Anton Melser wrote: > > > > There is actually quite a bit of write (at least the dump is increasing > > far more than what is being added manually by content writers... and I'm > > not even certain where it is coming from but that is another story!) > > > If you look at pg_stat_user_tables regularly that should give you an idea > what's being added/updated/deleted. > > > > yes checkpoint_segments is at the default... if I increase to 10 or so > > will that be better? > > > There will be less checkpoints, which may be better for you. But each > checkpoint could be doing more work, so they will be more disruptive, > which can be worse. It's not a parameter you can increase and that will > always be an improvement. > > Normally people doing write-heavy work set that into the 30-100 range. > You will use more disk space for the WAL files used by the server, and > recovery from a crash will take longer as well. The default of 3 keeps > WAL files at a total of about 112MB; increasing to 10 raises that to > 336MB, and at 30 you can expect to have 1GB of WAL files around on the > primary server. > > > > btw, we have a warm standby via wal copies if that makes a difference... > > > Changing checkpoint_segments doesn't alter how often WAL files are moved > over, but it will increase how many of them you need to keep around on the > secondary in order to rebuild the server after a crash. You should be > careful making changes here until you understand how all that fits > together. > > > -- > * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD > -- echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq' | dc This will help you for 99.9% of your problems ... -- 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] shared_buffers and shmmax what are the max recommended values?
> With 6GB of RAM, after that you could merrily increase shared_buffers to > 20 or so and possibly increase performance. Just watch your > checkpoints--they'll have more activity as you increase the buffer size, > and from your description you've still got checkpoint_segments at the tiny > default size. You say this is a web app, those typically aren't write > heavy so you'll probably be OK. Thanks, I'll get onto that... There is actually quite a bit of write (at least the dump is increasing far more than what is being added manually by content writers... and I'm not even certain where it is coming from but that is another story!), and yes checkpoint_segments is at the default... if I increase to 10 or so will that be better? btw, we have a warm standby via wal copies if that makes a difference... Cheers Anton -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] shared_buffers and shmmax what are the max recommended values?
Hi all, We have a web app that is using a 32 bit 8.1.4 (I know but upgrading is not an option for another couple of months...) running on Suse 10. We just increased from 3GO to 6GO of RAM, and I increased the various memory related values... First info - the server ONLY does one pg db (that is its purpose in life, and it runs pretty much nothing else, and certainly nothing we care about, all the apps that access it are on separate servers). I did, however, realise that I don't (didn't?) understand what shmmax REALLY is. It was at the default value (:-)), so even for 3GO was ridiculously low. I saw some recommendations on the list mentioning that shared_buffers (for an 8GO machine) should be set to 25 or something like that. So I merrily increased shmmax to 128MO and tried to start pg. Ouch! I needed to put it to much more than that... So the main question - what is the maximum recommended shmmax setting? I currently have it set to 1GO, but I think it probably needs to go higher - no? Here are the values that aren't at their defaults: shared_buffers = 5 work_mem = 8192 effective_cache_size = 525000 max_prepared_transactions = 100 maintenance_work_mem = 262144 max_fsm_pages = 30 max_fsm_relations = 1 Any suggestions most welcome. Cheers Anton -- 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] URGENT: Whole DB down ("no space left on device")
On 31/08/2007, Jeff Davis <[EMAIL PROTECTED]> wrote: > On Fri, 2007-08-31 at 22:34 +0800, Phoenix Kiula wrote: > > On 31/08/2007, Josh Tolley <[EMAIL PROTECTED]> wrote: > > > On 8/31/07, Zoltan Boszormenyi <[EMAIL PROTECTED]> wrote: > > > > Phoenix Kiula írta: > > > > > In addition to what others have already said, when things calm down > > > you should consider implementing some sort of monitoring system that > > > is configured to start screaming before you run into problems like > > > this. At my place of work, we've set up Nagios to monitor the space > > > left on various partitions, and email us when a partition gets above > > > 90% full. > > > > > > > > Wow, Nagois seems like a superb tool. Thanks for the recommendation! > > > > You might also consider OpenNMS. I spent about 3 hours trying to get it running and said - I'm at eval stage, and nagios/centreon is installed and working... (even if not as theoretically nice)... there are lots of very promising systems out there (hyperic, zenoss, etc) but if it ain't an apt-get or yum away then... why not just go with what *is* there? Surely it must be being used by more people, if not, why aren't the others in the repos? Random ramblings! Cheers Anton -- echo '16i[q]sa[ln0=aln100%Pln100/snlbx]sbA0D4D465452snlbxq' | dc This will help you for 99.9% of your problems ... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgres 8.2 binary for ubuntu 6.10?
Just a word of advice... unless you plan to spend lots of time on your db (like you want to think about it more than twice a week sort of thing...), just go with what you have in terms of the distro. We are running 8.1.4. And it just works, yes, even after all this time! You are certainly behind a good firewall, so if you have X.X.X, and it works (ie, your developpers have certified for X.X.X), why think about having the latest? Upgrading to new versions may well expose problems (like I remember someone talking about query optimisations a while back) that are non issues. If you are going to be pushing the limits, then compiling your own versions is not going to be an issue... Just my 2c Cheers Anton ps. I know, when a new version comes out so often it is soo hard to resist! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] should the postgres user have a password?
Sorry .pgpass :-( Anton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] should the postgres user have a password?
Hi, I have never given postgres a password before, but one of my clients put one on his postgres user - I suppose so he could use the postgres user from phppgadmin (not my choice !). But now I can't see how to get my backup scripts to work... can I put the password for tools like pg_dumpall (or psql, or whatever) on the CL. There is no option in the docs... Would it be better to remove the password (if so, any pointers, I couldn't find that either!) and make postgres only able to connect via pipes? Thanks again, Anton ps. I did google but obviously couldn't find the right combo of keywords... ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] recommendations for reducing mem usage on local dev machine
On 19/04/07, Andrej Ricnik-Bay <[EMAIL PROTECTED]> wrote: On 4/15/07, Anton Melser <[EMAIL PROTECTED]> wrote: > it might even make more sense), and with KDE/Gnome these days, I don't > think there is much difference with XP... Of course you could use fluxbox, twm or something else less bloated ... my window-manager has a 2MB foot-print. Or use vim instead of Eclipse ;} ... would save you approx. 240MB in the blink of an eye. I have long wanted to spend the time to get proficient enough at vim to be more productive than with IDEs... alas, it has just never happened! Cheers Anton ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] postgresql 8.1.4 to 8.2.3
On 14/04/07, Alain Roger <[EMAIL PROTECTED]> wrote: After clicking on your link i got "invalid project" page :-( and the whole page is empty... Ouch Alain... Try http://pgfoundry.org/projects/pg-migrator/ :-) But ask a single postgres oldskool cat (which I am patently not!) and you will get exactly the same answer "Do you value your data?". This is for people who want to tinker, or who simply can't dump/reload. Until it gets included in postgres core of course! > > RTFM :-). Between major versions (8.1 -> 8.2) you DO need to dump and > > reload. So do that... > > Actually, this isn't strictly true: http://pgfoundry.org/projects/pg- > migrator/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] question
On 13/04/07, Andrej Ricnik-Bay <[EMAIL PROTECTED]> wrote: On 4/13/07, Terry Martin <[EMAIL PROTECTED]> wrote: > I would like to know if I there is a utility to take a UDP packet which > has specific information in the payload and extract the information > from the packet and place it in the Postgres data base? Which OS (in Linux ulogd and/or tcpdump spring to mind), do you need it captured real-time or would batch-processing on the full-hour or end of day suit you just fine? ethereal (packet sniffer) is multiplatform and scriptable, and afaict is pretty much the best free option out there. Cheers Anton ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] recommendations for reducing mem usage on local dev machine
It's fairly likely that that report is misleading: most Unix versions of "top" report Postgres' shared memory as belonging to *each* backend, and I'll bet taskmanager is doing the same thing. You could reduce shared memory usage (cut shared_buffers in particular), which might make the reported usage drop to say 20mb per process, but you only saved 20mb not 20*5. It sounds to me like you're simply wishing for more than your box can handle. Have you thought about running the client and server parts of your development on separate boxes? Or maybe install an OS with less overhead than Windoze? Thanks for your advice Tom. And you are probably right - at work with 1.5gig I can even get this + VS2005 + EntMan 2005 open without it starting to swap. I have had nasty experiences running eclipse in both Gentoo and Fedora (even though the production environment is Suse, so it might even make more sense), and with KDE/Gnome these days, I don't think there is much difference with XP... I guess I'm just waiting till a system with native virtualisation (no more reboots!) and enough memory comes into my price range before doing an upgrade :-). Thanks - I'll just have to keep my open apps to a minimum! Cheers Anton ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] recommendations for reducing mem usage on local dev machine
On 14/04/07, Roman Neuhauser <[EMAIL PROTECTED]> wrote: # [EMAIL PROTECTED] / 2007-04-14 13:27:33 +0200: > Hi, > I am stuck for the moment with 1gig of ram on a win xp machine running > a 8.2.3 postgres. With the java website taking 300meg, how is it going to scale? It's not! The site is already serving thousands of concurrent users, and running on 8 servers (admin, load balancer, db, java servers, ...) - and it is running fine. I am developing a module, and pretty much need to have everything on my machine, hence my need to do with 1 gig what the real servers are doing with 10s. A LOT of stuff gets cached, which is why it is so mem hungry... Cheers Anton ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] recommendations for reducing mem usage on local dev machine
Hi, I am stuck for the moment with 1gig of ram on a win xp machine running a 8.2.3 postgres. With the java website taking 300meg, eclipse taking 250meg+, firefox 150meg+, all of which are going to be nasty to reduce the mem usage of, I am looking at reducing postgres usage (the java website runs on postgres). The db is not really a bottleneck for development, so I am not concerned about reducing things very low. At the moment I have around 10 postgres processes in the taskmanager, 5 of which are taking around 40meg. While the website is very db intensive, I really need to get this down to under 100meg to stop my system starting to swap. Any ideas? I looked at postgresql.conf but don't really know what is going to get the mem usage down when the db is not really being used. Cheers Anton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] postgresl for mysql?
OK, it got me for more than half a second... :-) And as you mention - not entirely ridiculous! Cheers Anton On 01/04/07, Tom Lane <[EMAIL PROTECTED]> wrote: Leonel <[EMAIL PROTECTED]> writes: > On 4/1/07, Anton Melser <[EMAIL PROTECTED]> wrote: >> What will they think of next! >> http://krow.livejournal.com/502908.html > Is today Aprils Fool's ? Yup. But he got me for about half a second, because this was proposed entirely seriously by the MySQL AB folk back when they were looking for a way out from under Oracle's purchase of InnoDB. Since they hired Jim Starkey to write "Falcon" for them, I don't think there's any interest in that anymore over there. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] postgresl for mysql?
What will they think of next! http://krow.livejournal.com/502908.html I suppose it makes as much sense as the others, except why would you want to use mysql if the storage is in postgres? Cheers Anton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] "sniffing" postgres queries
On 21/03/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Anton Melser" <[EMAIL PROTECTED]> writes: > I am having real trouble with a project that is a little out of my > league and I just can't find out how to find which table the app is > getting its values from (it's a long story...). I know what values I > am looking for, and would like to log everything (even if only for one > call of a web page...) that comes in and everything that goes out. Is > this possible at the db level? Even debug5 doesn't seem to give me the > result of a query... If it is only possible at the driver level (I did > something vaguely similar with the odbc driver I think), then any > pointers for a jdbc2 on 8.1.4? Doesn't log_statement = ALL get the job done for you? If you need to correlate queries with results then I guess not; there is no provision for logging query results. The nearby suggestion to use a packet sniffer is probably your only recourse for that. I'd suggest that you may also need to set the driver to use protocol version 2, so that all the queries come in in plain text --- otherwise you'll have some difficulty interpreting the packet streams ... Thanks both for your replies. I remember now that it was indeed ethereal that we used last time (for the odbc problem). I will probably try and avoid this solution for the moment... Thanks for your help. Cheers Anton ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] "sniffing" postgres queries
Hi, I am having real trouble with a project that is a little out of my league and I just can't find out how to find which table the app is getting its values from (it's a long story...). I know what values I am looking for, and would like to log everything (even if only for one call of a web page...) that comes in and everything that goes out. Is this possible at the db level? Even debug5 doesn't seem to give me the result of a query... If it is only possible at the driver level (I did something vaguely similar with the odbc driver I think), then any pointers for a jdbc2 on 8.1.4? Thanks Anton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] question about stored procedure / function
On 11/03/07, Alain Roger <[EMAIL PROTECTED]> wrote: Hi, i created the following function : -- Function: immense.sp_a_001(username "varchar", pwd "varchar") -- DROP FUNCTION immense.sp_a_001(username "varchar", pwd "varchar"); CREATE OR REPLACE FUNCTION immense.sp_a_001(username "varchar", pwd "varchar") RETURNS int4 AS $BODY$ DECLARE myrec immense.accounts%ROWTYPE; count INTEGER := 0; /**/ BEGIN FOR myrec IN SELECT * FROM immense.accounts WHERE account_login=$1 and account_pwd=$2 LOOP count := count + 1; END LOOP; RETURN count; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION immense.sp_a_001(username "varchar", pwd "varchar") OWNER TO immensesk; GRANT EXECUTE ON FUNCTION immense.sp_a_001(username "varchar", pwd "varchar") TO immensesk; However, postgreSQL add automatically the following line to each procedure and i do not know why ? GRANT EXECUTE ON FUNCTION immense.sp_a_001(username "varchar", pwd "varchar") TO public; normally, in such case (i mean without granted execution right to public on this procedure), only immensesk user should be able to run it... so why such thing ? it is not secured... or is there something i missed ? Where exactly does postgresql add this line? In pgadmin? Well then it's not postgres, but pgadmin. If you tell postgres that the execute rights go to X, then it is X that has those rights... Cheers Anton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Beginner's Questions
On 3/10/07, Don Lavelle <[EMAIL PROTECTED]> wrote: > Hi, all, > > Thank you all for your help! From what I've gathered, similarly > sized projects run on 100 MB of disk space and a 450 MHz processor. > My GUI and application logic aren't going to need much more than > that, so I should be good to go! > > PostgreSQL it is! I'm sure I will have many, many more questions as > I continue the development process. For something like what you are doing, providing (and this is a BIG providing) you won't want to distribute it under a commercial licence without paying a fee (it has gpl and commercial), something like db4o might be better. It is very lite and after you get used to it should save you lots of development time. It is an object database, and basically you just do things like objectDatastore.set(myObject); myOjbect = objectDatastore.get(ItsType); And instead of having to have a separate object and sql models and translation, you just have objects. It works in a client server environment, and for small projects is quite nice. Cheers Anton ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pg temp tables
On 06/03/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Anton Melser" <[EMAIL PROTECTED]> writes: > Thanks for your reply. I am managing a db that has some export scripts > that don't do a drop/create, but rather a delete from at the start of > the proc (6 or 7 tables used for this, and only this). Now given that > there is no vacuuming at all going on - this is clearly suboptimal but > in the general case is this better/worse than using temporary tables? Delete all rows, you mean? Have you considered TRUNCATE? Hi, ... I have considered lots of things - but I didn't write the scripts! Now that you mention it, I do remember that truncate is much better than delete from mytable; That is not what they wrote but hey. But even then, what are the advantages/disadvantages of temp tables? Is there a document somewhere I can consult which will give me the lowdown on permanent (but temporary) versus temporary tables in pg? Cheers Anton ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg temp tables
On 06/03/07, Robert Treat <[EMAIL PROTECTED]> wrote: On Saturday 03 March 2007 10:33, Anton Melser wrote: > Hi, > I have been going around telling everyone that there is no point using > physical tables in postgres for temporary storage within a procedure. > Why bother bothering the system with something which is only used in > one procedure I said to myself... I have just learnt that with MS Sql > Server, this is not the case, and that there are locks on some system > table and temp tables eat up memory and lots of other unfortunate > things. Can someone give me a 101 on temp table considerations? Or > rather give me "the good link"? The main issue against using temp tables involve bloat of some of the system catalogs, but it's no worse than doing create/drop cycles with standard tables, and better because they don't suffer as much i/o load. Thanks for your reply. I am managing a db that has some export scripts that don't do a drop/create, but rather a delete from at the start of the proc (6 or 7 tables used for this, and only this). Now given that there is no vacuuming at all going on - this is clearly suboptimal but in the general case is this better/worse than using temporary tables? Thanks again, Anton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] pg temp tables
Hi, I have been going around telling everyone that there is no point using physical tables in postgres for temporary storage within a procedure. Why bother bothering the system with something which is only used in one procedure I said to myself... I have just learnt that with MS Sql Server, this is not the case, and that there are locks on some system table and temp tables eat up memory and lots of other unfortunate things. Can someone give me a 101 on temp table considerations? Or rather give me "the good link"? Cheers Anton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] stored procedure optimisation...
RAISE NOTICE 'I am here, and myvar = % and thatvar = %', myvar, thatvar; Thanks... it is indeed a gem that little instruction!!! Cheers Anton ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] stored procedure optimisation...
Hi, I need to analyse some html to get some links out, and with only 25 lines in exports_tmp_links (and text_to_parse no more than around 10KB) this function has taken 10 minutes and counting. Something horribly wrong is going on here! Can someone give me any pointers? Cheers Anton delete from tmp_links_all; analyze exports_tmp_links; FOR tempRow IN SELECT * FROM exports_tmp_links LOOP startString := tempRow.text_to_parse; LOOP linkString := substring(startString, '(linkadministration.*=[0-9]+)'); IF linkString is null THEN EXIT; END IF; newlinkid := substring(linkString,'([0-9]+)'); INSERT INTO tmp_links_all (link_id,content_section,item_id,item_name,location_of_link) values (newlinkid,tempRow.content_section,tempRow.item_id, tempRow.item_name, tempRow.location_of_link); startString := substring(startString from position(newlinkid in startString)); END LOOP; END LOOP; analyze tmp_links_all; ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] select all matches for a regular expression ?
On 23/02/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Anton Melser" <[EMAIL PROTECTED]> writes: > I need to be able to get all the matches for a particular regexp from > a text field that I need to use in another query in a function. Is > this possible with plpgsql? Do I have to install the perl language? You need plperl (or pltcl; likely plpython would work too) --- the built-in regex functions don't have any way to return more than the first match. There's a patch pending to provide more functionality here for 8.3, but it won't help you today. Thanks for the info Cheers Anton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] greedy or not? regexps...
Intellectually challenged Anton strikes again! I wanted +, not *. Sometimes I think I'm not cut out for IT! :-( Thanks heaps, Anton On 23/02/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Anton Melser" <[EMAIL PROTECTED]> writes: > I am trying to understand the function substring. You haven't actually said anything that sounded surprising; and in any case, without seeing the data being operated on, we can't comment much on what's happening. I will note that '=([0-9]*)' is going to match to the *first* "=" in the string, whether there happen to be any digits after it or not, because the *-construct can match zero characters. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] select all matches for a regular expression ?
On 23/02/07, Peter Childs <[EMAIL PROTECTED]> wrote: On 23/02/07, Anton Melser <[EMAIL PROTECTED]> wrote: > Hi, > I need to be able to get all the matches for a particular regexp from > a text field that I need to use in another query in a function. Is > this possible with plpgsql? Do I have to install the perl language? > Cheers > Anton > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > yes. use "like" or "~" see http://www.postgresql.org/docs/8.2/static/functions-matching.html There is no need to use perl. ... I have read and re-read that page many times - I must be stupid :-(. For me both like and ~ on an expression will return true or false, and not a set of values. I have managed to get *one* value with substring(), but I need to get them all... As an example, I need to find all the occurences of digits in the following text myvar := 'hello4 is 4 very n1ce num8er'; so select substrings(myvar, '([0-9])); will return 4 4 1 8 Is *this* possible without perl? Could you give a paragraph number on that page if the info is there so I know exactly where to look? Thanks again, Anton ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] select all matches for a regular expression ?
Hi, I need to be able to get all the matches for a particular regexp from a text field that I need to use in another query in a function. Is this possible with plpgsql? Do I have to install the perl language? Cheers Anton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] greedy or not? regexps...
Hi,
I am trying to understand the function substring.
Here:
select SUBSTRING(cit1.summary, '=([0-9]*)') from cms_items cit1
where cit1.summary ~* '.*linkadministration.*[0-9]*';
gives me two empty strings and
select SUBSTRING(cit1.summary, '=([0-9]{1,10})') from cms_items cit1
where cit1.summary ~* '.*linkadministration.*[0-9]*';
gives me the right values.
However, bizarrely,
select SUBSTRING(ban1.url_id, '=([0-9]*)')
from banner ban1
where ban1.url_id ~* '.*linkadministration.*[0-9]*';
select SUBSTRING(ban1.url_id, '=([0-9]{1,10})')
from banner ban1
where ban1.url_id ~* '.*linkadministration.*[0-9]*';
Both give me the same result!!! The difference being that in case two
the numbers I am catching are at the end of the strings and in case 1
in the middle. Is this normal? Which is correct?
Cheers
Anton
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] getting postgres to emulate mysql/sqlserver bit datatype
On 13/02/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Anton Melser" <[EMAIL PROTECTED]> writes: > ERROR: operator is not unique: boolean = integer > I get this whether castcontext is 'a' or 'i'. If you make both cast directions the same priority then the system has no basis for choosing bool = bool over int = int or vice versa. Try making one direction 'i' and the other not. I'm not sure which one ought to be 'i', really --- it depends a lot on the details of the queries you are trying to make work. That did the trick. Both seem to work, so for me that's great. Thanks heaps. I may end up trying to get them to change it from 1 and 0 to '1' and '0', as sql server accepts it, and if sql server accepts it, I would be surprised if mysql doesn't... Thanks again, Anton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] getting postgres to emulate mysql/sqlserver bit datatype
On 12/02/07, Anton Melser <[EMAIL PROTECTED]> wrote: > I think actually what he needs is what Peter suggested upthread, namely > to weaken the context-restriction on the int-to-bool cast. Indeed... Peter's suggestion seems to have solved all my problems. So even though it probably shows just how embarrassingly bad my sql is... I spoke too soon! select executor_id, is_dedicated, is_connected, ping_time, host, port, usr_name, cpu_max, cpu_usage, cpu_avail, cpu_totalusage, mem_max, disk_max, num_cpus, os, arch from executor where is_dedicated = 1 and is_connected = 1 ERROR: operator is not unique: boolean = integer État SQL :42725 Astuce : Could not choose a best candidate operator. You may need to add explicit type casts. Caractère : 201 I get this whether castcontext is 'a' or 'i'. I am so close to having this fixed!!! Any ideas most welcome... Cheers Anton ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] getting postgres to emulate mysql/sqlserver bit datatype
I think actually what he needs is what Peter suggested upthread, namely to weaken the context-restriction on the int-to-bool cast. Indeed... Peter's suggestion seems to have solved all my problems. So even though it probably shows just how embarrassingly bad my sql is... update pg_cast set castcontext = 'i' where (castsource = (select oid from pg_type where typname = 'bool') and casttarget = (select oid from pg_type where typname = 'int4')) or (castsource = (select oid from pg_type where typname = 'int4') and casttarget = (select oid from pg_type where typname = 'bool')) For the archives. Thanks to everyone. Anton ps. This is probably only for situations where it is absolutely necessary... but I am now passing my nunit tests! :-) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] getting postgres to emulate mysql/sqlserver bit datatype
On 12/02/07, Tom Lane <[EMAIL PROTECTED]> wrote:
"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> Anton Melser wrote:
>> Is there any way
>> to force pg to accept 1 and 0 for boolean?
> postgres=# insert into bool_test values(1::boolean);
> INSERT 166968558 1
> postgres=# insert into bool_test values(0::boolean);
> INSERT 166968559 1
Possibly Anton is using an old version in which there wasn't a built in
int-to-bool cast?
In my searching I did turn up a comment (maybe from you even!) about
how it wouldn't work (before at least). I guess my problem is that
there is a body of sql that can't be changed, or at least the other
devs aren't interested enough in pg support to let me add a ton of if
pg else code. I think that creating a type is probably the way to go,
though if anyone has any advice I'm all ears.
I have .net code which has things like
bool myBool = datareader.GetBoolean(datareader.GetOrdinal("my_bool"));
Or something similar (I'm at work...). So I need to be able for npgsql
to return a boolean, but also need to be able to insert and compare
with straight 1, 0. I suppose there is a way that I can get around it
but after a couple of hours I haven't been able to come up with
anything.
Cheers
Anton
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] getting postgres to emulate mysql/sqlserver bit datatype
Hi, I am trying to port an app to postgres and have come up against a most annoying problem. The app works with both mysql and sqlserver, who both seem to have a bit datatype instead of a proper boolean like pg. Alas, pg won't accept 1 and 0 for boolean... and npgsql won't convert my numeric(1) into a boolean (and shouldn't either!). Is there any way to force pg to accept 1 and 0 for boolean? I know it probably isn't optimal, but hey. If there is a standard way to do this (and no, rewriting is not an option) I'm all ears. Cheers Antoine ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] drop table if exists mytable;
> I need to do a "drop table if exists" type thing. I realise I can Install 8.2 or use this function, posted by David Fetter: Thanks for your answers... so this really was something that was missing (I think it a little rich to come out with a "are you using a version without this" when it has just come out!) ? I am trying to add postgres support for alchemi (.net grid infrastructure), and I wanted to change as little as possible. Seeing as it didn't support postgres before, I suppose having 8.2 as a prereq ain't to bad. Cheers Antoine ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] drop table if exists mytable;
Hi, I need to do a "drop table if exists" type thing. I realise I can easily look in pg_tables, but for testing (if), don't I need to use a procedural language? In which case, I will need to install it if it doesn't exist - but I don't know how to test to see whether a language exists without using a language! Am I missing something simple here? Is there no way to do this outside of a function? Cheers Antoine ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Questions about horizontal partitioning
However, if the primary key is entirely within those six columns, there will have to be an index on it in both tables to enforce the primary key constraint. In that case, an inner join could be performed with an index lookup or an index scan plus hash join, for a query that didn't use any other columns. Whether that translates into a significant I/O reduction depends on how wide and how frequently non-NULL those other columns are. ... if someone is feeling pedagogical (and the answer isn't that complicated), could they explain why a simple index on the desired columns wouldn't be the best solution? Cheers Antoine ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Converting 7.x to 8.x
On 26/01/07, Jim Nasby <[EMAIL PROTECTED]> wrote:
On Jan 25, 2007, at 12:47 PM, Benjamin Smith wrote:
> On Tuesday 23 January 2007 13:55, Carlos wrote:
>> What would be the faster way to convert a 7.4.x database into an 8.x
>> database? A dump of the database takes over 20 hours so we want
>> to convert
>> the database without having to do a dump and resptore.
>
> You've probably already accounted for this, but make sure you've
> tried your
> options for loading the database. Using long ("insert") form vs
> copy can make
> a *huge* performance difference.
In case no one's mentioned it already, you can also perform this
migration using Slony, by making the 7.4 database the master, and
replicating to an 8.x database.
In case this is not what you meant above, you can pipe a dump directly
into psql, so that keeps it to only 20hrs...
Cheers
Antoine
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] temp tables and function performance
Hi, I am trying to move up in the world with my sql and need to do the following... I have a subscribers table and I need to export to csv (semi-colon separated) certain fields - that is fine, but I also need to export a multi-select field from another table as one string (0 to n values separated by commas) per line. The problem being that to get the actual string I have to go via 4 other relations... and as I have 200k+ subscribers this takes a while. My idea (which seems to work, though I haven't tested fully as it takes too damn long!), was to do the following. I would REALLY appreciate any pointers as my sql has never been this challenged! CREATE OR REPLACE FUNCTION mytest() RETURNS integer AS $BODY$DECLARE kindy INTEGER; BEGIN create temporary table tmp_interests( id bigint, value character varying(100) ) WITHOUT OIDS ON COMMIT DROP; insert into tmp_interests select distinct si.subid, rbi.value from restem rbi, cats cc, trm_terms tt, subrest si where rbi.key = cc.name and cc.catid = tt.modcid and tt.tid = si.themeid; create temporary table tmp_subscribers( email character varying(200), format character varying(4), interests character varying(1000), ) WITHOUT OIDS ON COMMIT DROP; insert into tmp_subscribers Select email, format, my_interests(id) as interests from subscriber; GET DIAGNOSTICS kindy = ROW_COUNT; copy tmp_subscribers to '/home/myname/subs.csv' WITH CSV DELIMITER AS ';' NULL AS ''; GET DIAGNOSTICS kindy = ROW_COUNT; return kindy; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; ... CREATE OR REPLACE FUNCTION my_interests(bigint) RETURNS character varying AS $BODY$DECLARE subid ALIAS FOR $1; interests character varying; myinterest RECORD; BEGIN interests := ''; FOR myinterest IN execute 'select value from tmp_interests where id = ' || subid LOOP if interests = '' then interests := myinterest.value; else interests := interests || ',' || myinterest.value; end if; END LOOP; RETURN interests; END$BODY$ LANGUAGE 'plpgsql' VOLATILE; ... select mytest(); If there are errors then please just focus on the logic, as I have cut back on the number of columns (for clarity) and changed a lot of the real table/names... Am I going about it the right way? Is there a better way? Thanks heaps, Antoine ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] n00b RAID + wal hot standby question
Thanks all for your very insightful and helpful answers. I will be able to really spend some time thinking about how the db will evolve (and so whether it is worth thinking about a change) in a week or so and will be able to think more on your answers then. Cheers Antoine ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] n00b RAID + wal hot standby question
Hi, I am just starting at a company and we are inheriting a previously built solution. It looks pretty good but my previous experience with pg is seriously small-time compared with this... I am very new at the job, and don't know what hd config we have but it will be RAID-something I imagine (hey I was working with desktop "servers" before this!). If that is very important I can find out. We seem to be saving our WAL to the same partition as PGDATA, and I notice that we are maxing out a reasonable looking server. The db is not very big (~4gig, 400meg pgdump), and though I can't see any vacuum strategy (autovacuum on a 8.1.4 is disabled), we didn't have as much consistent CPU usage at my old job (with a 6 gig db and MUCH less CPU and RAM, and probably as many connections), and my vacuum strategy was also pitiful! Sure, completely different environments, but I am thinking that WAL replication could be a factor. So my question... being in complete ignorance of how RAID works (the performance details)... would it be better to try and separate the WAL destination from PGDATA? How much of a difference could it make? Should we wait till the customer starts complaining (no explosion in traffic/db size realistic for the foreseeable future...)? Any abuse welcome. Cheers Antoine ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 8.1.5 installation problem with initdb on WinXP Home
Someone posted a solution to this in the last couple of days on this list. Have a good look... Cheers Antoine On 30/11/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: Hi, I apologize for being yet another noob posting about this problem. I have spent 2 days searching the archives and the net for a solution to no avail. Hence my post here. The problem I am having is on install of 8.1.5 it fails at the initdb call. In checking the log, it seems to fail when it creates the first data directory. It gets past the setting file permissions, but when it tries to create data/global it spits out an error message saying that "C:\Program Files" cannot be created. File exists. (Please excuse the fragmented message, I'm going off memory here). Of course at this point it rolls back the install. Does anyone have a solution to this problem? I would truly appreciate any help. Thanks. Alex. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] transfer just the data directories from linux to windows?
Hi, We have some backups from our prod server and I was wondering if there would be any problems with just copying the data directory to a windows install (same version - 8.1.4) ... any pointers? Cheers Antoine ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] "stopping" a single database in a cluster
Hi, Is it possible to stop a single database, like it is with Oracle? Our oracle admin asked this for FS backups, if there are 3 dbs (in separate tablespaces) then there seems no reason why this couldn't be possible... is it possible? Cheers Antoine ps. If this isn't possible, what experiences do people have with running several postgres instances on a single machine? ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] autovacuum blues
The idea is that you _can_ do without a cronjob. You may need to do a first ANALYZE just to get things warmed up, and then let autovacuum do its job. Thanks guys, I think I have it now! Cheers Antoine ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] autovacuum blues
On 09/11/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Anton Melser wrote: > Thanks for that. Just a clarification, can someone tell me what the > "number of tuples" means in the context of the multipliers? I mean, > when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I > get the min val, but what are we multiplying by 0.4? The total number > of tuples in the table? The total modified/deleted? pg_class.reltuples And so... For efficiency reasons, reltuples and relpages are not updated on-the-fly, and so they usually contain somewhat out-of-date values. They are updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX. A stand-alone ANALYZE, that is one not part of VACUUM, generates an approximate reltuples value since it does not read every row of the table. The planner will scale the values it finds in pg_class to match the current physical table size, thus obtaining a closer approximation. So I am still a little unclear... I need to analyse to get relevant stats for autovacuum... but autovacuum needs to be used to update the relevant stats? Can I REALLY do without a cronjob, or am I just thinking wishfully (I can invent expressions if I want!). Cheers Antoine ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] autovacuum blues
On 09/11/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Anton Melser wrote: > Hi, > I just can't understand why autovacuum is not working. I have a test > db/table which I insert values into (by the thousands) and can't work > out why my stats don't get updated. Could someone have a quick look at > my attached .conf and tell me what I am doing? > I am running it on FC5 8.1.4.fc5.1. You can tell whether autovacuum is running by using SHOW autovacuum; Note that if you have a firewall of some sort stopping UDP messages from being transmitted inside your machine, the stats collector may not start, which may stop the autovacuum daemon from starting. If you're not seeing stat updates then there's probably something like that going on. Maybe the system has emitted a warning message at server start; check the logs. Thanks for that. Just a clarification, can someone tell me what the "number of tuples" means in the context of the multipliers? I mean, when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I get the min val, but what are we multiplying by 0.4? The total number of tuples in the table? The total modified/deleted? I guess it is just that I assumed that it would kick in regularly, and it doesn't seem to. Probably it is not needed but I always got the feeling that after a good vacuum performance seemed better. Cheers Antoine ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] autovacuum blues
On 09/11/06, Richard Huxton wrote: Anton Melser wrote: > Hi, > I just can't understand why autovacuum is not working. I have a test > db/table which I insert values into (by the thousands) and can't work > out why my stats don't get updated. Could someone have a quick look at > my attached .conf and tell me what I am doing? > I am running it on FC5 8.1.4.fc5.1. 1. Check "ps auxw | grep postgres" to see if it's running. 2. If so, check your PG logs and see if you see any activity. Turn connection and statement logging on and see what appears. ... postgres 1300 0.0 1.1 20180 3048 ?S12:03 0:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data postgres 1302 0.0 0.2 9968 544 ?S12:03 0:00 postgres: logger process postgres 1304 0.0 0.4 20316 1188 ?S12:03 0:00 postgres: writer process postgres 1305 0.0 0.6 10968 1544 ?S12:03 0:00 postgres: stats buffer process postgres 1306 0.0 0.3 10200 796 ?S12:03 0:00 postgres: stats collector process ... Any chance you could give me some pointers on activating logging? My thoughts were to log log_planner_stats = on log_min_messages = info Anything else? Cheers Antoine ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] autovacuum blues
Hi, I just can't understand why autovacuum is not working. I have a test db/table which I insert values into (by the thousands) and can't work out why my stats don't get updated. Could someone have a quick look at my attached .conf and tell me what I am doing? I am running it on FC5 8.1.4.fc5.1. Cheers Antoine postgresql.conf Description: Binary data ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] grid things and postgres
Hi, I am working on the G System project (BSD licenced) and we are going to be (eventually) implementing a distributed database to hold data for the game that (eventually) will be developed. People have (though I don't think anyone has seriously investigated yet) suggested that we use a grid with postgresql databases to serve the data. There will also be processing of data, so it's not just a distributed database, I guess. The idea is to have a virtual reality universe (GUniverse) that is able to evolve. It is going to be along the lines of a MMORPG (or whatever), but not only can you get new guns, you can also develop them using resources, have hurricanes, whatever. Ideas are still fairly nebulous as you can see! My understanding is that (grid) machines will probably take over management of certain parts of the universe, and so people (cities, countries, etc) will be managed by different servers. When you go from one to another you would be passed over. There would also be events that might effect more than one person (city, country, etc) so would need to be able to be spread, etc. There would also be things that applied to the whole system. What exactly is handled by client and server is for the future. Does this sound like a job for globusTK? I read a bit about clusgres but it is not OSS. I realise that I don't have a clue but would like some ideas on how to get one! Does this sound like something postgresql might be at home with? Am I talking about a grid or nothing of the sort? I saw that oracle 10g has "grid" capabilities but aside from being out of the question, I don't think it would do what we wanted anyway. Please excuse the waffley nature of this post. Cheers Antoine ps. I think the devs are mainly C++ but as far as I could see java has a head start in terms of grids and it wouldn't be out of the question. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
