Re: [GENERAL] scroll cursor bug or me?

[Sim Zacks]

It seems to me that scroll cursors are not valid in plpgsql.
The following query in PGAdmin works. run one line at a time.

begin work;

declare bob scroll cursor for select * from testtbl;

fetch forward 5 from bob;
fetch prior  from bob;

rollback work;

"Larry Morroni" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi, I am hitting an issue when using SCROLL CURSORS.
>
> -->My Setup:
> PostgreSQL 8.0.3 on Windows XP Pro SP2
>
>
> -->My Test Function:
> CREATE OR REPLACE FUNCTION TestFunction () RETURNS varchar(50) AS
> $$
> DECLARE CursorProductGroups SCROLL CURSOR FOR SELECT * FROM TEST_TABLE;
> BEGIN
>  RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql
> ;
>
> -->psql spits back the following:
>
> STORE_PHYSICAL_A=# \i test.sql
> psql:test.sql:9: ERROR:  syntax error at or near "CURSOR"
> CONTEXT:  invalid type name "SCROLL CURSOR FOR SELECT * FROM TEST_TABLE"
> compile of PL/pgSQL function "testfunction" near line 1
> STORE_PHYSICAL_A=#
>
> This seems like a bug to me because my demo looks
> just like the examples given in the PostgreSQL documentation.  ie...
>
> -- BEGIN POSTGRES DOC EXAMPLE
> BEGIN WORK;
> DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;
> FETCH FORWARD 5 FROM liahona;
> CLOSE liahona;
> COMMIT WORK;
> -- END POSTGRES DOC EXAMPLE
>
> Any ideas?
>
> Larry Morroni



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

[GENERAL] Clustering and replication options

[Gregory Youngblood]

I am looking for some information about clustering and replication  
options for postgresql.


I am aware of pgcluster, but have been unable to find anyone willing  
to share details about actually using it in a production environment.  
That's a little disconcerting. Is pgcluster not really ready for full  
production use right now?


I really would like to setup a multi-master cluster of databases, and  
pgcluster seems just perfect for the task. I will be testing it for  
myself in a few weeks.


I was hoping I could solicit some advice, or really any information,  
about other possible clustering or replication methods. I know slony- 
I is available, and that it is used b the .org registry, I believe,  
as well as other places. It seems to be pretty mature as well.  
However, I'm concerned about the master to multi-slave model. For  
databases with mostly reads and fewer writes, that model seems good.  
But, it seems like replication lag, not to mention the added  
complexity of splitting read and write operations between two  
database connections, could cause problems for applications with a  
large number of both reads and writes. Any lag would potentially  
allow stale data to be read from a slave after it had been updated on  
the master.


Can someone point me at some good references for either slony-I or  
pgcluster, other than their actual home pages? I've spent more time  
reading and analyzing the pgcluster configuration than slony-I, as my  
needs include support for frequent, multiple writes.


Thanks,
Greg




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

  http://archives.postgresql.org

Re: [GENERAL] Making the DB secure

[Freemail]

Hi All,
I am really sorry for upsetting the list. You are absolutelly right, I do
have applied a network specialist, who stated that the VPN is a must, and we
started to configure and test the server together (using OpenVPN). 

Actually this application is going to operate in a test phase for months, so
we have the time to test securty issues. 
Dear Geoffrey, please do not worry, we won't misapply any sensitive data. 

BTW, I would like to close this thread on this list, because this is rather
off topic here. Thank you very much for opening up my eyes. I really thought
I can find it out alone. I wasn't right. Thanks for you all. 

Certainly anybody is wellcome to write me in private.


Regards,
-- Csaba Együd.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Geoffrey
Sent: Tuesday, June 21, 2005 11:41 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Making the DB secure

Együd Csaba wrote:
> Hi,
> thank you very much. These are very good ideas, I think. 
> I forgot one thing to mention. We will have very few clients (max. 20) 
> and all clients will be required to have a fix IP address. Fix IP 
> addresses can be listed in pg_hba.conf to filter incoming IPs very 
> efficiently. With this note, do you think we need VPN or other
enhancement?

YOU NEED A SECURITY CONSULTANT.  If you think you can rely on static ips as
a security tool, you are clueless.  I'm sorry, but the fact that you
mentioned that this database contains medical information really disturbs
me.

A static IP insures NOTHING.  A vpn will secure the connection and protect
it.

--
Until later, Geoffrey

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

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

sive
lock on the index's table, so until you roll back, no other transaction will
be able to touch the table at all.  So the whole thing may be a nonstarter
in a production database anyway :-(.  You can probably get away with
BEGIN;
DROP INDEX ...
EXPLAIN ...
ROLLBACK;
if you fire it from a script rather than by hand --- but EXPLAIN ANALYZE
might be a bad idea ...

regards, tom lane

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

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.10/25 - Release Date: 2005.06.21.
 



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.10/25 - Release Date: 2005.06.21.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] enebling regular user to create new users ?

[Zlatko Matić]

Berend,
 
Thank you for the function code. It helped me a lot!
Regards,
 
Zlatko 
 
---Original Message---
 

From: Berend Tober
Date: 06/15/05 18:08:22
To: Zlatko Matić
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] enebling regular user to create new users ?
 
Zlatko Matić wrote:
 
> I know that superusers are allowed to do everything on the database,
> but I consider this as dangerous. I want to  have some user group with
> rights of creating new users and giving them some authorizations, but
> without such wide power as superusers have. So,
> I was thinking about two possible scenarios:
> a) to allow regular users  to create new users
> b) to restrict superuser's permissions
>
> What is possible and what do you suggest ?
 
CREATE OR REPLACE FUNCTION create_user(name)
  RETURNS bool AS
'
DECLARE
  PWD VARCHAR;
  CMD VARCHAR;
BEGIN
  PWD := \'\'\'\' || get_random_string(8) || \'\'\'\';
  IF EXISTS(SELECT 1 FROM pg_user WHERE usename = $1) THEN
RETURN FALSE;
  END IF;
  CMD := \'CREATE USER "\' || $1 || \'" WITH ENCRYPTED PASSWORD \' ||
PWD || \' IN GROUP gen_user\';
  EXECUTE CMD;
  RETURN TRUE;
END;
'
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
REVOKE ALL ON FUNCTION create_user(name) FROM public;
GRANT EXECUTE ON FUNCTION create_user(name) TO GROUP pseudo_dba;
 
 
CREATE OR REPLACE FUNCTION alter_group(name, bool, name)
  RETURNS bool AS
'
DECLARE
  l_group ALIAS FOR $1;
  l_create_user ALIAS FOR $2;
  l_username ALIAS FOR $3;
  CMD VARCHAR;
  MIN_SUPER_USER INTEGER := 1;
BEGIN
  IF (l_create_user NOTNULL)  THEN
IF (l_create_user) THEN
  PERFORM create_user(l_username);
  CMD := \'ALTER GROUP \' || l_group || \' ADD USER "\' ||
l_username || \'"\';
  EXECUTE CMD;
ELSIF (l_group = \'gen_user\') THEN
  PERFORM drop_user(l_username);
ELSE
  CMD := \'ALTER GROUP \' || l_group || \' DROP USER "\' ||
l_username || \'"\';
  EXECUTE CMD;
END IF;
IF (SELECT COUNT(*) FROM group_members WHERE groname =
\'pseudo_dba\') < MIN_SUPER_USER THEN
  RAISE EXCEPTION \'At least % super user(s) must be defined in
order to create new user accounts.\', MIN_SUPER_USER;
END IF;
  END IF;
  RETURN TRUE;
END;
'
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
REVOKE ALL ON FUNCTION alter_group(name, bool, name) FROM public;
GRANT EXECUTE ON FUNCTION alter_group(name, bool, name) TO GROUP pseudo_dba;
 
-- etc., etc., etc.,

Re: [GENERAL] is this a bug ?

[Sim Zacks]

This is a normal error.
If you want to remove the sequence first you have to remove the default
value from the sequenced field.
remember serial means int4 with a default of get next serial number.

ALTER TABLE test
   ALTER COLUMN test_id_key DROP DEFAULT;

After that you could probably drop the sequence without a problem

"Cedric BRINER" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> hi,
>
> I'm facing this problem
>
> DROP TABLE test;
> DROP TABLE new_test;
> CREATE TABLE test (id serial NOT NULL UNIQUE, nom varchar(32));
> INSERT INTO test ("nom") values ('cedric');
> INSERT INTO test ("nom") values ('felix');
> INSERT INTO test ("nom") values ('julien');
>
> CREATE TABLE new_test (id serial NOT NULL UNIQUE, nom varchar(32));
> INSERT INTO new_test SELECT * FROM test;
>
> ALTER TABLE new_test ALTER COLUMN id SET DEFAULT
nextval('public.test_id_seq');
> DROP TABLE test;
>
> ALTER TABLE new_test RENAME TO test;
> ALTER TABLE new_test_id_key RENAME TO test_id_key;
>
> DROP SEQUENCE new_test_id_seq ;
> -- this give an error... is this normal
>
> is this the normal behaviour ?
>
> Ced.
> -- 
>
> Cedric BRINER
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>



---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Postfix/Maildrop and too many connections issues

[Sven Willenberger]

On Wed, 2005-06-22 at 01:30 +0200, Roman Neuhauser wrote:
> # [EMAIL PROTECTED] / 2005-06-21 15:00:12 -0400:
> > We have a system set up whereby postfix and maildrop gather user info
> > from a pg database (7.4.2 on FreeBSD 5.2.1) to do local mail acceptance
> > and delivery. I have configured max connections at 512 but I find that
> > this is not enough and I get "connection limit exceeded for
> > non-superusers" errors. I see upon ps ax that there are hundreds of idle
> > connections (state I).
> > 
> > Is there any way on the server end to close these connections (the
> > process is apparently for postfix and then maildrop to open a
> > connection, run one select statement, and supposedly close the
> > connection).
> 
> You are barking up the wrong software. See proxymap(8). (It's
> mentioned in http://www.postfix.org/PGSQL_README.html)
> 
Thanks for the tip ... edited my main.cf so that transport,aliases, etc
use proxymap so this should help somewhat. Apparently in version 2.2. it
is safe to use for UIDs/maildirs/paths etc so I may have to make the
upgrade leap. Now to see if maildrop (the local mailer) has a similar
feature.

Sven


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

[GENERAL] Problems upgrading to 7.4.8 from 7.2.4

[Sean Cardus]

Hi Everyone,

I've inherited an ancient Redhat 7.3 machine hosting the data for one of our
applications in a Postgres 7.2.4 database.

I must confess, I come from a MySQL/MS-SQL background so I'm a bit of a
Postgres newbie :)

I've setup a brand-new server running RHEL4 which is supplied with Postgres
7.4.8.  I've followed all the advice I can find with regard to dumping the
existing data and re-importing it to the new server.  All goes well except
for a few of the SQL statements during the import fail.

For example, one of the CREATE VIEW statements contains:
   WHERE (u.username = "varchar"(u.customer_id))

Another starts with:
CREATE VIEW "sub_domains_view" as
  SELECT (a.domain_name || evalstring(
(SELECT (' => '::"varchar" || da.domain_name) FROM account da WHERE

These result in the following type of error:

ERROR:  function varchar(integer) does not exist
HINT:  No function matches the given name and argument types. You may need
to add explicit type casts.

I've tried creating a cast using...

CREATE CAST (int4 AS varchar) WITH FUNCTION varchar(int4);

...but this fails - According to my Googling it appears the varchar()
function has been removed from Postgres sometime around version 7.3.

I've asked this question elsewhere and have been given a suggestion to
modify these statements to use a cast, eg: WHERE (u.username =
cast(u.customer_id as string))
Unfortunately I don't have access to the application source, so I'm not sure
if this type of change will have any impact on the application itself.

I was hoping I might be able to re-create the varchar() cast function(s) but
googling has only come up with examples of failed attempts.

Does anyone have any advice/ideas?

TIA,
Sean


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

Re: [GENERAL] Problems upgrading to 7.4.8 from 7.2.4

[Tom Lane]

"Sean Cardus" <[EMAIL PROTECTED]> writes:
> For example, one of the CREATE VIEW statements contains:
>WHERE (u.username = "varchar"(u.customer_id))

If you can change "varchar" to "text" it should still work.

regards, tom lane

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

Re: [GENERAL] 8.03 postgres install error

[Prasad Duggineni]

I have updated the software still it is occurring whenever I  reboot my 
computer. I don't have any references to old config file.Please advice me to 
fix this problem.

Here is the syslog.


Jun 22 10:16:30 lab5md9181 gpm: gpm startup succeeded
Jun 22 10:16:31 lab5md9181 postgres[887]: [2-1] 2005-06-22 10:16:31 EDT LOG: 
st

atement:
Jun 22 10:16:31 lab5md9181 postgres[887]: [3-1] 2005-06-22 10:16:31 EDT LOG: 
st

atement: set DateStyle to 'ISO'
Jun 22 10:16:32 lab5md9181 postgres[887]: [4-1] 2005-06-22 10:16:32 EDT LOG: 
st

atement:
Jun 22 10:16:32 lab5md9181 postgres[887]: [5-1] 2005-06-22 10:16:32 EDT LOG: 
st

atement: set geqo to 'OFF'
Jun 22 10:16:32 lab5md9181 postgres[887]: [6-1] 2005-06-22 10:16:32 EDT LOG: 
st

atement:
Jun 22 10:16:32 lab5md9181 postgres[887]: [7-1] 2005-06-22 10:16:32 EDT LOG: 
st

atement: set ksqo to 'ON'
Jun 22 10:16:32 lab5md9181 postgres[887]: [8-1] 2005-06-22 10:16:32 EDT 
ERROR:

unrecognized configuration parameter "ksqo"
Jun 22 10:16:32 lab5md9181 postgres[887]: [9-1] 2005-06-22 10:16:32 EDT LOG: 
st

atement: select oid from pg_type where typname='lo'
Jun 22 10:16:32 lab5md9181 postgres[887]: [10-1] 2005-06-22 10:16:32 EDT 
LOG:  s

tatement: select version()
Jun 22 10:16:32 lab5md9181 postgres[891]: [2-1] 2005-06-22 10:16:32 EDT LOG: 
st

atement:
Jun 22 10:16:32 lab5md9181 postgres[891]: [3-1] 2005-06-22 10:16:32 EDT LOG: 
st

atement: set DateStyle to 'ISO'
Jun 22 10:16:32 lab5md9181 postgres[891]: [4-1] 2005-06-22 10:16:32 EDT LOG: 
st

atement:
Jun 22 10:16:32 lab5md9181 postgres[891]: [5-1] 2005-06-22 10:16:32 EDT LOG: 
st

atement: set geqo to 'OFF'
Jun 22 10:16:32 lab5md9181 postgres[891]: [6-1] 2005-06-22 10:16:32 EDT LOG: 
st

atement:
Jun 22 10:16:32 lab5md9181 postgres[891]: [7-1] 2005-06-22 10:16:32 EDT LOG: 
st

atement: set ksqo to 'ON'
Jun 22 10:16:32 lab5md9181 postgres[891]: [8-1] 2005-06-22 10:16:32 EDT 
ERROR:

unrecognized configuration parameter "ksqo"
Jun 22 10:16:32 lab5md9181 postgres[891]: [9-1] 2005-06-22 10:16:32 EDT LOG: 
st

atement: select oid from pg_type where typname='lo'

- Original Message - 
From: "Tom Lane" <[EMAIL PROTECTED]>

To: "Prasad Duggineni" <[EMAIL PROTECTED]>
Cc: ; "Devrim GUNDUZ" <[EMAIL PROTECTED]>
Sent: Tuesday, June 21, 2005 10:23 AM
Subject: Re: [GENERAL] 8.03 postgres install error



"Prasad Duggineni" <[EMAIL PROTECTED]> writes:

Please advice me to fix this problem.



Jun 20 18:38:41 lab5md9181 postgres[2552]: [2-1] 2005-06-20 18:38:41 EDT
ERROR:
 unrecognized configuration parameter "ksqo"


KSQO has been obsolete for nigh five years now.  Update whatever
software you are using that expects it to do something.

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] 8.03 postgres install error

[Devrim GUNDUZ]

Hi,

On Wed, 22 Jun 2005, Prasad Duggineni wrote:

I have updated the software still it is occurring whenever I  reboot my 
computer. I don't have any references to old config file.Please advice me to 
fix this problem.


Hrrm... Did you use rpm -Uvh while upgrading? I hope you did not...

--
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com.tr  http://www.gunduz.org

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

Re: [GENERAL] 8.03 postgres install error

[Prasad Duggineni]

Yep I have used the rpms while upgrading the postgres 8.0.


- Original Message - 
From: "Devrim GUNDUZ" <[EMAIL PROTECTED]>

To: "Prasad Duggineni" <[EMAIL PROTECTED]>
Cc: "Tom Lane" <[EMAIL PROTECTED]>; ; "Devrim 
GUNDUZ" <[EMAIL PROTECTED]>

Sent: Wednesday, June 22, 2005 11:06 AM
Subject: Re: [GENERAL] 8.03 postgres install error




Hi,

On Wed, 22 Jun 2005, Prasad Duggineni wrote:

I have updated the software still it is occurring whenever I  reboot my 
computer. I don't have any references to old config file.Please advice me 
to fix this problem.


Hrrm... Did you use rpm -Uvh while upgrading? I hope you did not...

--
Devrim GUNDUZ
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com.tr  http://www.gunduz.org






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

[GENERAL] how to xml on debian?

[marcelo Cortez]

hi 

 any pointer's to install xml on debian be
appreciated.
best regards
 mdc
 



___ 
A tu celular ¿no le falta algo? 
Usá Yahoo! Messenger y Correo Yahoo! en tu teléfono celular. 
Más información en http://movil.yahoo.com.ar

---(end of broadcast)---
TIP 3: 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] Problem on function returning setof custom type

[Pablo Baena]

This happens when using left join on the select. 
This is a sample of what is happening to me.

-- The tables

CREATE TABLE test
(
  id numeric,
  blow varchar
) WITHOUT OIDS;

CREATE TABLE test1
(
  id numeric,
  bla2 varchar
) WITHOUT OIDS;

COPY test (id, blow) FROM stdin;1	sdasd2	sdaddxxsd4	s55ff\.

COPY test1 (id, bla2) FROM stdin;1	\.
--the custom type:

CREATE TABLE test1
(
  id numeric,
  bla2 varchar
) WITHOUT OIDS;


-- the function:

CREATE OR REPLACE FUNCTION vv(varchar)
  RETURNS SETOF custom AS
'


DECLARE
  rec custom%ROWTYPE;
   BEGIN
  FOR rec IN SELECT test.id, test.blow, test1.bla2
   FROM test
   LEFT JOIN test1 ON test.id = test1.id WHERE test1.bla2=\'$1\' LOOP
  RETURN NEXT rec;
  END LOOP;


  RETURN null;
   END;
 '
  LANGUAGE 'plpgsql' VOLATILE;
-- the results:
select * from vv ('');
-- returns empty set

-- while the actual sql query:
SELECT test.id, test.blow, test1.bla2
   FROM test
   LEFT JOIN test1 ON test.id = test1.id WHERE test1.bla2='';
-- returns
--id|blow|bla2

--1|sdasd|


Thanks for the help!
-- > There are a lot of us out there who both do and do not work for SunWow! Quantum programmers!

Re: [GENERAL] Problems upgrading to 7.4.8 from 7.2.4

[Sean Cardus]

Tom Lane <> wrote the following on 22 June 2005 15:20:

> "Sean Cardus" <[EMAIL PROTECTED]> writes:
>> For example, one of the CREATE VIEW statements contains:
>>WHERE (u.username = "varchar"(u.customer_id))
> 
> If you can change "varchar" to "text" it should still work.

Thanks for the advice Tom - I've tested this and the database does import
correctly.  I'm just concerned that the application might be expecting a
varchar column type in the returned data.

I've contacted the application developers and I'm still waiting for a
reply...

*crosses fingers*

Thanks again,
Sean


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

[GENERAL] setting up PostgreSQL on Linux RHL9 to allow ODBC connections from Windows

[SCassidy]

Hi,
I have seen a bunch of different documentation on how to set up to allow
ODBC, but I am a little confused about how much/what has to be set up to
allow an ODBC connection from Windows (mostly 2003, some XP) to an existing
PostgreSQL (7.4.6)  database on Linux (RedHat 9  version 2.4.20-6smp).

Most of the documentation is talking about setting up Unix to Unix odbc, or
Windows to Windows, not Windows to Unix/Linux.

Some documentation talks about installing the "full" distribution on
Windows, but I just want to allow some Windows software that "speaks" ODBC
(SilkTest) to be able to connect to an existing database.


In the document
http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-redhat,  it
says to download rpms and install them, but I don't really see any rpms for
this.  I see source distributions at
http://www.postgresql.org/ftp/odbc/versions/src/, but nothing for version
7.4, just 7.3 and 8.0.  I am reluctant to upgrade to 8.0, as I am not sure
it is stable yet, and my installation is running so well.


The howto-redhat document also talks about running:




   psql -d template1 -f /usr/share/pgsql/odbc.sql   





to modify template1, but what about existing databases?

I'm somewhat nervous about doing anything to potentially mess up anything
on my Linux server, as this is a production database.

Then, you have to set up the iodbc driver manager, etc.  On this server, I
have had some issues with using rpm's in the past, and have often had to
resort to building things myself (configure, make, etc.)

I'm also not sure about how to specify a DSN to do the connection from the
Windows side (I'm primarily a Unix/Linux person).

Does anyone have any specific advice/experiences to offer on setting this
up as simply as possible?

Thanks,
Susan




--
See our award-winning line of tape and disk-based
backup & recovery solutions at http://www.overlandstorage.com
--


---(end of broadcast)---
TIP 3: 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] Problem on function returning setof custom type

[Stephan Szabo]

On Wed, 22 Jun 2005, Pablo Baena wrote:

> FOR rec IN SELECT test.id , test.blow, test1.bla2
> FROM test
> LEFT JOIN test1 ON test.id  = test1.id WHERE
> test1.bla2=\'$1\' LOOP

This is going to compare to the exact string '$1' not to the value of the
argument since you've quoted it.  I think you'd just want test1.bla2 = $1
there.

---(end of broadcast)---
TIP 3: 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] Problem on function returning setof custom type

[Pablo Baena]

You are amazing! It solved it! Thanks!!On 6/22/05, Stephan Szabo <[EMAIL PROTECTED]> wrote:
On Wed, 22 Jun 2005, Pablo Baena wrote:> FOR rec IN SELECT 
test.id , test.blow, test1.bla2> FROM test> LEFT JOIN test1 ON test.id  = 
test1.id WHERE> test1.bla2=\'$1\' LOOPThis is going to compare to the exact string '$1' not to the value of theargument since you've quoted it.  I think you'd just want 
test1.bla2 = $1there.-- > There are a lot of us out there who both do and do not work for SunWow! Quantum programmers!

Re: [GENERAL] table and column information from cursor?

[Tom Lane]

John DeSoi <[EMAIL PROTECTED]> writes:
> I was wondering if there is some way I'm missing to get the table and 
> column information from a cursor. If I fetch from a cursor, the table 
> OID and column number values are 0 in the row description. If I execute 
> the same query directly without a cursor, the row description has the 
> correct values for table OID and column number. I'm using the v3 
> protocol via a socket with PostgreSQL 8.0.

Fixed in CVS tip.  Turned out that prepared statements had the same
issue, that is in

PREPARE foo AS SELECT ...;
EXECUTE foo;

the row description returned by EXECUTE wouldn't tell you where the
columns came from.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Clustering and replication options

[Ed L.]

On Wednesday June 22 2005 2:16 am, Gregory Youngblood wrote:
> I am looking for some information about clustering and
> replication options for postgresql.

Gregory,

FWIW, I've used Slony 1.0.5 for 10-15 db cluster migrations, 
usually from 7.3.4 clusters on one box to 7.4.6 clusters on 
another (slony is asyncronous master-slave).  That has typically 
involved running slony for anywhere from a few hours to a few 
days.  Where a dump and reload has required several hours of 
downtime, slony has allowed us to cutover in a matter of a 
minute or three.  We've also successfully used a perl version of 
dbmirror, also asyncronous master-slave.

I'd be very interested in hearing what syncronous multi-master 
replication solutions you find for pgsql.  I haven't kept up, 
but I'm not aware of any that are still alive.  The one using 
Spread and Ensemble ("Postgres R"?) appears dormant as of 7.2.

Ed
 


---(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] Perl DBI issue

[Peter Darley]

Folks,

I just installed a new machine with Fedora Core 4 with PostgreSQL 
8.0.3.  I
also installed DBI::Pg 1.41.  When I try to use a query with a placeholder
in it I get the error: "DBD::Pg::st execute failed: ERROR:  syntax error at
or near "$1" at character 1".  The same query with a value instead of a
placeholder works just fine.  The script follows this email.

I can use exactly the same script to connect to PostgreSQL on this 
machine
from a different machine, and I can not connect from this machine to a
PostgreSQL install of 7.4.1.  It looks to me like there is some problem
between DBI::Pg and the client library.  While poking around I saw that all
the other machines have a libpq.so, but this new machine doesn't (it has a
libpq.so.4 and libpq.so.4.0 tho).  I don't know if that is significant.

Any suggestions would be much appreciated.

Thanks,
Peter Darley


#!/usr/bin/perl

use DBI;
use strict;

my ($dbhCheck, $sthCheck, $Result);

$dbhCheck = DBI->connect("dbi:Pg:dbname=neo;host=production", 'user',
'password');
$sthCheck = $dbhCheck->prepare("SELECT COUNT(*) FROM Sample where
SurveyID=?");
$sthCheck->execute(316);
$Result = $sthCheck->fetchrow();
print "Sample has $Result members.\n";
$sthCheck->finish();
$dbhCheck->disconnect();


---(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] [HACKERS] [PATCHES] Removing Kerberos 4

[Greg Stark]

Tom Lane <[EMAIL PROTECTED]> writes:

> Last chance for any Kerberos 4 users to speak up --- otherwise I'll
> apply this soon.

If you just want someone to test it I can do that. I don't actually use it
normally though.

As far as security issues the only issues I'm aware of is a) it uses plain DES
which is just a 56 bit key and crackable by brute force and b) cross-domain
authentication is broken. 

But if you just have a single domain it's a lot simpler to set up than the
poster child for second system effect, Kerberos 5.

-- 
greg


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] dump/restore bytea fields

[David Parker]

In 7.4.5 I'm trying 
to dump and selectively load a database that contains bytea fields. I dumped it 
with the command:
 
pg_dump -p 24000 -Fc 
-f /home/dparker/temp/tazz.dmp tazz
 
When I try to 
restore it, after having generated/modified a list-file, I get the 
following error: 
 
% pg_restore -p 
24600 -a -d tazz -L ./tazz.toc /home/dparker/temp/tazz.dmppg_restore: 
ERROR:  out of memoryDETAIL:  Failed on request of size 
8388608.CONTEXT:  COPY library, line 1: 
"137   
DD93266ED40DC09A6B502DB31A254168    
1.0.0   
agents.so   
library  t   \\177ELF\\001\\001\\..."pg_restore: 
[archiver (db)] error returned by PQendcopy
What am I 
missing?
 
Thanks!
- 
DAP--David 
Parker    Tazz Networks    (401) 
709-5130 
 

Re: [GENERAL] [HACKERS] [PATCHES] Removing Kerberos 4

[Magnus Hagander]

> > Last chance for any Kerberos 4 users to speak up --- otherwise I'll 
> > apply this soon.
> 
> If you just want someone to test it I can do that. I don't 
> actually use it normally though.

I don't think "just testing" is enough - somebody needs to actually
maintain it... 


> As far as security issues the only issues I'm aware of is a) 
> it uses plain DES which is just a 56 bit key and crackable by 
> brute force and b) cross-domain authentication is broken. 

Yeah. But it has been declared dead by the Kerberos folks
(http://www.faqs.org/faqs/kerberos-faq/general/section-7.html. And this
document is from 2000, an dit was declared already then)...


//Magnus

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

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

[GENERAL] Vacuum advice

[David Mitchell]

Hi,

I have a system that has a moderate amount of activity on it, nothing 
strenuous. The activity is a real mixture of operations: selects, 
updates, inserts and deletes. One thing strange about our database is 
that we have a log of stored procedures that use temporary tables. Most 
of the time the system runs fine, and a nightly vacuum does the job. 
However, occasionally we need to import data, and this involves 
inserting several million rows into a table, but this just *cripples* 
postgres. After the import has been running for a while, simple selects 
take a long time, and strangely, the query planner starts using a 
sequential scan on a table with millions of items in it. Surely inserts 
shouldn't be doing this? Should I be running a vacuum during the import? 
After how many operations should I be calling vacuum? We tried using 
pg_autovacuum but it didn't seem to do a very good job since we would 
often find the query planner getting it wrong, and a vacuum analyze 
would fix it.


Part of the import is to update items in a table to keep track of where 
the import is up to. The update is just setting an integer column, so 
does this create a dead tuple or will postgres alter the column in-place?


Thanks for your help
--
David Mitchell
Software Engineer
Telogis

---(end of broadcast)---
TIP 3: 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] Setting global vars for use with triggers

[CSN]

I'd like to create a trigger which deletes associated
files whenever their corresponding row is deleted.
Problem is, I don't want to hard-code directory
locations in the trigger function. Is there a way to
set a DOCUMENT_ROOT-like variable in Postgresql which
triggers could access and use? I'm using PHP for the
trigger (and PG 8.x).

Thanks,
CSN

PS - Scott and I have been tossing around a few other
ideas here:
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10302693




 
Yahoo! Sports 
Rekindle the Rivalries. Sign up for Fantasy Football 
http://football.fantasysports.yahoo.com

---(end of broadcast)---
TIP 3: 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] [HACKERS] [PATCHES] Removing Kerberos 4

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> Yeah. But it has been declared dead by the Kerberos folks
> (http://www.faqs.org/faqs/kerberos-faq/general/section-7.html. And this
> document is from 2000, an dit was declared already then)...

Right.  The real question here is who's going to be using a 2005
database release with a pre-2000 security system?  There's a fair
amount of code there and no evidence that time spent on testing
and maintaining it is going to benefit anyone anymore.

If someone wakes up and says "hey, I'm still ACTUALLY using that code",
I'm willing to forbear ... but otherwise I think its time is long gone.

regards, tom lane

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

Re: [GENERAL] dump/restore bytea fields

[David Parker]

Never mind. Appears to have been local to a particular 
machine. I was able to pg_restore the same dump file on another box (which I 
should have tried before posting!) Sorry for the noise.
 
- DAP

  
  
  From: [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED] On Behalf Of David 
  ParkerSent: Wednesday, June 22, 2005 4:05 PMTo: postgres 
  generalSubject: [GENERAL] dump/restore bytea 
  fields
  
  In 7.4.5 I'm 
  trying to dump and selectively load a database that contains bytea fields. I 
  dumped it with the command:
   
  pg_dump -p 24000 
  -Fc -f /home/dparker/temp/tazz.dmp tazz
   
  When I try to 
  restore it, after having generated/modified a list-file, I get the 
  following error: 
   
  % pg_restore -p 
  24600 -a -d tazz -L ./tazz.toc /home/dparker/temp/tazz.dmppg_restore: 
  ERROR:  out of memoryDETAIL:  Failed on request of size 
  8388608.CONTEXT:  COPY library, line 1: 
  "137   
  DD93266ED40DC09A6B502DB31A254168    
  1.0.0   
  agents.so   
  library  t   \\177ELF\\001\\001\\..."pg_restore: 
  [archiver (db)] error returned by PQendcopy
  What am I 
  missing?
   
  Thanks!
  - 
  DAP--David 
  Parker    Tazz Networks    (401) 
  709-5130 
   

Re: [GENERAL] Vacuum advice

[Tom Lane]

David Mitchell <[EMAIL PROTECTED]> writes:
> However, occasionally we need to import data, and this involves 
> inserting several million rows into a table, but this just *cripples* 
> postgres. After the import has been running for a while, simple selects 
> take a long time, and strangely, the query planner starts using a 
> sequential scan on a table with millions of items in it. Surely inserts 
> shouldn't be doing this? Should I be running a vacuum during the import? 

PG 8.0 and up should handle this a bit more nicely.  Before 8.0 the
planner looked only at the table size recorded in pg_catalog (the
relpages and reltuples fields) and so of course it wouldn't react to a
still-in-progress mass insert.  Now it looks at the current actual
physical table size, and so it should be willing to switch to indexscan
if needed before the import completes.

If you *are* using 8.0 then we need to look closer.

> After how many operations should I be calling vacuum? We tried using 
> pg_autovacuum but it didn't seem to do a very good job since we would 
> often find the query planner getting it wrong, and a vacuum analyze 
> would fix it.

autovacuum has a problem with responding to transactions that are
still-in-progress too :-(

> Part of the import is to update items in a table to keep track of where 
> the import is up to. The update is just setting an integer column, so 
> does this create a dead tuple or will postgres alter the column in-place?

Postgres doesn't *ever* update anything in-place.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Setting global vars for use with triggers

[Douglas McNaught]

CSN <[EMAIL PROTECTED]> writes:

> I'd like to create a trigger which deletes associated
> files whenever their corresponding row is deleted.
> Problem is, I don't want to hard-code directory
> locations in the trigger function. Is there a way to
> set a DOCUMENT_ROOT-like variable in Postgresql which
> triggers could access and use? I'm using PHP for the
> trigger (and PG 8.x).

It's probably best to keep it in a one-row table--that way it'll be
saved when you back up the database, and you can change it with
standard SQL.

-Doug

---(end of broadcast)---
TIP 3: 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] PROBLEM: Function does not exist

[Julian Legeny]

Hello,

   I'm using postgres 8.0.3 under win xp. I have problem to call psql function.

   I have tables:
   1. BF_DOMAIN
   2. BF_USER
   3. BF_FILTER (this table uses FK from the previous two tables)

   and  function  INSERT_BF_FILTER that inserts data to the table BF_FILTER. But
when  I  call  this  function,  it  is  not  found  because incorrect parameters
"unknown" instead of varchar.

  Definition of tables and function:

CREATE TABLE bf_domain
(
  id serial NOT NULL,
  name varchar(100) NOT NULL,
  description varchar(1024),
  creation_date timestamp NOT NULL,
  modification_date timestamp NOT NULL,
  CONSTRAINT bf_dom_pk PRIMARY KEY (id),
  CONSTRAINT bf_dom_uq UNIQUE (name)
) 
WITH OIDS;



CREATE TABLE bf_user
(
  id serial NOT NULL,
  domain_id int4 NOT NULL,
  first_name varchar(50) NOT NULL,
  last_name varchar(50) NOT NULL,
  phone varchar(20) NOT NULL,
  fax varchar(20) NOT NULL,
  address varchar(1024) NOT NULL,
  email varchar(50) NOT NULL,
  login_name varchar(30) NOT NULL,
  "password" varchar(50) NOT NULL,
  login_enabled int2 NOT NULL DEFAULT 0,
  guest_access_enabled int2 NOT NULL DEFAULT 0,
  super_user int2 NOT NULL DEFAULT 0,
  internal_user int2 NOT NULL DEFAULT 0,
  creation_date timestamp NOT NULL,
  modification_date timestamp NOT NULL,
  CONSTRAINT bf_usr_pk PRIMARY KEY (id),
  CONSTRAINT bf_usr_fk FOREIGN KEY (domain_id) REFERENCES bastatest.bf_domain 
(id) ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT bf_usr_email_uq UNIQUE (email),
  CONSTRAINT bf_usr_login_uq UNIQUE (login_name)
) 
WITH OIDS;



CREATE TABLE bf_filter
(
  id serial NOT NULL,
  domain_id int4 NOT NULL,
  user_id int4 NOT NULL,
  name varchar(50) NOT NULL,
  description varchar(1024),
  data_type int4 NOT NULL,
  shared int2 NOT NULL DEFAULT 0,
  match_all int2 NOT NULL DEFAULT 0,
  page_size int4 NOT NULL DEFAULT 10,
  order_desc int2 NOT NULL DEFAULT 0,
  order_column int4,
  creation_date timestamp NOT NULL,
  modification_date timestamp NOT NULL,
  CONSTRAINT bf_fltr_pk PRIMARY KEY (id),
  CONSTRAINT bf_fltr_dom_fk FOREIGN KEY (domain_id) REFERENCES 
bastatest.bf_domain (id) ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT bf_fltr_usr_fk FOREIGN KEY (user_id) REFERENCES bastatest.bf_user 
(id) ON UPDATE NO ACTION ON DELETE CASCADE
) 
WITH OIDS;



CREATE TYPE type_int_timestamp AS
   (intgr int4,
tmstp timestamp);



CREATE OR REPLACE FUNCTION insert_bf_filter(int4, int4, "varchar", "varchar", 
int4, int2, int2, int4, int2, int4)
  RETURNS type_int_timestamp AS
$BODY$ 
DECLARE 
   IN_DOMAIN_ID ALIAS FOR $1; 
   IN_USER_ID ALIAS FOR $2; 
   IN_NAME ALIAS FOR $3; 
   IN_DESCRIPTION ALIAS FOR $4; 
   IN_DATA_TYPE ALIAS FOR $5; 
   IN_SHARED ALIAS FOR $6; 
   IN_MATCH_ALL ALIAS FOR $7; 
   IN_PAGE_SIZE ALIAS FOR $8; 
   IN_ORDER_DESC ALIAS FOR $9; 
   IN_ORDER_COLUMN ALIAS FOR $10; 
   out_key INTEGER; 
   out_timestamp TIMESTAMP; 
   output_result type_int_timestamp; 
BEGIN 
   SELECT INTO out_timestamp now(); 
   INSERT INTO BF_FILTER(DOMAIN_ID, USER_ID, NAME,
  DESCRIPTION, DATA_TYPE, SHARED, MATCH_ALL, PAGE_SIZE, 
  ORDER_DESC, ORDER_COLUMN, CREATION_DATE, MODIFICATION_DATE)
   VALUES (IN_DOMAIN_ID, IN_USER_ID, IN_NAME, IN_DESCRIPTION, IN_DATA_TYPE,
   IN_SHARED, IN_MATCH_ALL, IN_PAGE_SIZE, IN_ORDER_DESC, 
   IN_ORDER_COLUMN, out_timestamp, out_timestamp); 
   out_key := CURRVAL('bf_filter_id_seq'); 
   output_result.intgr := out_key; 
   output_result.tmstp := out_timestamp; 
   RETURN output_result; 
END 
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;




I call following SQL command from pgAdmin:
select INTGR, TMSTP from INSERT_BF_FILTER (53, 1354, 'test_filter_name', 
'test_filter_description', 1, 0, 0, 10, 0, 100)

And it returns me following error:
ERROR:  function insert_bf_filter(integer, integer, "unknown", "unknown", 
integer, integer, integer, integer, integer, integer) does not exist
HINT:  No function matches the given name and argument types. You may need to 
add explicit type casts.


Can you advice me what can be a problem?

-- 

Thank you for your answer,
best regards,

Julian

mailto:[EMAIL PROTECTED]


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Vacuum advice

[David Mitchell]

Thanks Tom,


If you *are* using 8.0 then we need to look closer.



Sorry, I should have mentioned, I am using PG 8.0. Also, although this 
is a 'mass insert', it's only kind of mass. While there are millions of 
rows, they are inserted in blocks of 500 (with a commit in between).


We're thinking we might set up vacuum_cost_limit to around 100 and put 
vacuum_cost_delay at 100 and then just run vacuumdb in a cron job every 
15 minutes or so, does this sound silly?



--
David Mitchell
Software Engineer
Telogis


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

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

[GENERAL] multiple action rules

[Scott Marlowe]

OK, so I'm trying to make a rule with >1 action.

The docs, not so helpfully, only seem to show a single action rule (at
least in the rules section, not sure about elsewhere)  The syntax
diagram:

CREATE RULE rule_name AS ON event
TO object [WHERE rule_qualification]
DO [INSTEAD] [action | (actions) | NOTHING];

seems to imply to me that >2 actions would look like this:

create rule audit_test as on delete to ntest do (
insert into naudit(id,path) values (OLD.id, OLD.path);
notify test;
)

But no matter what flavor of syntax I throw at this, I get: 

syntax error at or near "insert" at character xxx

So, what's the magic pixie dust I need to sprinkle on this to make it
work?

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

Re: [GENERAL] multiple action rules

[Scott Marlowe]

On Wed, 2005-06-22 at 18:31, Scott Marlowe wrote:
> OK, so I'm trying to make a rule with >1 action.
> 
> The docs, not so helpfully, only seem to show a single action rule (at
> least in the rules section, not sure about elsewhere)  The syntax
> diagram:
> 
> CREATE RULE rule_name AS ON event
> TO object [WHERE rule_qualification]
> DO [INSTEAD] [action | (actions) | NOTHING];
> 
> seems to imply to me that >2 actions would look like this:
> 
> create rule audit_test as on delete to ntest do (
> insert into naudit(id,path) values (OLD.id, OLD.path);
> notify test;
> )
> 
> But no matter what flavor of syntax I throw at this, I get: 
> 
> syntax error at or near "insert" at character xxx
> 
> So, what's the magic pixie dust I need to sprinkle on this to make it
> work?


Never mind, I finally figured it out...  About three minutes after
posting this.  sheesh.

---(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] Setting global vars for use with triggers

[Michael Fuhr]

On Wed, Jun 22, 2005 at 01:36:20PM -0700, CSN wrote:
>
> I'd like to create a trigger which deletes associated
> files whenever their corresponding row is deleted.

I looked at the thread you posted and saw that you were considering
using LISTEN/NOTIFY.  That's probably a good idea because if you
delete a file in a trigger, you don't have a way to undelete it if
the transaction rolls back.  If you use NOTIFY then the notification
should be delivered to the listener only if the transaction commits.

Ideally the listener wouldn't repeatedly sleep and check for
notifications -- you'd use a function like select() or poll() in C
(socket_select() in PHP) that would tell you immediately when data
is available on the database connection's socket (and only then).
Unfortunately I don't see a way for the PHP PostgreSQL interface
to get the connection's file descriptor as you would in C with
PQsocket().  If you use PHP then you might be stuck with sleep/check,
so you might want to consider writing the listener in another
language.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

   http://archives.postgresql.org

Re: [GENERAL] Vacuum advice

[Tom Lane]

David Mitchell <[EMAIL PROTECTED]> writes:
>> If you *are* using 8.0 then we need to look closer.

> Sorry, I should have mentioned, I am using PG 8.0. Also, although this 
> is a 'mass insert', it's only kind of mass. While there are millions of 
> rows, they are inserted in blocks of 500 (with a commit in between).

> We're thinking we might set up vacuum_cost_limit to around 100 and put 
> vacuum_cost_delay at 100 and then just run vacuumdb in a cron job every 
> 15 minutes or so, does this sound silly?

It doesn't sound completely silly, but if you are doing inserts and not
updates/deletes then there's not anything for VACUUM to do, really.
An ANALYZE command might get the same result with less effort.

I am however still wondering why 8.0 doesn't get it right without help.
Can you try a few EXPLAIN ANALYZEs as the table grows and watch whether
the cost estimates change?

(Also, if this is actually 8.0.0 and not a more recent dot-release,
I believe there were some bug fixes in this vicinity in 8.0.2.)

regards, tom lane

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

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

Re: [GENERAL] PROBLEM: Function does not exist

[Michael Fuhr]

On Thu, Jun 23, 2005 at 12:36:50AM +0200, Julian Legeny wrote:
>
> CREATE OR REPLACE FUNCTION insert_bf_filter(int4, int4, "varchar", "varchar", 
> int4, int2, int2, int4, int2, int4)

[-snip-]

> select INTGR, TMSTP from INSERT_BF_FILTER (53, 1354, 'test_filter_name', 
> 'test_filter_description', 1, 0, 0, 10, 0, 100)
> 
> And it returns me following error:
> ERROR:  function insert_bf_filter(integer, integer, "unknown", "unknown", 
> integer, integer, integer, integer, integer, integer) does not exist
> HINT:  No function matches the given name and argument types. You may need to 
> add explicit type casts.

Follow the hint: use explicit type casts for the int2 arguments.  Try this:

select INTGR, TMSTP
from INSERT_BF_FILTER(53, 1354, 'test_filter_name', 'test_filter_description',
  1, 0::int2, 0::int2, 10, 0::int2, 100);

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] multiple action rules

[Tom Lane]

Scott Marlowe <[EMAIL PROTECTED]> writes:
>> So, what's the magic pixie dust I need to sprinkle on this to make it
>> work?

> Never mind, I finally figured it out...  About three minutes after
> posting this.  sheesh.

If you think the docs are insufficiently clear, feel free to send in a
documentation patch.  Sometimes what seems obvious to the writer is less
so to readers ...

regards, tom lane

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

Re: [GENERAL] multiple action rules

[Scott Marlowe]

On Wed, 2005-06-22 at 19:49, Tom Lane wrote:
> Scott Marlowe <[EMAIL PROTECTED]> writes:
> >> So, what's the magic pixie dust I need to sprinkle on this to make it
> >> work?
> 
> > Never mind, I finally figured it out...  About three minutes after
> > posting this.  sheesh.
> 
> If you think the docs are insufficiently clear, feel free to send in a
> documentation patch.  Sometimes what seems obvious to the writer is less
> so to readers ...

Well, what really gets me is that basically, the syntax diagram in 
the psql environment has a syntax diagram that makes sense to me.

So I guess if there was a "patch" it would basically reference the syntax
rules given by psql \h create rule in the documentation.

But that makes me think that the syntax diagrams should probably be 
abstracted out of the pure sgml realm, and grabbed by both psql and the
docs during build time.

OK, took a break and looked through the developer docs, and, yet again,
someone has done the exact thing I would have done, they've put the same
basic syntax diagram as "\h create rule" in the docs...

So, thanks again...

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] PROBLEM: Function does not exist

[Julian Legeny]

Hello Michael,

   yes, you have right. Solution is cast it to smallint.

   Thank you for your advice,
   with best regards,

   Julian Legeny

Thursday, June 23, 2005, 2:48:17 AM, you wrote:

MF> On Thu, Jun 23, 2005 at 12:36:50AM +0200, Julian Legeny wrote:
>>
>> CREATE OR REPLACE FUNCTION insert_bf_filter(int4, int4, "varchar",
>> "varchar", int4, int2, int2, int4, int2, int4)

MF> [-snip-]

>> select INTGR, TMSTP from INSERT_BF_FILTER (53, 1354, 'test_filter_name',
>> 'test_filter_description', 1, 0, 0, 10, 0, 100)
>> 
>> And it returns me following error:
>> ERROR:  function insert_bf_filter(integer, integer, "unknown", "unknown",
>> integer, integer, integer, integer, integer, integer) does not exist
>> HINT:  No function matches the given name and argument types. You may need 
>> to add explicit type casts.

MF> Follow the hint: use explicit type casts for the int2 arguments.  Try this:

MF> select INTGR, TMSTP
MF> from INSERT_BF_FILTER(53, 1354, 'test_filter_name', 
'test_filter_description',
MF>   1, 0::int2, 0::int2, 10, 0::int2, 100);



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

   http://archives.postgresql.org

Re: [GENERAL] multiple action rules

[Tom Lane]

Scott Marlowe <[EMAIL PROTECTED]> writes:
> Well, what really gets me is that basically, the syntax diagram in 
> the psql environment has a syntax diagram that makes sense to me.

> So I guess if there was a "patch" it would basically reference the syntax
> rules given by psql \h create rule in the documentation.

Hmm?  psql's \h output is mechanically extracted from the sgml docs.
Or are you looking at someplace other than the CREATE RULE reference
page?

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] PROBLEM: Function does not exist

[Tom Lane]

Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Thu, Jun 23, 2005 at 12:36:50AM +0200, Julian Legeny wrote:
>> CREATE OR REPLACE FUNCTION insert_bf_filter(int4, int4, "varchar", 
>> "varchar", int4, int2, int2, int4, int2, int4)

> Follow the hint: use explicit type casts for the int2 arguments.

Or just declare the function arguments as int4.  I don't think there's
any material performance advantage from using int2 here anyway.

regards, tom lane

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

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

Re: [GENERAL] Vacuum advice

[David Mitchell]

We're thinking we might set up vacuum_cost_limit to around 100 and put 
vacuum_cost_delay at 100 and then just run vacuumdb in a cron job every 
15 minutes or so, does this sound silly?



It doesn't sound completely silly, but if you are doing inserts and not
updates/deletes then there's not anything for VACUUM to do, really.
An ANALYZE command might get the same result with less effort.


I think that perhaps the fact we are doing updates in the secondary 
table to track the import is the culprit here. It gets updated for each 
item inserted into the main table, so even though it has 500 rows, it 
ended up with about 2million dead tuples, which left a lot to be desired 
in terms of seq scan speed. Vacuum full cleared this up, so I assume a 
frequent regular vacuum would keep it in tip top condition.


We are using PG 8.0.1.

Thanks for your help Tom.

--
David Mitchell
Software Engineer
Telogis

---(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] Vacuum advice

[Karl O. Pinc]

On 06/22/2005 04:39:00 PM, Tom Lane wrote:

David Mitchell <[EMAIL PROTECTED]> writes:
> However, occasionally we need to import data, and this involves
> inserting several million rows into a table, but this just
*cripples*
> postgres. After the import has been running for a while, simple
selects
> take a long time, and strangely, the query planner starts using a
> sequential scan on a table with millions of items in it. Surely
inserts
> shouldn't be doing this? Should I be running a vacuum during the
import?

PG 8.0 and up should handle this a bit more nicely.  Before 8.0 the
planner looked only at the table size recorded in pg_catalog (the
relpages and reltuples fields) and so of course it wouldn't react to a
still-in-progress mass insert.  Now it looks at the current actual
physical table size, and so it should be willing to switch to
indexscan
if needed before the import completes.


I've made a habit of disconnecting, analyzing and re-connecting
every so often on mass data loading because any stored
procedures I'm using for the load, including triggers, will not have
their query plans updated to account for the new data.
It's a pain, so I figured I'd ask whether I'm missing
something and this is somehow unnecessary.


Karl <[EMAIL PROTECTED]>
Free Software:  "You don't pay back, you pay forward."
 -- Robert A. Heinlein


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

Re: [GENERAL] setting up PostgreSQL on Linux RHL9 to allow ODBC connections

[William Yu]

There's nothing on the server side that needs to be configured for 
Windows clients to access Postgres via ODBC. As long as TCP/IP is turned 
on. Just add a data source and configure the server 
ip/name/port/database/etc.





[EMAIL PROTECTED] wrote:

Hi,
I have seen a bunch of different documentation on how to set up to allow
ODBC, but I am a little confused about how much/what has to be set up to
allow an ODBC connection from Windows (mostly 2003, some XP) to an existing
PostgreSQL (7.4.6)  database on Linux (RedHat 9  version 2.4.20-6smp).

Most of the documentation is talking about setting up Unix to Unix odbc, or
Windows to Windows, not Windows to Unix/Linux.

Some documentation talks about installing the "full" distribution on
Windows, but I just want to allow some Windows software that "speaks" ODBC
(SilkTest) to be able to connect to an existing database.


In the document
http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-redhat,  it
says to download rpms and install them, but I don't really see any rpms for
this.  I see source distributions at
http://www.postgresql.org/ftp/odbc/versions/src/, but nothing for version
7.4, just 7.3 and 8.0.  I am reluctant to upgrade to 8.0, as I am not sure
it is stable yet, and my installation is running so well.


The howto-redhat document also talks about running:




   psql -d template1 -f /usr/share/pgsql/odbc.sql   






to modify template1, but what about existing databases?

I'm somewhat nervous about doing anything to potentially mess up anything
on my Linux server, as this is a production database.

Then, you have to set up the iodbc driver manager, etc.  On this server, I
have had some issues with using rpm's in the past, and have often had to
resort to building things myself (configure, make, etc.)

I'm also not sure about how to specify a DSN to do the connection from the
Windows side (I'm primarily a Unix/Linux person).

Does anyone have any specific advice/experiences to offer on setting this
up as simply as possible?

Thanks,
Susan




--
See our award-winning line of tape and disk-based
backup & recovery solutions at http://www.overlandstorage.com
--


---(end of broadcast)---
TIP 3: 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



---(end of broadcast)---
TIP 3: 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] Corrupted index

[Akash Garg]

During a vacuum, I ran into this error:
vacuumdb: vacuuming of database "friend" failed: ERROR:  invalid page
header in block 41661 of relation "friend_pkey"

I've read the posts on this newsgroup and it's clear that I have to
REINDEX to fix this.  The bigger question is -- why did this happen in
the first place?  I'm using open_sync as my WAL_SYNC_METHOD.  Is it
better to use fsync?

Running Suse 9.0 on a Quad Opteron, with direct attached storage.  The
RAID card is set to write through all of the writes, so I don't think
this is a case where the RAID card is lying about a commit.

-Akash

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])