Re: [SQL] Deploying PostgreSQL on virtualized hardware
I've been running PostgreSQL on a virtual server for several years now. I'm using VMWare with a Windows host and Linux guest. I've configured it to let Linux use a raw partition as a disk. Before I used a separate partition, the virtual disk had been a Windows file. Using the disk partition increased performance, but I don't recall now exactly how much. This is a small setup with a handful of users. The database contains only dozens of tables, but some have ~8 million rows. I've allocated 1Mb of RAM to the virtual machine. Hope this helps. TJ http://www.gnova.com In a couple months, I'm going to be considering how best to deploy an application I have that uses PostgreSQL as its back-end. It also makes heavy use of Perl under mod_perl and UMN MapServer with a sprinkling of PostGIS. ---(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
[SQL] duplicate key violates unique constraint
Hello During an "INSERT INTO" I get an "Error - duplicate key violates unique constraint" Is there any way, that i can test the error. Something like this?? IF error = "duplicate key violates unique constraint" then do something else insert into end if Thanks Shavonne
Re: [SQL] duplicate key violates unique constraint
On Tuesday 26 February 2008, Shavonne Marietta Wijesinghe wrote: > During an "INSERT INTO" I get an "Error - duplicate key violates > unique constraint" > > Is there any way, that i can test the error. Something like this?? > > IF error = "duplicate key violates unique constraint" then > do something > else > insert into > end if insert into ...; exception when unique_violation then do something; Teemu ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] duplicate key violates unique constraint
Thank you. I tried as you said. But i get a ERROR: syntax error at or near
"INSERT" at character 9
BEGIN
INSERT INTO my_shevi values ('a', 4, 2, 2);
EXCEPTION
WHEN unique_violation THEN
INSERT INTO my_shevi values ('a', 5, 2, 2);
END;
I don't see anything wrong with the code..
Shavonne
- Original Message -
From: "Teemu Torma" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, February 26, 2008 4:17 PM
Subject: Re: [SQL] duplicate key violates unique constraint
On Tuesday 26 February 2008, Shavonne Marietta Wijesinghe wrote:
During an "INSERT INTO" I get an "Error - duplicate key violates
unique constraint"
Is there any way, that i can test the error. Something like this??
IF error = "duplicate key violates unique constraint" then
  do something
else
  insert into
end if
insert into ...;
exception when unique_violation then
do something;
Teemu
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] duplicate key violates unique constraint
On Tue, Feb 26, 2008 at 05:05:01PM +0100, Shavonne Marietta Wijesinghe wrote: > Thank you. I tried as you said. But i get a ERROR: syntax error at or near > "INSERT" at character 9 > I don't see anything wrong with the code.. Well, except that there's no "EXCEPTION" statement in SQL? I think your correspondent was intending for this to be programmatic. You can do what you want with a subtransaction, though. Look into savepoints in the manual. A ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] duplicate key violates unique constraint
On Tuesday 26 February 2008, Andrew Sullivan wrote: > Well, except that there's no "EXCEPTION" statement in SQL? I think > your correspondent was intending for this to be programmatic. I was thinking pl/pgsql. Teemu ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Function returns error (view)
Hi
Don't know why I can't receive a return like my view fields (I'm newbie in
plpgsql). Postgresql returns me a erro . How can I received a answer like my
view structure?
When I Test my view I receive
SELECT seach_password('user_login_foo')
My view returns me
25746;"MARCELO ";"bio1";"bio1";"2008-02-19";"FALSE";"2008-02-12";"2008-02-12
12:51:40.229282";"TRUE"
30356;"JOSE DE JESUS
";"977";"377";"2008-02-19";"FALSE";"2008-02-12";"2008-02-12 12:52:19.688381
";"TRUE"
It's OK
but when I use function it returns me
ERROR: column "user_login_foo" does not exist
SQL state: 42703
Context: PL/pgSQL function "search_password" line 14 at for over execute
statement
Where is my fault?
Thanks
Flávio
*
vw_change_password attributes
cod_user integer,
user_name varchar(150),
openpsw varchar (32),
user_password varchar (50),
end timestamp,
validate boolean,
date_add timestamp,
user_time timestamp,
ok boolean
CREATE OR REPLACE FUNCTION seach_password(USER_FOO varchar(100))
RETURNS SETOF vw_change_password AS
$BODY$
DECLARE
r vw_change_password%ROWTYPE;
USER_FOO alias for $1;
sql TEXT;
BEGIN
sql= 'SELECT u.cod_user, u.user_name, u.openpsw, t.user_password, t.end,
t.validate, t.date_add, t.user_time, u.ok
FROM usuario u, change_user_password t
WHERE u.cod_user = t.cod_user AND t.cod_user
IN
(SELECT cod_user
FROM table_user
WHERE login='||USER_FOO||')';
FOR r IN EXECUTE sql
LOOP
RETURN NEXT r;
END LOOP;
IF NOT FOUND THEN
RAISE EXCEPTION 'USER not found', USER_FOO;
END IF;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Re: [SQL] duplicate key violates unique constraint
Shavonne,
You will probably always find someone on the list who will answer your
questions, but you really should read the manual too!
In this case you could have found the answer by reading
http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
>>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]>
2008-02-26 17:05 >>>
Thank you. I tried as you said. But i get a ERROR: syntax error at or
near
"INSERT" at character 9
BEGIN
INSERT INTO my_shevi values ('a', 4, 2, 2);
EXCEPTION
WHEN unique_violation THEN
INSERT INTO my_shevi values ('a', 5, 2, 2);
END;
I don't see anything wrong with the code..
Shavonne
- Original Message -
From: "Teemu Torma" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, February 26, 2008 4:17 PM
Subject: Re: [SQL] duplicate key violates unique constraint
> On Tuesday 26 February 2008, Shavonne Marietta Wijesinghe wrote:
>> During an "INSERT INTO" I get an "Error - duplicate key violates
>> unique constraint"
>>
>> Is there any way, that i can test the error. Something like this??
>>
>> IF error = "duplicate key violates unique constraint" then
>> Â Â do something
>> else
>> Â Â insert into
>> end if
>
> insert into ...;
> exception when unique_violation then
> do something;
>
> Teemu
>
> ---(end of
broadcast)---
> TIP 5: don't forget to increase your free space map settings
---(end of
broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] duplicate key violates unique constraint
I wrote the query in the SQL windows provided by the pgadmin. According to the examples in "Chapter 35. PL/pgSQL - SQL Procedural Language" i don't see what i did different. BEGIN SAVEPOINT s1; ... code here ... EXCEPTION WHEN ... THEN ROLLBACK TO s1; ... code here ... WHEN ... THEN ROLLBACK TO s1; ... code here ... END; Maybe missing tabs before the statement?? Shavonne - Original Message - From: "Teemu Torma" <[EMAIL PROTECTED]> To: Sent: Tuesday, February 26, 2008 5:28 PM Subject: Re: [SQL] duplicate key violates unique constraint On Tuesday 26 February 2008, Andrew Sullivan wrote: Well, except that there's no "EXCEPTION" statement in SQL? Â I think your correspondent was intending for this to be programmatic. I was thinking pl/pgsql. Teemu ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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: [SQL] Function returns error (view)
Hi Colin
When I translated from Portuguese to English I forgot a letter, but using
the corrected name I received an error.
2008/2/26, Colin Wetherbee <[EMAIL PROTECTED]>:
>
> Professor Flávio Brito wrote:
> > When I Test my view I receive
> >
> > SELECT seach_password('user_login_foo')
>
> [...]
>
> > ERROR: column "user_login_foo" does not exist
> > SQL state: 42703
> > Context: PL/pgSQL function "search_password" line 14 at for over execute
> > statement
>
>
> seach_password and
> search_password are different.
>
> Perhaps you have two functions with similar names, and one is broken?
>
>
> Colin
>
Re: [SQL] Function returns error (view)
I think you have a quoting problem
You want something like
WHERE login= 'Flavo'
But you're making something like
WHERE login = Flavo
Something like this should work...
CREATE OR REPLACE FUNCTION seach_password(USER_FOO IN
table_user.login%TYPE)
RETURNS SETOF vw_change_password AS
$BODY$
DECLARE
r vw_change_password%ROWTYPE;
USER_FOO alias for $1;
BEGIN
FOR r IN (
SELECT u.cod_user, u.user_name, u.openpsw, t.user_password, t.end,
t.validate, t.date_add, t.user_time, u.ok
FROM usuario u, change_user_password t
WHERE u.cod_user = t.cod_user AND t.cod_user IN (SELECT cod_user
FROM table_user WHERE login= USER_FOO))
LOOP
RETURN NEXT r;
END LOOP;
IF NOT FOUND THEN
RAISE EXCEPTION 'USER not found (%)', USER_FOO;
END IF;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
>>> "Professor Flávio Brito" <[EMAIL PROTECTED]> 2008-02-26
17:32 >>>
Hi
Don't know why I can't receive a return like my view fields (I'm newbie
in plpgsql). Postgresql returns me a erro . How can I received a answer
like my view structure?
When I Test my view I receive
SELECT seach_password('user_login_foo')
My view returns me
25746;"MARCELO
";"bio1";"bio1";"2008-02-19";"FALSE";"2008-02-12";"2008-02-12
12:51:40.229282";"TRUE"
30356;"JOSE DE JESUS
";"977";"377";"2008-02-19";"FALSE";"2008-02-12";"2008-02-12
12:52:19.688381";"TRUE"
It's OK
but when I use function it returns me
ERROR: column "user_login_foo" does not exist
SQL state: 42703
Context: PL/pgSQL function "search_password" line 14 at for over
execute statement
Where is my fault?
Thanks
Flávio
*
vw_change_password attributes
cod_user integer,
user_name varchar(150),
openpsw varchar (32),
user_password varchar (50),
end timestamp,
validate boolean,
date_add timestamp,
user_time timestamp,
ok boolean
CREATE OR REPLACE FUNCTION seach_password(USER_FOO varchar(100))
RETURNS SETOF vw_change_password AS
$BODY$
DECLARE
r vw_change_password%ROWTYPE;
USER_FOO alias for $1;
sql TEXT;
BEGIN
sql= 'SELECT u.cod_user, u.user_name, u.openpsw, t.user_password,
t.end, t.validate, t.date_add, t.user_time, u.ok
FROM usuario u, change_user_password t
WHERE u.cod_user = t.cod_user AND t.cod_user
IN
(SELECT cod_user
FROM table_user
WHERE login='||USER_FOO||')';
FOR r IN EXECUTE sql
LOOP
RETURN NEXT r;
END LOOP;
IF NOT FOUND THEN
RAISE EXCEPTION 'USER not found', USER_FOO;
END IF;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Re: [SQL] duplicate key violates unique constraint
Shavonne Marietta Wijesinghe wrote: > I wrote the query in the SQL windows provided by the pgadmin. That SQL window only executes SQL, so you can't use PL/pgSQL commands on it. PL/pgSQL can only be used in the context of a PL/pgSQL function, so you need to do CREATE FUNCTION to do that. If you want to use the SQL window you could use SAVEPOINT and ROLLBACK TO. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] duplicate key violates unique constraint
On Tue, Feb 26, 2008 at 11:36 AM, Shavonne Marietta Wijesinghe <[EMAIL PROTECTED]> wrote: > BEGIN > SAVEPOINT s1; > ... code here ... > EXCEPTION > WHEN ... THEN > ROLLBACK TO s1; > ... code here ... > WHEN ... THEN > ROLLBACK TO s1; > ... code here ... > END; PostgreSQL doesn't have anonymous blocks. You need to write a function. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Function returns error (view)
Professor Flávio Brito wrote:
When I Test my view I receive
SELECT seach_password('user_login_foo')
[...]
ERROR: column "user_login_foo" does not exist
SQL state: 42703
Context: PL/pgSQL function "search_password" line 14 at for over execute
statement
seach_password and
search_password are different.
Perhaps you have two functions with similar names, and one is broken?
Colin
---(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: [SQL] Function returns error (view)
Hi
After I did it I received it
ERROR: set-valued function called in context that cannot accept a set
SQL state: 0A000
Context: PL/pgSQL function "seach_password(" line 14 at return next
Error at WHERE login= USER_FOO ??
Thanks for your help
Flávio
2008/2/26, Bart Degryse <[EMAIL PROTECTED]>:
>
> I think you have a quoting problem
> You want something like
> WHERE login= 'Flavo'
> But you're making something like
> WHERE login = Flavo
>
> Something like this should work...
> CREATE OR REPLACE FUNCTION seach_password(USER_FOO
> IN table_user.login%TYPE)
> RETURNS SETOF vw_change_password AS
> $BODY$
> DECLARE
> r vw_change_password%ROWTYPE;
> USER_FOO alias for $1;
> BEGIN
> FOR r IN (
> SELECT u.cod_user, u.user_name, u.openpsw, t.user_password, t.end,
> t.validate, t.date_add, t.user_time, u.ok
> FROM usuario u, change_user_password t
> WHERE u.cod_user = t.cod_user AND t.cod_user IN (SELECT cod_user FROM
> table_user WHERE login= USER_FOO))
> LOOP
> RETURN NEXT r;
> END LOOP;
> IF NOT FOUND THEN
> RAISE EXCEPTION 'USER not found (%)', USER_FOO;
> END IF;
> RETURN;
> END
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> >>> "Professor Flávio Brito" <[EMAIL PROTECTED]> 2008-02-26 17:32
> >>>
> Hi
>
> Don't know why I can't receive a return like my view fields (I'm newbie in
> plpgsql). Postgresql returns me a erro . How can I received a answer like my
> view structure?
>
> When I Test my view I receive
>
> SELECT seach_password('user_login_foo')
>
> My view returns me
>
> 25746;"MARCELO
> ";"bio1";"bio1";"2008-02-19";"FALSE";"2008-02-12";"2008-02-12 12:51:
> 40.229282";"TRUE"
> 30356;"JOSE DE JESUS
> ";"977";"377";"2008-02-19";"FALSE";"2008-02-12";"2008-02-12 12:52:
> 19.688381";"TRUE"
>
> It's OK
>
> but when I use function it returns me
>
> ERROR: column "user_login_foo" does not exist
> SQL state: 42703
> Context: PL/pgSQL function "search_password" line 14 at for over execute
> statement
>
> Where is my fault?
>
> Thanks
>
> Flávio
>
>
> *
> vw_change_password attributes
>
> cod_user integer,
> user_name varchar(150),
> openpsw varchar (32),
> user_password varchar (50),
> end timestamp,
> validate boolean,
> date_add timestamp,
> user_time timestamp,
> ok boolean
>
>
> CREATE OR REPLACE FUNCTION seach_password(USER_FOO varchar(100))
> RETURNS SETOF vw_change_password AS
> $BODY$
> DECLARE
> r vw_change_password%ROWTYPE;
> USER_FOO alias for $1;
> sql TEXT;
> BEGIN
> sql= 'SELECT u.cod_user, u.user_name, u.openpsw, t.user_password, t.end,
> t.validate, t.date_add, t.user_time, u.ok
>FROM usuario u, change_user_password t
> WHERE u.cod_user = t.cod_user AND t.cod_user
> IN
> (SELECT cod_user
> FROM table_user
> WHERE login='||USER_FOO||')';
>
> FOR r IN EXECUTE sql
> LOOP
> RETURN NEXT r;
> END LOOP;
> IF NOT FOUND THEN
> RAISE EXCEPTION 'USER not found', USER_FOO;
> END IF;
> RETURN;
> END
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
>
Re: [SQL] duplicate key violates unique constraint
Could it be that the insert statement itself is the problem? What does the
table look like?
Edward W. Rouse
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bart Degryse
Sent: Tuesday, February 26, 2008 11:35 AM
To: pgsql-sql@postgresql.org; Shavonne Marietta Wijesinghe
Subject: Re: [SQL] duplicate key violates unique constraint
Shavonne,
You will probably always find someone on the list who will answer your
questions, but you really should read the manual too!
In this case you could have found the answer by reading
http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
>>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> 2008-02-26 17:05 >>>
Thank you. I tried as you said. But i get a ERROR: syntax error at or near
"INSERT" at character 9
BEGIN
INSERT INTO my_shevi values ('a', 4, 2, 2);
EXCEPTION
WHEN unique_violation THEN
INSERT INTO my_shevi values ('a', 5, 2, 2);
END;
I don't see anything wrong with the code..
Shavonne
- Original Message -
From: "Teemu Torma" <[EMAIL PROTECTED]>
To:
Sent: Tuesday, February 26, 2008 4:17 PM
Subject: Re: [SQL] duplicate key violates unique constraint
> On Tuesday 26 February 2008, Shavonne Marietta Wijesinghe wrote:
>& gt; During an "INSERT INTO" I get an "Error - duplicate key violates
>> unique constraint"
>>
>> Is there any way, that i can test the error. Something like this??
>>
>> IF error = "duplicate key violates unique constraint" then
>> Â Â do something
>> else
>> Â Â insert into
>> end if
>
> insert into ...;
> exception when unique_violation then
> do something;
>
> Teemu
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Function returns error (view)
How do you call your function? You should call it like this:
SELECT * FROM seach_password('Flavio');
Replace Flavio with the login of someone in table_user.
Also watch out for the function name: if you copied my suggestion it is
seach_... and not search_...
I would also suggest you replace the
...t.cod_user IN (subselect)
by a join construction. I think it's more performant.
>>> "Professor Flávio Brito" <[EMAIL PROTECTED]> 2008-02-26
19:20 >>>
Hi
After I did it I received it
ERROR: set-valued function called in context that cannot accept a set
SQL state: 0A000
Context: PL/pgSQL function "seach_password(" line 14 at return next
Error at WHERE login= USER_FOO ??
Thanks for your help
Flávio
2008/2/26, Bart Degryse <[EMAIL PROTECTED]>:
I think you have a quoting problem
You want something like
WHERE login= 'Flavo'
But you're making something like
WHERE login = Flavo
Something like this should work...
CREATE OR REPLACE FUNCTION seach_password(USER_FOO IN
table_user.login%TYPE)
RETURNS SETOF vw_change_password AS
$BODY$
DECLARE
r vw_change_password%ROWTYPE;
USER_FOO alias for $1;
BEGIN
FOR r IN (
SELECT u.cod_user, u.user_name, u.openpsw, t.user_password, t.end,
t.validate, t.date_add, t.user_time, u.ok
FROM usuario u, change_user_password t
WHERE u.cod_user = t.cod_user AND t.cod_user IN (SELECT cod_user
FROM table_user WHERE login= USER_FOO))
LOOP
RETURN NEXT r;
END LOOP;
IF NOT FOUND THEN
RAISE EXCEPTION 'USER not found (%)', USER_FOO;
END IF;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
>>> "Professor Flávio Brito" <[EMAIL PROTECTED]> 2008-02-26
17:32 >>>
Hi
Don't know why I can't receive a return like my view fields (I'm newbie
in plpgsql). Postgresql returns me a erro . How can I received a answer
like my view structure?
When I Test my view I receive
SELECT seach_password('user_login_foo')
My view returns me
25746;"MARCELO
";"bio1";"bio1";"2008-02-19";"FALSE";"2008-02-12";"2008-02-12
12:51:40.229282";"TRUE"
30356;"JOSE DE JESUS
";"977";"377";"2008-02-19";"FALSE";"2008-02-12";"2008-02-12
12:52:19.688381";"TRUE"
It's OK
but when I use function it returns me
ERROR: column "user_login_foo" does not exist
SQL state: 42703
Context: PL/pgSQL function "search_password" line 14 at for over
execute statement
Where is my fault?
Thanks
Flávio
*
vw_change_password attributes
cod_user integer,
user_name varchar(150),
openpsw varchar (32),
user_password varchar (50),
end timestamp,
validate boolean,
date_add timestamp,
user_time timestamp,
ok boolean
CREATE OR REPLACE FUNCTION seach_password(USER_FOO varchar(100))
RETURNS SETOF vw_change_password AS
$BODY$
DECLARE
r vw_change_password%ROWTYPE;
USER_FOO alias for $1;
sql TEXT;
BEGIN
sql= 'SELECT u.cod_user, u.user_name, u.openpsw, t.user_password,
t.end, t.validate, t.date_add, t.user_time, u.ok
FROM usuario u, change_user_password t
WHERE u.cod_user = t.cod_user AND t.cod_user
IN
(SELECT cod_user
FROM table_user
WHERE login='||USER_FOO||')';
FOR r IN EXECUTE sql
LOOP
RETURN NEXT r;
END LOOP;
IF NOT FOUND THEN
RAISE EXCEPTION 'USER not found', USER_FOO;
END IF;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
