Re: [GENERAL] MS SQL - PostgreSQL

[Klint Gore]

On Thu, 15 Sep 2005 07:16:25 +0200, Tino Wildenhain <[EMAIL PROTECTED]> wrote:
> Am Donnerstag, den 15.09.2005, 07:43 +0700 schrieb Irfan Syukur:
> > Dear Tino, 
> > 
> > In MS SQL, I can execute store procedure that I do not know it's name yet.
> > 
> > A Simplicity example :
> >CREATE   procedure  dbo.sp_run_batch (@as_spname varchar(20)) with 
> > recompile as
> > 
> >declare @li_retstat  smallint,   
> > @li_status   numeric(1,0), @ls_mesg   varchar(60)
> > 
> >exec @li_retstat = @as_spname @as_mesg = @ls_mesg output --(@as_spname = 
> > the name of stored procedure)
> >select @li_retstat, @ls_mesg
> >return 
> >GO
> > 
> > Can Postgres do that, how ?
> 
> Well, not that I know of. But in your example your function has a name.


Essentially, it's a generic execute procedure.  Pass it a procedure name
and it runs it.  It's just "execute" from plpgsql.

http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html

klint.

+---+-+
: Klint Gore: "Non rhyming:
: EMail   : [EMAIL PROTECTED]   :  slang - the:
: Snail   : A.B.R.I.:  possibilities  :
: Mail  University of New England   :  are useless"   :
:   Armidale NSW 2351 Australia : L.J.J.  :
: Fax : +61 2 6772 5376 : :
+---+-+

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

Re: [GENERAL] ERROR: type "temp_gc" already exists

[Janning Vygen]

> Janning Vygen  writes:
> > Am Samstag, 10. September 2005 18:05 schrieb Tom Lane:
> >> If there's no pg_depend entry then DROP TYPE should work.  Otherwise
> >> you might have to resort to manually DELETEing the pg_type row.
> 
> > Thanks for your detailed answer. I don't want to do anything wrong. To be 
> > sure, i have some more questions:
> 
> > - There is no entry in pg_depend. Should i just drop the entry from 
pg_type or 
> > should i REINDEX anyway?
> 
> Well, what did you do to check that there was no entry?  If the index is
> corrupt and you issued a query that used the index, it might have failed
> to find an entry that's actually there in the table (in fact, if we're
> assuming the DROP TYPE didn't happen because the system didn't find the
> dependency row while dropping the table, this is pretty much exactly
> what you'd expect).  I'd REINDEX and then check again.

What i did so far:

$ REINDEX TABLE pg_depend

$ SELECT * from pg_depend where objid = 16562879;
 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
-+---+--++--+-+-
(0 rows)


$ SELECT * from pg_type where typname = 'temp_gc';
typname | typnamespace | typowner | typlen | typbyval | typtype | typisdefined 
| typdelim | typrelid | typelem | typinput  | typoutput  | typreceive  |   
typsend   | typanalyze | typalign | typstorage | typnotnull | typbasetype | 
typtypmod | typndims | typdefaultbin | typdefault
-+--+--++--+-+--+--+--+-+---++-+-++--+++-+---+--+---+
 temp_gc |16847 |  100 | -1 | f| c   | t
| ,| 16562879 |   0 | record_in | record_out | record_recv | 
record_send | -  | d| x  | f  |   0 |   
 
-1 |0 |   |
(1 row)


$ DROP TYPE temp_gc;
ERROR:  type "temp_gc" does not exist

> If there's no pg_depend entry then DROP TYPE should work.  Otherwise
> you might have to resort to manually DELETEing the pg_type row.

There is no pg_depend entry as far as i can tell, but DROP TYPE doesn't work. 
Can i just DELETE the pg_type row now??

Sorry for asking again and again, but manipulating system catalogs seems to me 
very dangerous. It's live database and i dont want to do anything wrong.

kind regards
Janning

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

   http://archives.postgresql.org

[GENERAL] Help trying to write my first plpgsql function...

[Bjørn T Johansen]

I am trying to write a function that returns x rows, where x >= 0 and this is 
what I
have come up with...:

CREATE OR REPLACE FUNCTION trykkStatus (pressID SMALLINT) RETURNS REFCURSOR AS '
declare
orderID ordrenew.id%TYPE;
cur REFCURSOR;
begin
select id into orderID from ordrenew where now() between trykkstart and 
produsert and
presseid = $1 limit 1;
if not found then
   raise exception ''No rows'';
open cur for ((select 1 as colid, id, trykkstart, produsert, presseid from 
ordrenew
where produsert < (select trykkstart from ordrenew where id=orderID)
order by produsert desc limit 1)
union
(select 2 as colid, id, trykkstart, produsert, presseid from ordrenew where 
now()
between trykkstart and produsert and presseid = 1 limit 1)
union
(select 3 as colid, id, trykkstart, produsert, presseid from ordrenew where
trykkstart > (select produsert from ordrenew where id=orderID) order by 
trykkstart
limit 1) order by colid);
return(cur);
END;
' LANGUAGE 'plpgsql';


But this just gives me the following error:

syntax error at or near ";" at character 851

And I can't find anything wrong near any ; ?
Also, does this function do what I expect it to do?
And instead of rasing an error when no rows is found, can I return an "empty" 
cursor
instead?


Regards,

BTJ

-- 
---
Bjørn T Johansen

[EMAIL PROTECTED]
---
Someone wrote:
"I understand that if you play a Windows CD backwards you hear strange Satanic 
messages"
To which someone replied:
"It's even worse than that; play it forwards and it installs Windows"
---

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

Re: [GENERAL] Help trying to write my first plpgsql function...

[Gnanavel S]

'IF' block is not ended.On 9/15/05, Bjørn T Johansen <[EMAIL PROTECTED]> wrote:
I am trying to write a function that returns x rows, where x >= 0 and this is what Ihave come up with...:CREATE OR REPLACE FUNCTION trykkStatus (pressID SMALLINT) RETURNS REFCURSOR AS 'declareorderID 
ordrenew.id%TYPE;cur REFCURSOR;beginselect id into orderID from ordrenew where now() between trykkstart and produsert andpresseid = $1 limit 1;if not found then   raise exception ''No rows'';
open cur for ((select 1 as colid, id, trykkstart, produsert, presseid from ordrenewwhere produsert < (select trykkstart from ordrenew where id=orderID)order by produsert desc limit 1)union(select 2 as colid, id, trykkstart, produsert, presseid from ordrenew where now()
between trykkstart and produsert and presseid = 1 limit 1)union(select 3 as colid, id, trykkstart, produsert, presseid from ordrenew wheretrykkstart > (select produsert from ordrenew where id=orderID) order by trykkstart
limit 1) order by colid);return(cur);END;' LANGUAGE 'plpgsql';But this just gives me the following error:syntax error at or near ";" at character 851And I can't find anything wrong near any ; ?
Also, does this function do what I expect it to do?And instead of rasing an error when no rows is found, can I return an "empty" cursorinstead?Regards,BTJ-
Bjørn T Johansen[EMAIL PROTECTED]---Someone wrote:"I understand that if you play a Windows CD backwards you hear strange Satanic messages"
To which someone replied:"It's even worse than that; play it forwards and it installs Windows"--(end of broadcast)---
TIP 6: explain analyze is your friend-- with regards,S.GnanavelSatyam Computer Services Ltd.

Re: [GENERAL] Help trying to write my first plpgsql function...

[Bjørn T Johansen]

Yes, of course

But this function does not do what I need it to do... I want x rows returned, 
but
instead I just get a stringname...
Either how do I use this name or how do I return x rows?


BTJ

Gnanavel S wrote:
> 'IF' block is not ended.
> 
> On 9/15/05, *Bjørn T Johansen* <[EMAIL PROTECTED] >
> wrote:
> 
> I am trying to write a function that returns x rows, where x >= 0
> and this is what I
> have come up with...:
> 
> CREATE OR REPLACE FUNCTION trykkStatus (pressID SMALLINT) RETURNS
> REFCURSOR AS '
> declare
> orderID ordrenew.id%TYPE;
> cur REFCURSOR;
> begin
> select id into orderID from ordrenew where now() between trykkstart
> and produsert and
> presseid = $1 limit 1;
> if not found then
>raise exception ''No rows'';
> open cur for ((select 1 as colid, id, trykkstart, produsert,
> presseid from ordrenew
> where produsert < (select trykkstart from ordrenew where id=orderID)
> order by produsert desc limit 1)
> union
> (select 2 as colid, id, trykkstart, produsert, presseid from
> ordrenew where now()
> between trykkstart and produsert and presseid = 1 limit 1)
> union
> (select 3 as colid, id, trykkstart, produsert, presseid from
> ordrenew where
> trykkstart > (select produsert from ordrenew where id=orderID) order
> by trykkstart
> limit 1) order by colid);
> return(cur);
> END;
> ' LANGUAGE 'plpgsql';
> 
> 
> But this just gives me the following error:
> 
> syntax error at or near ";" at character 851
> 
> And I can't find anything wrong near any ; ?
> Also, does this function do what I expect it to do?
> And instead of rasing an error when no rows is found, can I return
> an "empty" cursor
> instead?
> 
> 
> Regards,
> 
> BTJ
> 
> --
> 
> ---
> 
> Bjørn T Johansen
> 
> [EMAIL PROTECTED] 
> 
> ---
> Someone wrote:
> "I understand that if you play a Windows CD backwards you hear
> strange Satanic messages"
> To which someone replied:
> "It's even worse than that; play it forwards and it installs Windows"
> 
> ---
> 
> ---(end of
> broadcast)---
> TIP 6: explain analyze is your friend
> 
> 
> 
> 
> -- 
> with regards,
> S.Gnanavel
> Satyam Computer Services Ltd.

-- 
---
Bjørn T Johansen (BSc,MNIF)
Executive Manager   
[EMAIL PROTECTED]   Havleik Consulting
Phone : +47 21 69 15 20 Bjørnebærstien 57
Fax : +47 41 13 09 15   N-1348 Rykkinn
Cellular : +47 926 93 298   http://www.havleik.no
---
Someone wrote:
"I understand that if you play a Windows CD backwards you hear strange Satanic 
messages"
To which someone replied:
"It's even worse than that; play it forwards and it installs Windows"
---

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

   http://archives.postgresql.org

Re: [GENERAL] Help trying to write my first plpgsql function...

[Bjørn T Johansen]

Oki, I found a way...

begin;
select trykkstatus(1,'refcurs'); (I have added one parameter to know the cursor 
name)
fetch all from refcurs;
commit;

But this returns two rowsets, first one for the select and then one for the 
fetch,
but how do I get rid of the row that is returned by the select?

BTJ

Bjørn T Johansen wrote:
> Yes, of course
> 
> But this function does not do what I need it to do... I want x rows returned, 
> but
> instead I just get a stringname...
> Either how do I use this name or how do I return x rows?
> 
> 
> BTJ
> 
> Gnanavel S wrote:
> 
>>'IF' block is not ended.
>>
>>On 9/15/05, *Bjørn T Johansen* <[EMAIL PROTECTED] >
>>wrote:
>>
>>I am trying to write a function that returns x rows, where x >= 0
>>and this is what I
>>have come up with...:
>>
>>CREATE OR REPLACE FUNCTION trykkStatus (pressID SMALLINT) RETURNS
>>REFCURSOR AS '
>>declare
>>orderID ordrenew.id%TYPE;
>>cur REFCURSOR;
>>begin
>>select id into orderID from ordrenew where now() between trykkstart
>>and produsert and
>>presseid = $1 limit 1;
>>if not found then
>>   raise exception ''No rows'';
>>open cur for ((select 1 as colid, id, trykkstart, produsert,
>>presseid from ordrenew
>>where produsert < (select trykkstart from ordrenew where id=orderID)
>>order by produsert desc limit 1)
>>union
>>(select 2 as colid, id, trykkstart, produsert, presseid from
>>ordrenew where now()
>>between trykkstart and produsert and presseid = 1 limit 1)
>>union
>>(select 3 as colid, id, trykkstart, produsert, presseid from
>>ordrenew where
>>trykkstart > (select produsert from ordrenew where id=orderID) order
>>by trykkstart
>>limit 1) order by colid);
>>return(cur);
>>END;
>>' LANGUAGE 'plpgsql';
>>
>>
>>But this just gives me the following error:
>>
>>syntax error at or near ";" at character 851
>>
>>And I can't find anything wrong near any ; ?
>>Also, does this function do what I expect it to do?
>>And instead of rasing an error when no rows is found, can I return
>>an "empty" cursor
>>instead?
>>
>>
>>Regards,
>>
>>BTJ
>>
>>--
>>
>> ---
>>
>>Bjørn T Johansen
>>
>>[EMAIL PROTECTED] 
>>
>> ---
>>Someone wrote:
>>"I understand that if you play a Windows CD backwards you hear
>>strange Satanic messages"
>>To which someone replied:
>>"It's even worse than that; play it forwards and it installs Windows"
>>
>> ---
>>
>>---(end of
>>broadcast)---
>>TIP 6: explain analyze is your friend
>>
>>
>>
>>
>>-- 
>>with regards,
>>S.Gnanavel
>>Satyam Computer Services Ltd.
> 
> 

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

Re: [GENERAL] Help trying to write my first plpgsql function...

[hubert depesz lubaczewski]

On 9/15/05, Bjørn T Johansen <[EMAIL PROTECTED]> wrote:
But this function does not do what I need it to do... I want x rows returned, butinstead I just get a stringname...Either how do I use this name or how do I return x rows?
for refcursors - just use returned name in subsequent "FETCH FROM ";

for set returning functions you have to declare them as "returns set of SOMETHING"
and then use return next SOMETHING;
insetead of return.

read the docs - it's quite good reading.

depesz

[GENERAL] Copy DataBases Server to server

[nicolas.hafner]

Hi everyone,
I've a problem that i'am not able to solve alone (i'm kinda newbie on 
postgresql).
we were running PostgreSQL 7.4 under Linux (Mandriva) but our HDD crashs 
and we lost the system but were able to backup the database files to an 
other HDD.
now we want to copy the databases  to our new server but it runs  
windows XP Pro 64bits and PostGreSQL 8.0.
I try something simple, copy the former databases files in 
postgresql\data\base but it doesn't work (i knew it won't be so simple) 
and i look the 3 .conf files but i've found nothing intersing for my 
case. I could easyly rebuild the relationship, but re-importing the data 
will be a last option (it takes a few days the last time).

I'll go with the questions:
First: Is it realistic to think that what we want is possible?
second: if yes, Is it for PostGre Gurus, or will i be able to do it?
third: thanks, that's all!


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

  http://archives.postgresql.org

Re: [GENERAL] Partial dates

[Marco Colombo]

On Wed, 2005-09-14 at 15:49 +1200, Brent Wood wrote:
> 
> Sanitizing is one thing, inventing data to fit an incomplete value into a
> date datatype is not good practice.

Choose another datatype, or make a new one, or split the date into
columns. The type of your data is not a timestamp nor a date.

In the first place, 0 is not NULL. So, even 1980-01-00 would be
different from 1980-01-NULL. For example, assuming 1980-01-00 is defined
to have some meaning, (1980-01-00 < 1980-01-02) is likely to be true,
but (1980-01-NULL < 1980-01-02) definitely is not.

You're just asking if there's a way to store a number of which the lower
bits are ignored (considered NULL). Obviously, no, you need a different
datatype or a different arrangement.

Note: the string 1980-01-00 just *looks* like a possible value, but
definitely it's not: there's simply no "space" (or time) between
1979-12-31 and 1980-01-01. It's much like trying to store sqrt(-1) into
a real. I hardly can imagine how MySQL manages to store that (the
1980-01-00, I mean).

.TM.
-- 
  /  /   /
 /  /   /   Marco Colombo
___/  ___  /   /  Technical Manager
   /  /   /  ESI s.r.l.
 _/ _/  _/  [EMAIL PROTECTED]


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

Re: [GENERAL] Copy DataBases Server to server

[Tino Wildenhain]

nicolas.hafner schrieb:

Hi everyone,
I've a problem that i'am not able to solve alone (i'm kinda newbie on 
postgresql).
we were running PostgreSQL 7.4 under Linux (Mandriva) but our HDD crashs 
and we lost the system but were able to backup the database files to an 
other HDD.
now we want to copy the databases  to our new server but it runs  
windows XP Pro 64bits and PostGreSQL 8.0.
I try something simple, copy the former databases files in 
postgresql\data\base but it doesn't work (i knew it won't be so simple) 
and i look the 3 .conf files but i've found nothing intersing for my 
case. I could easyly rebuild the relationship, but re-importing the data 
will be a last option (it takes a few days the last time).

I'll go with the questions:
First: Is it realistic to think that what we want is possible?
second: if yes, Is it for PostGre Gurus, or will i be able to do it?
third: thanks, that's all!


You need to set up exact same version of postgres and point it to your
backed up database cluster (filesystem copy).

Then make a regular backup via pg_dump from this running copy.
Best is with postgres 8 pg_dump from the other host.

Btw, to get all the tuning knobs and best out of your hardware,
you might use linux or bsd for your production server instead of
windows.

HTH
Tino


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

Re: [GENERAL] Help trying to write my first plpgsql function...

[Bjørn T Johansen]

Yes, I did and I found an answer... :)

But I am trying to use this function in a report designer and the result from 
the
select is in the way of the real data from the fetch... Is there a way around 
this?


BTJ

hubert depesz lubaczewski wrote:
> On 9/15/05, *Bjørn T Johansen* <[EMAIL PROTECTED] >
> wrote:
> 
> But this function does not do what I need it to do... I want x rows
> returned, but
> instead I just get a stringname...
> Either how do I use this name or how do I return x rows?
> 
> 
> for refcursors - just use returned name in subsequent "FETCH FROM
> ";
> 
> for set returning functions you have to declare them as "returns set of
> SOMETHING"
> and then use return next SOMETHING;
> insetead of return.
> 
> read the docs - it's quite good reading.
> 
> depesz

---(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] TSearch2 snowball version error

[Teodor Sigaev]

Snowball changes interfaces, I'll update tsearh2 sources today or tommorow.


William Leite Araújo wrote:

Hi,

I'm trying compile a new brazilian portuguese dictionary to TSearch2 
contrib, but found the errors:


portuguese_stem.c: In function `r_prelude':
portuguese_stem.c:481: error: void value not ignored as it ought to be
portuguese_stem.c:487: error: void value not ignored as it ought to be
portuguese_stem.c: In function `r_postlude':
portuguese_stem.c:610: error: void value not ignored as it ought to be
portuguese_stem.c:616: error: void value not ignored as it ought to be
portuguese_stem.c: In function `r_standard_suffix':
portuguese_stem.c:662: error: void value not ignored as it ought to be
portuguese_stem.c:672: error: void value not ignored as it ought to be
portuguese_stem.c:682: error: void value not ignored as it ought to be
portuguese_stem.c:692: error: void value not ignored as it ought to be
portuguese_stem.c:702: error: void value not ignored as it ought to be
portuguese_stem.c:715: error: void value not ignored as it ought to be
portuguese_stem.c:729: error: void value not ignored as it ought to be
portuguese_stem.c:744: error: void value not ignored as it ought to be
portuguese_stem.c:760: error: void value not ignored as it ought to be
portuguese_stem.c:775: error: void value not ignored as it ought to be
portuguese_stem.c:791: error: void value not ignored as it ought to be
portuguese_stem.c:806: error: void value not ignored as it ought to be
portuguese_stem.c:818: error: void value not ignored as it ought to be
portuguese_stem.c:832: error: void value not ignored as it ought to be
portuguese_stem.c: In function `r_verb_suffix':
portuguese_stem.c:856: error: void value not ignored as it ought to be
portuguese_stem.c: In function `r_residual_suffix':
portuguese_stem.c:880: error: void value not ignored as it ought to be
portuguese_stem.c: In function `r_residual_form':
portuguese_stem.c:902: error: void value not ignored as it ought to be
portuguese_stem.c:929: error: void value not ignored as it ought to be
portuguese_stem.c:935: error: void value not ignored as it ought to be
portuguese_stem.c: In function `portuguese_ISO_8859_1_stem':
portuguese_stem.c:993: error: void value not ignored as it ought to be
make: ** [portuguese_stem.o] Erro 1

 This after change the included file "header.h", the old 
"../runtime/header.h" is invalid.
 I think that is a version trouble, because the return of functions 
"slice_from_s" and "slice_del" are "int" in current snowball portuguese 
files, but on include files of version 8.0.2 and 8.0.3 of Portgresql the 
return type is "void".

 Help-me, please.

--
William Leite Araújo


--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

Re: [GENERAL] ERROR: type "temp_gc" already exists

[Tom Lane]

Janning Vygen <[EMAIL PROTECTED]> writes:
> $ DROP TYPE temp_gc;
> ERROR:  type "temp_gc" does not exist

The temp schema is evidently not in your search path.  You need
something like 
drop type pg_temp_NNN.temp_gc;

regards, tom lane

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

   http://archives.postgresql.org

[GENERAL] Problem with 64-bit Postgres

[Peter Alberer]

Hi, 

i am having a problem with a test installation of postgres 8.0.3 on a 64-bit
power5 system. Let me say first that the system seems to work correctly when
compiled in 32-bit mode (which seems to be standard compile mode with gcc on
this installation). 

I compiled postgres in 64-bit mode by adding the following switches in the
make file:
To the gcc lines:   -m64 -mcpu=power5 -mtune=power5
To ld lines:-m elf64ppc

Compiling in 64-bit mode also seems to work, but there is an error when
trying to access the postmaster process:

Server starts:
[EMAIL PROTECTED] pg803]$ bin/postmaster -D /opt/test/data
LOG:  could not send test message on socket for statistics collector:
Destination address required
LOG:  disabling statistics collector for lack of working socket
LOG:  database system was shut down at 2005-09-15 13:42:43 CEST
LOG:  checkpoint record is at 0/AB8AD8
LOG:  redo record is at 0/AB8AD8; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 544; next OID: 17230
LOG:  database system is ready

Client connects:
[EMAIL PROTECTED] pg803]# bin/psql -U nsadmin -l
psql: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

Server says:
LOG:  setsockopt(TCP_NODELAY) failed: Operation not supported

Did anyone have a similar problem and can provide some help?

TIA, peter

Ps:

The system is using the following gcc config:
Reading specs from /usr/lib/gcc/ppc64-redhat-linux/3.4.3/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
--infodir=/usr/share/info --enable-shared --enable-threads=posix
--disable-checking --with-system-zlib --enable-__cxa_atexit
--disable-libunwind-exceptions --enable-java-awt=gtk
--host=ppc64-redhat-linux --build=ppc64-redhat-linux
--target=ppc64-redhat-linux --with-cpu=default32
Thread model: posix
gcc version 3.4.3 20050227 (Red Hat 3.4.3-22.1)


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

   http://archives.postgresql.org

[GENERAL] Asychronous database replication

[Steve Manes]

I have a project on my plate which will involve potentially hundreds of 
PG8 databases in the field which will need to synchronize data with a 
central database.  The company is a secular nonprofit which delivers 
medical services to underprivileged kids as well as to disaster victims 
like those hit by Katrina.  We have six mobile medical units there now 
as a matter of fact.


Some of these databases will have 24/7 net connections; some may not 
even have telephone access for days so traditional database replication 
techniques won't work.  I've not found any third-party software yet 
which could help us here so I'm proceeding on the assumption that we're 
going to need to build it ourselves.


This sort of database topography is virgin ground for me but I'm 
guessing that others here have encountered this challenge before and 
will have some tips/advice/war stories to steer us in the right direction.


---(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] Problem with 64-bit Postgres

[Tom Lane]

"Peter Alberer" <[EMAIL PROTECTED]> writes:
> Server says:
> LOG:  setsockopt(TCP_NODELAY) failed: Operation not supported

It's pretty unclear why that would fail if it works in 32-bit mode.
Kernel bug maybe?  What is the platform exactly?

regards, tom lane

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

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

Re: [GENERAL] Problem with 64-bit Postgres

[Peter Alberer]

The system is a p5-510 running redhat advanced server 4.0 update 1. the
kernel version is 2.6.9-11.EL. what other details about the machine could be
helpful? 

regards, peter

-Ursprüngliche Nachricht-
Von: Tom Lane [mailto:[EMAIL PROTECTED] 
Gesendet: Donnerstag, 15. September 2005 16:22
An: Peter Alberer
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Problem with 64-bit Postgres 

"Peter Alberer" <[EMAIL PROTECTED]> writes:
> Server says:
> LOG:  setsockopt(TCP_NODELAY) failed: Operation not supported

It's pretty unclear why that would fail if it works in 32-bit mode.
Kernel bug maybe?  What is the platform exactly?

regards, tom lane


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

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

Re: [GENERAL] Howto create a plperlu function as user (not superuser)??

[Brent Wood]

On Wed, 14 Sep 2005, Tom Lane wrote:

> Brent Wood <[EMAIL PROTECTED]> writes:
> > That is not me. Sigh. Is there any way I can develop (with the various
> > "create or replace function" iterations this wiil probably require) this
> > plperlu function as a non superuser?
>
> If you could, it would be a security hole, which we would fix with
> utmost alacrity.  Untrusted-language functions may only be created
> by superusers.

Pretty much what I expected but figured I'd ask :-)

>
> Can you compartmentalize the depth-accessing function as a small
> plperlu function, and do all the interesting stuff in plain plperl
> atop that?
>

Much of the preprocessing required is in plpgsql using PostGIS functions.

The whole Perl thing is only about 10 lines long so not worth splitting if
avoidable. The depth accessing command is a one line system call with
coords as parameters. The rest just builds a bounding box for the point
location so that the call to GMT restricts it's access to the terrain
model to a few square degrees worth of data and not the entire few Gb :-)
A bit faster that way!


One option is another system with Postgres/PostGIS where I am superuser &
can develop, then get the superuser of the working database to run the
SQL's for me to create the functions. Or see if the situation can justify
me getting superuser status. Working with code can be easier than
wrestling with beauracracy :-)


Thanks,

  Brent

---(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] Strange Bug in exim4 postgresql lookup code or libpq?

[Alexander Neumann]

Hi,

I'm using exim4 together with postgresql.  Sometimes, data lookups fail
mysteriously and on the exim side nobody was able to help.

In short terms: The result code is PGRES_FATAL_ERROR, but the error message
is an empty string.

I opened up a bug in the exim bugzilla:
http://www.exim.org/bugzilla/show_bug.cgi?id=45

The complete description is there and possibly too long for this mailing
list...

Can someone please look at this?  I really need help here :)

Please CC: me on replies, as I'm not subscribed to this list.

Regards,
- Alexander


pgpBv4tYM6chE.pgp
Description: PGP signature

Re: [GENERAL] TSearch2 snowball version error

[Teodor Sigaev]

Fixed and commited in cvs.

Patches for already existsing versions :
http://www.sigaev.ru/gist/patch_snowball-7.4.gz
http://www.sigaev.ru/gist/patch_snowball-8.0.gz




I'm trying compile a new brazilian portuguese dictionary to TSearch2 
contrib, but found the errors:


--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

Re: [GENERAL] Speed problems

[Scott Marlowe]

On Wed, 2005-09-14 at 21:06, John Fabiani wrote:
> On Wednesday 14 September 2005 08:23, Scott Marlowe wrote:
> 
> > OK.  But how many are you updating between regular vacuums?  That's the
> > real issue.  If your regular vacuums aren't often enough, postgresql
> > starts lengthening the tables instead of reusing the space in them that
> > was freed by the last updates / deletes.
> >
> > Keep in mind, that in postgresql, all updates are really insert / delete
> > pairs, as far as storage is concerned.  So, updates create dead tuples
> > just like deletes would.
> >
> > > Is my use of indexes correct?
> >
> > Seems good to me.
> 
> Ok but this does seem to be a not a lot of records.  Even if the user updated 
> 500 times a day (500 * 200) will only add 10 records.  I would not expect 
> that performance would suffer adding 10 per day for at least a week.  
> Even if the number was double (in case I mis-read the user prior emails) 
> 20 or 100 at the end of the week would not account for the slow down? 
> Or am I miss reading?

I think he was saying he updated 200 at a go, but he was doing a LOT of
updates each day.  Not sure, I don't have the OP in my email client
anymore.

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

Re: [GENERAL] Problem with 64-bit Postgres

[Tom Lane]

"Peter Alberer" <[EMAIL PROTECTED]> writes:
> The system is a p5-510 running redhat advanced server 4.0 update 1. the
> kernel version is 2.6.9-11.EL. what other details about the machine could be
> helpful? 

Well, it works perfectly fine for me on a ppc64 RHEL machine at Red Hat...

I'm a bit suspicious of your method of getting a 64-bit build by
manually altering the compile/link flags.  I believe the recommended
way of switching 32/64 environment is via setarch.  I did

ppc64 sh
... configure and build within sub-shell ...

and got a working 64-bit executable without doing any special pushups.

regards, tom lane

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

Re: [GENERAL] ERROR: type "temp_gc" already exists

[Janning Vygen]

Am Donnerstag, 15. September 2005 15:31 schrieb Tom Lane:
> Janning Vygen <[EMAIL PROTECTED]> writes:
> > $ DROP TYPE temp_gc;
> > ERROR:  type "temp_gc" does not exist
>
> The temp schema is evidently not in your search path.  You need
> something like
>   drop type pg_temp_NNN.temp_gc;

great support! great software! thanks a lot again!
I managed it and now everything runs fine.

kind regards 
janning 



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

Re: [GENERAL] Strange Bug in exim4 postgresql lookup code or libpq?

[Martijn van Oosterhout]

On Thu, Sep 15, 2005 at 03:14:10PM +0200, Alexander Neumann wrote:
> Hi,
> 
> I'm using exim4 together with postgresql.  Sometimes, data lookups fail
> mysteriously and on the exim side nobody was able to help.
> 

> I opened up a bug in the exim bugzilla:
> http://www.exim.org/bugzilla/show_bug.cgi?id=45

The server log said:
2005-09-01 18:19:01 [5462] LOG:  statement: SELECT sa_tempreject_score FROM
exim_virtual_addresses WHERE domain = '.org' AND (mailbox = 'x' OR
mailbox = '*') ORDER BY mailbox DESC LIMIT 1
2005-09-01 18:19:01 [5462] LOG:  unexpected EOF on client connection

It's not clear if the EOF was caused by exim not closing down properly
or something else. There's nothing special about the domains you've x'd
out?

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgp6qvfXq0Qh3.pgp
Description: PGP signature

Re: [GENERAL] Strange Bug in exim4 postgresql lookup code or libpq?

[Alvaro Herrera]

On Thu, Sep 15, 2005 at 03:14:10PM +0200, Alexander Neumann wrote:

> I'm using exim4 together with postgresql.  Sometimes, data lookups fail
> mysteriously and on the exim side nobody was able to help.
> 
> In short terms: The result code is PGRES_FATAL_ERROR, but the error message
> is an empty string.
> 
> I opened up a bug in the exim bugzilla:
> http://www.exim.org/bugzilla/show_bug.cgi?id=45

Notice this part of the Postgres log:

2005-09-01 18:19:01 [5462] LOG:  statement: SELECT sa_tempreject_score FROM
exim_virtual_addresses WHERE domain = '.org' AND (mailbox = 'x' OR
mailbox = '*') ORDER BY mailbox DESC LIMIT 1
2005-09-01 18:19:01 [5462] LOG:  unexpected EOF on client connection


Why is the client closing the connection?  This seems to be a bug in the
client.  Notice that the last query registered for process 5462 is
against table sa_tempreject_score, but your first log snippet shows, on
line 2, a query against table sa_reject_score which is failing.  The
first line says something about a tempreject_score, which probably comes
from the last query Postgres received.

The connection cache code needs some looking at, apparently ...

-- 
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"Hay que recordar que la existencia en el cosmos, y particularmente la
elaboración de civilizaciones dentre de él no son, por desgracia,
nada idílicas" (Ijon Tichy)

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

Re: [GENERAL] Strange Bug in exim4 postgresql lookup code or libpq?

[Tom Lane]

Alexander Neumann <[EMAIL PROTECTED]> writes:
> In short terms: The result code is PGRES_FATAL_ERROR, but the error message
> is an empty string.

AFAIK that shouldn't happen, unless perhaps you are completely out of
memory in the client-side process.  What shows up in the postmaster
error log when this occurs?

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Problem with 64-bit Postgres

[Peter Alberer]

Hmm, i just tried to do the same thing (ppc64 sh, ...) but it seems to me
that the result is a 32bit executable:

[EMAIL PROTECTED] pg803]# ldd bin/psql
libpq.so.4 => /opt/learn-bench/pg803/lib/libpq.so.4 (0x0ff95000)
libz.so.1 => /usr/lib/libz.so.1 (0x0fbf)
libreadline.so.4 => /usr/lib/libreadline.so.4 (0x000e)
libtermcap.so.2 => /lib/libtermcap.so.2 (0x0fc3)
libcrypt.so.1 => /lib/libcrypt.so.1 (0x0004)
libresolv.so.2 => /lib/libresolv.so.2 (0x0fa0)
libnsl.so.1 => /lib/libnsl.so.1 (0x0008)
libdl.so.2 => /lib/libdl.so.2 (0x0fe6)
libm.so.6 => /lib/tls/libm.so.6 (0x0fdd)
libc.so.6 => /lib/tls/libc.so.6 (0xf7e97000)
/lib/ld.so.1 (0x0ffd)

[EMAIL PROTECTED] pg803]# readelf -h bin/psql
ELF Header:
  Magic:   7f 45 4c 46 01 02 01 00 00 00 00 00 00 00 00 00
  Class: ELF32
  Data:  2's complement, big endian
  Version:   1 (current)
  OS/ABI:UNIX - System V
  ABI Version:   0
  Type:  EXEC (Executable file)
  Machine:   PowerPC


When i compiled with the -m64 switch the result was an ELF64 file, and all
of the library references were going to /lib64/ ... 

[EMAIL PROTECTED] pg803]# ldd bin/psql
libpq.so.4 => /opt/learn-bench/pg803/lib/libpq.so.4
(0x00801000)
libz.so.1 => /usr/lib64/libz.so.1 (0x00802bcb)
libreadline.so.4 => /usr/lib64/libreadline.so.4 (0x00802bdb)
libtermcap.so.2 => /lib64/libtermcap.so.2 (0x00802bc8)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00852000)
libresolv.so.2 => /lib64/libresolv.so.2 (0x00802c20)
libnsl.so.1 => /lib64/libnsl.so.1 (0x00802de2)
libdl.so.2 => /lib64/libdl.so.2 (0x00802bc6)
libm.so.6 => /lib64/tls/libm.so.6 (0x00802bbd)
libc.so.6 => /lib64/tls/libc.so.6 (0x00802ba4)
/lib64/ld64.so.1 (0x00802ba0)

[EMAIL PROTECTED] pg803]# readelf -h bin/psql
ELF Header:
  Magic:   7f 45 4c 46 02 02 01 00 00 00 00 00 00 00 00 00
  Class: ELF64
  Data:  2's complement, big endian
  Version:   1 (current)
  OS/ABI:UNIX - System V
  ABI Version:   0
  Type:  EXEC (Executable file)
  Machine:   PowerPC64

What elf-class did your compilation produce? Is my assumption, that it
should be elf64 for a 64-bit executable correct?

Regards, peter



---(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] Problem with 64-bit Postgres

[Tom Lane]

"Peter Alberer" <[EMAIL PROTECTED]> writes:
> Hmm, i just tried to do the same thing (ppc64 sh, ...) but it seems to me
> that the result is a 32bit executable:

Hmm ... I got an elf64 executable.  I'm not entirely sure what drives
gcc's default choices about such things [ digs around... ]  The compiler
I was using says this for "gcc -v"

Reading specs from /usr/lib/gcc/ppc64-redhat-linux/3.4.4/specs
Configured with: ../configure --prefix=/usr --mandir=/usr/share/man 
--infodir=/usr/share/info --enable-shared --enable-threads=posix 
--disable-checking --with-system-zlib --enable-__cxa_atexit 
--disable-libunwind-exceptions --enable-languages=c,c++,objc,java,f77 
--enable-java-awt=gtk --host=ppc64-redhat-linux
Thread model: posix
gcc version 3.4.4 20050721 (Red Hat 3.4.4-2)

I see "--with-cpu=default32" in your config, which is probably the
significant difference, but I don't know what you should do to change
that.

regards, tom lane

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

[GENERAL] Deadlock

[Bart McFarling]

Is there some kind of log, table or something that I could get more
information about a deadlock situation that is occurring in my database? I
just get a transaction number and a process id, which is useless to me
because my application terminates on any errors from the database? It occurs
infrequently (about once a day) and I have no idea how to track it down.
Also sometimes the database just freezes (RedHat EL 3.0 Postgresql 8.0.1)
could this be a deadlock situation? There is nothing in the log about
deadlock or anything else when it freezes (this happens about once every
month or so)

Thanks,
Bart
 

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

Re: [GENERAL] oracle's first_value function for postgres?

[Ron Mayer]

Martijn van Oosterhout wrote:

On Tue, Sep 13, 2005 at 12:15:21PM -0700, Ben wrote:

Oracle has a very handy function called first_value, which can be used 
to turn a set like this:



Look at DISTINCT ON ()


Does postgres have something equivalent, or, even better, is there a 
reasonable way to express this in standard SQL?



In standard SQL, not really, which is why it's an extention...


No?It seems this is similar

SELECT a,b
FROM ( SELECT a,b,
RANK() OVER (
PARTITION BY a
ORDER BY b
) rank
FROM my_table )
WHERE rank = 1
ORDER BY a, rank DESC;

which I think is standard sql-99 with the sql-99 olap extention.

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

  http://archives.postgresql.org

[GENERAL] How to check is the table system

[Андрей]

Hello!

   In what way can I determine is the table system? ODBC driver does it 
by checking table name's prefix: if it begins with 'pg_' - driver 
desides that the table is system, but that's a bad idea. I can create 
table and call it 'pg_mytable', but it won't become system!


   Big Thanks,
   Andrei


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

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

Re: [GENERAL] How to check is the table system

[Alvaro Herrera]

On Thu, Sep 15, 2005 at 09:12:44PM +0300,  wrote:

>In what way can I determine is the table system? ODBC driver does it 
> by checking table name's prefix: if it begins with 'pg_' - driver 
> desides that the table is system, but that's a bad idea. I can create 
> table and call it 'pg_mytable', but it won't become system!

If a table is in the pg_catalog schema, it's a system table.  The pg_
prefix was used as a convention before the introduction of schemas in
7.3 -- users were not supposed to create tables with names beggining
with pg_.  I guess it's still a bad idea to create tables with such
names.

-- 
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"La gente vulgar solo piensa en pasar el tiempo;
el que tiene talento, en aprovecharlo"

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

Re: [GENERAL] How to check is the table system

[Tom Lane]

=?UTF-8?B?0JDQvdC00YDQtdC5?= <[EMAIL PROTECTED]> writes:
> In what way can I determine is the table system? ODBC driver does it 
> by checking table name's prefix: if it begins with 'pg_' - driver 
> desides that the table is system, but that's a bad idea.

Yup, that's been incorrect since PG 7.3.  The proper test is whether the
table is in the pg_catalog schema.

Depending on your purposes you might also want to exclude pg_toast.

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] CLUSTER equivalent

[Kevin Murphy]

I just wanted to confirm that the COPY command always stores data in the 
table in the order in which it appears in the import file.


I.e., if the import file is sorted ahead of time, am I correct in 
assuming that the COPY command can have the same effect as CLUSTER'ing 
(or as creating a new table as an ordered select on the loaded table)?  
(Indexes would of course be applied after the data load.)


Thanks,
Kevin Murphy


---(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] How to check is the table system

[Martijn van Oosterhout]

On Thu, Sep 15, 2005 at 09:12:44PM +0300,  wrote:
> Hello!
> 
>In what way can I determine is the table system? ODBC driver does it 
> by checking table name's prefix: if it begins with 'pg_' - driver 
> desides that the table is system, but that's a bad idea. I can create 
> table and call it 'pg_mytable', but it won't become system!

Check it is in the pg_catalog schema...

-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgphONwHyhMHM.pgp
Description: PGP signature

Re: [GENERAL] Deadlock

[Michael Fuhr]

On Thu, Sep 15, 2005 at 12:32:05PM -0500, Bart McFarling wrote:
> Is there some kind of log, table or something that I could get more
> information about a deadlock situation that is occurring in my database? I
> just get a transaction number and a process id, which is useless to me
> because my application terminates on any errors from the database?

See "Error Reporting and Logging" in the "Server Run-time Environment"
chapter of the documentation:

http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-CONFIG-LOGGING

> It occurs infrequently (about once a day) and I have no idea how to
> track it down.

You could log all queries or use log_min_error_statement to log
only queries that result in an error.  Typical causes of deadlock
are multiple transactions updating the same records in different
orders, and doing inserts/updates that reference the same foreign
keys in different orders (in released versions of PostgreSQL,
referential integrity checks do a SELECT FOR UPDATE on the referenced
key to ensure that it doesn't change while the transaction is still
active; in 8.1 such locks will be acquired with SELECT FOR SHARE,
which should reduce the incidence of deadlock).

> Also sometimes the database just freezes (RedHat EL 3.0 Postgresql 8.0.1)
> could this be a deadlock situation? There is nothing in the log about
> deadlock or anything else when it freezes (this happens about once every
> month or so)

What are the symptoms of this "freeze"?  Do only some queries block?
Do all queries block, even queries such as "SELECT now()"?  Are you
able to connect to the database at all?  If you can connect, have
you examined pg_locks?  If you can't connect, have you done a process
trace or used a debugger to see what the database is doing?

-- 
Michael Fuhr

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

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

Re: [GENERAL] Howto create a plperlu function as user (not superuser)??

[David Fetter]

On Wed, Sep 14, 2005 at 04:11:25PM +1200, Brent Wood wrote:
> Hi,
> 
> My problem is that the language needs to be plperlu (the unsecured
> implementation of plperl) to be allowed to execute the system call
> to get the depth at the specified location. To work, the plperlu
> function must be created by the superuser, who I assume is postgres.

The reason that only the superuser can do this is that a procedure in
any untrusted PL can do things like 'rm -rf $PGDATA' and have it
obeyed.

> That is not me.  Sigh.  Is there any way I can develop (with the
> various "create or replace function" iterations this wiil probably
> require) this plperlu function as a non superuser?

Nope.  Assuming you can't get access as db superuser, you might want
to look into LISTEN/NOTIFY system

http://blackhawk.supernews.net/listen.pl.txt
http://www.postgresql.org/docs/current/static/libpq-notify.html
http://www.postgresql.org/docs/current/static/sql-listen.html

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

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

[GENERAL] Questions about "varchar" NOT NULL default = char(1) ?

[Emi Lu]

Greetings,

If one column "col1" is defined as :

col1 varchar(1) not null default ''

Does it means that col1's definition is equal to

col1 char(1) not null default ''


Put it another way, will char '' be saved as char(1) or char '' does not 
use space at all?


Thanks a lot,
Emi

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

Re: [GENERAL] Deadlock

[Bart McFarling]

On Thu, Sep 15, 2005 at 12:32:05PM -0500, Bart McFarling wrote:
> Is there some kind of log, table or something that I could get more
> information about a deadlock situation that is occurring in my database? I
> just get a transaction number and a process id, which is useless to me
> because my application terminates on any errors from the database?

See "Error Reporting and Logging" in the "Server Run-time Environment"
chapter of the documentation:

http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-C
ONFIG-LOGGING

Will do. Ive played with these settings before, If I knew what tables the
processes were simultaneously trying to update, I think I could fix it
pretty quick. I know which application is locking it, I just cant figure out
why or where, this particular program is a beast its updating at least 10
tables with the push of 1 button. The funny thing is that the people who use
the system are prone to do each others work at the same time because they
don't know that the other has done it. But the deadlocks typically occur
between people in other locations (Dallas bumps heads with Memphis) which is
weird because they shouldn't even be looking at each others data. Im about
to replace the offending software so hopefully the re-written version wont
have this problem but a lot of the code was salvaged for use in the new
version so it may still suffer.  I use the user_write_lock_oid()(or
something like that) to keep people from accessing the same record.

> It occurs infrequently (about once a day) and I have no idea how to
> track it down.

You could log all queries or use log_min_error_statement to log
only queries that result in an error.  Typical causes of deadlock
are multiple transactions updating the same records in different
orders, and doing inserts/updates that reference the same foreign
keys in different orders (in released versions of PostgreSQL,
referential integrity checks do a SELECT FOR UPDATE on the referenced
key to ensure that it doesn't change while the transaction is still
active; in 8.1 such locks will be acquired with SELECT FOR SHARE,
which should reduce the incidence of deadlock).

> Also sometimes the database just freezes (RedHat EL 3.0 Postgresql 8.0.1)
> could this be a deadlock situation? There is nothing in the log about
> deadlock or anything else when it freezes (this happens about once every
> month or so)

What are the symptoms of this "freeze"?  Do only some queries block?
Do all queries block, even queries such as "SELECT now()"?  Are you
able to connect to the database at all?  If you can connect, have
you examined pg_locks?  If you can't connect, have you done a process
trace or used a debugger to see what the database is doing?

I can psql in a get a prompt but any statement will just freeze, regardless
of the table.

Im not sure if a SELECT now() will freeze. I wish I had gotten this email
earlier it hung about 5 minutes before I received this. Yes ive tried
looking at PQtrace() data but there are so many connections/transactions
going on its hard to tell what's what.  

-- 
Michael Fuhr

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

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

Re: [GENERAL] CLUSTER equivalent

[Neil Conway]

Kevin Murphy wrote:
I just wanted to confirm that the COPY command always stores data in the 
table in the order in which it appears in the import file.


This is not the case -- depending on the content of the FSM, the newly 
added rows might be distributed throughout the table.


-Neil


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

[GENERAL] character varying == text?

[CSN]

Just something I was curious about - is there any
difference at all between "character varying" (in the
SQL spec) without a length specified and "text" (not
in the SQL spec)?

Thanks,
CSN



__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

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

Re: [GENERAL] Questions about "varchar" NOT NULL default = char(1)

[Neil Conway]

Emi Lu wrote:

Greetings,

If one column "col1" is defined as :

col1 varchar(1) not null default ''

Does it means that col1's definition is equal to

col1 char(1) not null default ''


Not quite; for example,

neilc=# create table t1 (x char(1) not null);
CREATE TABLE
neilc=# create table t2 (x varchar(1) not null);
CREATE TABLE
neilc=# insert into t1 values ('');
INSERT 0 1
neilc=# insert into t2 values ('');
INSERT 0 1
neilc=# select octet_length(x) from t1;
 octet_length
--
1
(1 row)

neilc=# select octet_length(x) from t2;
 octet_length
--
0
(1 row)

Put it another way, will char '' be saved as char(1) or char '' does not 
use space at all?


I'm not sure what you mean.

-Neil


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

Re: [GENERAL] Asychronous database replication

[Chris Browne]

[EMAIL PROTECTED] (Steve Manes) writes:
> I have a project on my plate which will involve potentially hundreds
> of PG8 databases in the field which will need to synchronize data with
> a central database.  The company is a secular nonprofit which delivers
> medical services to underprivileged kids as well as to disaster
> victims like those hit by Katrina.  We have six mobile medical units
> there now as a matter of fact.
>
> Some of these databases will have 24/7 net connections; some may not
> even have telephone access for days so traditional database
> replication techniques won't work.  I've not found any third-party
> software yet which could help us here so I'm proceeding on the
> assumption that we're going to need to build it ourselves.
>
> This sort of database topography is virgin ground for me but I'm
> guessing that others here have encountered this challenge before and
> will have some tips/advice/war stories to steer us in the right
> direction.

Well, what you clearly want/need is asynchronous multimaster...

I'm involved with Slony-I, which is asynchronous but definitely,
consciously, intentionally NOT multimaster.

It seems to me that you might be able to usefully cannibalize
components from Slony-I; the trigger functions that it uses to
intercept updates seem likely to be useful.  Some of the data
structures would be useful, notably "sl_log_1", which is where the
updates are collected.

There are some conspicuous "troublesome bits" which Slony-I has evaded
since it is NOT multimaster.

For instance, you'll need some form of conflict resolution system, as
async multimaster allows inserting conflicting combinations of
updates.

You may need some special way of detecting updates to "balance
tables," that is, things where people typically updates of the form:

  update balance_table set balance = balance + 10;

In Slony-I, that becomes read, by the trigger, as, let's say...
  update balance_table set balance = 450; 

  (as the old value was 440, and 440+10 = 450)

I have been led to believe that Sybase has a sort of "delta update"
for this sort of thing...

It's worth your while to look into whatever you can find on how other
async multimaster systems function.  Two conspicuous (tho perhaps
unexpected) examples include:

 a) Palm Computing's PalmSync system - which addresses conflicts by
creating duplicate records and saying "You fix that..."

 b) Lotus Notes, which does a somewhat document-oriented sort of
async MM replication.

There's _some_ collected wisdom around; if you visit the Slony-I list,
you might be able to attract some commentary.  Just be aware that
we're not planning to make it a multimaster system :-).
-- 
output = reverse("gro.gultn" "@" "enworbbc")
http://cbbrowne.com/info/slony.html
TTY Message from The-XGP at MIT-AI:
[EMAIL PROTECTED] 02/59/69 02:59:69
Your XGP output is startling.

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

Re: [GENERAL] character varying == text?

[Tom Lane]

CSN <[EMAIL PROTECTED]> writes:
> Just something I was curious about - is there any
> difference at all between "character varying" (in the
> SQL spec) without a length specified and "text" (not
> in the SQL spec)?

The SQL standard doesn't allow "character varying" without a length spec.

But yeah, in Postgres they're essentially the same thing.

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

Re: [GENERAL] CLUSTER equivalent

[Kevin Murphy]

Neil Conway wrote:


Kevin Murphy wrote:

I just wanted to confirm that the COPY command always stores data in 
the table in the order in which it appears in the import file.



This is not the case -- depending on the content of the FSM, the newly 
added rows might be distributed throughout the table.


How about for a freshly created, empty table -- I should have qualified 
my original statement.


Thanks,
Kevin


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

Re: [GENERAL] CLUSTER equivalent

[Alvaro Herrera]

On Thu, Sep 15, 2005 at 05:09:51PM -0400, Kevin Murphy wrote:
> Neil Conway wrote:
> 
> >Kevin Murphy wrote:
> >
> >>I just wanted to confirm that the COPY command always stores data in 
> >>the table in the order in which it appears in the import file.
> >
> >This is not the case -- depending on the content of the FSM, the newly 
> >added rows might be distributed throughout the table.
> 
> How about for a freshly created, empty table -- I should have qualified 
> my original statement.

In that particular case, the answer is yes.  As is if you TRUNCATE the
table just prior to the COPY.

-- 
Alvaro Herrera -- Valdivia, Chile Architect, www.EnterpriseDB.com
"El destino baraja y nosotros jugamos" (A. Schopenhauer)

---(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] Create a pg table from CSV with header rows

[Robert Fitzpatrick]

Anyone know a package that can do this? Perferrably a Unix/Linux
package.

-- 
Robert


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

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

Re: [GENERAL] Deadlock

[Michael Fuhr]

[Please be careful with quoting -- you quoted some parts of my reply
but not others so it looks like you wrote those sections.  I've
fixed that in this reply.]

On Thu, Sep 15, 2005 at 03:20:02PM -0500, Bart McFarling wrote:
> > > Also sometimes the database just freezes (RedHat EL 3.0 Postgresql 8.0.1)
> > > could this be a deadlock situation? There is nothing in the log about
> > > deadlock or anything else when it freezes (this happens about once every
> > > month or so)
> >
> > What are the symptoms of this "freeze"?  Do only some queries block?
> > Do all queries block, even queries such as "SELECT now()"?  Are you
> > able to connect to the database at all?  If you can connect, have
> > you examined pg_locks?  If you can't connect, have you done a process
> > trace or used a debugger to see what the database is doing?
> 
> I can psql in a get a prompt but any statement will just freeze, regardless
> of the table.

What kinds of statements did you try?  Simple SELECTs?  Try querying
some other table, like pg_class or a test table that you created
just for that purpose.  If those queries work then the problem is
probably with locking.  You mentioned that you were using
user_write_lock_oid() but I don't think that should acquire a strong
enough lock to block all other queries to a table.  Are you doing
anything with LOCK?  What about database maintenance activities
like VACUUM FULL or CLUSTER?

The next time the "freeze" happens, try running the following query:

SELECT relation::regclass, * FROM pg_locks;

If you have have stats_command_string enabled then it might also be
useful to run this query:

SELECT * FROM pg_stat_activity;

Look for locks that haven't been granted, then look for the process
that holds locks on that table and see what that process is doing.
If that doesn't help track down the problem then you might need to
log every statement so you can see exactly who is doing what.

-- 
Michael Fuhr

---(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] ERROR from backend during send_query: 'ERROR: cursor "sql_cur0140cc48" does not exist'

[[EMAIL PROTECTED]]

The following errors only happen when using the "USE DECLARE/FETCH"
option in the ODBC Driver. When DECLARE/FETCH is set, the data sent to
some of our programs is reduced from 2 MBytes to 100 KBytes. We want to
achieve this reduction. But, with the DECLARE/FETCH option set,
implicit transactions are used ... and fail !!

 This is a PRODUCTION environment, with some 20 clients accessing the
DB.

Can anyone help? Thanks in advance.

Nuno Goncalves


The 4th item was truncated
The buffer size = 17 and the value is
'DD13m   &nbs
p;   
'
conn=20986272, query='declare SQL_CUR0140EF48 cursor for SELECT
T1.CdUsr, T1.Perfil, T2.DscPrf FROM  UsrPrf T1 LEFT OUTER JOIN
PrfTbl T2 ON  T1.Perfil = T2.Perfil  WHERE ( T1.CdUsr = 'Nuno
Goncalves' ) AND ( T1.Perfil = 'VC' ) ORDER BY T1.CdUsr, T1.Perfil '
conn=20986272, query='fetch 64 in SQL_CUR0140EF48'
    [ fetched 0 rows ]
conn=20986272, query='close SQL_CUR0140EF48'
conn=20986272, query='close SQL_CUR0140FA70'
conn=20986272, query='declare SQL_CUR0140FA70 cursor for SELECT
T1.CdUsr FROM UsrEsp T1 WHERE ( T1.CdUsr = 'Nuno Goncalves' ) ORDER BY
T1.CdUsr '
conn=20986272, query='fetch 64 in SQL_CUR0140FA70'
    [ fetched 1 rows ]
conn=20986272, query='declare SQL_CUR0140FC70 cursor for SELECT
T1.CdUsr, T1.Perfil, T2.DscPrf FROM  UsrPrf T1 LEFT OUTER JOIN
PrfTbl T2 ON  T1.Perfil = T2.Perfil  WHERE ( T1.CdUsr = 'Nuno
Goncalves' ) ORDER BY T1.CdUsr, T1.Perfil '
conn=20986272, query='fetch 64 in SQL_CUR0140FC70'
    [ fetched 0 rows ]
conn=20986272, query='close SQL_CUR0140FC70'
conn=20986272, query='declare SQL_CUR0140EF48 cursor for SELECT
T1.CdUsr, T1.Perfil, T2.DscPrf FROM  UsrPrf T1 LEFT OUTER JOIN
PrfTbl T2 ON  T1.Perfil = T2.Perfil  WHERE ( T1.CdUsr = 'Nuno
Goncalves' ) AND ( T1.Perfil = 'VC' ) ORDER BY T1.CdUsr, T1.Perfil '
conn=20986272, query='fetch 64 in SQL_CUR0140EF48'
    [ fetched 0 rows ]
conn=20986272, query='close SQL_CUR0140EF48'
conn=20986272, query='close SQL_CUR0140FA70'
conn=20986272, query='declare SQL_CUR0140FA70 cursor for SELECT
T1.CdUsr FROM UsrEsp T1 WHERE ( T1.CdUsr = 'Nuno Goncalves' ) ORDER BY
T1.CdUsr '
conn=20986272, query='fetch 64 in SQL_CUR0140FA70'
    [ fetched 1 rows ]
conn=20986272, query='declare SQL_CUR0140FC70 cursor for SELECT
T1.CdUsr, T1.Perfil, T2.DscPrf FROM  UsrPrf T1 LEFT OUTER JOIN
PrfTbl T2 ON  T1.Perfil = T2.Perfil  WHERE ( T1.CdUsr = 'Nuno
Goncalves' ) ORDER BY T1.CdUsr, T1.Perfil '
conn=20986272, query='fetch 64 in SQL_CUR0140FC70'
    [ fetched 0 rows ]
conn=20986272, query='close SQL_CUR0140FC70'
conn=20986272, query='declare SQL_CUR0140EF48 cursor for SELECT
T1.CdUsr, T1.Perfil, T2.DscPrf FROM  UsrPrf T1 LEFT OUTER JOIN
PrfTbl T2 ON  T1.Perfil = T2.Perfil  WHERE ( T1.CdUsr = 'Nuno
Goncalves' ) AND ( T1.Perfil = 'VC' ) ORDER BY T1.CdUsr, T1.Perfil '
conn=20986272, query='fetch 64 in SQL_CUR0140EF48'
    [ fetched 0 rows ]
conn=20986272, query='close SQL_CUR0140EF48'
conn=20986272, query='close SQL_CUR0140FA70'
conn=20986272, query='declare SQL_CUR0140FA70 cursor for SELECT
T1.CdUsr FROM UsrEsp T1 WHERE ( T1.CdUsr = 'Nuno Goncalves' ) ORDER BY
T1.CdUsr '
conn=20986272, query='fetch 64 in SQL_CUR0140FA70'
    [ fetched 1 rows ]
conn=20986272, query='declare SQL_CUR0140FC70 cursor for SELECT
T1.CdUsr, T1.Perfil, T2.DscPrf FROM  UsrPrf T1 LEFT OUTER JOIN
PrfTbl T2 ON  T1.Perfil = T2.Perfil  WHERE ( T1.CdUsr = 'Nuno
Goncalves' ) ORDER BY T1.CdUsr, T1.Perfil '
conn=20986272, query='fetch 64 in SQL_CUR0140FC70'
    [ fetched 0 rows ]
conn=20986272, query='close SQL_CUR0140FC70'
conn=20986272, query='declare SQL_CUR01732D38 cursor for SELECT
T1.CnCiaR, T2.CdCia, T3.RazonSocial, T1.CnCarga, T1.TpCarga, T1.FileX,
T1.RegTot, T1.RegOK, T1.FCarga, T1.HCarga, T1.StCarga, T1.Usr, T1.Pgm,
T1.FAud, T1.HAud, T1.CntMod FROM  Carga T1 LEFT OUTER JOIN Persona
T3 ON  T1.CnCiaR = T3.CnPer LEFT OUTER JOIN Compania T2 ON 
T1.CnCiaR = T2.CnCiaR  WHERE ( T1.CnCiaR = '1' ) AND ( T1.TpCarga
= '5' ) ORDER BY T1.CnCiaR, T1.TpCarga, T1.CnCarga  DESC,
T1.StCarga  DESC '
conn=20986272, query='fetch 64 in SQL_CUR01732D38'
    [ fetched 1 rows ]
conn=20986272, query='close SQL_CUR01732D38'
conn=20986272, query='declare SQL_CUR01728CE8 cursor for SELECT * FROM
cargar_pedido('EMB 74_2',1,'S','','Nuno Goncalves');
VACUUM pddasm;'
ERROR from backend during send_query: 'ERROR:  VACUUM cannot run
inside a transaction block'
conn=20986272, query='ROLLBACK'
STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error while
executing the query'
&nbs
p;   

&nbs
p;   
hdbc=20986272, stmt=24284392, result=24316648
&nbs
p;   
manual_result=0, prepare=1, internal=0
&nbs
p;   
bindings=24284888, bindings_allocated=1
&nbs
p;   
parameters=0, parameters_allocated=0
&nbs
p;   
statement_type=0, statement='SELECT * FROM cargar_pedido('EMB
74_2',1,'S','','Nuno Goncalves');
VACUUM pddasm;'
&nbs
p;   
stmt_with_pa

Re: [GENERAL] Asychronous database replication

[Greg Stark]

Chris Browne <[EMAIL PROTECTED]> writes:

> Well, what you clearly want/need is asynchronous multimaster...

I didn't catch anything in his description that answered whether he needs
multimaster or a simple single master with many slaves model would suffice.

> I'm involved with Slony-I, which is asynchronous but definitely,
> consciously, intentionally NOT multimaster.
> 
> It seems to me that you might be able to usefully cannibalize
> components from Slony-I; the trigger functions that it uses to
> intercept updates seem likely to be useful.  Some of the data
> structures would be useful, notably "sl_log_1", which is where the
> updates are collected.

A general purpose replication system is a lot trickier and more technical that
building an application-specific system. While all of the above is cool, if
you're able to design the application around certain design constraints you
can probably build something much simpler.

My first reaction to this description was to consider some sort of model where
the master database publishes text dumps of the master database which are
regularly downloaded and loaded on the slaves. The slaves treat those tables
as purely read-only reference tables. 

If you need data to propagate from the clients back to the server then things
get more complicated. Even then you could side step a lot of headaches if you
can structure the application in specific ways, such as guaranteeing that the
clients can only insert, never update records.


-- 
greg


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

Re: [GENERAL] Create a pg table from CSV with header rows

[Michael Fuhr]

On Thu, Sep 15, 2005 at 06:48:59PM -0400, Robert Fitzpatrick wrote:
> Anyone know a package that can do this? Perferrably a Unix/Linux
> package.

It would be trivial to write a script in a language like Perl to
read the first line of a file and generate a CREATE TABLE statement
from it, then issue a COPY command and send the rest of the file.
Determining the columns' data types would be a different matter:
if they weren't specified in the header then you'd have to guess
or perhaps make them all text.

-- 
Michael Fuhr

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

[GENERAL] BIG installations of PostgresQL?

[Logan Bowers]

Hello, 

 

My company is looking at two very large DB vendors to bear
most of data burden of our company; however I’d like to propose
PostgresQL to handle some of the tasks, specifically a large number read-only
search DBs.  Postgres already has the win in terms of features and it’s
now down to risk.  

 

Many of the other folks at the company feel an open-source
DB is more risky because it is less well tested compared to commercial
counterparts.  I’m looking for examples of large installations of
Postgres with huge data sets, high traffic volumes, high update rates, etc,
particularly large, recognizable names.  I know you guys get this question a
lot, but can anyone share experiences with using Postgres in large settings with
huge query rates, replication, etc?  Any stories you guys can share (in public
or private) would be greatly appreciated.  Thanks in advance!  

 

Logan Bowers

Re: [GENERAL] BIG installations of PostgresQL?

[Scott Marlowe]

Title: RE: [GENERAL] BIG installations of PostgresQL?






-Original Message-
From: [EMAIL PROTECTED] on behalf of Logan Bowers
Sent: Thu 9/15/2005 10:25 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] BIG installations of PostgresQL?

Hello,



My company is looking at two very large DB vendors to bear most of data
burden of our company; however I'd like to propose PostgresQL to handle
some of the tasks, specifically a large number read-only search DBs.
Postgres already has the win in terms of features and it's now down to
risk. 



Many of the other folks at the company feel an open-source DB is more
risky because it is less well tested compared to commercial
counterparts.  I'm looking for examples of large installations of
Postgres with huge data sets, high traffic volumes, high update rates,
etc, particularly large, recognizable names.  I know you guys get this
question a lot, but can anyone share experiences with using Postgres in
large settings with huge query rates, replication, etc?  Any stories you
guys can share (in public or private) would be greatly appreciated.
Thanks in advance! 

- END OF ORIGINAL MESSAGE 

(I apologize if this thing is going out as html mail, I'm stuck on a web - Exchange client at the moment...)

If they're read only databases, your risks are minimized, because you can always keep backup machines ready, and spread the load if needed.  Downtime can be minimized

There are many large to VERY large sites running on PostgreSQL, I'll let those folks on the list that are on large / high traffic pgsql sites list themselves for ya.  But it's especially popular with folks who need to do GIS stuff since it understands spatial types and data, and can index them.  also, it's ability to have functional and / or partial indexes is quite useful too.

The only test that counts is how it works for you.  If Oracle works for 1,000,000 other sites but can't handle your particular load, then it's a bad choice, not because it was or wasn't tested thoroughly by others, but because it doesn't work for you.

PostgreSQL gets a LOT of testing.  Look up OSDL, who use postgresql running a variety of loads to test and tune linux based servers.  Plus, being free, there are literally thousands of thousands of small installations that use it for things like back end databases for ticketing / tracking and reporting systems.  So, it's got lots of testing in the small to middle sized database category.

In the last couple of years it has started receiving the kind of testing under large and heavy loads, and the features and improvements to go with it, that allow it to start encroaching on more and more of the enterprise territory that Oracle and DB2 and a few of the big boys hold.  Is it equal to Oracle or DB2? 

That said, one problem I have never had with PostgreSQL is unreliability.  Even when beaten firmly into the ground under load, it just keeps working.  While certain loads present problems (high update rates that outrun vacuum can be an issue, but much less now than a few years ago) most (nearly all really) are handled with no hiccups or interruptions.

That said, if you want a "commercial" database, there are companies that are more than willing to sell you a commercialized version of PostgreSQL with their support and testing to stand behind it.  If your company is bound and determined to spend money to buy a database, they can do that with PostgreSQL too.  And sometimes, it's not a bad idea, especially when you're just starting out.