Re: [GENERAL] Socket command type I unknown
Joel Richard <[EMAIL PROTECTED]> writes: > ... we occasionally get the following message in our apache log > file. I'm looking for information on what it means: >DBD::Pg::db selectrow_array failed: FATAL: Socket command type I > unknown This looks to me like a protocol-level incompatibility: probably the client code is sending data in a slightly different format than the server is expecting, or one side or the other is off-by-one about message lengths, or something like that. One way or another the server is receiving an 'I' when it wasn't expecting that. I'm not aware of any such bugs on the server side in 7.3.4. What I suspect is a problem on the DBD::Pg side, where you did not specify what version you are using ... but if it's recent, it probably thinks that talking to 7.3.4 is a legacy problem ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Socket command type I unknown
Good evening, I hope that I am posting this to the right place. If not, please direct me to the appropriate mailing list and I will send to that one instead. Background Info: Debian Linux (Sarge) Server A -- Apache 2.0.54 + mod_perl + DBD::Pg Server B -- PostgreSQL 7.3.4, Compiled, not debian package (yes, I know we should upgrade) Although I haven't been able to pay much attention to it until recently, we occasionally get the following message in our apache log file. I'm looking for information on what it means: DBD::Pg::db selectrow_array failed: FATAL: Socket command type I unknown server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. DBD::Pg::db selectrow_array failed: no connection to the server After this, we get a series of errors like this: DBD::Pg::db selectrow_array failed: no connection to the server DBD::Pg::db selectrow_array failed: no connection to the server DBD::Pg::db selectrow_array failed: no connection to the server And I -know- that's causing trouble on my web server. :) A restart of the web server 'corrects' the problem by reestablishing the connections to the database. I suspect this might be caused by a mismatch between the client libraries on Server A (7.4.X) whereas Server B is a 7.3.X install. (I'm working on correcting this ASAP, which will also get us on 7.4.) Basically my question is this: What does this error indicate? I can't seem to find much about it on the net and to be honest, I have become rather illiterate in C over the past several years, so reading the source is not really a viable option. Any info would be appreciated. Thank you for your time. --Joel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Should I use PL/PGSQL or Perl/PGSQL?
I've been wondering, does anybody know which is more likely to be installed on a postgresql server? Which is faster? I'm writting an application in perl that is going to need to get broad information about heiarchial data (how many parents, settings common on parents, etc), and I'd like to put that data presentation logic into the database. Thanks, Tyler ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Bug with sequences and WAL ?
Philippe Ferreira <[EMAIL PROTECTED]> writes: > If a new sequence is created, its creation is propagated via WAL. > However, instead of getting the property 'is_called'=false (the correct > value before its first use), > we get 'is_called'=true after a PITR recovery. > Is it a bug, or a normal behaviour ? I don't think this is very important, because the normal behavior of sequences is that after a crash the sequence can be up to 32 (IIRC) counts beyond the last value actually delivered before the crash. To get "exact" restart behavior we'd need to emit a separate xlog record for each nextval() command, which seems like a pretty high price considering that you cannot assume no holes in the sequence values anyway. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Error with temporary tables
Read the FAQ. --- Claire McLister wrote: > Hi, > > I'm using a pgsql function that begins by creating a temporary > table, does some processing, and then drops the temporary table just > before exiting. It has been working fine for a while now, but > suddenly complains for some calls that "Relation with OID" does not > exist, at the point when it is executing the DROP table command. > > The general scheme is as follows: > > CREATE FUNCTION Foo(Integer) AS > ' > BEGIN >CREATE Temporary Table Bar AS (a left outer join of two tables) >Do processing >DROP Table Bar; >RETURN 1; > END > ' > > This is for Postgresql version 7.4.8 > > Can someone tell me what I'm doing wrong? Should I try to use 'ON > COMMIT DROP' instead? > > Thanks > > Claire > > -- > Claire McLister[EMAIL PROTECTED] > 1684 Nightingale Avenue Suite 201 > Sunnyvale, CA 94087408-733-2737(fax) > > http://www.zeemaps.com > > > > ---(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 > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Error with temporary tables
Hi, I'm using a pgsql function that begins by creating a temporary table, does some processing, and then drops the temporary table just before exiting. It has been working fine for a while now, but suddenly complains for some calls that "Relation with OID" does not exist, at the point when it is executing the DROP table command. The general scheme is as follows: CREATE FUNCTION Foo(Integer) AS ' BEGIN CREATE Temporary Table Bar AS (a left outer join of two tables) Do processing DROP Table Bar; RETURN 1; END ' This is for Postgresql version 7.4.8 Can someone tell me what I'm doing wrong? Should I try to use 'ON COMMIT DROP' instead? Thanks Claire -- Claire McLister[EMAIL PROTECTED] 1684 Nightingale Avenue Suite 201 Sunnyvale, CA 94087408-733-2737(fax) http://www.zeemaps.com ---(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: [GENERAL] Number format problem
Stéphane SCHILDKNECHT wrote: > select to_char(1485.12, '9G999D99'); > But, surprinsingly, I got 1,1485,12. The fr_FR locale is broken. You should report this to glibc. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to find first non-vacation day
"Andrus Moor" <[EMAIL PROTECTED]> writes:
> I have a table of vacations
>
> create table vacation (
> id integer primary key,
> dstart date,
> dend date );
>
>
> I need to find first non-vacation day before given date.
>
> This can be done using the following procedural vfp code
>
> function nonvacation( dbefore )
>
> for i=dbefore to date(1960,1,1) step -1
> select vacation
> locate for between( i, dstart, dend )
> if not found()
> return i
> endif
> endfor
> return null
>
> but this is very slow
>
> How to implement this as sql select statement ?
People try to get baroquely clever about building tiny tables to
represent these things; it tends not to work out well, because the
queries get even more baroque...
I'd create a table of all the days of the year:
create table days (
a_day date,
primary key(a_day)
);
Fill it in with 365 values:
insert into days
select '2005-12-31'::date + (generate_series||'days')::interval from
generate_series(1,365);
Suppose vacations are thus...
/* [EMAIL PROTECTED]/dba2 ~=*/ select * from vacation;
dstart |dend
+
2006-01-01 | 2006-01-01
2006-03-15 | 2006-03-19
2006-12-24 | 2006-12-25
(3 rows)
Forget about your representation of vacation; replace it with the
following "set of vacation days":
create table vacation_days as select distinct a_day from vacation, days where
a_day between dstart and dend;
Now, to find the *last working day* before, oh, say, 2006-03-18...
/* [EMAIL PROTECTED]/dba2 ~=*/ select max(a_day) from (select a_day from days
where a_day not in (select a_day from vacation_days)) as non_vac_days where
a_day < '2006-03-18';
max
2006-03-14
(1 row)
Determining cost...
/* [EMAIL PROTECTED]/dba2 ~=*/ explain analyze select max(a_day) from (select
a_day from days where a_day not in (select a_day from vacation_days)) as
non_vac_days where a_day < '2006-03-18';
QUERY PLAN
Aggregate (cost=5.43..5.44 rows=1 width=4) (actual time=0.644..0.647 rows=1
loops=1)
-> Index Scan using days_pkey on days (cost=1.10..5.33 rows=38 width=4)
(actual time=0.112..0.406 rows=72 loops=1)
Index Cond: (a_day < '2006-03-18'::date)
Filter: (NOT (hashed subplan))
SubPlan
-> Seq Scan on vacation_days (cost=0.00..1.08 rows=8 width=4)
(actual time=0.006..0.033 rows=8 loops=1)
Total runtime: 0.729 ms
(7 rows)
If you're doing a lot of calculations of "work days," then it would
make a lot of sense to create a "materialized calendar" representing
the work days of the year...
--- Start with all days
create table work_calendar as select a_day from days;
create unique index wc_day on work_calendar (a_day);
--- Drop out Saturday/Sunday
delete from work_calendar where date_part('dow', a_day) not in (0,6);
--- Drop out vacation days
delete from work_calendar where a_day in (select a_day from days, vacation
where a_day between dstart and dend);
/* [EMAIL PROTECTED]/dba2 ~=*/ select max(a_day) from work_calendar where a_day
< '2006-03-18';
max
2006-03-14
(1 row)
/* [EMAIL PROTECTED]/dba2 ~=*/ explain analyze select max(a_day) from
work_calendar where a_day < '2006-03-18';
QUERY PLAN
--
Result (cost=0.07..0.08 rows=1 width=0) (actual time=0.043..0.047 rows=1
loops=1)
InitPlan
-> Limit (cost=0.00..0.07 rows=1 width=4) (actual time=0.027..0.030
rows=1 loops=1)
-> Index Scan Backward using wc_day on work_calendar
(cost=0.00..3.73 rows=54 width=4) (actual time=0.019..0.019 rows=1 loops=1)
Index Cond: (a_day < '2006-03-18'::date)
Filter: (a_day IS NOT NULL)
Total runtime: 0.101 ms
(7 rows)
The overall point: If you create the calendars as sets of days, then
SQL provides you with *excellent* ways of manipulating them as sets
where you say things like "where day is in this set" and "where day is
*not* in that set" and such.
--
"cbbrowne","@","acm.org"
http://cbbrowne.com/info/nonrdbms.html
"If the programmer can simulate a construct faster then the compiler
can implement the construct itself, then the compiler writer has blown
it badly." -- Guy L. Steele, Jr., Tartan Laboratories
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to find first non-vacation day
[EMAIL PROTECTED] (Philip Hallstrom) writes: >>> I have a table of vacations >>> create table vacation ( >>> id integer primary key, >>> dstart date, >>> dend date ); >>> I need to find first non-vacation day before given date. >>> This can be done using the following procedural vfp code >>> function nonvacation( dbefore ) >>> for i=dbefore to date(1960,1,1) step -1 >>> select vacation >>> locate for between( i, dstart, dend ) >>> if not found() >>>return i >>>endif >>> endfor >>> return null >>> but this is very slow >>> How to implement this as sql select statement ? >> >> Haven't given a lot of thought to this, but why not? >> >> SELECT * >> FROM vacation >> WHERE >>dstart < '2006-02-03' >> ORDER BY dstart DESC >> LIMIT 1 > > Just realized I read the question wrong. The above would give you the > first vacation day... > > Maybe alter your table to include all days and add a boolean field to > indicate if it's a vacation day or not? Then you could probably use > the above with some tweaks to the where clause. The "big win" comes if you realize that "vacation," "the whole year," "work days," and such are all nicely described as "sets," and that SQL is fairly excellent at representing set operations. So create a calendar table that is the set of days in the year. Create a vacation table that is the set of vacation days in the year. That, combined with indication of other scheduled "non-working days" such as weekends, can easily define a set of days that are the "work calendar." There will be dozens or hundreds of entries in each table; that's fine, they'll still be small tables, easily searched for commonality/difference. -- select 'cbbrowne' || '@' || 'cbbrowne.com'; http://www3.sympatico.ca/cbbrowne/oses.html "Options to reboot are: -n Avoids the sync. It can be used if a disk or the processor is on fire." -- reboot(8) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Bug with sequences and WAL ?
Hi, If a new sequence is created, its creation is propagated via WAL. However, instead of getting the property 'is_called'=false (the correct value before its first use), we get 'is_called'=true after a PITR recovery. Is it a bug, or a normal behaviour ? (version of PostgreSQL : 8.0.4) Thank you, Philippe Ferreira. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to find first non-vacation day
I have a table of vacations create table vacation ( id integer primary key, dstart date, dend date ); I need to find first non-vacation day before given date. This can be done using the following procedural vfp code function nonvacation( dbefore ) for i=dbefore to date(1960,1,1) step -1 select vacation locate for between( i, dstart, dend ) if not found() return i endif endfor return null but this is very slow How to implement this as sql select statement ? Haven't given a lot of thought to this, but why not? SELECT * FROM vacation WHERE dstart < '2006-02-03' ORDER BY dstart DESC LIMIT 1 Just realized I read the question wrong. The above would give you the first vacation day... Maybe alter your table to include all days and add a boolean field to indicate if it's a vacation day or not? Then you could probably use the above with some tweaks to the where clause. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to find first non-vacation day
I have a table of vacations create table vacation ( id integer primary key, dstart date, dend date ); I need to find first non-vacation day before given date. This can be done using the following procedural vfp code function nonvacation( dbefore ) for i=dbefore to date(1960,1,1) step -1 select vacation locate for between( i, dstart, dend ) if not found() return i endif endfor return null but this is very slow How to implement this as sql select statement ? Haven't given a lot of thought to this, but why not? SELECT * FROM vacation WHERE dstart < '2006-02-03' ORDER BY dstart DESC LIMIT 1 ---(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
[GENERAL] How to find first non-vacation day
I have a table of vacations create table vacation ( id integer primary key, dstart date, dend date ); I need to find first non-vacation day before given date. This can be done using the following procedural vfp code function nonvacation( dbefore ) for i=dbefore to date(1960,1,1) step -1 select vacation locate for between( i, dstart, dend ) if not found() return i endif endfor return null but this is very slow How to implement this as sql select statement ? Andrus. ---(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: [GENERAL] grouping of functions
Tom Lane wrote: Is there any way to group functions logically? Put them in different schemas, perhaps? I thought of that, but that is not what I want. I want function to be in more that one group, so this would cause even more mess. Best regards, Rikard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] grouping of functions
Rikard Pavelic <[EMAIL PROTECTED]> writes: > Is there any way to group functions logically? Put them in different schemas, perhaps? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Error: "could not read from statistics collector pipe"
> 2006-01-27 10:37:29 FATAL could not read from statistics collector >pipe: No error > 2006-01-27 10:37:30 LOGstatistics collector process (PID 5940) >was terminated by signal 1 > try turning row level statistics off (or, during bulk inserts, turn inserts off completely). I've seem this too but never nailed it down. > (PostgreSQL 8.1.2, WinXP SP2, Athlon64 X2 3800, 2 GB RAM) > > These errors occur every 1-2 minutes when the DB is active. What do > these messages mean and how can I stop them from appearing? > > I also noticed that during inserting lots of records, the DB becomes > increasingly slower the more records were inserted. For example, the > first 10 records take 15 minutes, but records 30-40 take > 3 hours. Could this be related to the messages above? try defering index/key generationg until after the insert. probably what is happening is you are blowing out your sort memory and swapping occurs. of course, you can always bump sort_mem merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] grouping of functions
Hi! Is there any way to group functions logically? It's get pretty frustrating to locate some function when you have 500+ functions :( And if there isn't is there any plan to add this functionality? Thanks, Rikard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] regarding debugging?
In article <[EMAIL PROTECTED]>, [EMAIL PROTECTED] says... > > where we can check the execution of our program or we can dry run our code, > > is > > there aby option or feature with PGSQL for the same purpose that we can > > check > > our PGSQL statements? > > No, unfortunately not. What I do is: > > - error check everything you can, and use RAISE EXCEPTION with descriptive > error messages > > - come up with test cases to exercise all branches of code > > - print useful debugging info while tracking down problems > > > EMS Postgresql Manager has a debugger in it. Its not perfect but still it is quite useful. B. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PgSQL as part of commercial product
I bet donations to support the project are appreciated. Other than that, the postgreSQL license is BSD - which basically means you can do whatever you want, you just can't sue anyone if it's not working. This has been answered a thousand times, so checking mailing list archives and the FAQ should give you plenty of explanations On Thursday 02 February 2006 16:20, Arun Kannapiran wrote: > Dear Sir/Madam, > > I would appreciate it if you could answer the below queries. > > What are the licencing requirements for PgSQL ? > > The company I work for is building a client-server application with a > PgSQL backend to be sold commercially on the market, are there any > licencing or other payments that need to be made ? > > Thanks, > > Arun Kannapiran > > > > > Do you Yahoo!? > The New Yahoo! Movies: Check out the Latest Trailers, Premiere Photos and > full Actor Database. http://au.movies.yahoo.com > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax:+1 707 568 6416 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] C Language Stored Procedure Returning No Data
On Fri, Feb 03, 2006 at 09:07:48AM -0500, Jeff Trout wrote:
> On Feb 2, 2006, at 7:17 PM, Michael Fuhr wrote:
> >If you declare the function with "RETURNS bytea" then the function
> >must return something; if zero-length data and NULL aren't suitable
> >for indicating no data then you could raise an error and catch that
> >error in the client. Another possibility would be to make the
> >function set-returning ("RETURNS SETOF bytea" and some code changes)
> >and return no rows to indicate no data.
>
> Another possibility is to log an exception with elog & company.
That's what I meant by "raise an error." Incidentally, for user-
visible messages ereport is preferred over "its older cousin" elog
(perhaps ereport is what you meant by "& company").
http://www.postgresql.org/docs/8.1/interactive/error-message-reporting.html
"Therefore, elog should be used only for internal errors and low-level
debug logging. Any message that is likely to be of interest to
ordinary users should go through ereport."
> However that will also have the side effect of rolling back the txn
> if you are currently in one.
In 8.0 and later you could wrap the function call with a savepoint,
explicitly or implicitly (e.g., via an EXCEPTION clause in a PL/pgSQL
function). Exception-handling code in the caller could roll back
to the savepoint if an error is raised.
--
Michael Fuhr
---(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: [GENERAL] Primary keys for companies and people
> I definitely agree with you here, Merlin. Mutability is not the issue > at hand. May I ask what strategies you use for determining uniqueness > for people? Well, that depends on the particular problem at hand. If you had two john smiths in your system, how would you distinguish them? If you assinged an account number in your system and gave it to the person to refer back to you, this ok...this is not a surrogate key per se, but a meaningful alias. However, that technique can't always be applied, take the case of the 'contacts' table for an account. Since you don't give each contact of each accunt a number and you don't print a number representing them on various reports (there would be no reason to), adding a surrogate to the table adds nothing to your database, it's just meaningless infromation with no semantic value. There *must* be a semantic difference between the two John Smiths or you should be storing one record in the database, not two. If you kind determine an easy natural differentiator, invent one: create table contact ( account text, name text, memo text, primary key(account, name, memo) ); The memo field is blank in most cases unlees it's needed. Suppose you were filling contact information in your databse and Taking your second John Smith from an account...your operator says, 'we already have a john smith for your account, can you give us something to identify him?' Put that in the memo field and there you go. Now your operator is taking information which has value pertaining to the scope of the problem domain your application exists in. This is just one example of how to approach the problem Now there is no ambiguiity about which john smith you are dealing with. This may not be a perfect solution for every application but there is basically has to be a method of finding semantic unquenes to your data or you have a hole in your data model. Glossing over that hole with artificial information solves nothing. There are pracitcal reasons to use surrogates but the uniqueness argument generally holds no water. By 'generating' uniqueness you are breaking your data on mathematical terms. Until you truly understand the ramifcations of that statement you can't really understand when the practical cases apply. Many of the arguments for surrugates based on mutability and uniqueness are simply illogical. The performance issue is more complex and leads to the issue of practicality. I wouldn't find any fault with a modeler who benchmarked his app with a surrogate vs. a 5 part key and chose the former as long as he truly understood the downside to doing that (extra joins). Merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgres 7.3.2 -> 8.1.2 upgrade performance issue
OK - spent some more time profiling what was going on on both the client and server machine. It began ti look more like a connector problem (I was seeing low CPU usage on both client & server and could see very slow BIND/INSERT/COMMIT commands being sent to the db). Upgraded to Npgsql 1.0beta2 - from http://pgfoundry.org/projects/npgsql and things have improved significantly. Thanks for the help, David. -- David Brain - bandwidth.com [EMAIL PROTECTED] 919.297.1078 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] performance about trigger (after insert, update, delete)
Hello, I have a question about the performance of trigger/trigger functions. For example, I had a trigger setup as : *CREATE TRIGGER track_tableOperation AFTER INSERT OR UPDATE OR DELETE **ON tableName FOR EACH ROW** EXECUTE PROCEDURE tracking_info(); A track table as: ( table_name, username, updated_table_prim_key, time_stamp, action ) table_name: which table is changed username:who does the change updated_table_prim_key: primary key column values time_stamp: default now() action: added, deleted, updated, etc. * I have 50 tables, whenever there is a change (update, insert, delete) , I'd like to track this action and saved into my track table. If I design triggers for all these 50 tables, whenever there are changes for the table (Let's say users changed record values from GUI), a trigger function is run and values are saved into track table automatically. I'd like to know the performance about the above way for tracking table values updates. Your comments are very welcomed. Emi ---(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: [GENERAL] Postgres 7.3.2 -> 8.1.2 upgrade performance issue
Tom Lane wrote: Did you remember to VACUUM ANALYZE after loading the data? It sounds to me like bad choices of plans ... Yes - although I have autovacuum off, partly to make an apples to apples comparison with 7.3 and partly due to the nature of the app. On the application side I'm connecting via npgsql from .Net - the application is basically pumping large numbers of rows into a heavily indexed db (used for analysis later). Performance went from 100-200 rows per-second (which was being capped by cpu usage on the client side) to more like 10 rows per second. I'm going to investigate what effect upgrading npgsql has too - as it appears there is a new version available. I will report back with what I find. I just enabled autovacuum - with no apparent speed increase. Odd. David. -- David Brain - bandwidth.com [EMAIL PROTECTED] 919.297.1078 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] logging settings
Tom Lane wrote: I think you're getting bit by a standard beginner gotcha: commenting out an entry in postgresql.conf will not change the state of a running postmaster. (A comment is a no-op, eh?) You need to put in a non-comment entry that sets the desired state. This was indeed the case. I thought I had tried placing the values back without commenting the lines out, but apparently not. Thanks for your help! - Mott ---(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: [GENERAL] Postgres 7.3.2 -> 8.1.2 upgrade performance issue
Did you analyze after you imported the dump? Woody -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Brain Sent: Friday, February 03, 2006 10:03 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Postgres 7.3.2 -> 8.1.2 upgrade performance issue Hi, Recently tried an upgrade from 7.3.2 to 8.1.2. The actual upgrade went pretty well. However my application is now getting >10 times slower INSERT times than it was under 7.3.2. As far as possible I mirrored the settings in postgresql.conf (obviously I couldn't just drop in the old config). Any thoughts as to where to look, or what to do to fix this? Thanks, David. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgres 7.3.2 -> 8.1.2 upgrade performance issue
David Brain <[EMAIL PROTECTED]> writes: > Recently tried an upgrade from 7.3.2 to 8.1.2. The actual upgrade went > pretty well. However my application is now getting >10 times slower > INSERT times than it was under 7.3.2. As far as possible I mirrored the > settings in postgresql.conf (obviously I couldn't just drop in the old > config). > Any thoughts as to where to look, or what to do to fix this? Did you remember to VACUUM ANALYZE after loading the data? It sounds to me like bad choices of plans ... regards, tom lane ---(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
[GENERAL] Number format problem
Hi, There seems to be some tricky behaviour with number formating and french locale. I tried the following request: select to_char(1485.12, '9G999D99'); I was expecting to get: 1 485,12 But, surprinsingly, I got 1,1485,12. My postgresql server is an 8.1.2 version. The same problem occurs under Ubuntu Breezy and Debian Testing. My current configuration is [EMAIL PROTECTED] client_encoding=LATIN9 server_encoding=LATIN9 I tried to reconfigure locales and restart the server, but I can't get the result I expect. I really don't know what else I could do. Sincerely, -- Stéphane SCHILDKNECHT Président de PostgreSQLFr http://www.postgresqlfr.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] error calling pgmail function
indu ss <[EMAIL PROTECTED]> writes: > On executing the update statement i get error > CONTEXT: compile of PL/pgSQL function "pgmail" near > line 1 > PL/pgSQL function "test" line 10 at assignment Uh, you didn't show us the actual error, nor the problematic line within pgmail. (The reference to function "test" is just a stack back-trace of how you got to pgmail; it does not imply that that's where the error is.) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Postgres 7.3.2 -> 8.1.2 upgrade performance issue
Hi, Recently tried an upgrade from 7.3.2 to 8.1.2. The actual upgrade went pretty well. However my application is now getting >10 times slower INSERT times than it was under 7.3.2. As far as possible I mirrored the settings in postgresql.conf (obviously I couldn't just drop in the old config). Any thoughts as to where to look, or what to do to fix this? Thanks, David. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Number format problem
Hi, There seems to be some tricky behaviour with number formating ant french locale. i tried the following request: select to_char(1485.12, '9G999D99'); I was expecting to get: 1 485,12 But, surprinsingly, I got 1,1485,12. My postgresql server is an 8.1.2 version. The same problem occurs under Ubuntu Breezy and Debian Testing. My current configuration is [EMAIL PROTECTED] client_encoding=LATIN9 server_encoding=LATIN9 I tried to reconfigure locales and restart the server, but I can't get the result I expect. I really don't know what else I could do. Sincerely, -- Stéphane SCHILDKNECHT Président de PostgreSQLFr http://www.postgresqlfr.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] C Language Stored Procedure Returning No Data
On Feb 2, 2006, at 7:17 PM, Michael Fuhr wrote:
On Wed, Feb 01, 2006 at 12:56:30PM -0500, [EMAIL PROTECTED] wrote:
From a C stored procedure, how can I tell Postgres to pass on to
the Java client that there is No Data? A zero length byte array or
a null value is not the same as No Data.
If you declare the function with "RETURNS bytea" then the function
must return something; if zero-length data and NULL aren't suitable
for indicating no data then you could raise an error and catch that
error in the client. Another possibility would be to make the
function set-returning ("RETURNS SETOF bytea" and some code changes)
and return no rows to indicate no data.
Another possibility is to log an exception with elog & company.
However that will also have the side effect of rolling back the txn
if you are currently in one.
--
Jeff Trout <[EMAIL PROTECTED]>
http://www.jefftrout.com/
http://www.stuarthamm.net/
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Alternative to knoda, kexi and rekall?
> > PgAdmin III is available on Debian. Its package name is > pgadmin3. Try doing: > > > > apt-cache show pgadmin3 > > I am running Sarge and have found only pgaccess. > So it is in Testing or Unstable... You need to add one of the pgsql mirrors to get it. See http://www.pgadmin.org/download/debian.php. They have packages for Sarge and Etch both. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Alternative to knoda, kexi and rekall?
Am 2006-02-02 14:19:42, schrieb Juan Jose Comellas: > PgAdmin III is available on Debian. Its package name is pgadmin3. Try doing: > > apt-cache show pgadmin3 I am running Sarge and have found only pgaccess. So it is in Testing or Unstable... Will look for it tomorrow. Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSM LinuxMichi 0033/3/8845235667100 Strasbourg/France IRC #Debian (irc.icq.com) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PgSQL as part of commercial product
Read our FAQ. --- Arun Kannapiran wrote: > Dear Sir/Madam, > > I would appreciate it if you could answer the below queries. > > What are the licencing requirements for PgSQL ? > > The company I work for is building a client-server application with a > PgSQL backend to be sold commercially on the market, are there any > licencing or other payments that need to be made ? > > Thanks, > > Arun Kannapiran > > > > > Do you Yahoo!? > The New Yahoo! Movies: Check out the Latest Trailers, Premiere Photos and > full Actor Database. > http://au.movies.yahoo.com > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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
[GENERAL] PgSQL as part of commercial product
Dear Sir/Madam, I would appreciate it if you could answer the below queries. What are the licencing requirements for PgSQL ? The company I work for is building a client-server application with a PgSQL backend to be sold commercially on the market, are there any licencing or other payments that need to be made ? Thanks, Arun Kannapiran Do you Yahoo!? The New Yahoo! Movies: Check out the Latest Trailers, Premiere Photos and full Actor Database. http://au.movies.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] error calling pgmail function
Hello, I want to send mail on update of a field in a table. I'm using pgmail() function (from sourceforge) . I'm calling this function from another function which is called by trigger on update of the field. On executing the update statement i get error CONTEXT: compile of PL/pgSQL function "pgmail" near line 1 PL/pgSQL function "test" line 10 at assignment The line 10 corresponds to the line where pgmail function is called. pos := pgmail(p1,p2,p3,p4); Can anyone help me? I'm using postgres 7.4.7 version. Thanks __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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: [GENERAL] Automating backup
Richard Sydney-Smith <[EMAIL PROTECTED]> writes:
> pch := pchar('pg_dump -C -h '+host+' -U '+usr+' -p '+pswd+ ' -f
> '+bckup_path+' '+dbase);
>
> to postgres.
>
> as the operator is obviously logged in how do I
> (1) trap their user id
> (2) Send the call to pg_dump without knowing their password?
>
> I expect this is a well worn route and am hoping not to have to
> reinvent a wheel.
I don't think it's "well-worn" at all--everyone I've ever heard of
runs pg_dump from a cron script.
Why not have a shell script run by the operator that runs pg_dump and
then calls psql to insert the log record (assuming the dump succeeds)?
Putting the logic inside of the database doesn't seem to buy you
anything AFAICS.
-Doug
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] News from IBM (DB2)
On Fri, 3 Feb 2006, Nicolay A Vasiliev wrote: > Hello there! > > http://news.zdnet.co.uk/software/0,39020381,39249666,00.htm > > What do you think about this? > > Nicolay > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > I think that explain analyze will still be my friend. Cheers, Michael ---(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
