Re: [GENERAL] date_trunct() and start of week
Rikard Bosnjakovic, 27.11.2009 08:49: [...] I'm just curious which setting defines whether monday or sunday is considered the "first day in a week" Read 9.9.2 on http://www.postgresql.org/docs/8.1/static/functions-datetime.html and you will see that even if you find such setting, date_trunc() will always return monday as start of week: =[snip] source is a value expression of type timestamp or interval. (Values of type date and time are cast automatically, to timestamp or interval respectively.) field selects to which precision to truncate the input value. The return value is of type timestamp or interval with all fields that are less significant than the selected one set to zero (or one, for day and month). =[snip] Atleast that's how I interpret the last parenthesis in the paragraph. Hmm, I don't see that in there. It just states that the field will be set to "zero". But does zero refer to a Monday or a Sunday? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] return value for PQbinaryTuples
hi all, I don't why PQbinaryTuples function returns 1 even the select statement only returns two integer fields. Although there are some columns with type bytea in the table. Are there any documents describe this? -- Regards Sam -- Regards Sam
Re: [GENERAL] date_trunct() and start of week
Hi,
not all to zero : "that are less significant than the selected one set
to zero (or one, for day and month)"
so
select extract('dow' from date_trunc('week', current_date))
returns always 1 (i think accordingly to ISO-8601)
see
http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
dow:
"The day of the week (0 - 6; Sunday is 0)"
regards
Thomas
Thomas Kellerer schrieb:
>
> Hmm, I don't see that in there.
> It just states that the field will be set to "zero". But does zero
> refer to a Monday or a Sunday?
> Regards
> Thomas
>
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is there any reason why databases cannot have a binary formatted datatype?
Can't we have a data type called say "image" that is just a representation of the bytes and nothing else? It seems to me that bytea is a hangover from the old days. Is there some underlying physical reason why postgresql and other databases cannot handle binary data without going through all this silly escape stuff which must have a massive impact on performance. Or is it just because databases originally were built to handle just test data? Let's have a good discussion on this and perhaps an outcome for easier handling of binary data. I have a reason for this. I want to be able to easily store maps and programs that are transmitted to devices in the field. Regards Andrew -- ___ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___ -- 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] date_trunct() and start of week
Thomas Markus, 27.11.2009 09:41: Hi, not all to zero : "that are less significant than the selected one set to zero (or one, for day and month)" Sorry, I missed the "or one" part. see http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT dow: "The day of the week (0 - 6; Sunday is 0)" So essentially it *is* always returning Monday independently of any setting. Thanks for your help Regards Thomas -- 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] Is there any reason why databases cannot have a binary formatted datatype?
Andrew Maclean wrote: Can't we have a data type called say "image" that is just a representation of the bytes and nothing else? It seems to me that bytea is a hangover from the old days um, thats what BYTEA is. Is there some underlying physical reason why postgresql and other databases cannot handle binary data without going through all this silly escape stuff which must have a massive impact on performance. Or is it just because databases originally were built to handle just test data? thats all in the API's you use to access the database. you can pass in binary data directly without escaping if your API bindings support parameterized calls, like pqExecParams() -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Storing images in database for web applications
Hi all, I'm wondering if anyone has experience of storing and getting images to and from a database? We currently have the problem of images being uploaded to a single gateway used by many companies, most of which run several websites. As it stands, once they upload the image, it then has to be fsync'd to the appropriate servers (3-way in some cases) or accessed through our image proxy. But now we wish to redesign our various image upload systems into one system which will also make images available across various sites/companies and therefore servers. So one solution is to store images in a database. What I'm wondering is if this is an appropriate solution? What are the downsides? Could we expect severe performance hits? To help mitigate the issue of many sites continuously querying a database for images, we'd implement a caching system, so images would only be queried from the database probably around once an hour. The benefits I can see is that images are managed in one place and accessibly easily by all servers. The problem is putting everything in one place (all eggs in one basket), so if the server goes down, all sites lose their images once their cache has expired... unless we implemented a system that falls back to cache if connection fails, even if cache has expired. Any suggestion? Alternatives? Thanks Thom
Re: [GENERAL] date_trunct() and start of week
On 27 Nov 2009, at 8:49, Rikard Bosnjakovic wrote: > Read 9.9.2 on > http://www.postgresql.org/docs/8.1/static/functions-datetime.html > and you will see that even if you find such setting, date_trunc() will > always return monday as start of week: > > =[snip] > source is a value expression of type timestamp or interval. (Values of > type date and time are cast automatically, to timestamp or interval > respectively.) field selects to which precision to truncate the input > value. The return value is of type timestamp or interval with all > fields that are less significant than the selected one set to zero (or > one, for day and month). > =[snip] > Atleast that's how I interpret the last parenthesis in the paragraph. That seems an unlikely interpretation to me. I'm pretty sure 'day' in that text is meant as 'day of month', not as 'day of week'. That aside, if fields are getting set to zero (or one for day and month) it would be a bad idea to set day of week to zero or one as well, as it's value should be derived from day, month and year (unless for example dow and week were specified and day of month was not). The documentation doesn't explicitly say what a week would truncate to, but earlier in the documentation for extract() it explains it uses ISO-8601 when extracting weeks. It says there: "By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISO-8601 week starts on Monday.)" ^^ It seems safe to assume date_trunc() uses the same ISO standard when truncating dates. 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,4b0fb5a211731686815181! -- 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] Is there any reason why databases cannot have a binary formatted datatype?
Andrew Maclean wrote: > Is there some underlying physical reason why postgresql and other > databases cannot handle binary data without going through all this > silly escape stuff which must have a massive impact on performance. Or > is it just because databases originally were built to handle just test > data? What John said, but just a small note. The text representation of the various datatypes are fairly static. The binary representations can change - even between two databases with the same version number (floating-point vs big-int timestamps). For a meaningless (to the db) "blob" type that doesn't matter, but you will need to be aware of the issues and have appropriate tests in place for your application. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Bestpractice for upgrading from enterpriseDB 8.3.3 to rpm 8.4.1.
Chris Barnes wrote: > Is there anyone that has installed enterpriseDB (833) and upgraded to > later version or 8.4.1 using rpms? > > I am wondering what the best path would be to upgrade from > enterpriseDB. I don't know the precise changes between the E-DB rpms and the community ones, but the procedure for both should be the same. > Can I do an upgrade from enterpriceDB 8.3.3 to rpms 8.3.8 without > dumping and restoring the database? Should be fine. Worth checking the release-notes, but the developers only change on-disk formats if there is no alternative. It should just be a matter of stop-db, upgrade rpm, restart db. http://www.postgresql.org/docs/8.3/static/release.html Of course - make sure you have (1) A good backup and (2) copies of the original RPMs somewhere. Doesn't hurt to be paranoid. > When going from enterpricedb 8.3.3 to 8.4.1 using rpms, can I have > them simultaneously run in parrellel and export/import and remove > enterprisedb 8.3.3 later, would there be any issues? I'm guessing I > would have to install 8.4.1, configure for port (5433) and run them > in parellel to accomplish this? Debian's packaging system lets you do this. I don't know if you can with RPMs - it might be that both packages try to save postgresql.conf in the same place for example. If it's not possible, you could always compile your own version of 8.4 (it's simple enough and will sit in /usr/local/ out of the way). Then, once you've transferred the data, replace the RPMs and restore from a native 8.4 dump. HTH -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Storing images in database for web applications
On 27/11/2009 7:04 PM, Thom Brown wrote: > But now we wish to redesign our various image upload systems into one > system which will also make images available across various > sites/companies and therefore servers. So one solution is to store > images in a database. What I'm wondering is if this is an appropriate > solution? What are the downsides? Could we expect severe performance > hits? It depends a bit on your needs. If you need facilities provided by the database, such as transactional updates of images and associated metadata, then it's probably a good choice. Otherwise, you'll be paying the costs associated with storing blobs in a database without gaining much. There *are* costs to using a database over a network file system - mostly increased management. It's harder to do incremental backups and harder to work with those incremental backups (see PITR and continuous archiving) than it is with a regular file system. Storage requirements may be slightly increased. Deletion of images requires additional cleanup work (VACUUM) to free the space. Etc. With a database, you can't use the sendfile(...) system call or the like to transmit the data, so there's more work for the system when transmitting query results. It's also not generally transmitted as raw binary, but hex- or octal- encoded, which can be ugly to work with. You can request it as pure binary instead if you're using libpq directly, though. Why don't you use a HTTP-based image server? It's pretty low overhead, is easily cached by commonly available tools, etc. Failing that, there are plenty of network file systems out there (NFSv4 and CIFS come to mind) that would do the job. > To help mitigate the issue of many sites continuously querying a > database for images, we'd implement a caching system, so images would > only be queried from the database probably around once an hour. Memcached ? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Storing images in database for web applications
Thom, > I'm wondering if anyone has experience of storing and getting images to and > from a database? Yes. For a customer I have one application running for ~8 years which stores images (and various other BLOBS) within a PostgreSQL database. Started with 7.something, now running on 8.3; allways used BYTEA. It is a intranet and extranet application, so it does not get slashdotted or reddited. Another application is a public website, also storing HTML(fragments) and images within a PostgreSQL database. > The benefits I can see is that images are managed in one place and > accessibly easily by all servers. The problem is putting everything in one > place (all eggs in one basket), so if the server goes down, all sites lose > their images once their cache has expired... unless we implemented a system > that falls back to cache if connection fails, even if cache has expired. Your analyzes is correct. Other benefits are: - no filesystem fuss (rights, sharing, locking, names (windows / unix) - options for security: in my application, editing happens within a strongly firewalled VPN. The webserver in the big, bad internet only accesses the database; so the attack vectors are limited. - transactional save: no problems with "partially saved images", "images locked by whatever" - mime information etc. can be stored alongside the bytes - reporting options drawbacks are: - scaling is more challenging: static content from filesystems can be replicated ( as you do), replicating a database is more difficult - different performance characteristics then filesystem (search usually faster (index), but access to image contents has more layers to the disc) - backups of the database grow huge, and usually the dump of a file in the database is bigger then the file itself. (usually, because the effects of compression are hard to judge before) - dump / restore times grow Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - %s is too gigantic of an industry to bend to the whims of reality -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_standby instructions
When I execute the command cmd_archiver -I I am getting the following response Traceback (most recent call last): File "/export/home/postgres/8.4/pitrtools/cmd_archiver", line 56, in ? config.read(configfile) File "/usr/lib/python2.4/ConfigParser.py", line 262, in read for filename in filenames: TypeError: iteration over non-sequence I am not able to interpret any thing from the above message. Can you please give me some thoughts Regards On Wed, Nov 25, 2009 at 5:37 PM, Joshua D. Drake wrote: > On Wed, 2009-11-25 at 17:24 -0500, akp geek wrote: > > I have been trying to use the PITR tools , I am running into the > > following issue. I don't know how to resolve it. can you please help? > > I tried to search the online blogs, I did not find much for the error. > > > OSError: [Errno 2] No such file or directory: > > '/opt/postgres/archive/10.100.101.150' > > LOG: archive command failed with exit code 1 > > DETAIL: The failed archive command was: cmd_archiver > > -C /export/home/postgres/8.4/pitrtools/cmd_archiver.ini -F > > pg_xlog/00030001007D > > Did you run cmd_archiver -I ? > > Joshua D. Drake > > > > > -- > PostgreSQL.org Major Contributor > Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 > Consulting, Training, Support, Custom Development, Engineering > If the world pushes look it in the eye and GRR. Then push back harder. - > Salamander > >
Re: [GENERAL] pg_standby instructions
On Fri, Nov 27, 2009 at 14:19, akp geek wrote: [...] > TypeError: iteration over non-sequence > I am not able to interpret any thing from the above message. Can you please > give me some thoughts I can't say anything about the application itself, but the cryptic message means that the variable "filenames" is expected to be a sequenced typed variable. A sequence in Python is a string, a list or a tuple. To get the above error message requires the variable to be something else, like an integer or something. One possible cause for the problem is a configuration error. Perhaps a line contains a non-string, as in foo = bar, instead of foo = "bar". But again, I don't know about the application so I cannot give any better guess, but the above is what the error is about. -- - Rikard - http://bos.hack.org/cv/ -- 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] Invalid redo in checkpoint record
From: "Craig Ringer" Before you do re-create the cluster, if the data is unimportant is there any chance you could take a copy of it so it can be examined to see what happened? PostgreSQL should recover cleanly after a hard crash, and unless there's a storage subsystem issue or fsync was off this sort of thing might indicate an issue with Pg's crash recovery. Having a copy of the database would be really handy. The whole data directory would need to be tar'ed up and gzip'd. I would have gladly but unfortunately it's too late now! Unless you know of any backup files or logs which may have survived the drop-create process? If it ever happens again in the future I'll be sure to retain it. Shak -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date_trunct() and start of week
Thomas Kellerer writes: > I'm just curious which setting defines whether monday or sunday is considered > the "first day in a week" A look at the source code (timestamptz_trunc) shows that truncation to week start follows the ISO week conventions --- so weeks start on Monday, regardless of locale. Offhand I do not think that we pay attention to locale for any datetime calculations. However, there are other places that use Sunday for week start, so it does matter which calculation you ask for ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] READ ONLY & I/O ERROR
On Fri, Nov 27, 2009 at 4:53 AM, Sam Jas wrote: > > I will check that one. Also i have read one forum which tells that whenever > you face disk i/o run "dmesg" command it will give you detail information. > Today again i face disk i/o and i have run "dmesg" it has given me below o/p. > Can somebody help me to explain what is it telling ? > sd 0:0:3:0: SCSI error: return code = 0x0004 > end_request: I/O error, dev sdd, sector 16 > Buffer I/O error on device sdd, logical block 2 > Buffer I/O error on device sdd, logical block 3 > sd 0:0:3:0: SCSI error: return code = 0x0004 > end_request: I/O error, dev sdd, sector 0 Looks like you've got a bad drive. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] empty string causes planner to avoid index. Makes me sad.
PostgreSQL 8.2.12 on i386-pc-solaris2.10, compiled by GCC gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath) CREATE TABLE items ( field1 character(9) NOT NULL, field2 character varying(17) NOT NULL }; CREATE INDEX "field1-field2" ON items USING btree (field1, field2); About 15 million rows in the items table. explain select count(*) from items where field1 = '102100400' and field2 = ''; Aggregate (cost=231884.57..231884.57 rows=1 width=0) -> Bitmap Heap Scan on items (cost=4286.53..231841.95 rows=170468 width=0) Recheck Cond: ((field1 = '102100400'::bpchar) AND ((field2)::text = ''::text)) -> Bitmap Index Scan on "field1-field2-check" (cost=0.00..4282.27 rows=170468 width=0) Index Cond: ((field1 = '102100400'::bpchar) AND ((field2)::text = ''::text)) explain select count(*) from items where field1 = '102100400' and field2 = ' '; /*17 spaces*/ Aggregate (cost=34.83..34.83 rows=1 width=0) -> Index Scan using "field1-field2" on items (cost=0.00..34.82 rows=18 width=0) Index Cond: ((field1 = '102100400'::bpchar) AND ((field2)::text = ' '::text)) If I have any value in field2 other than an empty string '' (like '1' or 'space'), it will use the index. It appears that somehow the empty string is causing the planner to abandon the index. Can I get any insights into this? -- 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] empty string causes planner to avoid index. Makes me sad.
Jeff Amiel writes: > It appears that somehow the empty string is causing the planner to abandon > the index. You didn't actually show us such a case... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] empty string causes planner to avoid index. Makes me sad.
hmm...ok...planner is not using the index effectively (as effectively as when a non-empty value is passed in) --- On Fri, 11/27/09, Tom Lane wrote: > From: Tom Lane > Subject: Re: [GENERAL] empty string causes planner to avoid index. Makes me > sad. > To: "Jeff Amiel" > Cc: pgsql-general@postgresql.org > Date: Friday, November 27, 2009, 3:14 PM > Jeff Amiel > writes: > > It appears that somehow the empty string is causing > the planner to abandon the index. > > You didn't actually show us such a case... > > > regards, tom lane > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cannot allocate memory for output buffer
I am looking for some help regarding an python OperationalError that I recently received while executing a python script using sqlalchemy and psycopg2. The python script parses an xml file stored on a networked drive and enters the information into a pgsql database. Sometimes these xml files reference a binary file which is also located on the networked drive. These files are subsequently read in and stored in a table along with the file's md5. The binary data is stored within a bytea column. This script worked pretty well until recently when it came across a binary file about 258MB in size. While reading the file off the networked drive I received an OperationalError indicating that it was unable to allocate memory for the output buffer. My initial guess was that it ran out of memory, but according to the task manager the machine had close to 2GB free when the error occurred. I'm not 100% sure that this is a pgsql problem, but all Google searches show the exact error message within libpq's source code which is why I'm starting here. The machine that runs the script is a Windows XP machine running Python 2.6, PsycoPG2 2.0.10, and Postgresql 8.4. The database is running pgsql 8.4 on a FreeBSD 8.0 box with approximately 16GB or memory. I've been searching on Google and the mailing list archives for the past couple days trying to find an answer with no success. Any help is greatly appreciated. Thanks in advance. -- Pete Erickson redlamb _at_ redlamb _dot_ net -- 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] empty string causes planner to avoid index. Makes me sad.
Jeff Amiel writes: > hmm...ok...planner is not using the index effectively (as effectively as when > a non-empty value is passed in) You didn't show us any evidence of that, either. Both of your test cases are using the index. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot allocate memory for output buffer
Pete Erickson writes: > I am looking for some help regarding an python OperationalError that I > recently received while executing a python script using sqlalchemy and > psycopg2. The python script parses an xml file stored on a networked > drive and enters the information into a pgsql database. Sometimes > these xml files reference a binary file which is also located on the > networked drive. These files are subsequently read in and stored in a > table along with the file's md5. The binary data is stored within a > bytea column. This script worked pretty well until recently when it > came across a binary file about 258MB in size. While reading the file > off the networked drive I received an OperationalError indicating that > it was unable to allocate memory for the output buffer. My initial > guess was that it ran out of memory, but according to the task manager > the machine had close to 2GB free when the error occurred. Out of memory is probably exactly right. The textual representation of arbitrary bytea data is normally several times the size of the raw bits (worst case is 5x bigger, typical case perhaps half that). In addition to that you have to consider that there are likely to be several copies of the string floating around in your process' memory space. If you're doing this in a 32bit environment it doesn't surprise me at all that 258MB of raw data would exhaust available memory. Going to a 64bit implementation would help some, but I'm not sure that that's an available option for you on Windows, and anyway it doesn't eliminate the problem completely. If you want to process really large binary files you're going to need to divide them into segments. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] vacuumdb -z do a reindex?
I thought 'vacuumdb -z dbname' also reindex is this true? I've had a simple update running for over 4 hours now (see results from pg_top below). The sql is: The database has 1016789 records, vacuumdb -z is ran once a day. I have not ran 'reindexdb' in weeks. The system is a: 2xIntel 4-core Xeon Model E5430 (Harpertown) 2.66GHz, 32GB RAM and 8x145GB SAS drives configured with software RAID10 Your comments are appreciated. --irene last pid: 1185; load avg: 2.17, 2.21, 1.60; up 38+01:36:40 13:52:27 14 processes: 2 running, 12 sleeping CPU states: 14.0% user, 0.0% nice, 10.5% system, 75.4% idle, 0.0% iowait Memory: 11G used, 20G free, 456M buffers, 8724M cached Swap: PID USERNAME PRI NICE SIZE RES STATE TIME WCPUCPU COMMAND 28508 postgres 170 93M 38M run 265:53 58.42% 99.08% postgres: postgres metadata 140.252.26.34(34717) UPDATE 31609 postgres 160 91M 36M run 7:05 57.85% 98.09% postgres: system_admin metadata 140.252.26.34(43303) SELECT 25156 postgres 160 102M 46M sleep 7:28 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(40350) idle 25363 postgres 180 93M 37M sleep 5:08 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(35951) idle 31622 postgres 150 95M 38M sleep 1:45 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(51917) idle 31624 postgres 150 95M 38M sleep 0:14 0.00% 0.00% postgres: system_admin metadata 140.252.6.51(53908) idle 28755 postgres 150 91M 10M sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41270) idle 28757 postgres 150 91M 10M sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41272) idle 28756 postgres 150 91M 10M sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41271) idle 28758 postgres 150 91M 10M sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41273) idle 28754 postgres 150 92M 9724K sleep 0:02 0.00% 0.00% postgres: postgres keyword 140.252.26.33(41269) idle 25180 postgres 150 91M 7016K sleep 0:00 0.00% 0.00% postgres: postgres metadata 140.252.6.51(33997) idle 25179 postgres 150 91M 6956K sleep 0:00 0.00% 0.00% postgres: postgres metadata 140.252.6.51(47331) idle 1186 postgres 160 90M 4808K sleep 0:00 0.00% 0.00% postgres: arcsoft metadata [local] idle [arcs...@archdbn1 ~]$ date Fri Nov 27 13:53:28 MST 2009 -- - Irene BargEmail: ib...@noao.edu NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg 950 N. Cherry Ave.Voice: 520-318-8273 Tucson, AZ 85726 USA FAX: 520-318-8360 - -- 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] vacuumdb -z do a reindex?
Le vendredi 27 novembre 2009 à 22:17:50, Irene Barg a écrit : > I thought 'vacuumdb -z dbname' also reindex is this true? > No. vacuumdb -z is a VACUUM ANALYZE. Moreover, vacuumdb has no option to do a REINDEX. -- Guillaume. http://www.postgresqlfr.org http://dalibo.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] empty string causes planner to avoid index. Makes me sad.
--- On Fri, 11/27/09, Tom Lane wrote: > You didn't show us any evidence of that, either. Both > of your test > cases are using the index. Ok...third try. The cost when passing in an empty string is SIGNIFICANTLY higher than when not. Wouldn't seem that the planner is using the index effectively. Aggregate (cost=231884.57..231884.57 rows=1 width=0) versus Aggregate (cost=34.83..34.83 rows=1 width=0) By -- 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] empty string causes planner to avoid index. Makes me sad.
Le vendredi 27 novembre 2009 à 23:32:14, Jeff Amiel a écrit : > --- On Fri, 11/27/09, Tom Lane wrote: > > You didn't show us any evidence of that, either. Both > > of your test > > cases are using the index. > > Ok...third try. The cost when passing in an empty string is SIGNIFICANTLY > higher than when not. Wouldn't seem that the planner is using the index > effectively. > > Aggregate (cost=231884.57..231884.57 rows=1 width=0) > > versus > > Aggregate (cost=34.83..34.83 rows=1 width=0) > But in the first example (the empty string one), it fetched 170468 rows from the index, and in the second one (the 17-spaces string), it fetched only 18 rows. It seems quite normal that the first one is costier then the second one. -- Guillaume. http://www.postgresqlfr.org http://dalibo.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] Cannot allocate memory for output buffer
Thanks. Out of curiosity, if memory exhaustion was the problem, any idea why the task manager would show that I'm only using 1.2GB of the 3GB of memory? On 11/27/2009 5:15 PM, Tom Lane wrote: Pete Erickson writes: I am looking for some help regarding an python OperationalError that I recently received while executing a python script using sqlalchemy and psycopg2. The python script parses an xml file stored on a networked drive and enters the information into a pgsql database. Sometimes these xml files reference a binary file which is also located on the networked drive. These files are subsequently read in and stored in a table along with the file's md5. The binary data is stored within a bytea column. This script worked pretty well until recently when it came across a binary file about 258MB in size. While reading the file off the networked drive I received an OperationalError indicating that it was unable to allocate memory for the output buffer. My initial guess was that it ran out of memory, but according to the task manager the machine had close to 2GB free when the error occurred. Out of memory is probably exactly right. The textual representation of arbitrary bytea data is normally several times the size of the raw bits (worst case is 5x bigger, typical case perhaps half that). In addition to that you have to consider that there are likely to be several copies of the string floating around in your process' memory space. If you're doing this in a 32bit environment it doesn't surprise me at all that 258MB of raw data would exhaust available memory. Going to a 64bit implementation would help some, but I'm not sure that that's an available option for you on Windows, and anyway it doesn't eliminate the problem completely. If you want to process really large binary files you're going to need to divide them into segments. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] vacuumdb -z do a reindex?
On Fri, Nov 27, 2009 at 2:17 PM, Irene Barg wrote: > I've had a simple update running for over 4 hours now (see results from > pg_top below). The sql is: Have you looked in pg_locks and pg_stat_activity? > The database has 1016789 records, vacuumdb -z is ran once a day. I have not > ran 'reindexdb' in weeks. The system is a: > > 2xIntel 4-core Xeon Model E5430 (Harpertown) 2.66GHz, 32GB RAM and 8x145GB > SAS drives configured with software RAID10 So do you have autovacuum disabled? What pg version are you running? an 8 drive RAID array is usually pretty fast, unless it's on a bad RAID controller or something. What do "vmstat 10" and "iostat -x 10" say about your io activity? > > Your comments are appreciated. > --irene > >>> last pid: 1185; load avg: 2.17, 2.21, 1.60; up 38+01:36:40 >>> 13:52:27 >>> 14 processes: 2 running, 12 sleeping >>> CPU states: 14.0% user, 0.0% nice, 10.5% system, 75.4% idle, 0.0% >>> iowait >>> Memory: 11G used, 20G free, 456M buffers, 8724M cached >>> Swap: >>> PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND >>> 28508 postgres 17 0 93M 38M run 265:53 58.42% 99.08% postgres: >>> postgres metadata 140.252.26.34(34717) UPDATE >>> 31609 postgres 16 0 91M 36M run 7:05 57.85% 98.09% postgres: >>> system_admin metadata 140.252.26.34(43303) SELECT >>> 25156 postgres 16 0 102M 46M sleep 7:28 0.00% 0.00% postgres: >>> system_admin metadata 140.252.6.51(40350) idle >>> 25363 postgres 18 0 93M 37M sleep 5:08 0.00% 0.00% postgres: >>> system_admin metadata 140.252.6.51(35951) idle >>> 31622 postgres 15 0 95M 38M sleep 1:45 0.00% 0.00% postgres: >>> system_admin metadata 140.252.6.51(51917) idle >>> 31624 postgres 15 0 95M 38M sleep 0:14 0.00% 0.00% postgres: >>> system_admin metadata 140.252.6.51(53908) idle >>> 28755 postgres 15 0 91M 10M sleep 0:02 0.00% 0.00% postgres: >>> postgres keyword 140.252.26.33(41270) idle >>> 28757 postgres 15 0 91M 10M sleep 0:02 0.00% 0.00% postgres: >>> postgres keyword 140.252.26.33(41272) idle >>> 28756 postgres 15 0 91M 10M sleep 0:02 0.00% 0.00% postgres: >>> postgres keyword 140.252.26.33(41271) idle >>> 28758 postgres 15 0 91M 10M sleep 0:02 0.00% 0.00% postgres: >>> postgres keyword 140.252.26.33(41273) idle >>> 28754 postgres 15 0 92M 9724K sleep 0:02 0.00% 0.00% postgres: >>> postgres keyword 140.252.26.33(41269) idle >>> 25180 postgres 15 0 91M 7016K sleep 0:00 0.00% 0.00% postgres: >>> postgres metadata 140.252.6.51(33997) idle >>> 25179 postgres 15 0 91M 6956K sleep 0:00 0.00% 0.00% postgres: >>> postgres metadata 140.252.6.51(47331) idle >>> 1186 postgres 16 0 90M 4808K sleep 0:00 0.00% 0.00% postgres: >>> arcsoft metadata [local] idle [arcs...@archdbn1 ~]$ date >>> Fri Nov 27 13:53:28 MST 2009 > > -- > - > Irene Barg Email: ib...@noao.edu > NOAO/AURA Inc. http://www.noao.edu/noao/staff/ibarg > 950 N. Cherry Ave. Voice: 520-318-8273 > Tucson, AZ 85726 USA FAX: 520-318-8360 > - > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- When fascism comes to America, it will be intolerance sold as diversity. -- 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] empty string causes planner to avoid index. Makes me sad.
On Fri, 27 Nov 2009, Jeff Amiel wrote: > --- On Fri, 11/27/09, Tom Lane wrote: > > > You didn't show us any evidence of that, either.? Both > > of your test > > cases are using the index. > > Ok...third try. The cost when passing in an empty string is > SIGNIFICANTLY higher than when not. Wouldn't seem that the planner is > using the index effectively. But it's also estimating that it's aggregating over around 1 times as many rows presumably because it thinks empty string is alot more common. That might not be the case in the actual data, but the estimated difference is the likely cause of the plan differences. What are the actual runtimes and rowcounts for the queries with different values you're trying? Explain analyze output would be useful for that. -- 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] empty string causes planner to avoid index. Makes me sad.
Jeff Amiel writes: > Ok...third try. The cost when passing in an empty string is SIGNIFICANTLY > higher than when not. That just reflects the fact that it's expecting a lot more rows matching that query. I suppose this is because the statistics show you've got a lot more rows containing the empty string than other values. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot allocate memory for output buffer
Peter Erickson writes: > Thanks. Out of curiosity, if memory exhaustion was the problem, any idea > why the task manager would show that I'm only using 1.2GB of the 3GB of > memory? Well, it would've failed to allocate the next copy of the string that it needed ... and I think also we try to keep the output buffer size at a power of 2, so it might have been asking for 2GB for the output buffer. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Access a Field / Column of a resultset by Number
thats exactly the same i'm looking for: http://wiki.postgresql.org/wiki/Todo http://archives.postgresql.org/pgsql-patches/2005-07/msg00458.php (todo list for plpgsql) *Server-Side Languages *PL/pgSQL * *[D] Allow listing of record column names, and access to record columns *via variables, e.g. columns := r.(*), tval2 := r.(colname) * *Re: PL/PGSQL: Dynamic Record Introspection is that is possible in any pl* language? Daniel Schuchardt schrieb: hy group, i currently look for a solution to access a resultset in a db-stored function by number. in plpgsql thats not possible. so i checked out plpython. so far so good, thats working: CREATE OR REPLACE FUNCTION dokv_dorecnokeywords(sqlstatement VARCHAR) RETURNS VOID AS $$ rv = plpy.execute(sqlstatement, 1) |->"SELECT * FROM art WHERE ak_nr='TEST'" s = 'ak_bez' fieldname is static here, should be by number plpy.notice(s+'='+rv[0][s]) return $$ LANGUAGE plpythonu; now i need a solution to get the number of fields as well as the fieldnames of that resultset and run through all fields (by number or by name). (i need the fieldname too) -- Daniel Schuchardt /Softwareentwicklung/ /http://www.prodat-sql.de/ -- 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] Mysql migration to Postgresql
As said by Filip Rembiałkowski-3, you could try using some software that will help you in managing the transfer. I can recommend the use of data integration software such as Datastage (It is a licensed program) or Talend Open Studio (Talend is open source). Tell us how it went. mrciken wrote: > > Hello, > > Currently, we are using Mysql: we would like to change. Now, we would like > to use Postgresql. Only problem is that most of our customer addresses and > other information are on Mysql. > Can you help us with this? > > Thank you. > > -- View this message in context: http://old.nabble.com/Mysql-migration-to-Postgresql-tp26513373p26544102.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Access a Field / Column of a resultset by Number
hy group, i currently look for a solution to access a resultset in a db-stored function by number. in plpgsql thats not possible. so i checked out plpython. so far so good, thats working: CREATE OR REPLACE FUNCTION dokv_dorecnokeywords(sqlstatement VARCHAR) RETURNS VOID AS $$ rv = plpy.execute(sqlstatement, 1) |->"SELECT * FROM art WHERE ak_nr='TEST'" s = 'ak_bez' fieldname is static here, should be by number plpy.notice(s+'='+rv[0][s]) return $$ LANGUAGE plpythonu; now i need a solution to get the number of fields as well as the fieldnames of that resultset and run through all fields (by number or by name). (i need the fieldname too) -- Daniel Schuchardt /Softwareentwicklung/ /http://www.prodat-sql.de/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] return value for PQbinaryTuples
hi all, I don't why PQbinaryTuples function returns 1 even the select statement only returns two integer fields. Although there are some columns with type bytea in the table. Are there any documents describe this? -- Regards Sam
[GENERAL] incorrect restore from pg_dumpall
Hello, I am trying to restore my databases stored by a pg_dumpall command in the Karmic Koala box. The restore command is the following: psql -f /media/disk/.../backup -U postgres I have a PostsgreSQL 8.4 installed from repositories with postgis1.4.1. I recognized that the tables with spatial geometries were not restored but at the moment I am almost sure that it's caused by that the older version of postgis in the stored database. What is a kind of mystery for me that data in several tables were not restored, although the table definitions did (it means that I got several tables without data) althoug the data are there in the backup file (I checked physically). I really don't understand what's going on there, I did the same many times before, without any problem. Tomas -- 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] incorrect restore from pg_dumpall
In order to restore a backup taken with pg_dumpall you'll want to ensure that the postgis installed in your new system is identical to the postgis in your old one. This is because the postgis function definitions will be looking for a particular postgis library name... the name of the library from your old database. You can hack around this, and have your cake and eat it too, to an extent, by symlinking the name of your old postgis to your new postgis library. P On Fri, Nov 27, 2009 at 4:11 PM, Tomas Lanczos wrote: > Hello, > > I am trying to restore my databases stored by a pg_dumpall command in > the Karmic Koala box. The restore command is the following: > > psql -f /media/disk/.../backup -U postgres > > I have a PostsgreSQL 8.4 installed from repositories with postgis1.4.1. > I recognized that the tables with spatial geometries were not restored > but at the moment I am almost sure that it's caused by that the older > version of postgis in the stored database. What is a kind of mystery for > me that data in several tables were not restored, although the table > definitions did (it means that I got several tables without data) > althoug the data are there in the backup file (I checked physically). I > really don't understand what's going on there, I did the same many times > before, without any problem. > > Tomas > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Access a Field / Column of a resultset by Number
On Fri, Nov 27, 2009 at 10:09 AM, Daniel Schuchardt wrote: > thats exactly the same i'm looking for: > > http://wiki.postgresql.org/wiki/Todo > http://archives.postgresql.org/pgsql-patches/2005-07/msg00458.php > > (todo list for plpgsql) > > *Server-Side Languages > *PL/pgSQL > * > *[D] Allow listing of record column names, and access to record columns *via > variables, e.g. columns := r.(*), tval2 := r.(colname) > * > *Re: PL/PGSQL: Dynamic Record Introspection > > > is that is possible in any pl* language? Yes, if the language has the architecture to handle it. plpgsql doesn't right now. pltcl, plperl, and plain old C functions can examine records and do dynamic stuff with them. Any attempt at doing dynamic queries right now in plpgsql leads to madness, or so I've been told. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
