Re: [GENERAL] Best data type to use for sales tax percent
On Oct 8, 2009, at 3:04 PM, Mike Christensen wrote: I'm guessing numeric(2,3) should be fine, yes? Numeric is definitely what you want. You probably want at least four fractional digits, since sales tax (at least in California) is routinely set to a quarter of a point. For example, the basic sales tax rate in Los Angeles is 9.75%, so 0.0975. (There are other subtleties in sales tax calculation in California; feel free to ask off-list if you want more utterly non-PostgreSQL-related detail. :) ) -- -- Christophe Pettus x...@thebuild.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] numeric field overflow
On Oct 5, 2009, at 11:20 PM, Nurzhan Kirbassov wrote: So, does the precision part of the numeric type really means number of digits to the left of the decimal point, or what ? NUMERIC is behaving as documented. The way to think of it is when you are inserting: INSERT INTO test.test VALUES (1) into a NUMERIC(5,1), what you are doing is inserting: INSERT INTO test.test VALUES (1.0) 1.0 has six significant digits, rather than five, so the insert fails. -- -- Christophe Pettus x...@thebuild.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] How useful is the money datatype?
A quick check of the source code (src/backend/utils/adt/numeric.c) shows it's base 1, each digit represented as an int16. It's not strictly speaking BCD, but there's no computational difference. -- -- Christophe Pettus x...@thebuild.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] How useful is the money datatype?
On Oct 4, 2009, at 7:09 PM, Guy Rouillier wrote: There is no reason why PG could not support packed decimal. Is that not NUMERIC? -- -- Christophe Pettus x...@thebuild.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] Postgresql Web Hosting
On Sep 29, 2009, at 4:22 PM, Matt Friedman wrote: Anyone have thoughts on decent hosting companies for this sort of thing? I've had good experiences with Slicehost (http://slicehost.com/). They're a VPS provider, so you'll need to do some sysadmin-y stuff, but it does have the advantage that you can install exactly what you want (so, no being stuck with PG 8.0, for example). -- -- Christophe Pettus x...@thebuild.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] Limiting a user to a single or subset of databases in a cluster?
Of course, one can create a user that has no useful privileges on a particular database, but is there a way of limiting a user to being able to log into only a single one of, or a subset of, the databases in a cluster? -- -- Christophe Pettus x...@thebuild.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] Limiting a user to a single or subset of databases in a cluster?
On Sep 26, 2009, at 11:29 AM, Christophe Pettus wrote: Of course, one can create a user that has no useful privileges on a particular database, but is there a way of limiting a user to being able to log into only a single one of, or a subset of, the databases in a cluster? Like, say GRANT / REVOKE CONNECT ON database TO role, just for example? Never mind! -- -- Christophe Pettus x...@thebuild.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] vacuum won't even start
Hi all, I've a problem on a heavy loaded database: vacuums don't work since about a week. All I got is: mybase=# vacuum verbose analyze public.mytable; INFO: vacuuming public.mytable (I stop it after hours) Looking with top and iotop, I see the process takes some cpu and disk io time during several minutes, then it seems to fall asleep. The process isn't locked according to pg_stat_activity. My setup: - postgresql 8.3.7 with contribs ltree and pgcrypto - OS: debian etch kernel 2.6.24 - HW: 8cores Xeon/32GB RAM/3RAID10 volumes(index, data, pgxlog) - dbase size: about 240GB - millions of queries/day - 1000 locks continually - about 200 simultanous connections - load: 30%iowait, 60%user, 10%sys Autovacuum is disabled to prevent it from loading the server during peak hours. Regular vacuums running each night as cron job Since about a week the nightly vacuums don't work. I tried manual ones with no avail, same symptoms as above on small tables (350 rows) as well as on big ones (almost 1 billion rows) As the croned vacuums don't run anymore, I see now autovacuums (to prevent wraparound) running all the time, but their process don't use any cpu time nor disk io. Autovacuum seems to work well on the pg_catalog schema. The problem seems to start with some queries lasting more 15 hours. I tried to kill them (signal 15) with no avail. I can't restart the server as it's a big production server. We're planning to upgrade the hardware soon, but I suspect we'll have the same problems in the future as our platform is growing. Does anyone have any info about this problem, and the means to prevent it ? Thanks in advance. Regards, -- JC Ph'nglui mglw'nafh Cthulhu n'gah Bill R'lyeh Wgah'nagl fhtagn! -- 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] vacuum won't even start
Alvaro Herrera a écrit : Jean-Christophe Praud wrote: Hi all, I've a problem on a heavy loaded database: vacuums don't work since about a week. All I got is: mybase=# vacuum verbose analyze public.mytable; INFO: vacuuming public.mytable (I stop it after hours) Looking with top and iotop, I see the process takes some cpu and disk io time during several minutes, then it seems to fall asleep. The process isn't locked according to pg_stat_activity. What are your vacuum_cost_% parameters? I've let the default values (not even uncommented in the conf file ;) ): #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20# 0-1 credits #vacuum_cost_limit = 200# 1-1 credits -- JC Ph'nglui mglw'nafh Cthulhu n'gah Bill R'lyeh Wgah'nagl fhtagn!
Re: [GENERAL] vacuum won't even start
Tom Lane a écrit : Jean-Christophe Praud j...@steek.com writes: I've a problem on a heavy loaded database: vacuums don't work since about a week. All I got is: mybase=# vacuum verbose analyze public.mytable; INFO: vacuuming public.mytable (I stop it after hours) Looking with top and iotop, I see the process takes some cpu and disk io time during several minutes, then it seems to fall asleep. The process isn't locked according to pg_stat_activity. When vacuum wants to clean up a particular table page, it will wait until no other process is examining that page; and this wait is not visible in pg_locks. Perhaps you have got some queries referencing those tables that have stopped midway and are just sitting? Although pg_locks won't immediately show the wait, it could be useful to help identify the culprit --- look for other processes holding any type of lock on the table the vacuum is stuck on, and then go to pg_stat_activity to see how old their current query is. regards, tom lane Indeed, the tables I tried to vacuum have locks on them. AccessShareLock belonging to queries which seem sleeping. I tried to kill these queries but pg_cancel_backend() has no effect, and the process doesn't get the 15 signal. How can I get rid of these blocking queries without restarting the server ? They are not listed as waiting in pg_stat_activity. These queries are MOVE FORWARD on cursors, the underlying query is a rather complex one (unions, joins, functions calls) Regards, -- JC Ph'nglui mglw'nafh Cthulhu n'gah Bill R'lyeh Wgah'nagl fhtagn!
Re: [GENERAL] vacuum won't even start
Tom Lane a écrit : Jean-Christophe Praud j...@steek.com writes: Indeed, the tables I tried to vacuum have locks on them. AccessShareLock belonging to queries which seem sleeping. I tried to kill these queries but pg_cancel_backend() has no effect, and the process doesn't get the 15 signal. How can I get rid of these blocking queries without restarting the server ? They are not listed as waiting in pg_stat_activity. Have you tried killing the connected client sessions? regards, tom lane It works ! I had pgbouncer connections hanging for several days. Thanks for your help :) Regards, -- JC Ph'nglui mglw'nafh Cthulhu n'gah Bill R'lyeh Wgah'nagl fhtagn!
Re: [GENERAL] $Body$
It's just a string constant: http://www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS It could be anything between the dollar signs, but BODY is self- documenting. On Aug 30, 2009, at 3:37 PM, bilal ghayyad wrote: Hi List; What does it mean the $BODY$ when writing the function? In other words: why to use the $ sign? Regards Bilal -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- -- Christophe Pettus x...@thebuild.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] best practise/pattern for large OR / LIKE searches
SELECT * FROM tbl WHERE datanumber LIKE '%12345%' OR LIKE '%54321%' OR LIKE '%8766%' OR LIKE '%009%', .. The number of OR/LIKES are in the order of 50-100 items... the table tbl is a couple of million rows. Are the fixed strings in the wildcards words (i.e., are they completely arbitrarily embedded in the text, or are they delimited in some regular way)? If they are words, you might consider using the full text functionality to create an index of them, and searching using that. -- -- Christophe Pettus x...@thebuild.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] Figuring out relationships between tables.
On Aug 23, 2009, at 7:47 AM, Andrew Cooper wrote: An employee can only have 1 manager/supervisor but the hierarchy can be varying depths. Traditionally, that's done by having a supervisor field as part of the employee record, with either NULL or a special marker value to indicate no supervisor. The new WITH RECURSIVE functionality of 8.4 makes dealing with tree structures like this much easier. -- 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] Wich the best way to control the logic of a web application?
On Aug 19, 2009, at 9:46 AM, Andre Lopes wrote: What do you think aboout this? Should I mix logic in Database and PHP or should I control the logic only in the PHP? There are no real hard and fast rules, of course, just rules of thumb. Here are some of mine. First, remember that there is rarely an application for a database. One of the most useful parts of a database is that it provides a central repository for data across multiple applications. For now, the web interface might be the only way to get at the data, but live systems tend to grow clients. Soon, you have external processes handling data interchange with other companies, processes sending customers email... This means two things: 1. You want to make sure that the database maintains as much data integrity as it can, as long as that data integrity really must be enforced across all applications. For example, in an inventory database, if an item's inventory level *always* equals all inventory receivings minus all shipments, then that's a good candidate for a rule enforced by a trigger in the database. 2. On the other hand, be careful of business logic that isn't universal creeping into the database. For example, for the web application, you might send an email to a user when they register. But do you *really* want to do *exactly the same thing* on a bulk load of new users from an external source? In other examples, page-to-page flow is probably not a great candidate for encoding in the database; I would think that it makes far more sense for the database to store the state of the various business objects, and let the PHP application decide what to display to the user. Similarly, formatting is often a better idea in PHP, since you may have more information about the right kind of formatting. (Kind of a shame, since PostgreSQL's data type text formatting is in many ways superior to PHP's!) However, a business rule that is designed to prevent bad data from entering the database is a good candidate from being enforced in the database. Lastly, as a matter of preference, I don't like putting things into the database that can block for extended periods, like sending email. I think those are better put into external processes that run against the database. (And, of course, putting things that can block for an extended period into the web application isn't good either.) Here's one concrete example of a decision I made recently; of course, I may have made the wrong one. :) Customers on this side can search across a large number of different types of entities, including catalog items, catalog categories, product buying guides, articles, etc. The design required that these be presented in particular ways, separate one from the other. I could have implemented a procedure in the database which took the search and returned the results, but I decided that would be pushing too much of the UI display use case into what should be a data store. Instead, the application does separate queries for each type, and unifies the results. (This does have a negative performance characteristic, since the application has to make multiple trips to the database instead of calling one function, but it wasn't significant enough to be a problem.) Hope this helps! -- -- Christophe Pettus x...@thebuild.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] Video from the August 11, 2009 SFPUG meeting available
Greetings, The video of the August 11, 2009 SFPUG talk, featuring David Fetter's presentation on windowing and common table expressions, is now up: http://thebuild.com/blog/2009/08/13/sfpug-windowing-and-common-table-expressions/ -- -- Christophe Pettus x...@thebuild.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] mail alert
On Aug 11, 2009, at 12:19 AM, Jan Verheyden wrote: I was looking in what way it’s possible to alert via mail when some conditions are true in a database. An external script that connects to the database, checks for the condition, and sends the email if it exists is probably your best solution. You might want to take a look at check_postgres: http://bucardo.org/check_postgres/ -- -- Christophe Pettus x...@thebuild.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] Monitoring tools for Postgresql
On Jul 28, 2009, at 11:24 PM, mukeshp wrote: Can anyone suggest me tools for monitoring postgresql server. ? As it happens, there was a talk about that very topic on PG day: Check Please! What Your Postgres Databases Wishes You Would Monitor / Robert Treat / OmniTI http://media.postgresql.org/pgday-sjc-09/pgday-sjc-09-monitoring.mov -- 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] Video available for PGDay SJC '09
On Jul 28, 2009, at 7:17 AM, Andreas Wenk wrote: another good approach. I understand the point of view. But this menas to download the files anyway because playing mpeg files in the browser is the same as with mov files - I think. Historically, MOV has been the least-bad container format; Flash support on anything besides Windows has, traditionally, been very spotty. The files themselves are pretty much the same size; FLV is (as noted) a container format, not a codec, and the video is H.264 either way. And, of course, you do have to download the video either way; it just starts playing faster if you are using a Flash viewer. Requiring a download first does have the advantage that it keeps the bandwidth off of media.postgresql.org down, since the video only has to be downloaded to your desktop once, rather than each time you watch it. (I'm not sure how much of a real issue this is, however.) The right answer is to move to using the video tag, now that more browsers are supporting it. For the next event, I'll encourage providing a video-based viewer (since I don't control the HTML on postgresql.org, I can't make any grand promises). -- 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] Video available for PGDay SJC '09
On Jul 28, 2009, at 8:35 AM, Andreas Wenk wrote: uh - HTML 5 is supported by the browser when? Currently, the video tag is supported by Firefox 3.5, Safari 3 and 4, Chrome 3 (in alpha, I believe), iPhone 3. That being said, we can do a fallback to Quicktime, then to a Flash client, all the way back to IE6. A nice example of fallback code is available at: http://camendesign.com/code/video_for_everybody Now, Joshua Drake has often uploaded these videos to Vimeo, thus giving everyone an in-browser channel as well. Thanks! -- -- Christophe Pettus x...@thebuild.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] Video available for PGDay SJC '09
On Jul 28, 2009, at 3:55 PM, Greg Stark wrote: In reality I would be pretty happy with any page that had a link at the bottom to download an mpeg format file with H.264 data in it that mplayer can play. Well, mplayer claims it can play MOV, and the files are H.264, so assuming the mplayer docs aren't lying, you're all set. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Video available for PGDay SJC '09
Greetings, The video recordings of the sessions for PG Day SJC '09 are now available: Version 8.4: Easier to Administer than Ever / Josh Berkus / PostgreSQL Experts http://media.postgresql.org/pgday-sjc-09/pgday-sjc-09-easier.mov Rapid Upgrades with pg_Migrator / Bruce Momjian / EnterpriseDB http://media.postgresql.org/pgday-sjc-09/pgday-sjc-09-migrator.mov Check Please! What Your Postgres Databases Wishes You Would Monitor / Robert Treat / OmniTI http://media.postgresql.org/pgday-sjc-09/pgday-sjc-09-monitoring.mov PostgreSQL Pitfalls / Jeff Davis / Truviso http://media.postgresql.org/pgday-sjc-09/pgday-sjc-09-pitfalls.mov What works with Postgres: The Open Geo Data Interoperabilty Overview / Brian Hamlin / OSGeo Foundation http://media.postgresql.org/pgday-sjc-09/pgday-sjc-09-postgis.mov Very Large Databases and PostgreSQL: Issues and Solutions / John Cieslewicz / Aster Data http://media.postgresql.org/pgday-sjc-09/pgday-sjc-09-petabytes.mov pgGearman: A distributed worker queue for PostgreSQL / Brian Aker, Eric Day / Gearman Project http://media.postgresql.org/pgday-sjc-09/pgday-sjc-09-gearman.mov Lightning Talks http://media.postgresql.org/pgday-sjc-09/pgday-sjc-09-lightning.mov Many thanks to Steve Crawford for the audio system and assistance with setup, tear-down, and coordination during the event. -- -- Christophe Pettus x...@thebuild.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] Building from source vs RPMs
I'm moving from a long time in BSD-land to using Linux. I've always been in the habit of building PostgreSQL from the source tarballs. On Linux, is there an advantage either way to using the RPMs as opposed to building from source? Thanks! -- -- Christophe Pettus x...@thebuild.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] 3des key lengths and key management
On Jul 23, 2009, at 12:11 PM, Steve Atkins wrote: They asked me to open up my firewall to them, pointing at a fake server, just so they'd have something to audit, after failing our audit because we only allowed access to the application from inside our firewall. I'm glad it wasn't just me. We failed our audit because our firewall correctly detected their probes as an intrusion, and shut them down. It would be as if your home failed a security audit because no one could get through the external fence, so we couldn't verify that the door was locked. -- 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] commercial adaptation of postgres
On Jul 20, 2009, at 6:56 PM, Dennis Gearon wrote: I once talked to a company that made a custome version of Postgres. It split tables up on columns and also by rows, had some other custome features. It was enormously faster from what I gathered. I could of sworn it began with the letter 'T', but maybe not. I don't see anything like that on the commercial page of the posgres site. Truviso? http://truviso.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] search for partial dates
On Jun 11, 2009, at 1:23 PM, James B. Byrne wrote: Given a datetime column, not null, is there a single syntax that permits searching for all dates in a given year, year+month, and year+month+day such that a single parameterised query can handle all three circumstances? Well, of course, in a trivial sense: SELECT * FROM the_table WHERE datetimecolumn = $1 AND datetimecolumn = $2; The application has to create the appropriate values for the first and last days of the year or month in this case, but it's a rare language that doesn't that facility. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Rails and PostgreSQL now up on media.postgresql.org
Greetings, The video from the June 9, 2009 SFPUG meeting, PostgreSQL as a secret weapon for high-performance Ruby on Rails applications, is now available for viewing or download from the media.postgresql.org server: http://media.postgresql.org/sfpug/sfpug-rails-20090609.mov Thanks, -- Xof -- 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] Rails and PostgreSQL now up on media.postgresql.org
On Jun 10, 2009, at 6:27 PM, Chris wrote: Can you list the filesizes on http://media.postgresql.org/sfpug/ please? Josh Berkus handles that page, but I think that's a splendid idea. This particular video file is about 403MB. -- 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] Bloated Table
On May 27, 2009, at 3:25 PM, Greg Smith wrote: Interesting and an extremely common request. I just added an item to the Vacuum section of the TODO list while you were listing issues and potential solutions here: Provide more information in order to improve user-side estimates of dead space bloat in relations links to this thread. Given this, is there is a recommended way of detecting bloat in indices automatically, rather than just looking at them once in a while and saying, Hm, that looks pretty big? -- 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] do postgresql this job for me ? (firebird user)
On May 24, 2009, at 8:58 AM, Craig Ringer wrote: There isn't currently any REINDEX CONCURRENTLY option, but people seem to have a few workarounds that do the job if you really do need to rebuild an index on a live, active table. It's pretty straight-forward to do: CREATE INDEX CONCURRENTLY idx_new ON table ... ; DROP INDEX idx; ALTER INDEX idx_new RENAME TO idx; -- 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] 8.3: timestamp subtraction
On May 23, 2009, at 9:13 AM, Daniel Verite wrote: I don't know why this query returns false: SELECT '20040506 070809.01'::timestamp(6) - '20010203 040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval; If I just subtract the two timestamps, its result is the interval I specified. What may cause this? It works for me: test= SELECT '20040506 070809.01'::timestamp(6) - '20010203 040506.007000'::timestamp(6)= '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval; ?column? -- t (1 row) Could this be due to the OP's build of PG using floating point timestamps? -- 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] 8.3: timestamp subtraction
On May 23, 2009, at 10:44 AM, Havasvölgyi Ottó wrote: Thanks. I tested the standard Win32 distribution of 8.3.6. The same happens on 8.2. But on 8.0 it works. When I don't use milliseconds, then it works. Will 8.4 work fine on Win32 again? If the issue is using floating point timestamps, then the particular version of PostgreSQL isn't the issue; it's whether the distribution you were using was built with integer or floating point timestamps. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL in the cloud now up on media.postgresql.org
Greetings, The video from the May 12, 2009 SFPUG meeting, BIRT PostgreSQL, is now available for viewing or download from the media.postgresql.org server: http://media.postgresql.org/sfpug/sfpug-birt-20090512.mov Thanks, -- Xof -- 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] Controlling psql output
On May 8, 2009, at 9:51 AM, Gauthier, Dave wrote: This gets rid of the header and footer OK. But there is still a blank line as the first line in stdout. Also, each record has a preceding space before the column value. Is there a way to do what I want? sed?
Re: [GENERAL] Column oriented pgsql
On May 8, 2009, at 11:25 AM, John R Pierce wrote: you read your tables by column, rather than by row?? SQL queries are inherently row oriented, the fundamental unit of storage is a 'tuple', which is a representation of a row of a table. I believe what is referring to is the disk storage organization, clustering a single column from multiple rows together onto a page. For example, if your typical use of a table is to read one particular column from a large number of rows, this could (in theory) improve performance. AFAIK, PostgreSQL doesn't support 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] XML - PG ?
On May 6, 2009, at 10:47 AM, Gauthier, Dave wrote: Is there a way to read an XML file into a postgres DB? I’m thinking that it will create and relate whatever tables are necessary to reflect whatever’s implied by the XML file structure. There's no built-in functionality that does what you describe, although building such a thing would be very straight-forward. There are many application-specific decisions you'd need to make (what level of hierarchy in the XML file corresponds to a database/schema/table, for example, and how to handle nested fields and missing ones).
Re: [GENERAL] PostgreSQL Object-Oriented Database?
On Apr 27, 2009, at 7:00 AM, Robert Pepersack wrote: My agency has a contractor that created a PostgreSQL database that he calls object-oriented. I noticed that the contractor has more than one value in a column separated by commas. In the relational world, this obviously violates first normal form. When I asked about it, he said that it's because PostgreSQL is an object- oriented database. I'm very skeptical. Without knowing the details of the design, it's hard to comment on the design's strengths or weaknesses. However, there is nothing about PostgreSQL that requires any different design than one would use on any relational database. Arrays are, sometimes, a good solution for particular problems, and PostgreSQL has very fluent array functionality, but nothing about PostgreSQL requires their use versus more traditional relational designs. -- 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] Yet another drop table vs delete question
On Apr 21, 2009, at 12:28 PM, Peter Eisentraut wrote: Yes, but if you are asking that question, you probably really want to use TRUNCATE. The advantage of DROP TABLE being, of course, that DROP TABLE is transactionally-safe, while TRUNCATE is not. -- 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] Yet another drop table vs delete question
On Apr 21, 2009, at 1:20 PM, Tom Lane wrote: They're both going to drop data that might conceivably be visible in the snapshot of some concurrent transaction that hasn't yet touched the table (else it would have lock) but might wish to do so later. Unless I'm deeply misunderstanding something (always a possibility), DROP TABLE and TRUNCATE are not symmetrical in this regard. Once a transaction has issued a DROP TABLE, all other transactions that attempt to modify it are going to block on the first transaction's ACCESS EXCLUSIVE lock until it commits or aborts. In the case of TRUNCATE, the other transactions will see the table as being empty from the moment in the first transaction issues the TRUNCATE, and will see the rows reappear if the first transaction rolls back. Yes? -- 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] Yet another drop table vs delete question
On Apr 21, 2009, at 1:36 PM, Tom Lane wrote: I was thinking of MVCC semantics, which is a different issue. Indeed so, my error. This is a bit of a drift off-topic, but rereading the docs, I'm now having trouble visualizing the real-world effect of the non-MVCC-safeness of TRUNCATE. A transaction that queries the table before the TRUNCATE is going to pick up an ACCESS SHARED lock, which will prevent the TRUNCATE from running until it's released. The TRUNCATE will pick up an ACCESS EXCLUSIVE lock that will block any subsequent queries until the transaction doing the TRUNCATE commits. I'm sure there is a scenario under which a separate transaction could see non-MVCC behavior from TRUNCATE, but I'm having trouble see what it is. -- 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] Yet another drop table vs delete question
On Apr 21, 2009, at 2:15 PM, Jeff Davis wrote: In Session1, the serializable transaction sees an empty version of bar, even though it had tuples in at the time Session1 got its serializable snapshot. Indeed so, and I understand that part. But since Session1 didn't try to access 'bar', it can't distinguish that sequence from: Session2: BEGIN; TRUNCATE bar; COMMIT; Session1: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; SELECT * FROM foo; SELECT * from bar; COMMIT; I've been trying to come up with a scenario in which a TRUNCATE violates concurrency expectations; I'm sure one exists, but my brain isn't wrapping around it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL in the cloud now up on media.postgresql.org
Greetings, The video from the April 8, 2009 SFPUG meeting, PostgreSQL in the Cloud, is now available for viewing or download from the media.postgresql.org server: http://media.postgresql.org/sfpug/sfpug-cloud-20090408.mov Thanks to Josh Berkus for his organizational talents, and Dirk Jagdmann for running the live streaming during the event. -- Xof -- 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] PostgreSQL in the cloud now up on media.postgresql.org
On Apr 13, 2009, at 1:59 PM, Ivan Sergio Borgonovo wrote: wget says it is a 1Gb file. Is there anything smaller? Since I have the master right here, I'll be happy to reencode it to a smaller size (250mb is probably the reasonable lower limit before the video quality reaches the point that we might as well just post the audio :) ). -- 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] PostgreSQL in the cloud now up on media.postgresql.org
On Apr 13, 2009, at 2:54 PM, Joshua D. Drake wrote: Yes Vimeo can download and I will have it up soon. Thank you! My work here is done. :) -- 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] Connect without specifying a database?
On Apr 11, 2009, at 10:15 AM, li...@mgreg.com wrote: So, how does needing to connect to a database before querying about existing databases make any sense? Well, you have to connect to the database server, no matter what, in order to check on the existence of a database (unless you are doing it by groveling around in the data directories). The fact that you have to supply a database as part of that connection seems unremarkable. Suppose the documentation said something along these lines: All connections are to a specific database. For operations in which a user-created database would be inappropriate (such as checking for the existence of a user-created database), the database 'root' is created by initdb as a known default for such operations. That would seem to be utterly uncontroversial and normal, yes? OK s/root/template1/, and you're set. :) -- 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] unexpected check constraint violation
On Mar 23, 2009, at 1:41 PM, Jeremy Harris wrote: Because equality is not well-defined for real values? That was my first thought, too, but why would two identical real literals evaluate to different bit patterns? -- 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] Accent insensitive search?
What I've done in the past in this situation is to create a separate field with the text normalized to whatever the search form is (all lower case, accents stripped, etc.), and then index and search that from the application. Although I've not tried it, a functional index that did the same thing might work, as long as you were careful in how the queries were written to ensure that it was used. - 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] Accent insensitive search?
On Mar 18, 2009, at 11:24 AM, Alvaro Herrera wrote:
Hmm, if to_ascii() doesn't work, that's something worth some research.
Maybe the encoding config is broken, for example.
The docs say to_ascii() only works with LATIN1, LATIN2, LATIN9, and
WIN1250; maybe convert('string', 'UTF-8', 'SQL_ASCII')?
-
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] Re: [pgsql-advocacy] Video from the 2009-03-11 SFPUG talk on Unison, by Reese Hart
On Mar 17, 2009, at 9:57 PM, Joshua D. Drake wrote: It is also on Vimeo: http://www.vimeo.com/3732938 Joshua D. Drake Thanks! - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Video from the 2009-03-11 SFPUG talk on Unison, by Reese Hart
Hi, The video is now available for download! You can find it at: http://blog.thebuild.com/sfpug/sfpug-unison-20090311.mov Thanks, -- Christophe -- 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] Maximum transaction rate
On Mar 13, 2009, at 11:59 AM, Joshua D. Drake wrote: Wait, actually a good BBU RAID controller will disable the cache on the drives. So everything that is cached is already on the controller vs. the drives itself. Or am I missing something? Maybe I'm missing something, but a BBU controller moves the safe point from the platters to the controller, but it doesn't move it all the way into the OS. So, if the software calls fsync, but fsync doesn't actually push the data to the controller, you are still at risk... right? -- 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] Streaming PUG meeting: PostgreSQL Genetics!
On Mar 11, 2009, at 5:51 PM, CaT wrote: Will there be a saved version of this available for later viewing? Don't make me choose between steak and beer and postgres. 8( Yes! I'll announce it here when it's available. -- 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] pgsql announce now on twitter
On Mar 4, 2009, at 9:54 AM, Joshua D. Drake wrote: Not sure what the complainer is talking about here. pgsql-announce is moderated so spam should be almost nil. I'm not 100% sure what Twitter spam *is*, for that matter. -- 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] Running untrusted sql safely?
On Feb 15, 2009, at 2:47 PM, Stuart McGraw wrote: I just hoping for some confirmation that the permissions based approach did not have some holes in it that I am not seeing. Another possibility is to create a set of functions that contain the query operations you would like to allow, isolate those in a schema, and make that schema the only thing accessible to the (semi-)trusted users. Generally, I try to design things so that web servers and other clients who could potentially be compromised don't execute full- function SQL directly, but go through functions instead. -- 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] auto insert data every one minute
Hi Why don't use pgAgent http://pgadmin.org/docs/dev/pgagent.html ? Regards, Christophe Chauvet. Tino Wildenhain a écrit : Hi, searchelite wrote: ... i can use pg_sleep..but i have thousands of data to be inserted..is there any better way using pg_sleep? I wonder what is you complete problem? It seems all the advices given so far are shots-in-the-dark. Could you perhaps expand a bit? Also for sophisticated solution, if you stick to windows you might want to consider something different then just pure CMD, say some scripting language to support. Tino -- 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 are locks managed in PG?
Playing the straight man, I have to ask: Scalability issues with locks in PG vs Oracle? -- 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] lesslog incorrect resource manager data checksum.
Koichi-san, full_page_writes are written to be on by default in the global user configuration file (i.e. postgresql.conf). Query to the server showed that it is : show full_page_writes ; full_page_writes -- on I use the RPM version provided on pgfoundry. The main problem is located on pg_compresslog side (first) : it is unabe to compress xlogs. I send you (in private) a bunch of uncompressed logs that are known to report pg_compresslog problem. Once pg_compresslog will work, I plan to test the other side solution with pg_decompresslog :) Thanks for your reply! 2008/11/20 Koichi Suzuki [EMAIL PROTECTED] Hi, Sorry I was out and late to find your report. Lesslog replaces full page write to corresponding incremental log to decrease the size and so you should turn full_page_writes on for lesslog to work. Did you specify pg_decpmpresslog as restore command? If so, I don't see anything missing so far. Pg_decompresslog must be used in place of cat. If possible, could you try to share your archive log (a part may help) and let me know how you built lesslog? (Did you use rpm?) They will help. I've tested lesslog against pgbench and DBT-2 so I believe the coverage is reasonable. Sorry for the inconvenience and thanks a lot for your help. - Koichi Suzuki 2008/11/18 Jean-Christophe Arnu [EMAIL PROTECTED]: Hi, I'm trying to use lesslog 1.1 (out of NTT rpm available on pgfoundry) on my pg_xlog files out of a 8.3.3 postgresql server. I always get messages such as incorrect resource manager data checksum. This message is issued (as I've read the sources) when there's CRC mismatch in a record. Do you have any idea of what's going wrong (i.e. why my CRC are not matching)? Does this deals with full_page_writes to be set to on (by default in GUC) which is commented in my GUC (thus set to on) ? Is there something missing? Note : I'm transferring my xlog to another node with rsync. The log I get (a bunch of) for my test purpose are the ones on the remote node, they should be exactly the same as the one on the server. Thanks by advance for any ideao! -- Jean-Christophe Arnu -- Jean-Christophe Arnu
Re: [GENERAL] Question about the WITH RECURSIVE patch
On Nov 20, 2008, at 1:21 PM, Tom Lane wrote: See the fine manual, for instance last para here: http://developer.postgresql.org/pgdocs/postgres/queries-with.html Fine manual indeed... this the best explanation of WITH RECURSIVE I've ever read. Kudos to the documentation writer(s). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] lesslog incorrect resource manager data checksum.
Hi, I'm trying to use lesslog 1.1 (out of NTT rpm available on pgfoundry) on my pg_xlog files out of a 8.3.3 postgresql server. I always get messages such as incorrect resource manager data checksum. This message is issued (as I've read the sources) when there's CRC mismatch in a record. Do you have any idea of what's going wrong (i.e. why my CRC are not matching)? Does this deals with full_page_writes to be set to on (by default in GUC) which is commented in my GUC (thus set to on) ? Is there something missing? Note : I'm transferring my xlog to another node with rsync. The log I get (a bunch of) for my test purpose are the ones on the remote node, they should be exactly the same as the one on the server. Thanks by advance for any ideao! -- Jean-Christophe Arnu
[GENERAL] pg_lesslog/pg_standby
Hi folks, I was wondering on pg_lesslog integration on our architecture based on wal shipping using pg_standby (thanks Simon) as recovery tool. The main target is to reduce wal files accumulation on master host (thus consumming disk space) in case of slave unavailability. As far as I've read the source of pg_stanby, WAL size is checked in CustomizableNextWALFileReady function, allowing customization of file size check. Actually, the legacy way WAL is checked, is if file size is normal WAL file size then we've got a supposed-good WAL. Thus, we cannot use compressed log files out of pg_lesslog, we need to decompress it before processing, including a pre-processing stage and/or command. There are three ways to do such : - first, we could write a script on master to compress, rsync then decompress (on slave) file, but the idea to call a remote processing on slave seems to be quite hazardous to me (in some way it may lead to strange situations) - second, we could rsync compressed log to a directory, use a local daemon on slave to watch a directory and decompress files to final place (pg_standby WAL source directory) - third, we could add some functionnalities to pg_standby allowing to pre-process WAL file (thus customizing CustomizableNextWALFileReady function?), this might be usefull for other issues or use cases than pg_lesslog but it applies quite good to it :) What are your thoughts for each of these points? Thanks, -- Jean-Christophe Arnu
Re: [GENERAL] Are there plans to add data compression feature to postgresql?
On Oct 30, 2008, at 8:10 AM, Grzegorz Jaśkiewicz wrote: up to 8.3 it was massively slower on raid1 (software raid on linux), starting from 8.3 things got lot lot better (we speak 3x speed improvement here), but it still isn't same as on 'plain' drive. I'm a bit surprised to hear that; what would pg be doing, unique to it, that would cause it to be slower on a RAID-1 cluster than on a plain drive? -- 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] Decreasing WAL size effects
On Oct 30, 2008, at 2:54 PM, Gregory Stark wrote: Wouldn't it be just as good to indicate to the archive command the amount of real data in the wal file and have it only bother copying up to that point? Hm! Interesting question: Can the WAL files be truncated, rather than zeroed, safely? -- 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] postgresql and Mac OS X
On Oct 28, 2008, at 4:03 PM, Steve Atkins wrote: Installing from source means I can avoid the fragility of macports or fink, and know that I've built it in much the same way as the postgresql or solaris installation I'd be using for production. +1 It means I can easily pick the contrib modules I'm interested in. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)
On Sep 25, 2008, at 1:05 PM, Casey Allen Shobe wrote: As for the expectation above - could pl/pgsql be made compilable? Without getting into the argument as to the level of security provided, it strikes me that a reasonable approach would be a non- core pluggable language which accepts encrypted strings as functions, decrypts them (using a key compiled into the language module), and passes them on to PL/pgSQL for execution. This would keep the functionality out of core, allow the developer who distributes the code to plop in their own key and distribute the code as a compiled module, and minimizes reimplementation. This would, of course, be easily hacked with someone who can step through the language module with a debugger, but I don't see any reasonable way of preventing someone with that level of access from breaking the code without OS-level support. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres does not start, gives no error
On Sep 4, 2008, at 11:34 AM, Akhtar Yasmin-B05532 wrote: I am really stuck here. And need to get a way thru all of this. Any suggestions will be really appreciated. Have you confirmed that the user that you are logged in as when you attempt to start Postgres has write access to /home/data/www/pg7/data? -- 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] Oracle and Postgresql
On Aug 31, 2008, at 7:44 PM, Guy Rouillier wrote: CTOs/CIOs like to sleep at night. If you buy Oracle, and there's a problem, the conversation with the CEO is that Oracle broke. With PG, even if you have exactly the same level of support, that database you selected broke. The sad reality is that choosing something with a high industry presence makes it easier to offload blame for failures, especially when dealing with non-technical management. -- 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] ERROR: relation . . . does not exist
On Aug 30, 2008, at 6:26 AM, Albretch Mueller wrote: Well, then obviously there is the need for it and you were not successful enough at convincing these developers that they were confusing postgresql with a spreadsheet program The behavior you are looking for is typical of a spreadsheet, because spreadsheet programs (generally; I'm sure there are exceptions) don't have the notion of a schema; each cell can hold its own particular type. That being said, the automatic type-guessing that Excel, say, provides is far from foolproof; I've probably spent more time cleaning up Excel's bad guesses than would have been saved by my just specifying a type for each column. As has been noted, text representation of values are extremely ambiguous as of which Postgres type they mean... and, of course, you could have user-defined domains and types as well. It's true that it could take a wild guess, but that's not a traditional part of a database engine. That being said, it would not be too hard to write a client that accepted a CSV or tab-delimited file, parsed the header into column names, and then scanned the values of the columns to take a reasonable guess as to the column type from a highly limited set of possibilities. This is probably one of those classic twenty lines of Perl problems. It doesn't seem as though COPY INTO is the right place for that, since the particular guesses and set of types that one would make strike me as very closely tied to your particular application domain. -- 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] ERROR: relation . . . does not exist
On Aug 30, 2008, at 10:33 AM, Albretch Mueller wrote: well, yeah! I would totally agree with you, but since I doubt very much COPY FROM CSV is part of the SQL standard to beging with, why not spice it up a little more? I'd guess that coming up with a general algorithm to guess the type from a column of CSV text would satisfy no one, since we'd always miss a particular case that is important to someone (are times local or UTC? Should we assume integer or float? Varchar or text?), and the option is a forest of switches that would be extremely complex and error prone. This sounds very much like an application-domain problem, best solved in the application domain. -- 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] ERROR: relation . . . does not exist
You have made clear to me why my attempt for a RFE for COPY FROM CVS has found some technical resistance/disagreement, but I still think my idea even if not so popular for concrete and cultural reasons makes at least sense to some people It's a perfectly reasonable problem to want to solve; the question is whether COPY is the right place to solve it. I would think that a tool that reads the CSV data and produces a proposed schema definition for the table would be a more generally-useful approach. -- 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 LAST_INSERT_ID() to Postgres
On Aug 28, 2008, at 3:23 PM, D. Dante Lorenso wrote: I use RETURNING for all my insert and UPDATE statements now. Usually I'll return the primary key for the table, but sometimes I return a column that is created by one of my triggers. It's awesome to be able to do this in one query. Word. My current pet architecture is to set up Postgres like an application server (the web front end just call PL/pgSQL stuff rather than doing direct SQL), and this makes my life much easier 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] indexes on functions and create or replace function
On Aug 28, 2008, at 3:21 PM, Matthew Dennis wrote: I have no doubt that someone would complain about it, but I think it's better than the alternative. Determining if changing any function will cause an index to break is not a straight-forward problem. I don't believe that PG right now keeps a graph of which functions call which, so replacing a function deep in the logical calling hierarchy could break the index as easily as one mentioned at the top. -- 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] indexes on functions and create or replace function
On Aug 28, 2008, at 5:49 PM, Matthew Dennis wrote: Yes, I can see that would indeed be a problem. Are there future plans to start tracking such dependencies? It seems like it would be a good idea in general. I believe the EXECUTE statement would thwart such plans. -- 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] indexes on functions and create or replace function
On Aug 28, 2008, at 6:10 PM, Matthew Dennis wrote: I'm not sure I follow. Couldn't you track which statements were prepared that called a function and either reprepare (just like reindex, recheck, etc) or in the case of dropping a function, refuse to drop it because something depends on it? EXECUTE in PL/pgSQL accepts a string and executes it as a statement. (That's different from the SQL-level EXECUTE.) I'm not sure how one would track dependencies there. -- 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] indexes on functions and create or replace function
On Aug 28, 2008, at 6:51 PM, Alvaro Herrera wrote: Also, you have to keep in mind that we support pluggable languages. The function's source code is just an opaque string. Oh, ouch, right. I think that this is one of those cases where it's better that we simply advertise: BE AWARE OF THIS, rather than try to provide a half- baked solution that gives the illusion of safety without the 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] indexes on functions and create or replace function
On Aug 28, 2008, at 7:04 PM, Matthew Dennis wrote: The plpgsql execute statement, as I understand it, means take this string and execute like a client sent it to you. Of course, the string could come from anywhere. There's no inherent reason that I can think of (except good taste) that you could not write a function that retrieved a string out of a field in a table, executed it, and returned that as a value for use in an index. The client didn't send the string along, but it still creates dependencies. Anyway, as was correctly pointed out, tracking dependencies in PL/ pgSQL is bad enough, but PL/Java, PL/Perl... -- 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] indexes on functions and create or replace function
On Aug 28, 2008, at 7:22 PM, Matthew Dennis wrote: Yes, but in the case of pluggable languages, you still load something that constitutes the source. In the case of PL/Java, the jar for example. This would mean that, for example, if you changed any single function (no matter how distant and irrelevant to the one used to create a functional index), the jar would change, so we would have to invalidate all functional indexes written using functions contained in that jar. I'd certainly rebel at that: It would make deploying a new version of the jar very expensive, and unavoidably so. I'd have to say that the current situation falls into the category of slightly annoying, but it has the benefit that whether or not to rebuild the index is left 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
Re: [GENERAL] Postgre connect on Postgre
On Aug 26, 2008, at 7:10 PM, Anderson dos Santos Donda wrote: How I can connect a postgre database on another postgre database, and manipulate the datas on both database? There is a module in contrib just for such a purpose: http://www.postgresql.org/docs/8.3/interactive/dblink.html -- 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] plpgsql functions
On Aug 15, 2008, at 1:47 PM, Raymond O'Donnell wrote: For functions return SETOF any type, you need to use the following idiom: Or, you can use, RETURN QUERY your query here -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Running a PL/pgSQL function
I'm startled that I've never done this before, but... I have a PL/ pgSQL function that takes no arguments, returns VOID, and has a bunch of side effects on the database. The correct way of invoking this function is: SELECT my_func(); ... yes? Thanks; it seems to work fine, but using SELECT here is causing some part of my brain to scream counter-intuitive. -- 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] uuid
On Aug 3, 2008, at 1:18 PM, [EMAIL PROTECTED] wrote: ERROR: function uuid_ns_url() does not exist Remember to install the functions in your database using the SQL file in the contrib/uuid-ossp directory, uuid-ossp.sql. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/pgSQL equivalent to PQtransactionStatus?
My apologies if this is in the docs and I missed it, but is there a PL/pgSQL function equivalent for the pglib function PQtransactionStatus (i.e., a way to find out if we're in an open transaction block, and if that transaction is in an error status)? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL equivalent to PQtransactionStatus?
On Jul 31, 2008, at 11:12 AM, Richard Huxton wrote: A pl/pgsql function *always* executes within a transaction. Indeed so. What I'm looking for is a way of detecting if a transaction block has been opened (i.e., we're within a BEGIN). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL equivalent to PQtransactionStatus?
On Jul 31, 2008, at 11:49 AM, Alvaro Herrera wrote: Why does it matter? I'm attempting to clean out a connection that is in an unknown state (along the lines of what pgpool does when reusing an open connection). Of course, I could just fire an ABORT down, but it seems nicer to avoid doing so if no transaction block is open. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL equivalent to PQtransactionStatus?
On Jul 31, 2008, at 11:49 AM, Alvaro Herrera wrote: Why does it matter? Ah, I see, deep confusing on my part regarding PL/pgSQL and tranasctions! Ignore question. :) -- 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 do I set up automatic backups?
On Jul 29, 2008, at 1:24 PM, Rob Richardson wrote: I was asked how to automate the procedure, and I couldn't answer. The options are manifold! http://www.postgresql.org/docs/8.3/interactive/backup.html -- 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] Cursor
On Jul 29, 2008, at 2:35 PM, Tom Lane wrote: No, he does need an OPEN. Really? I thought that PG didn't use OPEN: The PostgreSQL server does not implement an OPEN statement for cursors; a cursor is considered to be open when it is declared. http://www.postgresql.org/docs/8.3/interactive/sql-declare.html -- 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] Cursor
On Jul 29, 2008, at 4:51 PM, Klint Gore wrote: It's different in PL/pgSQL. Ah, yes, sorry, didn't catch that it was a PL/pgSQL function. -- 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] Fetch for Update
On Jul 28, 2008, at 9:16 AM, Bob Pawley wrote: I haven't been able to find much information on Fetch for Update. Does 8.3 support this command?? Postgres doesn't have an explicit FETCH FOR UDPATE. You can either create the cursor with SELECT FOR UPDATE, or UPDATE the row in the cursor using UPDATE ... CURRENT OF. -- 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] php + postgresql
On Jul 25, 2008, at 11:20 AM, Andrew Sullivan wrote: On Fri, Jul 25, 2008 at 01:41:50PM -0400, Jonathan Bond-Caron wrote: I'd say the Web is just and always was a hack I have to object to this pretty strongly. He has a point, though. If you were starting out to build a user interface framework for building applications to be used by general users, I really doubt you'd end up with the current situation of HTTP, HTML, CSS, Javascript. But that's no matter, really, because here we are. Same for PHP. If you wanted to build a great, elegant, scripting language for writing web front ends, you almost certainly would not end up with PHP. But, here we are. Coming from a C++ and Java background, I find PHP to be just nasty in a lot of ways, but it gets the job done. Most developers don't make deep informed decisions about PHP vs other languages. They use it because everyone else is, there is a huge ecosystem of support around it, it's easy to get something flopping around on the table quickly, and they know *for sure* that they can host it anywhere. Which, really, are not terrible reasons to pick a development environment. Dragging the subject back to PostgreSQL, it's the same thing with MySQL vs PG. Very few people do detailed technical analyses of exactly which DB to use (and, if they do, they use PG :) ). They use MySQL because everyone else does, it gets the job done (or at least appears to), and, most importantly, every $9.95/month hosting plan in the world includes MySQL because Wordpress requires it. -- 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] rollback
On Jul 9, 2008, at 6:38 AM, Adrian Moisey wrote: I would like to be able to mark a point in my postgres database. After that I want to change a few things and rollback to that point. Does postgres support such a thing? Is it possible for me to do this? This seems to be exactly what transactions are designed to do. Could you tell us a bit more about the application and why a transaction isn't the right solution? -- 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] Default UUID in Postgres
On Jul 9, 2008, at 4:32 PM, Swaminathan Saikumar wrote: Hello, I created a Postgres table with a UUID. I want the UUID to be populated by default. PostgreSQL doesn't have built-in functions for generating UUIDs, but there is a module in contrib that will do so: http://www.postgresql.org/docs/current/static/datatype-uuid.html -- 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] To store and retrive image data in postgresql
On Jul 7, 2008, at 12:22 PM, aravind chandu wrote: I need to store an image in postgresql database and after that i need to retrive the image back.Can you please help me how to do this? Assuming you mean an image as in a binary visual image (like a JPEG), the data type you want is BYTEA. How to actually get BYTEA data into and out of PostgreSQL depends on what client library and language you are using. If your application allows it, it is often far more efficient to store a reference to the image in the database (such as a URI or file path), rather than the image itself. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] fitler database list
Is it a way to filter the database list on the tables containt in the database ? Thanks. -- 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] Mediawiki 1.10 and PG 8.3 upgrade
You'll need to get a particular revision of MediaWiki that is PG 8.3 compatible: http://people.planetpostgresql.org/greg/index.php?/archives/123- MediaWiki-is-Postgres-8.3-compatible.html Once that's done, it works fine (at least for 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] rounding problems
Yet another option, of course, is to simply not do any calculations in PostgreSQL, and accept the results from Excel as definitive... which seems to be what is desired, anyway. -- 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] Unloading a table consistently
On May 3, 2008, at 9:29 PM, Patrick TJ McPhee wrote: How about something along the lines of BEGIN; ALTER TABLE log RENAME to log_old; CREATE TABLE log(...); COMMIT; BEGIN; LOCK table log_old; COPY log_old TO 'filename-path'; DROP TABLE log_old; COMMIT; I believe this will keep the writers writing while keeping the efficiency of truncating. Brilliant. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unloading a table consistently
Hi, I will have a log table which, once a day or so, is copied to a file (for movement to a data warehouse), and the log table emptied. For performance, the log table on the production system has no indexes, and is write-only. (The unload process is the only reader.) To unload it, I will be doing: BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; COPY log TO 'filename-path'; TRUNCATE log; COMMIT; My understanding is that I need the SERIALIZABLE isolation level so that the COPY and TRUNCATE see exactly the same view of the table. Obviously, I don't want to lose data by having the TRUNCATE delete records that appeared while the COPY was executing. Is that correct? Is there a better way to handle this kind of thing that I'm missing? Thanks! -- Xof -- 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] Unloading a table consistently
On May 3, 2008, at 9:56 AM, Tom Lane wrote: This is a great deal less efficient than TRUNCATE, but it's secure for concurrent insertions, which TRUNCATE is definitely not. Exactly my question; thank you! -- Xof -- 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] Backup setup
For a database that big, you might consider using the WAL archiving strategy and shipping the WAL files offsite: http://www.postgresql.org/docs/8.3/interactive/continuous- archiving.html On Apr 23, 2008, at 8:14 AM, Gabor Siklos wrote: I need to back up our database off-site for disaster recovery. If I just back up the entire database data directory (i.e. /var/lib/ pgsql/data) will I be able to restore from there? Or should I instead just dump the data, using pg_dump, and back up the dump? The advantage of the first method would be that I would not have to wait for pg_dump (it takes quite long on our 60G+ database) and would just be able to configure the backup agent to monitor the data directory and do differential backups of the files there every hour or so. Your suggestions are much appreciated! -Gabor
Re: [GENERAL] How to modify ENUM datatypes?
On Apr 22, 2008, at 1:45 PM, D. Dante Lorenso wrote: So, the advice here is don't use ENUM? I think it's more Don't use ENUM for a type that you are planning to extend. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
