Re: [GENERAL] Footnote: ISP provider with postgres and perl dbi
After carefully examining what was out there, decided again to go with hub.org. Thanks again John On Apr 24, 2010, at 5:55 PM, Steve Atkins wrote: If an ISP is offering virtual private servers (where you get full access to your own virtual machine) then installing postgresql and perl on them will be trivial (just one command on popular linux distributions). So if you're wanting to run the database in your VM then most anyone offering VPS hosting will have what you need. You're unlikely to get perl or postgresql specific support - but your ISP isn't where you'd usually look for that. If you want somewhere that offers both virtual machines and managed postgresql hosting that's tougher, but http://www.postgresql.org/support/professional_hosting is probably a good place to start. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unable to run createlang (or psql for that matter)
If I open a bash terminal and type createlang -l, I get: JohnGage:~ johngage$ createlang -l -bash: createlang: command not found JohnGage:~ johngage$ psql -bash: psql: command not found as one can see, the same thing happens with psql. The way I have been using psql is by pulling down the plugins menu in pgAdmin3 and selecting (the only selection) psql. My $PATH variable includes the path to both createlang and psql. What am I doing wrong? John -- 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] Invalid objects
On 25 Apr 2010, at 07:34, Scott Bailey wrote: I can point you to the relevant code in GitHub if you're interested (it's Java). Absolutely. Thanks Scott Bailey Line 813 of http://github.com/okohll/agileBase/blob/master/gtpb_server/src/com/gtwm/pb/model/manageSchema/DatabaseDefn.java - private void updateViewDbAction is the top level function. Regards Oliver Kohll -- 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] UPDATE01: Unable to run createlang (or psql for that matter)
I have made some progress by looking at the documentation and at how Mac OSX sets up privileges. If I su to root and then su to postgres, I can access createlang and psql (although my user $PATH seems to be inoperative). What I can't do is su from me as user (not root) to postgres. I get asked for a password that is not any password that exists. For example, it is not the password that postgres uses to get to the databases. I am now going to see if I can find the mystery password for postgres. On Apr 25, 2010, at 11:08 AM, John Gage wrote: If I open a bash terminal and type createlang -l, I get: JohnGage:~ johngage$ createlang -l -bash: createlang: command not found JohnGage:~ johngage$ psql -bash: psql: command not found as one can see, the same thing happens with psql. The way I have been using psql is by pulling down the plugins menu in pgAdmin3 and selecting (the only selection) psql. My $PATH variable includes the path to both createlang and psql. What am I doing wrong? John -- 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] UPDATE02Unable to run createlang (or psql for that matter)
Question withdrawn. Answered via documentation. On Apr 25, 2010, at 11:08 AM, John Gage wrote: If I open a bash terminal and type createlang -l, I get: JohnGage:~ johngage$ createlang -l -bash: createlang: command not found JohnGage:~ johngage$ psql -bash: psql: command not found as one can see, the same thing happens with psql. The way I have been using psql is by pulling down the plugins menu in pgAdmin3 and selecting (the only selection) psql. My $PATH variable includes the path to both createlang and psql. What am I doing wrong? John -- 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
[GENERAL] User with GRANTS only on Views. Lock table on function will work?
Hi, I have a database were the users only can do operations using views, they have not access to physical tables. But I have a function with a lock on a physical table. Can I allow this users to run a function that locks a physical table? Best Regards,
[GENERAL] debug pgpool
Hi I am using DBT-2 database and I use pgpoolII 2.1 with that. I have modified the function pool_process_query.c in pgpoolII2.1. and I link pgpoolII2.1 with dbt2. for some reason the modified functionality doesnt work. I tried to debug pgpoolII2.1 using gdb. I set the beakpoint at pool_process_query.c and waited but it didnt resposnd. Does pgpool II 2.1 work with gdb? is there any way to debug the code? please Help. Thanks -- Megha
Re: [GENERAL] User with GRANTS only on Views. Lock table on function will work?
On 25/04/2010 13:24, Andre Lopes wrote: Hi, I have a database were the users only can do operations using views, they have not access to physical tables. But I have a function with a lock on a physical table. Can I allow this users to run a function that locks a physical table? You can create a function with SECURITY DEFINER, allowing it to be run with the privileges of the users who created it rather than the user who is running it. See here: http://www.postgresql.org/docs/8.4/static/sql-createfunction.html Would this do what you need? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] R: Catch exception from plpy
Hi Martin,
Thanks for your help ...But I still don' get it!
I edited your code to correct what I think are some typos but I still get the
error message. What am I doing wrong!?
Dario
--
create or replace function test_tryex() returns void AS
$$
try:
plpy.execute('create table tmp_foo (v1 int);')
plpy.execute('select * from tmp_foo);')
except Exception, ex:
plpy.notice(FUBAR!-- %s % str(ex))
return FUBAR!
return test_tryex function has succeeded
$$
LANGUAGE plpythonu;
--
select test_tryex();
--
WARNING: plpython: in function test_tryex:
DETAIL: class 'plpy.SPIError': Unknown error in PLy_spi_execute_query
NOTICE: ('FUBAR!-- error return without exception set',)
ERROR: relation tmp_foo already exists
CONTEXT: SQL statement create table tmp_foo (v1 int);
** Error **
ERROR: relation tmp_foo already exists
SQL state: 42P07
Context: SQL statement create table tmp_foo (v1 int);
--
Messaggio originale
Da: dario@libero.it
Data: 24/04/2010 14.48
A: pgsql-general@postgresql.org
Ogg: Catch exception from plpy
Hello,
Could someone show me how to catch exceptions generated by plpy.execute()?
From the documentation and other posts I understand that you need to call
plpy.
error() but I still cannot figure out how to use it.
For example, say I need a function that creates table foo if it doesn't
exists, otherwise returns the rows in foo. Initially I thought the following
shouldl work... but it doesn't!
--
create or replace function test_tryex() returns void AS $$
try:
plpy.execute('create table tmp_foo (v1 int);')
except:
plpy.execute('select * from tmp_foo;')
$$
language plpythonu;
--
-- If tmp_foo already exists I get:
select test_tryex();
** Error **
ERROR: relation tmp_foo already exists
SQL state: 42P07
Context: SQL statement create table tmp_foo (v1 int);
Many thanks in advance
Dario
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem saving emails to database.
Hi, It is the first time that I store emails in a database to send them later... Let me explain the problem... I'am sending text emails, and to break the lines of the message I use \n. The first problem was to stores the \. To INSERT \n I need to write \\n. When I do a SELECT I see \n but when I dump the database in the INSERTS I see \\n. The problem with this is that when I send an email the \n that I see in the SELECT returns simply n in the email and does not break the line. This problem have solution? Best Regards,
[GENERAL] ALTER Bigserial error
Hi, I'm trying to alter in a bigserial on GP/PG 8.1 I'm getting the error ERROR: type bigserial does not exist SQL state: 42704 I understand that bigserial isn't a 'true' type, that it's a notational convenience. And that I can alter in the default. But it is listed in the type table in the doc and I can create new tables with it. I should be able to use it in an alter. Is there a way to fix this bug? What would need to change for this issue to be resolved. Thanks Doug Little Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741 douglas.lit...@orbitz.commailto:douglas.lit...@orbitz.com [cid:image001.jpg@01CAE475.3ACF6F60] orbitz.comhttp://www.orbitz.com/ | ebookers.comhttp://www.ebookers.com/ | hotelclub.comhttp://www.hotelclub.com/ | cheaptickets.comhttp://www.cheaptickets.com/ | ratestogo.comhttp://www.ratestogo.com/ | asiahotels.comhttp://www.asiahotels.com/ inline: image001.jpg
[GENERAL] Plpgsql function syntax error at first coalesce statement
Hi all,
I'm trying to write my first plpgsql function and I'm running into a
problem that may or may not have to do with a coalesce statement.
I wrote a very similar sql function that does basically the same thing
for just one trainer where I pass in an id number and that one works fine.
I re-used much of the code from that one to write this plpgsql function
that is supposed to retrieve all trainers. I'm using the example in 38.6.4
http://www.postgresql.org/docs/8.4/interactive/plpgsql-implementation.html#PLPGSQL-RECORDS-ITERATING
as my basic template.
CREATE or replace FUNCTION view_all_trainers()
returns table (
pp_id integer,
tr_id integer,
pp_first_name text,
pp_last_name text,
pp_address text,
pp_city text,
pp_state text,
pp_zip text,
pp_county text,
email text,
phone text,
status text,
availability text,
west_ed boolean,
cda boolean,
blood_borne boolean,
fire_safety boolean,
med_admin boolean,
first_aid_cpr boolean,
child_abuse boolean,
staff_orientation boolean,
cacfp boolean,
other boolean,
HNS boolean,
ALE boolean,
CGD boolean,
GD boolean,
FR boolean,
PM boolean,
P boolean,
UCA boolean) AS $$
DECLARE
trainer RECORD;
BEGIN
FOR trainer IN SELECT tr_pp_id FROM trainers where tr_pp_id is not null
LOOP
SELECT
pp_id,
tr_id,
pp_first_name,
pp_last_name,
pp_address,
pp_city,
pp_state,
pp_zip,
pp_county,
coalesce(pp_email,'No E-Mail Address') as email,
coalesce(to_char(pp_work_phone::bigint,'FM(999)
999-'),'No Work Phone')
|| coalesce(' Ext. ' || pp_work_phone_extension,'') as phone,
tr_date_name as status,
case
when (select trs_tr_will_train from trainers_trainer_will_train
where trs_tr_will_train_pp_id = trainer.tr_pp_id) 1
then 'Any Location'
else 'In House Only'
end as availability,
case
when (select trs_tr_cat_id from trainers_trainer_categories
where trs_tr_cat_id = 1 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
is not null then 't'::boolean
else 'f'::boolean
end as west_ed,
case
when (select trs_tr_cat_id from trainers_trainer_categories
where trs_tr_cat_id = 2 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
is not null then 't'::boolean
else 'f'::boolean
end as cda,
case
when (select trs_tr_cat_id from trainers_trainer_categories
where trs_tr_cat_id = 3 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
is not null then 't'::boolean
else 'f'::boolean
end as blood_borne,
case
when (select trs_tr_cat_id from trainers_trainer_categories
where trs_tr_cat_id = 4 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
is not null then 't'::boolean
else 'f'::boolean
end as fire_safety,
case
when (select trs_tr_cat_id from trainers_trainer_categories
where trs_tr_cat_id = 5 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
is not null then 't'::boolean
else 'f'::boolean
end as med_admin,
case
when (select trs_tr_cat_id from trainers_trainer_categories
where trs_tr_cat_id = 6 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
is not null then 't'::boolean
else 'f'::boolean
end as first_aid_cpr,
case
when (select trs_tr_cat_id from trainers_trainer_categories
where trs_tr_cat_id = 7 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
is not null then 't'::boolean
else 'f'::boolean
end as child_abuse,
case
when (select trs_tr_cat_id from trainers_trainer_categories
where trs_tr_cat_id = 8 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
is not null then 't'::boolean
else 'f'::boolean
end as staff_orientation,
case
when (select trs_tr_cat_id from trainers_trainer_categories
where trs_tr_cat_id = 9 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
is not null then 't'::boolean
else 'f'::boolean
end as cacfp,
case
when (select trs_tr_cat_id from trainers_trainer_categories
where trs_tr_cat_id = 10 and trs_tr_cat_pp_id =
trainer.tr_pp_id)
is not null then 't'::boolean
else 'f'::boolean
end as other,
case
when (select sum(trs_tr_level) from trainers_trainer_levels
where trs_tr_level_core_area_id = 1 and
trs_tr_level_pp_id = trainer.tr_pp_id)
is
Re: [GENERAL] Problem saving emails to database.
On 25/04/2010 18:31, Andre Lopes wrote: Let me explain the problem... I'am sending text emails, and to break the lines of the message I use \n. The first problem was to stores the \. To INSERT \n I need to write \\n. When I do a SELECT I see \n but when I dump the database in the INSERTS I see \\n. I think, strictly speaking, you should have E'\\n' in the INSERT in order to escape the backslashes properly: postgres=# select '\\n'; WARNING: nonstandard use of \\ in a string literal LINE 1: select '\\n'; ^ HINT: Use the escape string syntax for backslashes, e.g., E'\\'. ?column? -- \n (1 row) postgres=# select E'\\n'; ?column? -- \n (1 row) The problem with this is that when I send an email the \n that I see in the SELECT returns simply n in the email and does not break the line. What are you using to run the queries and generate the emails? PHP? It sounds to me as if something else is doing something funny with the backslash after it gets it from the database. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Plpgsql function syntax error at first coalesce statement
Jeff Ross jr...@wykids.org writes: I'm trying to write my first plpgsql function and I'm running into a problem that may or may not have to do with a coalesce statement. No, it's not the coalesce ... When I try to run this I get the following error: jr...@acer:/var/www/stars/sql $ psql -f view_all_trainers.sql wykids psql:view_all_trainers.sql:189: ERROR: syntax error at or near $10 LINE 1: ... $9 , coalesce(pp_email,'No E-Mail Address') as $10 , coal... ^ The problem here is that you've got a collision between a plpgsql parameter name (email) and a name you are trying to use in the SELECT statement for a different purpose (as email is trying to label a result column of the SELECT). plpgsql isn't bright enough to figure out that you didn't mean for it to substitute the parameter's value into the SELECT at that point, so it tries to do so, via the $10 you can see there. (This will get improved in PG 9.0, but that doesn't help you today.) You need to avoid such naming conflicts. In this particular case it might be practical to just drop the AS clauses. In general it's a good plan to use a separate naming convention for parameters and plpgsql variables, such as prepending p_ or v_ to their names. regards, tom lane -- 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] ALTER Bigserial error
Little, Douglas douglas.lit...@orbitz.com writes: I'm trying to alter in a bigserial on GP/PG 8.1 I'm getting the error ERROR: type bigserial does not exist SQL state: 42704 I understand that bigserial isn't a 'true' type, that it's a notational convenience. And that I can alter in the default. But it is listed in the type table in the doc and I can create new tables with it. I should be able to use it in an alter. Is there a way to fix this bug? It isn't a bug; it could be argued to be a missing feature, but I don't think it's very high on anyone's priority list to add. Before anything could happen here there would need to be consensus on how to behave in all the various corner cases. For instance, should the ALTER override any pre-existing default expression for the column? If there's not already a sequence associated with the column, what initial value should it be created with? Should the ALTER make any attempt to check or correct the data in the column? The only case that seems to me to not have some debatable behavior involved is widening an existing serial column --- and you can do that now with ALTER TYPE bigint. Since serial/bigserial are just macros for column properties that you can set explicitly, it's always possible to get where you want to go with lower-level operations; and those operations give you full control over what happens, whereas a packaged-up ALTER TYPE bigserial operation wouldn't. regards, tom lane -- 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 me stop postgres from crashing.
On Apr 24, 4:13 pm, t...@sss.pgh.pa.us (Tom Lane) wrote: Sam s...@palo-verde.us writes: A particular web application I am working has a staging version running one a vps, and a production version running on another vps. They both get about the same usage, but the production version keeps crashing and has to be re-started daily for the last couple days. The log file at the time of crash looks like this: LOG: could not accept new connection: Cannot allocate memory LOG: select() failed in postmaster: Cannot allocate memory This looks like a system-level memory shortage. You might find useful information in the kernel log. I'd suggest enabling timestamps in the PG log (see log_line_prefix) so that you can correlate events in the two log files. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Thanks, for the responses. I've enabled the timestamps on the log lines. -- 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 me stop postgres from crashing.
On Apr 24, 3:53 pm, thombr...@gmail.com (Thom Brown) wrote: On 24 April 2010 18:48, Sam s...@palo-verde.us wrote: Hi, I am a web developer, I've been using postgesql for a few years but administratively I am a novice. A particular web application I am working has a staging version running one a vps, and a production version running on another vps. They both get about the same usage, but the production version keeps crashing and has to be re-started daily for the last couple days. The log file at the time of crash looks like this: LOG: could not accept new connection: Cannot allocate memory LOG: select() failed in postmaster: Cannot allocate memory FATAL: semctl(2457615, 0, SETVAL, 0) failed: Invalid argument LOG: logger shutting down LOG: database system was interrupted at 2010-04-24 09:33:39 PDT It ran out of memory. I am looking for a way to track down what is actually causing the memory shortage and how to prevent it or increase the memory available. The vps in question is a media temple DV running CentOS and postgres 8.1.18 Could you provide some more information? What do you get if you run sysctl -a | grep kernel.shm and sysctl -a | grep sem? And what are you developing in which connects to the database? Are you using persistent connections? And how many connections to you estimate are in use? What have you got max_connections and shared_buffers in your postgresql.conf file? And how much memory does your VPS have? Thom This application is php5/Zend Framework and using Doctrine ORM which manages the database connections, but they aren't persistent. max_connections is 100 and shared_buffers is 1000 What is the best way to profile the exact number of connections (for future reference)? Right now, there is almost no site usage, the site is not launched yet. The staging version of the site has been running for months without issues. There is a cron that runs every three minutes and checks a users account on another web service and tracks that activity. -- 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 me stop postgres from crashing.
On Apr 24, 3:53 pm, thombr...@gmail.com (Thom Brown) wrote: On 24 April 2010 18:48, Sam s...@palo-verde.us wrote: Hi, I am a web developer, I've been using postgesql for a few years but administratively I am a novice. A particular web application I am working has a staging version running one a vps, and a production version running on another vps. They both get about the same usage, but the production version keeps crashing and has to be re-started daily for the last couple days. The log file at the time of crash looks like this: LOG: could not accept new connection: Cannot allocate memory LOG: select() failed in postmaster: Cannot allocate memory FATAL: semctl(2457615, 0, SETVAL, 0) failed: Invalid argument LOG: logger shutting down LOG: database system was interrupted at 2010-04-24 09:33:39 PDT It ran out of memory. I am looking for a way to track down what is actually causing the memory shortage and how to prevent it or increase the memory available. The vps in question is a media temple DV running CentOS and postgres 8.1.18 Could you provide some more information? What do you get if you run sysctl -a | grep kernel.shm and sysctl -a | grep sem? And what are you developing in which connects to the database? Are you using persistent connections? And how many connections to you estimate are in use? What have you got max_connections and shared_buffers in your postgresql.conf file? And how much memory does your VPS have? Thom sysctl -a | grep kernel.shm error: Operation not permitted reading key kernel.cap-bound kernel.shmmni = 4096 kernel.shmall = 2097152 kernel.shmmax = 33554432 sysctl -a | grep sem error: Operation not permitted reading key kernel.cap-bound kernel.sem = 25032000 32 128 -- 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] Plpgsql function syntax error at first coalesce statement
On 04/25/10 12:32, Tom Lane wrote: Jeff Rossjr...@wykids.org writes: I'm trying to write my first plpgsql function and I'm running into a problem that may or may not have to do with a coalesce statement. No, it's not the coalesce ... When I try to run this I get the following error: jr...@acer:/var/www/stars/sql $ psql -f view_all_trainers.sql wykids psql:view_all_trainers.sql:189: ERROR: syntax error at or near $10 LINE 1: ... $9 , coalesce(pp_email,'No E-Mail Address') as $10 , coal... ^ The problem here is that you've got a collision between a plpgsql parameter name (email) and a name you are trying to use in the SELECT statement for a different purpose (as email is trying to label a result column of the SELECT). plpgsql isn't bright enough to figure out that you didn't mean for it to substitute the parameter's value into the SELECT at that point, so it tries to do so, via the $10 you can see there. (This will get improved in PG 9.0, but that doesn't help you today.) You need to avoid such naming conflicts. In this particular case it might be practical to just drop the AS clauses. In general it's a good plan to use a separate naming convention for parameters and plpgsql variables, such as prepending p_ or v_ to their names. regards, tom lane Thanks as always, Tom. I dropped the AS clauses and it runs and makes a function but now I have a different error: wykids=# select * from view_all_trainers(); ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function view_all_trainers line 6 at SQL statement Now I'm *really* confused. I thought the table structure I created at the beginning of the function was where the results would be returned to. I tried a variety of queries including select into and create table but they didn't work either. Jeff -- 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] Plpgsql function syntax error at first coalesce statement
On 25/04/2010 20:50, Jeff Ross wrote: Now I'm *really* confused. I thought the table structure I created at the beginning of the function was where the results would be returned to. I tried a variety of queries including select into and create table but they didn't work either. I think you have to do RETURN NEXT inside the loop: create function declare return_row record; ... begin ... for.. loop select ... into return_row; return next return_row; end loop; ... return; end; Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Plpgsql function syntax error at first coalesce statement
Jeff Ross jr...@wykids.org writes: Now I'm *really* confused. I thought the table structure I created at the beginning of the function was where the results would be returned to. Uh, you're using that as the destination for the FOR loop's SELECT. What exactly is the purpose of having a second SELECT within the loop? regards, tom lane -- 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] Plpgsql function syntax error at first coalesce statement
On 04/25/10 14:20, Tom Lane wrote: Jeff Rossjr...@wykids.org writes: Now I'm *really* confused. I thought the table structure I created at the beginning of the function was where the results would be returned to. Uh, you're using that as the destination for the FOR loop's SELECT. What exactly is the purpose of having a second SELECT within the loop? regards, tom lane I am only an egg. How else do I get the results I want--name, address, city, state, and so on through the list out to whatever calls this function? Jeff -- 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] Plpgsql function syntax error at first coalesce statement
Jeff Ross jr...@wykids.org writes: On 04/25/10 14:20, Tom Lane wrote: Uh, you're using that as the destination for the FOR loop's SELECT. What exactly is the purpose of having a second SELECT within the loop? How else do I get the results I want--name, address, city, state, and so on through the list out to whatever calls this function? Well, you could do SELECT all-that-stuff INTO some-record-variable FROM ...; RETURN NEXT some-record-variable; which is more or less what the error message is suggesting. Or you could merge the computations you want into the first SELECT (the one in the FOR) and just RETURN NEXT directly from the FOR's loop variable, instead of having two record variables. Or you could eliminate the explicit loop altogether and just RETURN QUERY one-big-query (if you're using a PG version new enough to have RETURN QUERY). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help with pgpool-dbt2 database
Hi I am new with pgpool ,postgres and Database systems. I am using pgpool II2.1, postgresql 8.3.9 and DBT2 benchmark. Can anyone please tell me what kind of changes I need to make in pgpool II2.1 config file and postgresql config file to run it with DBT2 databsae system? I am running pgpool with DBT2 . It shows that pgpool started but when I tried to debug it , it doesnt show me the desired function executed. Please anyone help... Thanks, -- Megha
Re: [GENERAL] Lock table, best option?
On Sat, Apr 24, 2010 at 4:46 PM, Andre Lopes lopes80an...@gmail.com wrote: I need to do a SELECT and an UPDATE, but I will have concurrent processes doing the same task. How can I prevent that the concurrent task don't have the same results in the SELECT? Locking a table? How can I do that? It sounds like you might be looking for SELECT ... FOR UPDATE, see: http://www.postgresql.org/docs/current/static/sql-select.html Basically, you could have each transaction issue SELECT ... FOR UPDATE for rows intended to be updated later. Only one transaction would be able to acquire the necessary locks for the same rows at the same time; the other transaction(s) would block until the locks are released by the first transaction's commit, and then would see the new values. This paragraph assumes you're using the default read committed transaction isolation level, you might want to read more at: http://www.postgresql.org/docs/current/static/transaction-iso.html And if you really want to know about full table locking, you can read more at: http://www.postgresql.org/docs/current/static/sql-lock.html though it doesn't sound like you'll actually need full table locks. Josh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Deadlock occur while creating new table to be used in partition.
Currently, I have a stored procedure(get_existing_or_create_lot), which will be
called by 2 or more processes simultaneously.
Every process will have a unique lot name. What the store procedure does it
1) Insert lot name into lot table. A unique lot id will be returned after
insertion into lot table.
2) Check if unit_{id} table does exist. For example, if the returned lot id is
14, PostgreSQL will check whether unit_14 table does exist. If no, CREATE
TABLE unit_14... will be executed.
The stored procedure code is as follow :
CREATE OR REPLACE FUNCTION get_existing_or_create_lot(text)
RETURNS TABLE(_lot_id int) AS
$BODY$DECLARE
_param_name ALIAS FOR $1;
_lot lot;
unit_table_index int;
unit_table_name text;
BEGIN
-- Insert lot name into lot table.
INSERT INTO lot(name) VALUES(_param_name) RETURNING * INTO _lot;
unit_table_index = _lot.lot_id;
unit_table_name = 'unit_' || unit_table_index;
IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name =
unit_table_name) THEN
EXECUTE 'CREATE TABLE ' || quote_ident(unit_table_name) || '
(
unit_id serial NOT NULL,
fk_lot_id int NOT NULL,
CHECK (fk_lot_id = ' || (unit_table_index) || '),
CONSTRAINT pk_unit_' || unit_table_index || '_id PRIMARY KEY
(unit_id),
CONSTRAINT fk_lot_' || unit_table_index || '_id FOREIGN KEY
(fk_lot_id) REFERENCES lot (lot_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE
CASCADE
) INHERITS (unit);';
EXECUTE 'CREATE INDEX fk_lot_' || unit_table_index || '_id_idx ON ' ||
quote_ident(unit_table_name) || '(fk_lot_id);';
END IF;
Unfortunately, I get the run time error ;
2010-04-26 13:28:28 MYTERROR: deadlock detected
2010-04-26 13:28:28 MYTDETAIL: Process 436 waits for AccessExclusiveLock on
relation 46757 of database 46753; blocked by process 4060.
Process 4060 waits for AccessExclusiveLock on relation 46757 of
database 46753; blocked by process 436.
Process 436: SELECT * FROM get_existing_or_create_lot('Testing02')
Process 4060: SELECT * FROM get_existing_or_create_lot('Testing02')
2010-04-26 13:28:28 MYTHINT: See server log for query details.
2010-04-26 13:28:28 MYTCONTEXT: SQL statement CREATE TABLE unit_16
(
unit_id serial NOT NULL,
fk_lot_id int NOT NULL,
CHECK (fk_lot_id = 16),
CONSTRAINT pk_unit_16_id PRIMARY KEY (unit_id),
CONSTRAINT fk_lot_16_id FOREIGN KEY (fk_lot_id) REFERENCES
lot (lot_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE
) INHERITS (unit);
PL/pgSQL function get_existing_or_create_lot line 39 at EXECUTE
statement
2010-04-26 13:28:28 MYTSTATEMENT: SELECT * FROM
get_existing_or_create_lot('Testing02')
May I know why does deadlock happen? How can I avoid?
Thanks and Regards
Yan Cheng CHEOK
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
