Re: [GENERAL] create temp in function

[A. Kretschmer]

am  Mon, dem 21.04.2008, um 15:22:52 -0600 mailte Kerri Reno folgendes:
> But if I create and run the following function, it bombs on the second run.  
> It
> gives me:
> compassdevel_lb=# select testtemp();
> NOTICE:  relid: 186270497
> NOTICE:  count: 0
>  testtemp
> --
>  t
> (1 row)
> 
> compassdevel_lb=# select testtemp();
> NOTICE:  relid: 
> ERROR:  relation with OID 186270497 does not exist
> CONTEXT:  SQL statement "SELECT  count(*) from schedrec"
> PL/pgSQL function "testtemp" line 9 at select into variables
> 
> Here is my function:
> create or replace function testtemp()
> returns boolean as
> $body$
> declare
> query text;
> relid integer;
> cnt integer;
> begin
> create temp table schedrec (sch text, cl text, st text);


Use EXECUTE for DDL-Statements within function, because the planner
cached the OID's.

Other solution: use 8.3.

More details about that:
http://merlinmoncure.blogspot.com/2007/09/as-previously-stated-postgresql-8.html

Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgres Encoding conversion problem

[Clemens Schwaighofer]

Hi,

I sometimes have a problem with conversion of encodings eg from UTF-8
tio ShiftJIS:

ERROR:  character 0xf0a0aeb7 of encoding "UTF8" has no equivalent in "SJIS"

I have no idea what character this is, I cannot view it in my browser, etc.

If I run the conversion through PHP with mb_convert_encoding it works,
perhaps he is ignoring the character.

Is there a way to do a similar thing, like ignoring this character in
postgres too?

-- 
[ Clemens Schwaighofer  -=:~ ]
[ IT Engineer/Manager, TEQUILA\ Japan IT Group   ]
[6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.co.jp   ]

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL on Vista

[Justin]

Gurjeet Singh wrote:
I would recommend creating rules in your firewall that let Postgres 
run and listen on sockets rather than turn off the firewall 
altogether. (The firewall rules _may_ not be the problem in your case, 
but you can still try)


On an orthogonal note, I just disliked the UAC in Vista... For the 
first month or so I tried to cope with it, hoping that I'd get used to 
it, but it keeps coming in the way so much that I had to finally turn 
it off... now life's much easier.


Best regards,

On Tue, Apr 22, 2008 at 5:17 AM, Justin <[EMAIL PROTECTED] 
> wrote:


this new keyboard has problems, or i can't type ;-) .

 it should be off not of

Justin wrote:



Dirk Verleysen wrote:


Hi,

I have been running a Postgres (8.2.4) on a Windows XP for over
3 months. Last week this machine died and I bought a new Vista
machine today. Installed everything on it and a Postgres
(8.2.7). The problem is that I cannot start the Postgres
service. I keep getting the following error: FATAL:  could not
create any TCP/IP sockets


Anyone has any idea what I can do ?

Thanks,

Dirk

turn of the firewall





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

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device


Good point

here' an article on how to configure vista's firewall. 
http://technet.microsoft.com/en-us/library/bb877967.aspx


Vista's firewall has been greatly improved its still  a pain to 
configure and gets in the way more than it helps. 

Re: [GENERAL] PostgreSQL on Vista

[Gurjeet Singh]

I would recommend creating rules in your firewall that let Postgres run and
listen on sockets rather than turn off the firewall altogether. (The
firewall rules _may_ not be the problem in your case, but you can still try)

On an orthogonal note, I just disliked the UAC in Vista... For the first
month or so I tried to cope with it, hoping that I'd get used to it, but it
keeps coming in the way so much that I had to finally turn it off... now
life's much easier.

Best regards,

On Tue, Apr 22, 2008 at 5:17 AM, Justin <[EMAIL PROTECTED]> wrote:

>  this new keyboard has problems, or i can't type ;-) .
>
>  it should be off not of
>
> Justin wrote:
>
>
>
> Dirk Verleysen wrote:
>
>
> Hi,
>
> I have been running a Postgres (8.2.4) on a Windows XP for over 3 months.
> Last week this machine died and I bought a new Vista machine today.
> Installed everything on it and a Postgres (8.2.7). The problem is that I
> cannot start the Postgres service. I keep getting the following error:
> FATAL:  could not create any TCP/IP sockets
>
>
> Anyone has any idea what I can do ?
>
> Thanks,
>
> Dirk
>
> turn of the firewall
>
>


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

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [GENERAL] Schema migration tools?

[Steve Atkins]

On Apr 21, 2008, at 7:44 PM, Christophe wrote:

Greetings,

We have the traditional three servers:

dev --> staging --> production

each with a PostgreSQL instance and the same schema, at least over  
time.  Right now, we handle schema migration (updating the schema  
for new releases) by manually-created scripts that apply the changes  
to staging and production.


I'd like a tool that would automatically create these scripts, and I  
wondered if anything like this existed.  The theory would be that it  
would consider two databases a and b, and produce the appropriate  
script to change b's schema to match a.


Does anything like this exist?  If not, I might have a new project...


There's a bunch. I've been using http://dbmstools.sourceforge.net/ for  
a while, and some others are http://xml2ddl.berlios.de/ and http://www.liquibase.org/ 
 . They're mostly focused on maintaining the schema in a non-sql- 
script format (with good reason) but can extract it from a running  
database too.


Or you can just maintain your schema by manually writing version n to n 
+1 upgrade scripts and version n to n-1 downgrade scripts and  
generating the schema for version n in the obvious way.


Cheers,
  Steve

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Schema migration tools?

[Joshua D. Drake]

Christophe wrote:

Greetings,
Does anything like this exist?  If not, I might have a new project...


Typically new projects are found over at www.pgfoundry.org. :P

Joshua D. Drake

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Schema migration tools?

[Christophe]

Greetings,

We have the traditional three servers:

dev --> staging --> production

each with a PostgreSQL instance and the same schema, at least over  
time.  Right now, we handle schema migration (updating the schema for  
new releases) by manually-created scripts that apply the changes to  
staging and production.


I'd like a tool that would automatically create these scripts, and I  
wondered if anything like this existed.  The theory would be that it  
would consider two databases a and b, and produce the appropriate  
script to change b's schema to match a.


Does anything like this exist?  If not, I might have a new project...

Thanks!
-- Christophe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] table as log (multiple writers and readers)

[David Wilson]

On Mon, Apr 21, 2008 at 7:55 PM, Joris Dobbelsteen
<[EMAIL PROTECTED]> wrote:
>
>  If you want to clean up the the staging table I have some concerns about
> the advisory lock. I think you mean exclusive table lock.

Either works, really. An advisory lock is really just a lock over
which you have control of the meaning, as long as you're using it in
the appropriate places. Also, an advisory lock on just the processes
doing staging-to-log moves would allow writes into the staging table
to continue concurrently with the staging-to-log transaction (whereas
an exclusive lock would unnecessarily prevent them).

Also, while Vance appears to have chosen to have a dedicated
staging-to-log process, even that isn't necessary- each reader can
simply do the lock/clear staging/unlock before any attempt to read-
unless you're polling that log table at truly crazy rates, the
overhead should be negligible and will ensure that the staging table
is simply cleared out "whenever necessary" while removing the
complexity of a separate process.


-- 
- David T. Wilson
[EMAIL PROTECTED]

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] table as log (multiple writers and readers)

[Joris Dobbelsteen]

David Wilson wrote:

(I originally missed replying to all here; sorry about the duplicate,
Vance, but figured others might be interested.

On Wed, Apr 16, 2008 at 1:55 PM, Vance Maverick <[EMAIL PROTECTED]> wrote:

 Another approach would be to queue the log entries in a "staging" table,
 so that a single process could move them into the log.  This is fairly
 heavyweight, but it would guarantee the consistent sequencing of the log
 as seen by a reader (even if the order of entries in the log didn't
 always reflect the true commit sequence in the staging table).  I'm
 hoping someone knows a cleverer trick.



Consider a loop like the following

advisory lock staging table
if (entries in table)
   copy entries to main log table as a single transaction
release advisory lock on staging table
read out and handle most recent log entries from main table

The advisory lock is automatically released on client disconnect, and
doing the whole thing within one transaction should prevent any
partial-copies on failures.

It doesn't matter that there are concurrent inserts to the staging
table because the staging table is always wiped all at once and
transferred in a synchronous fashion to the main table. You also can't
lose data, because it's always in one of the two tables.


If you want to clean up the the staging table I have some concerns about 
the advisory lock. I think you mean exclusive table lock.


There are other two options as well:

* Track which data is copies and remove those from the staging table 
that are in the new table.


* Use a serializable mode for the staging-to-log-copying transactions.
In this way you can just copy the table and trow away everything 
(without checking). This seems rather cheap and allows for concurrent 
processing.


- Joris

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: FW: Re: [GENERAL] create temp in function

[Adrian Klaver]

 -- Original message --
From: "Kerri Reno" <[EMAIL PROTECTED]>
> Adrian,
> 
> I don't understand.  Why do I need to use execute?  It runs fine the first
> time.  The second time it bombs, because it's not seeing schedrec
> correctly.  Which part should be in an execute query statement?

plpgsql caches query plans. In versions prior to 8.3 this meant that the first 
time you ran a function the plans for the statements where cached for use by 
later runs of the function in the same session. The error you are getting about 
OID missing means the function is looking for the OID of the temp table as it 
was cached in the first run and not finding it. To get around this you need to 
EXECUTE the create temp table statement. This causes the plan not be cached but 
run anew for each call of the function. If you follow the link I included in 
the previous email you will see some examples.

> 
> Thanks!
> Kerri
> 
> On 4/21/08, Adrian Klaver <[EMAIL PROTECTED]> wrote:
> >
> > Forgot to hit reply all.
> >
> > - Forwarded Message: --
> > From: [EMAIL PROTECTED] (Adrian Klaver)
> > To: "Kerri Reno" <[EMAIL PROTECTED]>
> > Subject: Re: [GENERAL] create temp in function
> > Date: Mon, 21 Apr 2008 23:01:53 +
> > > -- Original message --
> > > From: "Kerri Reno" <[EMAIL PROTECTED]>
> > > > Hi All!  I'm new to this list, but I've been using PG for a couple of
> > years
> > > > now.  I'm trying to do something in a function that I just can't seem
> > to do.
> > > >
> > > >
> > > > If I do the following in psql or pgadmin:
> > > > create temp table schedrec (sch text, cl text, st text);
> > > > select distinct(attrelid) from pg_attribute where attrelid =
> > > > 'schedrec'::regclass;
> > > > select * from schedrec;
> > > > drop table schedrec;
> > > >
> > > > I can do it over and over and over again without problem;
> > > >
> > > > But if I create and run the following function, it bombs on the second
> > run.
> > > > It gives me:
> > > > compassdevel_lb=# select testtemp();
> > > > NOTICE:  relid: 186270497
> > > > NOTICE:  count: 0
> > > >  testtemp
> > > > --
> > > >  t
> > > > (1 row)
> > > >
> > > > compassdevel_lb=# select testtemp();
> > > > NOTICE:  relid: 
> > > > ERROR:  relation with OID 186270497 does not exist
> > > > CONTEXT:  SQL statement "SELECT  count(*) from schedrec"
> > > > PL/pgSQL function "testtemp" line 9 at select into variables
> > > >
> > > > Here is my function:
> > > > create or replace function testtemp()
> > > > returns boolean as
> > > > $body$
> > > > declare
> > > > query text;
> > > > relid integer;
> > > > cnt integer;
> > > > begin
> > > > create temp table schedrec (sch text, cl text, st text);
> > > > select into relid distinct(attrelid) from pg_attribute where
> > attrelid =
> > > > 'schedrec'::regclass;
> > > > raise notice 'relid: %', relid;
> > > > select into cnt count(*) from schedrec;
> > > > raise notice 'count: %', cnt;
> > > > drop table schedrec;
> > > > if relid is null then
> > > > return false;
> > > > else
> > > > return true;
> > > > end if;
> > > > end;
> > > > $body$
> > > > language plpgsql security definer;
> > > >
> > > > Can anyone please help me with this?
> > >
> >
> > If you are running a version <8.3 you will need to use EXECUTE. See:
> >
> > 
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-S
> TATEMENTS-EXECUTING-DYN
> >
> > >
> > > > TIA,
> > > > Kerri




--
Adrian Klaver
[EMAIL PROTECTED]


--- Begin Message ---
Adrian,I don't understand.  Why do I need to use execute?  It runs fine the first time.  The second time it bombs, because it's not seeing schedrec correctly.  Which part should be in an execute query statement?
Thanks!KerriOn 4/21/08, Adrian Klaver <[EMAIL PROTECTED]> wrote:
Forgot to hit reply all.

- Forwarded Message: --
From: [EMAIL PROTECTED] (Adrian Klaver)
To: "Kerri Reno" <[EMAIL PROTECTED]>
Subject: Re: [GENERAL] create temp in function
Date: Mon, 21 Apr 2008 23:01:53 +
> -- Original message --
> From: "Kerri Reno" <[EMAIL PROTECTED]>
> > Hi All!  I'm new to this list, but I've been using PG for a couple of years
> > now.  I'm trying to do something in a function that I just can't seem to do.
> >
> >
> > If I do the following in psql or pgadmin:
> > create temp table schedrec (sch text, cl text, st text);
> > select distinct(attrelid) from pg_attribute where attrelid =
> > 'schedrec'::regclass;
> > select * from schedrec;
> > drop table schedrec;
> >
> > I can do it over and over and over again without problem;
> >
> > But if I create and run the following function, it bombs on the second run.
> > It gives me:
> > compassdevel_lb=# select testtemp();
> > NOTICE:  relid: 186270497
> > NOTICE:  count: 0
> >  testtemp
> > --
> >  t
> > (1 row)
> >
> > compassdevel_lb=# select tes

Re: FW: Re: [GENERAL] create temp in function

[Alvaro Herrera]

Kerri Reno wrote:
> Adrian,
> 
> I don't understand.  Why do I need to use execute?

It's a Postgres shortcoming.  It's fixed in 8.3 so if it annoys you too
much you can just upgrade.

> It runs fine the first
> time.  The second time it bombs, because it's not seeing schedrec
> correctly.  Which part should be in an execute query statement?

All sentences referring to the temp table.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: FW: Re: [GENERAL] create temp in function

[Kerri Reno]

Adrian,

I don't understand.  Why do I need to use execute?  It runs fine the first
time.  The second time it bombs, because it's not seeing schedrec
correctly.  Which part should be in an execute query statement?

Thanks!
Kerri

On 4/21/08, Adrian Klaver <[EMAIL PROTECTED]> wrote:
>
> Forgot to hit reply all.
>
> - Forwarded Message: --
> From: [EMAIL PROTECTED] (Adrian Klaver)
> To: "Kerri Reno" <[EMAIL PROTECTED]>
> Subject: Re: [GENERAL] create temp in function
> Date: Mon, 21 Apr 2008 23:01:53 +
> > -- Original message --
> > From: "Kerri Reno" <[EMAIL PROTECTED]>
> > > Hi All!  I'm new to this list, but I've been using PG for a couple of
> years
> > > now.  I'm trying to do something in a function that I just can't seem
> to do.
> > >
> > >
> > > If I do the following in psql or pgadmin:
> > > create temp table schedrec (sch text, cl text, st text);
> > > select distinct(attrelid) from pg_attribute where attrelid =
> > > 'schedrec'::regclass;
> > > select * from schedrec;
> > > drop table schedrec;
> > >
> > > I can do it over and over and over again without problem;
> > >
> > > But if I create and run the following function, it bombs on the second
> run.
> > > It gives me:
> > > compassdevel_lb=# select testtemp();
> > > NOTICE:  relid: 186270497
> > > NOTICE:  count: 0
> > >  testtemp
> > > --
> > >  t
> > > (1 row)
> > >
> > > compassdevel_lb=# select testtemp();
> > > NOTICE:  relid: 
> > > ERROR:  relation with OID 186270497 does not exist
> > > CONTEXT:  SQL statement "SELECT  count(*) from schedrec"
> > > PL/pgSQL function "testtemp" line 9 at select into variables
> > >
> > > Here is my function:
> > > create or replace function testtemp()
> > > returns boolean as
> > > $body$
> > > declare
> > > query text;
> > > relid integer;
> > > cnt integer;
> > > begin
> > > create temp table schedrec (sch text, cl text, st text);
> > > select into relid distinct(attrelid) from pg_attribute where
> attrelid =
> > > 'schedrec'::regclass;
> > > raise notice 'relid: %', relid;
> > > select into cnt count(*) from schedrec;
> > > raise notice 'count: %', cnt;
> > > drop table schedrec;
> > > if relid is null then
> > > return false;
> > > else
> > > return true;
> > > end if;
> > > end;
> > > $body$
> > > language plpgsql security definer;
> > >
> > > Can anyone please help me with this?
> >
>
> If you are running a version <8.3 you will need to use EXECUTE. See:
>
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> >
> > > TIA,
> > > Kerri
> > >
> > >
> >
>
> --
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
[EMAIL PROTECTED]  (928) 502-4240
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.

Re: [GENERAL] table as log (multiple writers and readers)

[Vance Maverick]

Thanks to all for your help.  I've adopted the scheme involving a
"staging" table -- the writer processes insert into that, then a single
"publisher" process pulls from that and writes to the log, giving a
clean serial order for any reader of the log.

Vance 

On Mon, 2008-04-21 at 23:59 +0200, Joris Dobbelsteen wrote:
> Craig Ringer wrote:
> [snip]
> > If you really want to make somebody cry, I guess you could do it with 
> > dblink - connect back to your own database from dblink and use a short 
> > transaction to commit a log record, using table-based (rather than 
> > sequence) ID generation to ensure that records were inserted in ID 
> > order. That'd restrict the "critical section" in which your various 
> > transactions were unable to run concurrently to a much shorter period, 
> > but would result in a log message being saved even if the transaction 
> > later aborted. It'd also be eye-bleedingly horrible, to the point where 
> > even the "send a message from a C function" approach would be nicer.
> 
> This will not work for the problem the TS has. Let a single transaction 
> hang for a long enough time before commit, while others succeed. It will 
> keep ordering of changes, but commits might come unordered.
> 
> The issue is, you don't really have the critical section as you 
> describe, there is no SINGLE lock you are 'fighting' for.
> 
> It will work with an added table write lock (or up), that will be the 
> lock for your critical section.
> 
> In my opinion I would just forget about this one rather quickly as you 
> more or less proposed...
> 
> - Joris

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

FW: Re: [GENERAL] create temp in function

[Adrian Klaver]

Forgot to hit reply all.

- Forwarded Message: --
From: [EMAIL PROTECTED] (Adrian Klaver)
To: "Kerri Reno" <[EMAIL PROTECTED]>
Subject: Re: [GENERAL] create temp in function
Date: Mon, 21 Apr 2008 23:01:53 +
> -- Original message --
> From: "Kerri Reno" <[EMAIL PROTECTED]>
> > Hi All!  I'm new to this list, but I've been using PG for a couple of years
> > now.  I'm trying to do something in a function that I just can't seem to do.
> > 
> > 
> > If I do the following in psql or pgadmin:
> > create temp table schedrec (sch text, cl text, st text);
> > select distinct(attrelid) from pg_attribute where attrelid =
> > 'schedrec'::regclass;
> > select * from schedrec;
> > drop table schedrec;
> > 
> > I can do it over and over and over again without problem;
> > 
> > But if I create and run the following function, it bombs on the second run.
> > It gives me:
> > compassdevel_lb=# select testtemp();
> > NOTICE:  relid: 186270497
> > NOTICE:  count: 0
> >  testtemp
> > --
> >  t
> > (1 row)
> > 
> > compassdevel_lb=# select testtemp();
> > NOTICE:  relid: 
> > ERROR:  relation with OID 186270497 does not exist
> > CONTEXT:  SQL statement "SELECT  count(*) from schedrec"
> > PL/pgSQL function "testtemp" line 9 at select into variables
> > 
> > Here is my function:
> > create or replace function testtemp()
> > returns boolean as
> > $body$
> > declare
> > query text;
> > relid integer;
> > cnt integer;
> > begin
> > create temp table schedrec (sch text, cl text, st text);
> > select into relid distinct(attrelid) from pg_attribute where attrelid =
> > 'schedrec'::regclass;
> > raise notice 'relid: %', relid;
> > select into cnt count(*) from schedrec;
> > raise notice 'count: %', cnt;
> > drop table schedrec;
> > if relid is null then
> > return false;
> > else
> > return true;
> > end if;
> > end;
> > $body$
> > language plpgsql security definer;
> > 
> > Can anyone please help me with this?
> 

If you are running a version <8.3 you will need to use EXECUTE. See:
 
http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-S
 TATEMENTS-EXECUTING-DYN

> 
> > TIA,
> > Kerri
> > 
> > 
> 


--
Adrian Klaver
[EMAIL PROTECTED]

-

--- Begin Message ---
--- Begin Message ---
Hi All!  I'm new to this list, but I've been using PG for a couple of years now.  I'm trying to do something in a function that I just can't seem to do. If I do the following in psql or pgadmin:
create temp table schedrec (sch text, cl text, st text);select distinct(attrelid) from pg_attribute where attrelid = 'schedrec'::regclass;select * from schedrec;drop table schedrec;
I can do it over and over and over again without problem;But if I create and run the following function, it bombs on the second run.  It gives me:compassdevel_lb=# select testtemp();
NOTICE:  relid: 186270497NOTICE:  count: 0 testtemp-- t(1 row)compassdevel_lb=# select testtemp();NOTICE:  relid: ERROR:  relation with OID 186270497 does not exist
CONTEXT:  SQL statement "SELECT  count(*) from schedrec"PL/pgSQL function "testtemp" line 9 at select into variablesHere is my function:create or replace function testtemp()    returns boolean as
$body$declare    query text;    relid integer;    cnt integer;begin    create temp table schedrec (sch text, cl text, st text);    select into relid distinct(attrelid) from pg_attribute where attrelid = 'schedrec'::regclass;
    raise notice 'relid: %', relid;    select into cnt count(*) from schedrec;    raise notice 'count: %', cnt;    drop table schedrec;    if relid is null then        return false;
    else        return true;    end if;end;$body$language plpgsql security definer;Can anyone please help me with this?TIA,Kerri-- Yuma Educational Computer ConsortiumCompass Development Team
Kerri Reno[EMAIL PROTECTED]  (928) 502-4240.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.
--- End Message ---
--- End Message ---

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL on Vista

[Justin]

this new keyboard has problems, or i can't type ;-) .

it should be off not of

Justin wrote:



Dirk Verleysen wrote:


Hi,

I have been running a Postgres (8.2.4) on a Windows XP for over 3 
months. Last week this machine died and I bought a new Vista machine 
today. Installed everything on it and a Postgres (8.2.7). The problem 
is that I cannot start the Postgres service. I keep getting the 
following error: FATAL:  could not create any TCP/IP sockets



Anyone has any idea what I can do ?

Thanks,

Dirk

turn of the firewall

Re: [GENERAL] PostgreSQL on Vista

[Justin]

Dirk Verleysen wrote:


Hi,

I have been running a Postgres (8.2.4) on a Windows XP for over 3 
months. Last week this machine died and I bought a new Vista machine 
today. Installed everything on it and a Postgres (8.2.7). The problem 
is that I cannot start the Postgres service. I keep getting the 
following error: FATAL:  could not create any TCP/IP sockets



Anyone has any idea what I can do ?

Thanks,

Dirk

turn of the firewall

Re: [GENERAL] table as log (multiple writers and readers)

[Joris Dobbelsteen]

Craig Ringer wrote:
[snip]
If you really want to make somebody cry, I guess you could do it with 
dblink - connect back to your own database from dblink and use a short 
transaction to commit a log record, using table-based (rather than 
sequence) ID generation to ensure that records were inserted in ID 
order. That'd restrict the "critical section" in which your various 
transactions were unable to run concurrently to a much shorter period, 
but would result in a log message being saved even if the transaction 
later aborted. It'd also be eye-bleedingly horrible, to the point where 
even the "send a message from a C function" approach would be nicer.


This will not work for the problem the TS has. Let a single transaction 
hang for a long enough time before commit, while others succeed. It will 
keep ordering of changes, but commits might come unordered.


The issue is, you don't really have the critical section as you 
describe, there is no SINGLE lock you are 'fighting' for.


It will work with an added table write lock (or up), that will be the 
lock for your critical section.


In my opinion I would just forget about this one rather quickly as you 
more or less proposed...


- Joris

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] create temp in function

[Kerri Reno]

Hi All!  I'm new to this list, but I've been using PG for a couple of years
now.  I'm trying to do something in a function that I just can't seem to do.


If I do the following in psql or pgadmin:
create temp table schedrec (sch text, cl text, st text);
select distinct(attrelid) from pg_attribute where attrelid =
'schedrec'::regclass;
select * from schedrec;
drop table schedrec;

I can do it over and over and over again without problem;

But if I create and run the following function, it bombs on the second run.
It gives me:
compassdevel_lb=# select testtemp();
NOTICE:  relid: 186270497
NOTICE:  count: 0
 testtemp
--
 t
(1 row)

compassdevel_lb=# select testtemp();
NOTICE:  relid: 
ERROR:  relation with OID 186270497 does not exist
CONTEXT:  SQL statement "SELECT  count(*) from schedrec"
PL/pgSQL function "testtemp" line 9 at select into variables

Here is my function:
create or replace function testtemp()
returns boolean as
$body$
declare
query text;
relid integer;
cnt integer;
begin
create temp table schedrec (sch text, cl text, st text);
select into relid distinct(attrelid) from pg_attribute where attrelid =
'schedrec'::regclass;
raise notice 'relid: %', relid;
select into cnt count(*) from schedrec;
raise notice 'count: %', cnt;
drop table schedrec;
if relid is null then
return false;
else
return true;
end if;
end;
$body$
language plpgsql security definer;

Can anyone please help me with this?
TIA,
Kerri

-- 
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
[EMAIL PROTECTED]  (928) 502-4240
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.

Re: [GENERAL] table as log (multiple writers and readers)

[Joris Dobbelsteen]

Andrew Sullivan wrote:

On Thu, Apr 17, 2008 at 12:44:51PM +0800, Craig Ringer wrote:

It won't work with multiple concurrent writers. There is no guarantee
that an INSERT with a timestamp older than the one you just saw isn't
waiting to commit.


This is pretty unlikely -- I won't say impossible, because I'm sure there's
some kernel-level race condition -- if you use the clock time approach and
SERIALIZABLE mode.


Don't, understand what SERIALIZABLE mode means (in this context):
* It makes restrictions on what you CAN & MAY read (i.e. controls 
visiblity).


If you want to serialize your accesses (which is something completely 
different) you are probably better of with using an exclusive table lock 
on the log table. Otherwise guarantees cannot be made on postgresql. 
Even worse, you MUST NOT use SERIALIZABLE mode if you are going to check 
you log table.


Similarly, handing referential integrity is a complex to handle and has 
special mechanisms in postgresql to handle that.



Going into detail about concurrently running code:
* Without synchronization mechanisms you CANNOT control in which order 
code is executed.



Also, you are not dealing with a race condition here. Its the semantics 
or requirements on the ordering. Race conditions are failures caused by 
a particular ordering (in time) of events, in such a way that corrupts 
the state. There can be prevented by proper locking, but have been 
overlooked (which is easy) and timing constraints are usually very 
stringent so that they are very hard to detect. Therefore, they are 
also, usually, very hard to encounter in common situations.




You could add a trigger that checks for other timestamps
< yours, I suppose.  Of course, that's pretty heavyweight, too.  How much is
the absolute serialization worth to you in performance?


You cannot do this in a reliable way. The Postgresql MVCC semantics are 
such that you can not do that in a reliable manner. The only way is if 
you take at least a write lock on the log table and ensure you are NOT 
in serializable mode.


Remember that you CANNOT view tuples that are not yet committed. In 
fact, with serializable mode you will only see changes from transactions 
that are completed BEFORE you started yours.



The only other thing I can suggest is what someone else did: commit them
with wallclock timestamps, and then have a different thread wake up every
_n_ seconds and put the records into the proper table in timestamp order.


You can guarantee:
* the proper ordering of log events for a single transaction (by 
property of transactions).
* modifications/delete to a single tuple (due to the synchronization 
point caused by the write lock).

This seems enough in the requested case.

You can even do so with a simple sequence counter if you are only 
interested the of ordering in time of events.


- Joris

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PostgreSQL on Vista

[Dirk Verleysen]

Hi,

I have been running a Postgres (8.2.4) on a Windows XP for over 3
months. Last week this machine died and I bought a new Vista machine
today. Installed everything on it and a Postgres (8.2.7). The problem
is that I cannot start the Postgres service. I keep getting the
following error: FATAL:  could not create any TCP/IP sockets


Anyone has any idea what I can do ?

Thanks,

Dirk

[GENERAL] Fwd: create temp in function

[Kerri Reno]

BTW, this happens the same in PostgreSQL 8.0 and 8.2.

Thanks!
Kerri

-- Forwarded message --
From: Kerri Reno <[EMAIL PROTECTED]>
Date: Apr 21, 2008 3:22 PM
Subject: create temp in function
To: pgsql-general@postgresql.org

Hi All!  I'm new to this list, but I've been using PG for a couple of years
now.  I'm trying to do something in a function that I just can't seem to do.


If I do the following in psql or pgadmin:
create temp table schedrec (sch text, cl text, st text);
select distinct(attrelid) from pg_attribute where attrelid =
'schedrec'::regclass;
select * from schedrec;
drop table schedrec;

I can do it over and over and over again without problem;

But if I create and run the following function, it bombs on the second run.
It gives me:
compassdevel_lb=# select testtemp();
NOTICE:  relid: 186270497
NOTICE:  count: 0
 testtemp
--
 t
(1 row)

compassdevel_lb=# select testtemp();
NOTICE:  relid: 
ERROR:  relation with OID 186270497 does not exist
CONTEXT:  SQL statement "SELECT  count(*) from schedrec"
PL/pgSQL function "testtemp" line 9 at select into variables

Here is my function:
create or replace function testtemp()
returns boolean as
$body$
declare
query text;
relid integer;
cnt integer;
begin
create temp table schedrec (sch text, cl text, st text);
select into relid distinct(attrelid) from pg_attribute where attrelid =
'schedrec'::regclass;
raise notice 'relid: %', relid;
select into cnt count(*) from schedrec;
raise notice 'count: %', cnt;
drop table schedrec;
if relid is null then
return false;
else
return true;
end if;
end;
$body$
language plpgsql security definer;

Can anyone please help me with this?
TIA,
Kerri

-- 
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
[EMAIL PROTECTED]  (928) 502-4240
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.

-- 
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
[EMAIL PROTECTED]  (928) 502-4240
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.

Re: [GENERAL] Storing computed values

[Colin Wetherbee]

Richard Broersma wrote:

On Mon, Apr 21, 2008 at 12:24 PM, Colin Wetherbee
<[EMAIL PROTECTED]> wrote:


Right, but the problem I see is that my locations are not actually
stored in foo.  Since many rows of foo can reference the same
location, the locations are stored in a separate table and, in
fact, are referenced by foreign keys (SERIAL/INTEGER types)
already.


I see, I haven't used PostGIS yet, so I am not sure how it is used. 
However, I am curious about the advantages or the intent of having 
duplicate points in a table that requires the surrogate ID in order

to be distinguished these duplicate point.  I only ask since I am not
 familiar with postGIS (perhaps this is the way it is intended to be 
used.)   To me this seems to be a bit de-normalized, unless there is 
something that differentiates or somehow makes these points unique 
these points.


In the example we're tossing around, these PostGIS points could be 
considered to be integers or anything else.  I just mentioned that they 
were PostGIS points because then it's easier to visualize a procedure 
that "connects" them.


The surrogate key exists because that's what users see, and it's a cheap
way for me to validate user input.  For example, if a user wants to 
connect point_id 123 and point_id 456, that's great, but if a user is 
connecting arbitrary longitudes and latitudes to others, that's not so 
great.  It wouldn't destroy anything, but it's not really how this 
application is intended to be used.


OTOH, I could keep the point_id in the points table and just not use it 
as the surrogate key for the main table anymore.


If you constrained your data to only allowed unique points in your 
points table, your problem would be solved in regards to caching, 
since you could simple use natural foreign keys to get the

connections data that you want.


Indeed.

I'll have to ponder natural foreign keys for a while before I make a 
decision on this.


Thank you very much for your input.

Colin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Storing computed values

[Richard Broersma]

On Mon, Apr 21, 2008 at 12:24 PM, Colin Wetherbee <[EMAIL PROTECTED]> wrote:

> Right, but the problem I see is that my locations are not actually stored in
> foo.  Since many rows of foo can reference the same location, the locations
> are stored in a separate table and, in fact, are referenced by foreign keys
> (SERIAL/INTEGER types) already.

I see, I haven't used PostGIS yet, so I am not sure how it is used.
However, I am curious about the advantages or the intent of having
duplicate points in a table that requires the surrogate ID in order to
be distinguished these duplicate point.  I only ask since I am not
familiar with postGIS (perhaps this is the way it is intended to be
used.)   To me this seems to be a bit de-normalized, unless there is
something that differentiates or somehow makes these points unique
these points.


If you constrained your data to only allowed unique points in your
points table, your problem would be solved in regards to caching,
since you could simple use natural foreign keys to get the connections
data that you want.

-- 
Regards,
Richard Broersma Jr.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Storing computed values

[Colin Wetherbee]

Richard Broersma wrote:

On Mon, Apr 21, 2008 at 11:19 AM, Colin Wetherbee <[EMAIL PROTECTED]> wrote:


My impression of functional indexes is that they're useful mostly in WHERE
queries, like the following.

SELECT foo, bar, baz FROM some_table WHERE lower(foo) = 'qux';

In this case, the index would be created on lower(foo).

How would I get the value of the functional index out of the index in my
case?


If this is the case, and you use Natural Foreign Keys, you only need
to select from your foo table:

SELECT Connect( location1, location2)
  FROM Foo
 WHERE location1 = ...
OR location2 = ...
OR connect(location1,location2) = ...; --this is where the
functional index would be useful


Right, but the problem I see is that my locations are not actually 
stored in foo.  Since many rows of foo can reference the same location, 
the locations are stored in a separate table and, in fact, are 
referenced by foreign keys (SERIAL/INTEGER types) already.


Colin


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Storing computed values

[Colin Wetherbee]

Colin Wetherbee wrote:

Richard Broersma wrote:
On Mon, Apr 21, 2008 at 11:02 AM, Colin Wetherbee 
<[EMAIL PROTECTED]> wrote:



Let's say my points table looks like this:

point_id | location
-+--
  1 | 0101... <-- some PostGIS geometry string
  2 | 0101...

And, my foo table, which contains data pertaining to these connections,
looks like this:

id | point_id_start | point_id_end
---++--
 1 |  1 |2

And, let's say my function is connect(location1, location2).



I would like to be able to retrieve that connection without using the
connect() procedure.  How would I be able to take advantage of a 
functional

index in this context?


I am not sure what kind of constraints you have on your points table
for location.  It location is unique, this might be an example where
you can solve your problem if you use a natural foreign key in foo
instead of a surrogate key.


Yes, every location is currently unique, and I can't think of a 
situation where I would have useful duplicates.



Then you could just create an index:

CREATE INDEX ON Foo Connect( location1, location2);


My impression of functional indexes is that they're useful mostly in 
WHERE queries, like the following.


SELECT foo, bar, baz FROM some_table WHERE lower(foo) = 'qux';

In this case, the index would be created on lower(foo).

How would I get the value of the functional index out of the index in my 
case?


I think I now see where you're going with this, but this makes my 
problem somewhat more interesting.


I can imagine two ways of solving this.

First, an INDEX with a JOIN... (I can see Tom Lane laughing at me now.)

CREATE INDEX foo_connect_idx
ON foo connect(p_start.location, p_end.location)
JOIN points AS p_start ON foo.point_id_start = p_start.point_id
JOIN points AS p_end ON foo.point_id_end = p_end.point_id;

Just in case this might work, I checked the documentation and found no 
mention of JOIN anywhere in the INDEX sections.


Second, bury the JOIN part in my PL/Perl function and use spi_ functions 
to retrieve my actual PostGIS locations so the process is transparent to 
CREATE INDEX.


Hmm.

Colin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Storing computed values

[Richard Broersma]

On Mon, Apr 21, 2008 at 11:19 AM, Colin Wetherbee <[EMAIL PROTECTED]> wrote:

> My impression of functional indexes is that they're useful mostly in WHERE
> queries, like the following.
>
> SELECT foo, bar, baz FROM some_table WHERE lower(foo) = 'qux';
>
> In this case, the index would be created on lower(foo).
>
> How would I get the value of the functional index out of the index in my
> case?

If this is the case, and you use Natural Foreign Keys, you only need
to select from your foo table:

SELECT Connect( location1, location2)
  FROM Foo
 WHERE location1 = ...
OR location2 = ...
OR connect(location1,location2) = ...; --this is where the
functional index would be useful


-- 
Regards,
Richard Broersma Jr.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Storing computed values

[Colin Wetherbee]

Richard Broersma wrote:

On Mon, Apr 21, 2008 at 11:02 AM, Colin Wetherbee <[EMAIL PROTECTED]> wrote:


Let's say my points table looks like this:

point_id | location
-+--
  1 | 0101... <-- some PostGIS geometry string
  2 | 0101...

And, my foo table, which contains data pertaining to these connections,
looks like this:

id | point_id_start | point_id_end
---++--
 1 |  1 |2

And, let's say my function is connect(location1, location2).



I would like to be able to retrieve that connection without using the
connect() procedure.  How would I be able to take advantage of a functional
index in this context?


I am not sure what kind of constraints you have on your points table
for location.  It location is unique, this might be an example where
you can solve your problem if you use a natural foreign key in foo
instead of a surrogate key.


Yes, every location is currently unique, and I can't think of a 
situation where I would have useful duplicates.



Then you could just create an index:

CREATE INDEX ON Foo Connect( location1, location2);


My impression of functional indexes is that they're useful mostly in 
WHERE queries, like the following.


SELECT foo, bar, baz FROM some_table WHERE lower(foo) = 'qux';

In this case, the index would be created on lower(foo).

How would I get the value of the functional index out of the index in my 
case?


Colin


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Storing computed values

[Richard Broersma]

On Mon, Apr 21, 2008 at 11:02 AM, Colin Wetherbee <[EMAIL PROTECTED]> wrote:

> Let's say my points table looks like this:
>
> point_id | location
> -+--
>   1 | 0101... <-- some PostGIS geometry string
>   2 | 0101...
>
> And, my foo table, which contains data pertaining to these connections,
> looks like this:
>
> id | point_id_start | point_id_end
> ---++--
>  1 |  1 |2
>
> And, let's say my function is connect(location1, location2).
>


> I would like to be able to retrieve that connection without using the
> connect() procedure.  How would I be able to take advantage of a functional
> index in this context?


I am not sure what kind of constraints you have on your points table
for location.  It location is unique, this might be an example where
you can solve your problem if you use a natural foreign key in foo
instead of a surrogate key.

Then you could just create an index:

CREATE INDEX ON Foo Connect( location1, location2);

-- 
Regards,
Richard Broersma Jr.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] In the belly of the beast (MySQLCon)

[Andrew Sullivan]

On Fri, Apr 18, 2008 at 11:15:09AM -0700, Joshua D. Drake wrote:

> sync transaction have to run? You know vacuum isn't working while
> that transaction is open right? Are you going to have to setup up a
> dozen different replicated sets in order to get it done? 

A dozen sets isn't really a big deal, though.  The big problem is when you
have really huge tables.

A

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Storing computed values

[Colin Wetherbee]

Colin Wetherbee wrote:

SELECT connect(p_start.location, p_end.location)
FROM foo
JOIN points AS p_start ON foo.point_id_start = points.point_id
JOIN points AS p_end   ON foo.point_id_end   = points.point_id
WHERE foo.id = 8192;


As I didn't test this code, my syntax was slightly incorrect.

JOIN ... ON ... = p_start.point_id
JOIN ... ON ... = p_end.point_id

Colin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Storing computed values

[Colin Wetherbee]

Richard Broersma wrote:

On Mon, Apr 21, 2008 at 10:34 AM, Colin Wetherbee <[EMAIL PROTECTED]> wrote:


I would like to have a table that contains a connection for each distinct
pair of points (point1 to point2 is the same as point2 to point1).  This
table would then be automatically updated every time a modification is made
to the reference table.  If my calculation is correct, the new table would
contain 3,654,456 rows using the current data set.

I realize I could use a TRIGGER to keep the connections table fresh, and
perhaps that's also a solution.

But, really, I'm just wondering if PostgreSQL has some automated, built-in
facility for situations like this?


Would a functional index do this for you?  Perhaps, you wouldn't even
need a table is you store these computed values in an index instead.


I'm not sure, as I've never used one before.  I think I briefly 
considered it a while back and decided it wouldn't do what I want 
because I don't know the value of the connection before-hand.  Perhaps 
you can steer me in the right direction.


Let's say my points table looks like this:

point_id | location
-+--
   1 | 0101... <-- some PostGIS geometry string
   2 | 0101...

And, my foo table, which contains data pertaining to these connections, 
looks like this:


id | point_id_start | point_id_end
---++--
 1 |  1 |2

And, let's say my function is connect(location1, location2).

Right now, in order to get my connection, I'm using something like:

SELECT connect(p_start.location, p_end.location)
FROM foo
JOIN points AS p_start ON foo.point_id_start = points.point_id
JOIN points AS p_end   ON foo.point_id_end   = points.point_id
WHERE foo.id = 8192;

I would like to be able to retrieve that connection without using the 
connect() procedure.  How would I be able to take advantage of a 
functional index in this context?


As I mentioned above, I don't know the result of connect() before the 
query; that's what I'm trying to compute, not what I'm trying to search 
against.


Thanks.

Colin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Storing computed values

[Richard Broersma]

On Mon, Apr 21, 2008 at 10:34 AM, Colin Wetherbee <[EMAIL PROTECTED]> wrote:

> I would like to have a table that contains a connection for each distinct
> pair of points (point1 to point2 is the same as point2 to point1).  This
> table would then be automatically updated every time a modification is made
> to the reference table.  If my calculation is correct, the new table would
> contain 3,654,456 rows using the current data set.
>
> I realize I could use a TRIGGER to keep the connections table fresh, and
> perhaps that's also a solution.
>
> But, really, I'm just wondering if PostgreSQL has some automated, built-in
> facility for situations like this?

Would a functional index do this for you?  Perhaps, you wouldn't even
need a table is you store these computed values in an index instead.


-- 
Regards,
Richard Broersma Jr.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Storing computed values

[Colin Wetherbee]

Greetings.

I have a question regarding storing computed values.  Essentially, it's 
a question about caching, and I'm willing to implement a cache on the 
client side or using pg_memcache, but I wonder if I can do this in a 
PostgreSQL table, instead?


The background is that I'm using PostGIS to store thousands of points 
across the globe.  Once in a while, I want to connect a subset of these 
points using an algorithm I have in a PL/Perl procedure.  Depending on 
where the endpoints are located, this algorithm takes between 10 ms and 
40 ms to run for each pair of points.  Hundreds of these points are 
connected and then displayed over the web, which, essentially, takes a 
really long time.  Some connections are calculated very frequently, and 
some connections will probably never be calculated, though it's 
difficult to determine which connections fit those descriptions.


The locations of these points sometimes change, and, relatively 
infrequently, new points are added.  Even more infrequently, some points 
are deleted.


I would like to have a table that contains a connection for each 
distinct pair of points (point1 to point2 is the same as point2 to 
point1).  This table would then be automatically updated every time a 
modification is made to the reference table.  If my calculation is 
correct, the new table would contain 3,654,456 rows using the current 
data set.


I realize I could use a TRIGGER to keep the connections table fresh, and 
perhaps that's also a solution.


But, really, I'm just wondering if PostgreSQL has some automated, 
built-in facility for situations like this?


Thanks.

Colin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Tsearch2 Spanish Dictionary

[Alvaro Herrera]

Mario Ignacio Rodríguez Cortés wrote:

> But in postgresql-8.3.1:
> 
> SELECT to_tsvector('spanish','estadística');
>  to_tsvector 
> -
>  'stic':2
> (1 row)

It works for me:

alvherre=# SELECT to_tsvector('spanish','estadística');
 to_tsvector  
--
 'estadist':1
(1 fila)

alvherre=# select version();
   version  
  
--
 PostgreSQL 8.4devel on x86_64-unknown-linux-gnu, compiled by GCC gcc-4.1 (GCC) 
4.1.3 20080114 (prerelease) (Debian 4.1.2-19)
(1 fila)


So I'm thinking you've got encoding problems.  Did you set up
client_encoding appropriately?

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Tsearch2 Spanish Dictionary

[Mario Ignacio Rodríguez Cortés]

Hi All:

I have installed postgresql 8.3.1 on a Gentoo server, but i think that
the spanish dictionary isn't the correct because i have another two
machines with other postgresql versions and tserch2 installed, and a
simple test that i do is make a query with the spanish dictionary, i get
the follow results, the problem that i saw is in the accent, if i put a
word with accent doesn't work:

In postgresql-8.1.8:

SELECT to_tsvector('spanish','estadística');
 to_tsvector  
--
 'estadist':1
(1 fila)


In postgresql-8.2.4:

SELECT to_tsvector('spanish','estadística');
 to_tsvector  
--
 'estadist':1
(1 fila)

But in postgresql-8.3.1:

SELECT to_tsvector('spanish','estadística');
 to_tsvector 
-
 'stic':2
(1 row)

whitout accent:

SELECT to_tsvector('spanish','estadistica');
 to_tsvector 
-
 'estadist':2
(1 row)



what should i do to make it work well?

Thanks.

Mario Rdz.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] help with "plpgsql"

[Shane Ambler]

Pau Marc Munoz Torres wrote:

Hi everybody

I trying to upload some plpsql functions to postgresql database using a perl
script and i get the following error


 psql:/usr/local/Make2D-DB_II
/pgsql/make2db_functions.pgsql:85: ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE LANGUAGE to load the language into the database.


and then when I try to create the language, i get

geldb=# CREATE LANGUAGE plpgsql;

ERROR:  language "plpgsql" already exists

anybody knows what's wrong?

Thanks



My first guess would be what db are you connected to?

Is the script connected to the same db you are connected to when you run 
psql?


I would probably add the CREATE LANGUAGE command to the start of the 
perl script.



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] help with "plpgsql"

[Christophe]

On Apr 21, 2008, at 8:51 AM, Pau Marc Munoz Torres wrote:

 psql:/usr/local/Make2D-DB_II
/pgsql/make2db_functions.pgsql:85: ERROR:  language "plpgsql" does  
not exist

HINT:  Use CREATE LANGUAGE to load the language into the database.


and then when I try to create the language, i get

geldb=# CREATE LANGUAGE plpgsql;

ERROR:  language "plpgsql" already exists


Are you certain that you are dealing with the same database in both  
situations?


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] In the belly of the beast (MySQLCon)

[Greg Smith]

On Sun, 20 Apr 2008, Scott Marlowe wrote:


I wonder if there's a comprehensive list somewhere...


There's a good list on the database side at 
http://www.postgresql.org/docs/current/interactive/populate.html but it 
doesn't dwelve into hardware changes.  I put a reference to that as a 
placeholder at http://wiki.postgresql.org/wiki/Performance_Optimization 
with the intention of including broader suggestions in a new page there 
one day.  You're welcome to take a stab at that yourself.


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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] help with "plpgsql"

[Craig Ringer]

Pau Marc Munoz Torres wrote:

ERROR:  language "plpgsql" already exists

anybody knows what's wrong?
  
Is there any chance you might be connecting to a different database with 
the perl script and with psql? Procedural languages must be installed 
into a particular database.


--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] help with "plpgsql"

[Pau Marc Munoz Torres]

Hi everybody

I trying to upload some plpsql functions to postgresql database using a perl
script and i get the following error


 psql:/usr/local/Make2D-DB_II
/pgsql/make2db_functions.pgsql:85: ERROR:  language "plpgsql" does not exist
HINT:  Use CREATE LANGUAGE to load the language into the database.


and then when I try to create the language, i get

geldb=# CREATE LANGUAGE plpgsql;

ERROR:  language "plpgsql" already exists

anybody knows what's wrong?

Thanks

-- 
Pau Marc Muñoz Torres

Laboratori de Biologia Computacional
Institut de  Biotecnologia   i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)

telèfon: 93 5812807
Email : [EMAIL PROTECTED]

Re: [GENERAL] JDBC addBatch more efficient?

[David Wall]

Does this means that the two features are independent one from each 
other ?
In other words, can we say that JDBC batch will limit information 
exchange between client and server while Postgres prepared statements 
will optimize their execution ?
I've not used it yet, but my impression is that you can use 
PreparedStatements (in a loop typically), often surrounded by jdbc 
transaction commit so each statement doesn't run in its own 
transactions, do an addBatch and then submit that so all of the 
statements are executed by the server as a group.  So in this case, yes, 
they should be independent of each other.


David

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_bulkloader (was Re: In the belly of the beast (MySQLCon))

[Chris Browne]

[EMAIL PROTECTED] ("Scott Marlowe") writes:
> What I keep dreaming of is a process that lets slony use pg_bulkloader
> or something like it to do the initial load...

Does there seem to be some likelihood of some portion of pg_bulkloader
getting added to core?  It sounds like it's worth looking at
pg_bulkloader...
-- 
select 'cbbrowne' || '@' || 'linuxdatabases.info';
http://www3.sympatico.ca/cbbrowne/wp.html
"It has every known bug fix to everything." -- KLH (out of context)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] In the belly of the beast (MySQLCon)

[Chris Browne]

[EMAIL PROTECTED] (Csaba Nagy) writes:
> On Sun, 2008-04-20 at 11:32 -0600, Scott Marlowe wrote:
>> On Sun, Apr 20, 2008 at 11:12 AM, Scott Ribe <[EMAIL PROTECTED]> wrote:
>> > > I am going to play with this and see where it breaks, but it's going to 
>> > > be
>> >  > an enormous time investment to babysit it.
>
> One more suggestion: if you happen to use the alt_perl tools, be sure to
> uncomment/change the line:
>
> $SYNC_CHECK_INTERVAL = 1000;
>
> In my experience it made a big difference to set that to 6 (meaning
> sync events created once per minute instead of once per second) for the
> synchronizing after copy phase. The number of events generated while the
> copy over is running can be so big that it will never get in sync again
> otherwise...

+1

I'm adding a note about that to the "best practices" in the admin guide.
-- 
select 'cbbrowne' || '@' || 'linuxfinances.info';
http://cbbrowne.com/info/lsf.html
Disco is to music what Etch-A-Sketch is to art.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC addBatch more efficient?

[Ivano Luberti]

Does this means that the two features are independent one from each other ?
In other words, can we say that JDBC batch will limit information 
exchange between client and server while Postgres prepared statements 
will optimize their execution ?


Kris Jurka ha scritto:



On Sun, 20 Apr 2008, David Wall wrote:

Just checking if the JDBC library's batch processing code is more 
efficient with respect to the postgresql back end or not.  Does it 
really batch the requests and submit them once over the link, or does 
it just send them to the database to be processed one at a time?




The JDBC driver's batch processing is more efficient than regular 
execution because it requires fewer network roundtrips so there's less 
waiting.  The JDBC batch is broken into an internal batch size of 256 
statement and all of these are sent over to the server at once.


Kris Jurka



--
==
Archimede Informatica NEWS!
==

Realizzato il Sistema Integrato per la biglietteria della Torre di Pisa:
prenotazione, vendita, pre-vendita ed emissione dei biglietti di ingresso
alla Torre sia online che presso le biglietterie dislocate sulla piazza:

_http://www.opapisa.it/boxoffice

_Partner del Progetto Ci-Tel "Front office Telematico per il cittadino"
Ente Coordinatore Comune di Pisa

_http://www.comune.pisa.it/doc/e-government.htm


_==
dott. Ivano Mario Luberti
Archimede Informatica societa' cooperativa a r. l.
Sede Operativa
Via Gereschi 36 - 56126- Pisa
tel.: +39-050- 580959
tel/fax: +39-050-9711344
e-mail: [EMAIL PROTECTED]
web: _http://www.archicoop.it


_ __ 

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC addBatch more efficient?

[David Wall]

The JDBC driver's batch processing is more efficient than regular 
execution because it requires fewer network roundtrips so there's less 
waiting.  The JDBC batch is broken into an internal batch size of 256 
statement and all of these are sent over to the server at once.


That's great, Kris.  I  believe our code only does 100 updates/deletes 
before committing the transaction anyway, so this should work well for us.


Thanks,
David

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to tell if 64 bit vs 32 bit engine?

[zedware]

On 4月21日, 上午1时35分, [EMAIL PROTECTED] (Peter Eisentraut) wrote:
> Zoltan Boszormenyi wrote:
> > select version();
>
> > It will tell you the compiler version and arch as well. You can deduce
> > from there.
>
> That approach is not reliable.  I often build and run a 32-bit build of
> PostgreSQL on a machine that claims to be something like
> x86_64-unknown-linux-gnu.
>
> --
> Sent via pgsql-general mailing list ([EMAIL PROTECTED])
> To make changes to your 
> subscription:http://www.postgresql.org/mailpref/pgsql-general

At the client side as libpq, `select version()' is the best solution
now.
It seems a good idea to let the backend support a GUC parameter like
those used in Linux/UNIX.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Missing Log File

[Howard Cole]

I think I've solved this one - the compared the old to the new 
postgresql.conf; the new version did not redirect the standard output.

Thanks.

Howard Cole wrote:
I've just installed Postgres 8.2.7 on a W2K3 machine, and created the 
data directory post installation using initdb.
Everything appears to be working fine, but there are no log files 
created. Nor is there a pg_log subdirectory.

I have made no changes to the postgresql.conf file. What am I missing?

Thanks,

Howard
www.selestial.com




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Missing Log File

[Howard Cole]

I've just installed Postgres 8.2.7 on a W2K3 machine, and created the 
data directory post installation using initdb.
Everything appears to be working fine, but there are no log files 
created. Nor is there a pg_log subdirectory.

I have made no changes to the postgresql.conf file. What am I missing?

Thanks,

Howard
www.selestial.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] In the belly of the beast (MySQLCon)

[Csaba Nagy]

On Sun, 2008-04-20 at 11:32 -0600, Scott Marlowe wrote:
> On Sun, Apr 20, 2008 at 11:12 AM, Scott Ribe <[EMAIL PROTECTED]> wrote:
> > > I am going to play with this and see where it breaks, but it's going to be
> >  > an enormous time investment to babysit it.

One more suggestion: if you happen to use the alt_perl tools, be sure to
uncomment/change the line:

$SYNC_CHECK_INTERVAL = 1000;

In my experience it made a big difference to set that to 6 (meaning
sync events created once per minute instead of once per second) for the
synchronizing after copy phase. The number of events generated while the
copy over is running can be so big that it will never get in sync again
otherwise...

Cheers,
Csaba.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Insert Rewrite rules

[David]

I dropped the trigger again and now it works... but it is really slow
it was maxing out the cpu doing 26r/s not that this is a big
problem because the firewall code wont be reling on the rewrite rules
to put the data in the correct spot as it will just insert it into the
correct table and I have modified my data loader to do the same to get
the 600G of data back in in partitioned tables.

And as far as I know it was a row level trigger but I could be wrong

On Mon, Apr 21, 2008 at 05:32:29PM +1000, Klint Gore wrote:
> David wrote:
> >I am having problems with the rewrite rules though it seems to be skipping 
> >over any rule that has a where statement in it ie
> >
> >CREATE OR REPLACE RULE firewall_test AS ON INSERT TO firewall DO INSTEAD 
> >INSERT INTO firewall_y2008m04d21 VALUES(NEW."time");
> >INSERT INTO firewall (time) VALUES('2008-04-21 12:00:00');
> >INSERT 1029459 1
> >
> >works but
> >
> >CREATE OR REPLACE RULE firewall_test AS ON INSERT TO firewall WHERE TRUE 
> >DO INSTEAD INSERT INTO firewall_y2008m04d21 VALUES(NEW."time");
> >INSERT INTO firewall (time) VALUES('2008-04-21 12:00:00');
> >ERROR:  No inserting to firewall please
> >
> >doesn't. I have placed a trigger on the table to prevent anything from 
> >inserting into the top level table hence the error.
> >
> >an example of the full rule we are trying to use that doesn't work is
> >
> >firewall_y2008m04d21_insert AS
> >ON INSERT TO firewall
> >   WHERE new."time" >= '2008-04-21 00:00:00'::timestamp without time zone 
> >   AND new."time" < '2008-04-22 00:00:00'::timestamp without time zone DO 
> >   INSTEAD  INSERT INTO firewall_y2008m04d21 ("time", firewall, pri, sent, 
> >   rcvd, lsent, lrcvd, duration, src, dst, arg, msg, ref, dstname, 
> >   srcname, agent, server, srcclass, dstclass, rule, username, proto, op, 
> >   result, vpn, type, cache, direction, content, fwdfor, coninfo, 
> >   tcpflags, method, action, policy, service, engine, state, fwid, block, 
> >   authprofile, summarised, realm, clientmac, account, count, interface) 
> >  VALUES (new."time", new.firewall, new.pri, new.sent, new.rcvd, 
> >  new.lsent, new.lrcvd, new.duration, new.src, new.dst, new.arg, new.msg, 
> >  new.ref, new.dstname, new.srcname, new.agent, new.server, new.srcclass, 
> >  new.dstclass, new.rule, new.username, new.proto, new.op, new.result, 
> >  new.vpn, new.type, new.cache, new.direction, new.content, new.fwdfor, 
> >  new.coninfo, new.tcpflags, new.method, new.action, new.policy, 
> >  new.service, new.engine, new.state, new.fwid, new.block, 
> >  new.authprofile, new.summarised, new.realm, new.clientmac, new.account, 
> >  new.count, new.interface)
> >
> >There is one of these for each day with only the times changing.
> >
> >Am I missing something or is this just broken?
> >  
> Have you got a statement trigger or a row trigger?
> 
> 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]
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 

-- 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to tell if 64 bit vs 32 bit engine?

[Uwe Seher]

On Fri, 18 Apr 2008 17:30:18 +0200, Keaton Adams <[EMAIL PROTECTED]>
wrote:

I did search the archives for this But didn't see a posting that  
directly answered the question.


How do I tell if the 32 bit version of PostgreSQL is running on a 64 bit  
machine, or if the 64 bit version was installed?  Is there a pg_ table  
that I can query or a config file I can look in to find out?  I found  
nothing in postgresql.conf that would indicate this.


Thanks,

Keaton





--

http://www.gispunkt.de - http://maps.gispunkt.de


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Insert Rewrite rules

[Klint Gore]

David wrote:

I am having problems with the rewrite rules though it seems to be skipping over 
any rule that has a where statement in it ie

CREATE OR REPLACE RULE firewall_test AS ON INSERT TO firewall DO INSTEAD INSERT INTO 
firewall_y2008m04d21 VALUES(NEW."time");
INSERT INTO firewall (time) VALUES('2008-04-21 12:00:00');
INSERT 1029459 1

works but

CREATE OR REPLACE RULE firewall_test AS ON INSERT TO firewall WHERE TRUE DO INSTEAD 
INSERT INTO firewall_y2008m04d21 VALUES(NEW."time");
INSERT INTO firewall (time) VALUES('2008-04-21 12:00:00');
ERROR:  No inserting to firewall please

doesn't. I have placed a trigger on the table to prevent anything from 
inserting into the top level table hence the error.

an example of the full rule we are trying to use that doesn't work is

firewall_y2008m04d21_insert AS
ON INSERT TO firewall
   WHERE new."time" >= '2008-04-21 00:00:00'::timestamp without time zone AND new."time" < '2008-04-22 00:00:00'::timestamp without time zone DO INSTEAD  INSERT INTO firewall_y2008m04d21 ("time", firewall, pri, sent, rcvd, lsent, lrcvd, duration, src, dst, arg, msg, ref, dstname, srcname, agent, server, srcclass, dstclass, rule, username, proto, op, result, vpn, type, cache, direction, content, fwdfor, coninfo, tcpflags, method, action, policy, service, engine, state, fwid, block, authprofile, summarised, realm, clientmac, account, count, interface) 
  VALUES (new."time", new.firewall, new.pri, new.sent, new.rcvd, new.lsent, new.lrcvd, new.duration, new.src, new.dst, new.arg, new.msg, new.ref, new.dstname, new.srcname, new.agent, new.server, new.srcclass, new.dstclass, new.rule, new.username, new.proto, new.op, new.result, new.vpn, new.type, new.cache, new.direction, new.content, new.fwdfor, new.coninfo, new.tcpflags, new.method, new.action, new.policy, new.service, new.engine, new.state, new.fwid, new.block, new.authprofile, new.summarised, new.realm, new.clientmac, new.account, new.count, new.interface)


There is one of these for each day with only the times changing.

Am I missing something or is this just broken?
  

Have you got a statement trigger or a row trigger?

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]


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] JDBC addBatch more efficient?

[Kris Jurka]

On Sun, 20 Apr 2008, David Wall wrote:

Just checking if the JDBC library's batch processing code is more efficient 
with respect to the postgresql back end or not.  Does it really batch the 
requests and submit them once over the link, or does it just send them to the 
database to be processed one at a time?




The JDBC driver's batch processing is more efficient than regular 
execution because it requires fewer network roundtrips so there's less 
waiting.  The JDBC batch is broken into an internal batch size of 256 
statement and all of these are sent over to the server at once.


Kris Jurka

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general