Re: [SQL] Trace for postgreSQL
On Tue, Feb 10, 2004 at 12:04:42PM +, beyaNet Consultancy wrote: > Hi, > can anyone tell me whether there is a trace facility (application) > available for postgreSQL version 7.4.1 which will enable me to see all > incoming requests being made to the database (ala SQL Server)? Sure. Alter your configuration to echo queries, and then watch your log file. Alternatively, you can enable the command string statistics function, and then you get the queries in near to real time in pg_stat_activity. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] pg_restore - don?t restore. Why?
On Fri, Feb 20, 2004 at 12:24:46AM -0300, 2000info wrote: > pg_dump, ok. > pg_restore, don?t restore. Why? If you didn't use a non-ASCII format from pg_dump, you don't need pg_restore. Just use psql. A -- Andrew Sullivan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Import from Ms Excel
On Tue, Mar 16, 2004 at 03:13:38PM +0530, Kumar wrote: > Dear Friends, > > Is possible to import data from MS Excel sheet into postgres > database 7.3.4 running on Linux 7.2 Yes. I find the easiest way is to export a delimited file from Excel and use the \copy command in psql. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Import from Ms Excel
On Tue, Mar 16, 2004 at 01:42:45PM +0200, Achilleus Mantzios wrote: > Another fancy lib (although not necessarilly pgsql specific), > is the POI project from jakarta. > You can read/write M$ XLS documents from java, and > subsequently (via jdbc) manipulate pgsql tables. > > The good part is that you can dynamically generate an excel file > with arbitary content. You can do the same thing (I have done it) with Perl using the Pg and WriteExcel modules. Excel has a lot of pretty annoying limitations, though, and it's fairly dangerous to get too dependent on it for this sort of thing, as you can easily run into its limitations. I suppose it depends on how big your resulting spreadsheets are going to be. In my experience, though, the first thing that happens when you deliver someone a summary spreadsheet is, they ask you for the raw data so they can double-check it. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] conversion of postgres database to oracle
On Tue, Mar 16, 2004 at 02:13:57PM +0200, cristi wrote: > How should I convert a postgres database to oracle? Send it out to ASCII and then import it to Oracle. But if you want support for going _to_ Oracle, you probably ought to get support from Oracle people, right? A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] transaction
On Tue, Apr 20, 2004 at 09:14:48PM +0200, H.J. Sanders wrote: > > - BEGIN WORK > > - INSERT ROW > > - IF FAILED THEN UPDATE ROW > > - COMMIT WORK You can do it the other way. Begin, update; if 0 rows are updated then insert. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] EXPORT / IMPORT
On Fri, May 07, 2004 at 10:20:52AM +0200, Freddy Villalba Arias wrote: > > Is there a simple way to generate an export / import script in > PostgreSQL (i.e. a script that contains INSERT statements for every row > in a table) ??? > > COPY is NOT a good option (personal opinion). pg_dump -d or -D. Note that restoring from this is going to be a whole lot slower than restoring from a COPY based dump. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] not really SQL but I need info on BLOBs
On Thu, May 06, 2004 at 04:46:22AM -0700, Theodore Petrosky wrote: > Thanks for the reply. Are there (in your opinion) > reasons why you would choose to store the images in > the db? Transactional integrity. If there's a risk that people are going to be deleting, &c. these images, then you can end up with references in the database to files that don't exist, because the filesystem operations can't be made subject to the transactions of the database. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] typecasting numeric(18,4) to varchar/text
On Tue, May 04, 2004 at 09:35:31AM -0400, [EMAIL PROTECTED] wrote: > create table a (a numeric(18,4)); > create table b (b varchar(25)); > insert into a values(12000.43); > insert into b select (a.a)::varchar; Which version is that? Here's my session: andrewtest=# create table a (a numeric(18,4)); CREATE TABLE andrewtest=# create table b (b varchar(25)); CREATE TABLE andrewtest=# insert into a values(12000.43); INSERT 17168 1 andrewtest=# insert into b select (a.a)::varchar; INSERT 17169 1 That's on 7.4.2. You might want to try casting to text first. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] feature request ?
On Thu, Jun 24, 2004 at 12:32:59PM -0500, Jaime Casanova wrote: > > Why not disallow the ability of boolean fields to be null? Why not do it yourself? That's what the NOT NULL constraint is for. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Preserving column order when recreating table.
On Wed, Sep 15, 2004 at 02:24:45PM +0200, Stef wrote: > I'm struggling with a situation where I > want to recreate a table (in more than 30 databases) to > fix the column order (attnum sequence) and in another case, > fix different definitions for the same column in a table e.g. > amount numeric(16,2) > in stead of : > amount numeric(16,5) I'm not sure why you want to do the former, but in any case, it's possible by creating a new table which has things the way you want; select all the old data from the old table into the new table (using the column names to get everything in the order you like, of course), and then rename the old table, rename the new table to the old table name, and drop the old table if you like. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Preserving column order when recreating table.
On Wed, Sep 15, 2004 at 04:22:01PM +0200, Stef wrote: > Andrew Sullivan mentioned : > => I'm not sure why you want to do the former, but in any case, it's > Because lazy people write inserts without specifying column names. Ugh. Sorry to say so, but this sounds to me really a lot like the cure is worse than the disease. The answer to "Bob did something incredibly stupid" is not "We'll bend ourselves into contortions to support it." (This is not to say I don't sympathise. You wouldn't believe how much I do.) > => possible by creating a new table which has things the way you want; > => select all the old data from the old table into the new table (using > => the column names to get everything in the order you like, of course), > > I like this idea, but each database may have a different table definition > for the same table, and if I want to automate this, I need to figure out the > column names on the fly. That's a little trickier, but you could figure it out with some queries from pg_class and pg_attribute. > Thanks!! No problem, but I think you need to have a long talk with your developers. Possibly while holding a baseball bat or something. Furrfu. This no-column-names thing is bound to bite you some day, and probably in tender bits where such bites would be unpleasant. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Preserving column order when recreating table.
On Wed, Sep 15, 2004 at 05:07:00PM +0200, Stef wrote: > Funny you say. It looks like "Bob" had a hand in pg_dump -d , 'cause I've > many times wished there were column names specified there, too :) > (I'm talking Prior 7.4 here, dunno if it's changed already) Dunno about previous, but pg_dump -D does what you want. I think the -d switch did it this way because you can get away with that if you're also creating the schema in the same breath. I agree that "Bob's" fingers have left their grotty marks in plenty of places. > is why I didn't use this solution originally. But I figured out a way to > modify pieces of the "create table" statement to drop all the indexes > and constraints first. > > Is there an easier way around this? I doubt it. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to check postgres running or not ?
On Sun, Sep 19, 2004 at 12:25:00PM -0400, Tom Lane wrote: > ps aux | grep postmaster | grep -v grep > (or use "ps -ef" if using a SysV-ish ps). Except that on Solaris, ps -ef _always_ shows "postmaster", even for the individual back ends. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to check postgres running or not ?
On Sun, Sep 19, 2004 at 01:12:07PM -0400, Tom Lane wrote: > > Except that on Solaris, ps -ef _always_ shows "postmaster", even for > > the individual back ends. > > Right, but if you see a backend then you can figure the system is up. Oops, good point. (And in any case, on Solaris you also have the ucb ps, so it makes no difference.) A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] vacuum analyze slows sql query
On Tue, Nov 02, 2004 at 06:50:31PM -0800, patrick ~ wrote: > We have a nightly "garbage collection" process that runs and purges > any old data. After this process a 'vacuum analyze' is kicked off > (regardless of whether or not any data was actually purged). > > At this point I should mention that our customer sites are running > PostgreSQL 7.1.3; however, I am able to reproduce the issue on 7.4.2. A 7.1 system takes an exclusive lock on any VACUUM. It's the same as VACUUM FULL in 7.4. Nothing you can do to make that not be sluggish. You want to get those sites off 7.1 anyway. At the very least, you should be aware of xid exhaustion which can be prevented in 7.1 only with an initdb and complete restore. Failure to accommodate that will mean that one day your databases will just disappear. Current VACUUM certainly does impose a serious I/O load; this is the reason for the vacuum setting tweaks in 8.0. See the -hackers archives (from more than a year ago now) for (for instance) Jan Wieck's discussion of his feature and the subsequent debates. > I noticed that a freshly created db with freshly inserted data (from > a previous pg_dump) would result in quite fast results. However, > after running 'vacuum analyze' the very same query slowed down about > 1250x (Time: 1080688.921 ms vs Time: 864.522 ms). > My best guess is that there's something going on inside your function. I'd be looking for locks here, though. That makes no sense, given that you've only 78 rows being returned. BTW, this topic should probably be better pursued on -performance. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] vacuum analyze slows sql query
On Wed, Nov 03, 2004 at 10:31:33AM -0800, patrick ~ wrote: > Just to clarify, the sliggishness isn't only during the vacuum > period. There are more more serious issues during the vacuum, > but i am not touching on those. The sluggishness is persistant > throughout daily operations. Then I suspect you have two problems, not one. The locking in 7.1 will certainly cause the timeouts during vacuum, but won't explain the other items. Someone else suggested REINDEX, also, which is likely needed. But again, you'll get more useful remarks from the -performance list. > Yes, the plan is to upgrade them with new release of our product. > I didn't know about the xid exhaustion problem. I'll need to > search the mailing list archives. You can learn a bit about this in the _current_ version of the docs under regular maintenance. > Again to clarify, the output I pasted was from my standalone > PostgreSQL box. That is, it wasn't being used other than those > quries being executed. That's even worse. I certainly can't explain your results, then. You really want to move to the -performance list for that. > I don't know if you looked at my stored function, but there are > no locks in it (no explicit ones anyway). Foreign keys are one well-known area of locking surprises. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Move table between schemas
On Tue, Nov 16, 2004 at 10:02:34AM +0100, Markus Schaber wrote: > Hello, > > Is there an easy way to move a table to another schema in PostgreSQL 7.4? > > ALTER TABLE and ALTER SCHEMA don't have this options. CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable oughta work. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Move table between schemas
On Tue, Nov 16, 2004 at 02:30:09PM +0200, Achilleus Mantzios wrote: > > > > CREATE TABLE newschema.newtable AS SELECT * FROM oldschema.oldtable > > > > oughta work. > > What about indexes, constraints, sequences,etc...??? You'll have to create those too, I'm afraid. I don't know of a way to move tables from one schema to another otherwise. You could do all the dependencies with a pg_dump -t, I suspect. Not tested that, though. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Way to stop recursion?
On Fri, Nov 26, 2004 at 01:03:38PM -0800, Jonathan Knopp wrote: > UPDATE rules work perfectly for what I need to do except I need them to > only run once, not try and recurse (which of course isn't allowedby > postgresql anyway). Triggers seem a less efficient way to do the same > thing, though I understand they would run recursively too. Here's the > table structure in question: You have to do this with a trigger. The problem is that the rule is expanded inline like a macro, so you can't prevent the behaviour you're seeing. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Way to stop recursion?
On Fri, Nov 26, 2004 at 04:31:11PM -0500, Tom Lane wrote: > > Seems to me that your real problem is a bogus database layout. If there > should only be one "common" value for a parent and children, then only > store one value ... that is, "common" should exist only in the parent. Tom's answers always make me realise that I should think harder before I talk. He's right, of course: one common value means store it once. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Failed system call was shmget(key=1, size=1155072, 03600).
On Fri, Dec 03, 2004 at 09:00:53AM +, Andrew M wrote: > DETAIL: Failed system call was shmget(key=1, size=1155072, 03600). > HINT: This error usually means that PostgreSQL's request for a shared > memory segment exceeded available memory or swap space. To reduce the > request size (currently 1155072 bytes), reduce PostgreSQL's > shared_buffers parameter (currently 50) and/or its max_connections > parameter (currently 10) > > What is the best way to resolve this? max_connections = 10? Does that > figure auto increase as more users request data? What are you trying to run this on? It might be that you need to alter your kernel settings. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] failed to find conversion function from "unknown" to text
On Wed, Jan 12, 2005 at 05:52:42PM +0100, Sz?cs Gábor wrote: > Question: is there a way to tell the server to convert unknown to something > (text or varchar, don't really care), or to write such a "conversion > function"? You ought to be able to cast (e.g. "SELECT case 'a'::text. . .) A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Syntax error while altering col-type
On Thu, Jan 13, 2005 at 09:48:02AM +0100, KÖPFERL Robert wrote: > I'm a little bit perplexed now... is it really the case that pre 8.0 systems > aren't able to change col-types? It really is. In fact, the feature was (IIRC) somewhat controversial, because there are all sorts of decisions that need to be made about what to do with incompatible types. What if you change from int8 to int4? What about varchar(4) to char(4)? Just to name two simple-minded examples. See the -general and -hackers archives for plenty of previous discussion of this stuff. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Syntax error while altering col-type
On Thu, Jan 13, 2005 at 01:30:21PM +0100, KÖPFERL Robert wrote: > but that's the reason, the USING clause exists. It however still remains a Right. Please see the archives about how this was hammered out. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Column with recycled sequence value
On Thu, Jan 13, 2005 at 06:08:20PM +0100, KÖPFERL Robert wrote: > Hi, > > suppose I have a let's say heavy used table. There's a column containing > UNIQUE in4 > values. The data type musn't exceed 32-Bit. Since however the table is heavy > used 2^32 will be reached soon and then? There are far less than 4G-records > saved thus these values may be reused. How can this be accomplished? You can set the sequence up to cycle (so once it gets to the end, it wraps around to the beginning again). The keyword is CYCLE at CREATE SEQUENCE time. It defaults to NO CYCLE. One potential problem, of course, are collisions on the table, because some value wasn't cleared out. It sounds like you don't have that problem though. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Column with recycled sequence value
On Thu, Jan 13, 2005 at 03:31:54PM -0600, Scott Marlowe wrote: > Any method that tries to reuse sequence numbers is a bad idea (TM) and Why? I can think of a dozen cases where it can be useful. It just depends on the application. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Column with recycled sequence value
On Thu, Jan 13, 2005 at 03:57:04PM -0600, Scott Marlowe wrote: > What cases are you thinking of? I've seen some very limited ones, like Off the top of my head - legacy application, closed, where you can't fix the source and can't have larger than 32bit datatype, but you have another way to ensure no dups. - the queue-management item you mentioned. - optimistic cases where a short search range is more important than that a transaction doesn't fail on insert - circular number spaces (xid uses this, after all) > the time someone is thinking of doing so it's because a boss who doesn't > get it wants a pretty list with no holes in the sequence or something > equally silly. Like they have some business problem they need solved, and doing it this way is ugly but relatively cheap, and doing it the other way means replacing 4 software systems and retraining 100 people. Is it a pretty design? Probably not. Is it something that is, of all the compromises available, the best one under the circumstances? I dunno; I'd have to look at the circumstances. I think it's probably usually a good idea to avoid this, sure, but I'm not willing to make it a blanket statement. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SQL design question: null vs. boolean values
On Sat, Jan 15, 2005 at 06:40:18AM -0800, j.random.programmer wrote: > field_foo char(1) check (field_foo in 'y', 'n') > > The second choice always implies that NULL means > unknown, > whereas for the first choice, unknown is coded as 'u'. NULL actually means "unknown". SQL uses 3-valued logic: T, F, and NULL. So NULL here is a not-unreasonable choice. (Some would argue, however, that it's always better to have definite data. in which case, your three-option choice is what they'd prefer. My own view is that nullable boolean columns capture exactly the 3-value logic of SQL, so what's the problem?) > In the user form, I have a field like: > > field_bar > [] select_me > > with ONE choice, which is optional. > > Should I code this as: > > field_bar char(1) not null check (field_foo in 'y', > 'n') I'd use "boolean not null default 'f'", myself. But in any case, this is _not_ a use for NULL, because you know absolutely what the deal was: either the user selected, or else it didn't. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] PostgreSQL ignores my indexes
This is probably better on -performance, and is certainly a FAQ. But. . . On Wed, Feb 23, 2005 at 03:01:52PM +0100, Thomas Braad Toft wrote: > > Table device contains 5285 rows, tmeevent contains 834912 rows. ^^ > -> Seq Scan on tmeevent (cost=0.00..23606.12 rows=834912 width=138) > (actual time=0.04..2193.97 rows=834912 loops=1) ^^ > -> Seq Scan on device (cost=0.00..564.85 rows=5285 width=29) (actual > time=0.04..25.07 rows=5285 loops=1) > Why isn't the planner using my indexes? I tried making them as both rtree Because there's no advantage to using an index when you are fetching 100% of both tables. This is the most efficient plan. Of course, it's an open question whether you want to get 100% of both tables. But that's what you're doing, and using the index would be more expoensive than this. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Postgres performance
This sort of discussion should really go onto -performance, but I'm at pains to stomp out a common misperception. On Wed, Mar 02, 2005 at 10:45:38PM +0100, PFC wrote: > > Sure, postgres is (a bit but not much) slower for a simple > query like SELECT * FROM one table WHERE id=some number, and This is true _only if_ nobody else is writing at the same time you are. That is, for single-user or read-only databases, MySQL appears to have a really significant advantage when using the standard MyISAM table type. The problem with that table type is that it requires the _whole table_ be locked during write operations. In any case, for any sort of real database work, nobody sane would use anything except the InnoDB table type. That's a more reasonable fruit-comparison than MySQL using MyISAM. In the latter case, you may as well compare PostgreSQL to flat file writing. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Question on triggers and plpgsql
On Fri, Apr 08, 2005 at 10:36:26AM -0400, Tom Lane wrote: > AFAICS the only way that you could get into a can't-roll-back situation > is if the trigger tries to propagate the update outside the database. > For instance, the proverbial trigger to send mail: once sent you can't > cancel it. But really this is dangerous even in an AFTER trigger --- > the transaction could still be rolled back after the AFTER trigger > fires. People who know more about this will no doubt correct me, but isn't such a case crying out for LISTEN/NOTIFY instead? That is, your trigger puts the mail content into a table of mails to be sent, and wakes up the mail-sender client with the NOTIFY; the NOTIFY and the commit to the mail-it table only happen in that case if the transaction commits. And since mail is async anyway, the extra few seconds shouldn't make any difference, right? A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] getting count for a specific querry
On Fri, Apr 08, 2005 at 09:29:13AM -0400, Joel Fradkin wrote: > My understanding was this gets slower as you move further into the data, but > we have several options to modify the search, and I do not believe our > clients will page very far intro a dataset. > It gets slower because when you do an offset of 50, you have to pass through the first 50 before picking up the ones you want. If you offset 100, you scan through the first 100. &c. If you don't want to pay that, you need to use a cursor, but this causes the problem that you have to keep your cursor open across page views, which is a tricky issue on the Web. > Currently it returns all records with a count and a display of the records > your viewing like 1-50 of 470, next page is 51-100 etc. > > Is there a fast way to get the count? Not really, no. You have to perform a count() to get it, which is possibly expensive. One way to do it, though, is to do SELECT count(*) FROM tablename WHERE condition LIMIT n; or something like that. Assuming the condition is reasonably limited (i.e. it's not going to cost you a fortune to run this), you'll get the right number back if the number is < n or else you'll get n. If you have n, your application can say "viewing 1-50 of at least n records". This is something you see from time to time in this sort of application. > getting heat that my search is now case sensitive. What is the best way to > get a case insensitive search? I could use ~* or perhaps do an > UPPER(firstname) in the select etc? The upper() (or lower() -- whatever) stragegy is what I'd use. In any case, you want to make sure you put functional indexes on all such columns, because otherwise you'll never get an index scan. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Question on triggers and plpgsql
On Fri, Apr 08, 2005 at 11:35:47AM -0400, Tom Lane wrote: > What this does do for you is replace the risk of phantom emails (mail > sent but corresponding action inside the database never committed) > with the risk of duplicate emails (mail-sender sends you another one > after it restarts). In most cases I think I'd prefer the latter. Me too. Besides, you already have this risk with SMTP, because a message can be queued and accepted on the remote side when the local side goes away, so that the session is completed improperly. Depending on configuration and a bunch of painful start-up possibilities with the server, you might well get a duplicate copy of a mail transmitted later. (In the present age, given the remarkable quality of networks and mail servers everyone has, you almost never have this happen any more. But it's still strictly speaking possible.) A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] getting count for a specific querry
On Fri, Apr 08, 2005 at 04:17:45PM +, Ragnar Hafstað wrote: > On Fri, 2005-04-08 at 11:07 -0400, Andrew Sullivan wrote: > > > > SELECT count(*) FROM tablename WHERE condition LIMIT n; > the LIMIT clause limits the number of rows returned by the select, > in this case 1 row. > > maybe you mean something like: > > test=# select count(*) from (select * from a limit 2) as foo; Yes, that was stupid of me. That's what I meant, though. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] getting count for a specific querry
On Fri, Apr 08, 2005 at 03:23:25PM -0400, Joel Fradkin wrote: > Believe me I just spent two months converting our app, I do not wish to give > our queries run much slower on postgres. As mentioned I purchased a 4 proc I suspect you want the -performance list. And it'd be real handy to get some EXPLAIN ANALYSE results for the offending queries in order to help you (where "handy" is read as "necessary"). A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Update aborted if trigger function fails?
On Tue, Apr 12, 2005 at 10:55:30AM -0400, Carlos Moreno wrote: > > I guess the concern came up as result of a particular > situation, in which failing to properly process the > trigger function is not that crucial (I wanted to > update some additional information that is "optional", > and that can be reconstructed easily after discovering > that the trigger function had been failing). But in If you can do some things asynchronously, and you don't care about them very much, then you can use LISTEN/NOTIFY to do such processing. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
On Thu, Apr 14, 2005 at 06:38:14PM +0200, Andreas Joseph Krogh wrote: > Hi everybody, I have table which inly has 298 rows in it, but EXPLAIN ANALYZE > shows that PG thinks it has as much as 160057 rows AFAICT from the > EXPLAIN-output. It does have 160057 rows: > INFO: vacuuming "public.onp_web_index" > INFO: "onp_web_index": found 0 removable, 160057 nonremovable row versions > in > 206940 pages > DETAIL: 159759 dead row versions cannot be removed yet. ^^^ You no doubt have a long-running transaction keeping the rows from being recovered. Look for "idle in transaction" as a first guess. > DETAIL: 160057 index row versions were removed. > 0 index pages have been deleted, 0 are currently reusable. > CPU 2.40s/0.92u sec elapsed 286.41 sec. > > Here it says that it removed 160057 rows, doesn't it? No; that's the index row versions. > Is this normal, shall I just overlook the "rows=160057" output from EXPLAIN, > or is something wrong? > What does the line "DETAIL: 159759 dead row versions cannot be removed yet." > mean? It means that those rows were marked invalidated after some existing transaction started. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
On Thu, Apr 14, 2005 at 07:21:38PM +0200, Andreas Joseph Krogh wrote: > > So, what you're suggesting is that a restart of the webapp should make vacuum > able to delete those dead rows? Yes, but that'll only solve your problem for now. You'll have the problem again soon. What's keeping open the transaction? A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
On Thu, Apr 14, 2005 at 07:54:19PM +0200, Andreas Joseph Krogh wrote: > Any hints on how I can find out what's keeping the connection idle in a > transaction? I realize now that I should probably ask that question on the > pgsql-jdbc-list:-) Nope. That's a problem with your pool software. It's no doubt issuing "BEGIN;" as soon as it connects. It's BAD (broken as designed) in that case. You need to convince it not to do that, or else you need to go around and kill -2 such connections from time to time. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
On Thu, Apr 14, 2005 at 03:04:47PM -0400, Alvaro Herrera wrote: > > It is a Postgres limitation as well. We _could_ make the server "really > start the transaction" at the point the first query is issued instead of > when the BEGIN is issued. In fact this problem would go away if we did > that. Yeah; I seem to remember Tom Lane suggesting such an approach at one point. I had the vague idea it might happen in 8.0, but for some reason I think it didn't. (Anyway, that's probably off topic for this list.) A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
On Thu, Apr 14, 2005 at 09:57:03PM +0200, Andreas Joseph Krogh wrote: > Is it safe to issue a "kill -2 ", will the > connection-pool reconnect? The kill -2 will definitely work to shut the connectiond down cleanly. Actually, I should put that more precisely: you should send SIGINT. I don't know of any systems where that's not 2, but I suppose it's logically possible. As for the reconnection, it'll depend entirely on what your pool does when its connections are closed. You'll have to test it. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Nested Sets
On Sat, Apr 16, 2005 at 06:28:27PM +0400, A. Kulikov wrote: > btw, do stored procedures (user functions in other words) have to > implement table locking or be carried out inside a transaction such > that the nested set indexes remain intact? They're always in a transaction. Everything in Postgres is always inside a transaction (although it might be a transaction of a single statement). You shouldn't need to do anything special around table locking. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query about SQL in PostgreSQL
On Tue, Apr 19, 2005 at 11:59:58AM +0200, KÖPFERL Robert wrote: > Postgres has the weird behavour to compare identifies case sensitive BUT to > downcast any non-quoted identifier inside an SQL statement. > So it is reccomended to just use lower case (for readability) Or never double-quote identifiers. I sort of don't believe exactly the original poster's report, however, because the error message wasn't right no matter what. I'd like to see a real session transcript. Also, this probably belongs on -general. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle?
On Thu, May 12, 2005 at 01:07:00PM -0600, [EMAIL PROTECTED] wrote: > > Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If > so, we can write the following query: No. What is the purpose of your query? You could use ORDER BY and LIMIT..OFFSET to do what you want. I think. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as
On Tue, May 17, 2005 at 03:43:32PM -0300, Alain wrote: > > I tried using both the name and the primary key (with a combined index), > to get faster to the record I want, but I was not sucessfull in building > a where clause. > > I would appreciate any help, in fact this is my primary reason for > joining this list ;-) Well, then, table schemas, data distribution, EXPLAIN and EXPLAIN ANALYSE output, and some statement of what you're trying to get out is likely what we need to see. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] cursor "" does not exist
On Wed, Jun 15, 2005 at 06:45:56PM -0400, Vsevolod (Simon) Ilyushchenko wrote: > While those that fail look like this: > > Request select * from material_pkg.ListCautions_fcn($1,$2) as result B > Response result C SELECT > > Note that the successful ones contain strings "S_1" and "BEGIN", and the > failed ones do not. However, there also are successful queries without > these strings, but they are not "select *" queries. Eg, > I have a feeling it's some idiosyncrasy that I'm not familiar with. Does > "BEGIN" refer to the beginning of a trasaction? Yes. A transaction looks like this: BEGIN; SQL1; SQL2; COMMIT; But it also looks like this: SQL3; because in PostgreSQL, everything is always automatically in a transaction, and a bald SQL statement is just a transaction one statement long. With autocommit off, I think what you get is no COMMIT, but you still get the bald transaction. What you really need is to make sure you're starting a multi-statement transaction every time. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Make COUNT(*) Faster?
On Fri, Jul 08, 2005 at 08:07:27AM -0700, Steve Wampler wrote: > Bruno Wolff III wrote: > > No, it is an exact count. > > Yes, for the transaction, but it's an approximation of the number of > tuples in the table - which is probably what the people who worry about > its cost are more interested in (an approximate count for the table). You seem to have the wrong idea of "tuples in the table" here. You need to think harder about MVCC visibility rules. Given MVCC, there isn't really a "view from nowhere" in the system -- there's just the idea of what tuple visibility. For a little more, you might want to look at the presentation Tom Lane made for this: http://www.postgresql.org/files/developer/transactions.pdf A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: **SPAM** [SQL] Faster count(*)?
On Tue, Aug 09, 2005 at 10:49:14PM -0400, Tom Lane wrote: > > Current best practice is to run the explain and parse out the "rows" > figure using a perl (or axe-of-choice) regexp, though we could be > persuaded to supply a simpler API if there's enough demand for it. FWIW, this was another one of those things I must have heard a dozen times at OSCON. I suspect the simpler API would be popular, particularly since post-8.0 the estimates are more reliable than they used to be. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] DATESTYLE and 0000-00-00
On Thu, Oct 06, 2005 at 09:01:22AM -0400, Joshua Kramer wrote: > I have my DATESTYLE set to ISO MDY. > > When I try to create a table with a default date of -00-00, psql says > that this is an invalid date. Why, and can (or how can I) get it to > accept -00-00 as a valid date? You can't. There's no year 0. As the docs say, if you don't like that, please complain to the Vatican; we can't help you. If you are trying to say, "Date unknown," you can use NULL (and maybe add another field to indicate whether the NULL means "no date" or "date unknown"). Note that your datestyle has nothing to do with this: Postgres won't let you put an invalid date into a date field. This also has the happy consequence that you can't accidentally create leap years where there aren't any: andrewtest=# SELECT '2005-02-29'::date; ERROR: date/time field value out of range: "2005-02-29" If you really think you have to have such dates, you are probably mistaken. If you have to have them because some application you can't control relies on them, you could store them in a text column, and coerce them to dates when you select (but be prepared for the errors you'll get). But my suggestion is that if you really think you have to have such dates, you should redesign (which might mean "replace") your application. Anything that uses dates with a year 0 is so fundamentally mistaken about how dates work that I wouldn't trust it. -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] pg, mysql comparison with "group by" clause
On Thu, Oct 13, 2005 at 12:24:55AM -0400, Greg Stark wrote: > Well the "constants and the like" are precisely the point. There > are plenty of cases where adding the column to the GROUP BY is > unnecessary and since Postgres makes no attempt to prune them out, > inefficient. But inefficient pruning is an optimiser problem, not something that should lead one to invent a whole syntax change that (a) violates the standard and (b) is at least somewhat opaque in meaning. The right thing to do is surely to make the optimiser smarter, no? (Think, "What does DB2 have that we don't?") A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Update timestamp on update
On Wed, Oct 12, 2005 at 10:52:04PM -0400, Tom Lane wrote: > the documentation of the "core" system shouldn't rely on them ... but > that leaves us presenting C-code triggers as the only examples in > chapter 35. There is a paragraph in there suggesting you go look at > the PL languages first, but obviously it's not getting the job done. > > Anybody have a better idea? It could just be made a little friendlier, I think. At the beginning of the trigger chapter is this: --snip-- This chapter describes how to write trigger functions. Trigger functions can be written in C or in some of the available procedural languages. It is not currently possible to write a SQL-language trigger function. --snip-- We could just add a little note by way of modification. Here's a (somewhat verbose, I fear) suggestion: --snip-- This chapter describes how to write trigger functions. Trigger functions can be written in C or in some of the available procedural languages. This chapter deals only with functions that are written in C. If you are unfamiliar with C, you may want also to look at the chapters on procedural languages, because there are some examples there that may be easier for you to understand. To use a procedural language for a trigger, you will need to install that language; see the relevant chapter for instructions on how to do so. It is not currently possible to write a SQL-language trigger function. --snip-- A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] pg, mysql comparison with "group by" clause
On Thu, Oct 13, 2005 at 02:26:58PM -0400, Greg Stark wrote: > Scott Marlowe <[EMAIL PROTECTED]> writes: > > could run this query at the same time and get different data from the > > same set and the same point in time. > > I'm pretty unsympathetic to the "we should make a language less powerful and > more awkward because someone might use it wrong" argument. That's not what Scott's saying. Scott is saying that the syntax you're talking about is _formally wrong_. That's surely not "more powerful", except in the sense that stepping on a land mine is more powerful than many other ways you could shoot yourself in the foot. > path. In an ideal world the user should be guaranteed that > equivalent queries would always result in the same plan regardless > of how they're written. And again, I say it sounds like you're actually arguing for "the optimiser needs to get better". Special-purpose, formally wrong syntax is surely not better than making the optimiser get the right syntax right every time, is it? A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] cast
On Fri, Oct 14, 2005 at 01:08:43PM -0500, Judith Altamirano Figueroa wrote: > Hello I have a query that ran in 7.0.2, but in 8.0.1 does not, the query > is the next: It'd help if we could see the table schema and the error message. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] why vacuum
On Thu, Oct 27, 2005 at 10:22:41AM +0200, Mario Splivalo wrote: > offers no replication at all, you need to use slony (wich is also a poor > replacement for a wannabe replication), or some other commercial > products. What about 2PC? What about linking the databases from Slony is in fact a community-supported system; so I don't know why you think that amounts to "no replication at all". And since this is a community-supported system, it'd be nice if you said why it's a "poor replacement for wannabe replication". What's wrong with it? > Btw, I 'ported' the merge replication from MSSQL to postgres. It > basicaly adds triggers to every table that is 'published' for > replication. There is a separate table to store and calculate the change > differences from several servers (so you could do update on any of the > servers and change will be propagated to the others). I'm missing 2PC > badly here, I wrote some stupid python 'thingie' wich should act as 2PC > serializer, but that's slow as hell. And triggers slow down postgres > quite a bit. This is interesting. Care to package it up for others, or write a proof-of-concept outline for the lists or General Bits or something like that? This is a different sort of replication people are asking for. Note that you get 2PC in the next Postgres release. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] why vacuum
On Thu, Oct 27, 2005 at 02:34:13PM +0200, Mario Splivalo wrote: > > Postgres itself offers no replication. You could achive some sort of > replication by restoring the parts of WAL files, but that's rather > inconvinient. Then, if you want to replicate your data in any way, you Well, AFAIK Oracle itself offers no replication, either. If you want it, you have to buy a license for it. Which means it's an add-on. Heck, most Linux distributions' kernels don't offer support for network cards: they're an add-on. We call them modules. (In case it's not clear, I don't buy the "itself/add-on" distinction. The point is that the whole system works together. PostgreSQL most definitely offers replication. In fact, you can get warm-standby with WAL shipping, or read-only capabilities with Slony or some other tools.) > like that :) I am sorry. I was just addressing the issue where > replication to one means 'just move my data here', and at others it > means 'merge my data'. Yes: this multiple-meaning "replication" word causes a great deal of confusion. But just because one person has need A does not mean that need B isn't a real one. My employer, Afilias, sponsors the Slony work, in the direction of our needs. We did not initially have a many-write-nodes scenario in mind, and we had a pressing need for a "single master" system. So that's the itch we scratched. > I'll be glad to, I'm just not that familiar (in fact, i'm not familiar > at all) with the 'proof-of-cocept' or 'General Bits' terms, so if you > could http-redirect me, I'll be most thankfull. Proof of concept is just a description of what you did, how it worked, design and limitations, &c. Post it to the lists (uh, -general or maybe -hackers, I suppose), or put it on a web page or whatever. General Bits is a fairly regular column that Elein Mustain puts out. I bet she'd include a submission on this topic, although you'd have to ask her. You can find GB at <http://varlena.com/varlena/GeneralBits/>. > Mentioning the 2PC, is it available in pg8.1beta4? Should be. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] why vacuum
On Fri, Oct 28, 2005 at 01:09:43PM +0200, Mario Splivalo wrote: > I'll go trough my code, it's been a while since I touched it, I'll write > some documentation and I'll inform the comunity. Thnx for the pointouts. And thank _you_ for proposing to do this. If everyone contributes their discoveries and improvements, we all benefit. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] how to do a find and replace
On Thu, Nov 17, 2005 at 02:51:05PM -0800, Dawn Buie wrote:
> I'm just confused about how I should write code to update the selected
> items to remove the 'v.'
>
> Would I use substring? An example would be much appreciated.
You need a combination of overlay and location. The following will
work if you always have _only_ 'v.' in there in the one place you
want it. If it is too early in the string, this _won't_ work:
andrewtest=# SELECT version();
version
---
PostgreSQL 7.4.7 on i386-pc-linux-gnu, compiled by GCC
i386-linux-gcc (GCC) 3.3.5 (Debian 1:3.3.5-12)
(1 ligne)
andrewtest=# SELECT * from mytable ;
location
-
/0/v.myimage.jpg
/0/v.myotherimage.jpg
/0/v.myvthotherimage.jpg
/0/v.myvthotherv.image.jpg
(4 lignes)
Note that I've fiddled with the initial spacing here, in case that
hasn't been totally consistent either. This is for illustration.
andrewtest=# select overlay(location placing '' from (position('v.'
in location)) for 2) from mytable;
overlay
---
/0/myimage.jpg
/0/myotherimage.jpg
/0/myvthotherimage.jpg
/0/myvthotherv.image.jpg
(4 lignes)
Note here that the _second_ 'v.' in the last entry doesn't get pulled
out. These functions work on the first hit, so this is as expected.
But if you have something like '/v.0/v.myimage.jpeg' you'll not lose
the 'v.' you want, I expect.
A
--
Andrew Sullivan | [EMAIL PROTECTED]
The whole tendency of modern prose is away from concreteness.
--George Orwell
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] idea for a geographically distributed database: how best to implement?
On Fri, Nov 18, 2005 at 09:09:24AM -, Andy Ballingall wrote: > That's very interesting! I've not used slony yet, so I'll setup some tests > and try exactly this mechanism. If you do this with Slony, and have any success, I know that the folks on the Slony list would dearly like to hear about it (especially if you can provide details of how you did it). If you get this working in a production system, I can think of more than one conference that would _also_ like a paper on it. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] does indexes need statistics?
On Thu, Nov 24, 2005 at 07:43:28PM +0100, Mauricio Fernandez A. wrote: > Is it necesary the statistics be enabled when I create indexes or to > use them? Uh, yes, but you can't turn them off. I suspect I don't understand your question. Do you mean the "runtime statistics" section of the configuration file? If that's what you mean, then no. Those aren't the same thing as the statistical data used by the planner; the latter is updated when you run "analyse". A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Does VACUUM reorder tables on clustered indices
On Sun, Dec 18, 2005 at 10:08:22PM -0500, Tom Lane wrote: > > Just for the record, that behavior is seriously broken: it violates > MVCC if any of the deleted tuples are still visible to anyone else. Does it remove tuples that VACUUM FULL wouldn't? I always thought it did essentially the same thing? A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to Force Transactions to Process Serially on A Table
On Mon, Dec 19, 2005 at 08:46:39AM -0500, Lane Van Ingen wrote: > Thanks, that helped. > > Please answer 2 other related questions, if you would: > (1) What must I do to 'Be prepared for serialization failures' (how to > detect, how to handle)? > Do you have a sample? You'll get an error. You should read this section of the docs: http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html#XACT-SERIALIZABLE > (2) Also, I am assuming that the effect of all of this is to just force > transactions to wait in line > to be processed serially, and that it only lasts as long as the pl/pgsql > transaction block or > the next COMMIT. No. The effect is to _emulate_ the case where the set transaction is processed serially. Importantly, on a high-concurrency database, you tend to get serialization failures. Moreover, it is not true mathematical serialization. See section 12.2.2.1 for details in case you need such a feature, in which case you're back to explicit locking. A > > -Original Message- > From: Achilleus Mantzios [mailto:[EMAIL PROTECTED] > Sent: Monday, December 19, 2005 9:25 AM > To: Lane Van Ingen > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] How to Force Transactions to Process Serially on A Table > > O Lane Van Ingen ?? Dec 19, 2005 : > > > I am using PL/SQL functions on Windows 2003, version 8.0.1. > > > > I have not used explicit PostgreSQL LOCKing before, but I need some advice > > on how properly to use some explicit locking. I think that duplicate key > > violations I am now getting are the result. > > > > I want to force transactions being used to update a table to be processed > on > > a first-come, first-served basis. I want my Pl/sql function to execute to > > completion on each transaction before another starts. > > > > Need some advice on how to do this. From what I can read in the docs, it > > looks like I need to solve the problem by using the following, but doing > so > > gives me an SPI_execution error: > > BEGIN; > > LOCK IN SHARE ROW EXCLUSIVE MODE; > > lock adns_report_hour_history in share row exclusive mode; > > INSERT INTO VALUES ... - or - UPDATE SET > > COMMIT; > > Will this make the next transaction wait until the previous transaction > has > > completed? Do I need to set any config parameters? > > > > If you can include an actual code snippet in the response, it would help > ... > > what you want is to set the xaction isolation level. > > BEGIN; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > .. > COMMIT; > > Be prepared for serialization failures though. > > > > > > > > > ---(end of broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > > > > -- > -Achilleus > > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Does VACUUM reorder tables on clustered indices
On Wed, Dec 21, 2005 at 06:36:45PM -0500, Chris Browne wrote: > If I'm considering clustering the Slony-I "sl_log_1" table, forcing it > into memory *is* something I'll consider doing in order to minimize > the time that would-be writers are blocked from writing... Given what Tom Lane recently reported (and assuming I understood his remarks), I think it's a Mighty Bad Idea to CLUSTER sl_log_1. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] INSERT waiting under heavy load
On Fri, Jan 06, 2006 at 04:49:09PM -0500, Tom Lane wrote: > performance risk in the sort of scenario you are describing. > (I'm not sure why it would manifest as transactions showing "INSERT > waiting" state though.) It's because of I/O. When you have a large number of updates, the planner always assumes an indexscan (correctly), but you end up scanning megabytes of dead tuples. With a large number of open transactions, most of the time VACUUM can't recover the space. I agree with what Tom said earlier in this thread: the design is guaranteed to lose. A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Changing the transaction isolation level within the stored
On Thu, Jan 26, 2006 at 10:42:54AM +0100, Markus Schaber wrote: > AFAIK, in PostgreSQL normal SQL commands cannot create deadlocks at all, > the only way to introduce deadlocks is to issue LOCK commands to take > locks manually. And for this rare case, PostgreSQL contains a deadlock > detection routine that will abort one of the insulting transactions, and > the others can proceed. You can too. Consider this: t1t2 BEGIN;BEGIN; UPDATE table1 SET col1= UPDATE table2 SET col1= col1+5; (SELECT col3 FROM DELETE FROM table2 WHERE table3); col1 = col1+6;UPDATE table1 SET col1 = col1 +5; COMMIT; COMMIT; Suppose these are concurrent. The problem here is that each transaction need something in the other transaction either to complete or rollback before the work can proceed. So one of them has to lose. A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Changing the transaction isolation level within the stored
On Thu, Jan 26, 2006 at 01:51:27PM +0100, Markus Schaber wrote: > Hmm, are you shure that this is correct? The delete will always delete 0 > rows. Quite, and no it won't. The contrived example is actually a simplification of a case one of our developers implemented. The conflict is on the updates. Two concurrent transactions likely wouldn't be enough to cause it on a fast system, but multiple ones for sure will. The problem is that the updates have to wait for one another to complete in order to know what result they can use, but then the _other_ contention on the other table causes them to have to wait for one another there. I don't think anybody would have gone to the trouble of putting in deadlock detection if the only way to deadlock was to trip over yourself with manual locking: presumably, if you're issuing locks by hand, you either know what you're doing or get what you deserve. > Depending on the transaction isolation level and exact timings, > colliding queries may lead to different results or even one transaction > aborted, but there is no deadlock under MVCC. > > Not needing such locks is the whole point in using MVCC at all. I think you don't have a clear idea of what locks are necessary for updates. Write operations on a row must block other write operations on the same row. If more than one transaction needs the same kinds of locks on two different tables, but attempts to get those locks in the opposite order, you are all but guaranteed a deadlock. MVCC helps, but it can't avoid locking the same data when that data is being updated. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Does PostgreSQL support job?
On Wed, Feb 01, 2006 at 05:53:52PM -0500, Daniel Caune wrote: > I try to find in the documentation whether PostgreSQL supports job, but > I miserably failed. Does PostgreSQL support job? If not, what is the I don't know what "job" is, but it sounds like you want "cron" (since you mention it). Yes, use that. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Syntax for "IF" clause in SELECT
What you want is the SQL-standard CASE statement. A On Wed, Feb 08, 2006 at 06:06:10PM -0800, Ken Hill wrote: > This has been something I've been trying do so that I can do some column > comparisons as part of "data-cleaning" work. I'll let you know if this > helps me accomplish my task! > > On Wed, 2006-02-08 at 15:20 -0800, Bricklen Anderson wrote: > > > [EMAIL PROTECTED] wrote: > > > Greetings, > > > > > > the following is an MySQL statement that I would like to > > > translate to PostgreSQL: > > > > > > Could someone point me to a documentation of a coresponding > > > Systax for an "IF" clause in the a SELECT, > > > or is the some other way to do this > > > > > > select > > > if(spektrum is null,' ','J'), > > > if(s19 is null,' ','J'), > > > if(OhneGrenze is null,' ','J'), > > > from namen; > > > > > > > > > Do I need to create my own function to allow this behaviour! > > > > > > > > > my best regards, > > > > > > Stefan > > > > use CASE > > > > Since I'm not a user of MySQL, and if I'm reading your query correctly: > > try > > select (CASE when spektrum is null then 'J' else spektrum end), > > ... > > > > or if you are just trying to replace nulls, then try COALESCE > > > > ---(end of broadcast)--- > > TIP 1: if posting/reading through Usenet, please send an appropriate > >subscribe-nomail command to [EMAIL PROTECTED] so that your > >message can get through to the mailing list cleanly -- Andrew Sullivan | [EMAIL PROTECTED] It is above all style through which power defers to reason. --J. Robert Oppenheimer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Modifying postgresql.org file
On Wed, Feb 15, 2006 at 08:15:46AM -0800, Ken Hill wrote: > It has been suggested to me to increase my work_mem to make queries > preform faster. I believe I do this in the 'postgresql.org' file. I > seem to have two of these files: > > /etc/postgresql/7.4/main/postgresql.org > /usr/share/postgresql/7.4/postgresql.conf.sample Where did you get this version of Postgres? The main config file should be $PGDATA/postgresql.conf. Anything else is probably the result of your system's packaging having done some magic. But in any case, unless I'm misremembering, the work_mem setting isn't in 7.4. You can check the docs on postgresql.org. There's a whole section on the configuration variables, and manuals are available for several releases back. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Modifying postgresql.org file
On Wed, Feb 15, 2006 at 08:35:31AM -0800, Ken Hill wrote: > > This is how Ubuntu installed postgresql via it's synaptic package > manager. Ok, then I suspect you need to consult the Ubuntu docs about what they did differently. If my understanding of Ubuntu is correct, that should be under /usr/share/doc/. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to force PostgreSQL using an index
On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote: > Hi, > > > > Is there a way to force PostgreSQL using an index for a SELECT > statement? I just want to confirm that the index PostgreSQL decides to > use is better than the index I supposed PostgreSQL would use (I already > analyze the table). Your best bet is to do set enable_indexscan=false; and then do the EXPLAIN ANALYSE for your select. You might also find that fiddling with other settings affects the planner's idea of what would be a good plan. The planner is sensitive to what it thinks it knows about your environment. A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] How to force PostgreSQL using an index
On Wed, Feb 15, 2006 at 05:26:57PM -0500, Daniel Caune wrote: > I have an index on EVENT_DATE_CREATED that does it job. But I though > that I can help my favourite PostgreSQL if I create a composite index on > EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED > is more dense that EVENT_NAME). > > PostgreSQL prefer the simple index rather than the composite index (for > I/O consideration, I suppose). I wanted to know how bad the composite > index would be if it was used (the estimate cost). You could do that by turning up the data the planner spits out, to see why it picks this index. It doesn't do so automatically, I think: I _think_ it's cost based (Tom will probably chime in here and remind me how little I know). My bet is that the second column isn't adding enough selectivity to help. One thing that might affect this is to fiddle with the SET STATISTICS settings on the column(s) in question. You might find that as the samples get better, your index turns out to be usefully selective, and it gets chosen. But to answer your question, no, you can't tell it "use index foo". A -- Andrew Sullivan | [EMAIL PROTECTED] This work was visionary and imaginative, and goes to show that visionary and imaginative work need not end up well. --Dennis Ritchie ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Using EXPLAIN-ANALYZE
On Thu, Feb 16, 2006 at 12:07:10PM -0800, Kashmira Patel (kupatel) wrote: > Hi all, >I am pretty new to using Postrgres, and have been looking at the > messages in this forum for a while. I have noticed that the use of > EXPLAIN-ANALYZE is recommended quite a lot. I read the Postgres docs, > but am not quite sure I understand how this works. Is there some > tutorial or any other documentation how this can be used? Well, here's the short version: EXPLAIN [query] tells you what the planner _thinks_ it should do. EXPLAIN ANALYZE [query] tells you what the planner thinks it should do, and also executes the query and reports back how long every step took, how many rows were returned, &c. (For this reason, you want to wrap it in BEGIN;...;ROLLBACK; if it changes data.) For more, see the EXPLAIN EXPLAINED tutorial on techdocs.postgresql.org (<http://techdocs.postgresql.org/oscon2005/robert.treat/OSCON_Explaining_Explain_Public.sxi>) A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Using EXPLAIN-ANALYZE
On Thu, Feb 16, 2006 at 01:08:40PM -0800, Kashmira Patel (kupatel) wrote: > So I would have to put in lots of rows of data in the table before using > the explain command? Well, no, but you won't get useful information without it. PostgreSQL has a cost-based optimizer. The query plan is affected by the nature of your data. That's what the ANALYZE command (on its own, or with VACUUM) is for. A > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan > Sent: Thursday, February 16, 2006 12:39 PM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] Using EXPLAIN-ANALYZE > > On Thu, Feb 16, 2006 at 12:07:10PM -0800, Kashmira Patel (kupatel) > wrote: > > Hi all, > >I am pretty new to using Postrgres, and have been looking at the > > messages in this forum for a while. I have noticed that the use of > > EXPLAIN-ANALYZE is recommended quite a lot. I read the Postgres docs, > > but am not quite sure I understand how this works. Is there some > > tutorial or any other documentation how this can be used? > > Well, here's the short version: > > EXPLAIN [query] tells you what the planner _thinks_ it should do. > > EXPLAIN ANALYZE [query] tells you what the planner thinks it should do, > and also executes the query and reports back how long every step took, > how many rows were returned, &c. (For this reason, you want to wrap it > in BEGIN;...;ROLLBACK; if it changes data.) > > For more, see the EXPLAIN EXPLAINED tutorial on techdocs.postgresql.org > (<http://techdocs.postgresql.org/oscon2005/robert.treat/OSCON_Explaining > _Explain_Public.sxi>) > > A > > -- > Andrew Sullivan | [EMAIL PROTECTED] > In the future this spectacle of the middle classes shocking the avant- > garde will probably become the textbook definition of Postmodernism. > --Brad Holland > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Update in all tables
On Wed, Feb 22, 2006 at 11:59:06AM -0600, Judith Altamirano Figueroa wrote: > Hello everybody I need to update a field with the same value in the > tables of my data base but this field exists in almost all tables and > has the same value, I don't want to code a script, so my question is if > there is some way to update that field with a query and affects all the > tables that contain the field? No, sorry. While we're at it, though, if you have the same field in several tables, it's a good sign that your database is badly normalised. You shouldn't have to update more than one table (which is why there isn't a way to do this automatically). A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about index scan vs seq scan when using count()
On Thu, Feb 23, 2006 at 01:44:43PM -0800, Kashmira Patel (kupatel) wrote: > My understanding of this statement is that if I use count() without a > WHERE clause, then essentially, it is applied to the entire table and > hence requires a seq scan. > But it should not require a seq scan if I have a condition. It may not require it, but it might select it anyway. > For example: I have a table vm_message with an index on column msgid. > Will the following do a sequential scan or an index? > > select count(*) from vm_message where msgid = 3; How much of the table is that? How many rows? EXPLAIN ANALYSE will tell you if you have the right plan (estimate vs. actual). The real question is, are you sure an indexscan is faster? A -- Andrew Sullivan | [EMAIL PROTECTED] It is above all style through which power defers to reason. --J. Robert Oppenheimer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about index scan vs seq scan when using count()
On Thu, Feb 23, 2006 at 02:25:34PM -0800, Kashmira Patel (kupatel) wrote: > > [Kashmira] I did do an EXPLAIN ANALYZE as well, it also showed a > sequential scan. The table has about 600+ rows, with around 6 of them > matching the given id. Wouldn't an index scan be faster in this case? EXPLAIN ANALYSE will always choose the same plan as EXPLAIN. The difference is that it shows you the estimate and actual. I am surprised you're getting a seqscan for that, though. Is there something about the index you're not telling us? Is your system tuned correctly? Maybe 600 rows is so small that a seqscan's just as fast. A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Can't connect to the db
Looks like your database didn't start up. You should look at your server logs to see why. A On Fri, Feb 24, 2006 at 04:17:14PM -0600, Judith wrote: > Hello every one I was using my db with an application in perl, but > something occurs with the application and the pc got frozen and I had > to restart my PC, when it boot again I coudn't restart the psql service > when I do the psql mydb it shows me the next error, the error is in > spanish but it says something like: > >psql: can't connect to the server: there is no file or directory > >is the server running local and accepting conecctions in the Unix > domain socket «/tmp/.s.PGSQL.5432» > > do I have to reinstall again the postgres or can I do something else? > > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
On Wed, Mar 01, 2006 at 01:30:23PM -, Simon Kinsella wrote: > I'm trying to achieve this with a BEFORE DELETE trigger, which would set the > 'marked_for_deletion' field to TRUE before attempting the delete proper. > Then if the DELETE fails the row would still be tagged and I'd be happy. > Problem is, when the DELETE operation fails with a RESTRICT VIOLATION error > the entire operation is rolled back, including the BEFORE triggers, leaving > me back where I started. Yes. In 8.1, you could use a subtransaction for the DELETE, which I think would allow you to rollback at that point and still leave the UPDATE in place. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Replication - state of the art?
On Wed, Mar 01, 2006 at 09:51:46AM -0800, Bryce Nesbitt wrote: > switch over and rebuild the DB. "No-lost transaction" is far more > important than switch time. You can't guarantee that without two phase commit, no matter what you do. Log shipping doesn't require you to have an active database running on the origin (slony-1 does, which is one of its potential drawbacks). But that won't help you if a transaction committed at the instant an earthquake hit your datacentre, wiping it out. You can't get the data off the failed origin no matter what. > Anyone here using replication or transaction journaling? Has it proved > reliable, easy to maintain? Define "easy". Every possible replication system is going to have slightly grotty corners into which you find yourself wandering. The question is merely whether the room is octagonal or merely rectangular. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Change date format through an environmental variable?
On Wed, Mar 01, 2006 at 12:32:26PM -0500, Mark Fenbers wrote: > have no access the SQL). Is this possible? I know about the DATESTYLE > variable, but that seems to work only within a query transaction, and > has no effect if trying to set it as an envvar. No, it won't work as an environment variable. You can alter the postgresql.conf file, though. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Change date format through an environmental variable?
On Wed, Mar 01, 2006 at 02:20:57PM -0500, Mark Fenbers wrote: > I found PGDATESTYLE that solves my problem, but ever since, I've been > looking for a comprehensive list of environmental variables that Pg > recognizes, but haven't been able to find such a list in any of the > books I looked in or the man pages. Anyone know where I can find such a > list? That's a client variable, and it works for libpq-based clients that don't do something funny with them (none of them ought to, but one can't guarantee others' programs). So you're not modifying for other clients, AFAIK, just for you. (If that's what you want, well, good, but it's important to know what it does.) The list for 8.1 is in the docs: http://www.postgresql.org/docs/8.1/interactive/libpq-envars.html A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Replication - state of the art?
On Wed, Mar 01, 2006 at 11:28:06AM -0800, Bryce Nesbitt wrote: > Actually let me loosen that a bit: we don't need two phase commit. We > can loose the most recent transaction, or even the last few seconds of > transactions. What we can't survive is -- on the day of the emergency > -- a long and complicated DB rebuild with mistakes and hard-to-debug > data issues. Then I suggest you use Slony-I. While it is not plug and play, the thing it _is_ designed to handle reasonably well is failover and (better) switchover. Most systems plan to solve that piece of functionality later, with a script or something, at which point it is apparent that setting up failover or swichover to be anything approaching safe is actually very tricky. (Log shipping is probably not in this category, but AFAIK the promote-to-live support for a standby database copy is still not all built by anyone. If you like rolling your own, however, it might be your answer.) > There's no fire creating demand for replication, so there is little time > budget. > So is there a sort of padded, no-sharp-corners, playroom that gets us > 90% of the way there? The "no budget" remark here is what makes me strike CMD's Mammoth Replicator off the list. But I'm sure their administration tools are far sweeter than the admittedly hackish ones that Slony currently delivers out of the box. > nightly) into something more reasonable (like 500 milliseconds). But > risk -- of data corruption -- > and time --too much-- will can the project. Another big reason to use a live-standby system like Slony is that once you have the extra database online, you suddenly think of all sorts of nifty queries you can move there without destroying your production performance. Be careful not to get addicted, is all. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Replication - state of the art?
On Wed, Mar 01, 2006 at 04:15:18PM -0600, Jim C. Nasby wrote: > You could also use WAL shipping and some PITR trickery to keep a 'warm > standby' database up to date. How far behind it falls is up to you, > since you'll be periodically syncing the current WAL file to the backup > machine. Do the sync once a minute, and at most you lose 60 seconds of > data. Right. But you have to write all of that, and write the failover scripts, and such like. And the OP suggested that there wasn't time budget for that. But it'd work. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
On Wed, Mar 01, 2006 at 08:41:20PM -, Simon Kinsella wrote: > Ok thanks, will check this out. Is that the same as savepoints, or something > different? (am using 8.1.2) Yes, same thing. > At the moment I'm investigating using a rule (rewrite the DELETE as an > UPDATE to set the flag, then use an AFTER UPDATE trigger to attempt to > delete the row if the flag was set). Not sure if it's going to work but if > so I'll post back. In a message you sent that I read after I sent mine, you also said you had to be able to handle deletes from the table with CASCADE. My suggestion won't work for that, I don't think (but it might be worth a try). The only other thing I can think of is just flag everything, and use a daemon to go around and perform the actual deletes for you. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
On Fri, Mar 03, 2006 at 12:19:22AM -, Simon Kinsella wrote: > Hi Andrew, > > I think I may have cracked this problem by combining a RULE ON DELETE which > calls a function instead of the standard DELETE op. No triggers. It was a Ah. Yes, likely. Yeah, you can't do that. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Sequential scan where Index scan expected (update)
On Thu, Mar 02, 2006 at 11:28:49PM -0800, Bryce Nesbitt wrote: > Can anyone help figure out why? Well. . . > > > demo=# \d xx_thing > -+-+--- > thing_id | bigint | not null ^^ > demo=# explain update xx_thing_event set thing_color='foo' where ^^ . . .you haven't actually given us the right schema here, but if I had to guess, I'd say you could put this > thing_event_id=1; ^ in quotes. The automatic int4-int8 coercion is probably your problem. Also > -- > PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 ^ you need to upgrade PostgreSQL Right Now. There are serious problems with earlier 7.4 releases. Get the latest, or risk data corruption. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Sequential scan where Index scan expected (update)
On Fri, Mar 03, 2006 at 11:35:55AM -0800, Bryce Nesbitt wrote: > Yup that's it. But this project uses (ugh) Hibernate. I can't change > it. I may have to change > from BIGINT primary keys to INT. > Well, you could upgrade from 7.4. > Also: > Any hints on the table statistics? I turn them on, in a session, as > user postgres, but get nothing: You have to SIGHUP to get those to take effect, IIRC. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] "could not open file" issue
On Mon, Apr 24, 2006 at 12:17:07PM -0400, Daniel Caune wrote: > Hi, > > Is there any way to solve the following issue without dropping the > table? I doubt you'll be able to drop the table. I think you have some sort of corruption. Assuming your hardware is good, you maybe oughta take this over to -general to see if the wizards can identify your problem. (But check your hardware first.) A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] any one can help me how to get multiple rows in postgresql using arrays in functions
On Thu, Apr 27, 2006 at 04:57:26PM +0530, Penchalaiah P. wrote: > any one can help me how to get multiple rows in postgresql using arrays > in functions What is it you are trying to do? A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] ERROR: plan should not reference subplan's variable
On Wed, May 03, 2006 at 08:08:22AM +0300, Catalin Pitis wrote: > Hi Tom > > Could you tell me when will 8.1.4 be released with the problem solved? In general, you can't rely on hard release dates in a free software project. Given that's in the STABLE branch, however, you could get it directly from CVS and build it yourself. Directions for doing this are on the website (possibly in the developer's section, note). A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] i am getting error when i am using copy command
On Wed, May 03, 2006 at 10:02:37AM +0200, Markus Schaber wrote: > > file ?/tmp/penchal.out? for writing: no such file or directory.. > > It seems that the /tmp directory is missing from your system. Right. And notice that the "your system" here is the _server_, not the machine you're running psql from. If you want local files, you need to use \copy instead. A -- Andrew Sullivan | [EMAIL PROTECTED] It is above all style through which power defers to reason. --J. Robert Oppenheimer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] is an explicit lock necessary?
On Thu, May 04, 2006 at 11:10:56AM -0700, Ash Grove wrote: > Hi, > > Does beginning a transaction put locks on the tables > queried within the transaction? You mean like a table lock? No. A transaction does entail some locks: for instance, an access exclusive lock will block behind your share lock while you're looking at the table (because the exclusive lock wants to be exclusive, of course). See the concurrency control section of the manual. > In the example below, is #2 necessary? My thought was No. currval() is local to your _session_ (not even your transaction). The docs explain how this works. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Help with a seq scan on multi-million row table
On Thu, May 11, 2006 at 10:09:44AM -0700, [EMAIL PROTECTED] wrote: > Hi Markus & Tom, > > Higher statistics for this column hm, I'd love to try changing > it to see how that changes things, but I'm afraid I don't know how > to do that. How can I change the statistics target value for this > column? > > Ah, I think I found the place: No. Just ALTER TABLE [name] ALTER [column] SET STATISTICS. See http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html for more. You'll need to ANALYSE afterwards. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Add column and specify the column position in a table
On Thu, May 18, 2006 at 05:43:19PM +0200, Guillaume Lelarge wrote: > Do you mean that, using "alter table test add column" with the "after" > option, MySQL creates a new table, populates it with the old table > data and finally drops the old table ? I mean, there's the same > performance problem with big tables ? MySQL does that for a great deal of its DDL. Yes, the performance is awful for this on big tables. The reason I didn't answer the OP's question, though, is that I can't think of a legitimate reason to do this anyway. The physical layout of the columns should not be of concern to the developer, who should be naming the columns anyway. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
On Wed, May 24, 2006 at 05:35:10PM +0700, andi wrote:
>
> But my primary key is not in correct order like
What does this mean? Is the key being generated by a sequence (i.e.
is the column DEFAULT nextval('some_sequence'))? If so, the primary
key will be assigned in COMMIT order. Note that the sequence does
not guarantee no gaps, however.
A
--
Andrew Sullivan | [EMAIL PROTECTED]
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
On Fri, May 26, 2006 at 05:11:26PM +0700, andi wrote:
> select rank() over(order by testeridpk ) as rank , * from tester;
>
> I get the result is like this,
>
>
> RANK TESTERIDPK TESTER_NAME
>
> 1 10TESSS
>
> 2 90NAMAAA
>
> 3 100
>
>
> How in postgres sql I get the same result , please help me, because iam
> really frustating with this duty.
There's no built in for that that I know of. You could use a
temporary sequence to do it:
BEGIN;
CREATE SEQUENCE tempseq;
SELECT nextval('tempseq') as rank, testeridpk, tester_name FROM testers
ORDER BY testeridpk;
ROLLBACK;
which, I _think_, will get you what you want (i.e. that's not
tested). The ROLLBACK is just there to clean up the sequence.
A
--
Andrew Sullivan | [EMAIL PROTECTED]
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] hi how to use encryption for incomtax in postgresql
On Fri, May 26, 2006 at 04:01:47PM +0530, Penchalaiah P. wrote: > hi sir > > > > how to use encryption to password in postgresql with examples I guess you want to read this: http://www.postgresql.org/docs/8.1/interactive/client-authentication.html ? A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] PLEASE help ME , HOW TO GENERATE PRIMARY Keys on the fly
On Fri, May 26, 2006 at 09:08:20AM -0500, Bruno Wolff III wrote: > > Rollbacks will not reset sequence values. Use setval to do that. No, what I posted was the CREATE SEQUENCE after the BEGIN. ROLLBACK gets rid of the sequence. The next time you create the same sequence, therefore, it also starts at 1. I don't actually know what this ranking is useful for, to be honest, but people ask for it, and this is a stupid Postgres trick that can make it happen. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] hi still i am unable to provide encryption to a particular table....
On Mon, May 29, 2006 at 03:13:59PM +0530, Penchalaiah P. wrote: > and port number here wt I will get... my requirement is to do encryption > to one of my table.. if any body knows about this please help me Is this one-way encryption (i.e. you want a hash?) You can use built-in md5 for that. If you want something more, use the pgcrypto contrib/ items. A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
