Re: [GENERAL] optimizing daily data storage in Pg
Does ordering the data in the table by day, cell_id help at all? How big ARE the indexes we're talking about? If the total size of all the indexes you need to do your queries run into the hundreds of gigabytes, going from 12 to 32 Gigs of RAM may be like flapping your arms our the window of your car in terms of effectiveness. If the indexes that you need at one time add up to something in the 32 to 256Gig range then adding enough memory to hold most or all of them at once would help and is doable, but the price goes up fast when you get to the 64Gig range. If everything is read only, then pg may or may not be the best fit. It sounds more like what you're doing is batch processing, or at least batch processing friendly. If you could roll up your data, either in the db or beforehand while preparing it, that might be a big win. Is your machine IO bound when running these queries? What does iostat -xd 1 say about throughput and % utilization? I'm assuming you're running an OS with sysstat available. If you're on something else, then you'll need to research how to see your IO workload on that OS. htop is a nice graphical way of seeing your wait states as well, with the red bars representing IO wait on a machine. If your machine is IO bound, and you've gotten enough ram to hold the working set of your indexes, then you'll need more hard drives under good controllers to make it faster. The bad news is that RAID controllers and lots of hard drives can be expensive, the good news is that reporting servers (which is sounds like what this is) use a lot of sequential access, and throwing more drives at the problem gives big gains, usually. As far as partitioning goes, I think you either need to use fewer partitions, or just use individual tables without using the parent table to access them. It's a well known problem with partitioning that past a few hundred or so child tables things get slow pretty fast. Having 200 to 500 tables, maybe even 1,000 is workable, but past that no, not really. If you're IO bound, then you'll likely need more CPU horsepower. -- 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] Bitmask trickiness
On Fri, Jul 23, 2010 at 3:02 PM, Scott Marlowe scott.marl...@gmail.com wrote: If you mean, did I read the bit in the doco where it said nothing at all in the 'these are great advantages' style I've just described, but instead makes the fairly obvious point that a bit string takes 8 bits to store a group of 8 bits (well, stone me!!) Wow, I'm surprised you get any help with your attitude. I posted a link and asked a question and right up front got my head handed to me. To quote: Why on Earth would I want to store this sort of stuff in a bit string?! I don't know about you, but I find looking at 21205 a darn'd site easier than staring blankly at 101001011010101!! Like I'd somehow bitten your hand when I asked my question. That is not attitude, Scott. That is me asking a question riddled with rhetorical exclamations. You asked me why I wasn't using bitstrings. I asked why on Earth I would. That's all. The only attitude you could reasonably detect there, I think, is surprise that when I ask a precise technical question, someone would feel it fair game to question the entire basis of a database design about which they know extremely little (which is not their -or your- fault, because you only get to know the details I include in the question, after all. Fact remains, on such slim foundations, I wouldn't build a mountain of questioning the reasons for or behind someone else's work). PLUS has extra overhead, then yes, I did read that part of your first link... and nevertheless concluded that, overall, there is... er, some extra overhead in storing bitstrings. Well, your initial answer certainly didn't give ANY idea that you'd read that page. It neither gave the impression I had, nor gave any indication that I hadn't! That's the point: you've assumed something you needn't have. So what precisely about that first article, which I did indeed read, would you have expected to lead me to the conclusion that I'd SAVE significant amounts of space or find some other technically-compelling reason for switching? I didn't expect such. I asked why you weren't using them, and gave you some links to read on it. It clearly states that bit strings use a bit per bit, plus some overhead. Now, I had no idea if you were dealing with bigints and 60 bit strings or 5 bit strings. In fact, you did little to really describe your project and preferences in your post. Which is why my response was short and concise, I had little to go on. Exactly. Now, I grant you it's difficult when you can't see me; when there's only the written word to go on; when you don't know my personal modes of expression, nor I yours. But that's precisely why I wouldn't answer a narrowly-scoped technical question with even a hint of a suggestion that the entire basis of the question was suspect: I didn't give you any details of my project or preferences, because I was asking a precise, narrowly-focussed question about getting one specific result with one specific query structure. Honestly, when I want general consulting, I pay for it. You really don't have to try and give it away for free. My point is that there's nothing much in it, storage-wise, either way. Well, there is the fact that bit strings can restrict the size of the entry so you don't accidentally get an int stored that's got more bits than your model can handle. Well, that's fortunately not something I have to worry about. Somewhere around the 192th bit, I'll start panicking. When I've only got 15 of the blighters to worry about, I think I'll cope. There's also the issue that if / when you ever get close to the last bit in an int bitstring may behave oddly because of sign issues. And also something I don't have to worry about. So there's no compelling technical reason to switch. I never said there was. I simply asked a question, and got my hand bitten. No, you got a question asked back at you: *WHY* would you think I should be using a bitstring? It was an honest question, basically wondering if there's something about bitstrings that make them such a great idea. It's not a data type Oracle users are greatly familiar with, you see. And without a technically-compelling reason, the rest of the post I was referring to simply boiled down, as far as I could tell, to a matter of personal preference. No less valid for that, of course. But ultimately, not something that would hold much sway with me. Sure, fine, whatever you want. I wasn't trying to convince you either way. I do think using the right type for the job makes more sense, but again, it's personal preference. Indeed. Although in this case, it's not even the right type for the job. But simply saying your design is broken... wo! might well scare the children, but doesn't really do anything for me, because I know for a certainty that it's not broken at all. I asked if there was a reason you were avoiding bit strings. Hardly a your design is broken point. I'm
Re: [GENERAL] Bitmask trickiness
On 7/23/2010 2:38 AM, Howard Rogers wrote: Still doesn't answer the precise, specific technical question I actually asked, though, does it?! Which was answered by Stephen Cook was it not? I.e. use plain old equals? Maybe I should assume you haven't read the thread, then?! God knows what that answer even actually meant, but hopefully you read my reply where I pointed out that it's no answer at all. 21205 4098 = what, precisely? Never mind: another rhetorical question. Plain old equals doesn't come close. Hate to interrupt your flame war, and I apologize for not being precise in my meaning first try... You don't need any bitwise anything to compare two bitmasks-hiding-in-integers, just check for equality. Instead of select * from coloursample where colour 10 = 10; just try select * from coloursample where colour = 10;. If you want to probe for two values, that MUST be in there, and WITHOUT anything else, bitwise OR them together as the probe value and use plain old equals there too. You only need the bitwise AND stuff for checking for a value that MUST be in there, regardless of whether or not other values are in there as well. Hope I was clearer this time. Originally I just fired off a quickie email to get you past your coder's block. -- Stephen -- 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] Bitmask trickiness
I thought to do select * from coloursample where colour 10 = 10; ...but that's not right, because it finds the third record is a match. What's not entirely clear to me is whether you only want to find colours that have BOTH Yellow and Orange set and nothing else, or colours that have EITHER Yellow and Orange set and nothing else. The first case has been answered by Stephen (use a straight 'equals'). The other case is a bit more complicated. That 11 matches using 10 is because you filtered out all the other bits in your comparison by anding them with '0', while they /are/ relevant: they aren't allowed to be '1' after all. You probably need to look at the inverted versions of these numbers to get what you need. My bit-foo is a bit rusty, but this looks like what you need (I used bit-strings for my own convenience): development= select (~ '01010'::bit(5)) | '01010'::bit(5) = '1'::bit(5); ?column? -- t (1 row) development= select (~ '01011'::bit(5)) | '01010'::bit(5) = '1'::bit(5); ?column? -- f (1 row) development= select (~ '01110'::bit(5)) | '01010'::bit(5) = '1'::bit(5); ?column? -- f (1 row) development= select (~ '11010'::bit(5)) | '01010'::bit(5) = '1'::bit(5); ?column? -- f (1 row) development= select (~ '00010'::bit(5)) | '01010'::bit(5) = '1'::bit(5); ?column? -- t (1 row) development= select (~ '01000'::bit(5)) | '01010'::bit(5) = '1'::bit(5); ?column? -- t (1 row) 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,4c49503f286213027486771! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Finding last checkpoint time
On Tue, 2010-07-20 at 16:15 -0400, Greg Smith wrote: Devrim GÜNDÜZ wrote: Is there a way to find last checkpoint time via SQL command? I know I can grep xlogs by turning on log_checkpoints, but I'd prefer an SQL solution. Not directly. Best you can do without linking in new server code is either import the logs via CVS to get them into a table, or shell out and look at what comes out of pg_controldata. There have been two works in progress to improve this situation that didn't make it through to commit yet. snip What about adding a column to pg_stat_bgwriter, like last_checkpoint or similar? Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Difference between EXPLAIN ANALYZE SELECT ... total runtime and SELECT ... runtime
2010/7/23 A. Kretschmer andreas.kretsch...@schollglas.com: Maybe cheaply or virtuell hardware? Cheap - probably, I known processor, but don't know mainboard. It's dedicated server. Maybe it has silent problems with time keeping. Virtual - also yes. Postgresql run in separate vserver, but I've executed psql also from that vserver, so there should be no differences. But I'll try to test it without vserver. There are some issues with functions like gettimoofday(), see here: http://archives.postgresql.org/pgsql-general/2007-01/msg01653.php (and the whole thread) Thanks, i will look at it. -- Piotr Gasidło -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Data dumps to files - best methods?
Good day all As I am fairly new to postgresql I am trying to find some more info regarding options to dump specific data to files. Let me give you guys some idea on what I mean by this Currently there is a java/perl process that runs and creates datadumps for clients with specific data as per specifications and this creates 4 output files which then are compressed and ftp'd to the client. The current process takes a very long time to run despite the fact that it does not need to process a lot of data. Optimisations have been done on the Database side and the process is still running very long. What we are now trying to achieve is to use the Linux scripting and SQL scripting combination to try and rewrite the process in order to compare the two processes. However, even though I can get the sql query,etc... how will I use this to dump the data into the relevant files? Any suggestions and assistance would be greatly appreciated. Regards Machiel
Re: [GENERAL] Bitmask trickiness
On 7/23/2010 5:33 AM, Howard Rogers wrote: ...so select * from table where 21205 | 4097 = 21205 would correctly grab that record. So I'm assuming you mean the 'stored value' should be on both sides of the equals test. If so, that would indeed seem to be the ultimate answer to the question (though I wouldn't myself call it a 'plain old equals' :-) ) Hope I was clearer this time. Originally I just fired off a quickie email to get you past your coder's block. I do indeed think the magic of BIT OR is the missing ingredient I was looking for, and I very much appreciate your help leading me to it. My apologies for being too dense to spot what you were talking about before. I think I misunderstood you the whole time actually, or maybe was injecting some of my other thoughts into your problem. I figured you meant you wanted to find records where your probe value has exactly the same bit pattern as your stored value (probe bits, and only probe bits, set; hence the plain old equals). Rather (and I just confirmed this looking at the OP) you want any records where the stored value has all of the probe value's bits set, regardless of the other bits in the stored value. So yeah, check if ORing the stored and probe values equals the stored value. Oh well, even if I misread, glad to help you stumble upon what you wanted eventually. -- Stephen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Centos 5.5 and Postgres 9.0.beta3 install errors
Dear all, Can anyone help me with the errors I seem to be having installing the latest Postgresql 9.0 beta 3 rpms from the PGDG-9.0 repository on Centos 5.5? I am having dependency issues: Error: Missing Dependency: libpq.so.4 is needed by package postgresql-libs-9.0-beta3_1PGDG.el5.i386 (pgdg90) Error: Missing Dependency: libecpg.so.5 is needed by package postgresql-libs-9.0-beta3_1PGDG.el5.i386 (pgdg90) Error: Missing Dependency: libpgtypes.so.2 is needed by package postgresql-libs-9.0-beta3_1PGDG.el5.i386 (pgdg90) I've tried installing postgresql-libs from the original Centos repository which provides all of the above files however when trying to update the packages to PG 9.0 I continue to get these errors. Thank you, Tom Robst -- -- 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] Centos 5.5 and Postgres 9.0.beta3 install errors
On Fri, 2010-07-23 at 10:50 +0100, Tom Robst wrote: Can anyone help me with the errors I seem to be having installing the latest Postgresql 9.0 beta 3 rpms from the PGDG-9.0 repository on Centos 5.5? I am having dependency issues: Error: Missing Dependency: libpq.so.4 is needed by package postgresql-libs-9.0-beta3_1PGDG.el5.i386 (pgdg90) Error: Missing Dependency: libecpg.so.5 is needed by package postgresql-libs-9.0-beta3_1PGDG.el5.i386 (pgdg90) Error: Missing Dependency: libpgtypes.so.2 is needed by package postgresql-libs-9.0-beta3_1PGDG.el5.i386 (pgdg90) Apparently RPM repo maintainers needs not only to build, but test installing, etc. before pushing packages. I just tried it with my repo, and it worked well: Package Arch VersionRepository Size Installing: httpd x86_642.2.3-43.el5.centosbase 1.2 M postgresql-server x86_649.0-beta3_1PGDG.rhel5 pgdg904.5 M Installing for dependencies: apr x86_641.2.7-11.el5_3.1 base 118 k apr-util x86_641.2.7-11.el5 base 79 k compat-postgresql-libsx86_644-1PGDG.rhel5 pgdg90 62 k libxslt x86_641.1.17-2.el5_2.2 base 488 k postgresqlx86_649.0-beta3_1PGDG.rhel5 pgdg901.3 M postgresql-libs x86_649.0-beta3_1PGDG.rhel5 pgdg90206 k Transaction Summary Install 8 Package(s) Upgrade 0 Package(s) Total download size: 8.0 M Is this ok [y/N]: y See the compat-postgresql-libs there. My repo? It is http://yum.pgrpms.org . -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Centos 5.5 and Postgres 9.0.beta3 install errors
Hi Devrim, Thank you for your reply...I've run an yum clean all and re-installed the pgdg-centos-9.0-2.noarch rpm but now I'm getting this error: http://yum.pgrpms.org/9.0/redhat/rhel-5-i386/postgresql-libs-9.0-beta3_1PGDG.rhel5.i386.rpm: [Errno -1] Package does not match intended download (on all the packages from yum.pgrpms.org). Is this a problem at my end? Thank you, Tom -- On 07/23/10 11:11, Devrim GÜNDÜZ wrote: On Fri, 2010-07-23 at 10:50 +0100, Tom Robst wrote: Can anyone help me with the errors I seem to be having installing the latest Postgresql 9.0 beta 3 rpms from the PGDG-9.0 repository on Centos 5.5? I am having dependency issues: Error: Missing Dependency: libpq.so.4 is needed by package postgresql-libs-9.0-beta3_1PGDG.el5.i386 (pgdg90) Error: Missing Dependency: libecpg.so.5 is needed by package postgresql-libs-9.0-beta3_1PGDG.el5.i386 (pgdg90) Error: Missing Dependency: libpgtypes.so.2 is needed by package postgresql-libs-9.0-beta3_1PGDG.el5.i386 (pgdg90) Apparently RPM repo maintainers needs not only to build, but test installing, etc. before pushing packages. I just tried it with my repo, and it worked well: Package Arch VersionRepository Size Installing: httpd x86_642.2.3-43.el5.centosbase 1.2 M postgresql-server x86_649.0-beta3_1PGDG.rhel5 pgdg904.5 M Installing for dependencies: apr x86_641.2.7-11.el5_3.1 base 118 k apr-util x86_641.2.7-11.el5 base 79 k compat-postgresql-libsx86_644-1PGDG.rhel5 pgdg90 62 k libxslt x86_641.1.17-2.el5_2.2 base 488 k postgresqlx86_649.0-beta3_1PGDG.rhel5 pgdg901.3 M postgresql-libs x86_649.0-beta3_1PGDG.rhel5 pgdg90206 k Transaction Summary Install 8 Package(s) Upgrade 0 Package(s) Total download size: 8.0 M Is this ok [y/N]: y See the compat-postgresql-libs there. My repo? It is http://yum.pgrpms.org .. !DSPAM:4,4c496e0a280414995866539! -- 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] Data dumps to files - best methods?
Hi, Am 23.07.2010 um 10:32 schrieb Machiel Richards: As I am fairly new to postgresql I am trying to find some more info regarding options to dump specific data to files. However, even though I can get the sql query,etc... how will I use this to dump the data into the relevant files? You can use the \copy command in psql to export the result of query into a file. For example: psql -c \\copy (select * from atable) to 'myfile.csv' with delimiter as ',' csv header adb will copy all rows from atable in adb to myfile.csv in csv format. Ralf
Re: [GENERAL] Data dumps to files - best methods?
Thank you very much, I think this will help a lot. Will ask for more details once I receive the full specs,etc... Machiel Richards MySQL DBA Email: machi...@rdc.co.za Tel: 0861 732 732 RDC_Logo From: Ralf Schuchardt [mailto:r...@gmx.de] Sent: 23 July 2010 01:37 PM To: Machiel Richards Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Data dumps to files - best methods? Hi, Am 23.07.2010 um 10:32 schrieb Machiel Richards: As I am fairly new to postgresql I am trying to find some more info regarding options to dump specific data to files. However, even though I can get the sql query,etc... how will I use this to dump the data into the relevant files? You can use the \copy command in psql to export the result of query into a file. For example: psql -c \\copy smb://copy (select * from atable) to 'myfile.csv' with delimiter as ',' csv header adb will copy all rows from atable in adb to myfile.csv in csv format. Ralf image001.jpg
Re: [GENERAL] index scan and functions
Le mardi 20 juillet 2010, à 10:11:21 +0200, Harald a écrit : In article 20100719162547.ga17...@localhost, arno a...@renevier.net writes: Thanks, that's exactly what I was looking for. No, I'd say you're looking for the ip4r package which provides an indexable IP address range type. Thanks, I'll look into it. signature.asc Description: Digital signature
Re: [GENERAL] Difference between EXPLAIN ANALYZE SELECT ... total runtime and SELECT ... runtime
W dniu 23 lipca 2010 10:51 użytkownik Piotr Gasidło
qua...@barbara.eu.org napisał:
2010/7/23 A. Kretschmer andreas.kretsch...@schollglas.com:
Maybe cheaply or virtuell hardware?
(...)
There are some issues with functions
like gettimoofday(), see here:
(..)
Just tested it on my workstation. No vserver. The same result.
EXPLAIN ANALYZE SELECT ...
Total runtime: 72.745 ms
Time: 1916,269 ms
SELECT ...
Time: 2121,198 ms
The table structure is:
ocaches
ocaches_2010_06_0 (inherits ocaches)
ocaches_2010_06_1 (inherits ocaches)
ocaches_2010_06_2 (inherits ocaches)
ocaches_2010_06_3 (inherits ocaches)
ocaches_2010_06_4 (inherits ocaches)
ocaches_2013_06_4 (inherits ocaches)
Constraint checks by date on each partition.
\d ocaches
Table ocaches
Column |Type | Modifiers
---+-+---
oc_count | integer |
oc_h_id | integer |
oc_date_from | date|
oc_date_to| date|
oc_duration | integer |
...
Many columns mainly integer and integer[].
Index on column oc_h_id on each paritioned table.
query:
SELECT
oc_h_id,oc_duration,SUM(oc_count) FROM ocaches_joined WHERE
oc_date_from = '2010-07-22'::date AND oc_date_from =
'2010-07-24'::date AND oc_h_id =
ANY('{32842,3095,27929,2229,22769,3098,33433,22559,226,2130,226,2130,2229,3095,3098,22559,22769,27929,32842,33433}'::int[])
GROUP BY oc_h_id, oc_duration;
--
Piotr Gasidło
--
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] Question about SCO openserver and postgres...
On 07/22/2010 05:39 PM, Scott Marlowe wrote: On Thu, Jul 22, 2010 at 3:24 PM, Edmundo Robles L. erob...@sensacd.com.mx wrote: Hi! I have a problem with the max postgres connections on SCO Openserver 5.0.7, so ...my boss decided to buy the SCO Openserver 6.0 but this version comes in 2 editions: Starter and Enterprise. If SCO 5.0.7 only allows 95 ( -3 used by superuser) connections to postgres... Do you know how many connections to postgres can i have with OpenServer in Starter Edition or Enterprise edition? Are you sure this isn't just a limit in max_connections in postgresql.conf? Yes, i sure. i have the same problem with postgres 7.2 (100 connections) and 8.3.11 (only 95 :-( ) I change the max_connections on postgres , on SCO 5.0.7 set the SHMMAX,SHM* to the maximun value and relink the SCO kernel but always , i have only 95 client connected to postgres no more. That is because we want buy SCO 6.0 but i don't know if we will have the same problem, our programs are developed on SCO so the migration to another operative system is not a choice... for now. By the way i send a mail to SCO but until now they don't answer to me. -- 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] optimizing daily data storage in Pg
On 7/23/2010 12:39 AM, P Kishor wrote: On Thu, Jul 22, 2010 at 4:56 PM, Andy Colsona...@squeakycode.net wrote: On 7/22/2010 9:41 AM, P Kishor wrote: I have been struggling with this for a while now, have even gone down a few paths but struck out, so I turn now to the community for ideas. First, the problem: Store six daily variables for ~ 25 years for cells in a grid. * Number of vars = 6 * Number of cells ~ 13 million * Number of days ~ 9125 (25 * 365) Optimize the store for two different kinds of queries: Query one: Retrieve the value of a single var for all or a portion of the cells for a single day. This is analogous to an image where every pixel is the value of a single var. SELECTvarFROM d WHERE yr = ? AND yday = ?; SELECTvarFROM d WHERE yr = ? AND yday = ? AND cell_id IN (?,?,?...); Query two: Retrieve values for all the days or a duration of days for a single var for a single cell. This is like grabbing a column out of a table in which each row holds all the vars for a single day. SELECTvarFROM d WHERE cell_id = ?; SELECTvarFROM d WHERE cell_id IN (?,?,?...); First, I must admit to not reading your entire email. I am not sure how to respond to your feedback give that you haven't read the entire email. Nevertheless, thanks for writing... Heh, sorry, my point was, you put a lot of information into your email, and I was going to only use one bit of it: row counts. Second, Query 1 should be fast, regardless of how you layout the tables. It is not fast. Right now I have data for about 250,000 cells loaded. That comes to circa 92 million rows per year. Performance is pretty sucky. This query should return one record, correct? This should be very fast, PG should be able to find the record in the index within 5 seeks, and then find the data in one seek. Can you post 'explain analyze' for this query. (Or, it could be the case, I totally misunderstood your data) Third, Query 2 will return 13M rows? I dont think it matters how you layout the tables, returning 13M rows is always going to be slow. Yes, I understand that. In reality I will never get 13 M rows. For display purposes, I will probably get around 10,000 rows to 50,000 rows. When more rows are needed, it will be to feed a model, so that can be offline (without an impatient human being waiting on the other end). Right now, my main problem is that I have either too many rows (~4 B rows) in a manageable number of tables (25 tables) or manageable number of rows (~13 M rows) in too many tables (~9000 tables). Searching by just cell_id is not going to be very selectable, and with large result-sets I can see this one being slow. As Scott talked about in his response, this one will come down to hardware. Have you dd tested your hardware? -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] JASPA (JAva SPATial) for PostgreSQL and H2 released
(sorry for the cross posting) * text in english ** After one year of development, we are pleased to announce the release 0.1RC1 of JASPA (JAva SPAtial). JASPA potentially brings around 200 spatial functions to any relational database system that supports a full set of java store procedures. In this 0.1 version JASPA supports PostgreSQL and H2, and we are already working with HSQLDB. JASPA has a similar functionality as PostGIS 1.4, supporting spatial operators and predicates, arrays of geometries, spatial aggregates, etc. JASPA is written in java and is easy to extend using java store procedures. JASPA is licensed under the GNU GPL. This project has been possible thanks to other open source projects. Besides to take advantage of these projects we have used their mailing lists and they helped us a lot. The JASPA team is very grateful to them: - PostGIS. The mirror on which JASPA has been looking at. - JTS (Java Topology Suite). This library is widely used in JASPA and many open source initiatives bringing the possibility to use spatial analysis to the java open source world. - GeoTools (used for projections, KML support and Shape to JASPA converters) - PostgreSQL and PLJAVA (Java store procedures for PostgreSQL) - H2 and H2Spatial (a Java database and its spatial extension used at the beginning of JASPA) - HSQLDB (a Java database which probably will be supported by JASPA in the next release) - gvSIG (a desktop SIG which we hope it can connect to JASPA soon) The authors of this project are: Jose C. Martinez-Llario. Developer and project director. (1) Marta Gonzalez-Alcaide. Tester and document builder. (1) (1)Deparment of Cartographic Engineering, Geodesy and Photogrammetry at La Universidad Politecnica de Valencia (Spain). Any contributor is very welcomed to join the JASPA project. JASPA Download: - The latest software can be found at: http://forge.osor.eu/projects/jaspa/ - The full JASPA documentation can be found at: http://jaspa.forge.osor.eu/. The online manual (the pdf version has more than 300 pages) includes the installation process from binaries and source, a tutorial and a complete reference of the JASPA commands. - Mailing list: http://lists.forge.osor.eu/mailman/listinfo/jaspa-users - JASPA is hosted on OSOR.EU at: http://www.osor.eu/projects/jaspa * text in spanish ** Tras un año de desarrollo, queremos presentaros la versión 0.1RC1 de JASPA (JAva SPAtial). JASPA potencialmente implementa alrededor de 200 funciones espaciales sobre cualquier base de datos relacional que soporte procedimientos almacenados en java. En esta primera versión JASPA soporta PostgreSQL y H2. Actualmente estamos trabajando para incorporar HSQLDB. JASPA tiene una funcionalidad muy similar a PostGIS 1.4, soportando predicados y operadores espaciales, arreglos de geometrías, agregados espaciales, etc. JASPA está programado en java y es bastante fácil de extender ampliando funcionalidades utilizando procedimientos almacenados en java. JASPA tiene una licencia GNU GPL. Este proyecto ha sido posible gracias a otros muchos proyectos de código abierto. Además de utilizar estos proyectos, sus correspondientes listas de correo nos han ayudado de forma rápida y eficaz. El equipo de JASPA quiere mostrar su gratitud especialmente a: - PostGIS. Es el espejo en el que JASPA se ha mirado. - JTS (Java Topology Suite). Biblioteca ampliamente utilizada en JASPA y muchos otros proyectos de código abierto, ofreciendo la posibilidad de realizar análisis espaciales. - GeoTools (biblioteca utilizada para las proyecciones y el soporte de KML y los importadores shape). - PostgreSQL and PLJAVA (implementa procedimientos almacenados en java para PostgreSQL) - H2 y H2Spatial (H2 es una base de datos especial desarrollada en Java, al inicio de JASPA H2 Spatial y Spatial Box ofrecieron ideas en la que se basó JASPA) - HSQLDB (base de datos java que posiblemente será soportada por JASPA en la próxima versión) - gvSIG (SIG de escritorio con el que realmente deseamos que JASPA se pueda conectar) Los autores de este proyecto son: Jose C. Martinez-Llario. Developer and project director. (1) Marta Gonzalez-Alcaide. Tester and document builder. (1) (1)Deparment of Cartographic Engineering, Geodesy and Photogrammetry at La Universidad Politecnica de Valencia (Spain). Cualquier persona que quiera contribuir es bienvenida. JASPA Download: - Los binarios y el código fuente se puede encontrar en: http://forge.osor.eu/projects/jaspa/ - La documentación complete de JASPA en: http://jaspa.forge.osor.eu/. El manual en línea (la versión pdf tiene más de 300 páginas) incluye el proceso de instalación de forma detallada de los binarios y si se quiere compilar el fuente, un tutorial y una guía de referencia de todos los comandos de JASPA. - Lista de distribución (de momento sólo en inglés):
Re: [GENERAL] Bitmask trickiness
On Fri, Jul 23, 2010 at 7:57 PM, Stephen Cook scli...@gmail.com wrote: On 7/23/2010 5:33 AM, Howard Rogers wrote: ...so select * from table where 21205 | 4097 = 21205 would correctly grab that record. So I'm assuming you mean the 'stored value' should be on both sides of the equals test. If so, that would indeed seem to be the ultimate answer to the question (though I wouldn't myself call it a 'plain old equals' :-) ) Hope I was clearer this time. Originally I just fired off a quickie email to get you past your coder's block. I do indeed think the magic of BIT OR is the missing ingredient I was looking for, and I very much appreciate your help leading me to it. My apologies for being too dense to spot what you were talking about before. I think I misunderstood you the whole time actually, or maybe was injecting some of my other thoughts into your problem. I figured you meant you wanted to find records where your probe value has exactly the same bit pattern as your stored value (probe bits, and only probe bits, set; hence the plain old equals). Rather (and I just confirmed this looking at the OP) you want any records where the stored value has all of the probe value's bits set, regardless of the other bits in the stored value. So yeah, check if ORing the stored and probe values equals the stored value. Oh well, even if I misread, glad to help you stumble upon what you wanted eventually. -- Stephen No worries. We got there in the end! Thanks again, HJR -- 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] Bitmask trickiness
Hate to interrupt your flame war, and I apologize for not being precise in my meaning first try... You don't need any bitwise anything to compare two bitmasks-hiding-in-integers, just check for equality. Instead of select * from coloursample where colour 10 = 10; just try select * from coloursample where colour = 10;. Which works for that example. But please consider the more realistic one I also posted. The stored value is 21205 (I think... it was all a long time ago). I probe that with 4098. I do NOT want that returned, because the '2' bit is not set in the 21205 value. So select * from table where 21205=4098 won't cut it, will it? If you want to probe for two values, that MUST be in there, and WITHOUT anything else, bitwise OR them together as the probe value and use plain old equals there too. You only need the bitwise AND stuff for checking for a value that MUST be in there, regardless of whether or not other values are in there as well. A Bitwise OR? Ah ha: I think that might be the key: ims=# select 21205 | 4098; ?column? -- 21207 (1 row) So again, what exactly am I supposed to test for here? I mean, select * from table where 21205 | 4098 = 21205 would do it, I suppose, because that would correctly reject the row. But so would select * from table where 21205 | 4098 = 4098. However, if I stumble on a bit further, I do manage this: ims=# select 21205 | 4097; ?column? -- 21205 (1 row) ...so select * from table where 21205 | 4097 = 21205 would correctly grab that record. So I'm assuming you mean the 'stored value' should be on both sides of the equals test. If so, that would indeed seem to be the ultimate answer to the question (though I wouldn't myself call it a 'plain old equals' :-) ) Hope I was clearer this time. Originally I just fired off a quickie email to get you past your coder's block. I do indeed think the magic of BIT OR is the missing ingredient I was looking for, and I very much appreciate your help leading me to it. My apologies for being too dense to spot what you were talking about before. Regards HJR -- 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] Bitmask trickiness
On Fri, Jul 23, 2010 at 6:17 PM, Alban Hertroys dal...@solfertje.student.utwente.nl wrote: I thought to do select * from coloursample where colour 10 = 10; ...but that's not right, because it finds the third record is a match. What's not entirely clear to me is whether you only want to find colours that have BOTH Yellow and Orange set and nothing else, or colours that have EITHER Yellow and Orange set and nothing else. The first case has been answered by Stephen (use a straight 'equals'). The other case is a bit more complicated. That 11 matches using 10 is because you filtered out all the other bits in your comparison by anding them with '0', while they /are/ relevant: they aren't allowed to be '1' after all. You probably need to look at the inverted versions of these numbers to get what you need. My bit-foo is a bit rusty, Hehe. Mine too, it would seem! but this looks like what you need (I used bit-strings for my own convenience): development= select (~ '01010'::bit(5)) | '01010'::bit(5) = '1'::bit(5); ?column? -- t (1 row) development= select (~ '01011'::bit(5)) | '01010'::bit(5) = '1'::bit(5); ?column? -- f (1 row) development= select (~ '01110'::bit(5)) | '01010'::bit(5) = '1'::bit(5); ?column? -- f (1 row) development= select (~ '11010'::bit(5)) | '01010'::bit(5) = '1'::bit(5); ?column? -- f (1 row) development= select (~ '00010'::bit(5)) | '01010'::bit(5) = '1'::bit(5); ?column? -- t (1 row) development= select (~ '01000'::bit(5)) | '01010'::bit(5) = '1'::bit(5); ?column? -- t (1 row) Alban Hertroys Thanks Alban. Steve, too, has joined in again above: the bit-wise OR, together with an equality test on the stored value, would seem to be what's called for. Appreciate the contribution. I think we can all go home now!! :-) Regards HJR -- 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] Information Extract
Please don't ever post the same question to multiple lists like you've done here with this one again. The suggested practice for the postgresql.org lists is to try the most appropriate list with a question, then consider asking on another list only if you haven't gotten any responses after a day or two. Posting to multiple ones at once will annoy those of us who are subscribed to all of them, which actually makes it less likely you'll get a good answer to your question. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Finding last checkpoint time
Devrim GÜNDÜZ wrote: What about adding a column to pg_stat_bgwriter, like last_checkpoint or similar? If you look at the messages I linked to, you'll find that's one of the ideas that's been proposed and shot down. We even had a patch... -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with pg_try_advisory_lock and two connections (seemingly) getting the same lock
On Tue, Jul 20, 2010 at 10:52 PM, Craig Ringer cr...@postnewspapers.com.au wrote: On 21/07/10 07:27, Brett Hoerner wrote: Here is an example query, SELECT q.* FROM (SELECT id, job, arg FROM queue WHERE job = 'foo' OR job = 'bar' OFFSET 0) AS q WHERE pg_try_advisory_lock(1, q.id) LIMIT 10 (For information on OFFSET 0 see: http://blog.endpoint.com/2009/04/offset-0-ftw.html) Now if I have two workers running I will periodically see that each worker gets a row with the same q.id (and thus does the work). How is that possible? The outer query seemingly does a WHERE on an advisory_lock. Does anyone have any ideas? Am I grossly misusing advisory_locks? You kick off two queries at once. Both have subqueries that grab a set of id,job,arg . There's no exclusion at this stage, so they can easily both land up with some or all of the same results. THEN you filter the result. The filter will drop the result list to empty if it can't acquire the lock. Under what circumstances can it not acquire the lock? If another transaction holds it. The first transaction might have grabbe the data, acquired the lock, done its processing, and committed/rolled back to *release* the lock before the second transaction gets around to checking the lock. In this case, the second transaction will happily acquire the lock. Classic race condition. You should probably use one of the existing queuing mechanisms rather than rolling your own, because building a high-performance, reliable queueing mechanism is surprisingly hard to build. A search of the archives here will turn up several options. I've noticed that PGQ from Skytools gets mentioned a lot. I generally agree with your statements, but there is one correction to make: advisory locks are not released at end of transaction. You don't have to worry about the race as long as you make sure the record 'get' and 'lock' are done in the same operation. This is indeed quite tricky to get right, but I'm interested on academic grounds; I want to know if the issue is the lock itself or the post lock handling. OP: can we also see how the lock is released? merlin -- 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] Bitmask trickiness
Howard Rogers wrote: That's the point: you've assumed something you needn't have. You seem to have assumed that Scott was trying to be a jerk here, when he was just trying to help you out by suggesting a feature in PostgreSQL you may not have been familiar with, one that makes this particular sort of job significantly easier to do. Honestly, when I want general consulting, I pay for it. You really don't have to try and give it away for free. If you don't want general suggestions going beyond what you might have specifically asked about, I'm afraid you are on the wrong set of mailing lists. People pop up here every day asking very specific things that suggest they are in fact going about something in completely the wrong way. That makes it pretty common for one's general motive to be asked about. Also, PostgreSQL is so large that it's easy for people to not be aware of major pieces to it yet, so there's usually some questions to try and feel that out too. The attitude you should have here is if I want to be able to tell people exactly what they should and shouldn't say, I'll have to pay for that. Community PostgreSQL support is not going to shut up and assume you know what you're doing unless you prove that with your comments. P.S. This little I've been doing this for X long pissing game is going to end making everyone look like n00bs when Tom gets back. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- 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] Bitmask trickiness
On Fri, Jul 23, 2010 at 10:04 AM, Greg Smith g...@2ndquadrant.com wrote: P.S. This little I've been doing this for X long pissing game is going to end making everyone look like n00bs when Tom gets back. No pissing match on my end. I honestly feel more comfortable working with these kinds of things in binary than decimal, and that's all my comment meant. -- 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 problem
On 07/22/2010 09:02 PM, Andy Colson wrote: On 07/22/2010 04:03 PM, Gary Fu wrote: Hi, System information: - psql 8.4.4 on a client with CentOS 5.5 (64 bits) - postgres 8.4.4 on the server with CentOS 5.5 (64 bits) - the client is connected with vpn I have a script to create a table with some comments in front. When I use the command 'psql -f script.sql' to load it, it hangs. However, if I remove the comments, OR remove some of the columns from the table, it works okay. It looks like to me, the psql will hang with large size of the script file. I tried 'psql script.sql' and 'cat script.sql | psql' with the same result. However, I tried it on another client host (CentOS 5.5 32 bits), I don't see this problem. Any idea and suggestion ? Thanks, Gary Line endings? How about a sample? What comment style: -- /* (* # ; ' // -Andy Below is an example that I created. It works okay, but when I add any character in the comment or in the table definition, it fails (hangs). I checked the server process (with ps command), and I can see that connection is 'idle'. By the way, the size 1484 may not mean anything, since I tried another case (with different comment and table) and the problem still happens but the size 1484 is not the break point. I think this may be CentOS(64 bits)/ssh related, since I don't have the problem with CentOS(32 bits) and we have the same application to install the tables with the same command on mandriva. Thanks, Gary Sample file: /* =head1 NAME ProblemFiles =head1 DESCRIPTION The ProblemFiles table is used to store the file names that have problem to be handled by PollAndArchive and VerifyFiles programs. =head1 FIELDS ProblemId - The Id for the problem file FileName - The full file name with problem Reason - The reason for the file to be inserted IsDN - This FileName is a DN (DDR or DS) file DNFile - The DN file for the FileName in problem DNType - The DN type (1 for DDR, 2 for DS, 0 for Unknown) FtpPath - The ftp incoming path for the problem file, so we know where to get the file again if necessary Adhoc - None for sd3e normal subscription, SD3E for sd3e adhoc (handled as subscription) Land/Ocean/Atmosphere/Ozone/Sounder/NICSE for peates' ad-hoc CkSum - Th checksum of the file (only for file with DDR file on Reason : Missing, Duplicate, so that they can be verified again if necessary test test test tt =cut */ -- -- Name: ProblemFiles Type: Table -- create table ProblemFiles ( ProblemIdserial primary key, FileName varchar(256) not null, Reason varchar(16) not null, IsDN int not null default 0, DNFile varchar(256) null, DNType int not null default 1, InsertTime timestampnot null default now() ); -- 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] Question about SCO openserver and postgres...
On Fri, Jul 23, 2010 at 7:42 AM, Edmundo Robles L. erob...@sensacd.com.mx wrote: On 07/22/2010 05:39 PM, Scott Marlowe wrote: On Thu, Jul 22, 2010 at 3:24 PM, Edmundo Robles L. erob...@sensacd.com.mx wrote: Hi! I have a problem with the max postgres connections on SCO Openserver 5.0.7, so ...my boss decided to buy the SCO Openserver 6.0 but this version comes in 2 editions: Starter and Enterprise. If SCO 5.0.7 only allows 95 ( -3 used by superuser) connections to postgres... Do you know how many connections to postgres can i have with OpenServer in Starter Edition or Enterprise edition? Are you sure this isn't just a limit in max_connections in postgresql.conf? Yes, i sure. i have the same problem with postgres 7.2 (100 connections) and 8.3.11 (only 95 :-( ) I change the max_connections on postgres , on SCO 5.0.7 set the SHMMAX,SHM* to the maximun value and relink the SCO kernel but always , i have only 95 client connected to postgres no more. That is because we want buy SCO 6.0 but i don't know if we will have the same problem, our programs are developed on SCO so the migration to another operative system is not a choice... for now. By the way i send a mail to SCO but until now they don't answer to me. I'd be working on a schedule to get off of SCO if it was up to me. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can WAL files be shipped to multiple servers?
I am working on shipping WAL files, can WAL files do one-to-many shipping? The target PG instances are running in the recovery mode waiting for the WAL files. also, once the target PG database receives WAL files and update its own database, can I run pg_dump to dump all the data when it is in recovery mode? Thanks guys.
Re: [GENERAL] Can WAL files be shipped to multiple servers?
On Fri, Jul 23, 2010 at 18:29, Steeles stee...@gmail.com wrote: I am working on shipping WAL files, can WAL files do one-to-many shipping? The target PG instances are running in the recovery mode waiting for the WAL files. Yes. Just copy the files to multiple machines, the slaves are completely independent. also, once the target PG database receives WAL files and update its own database, can I run pg_dump to dump all the data when it is in recovery mode? No. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can WAL files be shipped to multiple servers?
Thanks for the quick reply. so if I switch target database from recovery mode to normal mode and do pg_dump to backup, then switch it back to recovery mode. Will it break the relationship between source and target? Do I need to re-configure it in order to receive the WAL files? On Fri, Jul 23, 2010 at 12:31 PM, Magnus Hagander mag...@hagander.netwrote: On Fri, Jul 23, 2010 at 18:29, Steeles stee...@gmail.com wrote: I am working on shipping WAL files, can WAL files do one-to-many shipping? The target PG instances are running in the recovery mode waiting for the WAL files. Yes. Just copy the files to multiple machines, the slaves are completely independent. also, once the target PG database receives WAL files and update its own database, can I run pg_dump to dump all the data when it is in recovery mode? No. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [GENERAL] Bitmask trickiness
the universal solution is a AND with one mask (which has a 1 in every position you wish to test for and a zero in each position you wish to ignore) and an XOR with another mask (that has a 1 in each position that you want to test for a 1 and a zero in each position that you wish to test for a 0)), then a test if the result is == zero. in a language like SQL, this could be simplified to a AND with MASK1 and a COMPARE for equality with MASK2, with the same result. -jrp (old assembler programmer) -- 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] Can WAL files be shipped to multiple servers?
On Fri, Jul 23, 2010 at 18:36, Steeles stee...@gmail.com wrote: Thanks for the quick reply. so if I switch target database from recovery mode to normal mode and do pg_dump to backup, then switch it back to recovery mode. You can't switch back. Well, you can - by setting up the replication again from scratch, meaning from a new base backup. That also means that if the master crashes while you're backing up your slave, it will be out of date. Will it break the relationship between source and target? Do I need to re-configure it in order to receive the WAL files? Yes. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can WAL files be shipped to multiple servers?
On 07/23/2010 09:31 AM, Magnus Hagander wrote: On Fri, Jul 23, 2010 at 18:29, Steelesstee...@gmail.com wrote: I am working on shipping WAL files, can WAL files do one-to-many shipping? The target PG instances are running in the recovery mode waiting for the WAL files. Yes. Just copy the files to multiple machines, the slaves are completely independent. also, once the target PG database receives WAL files and update its own database, can I run pg_dump to dump all the data when it is in recovery mode? No. Just curious does this restriction also apply to Hot Standby in 9.0? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Finding last checkpoint time
Excerpts from Greg Sabino Mullane's message of jue jul 22 13:34:25 UTC 2010: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Or you can use pg_controldata /path/to/pgdata and look at Time of latest checkpoint. Assuming your system is using English. Otherwise, you'll have to build a collection of .po strings as we did for check_postgres.pl. Needless to say, I'd greatly prefer some other way to grab the information! Hmm, wouldn't have it been easier to set LC_MESSAGES to C before calling pg_controldata? -- 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] Can WAL files be shipped to multiple servers?
Steeles wrote: also, once the target PG database receives WAL files and update its own database, can I run pg_dump to dump all the data when it is in recovery mode? Not while it's in recovery mode. If you upgrade to the soon to be released PostgreSQL 9.0, it's possible to bring the server up in Hot Standby mode, which will allow pg_dump to execute against the standby. Note that you have to be careful to tune the standby for this, as pg_dump will act like a really long running transaction and there's a risk of it being canceled if activity on the master conflicts with what it's trying to dump. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] prepared statements
Hi all,
I'm working with prepared statements directly in pg for the first time
and have a couple of questions.
Does a prepared statement used to insert into a table need to insert
into all columns of the table? I've found that, for a table with a
serial sequence key as its first column, I have to specify the key in
my prepared statement or I get type errors: ERROR: column foo_id
is of type integer but expression is of type character varying.
What's the best way to specify the next value for the serial sequence
key if subqueries are not allowed in a prepared statement's execute
parameter: ERROR: cannot use subquery in EXECUTE parameter
For example, given the following table definition:
CREATE TABLE foo (
foo_id SERIAL PRIMARY KEY,
nameVARCHAR(32) UNIQUE NOT NULL,
description TEXT,
bodyTEXTDEFAULT NULL,
created timestamp DEFAULT
CURRENT_TIMESTAMP,
UNIQUE (name));
What's the best way to insert several records that have lots of
special characters in the body column? eg:
PREPARE fooprep (int, VARCHAR(32), text, text) AS
INSERT INTO foo VALUES ($1, $2, $3, $4);
EXECUTE (fooprep
(SELECT nextval('foo_id_seq')),
'foo1',
'this is foo1',
'#!()[]{}
qwepoiasdlkjzxcmnb
/\1\2\3\4\5\6\7\8\9/');
Thanks in advance!
Scott
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Blocked inserts on tables with FK to tables for which UPDATE has been revoked
Hello,
I have encountered a problem with inserts failing because of permissions
issues when the table in which I try to insert has foreign key constraints to
tables for which UPDATE has been revoked.
The script bellow show how to reproduce the problem with a bare-bones test
case. Reproducibility is 100% on PostgreSQL 8.2.5 (I know it's not even the
latest revision of the 8.2 line, but it's what I have to work with :( ) I
Googled the error message and a couple of meaningful keywords, searched the
documentation and the mailing list archives without success.
CREATE DATABASE test WITH OWNER afsugil ENCODING 'LATIN1';
CREATE ROLE test WITH NOCREATEDB NOCREATEROLE NOCREATEUSER INHERIT
LOGIN;
\c test afsugil
CREATE TABLE station (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
INSERT INTO station (name) VALUES ('Montreal');
INSERT INTO station (name) VALUES ('Toronto');
INSERT INTO station (name) VALUES ('Calgary');
INSERT INTO station (name) VALUES ('Vancouver');
INSERT INTO station (name) VALUES ('Halifax');
SELECT * FROM station;
-- id | name
-- +---
-- 1 | Montreal
-- 2 | Toronto
-- 3 | Calgary
-- 4 | Vancouver
-- 5 | Halifax
CREATE TABLE observation (
station INTEGER NOT NULL REFERENCES station (id) MATCH FULL,
date TIMESTAMP NOT NULL,
value REAL,
PRIMARY KEY (station, date)
);
-- The insert below works
INSERT INTO observation (station, date, value) VALUES (
1, '2010-07-22 14:00:00', 42
);
REVOKE UPDATE ON station FROM afsugil;
-- The insert below no longer works
INSERT INTO observation (station, date, value) VALUES (
2, '2010-07-22 14:00:00', 14
);
-- ERROR: permission denied for relation station
-- CONTEXT: SQL statement SELECT 1 FROM ONLY public.station x WHERE id
= $1 FOR SHARE OF x
\c postgres afsudev
DROP DATABASE test;
DROP USER test;
This is a pretty severe issue for me since, I don't want users that need to
input data to also have the right to modify references tables. This is,
also, not how I would have expected the permissions to behave.
Any help to resolve this issue will be greatly appreciated!
Best Regards,
Samuel
--
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 distribute quantity if same product is in multiple rows
Tim,
Thank you.
It can be done in SQL: SUM(kogus) OVER (PARTITION BY toode
ORDER BY ID) - kogus (*1) will give you the running sum of
the product up to that row. You can then subtract that value
from the delivered quantity to calculate the delivered quan-
tity for the current row.
I tried to get list of undelivered items using script below.
Second row value (22) is incorrect (it seems to be is cumulative sum but
must be undelivered quantity for this row).
How to fix this so that every row contains correct undelivered quantity ?
Andrus.
-- Order details
CREATE TEMP TABLE tellrid (
id serial primary key,
toode char(20), -- product id
kogus numeric(12,5) ) -- ordered quantity
on commit drop;
insert into tellrid (toode,kogus) values ('PRODUCT1', 10 );
insert into tellrid (toode,kogus) values ('PRODUCT1', 20 );
-- Delivery details
CREATE TEMP TABLE rid (
id serial primary key,
toode char(20), -- product id
kogus numeric(12,5) ) -- delivered quantity
on commit drop;
insert into rid (toode,kogus) values ('PRODUCT1', 8 );
select
tellrid.id,
max(tellrid.kogus) as ordered,
GREATEST( 0,
SUM(MAX(tellrid.kogus) )
OVER (PARTITION BY MAX(tellrid.toode) ORDER BY tellrid.ID)
- COALESCE( SUM(rid.kogus),0)
) as not_delivered
from tellrid
LEFT JOIN rid USING (toode)
GROUP BY 1
Observed:
id ordered not_delivered
1 10.0 2.0
2 20.022.0
Expected:
id ordered not_delivered
1 10.0 2.0
2 20.020.0
--
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 improve performance in reporting database?
On Thu, Jul 22, 2010 at 2:31 PM, Greg Smith g...@2ndquadrant.com wrote: You can build those manually with PostgreSQL if you really want them: http://wiki.postgresql.org/wiki/Materialized_Views Another thing to consider... In our case we use a materialized view to keep track of counts of various things that are expensive to count, such as number of members of a specific account, or number of times a particular URL was viewed. What we end up with is many processes trying to update the same counter row, and we end up with lots and lots of lock contention. Which reminds me... Greg, are you done with your book yet and when can I get you back in here to help with my locking 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] Finding last checkpoint time
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Hmm, wouldn't have it been easier to set LC_MESSAGES to C before calling pg_controldata? To be honest, I can't remember why that wasn't working for me when I tried it some time ago. I just verified that it *will* work, however, when I set LANGUAGE (LC_MESSAGES has no effect). Specifically, LANGUAGE changes the headers of pg_controldata (but not the actual output, LC_ALL does that). Thanks for the nudge, I'll get to rewriting some code. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201007231456 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkxJ6IgACgkQvJuQZxSWSsi3/QCg8U48WvgzqmN1edj+axXOHQp0 mAYAoNyBBfQ6FQ0yxCRtESpT2bMXa7tT =vRSD -END PGP SIGNATURE- -- 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] Question about SCO openserver and postgres...
Edmundo Robles L. pisze: On 07/22/2010 05:39 PM, Scott Marlowe wrote: On Thu, Jul 22, 2010 at 3:24 PM, Edmundo Robles L. erob...@sensacd.com.mx wrote: Hi! I have a problem with the max postgres connections on SCO Openserver 5.0.7, so ...my boss decided to buy the SCO Openserver 6.0 but this version comes in 2 editions: Starter and Enterprise. If SCO 5.0.7 only allows 95 ( -3 used by superuser) connections to postgres... Do you know how many connections to postgres can i have with OpenServer in Starter Edition or Enterprise edition? Are you sure this isn't just a limit in max_connections in postgresql.conf? Yes, i sure. i have the same problem with postgres 7.2 (100 connections) and 8.3.11 (only 95 :-( ) I change the max_connections on postgres , on SCO 5.0.7 set the SHMMAX,SHM* to the maximun value and relink the SCO kernel but always , i have only 95 client connected to postgres no more. That is because we want buy SCO 6.0 but i don't know if we will have the same problem, our programs are developed on SCO so the migration to another operative system is not a choice... for now. By the way i send a mail to SCO but until now they don't answer to me. ... so keep your programs on SCO, but setup DB separately on a more friendly OS. As as your programs have configurable connection info, of course -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Prefix LIKE search and indexes issue.
Hello list, So, I have a small query design issue and I'd like to borrow some of your wisdom. Let's say I a users relation, and each user has a reversed_domain field. id | name | reversed_domain 1Josh com.app ... I then have a firefox plugin which makes request to my application server, sending along the current URL the user is browsing. Let's say the URL is http://mycompany.app.com/login;. The code on the app takes this data and: 1) Extracts the domain out of it; 2) Reverses the domain We then get the following string as a result: com.app.mycompany. I then want to find the user Josh, by reversed_domain. However, as you can see, the strings are different, and in most cases will be. I just want it to match the first two parts of the domain (com.app). The following query works: SELECT * FROM users WHERE 'com.app.mycompany' LIKE reversed_domain || % However, it does sequential search, meaning it doesn't use any index. What I would like to know is, how could I make it use an index? I've done some research and asked around #postgres but things are still not clear to me. Some good souls hinted me at the prefix extension, but how would I use it? Is there any other simpler / extension-free way to solve this issue? Thanks in advance, Marcelo. -- 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] prepared statements
Scott Frankel wrote: I've found that, for a table with a serial sequence key as its first column, I have to specify the key in my prepared statement or I get type errors: ERROR: column foo_id is of type integer but expression is of type character varying. Let's try: test= create table t(a serial, b int); NOTICE: CREATE TABLE will create implicit sequence t_a_seq for serial column t.a CREATE TABLE test= prepare a as insert into t(b) values($1); PREPARE test= execute a(2); INSERT 0 1 test= select * from t; a | b ---+--- 1 | 2 (1 row) No error here... Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Finding last checkpoint time
Excerpts from Greg Sabino Mullane's message of vie jul 23 19:08:27 UTC 2010: Hash: RIPEMD160 Hmm, wouldn't have it been easier to set LC_MESSAGES to C before calling pg_controldata? To be honest, I can't remember why that wasn't working for me when I tried it some time ago. I just verified that it *will* work, however, when I set LANGUAGE (LC_MESSAGES has no effect). Hmm, now that you mention it, I think I remember different systems honoring different env vars. To be safe you probably want to set the lot of them -- LANGUAGE, LANG, LC_ALL. Note that LC_MESSAGES has no effect if LC_ALL is set. I think other vars also override LC_MESSAGES in some systems. Still, this is way better than a collection of translated strings; consider a translator fixing a typo, or choosing a different wording, or a new language being added. -- 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] Can WAL files be shipped to multiple servers?
Excerpts from Steeles's message of vie jul 23 12:36:41 -0400 2010: Thanks for the quick reply. so if I switch target database from recovery mode to normal mode and do pg_dump to backup, then switch it back to recovery mode. The only way to switch back is to have a prior filesystem-level snapshot before you put it in normal mode, and restore to that snapshot after pg_dump is complete. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump, shemas, backup strategy
I've been using MySQL for years. I switched (er, mostly) to PostgreSQL recently because I need to use PostGIS. It is all working now for the most part, and PostGIS is absolutely wonderful. I run CentOS 5.x and I do not like to upgrade vendor supplied packages. My version of pg_dump is from postgresql-8.1.21-1.el5_5.1 - I'm assuming the 8.1.21 is the important part. In writing my backup cron job, I ran into a small problem. It seems that my version of pg_dump does not accept the -T option for excluding tables. There are a couple tables that never need to be included in the backup (IE php session data). Since I prefer not to upgrade pgsql at this time, I was wondering if this is where schemas might help? IE can I put those few tables into a different schema and then tell pg_dump to only dump the public schema? Schema is kind of a new concept to me. For my code, I use the php pear::MDB2 wrapper (which made moving from MySQL to PostgreSQL much easier, just had to fix some non standard SQL I had). If I move stuff out of the public schema, am I going to have tell MDB2 how to find which schema it is in? I guess that may be better suited for php list, but hopefully someone knows. Finally, the one part of my site that is NOT moved over to PostgreSQL is the site content search engine, which is sphyder. I would like to move that over as I do not see a need to run two databases if one will suffice. Sphyder also does not use a database layer or prepared statements (and I love prepared statements for security aspect), so to move it over it looks like what I should do is first port it to use MDB2 with prepared statements and then fix any sql that causes it to break in PostgreSQL. When everything was MySQL - I ran sphyder in its own database so that a bug in sphyder code could not be exploited to hack my main database. However, I'm wondering if that is an area where schema would be better. IE create a schema called sphyder and only give the sphyder user permission to select from the sphyder schema. Is that what the concept of schemas is for? Thanks for helping out a n00b. - Michael A. Peters http://www.shastaherps.org/ -- 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] JASPA (JAva SPATial) for PostgreSQL and H2 released
Is it really GPL? Any code I write that uses JASPA must also be GPL'ed? Shouldn't it be LGPL? 2010/7/24 Jose C. Martinez-Llario jomar...@cgf.upv.es (sorry for the cross posting) * text in english ** After one year of development, we are pleased to announce the release 0.1RC1 of JASPA (JAva SPAtial). JASPA potentially brings around 200 spatial functions to any relational database system that supports a full set of java store procedures. In this 0.1 version JASPA supports PostgreSQL and H2, and we are already working with HSQLDB. JASPA has a similar functionality as PostGIS 1.4, supporting spatial operators and predicates, arrays of geometries, spatial aggregates, etc. JASPA is written in java and is easy to extend using java store procedures. JASPA is licensed under the GNU GPL. This project has been possible thanks to other open source projects. Besides to take advantage of these projects we have used their mailing lists and they helped us a lot. The JASPA team is very grateful to them: - PostGIS. The mirror on which JASPA has been looking at. - JTS (Java Topology Suite). This library is widely used in JASPA and many open source initiatives bringing the possibility to use spatial analysis to the java open source world. - GeoTools (used for projections, KML support and Shape to JASPA converters) - PostgreSQL and PLJAVA (Java store procedures for PostgreSQL) - H2 and H2Spatial (a Java database and its spatial extension used at the beginning of JASPA) - HSQLDB (a Java database which probably will be supported by JASPA in the next release) - gvSIG (a desktop SIG which we hope it can connect to JASPA soon) The authors of this project are: Jose C. Martinez-Llario. Developer and project director. (1) Marta Gonzalez-Alcaide. Tester and document builder. (1) (1)Deparment of Cartographic Engineering, Geodesy and Photogrammetry at La Universidad Politecnica de Valencia (Spain). Any contributor is very welcomed to join the JASPA project. JASPA Download: - The latest software can be found at: http://forge.osor.eu/projects/jaspa/ - The full JASPA documentation can be found at: http://jaspa.forge.osor.eu/. The online manual (the pdf version has more than 300 pages) includes the installation process from binaries and source, a tutorial and a complete reference of the JASPA commands. - Mailing list: http://lists.forge.osor.eu/mailman/listinfo/jaspa-users - JASPA is hosted on OSOR.EU at: http://www.osor.eu/projects/jaspa * text in spanish ** Tras un año de desarrollo, queremos presentaros la versión 0.1RC1 de JASPA (JAva SPAtial). JASPA potencialmente implementa alrededor de 200 funciones espaciales sobre cualquier base de datos relacional que soporte procedimientos almacenados en java. En esta primera versión JASPA soporta PostgreSQL y H2. Actualmente estamos trabajando para incorporar HSQLDB. JASPA tiene una funcionalidad muy similar a PostGIS 1.4, soportando predicados y operadores espaciales, arreglos de geometrías, agregados espaciales, etc. JASPA está programado en java y es bastante fácil de extender ampliando funcionalidades utilizando procedimientos almacenados en java. JASPA tiene una licencia GNU GPL. Este proyecto ha sido posible gracias a otros muchos proyectos de código abierto. Además de utilizar estos proyectos, sus correspondientes listas de correo nos han ayudado de forma rápida y eficaz. El equipo de JASPA quiere mostrar su gratitud especialmente a: - PostGIS. Es el espejo en el que JASPA se ha mirado. - JTS (Java Topology Suite). Biblioteca ampliamente utilizada en JASPA y muchos otros proyectos de código abierto, ofreciendo la posibilidad de realizar análisis espaciales. - GeoTools (biblioteca utilizada para las proyecciones y el soporte de KML y los importadores shape). - PostgreSQL and PLJAVA (implementa procedimientos almacenados en java para PostgreSQL) - H2 y H2Spatial (H2 es una base de datos especial desarrollada en Java, al inicio de JASPA H2 Spatial y Spatial Box ofrecieron ideas en la que se basó JASPA) - HSQLDB (base de datos java que posiblemente será soportada por JASPA en la próxima versión) - gvSIG (SIG de escritorio con el que realmente deseamos que JASPA se pueda conectar) Los autores de este proyecto son: Jose C. Martinez-Llario. Developer and project director. (1) Marta Gonzalez-Alcaide. Tester and document builder. (1) (1)Deparment of Cartographic Engineering, Geodesy and Photogrammetry at La Universidad Politecnica de Valencia (Spain). Cualquier persona que quiera contribuir es bienvenida. JASPA Download: - Los binarios y el código fuente se puede encontrar en: http://forge.osor.eu/projects/jaspa/ - La documentación complete de JASPA en: http://jaspa.forge.osor.eu/. El manual en línea (la versión pdf tiene más de 300 páginas) incluye el proceso de instalación de forma detallada de los
Re: [GENERAL] JASPA (JAva SPATial) for PostgreSQL and H2 released
On 07/23/10 3:48 PM, Kerry Sainsbury wrote: Is it really GPL? Any code I write that uses JASPA must also be GPL'ed? Shouldn't it be LGPL? IANAL, but if this is PL/Java based, then your code shouldn't need to be GPL as you're not linking with it, you're just 'using' it. Now, if you make any changes to the pl/java code, those changes would be GPL -- 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] prepared statements
Works! The bug in my example was not passing the INSERT statement an explicit list of column names, as per any non-prepared insert. Thanks! Scott On Jul 23, 2010, at 2:53 PM, Daniel Verite wrote: Scott Frankel wrote: I've found that, for a table with a serial sequence key as its first column, I have to specify the key in my prepared statement or I get type errors: ERROR: column foo_id is of type integer but expression is of type character varying. Let's try: test= create table t(a serial, b int); NOTICE: CREATE TABLE will create implicit sequence t_a_seq for serial column t.a CREATE TABLE test= prepare a as insert into t(b) values($1); PREPARE test= execute a(2); INSERT 0 1 test= select * from t; a | b ---+--- 1 | 2 (1 row) No error here... Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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
