Re: [GENERAL] Kernel kills postgres process - help need

[Alvaro Herrera]

Hervé Piedvache escribió:

> Another, may be stupid question, but when you have several web nodes like 
> me ... with several physical database (I'm not talking about replication, 
> it's just that the web node can contact 3 or 4 differents database for 
> differents applications), what is the best way to process with a pooler ... 
> install one on each node or one on each database ?

I don't really know the answer to this, but if you have one per database
server, then all the web nodes are going to share the connections to
that database server.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(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] 8.3: where's the replacement tsearch2 module?

[Pierre Thibaudeau]

> > My database uses tsearch2.  I was about to follow the conversions
> > instructions found at Appendix F31 (on the new tsearch module).
> > http://www.postgresql.org/docs/8.3/static/tsearch2.html
> The docs will need to be updated because tsearch2 is now in the core and
> should already be available, it's not an external module any more.

Appendix F31 (which I quoted previously) indeed acknowledges that fact
(that tsearch2 is in the core).  The instructions I focused on explain
how to "convert" an 8.2 database already using tsearch2, without going
through the trouble of renaming the tsearch2 functions.  Therefore, I
think the docs are fine.  The problem is the missing tsearch2.sql
script...

>You'd have to edit it to replace MODULE_PATHNAME with the right thing.
>A bigger problem is that the tsearch2 dll is probably omitted too :-(

Indeed, I cannot find tsearch2.dll anywhere...

---(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] Mechanics of Select

[Willem Buitendyk]

As others have suggested my big problem with the function I wrote was 
that I had made it Volatile instead of Immutable (it is no doubt 
suffering from code bloat as well).  That made all the difference. 
Curiously though - I tried it just with the date_trunc function and it 
was just as slow as my old Volatile function.


select * from track where datetime >= '2007-04-01' and datetime < 
date_trunc('month','2007-04-01'::timestamp)+interval '1 month'; was 
about 55s
select * from track where datetime >= '2007-04-01' and datetime < 
first_day_next_month('2007-04-01'); was about 36s


cheers

Greg Smith wrote:

On Sun, 10 Feb 2008, Willem Buitendyk wrote:

I have the following function that returns the first day of the next 
month from whatever date is inserted.


See if you can do this with date_trunc instead to avoid calling a 
function, which avoids the whole thing.  The first day of next month is:


select date_trunc('month',now())+interval '1 month';

I'd be curious how the runtime using that compares with the plpgsql 
version you've done.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD




---(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] Mechanics of Select

[Alban Hertroys]

On Feb 11, 2008, at 12:43 AM, brian wrote:

Try:

CREATE OR REPLACE FUNCTION first_day_next_month(inputdate date)
 RETURNS date AS
$BODY$
DECLARE
resultdate date;
BEGIN
SELECT INTO resultdate to_date(to_char((inputdate + interval \
'1 month'), '-MM') || '-01', '-mm-dd');
RETURN resultdate;
END;
$BODY$
LANGUAGE 'plpgsql';



No need for the variable or the SELECT, and it's an immutable  
function, so better define that. Besides that it's probably better to  
use the date_trunc function here.


Try:

CREATE OR REPLACE FUNCTION first_day_next_month(inputdate date)
 RETURNS date AS
$BODY$
BEGIN
RETURN date_trunc('month', inputdate + interval '1 month');
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;

And with that I wonder why you'd even need a function :)

Another thing I've taught myself is to prefix local variables and  
parameters in functions, so that they can NEVER accidentally match a  
column name that you use in a query (I usually use '_'). Otherwise  
you can get silly queries like "SELECT * FROM table WHERE x = x" that  
look perfectly fine while you're writing them down, being perfectly  
able to make the distinction between *variable x* and *column x* in  
your mind.


Regards,
Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,47af8f8e167321323610058!



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] 8.3: where's the replacement tsearch2 module?

[Tom Lane]

"Pierre Thibaudeau" <[EMAIL PROTECTED]> writes:
> I am assuming that the "replacement tsearch2 module" is some file
> "tsearch2.sql" found in the folder share/contrib.  However, no such
> file, or anything that looks remotely like it, in that folder or in
> any folder around.

Hmm, it's definitely getting built by the Windows buildfarm members,
so this is not an oversight in the MSVC build script.  I think it
must be an omission in the installer ... Dave, Magnus?

> (If I download the sources, I can find a 16 Kb file entitled
> "tsearch2.sql.in" inside contrib/tsearch2.  Is that the file I want?)

You'd have to edit it to replace MODULE_PATHNAME with the right thing.
A bigger problem is that the tsearch2 dll is probably omitted too :-(

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] 8.3: where's the replacement tsearch2 module?

[Chris]

Pierre Thibaudeau wrote:

I just downloaded the 8.3 Windows installation (binary with installer).

My database uses tsearch2.  I was about to follow the conversions
instructions found at Appendix F31 (on the new tsearch module).
http://www.postgresql.org/docs/8.3/static/tsearch2.html

However, I hit a problem when I get to step 2:  "In the new
installation, create empty database(s) and install the replacement
tsearch2 module into each database that will use text search."


The docs will need to be updated because tsearch2 is now in the core and 
should already be available, it's not an external module any more.


--
Postgresql & php tutorials
http://www.designmagick.com/

---(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] Mechanics of Select

[brian]

Willem Buitendyk wrote:
I have the following function that returns the first day of the next 
month from whatever date is inserted.  If I use this as part of a select 
statement then it takes almost twice as long to perform.  Is this 
because for each scanned record this function is being called?  If so 
any ideas how I could make this only occur once?


For instance:

select * from track where datetime >= '2007-04-01' and datetime <  
'2007-05-01'; takes about 30 ms to return 650K rows.


select * from track where datetime >= '2007-04-01' and datetime < 
first_day_next_month'2007-04-01'; takes about 55 ms to return 650K rows


CREATE OR REPLACE FUNCTION first_day_next_month(inputdate date)
 RETURNS date AS
$BODY$
declare
inputmonth1 integer;
inputyear1 integer;
inputmonth2 integer;
inputyear2 integer;
resultdate date;
BEGIN
inputmonth1 = extract(month from inputdate)::integer;  inputyear1 = 
extract(year from inputdate)::integer;


if inputmonth1 = 12 then
inputyear2 = inputyear1 + 1;
else
inputyear2 = inputyear1;
end if;

if inputmonth1 = 12 then
inputmonth2 = 1;
else
inputmonth2 = inputmonth1 + 1;
end if;

resultdate = (inputyear2)::text || '-' || (inputmonth2)::text || '-' || 
'01';

resultdate = to_date(resultdate::text,'-MM-DD');

RETURN resultdate;
END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE
 COST 100;




Try:

CREATE OR REPLACE FUNCTION first_day_next_month(inputdate date)
 RETURNS date AS
$BODY$
DECLARE
resultdate date;
BEGIN
SELECT INTO resultdate to_date(to_char((inputdate + interval \
'1 month'), '-MM') || '-01', '-mm-dd');
RETURN resultdate;
END;
$BODY$
LANGUAGE 'plpgsql';

Mind the wrap.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] copy question - fixed width?

[Tom Lane]

Klint Gore <[EMAIL PROTECTED]> writes:
> Is there any way to make copy work with fixed width files?

I'd suggest using a simple sed script to convert the data into the
format COPY understands.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Mechanics of Select

[Tom Lane]

Willem Buitendyk <[EMAIL PROTECTED]> writes:
> I have the following function that returns the first day of the next 
> month from whatever date is inserted.  If I use this as part of a select 
> statement then it takes almost twice as long to perform.  Is this 
> because for each scanned record this function is being called?  If so 
> any ideas how I could make this only occur once?

Don't declare it VOLATILE, when (AFAICS) you have no reason to.
It would be evaluated only once if it were marked IMMUTABLE.

BTW, I think you're doing it the hard way --- this could almost
certainly be a one-liner if you were using the available date
arithmetic facilities.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Mechanics of Select

[Bill Moran]

Willem Buitendyk <[EMAIL PROTECTED]> wrote:
>
> I have the following function that returns the first day of the next 
> month from whatever date is inserted.  If I use this as part of a select 
> statement then it takes almost twice as long to perform.  Is this 
> because for each scanned record this function is being called?

An explain of the query would help you answer that question.

>  If so 
> any ideas how I could make this only occur once?

Don't mark it as VOLITILE.  Sounds like an IMMUTABLE function to me.
PostgreSQL is doing exactly what you told it to do.

> For instance:
> 
> select * from track where datetime >= '2007-04-01' and datetime <  
> '2007-05-01'; takes about 30 ms to return 650K rows.
> 
> select * from track where datetime >= '2007-04-01' and datetime < 
> first_day_next_month'2007-04-01'; takes about 55 ms to return 650K rows
> 
> CREATE OR REPLACE FUNCTION first_day_next_month(inputdate date)
>   RETURNS date AS
> $BODY$
> declare
> inputmonth1 integer;
> inputyear1 integer;
> inputmonth2 integer;
> inputyear2 integer;
> resultdate date;
> BEGIN
> inputmonth1 = extract(month from inputdate)::integer;  
> inputyear1 = extract(year from inputdate)::integer;
> 
> if inputmonth1 = 12 then
> inputyear2 = inputyear1 + 1;
> else
> inputyear2 = inputyear1;
> end if;
> 
> if inputmonth1 = 12 then
> inputmonth2 = 1;
> else
> inputmonth2 = inputmonth1 + 1;
> end if;
> 
> resultdate = (inputyear2)::text || '-' || (inputmonth2)::text || '-' || 
> '01';
> resultdate = to_date(resultdate::text,'-MM-DD');
> 
> RETURN resultdate;
> END;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE
>   COST 100;
> 
> 
> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster


-- 
Bill Moran
http://www.potentialtech.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] Mechanics of Select

[Greg Smith]

On Sun, 10 Feb 2008, Willem Buitendyk wrote:

I have the following function that returns the first day of the next month 
from whatever date is inserted.


See if you can do this with date_trunc instead to avoid calling a 
function, which avoids the whole thing.  The first day of next month is:


select date_trunc('month',now())+interval '1 month';

I'd be curious how the runtime using that compares with the plpgsql 
version you've done.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Alter Temporary table in Stored Procedure

[Tom Lane]

"Nykolyn, Andrew" <[EMAIL PROTECTED]> writes:
> I have the following code in my stored procedure:

>   create temporary table t_resultset as select * from
> get_createtempmsg();
>   alter table t_resultset add column seq serial;

> The error I get is - relation "public.t_resultset"  does not exist.

> I am using 8.1.3

Hmm, well, the case seems to work for me in 8.1.11, but only for rather
small values of "work": the sequence is created in the public schema,
which is the wrong place for a temp object.  On looking back, this whole
area is badly broken in releases before 8.3 --- see discussion of
bug #3403:
http://archives.postgresql.org/pgsql-bugs/2007-06/msg00114.php

We concluded that the fix was too invasive to risk back-patching,
so it's unlikely that anything will ever get done about it in 8.1.x
or 8.2.x.  If you really need this to work, I'd suggest updating
to 8.3.

regards, tom lane

---(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] copy question - fixed width?

[Klint Gore]

Is there any way to make copy work with fixed width files?

eg
 create table t1 (code char(5), description char(30));
 copy t1 from '/tmp/afile' delimiter as 

where afile looks something like
1test16789012345678901234567890
2test26789012345678901234567890
3test36789012345678901234567890

at the moment i have a function that does
 create temp table tmp1 (line text);
 copy tmp1 from '/tmp/afile';
 looking at information schema for t1 definition>
 magnitude quicker to use the view>

 insert into t1 select * from tmpview;

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266

EMail: [EMAIL PROTECTED]


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Mechanics of Select

[Willem Buitendyk]

I have the following function that returns the first day of the next 
month from whatever date is inserted.  If I use this as part of a select 
statement then it takes almost twice as long to perform.  Is this 
because for each scanned record this function is being called?  If so 
any ideas how I could make this only occur once?


For instance:

select * from track where datetime >= '2007-04-01' and datetime <  
'2007-05-01'; takes about 30 ms to return 650K rows.


select * from track where datetime >= '2007-04-01' and datetime < 
first_day_next_month'2007-04-01'; takes about 55 ms to return 650K rows


CREATE OR REPLACE FUNCTION first_day_next_month(inputdate date)
 RETURNS date AS
$BODY$
declare
inputmonth1 integer;
inputyear1 integer;
inputmonth2 integer;
inputyear2 integer;
resultdate date;
BEGIN
inputmonth1 = extract(month from inputdate)::integer;  
inputyear1 = extract(year from inputdate)::integer;


if inputmonth1 = 12 then
inputyear2 = inputyear1 + 1;
else
inputyear2 = inputyear1;
end if;

if inputmonth1 = 12 then
inputmonth2 = 1;
else
inputmonth2 = inputmonth1 + 1;
end if;

resultdate = (inputyear2)::text || '-' || (inputmonth2)::text || '-' || 
'01';

resultdate = to_date(resultdate::text,'-MM-DD');

RETURN resultdate;
END;
$BODY$
 LANGUAGE 'plpgsql' VOLATILE
 COST 100;



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] pg_restore seems slow

[Willem Buitendyk]

I did use the 'd' switch but I didn't use the 'C' switch so I'm not sure 
a database was actually created.  Anyways, after I used the correct 
switches all work fast - really fast.  About a 1M records per minute.  I 
was able to peek into the server processes to see the current copy 
commands in effect.  Would still like to see a progress indicator though :)


Willem

Gurjeet Singh wrote:
On Feb 9, 2008 10:42 AM, Willem Buitendyk <[EMAIL PROTECTED] 
> wrote:


I'm trying to restore my database from 8.26 into 8.3 (win32) but find
the process to be exceedingly slow.  The database has about 60M
records.
I realize there will be differences based on hardware, available
memory,
complexity of records but when I first tried a restore with the
verbose
option I was able to calculate based on the index incrementing that it
was inserting about 6500 records per minute.
At that rate it would take 153 hours to restore my db.  I then tried
minimizing the verbosity window and would open it only after a minute
and the speed was improved to about 2 records per minute.  I'm
hoping without the verbose option that the speed increases to at least
20 records per minute which would be a fairly reasonable 5 hours.
So is there any way besides using verbose to calculate the speed at
which pg_restore is inserting records?  It would be great to have a
'progress' option so that a person could time going out for a sail in
the morning and then return at just the right time.  Guess you
know what
I'd rather be doing instead of staring at the command prompt :)

---(end of
broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


By any chance, are you using -d or -D option while doing pg_dump?

Best regards,
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

17° 29' 34.37"N,   78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N,   73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device 



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] ERROR: expected just one rule action

[Tom Lane]

Dave Livesay <[EMAIL PROTECTED]> writes:
> I'm sure it has something to do with the complex build environment  
> and getting up to speed with the new tools.

FWIW, I just verified that your queries work fine for me in CVS HEAD
on a G4, under both 10.4 (building with Xcode 2.5) and 10.5 (building
with Xcode 3.0).  So it does seem likely that it's the 10.5-back-to-10.4
business that's the problem.  I know zip about that aspect of things,
though.

If these are "universal" (Intel+PPC) binaries, that could be an issue
too.  There's been some discussion recently about how to build universal
binaries for PG, but I don't think anyone's figured out a really nice
way to do it.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] 8.3: where's the replacement tsearch2 module?

[Pierre Thibaudeau]

I just downloaded the 8.3 Windows installation (binary with installer).

My database uses tsearch2.  I was about to follow the conversions
instructions found at Appendix F31 (on the new tsearch module).
http://www.postgresql.org/docs/8.3/static/tsearch2.html

However, I hit a problem when I get to step 2:  "In the new
installation, create empty database(s) and install the replacement
tsearch2 module into each database that will use text search."

I am assuming that the "replacement tsearch2 module" is some file
"tsearch2.sql" found in the folder share/contrib.  However, no such
file, or anything that looks remotely like it, in that folder or in
any folder around.

(If I download the sources, I can find a 16 Kb file entitled
"tsearch2.sql.in" inside contrib/tsearch2.  Is that the file I want?)

For reference, here is the full content of the share/contrib folder,
after the Windows installation:

_int.sql
adminpack.sql
autoinc.sql
btree_gist.sql
chkpass.sql
cube.sql
dblink.sql
earthdistance.sql
fuzzystrmatch.sql
hstore.sql
insert_username.sql
int_aggregate.sql
isn.sql
lo.sql
ltree.sql
moddatetime.sql
pageinspect.sql
pg_buffercache.sql
pg_freespacemap.sql
pg_trgm.sql
pgcrypto.sql
pgrowlocks.sql
pgstattuple.sql
pgxml.sql
pldbgapi.sql
refint.sql
seg.sql
sslinfo.sql
tablefunc.sql
timetravel.sql
uninstall__int.sql
uninstall_adminpack.sql
uninstall_btree_gist.sql
uninstall_chkpass.sql
uninstall_cube.sql
uninstall_dblink.sql
uninstall_earthdistance.sql
uninstall_fuzzystrmatch.sql
uninstall_hstore.sql
uninstall_int_aggregate.sql
uninstall_isn.sql
uninstall_lo.sql
uninstall_ltree.sql
uninstall_pageinspect.sql
uninstall_pg_buffercache.sql
uninstall_pg_freespacemap.sql
uninstall_pg_trgm.sql
uninstall_pgrowlocks.sql
uninstall_pgstattuple.sql
uninstall_pgxml.sql
uninstall_seg.sql
uninstall_sslinfo.sql
uninstall_tablefunc.sql

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Trouble with Mixed UTF-8 and Latin1 data

[Hannes Dorbath]

valgog wrote:
I know it looks like a mess

Indeed, that is what client encoding is for :)


One idea is to write the function, that will normalize the data to
UTF-8 in PL/pgSQL (that I could not do from the first try)


You could use convert(), iterate over the rows and catch the exceptions 
in pl/pgsql.


http://www.postgresql.org/docs/8.3/interactive/functions-string.html
http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html


--
Best regards,
Hannes Dorbath

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] ERROR: expected just one rule action

[Dave Livesay]

On Feb 10, 2008, at 2:21 PM, Tom Lane wrote:


Dave Livesay <[EMAIL PROTECTED]> writes:

This is the first version of PostgreSQL provided by a certain very
helpful fellow, who has been making PostgreSQL binaries available for
us Mac users for years, since he upgraded to Mac OS X 10.5 (aka
Leopard). I have not yet upgraded to Leopard, due to some serious
compatibility problems it introduced, so I am testing this build for
compatibility with Mac OS X 10.4 (aka Tiger). It is working
flawlessly on my Intel Mac, but I'm encountering the errors
previously mentioned on an older G4 Mac.


Hmm.  I'm fairly confident that PG works fine on 10.4/PPC when built
on that platform, since (a) we have a build farm member testing that
case and (b) up till about a week ago I was testing that case
reasonably regularly on my own laptop.  (Now I'm using 10.5.)
So what it sounds like to me is a tools compatibility problem.
It's odd that you'd see a problem only here and not all over the
place ... have you tried running the regression tests against this
build?

regards, tom lane


I'm sure it has something to do with the complex build environment  
and getting up to speed with the new tools. If you have some  
experience with the Leopard tools, maybe he could post his questions  
here if he can't figure it out. Or maybe I should just figure out how  
to build it.


---(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] Alter Temporary table in Stored Procedure

[Nykolyn, Andrew]

> Does any know of a way that I can alter a temporary table by adding a 
> serial column within a stored procedure after it has been created?

It should just work.  What did you try, exactly, and what error message
did you get?


I have the following code in my stored procedure:

create temporary table t_resultset as select * from
get_createtempmsg();

alter table t_resultset add column seq serial;

Where get_createtempmsg() is a stored procedure that returns a composite
type with no records.

The error I get is - relation "public.t_resultset"  does not exist.

I am using 8.1.3

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] ERROR: expected just one rule action

[Tom Lane]

Dave Livesay <[EMAIL PROTECTED]> writes:
> This is the first version of PostgreSQL provided by a certain very  
> helpful fellow, who has been making PostgreSQL binaries available for  
> us Mac users for years, since he upgraded to Mac OS X 10.5 (aka  
> Leopard). I have not yet upgraded to Leopard, due to some serious  
> compatibility problems it introduced, so I am testing this build for  
> compatibility with Mac OS X 10.4 (aka Tiger). It is working  
> flawlessly on my Intel Mac, but I'm encountering the errors  
> previously mentioned on an older G4 Mac.

Hmm.  I'm fairly confident that PG works fine on 10.4/PPC when built
on that platform, since (a) we have a build farm member testing that
case and (b) up till about a week ago I was testing that case
reasonably regularly on my own laptop.  (Now I'm using 10.5.)
So what it sounds like to me is a tools compatibility problem.
It's odd that you'd see a problem only here and not all over the
place ... have you tried running the regression tests against this
build?

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

Re: [GENERAL] Alter Temporary table in Stored Procedure

[Tom Lane]

"Nykolyn, Andrew" <[EMAIL PROTECTED]> writes:
> Does any know of a way that I can alter a temporary table by adding a
> serial column within a stored procedure after it has been created?

It should just work.  What did you try, exactly, and what error message
did you get?

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Is PG a moving target?

[Erik Jones]

On Feb 10, 2008, at 10:44 AM, Dave Livesay wrote:

I noticed that, in one of the third-party databases I have  
installed on my server, one foreign key constraint could not be  
implemented. (The key columns are of incompatible types.) In  
previous upgrades I had seen a warning concerning this constraint,  
and had passed this information along to the people who maintain  
this database, but they ignored it. Now the warnings have turned  
into an error, and the constraint isn't being implemented.


So this is an issue I've been aware of for a long time (more than  
two years, in fact), and if I'd been responsible for maintaining  
the database, I would have fixed it long ago.


Maybe I'm overly optimistic, but I get the impression that, if you  
pay attention to warnings and fix your problems in a timely manner,  
you're unlikely to be blindsided when the rules get tightened up in  
subsequent releases.


True, however, there was never a "transitional" release that issued  
warning when using implicit type casts in expressions like (heh):  
some_timestamp_field LIKE '2008-01-02%'.  I think having a  
transitionary period in which warnings were emitted or having the  
ability to switch the casting behavior on and off, much like what was  
done with backslash escaped strings, would have made the change much  
more appealing.  For large applications that used the implicit type  
casts a lot (and I even remember the implicit timestamp to string  
casting being recommended usage on this list) being able to turn the  
behaviour on and off on a per-session basis would have made the  
migration LOADS simpler.


Erik Jones

DBA | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Change column type to numeric

[Michael Fuhr]

On Sun, Feb 10, 2008 at 11:37:45AM -0700, Jake Franklin wrote:
> test=# alter table foo alter column amount type numeric(10,2) USING
> cast(amount AS numeric);
> ERROR:  invalid input syntax for type numeric: ""
> 
> I'm assuming that it's trying to cast a blank value as numeric and
> failing.  Does anyone know of an easy way to work around this?

You could convert the empty strings to NULL:

USING cast(nullif(amount, '') AS numeric)

-- 
Michael Fuhr

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Change column type to numeric

[Jake Franklin]

Forgive me if this question has an obvious answer, I'm sorta new to posgresql.

I have a table that's already populated with quite a bit of records.
I'd like to alter a column called "amount" from character varying to
numeric, so I don't have to re-load all of my data sets.

I've already dropped the column default.  When attempting to change
the column type, I get:

test=# alter table foo alter column amount type numeric(10,2) USING
cast(amount AS numeric);
ERROR:  invalid input syntax for type numeric: ""

I'm assuming that it's trying to cast a blank value as numeric and
failing.  Does anyone know of an easy way to work around this?

Thanks!

--Jake

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] ERROR: expected just one rule action

[Dave Livesay]

The history is rather interesting. :-)

This is the first version of PostgreSQL provided by a certain very  
helpful fellow, who has been making PostgreSQL binaries available for  
us Mac users for years, since he upgraded to Mac OS X 10.5 (aka  
Leopard). I have not yet upgraded to Leopard, due to some serious  
compatibility problems it introduced, so I am testing this build for  
compatibility with Mac OS X 10.4 (aka Tiger). It is working  
flawlessly on my Intel Mac, but I'm encountering the errors  
previously mentioned on an older G4 Mac.


Since there are so many variables involved--major PostgreSQL upgrade,  
major Mac OS X upgrade (yes, 10.4 -> 10.5 is a major upgrade ;-) ),  
support for two different processor families--I'm just trying to  
narrow down what he needs to look at, and your suggestions are very  
helpful.


I hadn't tried any backslash commands, but someone else with a  
similar configuration said he was seeing errors with \du and other  
psql commands. I first noticed the problem when connecting to  
databases in PGAdmin3. (I'm a GUI-oriented user.)


"SELECT * FROM pg_rewrite" seems to work fine.

Thank you very much for your help!

On Feb 10, 2008, at 12:42 PM, Tom Lane wrote:


Dave Livesay <[EMAIL PROTECTED]> writes:

Can anyone interpret this error message?


Something's whacked out about your ON SELECT rules for these views.
Further than that is harder to say --- have you tried looking at
\d output for them, or looked into pg_rewrite?


It appears in response to each of the following queries in one
instance of PostgreSQL 8.3:


What's the history of that instance?

regards, tom lane




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] Alter Temporary table in Stored Procedure

[Nykolyn, Andrew]

Does any know of a way that I can alter a temporary table by adding a
serial column within a stored procedure after it has been created?  Any
help greatly appreciated.  Thanks.

Andy Nykolyn
Northrop Grumman

Re: [GENERAL] Is PG a moving target?

[Dave Livesay]

I noticed that, in one of the third-party databases I have installed  
on my server, one foreign key constraint could not be implemented.  
(The key columns are of incompatible types.) In previous upgrades I  
had seen a warning concerning this constraint, and had passed this  
information along to the people who maintain this database, but they  
ignored it. Now the warnings have turned into an error, and the  
constraint isn't being implemented.


So this is an issue I've been aware of for a long time (more than two  
years, in fact), and if I'd been responsible for maintaining the  
database, I would have fixed it long ago.


Maybe I'm overly optimistic, but I get the impression that, if you  
pay attention to warnings and fix your problems in a timely manner,  
you're unlikely to be blindsided when the rules get tightened up in  
subsequent releases.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] ERROR: expected just one rule action

[Tom Lane]

Dave Livesay <[EMAIL PROTECTED]> writes:
> Can anyone interpret this error message?

Something's whacked out about your ON SELECT rules for these views.
Further than that is harder to say --- have you tried looking at
\d output for them, or looked into pg_rewrite?

> It appears in response to each of the following queries in one  
> instance of PostgreSQL 8.3:

What's the history of that instance?

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Lockless pg_buffercache

[Tom Lane]

"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> So, what's the real burden like from the pg_buffercache contrib module?

I wonder whether pg_buffercache should be changed to work like the
statistics views do, ie, you take a snapshot during the first call
within a transaction.  This would allow correlation of the results
across multiple queries.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Where is the system-wide psqlrc on RHEL4?

[Andrej Ricnik-Bay]

On 10/02/2008, Dean Gibson (DB Administrator) <[EMAIL PROTECTED]> wrote:
> It's not installed in the base/server/libs RPMs.  I had to search the
> uninstalled PostgreSQL RPMs for it, and then (temporarily) install the
> "devel" RPM to run it.  For CentOS 4.4 & RHEL4, the system-wide psqlrc
> is in /etc/sysconfig/pgsql/
Another alternative (quick & dirty) would have been to
strace -o whereisit psql
and
grep psqlrc whereisit


> -- Dean
Cheers,
Andrej

-- 
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

---(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] ERROR: expected just one rule action

[Dave Livesay]

Can anyone interpret this error message?

It appears in response to each of the following queries in one  
instance of PostgreSQL 8.3:
SELECT usecreatedb, usesuper, CASE WHEN usesuper THEN  
pg_postmaster_start_time() ELSE NULL END as upsince FROM pg_user  
WHERE usename=current_user ;
SELECT rolcreaterole, rolcreatedb FROM pg_roles WHERE rolname =  
current_user;
SELECT setting FROM pg_settings WHERE name IN ('autovacuum',  
'track_counts');
SELECT oid, *, pg_catalog.shobj_description(oid, 'pg_authid') AS  
description FROM pg_roles WHERE NOT rolcanlogin ORDER BY rolname;
SELECT oid, *, pg_catalog.shobj_description(oid, 'pg_authid') AS  
description FROM pg_roles WHERE rolcanlogin ORDER BY rolname;


These queries work fine in another instance of 8.3, so it's not the  
queries themselves.


---(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] POLL: Women-sized t-shirts for PostgreSQL

[Lincoln Yeoh]

At 08:48 AM 2/9/2008, Alvaro Herrera wrote:

Joshua D. Drake escribió:
> Richard Broersma Jr wrote:

>> I personally wouldn't even mind having a PG polo that has 3rd part
>> vendor logos on the sleeves if that would help make PG polo shirts
>> available.
>
> O.k., o.k. :) I will look into costs.

Hmm, did this go anywhere?  I still look for the shiny new Pg polos on
the mail every day, only to be disappointed.


How about have velcro pads on the sleeves so that 
you can change the vendor logos whenever 
necessary, while keeping the base Postgresql?


So you can walk into some place with an IBM bunch 
in the morning, then in the afternoon you can go 
with a Red Hat team to another place, without 
having to change your polo shirt in between :).


After all that "consulting", you might wish to 
burn[1] the used shirt and get a new shirt to wear the next day...


;)

Link.

[1] Or recycle it responsibly.


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Lockless pg_buffercache

[Scott Marlowe]

On Feb 9, 2008 10:34 PM, Markus Bertheau <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I want to use the pg_buffercache contrib module for monitoring our
> server. It takes a lock on all buffers and then on each buffer header
> in order to get a consistent picture of the buffers. I would be
> running the function provided by the module once every 5 minutes.

If it takes 500mS every 5 minutes that might be an acceptable decrease
in performance for that second or two.

> I'm worrying about the performance hit of that - a comment in the code
> says it's horrible for concurrency.

It is, but again, it really depends on the time frame etc.  If you can
run it quickly as a series of related queries or a user function, it
should be fast enough to not be a significant burden.

So, what's the real burden like from the pg_buffercache contrib module?

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq