[SQL] undefined relations in pg_locks
Hi there, Sorry I repeat a problem which concerned me since two months, but I got no answer. It's not clear for me whether it is trivial or without a solution. I promisse not to repeat it in the future if I will give no answer this time too. So, I'm working with "PostgreSQL 8.2.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.0.2 20051125 (Red Hat 4.0.2-8)"; I have a procedure where a dead-lock occurs, and I'm trying to find the tables involved in the lock. A little description of how my procedure works maybe helps. So I have a loop where I call a second procedure with exception trapping. At its turn, it calls a third procedure. I guess an exception may occur in the third procedure, where I use a temporary table. When an exception occurs, I guess my temporary table table is not dropped, and this may lock the process when another call try to create that temporary table again. Could be this scenario what there happens to me ? Unfortunatelly, I don't find the related objects of the oids of "relation" field. Also all the fields "classid" and "objid" are null. May I suppose there were references to temporary tables? However, how cand I get the related objects involved in this lock, by other way than analyse deeply in the code ? TIA, Sabin -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] pl/PgSQL, variable names in NEW
Hello, is it possible to use variables as field names in the NEW record? Let's suppose I have a varchar attname containg the name of the field and I want to know a value that field of the NEW record. Problem is that I get an error 'record "new" has no field "attname"'. Of course I want to use a value of NEW.author when col.attname = attname = 'author'. Is there a solution? Example trigger function. It finds all columns in the table which are referenced in other tables and checks if the value of the column has changed. If yes, then invoke some other function. The problem is that the column name is in the 'col' record and is different during the loop and at each function call. CREATE OR REPLACE FUNCTION replace_values() RETURNS trigger AS $BODY$ DECLARE col record; attname varchar; BEGIN FOR col IN SELECT DISTINCT pgaf.attname, pgaf.attnum FROM pg_constraint, pg_attribute AS pgaf WHERE pg_constraint.contype = 'f' -- fkey AND pg_constraint.confrelid = TG_RELID -- table oid AND pgaf.attrelid = TG_RELID AND pgaf.attnum = ANY(pg_constraint.confkey) LOOP attname := col.attname; IF NEW.attname <> OLD.attname THEN RAISE NOTICE ' value changed from "%" to "%"', OLD.attname, NEW.attname; -- INVOKE OTHER FUNCTION END IF; END LOOP; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; -- Martin Edlman Fortech Ltd. 57001 Litomysl, CZ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pl/PgSQL, variable names in NEW
Hello no, it's not possible in plpgsql. Please, use plperl or plpython. Regards Pavel Stehule On 07/04/2008, Martin Edlman <[EMAIL PROTECTED]> wrote: > Hello, > > is it possible to use variables as field names in the NEW record? > Let's suppose I have a varchar attname containg the name of the field and I > want to know a value that field of the NEW record. > > Problem is that I get an error 'record "new" has no field "attname"'. Of > course I want to use a value of NEW.author when col.attname = attname = > 'author'. > > Is there a solution? > > Example trigger function. It finds all columns in the table which are > referenced in other tables and checks if the value of the column has > changed. If yes, then invoke some other function. The problem is that the > column name is in the 'col' record and is different during the loop and at > each function call. > > CREATE OR REPLACE FUNCTION replace_values() RETURNS trigger AS > $BODY$ > DECLARE > col record; > attname varchar; > BEGIN > FOR col IN > SELECT DISTINCT pgaf.attname, pgaf.attnum > FROM pg_constraint, pg_attribute AS pgaf > WHERE pg_constraint.contype = 'f' -- fkey > AND pg_constraint.confrelid = TG_RELID -- table oid > AND pgaf.attrelid = TG_RELID > AND pgaf.attnum = ANY(pg_constraint.confkey) LOOP > > attname := col.attname; > IF NEW.attname <> OLD.attname THEN > RAISE NOTICE ' value changed from "%" to > "%"', OLD.attname, NEW.attname; > -- INVOKE OTHER FUNCTION > END IF; > END LOOP; > > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > -- > Martin Edlman > Fortech Ltd. > 57001 Litomysl, CZ > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Problem commit in function
Hi, I need to execute COMMIT in a function pgsql, there is a way? Can I have any example? Thanks in advance. Luke. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem commit in function
On Mon, Apr 7, 2008 at 6:00 PM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hi, > I need to execute COMMIT in a function pgsql, > there is a way? > No. Transaction control statements like COMMIT/ROLLBACK/SAVEPOINT are not supported inside plpgsql functions. If you can tell us what you are trying to do, somebody may help you with that. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Commit every processed record
Hi, I have to execute commit for evey record that i processed during a cursor fetch in a function. There is a way to do it? Thanks in advance. Luke. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] how to returns set of records in PL/python
Hi , Iam new to plpython,how can I return a recordset from a plpython function? Is there is any way give me an example; plpgsql function CREATE OR REPLACE FUNCTION function_to_get_all_countries() RETURNS SETOF RECORD AS $BODY$ DECLARE r RECORD; BEGIN FOR r IN SELECT pk_bint_country_id,vchr_country FROM tbl_country LOOP RETURN NEXT r; END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql'; How i can get the same result using plpythonu and how I can call the function (is there any user defined type required like in plpgsql)? Thanks in advance Anoop
Re: [SQL] how to returns set of records in PL/python
Anoop G wrote:
Hi ,
Iam new to plpython,how can I return a recordset from a plpython
function?
Is there is any way give me an example;
plpgsql function
CREATE OR REPLACE FUNCTION function_to_get_all_countries() RETURNS SETOF
RECORD AS $BODY$
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT pk_bint_country_id,vchr_country FROM tbl_country LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END
$BODY$ LANGUAGE 'plpgsql';
How i can get the same result using plpythonu and how I can call the
function (is there any user defined type required like in plpgsql)?
Thanks in advance
Anoop
Yes, plpython can return setofs. You may need to define a new type for that.
After that you can return a list, tuple, dictionary, set, generator
object, or any
You may also check the plpython version, as i recall, its kind of *new*
issue.
This works on 8.2.5, python 2.5,
For a simple case, something like that would work
regression=# \d countries
Table "public.countries"
Column| Type| Modifiers
--+---+---
country_id | integer |
country_name | character varying |
CREATE OR REPLACE FUNCTION get_countries()
returns setof countries
security definer
as
$$
return plpy.execute("select * from countries")
$$ language plpythonu;
Hope that helps.
Gerardo
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to returns set of records in PL/python
Anoop G wrote: Hi , Iam new to plpython,how can I return a recordset from a plpython function? Is there is any way give me an example; plpgsql function CREATE OR REPLACE FUNCTION function_to_get_all_countries() RETURNS SETOF RECORD AS $BODY$ DECLARE r RECORD; BEGIN FOR r IN SELECT pk_bint_country_id,vchr_country FROM tbl_country LOOP RETURN NEXT r; END LOOP; RETURN; END $BODY$ LANGUAGE 'plpgsql'; How i can get the same result using plpythonu and how I can call the function (is there any user defined type required like in plpgsql)? Thanks in advance Anoop Forgot to mention that postgres home page has some info about plpython. http://www.postgresql.org/docs/8.3/static/plpython-funcs.html Cya. Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem commit in function
[EMAIL PROTECTED] wrote: Hi, I need to execute COMMIT in a function pgsql, there is a way? A function runs in a transaction. It cannot, as far as I know, commit one. Why do you need to do that, anyway? What're you trying to achieve? -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Commit every processed record
[EMAIL PROTECTED] wrote: Hi, I have to execute commit for evey record that i processed during a cursor fetch in a function. There is a way to do it? Sure. Do it outside PL/PgSQL, using a database client API like those available for Python, Java, Perl, etc. If you really need to do it in PL/PgSQL, is there any reason you can't do all the work in one transaction and commit when the function finishes? -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Commit every processed record
am Mon, dem 07.04.2008, um 14:46:50 +0200 mailte [EMAIL PROTECTED] folgendes: > Hi, > I have to execute commit for evey record that i processed during a cursor > fetch in a function. > There is a way to do it? No. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] undefined relations in pg_locks
"Sabin Coanda" <[EMAIL PROTECTED]> writes: > Unfortunatelly, I don't find the related objects of the oids of "relation" > field. Also all the fields "classid" and "objid" are null. > May I suppose there were references to temporary tables? Are you sure they are in the same database you are working in? If the locktype is "relation" and the database column matches your database's OID, the relation column definitely ought to match some OID in pg_class. It is possible that you can't see the pg_class row because the relation was created in a transaction that hasn't committed yet --- but such an entry could never be a reason for a deadlock, because no other transaction could be trying to lock it. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Commit every processed record
Well, actually there is. Do the processing in a plperlu function which uses it's own connection to the db. Then every instance of the function will have it's own transaction. Try to start that perl connection outside the function or your performance will drop too much. I use this technique to fetch, process and store records from an oracle db (erp) in a postgresql db (datawarehousing/reporting/external data). It gets me some 500,000 records in little over 50 minutes. Probably real perl addicts would have a lot to say about my coding, but being a perl illiterate (does one write it like this?) I must say that I'm quite happy with the result. All I used was the perl chapter in the postgresql manual (big hurray for the manual), perl express for testing and debugging of a lot of very basic things like hashes (yes, illiterate !-) and EMS SQL Manager. Good luck >>> "A. Kretschmer" <[EMAIL PROTECTED]> 2008-04-07 15:01 >>> am Mon, dem 07.04.2008, um 14:46:50 +0200 mailte [EMAIL PROTECTED] folgendes: > Hi, > I have to execute commit for evey record that i processed during a cursor > fetch in a function. > There is a way to do it? No. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ( http://wwwkeys.de.pgp.net/ ) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] advocacy: case studies
Can it be one of those things that end up in the "case studies" http://www.postgresql.org/about/casestudies/ http://www.theregister.co.uk/2008/04/03/xtremedata_fpga_xeon_data_warehouse/ "The XtremeDB is built from PostgreSQL – a full-featured, open-source RDBMS, that has been re-engineered by XDI," the company says in a newsletter. "All of the front-end PostgreSQL interfaces have been maintained intact and the back-end execution engine has been transformed to leverage the shared-nothing parallel cluster environment with FPGA acceleration." ? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] advocacy: case studies
That's really cool. On Apr 7, 2008, at 3:33 PM, Ivan Sergio Borgonovo wrote: Can it be one of those things that end up in the "case studies" http://www.postgresql.org/about/casestudies/ http://www.theregister.co.uk/2008/04/03/xtremedata_fpga_xeon_data_warehouse/ "The XtremeDB is built from PostgreSQL – a full-featured, open-source RDBMS, that has been re-engineered by XDI," the company says in a newsletter. "All of the front-end PostgreSQL interfaces have been maintained intact and the back-end execution engine has been transformed to leverage the shared-nothing parallel cluster environment with FPGA acceleration." ? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
