Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished

On Thu, 23 Aug 2007, David Fetter wrote: -Is the Wiki the right place to build this table at? Large Wiki tables get very difficult to manage. They're very easy to manage using things like the Firefox/Mozilla plugin viewsourcewith The kind

Re: [GENERAL] Can postgresql support chinese word?

On Aug 28, 2007, at 22:33 , smithveg wrote: Anyone can tell me does PostgreSQL support chinese word? Which encoding or datatype or anything else should i know if i want to store the chinese word. PostgreSQL supports a number of different character sets in the client, including UTF8, BIG5,

[GENERAL] Can postgresql support chinese word?

Hi, I am new to PostgreSQL. Anyone can tell me does PostgreSQL support chinese word? Which encoding or datatype or anything else should i know if i want to store the chinese word. I do want to develop a simple application using VB.NET and PostgreSQL database system. Thansk -- Smithveg

Re: [GENERAL] Is there a better way to do this?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/28/07 20:06, Tom Lane wrote: [snip] > > As Michael says, the speed argument is really kinda minor compared > to the other ones, but it's real enough. Every little bit counts, though. For example, if it's part of an otherwise computationally-in

Re: [GENERAL] Seeking datacenter PITR backup suggestions

On Tue, Aug 28, 2007 at 09:54:23PM -0400, Tom Lane wrote: > Decibel! <[EMAIL PROTECTED]> writes: > > On Tue, Aug 28, 2007 at 08:31:10PM +0100, Gregory Stark wrote: > >> Postgres tries to reuse WAL files. Once the archive_command completes it > >> believes it is safe to reuse the old file without de

Re: [GENERAL] Seeking datacenter PITR backup suggestions

Decibel! <[EMAIL PROTECTED]> writes: > On Tue, Aug 28, 2007 at 08:31:10PM +0100, Gregory Stark wrote: >> Postgres tries to reuse WAL files. Once the archive_command completes it >> believes it is safe to reuse the old file without deleting it. That will do >> nasty things if you've used ln as your

Re: [GENERAL] Out of Memory - 8.2.4

Tom Lane escribió: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > >> Given that the worst-case consequence is extra index vacuum passes, > >> which don't hurt that much when a table is small, maybe some smaller > >> estimate like 100 TIDs per page would be enough. Or, instead of > >> using a hard

Re: [GENERAL] Out of Memory - 8.2.4

Alvaro Herrera <[EMAIL PROTECTED]> writes: > Tom Lane escribió: >> We could set a hard limit at RelationGetNumberOfBlocks * >> MaxHeapTuplesPerPage TIDs, but that is *extremely* conservative >> (it'd work out to allocating about a quarter of the table's actual size >> in bytes, if I did the math r

Re: [GENERAL] autovacuum not running

Ben wrote: > Hm, I assumed it wasn't running because pg_stat_all_tables shows the last > vacuum from several weeks ago, and this is an active db. Also, I see no > vacuum activity in the logs. But "show autovacuum" does show it being > on > > So if it is running after all, how can I track down

Re: [GENERAL] autovacuum not running

On Tue, Aug 28, 2007 at 03:10:34PM -0700, Ben wrote: > Hm, I assumed it wasn't running because pg_stat_all_tables shows the last > vacuum from several weeks ago, and this is an active db. Also, I see no > vacuum activity in the logs. But "show autovacuum" does show it being > on Last vacuum,

Re: [GENERAL] Reliable and fast money transaction design

On Tue, Aug 28, 2007 at 05:48:50PM +0200, cluster wrote: > I need a way to perform a series of money transactions (row inserts) > together with some row updates in such a way that integrity is ensured > and performance is high. > > I have two tables: > ACCOUNTS ( > account_id int, > b

Re: [GENERAL] Is there a better way to do this?

Michael Glaesemann <[EMAIL PROTECTED]> writes: > The concatenation is probably less efficient: you're casting an int > to text and then the text to interval with the concatenation you're > using. I don't know how that compares in terms of cycles to the int * > interval math, but efficiency is

Re: [GENERAL] Question regarding autovacuum

"Scott Marlowe" <[EMAIL PROTECTED]> writes: > So it's a good idea to allocate 20 to 50% more than what vacuum > verbose says you'll need for overhead. also keep in mind that vacuum > verbose only tells you what the one db in the server needs. No, that's not true --- the numbers it prints are clus

Re: [GENERAL] Is there a better way to do this?

On Aug 28, 2007, at 19:30 , Tom Lane wrote: Michael Glaesemann <[EMAIL PROTECTED]> writes: Note that interval '1 day' is not equal to interval '24 hours'. '1 day' can be 23 or 25 hours across daylight saving time boundaries. When you are adding to timestamp without time zone, they *are* inte

Re: [GENERAL] autovacuum not running

Ben <[EMAIL PROTECTED]> writes: > Hm, I assumed it wasn't running because pg_stat_all_tables shows the last > vacuum from several weeks ago, and this is an active db. Also, I see no > vacuum activity in the logs. But "show autovacuum" does show it being > on Just to double-check, make sure s

Re: [GENERAL] Out of Memory - 8.2.4

Tom Lane escribió: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > Marko Kreen escribi�: > >> I've experienced something similar. The reason turned out to be > >> combination of overcommit=off, big maint_mem and several parallel > >> vacuums for fast-changing tables. Seems like VACUUM allocates

Re: [GENERAL] Is there a better way to do this?

Michael Glaesemann <[EMAIL PROTECTED]> writes: > Note that interval '1 day' is not equal to interval '24 hours'. '1 > day' can be 23 or 25 hours across daylight saving time boundaries. When you are adding to timestamp without time zone, they *are* interchangeable, since no daylight-savings arith

Re: [GENERAL] Geographic High-Availability/Replication

On Fri, Aug 24, 2007 at 06:54:35PM +0200, Markus Schiltknecht wrote: > Gregory Stark wrote: > >Only if your application is single-threaded. By single-threaded I don't > >refer > >to operating system threads but to the architecture. If you're processing a > >large batch file handling records one by

Re: [GENERAL] Is there a better way to do this?

Michael Glaesemann wrote: On Aug 28, 2007, at 17:22 , D. Dante Lorenso wrote: Michael Glaesemann wrote: On Aug 28, 2007, at 16:55 , D. Dante Lorenso wrote: RETURN time + (days || ' days')::INTERVAL; It's bad practice to concatenate like this. Use time + days * interval '1 day' and be done

Re: [GENERAL] Seeking datacenter PITR backup procedures [RESENDING]

On Thu, Aug 23, 2007 at 06:58:36PM -0400, Bill Moran wrote: > Decibel! <[EMAIL PROTECTED]> wrote: > > > > On Aug 19, 2007, at 7:23 AM, Bill Moran wrote: > > >> Assumptions: > > >> a. After pg_stop_backup(), Pg immediately recycles log files and > > >> hence wal > > >> logs can be copied to backup

Re: [GENERAL] Seeking datacenter PITR backup suggestions

On Tue, Aug 28, 2007 at 08:31:10PM +0100, Gregory Stark wrote: > "Steve Crawford" <[EMAIL PROTECTED]> writes: > > > 4. Much more up-to-the-minute recovery data. > > > > In your scenario, what about using "cp -l" (or "ln") instead? Since the > > hard-link it is only creating a new pointer, it will

Re: [GENERAL] Seeking datacenter PITR backup suggestions

On Tue, Aug 28, 2007 at 11:05:52AM -0700, Steve Crawford wrote: > In your scenario, what about using "cp -l" (or "ln") instead? Since the > hard-link it is only creating a new pointer, it will be very fast and > save a bunch of disk IO on your server and it doesn't appear that the > tempdir is for

Re: [GENERAL] Out of Memory - 8.2.4

Alvaro Herrera <[EMAIL PROTECTED]> writes: > Marko Kreen escribió: >> I've experienced something similar. The reason turned out to be >> combination of overcommit=off, big maint_mem and several parallel >> vacuums for fast-changing tables. Seems like VACUUM allocates >> full maint_mem before star

Re: [GENERAL] Install on 32 or 64 bit Linux?

On 8/29/07, Ralph Smith <[EMAIL PROTECTED]> wrote: > Hello, Hi > Are there any recommendations on whether to install onto 32 vs 64 bit Linux? > We're going to be using virtual machines. In addition to Ron's question I'd be curious to hear about the hardware platform and intended use of the datab

Re: [GENERAL] Is there a better way to do this?

On Aug 28, 2007, at 17:46 , Michael Glaesemann wrote: I'm sure others could provide more cogent explanations, but those are my initial thoughts. Thinking about this a little bit more: pushing interpolation/ concatenation to the furthest extreme you get to using eval-like construct, which

Re: [GENERAL] Install on 32 or 64 bit Linux?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/28/07 16:21, Ralph Smith wrote: > Hello, > > We're at a crossroads here and it's time to upgrade boxes and versions > of PG. > This eMail query is about the first step. > > Are there any recommendations on whether to install onto 32 vs 64 bit >

Re: [GENERAL] Is there a better way to do this?

On Aug 28, 2007, at 17:22 , D. Dante Lorenso wrote: Michael Glaesemann wrote: On Aug 28, 2007, at 16:55 , D. Dante Lorenso wrote: RETURN time + (days || ' days')::INTERVAL; It's bad practice to concatenate like this. Use time + days * interval '1 day' and be done with it. Why? Is thi

[GENERAL] Will Index improve the speed?

Hi all, I got a table with many columns of data which got an index on one of the fields (Tsearch2 Gist). I thought that maybe if I'll create a new table with 2 fields (primary key reference to the previous table & the index field from the previous table) and made the index on the index field, may

Re: [GENERAL] Out of Memory - 8.2.4

Erik Jones escribió: > On Aug 28, 2007, at 4:33 PM, Marko Kreen wrote: > >> I've experienced something similar. The reason turned out to be >> combination of overcommit=off, big maint_mem and several parallel >> vacuums for fast-changing tables. Seems like VACUUM allocates >> full maint_mem befor

Re: [GENERAL] Question regarding autovacuum

Scott Marlowe wrote: On 8/28/07, Karl Denninger <[EMAIL PROTECTED]> wrote: Am I correct in that this number will GROW over time? Or is what I see right now (with everything running ok) all that the system will ever need? They will grow at first to accomodate your typical load of de

Re: [GENERAL] Out of Memory - 8.2.4

Marko Kreen escribió: > I've experienced something similar. The reason turned out to be > combination of overcommit=off, big maint_mem and several parallel > vacuums for fast-changing tables. Seems like VACUUM allocates > full maint_mem before start, whatever the actual size of the table. Hmm.

Re: [GENERAL] Is there a better way to do this?

Michael Glaesemann wrote: On Aug 28, 2007, at 16:55 , D. Dante Lorenso wrote: RETURN time + (days || ' days')::INTERVAL; It's bad practice to concatenate like this. Use time + days * interval '1 day' and be done with it. Why? Is this functionality expected to break in the future or has

Re: [GENERAL] Question regarding autovacuum

On 8/28/07, Karl Denninger <[EMAIL PROTECTED]> wrote: > > Am I correct in that this number will GROW over time? Or is what I see > right now (with everything running ok) all that the system > will ever need? They will grow at first to accomodate your typical load of dead tuples created between

Re: [GENERAL] Out of Memory - 8.2.4

On Aug 28, 2007, at 4:33 PM, Marko Kreen wrote: On 8/24/07, Jeff Amiel <[EMAIL PROTECTED]> wrote: Over last 2 days, have spotted 10 "Out of Memory" errors in postgres logs (never saw before with same app/usage patterns on tuned hardware/postgres under FreeBSD) Aug 22 18:08:24 db-1 postgres[164

Re: [GENERAL] autovacuum not running

Hm, I assumed it wasn't running because pg_stat_all_tables shows the last vacuum from several weeks ago, and this is an active db. Also, I see no vacuum activity in the logs. But "show autovacuum" does show it being on So if it is running after all, how can I track down why things aren't

Re: [GENERAL] Is there a better way to do this?

On Aug 28, 2007, at 16:55 , D. Dante Lorenso wrote: RETURN time + (days || ' days')::INTERVAL; It's bad practice to concatenate like this. Use time + days * interval '1 day' and be done with it. Michael Glaesemann grzm seespotcode net ---(end of broadca

Re: [GENERAL] Is there a better way to do this?

On Aug 28, 2007, at 16:51 , Michael Glaesemann wrote: If you mean 24 hours (which you're getting with your 24 * 3600 * interval '2 second'), you could do Or, 24 * 3600 * interval '1 second', rather Michael Glaesemann grzm seespotcode net ---(end of broadcast)

Re: [GENERAL] Is there a better way to do this?

On 8/28/07, Wei Weng <[EMAIL PROTECTED]> wrote: > Is there a more concise way to do this? CREATE OR REPLACE FUNCTION ADDDAYS (TIMESTAMP WITHOUT TIME ZONE, INT) RETURNS TIMESTAMP WITHOUT TIME ZONE AS ' SELECT $1+($2 * ''1 DAY''::INTERVAL) ' LANGUAGE SQL; ---(end of broadcas

Re: [GENERAL] Is there a better way to do this?

Wei Weng wrote: I want to implement something like the following: CREATE OR REPLACE FUNCTION AddDays You don't know how many seconds are in a day, so just add the days using SQL. RETURN time + (days || ' days')::INTERVAL; You don't even need to make that a function, just do that you

Re: [GENERAL] Is there a better way to do this?

Wei Weng escreveu: Hi all I want to implement something like the following: CREATE OR REPLACE FUNCTION AddDays (TIMESTAMP WITHOUT TIME ZONE , INT) RETURNS TIMESTAMP WITHOUT TIME ZONE AS ' DECLARE time ALIAS FOR $1; days ALIAS FOR $2; BEGIN RETURN time+days*24*

Re: [GENERAL] Is there a better way to do this?

On Aug 28, 2007, at 15:59 , Wei Weng wrote: I don't really like this implementation. Is there a more concise way to do this? create or replace function add_days(timestamp, int) returns timestamp language sql as $body$ select $1 + $2 * interval '1 day' $body$; Note that interval '1 day' is n

Re: [GENERAL] autovacuum not running

Ben wrote: > My autovacuum daemon isn't running on 8.2.4, and I'm guessing it's because > I changed my unix socket directory in postgresql.conf. Is there a way I can > tell autovacuum which socket file to use, or which IP to connect to? It doesn't use a socket. How do you know it's not running?

Re: [GENERAL] Is there a better way to do this?

On Tue, Aug 28, 2007 at 04:59:46PM -0400, Wei Weng wrote: > Hi all > > I want to implement something like the following: Well, you could always implement it as SQL instead (untested): CREATE OR REPLACE FUNCTION AddDays (TIMESTAMP WITHOUT TIME ZONE , INT) RETURNS TIMESTAMP WITHOUT T

[GENERAL] autovacuum not running

My autovacuum daemon isn't running on 8.2.4, and I'm guessing it's because I changed my unix socket directory in postgresql.conf. Is there a way I can tell autovacuum which socket file to use, or which IP to connect to? ---(end of broadcast)--- TI

[GENERAL] Is there a better way to do this?

Hi all I want to implement something like the following: CREATE OR REPLACE FUNCTION AddDays (TIMESTAMP WITHOUT TIME ZONE , INT) RETURNS TIMESTAMP WITHOUT TIME ZONE AS ' DECLARE time ALIAS FOR $1; days ALIAS FOR $2; BEGIN RETURN time+days*24*3600*''1 second''::I

Re: [GENERAL] Out of Memory - 8.2.4

On 8/24/07, Jeff Amiel <[EMAIL PROTECTED]> wrote: > Over last 2 days, have spotted 10 "Out of Memory" > errors in postgres logs (never saw before with same > app/usage patterns on tuned hardware/postgres under > FreeBSD) > > Aug 22 18:08:24 db-1 postgres[16452]: [ID 748848 > local0.warning] [6-1] 2

[GENERAL] Install on 32 or 64 bit Linux?

Hello, We're at a crossroads here and it's time to upgrade boxes and versions of PG. This eMail query is about the first step. Are there any recommendations on whether to install onto 32 vs 64 bit Linux? We're going to be using virtual machines. Our application consists mostly of near-seq

Re: [GENERAL] Question regarding autovacuum

Steve Crawford wrote: Karl Denninger wrote: Are your FSM settings enough to keep track of the dead space you have? I don't know. How do I check? vacuum verbose; Toward the bottom you will see something like: ... 1200 page slots are required to track all free space. Current

Re: [GENERAL] Question regarding autovacuum

Tom Lane wrote: Karl Denninger <[EMAIL PROTECTED]> writes: But... .shouldn't autovacuum prevent this? Is there some way to look in a log somewhere and see if and when the autovacuum is being run - and on what? There's no log messages (at the default log verbosity anyway). But you co

Re: [GENERAL] Out of Memory - 8.2.4

On Aug 28, 2007, at 3:55 PM, Tom Lane wrote: Jeff Amiel <[EMAIL PROTECTED]> writes: I notice in the log entries for the out of memory events have no username, database name or host identifier (while regular logged events do) Does that mean anything to anybody? Means they're coming from a

Re: [GENERAL] Question regarding autovacuum

Karl Denninger wrote: >> Are your FSM settings enough to keep track of the dead space you have? >> > I don't know. How do I check? vacuum verbose; Toward the bottom you will see something like: ... 1200 page slots are required to track all free space. Current limits are: 453600 page slots, 100

Re: [GENERAL] Question regarding autovacuum

Karl Denninger <[EMAIL PROTECTED]> writes: > But... .shouldn't autovacuum prevent this? Is there some way to look in > a log somewhere and see if and when the autovacuum is being run - and on > what? There's no log messages (at the default log verbosity anyway). But you could look into the pg_

Re: [GENERAL] Question regarding autovacuum

I don't know. How do I check? Karl Denninger ([EMAIL PROTECTED]) http://www.denninger.net Alvaro Herrera wrote: Karl Denninger wrote: A manual "Vacuum full analyze" fixes it immediately. But... .shouldn't autovacuum prevent this? Is there some way to look in a log somewhere and see

Re: [GENERAL] Out of Memory - 8.2.4

--- Alvaro Herrera <[EMAIL PROTECTED]> wrote: >2.168.20.44 28785LOG: duration: 22606.146 ms execute : select > > Interesting. What's your log_line_prefix? Does it have "%q" somewhere? No, no %q...not quite sure what it means: "stop here in non-session processes" __

Re: [GENERAL] Out of Memory - 8.2.4

Jeff Amiel <[EMAIL PROTECTED]> writes: > I notice in the log entries for the out of memory events have no username, > database name or host > identifier (while regular logged events do) Does that mean anything to > anybody? Means they're coming from autovacuum, likely? Autovacuum probably *sho

Re: [GENERAL] Out of Memory - 8.2.4

Jeff Amiel wrote: > I notice in the log entries for the out of memory events have no username, > database name or host > identifier (while regular logged events do) Does that mean anything to > anybody? > > Aug 28 08:25:50 db-1 postgres[29019]: [ID 748848 local0.warning] [111900-1] > 2007-08-2

Re: [GENERAL] Out of Memory - 8.2.4

I notice in the log entries for the out of memory events have no username, database name or host identifier (while regular logged events do) Does that mean anything to anybody? Aug 28 08:25:50 db-1 postgres[29019]: [ID 748848 local0.warning] [111900-1] 2007-08-28 08:25:50.081 CDT29019ERROR:

Re: [GENERAL] PickSplit method of 2 columns ... error

Yes, thanx. This would be useful as some of our clients are getting swamped (and confused) with these messages in the log files. Cheers, Kevin Tom Lane wrote: Teodor Sigaev <[EMAIL PROTECTED]> writes: Split page algorithm was rewrited for 8.2 for multicolumn indexes and API for user-define

Re: [GENERAL] Question regarding autovacuum

Karl Denninger wrote: > A manual "Vacuum full analyze" fixes it immediately. > > But... .shouldn't autovacuum prevent this? Is there some way to look in a > log somewhere and see if and when the autovacuum is being run - and on > what? Are your FSM settings enough to keep track of the dead spa

[GENERAL] Question regarding autovacuum

Running 8.2.4. The following is in my postgresql.conf: # - Query/Index Statistics Collector - #stats_command_string = on update_process_title = on stats_start_collector = on # needed for block or row stats # (change requires restart) #stats_b

Re: [GENERAL] Seeking datacenter PITR backup suggestions

Gregory Stark wrote: >> In your scenario, what about using "cp -l" (or "ln") instead? > > Postgres tries to reuse WAL files. Once the archive_command completes it > believes it is safe to reuse the old file without deleting it. That will do > nasty things if you've used ln as your archive com

Re: [GENERAL] Seeking datacenter PITR backup suggestions

"Steve Crawford" <[EMAIL PROTECTED]> writes: > 4. Much more up-to-the-minute recovery data. > > In your scenario, what about using "cp -l" (or "ln") instead? Since the > hard-link it is only creating a new pointer, it will be very fast and > save a bunch of disk IO on your server and it doesn't ap

Re: [GENERAL] Can this function be declared IMMUTABLE?

"Tom Lane" <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] writes: >> Well, I am considering a function that does select from a table, but the >> table contents change extremely infrequently (the table is practically a >> list of constants). Would it be safe to declare the function IMMUTABLE

Re: [GENERAL] PickSplit method of 2 columns ... error

Teodor Sigaev <[EMAIL PROTECTED]> writes: > Split page algorithm was rewrited for 8.2 for multicolumn indexes and API for > user-defined pickSplit function was extended to has better results with index > creation. But GiST can interact with old functions - and it says about this. > That isn't me

Re: [GENERAL] Indexing Foreign Key Columns

On Tue, 28 Aug 2007 13:19:32 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Josh Trutwin <[EMAIL PROTECTED]> writes: > > I am curious if there are any rules of thumb for when to index a > > foreign key column? > > (You realize of course that there's already an index on the > referenced column, else

Re: [GENERAL] Seeking datacenter PITR backup suggestions

> In general, your handling of WAL files seems fragile and error-prone Indeed. I would recommend simply using rsync to handle pushing the files. I see several advantages: 1. Distributed load - you aren't copying a full-day of files all at once. 2. Very easy to set-up - you can use it directl

Re: [GENERAL] Turning off atime on PostgreSQL DB volumes

We use noatime on our production database without issues. On 8/28/07, Bill Moran <[EMAIL PROTECTED]> wrote: > In response to "Keaton Adams" <[EMAIL PROTECTED]>: > > > After reading several articles on the performance drag that Linux atime > > has on file systems we would like to mount our DB volum

Re: [GENERAL] Turning off atime on PostgreSQL DB volumes

In response to "Keaton Adams" <[EMAIL PROTECTED]>: > After reading several articles on the performance drag that Linux atime > has on file systems we would like to mount our DB volumes with the > noatime parameter to see just what type of a performance gain we will > achieve. Does PostgreSQL use

Re: [GENERAL] Turning off atime on PostgreSQL DB volumes

Keaton Adams wrote: > After reading several articles on the performance drag that Linux atime > has on file systems we would like to mount our DB volumes with the > noatime parameter to see just what type of a performance gain we will > achieve. Does PostgreSQL use atime in any way when reading/wr

Re: [GENERAL] Turning off atime on PostgreSQL DB volumes

"Keaton Adams" <[EMAIL PROTECTED]> writes: > After reading several articles on the performance drag that Linux atime > has on file systems we would like to mount our DB volumes with the > noatime parameter to see just what type of a performance gain we will > achieve. Does PostgreSQL use atime in

Re: [GENERAL] 8.1.10 release?

"George Pavlov" <[EMAIL PROTECTED]> writes: > What's the plan for releasing the next 8.1? It is on the radar screen, but core has been trying to focus on getting 8.3 ready for beta. Thankfully, we are starting to see some light at the end of that tunnel ... maybe another couple weeks.

Re: [GENERAL] Can this function be declared IMMUTABLE?

[EMAIL PROTECTED] writes: > Well, I am considering a function that does select from a table, but the > table contents change extremely infrequently (the table is practically a > list of constants). Would it be safe to declare the function IMMUTABLE > provided that the table itself is endowed wi

Re: [GENERAL] Indexing Foreign Key Columns

Josh Trutwin <[EMAIL PROTECTED]> writes: > I am curious if there are any rules of thumb for when to index a > foreign key column? (You realize of course that there's already an index on the referenced column, else you wouldn't have been allowed to reference it.) You need an index on the referenci

Re: [GENERAL] INSERT doc discrepancy

Alvaro Herrera <[EMAIL PROTECTED]> writes: > Kristo Kaiv wrote: >> but it seems if i want to return the result into a record i have to use it >> with INTO clause in the end: > Ah, you are using it in plpgsql! OK, but the explanation to the > discrepancy is that the second INTO is not part of the

Re: [GENERAL] Tables dissapearing

On 28/08/2007 15:48, Jeff Amiel wrote: Alien or supernatural intervention: 18-1 Obscure postgresql bug nobody else has ever seen: 25-1 That's the sort of confidence in the DBMS we all like to see! :-) Ray. --- Raymond O'Donnell,

Re: [GENERAL] 8.1.10 release?

George Pavlov wrote: > What's the plan for releasing the next 8.1? There hasn't been a release > since April and there have been fixes. (I personally am particularly > interested in "implement chunking protocol for writes to the syslogger > pipe" because without it over 2/3 of attempts at query ana

[GENERAL] Can this function be declared IMMUTABLE?

Hello, I have a question about whether I can safely declare a function IMMUTABLE. Citing the PostgreSQL documentation under "Function Volatility Categories" in the section on "Extending SQL": It is generally unwise to select from database tables within a

Re: [GENERAL] Can this function be declared IMMUTABLE?

In response to [EMAIL PROTECTED]: > Hello, > > I have a question about whether I can safely declare a function IMMUTABLE. > Citing the PostgreSQL documentation under "Function Volatility > Categories" in the section on "Extending SQL": > > It is general

[GENERAL] 8.1.10 release?

What's the plan for releasing the next 8.1? There hasn't been a release since April and there have been fixes. (I personally am particularly interested in "implement chunking protocol for writes to the syslogger pipe" because without it over 2/3 of attempts at query analysis fail for me). George

[GENERAL] OT? Courier + PgSQL problem

Hi all, I fully acknowledge that this may be off topic, but hopefully not too much. :) I am hoping some of you have used PgSQL this way and can help as I am not on any courier mail lists. I have a problem I can't seem to figure out. I am trying to get Courier to read email over POP3 using

Re: [GENERAL] PickSplit method of 2 columns ... error

Split page algorithm was rewrited for 8.2 for multicolumn indexes and API for user-defined pickSplit function was extended to has better results with index creation. But GiST can interact with old functions - and it says about this. That isn't mean some real problem or error - index will be the

Re: [GENERAL] Bigtime scaling of Postgresql (cluster and stuff I suppose)

Hi, Bill Moran wrote: While true, I feel those applications are the exception, not the rule. Most DBs these days are the blogs and the image galleries, etc. And those don't need or want the overhead associated with synchronous replication. Uhm.. do blogs and image galleries need replication a

[GENERAL] ecpg: dtime_t vs timestamp

We have upgraded from Version 7.4.x to Version 8.2.4. In 7.4.x, we use the Informix compatibility functionality to use legacy code. Our .pgc code looks as follows: #include "Ice.h" EXEC SQL include sqlda; EXEC SQL include sqltypes; EXEC SQL include sql3types; EXEC SQL include pgtypes_timest

[GENERAL] Reliable and fast money transaction design

I need a way to perform a series of money transactions (row inserts) together with some row updates in such a way that integrity is ensured and performance is high. I have two tables: ACCOUNTS ( account_id int, balance int ); TRANSACTIONS ( transaction_id int, source_

Re: [GENERAL] One database vs. hundreds?

Kynn Jones wrote: I'm hoping to get some advice on a design question I'm grappling with. I have a database now that in many respects may be regarded as an collection of a few hundred much smaller "parallel databases", all having the same schema. What I mean by this is that, as far as the intend

Re: [GENERAL] Read Access to database

This seems unnecessarily complicated. Yes, I believe you do have to grant select on every table, but you can use psql to generate the queries, then execute them. i.e. -- show only tuples /t -- output to temp script file. /o script.sql -- generate your script using pg_tables SELECT 'GRANT SEL

Re: [GENERAL] Tables dissapearing

At 03:15 PM 8/28/2007, Kamil Srot wrote: Andrew, Alvaro... well, sure SQL injection is possibility I cannot ignore... (and sure as "dad" of this application, I think it's not the case :-) ... just kidding... As even the injected SQL will be shown in the logs, so we'll know more after some time.

Re: [GENERAL] Tables dissapearing

On Aug 28, 2007, at 9:41 AM, Kamil Srot wrote: Jeff Amiel wrote: My entire shop has set up a betting pool on the outcome of this...so I hope you post results regardless of the outcome, Kamil. Heh, is the pool still open? Maybe I can make at least something from it :-D (in all seriousness,

Re: [GENERAL] Tables dissapearing

Kamil Srot wrote: Heh, is the pool still open? Maybe I can make at least something from it :-D Current odds Application bug: even money Application configuration issue: 2-1 Rogue cron job or other maintenance process: 4-1 Somebody messing with you (or SQL injection): 8-1 XID wrap

Re: [GENERAL] Tables dissapearing

Jeff Amiel wrote: My entire shop has set up a betting pool on the outcome of this...so I hope you post results regardless of the outcome, Kamil. Heh, is the pool still open? Maybe I can make at least something from it :-D (in all seriousness, we hope you find/fix the problem before things get

Re: [HACKERS] [GENERAL] Undetected corruption of table files

At 11:48 PM 8/27/2007, Trevor Talbot wrote: On 8/27/07, Jonah H. Harris <[EMAIL PROTECTED]> wrote: > On 8/27/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > that and the lack of evidence that they'd actually gain anything > > I find it somewhat ironic that PostgreSQL strives to be fairly > non-corrup

Re: [GENERAL] One database vs. hundreds?

Thank you very much for your replies. Given the differences in the opinions expressed, I thought I would describe the database briefly. The purpose of the database is basically translation of terms. Imagine a collection of disjoint sets A, B, C, ... Now imagine that for each element of a set mul

Re: [GENERAL] Tables dissapearing

My entire shop has set up a betting pool on the outcome of this...so I hope you post results regardless of the outcome, Kamil. (in all seriousness, we hope you find/fix the problem before things get really ugly) Kamil Srot wrote: Richard Huxton wrote: Kamil Srot wrote: In the version used o

Re: [GENERAL] Bigtime scaling of Postgresql (cluster and stuff I suppose)

In response to Markus Schiltknecht <[EMAIL PROTECTED]>: > Hi, > > Bill Moran wrote: > > First off, "clustering" is a word that is too vague to be useful, so > > I'll stop using it. There's multi-master replication, where every > > database is read-write, then there's master-slave replication, wh

Re: [GENERAL] problem with transactions in VB.NET using npgsql

At 7:05 PM -0400 8/27/07, Tom Lane wrote: Owen Hartnett <[EMAIL PROTECTED]> writes: I assign the transaction object to each of the commands, but it seems that some tables will get updated, even when I call rollback. Is something I'm calling secretly calling "commit" somewhere? Dunno anythi

[GENERAL] Turning off atime on PostgreSQL DB volumes

After reading several articles on the performance drag that Linux atime has on file systems we would like to mount our DB volumes with the noatime parameter to see just what type of a performance gain we will achieve. Does PostgreSQL use atime in any way when reading/writing data? If we turn off/

Re: [GENERAL] difference between function and stored procedure

On 8/28/07, Marcelo de Moraes Serpa <[EMAIL PROTECTED]> wrote: > Hello list, > > Is there any difference between a PGSQL Function and Stored Procedure in > PostgreSQL (8.2) ? > > If so, what difference? Is the SQL used to create a SP different from the > SQL used to create a function ? Strictly sp

Re: [GENERAL] difference between function and stored procedure

On Aug 28, 2007, at 8:24 , Marcelo de Moraes Serpa wrote: Is there any difference between a PGSQL Function and Stored Procedure in PostgreSQL (8.2) ? No. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain anal

[GENERAL] difference between function and stored procedure

Hello list, Is there any difference between a PGSQL Function and Stored Procedure in PostgreSQL (8.2) ? If so, what difference? Is the SQL used to create a SP different from the SQL used to create a function ? Thanks in advance, Marcelo.

Re: [GENERAL] delete vs insert vs update due to primary key dups -> which is better

On Aug 28, 2007, at 3:10 , Richard Huxton wrote: Ow Mun Heng wrote: less typing per insert/update statement so it'll be where a.pkey = b.pkey instead of a.key1 = b.key1 and a.key2 = b.key2 and ... up to key5 I'd still leave it alone, but it's your database. And you can use the row compar

  1   2   >